Re: Slow as Christmas join
Kevin Cagle wrote: Hello! I have a couple of tables I am doing a join on. Both have 6 fields. Most fields have fewer than 16 characters in them, but at most 75. Neither table is huge: one with 14004 rows and the other with 23677. I created a temp table to insert the data into. When I perform the join, it takes about 17 minutes to complete. The result is only 23674 rows. Question is, why is this query taking so long? I have searched the manual and only found an example where they talk about millions of rows being slow and mine has far fewer than that. It is a one to many relationship between the two tables, but should that really slow things down that much? Is there a way to speed things up a little...or a lot? Just for reference, here is the syntax I am using: insert into tmp1 select table2.field1, table1.field2, table1.field3, table2.field2, table2.field3, table2.field4, table2.field5, table2.field6, table1.field4, table1.field5, table1.field6 from table2,table1 where table2.field1=table1.field1; Hi! You may want to revisit the query statement above and see if you can do an outer join. That should help you as far as the query statement goes. Next, on to your indexes... (I have tried the same query with the tables reversed, i.e, table1.field1=table2.field1 but it still takes a long time.) MySQL Version: 4.0.15 Mac OS X 10.3.7 Explain: ++--+---+--+-+--+--- +---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--- +---+ | table1 | ALL | NULL | NULL |NULL | NULL | 14004 | | | table2 | ALL | NULL | NULL |NULL | NULL | 23677 || ++--+---+--+-+--+--- +---+ Judging from the output of your EXPLAIN statement, MySQL is forced to do a full table scan (14004 rows in one table and 23677 rows) in another using your current query statement. At the table-level, you should consider using an index, especially in regards to the columns that you list in your WHERE clause since you say that this is a one-to-many relationship. Going back to your query statement, field1 in each table could stand to be indexed. If there is anything else that will help you solve this problem, let me know and I will be happy to provide it! Try the two steps I mentioned and see if that doesn't help you out. Thanks In Advance! --kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
Hi, An SQL-statement that is conceptually equal to the following caused some astonishment. UPDATE table1 LEFT JOIN table2 ON table2.ID=table2.ID_table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id My problem is the IF condition, which alway evaluates to false no matter what conditions value was before the query. My explanation is, that MySQL first updates table1 and uses the changed values to update table2. In the meantime I solved it on application-level, but up to now I consindered a multi table update an atomic operation. Is this behavior correct? Greetings Kai -- This signature is left as an exercise for the reader. Unsatz des Jahres: $POLITIKER ruft $PARTEI zur Geschlossenheit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where can I download HPUX11.0 PA-RISC 2.0 (LP64) platform binaries for 4.1.8 mysql?
Hi Vivek, all! Am Die, 2004-12-28 um 00.23 schrieb Rajan, Vivek K: Hello- I am looking for ELF-64 relocatable object file - PA-RISC 2.0 (LP64) platform binaries for 4.1.8 mysql. Please lemme know where I can download this from? Thanx, Vivek PS: I downloaded this from the mysql.com mysql-standard-4.1.8-hp-hpux11.00-hppa2.0w ptdh172:vkrajanfile libmysqlclient.a libmysqlclient.a: archive file -PA-RISC1.1 relocatable library ptdh172:vkrajanfile libmysqlclient_r.a libmysqlclient_r.a: archive file -PA-RISC1.1 relocatable library ptdh172:vkrajan I don't see PA-RISC 2.0 binaries. I fear you issued the wrong command: You checked the archive (format), but probably not their contents. Please compare (run on Linux): for L in `find mysql-standard-4.1.8-hp-hpux11.* -name '*client*.a'` do ls -l $L ar xv $L libmysql.o file libmysql.o rm libmysql.o echo done -rw-r--r--1 mysqldev users 1921214 Dec 16 11:35 mysql-standard-4.1.8-hp-hpux11.00-hppa2.0w-64bit/lib/libmysqlclient.a x - libmysql.o libmysql.o: ELF 64-bit () MSB relocatable, PA-RISC, version 1, not stripped -rw-r--r--1 mysqldev users 1900414 Dec 16 11:33 mysql-standard-4.1.8-hp-hpux11.00-hppa2.0w-64bit/lib/libmysqlclient_r.a x - libmysql.o libmysql.o: ELF 64-bit () MSB relocatable, PA-RISC, version 1, not stripped -rw-r--r--1 mysqldev users 1909374 Dec 14 22:10 mysql-standard-4.1.8-hp-hpux11.11-hppa2.0w-64bit/lib/libmysqlclient.a x - libmysql.o libmysql.o: ELF 64-bit () MSB relocatable, PA-RISC, version 1, not stripped -rw-r--r--1 mysqldev users 1888918 Dec 14 22:08 mysql-standard-4.1.8-hp-hpux11.11-hppa2.0w-64bit/lib/libmysqlclient_r.a x - libmysql.o libmysql.o: ELF 64-bit () MSB relocatable, PA-RISC, version 1, not stripped So according to my check, both for HP-UX 11.00 and 11.11, both the 'libmysqlclient.a' and the 'libmysqlclient_r.a' contain objects which are described (by 'file' on Linux) as: ELF 64-bit () MSB relocatable, PA-RISC When I copy the archives to an HP-UX 11 machine, extract 'libmysql.o', and run 'file', I get the string you desire: libmysql.o: ELF-64 relocatable object file - PA-RISC 2.0 (LP64) (again, checked for all four archives) Extract a file from the archive and check that with 'file'. Regards, and a Happy New Year to all! Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: are autoincrement values not always increasing in innodb?
Frank, my experience MySQL returns the the rows in the order that you inserted them This is true, if, and only if you have never deleted a record from the table. Like most SQL servers, MySQL leaves deleted records' space in the physical table unoccupied, but still available. When you insert a record, it first checks if the new record can not be inserted into an already allocated space (previously occupied by a valid record). If so, it will insert it there, else it will be appended to the table. This will explain the order in which your records are listed. Record 5 either got inserted into an open space, OR it was inserted while the other client thread/transaction inserted the other 4 records. If you optimize your table, then only is it truly purged from space previously occupied by deleted records. To answer your question though, the previous situation has nothing to do with your autoinc values, which will always be incremented - guaranteed. This behaviour can be changed though if you actually specify a value for an AUTOINC column during the insert, and thereby not allow MySQL to do or follow it's normal course in incrementing the autoinc. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -Original Message- From: Frank Sonntag [EMAIL PROTECTED] To: mysql@lists.mysql.com mysql@lists.mysql.com CC: Subject: are autoincrement values not always increasing in innodb? Sent: Mon, 27 Dec 2004 00:45:37 GMT Received: Mon, 27 Dec 2004 00:49:46 GMT Read: Tue, 28 Dec 2004 10:38:11 GMT Hi, does InnoDB guarantee that the values of an autoincrement column do always increase? What happened to me is that a select * from my_table returns something like id | ... 10 11 5 12 13 where id is defined as int(10) unsigned NOT NULL auto_increment and is the primary key of the table. The inserts corresponding to ids (10, 11, 12, 13) are done inside one transaction, the insert that generates id = 5, in another (concurrent) one. Cheers Frank -- 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: importing data
I didn't, but I'll give it a shot and see what happens. Curtis Michael J. Pawlowsky wrote: Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Did you try simply repairing the table? http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk errors
Hello. Have you stopped MySQL server before executing myisamchk? sirisha gnvg [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 21 lines --] hello everybody, I used myisamchk /path/to/datadir/mysql/*.* to find memory already in use and free memory available for all tables in 'mysql' database.where mysql in above path refers to mysql database. I got two types of errors for all tables in that directory. They are myisamchk:error: -1 when opening myisam_table 'abcd.MYD' myisamchk1 error:'abcd.frm' doen't have a correct index definition.You need to create it before u can do a repair.I am using Red hat linux mysql version. Please give more information about these errors and the ways to rectify them. Thank you, yours, sirisha. Yahoo! India Matrimony: Find your life partneronline. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: MySQL 4.0.23 wrong shared libraries output??
Hello. This is a bug. See: http://bugs.mysql.com/bug.php?id=7401 Neo Wee Teck [EMAIL PROTECTED] wrote: 4.0.22 libdbug.a libmyisam.a libmysqlclient.so.12.0.0 libnisam.a libheap.a libmyisammrg.a libmysqlclient.so libmystrings.alibvio.a libmerge.a libmysqlclient.la libmysqlclient.so.12libmysys.a 4.0.23 libdbug.a libmerge.a libmyisammrg.a libmysqlclient.12 libmysqlclient.la libmysys.a libvio.a libheap.a libmyisam.a libmysqlclient libmysqlclient.12.0.0 libmystrings.a libnisam.a Note that libraries that are compiled from 4.0.23 doesn't have .so (I didn't make any typo myself) When executing ldd mysqldir/mysql linux-gate.so.1 = (0xe000) libreadline.so.4 = /usr/lib/libreadline.so.4 (0xb7fbb000) libncurses.so.5 = /usr/lib/libncurses.so.5 (0xb7f7b000) libmysqlclient.so.12 = not found Compile flags for 4.0.23 and 4.0.22 CFLAGS=-O2 -march=pentium4 -mcpu=pentium4 -fomit-frame-pointer -mmmx -msse -msse2 -mfpmath=sse CXXFLAGS=$CFLAGS ./configure --without-readline --without-bench --without-man --without-docs --with-vio --without-extra-tools --without-debug --with-unix-socket-path=/var/mysql/mysql.sock --enable-assembler --enable-shared --disable-static Anyone got this problem too? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Newbie: Using SELECT to Get Table Status?
Hello. You may use count(*) for counting rows. Robinson, Eric [EMAIL PROTECTED] wrote: The mysqlSHOW TABLE STATUS command returns such things as the number or rows and time of the last update.=20 Is it possible to get the same information using a SELECT statement? FYI, I am writing a script to monitor replication status by comparing this information between the master and slave servers. -- Eric Robinson -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: importing data
OK, now I really feel stupid. It helps to change the ownership of the files to mysql:mysqlduh. Curtis Curtis Maurand wrote: I didn't, but I'll give it a shot and see what happens. Curtis Michael J. Pawlowsky wrote: Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Did you try simply repairing the table? http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regression bug in 4.0.23? FROM_UNIXTIME(0) is now NULL, not 1970-01-01 01:00:00
Hi there, A bug was fixed in 4.0.23: * Fixed bug which caused FROM_UNIXTIME() function to return wrong result if the argument was too big. http://bugs.mysql.com/6439 However, it looks like it has side effects: select FROM_UNIXTIME(0) returns 1970-01-01 01:00:00 in 4.0.22 but NULL in 4.0.23... (European localtime) Needless to say, this confuses application logic (especially if the type is DATETIME NOT NULL). Is this a bug? Should I file a bug at http://bugs.mysql.com/ ? If it was intentional, wouldn't it have been prudent to mention this altered behavior in the release notes? Peter To reproduce: SELECT FROM_UNIXTIME(0); CREATE TABLE `fromunixtime1` ( `timestamp` TIMESTAMP NOT NULL ); INSERT INTO `fromunixtime1` (`timestamp`) values (FROM_UNIXTIME(0)); SELECT `timestamp` FROM `fromunixtime1`; CREATE TABLE `fromunixtime2` ( `datetime` DATETIME NOT NULL ); INSERT INTO `fromunixtime2` (`datetime`) values (FROM_UNIXTIME(0)); SELECT `datetime` FROM `fromunixtime2`; 4.0.22 output: +-+ | FROM_UNIXTIME(0)| +-+ | 1970-01-01 01:00:00 | +-+ 1 row in set (0.00 sec) ++ | timestamp | ++ | 00 | ++ 1 row in set (0.00 sec) +-+ | datetime| +-+ | 1970-01-01 01:00:00 | +-+ mysql --version mysql Ver 12.22 Distrib 4.0.22, for pc-linux (i386) (Debian unstable - a couple of days ago) 4.0.23 output: +--+ | FROM_UNIXTIME(0) | +--+ | NULL | +--+ 1 row in set (0.00 sec) ++ | timestamp | ++ | 20041228151915 | ++ 1 row in set (0.00 sec) ERROR 1048: Column 'datetime' cannot be null Empty set (0.00 sec) mysql --version mysql Ver 12.22 Distrib 4.0.23, for pc-linux-gnu (i386) (Debian unstable - present) -- Peter Valdemar Mørch http://www.morch.com -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.298 / Virus Database: 265.6.5 - Release Date: 12/26/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparison SQL always return false
Hi,
I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
I found that if I assign a hard coded string (with double quote) to the
string variable which in turns become part of the SQL statement, the SQL
does return result.
If I assign a value which is return from an array in perl (eg.
@array[1]) and use this variable in the SQL statement, the SQL does not
return any result.
For example:
The following code does not return any result
my $mycustcode = @outlets[1]; # this array contains string 07-2-0057
However, if I use the following hard coded assignment, the SQL statement
returns result:
# my $mycustcode = 07-2-0057;
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales)
as sales
from customer c, transaction t
where c.custcode = t.custcode
and date(t.date) = $start_date
and date(t.date) = $end_date
and (c.type = EXPORT or c.type = LOCAL)
and (c.custcode = $mycustcode)
group by c.custcode;};
I may be using incorrect way to split the tokens, here is the function
to extract a list of the token:
$outlet_str = $in{'outlets'}
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi table update
Sorry for duplicating this message. But I sent the other one with the wrong subject. Hi, An SQL-statement that is conceptually equal to the following caused some astonishment. UPDATE table1 LEFT JOIN table2 ON table2.ID=table2.ID_table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id My problem is the IF condition, which alway evaluates to false no matter what conditions value was before the query. My explanation is, that MySQL first updates table1 and uses the changed values to update table2. In the meantime I solved it on application-level, but up to now I consindered a multi table update an atomic operation. Is this behavior correct? Greetings Kai -- This signature is left as an exercise for the reader. Unsatz des Jahres: $POLITIKER ruft $PARTEI zur Geschlossenheit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
One reason may be due to your ON condition. ON table2.ID=table2.ID_table1 You reference table 2 twice and I doubt that on the table2 table id equals id_table1 very often. Because table2 is LEFT JOINed to table1, you will only be able to update the rows that match your ON condition. Again, that would mean that you have nothing to update which would cause no changes to table2 so it would appear that the IF() is always false. You can LEFT JOIN only those records you want to change. That way you can avoid the IF() and not worry about updating any other records. I think it should look something like this: UPDATE table1 LEFT JOIN table2 ON table1.ID=table2.ID_table1 AND table1.condition=7 SET table2.value=table2.value-1, table1.condition=8 WHERE table1.ID=$id Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kai Ruhnau [EMAIL PROTECTED] wrote on 12/28/2004 04:22:34 AM: Hi, An SQL-statement that is conceptually equal to the following caused some astonishment. UPDATE table1 LEFT JOIN table2 ON table2.ID=table2.ID_table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id My problem is the IF condition, which alway evaluates to false no matter what conditions value was before the query. My explanation is, that MySQL first updates table1 and uses the changed values to update table2. In the meantime I solved it on application-level, but up to now I consindered a multi table update an atomic operation. Is this behavior correct? Greetings Kai -- This signature is left as an exercise for the reader. Unsatz des Jahres: $POLITIKER ruft $PARTEI zur Geschlossenheit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] UPDATE table1 LEFT JOIN table2 ON table2.ID=table2.ID_table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id Is this behavior correct? [/snip] In the sense that it did what was given to it in the query, yes. From http://www.mysql.com/update If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement will fail and roll back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly. If you are using MyISAM style tables you should never attempt multiple table updates without triggers and/or stored procedures to update the subsequent tables. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
Zitat von [EMAIL PROTECTED]: One reason may be due to your ON condition. ON table2.ID=table2.ID_table1 You reference table 2 twice and I doubt that on the table2 table id equals id_table1 very often. Because table2 is LEFT JOINed to table1, you will only be able to update the rows that match your ON condition. Again, that would mean that you have nothing to update which would cause no changes to table2 so it would appear that the IF() is always false. Hmm, I really should have read that query twice, sorry UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id Problem: table2.value is never changed, even if table1.condition equals 7 before the query. Greetings Kai -- This signature is left as an exercise for the reader. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie: Using SELECT to Get Table Status?
I knew about COUNT(*) but thanks. I was mostly looking for a way to get the time stamp of the last update or insert. I did find a way using an ADODB.Command object. I was able to send the command SHOW TABLE STATUS LIKE 'TABLENAME'; The results were returned as a recordset. Have a great day. -- Eric Robinson -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 28, 2004 12:22 AM To: mysql@lists.mysql.com Subject: Re: Newbie: Using SELECT to Get Table Status? Hello. You may use count(*) for counting rows. Robinson, Eric [EMAIL PROTECTED] wrote: The mysqlSHOW TABLE STATUS command returns such things as the number or rows and time of the last update.=20 Is it possible to get the same information using a SELECT statement? FYI, I am writing a script to monitor replication status by comparing this information between the master and slave servers. -- Eric Robinson -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
Jay Blanchard [EMAIL PROTECTED] wrote on 12/28/2004 10:21:43 AM: [snip] UPDATE table1 LEFT JOIN table2 ON table2.ID=table2.ID_table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id Is this behavior correct? [/snip] In the sense that it did what was given to it in the query, yes. From http://www.mysql.com/update If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement will fail and roll back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly. If you are using MyISAM style tables you should never attempt multiple table updates without triggers and/or stored procedures to update the subsequent tables. That may be good advice IF you have a MySQL version that supports triggers and stored procedures. And I know for a fact that I do not want to nor should I need to code a trigger or SP EACH and EVERY time I want to update two tables at one time or use the values of one table to update another. I believe your answer was neither on topic nor very useful. That is an excellent warning you quoted from the manual but I do not believe that it applies to this particular problem. I also humbly disagree with your analysis of the warning as neither triggers nor stored procedures are in production MySQL, yet, but multiple table updates are. (I am operating on the belief that triggers and stored procedures are part of the 5.x release that is still in testing and development and not part of 4.1.x. I could be mistaken.) Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
Can you provide us a test case (sample table definitions and sample data) ? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kai Ruhnau [EMAIL PROTECTED] wrote on 12/28/2004 10:23:23 AM: Zitat von [EMAIL PROTECTED]: One reason may be due to your ON condition. ON table2.ID=table2.ID_table1 You reference table 2 twice and I doubt that on the table2 table id equals id_table1 very often. Because table2 is LEFT JOINed to table1, you will only be able to update the rows that match your ON condition. Again, that would mean that you have nothing to update which would cause no changes to table2 so it would appear that the IF() is always false. Hmm, I really should have read that query twice, sorry UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID=$id Problem: table2.value is never changed, even if table1.condition equals 7 before the query. Greetings Kai -- This signature is left as an exercise for the reader. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] I believe your answer was neither on topic nor very useful. That is an excellent warning you quoted from the manual but I do not believe that it applies to this particular problem. I also humbly disagree with your analysis of the warning as neither triggers nor stored procedures are in production MySQL, yet, but multiple table updates are. (I am operating on the belief that triggers and stored procedures are part of the 5.x release that is still in testing and development and not part of 4.1.x. I could be mistaken.) [/snip] Thank you for your assessment of my answer. It is too bad that your reply was also not very useful in the context of the OP's question. As a user of MySQL for several years I have found that multi-table updates can be done, but are very tricky, especially with regards to the order that the query is processed in. There is a good discussion of this at http://www.mysql.com/update You are correct in your belief that 4.1.x does not support triggers and or SP. Given that, I would suggest that the OP utilize his programming language to generate these actions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is there a utility like mysqlbinlog but instead processes the query log?
is there a utility like mysqlbinlog but instead processes the query log? I would like to reprocess the queries from the query log. additional features that would be nice would include: listing unique queries the number of times each unique query is executed Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
Zitat von [EMAIL PROTECTED]: Can you provide us a test case (sample table definitions and sample data) ? Sure. -- CREATE TABLE table1 ( ID int(10) unsigned NOT NULL default '0', condition int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table1 VALUES (1,7); INSERT INTO table1 VALUES (2,8); CREATE TABLE table2 ( ID int(10) unsigned NOT NULL default '0', ID_table1 int(10) unsigned NOT NULL default '0', value int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table2 VALUES (1,1,5); INSERT INTO table2 VALUES (2,2,2); -- And the query: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) After that the value's in table2 are still 5 and 2. But I would expect the 5 to be a 4. MySQL: 4.0.20 Greetings Kai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi table update
[snip] And the query: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) After that the value's in table2 are still 5 and 2. But I would expect the 5 to be a 4. [/snip] It was concerning the warning I sent about InnoDB and the order of precedence.the query to solve the problem is here update table2, table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1 Note that you put table1 first in your query above, so table 1 will get updated first. Since that is the case the conditions for updating table2 are never met. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparison SQL always return false
sam wun wrote:
Hi,
I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
I found that if I assign a hard coded string (with double quote) to
the string variable which in turns become part of the SQL statement,
the SQL does return result.
If I assign a value which is return from an array in perl (eg.
@array[1]) and use this variable in the SQL statement, the SQL does
not return any result.
$array[1] not @array[1].
For example:
The following code does not return any result
my $mycustcode = @outlets[1]; # this array contains string 07-2-0057
However, if I use the following hard coded assignment, the SQL
statement returns result:
# my $mycustcode = 07-2-0057;
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type,
sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and date(t.date) = $start_date
and date(t.date) = $end_date
and (c.type = EXPORT or c.type = LOCAL)
and (c.custcode = $mycustcode)
group by c.custcode;};
I may be using incorrect way to split the tokens, here is the function
to extract a list of the token:
$outlet_str = $in{'outlets'}
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error in Complex SELECT
I'm trying to do the following SELECT statement: SELECT * FROM matches WHERE question_id = ANY (SELECT server_id FROM questions WHERE results_id = 'MyID') I've tried rephrasing it as: SELECT * FROM matches WHERE question_id IN (SELECT server_id FROM questions WHERE questions.results_id = 'MyID') And even: SELECT * FROM matches WHERE question_id IN (SELECT q.server_id FROM questions as q WHERE q.results_id = 'MyID') But no go--in all cases, I get an syntax error near the first parentheses. Any suggestions? This is on MySQL 4.0.22-standard. Thanks, Adam Ernst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
My Testing and results at the bottom. Suspect a buggy implementation of the IF() calculation (see tests and results). Kai Ruhnau [EMAIL PROTECTED] wrote on 12/28/2004 11:06:43 AM: Zitat von [EMAIL PROTECTED]: Can you provide us a test case (sample table definitions and sample data) ? Sure. -- CREATE TABLE table1 ( ID int(10) unsigned NOT NULL default '0', condition int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table1 VALUES (1,7); INSERT INTO table1 VALUES (2,8); CREATE TABLE table2 ( ID int(10) unsigned NOT NULL default '0', ID_table1 int(10) unsigned NOT NULL default '0', value int(10) unsigned NOT NULL default '0' ) TYPE=InnoDB; INSERT INTO table2 VALUES (1,1,5); INSERT INTO table2 VALUES (2,2,2); -- And the query: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) After that the value's in table2 are still 5 and 2. But I would expect the 5 to be a 4. MySQL: 4.0.20 Greetings Kai This worked: UPDATE table1 LEFT JOIN table2 ON table2.ID_table1=table1.ID AND table1.condition=7 SET table2.value= table2.value-1, table1.condition=8 WHERE table1.ID IN (1,2); localhost.testselect * from table1; ++---+ | ID | condition | ++---+ | 1 | 8 | | 2 | 8 | ++---+ 2 rows in set (0.00 sec) localhost.testselect * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 4 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) These did not: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2); localhost.testselect * from table1; ++---+ | ID | condition | ++---+ | 1 | 8 | | 2 | 8 | ++---+ 2 rows in set (0.00 sec) localhost.testselect * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 5 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=IF(table1.condition=7,table2.value-1,table2.value), table1.condition=8 WHERE table1.ID IN (1,2); localhost.testselect * from table1; ++---+ | ID | condition | ++---+ | 1 | 8 | | 2 | 8 | ++---+ 2 rows in set (0.00 sec) localhost.testselect * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 5 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) However when I tried the same update WITHOUT trying to update table1.condition in the same query, it worked every time: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=IF(table1.condition=7,table2.value-1,table2.value) WHERE table1.ID IN (1,2); localhost.testselect * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 4 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=(table2.value-IF(table1.condition=7,1,0)) WHERE table1.ID IN (1,2); localhost.testselect * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 4 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0) WHERE table1.ID IN (1,2); localhost.testselect * from table2; ++---+---+ | ID | ID_table1 | value | ++---+---+ | 1 | 1 | 4 | | 2 | 2 | 2 | ++---+---+ 2 rows in set (0.00 sec) So -- This may be a bug in the way that my version (4.1.1a-alpha-nt-log) and yours prioritizes the IF() calculation. I guess it is checking the value AFTER table1.condition gets set to 8. However, I would have also assumed that table1.condition would have held it's ORIGINAL value of 7 long enough to make your IF() condition true. Fortunately, I found a JOIN workaround that achieved the expected results. Let me know if you need any help adapting it to fit your actual tables. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Error in Complex SELECT
I believe your problem is that versions of MySQL prior to 4.1 (i think) do not allow subqueries. You may try rewriting your query as a join. On Tue, 28 Dec 2004 12:14:06 -0600, Adam Ernst [EMAIL PROTECTED] wrote: I'm trying to do the following SELECT statement: SELECT * FROM matches WHERE question_id = ANY (SELECT server_id FROM questions WHERE results_id = 'MyID') I've tried rephrasing it as: SELECT * FROM matches WHERE question_id IN (SELECT server_id FROM questions WHERE questions.results_id = 'MyID') And even: SELECT * FROM matches WHERE question_id IN (SELECT q.server_id FROM questions as q WHERE q.results_id = 'MyID') But no go--in all cases, I get an syntax error near the first parentheses. Any suggestions? This is on MySQL 4.0.22-standard. Thanks, Adam Ernst -- 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: Multi table update
Jay Blanchard [EMAIL PROTECTED] wrote on 12/28/2004 11:38:58 AM: [snip] And the query: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) After that the value's in table2 are still 5 and 2. But I would expect the 5 to be a 4. [/snip] It was concerning the warning I sent about InnoDB and the order of precedence.the query to solve the problem is here update table2, table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1 Note that you put table1 first in your query above, so table 1 will get updated first. Since that is the case the conditions for updating table2 are never met. I disagree with your logic, not your statement. It works fine. Especially when using InnoDB, the table1 values should remain their original values throughout the entire statement. The row locking should prevent a visible change in a row of table1 between SET statements in the same UPDATE. If a row has a condition value of 7 coming into the SET clause, it should have that value for every element of the SET clause. The row should only be updated when the engine moves into the next row. The cumulative changes would be committed when the transaction commits. I interpreted the warning you quoted this way: An UPDATE statement may be evaluated in a sequence that could violate FOREIGN KEY constraints because the query optimizer does not favor parent tables over child tables in computing order of operations. I did not read it as saying: the order in which you join your tables will make a difference in the evaluation of the SET assignments during the execution of an UPDATE statement. Because CHANGING THE ORDER by which the tables are joined CHANGES THE RESULTS, this is definitely a BUG. These two statements SHOULD have the same results but DO NOT: update table1, table2 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1; Rows matched: 4 Changed: 1 Warnings: 0 update table2, table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1; Rows matched: 4 Changed: 2 Warnings: 0 I would be deliriously happy if someone could explain WHY these statement _should_ have different results. If I am wrong and these UPDATE statements are supposed to provide different results, I would like to learn how I became so confused over something this fundamental. Until I get credible information to the contrary, I will stick with the hypothesis that this is a BUG in the execution engine. Thanks in advance, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Help upgrading from 4.1.1 to 4.1.8
I haven't found anything in the documentation that will help me upgrade mysql from 4.1.1 to 4.1.8. We have several databases running under 4.1.1. How do they get migrated to run under 4.1.8? How do we migrate our user data over? Can someone point me toward a FAQ that might help me or the documentation that I haven't found yet, or tell me how to do this? Thanks!
Weird server timeout problem
Hello, I've been fighting this problem for a while, and now it's time to ask the experts. Please also CC me on this, I'm only on the list as a digest. I have a dual Xeon 2.8ghz HT server with plenty of ECC ram running Linux 2.6 and MySQL 4.1.8 (4.1.7 also). Anywhere from 0.001% to 0.05% or so of the queries (identical) take longer than expected to complete. Most queries are reported as completing in 0.030 seconds or less as reported by time(1). The queries I'm having problems with take 5-15 seconds to complete, but the weird part is that they're the exact same queries. There seems to be no load problem (stays under 1.0 most of the time, lots of free and cached ram) and the problem happens even when the server is almost 100% idle. Here is some output from my testing (expl. under output): snip real0m0.045s user0m0.007s sys 0m0.004s real0m0.052s user0m0.009s sys 0m0.003s real0m0.023s user0m0.010s sys 0m0.004s real0m0.023s user0m0.011s sys 0m0.006s real0m6.802s user0m0.008s sys 0m0.003s real0m0.553s user0m0.003s sys 0m0.004s ctrl+c Those results are from: while true; do time mysql -u user -ppass -h mysql-server databasename -e SELECT * FROM table WHERE x = 'y'; /dev/null; done I CTRL+C'd it when it took 6 seconds. There was about 30 seconds of very quick responses that I've left out. This is as simple as I can show it, I first noticed the problem with a custom app that has a 3 second timeout and was failing at times. It happens in every table on this server, and I've myismchecked all of them. It doesn't seem (seem here, is important) to happen, however, when the database is copied and ran on a completely unloaded server (same software). I've tried using the static mysqld binary from mysql.com with the same results as the distro's binary. I've also tried different glibc versions (although this shouldn't affect the static mysqld anyways) also with no luck. I'd be very interested in hearing any theories and/or solutions to my mystery. Thanks much, Dale -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unique key - primary key
Hi, I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid (HONstid,HONname) whereas on table 2 it says ... PRIMARY KEY (IMstid,IMfnum) What is the difference? Is there any? Table1: mysql desc table1; ... | HONstid | int(3)| | PRI | 1| | | HONname | char(20) | | PRI | standard | | ... Table2: mysql desc table2; ... | IMstid | int(3) | | PRI | 0 | | | IMfnum | int(6) | | PRI | 0 | | ... A general question: is there a difference between a primary key and a unique key, or they are synonims? Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi table update
Zitat von Jay Blanchard [snip] And the query: UPDATE table1 INNER JOIN table2 ON table2.ID_table1=table1.ID SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID IN (1,2) After that the value's in table2 are still 5 and 2. But I would expect the 5 to be a 4. [/snip] It was concerning the warning I sent about InnoDB and the order of precedence.the query to solve the problem is here My reading of the mentioned paragraph is another: It only describes the case when foreign key constraints may temporably be broken during a multi table update, so that the InnoDB engine does not perform the query. Although my question goes in that direction, it is more about why this statement does not act atomically from my applications point of view. update table2, table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1 I wonder if a subselect can help here, too. Gretins Kai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rpms for rhel
Which rpms are the right ones to use for RHEL v3u4? I'm not sure which release of mysql I need, yet, but looking at the site, I'm not sure how to tell which are the ones that would even be appropriate for my system. TIA, -ste -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unique key - primary key
Hello, I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid (HONstid,HONname) whereas on table 2 it says ... PRIMARY KEY (IMstid,IMfnum) What is the difference? Is there any? A table can have only 1 primary key, but multiple unique constraints. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unique key - primary key
At 22:27 +0100 12/28/04, Martijn Tonies wrote: Hello, I have two tables, seemigly very similar setup; the primary key is the combination of two columns. With mysqldump, however, the table definition of the two tables looks different. Mysqldump on table 1 says ... UNIQUE KEY HONstid (HONstid,HONname) whereas on table 2 it says ... PRIMARY KEY (IMstid,IMfnum) What is the difference? Is there any? A table can have only 1 primary key, but multiple unique constraints. And columns in primary keys must be NOT NULL. Columns in unique keys can be NULL (if they are NOT NULL, then the unique key is functionally the same as a primary key). -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monitoring replication in mysql
We use a monitoring system that does TCP based checks on our various systems and can alerts us based on criteria we define. So we right shell scripts that run locally and return certain values and tie those scripts to specific TCP ports using /etc/inetd.conf and /etc/services - This is the script we use to monitor replication on every machine (it's much shorter without my excessive comments): #!/bin/sh # # # Bruce's MySQL Replication Verification Script # /usr/local/mysql/bin/mysql -e show status like 'Slave_running'; This script is then tied to a port, so any web browser or our monitoring system hits http://mysqlserver: (or whatever port you decide on) should get this: Variable_nameValue Slave_runningON From there our monitor takes that data and looks for the keyword ON, if it's there it's happy, if it matches the keyword OFF it sends an alert page and marks the instance as in warning state, any response that doesn't include ON or OFF generates a service down state and also sends pages etc... (If MySQL is running then the slave status will either be ON or OFF... If mysql isn't running the mysql client returns it's own error saying it's unable to connect). Best Regards, Bruce On 12/28/04 1:44 PM, Bruce Dembecki [EMAIL PROTECTED] wrote: Tucker, Gabriel wrote: Anil Write a script that does a slave status and check if either of the threads are running. You could further check for error numbers and descriptions. This is what we do. Gabe -Original Message- From: Anil Doppalapudi [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 21, 2004 6:05 AM To: [EMAIL PROTECTED] Subject: Monitoring replication in mysql Hi, we have no of mysql replication setups in our setup. how to monitor those replication setups.my aim is if any slave goes down my script should immediately send an alert mail to me. if anybody having already developed scripts please let me know otherwise just give me an idea what to monitor in in replication setup. Thanks Anil DBA We have a script that monitors output from SHOW SLAVE STATUS, but actually had one time when replication died, but output from above command looked perfectly fine. It was due to massive table corruption, which was in turn due to filesystem corruption. Now, we have the same test running, but we also have a backup monitor which inserts a value in the master and tries to read it from all replicants. We allow an acceptable delay (5-10 minutes) before we page all admins with this backup test. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unique key - primary key
Hi, RE: And columns in primary keys must be NOT NULL. Columns in unique keys can be NULL (if they are NOT NULL, then the unique key is functionally the same as a primary key). OK, thanks guys for the explanation. Then the result of mysqldump table definition part: UNIQUE KEY HONstid (HONstid,HONname) means that I have two unique keys: HONstid and also the (HONstid,HONname) combination? This does not make too much sense; if I can have only one HONstid in my table, then of course i can have only one kind of (HONstid,HONname) combination. In the actual table, I have multiple occurences of values (HONstid,HONname)in the HONstid column, i.e. it does not seem like HONstid in itself was a unique key. Only the (HONstid,HONname) combination is unique. Cheers Gaspar p.s.: Paul, I very much enjoy your MySQL book. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fixing the worst InnoDB corruption bug in 3 years - when
In the MySQL Manual under InnoDB in the Using Per-Table Tablespace section it says clearly at the top: NOTE: CRITICAL BUG in 4.1 if you specify innodb_file_per_table in `my.cnf'! If you shut down mysqld, then records may disappear from the secondary indexes of a table. See (Bug #7496) for more information and workarounds. Following the link to Bug 7496 (http://bugs.mysql.com/bug.php?id=7496) we are told two important things: 1. This is the worst InnoDB corruption bug in 3 years. 2. Will be fixed in 4.1.9. So thanks to Heikki for finding and fixing this. So now to the question... As a person in the process of migrating from 4.0 to 4.1 and having already scheduled the downtime with my clients for this Friday morning, and having to do a full dump and import already as part of the migration process I'd like to know WHEN the fix will be available. I don¹t have a lot of opportunities for a full dump and import, so this is a crucial time for me, and there are some benefits with innodb_file_per_table that are important to us. If we go with history then we should expect a new version of the current MySQL products every 2 months approximately. Having just received 4.1.8 I'd not like to see MySQL leave InnoDB's worst corruption bug in three years sit for two months when a fix has already been written. Can we have a new build with this fix included please? When can we have it? The grab it from the nightly snapshots and compile it yourself answer won't cut it when we have to deploy into production and MySQL's company line is to only use MySQL official binaries in production. If not 4.1.9 can we call it 4.1.8b and get it shipped (there's already a 4.1.8a). Best regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL unixsocket to tcp connection
Hi, I have a RADIUS server (radiator), reading and writting intensively in a mysql database. I was timming my scripts, and the biggest time the scripts took are in the DBI-connect process The database is in another server, so I have to connect to the database over tcp. My question is: Is there any program that opens a connection to mysql, and that I could use to connect to the database using unixsocket using always the same connection? Anyone have anyother ideia how I could speed up my connection phase? Thanks everyone, José Miguel Fernandes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW TABLE STATUS: Update_Time Is Wrong?
When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS: Update_Time Is Wrong?
Robinson, Eric wrote: When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? Let us know if your timezones are set correctly. -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
value of array to sql statement
Hi list,
There is problem when with executing the sql statement in mysql 5.0.
I m not sure whether version 4.x has the same problem.
The problem is if I use static (hardcoded) values in the SQL statement,
mysql returns result for each element in the array.
If I use dynamically allocated value that assigned to the array, the sql
statement only return result for the first element of the array.
To illustrate the problem please see the code below.
The following perl code does return result when execute the SQL statement:
@outlets = (07-6-0057,07-3-0051,07-2-0036);
my $mycustcode = $outlets[1]; # also work for [0] or [2]
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = $mycustcode
group by c.custcode;};
However if I change the code to be a bit more dynamic like the following:
In html.pl: # the following value (in the OPTION tag) will be submitted
to the query1_result.cgi.
while ($aref = $sth-fetchrow_arrayref){
print OPTION value=$aref-[0],$aref-[1]: $aref-[2]/Option\n;
}
In query1_result.cgi:
sub split_outlets_to_array
{
my ($s) = @_;
@outlets = split(',',$s);
return @outlets;
}
$outlet_str = $in{'outlets'}; # the outlets contains a list of custcodes
that seperated by comma.
@outlets_array = split_outlets_to_array($outlet_str);
my $mycustcode = $outlets_array[0]; # but value of [1] and [2] can't
make the following SQL statement return a result.
$sql = qq {
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = $mycustcode
group by c.custcode;};
Summary:
The problem with the second case is that outlets_array[0] does make the
SQL statement return result, but [1] and [2] does not.
With the first case (with hardcoded values), all elements of
outlets_array does make the SQL return result.
There may be problem in the code $in{'outlets'}. However from printing
each element of the array on the html page, I found nothing wrong with
the value, they are all printed in the following format on the html page:
outlets[0]: 07-6-0057
outlets[1]: 07-3-0051
outlets[2]: 07-2-0036
I don't know what caused this error when execute it with the SQL statement.
I tried to turn on warninig with -w, but not sure how to see them when
running in web browser.
Thanks
Jack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help: Index performance on moderately sized table
Hopefully someone can help me out here :) I have a pretty simple table that is 400k rows and growing. I'm doing, to me, some relatively simple searches but they are taking forever (6 seconds in my case is faaar too long). Anyway, here's the setup: Tested on MySQL 4.0.21 (Windows 2000 Server, single 1.5GHz Athalon) and 4.1.7 (Mac OS X dual 2.0GHz G5): Schema of table (there's more than this, but this is the focus of my current issue): create table queries ( id bigint unsigned primary key auto_increment, created datetime not null, author varchar(100) not null, index (created) index (author) )type=myisam; so, when I do something like this: select created, author from queries where created = '2004-01-01' and created '2005-01-01' limit 10; Or select created, author from queries order by author limit 10; I get back what I want in less than 1 second. If I do this, though: select created, author from queries where created = '2004-01-01' and created '2005-01-01' order by author limit 10; It takes between 4 and 10 seconds (depending on what I'm up to). It seems like the where and the order don't play nice, and I can't seem to figure out how to get an index that applies to all the criteria. I've tried multicolumn indexes, forcing indexes, setting the max_seeks_for_key to 100, etc. Nothing seems to make it better. If anyone has any advice, I'd love to hear it. Adam. -- --- Adam Randall http://www.xaren.net [EMAIL PROTECTED] AIM/iChat: blitz574 [EMAIL PROTECTED] Macintosh users are a special case. They care passionately about the Mac OS and would rewire their own bodies to run on Mac OS X if such a thing were possible. -- Peter H. Lewis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regression bug in 4.0.23? FROM_UNIXTIME(0) is now NULL, not 1970-01-01 01:00:00
Hi, Peter! * Peter Valdemar M?rch [EMAIL PROTECTED] [04/12/28 17:33]: Hi there, A bug was fixed in 4.0.23: * Fixed bug which caused FROM_UNIXTIME() function to return wrong result if the argument was too big. http://bugs.mysql.com/6439 However, it looks like it has side effects: select FROM_UNIXTIME(0) returns 1970-01-01 01:00:00 in 4.0.22 but NULL in 4.0.23... (European localtime) Needless to say, this confuses application logic (especially if the type is DATETIME NOT NULL). Is this a bug? Should I file a bug at http://bugs.mysql.com/ ? Yes this is a bug. It was already reported as #7515 from_unixtime(0) now returns NULL instead of the epoch. So no need to report it once more. It will be fixed ASAP. Anyway, thank you for raising this question once again! -- Dmitri Lenev, Software Developer MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
