transaction savepoints
Hi I'm running mysql 4.0.14 I'm doing an update from a webpage involving 3 tabIes, using 3 update queries and need to be able to roll back to before the first update query if the second or third query go pear shape. So I need to set a savepoint to rollback to if anyone could show me a an example of the code necessary it would be appreciated. Thanks Bob
Case (in)sensitive table names, 4.0.15, OSX, InnoDB
Hi. We are migrating our Solaris setup to an OSX server. I used InnoDB Hot Backup to copy the InnoDB files, and copied the .frm files for each of the databases. Under OSX I connect top the server and it sees the databases. If I use some_database where some_database has mixed case table names I see errors such as this: mysql use some_database Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'deletedForum' Didn't find any fields in table 'deletedForumProp' Didn't find any fields in table 'deletedMessage' Didn't find any fields in table 'deletedMessageProp' If I show tables; I get a list such as this: mysql show tables; +-+ | Tables_in_some_database | +-+ | deletedForum| | deletedForumProp| | deletedMessage | | deletedMessageProp | If I try to select data in a field I get errors such as this: mysql select * from deletedMessage; ERROR 1146: Table 'some_database.deletedmessage' doesn't exist If I show table status; I get information such as this (sorry, this is going to be messy - but you get the idea): mysql show table status; +-+--++--++- +-+--+---++- +-+++--- -+ | Name| Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +-+--++--++- +-+--+---++- +-+++--- -+ | deletedForum| NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Table 'some_database.deletedforum' doesn't exist| | deletedForumProp| NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Table 'some_database.deletedforumprop' doesn't exist| | deletedMessage | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Table 'some_database.deletedmessage' doesn't exist | | deletedMessageProp | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Table 'some_database.deletedmessageprop' doesn't exist | In another database which has only lower case table names I have no problems and all works well. Given I ask to select data from deletedMessage and it tells me there is no table .deletedmessage something somewhere dropped the uppercase in the table name... The idea of making all the table names lower case isn't a good one, there are 50+ databases, with 40+ tables each, and over 100 applications that call these databases all with innumerable table calls. I don't know if it's an InnoDB issue, an OSX issue or what's going on or how I might solve it. I don't seem to see anything on the list archive about this. Any ideas? Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REFERENCES question in mySQL 4.1 - Take 2
On Fri, 2003-09-26 at 01:14, Heikki Tuuri wrote: CREATE TABLE phone (id char(5) not null REFERENCES person(id), num char(10) not null, PRIMARY KEY (id, num)); It simply ignores that REFERENCES definition. It is in the TODO list to add support for that syntax, too. Until then you have to use the 'long' syntax: Is it possible to make it generate an error when the wrong syntax occure, as ignoring the FK syntax is not very assuring ? /BL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Case (in)sensitive table names, 4.0.15, OSX, InnoDB
Further examination of the documents reveals a variable that addresses this... I fixed it with this entry in my.cnf: set-variable= lower_case_table_name=0 According to the MySQL manual lower_case_table_name defaults to 0 for all instances except Windows, where it defaults to 1... Not clear why this instance was behaving as if it was set to 1. Best Regards, Bruce On 9/25/03 11:27 PM, Bruce Dembecki [EMAIL PROTECTED] wrote: Hi. We are migrating our Solaris setup to an OSX server. I used InnoDB Hot Backup to copy the InnoDB files, and copied the .frm files for each of the databases. Under OSX I connect top the server and it sees the databases. If I use some_database where some_database has mixed case table names I see errors such as this: mysql use some_database Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'deletedForum' Didn't find any fields in table 'deletedForumProp' Didn't find any fields in table 'deletedMessage' Didn't find any fields in table 'deletedMessageProp' If I show tables; I get a list such as this: mysql show tables; +-+ | Tables_in_some_database | +-+ | deletedForum| | deletedForumProp| | deletedMessage | | deletedMessageProp | If I try to select data in a field I get errors such as this: mysql select * from deletedMessage; ERROR 1146: Table 'some_database.deletedmessage' doesn't exist If I show table status; I get information such as this (sorry, this is going to be messy - but you get the idea): mysql show table status; +-+--++--++--- --+-+--+---++- +-+++- ---+ | Name| Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +-+--++--++--- --+-+--+---++- +-+++- ---+ | deletedForum| NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Table 'some_database.deletedforum' doesn't exist| | deletedForumProp| NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Table 'some_database.deletedforumprop' doesn't exist| | deletedMessage | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Table 'some_database.deletedmessage' doesn't exist | | deletedMessageProp | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL | NULL | Table 'some_database.deletedmessageprop' doesn't exist | In another database which has only lower case table names I have no problems and all works well. Given I ask to select data from deletedMessage and it tells me there is no table .deletedmessage something somewhere dropped the uppercase in the table name... The idea of making all the table names lower case isn't a good one, there are 50+ databases, with 40+ tables each, and over 100 applications that call these databases all with innumerable table calls. I don't know if it's an InnoDB issue, an OSX issue or what's going on or how I might solve it. I don't seem to see anything on the list archive about this. Any ideas? Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't execute code in VB 6.0
I think you can't. Even MySQLcc runs one after one. In fact it takes one statement and runs it. After that, it runs the following statement. The thing is not sent on the server at once. I might be wrong! (not an expert) Thanks Emery - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 26, 2003 05:52 Subject: Can't execute code in VB 6.0 I'm trying to execute a code with multiple SQL statement over VB 6.0. The code includes the creation of temporary tables, multiple inserts and one final select... It looks like i can only excecut one single SQL statement... does any one know how to fix that? I can run the code with out problems using MySQL Control Center. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL powered MailServer [dbmail.org]
Thank you very much. Emery - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 21:28 Subject: Re: MySQL powered MailServer I don't know about the hMailServer, but you may look at dbMail too (dbmail.org). You can use MySQL or PostgresSQL as the backend. Although I think it's only for Unix/Linux systems. I've been meaning to try setting it up on OSX, but haven' gotten around to it yet. On Thursday, September 25, 2003, at 01:17 PM, Director General: NEFACOMP wrote: Just wanted to know if anyone knows the status of the MySQL powered Mailserver found at http://www.mysql.com/portal/software/item-239.html -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Noob Question: Error 1046
Do you have a database you want to create that table in? You can use the test database if you are just testing. In that case you do: mysql USE test If you want to create your own database: mysql CREATE DATABASE database_name mysql USE database_name And then continue your queries. This means, you need to tell MySQL the database your queris should run on. Hope this helps Thanks Emery - Original Message - From: Charlie Brewer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 21:12 Subject: Noob Question: Error 1046 Evenin', Sorry to send noob questions, I know how annoying they can be, but we need help too ;) Anyways, Im using a self teach book to teach myself PHP, MySQL and Apache. The book is getting into MySQL and basic commands. Anyways, they gave an example, and I typed it in as shown in the book, but I get an error. I took a screenshot (I thought that would be easier) and posted it here: http://hostultra.org/brewer/mysql.jpg Any help would be very much so appreciated, thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ideas on creating connections
The problem is not when sending FLOTs to the server, but it when RECEIVING floats FROM the server. Thanks Emery - Original Message - From: Mike R. Duncan [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 23:55 Subject: RE: Ideas on creating connections try adding a '\' before the comma like a \n in c++ -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 10:41 AM To: Roger Davis; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Ideas on creating connections Thank you for your time. I don't want to be too much demanding. I currently have a problem with MyODBC 3.51. When my client machine is set to use French numbers (with a COMMA instead of a PERIOD or DOT for floats), MyODBC does not let the decimal part to pass. Venu told me he will try to look into this before the next release. It seems I am impatient!!! You said you are trying to move from DAO to using the MySQL DLL (libmysql.dll). Can we share some code using VB and the DLL? Just send me a piece of code doing the following: = - Creating a connection to the MySQL server, - Running a DELETE query on the server (or any other query that doesn't need to return values) - Running a SELECT query against the MySQL server and - Populating data of the RecordSet creating into a Flex grid - Closing and Distroying the RecordSet - Closing and Distroying the Connection = One time I saw such a code on http://www.vbmysql.com but it seems to have dissappeared. Thanks Emery - Original Message - From: Roger Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 19:08 Subject: RE: Ideas on creating connections DAO is, I guess you would say, the predessor to ADO. I am currently in the process of getting rid of all the DAO code in the application and moving to a custom recordset using the libmysql.dll. Some of this is done in VB and it is still extremely fast for a VB based recordset. Although there are numerous ways to do this, it is really difficult to tell what would be the best in your situation. As has been mentioned before, if you are doing queries one after another in your application, then by all means connect once and pass all the queries through that connection. If you a making very few queries and you have limited resources on the server, then it would be best to connect only when you need to and drop the connection when you are done. These are of course only my opinions based on what I have experienced in the past. Your experience may vary. Void where prohibited. Roger -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 12:48 PM To: Roger Davis; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Ideas on creating connections I never use DAO. Is there any benefit over ADO? Thanks Emery - Original Message - From: Roger Davis [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 18:14 Subject: RE: Ideas on creating connections My experience is this. I use DAO through MyODBC, and I also use the libmysql.dll with a custom recordset. Connection times through both options are extremely fast. Fast enough in my situation that it can basically can be ignored. There are times when I open several connections to the server at once and have yet to run into any problems. If you strictly want to use ADO though, I would suggest that you open one connection to the server and process your queries through that connection. Leave it open for the life of the program. While the program is open, the connection to the server will stay open and the thread for MySQL will be a sleep. Just my $0.02. Roger -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 11:57 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Ideas on creating connections Hi groups, I need some experienced users to guide me on this issue. I am developping an Application that will be accessing a MySQL database through MyODBC. The application is developped using Visual Basic. Since my application will be used simultaneously by more than 200 users, I want to know the implication of using one of the following methods for connecting to the server: 1. At logon, I create a connection to the server and maintain it throught the application life. By the application life I mean that the connection stays open as long as the application is loaded in the memory. So, for a user who is online for 3 hours, the connection is on for those three hours, even if
Re: innodb: storage requirements
# [EMAIL PROTECTED] / 2003-09-25 08:58:09 +0300: From: Roman Neuhauser [EMAIL PROTECTED] Also, is there a way to *measure* the fragmentation of a table? If there is, how should the info be interpreted? Sorry, no. But adding such a feature would be easy: just look at page numbers, page fillfactors, and the total size of the 'segment' storing the B-tree leaf pages. Having this, and other management features, in InnoDB would be a boon. InnoDB adds much complexity to MySQL, but the administrator doesn't have many tools to monitor its health. Many more parts that can break, but that are not visible to the administrator. What I've been missing most dearly from MySQL/InnoDB lately is documentation to the output of SHOW INNODB STATUS and friends. Section 9.1 of the InnoDB manual doesn't cut it. Also, don't be afraid to dive into finer details when describing the innards of InnoDB: I was trying to help myself before posting here, but found that section 12 just scratches the surface. All in all, thanks for InnoDB, and thanks for the support. -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1 child with 2 parents
Hi, I have a table with a foreign key that references two different parent tables. I can't insert a row into this child table unless the data is in both parent tables. I don't want the data to be in both parent tables. Is there a way round? thanks Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Excluding Results from a database
Hi, I asked for some help about a week ago and thanks to everybody who got me a little closer to the solution. I am developing an online fantasy rugby game. Visitors buy and sell players and earn points depending on those players' performances in real matches. The only problem I have is that when visitors go to the 'Purchase Players' screen, I cannot exclude players that they already own. For example, if they already have Jonny Wilkinson in their team, Wilkinson is still available to purchase again (anybody familiar with rugby will appreciate the issue with a visitor fielding a team of 15 Jonny Wilkinsons). I am using two queries. The first query selects the players that are available to buy: $teamqry = SELECT * FROM players, countries WHERE players.position = '$pos' AND players.country = countries.countryid AND players.price = '$availablebudget' ORDER BY players.price DESC; The second brings up the squad that a visitor has currently selected: $squadqry = SELECT * FROM transactions, players, positions, countries WHERE transactions.managerid = '$userid' AND transactions.playerid = players.playerid AND players.position = positions.positionid AND players.country = countries.countryid AND datein '$now' AND dateout '$now' ORDER BY positionid ASC; Basically I need to be able to produce a list of all the players that appear in the first query ($teamqry) EXCEPT those that appear in the second ($squadqry). Help!?!?!??! Thanks very much, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excluding Results from a database
Using the Query of the players already selected build a list like : $player_list=player1, player2, player3, ...; and then use that list to not return selected ones SELECT player_id FROM playerTable WHERE player_id NOT IN($player_list); I don't know exactly how your tables are, but I hope the above idea will be OK. Thanks Emery - Original Message - From: Matt MacLeod [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 26, 2003 11:51 Subject: Excluding Results from a database Hi, I asked for some help about a week ago and thanks to everybody who got me a little closer to the solution. I am developing an online fantasy rugby game. Visitors buy and sell players and earn points depending on those players' performances in real matches. The only problem I have is that when visitors go to the 'Purchase Players' screen, I cannot exclude players that they already own. For example, if they already have Jonny Wilkinson in their team, Wilkinson is still available to purchase again (anybody familiar with rugby will appreciate the issue with a visitor fielding a team of 15 Jonny Wilkinsons). I am using two queries. The first query selects the players that are available to buy: $teamqry = SELECT * FROM players, countries WHERE players.position = '$pos' AND players.country = countries.countryid AND players.price = '$availablebudget' ORDER BY players.price DESC; The second brings up the squad that a visitor has currently selected: $squadqry = SELECT * FROM transactions, players, positions, countries WHERE transactions.managerid = '$userid' AND transactions.playerid = players.playerid AND players.position = positions.positionid AND players.country = countries.countryid AND datein '$now' AND dateout '$now' ORDER BY positionid ASC; Basically I need to be able to produce a list of all the players that appear in the first query ($teamqry) EXCEPT those that appear in the second ($squadqry). Help!?!?!??! Thanks very much, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excluding Results from a database
Bang on, Emery. Works a treat. Thanks very much. Matt Director General: NEFACOMP wrote: Using the Query of the players already selected build a list like : $player_list=player1, player2, player3, ...; and then use that list to not return selected ones SELECT player_id FROM playerTable WHERE player_id NOT IN($player_list); I don't know exactly how your tables are, but I hope the above idea will be OK. Thanks Emery - Original Message - From: Matt MacLeod [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 26, 2003 11:51 Subject: Excluding Results from a database Hi, I asked for some help about a week ago and thanks to everybody who got me a little closer to the solution. I am developing an online fantasy rugby game. Visitors buy and sell players and earn points depending on those players' performances in real matches. The only problem I have is that when visitors go to the 'Purchase Players' screen, I cannot exclude players that they already own. For example, if they already have Jonny Wilkinson in their team, Wilkinson is still available to purchase again (anybody familiar with rugby will appreciate the issue with a visitor fielding a team of 15 Jonny Wilkinsons). I am using two queries. The first query selects the players that are available to buy: $teamqry = SELECT * FROM players, countries WHERE players.position = '$pos' AND players.country = countries.countryid AND players.price = '$availablebudget' ORDER BY players.price DESC; The second brings up the squad that a visitor has currently selected: $squadqry = SELECT * FROM transactions, players, positions, countries WHERE transactions.managerid = '$userid' AND transactions.playerid = players.playerid AND players.position = positions.positionid AND players.country = countries.countryid AND datein '$now' AND dateout '$now' ORDER BY positionid ASC; Basically I need to be able to produce a list of all the players that appear in the first query ($teamqry) EXCEPT those that appear in the second ($squadqry). Help!?!?!??! Thanks very much, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Information required
Heloo hi I require information that latest version(4.0.15) of mysql provides support for executing subqueries and prepared statement,if it is possible in this new version of mysql then i require information about drivers for prepared state ment and how to user sub quries in mysql bye Waseem Ahmad Zia - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Information required
Hello hi I require information that latest version(4.0.15) of mysql provides support for executing subqueries and prepared statement,if it is possible in this new version of mysql then i require information about drivers for prepared state ment and how to user sub quries in mysql bye Waseem Ahmad Zia - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: Information required
Waseem Ahmad Zia wrote: Hello hi I require information that latest version(4.0.15) of mysql provides support for executing subqueries and prepared statement,if it is possible in this new version of mysql then i require information about drivers for prepared state ment and how to user sub quries in mysql bye Waseem Ahmad Zia Subqueries and prepared statements are not available in 4.0.15. They will be in 4.1 Check the manual for details : http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html http://www.mysql.com/doc/en/C_API_Prepared_statements.html Regards, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Information required
Waseem Ahmad Zia [EMAIL PROTECTED] wrote: I require information that latest version(4.0.15) of mysql provides support for executing subqueries and prepared statement,if it is possible in this new version of mysql then i require information about drivers for prepared state ment and how to user sub quries in mysql Subqueries and prepared statements are supported from MySQL 4.1. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY performance on large tables
Hi: Issuing a simple group by like this: select C_SF, count(*), sum(je) as sum_je from kp_data group by C_SF; against a large (1.4G) table holding a 5 mln records with 60 columns takes about 330 secs on my Win2000 development box, a 2.0GHz P4 w/ 1G RAM and an IDE MAXTOR drive. Reducing the column count helped cut time down to 20 secs, but that is not exactly what we need for this OLAP web app. I tried the following optimization hints from the ref manual with moderate results: * adding 'order by null' to avoid the final filesort pass. * increasing buffer sizes to support in-memory operations key_buffer=64M table_cache=64 sort_buffer=64M read_buffer_size=16M How have you been optimizing your queries and DB setups in comparable situations? TIA Jan Torres -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR
Greetings. When I execute the SQL script below in
mySQL 4.1, I get
ERROR 1216: Cannot add or update a child row: a
foreign key constraint fails
It is, of course, choking on the enroll row insert.
Why is this happening? Here are some things that make
the problem go away:
1. Take out the name VARCHAR(30) attribute from
student OR change the type to CHAR(30): In Section
6.5.3.1 (second bullet), it says that if one attribute
is variable length, all attributes silently become
variable length. Does this mean student.sid is really
a VARCHAR? Could this be related to the problem?
2. Change type of enroll.sid to VARCHAR(5).
3. Change Earl to Early
My theory: student.sid get silently changed from
CHAR to VARCHAR since student.name is VARCHAR;
however, enroll.sid does not change because there are
no variable length fields in enroll. This means that
enroll.sid = Earl
student.sid = Earl
Consequently, there is no match.
Note that I did try specifying a length for the index
on sid (i.e., INDEX sidindex (sid(5)),) but that
didn't help. Even shortening to 4 doesn't help, which
doesn't jive with my cohersion theory, assuming I
understand the index length specification.
Ideas?
Thanks in advance!
CREATE TABLE student (
sid CHAR(5) PRIMARY KEY,
name VARCHAR(30)
);
CREATE TABLE course (
cid CHAR(5) PRIMARY KEY,
name CHAR(20)
);
CREATE TABLE enroll (
cid CHAR(5) NOT NULL,
sid CHAR(5) NOT NULL,
PRIMARY KEY(cid, sid),
INDEX sidindex (sid),
FOREIGN KEY (sid) REFERENCES student(sid),
FOREIGN KEY (cid) REFERENCES course(cid)
);
INSERT INTO student VALUES ('Earl', 'Earl Jones');
INSERT INTO course VALUES ('BRTLT', 'British
Literature');
INSERT INTO enroll VALUES ('BRTLT', 'Earl');
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connect to 4.1 using MyOBDC
I believe the problem is in the ODBC drivers on MS Winx platforms. I have no problems accessing MySQL from MySQL Control Center or from perl or other programs NOT using ODBC. I have a MS FoxPro application using ODBC to connect to MS SQL Server via TCP/IP and MS FoxPro has problems connecting sometime to MS SQL. Given that problem, and my success accessing MySQL from any place with a natural (not ODBC) driver, I would look to ODBC first the MySQL second. At 01:57 PM 9/25/2003, Randy Chrismon wrote: Seems to be a problem with the fact that the server is on a Linux box. I was having this problem connecting the mysql command environment to the linux server until I added -protocol=TCP to the login statement. Or maybe it's just that we use a TCP/IP network (doesn't everybody?). Anyway, my test user is using Win2K, accessing the MySQL server which resides on a Redhat 9 linux box. We are on the same network domain inside the firewall. Essentially, I believe I need some way to tell the ODBC driver (and also the mysql command center and the command line) that the protcol is TCP. Is their an environment variable that would do this? This is starting to make me look bad... Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GROUP BY performance on large tables
a minor tweak should result from doing count(1) instead of count(*) From what I know (not much) the * causes the db to do a secondary lookup for the names of the columns, even though you're not using it at all. As you're selecting every record in the table, I'm pretty sure indexing won't help you... What I've done in the past, depending on how 'real time' the data needs to be, is either 1- schedule a job that runs the query, and puts results in another table, then app hits that table 2- when a record is put into that table, update a second table with new numbers. Ideally, this would be done in a trigger (coming soon to a MySQL version near you) on insert update count_table set total_count = total_count + 1, je_total = je_total + :new.je_total; on update update count_table set je_total = je_total + :new.je_total - :old.je_total; on delete update count_table set total_count = total_count - 1, je_total = je_total - :old.je_total; hope this helps, Dan Greene -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 26, 2003 7:34 AM To: [EMAIL PROTECTED] Subject: GROUP BY performance on large tables Hi: Issuing a simple group by like this: select C_SF, count(*), sum(je) as sum_je from kp_data group by C_SF; against a large (1.4G) table holding a 5 mln records with 60 columns takes about 330 secs on my Win2000 development box, a 2.0GHz P4 w/ 1G RAM and an IDE MAXTOR drive. Reducing the column count helped cut time down to 20 secs, but that is not exactly what we need for this OLAP web app. I tried the following optimization hints from the ref manual with moderate results: * adding 'order by null' to avoid the final filesort pass. * increasing buffer sizes to support in-memory operations key_buffer=64M table_cache=64 sort_buffer=64M read_buffer_size=16M How have you been optimizing your queries and DB setups in comparable situations? TIA Jan Torres -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X Installation and Setup
At 3:47 -0500 22-09-2003, Andy Callan wrote: I tried to kill 3306 and it claimed there was no such process I have a GUI fronted called MacSQL already installed that i was using before when I could only edit test_... db's in mySQL but I doubt that could be causing a problem since it didn't for PostgreSQL so i really don't know what is going on with this. You can not kill 3306. It is the TCP port. You have to try to figure out what process is listening (netstat command) and then kill it. You can also look at ps to find Mysql process. Last: Try to execute mysqladmin --shutdown and then relaunch mysql Santino PS: Disable automatic startup of MySql 3.xx from Application Utility MySqlManager. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 child with 2 parents
[EMAIL PROTECTED] wrote: I have a table with a foreign key that references two different parent tables. I can't insert a row into this child table unless the data is in both parent tables. I don't want the data to be in both parent tables. It's how foreign key constraints work. Foreign key can take only those values contained in the referenced key. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Somebody tried MySQL version 5 / Stored Procedures
Hi all, We want to migrate to MySQL, and because of its speed, we want to use MyISAM db. I plan to write code as an alternate transaction mechanism. But I have read that MySQL version 5 supports Stored Procedures (PL/SQL like). In PL/SQL it is possible to use in your stored procedures transactions (at the end (eg) of a stored procedure you can do a commit and in an exception handler (eg) you can do a rollback). My question to somebody who has tried version 5 (or the development team), is this possible in MySQL stored procedures too? So it is not nessecery for me to write much code, but just a little and in version 5 I write my transactions to stored procedures... In anticipation much thanks!! Kind Regards Hans van Dalen/ NL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlclient lib
Dear Friends where i can find an mysqlclient.lib compiled for borland compiler I use mysql 4 as an windows server Regards Luiz --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.521 / Virus Database: 319 - Release Date: 23/09/03 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REFERENCES question in mySQL 4.1 - Take 2
Bo, - Alkuperäinen viesti - Lähettäjä: Bo Lorentsen [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Friday, September 26, 2003 9:41 AM Aihe: Re: REFERENCES question in mySQL 4.1 - Take 2 On Fri, 2003-09-26 at 01:14, Heikki Tuuri wrote: CREATE TABLE phone (id char(5) not null REFERENCES person(id), num char(10) not null, PRIMARY KEY (id, num)); It simply ignores that REFERENCES definition. It is in the TODO list to add support for that syntax, too. Until then you have to use the 'long' syntax: Is it possible to make it generate an error when the wrong syntax occure, as ignoring the FK syntax is not very assuring ? yes, this is a known problem. /BL Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb: storage requirements
Roman, - Alkuperäinen viesti - Lähettäjä: Roman Neuhauser [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Friday, September 26, 2003 12:02 PM Aihe: Re: innodb: storage requirements # [EMAIL PROTECTED] / 2003-09-25 08:58:09 +0300: From: Roman Neuhauser [EMAIL PROTECTED] Also, is there a way to *measure* the fragmentation of a table? If there is, how should the info be interpreted? Sorry, no. But adding such a feature would be easy: just look at page numbers, page fillfactors, and the total size of the 'segment' storing the B-tree leaf pages. Having this, and other management features, in InnoDB would be a boon. InnoDB adds much complexity to MySQL, but the administrator doesn't have many tools to monitor its health. Many more parts that can break, but that are not visible to the administrator. What I've been missing most dearly from MySQL/InnoDB lately is documentation to the output of SHOW INNODB STATUS and friends. Section 9.1 of the InnoDB manual doesn't cut it. Also, don't be afraid to dive into finer details when describing the innards of InnoDB: I was trying to help myself before posting here, but found that section 12 just scratches the surface. All in all, thanks for InnoDB, and thanks for the support. it would be nice if academic people and others would publish more papers on the internals. The page http://www.innodb.com/books.html contains some links. The source code itself contains very long comments at the start of some .c files. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't execute code in VB 6.0
I found in other discusion thread that currently it's not possible... What i did was to split the code in multiple SQL Statements, i put them in an array and executeted one by one... this solved the problem, but i'm concerned about performance issues. FB I think you can't. Even MySQLcc runs one after one. In fact it takes one statement and runs it. After that, it runs the following statement. The thing is not sent on the server at once. I might be wrong! (not an expert) Thanks Emery - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 26, 2003 05:52 Subject: Can't execute code in VB 6.0 I'm trying to execute a code with multiple SQL statement over VB 6.0. The code includes the creation of temporary tables, multiple inserts and one final select... It looks like i can only excecut one single SQL statement... does any one know how to fix that? I can run the code with out problems using MySQL Control Center. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Connect to 4.1 using MyOBDC
On Friday, September 26, 2003 7:10 AM, Patrick Shoaf wrote: ...Given that problem, and my success accessing MySQL from any place with a natural (not ODBC) driver, I would look to ODBC first the MySQL second. I'm coming into this discussion late, so I apologize if this is too elementary or has already been discussed...however, we are using MyODBC on windows with MySQL and I've found that I must create my own DSN (via the windows ODBC Admin tool) and actually test the DSN (instantiates the driver?) before I can use the MyODBC driver. -dn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transaction savepoints
Robert Morgan [EMAIL PROTECTED] wrote: Hi I'm running mysql 4.0.14 I'm doing an update from a webpage involving 3 tabIes, using 3 update queries and need to be able to roll back to before the first update query if the second or third query go pear shape. So I need to set a savepoint to rollback to if anyone could show me a an example of the code necessary it would be appreciated. Before the first UPDATE query create a savepoint SAVEPOINT savepoin_name Then execute UPDATE queries. If you want to do rollback use ROLLBACK TO SAVEPOINT savepoint_name You can find info about savepoints at: http://www.mysql.com/doc/en/Savepoints.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlclient lib
At 11:07 26/9/2003 -0300, Luiz Rafael Culik Guimaraes wrote: Dear Friends where i can find an mysqlclient.lib compiled for borland compiler Don't exists. Best to use the dynamic library libmysql.dll and linking statically through a *.lib file created with implib tool. I use mysql 4 as an windows server Regards Luiz -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stripping carrige returns out of fields
I have a query that is pulling user comments, supplied by via web internface and creating a text file out of them. In these comments are all sorts of carrige returns. I've tried stripping them out with the following queries, but once imported into excel, the carrige returns are still there. so obviously i'm doing something wrong I tried doing with the ASCII number .(no idea if this is valid) SELECT Business_Unit.Business_Unit, Category.Category_Name, REPLACE(Comment_Original, ASCII(10),ASCII(32)) FROM `Comment` INNER JOIN Cat egory ON Comment.Category_ID = Comment.Category_ID INNER JOIN Survey_Response ON Comment.Survey_Key = Survey_Response.Survey_Key INNER JOIN Business_Unit ON Survey_Response.BUKey = Business_Unit.BUKey WHERE Comment_Original ' ' AND Category.Category_Name IS NOT NULL GROUP BY Business_Unit.BUKey, Category.Category_Name, Comment_Original ORDER BY Business_Unit.BUKey, Category.Category_ID ASC INTO OUTFILE '/export/home/jdyke/comments_NoB.csv' fields terminated by ',' OPTIONALLY ENCLOSED BY '' lines terminated by '\n'; Also with the escaped charatcer. SELECT Business_Unit.Business_Unit, Category.Category_Name, REPLACE(Comment_Original, '\n',' ') FROM `Comment` INNER JOIN Category ON Co mment.Category_ID = Comment.Category_ID INNER JOIN Survey_Response ON Comment.Survey_Key = Survey_Response.Survey_Key INNER JOIN Business_UnitON Survey_Response.BUKey = Business_Unit.BUKey WHERE Comment_Original ' ' AND Category.Category_Name IS NOT NULL GROUP BY Business_Unit.BUKey, Category.Category_Name, Comment_Original ORDER BY Business_Unit.BUKey, Category.Category_ID ASC INTO OUTFILE '/export/home/jdyke/comments_NoB.csv' fields terminated by ',' OPTIONALLY ENCLOSED BY '' lines terminated by '\n'; Is REPLACE the right SQL Function to use? is there a better way to determine this character? Thanks Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql problems
hi i am trying to follow the steps in installing mysql database. i should to install the , mysql package (binary) under /usr/local/. i tried the commad rpm -i --prefix=/usr/local/ MYSQL-server-4.0.15-i386.rpm MYSQL-client-4 (do not remeber the name). when i execute i get NOKEY, keyid 5072e1f which i do not know what it means. and i get an error message too Error package MYSQL-server is not relectotable. how can i force the package under /usr/local. i have redhat 8.0. i am trying to do web database so i do not know if it is enough to istall the server only. i read the notes but the rpm package is different from other packages in installions and configurations. can you please tell the steps to install it and configure it? thanks __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 64 bit vs. Mysql 32bit
Hello. We recently aquired a dual Opteron system to run mysql on. We were previously using a dual Athlon MP system. We have encountered the following issue. We have a script that updates records in some rather large tables. This script runs more slowly on the opteron system than on the athlon system. The database was created on the athlon system an moved over. No tables or indexes were recreated. When benchmarking the two systems the opteron is much faster than the athlon system. This does not seem to carry over to our updates. Ideas? We have already (and are still open to more) tweaked my.cnf from the my-huge.cnf included in the Opteron binary from mysql.com (this is theirs, we haven't recompiled). We are running on SUSE8.2 for opteron. The opteron has 4GB of memory whereas the athlon had only 2GB. The tables are both on RAID5 on similar LSI controllers. The newer controller actually has better performance. Our my.cnf is included below. Any ideas on fixing this one? We really need this update query to run as quickly as it did on the athlon (at the very least). Some suggestions have been to recreate the tables and indexes using the 64bit binary on the opteron system. As this will take a while, would it have an effect? Anyone? I appreciate any comments, any direction. Thanks, Santiago -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple foreign key references on one column
# [EMAIL PROTECTED] / 2003-09-25 14:59:33 +0100:
CREATE TABLE `pheno` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`relevant` enum('y','n') default NULL,
`phenotype` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`,`phenotype`),
KEY `id` (`id`),
CONSTRAINT `0_125` FOREIGN KEY (`id`) REFERENCES
`monogenic` (`phenotype_ID`) ON DELETE CASCADE,
CONSTRAINT `0_127` FOREIGN KEY (`id`) REFERENCES
`knockout` (`phenotype_ID`) ON DELETE CASCADE
) TYPE=InnoDB
The problem is that I can't insert a record into this
table unless the value of 'id' is present in both the
mongenic and knockout tables. I receive the following error:
Cannot add or update a child row: a foreign key
constraint fails
what did you expect to happen instead, or, what did you expect the
two FK's to do instead?
--
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL certification [slightly-ot]
I have recently re-entered the job market and I was wondering if anyone has found that having certification really helps in landing a position. If so, which cert do you have? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Segmentation fault
I get this: /usr/local/mysql/bin/mysqld_safe: line 342: 23172 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 When attempting to do: Select n from table.n where field1='x' and field2='y' and (field3= or field3='z1' . . . field3='z8816'); Yes, there are 8816 values for z included in this select. Any ideas on how to correct for the seg fault? This is being run on Mysql 4.0.15 x86_64 on dual Opteron with 4GB on SUSE8.2 Any ideas? thanks, Santiago -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't open privilege tables: Out of memory error
Description: bin/mysqld_safe --user=mysql fails with the following error (from data/host.err file) 030925 10:58:58 mysqld started 030925 10:58:59 InnoDB: Started 030925 10:58:59 Fatal error: Can't open privilege tables: Out of memory (Needed 2976468256 bytes) 030925 10:58:59 Aborting How-To-Repeat: startup of mysqld_safe fails for version 4.0.15; running concurrent with mysqld 3.23.54a using the same data files using different socket and port Fix: none known yet Submitter-Id: submitter ID Originator:Jim Rapp Organization: UCSD Academic Computing Services MySQL support: [none | licence | email support | extended email support ] Synopsis: can't start mysqld 4.0.15 (using binary installation for Solaris 5.8 64-bit) Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.15-standard (Official MySQL-standard binary) C compiler:gcc (GCC) 3.3 C++ compiler: gcc (GCC) 3.3 Environment: G E N E R A LI N F O R M A T I O N DESCRIPTION VALUE Host Nameinsci14.ucsd.edu Host Aliases insci14 loghost Host Address(es) 132.239.56.230 Host ID 80fec484 Serial Number2164180100 Manufacturer Sun (Sun Microsystems) Manufacturer (Short) Sun Manufacturer (Full) Sun Microsystems System Model Enterprise 450 Model 4300 Main Memory 512 MB Virtual Memory 455 MB ROM Version OBP 3.20.0 2000/10/24 10:47 Number of CPUs 4 CPU Type sparcv9+vis CPU Speed296 MHz App Architecture sparc Kernel Architecture sun4u Kernel Bit Size 64 OS Name SunOS OS Version 5.8 OS Distribution Solaris 8 7/01 s28s_u5wos_08 SPARC Libc Namelibc Libc Version 1 Kernel Version SunOS Release 5.8 Version Generic_108528-23 64-bit Boot TimeTue Sep 23 07:11:52 2003 PDT Current Time Thu Sep 25 11:11:51 2003 PDT System: SunOS insci14.ucsd.edu 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-4 Architecture: sun4 Some paths: /bin/perl /usr/ccs/bin/make /opt/SUNWspro/bin/cc Compilation info: CC='gcc' CFLAGS='-m64' CXX='gcc' CXXFLAGS='-m64' LDFLAGS='' ASFLAGS='' LIBC: -rw-r--r-- 1 root bin 1788708 Jul 29 14:57 /lib/libc.a lrwxrwxrwx 1 root root 11 Jun 20 2002 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1157900 Jul 29 14:57 /lib/libc.so.1 -rw-r--r-- 1 root bin 1788708 Jul 29 14:57 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Jun 20 2002 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1157900 Jul 29 14:57 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' 'CFLAGS=-m64' 'CXXFLAGS=-m64' '--enable-assembler' '--with-named-z-libs=no' '--with-named-curses-libs=-lcurses' '--disable-shared' '--with-embedded-server' '--with-innodb' 'CC=gcc' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple tablespace + subqueries
Eduardo, - Original Message - From: Eduardo D Piovesam [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, September 25, 2003 10:45 PM Subject: Multiple tablespace + subqueries Hello all, We want to use MySQL (InnoDB) for a corporate/non-stop environment, but we really want/need multiple tablespace support + subqueries. When will both be available for a production environment? Only in 4.1.x? Any date? multiple tablespaces already work in my private 4.1 tree and pass heavy stress tests. They will be included in MySQL-4.1.1. I guess 4.1.1 will be released around November 15th, 2003. People often start using MySQL in production when it is labeled beta. Either 4.1.1 or 4.1.2 will get that label. Thank you! Eduardo Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple tablespace + subqueries
Heikki, Great! Thank you! Regards, Eduardo - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 26, 2003 4:31 PM Subject: Re: Multiple tablespace + subqueries Eduardo, - Original Message - From: Eduardo D Piovesam [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, September 25, 2003 10:45 PM Subject: Multiple tablespace + subqueries Hello all, We want to use MySQL (InnoDB) for a corporate/non-stop environment, but we really want/need multiple tablespace support + subqueries. When will both be available for a production environment? Only in 4.1.x? Any date? multiple tablespaces already work in my private 4.1 tree and pass heavy stress tests. They will be included in MySQL-4.1.1. I guess 4.1.1 will be released around November 15th, 2003. People often start using MySQL in production when it is labeled beta. Either 4.1.1 or 4.1.2 will get that label. Thank you! Eduardo Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about the bin-log index
We have 7/24 replication configuration that is generating 5M bin logs every 5mins or so. We have a process to remove the bin-logs but I was wondering if it would be safe to remove entries or even rotate the bin log index. E.g. copy the existing bin log index to filename.0 then echo bin-log index. Robert Hamel
RE: Question about the bin-log index
---Original Message- --From: Robert Hamel [mailto:[EMAIL PROTECTED] --Sent: Friday, September 26, 2003 1:41 PM --every 5mins or so. We have a process to remove the bin-logs but I was --wondering if it would be safe to remove entries or even rotate the bin --log index. E.g. copy the existing bin log index to filename.0 then echo -- bin-log index. mySQL does this already. Use max-binlog-size or max-relaylog-size to set the max log size. This will enable mysql to rotate the log to hostname-bin.0XX. Then use the command purge MASTER LOGS TO 'binlog' to remove the ones you don't want. Also lookup backlog to see how to automate this. -- -- --Robert Hamel - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql with SAS
Has anybody out there used sas as a frontend of MYSQL. It is a great datamanagement and statisics program, but it seems to have a few problems dealing with mysql. One, it really wants to use cursors (which mysql doesn't support yet) but thats fine. In the absence of that when you set mysql as a library and try to look at tables in tries to retreive the entire table and I see no way of setting limit. Also when trying to use the build query function it tries to retrieve the entire table instead of just the table column names for UI selection purposes. Anyone else had these problems? How did you get around them? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR
IMO, this is a bug in mySQL. It's fine to convert to fixed to variable length under the hood; however, it should not impact implementation. What's the best work around for this feature? It looks like I will be forced to change my enroll to use VARCHAR. I shouldn't have to do this to make it work. Other suggestions? Thanks. --- Harald Fuchs [EMAIL PROTECTED] wrote: In article [EMAIL PROTECTED], Ed Smith [EMAIL PROTECTED] writes: Greetings. When I execute the SQL script below in mySQL 4.1, I get ERROR 1216: Cannot add or update a child row: a foreign key constraint fails It is, of course, choking on the enroll row insert. Why is this happening? Here are some things that make the problem go away: 1. Take out the name VARCHAR(30) attribute from student OR change the type to CHAR(30): In Section 6.5.3.1 (second bullet), it says that if one attribute is variable length, all attributes silently become variable length. Does this mean student.sid is really a VARCHAR? Could this be related to the problem? Exactly. The VARCHAR name switches sid from CHAR to VARCHAR (as SHOW CREATE TABLE would have told you), whereas enroll.sid stays CHAR because there's no VARCHAR column. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR
# [EMAIL PROTECTED] / 2003-09-26 15:12:03 -0700: --- Harald Fuchs [EMAIL PROTECTED] wrote: Ed Smith [EMAIL PROTECTED] writes: Greetings. When I execute the SQL script below in mySQL 4.1, I get ERROR 1216: Cannot add or update a child row: a foreign key constraint fails It is, of course, choking on the enroll row insert. Why is this happening? Here are some things that make the problem go away: 1. Take out the name VARCHAR(30) attribute from student OR change the type to CHAR(30): In Section 6.5.3.1 (second bullet), it says that if one attribute is variable length, all attributes silently become variable length. Does this mean student.sid is really a VARCHAR? Could this be related to the problem? Exactly. The VARCHAR name switches sid from CHAR to VARCHAR (as SHOW CREATE TABLE would have told you), whereas enroll.sid stays CHAR because there's no VARCHAR column. What's the best work around for this feature? It looks like I will be forced to change my enroll to use VARCHAR. I shouldn't have to do this to make it work. Other suggestions? Why do you use VARCHAR in the other table in the first place? Fixed record length is always better. -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlclient lib
Hi At 11:07 26/9/2003 -0300, Luiz Rafael Culik Guimaraes wrote: Dear Friends where i can find an mysqlclient.lib compiled for borland compiler thansk, but for libmysql.dll i created then import lib, but still asking for function inside mysqlclient.lib Regards Luiz --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.521 / Virus Database: 319 - Release Date: 23/09/03 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can someone explain the difference between these two queries?
SELECT count(*) FROM commercial_entity, country, user_account, address_list LEFT JOIN state_province ON address_list.state_province_id = state_province.state_province_id LEFT JOIN contact_info ON address_list.contact_info_id = contact_info.contact_info_id WHERE address_list.address_type_id = 101 AND commercial_entity.commercial_entity_id = address_list.commercial_entity_id AND address_list.country_id = country.country_id AND commercial_entity.user_account_id = user_account.user_account_id AND user_account.user_role_id IN (101, 101); SELECT count(*) FROM commercial_entity, address_list, country, user_account LEFT JOIN state_province ON address_list.state_province_id = state_province.state_province_id LEFT JOIN contact_info ON address_list.contact_info_id = contact_info.contact_info_id WHERE address_list.address_type_id = 101 AND commercial_entity.commercial_entity_id = address_list.commercial_entity_id AND address_list.country_id = country.country_id AND commercial_entity.user_account_id = user_account.user_account_id AND user_account.user_role_id IN (101, 101); The first one takes 1.73 seconds, the other takes 0.34 seconds. The only difference is the order of the tables (address_list comes last in the FROM clause in the first example, and is in the middle of the FROM clause in the second example)... I stumbed on this when benchmarking Postgres vs MySQL, and accidentally pulled the Postgres version of the query and put it into the MySQL window (Postgres won't run the MySQL version, but MySQL runs the Postgres version). Oracle has the concept of a driving table. Is this a similar example? David. - Original Message - From: Mike R. Duncan [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 2:55 PM Subject: RE: Ideas on creating connections try adding a '\' before the comma like a \n in c++ -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 10:41 AM To: Roger Davis; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Ideas on creating connections Thank you for your time. I don't want to be too much demanding. I currently have a problem with MyODBC 3.51. When my client machine is set to use French numbers (with a COMMA instead of a PERIOD or DOT for floats), MyODBC does not let the decimal part to pass. Venu told me he will try to look into this before the next release. It seems I am impatient!!! You said you are trying to move from DAO to using the MySQL DLL (libmysql.dll). Can we share some code using VB and the DLL? Just send me a piece of code doing the following: = - Creating a connection to the MySQL server, - Running a DELETE query on the server (or any other query that doesn't need to return values) - Running a SELECT query against the MySQL server and - Populating data of the RecordSet creating into a Flex grid - Closing and Distroying the RecordSet - Closing and Distroying the Connection = One time I saw such a code on http://www.vbmysql.com but it seems to have dissappeared. Thanks Emery - Original Message - From: Roger Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 19:08 Subject: RE: Ideas on creating connections DAO is, I guess you would say, the predessor to ADO. I am currently in the process of getting rid of all the DAO code in the application and moving to a custom recordset using the libmysql.dll. Some of this is done in VB and it is still extremely fast for a VB based recordset. Although there are numerous ways to do this, it is really difficult to tell what would be the best in your situation. As has been mentioned before, if you are doing queries one after another in your application, then by all means connect once and pass all the queries through that connection. If you a making very few queries and you have limited resources on the server, then it would be best to connect only when you need to and drop the connection when you are done. These are of course only my opinions based on what I have experienced in the past. Your experience may vary. Void where prohibited. Roger -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 12:48 PM To: Roger Davis; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Ideas on creating connections I never use DAO. Is there any benefit over ADO? Thanks Emery - Original Message - From: Roger Davis [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 18:14 Subject: RE: Ideas on creating connections My experience is this. I use DAO through MyODBC, and I also use the libmysql.dll with a custom recordset. Connection times through both options are extremely fast. Fast enough in
Re: Can someone explain the difference between these two queries?
In the last episode (Sep 26), David Griffiths said: - Original Message - From: Mike R. Duncan [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 2:55 PM Subject: RE: Ideas on creating connections Please don't hijack threads. [ snip 2 queries ] The first one takes 1.73 seconds, the other takes 0.34 seconds. The only difference is the order of the tables (address_list comes last in the FROM clause in the first example, and is in the middle of the FROM clause in the second example)... What does an EXPLAIN SELECT .. on both queries print? LEFT JOINs definitely impose a table order, and it's possible MySQL is incorrectly ordering other joins on those tables. You didn't mention what version of MySQL you are using, btw. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pls hep me on MySQL-openssl configuration
Could anyone tell me how to configure MySQL to apply openssl in win32 platdform? I have tried --with-vio --with-openssl but it seems doesn't work. FYI, I manage to compile openssl with Perl and VC++. But when I use show variables have_openssl to check the status, it still return me no. Details in steps description are much appreciated. Thank you. - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR
Hi, - Original Message - From: Roman Neuhauser Sent: Friday, September 26, 2003 6:05 PM Subject: Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR # [EMAIL PROTECTED] / 2003-09-26 15:12:03 -0700: --- Harald Fuchs [EMAIL PROTECTED] wrote: Ed Smith [EMAIL PROTECTED] writes: Greetings. When I execute the SQL script below in mySQL 4.1, I get ERROR 1216: Cannot add or update a child row: a foreign key constraint fails It is, of course, choking on the enroll row insert. Why is this happening? Here are some things that make the problem go away: 1. Take out the name VARCHAR(30) attribute from student OR change the type to CHAR(30): In Section 6.5.3.1 (second bullet), it says that if one attribute is variable length, all attributes silently become variable length. Does this mean student.sid is really a VARCHAR? Could this be related to the problem? Exactly. The VARCHAR name switches sid from CHAR to VARCHAR (as SHOW CREATE TABLE would have told you), whereas enroll.sid stays CHAR because there's no VARCHAR column. What's the best work around for this feature? It looks like I will be forced to change my enroll to use VARCHAR. I shouldn't have to do this to make it work. Other suggestions? Why do you use VARCHAR in the other table in the first place? Fixed record length is always better. Indeed, but you can't get a CHAR(3) even when you want it if you have other fields that need to be TEXT/BLOB. MySQL, in its infinite wisdom, and which I'm getting very tired of, thinks that your CHAR fields will be better off as VARCHARs. :mad: When *I KNOW* what length a field's content *WILL ALWAYS BE*, and *I SPECIFY* CHAR, I don't care what MySQL thinks because of other column types. I WANT IT CHAR!!! :-) Well, now you know what I think... Destroy the silent CHAR - VARCHAR change please! (VARCHAR(3) - CHAR is OK.) Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can someone explain the difference between these two queries?
Hi, Table order can definitely make a difference in the order that MySQL actually reads them. Like if MySQL *thinks* the cost to join 2 different tables is equal, it will use the one that's listed first, first. But if you know the cost is different and change the table order, it can help the optimizer. STRAIGHT JOIN can be used in extreme cases where the optimizer doesn't read the tables in the best order even though you've listed them that way. Matt - Original Message - From: Dan Nelson Sent: Friday, September 26, 2003 8:11 PM Subject: Re: Can someone explain the difference between these two queries? In the last episode (Sep 26), David Griffiths said: - Original Message - From: Mike R. Duncan Sent: Thursday, September 25, 2003 2:55 PM Subject: RE: Ideas on creating connections Please don't hijack threads. [ snip 2 queries ] The first one takes 1.73 seconds, the other takes 0.34 seconds. The only difference is the order of the tables (address_list comes last in the FROM clause in the first example, and is in the middle of the FROM clause in the second example)... What does an EXPLAIN SELECT .. on both queries print? LEFT JOINs definitely impose a table order, and it's possible MySQL is incorrectly ordering other joins on those tables. You didn't mention what version of MySQL you are using, btw. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
some connector/j tests fail
hi, just donwloaded mysql and connector/j. the driver seems to work fine for simple stuff. but i can not get all of the tests to run (please see typescript below). ant test give ms a :Overriding previous definition of reference to non.test.sources the perf tests seem to be missing. has anyone gotten all the tests to run? any pointers will be appreciated. thanks Script started on Fri Sep 26 19:38:48 2003 [EMAIL PROTECTED]:/usr/src/mysql-connector-java-3.0.8-stable$ ant test Buildfile: build.xml Overriding previous definition of reference to non.test.sources clean: init: [copy] Copying 83 files to /usr/src/build-mysql-jdbc/mysql-connector-java-3.0.8-stable [copy] Copied 2 empty directories to /usr/src/build-mysql-jdbc/mysql-connector-java-3.0.8-stable [copy] Copying 3 files to /usr/src/build-mysql-jdbc/mysql-connector-java-3.0.8-stable [mkdir] Created dir: /usr/src/build-mysql-jdbc/clover [mkdir] Created dir: /usr/src/build-mysql-jdbc/clover/db [mkdir] Created dir: /usr/src/build-mysql-jdbc/clover/report compile-driver: [javac] Compiling 47 source files to /usr/src/build-mysql-jdbc/mysql-connector-java-3.0.8-stable [javac] Note: Some input files use or override a deprecated API. [javac] Note: Recompile with -deprecation for details. compile-testsuite: [javac] Compiling 24 source files to /usr/src/build-mysql-jdbc/mysql-connector-java-3.0.8-stable [javac] Note: /usr/src/build-mysql-jdbc/mysql-connector-java-3.0.8-stable/testsuite/simple/BlobTest.java uses or overrides a deprecated API. [javac] Note: Recompile with -deprecation for details. compile: test: [mkdir] Created dir: /usr/src/build-mysql-jdbc/junit [junit] Running testsuite.perf.BasePerfTest [junit] Tests run: 1, Failures: 1, Errors: 0, Time elapsed: 4.311 sec [junit] TEST testsuite.perf.BasePerfTest FAILED [junit] Running testsuite.perf.LoadStorePerfTest [junit] Tests run: 1, Failures: 0, Errors: 1, Time elapsed: 749.184 sec [junit] TEST testsuite.perf.LoadStorePerfTest FAILED [junit] Running testsuite.perf.RetrievalPerfTest [junit] Tests run: 3, Failures: 1, Errors: 0, Time elapsed: 139.727 sec [junit] TEST testsuite.perf.RetrievalPerfTest FAILED [junit] Running testsuite.simple.SSLTest [junit] Tests run: 1, Failures: 0, Errors: 0, Time elapsed: 6.626 sec [junit] Running testsuite.simple.BlobTest [junit] Tests run: 2, Failures: 0, Errors: 2, Time elapsed: 10.349 sec [junit] TEST testsuite.simple.BlobTest FAILED [junit] Running testsuite.simple.StatementsTest [junit] Tests run: 9, Failures: 1, Errors: 0, Time elapsed: 8.403 sec [junit] TEST testsuite.simple.StatementsTest FAILED [junit] Running testsuite.simple.DataSourceTest [junit] Tests run: 1, Failures: 0, Errors: 1, Time elapsed: 6.75 sec [junit] TEST testsuite.simple.DataSourceTest FAILED [junit] Running testsuite.simple.MiniAdminTest [junit] Tests run: 2, Failures: 0, Errors: 0, Time elapsed: 6.965 sec [junit] Running testsuite.simple.TraversalTest [junit] Tests run: 1, Failures: 0, Errors: 0, Time elapsed: 7.612 sec [junit] Running testsuite.simple.MetadataTest [junit] Tests run: 2, Failures: 0, Errors: 0, Time elapsed: 8.04 sec [junit] Running testsuite.simple.NumbersTest [junit] Tests run: 1, Failures: 0, Errors: 0, Time elapsed: 6.842 sec [junit] Running testsuite.simple.TimeTest [junit] Tests run: 1, Failures: 0, Errors: 1, Time elapsed: 6.838 sec [junit] TEST testsuite.simple.TimeTest FAILED [junit] Running testsuite.simple.ConnectionTest [junit] Tests run: 3, Failures: 0, Errors: 0, Time elapsed: 7.154 sec [junit] Running testsuite.simple.UpdatabilityTest [junit] Tests run: 4, Failures: 0, Errors: 0, Time elapsed: 9.149 sec [junit] Running testsuite.simple.EscapeProcessingTest [junit] Tests run: 1, Failures: 0, Errors: 0, Time elapsed: 6.863 sec [junit] Running testsuite.simple.TransactionTest [junit] Tests run: 1, Failures: 0, Errors: 0, Time elapsed: 6.956 sec [junit] Running testsuite.simple.DateTest [junit] Tests run: 1, Failures: 0, Errors: 0, Time elapsed: 7.856 sec [junit] Running testsuite.regression.NumbersRegressionTest [junit] Tests run: 2, Failures: 0, Errors: 0, Time elapsed: 6.69 sec [junit] Running testsuite.regression.MetaDataRegressionTest [junit] Tests run: 4, Failures: 0, Errors: 0, Time elapsed: 7.257 sec [junit] Running testsuite.regression.ResultSetRegressionTest [junit] Tests run: 5, Failures: 0, Errors: 0, Time elapsed: 7.768 sec [junit] Running testsuite.regression.ConnectionRegressionTest [junit] Tests run: 1, Failures: 0, Errors: 0, Time elapsed: 37.058 sec [junit] Running testsuite.regression.StringRegressionTest [junit] Tests run: 7, Failures: 0, Errors: 0, Time elapsed: 9.159 sec [junit] Running testsuite.regression.StatementRegressionTest [junit] Tests run: 5, Failures: 0,
Re: Mysql 64 bit vs. Mysql 32bit
I'm just curious.. what kind of application(s) are you hosting in such a server??? Santiago Flores [EMAIL PROTECTED] 26/09/2003 11:50 a.m. To [EMAIL PROTECTED] cc Subject Mysql 64 bit vs. Mysql 32bit Hello. We recently aquired a dual Opteron system to run mysql on. We were previously using a dual Athlon MP system. We have encountered the following issue. We have a script that updates records in some rather large tables. This script runs more slowly on the opteron system than on the athlon system. The database was created on the athlon system an moved over. No tables or indexes were recreated. When benchmarking the two systems the opteron is much faster than the athlon system. This does not seem to carry over to our updates. Ideas? We have already (and are still open to more) tweaked my.cnf from the my-huge.cnf included in the Opteron binary from mysql.com (this is theirs, we haven't recompiled). We are running on SUSE8.2 for opteron. The opteron has 4GB of memory whereas the athlon had only 2GB. The tables are both on RAID5 on similar LSI controllers. The newer controller actually has better performance. Our my.cnf is included below. Any ideas on fixing this one? We really need this update query to run as quickly as it did on the athlon (at the very least). Some suggestions have been to recreate the tables and indexes using the 64bit binary on the opteron system. As this will take a while, would it have an effect? Anyone? I appreciate any comments, any direction. Thanks, Santiago -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 64 bit vs. Mysql 32bit
Some suggestions have been to recreate the tables and indexes using the 64bit binary on the opteron system. I'm confused - are you running the 32-bit binary, or the 64-bit binary? David - Original Message - From: Santiago Flores [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 26, 2003 10:50 AM Subject: Mysql 64 bit vs. Mysql 32bit Hello. We recently aquired a dual Opteron system to run mysql on. We were previously using a dual Athlon MP system. We have encountered the following issue. We have a script that updates records in some rather large tables. This script runs more slowly on the opteron system than on the athlon system. The database was created on the athlon system an moved over. No tables or indexes were recreated. When benchmarking the two systems the opteron is much faster than the athlon system. This does not seem to carry over to our updates. Ideas? We have already (and are still open to more) tweaked my.cnf from the my-huge.cnf included in the Opteron binary from mysql.com (this is theirs, we haven't recompiled). We are running on SUSE8.2 for opteron. The opteron has 4GB of memory whereas the athlon had only 2GB. The tables are both on RAID5 on similar LSI controllers. The newer controller actually has better performance. Our my.cnf is included below. Any ideas on fixing this one? We really need this update query to run as quickly as it did on the athlon (at the very least). Some suggestions have been to recreate the tables and indexes using the 64bit binary on the opteron system. As this will take a while, would it have an effect? Anyone? I appreciate any comments, any direction. Thanks, Santiago -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help about MySql
Dear Sir, I am working at a project developed by using MySql to store various information. The program is written on VC++6.0 paltform. I met with a problem that the program using mysql_init function to call mysql can't be interrupted by ctrl - C. The program can only be interrupt by ctrl_break. Is there any configuration needed to modify? Can SIGINT signal work well in VC++ program concerning mysql_init function ? Could you give me some help to solve the problem? Yours sincerely, Wang Xiaogang [EMAIL PROTECTED] Sept. 27 2003 __ === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
