My test is under Debian Lenny
The SQL client was from Windows - SQLyog.
MySQL v5.0.51a-24+Lenny4
concurrent_insert = 1
innodb_commit_concurrency = 0
> Hi,
>
> I just tested it out here as well:
> mysql> CREATE TABLE `care_person` ( `pid` int(11) unsigned NOT NULL
> AUTO_INCREMENT, `name` varchar(60) NOT NULL, PRIMARY KEY (`pid`) )
> ENGINE=InnoDB DEFAULT CHARSET=utf8;
> Query OK, 0 rows affected (0.02 sec)
>
> mysql> INSERT INTO `care_person` (`pid`, `name`) VALUES (10000000,
> 'Donald');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> SELECT LAST_INSERT_ID();
> +------------------+
> | LAST_INSERT_ID() |
> +------------------+
> | 10000001 |
> +------------------+
> 1 row in set (0.00 sec)
>
> So we have a different behaviour under windows and linux. But I would no
> like to have such autoincrement decrements (AI - 1) in the code
> (readability, maintainability, reliability.. in case that this will be
> fixed by mysql later it would be a real ugly workaround what might bring
> working HIS outside in real trouble by updating mysql).
>
> Before doing that, I would suggest to create an dummy record in
> care_person. (if adodb does not support here any suggestion on that
> issue)
>
> Robert
>
>
>
>
> Am Mittwoch, den 29.09.2010, 15:49 +0530 schrieb Ap.Muthu:
>> Hi Robert,
>>
>> I have tested it to be a MySQL issue and not a ADOdb issue.
>> On execution of the said statements, I get the Last Insert ID as 0.
>> So our care2x code seems to be inserting this kind of insert sql.
>> Possibly if we altered the AUTO_INCREMENT value on the table to be
>> (10000000 - 1)
>> and then performed a normal SQL insert without the
>> AUTO_INCREMENT field "pid", we might be able avoid such issues.
>>
>> CREATE TABLE `care_person` (
>> `pid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
>> `name` VARCHAR(60) NOT NULL,
>> PRIMARY KEY (`pid`)
>> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
>>
>> The attached pre-test image is the situation just after table creation.
>>
>> INSERT INTO `care_person` (`pid`, `name`) VALUES (10000000, 'Robert');
>>
>> SELECT LAST_INSERT_ID();
>>
>>
>> After execution of the said 3 sqls, the backup schema of the table is:
>>
>> CREATE TABLE `care_person` (
>> `pid` int(11) unsigned NOT NULL auto_increment,
>> `name` varchar(60) NOT NULL,
>> PRIMARY KEY (`pid`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8;
>>
>> The attached mess image is taken after all the above SQLs have been
>> executed.
>>
>> Regards,
>> Ap.Muthu
>>
>> > Sorry, I do not get the point. When you do that then you will have the
>> > query result of LAST_INSERT_ID() 10000001....
>> >
>> > There is on table definition an autoincrement and you give in your
>> > insert statement an fixed value to it? The result is always strange
>> > (guess it will be 10000001 instead of 10000000 - not tested so
>> > far...why?)
>> >
>> >
>> > Do do you want to make me busy? :-)
>> >
>> > Robert
>> >
>> >
>> > Am Mittwoch, den 29.09.2010, 15:12 +0530 schrieb Ap.Muthu:
>> >> Hi Robert,
>> >>
>> >> Try to make the table without any records and the default
>> >> AUTO_INCREMENT
>> >> to
>> >> start with should be 1 (if the parameter AUTO_INCREMENT=1is omitted).
>> >> Then make an insert with a pid = 10000000 in the insert statement and
>> >> check
>> >> the Last insert ID():
>> >>
>> >> CREATE TABLE `care_person` (
>> >> `pid` int(11) unsigned NOT NULL AUTO_INCREMENT,
>> >> `name` varchar(60) NOT NULL,
>> >> PRIMARY KEY (`pid`)
>> >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>> >>
>> >> INSERT INTO `care_person` (`pid`, `name`) VALUES (10000000, 'Robert');
>> >>
>> >> SELECT LAST_INSERT_ID();
>> >>
>> >> Regards,
>> >> Ap.Muthu
>> >>
>> >>
>> >> > ...I tested out with mysql client and standard call, and it worked
>> >> > perfectly:
>> >> >
>> >> > mysql> create database dropme;
>> >> > Query OK, 1 row affected (0.02 sec)
>> >> > mysql> \u dropme
>> >> > Database changed
>> >> > mysql> CREATE TABLE `care_person` ( `pid` int(11) unsigned NOT
>> >> > NULL
>> >> > AUTO_INCREMENT, `name` varchar(60) NOT NULL, PRIMARY KEY
>> >> > (`pid`) )
>> >> > ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8;
>> >> > Query OK, 0 rows affected (0.06 sec)
>> >> > mysql> insert into care_person (name) VALUES ('Donald');
>> >> > Query OK, 1 row affected (0.00 sec)
>> >> >
>> >> > mysql> SELECT LAST_INSERT_ID();
>> >> > +------------------+
>> >> > | LAST_INSERT_ID() |
>> >> > +------------------+
>> >> > | 10000001 |
>> >> > +------------------+
>> >> > 1 row in set (0.00 sec)
>> >> >
>> >> > That would mean as logic consequence: MySQL does not have that issue
>> >> > on
>> >> > my ubuntu here. But the behaviour of adodb method Insert_ID() had
>> >> > reproducibly that issue on the same ubuntu. Is there any reason not
>> >> > to
>> >> > try with latest adbodb layer if that strange behaviour could be
>> >> > covered
>> >> > with it?
>> >> >
>> >> > Robert
------------------------------------------------------------------------------
Start uncovering the many advantages of virtual appliances
and start using them to simplify application deployment and
accelerate your shift to cloud computing.
http://p.sf.net/sfu/novell-sfdev2dev
_______________________________________________
Care2002-developers mailing list
Care2002-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/care2002-developers
