Re: [PHP-DB] Transaction suddenly not working
--- Stuart Felenstein [EMAIL PROTECTED] wrote:
I know recently the server was update from PHP 4.3.8
to 4.3.9 but that shouldn't have effected anything.
Code:
$query = INSERT INTO table1 (ProfileID, LurkID,
ProfileName, Edu,...
VALUES (null,...
ProfileID is the PrimaryID (autoinc) - I've set the
input value to null for the auto-inc to properly
work.
I assume this is correct and it did work at one
time.
Then before the next query:
$LID = mysql_insert_id();
Then each subsequent query uses $LID in the
subsequent
tables.
However $LID is now returning 0 and the transaction
fails.
At the end of the transaction I follow through with
this:
//resx = all the queries
if($res1 $res2 $res3 $res4 $res5
$res6
res7 ) // If all results are true
{
commit();
//echo your insertions were successful;
unset($all session variable, $LID, );
}else{
...
Puzzling since this script was working great. We did
also upgrad MySQL from 4.0.21 to 4.0.22-standard
I wanted to add - I realize that if there is an error
in the first query (where the id is generated) it
would result in a value of 0
I've echoed out the query and am seeing this:
Duplicate entry '0-i' for key 1 transaction failed
Never seen a 0-i before.
Stuart
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transaction suddenly not working
--- denys [EMAIL PROTECTED] wrote: hi! what if you try to ignore the primary key in fields and values list ? MySQL should assign a valid primary key. It's what I'm doing on 4.0.22 and it's working If not, try to echo your query and write it in MySQL( GUI or console). Tried ignoring , first removing the null and ProfileID column name - result still failure. Now the printout of the query is this: 0: INSERT INTO Table1 (LurkID, ProfileName, Edu, WorkAuth, WorkExp, CarLev, Secu, Confi, Relo, Telecomu, City1, State1, City2, State2, TravelPref, SalaryAnnual, SalaryHourly, Available) VALUES (47, '', 7, 2, 1015, 5, , '', '', '', 'fds', 5, '', , 4, 26, , 2004-02-02)01062 : Duplicate entry '0-f' for key 1 transaction failed When I tried the Insert statement into a mysql gui it spit back a syntax error , apparently for the blank values ..,'', yet those are set to allow null. I went ahead and tried just an insert on the first 4 columns , that worked. Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transaction suddenly not working
--- Stuart Felenstein [EMAIL PROTECTED] wrote: One last thing to add on this issue. This table was created using tabletype InnoDB. Now I discovered earlier that this table and (many others) had switched over to MyISAM. I'm still trying to trace down the cause with my ISP. Anyway, using a mysql gui, i put an index, today on the primaryID. Now Im looking at the database in PHP MYADMIN and under indexes it's showing this : Indexes : Keyname TypeCardinality Field PRIMARY PRIMARY 2 ProfileID ProfileID_2 UNIQUE 2 ProfileID ProfileID_4 UNIQUE 2 ProfileID PID INDEX 2 ProfileID ProfileID INDEX 2 ProfileID ProfileID_3 INDEX 2 ProfileID -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transaction suddenly not working
Stuart Felenstein a écrit :
--- Stuart Felenstein [EMAIL PROTECTED] wrote:
I know recently the server was update from PHP 4.3.8
to 4.3.9 but that shouldn't have effected anything.
Code:
$query = INSERT INTO table1 (ProfileID, LurkID,
ProfileName, Edu,...
VALUES (null,...
ProfileID is the PrimaryID (autoinc) - I've set the
input value to null for the auto-inc to properly
work.
I assume this is correct and it did work at one
time.
Then before the next query:
$LID = mysql_insert_id();
Then each subsequent query uses $LID in the
subsequent
tables.
However $LID is now returning 0 and the transaction
fails.
At the end of the transaction I follow through with
this:
//resx = all the queries
if($res1 $res2 $res3 $res4 $res5
$res6
res7 ) // If all results are true
{
commit();
//echo your insertions were successful;
unset($all session variable, $LID, );
}else{
...
Puzzling since this script was working great. We did
also upgrad MySQL from 4.0.21 to 4.0.22-standard
I wanted to add - I realize that if there is an error
in the first query (where the id is generated) it
would result in a value of 0
I've echoed out the query and am seeing this:
Duplicate entry '0-i' for key 1 transaction failed
Never seen a 0-i before.
Stuart
hi!
what if you try to ignore the primary key in fields and values list ?
MySQL should assign a valid primary key. It's what I'm doing on 4.0.22
and it's working If not, try to echo your query and write it in
MySQL( GUI or console).
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Transaction suddenly not working
To view the terms under which this email is distributed, please go to http://disclaimer.leedsmet.ac.uk/email.htm On 29 November 2004 13:19, Stuart Felenstein wrote: Now the printout of the query is this: 0: INSERT INTO Table1 (LurkID, ProfileName, Edu, WorkAuth, WorkExp, CarLev, Secu, Confi, Relo, Telecomu, City1, State1, City2, State2, TravelPref, SalaryAnnual, SalaryHourly, Available) VALUES (47, '', 7, 2, 1015, 5, , '', '', '', 'fds', 5, '', You have 2 successive commas in this last line, with no value in between -- this is your syntax error. , 4, 26, , 2004-02-02)01062 : Duplicate entry '0-f' for key 1 And again on this line -- twice, in fact, since the previous line ends with a comma, too. Track down whatever is outputting these null values, fix it, and you should be good to go (well, at least less bad!). Cheers! Mike - Mike Ford, Electronic Information Services Adviser, Learning Support Services, Learning Information Services, JG125, James Graham Building, Leeds Metropolitan University, Headingley Campus, LEEDS, LS6 3QS, United Kingdom Email: [EMAIL PROTECTED] Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Transaction suddenly not working
--- Ford, Mike [EMAIL PROTECTED] wrote: 0: INSERT INTO Table1 (LurkID, ProfileName, Edu, WorkAuth, WorkExp, CarLev, Secu, Confi, Relo, Telecomu, City1, State1, City2, State2, TravelPref, SalaryAnnual, SalaryHourly, Available) VALUES (47, '', 7, 2, 1015, 5, , '', '', '', 'fds', 5, '', You have 2 successive commas in this last line, with no value in between -- this is your syntax error. , 4, 26, , 2004-02-02)01062 : Duplicate entry '0-f' for key 1 And again on this line -- twice, in fact, since the previous line ends with a comma, too. Mike are you saying that I should put single quotes around all values regardless of the type ? The double commas are happening because around an int I don't use 'value' the single quotes . ? Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Transaction suddenly not working
--- Ford, Mike [EMAIL PROTECTED] wrote: 0: INSERT INTO Table1 (LurkID, ProfileName, Edu, WorkAuth, WorkExp, CarLev, Secu, Confi, Relo, Telecomu, City1, State1, City2, State2, TravelPref, SalaryAnnual, SalaryHourly, Available) VALUES (47, '', 7, 2, 1015, 5, , '', '', '', 'fds', 5, '', You have 2 successive commas in this last line, with no value in between -- this is your syntax error. , 4, 26, , 2004-02-02)01062 : Duplicate entry '0-f' for key 1 And again on this line -- twice, in fact, since the previous line ends with a comma, too. Mike are you saying that I should put single quotes around all values regardless of the type ? The double commas are happening because around an int I don't use 'value' the single quotes . ? Don't know about other database engines, but in MySQL, it doesn't matter if an int is inserted as a string, i.e., with the quotes. My recollection is that MySQL handles the type conversion internally. So, assuming you are validating the entries before inserting, if a value is missing, assign to it ''. Whatever the field has as a default value will then be inserted into the table. dave
