Innodb crash under Win2000
Description: Hello, We are working with mysqld-max-nt 4.0.14 under Win2000 service-pack4. We have a server with two Pentium-III MMX 500Mhz proccesors and 780MB Ram. We work with InnoDB tables. We have reserved 300MB to InnoDB and use a RAID 5. We were working when Mysql/Innodb crashed. We were not able to connect to MySql. We noticed that There wasnt the mysqld-max-nt service (this service is manual) So, we ran the service. Then, I tried to check the database but I lost the connection when I was checking a determined table, lincompras. So I run the service again, and altered the lincompras table type to myisam and later to innodb, checked again the database and didnt get any error. The error log file is: InnoDB: Dump of the child page: 030909 8:43:03 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 6068cb63041d0425042186968aac45 We have been working without problems but when I have opened the error log file I have seen the next error: MySql: preparado para conexiones 030909 12:05:22 InnoDB: error clustered record for sec rec not found InnoDB: index CliFecArt table peco/linventas InnoDB: sec index record RECORD: info bits 0 0: len 6; hex 303030333839; asc 000389;; 1: len 3; hex 8fa6ac; asc .;; 2: len 8; hex 3030343231343130; asc 00421410;; 3: len 10; hex 4a41303330b032323137; asc JA0302217;; 4: len 10; hex 58413033303033353134; asc XA03003514;; 5: len 2; hex 8001; asc .;; InnoDB: clust index record RECORD: info bits 0 0: len 10; hex 4a413033303035313931; asc JA03005191;; 1: len 0; hex ; asc ;; 2: len 2; hex 8001; asc .;; 3: len 6; hex 005acd6e; asc ...Zn;; 4: len 7; hex 805c010084; asc ..\..;; 5: len 0; hex ; asc ;; 6: len 3; hex 8fa729; asc .);; 7: len 2; hex 3030; asc 00;; 8: len 8; hex 3030323731303430; asc 00271040;; 9: len 32; hex 524f4c4c4f203530204d54532e43494e5441205445534142414e442d34363631; asc ROLLO 50 MTS.CINTA TESABAND-4661;; 10: len 8; hex f03f; asc ..?;; 11: len 1; hex 31; asc 1;; 12: len 8; hex 4a40; asc ..J@;; 13: len 4; hex 5242; asc ..RB;; 14: len 8; hex fca9f1d24d023640; asc M.6@;; 15: len 4; hex 8041; asc ..A;; 16: len 4; hex ; asc ;; 17: len 2; hex 4547; asc EG;; 18: len 3; hex 202020; asc;; 19: len 4; hex ; asc ;; 20: TRANSACTION 0 5953488, ACTIVE 1 sec, OS thread id 1864 fetching rows, thread declared inside InnoDB 415 MySQL thread id 89, query id 54296 192.168.1.210 lourdes Sending data SELECT `LinVentas`.`Can`, `LinVentas`.`MCa`, `LinVentas`.`Pre`, `LinVentas`.`Dto`, `Clientes`.`Ruta_Comercial`, `Ruta_Comercial`.`Nom`, `LinVentas`.`C InnoDB: Make a detailed bug report and send it InnoDB: to [EMAIL PROTECTED] Any ideas? Thanks in advance, Rafa How-To-Repeat: - Fix: - Synopsis:Innodb crash under win2000 Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.14 Exectutable: mysqld-max-nt Environment: 2 Pentium III-MMX, 500 MHZ, 780 MB System:Windows 2000 Compiler: - Architecture: i __ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alias Error in a select statement
Description: Hello, When I run the following query: Select Can as MiCantidad, MiCantidad * 2 from Stocks limit 10 I get the following error: 'The column 'MiCantidad' in field list is unknown' So, Can I reference to a column by its alias in a select statement? Thanks in advance, Rafa How-To-Repeat: Select Can as MiCantidad, MiCantidad * 2 from stocks limit 10 Fix: - Synopsis:optimizer bug in the index used by mysql/Innodb in the search Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.12 Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ Try AOL and get 1045 hours FREE for 45 days! http://free.aol.com/tryaolfree/index.adp?375380 Get AOL Instant Messenger 5.1 for FREE! Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promos=380455 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimizer bug in the index used by mysql/Innodb in the search
Description: Hello Peter, Have you explained to Heikki this problem? Have you fixed it? Please, tell me about it. Regards, Rafa How-To-Repeat: Select ... from giros ... Fix: - Synopsis:optimizer bug in the index used by mysql/Innodb in the search Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.11 Gamma(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ Try AOL and get 1045 hours FREE for 45 days! http://free.aol.com/tryaolfree/index.adp?375380 Get AOL Instant Messenger 5.1 for FREE! Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promos=380455 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in the index used by mysql/Innodb in the search
Description: Hello Peter, I have sent to ftp://support.mysql.com/pub/mysql/secret the table definition and data (Giros.txt) in a compressed file named Giros.zip so you can invetigate it. You can see the following queries: SELECT TIPO,DOC,NRE FROM GIROS WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE'01/01') OR (GIROS.TIPO='R' AND GIROS.DOC'ZA03003996') OR GIROS.TIPO'R' ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC LIMIT 1 InnoDB Time: 0.20 secs. Explain: table type possible_keys keykey_len ref rows Extra GIROS range PRIMARY,TipoFeVCod TipoFeVCod1 NULL 9417 Using where; Using index; Using filesort MYISAM Time:0.02 secs Explain: table type possible_keys keykey_len ref rows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16NULL 19472 Using where; Using index --- SELECT TIPO,DOC,NRE FROM GIROS WHERE CONCAT(TIPO,DOC,NRE)='RZA0300399601/01' ORDER BY TIPO DESC,DOC DESC,NRE DESC LIMIT 1 INNODB/MyIsam Time: 0.02 secs. INNODB/MyIsam Explain: table type possible_keys key key_len ref rows Extra GIROS index NULL PRIMARY 16 NULL 19516 Using where; Using index Both MyIsam and InnoDB return the record in the same time and use the same index PRIMARY. This query is the same as the above query. --- Also, in this query (I deleted the third condition of the where clause TIPO'R') SELECT TIPO,DOC,NRE FROM GIROS WHERE (TIPO='R' AND DOC='ZA03003996' AND NRE'01/01') OR (TIPO='R' AND DOC'ZA03003996') ORDER BY TIPO DESC, DOC DESC,NRE DESC limit 1 Both MyIsam and InnoDB use the index PRIMARY. InnoDB Time:0.03 secs. Explain: table type possible_keys key key_len refrows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 1 Const 1933 Using where; Using index MyIsam Time: 0.02 secs. table type possible_keys key key_len refrows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16 Null 3910 Using where; Using index I don´t understand why if I add the third condition: OR TIPO'R' InnoDB isn´t still using the PRIMARY INDEX. --- Finally, SELECT TIPO,DOC,NRE FROM GIROS WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE'01/01') OR (GIROS.TIPO='R' AND GIROS.DOC'ZA03003996') OR GIROS.TIPO'R' ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC There is no LIMIT. Table type MyIsam: Returned records: 19486 in 0.59 secs. Explain: table type possible_keys key key_len ref rows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16 NULL 19472 Using where; Using index Table type InnoDb: Returned records: 19486 in 1.18 secs. Explain: table type possible_keys key key_len ref rows Extra GIROS range PRIMARY,TipoFeVCod TipoFeVCod 1 NULL 9417 Using where; Using index; Using filesort With InnoDB, the optimizer believes it must examine 9417 records, but actually it returns 19486 records, the same records as MyIsam but twice slower, so I think the optimizer is okey in MyIsam and wrong in InnoDB. Thanks in advance, Rafa How-To-Repeat: Select ... from giros ... Fix: - Synopsis:optimizer bug in the index used by mysql/Innodb in the search Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.11 Gamma(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in the index used by mysql/Innodb in the search
Description: Hello Alexander, You wrote: From EXPLAIN result output you can do only opposite conclusion: With InnoDB MySQL chooses to use Index TipoFeVCod for which it expects to match 9417 rows. This is about twice less rows than expected with PRIMARY key in second explain 19472 But the fact is that the query SELECT TIPO,DOC,NRE FROM GIROS WHERE (TIPO='R' AND DOC='ZA03003996' AND NRE'01/01') OR (TIPO='R' AND DOC'ZA03003996') OR TIPO'R' ORDER BY TIPO DESC, DOC DESC, NRE DESC LIMIT 1 returns only one record (limit 1), and in MyIsam it returns the record in 0.02 secs. while in innoDB it returns the record in 0.20 secs. (10 times slower) regardless of InnoDB expects to match only 9417 (I think the optimizer is wrong). The number of records in the table Giros is 19507. There are 15278 records of tipo='E' and 4229 of tipo='R'. I think the optimizer is wrong when it expects to match 9417 rows and the WHERE CONDITION matches fully whith the PRIMARY INDEX, so I don´t understand why it chooses the other index. --- On the other hand, let's see the following query which is like the above query: SELECT TIPO,DOC,NRE FROM GIROS WHERE CONCAT(TIPO,DOC,NRE)='RZA0300399601/01' ORDER BY TIPO DESC,DOC DESC,NRE DESC LIMIT 1 INNODB/MyIsam Time: 0.02 secs. INNODB/MyIsam Explain: table type possible_keys key key_len ref rows Extra GIROS index NULL PRIMARY 16 NULL 19516 Using where; Using index Both MyIsam and InnoDB return the record in the same time and use the same index PRIMARY. This query is the same that the above query. --- Also, in this query (I deleted the third condition of the where clause TIPO'R') SELECT TIPO,DOC,NRE FROM GIROS WHERE (TIPO='R' AND DOC='ZA03003996' AND NRE'01/01') OR (TIPO='R' AND DOC'ZA03003996') ORDER BY TIPO DESC, DOC DESC,NRE DESC limit 1 Both MyIsam and InnoDB use the index PRIMARY. InnoDB Time:0.03 secs. Explain: table type possible_keys key key_len refrows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 1 Const 1933 Using where; Using index MyIsam Time: 0.02 secs. table type possible_keys key key_len refrows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16 Null 3910 Using where; Using index I don´t understand why if I add the third condition: OR TIPO'R' InnoDB isn´t still using the PRIMARY INDEX. --- Finally, SELECT TIPO,DOC,NRE FROM GIROS WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE'01/01') OR (GIROS.TIPO='R' AND GIROS.DOC'ZA03003996') OR GIROS.TIPO'R' ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC There is no LIMIT. Table type MyIsam: Returned records: 19486 in 0.59 secs. Explain: table type possible_keys key key_len ref rows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16 NULL 19472 Using where; Using index Table type InnoDb: Returned records: 19486 in 1.18 secs. Explain: table type possible_keys key key_len ref rows Extra GIROS range PRIMARY,TipoFeVCod TipoFeVCod 1 NULL 9417 Using where; Using index; Using filesort With InnoDB, the optimizer believes it must examine 9417 records, but actually it returns 19486 records, the same records as MyIsam but twice slower, so I think the optimizer is okey in MyIsam and wrong in InnoDB. Thanks in advance, Rafa How-To-Repeat: Select ... from giros ... Fix: - Synopsis:optimizer bug in the index used by mysql/Innodb in the search Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.11 Gamma(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Crystal 9 cannot see the tables of a mysql database
Hello, I am using mysql 4.0.11 and MyODBC 3.51.05 and Crystal 9 cannot see the tables of a database. Any ideas? thanks, Rafa. __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in the index used by mysql/Innodb in the search
Description: Hello, I am working with mysql/InnoDb 4.0.11 under Windows 2000. I have the following table: CREATE TABLE `giros` ( `Doc` varchar(10) NOT NULL default '', `Tipo` char(1) NOT NULL default '', `NRe` varchar(5) NOT NULL default '', `FoP` char(2) NOT NULL default '', `FeL` date default NULL, `FeV` date default NULL, `NRm` varchar(6) NOT NULL default '', `Pis` char(2) NOT NULL default '', `Imp` decimal(20,4) NOT NULL default '0.', `Cod` varchar(6) NOT NULL default '', `Nom` varchar(40) NOT NULL default '', `Dir` varchar(35) NOT NULL default '', `Cop` varchar(6) NOT NULL default '', `Pob` varchar(30) NOT NULL default '', `Pro` varchar(25) NOT NULL default '', `BCd` varchar(8) NOT NULL default '', `BNm` varchar(40) NOT NULL default '', `BDr` varchar(35) NOT NULL default '', `BPb` varchar(30) NOT NULL default '', `BPr` varchar(25) NOT NULL default '', `Cba` varchar(10) NOT NULL default '', `Dct` char(2) NOT NULL default '', `NLe` text, `Cob` tinyint(4) unsigned NOT NULL default '0', `Ctb` tinyint(4) unsigned NOT NULL default '0', `BCp` varchar(6) NOT NULL default '', `Impreso` tinyint(4) unsigned NOT NULL default '0', `Est` varchar(20) NOT NULL default '', `Linea` int(11) NOT NULL default '0', PRIMARY KEY (`Tipo`,`Doc`,`NRe`), KEY `NRmLinea` (`NRm`,`Linea`), KEY `TipoFeVCod` (`Tipo`,`FeV`,`Cod`), KEY `CodTipoDocFeV` (`Cod`,`Tipo`,`Doc`,`FeV`) ) TYPE=InnoDB; I ran the following query: SELECT TIPO,DOC,NRE FROM GIROS WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE'01/01') OR (GIROS.TIPO='R' AND GIROS.DOC'ZA03003996') OR GIROS.TIPO'R' ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC LIMIT 1 Time: 0.20 secs. Explain: table type possible_keys keykey_len ref rows Extra GIROS range PRIMARY,TipoFeVCod TipoFeVCod1 NULL 9417 Using where; Using index; Using filesort I changed the table type to MyIsam (alter table giros type=MyIsam), and I ran the above query: Time:0.02 secs Explain: table type possible_keys keykey_len ref rows Extra GIROS range PRIMARY,TipoFeVCod PRIMARY 16NULL 19472 Using where; Using index So, MySQL/MyIsam uses the right index (the primary index) while MySQL/InnoDB uses a wrong index (TipoFeVCod). Thanks in advance, Rafa How-To-Repeat: Select ... from giros ... Fix: - Synopsis:optimizer bug in the index used by mysql/Innodb in the search Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.11 Gamma(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DeadLock bug using mysql/Innodb
Description: Hi Heikki, In your case: CREATE TABLE t (a INT NOT NULL, PRIMARY KEY (a)) TYPE = INNODB; INSERT INTO t VALUES (90); INSERT INTO t VALUES (110); User 1: SELECT * FROM t WHERE a = 100 FOR UPDATE; User 2: SELECT * FROM t WHERE a = 100 FOR UPDATE; Now if user 1 tries to do INSERT INTO t VALUES (100), he will receive a deadlock. The algorithm is that a cursor in InnoDB always travels in one direction. User 2 has his cursor waiting on the row (110) for an exclusive row next-key lock. If we allowed user 1 to insert (100), it would be a 'phantom row' in User 2's SELECT: if User 1 committed his transaction, and User 2 would repeat his own SELECT, a new row (100) would have appeared in the result set. In my case, it's a bit different because User 1 would insert row (116), a new last document. I think that User 2 should not lock User 1. User 1 should be able to insert row (100), and it would not be a 'phantom row' in User 2's SELECT because he has not get the rows yet because they are locked by User 1. So, when User 1 does a COMMIT, User 2 would see rows 100 and 110. Also, the manual says: A select ... for update will read the latest available data setting exclusive locks on each row it reads. So User 2 must wait for User 1 Commit/Rollback to get the latest data. And as you said: A way to fix the inoptimality would be to let User 2 reverse his cursor if it ends up waiting for a row lock and there is an insert immediately before that row. But that is a bit complicated. It should be a good solution!!! On the other hand, if I change the isolation level (available from version 4.0.5), to READ COMMITED, all the selects for update will only lock index records not the gaps before them, so I think this can be the solution for User 1 to insert row (100), isn´t it?. So READ COMMITED is the solution for me to insert a new last document without having to check for the dead-lock problem and for locks work fine. Thanks very much for your attention, Rafa. How-To-Repeat: Select Ped from Pedidos where Ped'CA02' and Ped'CA02Z' order by Ped DESC limit 1 for update Fix: - Synopsis:Subject:DeadLock bug using mysql/Innodb Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.4 beta(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DeadLock bug using mysql/Innodb
Description:
Hello,
I have a deadlock problem when I try to execute some update sentences
using 'Lock For Update'.
I need to create a new document which is identified by a unique number,
which is stored in the field 'Ped' of the table 'Pedidos'.
To obtain a new document number I add 1 to the counter of the last document,
and the counter has to begin with a character set formed by 4 characters.
For example, I want to obtain the last document number which begins
by 'CA02'. The steps I must follow are:
- I obtain the last document which begins by 'CA02'. I do the selection of
the last document using 'Select ... For Update':
Select Ped From Pedidos Where Ped'CA02' And Ped'CA02Z'
Order by Ped Desc Limit 1 For Update
Now, I have the last document which begins by 'CA02' and I don´t
allow anyone to access to this record because I set an exclusive lock using
'For Update'.
If other user tries to create a new document with begins with the same charater
set 'CA02', he must execute the same above sentence and this user remains locked.
But when the first user tries to insert the new document with the new number,
it obtains the following error:
Error 1213: Deadlock found When trying to get lock; try restarting transaction
InnoDB executes a rollback sentence for the first user and the second user
is unlocked.
I will explain the sentences executed with the client of mysql 4.0.4:
User 1 User 2
-- --
Begin; Begin;
Select Ped from Pedidos Select Ped from Pedidos
where Ped'CA02' where Ped'CA02'
and Ped'CA02Z' and Ped'CA02Z'
order by Ped desc limit 1order by Ped desc limit 1
for update; for update;
+++CA02000155 ---locked
Insert into Pedidos(ped)
values('CA02000156');
Error 1213 Deadlock found. Unlocked...
It seems that the user 2 locks user 1, but it should not be because user 2 is actually
locked by user 1.
On the other hand, the following sentences work fine:
User 1 User 2
-- --
Begin; Begin;
Select Ped from Pedidos Select Ped from Pedidos
wherewhere
left(Ped,4)='CA02' left(Ped,4)='CA02'
order by Ped descorder by Ped desc
limit 1 for update; limit 1 for update;
+++CA02000155 ---locked
Insert into Pedidos(ped)
values('CA02000156');
Ok.
Commit; ---unlocked
I have sent to ftp://support.mysql.com/pub/mysql/secret the table definition
and data (Pedidos.txt) in a compressed file named Pedidos.zip so you can
reproduce the bug.
Thanks in advance,
Rafa
How-To-Repeat:
Select Ped from Pedidos where Ped'CA02' and Ped'CA02Z' order by
Ped DESC limit 1 for update
Fix:
-
Synopsis:Subject:DeadLock bug using mysql/Innodb
Submitter-Id: submitter ID
Originator: Rafa
Organization: Pecomark
MySQL support: none
Severity: non-critical
Priority: medium
Category: mysqld-max-nt
Class: sw-bug
Release:mysqld 4.0.4 beta(InnoDB)
Exectutable: mysqld-max-nt
Environment: Pentium III-MMX, 500 MHZ, 540 MB
System:Windows 2000
Compiler: -
Architecture: i
__
The NEW Netscape 7.0 browser is now available. Upgrade now!
http://channels.netscape.com/ns/browsers/download.jsp
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Indexing questions
Hello, I have some doubts about the index behaviour in InnoDB. I have a table named 'Albaranes'. The definition is as follow: CREATE TABLE `albaranes` ( `Cli` varchar(6) NOT NULL default '', `Alb` varchar(8) NOT NULL default '', . . . PRIMARY KEY (`Alb`), UNIQUE KEY `Cliente` (`Cli`,`Alb`), ) TYPE=InnoDB; The index 'Cliente' is composed of the field 'Cli' and the field 'Alb' which belong to the primary index. I have read in the InnoDB documentation the following text: The records in non-clustered indexes (we also call them secondary indexes), in InnoDB contain the primary key value for the row. InnoDB uses this primary key value to search for the row from the clustered index. Well, according to the text it wouldn`t be necessary to add the field 'Alb' to the 'Cliente' index to access to a specific record because InnoDB does that. My question is: If I add the field 'Alb' to the 'Cliente' index, does InnoDB add the field 'Alb' too? I have made tests and I have noticed that if I define the index 'Cliente' as (Cli) the time that the query takes in recovering all the table records is more or less the same that the query takes if I define the index 'Cliente' as (Cli,Alb) (there isn't a considerable difference). The query executed is 'Select Cli,Alb From Albaranes where Cli 'AnyValue''. I'm interesting in recovering the records ordered by Cli,Alb for increasing the speed of some reports so, I executed the query 'Select Cli,Alb From Albaranes where Cli 'AnyValue' Order by Cli,Alb' and I checked that if I define the index as (Cli) InnoDB doesn´t use the field 'Alb' (which is used to search for the row from the clustered index by InnoDB) to order the selected records. I suppose InnoDB only uses this primary key value to search for the rows and not to order the rows, doesn´t it? My last question is if I add the fields from the clustered index to other secondary indexes (for example the index 'Cliente'), have I to define the secondary index as 'UNIQUE'? I think that it´s not necessary and the only I could obtain is that inserts are slower because InnoDB has to preserve the data uniqueness. Let me ask a question that is not related to InnoDB. I have three primary fields (varchar) in a table and I have some doubts in the way of defining them. What is better, defining three fields and the primary index is formed by all of them or defining only one field which is composed of the concatenation of the three fields and the index is formed only by one field?. I'm always going to search by the first field. Regards, Lourdes. __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB indexing questions
Hello, I have some doubts about the index behaviour in InnoDB. I have a table named 'Albaranes'. The definition is as follow: CREATE TABLE `albaranes` ( `Cli` varchar(6) NOT NULL default '', `Alb` varchar(8) NOT NULL default '', . . . PRIMARY KEY (`Alb`), UNIQUE KEY `Cliente` (`Cli`,`Alb`), ) TYPE=InnoDB; The index 'Cliente' is composed of the field 'Cli' and the field 'Alb' which belong to the primary index. I have read in the InnoDB documentation the following text: The records in non-clustered indexes (we also call them secondary indexes), in InnoDB contain the primary key value for the row. InnoDB uses this primary key value to search for the row from the clustered index. Well, according to the text it wouldn`t be necessary to add the field 'Alb' to the 'Cliente' index to access to a specific record because InnoDB does that. My question is: If I add the field 'Alb' to the 'Cliente' index, does InnoDB add the field 'Alb' too? I have made tests and I have noticed that if I define the index 'Cliente' as (Cli) the time that the query takes in recovering all the table records is more or less the same that the query takes if I define the index 'Cliente' as (Cli,Alb) (there isn't a considerable difference). The query executed is 'Select Cli,Alb From Albaranes where Cli 'AnyValue''. I'm interesting in recovering the records ordered by Cli,Alb for increasing the speed of some reports so, I executed the query 'Select Cli,Alb From Albaranes where Cli 'AnyValue' Order by Cli,Alb' and I checked that if I define the index as (Cli) InnoDB doesn´t use the field 'Alb' (which is used to search for the row from the clustered index by InnoDB) to order the selected records. I suppose InnoDB only uses this primary key value to search for the rows and not to order the rows, doesn´t it? My last question is if I add the fields from the clustered index to other secondary indexes (for example the index 'Cliente'), have I to define the secondary index as 'UNIQUE'? I think that it´s not necessary and the only I could obtain is that inserts are slower because InnoDB has to preserve the data uniqueness. Let me ask a question that is not related to InnoDB. I have three primary fields (varchar) in a table and I have some doubts in the way of defining them. What is better, defining three fields and the primary index is formed by all of them or defining only one field which is composed of the concatenation of the three fields and the index is formed only by one field?. I'm always going to search by the first field. Regards, Lourdes. __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in selecting fields that don´t belong to the index used by mysql/Innodb
Description: Hello, We reported a problem about selecting fields that some of them didn´t belong to the used index on September. We have already installed MySql 4.0.4 and we have a similar problem (and also we are still having the original problem) when we select fields which some of them don´t belong to the primary index that MySql should use in the search. In this query, we haven´t any OR condition in the WHERE clause as we had in the query that we made on September. Moreover, in this case MySQL doesn´t use any index when runs the query. So, we think this problem is bigger than the previous one. We have sent to ftp://support.mysql.com/pub/mysql/secret the table definition and data (albaranes.txt) in a compressed file named Albaranes.zip so you can reproduce the bug. We hope the test we are going to explain can help you to fix the bug. Now, the table definition is (we have got 45055 records) CREATE TABLE `albaranes` ( `Cli` varchar(6) NOT NULL default '', `Alb` varchar(8) NOT NULL default '', `Fac` varchar(8) NOT NULL default '', `Ped` varchar(8) NOT NULL default '', `Pis` char(2) NOT NULL default '', `Fec` date default NULL, `Dom` char(2) NOT NULL default '', `FoP` char(2) NOT NULL default '', `Ven` char(3) NOT NULL default '', `Rep` char(3) NOT NULL default '', `Ba1` decimal(20,4) NOT NULL default '0.', `Ba2` decimal(20,4) NOT NULL default '0.', `Ba3` decimal(20,4) NOT NULL default '0.', `Ba4` decimal(20,4) NOT NULL default '0.', `Iv1` float NOT NULL default '0', `Iv2` float NOT NULL default '0', `Iv3` float NOT NULL default '0', `Iv4` float NOT NULL default '0', `Re1` float NOT NULL default '0', `Re2` float NOT NULL default '0', `Re3` float NOT NULL default '0', `Re4` float NOT NULL default '0', `Dps` decimal(20,4) NOT NULL default '0.', `Dpj` double NOT NULL default '0', `Por` decimal(20,4) NOT NULL default '0.', `TCV` decimal(20,4) NOT NULL default '0.', `TCR` decimal(20,4) NOT NULL default '0.', `TBe` decimal(20,4) NOT NULL default '0.', `Nts` text, `GAS` decimal(20,4) NOT NULL default '0.', `ENV` varchar(40) NOT NULL default '', `NFV` tinyint(4) unsigned NOT NULL default '0', `NFR` tinyint(4) unsigned NOT NULL default '0', `Usuario` char(2) NOT NULL default '', `Tar` varchar(6) NOT NULL default '', `Umv` date default NULL, `Retenido` tinyint(4) unsigned NOT NULL default '0', `Total` tinyint(4) unsigned NOT NULL default '0', `Tipo_portes` tinyint(4) unsigned NOT NULL default '0', `Agencia` char(2) NOT NULL default '', `Nom_agencia` varchar(255) NOT NULL default '', `Peso` float NOT NULL default '0', `Volumen` float NOT NULL default '0', `Num_expedicion` varchar(20) NOT NULL default '', `Instalacion` varchar(20) NOT NULL default '', `Bultos` smallint(6) NOT NULL default '0', `Doc_manual` varchar(8) NOT NULL default '', `Integrado` tinyint(4) unsigned NOT NULL default '0', `Su_Pedido` varchar(40) NOT NULL default '', `RecalculaPreciosCompra` tinyint(4) unsigned NOT NULL default '0', PRIMARY KEY (`Alb`), UNIQUE KEY `Cliente` (`Cli`,`Alb`), UNIQUE KEY `FecCliAlb` (`Cli`,`Fec`,`Alb`), UNIQUE KEY `PorInstalacion` (`Cli`,`Instalacion`,`Fec`,`Alb`), UNIQUE KEY `PorSuPedido` (`Cli`,`Su_Pedido`,`Fec`,`Alb`), KEY `Facturas` (`Fac`) ) TYPE=InnoDB; And now the tests: INNODB == 1) explain select * from albaranes where alb 'SA' limit 10 table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL, ref=NULL,rows=45240,Extra=NULL. Time: 0.85 secs. MySql must use the PRIMARY index !!! 2) explain select alb from albaranes where alb 'SA' limit 10 table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY, key_len=8,ref=NULL,rows=58698,Extra=where used; Using index. Time:0.05 secs. If we only select the ALB field which belongs to the PRIMARY INDEX, the query runs okey. 3) explain select * from albaranes where alb 'BA' limit 10 table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY, key_len=8,ref=null,rows=41440,Extra=where used. Time:0.10 secs. The records with alb 'BA' are at the beginning of the table. This is the difference between explain 1 and explain 3. 4) explain select * from albaranes where alb 'MA' limit 10 table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL, ref=NULL,rows=45240,Extra=where used. Time: 0.80 secs. MySql must use the PRIMARY index !!! 5) explain select * from albaranes where alb 'SB' limit 10 table=albaranes,type=range,possible_keys=PRIMARY,key=PRIMARY, key_len=8,ref=null,rows=16224,Extra=where used. Time:0.09 secs. The records with alb'SB' are at the end of the table. 6) explain select * from albaranes where alb 'SB' limit 10 table=albaranes, type=ALL, possible_keys=PRIMARY,key=NULL,Key_len=NULL, ref=NULL,rows=44499,Extra=where
optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb
Description: Hello, I wrote the following text in my last message (It was an answer to Monty): Hello, Monty I have already sent to pub/mysql/secret the table definition and data (clientes.txt) and my.ini file in a compressed file named clientes.zip. You can import clientes.txt and test it. I have the problem only with InnoDb tables. I tell you my test with the above table clientes ( InnoDB table I ran the following query: Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50; Time:0.08 secs. Handler_read_next: 1850 Explain: tabletype possible_keys keykey_len ref rows Extra Clientes index PRIMARY,Nombre Nombre 46 NULL 3899 where used I changed the table type to MyIsam (alter table clientes type=MyIsam), and I ran the above query: MyIsam table Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50; Time:0.00 secs. Handler_read_next: 49 Explain: tabletype possible_keys keykey_len ref rows Extra Clientes range PRIMARY,Nombre Nombre 46 NULL 4057 where used Thanks in advance, Rafa and I have received no answer, so, Have you received the file clientes.zip? Have you tested it? Let me know about it. Thanks in advance, Rafa How-To-Repeat: Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50; Fix: - Synopsis:optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.3 beta(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Boolean Data Type in MySQL
MySql doesn´t support boolean data type. Try TinyInt unsigned, with two values: 0 False, 1 True. Regards, Rafa RE: Boolean Data Type in MySQL __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in selecting fields that don´t belong to the index used by mysql
Description: Hello Monty, I have already sent to pub/mysql/secret the table definition and data (clientes.txt) and my.ini file in a compressed file named clientes.zip. You can import clientes.txt and test it. I have only then problem with InnoDb tables. I tell you my test with the above table clientes ( InnoDB table I ran the following query: Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50; Time:0.08 secs. Handler_read_next: 1850 Explain: tabletype possible_keys keykey_len ref rows Extra Clientes index PRIMARY,Nombre Nombre 46 NULL 3899 where used I changed the table type to MyIsam (alter table clientes type=MyIsam), and I ran the above query: MyIsam table Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50; Time:0.00 secs. Handler_read_next: 49 Explain: tabletype possible_keys keykey_len ref rows Extra Clientes range PRIMARY,Nombre Nombre 46 NULL 4057 where used Thanks in advance, Rafa How-To-Repeat: Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50; Fix: - Synopsis:optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.3 beta(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb
Description: Heikki, 1) There is only one record with 'GARCIA MANCILLA,S.L'. 2) Mysql reported 53 scanned rows when I ran your mock-up query on your mock-up table. This is ok. 3) I'm using mysql.exe client (sometimes I use front-end) and i don´t think it removes the LIMIT 50 from my query. 4) I'm using mysqld-max-nt 4.0.3 beta. I have sent to pub/mysql/secret the table clientes (clientes.txt) and the my.ini file in the compressed file named clientes.zip. I tell you my test. a) I ran the following query: Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50; Time:0.08 secs. Handler_read_next: 1850 (You can verify it through the file clientes.txt I sent) b) I changed the table type to MyIsam, and I ran the above query: Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50; Time:0.00 secs. Handler_read_next: 49 So, I have no problems with your mock-up table because (I think) it has not too many fields. Also, in my real table, the query is only slow with Innodb table handler. It's fast with MyIsam table handler. I would like you can guess what the problem is and I thank you for your attention. thanks in advance, Rafa. How-To-Repeat: Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'a2618' And Cod'059336') Or Nom'a2618' Order by Nom ASC, Cod ASC Limit 50; Fix: - Synopsis:optimizer bug in selecting fields that don´t belong to the index used by mysql/innodb Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.3 beta(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
optimizer bug in selecting fields that don´t belong to the index used by mysql
Description: Hello, I am working with mysqld-max-nt under windows 2000 and unsing InnoDb tables. I have the following table: CREATE TABLE clientes ( Cod varchar(6) NOT NULL default '', Nom varchar(40) NOT NULL default '', Nif varchar(10) NOT NULL default '', Nombre_comercial varchar(40) NOT NULL default '', ... ... ... PRIMARY KEY (Cod), UNIQUE KEY Nombre (Nom,Cod) UNIQUE KEY Nombre (Nombre_comercial,Cod) ... ... ) TYPE=InnoDB; I don´t understand why this select: Select Cod,Nom,Nif,Nombre_comercial from Clientes Where (Nom = 'GARCIA MANCILLA,S.L' And Cod'061642') Or Nom'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50; (Mysql uses the unique index: Nombre) is much slower than the following selects in which I use a temporary table: DROP TABLE IF EXISTS TEMPORAL; CREATE TEMPORARY TABLE TEMPORAL type=heap Select Cod,Nom From Clientes Where (Nom = 'GARCIA MANCILLA,S.L' And Cod'061642') Or Nom'GARCIA MANCILLA,S.L' Order by Nom ASC, Cod ASC Limit 50; (I select Cod,Nom wich are fields from the unique index Nombre, so it's very fast). (I select the other fields (nif,nombre_comercial) which don´t belong to the unique index named Nombre). SELECT TEMPORAL.COD,TEMPORAL.NOM,NIF,NOMBRE_COMERCIAL FROM TEMPORAL INNER JOIN CLIENTES ON TEMPORAL.COD=CLIENTES.COD; (Inner join through the field cod, which is the primary index, so it's very fast). Synopsis: optimizer bug in selecting fields that don´t belong to the index used by mysql Submitter-Id: submitter ID Originator: Rafa Organization: Pecomark MySQL support: none Severity: non-critical Priority: medium Category: mysqld-max-nt Class: sw-bug Release:mysqld 4.0.3 beta(InnoDB) Exectutable: mysqld-max-nt Environment: Pentium III-MMX, 500 MHZ, 540 MB System:Windows 2000 Compiler: - Architecture: i __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySql/InnoDB crashes after a Select...order by DESC... LIMIT...
Hello, I am working with mysqld-max-nt under windows 2000 and unsing InnoDb tables. After running the following select: Select cod,nom from grupos where cod '005' order by cod desc limit 1 mysqld crashes. Also it crashes whith limit 10, limit 50, or whatever limit you indicate. However, the following selects run ok: Select cod,nom from grupos where cod '005' order by cod desc (without limit) Select * from grupos where cod ’D01’ order by cod desc limit 1 Select * from grupos where strcmp(cod,’005')0 order by cod desc limit 1 Select * from grupos where cod 005 order by cod desc limit 1 Select * from grupos where cod 5 order by cod desc limit 1 This error only occurs with InnoDb tables and MySql 4.0.3 (with Mysql 3.23.52 it runs ok). With MyIsam tables it runs ok even under mysql 4.0.3 I enclose the file grupos.sql with the table definition and data. Thanks in advance, Rafa. P.D: Sorry for my english. __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
