[RESOLVED] can auto-increment fail on insert ?

[steamPunk]

Hi

If I have a row that inserts completely normally, is it possible for the auto-increment id to fail for some reason ? So that the row is inserted but the auto-increment is left blank ?

this just happens occasionally, it's not systematic

thanks

[dagon]

I have never seen it fail, post the code your using to insert, and the table structure

[steamPunk]

here's the table structure

CREATE TABLE IF NOT EXISTS `comm_en` (
  `CT_Client` varchar(17) NOT NULL,
  `ID_Client_SITE` int(11) NOT NULL,
  `DATE_COMMANDE` date NOT NULL,
  `DATE_LIVRAISON` date NOT NULL,
  `POINT_LIVRAISON` varchar(50) NOT NULL,
  `MESSAGE_CLIENT` varchar(600) NOT NULL,
  `DO_Piece` varchar(9) NOT NULL,
  `ID_Commande_Site` int(11) NOT NULL auto_increment,
  `PAYE` tinyint(4) NOT NULL default '0',
  `comm_temp` varchar(16) NOT NULL,
  `TIMESTAMP` bigint(20) default NULL,
  `TIMESTAMP_CONF` datetime NOT NULL,
  `comm_arch` tinyint(4) NOT NULL default '0',
  `comm_SAGE` text NOT NULL,
  KEY `ID_Commande_Site` (`ID_Commande_Site`),
  KEY `ID_Client_SITE` (`ID_Client_SITE`),
  KEY `CT_Client` (`CT_Client`),
  KEY `DATE_LIVRAISON` (`DATE_LIVRAISON`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=87774 ;

and here's the insert code

		if(!isset($_SESSION['currentOrder'])||($_SESSION['currentOrder']=="")){
			$sql="INSERT INTO comm_en (
			CT_Client,
			ID_Client_SITE,
			DATE_COMMANDE,
			DATE_LIVRAISON,
			POINT_LIVRAISON,
			MESSAGE_CLIENT,
			PAYE,
			DO_Piece,
			ID_Commande_Site,
			comm_temp,
			TIMESTAMP		
			) VALUES (
			'$CT_Num',
			'".$_SESSION['client']["ID_Client_SITE"]."',
			'$DATE_COMMANDE',
			'$DATE_LIVRAISON',
			'$POINT_LIVRAISON',
			'$MESSAGE_CLIENT',
			'$PAYE',
			'$DO_Piece',
			'',
			'".$comm_temp."',
			'".time()."'			
			)";		

			$m = mysql_query($sql);				
			$ID_Commande_Site = mysql_insert_id();

what's strange is that it only happens occasionally ...

[steamPunk]

if it really never fails then maybe something is deleting the ID_Commande_Site field after the order has been inserted - but I need to be sure that the auto-increment can never fail

[steamPunk]

In fact I'm almost 100% certain it's not the auto-increment that's failing

thanks anyway 🙂

[johanafm]

You have your auto_increment on an int field that does not allow null. So actually getting a null value for that field seems impossible.
But why not completely remove that field from the insert query?

[steamPunk]

yes, good point !