Bug in UNION?
Hello, Try this: mysql CREATE TABLE a ( id int NOT NULL PRIMARY KEY, des varchar(20) default NULL ); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO a VALUES (1,'pepe'),(2,'juan'),(3,'mariano'); Query OK, 3 rows affected (0.00 sec) Registros: 3 Duplicados: 0 Peligros: 0 mysql select NULL as id, des - from a - union - select id, des - from a; +--+-+ | id | des | +--+-+ | NULL | pepe| | NULL | juan| | NULL | mariano | | | pepe| | | juan| | | mariano | +--+-+ 6 rows in set (0.00 sec) Is this OK? I think that no. Then try this: mysql select id, des - from a - union - select NULL as id, des - from a; ++-+ | id | des | ++-+ | 1 | pepe| | 2 | juan| | 3 | mariano | | 0 | mariano | ++-+ 4 rows in set (0.00 sec) Is this OK? Again, I think that no. Thanks, José Ceferino Ortega - 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: Foreign Keys and InnoDb in MySQL
Hello, In Case 1, you need to change: index i_Tb (customerId, carId) = index i_Tb (carId, customerId) or add index i_carId (carId) In Case 2, you need : foreign key (customerId) references customerTb (customerId) = foreign key (customerId) references customerTb (customerId) on delete cascade. José Ceferino Ortega -Mensaje original- De: Bruce Lee [mailto:itnobita;hotmail.com] Enviado el: viernes, 15 de noviembre de 2002 4:35 Para: [EMAIL PROTECTED] Asunto: Foreign Keys and InnoDb in MySQL Hello, I have a problem about foreign key in using MySQL.com manual. According to the example in MySQL.com manual, one foreign key is ok. However, I have 3 tables (ie. 2 for parent tables and 1 for child table). When I use 2 or more foreign keys, I have the following errors. Case 1: mysql create table customerTb ( customerId int not null, primary key (customerId) ) type = innodb; mysql insert into customerTb values (1), (2), (3), (4), (5); mysql create table carTb ( carId int not null, primary key (carId) ) type = innodb; mysql insert into carTb values (1), (2), (3), (4), (5); mysql create table buyTb ( customerId int not null, carId int not null, primary key (customerId, carId), index i_Tb (customerId, carId), foreign key (customerId) references customerTb (customerId), foreign key (carId) references carTb (carId) on delete cascade ) type = innodb; ERROR 1005 at line 15: Can't create table '.\ibdata\buytb.frm' (errno: 150) Case 2: mysql create table customerTb ( customerId int not null, primary key (customerId) ) type = innodb; mysql insert into customerTb values (1), (2), (3), (4), (5); mysql create table carTb ( carId int not null, primary key (carId) ) type = innodb; mysql insert into carTb values (1), (2), (3), (4), (5); mysql create table buyTb ( customerId int not null, carId int not null, primary key (customerId, carId), index i_customerTb (customerId), index i_carTb (carId), foreign key (customerId) references customerTb (customerId), foreign key (carId) references carTb (carId) on delete cascade ) type = innodb; mysql insert into buyTb values (1, 1), (1, 2), (1, 3), (2, 3), (4, 3), mysql (4, 4), (4, 5), (5, 5), (5, 1); mysql delete from customerTb where customerId = 1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails I have thought it for more than three nights, but I still have no solution for this. Can the manual state whether Innodb can support more then one foreign key or not? It makes me so confused. Thanks a lot! Nobita _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail - 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 - 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
Is this a bug in TIMESTAMP?
Hi everybody! I have found following 'bug' in type TIMESTAMP: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1279 to server version: 3.23.48-Max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql Create a table with 2 TIMESTAMP fields: CREATE TABLE `prueba` ( `id` int(10) unsigned NOT NULL auto_increment, `fecha1` timestamp(14) NOT NULL, `fecha2` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ); Insert some data: mysql INSERT INTO prueba VALUES (NULL,20020313100138,20020313100340); mysql INSERT INTO prueba VALUES (NULL,20020313094114,20020312094031); mysql select * from prueba; ++++ | id | fecha1 | fecha2 | ++++ | 1 | 20020313100138 | 20020313100340 | ++++ And then: mysql UPDATE prueba SET fecha1= '20020313101000' WHERE id=1; mysql select * from prueba; ++++ | id | fecha1 | fecha2 | ++++ | 1 | 20020313101000 | 20020313100340 | ++++ It's OK. But if: mysql UPDATE prueba SET fecha2= '20020313101000' WHERE id=1; mysql select * from prueba; ++++ | id | fecha1 | fecha2 | ++++ | 1 | 20020313101501 | 20020313101000 | ++++ mysql select now(); +-+ | now() | +-+ | 2002-03-13 10:15:23 | +-+ Field 'fecha1' has changed to 'now()'. Is this a bug? Thanks, José Ceferino Ortega - 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: Is The MySQL Mailing List On Crack?
Yes, I'm getting duplicate messages too. -Mensaje original- De: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Enviado el: miércoles, 13 de febrero de 2002 2:15 Para: [EMAIL PROTECTED] Asunto: Is The MySQL Mailing List On Crack? I'm getting duplicate messages separated by several hours. Am I alone in this, or are others seeing it too? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 5 days, processed 189,890,555 queries (402/sec. avg) - 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 - 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: What is wrong?
If you supress aggregate funtions and group by clause then: select *, (case a.tipo when 'F' then case a2.tipo when 'F' then a2.valor when 'M' then a2.valor/2 else 0 end when 'M' then a.valor/2 else null end) as valorCalculado from a left join b on (a.id=b.idA) left join a a2 on (b.idB=a2.id); And the result is: ++--+---+--+--+--+--+--+---+ + | id | tipo | valor | idA | idB | cantidad | id | tipo | valor | valorCalculado | ++--+---+--+--+--+--+--+---+ + | 1 | F| 10.3 | 1| 2|1 | 2| M| 12.8 | 6.4 | | 1 | F| 10.3 | 1| 3|2 | 3| C| 0 | 0 | | 2 | M| 12.8 | NULL | NULL | NULL | NULL | NULL | NULL | 6.4 | | 3 | C| 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 4 | C| 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 5 | M| 11.2 | 5| 4|1 | 4| C| 0 | 5.6 | | 6 | F| 12.3 | NULL | NULL | NULL | NULL | NULL | NULL | 0 | ++--+---+--+--+--+--+--+---+ + 7 rows in set (0.01 sec) And with Group By clause: select *, (case a.tipo when 'F' then case a2.tipo when 'F' then a2.valor when 'M' then a2.valor/2 else 0 end when 'M' then a.valor/2 else null end) as valorCalculado from a left join b on (a.id=b.idA) left join a a2 on (b.idB=a2.id) group by a.id; The result is: ++--+---+--+--+--+--+--+---+ + | id | tipo | valor | idA | idB | cantidad | id | tipo | valor | valorCalculado | ++--+---+--+--+--+--+--+---+ + | 1 | F| 10.3 | 1| 2|1 | 2| M| 12.8 | 6.4 | | 2 | M| 12.8 | NULL | NULL | NULL | NULL | NULL | NULL | 6.4 | | 3 | C| 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 4 | C| 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 5 | M| 11.2 | 5| 4|1 | 4| C| 0 | 5.6 | | 6 | F| 12.3 | NULL | NULL | NULL | NULL | NULL | NULL | 0 | ++--+---+--+--+--+--+--+---+ + It isn't wrong. But with SUM function... Jose Ceferino Ortega -Mensaje original- De: Fochtman, Michael [mailto:[EMAIL PROTECTED]] Enviado el: viernes, 01 de febrero de 2002 14:19 Para: 'J. Ceferino Ortega'; [EMAIL PROTECTED] Asunto: RE: What is wrong? CREATE TABLE `a` ( `id` varchar(12) NOT NULL default '', `tipo` char(1) default NULL, `valor` double default NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; CREATE TABLE `b` ( `idA` varchar(12) NOT NULL default '', `idB` varchar(12) NOT NULL default '', `cantidad` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`idA`,`idB`) ) TYPE=InnoDB; And these datas: INSERT INTO `a` VALUES(1,F,10.3),(2,M,12.8),(3,C,0),(4,C ,0),(5 ,M,11.2),(6,F,12.3); INSERT INTO `b` VALUES(1,2,1),(1,3,2),(5,4,1); When I do this query: select a.id, a.tipo, (case a.tipo when 'F' then sum( case a2.tipo when 'F' then a2.valor when 'M' then a2.valor/2 else 0 end ) when 'M' then a.valor/2 else null end) as valorCalculado from a left join b on (a.id=b.idA) left join a a2 on (b.idB=a2.id) group by a.id; Result is: ++--++ | id | tipo | valorCalculado | ++--++ | 1 | F|6.4 | | 2 | M| 0 | | 3 | C| 0 | | 4 | C| 0 | | 5 | M| 0 | | 6 | F| 0 | ++--++ Rows 2 and 5 are wrong. Why? Well, I think 5 is right. When a.id=5, the first 'left join' matches the last record in the 'b' table. Now b.idB=4. This matches the forth record in a2. a2.tipo='C' and your case returns 0 when a2.tipo is not 'M' or 'F'. As for row 2, I'm not so sure. No record in the second table has b.idA value of 2 so the 'left join b on (a.id=b.idA)' doesn't match any records. But your case on a.tipo should fall to the 'when 'M' then a.valor/2' expression and be able to return 6.4. The fact that it doesn't may mean some of the other rows are exhibiting the same behavior, but you can't tell because the value of 'valor' in rows 3 and 4 are 0 anyway. Anyone have an idea why row 2 is not returning 6.4??? Mike Fochtman - 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
where is mysqldumpslow?
From the manual (section 4.9.5 'The Slow Query Log'): The slow query log can be used to find queries that take a long time to execute and are thus candidates for optimisation. With a large log, that can become a difficult task. You can pipe the slow query log through the mysqldumpslow command to get a summary of the queries which appear in the log. Where is mysqldumpslow? Thanks, José Ceferino Ortega - 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: Bug in UNIQUE?
NULL is always not equal NULL in SQL, and if this UNIQUE index isn't NOT NULL, then this behavior is right. -Mensaje original- De: Dan Nelson [mailto:[EMAIL PROTECTED]] Enviado el: viernes, 01 de febrero de 2002 6:00 Para: Carsten Gehling CC: Jude Insley; [EMAIL PROTECTED]; Paul DuBois Asunto: Re: Bug in UNIQUE? In the last episode (Feb 01), Carsten Gehling said: The SQL below illustrates what I believe is a bug in MySQL up to and including 3.23.47. Essentially I need a unique key where one or more of the component fields of the unique key can be NULL. What seems to happen is that you can add duplicate rows if the value is NULL. This is how UNIQUE indexes work in MySQL. All values except NULL must be unique. If you use a PRIMARY KEY instead, this won't happen. Of course, the reason it won't happen is that PRIMARY KEY disallows NULL entirely. I thought that UNIQUE indexes constituted what is known in the relational model as a candidate key and, AFAIR, none of the members in a candidate key may contain NULL values. a UNIQUE index must also have the NOT NULL attribute to be a candidate key. Otherwise it's just an index. -- Dan Nelson [EMAIL PROTECTED] - 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 - 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
What is wrong?
I have 2 tables: CREATE TABLE `a` ( `id` varchar(12) NOT NULL default '', `tipo` char(1) default NULL, `valor` double default NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; CREATE TABLE `b` ( `idA` varchar(12) NOT NULL default '', `idB` varchar(12) NOT NULL default '', `cantidad` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`idA`,`idB`) ) TYPE=InnoDB; And these datas: INSERT INTO `a` VALUES(1,F,10.3),(2,M,12.8),(3,C,0),(4,C,0),(5 ,M,11.2),(6,F,12.3); INSERT INTO `b` VALUES(1,2,1),(1,3,2),(5,4,1); When I do this query: select a.id, a.tipo, (case a.tipo when 'F' then sum( case a2.tipo when 'F' then a2.valor when 'M' then a2.valor/2 else 0 end ) when 'M' then a.valor/2 else null end) as valorCalculado from a left join b on (a.id=b.idA) left join a a2 on (b.idB=a2.id) group by a.id; Result is: ++--++ | id | tipo | valorCalculado | ++--++ | 1 | F|6.4 | | 2 | M| 0 | | 3 | C| 0 | | 4 | C| 0 | | 5 | M| 0 | | 6 | F| 0 | ++--++ Rows 2 and 5 are wrong. Why? Thanks, José Ceferino Ortega - 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: UNIQUE in InnoDB
Hi Heikki, I have proven this in MySQL 3.23.47 and I have found a problem with CHECK TABLE, that reports error - corrupted table if there are several NULL's in a UNIQUE index. Happy end of year to all!, José Ceferino Ortega -Mensaje original- Hi! I will change InnoDB so that it allows several NULL values in secondary indexes even if that index would have been declared UNIQUE. Oracle has a similar convention. In the case of foreign keys, I advise against using NULL values, because in SQL NULL really means 'an unknown value'. Actually, since NULL means an 'unknown value' it would be logical that the SQL standard would ban the use of NULLs in any UNIQUE index. Currently it has only been banned in a primary key. Regards, Heikki Innobase Oy --- See http://www.innodb.com for the latest news about InnoDB Order commercial MySQL/InnoDB support at https://order.mysql.com/ I have found a problem with UNIQUE in InnoDB. Example: mysql CREATE TABLE b ( - idB int(11) NOT NULL auto increment, - valor int(11) default NULL, - PRIMARY KEY (idB), - UNIQUE KEY valor (valor) - ) TYPE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO b (idB, valor) VALUES (1,1),(2,2),(3,NULL),(4,NULL); Query OK, 4 rows affected (0.01 sec) Registros: 4 Duplicados: 0 Peligros: 0 mysql select * from b; +-+---+ | idB | valor | +-+---+ | 1 | 1 | | 2 | 2 | | 3 | NULL | | 4 | NULL | +-+---+ 4 rows in set (0.00 sec) mysql alter table b type=InnoDB; ERROR 1062: Entrada duplicada 'NULL' para la clave 2 Why? I know that NULL != NULL, then behaviour in MyISAM is correct but in InnoDB in not. With foreign key constraints, I have found the same problem: I can't insert a NULL in a column that references other. Thanks, José Ceferino Ortega --- - - 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 - 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
UNIQUE in InnoDB
I have found a problem with UNIQUE in InnoDB. Example: mysql CREATE TABLE b ( - idB int(11) NOT NULL auto_increment, - valor int(11) default NULL, - PRIMARY KEY (idB), - UNIQUE KEY valor (valor) - ) TYPE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO b (idB, valor) VALUES (1,1),(2,2),(3,NULL),(4,NULL); Query OK, 4 rows affected (0.01 sec) Registros: 4 Duplicados: 0 Peligros: 0 mysql select * from b; +-+---+ | idB | valor | +-+---+ | 1 | 1 | | 2 | 2 | | 3 | NULL | | 4 | NULL | +-+---+ 4 rows in set (0.00 sec) mysql alter table b type=InnoDB; ERROR 1062: Entrada duplicada 'NULL' para la clave 2 Why? I know that NULL != NULL, then behaviour in MyISAM is correct but in InnoDB in not. With foreign key constraints, I have found the same problem: I can't insert a NULL in a column that references other. Thanks, José Ceferino Ortega - 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: redaht 7.1 / AMD K6-2: 4.0 rpm error (illegal instruction)
I have the same problem with RedHat 7.1 (fully updated) and a Pentium. Any answer/solution? -Mensaje original- De: Jean-Luc Fontaine [mailto:[EMAIL PROTECTED]] Enviado el: miercoles, 17 de octubre de 2001 9:33 Para: [EMAIL PROTECTED] Asunto: redaht 7.1 / AMD K6-2: 4.0 rpm error (illegal instruction) # rpm -i MySQL-4.0.0-2.i386.rpm Installing all prepared tables /usr/bin/mysql_install_db: line 1: 1171 Illegal instruction (core dumped) /usr/sbin/mysqld --bootstrap --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --skip-innodb --skip-bdb Installation of grant tables failed! Examine the logs in /var/lib/mysql for more information. You can also try to start the mysqld daemon with: /usr/sbin/mysqld --skip-grant You can use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/bin/mysqlbug script! Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 278: 1197 Illegal instruction (core dumped) $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 011016 23:09:08 mysqld ended -- Jean-Luc Fontaine - 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
