behavior and documents conflict for SELECT LAST_INSERT_ID()
Dear friends, 1. https://bugs.mysql.com/bug.php?id=78934 2. https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id The document (2) says that LAST_INSERT_ID() will not be changed if no rows are inserted successfully. But (1) says that it's undefined when no rows are inserted successfully, which is in real case. What is the problem? the document or the code? Best Regards, Chenxi Li
last_insert_id
Hi; mysql select * from products; ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | ID | SKU | Category | Name | Title | Description | Price | SortFactor | Availability | OutOfStock | ShipFlatFee | ShipPercentPrice | ShipPercentWeight | Associations | TempPrice | LastDatePrice | Weight | Metal| PercentMetal | pic0 | pic1 | sizes | colorsShadesNumbersShort | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | 1 | prodSKU1 | prodCat1 | name1 | title1 | desc| 12.34 | 500 |1 | 0 | 10.00 |5 | 2 | | 1 | 2000-01-01| 2.50 | 14k gold | 20 | NULL | NULL | Extra-small | | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ 1 row in set (0.00 sec) mysql select last_insert_id() from products; +--+ | last_insert_id() | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql Now, I was expecting 1, not 0! What up? TIA, Victor
Re: last_insert_id
2009/12/27 Victor Subervi victorsube...@gmail.com: mysql select * from products; [...] mysql select last_insert_id() from products; [...] Now, I was expecting 1, not 0! What up? [...] LAST_INSERT_ID() (no arguments) returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. [...] If no rows were (successfully) inserted, LAST_INSERT_ID() returns 0. http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id http://dev.mysql.com/doc/refman/5.1/en/getting-unique-id.html Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
On Sun, Dec 27, 2009 at 11:27 AM, Mattia Merzi mattia.me...@gmail.comwrote:
2009/12/27 Victor Subervi victorsube...@gmail.com:
mysql select * from products;
[...]
mysql select last_insert_id() from products;
[...]
Now, I was expecting 1, not 0! What up?
[...] LAST_INSERT_ID() (no arguments) returns the first
automatically generated value successfully inserted for
an AUTO_INCREMENT column as a result of the most
recently executed INSERT statement. [...] If no rows
were (successfully) inserted, LAST_INSERT_ID() returns 0.
But it *is* auto incremented!
mysql describe products;
+--+---+--+-+++
| Field|
Type
| Null | Key | Default| Extra |
+--+---+--+-+++
| ID | tinyint(5)
unsigned
| NO | PRI | NULL | auto_increment |
| SKU |
varchar(40)
| NO | UNI | NULL ||
| Category |
varchar(40)
| YES | | NULL ||
| Name |
varchar(50)
| NO | | NULL ||
| Title|
varchar(100)
| NO | | NULL ||
| Description |
mediumtext
| NO | | NULL ||
| Price|
float(8,2)
| YES | | NULL ||
| SortFactor |
int(4)
| YES | | 500||
| Availability |
tinyint(1)
| NO | | 1 ||
| OutOfStock |
tinyint(1)
| NO | | 0 ||
| ShipFlatFee |
float(5,2)
| NO | | 10.00 ||
| ShipPercentPrice | tinyint(2)
unsigned
| NO | | 5 ||
| ShipPercentWeight| tinyint(2)
unsigned
| NO | | 2 ||
| Associations |
varchar(40)
| NO | | NULL ||
| TempPrice|
tinyint(1)
| NO | | 1 ||
| LastDatePrice|
date
| NO | | 2000-01-01 ||
| Weight |
float(7,2)
| NO | | NULL ||
| Metal| enum('14k gold','18k gold','white
gold','silver','tungsten','titanium')
| NO | | NULL ||
| PercentMetal | tinyint(2)
unsigned
| NO | | NULL ||
| pic0 |
mediumblob
| YES | | NULL ||
| pic1 |
mediumblob
| YES | | NULL ||
| sizes|
set('Extra-small','Small','Medium','Large','XLarge','XXLarge','XXXLarge')
| YES | | NULL ||
| colorsShadesNumbersShort |
set('blue:99','gray:465945','purple:50404D','navy-blue:CC7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black:FF','yellow:9ACD32')
| YES | | NULL ||
+--+---+--+-+++
23 rows in set (0.00 sec)
mysql
Re: last_insert_id
last_insert_id() returns the last id auto-incremented in *the current
session*. If you disconnect and reconnect, it can not be retrieved.
- michael dykman
On Sun, Dec 27, 2009 at 11:42 AM, Victor Subervi
victorsube...@gmail.com wrote:
On Sun, Dec 27, 2009 at 11:27 AM, Mattia Merzi mattia.me...@gmail.comwrote:
2009/12/27 Victor Subervi victorsube...@gmail.com:
mysql select * from products;
[...]
mysql select last_insert_id() from products;
[...]
Now, I was expecting 1, not 0! What up?
[...] LAST_INSERT_ID() (no arguments) returns the first
automatically generated value successfully inserted for
an AUTO_INCREMENT column as a result of the most
recently executed INSERT statement. [...] If no rows
were (successfully) inserted, LAST_INSERT_ID() returns 0.
But it *is* auto incremented!
mysql describe products;
+--+---+--+-+++
| Field |
Type
| Null | Key | Default | Extra |
+--+---+--+-+++
| ID | tinyint(5)
unsigned
| NO | PRI | NULL | auto_increment |
| SKU |
varchar(40)
| NO | UNI | NULL | |
| Category |
varchar(40)
| YES | | NULL | |
| Name |
varchar(50)
| NO | | NULL | |
| Title |
varchar(100)
| NO | | NULL | |
| Description |
mediumtext
| NO | | NULL | |
| Price |
float(8,2)
| YES | | NULL | |
| SortFactor |
int(4)
| YES | | 500 | |
| Availability |
tinyint(1)
| NO | | 1 | |
| OutOfStock |
tinyint(1)
| NO | | 0 | |
| ShipFlatFee |
float(5,2)
| NO | | 10.00 | |
| ShipPercentPrice | tinyint(2)
unsigned
| NO | | 5 | |
| ShipPercentWeight | tinyint(2)
unsigned
| NO | | 2 | |
| Associations |
varchar(40)
| NO | | NULL | |
| TempPrice |
tinyint(1)
| NO | | 1 | |
| LastDatePrice |
date
| NO | | 2000-01-01 | |
| Weight |
float(7,2)
| NO | | NULL | |
| Metal | enum('14k gold','18k gold','white
gold','silver','tungsten','titanium')
| NO | | NULL | |
| PercentMetal | tinyint(2)
unsigned
| NO | | NULL | |
| pic0 |
mediumblob
| YES | | NULL | |
| pic1 |
mediumblob
| YES | | NULL | |
| sizes |
set('Extra-small','Small','Medium','Large','XLarge','XXLarge','XXXLarge')
| YES | | NULL | |
| colorsShadesNumbersShort |
set('blue:99','gray:465945','purple:50404D','navy-blue:CC7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black:FF','yellow:9ACD32')
| YES | | NULL | |
+--+---+--+-+++
23 rows in set (0.00 sec)
mysql
--
- michael dykman
- mdyk...@gmail.com
May you live every day of your life.
Jonathan Swift
Larry's First Law of Language Redesign: Everyone wants the colon.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman mdyk...@gmail.com wrote: last_insert_id() returns the last id auto-incremented in *the current session*. If you disconnect and reconnect, it can not be retrieved. Ahah! So how do I retrieve the last id inserted irrespective of connection? TIA, V
Re: last_insert_id
Victor Subervi wrote: On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman mdyk...@gmail.com wrote: last_insert_id() returns the last id auto-incremented in *the current session*. If you disconnect and reconnect, it can not be retrieved. Ahah! So how do I retrieve the last id inserted irrespective of connection? Would max() work for you? This isn't necessarily foolproof, as it would show the highest ID if you used max(id), for instance - this won't necessarily be what you were expecting, but in most cases will be what you'd imagine it would be. An example of where it wouldn't be: Although ID is auto_increment, you could define a row as, say, '10005583429'. This would be a valid input. Selecting max(id) would return that number. However, auto_increment wouldn't change - it would still be '34' (or whatever) for the next line. Thus, max(id) would be wrong for however long it takes for auto_increment to get to that figure, which could potentially be a long time. Cheers, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
At 11:13 AM -0500 12/27/09, you wrote: Hi; mysql select * from products; ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | ID | SKU | Category | Name | Title | Description | Price | SortFactor | Availability | OutOfStock | ShipFlatFee | ShipPercentPrice | ShipPercentWeight | Associations | TempPrice | LastDatePrice | Weight | Metal| PercentMetal | pic0 | pic1 | sizes | colorsShadesNumbersShort | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ | 1 | prodSKU1 | prodCat1 | name1 | title1 | desc| 12.34 | 500 |1 | 0 | 10.00 |5 | 2 | | 1 | 2000-01-01| 2.50 | 14k gold | 20 | NULL | NULL | Extra-small | | ++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+ 1 row in set (0.00 sec) mysql select last_insert_id() from products; +--+ | last_insert_id() | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql Now, I was expecting 1, not 0! What up? TIA, Victor The normal procedure would be to: insert into products values (null, 'prodsku2',...); select last_insert_id(); (assuming ID is your autoincremented field). Do the select last_insert_id() immediately after your insert, and it is guaranteed to give you the ID of the record you just inserted, regardless of what inserts may be happening in other sessions (and if the insert was not successful, it will return 0). If you want to get the highest ID that has been inserted regardless of session or without doing an insert first, you could do a select max(ID). Depending on your overall database design, this may or may not give you what you want. Eg: (1) you can explicitly specify a value for an autoincrement field (eg, insert into products values (1000,'prodsku3'...), which could leave a gap. However, the next autoincrement value in this case would be 1001 and is probably what you want. (2) autoincrement values are not reused after deletion, so if you deleted the record with ID=1000 inserted in (1), the next autoincrement would still be 1001, even if the existing records are IDs 1,2,3. This is usually the desired behavior, but again, may not be what *you* need. I'd recommend spending some time reading the documentation for autoincrement fields and the last_insert_id() function. - sbe - -- ++ | Steve Edberg edb...@edberg-online.com | | Programming/Database/SysAdminhttp://www.edberg-online.com/ | ++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
On Sun, Dec 27, 2009 at 1:30 PM, Gary Smith li...@l33t-d00d.co.uk wrote: Victor Subervi wrote: On Sun, Dec 27, 2009 at 12:00 PM, Michael Dykman mdyk...@gmail.com wrote: last_insert_id() returns the last id auto-incremented in *the current session*. If you disconnect and reconnect, it can not be retrieved. Ahah! So how do I retrieve the last id inserted irrespective of connection? Would max() work for you? Ahah! No space! Got it. Thanks. V
Re: last_insert_id
Steve Edberg wrote: (2) autoincrement values are not reused after deletion, so if you deleted the record with ID=1000 inserted in (1), the next autoincrement would still be 1001, even if the existing records are IDs 1,2,3. This is usually the desired behavior, but again, may not be what *you* need. Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch which changes this behaviour, or is my mind dribbling out of my ears? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
Gary Smith skrev: ... An example of where it wouldn't be: Although ID is auto_increment, you could define a row as, say, '10005583429'. This would be a valid input. Selecting max(id) would return that number. However, auto_increment wouldn't change - it would still be '34' (or whatever) for the next line. Not quite... CREATE TABLE t (id bigint unsigned primary key auto_increment); INSERT INTO t VALUES (10005583429); INSERT INTO t VALUES (null); SELECT * FROM t; +-+ | id | +-+ | 10005583429 | | 10005583430 | +-+ 2 rows in set (0.00 sec) / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
Gary Smith wrote: Steve Edberg wrote: (2) autoincrement values are not reused after deletion, so if you deleted the record with ID=1000 inserted in (1), the next autoincrement would still be 1001, even if the existing records are IDs 1,2,3. This is usually the desired behavior, but again, may not be what *you* need. Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch which changes this behaviour, or is my mind dribbling out of my ears? As far as I'm aware there's no mode to change the default behaviour, but you can always reset the autoincrement value: ALTER TABLE tbl AUTO_INCREMENT = n; Do that, and the next inserted record will have id = n, provided that n is greater than the current maximum value. If, on the other hand, n is lower than or equal to the current maximum value, the next id will be the next value higher than the current maximum. So ALTER TABLE tbl AUTO_INCREMENT = 1; on a non-empty table is functionally equivalent to ALTER TABLE tbl AUTO_INCREMENT = MAX(id) + l (which isn't valid SQL, so don't try it!) If you want to reuse autoincrement values above the current maximum, therefore, you can achieve that in practice by resetting the autoincrement value prior to any insertion. What you can't do, though, is get autoincrement to insert values into the middle of a sequence. So if you have, say, ids 1,2,3,4,5,8,9 and you issue ALTER TABLE tbl AUTO_INCREMENT = 1; or ALTER TABLE tbl AUTO_INCREMENT = 6; then the next inserted id will still be 10, not 6. Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LAST_INSERT_ID and CRC32
Thank you very much for all answers I will trying Triggers and the example with the update after an INSERT. Ant then, I use the best for me;-) Thunder Yes, Triggers... I so rarely use them I forget they exist. On Tue, May 5, 2009 at 10:22 AM, Thomas Pundt mli...@rp-online.de wrote: Johnny Withers schrieb: Well, I think an update after insert is the only way. Other than perpopulating another table with possibe crc values then usinga join: Select id from testtable Inner join crctable on testtable.id=crctable.id Where crctable.crcval='xxx' Just be sure to index the crcval column. From my understanding, a TRIGGER might do exactly what Thunder needs. http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Ciao, Thomas Pundt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LAST_INSERT_ID and CRC32
Johnny Withers schrieb: Well, I think an update after insert is the only way. Other than perpopulating another table with possibe crc values then usinga join: Select id from testtable Inner join crctable on testtable.id=crctable.id Where crctable.crcval='xxx' Just be sure to index the crcval column. From my understanding, a TRIGGER might do exactly what Thunder needs. http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Ciao, Thomas Pundt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LAST_INSERT_ID and CRC32
Yes, Triggers... I so rarely use them I forget they exist. On Tue, May 5, 2009 at 10:22 AM, Thomas Pundt mli...@rp-online.de wrote: Johnny Withers schrieb: Well, I think an update after insert is the only way. Other than perpopulating another table with possibe crc values then usinga join: Select id from testtable Inner join crctable on testtable.id=crctable.id Where crctable.crcval='xxx' Just be sure to index the crcval column. From my understanding, a TRIGGER might do exactly what Thunder needs. http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Ciao, Thomas Pundt -- - Johnny Withers 601.209.4985 joh...@pixelated.net
LAST_INSERT_ID and CRC32
Hello,
I have a questions and I hope, that is possible in MySQL.
I have the following short Table.
CREATE TABLE IF NOT EXISTS `testtable` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`id-crc` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id-crc` (`id-crc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
`id` is the unique autoincrement
in `id-crc` I would like save the CRC32 from `id` (the coloumn is unique)
E.G.
id id-crc
1 2212294583 -- CRC32('1')
2 450215437 -- CRC32('2')
3 1842515611 -- CRC32('3')
I would like insert the CRC32 directly when I make a new Insert. E.G.
INSERT INTO `db283796092`.`testtable`
(
`id` , `id-crc`
)
VALUES (
NULL , LAST_INSERT_ID()
);
But LAST_INSERT_ID() is 0 How can I make that, that he use the actual
INSERT-ID?
Best regards
Thunder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LAST_INSERT_ID and CRC32
I don't think its possible to do what you want in a single statement. Since
LAST_INSERT_ID() is set to the last insert id of the connection... and the
row you are inserting doesn't exist.. well.. until you create it, it will
always either be zero or the record BEFORE your next insert, ie:
INSERT INTO testtable(NULL,LAST_INSERT_ID());
INSERT INTO testtable(NULL,LAST_INSERT_ID());
would produce
IDCRC32ID
1 0
2 1
You could run an update immediately after the insert to set the CRC32
column:
UPDATE testtable SET id-crc=CRC32(LAST_INSERT_ID()) WHERE
id=LAST_INSERT_ID();
Not quite sure why you need the CRC32 value of the ID, will it not always be
the same value for the given ID number? Wouldn't it be easier to do it on
the select side of the equation?
SELECT id,CRC32(id) AS id-crc... FROM testtable...
-jw
On Sun, May 3, 2009 at 7:16 AM, thun...@isfahan.at thun...@isfahan.atwrote:
Hello,
I have a questions and I hope, that is possible in MySQL.
I have the following short Table.
CREATE TABLE IF NOT EXISTS `testtable` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`id-crc` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id-crc` (`id-crc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
`id` is the unique autoincrement
in `id-crc` I would like save the CRC32 from `id` (the coloumn is unique)
E.G.
id id-crc
1 2212294583 -- CRC32('1')
2 450215437 -- CRC32('2')
3 1842515611 -- CRC32('3')
I would like insert the CRC32 directly when I make a new Insert. E.G.
INSERT INTO `db283796092`.`testtable` (
`id` , `id-crc`
)
VALUES (
NULL , LAST_INSERT_ID()
);
But LAST_INSERT_ID() is 0 How can I make that, that he use the actual
INSERT-ID?
Best regards
Thunder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
--
-
Johnny Withers
601.209.4985
joh...@pixelated.net
Re: LAST_INSERT_ID and CRC32
Hi Johnny,
I need the CRC32 for a unique URL-ID...
I think it isn't to slow when I make a SELECT later in this form:
Rows in Table: 825,984
Search for id: 2532552 (CRC32: 46316330)
SELECT id FROM `testtable` WHERE id = 2532552
0.0005 sec.
SELECT id FROM `testtable` WHERE CRC32(id) = 46316330
0.5712 sec.
OK, I can make an UPDATE after an INSERT but then I can't use UNIQUE for
the Coloumn with the CRC32...
Before I can make an Update, the Value is 0...
I don't think its possible to do what you want in a single statement. Since
LAST_INSERT_ID() is set to the last insert id of the connection... and the
row you are inserting doesn't exist.. well.. until you create it, it will
always either be zero or the record BEFORE your next insert, ie:
INSERT INTO testtable(NULL,LAST_INSERT_ID());
INSERT INTO testtable(NULL,LAST_INSERT_ID());
would produce
IDCRC32ID
1 0
2 1
You could run an update immediately after the insert to set the CRC32
column:
UPDATE testtable SET id-crc=CRC32(LAST_INSERT_ID()) WHERE
id=LAST_INSERT_ID();
Not quite sure why you need the CRC32 value of the ID, will it not always be
the same value for the given ID number? Wouldn't it be easier to do it on
the select side of the equation?
SELECT id,CRC32(id) AS id-crc... FROM testtable...
-jw
On Sun, May 3, 2009 at 7:16 AM, thun...@isfahan.at thun...@isfahan.atwrote:
Hello,
I have a questions and I hope, that is possible in MySQL.
I have the following short Table.
CREATE TABLE IF NOT EXISTS `testtable` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`id-crc` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id-crc` (`id-crc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
`id` is the unique autoincrement
in `id-crc` I would like save the CRC32 from `id` (the coloumn is unique)
E.G.
id id-crc
1 2212294583 -- CRC32('1')
2 450215437 -- CRC32('2')
3 1842515611 -- CRC32('3')
I would like insert the CRC32 directly when I make a new Insert. E.G.
INSERT INTO `db283796092`.`testtable` (
`id` , `id-crc`
)
VALUES (
NULL , LAST_INSERT_ID()
);
But LAST_INSERT_ID() is 0 How can I make that, that he use the actual
INSERT-ID?
Best regards
Thunder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LAST_INSERT_ID and CRC32
I'm so sorry for the mistake...
I mean:
I think it is to slow when I make a SELECT later in this form:
I don't think its possible to do what you want in a single statement. Since
LAST_INSERT_ID() is set to the last insert id of the connection... and the
row you are inserting doesn't exist.. well.. until you create it, it will
always either be zero or the record BEFORE your next insert, ie:
INSERT INTO testtable(NULL,LAST_INSERT_ID());
INSERT INTO testtable(NULL,LAST_INSERT_ID());
would produce
IDCRC32ID
1 0
2 1
You could run an update immediately after the insert to set the CRC32
column:
UPDATE testtable SET id-crc=CRC32(LAST_INSERT_ID()) WHERE
id=LAST_INSERT_ID();
Not quite sure why you need the CRC32 value of the ID, will it not always be
the same value for the given ID number? Wouldn't it be easier to do it on
the select side of the equation?
SELECT id,CRC32(id) AS id-crc... FROM testtable...
-jw
On Sun, May 3, 2009 at 7:16 AM, thun...@isfahan.at thun...@isfahan.atwrote:
Hello,
I have a questions and I hope, that is possible in MySQL.
I have the following short Table.
CREATE TABLE IF NOT EXISTS `testtable` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`id-crc` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id-crc` (`id-crc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
`id` is the unique autoincrement
in `id-crc` I would like save the CRC32 from `id` (the coloumn is unique)
E.G.
id id-crc
1 2212294583 -- CRC32('1')
2 450215437 -- CRC32('2')
3 1842515611 -- CRC32('3')
I would like insert the CRC32 directly when I make a new Insert. E.G.
INSERT INTO `db283796092`.`testtable` (
`id` , `id-crc`
)
VALUES (
NULL , LAST_INSERT_ID()
);
But LAST_INSERT_ID() is 0 How can I make that, that he use the actual
INSERT-ID?
Best regards
Thunder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LAST_INSERT_ID and CRC32
Well, I think an update after insert is the only way. Other than
perpopulating another table with possibe crc values then usinga join:
Select id from testtable
Inner join crctable on testtable.id=crctable.id
Where crctable.crcval='xxx'
Just be sure to index the crcval column.
On Sunday, May 3, 2009, thun...@isfahan.at thun...@isfahan.at wrote:
Hi Johnny,
I need the CRC32 for a unique URL-ID...
I think it isn't to slow when I make a SELECT later in this form:
Rows in Table: 825,984
Search for id: 2532552 (CRC32: 46316330)
SELECT id FROM `testtable` WHERE id = 2532552
0.0005 sec.
SELECT id FROM `testtable` WHERE CRC32(id) = 46316330
0.5712 sec.
OK, I can make an UPDATE after an INSERT but then I can't use UNIQUE for the
Coloumn with the CRC32...
Before I can make an Update, the Value is 0...
I don't think its possible to do what you want in a single statement. Since
LAST_INSERT_ID() is set to the last insert id of the connection... and the
row you are inserting doesn't exist.. well.. until you create it, it will
always either be zero or the record BEFORE your next insert, ie:
INSERT INTO testtable(NULL,LAST_INSERT_ID());
INSERT INTO testtable(NULL,LAST_INSERT_ID());
would produce
ID CRC32ID
1 0
2 1
You could run an update immediately after the insert to set the CRC32
column:
UPDATE testtable SET id-crc=CRC32(LAST_INSERT_ID()) WHERE
id=LAST_INSERT_ID();
Not quite sure why you need the CRC32 value of the ID, will it not always be
the same value for the given ID number? Wouldn't it be easier to do it on
the select side of the equation?
SELECT id,CRC32(id) AS id-crc... FROM testtable...
-jw
On Sun, May 3, 2009 at 7:16 AM, thun...@isfahan.at thun...@isfahan.atwrote:
Hello,
I have a questions and I hope, that is possible in MySQL.
I have the following short Table.
CREATE TABLE IF NOT EXISTS `testtable` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`id-crc` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id-crc` (`id-crc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
`id` is the unique autoincrement
in `id-crc` I would like save the CRC32 from `id` (the coloumn is unique)
E.G.
id id-crc
1 2212294583 -- CRC32('1')
2 450215437 -- CRC32('2')
3 1842515611 -- CRC32('3')
I would like insert the CRC32 directly when I make a new Insert. E.G.
INSERT INTO `db283796092`.`testtable` (
`id` , `id-crc`
)
VALUES (
NULL , LAST_INSERT_ID()
);
But LAST_INSERT_ID() is 0 How can I make that, that he use the actual
INSERT-ID?
Best regards
Thunder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
--
-
Johnny Withers
601.209.4985
joh...@pixelated.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL, perl, last_insert_id() question
Hi,
On Jan 28, 2008 3:29 PM, Dean Karres [EMAIL PROTECTED] wrote:
Hi,
I know that someone is going to say, go ask the perl module guys and
I will but they are likely to say, go ask the MySQL guys. I'll be
asking in both groups.
I am installing a script on a brand new RedHat, Fedora Core 7 (x86_64)
box that is running MySQL 5.0.45 (rpm install). I also have a mix
of other rpm MySQL installs on older Redhat boxes that are also a
mix of 32 and 64 bit machines.
The script works fine on the older installs. For example another 64
bit machine has a MySQL rpm rev of 5.0.27
I have a valid DB handle and after an insert I try to get the ID of
that inserted row, a la:
my $id = $DBH-last_insert_id();
This has been working everywhere until I tried installing the exact
same script on this newest machine. When the script runs now I get:
DBI last_insert_id: invalid number of arguments: got handle + 0, expected
handle + between 4 and 5
Usage: $h-last_insert_id($catalog, $schema, $table_name,
$field_name [, \%attr ]) at /usr/local/bin/myScript line 454.
Ok, I know the DBI Docs say that some versions of the drivers may
demand an argument list for last_insert_id() but since I have not seen
this behavior in the earlier versions is this new or is there a
problem?
If you don't need your script to work on other databases, you should
use $dbh-{mysql_insert_id} instead. This accesses the API function
of the same name, instead of going through DBD's wrapper. It doesn't
have to send another query to the server.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL, perl, last_insert_id() question
Hi, I know that someone is going to say, go ask the perl module guys and I will but they are likely to say, go ask the MySQL guys. I'll be asking in both groups. I am installing a script on a brand new RedHat, Fedora Core 7 (x86_64) box that is running MySQL 5.0.45 (rpm install). I also have a mix of other rpm MySQL installs on older Redhat boxes that are also a mix of 32 and 64 bit machines. The script works fine on the older installs. For example another 64 bit machine has a MySQL rpm rev of 5.0.27 I have a valid DB handle and after an insert I try to get the ID of that inserted row, a la: my $id = $DBH-last_insert_id(); This has been working everywhere until I tried installing the exact same script on this newest machine. When the script runs now I get: DBI last_insert_id: invalid number of arguments: got handle + 0, expected handle + between 4 and 5 Usage: $h-last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ]) at /usr/local/bin/myScript line 454. Ok, I know the DBI Docs say that some versions of the drivers may demand an argument list for last_insert_id() but since I have not seen this behavior in the earlier versions is this new or is there a problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL, perl, last_insert_id() question
Do you have the latest version of DBI and DBD::mysql installed? First try: $ cpan cpan install DBI cpan install DBD::mysql You can also do: my $sth = $dbh-prepare(select last_insert_id()); $sth-execute(); my ($last_insert_id) = $sth-fetchrow_array(); Octavian - Original Message - From: Dean Karres [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 28, 2008 10:29 PM Subject: MySQL, perl, last_insert_id() question Hi, I know that someone is going to say, go ask the perl module guys and I will but they are likely to say, go ask the MySQL guys. I'll be asking in both groups. I am installing a script on a brand new RedHat, Fedora Core 7 (x86_64) box that is running MySQL 5.0.45 (rpm install). I also have a mix of other rpm MySQL installs on older Redhat boxes that are also a mix of 32 and 64 bit machines. The script works fine on the older installs. For example another 64 bit machine has a MySQL rpm rev of 5.0.27 I have a valid DB handle and after an insert I try to get the ID of that inserted row, a la: my $id = $DBH-last_insert_id(); This has been working everywhere until I tried installing the exact same script on this newest machine. When the script runs now I get: DBI last_insert_id: invalid number of arguments: got handle + 0, expected handle + between 4 and 5 Usage: $h-last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ]) at /usr/local/bin/myScript line 454. Ok, I know the DBI Docs say that some versions of the drivers may demand an argument list for last_insert_id() but since I have not seen this behavior in the earlier versions is this new or is there a problem? -- 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]
Re: Using the last_insert_id() function
Hi,
gwh wrote:
Hi everyone,
I¹m trying to figure out the best sequence to enter data into my database
(the SQL code for the structure is included below). If I have a number of
tab delimited .txt files containing the data for the different tables, I
thought as a first step I could use the following code to populate the
garments table:
LOAD DATA INFILE 'garments.txt'
INTO TABLE garments
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r'
On most systems that will be '\n', unless your files have Mac line endings. If
they have Windows line endings it might be '\r\n'.
I also thought I could use the same code to insert the data into the
colours, sizes and categories tables. My problem starts when I have to try
to enter the correct garment_id from the garments table into the correct
foreign key fields in the garments_to_colour, garments_to_sizes and
garments_to_categories look up tables. Do I need to do this manually or can
I use the last_insert_id() function? But not sure if this would work since
I¹m not populating the garments table one row at a time I¹m inserting all
info in one go with LOAD DATA INFILE, as mentioned above.
Just wondered if someone could help solve this problem?
You will probably have to LOAD DATA INFILE into a temporary or staging table,
then query against it to populate the other tables.
CREATE TABLE `garments` (
`garment_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier` varchar(30) NOT NULL,
`garment_type` varchar(30) NOT NULL,
`title` varchar(60) NOT NULL,
`code` varchar(20) NOT NULL,
`description` varchar(400) NOT NULL,
`extra_info` varchar(50) default NULL,
`image` enum('y','n') NOT NULL,
`swatch_image` enum('y','n') NOT NULL,
PRIMARY KEY (`garment_id`),
UNIQUE KEY `supplier` (`supplier`,`garment_type`, `description`,
`title`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `colours` (
`colour_id` smallint(5) unsigned NOT NULL auto_increment,
`colour` varchar(20) NOT NULL,
PRIMARY KEY (`colour_id`),
UNIQUE KEY `colour` (`colour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_colour` (
`garment_id` smallint(5) unsigned NOT NULL,
`colour_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`colour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `sizes` (
`size_id` smallint(5) unsigned NOT NULL auto_increment,
`size` varchar(15) NOT NULL,
PRIMARY KEY (`size_id`),
UNIQUE KEY `size` (`size`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_size` (
`garment_id` smallint(5) unsigned NOT NULL,
`size_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `categories` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_category` (
`garment_id` smallint(5) unsigned NOT NULL,
`cat_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using the last_insert_id() function
Hi everyone,
I¹m trying to figure out the best sequence to enter data into my database
(the SQL code for the structure is included below). If I have a number of
tab delimited .txt files containing the data for the different tables, I
thought as a first step I could use the following code to populate the
garments table:
LOAD DATA INFILE 'garments.txt'
INTO TABLE garments
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r'
I also thought I could use the same code to insert the data into the
colours, sizes and categories tables. My problem starts when I have to try
to enter the correct garment_id from the garments table into the correct
foreign key fields in the garments_to_colour, garments_to_sizes and
garments_to_categories look up tables. Do I need to do this manually or can
I use the last_insert_id() function? But not sure if this would work since
I¹m not populating the garments table one row at a time I¹m inserting all
info in one go with LOAD DATA INFILE, as mentioned above.
Just wondered if someone could help solve this problem?
CREATE TABLE `garments` (
`garment_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier` varchar(30) NOT NULL,
`garment_type` varchar(30) NOT NULL,
`title` varchar(60) NOT NULL,
`code` varchar(20) NOT NULL,
`description` varchar(400) NOT NULL,
`extra_info` varchar(50) default NULL,
`image` enum('y','n') NOT NULL,
`swatch_image` enum('y','n') NOT NULL,
PRIMARY KEY (`garment_id`),
UNIQUE KEY `supplier` (`supplier`,`garment_type`, `description`,
`title`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `colours` (
`colour_id` smallint(5) unsigned NOT NULL auto_increment,
`colour` varchar(20) NOT NULL,
PRIMARY KEY (`colour_id`),
UNIQUE KEY `colour` (`colour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_colour` (
`garment_id` smallint(5) unsigned NOT NULL,
`colour_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`colour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `sizes` (
`size_id` smallint(5) unsigned NOT NULL auto_increment,
`size` varchar(15) NOT NULL,
PRIMARY KEY (`size_id`),
UNIQUE KEY `size` (`size`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_size` (
`garment_id` smallint(5) unsigned NOT NULL,
`size_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `categories` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_category` (
`garment_id` smallint(5) unsigned NOT NULL,
`cat_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LAST_INSERT_ID LAST_UPDATE_ID
How can I select (retrieve) the last updated cell (field). Basically I need to
pull the new information only.
I'm using for updating my database:
UPDATE table SET column = CONCAT_WS ('column,' . $column.') WHERE column=
value;
I need to select data:
SELECT * FROM table WHERE column=Whatever
I found LAST_INSERT_ID but i doesn't work
What I want to do is:
1-user has 10 columns (fields).
2- User updated one of these fields using CONCAT_WS(adding new data to previous
data).
3- When user views any of his information he sees only the last updated part of
the data.
So, timestamp/datetime field is not going to work.
-
Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
Re: InnoDB Transaction and LAST_INSERT_ID()
Mike Kruckenberg wrote: mysql SET @staff_id = LAST_INSERT_ID(); Query OK, 0 rows affected (0.01 sec) I don't know if this behaviour has changed in later versions of mysql, but using session variables, although lovely, was the quickest way to break replication (at least up to and including 4.0.27) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Transaction and LAST_INSERT_ID()
Hi List,
Let's suppose I have these two tables:
CREATE TABLE `changes` (
`ID` int(12) unsigned NOT NULL auto_increment,
`Key` varchar(25) collate latin1_general_cs NOT NULL default '',
`Table` varchar(25) collate latin1_general_cs NOT NULL default '',
`Value` text collate latin1_general_cs NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
AUTO_INCREMENT=1;
CREATE TABLE `staff` (
`ID` int(3) unsigned NOT NULL auto_increment,
`Name` varchar(35) collate latin1_general_cs NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
AUTO_INCREMENT=1;
The idea is to have a audit trail to record the changes made. So, I want to
insert a new record in the staff table and right after this, insert a
record in the changes table.
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
Is there a better way to do this or this is fine? I will be using this with
PHP4.
Thanks for any help.
Andre
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Andre Matos wrote:
The idea is to have a audit trail to record the changes made. So, I want to
insert a new record in the staff table and right after this, insert a
record in the changes table.
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
LAST_INSERT_ID() is connection-specific so the ID will be the one that
was assigned during this particular PHP page's connection to the database.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Andre Matos wrote:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
Is there a better way to do this or this is fine? I will be using this with
PHP4.
To further clarify (my initial reply didn't give much detail), when an
auto increment value is created for inserting it is in the scope of the
current connection, and is not changed by the outcome of the transaction.
If you follow the string of SQL statements against your tables you'll
see that the ID assigned to the record is not released on a rollback,
the second insert gets a new auto increment value.
mysql SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 1 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 1 | 1 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
mysql select last_insert_id();
+--+
| last_insert_id() |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql rollback;
Query OK, 0 rows affected (0.26 sec)
mysql select * from staff;
Empty set (0.00 sec)
mysql select * from changes;
Empty set (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 2 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 2 | 2 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Thanks Mike.
I understand the possible gaps that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my staff and change tables to have this structure to
simulate my problem:
++--++
| ID | Name | Gender |
++--++
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql select * from staff;
++--++
| ID | Name | Gender |
++--++
| 1 | ABC | M |
++--++
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
| 1 | 1 | staff | ID| 1 |
++-+---+---+---+
| 2 | 1 | staff | Name | ABC |
++-+---+---+---+
| 3 | 2 | staff | Gender| M |
++-+---+---+---+
3 row in set (0.00 sec)
See that I have a problem in the third line at the Key column where I
should have 1 but I got 2 instead. This happened because of
LAST_INSERT_ID() used the ID from the changes table instead of the desired
staff table.
Is there any way to avoid this? What about the mysql_insert_id()?
Andre
On 11/28/06 7:50 PM, Mike Kruckenberg [EMAIL PROTECTED] wrote:
Andre Matos wrote:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
Is there a better way to do this or this is fine? I will be using this with
PHP4.
To further clarify (my initial reply didn't give much detail), when an
auto increment value is created for inserting it is in the scope of the
current connection, and is not changed by the outcome of the transaction.
If you follow the string of SQL statements against your tables you'll
see that the ID assigned to the record is not released on a rollback,
the second insert gets a new auto increment value.
mysql SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 1 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 1 | 1 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
mysql select last_insert_id();
+--+
| last_insert_id() |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql rollback;
Query OK, 0 rows affected (0.26 sec)
mysql select * from staff;
Empty set (0.00 sec)
mysql select * from changes;
Empty set (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 2 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 2 | 2 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Andre Matos wrote:
Thanks Mike.
I understand the possible gaps that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my staff and change tables to have this structure to
simulate my problem:
++--++
| ID | Name | Gender |
++--++
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql select * from staff;
++--++
| ID | Name | Gender |
++--++
| 1 | ABC | M |
++--++
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
| 1 | 1 | staff | ID| 1 |
++-+---+---+---+
| 2 | 1 | staff | Name | ABC |
++-+---+---+---+
| 3 | 2 | staff | Gender| M |
++-+---+---+---+
3 row in set (0.00 sec)
See that I have a problem in the third line at the Key column where I
should have 1 but I got 2 instead. This happened because of
LAST_INSERT_ID() used the ID from the changes table instead of the desired
staff table.
Is there any way to avoid this? What about the mysql_insert_id()?
I see. In this case you could make it two operations and use the
mysql_insert_id() to capture the id from the first insert, setting a
variable to that in PHP and using that variable to ensure the same number.
What I would consider is setting a database variable inside the
transaction to store the id - the @ signifies it's a session variable
that is specific to this connection:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
SET @staff_id = LAST_INSERT_ID();
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'ID', @staff_id);
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
So you store the value after the first insert and then reuse. You can
see it in the following string of SQL commands to demonstrate:
mysql INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
Query OK, 1 row affected (0.01 sec)
mysql SET @staff_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.01 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.01 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select LAST_INSERT_ID();
+--+
| LAST_INSERT_ID() |
+--+
|5 |
+--+
1 row in set (0.00 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Thanks for all your help Mike.
Problem solved. I divided to process in two parts: one write the
insert/update/delete and then write the changes in the audit trail. All this
inside one transaction. If the first part fails, ROLLBACK. If the second
part fails, ROLLBACK, otherwise, if both were done ok, then COMMIT.
I just wanted to have all this in only one part, but that's fine. It's
working fine.
Final question: Can I create an audit trail using TRIGGER in MySQL 5? This
would be the best because any changes in the database (insert/update/delete)
will start the trigger which will be responsible for writing the audit
trail.
Thanks again!!!
Andre
On 11/28/06 9:22 PM, Mike Kruckenberg [EMAIL PROTECTED] wrote:
Andre Matos wrote:
Thanks Mike.
I understand the possible gaps that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my staff and change tables to have this structure to
simulate my problem:
++--++
| ID | Name | Gender |
++--++
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql select * from staff;
++--++
| ID | Name | Gender |
++--++
| 1 | ABC | M |
++--++
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
| 1 | 1 | staff | ID| 1 |
++-+---+---+---+
| 2 | 1 | staff | Name | ABC |
++-+---+---+---+
| 3 | 2 | staff | Gender| M |
++-+---+---+---+
3 row in set (0.00 sec)
See that I have a problem in the third line at the Key column where I
should have 1 but I got 2 instead. This happened because of
LAST_INSERT_ID() used the ID from the changes table instead of the desired
staff table.
Is there any way to avoid this? What about the mysql_insert_id()?
I see. In this case you could make it two operations and use the
mysql_insert_id() to capture the id from the first insert, setting a
variable to that in PHP and using that variable to ensure the same number.
What I would consider is setting a database variable inside the
transaction to store the id - the @ signifies it's a session variable
that is specific to this connection:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
SET @staff_id = LAST_INSERT_ID();
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'ID', @staff_id);
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
So you store the value after the first insert and then reuse. You can
see it in the following string of SQL commands to demonstrate:
mysql INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
Query OK, 1 row affected (0.01 sec)
mysql SET @staff_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.01 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.01 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select LAST_INSERT_ID();
+--+
| LAST_INSERT_ID() |
+--+
|5 |
+--+
1 row in set (0.00 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
last_insert_id problem
Hi Im using the latest version of mysql. When I run the following query : select last_insert_id() if get the error message: function vcontacts.last_insert_id does not exist note: vcontacts is the name of my database. anyone know why? thanks
Re: last_insert_id problem
Afshad Dinshaw wrote: Hi Im using the latest version of mysql. When I run the following query : select last_insert_id() if get the error message: function vcontacts.last_insert_id does not exist note: vcontacts is the name of my database. anyone know why? thanks I can run it both uppercase and lowercase (mysql 4.1.13-Max). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'on duplicate key update' and 'last_insert_id'
I have a table `event` with two keys:
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`location_id` MEDIUMINT(8) UNSIGNED NOT NULL,
`timestamp` DATETIME NOT NULL,
`type` ENUM('0','1','2','3','4','5','6','7','8','9') NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`location_id`,`timestamp`,`type`)
Sometimes a client will attempt to perform an insert into the table where the
row has already been inserted, i.e. the unique key already exists. In that case
I want it to appear as though it wasn't there before and has been inserted,
returning the new value of id.
I don't want to perform an INSERT IGNORE as this ignores far more errors than
just duplicate keys.
I'd rather not use REPLACE as if the unique key matches then the rest of the
row definitely matches.
That leaves me with ON DUPLICATE KEY UPDATE. It's not amazingly helpful as you
have to provide a column to update - however I can just say e.g.
ON DUPLICATE KEY UPDATE id=id
The problem with this is that if I then do SELECT LAST_INSERT_ID(); then I
don't get the id of the 'updated' table, I get the *next* auto increment value.
Is the last bit a bug? Can I get what I want without using REPLACE? Does this
post make sense?
Thanks ;-D
Rob
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'on duplicate key update' and 'last_insert_id'
On Jun 30, 2006, at 10:44 AM, Rob Desbois wrote: That leaves me with ON DUPLICATE KEY UPDATE. It's not amazingly helpful as you have to provide a column to update - however I can just say e.g. ON DUPLICATE KEY UPDATE id=id The problem with this is that if I then do SELECT LAST_INSERT_ID (); then I don't get the id of the 'updated' table, I get the *next* auto increment value. Is the last bit a bug? Yes, http://bugs.mysql.com/bug.php?id=19243 -- David Hillman LiveText, Inc 1.866.LiveText x235
Questions about last_insert_id() instead of @@IDENTITY
I am migrating from MS SQL Server to MySQL 4.1. I have code in my ColdFusion/SQL Server application that went something like this: cfquery name=insertquery SET NOCOUNT ON INSERT INTO TABLE () SELECT @@IDENTITY AS adid SET NOCOUNT OFF /cfquery That whole block was processed in coldfusion as a single query. I gave it a name of insertquery, and I can get the ID back as insertquery.adid. I am having trouble getting the same functionality with MySQL. cfquery name=insertquery INSERT INTO TABLE (...) SELECT LAST_INSERT_ID() as adid /cfquery I get this MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO rps_names (nameid, name) VALUES (NULL, Ryan Smithland) SELECT LA' at line 2 With MSSQL the trick was the NO COUNT. That told the database server not to return a count of the rows affected. Is there something similar I need to do in MySQL to get this to work? Thanks, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about last_insert_id() instead of @@IDENTITY
On 9/7/2005 1:17 PM Ryan Stille intelligently wrote: I am migrating from MS SQL Server to MySQL 4.1. I have code in my ColdFusion/SQL Server application that went something like this: cfquery name=insertquery SET NOCOUNT ON INSERT INTO TABLE () SELECT @@IDENTITY AS adid SET NOCOUNT OFF /cfquery That whole block was processed in coldfusion as a single query. I gave it a name of insertquery, and I can get the ID back as insertquery.adid. I am having trouble getting the same functionality with MySQL. cfquery name=insertquery INSERT INTO TABLE (...) SELECT LAST_INSERT_ID() as adid /cfquery I get this MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO rps_names (nameid, name) VALUES (NULL, Ryan Smithland) SELECT LA' at line 2 With MSSQL the trick was the NO COUNT. That told the database server not to return a count of the rows affected. Is there something similar I need to do in MySQL to get this to work? Thanks, -Ryan it might be best to ask this in the cf forums, however did you try to separate the statements with a ; ? I haven't tried 2 query statements for MySQL in cfquery...yet ;-) Also I believe MySQL 3.23+ supports @@identity HTH, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Questions about last_insert_id() instead of @@IDENTITY
it might be best to ask this in the cf forums, however did you try to separate the statements with a ; ? I haven't tried 2 query statements for MySQL in cfquery...yet ;-) Also I believe MySQL 3.23+ supports @@identity Yes I've tried semicolons after the statements, and I've tried setting autocommit = 0 at the beginning also. I will ask on a CF list, because they may have run into this issue. But really I think it's a MySQL question. Thanks, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about last_insert_id() instead of @@IDENTITY
http://dev.mysql.com/doc/mysql/en/insert-select.html There are 2 forms of INSERT INTO statement 1. INSERT INTO tbl_name (...) VALUES(...); 2. INSERT INTO tbl_name (...) SELECT ...; there's no : INSERT INTO tbl_name (...) VALUES(...) SELECT ...; its either ... or in MySQL i.e. aither provide values or get then form SELECT ... but not both in same INSERT ... so indeed: 'INSERT INTO rps_names (nameid, name) VALUES (NULL, Ryan Smithland) SELECT LA' - is invalid query! Ryan Stille wrote: I am migrating from MS SQL Server to MySQL 4.1. I have code in my ColdFusion/SQL Server application that went something like this: cfquery name=insertquery SET NOCOUNT ON INSERT INTO TABLE () SELECT @@IDENTITY AS adid SET NOCOUNT OFF /cfquery That whole block was processed in coldfusion as a single query. I gave it a name of insertquery, and I can get the ID back as insertquery.adid. I am having trouble getting the same functionality with MySQL. cfquery name=insertquery INSERT INTO TABLE (...) SELECT LAST_INSERT_ID() as adid /cfquery I get this MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO rps_names (nameid, name) VALUES (NULL, Ryan Smithland) SELECT LA' at line 2 With MSSQL the trick was the NO COUNT. That told the database server not to return a count of the rows affected. Is there something similar I need to do in MySQL to get this to work? Thanks, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
last_insert_id
Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? - or do I have to lock my table, execute the query, retrieve the id, unlock the table? - is there a way to make an atomic query with this that would avoid me to use locks? Thanks a lot for any help, Melissa _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
Hi, You can probably use SELECT LAST_INSERT_ID() which keeps auto-increment values on a per connection basis. See: http://dev.mysql.com/doc/mysql/en/getting-unique-id.html /Johan mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? - or do I have to lock my table, execute the query, retrieve the id, unlock the table? - is there a way to make an atomic query with this that would avoid me to use locks? Thanks a lot for any help, Melissa _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
mel list_php [EMAIL PROTECTED] wrote on 15/02/2005 10:18:55: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? - or do I have to lock my table, execute the query, retrieve the id, unlock the table? - is there a way to make an atomic query with this that would avoid me to use locks? last_insert_id is on a per-connection basis i.e. it gives the last id inserted using that particular connection. Therefore you will always get the most recent ID that you inserted, not the most recent that anyone inserted. I think, therefore, that the natural behaviour is what you want. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
Yes that's what I mean I arranged to have single queries for the inserts. For example I avoid doing a select on criteria to retrieve that id an then an update of this id. thanks for help!!! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: last_insert_id Date: Tue, 15 Feb 2005 10:50:40 + mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
I just would ask for a precision: In my system, I include a connection file with my parameters (host,user,pass).This is the details of the account allowed to establish the connection with the mysql server. When 2 users are connecting to the database (through the web), they will use the same details (host,user,pass) for the connection. Does that mean that they are sharing the same mysql connection (and in that case will I need a lock?) or are they each of them opening their own connection? Can I check that somewhere? Thanks a lot. From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: last_insert_id Date: Tue, 15 Feb 2005 10:50:40 + mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
really sorry to bother you with my connections problems. I've made a test using select connection_id(), to see what was the current identifier for my connection. Each time I change of page, the connection_id is different, I suppose that is because I require my connection file at the beginning of each script. This seems fine to me as I don't want to lock the tables, and the last_insert_id is performed in the same file than the insert, so on the per connection basis it's perfect (thank you very much for your help!!). But it seems a bit strange to me to open so many connections. I know there is a limit somewhere, at the moment it is not a problem I don't have a lot of users and they are not coming often, but can it become one in the future? I tried to find in the documentation some information on when is a connection open or if it is possible to keep one connection per user, but found nothing. In addition I don't think it is possible because for the mysql server only one user gets connected, it doesn't care about the users I have in my authentication table. If I misunderstood something please point me to some doc or give me some advice... From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: last_insert_id Date: Tue, 15 Feb 2005 10:50:40 + mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Hi! Am Di, den 18.01.2005 schrieb Paul DuBois um 3:53: At 21:27 -0500 1/17/05, Andre Matos wrote: Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Using MAX() won't guarantee that you won't have gaps. What you're describing cannot be achieved in the general case. Consider this scenario: - Transaction T1 begins, generates an AUTO_INCREMENT value n. - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. - Transaction T2 commits. - Transaction T1 rolls back. You now have a gap at value n, and a used value of n+1. MAX() at this point returns n+1, not n, so that won't help you reuse n. With more than two transactions running simultaneously, each of which can roll back or commit, the situation becomes more complex. IMO, Andre's only chance is to code his transactions in such a way that they need not rollback (only do so if the whole system stops). One way that comes to my mind is to accumulate all data in some temporary table, using some other value as ID (or in application variables), and only after the final yes, do it confirmation transfer them to the true tables with the auto-increment ID. In future releases, stored procedures might be another way to ensure all actions are grouped without an intervening parallel rollback. If your concurrency requirements are low and you can stand wait time, you could keep the next ID in an application-controlled table, locked from its retrieval to a final increment at transaction commit; but I agree these low requirements are unusual. As an alternative, a rollback might create a dummy record using that ID which acts as a placeholder, maybe with a remark user rollback or similar - if that is permissible in the application. Might be worth reconsidering whether you really require no gaps. It's generally better to try to design an application not to have that dependency. Paul, while I agree with that preference, I know that sometimes there is no choice. As an example, some German bookkeeping regulation requires you to use dense booking numbers (without gap). So I know of a software project that used _descending_ numbers because they were faster to generate in their environment than ascending ones. (This does not solve the rollback issue, of course.) Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Andre, I would recommend a table for recovering id's that are lost due to rollback. Before you actually rollback, take the generated ID and push it into this table. Then change the way you acquire id's on insert. You will want to check to see if this table has an ID before you auto_increment the table you are inserting the record into. This should be a little less resource intensive than to put all data into temporary tables. Clint From: Joerg Bruehe [EMAIL PROTECTED] To: mysql@lists.mysql.com CC: Andre Matos [EMAIL PROTECTED], Paul DuBois [EMAIL PROTECTED] Subject: Re: Rollback and INSERT_ID() or LAST_INSERT_ID() Date: Tue, 18 Jan 2005 11:08:40 +0100 Hi! Am Di, den 18.01.2005 schrieb Paul DuBois um 3:53: At 21:27 -0500 1/17/05, Andre Matos wrote: Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Using MAX() won't guarantee that you won't have gaps. What you're describing cannot be achieved in the general case. Consider this scenario: - Transaction T1 begins, generates an AUTO_INCREMENT value n. - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. - Transaction T2 commits. - Transaction T1 rolls back. You now have a gap at value n, and a used value of n+1. MAX() at this point returns n+1, not n, so that won't help you reuse n. With more than two transactions running simultaneously, each of which can roll back or commit, the situation becomes more complex. IMO, Andre's only chance is to code his transactions in such a way that they need not rollback (only do so if the whole system stops). One way that comes to my mind is to accumulate all data in some temporary table, using some other value as ID (or in application variables), and only after the final yes, do it confirmation transfer them to the true tables with the auto-increment ID. In future releases, stored procedures might be another way to ensure all actions are grouped without an intervening parallel rollback. If your concurrency requirements are low and you can stand wait time, you could keep the next ID in an application-controlled table, locked from its retrieval to a final increment at transaction commit; but I agree these low requirements are unusual. As an alternative, a rollback might create a dummy record using that ID which acts as a placeholder, maybe with a remark user rollback or similar - if that is permissible in the application. Might be worth reconsidering whether you really require no gaps. It's generally better to try to design an application not to have that dependency. Paul, while I agree with that preference, I know that sometimes there is no choice. As an example, some German bookkeeping regulation requires you to use dense booking numbers (without gap). So I know of a software project that used _descending_ numbers because they were faster to generate in their environment than ascending ones. (This does not solve the rollback issue, of course.) Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- 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]
Rollback and INSERT_ID() or LAST_INSERT_ID()
Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Just let it increment. Keeping it incremented is MySQL's way ot insuring that the same id doesn't get used twice for different records. It's doing everything correctly. -Eric On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos [EMAIL PROTECTED] wrote: Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Thanks. Andre On 1/17/05 8:14 PM, Eric Bergen [EMAIL PROTECTED] wrote: Just let it increment. Keeping it incremented is MySQL's way ot insuring that the same id doesn't get used twice for different records. It's doing everything correctly. -Eric On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos [EMAIL PROTECTED] wrote: Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
At 21:27 -0500 1/17/05, Andre Matos wrote: Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Using MAX() won't guarantee that you won't have gaps. What you're describing cannot be achieved in the general case. Consider this scenario: - Transaction T1 begins, generates an AUTO_INCREMENT value n. - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. - Transaction T2 commits. - Transaction T1 rolls back. You now have a gap at value n, and a used value of n+1. MAX() at this point returns n+1, not n, so that won't help you reuse n. With more than two transactions running simultaneously, each of which can roll back or commit, the situation becomes more complex. Might be worth reconsidering whether you really require no gaps. It's generally better to try to design an application not to have that dependency. Thanks. Andre On 1/17/05 8:14 PM, Eric Bergen [EMAIL PROTECTED] wrote: Just let it increment. Keeping it incremented is MySQL's way ot insuring that the same id doesn't get used twice for different records. It's doing everything correctly. -Eric On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos [EMAIL PROTECTED] wrote: Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. Andre -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Yes, I know about this. What I plan is to remove the auto_increment. I will do this by hand locking the entire table just right before inserting the field. Actually, it is running like this. I am just upgrading the PHP. I will keep the auto_increment in other tables that does not have this kind of problem. Thanks a lot for your help. Andre On 1/17/05 9:53 PM, Paul DuBois [EMAIL PROTECTED] wrote: At 21:27 -0500 1/17/05, Andre Matos wrote: Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Using MAX() won't guarantee that you won't have gaps. What you're describing cannot be achieved in the general case. Consider this scenario: - Transaction T1 begins, generates an AUTO_INCREMENT value n. - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. - Transaction T2 commits. - Transaction T1 rolls back. You now have a gap at value n, and a used value of n+1. MAX() at this point returns n+1, not n, so that won't help you reuse n. With more than two transactions running simultaneously, each of which can roll back or commit, the situation becomes more complex. Might be worth reconsidering whether you really require no gaps. It's generally better to try to design an application not to have that dependency. Thanks. Andre On 1/17/05 8:14 PM, Eric Bergen [EMAIL PROTECTED] wrote: Just let it increment. Keeping it incremented is MySQL's way ot insuring that the same id doesn't get used twice for different records. It's doing everything correctly. -Eric On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos [EMAIL PROTECTED] wrote: Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id() and load data infile
Hello. As said at: http://dev.mysql.com/doc/mysql/en/Information_functions.html If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. LOAD DATA INFILE inserts several rows a time, so this is a usual behavior of LAST_INSERT_ID(). Martin d'Anjou [EMAIL PROTECTED] wrote: Hi, I am trying to find in the manual what select last_insert_id() will return after a load data infile command is executed. From my experiments, it returns the id of the FIRST record in the file, I would have expected the LAST, but that is not the case. Is that documented somewhere? Can I rely on this behavior to be consistent? Thanks, Martin -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
last_insert_id() and load data infile
Hi, I am trying to find in the manual what select last_insert_id() will return after a load data infile command is executed. From my experiments, it returns the id of the FIRST record in the file, I would have expected the LAST, but that is not the case. Is that documented somewhere? Can I rely on this behavior to be consistent? Thanks, Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DBI and last_insert_id()
You wrote $pk = $dbh-last_insert_id(); I believe you can get the created ID with the query, Select @@IDENTITY; This returns the last created ID from the connector object. Peace -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DBI and last_insert_id()
last_insert_id() should work. You are updating using @bind_values. What is
the text of that? Does it indeed create a record?
God Bless
Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.
GOD BLESS AMERICA!
To God Be The Glory!
-Original Message-
From: Daniel Kasak [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 15, 2004 12:42 AM
To: [EMAIL PROTECTED]
Subject: DBI and last_insert_id()
Hi all.
I'm writing an app in Perl / Gtk2, and I'm having trouble with DBI's
last_insert_id() function.
I have so far:
eval {
$sth-execute (@bind_values) || die $dbh-errstr;
};
# If the above failed, there will be something in the special
variable $@
if ($@) {
# Dialog explaining error...
my $dialog = msgbox(
$prospects-get_widget(Prospects),
Error updating recordset!,
Database Server says:\n . $dbh-errstr,
1
);
$dialog-run;
$dialog-destroy;
warn Error updating recordset:[EMAIL PROTECTED] . $@ .
\n\n;
return 0;
}
$pk = $dbh-last_insert_id();
The statement executes successfully, and the data is inserted. However
the above line that fetches the last_insert_id value from MySQL always
returns undef. The table has an auto_increment column. What's going on?
Dan
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBI and last_insert_id()
Paul McNeil wrote: last_insert_id() should work. You are updating using @bind_values. What is the text of that? Does it indeed create a record? @bind_values is an array of values that gets populated from my form. It has the same number of elements as the number of placeholders in my SQL, and yes, the record is created, and I see the 'insert into' statement appear immediately in the query log. Perl's DBI is supposed to then allow me to use the 'last_insert_id()' function of the database handle: my $inserted_id = $dbh-last_insert_id(); to get the value into $inserted_id. But it doesn't work. I haven't yet tried doing a 'select last_insert_id()' or 'select @@identity' statement, but I suppose I can fall back to that. I was just wondering if I was doing something wrong, but the more I look at it ( and it's a pretty simple script ) the more I think there's something up with DBI. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBI and last_insert_id()
You might have better luck with this on the [EMAIL PROTECTED] list,
re: why this doesn't work. This works for me though:
$pk = $dbh-{mysql_insertid};
On Mon, 2004-06-14 at 21:42, Daniel Kasak wrote:
Hi all.
I'm writing an app in Perl / Gtk2, and I'm having trouble with DBI's
last_insert_id() function.
I have so far:
eval {
$sth-execute (@bind_values) || die $dbh-errstr;
};
# If the above failed, there will be something in the special
variable $@
if ($@) {
# Dialog explaining error...
my $dialog = msgbox(
$prospects-get_widget(Prospects),
Error updating recordset!,
Database Server says:\n . $dbh-errstr,
1
);
$dialog-run;
$dialog-destroy;
warn Error updating recordset:[EMAIL PROTECTED] . $@ .
\n\n;
return 0;
}
$pk = $dbh-last_insert_id();
The statement executes successfully, and the data is inserted. However
the above line that fetches the last_insert_id value from MySQL always
returns undef. The table has an auto_increment column. What's going on?
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBI and last_insert_id()
Garth Webb said:
You might have better luck with this on the [EMAIL PROTECTED] list,
re: why this doesn't work. This works for me though:
$pk = $dbh-{mysql_insertid};
On Mon, 2004-06-14 at 21:42, Daniel Kasak wrote:
Hi all.
I'm writing an app in Perl / Gtk2, and I'm having trouble with DBI's
last_insert_id() function.
I have so far:
eval {
$sth-execute (@bind_values) || die $dbh-errstr;
};
# If the above failed, there will be something in the special
variable $@
if ($@) {
# Dialog explaining error...
my $dialog = msgbox(
$prospects-get_widget(Prospects),
Error updating recordset!,
Database Server says:\n .
$dbh-errstr, 1
);
$dialog-run;
$dialog-destroy;
warn Error updating recordset:[EMAIL PROTECTED] . $@
.
\n\n;
return 0;
}
$pk = $dbh-last_insert_id();
The statement executes successfully, and the data is inserted. However
the above line that fetches the last_insert_id value from MySQL
always returns undef. The table has an auto_increment column. What's
going on?
Dan
($pk) = $dbh-selectrow_array('SELECT LAST_INSERT_ID()');
BTW this also works in java.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DBI and last_insert_id()
Hi all.
I'm writing an app in Perl / Gtk2, and I'm having trouble with DBI's
last_insert_id() function.
I have so far:
eval {
$sth-execute (@bind_values) || die $dbh-errstr;
};
# If the above failed, there will be something in the special
variable $@
if ($@) {
# Dialog explaining error...
my $dialog = msgbox(
$prospects-get_widget(Prospects),
Error updating recordset!,
Database Server says:\n . $dbh-errstr,
1
);
$dialog-run;
$dialog-destroy;
warn Error updating recordset:[EMAIL PROTECTED] . $@ .
\n\n;
return 0;
}
$pk = $dbh-last_insert_id();
The statement executes successfully, and the data is inserted. However
the above line that fetches the last_insert_id value from MySQL always
returns undef. The table has an auto_increment column. What's going on?
Dan
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id() value not updated
paqogomez [EMAIL PROTECTED] wrote: I am trying to build a stored procedure in v. 5. This is what I have so far. delimiter | create procedure get_id(out oid int) begin insert into mercury.merchant (name) values(null); select last_insert_id() into @mid; insert into mercury.customers( address1 ) values (null); select last_insert_id() into @cid; insert into mercury.item_information( description ) values (null); select last_insert_id() into @iiid; insert into mercury.fee_information ( delivery_fee ) values (null); select last_insert_id() into @fiid; insert into mercury.orders ( customer_id, merchant_id, item_information_id, fee_information_id ) values ( @cid, @mid, @iiid, @fiid ); select last_insert_id() into oid; end | My problem is that last_insert_id() is not updated for each insert statement, only on the connection. The documentation says something about using insert ignore, but I couldnt get this to give me any different results. Is there any way to do what I want here and still have the procedure be transaction safe? This issue is related to the known bug. It's already fixed: http://bugs.mysql.com/bug.php?id=3117 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
last_insert_id() value not updated
I am trying to build a stored procedure in v. 5. This is what I have so far. delimiter | create procedure get_id(out oid int) begin insert into mercury.merchant (name) values(null); select last_insert_id() into @mid; insert into mercury.customers( address1 ) values (null); select last_insert_id() into @cid; insert into mercury.item_information( description ) values (null); select last_insert_id() into @iiid; insert into mercury.fee_information ( delivery_fee ) values (null); select last_insert_id() into @fiid; insert into mercury.orders ( customer_id, merchant_id, item_information_id, fee_information_id ) values ( @cid, @mid, @iiid, @fiid ); select last_insert_id() into oid; end | My problem is that last_insert_id() is not updated for each insert statement, only on the connection. The documentation says something about using insert ignore, but I couldnt get this to give me any different results. Is there any way to do what I want here and still have the procedure be transaction safe? TIA Chad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LAST_INSERT_ID() and Stored Procs
Hi all. Another hiccup along the happy road with MySQL 5.0! The last bit of a stored procedure I have just put together does this: -- insert cluster row INSERT INTO clusters (Name) VALUES (sName); SELECT LAST_INSERT_ID() INTO iNewClusterID; -- insert map row INSERT INTO map (X, Y) VALUES (iX,iY); SELECT LAST_INSERT_ID() INTO iNewMapID; -- insert map_clusters row INSERT INTO map_clusters (MapID, ClusterID) VALUES (iNewMapID, iNewClusterID); The last table mentioned, map_clusters has an FK on either column - each pointing to one of the other two tables. The procedure always fails on this insert, citing that there has been an FK violation. I've returned the values of iNewClusterID and iNewMapID out as parameters, and they always seem to be 0. However, I tried this: Create procedure id_test (out id int) Begin Select last_insert_id() into id; End And this correctly returns the last insert_id for the current connection. Most puzzling - I saw a closed bug from March on mysql.com which would have explained this, however, then, the above short procedure would have failed as well! Has anyone out there run into similar troubles? Cheers, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LAST_INSERT_ID() and Stored Procs
Oh. If only I'd tried ONE more thing before mailing that out! If anyone does have the same problem, the vital (missing) piece of information is that I was using MySQLCC. It seems to have problems with SPs unless you open a new query window after changing the contents of a procedure.. Thanks, Matt -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: 23 April 2004 02:08 To: 'MySQL List' Subject: LAST_INSERT_ID() and Stored Procs Hi all. Another hiccup along the happy road with MySQL 5.0! The last bit of a stored procedure I have just put together does this: -- insert cluster row INSERT INTO clusters (Name) VALUES (sName); SELECT LAST_INSERT_ID() INTO iNewClusterID; -- insert map row INSERT INTO map (X, Y) VALUES (iX,iY); SELECT LAST_INSERT_ID() INTO iNewMapID; -- insert map_clusters row INSERT INTO map_clusters (MapID, ClusterID) VALUES (iNewMapID, iNewClusterID); The last table mentioned, map_clusters has an FK on either column - each pointing to one of the other two tables. The procedure always fails on this insert, citing that there has been an FK violation. I've returned the values of iNewClusterID and iNewMapID out as parameters, and they always seem to be 0. However, I tried this: Create procedure id_test (out id int) Begin Select last_insert_id() into id; End And this correctly returns the last insert_id for the current connection. Most puzzling - I saw a closed bug from March on mysql.com which would have explained this, however, then, the above short procedure would have failed as well! Has anyone out there run into similar troubles? Cheers, Matt -- 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]
Changing LAST_INSERT_ID()/AUTO_INCREMENT()
I have a ticketing system that sets the ID of the ticket as the LAST_INSERT_ID. By default, it was counting up from 0. I updated the ID of the last ticket to reflect a different numbering scheme (MM). I would like to automate this but I don't want to change the ID of a current ticket. I was hoping that there was a way to update the LAST_INSERT_ID. Is this possible? If so, what is the query that should be run? Thank You, Jason Williard Janix
Re: Changing LAST_INSERT_ID()/AUTO_INCREMENT()
Hi Jason,
For MyISAM tables only, you can manually set the auto
increment counter using the syntax:
ALTER TABLE table_name AUTO_INCREMENT = 1000
Keep in mind, however, that this does not change the
value of the LAST_INSERT_ID() since it still
represents the value last inserted. On a freshly
created table, that is 0. So if you create a table,
alter the AUTO_INCREMENT value to 1000, until you
insert a record, LAST_INSERT_ID() will return 0. Once
you insert a record, LAST_INSERT_ID() will then return
1000 (or whatever value you used in the ALTER
statement). (See below for an example session).
Another major caveat (also illustrated below) is that
the value returned by LAST_INSERT_ID() is
session/connection specific and therefore cannot be
trusted in the manner you are thinking of, assuming
you plan to create your ticket number using a syntax
similar to:
INSERT INTO tickets SET ticket_num =
CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y%m'),
LAST_INSERT_ID());
Lastly, you will not be able to use the ALTER TABLE
table_name AUTO_INCREMENT = 1000 command to reset the
ticket number to a starting value each month (if that
was your intent).
There are a lot of little gotchas when using
AUTO_INCFREMENT and especially LAST_INSERT_ID() for
anything other than a simple incremental counter. You
may have to get rather sophisticated in the way you
solve your problem; someone else on the form may have
some ideas from experience. Nevertheless, whatever
your solution ends up being, I highly recommend you
test the heck out it, and make sure you test with
concurrent connections/sessions.
Hope that helps :)
Good Luck,
Mark
===START EXAMPLE===
~From Session 1~
mysql CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
mysql use testdb;
Database changed
mysql CREATE TABLE table01(id SMALLINT(4) UNSIGNED
ZEROFILL NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),
data VARCHAR(10));
Query OK, 0 rows affected (0.03 sec)
mysql SELECT LAST_INSERT_ID();
+--+
| last_insert_id() |
+--+
|0 |
+--+
1 row in set (0.00 sec)
mysql ALTER TABLE table01 AUTO_INCREMENT = 1000;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql SELECT LAST_INSERT_ID();
+--+
| last_insert_id() |
+--+
|0 |
+--+
1 row in set (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM table01;
+--+---+
| id | data |
+--+---+
| 1000 | last=0|
| 1001 | last=1000 |
+--+---+
2 rows in set (0.00 sec)
~From Session 2~
mysql use testdb;
Database changed
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM table01;
+--+---+
| id | data |
+--+---+
| 1000 | last=0|
| 1001 | last=1000 |
| 1002 | last=0|
| 1003 | last=1002 |
+--+---+
4 rows in set (0.00 sec)
~Back to Session 1~
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM table01;
+--+---+
| id | data |
+--+---+
| 1000 | last=0|
| 1001 | last=1000 |
| 1002 | last=0|
| 1003 | last=1002 |
| 1004 | last=1001 |
| 1005 | last=1004 |
| 1006 | last=1005 |
+--+---+
7 rows in set (0.00 sec)
Notice the flux in the last value due to the reasons
described above.
===END EXAMPLE===
--- Jason Williard [EMAIL PROTECTED] wrote:
I have a ticketing system that sets the ID of the
ticket as the
LAST_INSERT_ID. By default, it was counting up from
0. I updated the
ID of the last ticket to reflect a different
numbering scheme
(MM). I would like to automate this but I
don't want to change
the ID of a current ticket. I was hoping that there
was a way to update
the LAST_INSERT_ID. Is this possible? If so, what
is the query that
should be run?
Thank You,
Jason Williard
Janix
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing LAST_INSERT_ID()/AUTO_INCREMENT()
Thanks, Mark, for the syntax. I had actually played around enough to
figure it out already and got it to work the way that I wanted to. I
guess when I asked the question, I wasn't fully aware of what I was
wanting. The LAST_INSERT_ID wasn't a major issue. I ended up using the
following code:
$new_increment = date('Ym') * 1 + 1;
$query = mysql_query( ALTER TABLE `$calls` AUTO_INCREMENT =
$new_increment );
if ( !$query ) {
die( Couldn't Alter Table! );
}
else{
echo Successfully updated AUTO_INCREMENT: $new_increment\n;
}
After a few tests, it seems to be working properly. I've gone ahead and
setup a cron job to take care of this once a month.
Thanks again,
Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
last_insert_id()
Hi, Is it possible to obtain the last_insert_id() for a particular column in a particular table? eg, say i wanted to obtain the last insert id of a column called id in table reference, something along the lines of: last_insert_id(reference.id) The reason I ask is because I want to initially insert values into two tables and then insert values into a third using the last_insert_id() from the first two tables. Obviously, the last_insert_id from the first insert is replaced by the last_insert_id from the second insert. This is all done in a perl script and I could store the first last_insert_id in a variable but I thought their might be a more elegant way round it? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id()
R.Dobson said: Hi, Is it possible to obtain the last_insert_id() for a particular column in a particular table? eg, say i wanted to obtain the last insert id of a column called id in table reference, something along the lines of: last_insert_id(reference.id) The reason I ask is because I want to initially insert values into two tables and then insert values into a third using the last_insert_id() from the first two tables. Obviously, the last_insert_id from the first insert is replaced by the last_insert_id from the second insert. This is all done in a perl script and I could store the first last_insert_id in a variable but I thought their might be a more elegant way round it? tia Rich last_insert_id is connection specific and contains the value of the auto-increment column for the last record you inserted using that connection. The sequence is - do insert of record with auto-increment column - get value of last_insterted_id (i.e., the value asigned to the auto-increment column to that record). There is only one column so there is no need for figuring out which column. If you do two inserts you must get the last_inserted_id BETWEEN the inserts. If the second table also has a auto-increment column you will have to get its value after that insert. Hope this helps. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id()
Don't forget that if you commit then last_insert_id will return 0 - Original Message - From: William R. Mussatto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 6:56 PM Subject: Re: last_insert_id() : R.Dobson said: : Hi, : : Is it possible to obtain the last_insert_id() for a particular column in : a particular table? : eg, say i wanted to obtain the last insert id of a column called id in : table reference, something along the lines of: : : last_insert_id(reference.id) : : The reason I ask is because I want to initially insert values into two : tables and then insert values into a third using the last_insert_id() : from the first two tables. Obviously, the last_insert_id from the first : insert is replaced by the last_insert_id from the second insert. This is : all done in a perl script and I could store the first last_insert_id in : a variable but I thought their might be a more elegant way round it? : : tia : Rich : last_insert_id is connection specific and contains the value of the : auto-increment column for the last record you inserted using that : connection. The sequence is : - do insert of record with auto-increment column : - get value of last_insterted_id (i.e., the value asigned to the : auto-increment column to that record). There is only one column so there : is no need for figuring out which column. : : If you do two inserts you must get the last_inserted_id BETWEEN the : inserts. If the second table also has a auto-increment column you will : have to get its value after that insert. : : Hope this helps. : : William R. Mussatto, Senior Systems Engineer : Ph. 909-920-9154 ext. 27 : FAX. 909-608-7061 : : : : -- : 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]
Re: LAST_INSERT_ID() returns different values on different connections
At 16:02 +0300 6/17/03, Baris Akin wrote: Hello, I try to get last inserted autoincrement record ID on table with LAST_INSERT_ID() function (SELECT LAST_INSERT_ID() FROM TABLE). Every connection returns it's own last inserted ID not actual ID. Is this a bug? Also it returns more than one record (20 rows). It's supposed to return the last ID generated on that connection. (I'm not sure what you mean by actual ID, because I would expect the connection's last ID and actual ID to mean the same thing.) The reason you get back multiple records is because you're using SELECT LAST_INSERT_ID() FROM TABLE rather than SELECT LAST_INSERT_ID() What's happening with your query is that you're returning the ID once per row in your table. Drop the FROM TABLE in your query and you'll get just one row back. Thanks Baris AKIN Istanbul/TURKEY -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LAST_INSERT_ID() returns different values on different connections
Hello, I try to get last inserted autoincrement record ID on table with LAST_INSERT_ID() function (SELECT LAST_INSERT_ID() FROM TABLE). Every connection returns it's own last inserted ID not actual ID. Is this a bug? Also it returns more than one record (20 rows). Thanks Baris AKIN Istanbul/TURKEY
Re: LAST_INSERT_ID() returns different values on different connections
On Tue, Jun 17, 2003 at 04:02:46PM +0300, Baris Akin wrote: I try to get last inserted autoincrement record ID on table with LAST_INSERT_ID() function (SELECT LAST_INSERT_ID() FROM TABLE). Every connection returns it's own last inserted ID not actual ID. Is this a bug? Also it returns more than one record (20 rows). It's not a bug. LAST_INSERT_ID is meant to return the last inserted ID per connection, NOT per table and NOT per connection per table. The 'FROM TABLE' above is therefore redundant. If LAST_INSERT_ID wasn't kept per connection, it would be useless in multi-user cases. It gives you information on what you just inserted, not what someone else did with another connection. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#IDX1362 Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LAST_INSERT_ID() returns different values on different connections
Baris Akin [EMAIL PROTECTED] wrote: LAST_INSERT_ID() function (SELECT LAST_INSERT_ID() FROM TABLE). Every connection returns it's own last inserted ID not actual ID. Is this a bug? No. LAST_INSERT_ID() works per-connection. It returns the last inserted auto_increment value from current connection. Look at the manual how this function works: http://www.mysql.com/doc/en/Miscellaneous_functions.html Also it returns more than one record (20 rows). It should returns one value, if it returns more the one value, show me an example. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Autoincrement/last_insert_id race safe?
A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? It seems that another program could be inserting at almost the same time, and could increment the counter again before my last_insert_id() checks it. In that case, I would not be dealing with the same line I just inserted. Does anyone know if this is a genuine concern? If it is, can anyone think of a workaround? Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
Hi, On Wed, Mar 26, 2003 at 03:17:42PM +0100, Steve Rapaport wrote: A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? Yes. The last inserted id is kept per connection. So unless YOU insert another record using the same connection, the last_insert_id isn't changed. Also, last_insert_id in a new connection will give you NULL, regardless of what you did in a previous connection. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? Yes It seems that another program could be inserting at almost the same time, and could increment the counter again before my last_insert_id() checks it. In that case, I would not be dealing with the same line I just inserted. last_insert_id is stored on a per-connection basis, and frecords the last insert done by that connection -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoincrement/last_insert_id race safe?
On Wednesday 26 March 2003 08:25, you wrote: A programmer just asked me about a possible race condition, and I didn't know what to answer: If I insert a line using autoincrement, then ask for last_insert_id() am I guaranteed to get the same ID I just inserted? Yes It seems that another program could be inserting at almost the same time, and could increment the counter again before my last_insert_id() checks it. In that case, I would not be dealing with the same line I just inserted. last_insert_id is stored on a per-connection basis, and frecords the last insert done by that connection so, if your using a connection pooling system then a race condition can happen, but its only due to the connections being shared. anyone happen to know if coldfusion's use of odbc locks the connection per page request or does it just do it on queries and unlocks it as soon as the query is done? -- sql sql sql mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Bug: last_insert_id() not replicated correctly
On Tuesday 18 March 2003 17:20, Chris Wilson wrote: Using mysql 2.23.54a as both master slave: ** On master: mysql CREATE DATABASE repl_test; Query OK, 1 row affected (0.03 sec) mysql USE repl_test; Database changed mysql CREATE TABLE test ( - a INT UNSIGNED AUTO_INCREMENT NOT NULL , - b INT UNSIGNED NOT NULL, - PRIMARY KEY (a) - ); Query OK, 0 rows affected (0.02 sec) mysql INSERT INTO test (b) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql INSERT INTO test (b) VALUES (LAST_INSERT_ID()); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO test (b) VALUES (LAST_INSERT_ID()); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM test; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 2 | +---+---+ 3 rows in set (0.00 sec) ** On slave: mysql USE repl_test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql SELECT * FROM test; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +---+---+ 3 rows in set (0.00 sec) Looking at the binlog it appears that the problem is on the master and that LAST_INSERT_ID gets set to the same value as INSERT_ID rather than the previous value (ie this problem only affects inserts that are inserting into tables with auto increment columns). Seems, it's already fixed. I tested on 3.23.56 and ypur example worked fine. I got the following result on both boxes. mysql SELECT * FROM test; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 2 | +---+---+ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Bug: last_insert_id() not replicated correctly
Hi all! Using mysql 2.23.54a as both master slave: ** On master: mysql CREATE DATABASE repl_test; Query OK, 1 row affected (0.03 sec) mysql USE repl_test; Database changed mysql CREATE TABLE test ( - a INT UNSIGNED AUTO_INCREMENT NOT NULL , - b INT UNSIGNED NOT NULL, - PRIMARY KEY (a) - ); Query OK, 0 rows affected (0.02 sec) mysql INSERT INTO test (b) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql INSERT INTO test (b) VALUES (LAST_INSERT_ID()); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO test (b) VALUES (LAST_INSERT_ID()); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM test; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 2 | +---+---+ 3 rows in set (0.00 sec) ** On slave: mysql USE repl_test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql SELECT * FROM test; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +---+---+ 3 rows in set (0.00 sec) Looking at the binlog it appears that the problem is on the master and that LAST_INSERT_ID gets set to the same value as INSERT_ID rather than the previous value (ie this problem only affects inserts that are inserting into tables with auto increment columns). Relevant bit of binlog is: # at 472606546 #030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0 error_code=0 use repl_test; SET TIMESTAMP=1047548285; CREATE TABLE test ( a INT UNSIGNED AUTO_INCREMENT NOT NULL , b INT UNSIGNED NOT NULL, PRIMARY KEY (a) ); # at 472606683 #030313 9:38:05 server id 101 Intvar SET INSERT_ID = 1; # at 472606705 #030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0 error_code=0 SET TIMESTAMP=1047548285; INSERT INTO test (b) VALUES (1); # at 472606770 #030313 9:38:05 server id 101 Intvar SET LAST_INSERT_ID = 2; # at 472606792 #030313 9:38:05 server id 101 Intvar SET INSERT_ID = 2; # at 472606814 #030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0 error_code=0 SET TIMESTAMP=1047548285; INSERT INTO test (b) VALUES (LAST_INSERT_ID()); # at 472606894 #030313 9:38:06 server id 101 Intvar SET LAST_INSERT_ID = 3; # at 472606916 #030313 9:38:06 server id 101 Intvar SET INSERT_ID = 3; # at 472606938 #030313 9:38:06 server id 101 Query thread_id=5122 exec_time=0 error_code=0 SET TIMESTAMP=1047548286; INSERT INTO test (b) VALUES (LAST_INSERT_ID()); # at 472606546 #030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0 error_code=0 use repl_test; SET TIMESTAMP=1047548285; CREATE TABLE test ( a INT UNSIGNED AUTO_INCREMENT NOT NULL , b INT UNSIGNED NOT NULL, PRIMARY KEY (a) ); # at 472606683 #030313 9:38:05 server id 101 Intvar SET INSERT_ID = 1; # at 472606705 #030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0 error_code=0 SET TIMESTAMP=1047548285; INSERT INTO test (b) VALUES (1); # at 472606770 #030313 9:38:05 server id 101 Intvar SET LAST_INSERT_ID = 2; # at 472606792 #030313 9:38:05 server id 101 Intvar SET INSERT_ID = 2; # at 472606814 #030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0 error_code=0 SET TIMESTAMP=1047548285; INSERT INTO test (b) VALUES (LAST_INSERT_ID()); # at 472606894 #030313 9:38:06 server id 101 Intvar SET LAST_INSERT_ID = 3; # at 472606916 #030313 9:38:06 server id 101 Intvar SET INSERT_ID = 3; # at 472606938 #030313 9:38:06 server id 101 Query thread_id=5122 exec_time=0 error_code=0 SET TIMESTAMP=1047548286; INSERT INTO test (b) VALUES (LAST_INSERT_ID()); Let me know if any more info needed! Regards, Chris - 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: Connector J (3.1.0) - invalid return upon select last_insert_id()
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tea Yu wrote: Not really, I substituted the queryStmt with the actual one but it gave the same result. After some work: select last_insert_id();//getInt() should return 14 //but now it gives java.sql.SQLException: Invalid value for getInt() - 'qt' at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1348) where thisRow([0]) that ResultSet holds was = (49, 52) I haven't looked deeper into ResultSet and StringUtils... Is this as expected? My platform is WinME and my JRE is 1.4.1_01 thanks tea The only way I can debug this is if you give me a repeatable test case. Since the test case I showed you does not repeat the bug, you will need to generate a standalone test case, with schema, data, and the java code that demonstrates the issue. -Mark Hey mark, here are the files that repeats my test. P.S. Test.class was compiled by J2SDK1.4.1_01 on WinME, just in case you need to compare this with your compiled ver. JDBCDriver used was mysql-connector-java-3.1.0-alpha-bin.jar. Thanks Tea I actually found what was causing this earlier today. It is an odd case that only happens when your JVM's default encoding is not single-byte (so I never saw it in my testsuites). It is fixed in the nightly snapshots of both Connector/J 3.0.x and 3.1.x that will be up by tomorrow (the compile and upload happens at 00:00 GMT), see http://mmmysql.sourceforge.net/snapshots/. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+Yr5gtvXNTca6JD8RAlBQAKCd+op/ojys+Gf9ZuOZz22jUfl6YgCfStul MB8f3v64KjADyGs9TqdRrBk= =4Eu5 -END PGP SIGNATURE- - 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: Connector J (3.1.0) - invalid return upon select last_insert_id()
The only way I can debug this is if you give me a repeatable test case. Since the test case I showed you does not repeat the bug, you will need to generate a standalone test case, with schema, data, and the java code that demonstrates the issue. -Mark Hey mark, here are the files that repeats my test. P.S. Test.class was compiled by J2SDK1.4.1_01 on WinME, just in case you need to compare this with your compiled ver. JDBCDriver used was mysql-connector-java-3.1.0-alpha-bin.jar. Thanks Tea I actually found what was causing this earlier today. It is an odd case that only happens when your JVM's default encoding is not single-byte (so I never saw it in my testsuites). It is fixed in the nightly snapshots of both Connector/J 3.0.x and 3.1.x that will be up by tomorrow (the compile and upload happens at 00:00 GMT), see http://mmmysql.sourceforge.net/snapshots/. -Mark Hey, you're a lifesaver! I look forward to the new drivers! Thanks and regards Tea - 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: Connector J (3.1.0) - invalid return upon select last_insert_id()
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tea Yu wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tea Yu wrote:
Hi there!
Sorry, cause I didn't copy-n-paste those so there were typos, actually I
created the table in console:
1) create table test (id INTEGER NOT NULL AUTO_INCREMENT, name
VARCHAR(255),
primary key (id));
2) here is the code segment
String insertStmt = insert into test (name) values ('hi');
String queryStmt = select last_insert_id();
try {
stmt.executeUpdate(insertStmt);
ResultSet rs = stmt.executeQuery(queryStmt);
if(rs.next())
System.out.println(last insert id: + rs.getInt(1));
} catch (SQLException e) {
System.out.println(e);
}
if
a) I comment out the rs.getInt(1) line, no exception throws... else it
throws something like
java.sql.SQLException: Invalid value for getInt() - 't'
b) if I use getString(1) instead, no exception throws...
but it returns t, u or something
3) I tried to insert records thru MySQLCC without errors.
More thoughts?
Regards
Tea
The only thing I can guess (because my testsuite is really the same
code, I just don't store the queries in variables), is that 'queryStmt'
does not hold the query you think it does. Try inspecting it in a
debugger or printing it out before you execute the query, to see what
query you are really executing.
-mark
Not really, I substituted the queryStmt with the actual one but it gave the
same result.
After some work:
select last_insert_id();//getInt() should return 14
//but now it gives
java.sql.SQLException: Invalid value for getInt() - 'qt'
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1348)
where thisRow([0]) that ResultSet holds was = (49, 52)
I haven't looked deeper into ResultSet and StringUtils... Is this as
expected?
My platform is WinME and my JRE is 1.4.1_01
thanks
tea
The only way I can debug this is if you give me a repeatable test case.
Since the test case I showed you does not repeat the bug, you will need
to generate a standalone test case, with schema, data, and the java code
that demonstrates the issue.
-Mark
- --
MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
___/ www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE+YKwMtvXNTca6JD8RAgyPAKCFkk9RmrJ0y/P3qm/e7uXdYs1OhwCfZdHN
V3Nhbe7Nv/YveuN84OEsZHQ=
=EMfY
-END PGP SIGNATURE-
-
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: Connector J (3.1.0) - invalid return upon select last_insert_id()
Hi! I just tried
Windows ME + MySQL Server - 4.0.9/4.0.11 +
ConnectorJ - 3.0.4/3.0.6/3.1.0
with an InnoDB table
test (id bigint not null auto_increment, name varchar(255))
also tried
test (id int not null auto_increment, name varchar(255))
and did an SQLQuery thru JDBC
insert into TABLE (name) values ('test');
select last_insert_id() from TABLE;
but got the Exception
java.sql.SQLException: Invalid value for getInt() - 'q'
while successive running of program returns
'r', 's', 't'...
Query from console returns the correct result, but it returns n rows in set
if last_insert_id() is n
e.g. if select last_insert_id() = 7, 7 rows are returned in set
Is there a bug in ConnectorJ regarding this... seems nobody else is having
such a problem...?
Best regards
Tea
-
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: Connector J (3.1.0) - invalid return upon select last_insert_id()
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tea Yu wrote:
Hi! I just tried
Windows ME + MySQL Server - 4.0.9/4.0.11 +
ConnectorJ - 3.0.4/3.0.6/3.1.0
with an InnoDB table
test (id bigint not null auto_increment, name varchar(255))
also tried
test (id int not null auto_increment, name varchar(255))
and did an SQLQuery thru JDBC
insert into TABLE (name) values ('test');
select last_insert_id() from TABLE;
but got the Exception
java.sql.SQLException: Invalid value for getInt() - 'q'
while successive running of program returns
'r', 's', 't'...
Query from console returns the correct result, but it returns n rows in set
if last_insert_id() is n
e.g. if select last_insert_id() = 7, 7 rows are returned in set
Is there a bug in ConnectorJ regarding this... seems nobody else is having
such a problem...?
I can't reproduce this error with the following test:
public void testLastInsertId() throws Exception {
try {
stmt.executeUpdate(DROP TABLE IF EXISTS lastInsertTest);
stmt.executeUpdate(CREATE TABLE lastInsertTest (id bigint not
null
auto_increment primary key, name varchar(255)));
stmt.executeUpdate(INSERT INTO lastInsertTest (name) values
('test'));
rs = stmt.executeQuery(SELECT LAST_INSERT_ID() FROM
lastInsertTest);
rs.next();
rs.getInt(1);
} finally {
stmt.executeUpdate(DROP TABLE IF EXISTS lastInsertTest);
}
}
However, your SQL is a little funny, for example you can't create a
table using the SQL you give, as AUTO_INCREMENT columns also have to be
the primary key. Also, you call the table 'test', but then refer to it
as 'TABLE' when doing your SELECT_LAST_INSERT_ID()...so hopefully these
are cut-and-paste errors, otherwise it seems you are not creating the
queries you think you are.
- --
MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
___/ www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE+X2wntvXNTca6JD8RApMYAJ4sHFs2ClyJAMUiUcqs0F9nkzS8WQCgvqfO
wK/abVjxu31u8Uyveq1gk48=
=VBbn
-END PGP SIGNATURE-
-
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: Connector J (3.1.0) - invalid return upon select last_insert_id()
You don't select last_insert_id() from table.
This will return the last_insert_id() for each row of the table.
You just select last_insert_id().
Tea Yu wrote:
Hi! I just tried
Windows ME + MySQL Server - 4.0.9/4.0.11 +
ConnectorJ - 3.0.4/3.0.6/3.1.0
with an InnoDB table
test (id bigint not null auto_increment, name varchar(255))
also tried
test (id int not null auto_increment, name varchar(255))
and did an SQLQuery thru JDBC
insert into TABLE (name) values ('test');
select last_insert_id() from TABLE;
but got the Exception
java.sql.SQLException: Invalid value for getInt() - 'q'
while successive running of program returns
'r', 's', 't'...
Query from console returns the correct result, but it returns n rows in set
if last_insert_id() is n
e.g. if select last_insert_id() = 7, 7 rows are returned in set
Is there a bug in ConnectorJ regarding this... seems nobody else is having
such a problem...?
Best regards
Tea
-
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: Connector J (3.1.0) - invalid return upon select last_insert_id()
Hi there!
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tea Yu wrote:
Hi! I just tried
Windows ME + MySQL Server - 4.0.9/4.0.11 +
ConnectorJ - 3.0.4/3.0.6/3.1.0
with an InnoDB table
test (id bigint not null auto_increment, name varchar(255))
also tried
test (id int not null auto_increment, name varchar(255))
and did an SQLQuery thru JDBC
insert into TABLE (name) values ('test');
select last_insert_id() from TABLE;
but got the Exception
java.sql.SQLException: Invalid value for getInt() - 'q'
while successive running of program returns
'r', 's', 't'...
Query from console returns the correct result, but it returns n rows in
set
if last_insert_id() is n
e.g. if select last_insert_id() = 7, 7 rows are returned in set
Is there a bug in ConnectorJ regarding this... seems nobody else is
having
such a problem...?
I can't reproduce this error with the following test:
public void testLastInsertId() throws Exception {
try {
stmt.executeUpdate(DROP TABLE IF EXISTS lastInsertTest);
stmt.executeUpdate(CREATE TABLE lastInsertTest (id bigint not null
auto_increment primary key, name varchar(255)));
stmt.executeUpdate(INSERT INTO lastInsertTest (name) values ('test'));
rs = stmt.executeQuery(SELECT LAST_INSERT_ID() FROM lastInsertTest);
rs.next();
rs.getInt(1);
} finally {
stmt.executeUpdate(DROP TABLE IF EXISTS lastInsertTest);
}
}
However, your SQL is a little funny, for example you can't create a
table using the SQL you give, as AUTO_INCREMENT columns also have to be
the primary key. Also, you call the table 'test', but then refer to it
as 'TABLE' when doing your SELECT_LAST_INSERT_ID()...so hopefully these
are cut-and-paste errors, otherwise it seems you are not creating the
queries you think you are.
Sorry, cause I didn't copy-n-paste those so there were typos, actually I
created the table in console:
1) create table test (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(255),
primary key (id));
2) here is the code segment
String insertStmt = insert into test (name) values ('hi');
String queryStmt = select last_insert_id();
try {
stmt.executeUpdate(insertStmt);
ResultSet rs = stmt.executeQuery(queryStmt);
if(rs.next())
System.out.println(last insert id: + rs.getInt(1));
} catch (SQLException e) {
System.out.println(e);
}
if
a) I comment out the rs.getInt(1) line, no exception throws... else it
throws something like
java.sql.SQLException: Invalid value for getInt() - 't'
b) if I use getString(1) instead, no exception throws...
but it returns t, u or something
3) I tried to insert records thru MySQLCC without errors.
More thoughts?
Regards
Tea
-
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: Connector J (3.1.0) - invalid return upon select last_insert_id()
Yea thanks for correct this, so I'm getting the right resultSet in
console...
but still having problem thru ConnectorJ, select last_insert_id() returns
a String to me, rs.getString(1) throws no exception.
Tea
You don't select last_insert_id() from table.
This will return the last_insert_id() for each row of the table.
You just select last_insert_id().
Tea Yu wrote:
Hi! I just tried
Windows ME + MySQL Server - 4.0.9/4.0.11 +
ConnectorJ - 3.0.4/3.0.6/3.1.0
with an InnoDB table
test (id bigint not null auto_increment, name varchar(255))
also tried
test (id int not null auto_increment, name varchar(255))
and did an SQLQuery thru JDBC
insert into TABLE (name) values ('test');
select last_insert_id() from TABLE;
but got the Exception
java.sql.SQLException: Invalid value for getInt() - 'q'
while successive running of program returns
'r', 's', 't'...
Query from console returns the correct result, but it returns n rows in
set
if last_insert_id() is n
e.g. if select last_insert_id() = 7, 7 rows are returned in set
Is there a bug in ConnectorJ regarding this... seems nobody else is
having
such a problem...?
Best regards
Tea
-
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: Connector J (3.1.0) - invalid return upon select last_insert_id()
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tea Yu wrote:
Hi there!
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tea Yu wrote:
Hi! I just tried
Windows ME + MySQL Server - 4.0.9/4.0.11 +
ConnectorJ - 3.0.4/3.0.6/3.1.0
with an InnoDB table
test (id bigint not null auto_increment, name varchar(255))
also tried
test (id int not null auto_increment, name varchar(255))
and did an SQLQuery thru JDBC
insert into TABLE (name) values ('test');
select last_insert_id() from TABLE;
but got the Exception
java.sql.SQLException: Invalid value for getInt() - 'q'
while successive running of program returns
'r', 's', 't'...
Query from console returns the correct result, but it returns n rows in
set
if last_insert_id() is n
e.g. if select last_insert_id() = 7, 7 rows are returned in set
Is there a bug in ConnectorJ regarding this... seems nobody else is
having
such a problem...?
I can't reproduce this error with the following test:
public void testLastInsertId() throws Exception {
try {
stmt.executeUpdate(DROP TABLE IF EXISTS lastInsertTest);
stmt.executeUpdate(CREATE TABLE lastInsertTest (id bigint not null
auto_increment primary key, name varchar(255)));
stmt.executeUpdate(INSERT INTO lastInsertTest (name) values ('test'));
rs = stmt.executeQuery(SELECT LAST_INSERT_ID() FROM lastInsertTest);
rs.next();
rs.getInt(1);
} finally {
stmt.executeUpdate(DROP TABLE IF EXISTS lastInsertTest);
}
}
However, your SQL is a little funny, for example you can't create a
table using the SQL you give, as AUTO_INCREMENT columns also have to be
the primary key. Also, you call the table 'test', but then refer to it
as 'TABLE' when doing your SELECT_LAST_INSERT_ID()...so hopefully these
are cut-and-paste errors, otherwise it seems you are not creating the
queries you think you are.
Sorry, cause I didn't copy-n-paste those so there were typos, actually I
created the table in console:
1) create table test (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(255),
primary key (id));
2) here is the code segment
String insertStmt = insert into test (name) values ('hi');
String queryStmt = select last_insert_id();
try {
stmt.executeUpdate(insertStmt);
ResultSet rs = stmt.executeQuery(queryStmt);
if(rs.next())
System.out.println(last insert id: + rs.getInt(1));
} catch (SQLException e) {
System.out.println(e);
}
if
a) I comment out the rs.getInt(1) line, no exception throws... else it
throws something like
java.sql.SQLException: Invalid value for getInt() - 't'
b) if I use getString(1) instead, no exception throws...
but it returns t, u or something
3) I tried to insert records thru MySQLCC without errors.
More thoughts?
Regards
Tea
The only thing I can guess (because my testsuite is really the same
code, I just don't store the queries in variables), is that 'queryStmt'
does not hold the query you think it does. Try inspecting it in a
debugger or printing it out before you execute the query, to see what
query you are really executing.
-mark
- --
MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
___/ www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE+X4FptvXNTca6JD8RAmbvAKCSP6jENF9/DlJQrdTulDmC6A5BsACeOVGS
dobDs9BgnVxc2zTxSV18B9k=
=x4ct
-END PGP SIGNATURE-
-
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: Connector J (3.1.0) - invalid return upon select last_insert_id()
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tea Yu wrote:
Hi there!
Sorry, cause I didn't copy-n-paste those so there were typos, actually I
created the table in console:
1) create table test (id INTEGER NOT NULL AUTO_INCREMENT, name
VARCHAR(255),
primary key (id));
2) here is the code segment
String insertStmt = insert into test (name) values ('hi');
String queryStmt = select last_insert_id();
try {
stmt.executeUpdate(insertStmt);
ResultSet rs = stmt.executeQuery(queryStmt);
if(rs.next())
System.out.println(last insert id: + rs.getInt(1));
} catch (SQLException e) {
System.out.println(e);
}
if
a) I comment out the rs.getInt(1) line, no exception throws... else it
throws something like
java.sql.SQLException: Invalid value for getInt() - 't'
b) if I use getString(1) instead, no exception throws...
but it returns t, u or something
3) I tried to insert records thru MySQLCC without errors.
More thoughts?
Regards
Tea
The only thing I can guess (because my testsuite is really the same
code, I just don't store the queries in variables), is that 'queryStmt'
does not hold the query you think it does. Try inspecting it in a
debugger or printing it out before you execute the query, to see what
query you are really executing.
-mark
Not really, I substituted the queryStmt with the actual one but it gave the
same result.
After some work:
select last_insert_id();//getInt() should return 14
//but now it gives
java.sql.SQLException: Invalid value for getInt() - 'qt'
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1348)
where thisRow([0]) that ResultSet holds was = (49, 52)
I haven't looked deeper into ResultSet and StringUtils... Is this as
expected?
My platform is WinME and my JRE is 1.4.1_01
thanks
tea
-
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
last_insert_id() returns 0 in windows
Okay, I've seen just about every question on last_insert_id(), except this one: I am running MySQL on Win XP and when I generate a test table (test) with an AUTO_INCREMENT column (aid) and a second column (a) then use an insert statement like: INSERT INTO test (a) values (1); then: SELECT LAST_INSERT_ID(); I get a return value of 0. I considered the fact that maybe my connection is closing, but when I create the same table on a remote Linux server it returns the proper AUTO_INCREMENT ID. Is there a server variable I need to set or something that I should be looking for in order to make this work on Windows? Al Kearns, Sales Representative/WebMaster, MCP [EMAIL PROTECTED] 1-866-858-9200 - 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: last_insert_id() returns 0 in windows
In the last episode (Feb 01), Alan said: Okay, I've seen just about every question on last_insert_id(), except this one: I am running MySQL on Win XP and when I generate a test table (test) with an AUTO_INCREMENT column (aid) and a second column (a) then use an insert statement like: INSERT INTO test (a) values (1); then: SELECT LAST_INSERT_ID(); I get a return value of 0. I considered the fact that maybe my connection is closing, but when I create the same Are you running these commands from the mysql CLI prompt, or are you using some other tool? If you aren't using the CLI, what does the query SELECT CONNECTION_ID() return just before your insert and just after your SELECT LAST_INSERT_ID() ? If they are different, your tool is probably opening a new connection for every command you send, and you need to use a different tool :) -- 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
Re: Re: last_insert_id() returns 0 in windows
Could it be that the table types are different? the innodb table types seem to ignore auto_increment while MyISAM are more accommodating... so it might be that your table on Linux is MyISAM type while the table on XP is Innodb type. --- Alan [EMAIL PROTECTED] wrote: Okay, I've seen just about every question on last_insert_id(), except this one: I am running MySQL on Win XP and when I generate a test table (test) with an AUTO_INCREMENT column (aid) and a second column (a) then use an insert statement like: INSERT INTO test (a) values (1); then: SELECT LAST_INSERT_ID(); I get a return value of 0. I considered the fact that maybe my connection is closing, but when I create the same table on a remote Linux server it returns the proper AUTO_INCREMENT ID. Is there a server variable I need to set or something that I should be looking for in order to make this work on Windows? Al Kearns, Sales Representative/WebMaster, MCP [EMAIL PROTECTED] 1-866-858-9200 - 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 __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - 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: Accessing last_insert_id problem.
LAST_INSERT_ID is held for the database connection, not agaist the server So, as long as you do not do another insert using the same database connection, LAST_INSERT_ID will be fine. (for database connection, $dbh=DBI-connect. ) No table locking required. Regards M On Wed, 18 Dec 2002, Jeff Snoxell wrote: Date: Wed, 18 Dec 2002 12:21:14 + From: Jeff Snoxell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Accessing last_insert_id problem. Hi, I'm adding records to a db using the Perl DBI. Subsequent to adding a record I need to know the value of the auto-incrementing 'Ref' field so that I can place a copy of the relavent details into a log file. I could query for the LAST_INSERT_ID but what if another process has added another record in the interim? Is this a job for table locking? Many thanks, Jeff - 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 -- Matthew Smith Nominet UK - 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: Accessing last_insert_id problem.
Jeff Snoxell wrote: At 09:46 19/12/02 -0500, you wrote: Jeff Snoxell wrote: Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table Are you using InnoDB tables? You'll have to do something akin to ALTER TABLE my_table AUTO_INCREMENT=1 ... at least according to Paul ... :) No, I'm using MyISAM I believe. Jeff If you want to set the ID back to zero, then I assume you are deleteing all of the records in the table. If so, why not simply drop the table and recreate it? Seems to work for me as the session below demonstrates. If you want to do something else, you better ask again so we can answer you real question. clip mysql create table test ( id int auto_increment, d int, primary key (id) ); Query OK, 0 rows affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql select * from test; ++--+ | id | d| ++--+ | 1 |2 | | 2 |2 | | 3 |2 | ++--+ 3 rows in set (0.00 sec) mysql drop table test; Query OK, 0 rows affected (0.00 sec) mysql create table test ( id int auto_increment, d int, primary key (id) ); Query OK, 0 rows affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.01 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql select * from test; ++--+ | id | d| ++--+ | 1 |2 | | 2 |2 | | 3 |2 | ++--+ 3 rows in set (0.00 sec) mysql = end clip = -- Will Will Merrell Virtual Assistant [EMAIL PROTECTED] Moreland Business Solutions - Your partner in business. http://www.morelandsolutions.com - 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: Accessing last_insert_id problem.
I'm working with MySQL 3.23.36 so, according to MySQL, Paul Dubois, New Riders running the query: DELETE FROM my_table_name should reset the auto-increment value... but it doesnt'. What SQL do I use to reset the val. You can't believe anything that book says. The author didn't even include anything about TRUNCATE TABLE my_table_name. Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table then: INSERT INTO my_table SET Name='Jeff' then: SELECT * FROM my_table and lo-and-behold, the 'Ref' primary key auto-update field is 37062, not 1 as I hoped. I am managing to reset it to '1' by using a windows mysql client program but it doesn't show me what SQL it's executing in order to obtain the desired result. Thanks, Jeff - 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: Accessing last_insert_id problem.
http://www.mysql.com/doc/en/SET_OPTION.html last option(s) maybe you can do somehting with that Gr At 10:54 19-12-02 +, Jeff Snoxell wrote: I'm working with MySQL 3.23.36 so, according to MySQL, Paul Dubois, New Riders running the query: DELETE FROM my_table_name should reset the auto-increment value... but it doesnt'. What SQL do I use to reset the val. You can't believe anything that book says. The author didn't even include anything about TRUNCATE TABLE my_table_name. Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table then: INSERT INTO my_table SET Name='Jeff' then: SELECT * FROM my_table and lo-and-behold, the 'Ref' primary key auto-update field is 37062, not 1 as I hoped. I am managing to reset it to '1' by using a windows mysql client program but it doesn't show me what SQL it's executing in order to obtain the desired result. Thanks, Jeff - 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
