wrong value in mysql_insert_id()? 1

[DaveC426913]

My INSERT operation is returning a value that is different than the highest auto_incr value. Has it slipped up somewhere? Each time I run the operation, the mysql_insert_id() does increment, but the value is way down in the 1500's, even though the latest records created are in the 1700s.

My code:

$query1 = "INSERT INTO events (event_code,client_dbase,event_id,modified,beta_date,event_date,setup_date,
practice_date,derig_date,expiry_date,zone_type,event_type,audience_type,
viewers,quote_id,invoice_id,sales,pm,est_value,notes,ehi_status)
VALUES (NULL,'$clientDB','$eventID',NOW(),'$betaDate','$eventDate','$setupDate',
'$practDate','$derigDate','$expirDate','$zoneType','$eventType','$audncType',
'$viewers','$quoteID','$invoiceID','$sales','$pm','$value','$notes','100')";

$result1 = $Data->PostData($mainDB, $query1);


if ($result1){
$lastID = mysql_insert_id();
$reverse[$mainDB] = "DELETE FROM events WHERE event_code='$lastID'";
}

The above record (note the 'foo!') was just placed in the db as event_code 1710. However, the value I get back from mysql_insert_id(); is 1563. If I run this again, I'll get 1711 and 1564.


I know there is a value you can set that tells it where to start auto-incrementing from, is this the problem? While I do know how to set it in phpMyadmin, my avaiable choices are Webmin or the CommandLine, which I don't know how to do.



The database and the app were copied from another system by my sysAdmin, is that likely what caused the discrepancy?

 

[jpadie]

can you post a dump of your table schema and status

$sql[] = "show fields from events";
$sql[]= "show table status like 'events'";
foreach ($sql as $qry):
 $result = mysql_query($qry);
 echo "<pre>";
 while ($row=mysql_fetch_assoc($result)):
  echo "<br/>";
  print_r($row);
 endwhile;
 echo "<hr/></pre>";
endforeach;

 

[DaveC426913]

Did it directly, rather than through php.

(Sorry. TT's message formatting features suck

Field	Type	Null	Key	Default	Extra
event_code	int(10) unsigned		PRI		auto_increment
client_dbase	varchar(32)		MUL		
event_id	varchar(16)		MUL		
modified	timestamp(14)	YES			
beta_date	date	YES			
event_date	datetime	YES	MUL		
setup_date	datetime	YES			
practice_date	datetime	YES			
derig_date	datetime	YES			
expiry_date	date	YES			
zone_type	tinyint(2)			0	
event_type	tinyint(2)		MUL	0	
audience_type	tinyint(2)			0	
viewers	varchar(32)	YES			
quote_id	varchar(16)				
invoice_id	varchar(16)				
sales	smallint(5)		MUL	0	
pm	smallint(5)		MUL	0	
est_value	varchar(16)			0	
notes	text	YES			
ehi_status	tinyint(2)		MUL	0	


Name	Type	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Create_options	Comment
events	MyISAM	Dynamic	1452	85	123540	4294967295	141312	88	1711	2002-11-04 16:16:26	2006-03-31 16:36:40

 

[DaveC426913]

Name Type Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Create_options Comment
events MyISAM Dynamic 1452 85 123540 4294967295 141312 88 1711 2002-11-04 16:16:26 2006-03-31 16:36:40

 

[DaveC426913]

Name	Type	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time		Update_time		Check_time	Create_options	Comment
events	MyISAM	Dynamic	        1452	85	        123540          4294967295	141312	        88	        1711	        2002-11-04 16:16:26	2006-03-31 16:36:40

 

[jpadie]

Hi Dave

can you try the following code snip instead?

$query1  = "INSERT INTO events (client_dbase,event_id,modified,beta_date,event_date,setup_date,
                                    practice_date,derig_date,expiry_date,zone_type,event_type,audience_type,
                                    viewers,quote_id,invoice_id,sales,pm,est_value,notes,ehi_status)
                VALUES ('$clientDB','$eventID',NOW(),'$betaDate','$eventDate','$setupDate',
                        '$practDate','$derigDate','$expirDate','$zoneType','$eventType','$audncType',
                        '$viewers','$quoteID','$invoiceID','$sales','$pm','$value','$notes','100')";
    
    $result1 = $Data->PostData($mainDB, $query1);


    if ($result1){
        $lastID = mysql_insert_id();
        $reverse[$mainDB] = "DELETE FROM events WHERE event_code='$lastID'";
    }

also can you let me know what abstraction layer you are using for the $Data->PostData call? some abstraction layers use persistent connections which (i recall but may be mistaken) sometimes cause problems.

I believe the issue with mysql_insert_id is that you are setting a value for it (NULL). again my (dim) recollection is that this screws the lastid function up and instead returns number of rows. normal practice is not to assign a value to the auto-incrementing column.

Another potential issue is that you are declaring the column type as int. there is verbage in the php manual about BIGINT not working with mysql_insert_id: i haven't investigated this myself!

remember you can always get the answer from the horse's mouth.

echo "last insert id is :" . mysql_result(mysql_query ("SELECT LAST_INSERT_ID()"),0,0);

 

[DaveC426913]

can you try the following code snip instead?"

Same thing. Returned '1564'.

" can you let me know what abstraction layer you are using for the $Data->PostData call? "

Uh, not without help.

"Another potential issue is that you are declaring the column type as int. there is verbage in the php manual about BIGINT not working with mysql_insert_id"

Jeez, I'd hate to make a change of that scale... But I am merely experimenting, so if it blows up, no loss.

"remember you can always get the answer from the horse's mouth."

last insert id is :1566

 

[jpadie]

can we remove the abstraction layer from the equation.

replace the offending line with

 $result1 = mysql_query($query1) or die (mysql_error());

 

[DaveC426913]

You mean replace
$result1 = $Data->PostData($mainDB, $query1);
with
$result1 = mysql_query($query1) or die (mysql_error());
?
How will it know what db to look in then?


Anyay, it doesn't know what to do with that: "Unknown column 'client_dbase' in 'field list'"


I checked what mainDB contains: echo mainDB; returns _emu_, the name of the correct database. (Though I don't know how your statement could know that.)

 

[jpadie]

you need to tell it which database to connect to with
mysql_connect(hostname, username, password)
and
mysql_select_db(databasename)

if the above query is producing an error you should be worried. it was copied from your post. it may well be that the database abstraction layer is hiding things from you that you need to know about.

go back to basics and use normal mysql code to get rid of the problems. and don't use an abstraction layer (or any code that you have not written) until you understand it thoroughly (otherwise debugging is nigh impossible and there are serious security ramifications!)

 

[DaveC426913]

you need to tell it which database to connect to with
mysql_connect(hostname, username, password)
and
mysql_select_db(databasename)"

OK, that worked, and has correctly inserted the record.

So, I guess it's that abstraction layer?



(P.S. I really appreciate the hand-holding.)

 

[jpadie]

yes. abstraction layer it is.

could be a number of things within the abstraction layer that are causing the issue. not least how it connects to the db. if you can tell us which one abstraction layer you are using that would be a good start.

you can usually tell by having a look at the files are "required" or "included" at the beginning of scripts.

 

[DaveC426913]

Here is the code traced back to the db:

$Data = new Data($clientDB,$eventID,'db');

$result1 = $Data->PostData($mainDB, $query1);



class Data 
{
    // constructor
    function Data ($clientDB,$eventID,$src = "db")
    {
        $this->clientDB = $clientDB; // the client db name, also directory, etc.
        $this->eventID  = $eventID;
        $this->source   = $src;
    }

    function PostData ($db,$query)
    {
        $result = $this->_dbQuery($db,$query);
        if ($result)
            $liveUpdate = $this->_livedbQuery($db,$query);
        // ToDo: need to process SQL result so not to return a SQL object
        if ($liveUpdate)
            return $result;
        else
            return false;
        
    }


    function _livedbQuery ($db,$query)
    {
        $dbHost = "<server>";
        $dbUser = "<u>";
        $dbPass = "<p>";
        mysql_connect($dbHost,$dbUser,$dbPass) or die("connection error4 ... [" . mysql_error()."]");
        mysql_select_db($db) or die("db select error for \"".$db."\"... " . mysql_error());
        $result = mysql_query($query) or die("error occurred within Data class ... ".mysql_error()." running query: " . $query);
    }

}

 

[DaveC426913]

OK, in looking at the class.data.inc code, I've noticed a discrepancy:

The config.php file points to 'localhost', yet the class.data.inc file points to 'staging.ob.com'. I beleive they should be the same, as does my network admin guy.

So, I set class.data to 'localhost' and now I'm getting very different behaviour. It's not fixed: it behaves better but still gives me the wrong event_id. Now I don't get errors, but I do retrieve the wrong event (it is now correctly retrieving the event at id 1560-ish, rather than 1710-ish).

I'll look into it further and get back to you when I get stumped again.

Thanks jpadie!

 

[DaveC426913]

Ok, I'm back on the project...

So, it is not throwing errors anymore, it is successfully (but incorrectly) getting the record at event_code 1572, despite the fact that the the latest event_code is 1723, and the fast that the auto increment is also set to 1723.

I don't understand the correlation between these two numbers. Or at least, I don't understand the _discrepancy_.

So, the simple question is: why would a SELECT LAST_INSERT_ID() query return a number that is nowhere near the auto_increment of the table?

Help!

 

[jpadie]

i share your confusion. i would post this into the mysql forum as they are more likely to be able to assist.

 

[sleipnir214]

I disagree.

As I stated in thread434-1217815 , I have never seen MySQL return a wrong value. I have, however, seen PHP code mess up the value.

Again, as I stated in thread434-1217815 , I have had problems with object-oriented code that used mysql_* functions unless the database handles, resource handles, etc were explicitly stored in variables and passed around as parameters to the functions.

Again, as I stated in thread434-1217815 , opening a connection every time a query is made is a very bad design. PHP is supposed to return an existing database handle if mysql_connect() is called a second time in the same script with the same parameters, but this may not be so -- and since last insert IDs are tracked by the connection, perhaps this repeated connection-opening is throwing MySQL off. Editing the class so that it opens one connection when the object is constructed and the reusing that connection handle is something to try. Considering that opening a handle takes time, it's also a performance issue.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[jpadie]

but the OP is now talking about the MYSQL value and not the php value:

why would a SELECT LAST_INSERT_ID() query

surely you are not saying that PHP can dynamically alter a sql query and/or its results in this way of its own "volition"?

 

[sleipnir214]

jpadie:
I do not know.

I do know that when the last insert ID is stored per-connection and someone's code keeps perpetually reopening connections, perhaps rewriting a test version of the function so that the connection to the database is opened once is an avenue of investigation.

Also, I do know from personal experience that when I've included mysql_* functions inside class code, when I did not explicitly store database- and resource-handles in varables and explicitly pass the handles to the functions (you know, all those optional parameters so many people ignore), I got squirrely behavior. And that perhaps rewriting the class code so that all the mysql_* functions are invoked with all parameters would be another avenue of investigation.



Want the best answers? Ask the best questions! TANSTAAFL!