MySQL 5.0 : error using max(idrow) on a null value
Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Bye, Stefano.
Re: MySQL 5.0 : error using max(idrow) on a null value
On Friday 04 November 2005 07:30 am, AESYS S.p.A. [Enzo Arlati] wrote: Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff pgpApFheY1YGC.pgp Description: PGP signature
R: MySQL 5.0 : error using max(idrow) on a null value
For a while my application should support both mysql 4 and 5 ( teh same copy on different sites of course ) so I should keep using a soluting wich should works well on both revision. I also have a couple of server with their database configured as master slave, so I don't trust to use autoincrement. When I can leave ther revision 4 at all I think to use the autoincrement using a trigger and mybe something like the oracle sequence . regards, Enzo Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -Messaggio originale- Da: Jeff Smelser [mailto:[EMAIL PROTECTED] Inviato: venerdì 4 novembre 2005 16.42 A: mysql@lists.mysql.com Oggetto: Re: MySQL 5.0 : error using max(idrow) on a null value On Friday 04 November 2005 07:30 am, AESYS S.p.A. [Enzo Arlati] wrote: Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: MySQL 5.0 : error using max(idrow) on a null value
select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; Does changing max(c.idrow)+1 to coalesce(max(c.idrow),0)+1 solve your problem? --Pete On Fri, Nov 04, 2005 at 04:56:26PM +0100, AESYS S.p.A. [Enzo Arlati] wrote: For a while my application should support both mysql 4 and 5 ( teh same copy on different sites of course ) so I should keep using a soluting wich should works well on both revision. I also have a couple of server with their database configured as master slave, so I don't trust to use autoincrement. When I can leave ther revision 4 at all I think to use the autoincrement using a trigger and mybe something like the oracle sequence . regards, Enzo Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -Messaggio originale- Da: Jeff Smelser [mailto:[EMAIL PROTECTED] Inviato: venerd? 4 novembre 2005 16.42 A: mysql@lists.mysql.com Oggetto: Re: MySQL 5.0 : error using max(idrow) on a null value On Friday 04 November 2005 07:30 am, AESYS S.p.A. [Enzo Arlati] wrote: Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -- 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]