Re: How to get auto Increment ID of INSERT?

[Carsten Pedersen]

LAST_INSERT_ID() returns the latest ID for the current connection. As 
long as you yourself can guarantee that no other queries are executed 
using that connection, you're fine. If another record others is inserted 
using another connection, that connection will return a different 
LAST_INSERT_ID().


Best,

/ Carsten

On 08-10-2015 15:48, Richard Reina wrote:

If I insert a record into a table with an auto increment ID how can I get
that records ID value? I have read about SELECT LAST_INSERT_ID() statement,
however, do not two statements introduce the risk that another insert may
occur in the interum? Is there a full proof way of getting the ID of the
record that you have just inserted?

Thanks



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

How to get auto Increment ID of INSERT?

[Richard Reina]

If I insert a record into a table with an auto increment ID how can I get
that records ID value? I have read about SELECT LAST_INSERT_ID() statement,
however, do not two statements introduce the risk that another insert may
occur in the interum? Is there a full proof way of getting the ID of the
record that you have just inserted?

Thanks

Re: auto-increment more than one field

[Jan Steinman]

 From: rounak jain rounak.m...@gmail.com
 
 I have a table which needs two fields with auto-increment.

I don't know if you have such control over your installation, but you might 
consider the work-alike MariaDB, which I believe supports auto-increment on 
multiple fields, as well as a slew of other features. (Virtual columns are 
nice.)

 Burn down your cities and leave our farms, and your cities will spring up 
again as if by magic; but destroy our farms and the grass will grow in the 
streets of every city in the country. -- William Jennings Bryan
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: auto-increment more than one field

[Michael Dykman]

refer to http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html





On Sun, May 12, 2013 at 9:39 AM, rounak jain rounak.m...@gmail.com wrote:

 I have a table which needs two fields with auto-increment.
 I have the found the answer here:

 http://stackoverflow.com/questions/13642915/mysql-table-with-more-than-one-auto-incremented-
 coloumn?rq=1

 I am  using MySqlWorkbench. I can see Triggers section below the table. I
 don't know how to use the trigger script suggested in the link. Thanks.




-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

 Sveta Smirnova at Mysql just confirmed this bug in 5.5.13:
http://bugs.mysql.com/45670


On Wed, Jun 15, 2011 at 5:38 PM, Claudio Nanni claudio.na...@gmail.comwrote:

 No worries!

 I think I would have figured that out!

 I'll feedback you tomorrow.

 Thanks again

 Claudio

 2011/6/15 Hank hes...@gmail.com

 Oops... big typo in above steps... add the following line:

  replicate-ignore-table=db.log

 to the SLAVE my.cnf, and restart the SLAVE server.

 The master does not need to be restarted or changed. Just the SLAVE.

 Sorry about that.

 -Hank Eskin



 On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni claudio.na...@gmail.com
 wrote:

  Great investigation Hank,
  congratulations.
 
  I will try this tomorrow morning(11:20pm now)  and let you know if I can
  reproduce it on my environments.
 
  Thanks!
 
  Claudio
 
 
  2011/6/15 Hank hes...@gmail.com
 
  Two additional notes:
 
  1.  Using the replicate-wild-ignore-table option in my.cnf produces
 the
  same results.
 
  2.  If the my.cnf replicate-ignore-table=db.log setting on the master
   is
  removed and mysql restarted so db.log is no longer ignored in
  replication,
  this bug goes away and correct results are reported on the slave.
 
  -Hank Eskin
 
 
  On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote:
 
  
   This is a follow-up to my previous post.  I have been narrowing down
  what
   is causing this bug.  It is a timing issue of a replication ignored
  table
   with an auto-increment primary key values leaking over into a
  non-ignored
   table with inserts immediately after the ignore table has had rows
  inserted.
  
   Basically, data from the ignored table is corrupting a non-ignored
 table
  on
   the slave upon immediate inserts.
  
   Here is how to repeat:
  
   On a master issue:
  
   use db;
   drop table test;
   CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
   drop table log;
   CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log
  varchar(20),
PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;
  
   make sure those two tables are created on the slave through regular
   replication.
  
   on slave
  
   desc test;
   desc log;
  
   Once replicated, on the master, add the following line to the
 [mysqld]
   section of my.cnf, and then restart mysql.
  
replicate-ignore-table=db.log
  
   The on the master, issue the following statements as a copy/paste of
 all
  of
   them at once.
   It's critical that the statements are executed in immediate
 succession
  (no
   delays)
  
   insert into log values (null,info1);
   insert into log values (null,info2);
   insert into log values (null,info3);
   insert into log values (null,info4);
   insert into test values (1,null);
   insert into log values (null,info5);
   insert into test values (1,null);
   insert into log values (null,info6);
   insert into test values (2,null);
   insert into log values (null,info7);
   insert into test values (2,null);
   insert into log values (null,info8);
  
   Here are the results from the master (all correct):
  
   masterselect * from log;
   +---+---+
   | id| log   |
   +---+---+
   | 4 | info1 |
   | 5 | info2 |
   | 6 | info3 |
   | 7 | info4 |
   | 8 | info5 |
   | 9 | info6 |
   | 44450 | info7 |
   | 44451 | info8 |
   +---+---+
   masterselect * from test;
   ++-+
   | id | cnt |
   ++-+
   |  1 |   1 |
   |  1 |   2 |
   |  2 |   1 |
   |  2 |   2 |
   ++-+
   Here are the results from the slave:
  
   slaveselect * from log;
  
   Empty set (0.00 sec)  --- as expected, since it is ignored
  
   slaveselect * from test;
   ++---+
   | id | cnt   |
   ++---+
   |  1 | 7 |   -- should be 1, but has values from log on the
  master
   |  1 | 8 |   -- should be 2
   |  2 | 9 |   -- should be 1
   |  2 | 44450 |   -- should be 2
   ++---+
  
   If there is the slightest delay between the inserts into log and
  test,
   the replication happens correctly.
  
   Thoughts?
  
   -Hank Eskin
  
 
 
 
 
  --
  Claudio
 




 --
 Claudio

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

Very interesting. Waiting for update.
On Jun 15, 2011 4:51 AM, Hank hes...@gmail.com wrote:

 The slave is receiving null as the statement based insert, not an out of
 range number from the master.

 I've been doing more research all day on this bug and have a bit more
 information as to what's causing it.  I plan to write it up tomorrow and
 post it.

 Basically, everything works perfectly, until I add a
 replication-ignore-table=xxx statement in my.cnf where xxx is a
 different table with a unique id INT auto-increment as the single primary
 key  And then the values being inserted into the test table (above, not
 ignored) represent the last-insert-id of the replication *ignored* table
on
 the slave

 Yeah, pretty strange, I know.  But totally repeatable.

 -Hank


 2011/6/14 Halász Sándor h...@tbbs.net

   2011/06/13 22:38 -0400, Hank 
  But that bug report was closed two years ago.  I have no idea if it's
the
  server sending bad data or the slaves. I think it's the slaves, because
on
  the slave error, it clearly is getting this statement:  insert into
test
  values (1,null) to replicate, but when it is executed, the null is
  converted into a random number.  But it's happening on all of my slaves,
a
  mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
  
 
  If the master were sending random big numbers, and replication on the
slave
  in the usual way handled out-of-bound numbers when not allowed to fail,
then
  65535 would be an expected value for a signless 16-bit number. Of
course, if
  this were true, the slave would be getting not that statement but
insert
  into test values (1,469422).
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
 
 

Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

This is a follow-up to my previous post.  I have been narrowing down what is
causing this bug.  It is a timing issue of a replication ignored table with
an auto-increment primary key values leaking over into a non-ignored table
with inserts immediately after the ignore table has had rows inserted.

Basically, data from the ignored table is corrupting a non-ignored table on
the slave upon immediate inserts.

Here is how to repeat:

On a master issue:

use db;
drop table test;
CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
 AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
drop table log;
CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20),
 PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;

make sure those two tables are created on the slave through regular
replication.

on slave

desc test;
desc log;

Once replicated, on the master, add the following line to the [mysqld]
section of my.cnf, and then restart mysql.

 replicate-ignore-table=db.log

The on the master, issue the following statements as a copy/paste of all of
them at once.
It's critical that the statements are executed in immediate succession (no
delays)

insert into log values (null,info1);
insert into log values (null,info2);
insert into log values (null,info3);
insert into log values (null,info4);
insert into test values (1,null);
insert into log values (null,info5);
insert into test values (1,null);
insert into log values (null,info6);
insert into test values (2,null);
insert into log values (null,info7);
insert into test values (2,null);
insert into log values (null,info8);

Here are the results from the master (all correct):

masterselect * from log;
+---+---+
| id| log   |
+---+---+
| 4 | info1 |
| 5 | info2 |
| 6 | info3 |
| 7 | info4 |
| 8 | info5 |
| 9 | info6 |
| 44450 | info7 |
| 44451 | info8 |
+---+---+
masterselect * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |
|  1 |   2 |
|  2 |   1 |
|  2 |   2 |
++-+
Here are the results from the slave:

slaveselect * from log;

Empty set (0.00 sec)  --- as expected, since it is ignored

slaveselect * from test;
++---+
| id | cnt   |
++---+
|  1 | 7 |   -- should be 1, but has values from log on the master
|  1 | 8 |   -- should be 2
|  2 | 9 |   -- should be 1
|  2 | 44450 |   -- should be 2
++---+

If there is the slightest delay between the inserts into log and test,
the replication happens correctly.

Thoughts?

-Hank Eskin

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Two additional notes:

1.  Using the replicate-wild-ignore-table option in my.cnf produces the
same results.

2.  If the my.cnf replicate-ignore-table=db.log setting on the master  is
removed and mysql restarted so db.log is no longer ignored in replication,
this bug goes away and correct results are reported on the slave.

-Hank Eskin


On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote:


 This is a follow-up to my previous post.  I have been narrowing down what
 is causing this bug.  It is a timing issue of a replication ignored table
 with an auto-increment primary key values leaking over into a non-ignored
 table with inserts immediately after the ignore table has had rows inserted.

 Basically, data from the ignored table is corrupting a non-ignored table on
 the slave upon immediate inserts.

 Here is how to repeat:

 On a master issue:

 use db;
 drop table test;
 CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
  AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
 drop table log;
 CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20),
  PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;

 make sure those two tables are created on the slave through regular
 replication.

 on slave

 desc test;
 desc log;

 Once replicated, on the master, add the following line to the [mysqld]
 section of my.cnf, and then restart mysql.

  replicate-ignore-table=db.log

 The on the master, issue the following statements as a copy/paste of all of
 them at once.
 It's critical that the statements are executed in immediate succession (no
 delays)

 insert into log values (null,info1);
 insert into log values (null,info2);
 insert into log values (null,info3);
 insert into log values (null,info4);
 insert into test values (1,null);
 insert into log values (null,info5);
 insert into test values (1,null);
 insert into log values (null,info6);
 insert into test values (2,null);
 insert into log values (null,info7);
 insert into test values (2,null);
 insert into log values (null,info8);

 Here are the results from the master (all correct):

 masterselect * from log;
 +---+---+
 | id| log   |
 +---+---+
 | 4 | info1 |
 | 5 | info2 |
 | 6 | info3 |
 | 7 | info4 |
 | 8 | info5 |
 | 9 | info6 |
 | 44450 | info7 |
 | 44451 | info8 |
 +---+---+
 masterselect * from test;
 ++-+
 | id | cnt |
 ++-+
 |  1 |   1 |
 |  1 |   2 |
 |  2 |   1 |
 |  2 |   2 |
 ++-+
 Here are the results from the slave:

 slaveselect * from log;

 Empty set (0.00 sec)  --- as expected, since it is ignored

 slaveselect * from test;
 ++---+
 | id | cnt   |
 ++---+
 |  1 | 7 |   -- should be 1, but has values from log on the master
 |  1 | 8 |   -- should be 2
 |  2 | 9 |   -- should be 1
 |  2 | 44450 |   -- should be 2
 ++---+

 If there is the slightest delay between the inserts into log and test,
 the replication happens correctly.

 Thoughts?

 -Hank Eskin

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

Great investigation Hank,
congratulations.

I will try this tomorrow morning(11:20pm now)  and let you know if I can
reproduce it on my environments.

Thanks!

Claudio


2011/6/15 Hank hes...@gmail.com

 Two additional notes:

 1.  Using the replicate-wild-ignore-table option in my.cnf produces the
 same results.

 2.  If the my.cnf replicate-ignore-table=db.log setting on the master  is
 removed and mysql restarted so db.log is no longer ignored in
 replication,
 this bug goes away and correct results are reported on the slave.

 -Hank Eskin


 On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote:

 
  This is a follow-up to my previous post.  I have been narrowing down what
  is causing this bug.  It is a timing issue of a replication ignored table
  with an auto-increment primary key values leaking over into a non-ignored
  table with inserts immediately after the ignore table has had rows
 inserted.
 
  Basically, data from the ignored table is corrupting a non-ignored table
 on
  the slave upon immediate inserts.
 
  Here is how to repeat:
 
  On a master issue:
 
  use db;
  drop table test;
  CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
   AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
  drop table log;
  CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log
 varchar(20),
   PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;
 
  make sure those two tables are created on the slave through regular
  replication.
 
  on slave
 
  desc test;
  desc log;
 
  Once replicated, on the master, add the following line to the [mysqld]
  section of my.cnf, and then restart mysql.
 
   replicate-ignore-table=db.log
 
  The on the master, issue the following statements as a copy/paste of all
 of
  them at once.
  It's critical that the statements are executed in immediate succession
 (no
  delays)
 
  insert into log values (null,info1);
  insert into log values (null,info2);
  insert into log values (null,info3);
  insert into log values (null,info4);
  insert into test values (1,null);
  insert into log values (null,info5);
  insert into test values (1,null);
  insert into log values (null,info6);
  insert into test values (2,null);
  insert into log values (null,info7);
  insert into test values (2,null);
  insert into log values (null,info8);
 
  Here are the results from the master (all correct):
 
  masterselect * from log;
  +---+---+
  | id| log   |
  +---+---+
  | 4 | info1 |
  | 5 | info2 |
  | 6 | info3 |
  | 7 | info4 |
  | 8 | info5 |
  | 9 | info6 |
  | 44450 | info7 |
  | 44451 | info8 |
  +---+---+
  masterselect * from test;
  ++-+
  | id | cnt |
  ++-+
  |  1 |   1 |
  |  1 |   2 |
  |  2 |   1 |
  |  2 |   2 |
  ++-+
  Here are the results from the slave:
 
  slaveselect * from log;
 
  Empty set (0.00 sec)  --- as expected, since it is ignored
 
  slaveselect * from test;
  ++---+
  | id | cnt   |
  ++---+
  |  1 | 7 |   -- should be 1, but has values from log on the
 master
  |  1 | 8 |   -- should be 2
  |  2 | 9 |   -- should be 1
  |  2 | 44450 |   -- should be 2
  ++---+
 
  If there is the slightest delay between the inserts into log and
 test,
  the replication happens correctly.
 
  Thoughts?
 
  -Hank Eskin
 




-- 
Claudio

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Oops... big typo in above steps... add the following line:

  replicate-ignore-table=db.log

to the SLAVE my.cnf, and restart the SLAVE server.

The master does not need to be restarted or changed. Just the SLAVE.

Sorry about that.

-Hank Eskin



On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni claudio.na...@gmail.comwrote:

 Great investigation Hank,
 congratulations.

 I will try this tomorrow morning(11:20pm now)  and let you know if I can
 reproduce it on my environments.

 Thanks!

 Claudio


 2011/6/15 Hank hes...@gmail.com

 Two additional notes:

 1.  Using the replicate-wild-ignore-table option in my.cnf produces the
 same results.

 2.  If the my.cnf replicate-ignore-table=db.log setting on the master
  is
 removed and mysql restarted so db.log is no longer ignored in
 replication,
 this bug goes away and correct results are reported on the slave.

 -Hank Eskin


 On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote:

 
  This is a follow-up to my previous post.  I have been narrowing down
 what
  is causing this bug.  It is a timing issue of a replication ignored
 table
  with an auto-increment primary key values leaking over into a
 non-ignored
  table with inserts immediately after the ignore table has had rows
 inserted.
 
  Basically, data from the ignored table is corrupting a non-ignored table
 on
  the slave upon immediate inserts.
 
  Here is how to repeat:
 
  On a master issue:
 
  use db;
  drop table test;
  CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
   AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
  drop table log;
  CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log
 varchar(20),
   PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;
 
  make sure those two tables are created on the slave through regular
  replication.
 
  on slave
 
  desc test;
  desc log;
 
  Once replicated, on the master, add the following line to the [mysqld]
  section of my.cnf, and then restart mysql.
 
   replicate-ignore-table=db.log
 
  The on the master, issue the following statements as a copy/paste of all
 of
  them at once.
  It's critical that the statements are executed in immediate succession
 (no
  delays)
 
  insert into log values (null,info1);
  insert into log values (null,info2);
  insert into log values (null,info3);
  insert into log values (null,info4);
  insert into test values (1,null);
  insert into log values (null,info5);
  insert into test values (1,null);
  insert into log values (null,info6);
  insert into test values (2,null);
  insert into log values (null,info7);
  insert into test values (2,null);
  insert into log values (null,info8);
 
  Here are the results from the master (all correct):
 
  masterselect * from log;
  +---+---+
  | id| log   |
  +---+---+
  | 4 | info1 |
  | 5 | info2 |
  | 6 | info3 |
  | 7 | info4 |
  | 8 | info5 |
  | 9 | info6 |
  | 44450 | info7 |
  | 44451 | info8 |
  +---+---+
  masterselect * from test;
  ++-+
  | id | cnt |
  ++-+
  |  1 |   1 |
  |  1 |   2 |
  |  2 |   1 |
  |  2 |   2 |
  ++-+
  Here are the results from the slave:
 
  slaveselect * from log;
 
  Empty set (0.00 sec)  --- as expected, since it is ignored
 
  slaveselect * from test;
  ++---+
  | id | cnt   |
  ++---+
  |  1 | 7 |   -- should be 1, but has values from log on the
 master
  |  1 | 8 |   -- should be 2
  |  2 | 9 |   -- should be 1
  |  2 | 44450 |   -- should be 2
  ++---+
 
  If there is the slightest delay between the inserts into log and
 test,
  the replication happens correctly.
 
  Thoughts?
 
  -Hank Eskin
 




 --
 Claudio

Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

No worries!

I think I would have figured that out!

I'll feedback you tomorrow.

Thanks again

Claudio

2011/6/15 Hank hes...@gmail.com

 Oops... big typo in above steps... add the following line:

  replicate-ignore-table=db.log

 to the SLAVE my.cnf, and restart the SLAVE server.

 The master does not need to be restarted or changed. Just the SLAVE.

 Sorry about that.

 -Hank Eskin



 On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni claudio.na...@gmail.com
 wrote:

  Great investigation Hank,
  congratulations.
 
  I will try this tomorrow morning(11:20pm now)  and let you know if I can
  reproduce it on my environments.
 
  Thanks!
 
  Claudio
 
 
  2011/6/15 Hank hes...@gmail.com
 
  Two additional notes:
 
  1.  Using the replicate-wild-ignore-table option in my.cnf produces
 the
  same results.
 
  2.  If the my.cnf replicate-ignore-table=db.log setting on the master
   is
  removed and mysql restarted so db.log is no longer ignored in
  replication,
  this bug goes away and correct results are reported on the slave.
 
  -Hank Eskin
 
 
  On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote:
 
  
   This is a follow-up to my previous post.  I have been narrowing down
  what
   is causing this bug.  It is a timing issue of a replication ignored
  table
   with an auto-increment primary key values leaking over into a
  non-ignored
   table with inserts immediately after the ignore table has had rows
  inserted.
  
   Basically, data from the ignored table is corrupting a non-ignored
 table
  on
   the slave upon immediate inserts.
  
   Here is how to repeat:
  
   On a master issue:
  
   use db;
   drop table test;
   CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
   drop table log;
   CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log
  varchar(20),
PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4;
  
   make sure those two tables are created on the slave through regular
   replication.
  
   on slave
  
   desc test;
   desc log;
  
   Once replicated, on the master, add the following line to the [mysqld]
   section of my.cnf, and then restart mysql.
  
replicate-ignore-table=db.log
  
   The on the master, issue the following statements as a copy/paste of
 all
  of
   them at once.
   It's critical that the statements are executed in immediate succession
  (no
   delays)
  
   insert into log values (null,info1);
   insert into log values (null,info2);
   insert into log values (null,info3);
   insert into log values (null,info4);
   insert into test values (1,null);
   insert into log values (null,info5);
   insert into test values (1,null);
   insert into log values (null,info6);
   insert into test values (2,null);
   insert into log values (null,info7);
   insert into test values (2,null);
   insert into log values (null,info8);
  
   Here are the results from the master (all correct):
  
   masterselect * from log;
   +---+---+
   | id| log   |
   +---+---+
   | 4 | info1 |
   | 5 | info2 |
   | 6 | info3 |
   | 7 | info4 |
   | 8 | info5 |
   | 9 | info6 |
   | 44450 | info7 |
   | 44451 | info8 |
   +---+---+
   masterselect * from test;
   ++-+
   | id | cnt |
   ++-+
   |  1 |   1 |
   |  1 |   2 |
   |  2 |   1 |
   |  2 |   2 |
   ++-+
   Here are the results from the slave:
  
   slaveselect * from log;
  
   Empty set (0.00 sec)  --- as expected, since it is ignored
  
   slaveselect * from test;
   ++---+
   | id | cnt   |
   ++---+
   |  1 | 7 |   -- should be 1, but has values from log on the
  master
   |  1 | 8 |   -- should be 2
   |  2 | 9 |   -- should be 1
   |  2 | 44450 |   -- should be 2
   ++---+
  
   If there is the slightest delay between the inserts into log and
  test,
   the replication happens correctly.
  
   Thoughts?
  
   -Hank Eskin
  
 
 
 
 
  --
  Claudio
 




-- 
Claudio

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

You should also have a look at the slave relay log.

But in any case sounds like a bug.

Claudio
On Jun 14, 2011 5:18 AM, Hank hes...@gmail.com wrote:
 Both my master and slave bin logs look OK (I think)..

 master bin log:

 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 SET TIMESTAMP=1308012505/*!*/;
 SET @@session.pseudo_thread_id=9/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
 @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1,
 @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET

@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
 SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1308012505/*!*/;
 insert into test values (1,null)
 /*!*/;
 SET TIMESTAMP=1308012505/*!*/;
 COMMIT
 /*!*/;


 slave bin log:

 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 SET TIMESTAMP=1308012505/*!*/;
 SET @@session.pseudo_thread_id=9/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
 @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1,
 @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET

@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
 SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1308012505/*!*/;
 insert into test values (1,null)
 /*!*/;
 SET TIMESTAMP=1308012505/*!*/;
 COMMIT
 /*!*/;


 -Hank


 On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote:


 Yes, it's basic out-of-the box mysql replication.

 This appears to be an instance of this bug:
 http://bugs.mysql.com/bug.php?id=45670

 But that bug report was closed two years ago. I have no idea if it's the
 server sending bad data or the slaves. I think it's the slaves, because
on
 the slave error, it clearly is getting this statement: insert into test
 values (1,null) to replicate, but when it is executed, the null is
 converted into a random number. But it's happening on all of my slaves, a
 mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
 http://bugs.mysql.com/bug.php?id=45670
 -Hank



 On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.com
wrote:

 Hank,

 I can't reproduce it right now,
 But it really seems a bug.
 Just a shot in the dark, Are you sure you have statement based and not
 mixed replication?
 I don't even know if that would affect , just an idea.

 Claudio
 On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote:
  Hello All,
 
  I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
 5.5.8
  32 and 64-bit slaves (statement based replication).
 
  I'm finding an auto-increment field (part of a compound primary key)
 updates
  correctly using null to insert the next value on the master.. but
when
  this statement is replicated on the slaves, instead of inserting the
 next
  value of the auto-increment field, it inserts 65535 for 'smallint'
  definitions of 'cnt' and seemingly high random numbers around 469422
for
  definitions of 'int' or 'bigint'.
 
  Easy to repeat:
 
  master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT
NULL
  AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
  master: desc test;
  +---+-+--+-+-++
  | Field | Type | Null | Key | Default | Extra |
  +---+-+--+-+-++
  | id | int(11) | NO | PRI | NULL | |
  | cnt | int(11) | NO | PRI | NULL | auto_increment |
  +---+-+--+-+-++
 
  master: insert into test values (1,null);
  master: select * from test;
  ++-+
  | id | cnt |
  ++-+
  | 1 | 1 | --- looks good!
  ++-+
 
  slave: desc test;
  +---+-+--+-+-++
  | Field | Type | Null | Key | Default | Extra |
  +---+-+--+-+-++
  | id | int(11) | NO | PRI | NULL | |
  | cnt | int(11) | NO | PRI | NULL | auto_increment |
  +---+-+--+-+-++
 
  slave: select * from test;
  +++
  | id | cnt |
  +++
  | 1 | 469422 |  should be 1
  +++
 
  But the problem continues...
 
  master: insert into test values (1,null);
  master: select * from test;
  ++-+
  | id | cnt |
  ++-+
  | 1 | 1 | --- correct !
  | 1 | 2 | --- correct !
  ++-+
 
  slave select * from test;
  +++
  | id | cnt |
  +++
  | 1 | 469422

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

That is the slave relay log dump I posted (and mis-labeled). Thanks.

-Hank

On Tue, Jun 14, 2011 at 2:34 AM, Claudio Nanni claudio.na...@gmail.comwrote:

 You should also have a look at the slave relay log.

 But in any case sounds like a bug.

 Claudio
 On Jun 14, 2011 5:18 AM, Hank hes...@gmail.com wrote:
  Both my master and slave bin logs look OK (I think)..
 
  master bin log:
 
  /*!40019 SET @@session.max_insert_delayed_threads=0*/;
  /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  DELIMITER /*!*/;
  SET TIMESTAMP=1308012505/*!*/;
  SET @@session.pseudo_thread_id=9/*!*/;
  SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
  @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  SET @@session.sql_mode=0/*!*/;
  SET @@session.auto_increment_increment=1,
  @@session.auto_increment_offset=1/*!*/;
  /*!\C latin1 *//*!*/;
  SET
 
 @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
  SET @@session.lc_time_names=0/*!*/;
  SET @@session.collation_database=DEFAULT/*!*/;
  BEGIN
  /*!*/;
  use test/*!*/;
  SET TIMESTAMP=1308012505/*!*/;
  insert into test values (1,null)
  /*!*/;
  SET TIMESTAMP=1308012505/*!*/;
  COMMIT
  /*!*/;
 
 
  slave relay log:
 
  /*!40019 SET @@session.max_insert_delayed_threads=0*/;
  /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  DELIMITER /*!*/;
  SET TIMESTAMP=1308012505/*!*/;
  SET @@session.pseudo_thread_id=9/*!*/;
  SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
  @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  SET @@session.sql_mode=0/*!*/;
  SET @@session.auto_increment_increment=1,
  @@session.auto_increment_offset=1/*!*/;
  /*!\C latin1 *//*!*/;
  SET
 
 @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
  SET @@session.lc_time_names=0/*!*/;
  SET @@session.collation_database=DEFAULT/*!*/;
  BEGIN
  /*!*/;
  use test/*!*/;
  SET TIMESTAMP=1308012505/*!*/;
  insert into test values (1,null)
  /*!*/;
  SET TIMESTAMP=1308012505/*!*/;
  COMMIT
  /*!*/;
 
 
  -Hank
 
 
  On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote:
 
 
  Yes, it's basic out-of-the box mysql replication.
 
  This appears to be an instance of this bug:
  http://bugs.mysql.com/bug.php?id=45670
 
  But that bug report was closed two years ago. I have no idea if it's the
  server sending bad data or the slaves. I think it's the slaves, because
 on
  the slave error, it clearly is getting this statement: insert into test
  values (1,null) to replicate, but when it is executed, the null is
  converted into a random number. But it's happening on all of my slaves,
 a
  mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
  http://bugs.mysql.com/bug.php?id=45670
  -Hank
 
 
 
  On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni 
 claudio.na...@gmail.comwrote:
 
  Hank,
 
  I can't reproduce it right now,
  But it really seems a bug.
  Just a shot in the dark, Are you sure you have statement based and not
  mixed replication?
  I don't even know if that would affect , just an idea.
 
  Claudio
  On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote:
   Hello All,
  
   I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
  5.5.8
   32 and 64-bit slaves (statement based replication).
  
   I'm finding an auto-increment field (part of a compound primary key)
  updates
   correctly using null to insert the next value on the master.. but
 when
   this statement is replicated on the slaves, instead of inserting the
  next
   value of the auto-increment field, it inserts 65535 for 'smallint'
   definitions of 'cnt' and seemingly high random numbers around 469422
 for
   definitions of 'int' or 'bigint'.
  
   Easy to repeat:
  
   master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT
 NULL
   AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
   master: desc test;
   +---+-+--+-+-++
   | Field | Type | Null | Key | Default | Extra |
   +---+-+--+-+-++
   | id | int(11) | NO | PRI | NULL | |
   | cnt | int(11) | NO | PRI | NULL | auto_increment |
   +---+-+--+-+-++
  
   master: insert into test values (1,null);
   master: select * from test;
   ++-+
   | id | cnt |
   ++-+
   | 1 | 1 | --- looks good!
   ++-+
  
   slave: desc test;
   +---+-+--+-+-++
   | Field | Type | Null | Key | Default | Extra |
   +---+-+--+-+-++
   | id | int(11) | NO | PRI | NULL | |
   | cnt | int(11) | NO | PRI | NULL | auto_increment |
   +---+-+--+-+-++
  
   slave: select * from test;
   +++
   | id | cnt |
   +++
   | 1 | 469422 |  should be 1

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hal�sz S�ndor]

 2011/06/13 22:38 -0400, Hank 
But that bug report was closed two years ago.  I have no idea if it's the
server sending bad data or the slaves. I think it's the slaves, because on
the slave error, it clearly is getting this statement:  insert into test
values (1,null) to replicate, but when it is executed, the null is
converted into a random number.  But it's happening on all of my slaves, a
mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.


If the master were sending random big numbers, and replication on the slave in 
the usual way handled out-of-bound numbers when not allowed to fail, then 65535 
would be an expected value for a signless 16-bit number. Of course, if this 
were true, the slave would be getting not that statement but insert into test 
values (1,469422).


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

The slave is receiving null as the statement based insert, not an out of
range number from the master.

I've been doing more research all day on this bug and have a bit more
information as to what's causing it.  I plan to write it up tomorrow and
post it.

Basically, everything works perfectly, until I add a
replication-ignore-table=xxx statement in my.cnf where xxx is a
different table with a unique id INT auto-increment as the single primary
key  And then the values being inserted into the test table (above, not
ignored) represent the last-insert-id of the replication *ignored* table on
the slave

Yeah, pretty strange, I know.  But totally repeatable.

-Hank


2011/6/14 Halász Sándor h...@tbbs.net

  2011/06/13 22:38 -0400, Hank 
 But that bug report was closed two years ago.  I have no idea if it's the
 server sending bad data or the slaves. I think it's the slaves, because on
 the slave error, it clearly is getting this statement:  insert into test
 values (1,null) to replicate, but when it is executed, the null is
 converted into a random number.  But it's happening on all of my slaves, a
 mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
 

 If the master were sending random big numbers, and replication on the slave
 in the usual way handled out-of-bound numbers when not allowed to fail, then
 65535 would be an expected value for a signless 16-bit number. Of course, if
 this were true, the slave would be getting not that statement but insert
 into test values (1,469422).


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com

Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Hello All,

I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8
32 and 64-bit slaves (statement based replication).

I'm finding an auto-increment field (part of a compound primary key) updates
correctly using null to insert the next value  on the master.. but when
this statement is replicated on the slaves, instead of inserting the next
value of the auto-increment field, it inserts 65535 for 'smallint'
definitions of 'cnt' and seemingly high random numbers around 469422 for
definitions of 'int' or 'bigint'.

Easy to repeat:

master:  CREATE TABLE test  ( id int NOT NULL, cnt  int unsigned  NOT NULL
 AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
master:  desc test;
 +---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| id| int(11) | NO   | PRI | NULL||
| cnt   | int(11) | NO   | PRI | NULL| auto_increment |
+---+-+--+-+-++

master:  insert into test values (1,null);
master:  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |   --- looks good!
++-+

slave:  desc test;
 +---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| id| int(11) | NO   | PRI | NULL||
| cnt   | int(11) | NO   | PRI | NULL| auto_increment |
+---+-+--+-+-++

slave: select * from test;
+++
| id | cnt|
+++
|  1 | 469422 |  should be 1
+++

But the problem continues...

master:  insert into test values (1,null);
master:  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |   --- correct !
|  1 |   2 |   --- correct !
++-+

slave select * from test;
+++
| id | cnt|
+++
|  1 | 469422 |   should be 1
|  1 | 470673 |   should be 2
+++

Now if I repeat the entire scenario using smallint for the 'cnt' field,
here are the results:

master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned  NOT NULL
 AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
master desc test;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| id| int(11)  | NO   | PRI | NULL||
| cnt   | smallint(5) unsigned | NO   | PRI | NULL| auto_increment |
+---+--+--+-+-++
master:  insert into test values (1,null);
master:  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |    correct!
++-+

slave select * from test;
+++
| id | cnt|
+++
|  1 | 65535 | should be 1
+++

but this is different:

master:  insert into test values (1,null);
master:  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 | correct!
|  1 |   2 |   correct!
++-+

slave select * from test;
++---+
| id | cnt   |
++---+
|  1 | 65535 |    should be 1, missing second record, too
++---+
slave show slave status;

 Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default
database: 'test'. Query: 'insert into test values (1,null)'

.. at which point I have to restart the slave due to the error:

slave SET GLOBAL sql_slave_skip_counter=1; slave start;
slave select * from test;
++---+
| id | cnt   |
++---+
|  1 | 65535 |   should be 1, still missing second record, too (of
course)
++---+


Now if I manually replicate the statements just on the slave - it works
perfectly:

slave: truncate table test;
slave:  insert into test values (1,null);
slave:  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |
++-+
slave:  insert into test values (1,null);
slave:  select * from test;
++-+
| id | cnt |
++-+
|  1 |   1 |
|  1 |   2 |
++-+

So something in the replication code is munging the 'null' into some random
value and trying to insert it.  Seems strange that direct statements would
work, but replicated statements do not.

Nothing really changed on my system, but for some reason, this all started
happening about a week or so ago. I've been running this 5.5.8/5.5.11
configuration for months now (since 5.5.8 was released).The PHP code
that does this hasn't changed one bit, and this is a simplified version of
the database and code that is running in production.

Additional note: If I drop the 'id' field, and the primary key is just the
auto-increment field, it works correctly in replication.

Any ideas?  Can anyone else replicate

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Claudio Nanni]

Hank,

I can't reproduce it right now,
But it really seems a bug.
Just a shot in the dark, Are you sure you have statement based and not mixed
replication?
I don't even know if that would affect , just an idea.

Claudio
 On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote:
 Hello All,

 I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
5.5.8
 32 and 64-bit slaves (statement based replication).

 I'm finding an auto-increment field (part of a compound primary key)
updates
 correctly using null to insert the next value on the master.. but when
 this statement is replicated on the slaves, instead of inserting the next
 value of the auto-increment field, it inserts 65535 for 'smallint'
 definitions of 'cnt' and seemingly high random numbers around 469422 for
 definitions of 'int' or 'bigint'.

 Easy to repeat:

 master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL
 AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
 master: desc test;
 +---+-+--+-+-++
 | Field | Type | Null | Key | Default | Extra |
 +---+-+--+-+-++
 | id | int(11) | NO | PRI | NULL | |
 | cnt | int(11) | NO | PRI | NULL | auto_increment |
 +---+-+--+-+-++

 master: insert into test values (1,null);
 master: select * from test;
 ++-+
 | id | cnt |
 ++-+
 | 1 | 1 | --- looks good!
 ++-+

 slave: desc test;
 +---+-+--+-+-++
 | Field | Type | Null | Key | Default | Extra |
 +---+-+--+-+-++
 | id | int(11) | NO | PRI | NULL | |
 | cnt | int(11) | NO | PRI | NULL | auto_increment |
 +---+-+--+-+-++

 slave: select * from test;
 +++
 | id | cnt |
 +++
 | 1 | 469422 |  should be 1
 +++

 But the problem continues...

 master: insert into test values (1,null);
 master: select * from test;
 ++-+
 | id | cnt |
 ++-+
 | 1 | 1 | --- correct !
 | 1 | 2 | --- correct !
 ++-+

 slave select * from test;
 +++
 | id | cnt |
 +++
 | 1 | 469422 |  should be 1
 | 1 | 470673 |  should be 2
 +++

 Now if I repeat the entire scenario using smallint for the 'cnt' field,
 here are the results:

 master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL
 AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
 master desc test;
 +---+--+--+-+-++
 | Field | Type | Null | Key | Default | Extra |
 +---+--+--+-+-++
 | id | int(11) | NO | PRI | NULL | |
 | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
 +---+--+--+-+-++
 master: insert into test values (1,null);
 master: select * from test;
 ++-+
 | id | cnt |
 ++-+
 | 1 | 1 |  correct!
 ++-+

 slave select * from test;
 +++
 | id | cnt |
 +++
 | 1 | 65535 |  should be 1
 +++

 but this is different:

 master: insert into test values (1,null);
 master: select * from test;
 ++-+
 | id | cnt |
 ++-+
 | 1 | 1 |  correct!
 | 1 | 2 |  correct!
 ++-+

 slave select * from test;
 ++---+
 | id | cnt |
 ++---+
 | 1 | 65535 |  should be 1, missing second record, too
 ++---+
 slave show slave status;

  Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default
 database: 'test'. Query: 'insert into test values (1,null)'

 .. at which point I have to restart the slave due to the error:

 slave SET GLOBAL sql_slave_skip_counter=1; slave start;
 slave select * from test;
 ++---+
 | id | cnt |
 ++---+
 | 1 | 65535 |  should be 1, still missing second record, too (of
 course)
 ++---+


 Now if I manually replicate the statements just on the slave - it works
 perfectly:

 slave: truncate table test;
 slave: insert into test values (1,null);
 slave: select * from test;
 ++-+
 | id | cnt |
 ++-+
 | 1 | 1 |
 ++-+
 slave: insert into test values (1,null);
 slave: select * from test;
 ++-+
 | id | cnt |
 ++-+
 | 1 | 1 |
 | 1 | 2 |
 ++-+

 So something in the replication code is munging the 'null' into some
random
 value and trying to insert it. Seems strange that direct statements would
 work, but replicated statements do not.

 Nothing really changed on my system, but for some reason, this all started
 happening about a week or so ago. I've been running this 5.5.8/5.5.11
 configuration for months now (since 5.5.8 was released). The PHP code
 that does this hasn't changed one bit, and this is a simplified version of
 the database and code that is running in production.

 Additional note: If I drop

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Yes, it's basic out-of-the box mysql replication.

This appears to be an instance of this bug:
http://bugs.mysql.com/bug.php?id=45670

But that bug report was closed two years ago.  I have no idea if it's the
server sending bad data or the slaves. I think it's the slaves, because on
the slave error, it clearly is getting this statement:  insert into test
values (1,null) to replicate, but when it is executed, the null is
converted into a random number.  But it's happening on all of my slaves, a
mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
http://bugs.mysql.com/bug.php?id=45670
-Hank


On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote:

 Hank,

 I can't reproduce it right now,
 But it really seems a bug.
 Just a shot in the dark, Are you sure you have statement based and not
 mixed replication?
 I don't even know if that would affect , just an idea.

 Claudio
  On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote:
  Hello All,
 
  I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
 5.5.8
  32 and 64-bit slaves (statement based replication).
 
  I'm finding an auto-increment field (part of a compound primary key)
 updates
  correctly using null to insert the next value on the master.. but when
  this statement is replicated on the slaves, instead of inserting the next
  value of the auto-increment field, it inserts 65535 for 'smallint'
  definitions of 'cnt' and seemingly high random numbers around 469422 for
  definitions of 'int' or 'bigint'.
 
  Easy to repeat:
 
  master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL
  AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
  master: desc test;
  +---+-+--+-+-++
  | Field | Type | Null | Key | Default | Extra |
  +---+-+--+-+-++
  | id | int(11) | NO | PRI | NULL | |
  | cnt | int(11) | NO | PRI | NULL | auto_increment |
  +---+-+--+-+-++
 
  master: insert into test values (1,null);
  master: select * from test;
  ++-+
  | id | cnt |
  ++-+
  | 1 | 1 | --- looks good!
  ++-+
 
  slave: desc test;
  +---+-+--+-+-++
  | Field | Type | Null | Key | Default | Extra |
  +---+-+--+-+-++
  | id | int(11) | NO | PRI | NULL | |
  | cnt | int(11) | NO | PRI | NULL | auto_increment |
  +---+-+--+-+-++
 
  slave: select * from test;
  +++
  | id | cnt |
  +++
  | 1 | 469422 |  should be 1
  +++
 
  But the problem continues...
 
  master: insert into test values (1,null);
  master: select * from test;
  ++-+
  | id | cnt |
  ++-+
  | 1 | 1 | --- correct !
  | 1 | 2 | --- correct !
  ++-+
 
  slave select * from test;
  +++
  | id | cnt |
  +++
  | 1 | 469422 |  should be 1
  | 1 | 470673 |  should be 2
  +++
 
  Now if I repeat the entire scenario using smallint for the 'cnt' field,
  here are the results:
 
  master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT
 NULL
  AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
  master desc test;
  +---+--+--+-+-++
  | Field | Type | Null | Key | Default | Extra |
  +---+--+--+-+-++
  | id | int(11) | NO | PRI | NULL | |
  | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
  +---+--+--+-+-++
  master: insert into test values (1,null);
  master: select * from test;
  ++-+
  | id | cnt |
  ++-+
  | 1 | 1 |  correct!
  ++-+
 
  slave select * from test;
  +++
  | id | cnt |
  +++
  | 1 | 65535 |  should be 1
  +++
 
  but this is different:
 
  master: insert into test values (1,null);
  master: select * from test;
  ++-+
  | id | cnt |
  ++-+
  | 1 | 1 |  correct!
  | 1 | 2 |  correct!
  ++-+
 
  slave select * from test;
  ++---+
  | id | cnt |
  ++---+
  | 1 | 65535 |  should be 1, missing second record, too
  ++---+
  slave show slave status;
 
   Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query.
 Default
  database: 'test'. Query: 'insert into test values (1,null)'
 
  .. at which point I have to restart the slave due to the error:
 
  slave SET GLOBAL sql_slave_skip_counter=1; slave start;
  slave select * from test;
  ++---+
  | id | cnt |
  ++---+
  | 1 | 65535 |  should be 1, still missing second record, too (of
  course)
  ++---+
 
 
  Now if I manually replicate the statements just on the slave - it works
  perfectly:
 
  slave: truncate table test;
  slave: insert into test values (1,null

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

[Hank]

Both my master and slave bin logs look OK (I think)..

master bin log:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
SET TIMESTAMP=1308012505/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
use test/*!*/;
SET TIMESTAMP=1308012505/*!*/;
insert into test values (1,null)
/*!*/;
SET TIMESTAMP=1308012505/*!*/;
COMMIT
/*!*/;


slave bin log:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
SET TIMESTAMP=1308012505/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
use test/*!*/;
SET TIMESTAMP=1308012505/*!*/;
insert into test values (1,null)
/*!*/;
SET TIMESTAMP=1308012505/*!*/;
COMMIT
/*!*/;


-Hank


On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote:


 Yes, it's basic out-of-the box mysql replication.

 This appears to be an instance of this bug:
 http://bugs.mysql.com/bug.php?id=45670

 But that bug report was closed two years ago.  I have no idea if it's the
 server sending bad data or the slaves. I think it's the slaves, because on
 the slave error, it clearly is getting this statement:  insert into test
 values (1,null) to replicate, but when it is executed, the null is
 converted into a random number.  But it's happening on all of my slaves, a
 mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes.
  http://bugs.mysql.com/bug.php?id=45670
 -Hank



 On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni 
 claudio.na...@gmail.comwrote:

 Hank,

 I can't reproduce it right now,
 But it really seems a bug.
 Just a shot in the dark, Are you sure you have statement based and not
 mixed replication?
 I don't even know if that would affect , just an idea.

 Claudio
  On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote:
  Hello All,
 
  I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and
 5.5.8
  32 and 64-bit slaves (statement based replication).
 
  I'm finding an auto-increment field (part of a compound primary key)
 updates
  correctly using null to insert the next value on the master.. but when
  this statement is replicated on the slaves, instead of inserting the
 next
  value of the auto-increment field, it inserts 65535 for 'smallint'
  definitions of 'cnt' and seemingly high random numbers around 469422 for
  definitions of 'int' or 'bigint'.
 
  Easy to repeat:
 
  master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL
  AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
  master: desc test;
  +---+-+--+-+-++
  | Field | Type | Null | Key | Default | Extra |
  +---+-+--+-+-++
  | id | int(11) | NO | PRI | NULL | |
  | cnt | int(11) | NO | PRI | NULL | auto_increment |
  +---+-+--+-+-++
 
  master: insert into test values (1,null);
  master: select * from test;
  ++-+
  | id | cnt |
  ++-+
  | 1 | 1 | --- looks good!
  ++-+
 
  slave: desc test;
  +---+-+--+-+-++
  | Field | Type | Null | Key | Default | Extra |
  +---+-+--+-+-++
  | id | int(11) | NO | PRI | NULL | |
  | cnt | int(11) | NO | PRI | NULL | auto_increment |
  +---+-+--+-+-++
 
  slave: select * from test;
  +++
  | id | cnt |
  +++
  | 1 | 469422 |  should be 1
  +++
 
  But the problem continues...
 
  master: insert into test values (1,null);
  master: select * from test;
  ++-+
  | id | cnt |
  ++-+
  | 1 | 1 | --- correct !
  | 1 | 2 | --- correct !
  ++-+
 
  slave select * from test;
  +++
  | id | cnt |
  +++
  | 1 | 469422 |  should be 1
  | 1 | 470673 |  should be 2
  +++
 
  Now if I repeat the entire scenario using smallint for the 'cnt'
 field,
  here are the results:
 
  master CREATE TABLE test (id int

[setting value when INSERT for auto increment]

[Grega Leskovšek]

Should it be null?
INSERT INTO `friendlyCMS`.`log` (`idlog`, `imepriimek`, `clock`,
`action`, `onfile`, `filesize`) VALUES (NULL, $_COOKIE['user'],
CURRENT_TIMESTAMP, 'saved',$filename, filesize($filename));
idlog is primaryk ey auto inrement not null...
When insertin the value what should I pass it? NULL? Thanks in advance!!
-- When the sun rises I receive and when it sets I forgive -
http://moj.skavt.net/gleskovs/
Always in Heart, Grega Leskovšek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [setting value when INSERT for auto increment]

[Johan De Meersman]

If you're asking what I think you're asking, then yes, both NULL and 0 will 
trigger an autoincrement field to put in the next value.

- Original Message -
 From: Grega Leskovšek legr...@gmail.com
 To: mysql@lists.mysql.com
 Sent: Monday, 16 May, 2011 4:49:43 PM
 Subject: [setting value when INSERT for auto increment]

 Should it be null?
 INSERT INTO `friendlyCMS`.`log` (`idlog`, `imepriimek`, `clock`,
 `action`, `onfile`, `filesize`) VALUES (NULL, $_COOKIE['user'],
 CURRENT_TIMESTAMP, 'saved',$filename, filesize($filename));
 idlog is primaryk ey auto inrement not null...
 When insertin the value what should I pass it? NULL? Thanks in
 advance!!

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [setting value when INSERT for auto increment]

[Basil Daoust]

since your listing the columns, you could just leave off `idlog` from 
the named columns and thus not also need to include the null in the 
inserted values.


INSERT INTO `friendlyCMS`.`log`
(`imepriimek`, `clock`, `action`, `onfile`, `filesize`)
VALUES ($_COOKIE['user'], CURRENT_TIMESTAMP, 'saved', $filename, 
filesize($filename));



On 16/05/2011 9:59 AM, Johan De Meersman wrote:

If you're asking what I think you're asking, then yes, both NULL and 0 will 
trigger an autoincrement field to put in the next value.

- Original Message -

From: Grega Leskovšeklegr...@gmail.com
To: mysql@lists.mysql.com
Sent: Monday, 16 May, 2011 4:49:43 PM
Subject: [setting value when INSERT for auto increment]

Should it be null?
INSERT INTO `friendlyCMS`.`log` (`idlog`, `imepriimek`, `clock`,
`action`, `onfile`, `filesize`) VALUES (NULL, $_COOKIE['user'],
CURRENT_TIMESTAMP, 'saved',$filename, filesize($filename));
idlog is primaryk ey auto inrement not null...
When insertin the value what should I pass it? NULL? Thanks in
advance!!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Auto-Increment Values in Mysql

[Adarsh Sharma]

Dear all,


I have an auto-increment column in Mysql database table. Let's say the 
column has below values :



1
2
3
4
5
6
7
8
9
10


Now if i deleted some rows where id= 3 ,5 and 8

The data look like as :

1
2
4
6
7
9
10

I want to have it id's as
1
2
3
4
5
6
7
and next data is inserted right at 8

Please help how to achieve it.


Thanks  best Regards
Adarsh Sharma

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto-Increment Values in Mysql

[Mark Goodge]

On 09/02/2011 11:41, Adarsh Sharma wrote:

Dear all,


I have an auto-increment column in Mysql database table. Let's say the
column has below values :


1
2
3
4
5
6
7
8
9
10


Now if i deleted some rows where id= 3 ,5 and 8

The data look like as :

1
2
4
6
7
9
10

I want to have it id's as
1
2
3
4
5
6
7
and next data is inserted right at 8

Please help how to achieve it.


Firstly, if this matters to you then an autoincrementing value is 
probably not what you should be using in the first place. The main point 
of autoincrement is that it doesn't matter what the actual value is, 
it's just a way of achieving a unique key for the table. If you care 
what the values are, then just use an INT field and generate the numbers 
yourself.


That said, if you want to renumber an autoincrementing field then the 
simplest way is to drop it and re-add it:


ALTER TABLE `mytable` DROP `myfield`;

ALTER TABLE `mytable` ADD `myfield` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Mark
--
http://mark.goodge.co.uk
http://www.ratemysupermarket.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: Auto-Increment Values in Mysql

[Reindl Harald]

You do not want this really from the point you understood
what a primary key does in a database

The PRIKEY is unqiue for a record and if the record
does no longer exist his PRIKEY must never return

Sample:
* website
* shop-products
* you have prodid 500 costs 200$
* you delete the product
* there are bookmarks out there
* they NEVER should point to another product what costs 5.000 $ as sample

in no rdbms auto_increment values are reused and if than
you have found a hard bug

Am 09.02.2011 12:41, schrieb Adarsh Sharma:
 Dear all,
 
 I have an auto-increment column in Mysql database table. 
 Let's say the column has below values :
 Now if i deleted some rows where id= 3 ,5 and 8
 
 The data look like as :
 
 1
 2
 4
 6
 7
 9
 10
 
 I want to have it id's as
 1
 2
 3
 4
 5
 6
 7
 and next data is inserted right at 8
 Please help how to achieve it.

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/



signature.asc
Description: OpenPGP digital signature

Re: Auto-Increment Values in Mysql

[Jan Steinman]

 From: Adarsh Sharma adarsh.sha...@orkash.com
 
 I have an auto-increment column in Mysql database table. Let's say the column 
 has below values :

I'll echo what others have said.

Auto-increment is typically used to generate unique primary keys. If this 
column is your primary key, DO NOT change its value! PK = identity.

The value of a record is the key, the whole key, and nothing but the key, so 
help me Codd. :-)

If the auto-inc field is NOT the primary key, AND if it must have contiguous 
values, as others said, you probably want to have your business logic keeping 
track of it, rather than using auto-increment.

I know others have said the same thing, but sometimes it help to hear it put 
differently.


In my mind are many dwellings. Each of the dwellings we create ourselves - the 
house of anger, the house of despair, the house of self pity, the house of 
indifference, the house of negative, the house of positive, the house of hope, 
the house of joy, the house of peace, the house of enthusiasm, the house of 
cooperation, the house of giving. Each of these houses we visit each day. We 
can stay in any house for as long as we want. We can leave these mental houses 
any time we wish. We create the dwelling, we stay in the dwelling, we leave the 
dwelling whenever we wish. We can create new rooms, new houses. Whenever we 
enter these dwellings, this becomes our world until we leave for another. What 
world will we live in today? -- Don Coyhis
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: INSERT with auto increment

[David Stoltz]

Awesome - thanks all for that clarification!


-Original Message-
From: Michael Dykman [mailto:mdyk...@gmail.com] 
Sent: Wednesday, June 30, 2010 1:42 PM
To: David Stoltz
Cc: mysql@lists.mysql.com
Subject: Re: INSERT with auto increment

generally, it is:
INSERT INTO TABLE1 (fieldname [ , fieldname]* ) VALUES (value[, value]*)


If you don't list the columns, it assumes you are inserting all of them,
so:

INSERT INTO TABLE1 (mycolumn  ) VALUES ('stuff')


This will also work
INSERT INTO TABLE1 VALUES (0, 'stuff')

the auto-increment will engage on an insert of 0

 - michael dykman


On Wed, Jun 30, 2010 at 1:30 PM, David Stoltz dsto...@shh.org wrote:
 Hi All,



 In MS SQL, if the table has an identity field/primary key which is set
 to auto increment, you can leave the value out of an INSERT statement,
 and the next highest value will be automatically inserted...



 For instance, with a two column table I could do INSERT INTO TABLE1
 VALUES('stuff')



 I'm having trouble doing the same thing in mySQL...



 In mySQL, if I expressly give it a value, like INSERT INTO TABLE1
 VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I
 don't have a matching number of columns.



 The field in question has a foreign key in another table, making this
a
 primary key in theory, but there's nothing in myphpadmin that shows
this
 as a primary key - perhaps this is the problem?



 Need some guidance



 Thanks!

 Dave





-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

INSERT with auto increment

[David Stoltz]

Hi All,

 

In MS SQL, if the table has an identity field/primary key which is set
to auto increment, you can leave the value out of an INSERT statement,
and the next highest value will be automatically inserted...

 

For instance, with a two column table I could do INSERT INTO TABLE1
VALUES('stuff')

 

I'm having trouble doing the same thing in mySQL...

 

In mySQL, if I expressly give it a value, like INSERT INTO TABLE1
VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.

 

The field in question has a foreign key in another table, making this a
primary key in theory, but there's nothing in myphpadmin that shows this
as a primary key - perhaps this is the problem?

 

Need some guidance 

 

Thanks!

Dave

Re: INSERT with auto increment

[Michael Dykman]

generally, it is:
INSERT INTO TABLE1 (fieldname [ , fieldname]* ) VALUES (value[, value]*)


If you don't list the columns, it assumes you are inserting all of them, so:

INSERT INTO TABLE1 (mycolumn  ) VALUES ('stuff')


This will also work
INSERT INTO TABLE1 VALUES (0, 'stuff')

the auto-increment will engage on an insert of 0

 - michael dykman


On Wed, Jun 30, 2010 at 1:30 PM, David Stoltz dsto...@shh.org wrote:
 Hi All,



 In MS SQL, if the table has an identity field/primary key which is set
 to auto increment, you can leave the value out of an INSERT statement,
 and the next highest value will be automatically inserted...



 For instance, with a two column table I could do INSERT INTO TABLE1
 VALUES('stuff')



 I'm having trouble doing the same thing in mySQL...



 In mySQL, if I expressly give it a value, like INSERT INTO TABLE1
 VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I
 don't have a matching number of columns.



 The field in question has a foreign key in another table, making this a
 primary key in theory, but there's nothing in myphpadmin that shows this
 as a primary key - perhaps this is the problem?



 Need some guidance



 Thanks!

 Dave





-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: INSERT with auto increment

[Jo�o C�ndido de Souza Neto]

You can choose between:

INSERT INTO TABLE1 VALUES (null,'stuff')

or

INSERT INTO TABLE1 (stuffField) VALUES ('stuff')

-- 
João Cândido de Souza Neto

David Stoltz dsto...@shh.org escreveu na mensagem 
news:487e7d0857fe094590bf2dc33fe3e1080a102...@shhs-mail.shh.org...
Hi All,



In MS SQL, if the table has an identity field/primary key which is set
to auto increment, you can leave the value out of an INSERT statement,
and the next highest value will be automatically inserted...



For instance, with a two column table I could do INSERT INTO TABLE1
VALUES('stuff')



I'm having trouble doing the same thing in mySQL...



In mySQL, if I expressly give it a value, like INSERT INTO TABLE1
VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.



The field in question has a foreign key in another table, making this a
primary key in theory, but there's nothing in myphpadmin that shows this
as a primary key - perhaps this is the problem?



Need some guidance



Thanks!

Dave




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: INSERT with auto increment

[Jan Steinman]

From: David Stoltz dsto...@shh.org

In mySQL, if I expressly give it a value, like INSERT INTO TABLE1
VALUES(17,'stuff') - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.


Use NULL for the autoinsert column.


I made it a rule to forbear all direct contradictions to the  
sentiments of others, and all positive assertion of my own. I even  
forbade myself the use of every word or expression in the language  
that imported a fixed opinion, such as certainly, undoubtedly,  
etc. I adopted instead of them I conceive, I apprehend, or I  
imagine a thing to be so or so; or so it appears to me at present.  
When another asserted something that I thought an error, I denied  
myself the pleasure of contradicting him abruptly, and of showing him  
immediately some absurdity in his proposition. In answering I began by  
observing that in certain cases or circumstances his opinion would be  
right, but in the present case there appeared or seemed to me some  
difference, etc. I soon found the advantage of this change in my  
manner; the conversations I engaged in went on more pleasantly. The  
modest way in which I proposed my opinions procured them a readier  
reception and less contradiction. I had less mortification when I was  
found to be in the wrong, and I more easily prevailed with others to  
give up their mistakes and join with me when I happened to be in the  
right. -- Benjamin Franklin

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto Increment in InnoDB

[Johan De Meersman]

It might also be done by keeping a last-revision table. Then you'd only
select 1 record from that, and up the number.


On Thu, Apr 22, 2010 at 5:34 PM, Chris W 4rfv...@cox.net wrote:

 Johan De Meersman wrote:

 On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dk
 wrote:



  Wouldn't that strategy cause problems if one or more rows have been
 deleted in the meantime? (i.e. sequence numbers 1-4 have been created,
 row
 2 has been deleted - new sequence number would be 4).




 Yeps. I'm none too sharp today, apparently. Max() it is.




 That may not be an issue in this case.  Since it sounds like he is keeping
 a revision history, I wouldn't be surprised if he plans on not allowing the
 deleting of records, unless of course all of the revision history for a
 given file Cluster/File are deleted.  If that is the case the count would
 work fine.  If that is not the case, max may not work either since if the
 last revision record has been deleted then using max will give faulty data
 as well.  Seems the only way for something like this to work is if you keep
 the full revision history.  Although I suppose that if you were to keep say
 the most recent  X revisions then the last revision would always be in the
 table and max could work where count would not always.

 Chris W


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Auto Increment in InnoDB

[Aveek Misra]

I have a InnoDB table which contains columns named 'cluster' and 'file' 
('cluster' + 'file' is a primary key). I want to add a new column that 
tracks the revision number of a file for a given cluster and a file. The 
situation is tailor made for a MyIsam table where I can add a new Auto 
Increment column as a secondary column in a multiple column index. How 
can I get the same behavior in an InnoDB table? Given below is a view of 
how the records will look like



| Cluster |File|   Rev   |

| clusterA   |   fileA   |   1|

| clusterA   |   fileA   |   2|
-
| clusterB   |   fileA   |   1|

| clusterB   |   fileB   |   1|




Thanks
Aveek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto Increment in InnoDB

[Johan De Meersman]

You can't, iirc - if you add an autoincrement to InnoDB it MUST be the
primary key.

You *can*, however, add that, set it as PK and stick a unique index on
(cluster, file) instead. Behaviour will be identical, but be aware that
there will be some performance implications - you will now have to do an
extra primary key lookup every time you select based on the (cluster,file)
key.


On Thu, Apr 22, 2010 at 7:03 AM, Aveek Misra ave...@yahoo-inc.com wrote:

 I have a InnoDB table which contains columns named 'cluster' and 'file'
 ('cluster' + 'file' is a primary key). I want to add a new column that
 tracks the revision number of a file for a given cluster and a file. The
 situation is tailor made for a MyIsam table where I can add a new Auto
 Increment column as a secondary column in a multiple column index. How can I
 get the same behavior in an InnoDB table? Given below is a view of how the
 records will look like

 
 | Cluster |File|   Rev   |
 
 | clusterA   |   fileA   |   1|
 
 | clusterA   |   fileA   |   2|
 -
 | clusterB   |   fileA   |   1|
 
 | clusterB   |   fileB   |   1|
 



 Thanks
 Aveek

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Auto Increment in InnoDB

[Aveek Misra]

I am not sure I understand. If I make the autoincrement column as part 
of the primary key as (rev + cluster + file), how do I ensure that a 
reset of the revision number is done as soon as (cluster + file) 
combination changes? It looks like I need to do the following to mimic 
the same behavior as that of an autoincrement column in MyISAM


SELECT @id := IFNULL(MAX(rev), 0) FROM table WHERE cluster='clusterA' 
AND file='fileA' ;

SET @id := @id + 1;
INSERT INTO table (cluster, file, rev) VALUES ('clusterA', 'fileA', @id);

Additionally I guess the above needs to be encapsulated in a transaction 
to ensure atomic updates to the 'rev' number for a given cluster and 
file combination. Any thoughts?


Thanks
Aveek

Johan De Meersman wrote:
You can't, iirc - if you add an autoincrement to InnoDB it MUST be the 
primary key.


You *can*, however, add that, set it as PK and stick a unique index on 
(cluster, file) instead. Behaviour will be identical, but be aware 
that there will be some performance implications - you will now have 
to do an extra primary key lookup every time you select based on the 
(cluster,file) key.



On Thu, Apr 22, 2010 at 7:03 AM, Aveek Misra ave...@yahoo-inc.com 
mailto:ave...@yahoo-inc.com wrote:


I have a InnoDB table which contains columns named 'cluster' and
'file' ('cluster' + 'file' is a primary key). I want to add a new
column that tracks the revision number of a file for a given
cluster and a file. The situation is tailor made for a MyIsam
table where I can add a new Auto Increment column as a secondary
column in a multiple column index. How can I get the same behavior
in an InnoDB table? Given below is a view of how the records will
look like


| Cluster |File|   Rev   |

| clusterA   |   fileA   |   1|

| clusterA   |   fileA   |   2|
-
| clusterB   |   fileA   |   1|

| clusterB   |   fileB   |   1|




Thanks
Aveek

-- 
MySQL General Mailing List

For list archives: http://lists.mysql.com/mysql
To unsubscribe:  
 http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be





--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto Increment in InnoDB

[Johan De Meersman]

On Thu, Apr 22, 2010 at 12:09 PM, Aveek Misra ave...@yahoo-inc.com wrote:

 I am not sure I understand. If I make the autoincrement column as part of
 the primary key as (rev + cluster + file), how do I ensure that a reset of
 the revision number is done as soon as (cluster + file) combination changes?



You want the autoincrement to go up every time you change the values in your
primary key ?

Aside from it not being a recommended practice, to put it mildly, that you
update primary key values (possible referential inconsistency), I'm not
aware of this behaviour in MyISAM, either.

An autoincrement is assigned if, and only if you assign NULL or (zero) to an
autoincrement column during an insert.

If your application behaves differently, it is probably already done either
in the application, or possibly through the use of triggers.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Auto Increment in InnoDB

[Aveek Misra]

MyISAM has this really cool feature where you can specify autoincrement 
on a secondary column in a multiple column index. In such a case the 
generated value for the autoincrement column is calculated as 
MAX(autoincrement column) + 1 WHERE prefix='given-prefix'. For more 
refer to 
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html. This 
is exactly what I want, however I have an InnoDB table so this will not 
work (in an InnoDB table, you cannot specify autoincrement on a 
secondary column). So what I wanted to know was if there is some easy 
way to mimic that behavior. In my last mail that I sent, in order to 
mimic that functionality on InnoDB, I had to write several statements 
that possibly need to be a part of a transaction. Of course that also 
meant that I cannot specify that column as an autoincrement but instead 
specify it something as INT NOT NULL.


Thanks
Aveek

Johan De Meersman wrote:

On Thu, Apr 22, 2010 at 12:09 PM, Aveek Misra ave...@yahoo-inc.com wrote:

  

I am not sure I understand. If I make the autoincrement column as part of
the primary key as (rev + cluster + file), how do I ensure that a reset of
the revision number is done as soon as (cluster + file) combination changes?





You want the autoincrement to go up every time you change the values in your
primary key ?

Aside from it not being a recommended practice, to put it mildly, that you
update primary key values (possible referential inconsistency), I'm not
aware of this behaviour in MyISAM, either.

An autoincrement is assigned if, and only if you assign NULL or (zero) to an
autoincrement column during an insert.

If your application behaves differently, it is probably already done either
in the application, or possibly through the use of triggers.



  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto Increment in InnoDB

[Johan De Meersman]

Kudos for managing to drag up such an obscure piece of functionality :-) I
can see where it would be useful, though.

As to your question, though: given that that page indicates that it will
reuse deleted sequence numbers, I think your best bet would be select @id :=
count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should
be slightly faster than a max(), I think. That in a trigger on your table
should emulate the behaviour pretty closely.

Am I mistaken, or does your code try to start from sequence 0 ?

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Auto Increment in InnoDB

[Aveek Misra]

How can count(*) in an InnoDB table be faster than MAX() considering 
that the former needs to do a table scan and the latter can use an index 
if correctly used? My code starts the sequence from 1.


Thanks
Aveek

Johan De Meersman wrote:
Kudos for managing to drag up such an obscure piece of functionality 
:-) I can see where it would be useful, though.


As to your question, though: given that that page indicates that it 
will reuse deleted sequence numbers, I think your best bet would be 
select @id := count(*)+1 from table where cluster='clusterA' AND 
file='fileA' ; - should be slightly faster than a max(), I think. That 
in a trigger on your table should emulate the behaviour pretty closely.


Am I mistaken, or does your code try to start from sequence 0 ?

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto Increment in InnoDB

[Johan De Meersman]

The count happens after the where on an index - it should just count the
appropriate index rows without looking at the values. Worth benchmarking on
your dataset, though.


On Thu, Apr 22, 2010 at 1:22 PM, Aveek Misra ave...@yahoo-inc.com wrote:

 How can count(*) in an InnoDB table be faster than MAX() considering that
 the former needs to do a table scan and the latter can use an index if
 correctly used? My code starts the sequence from 1.


 Thanks
 Aveek

 Johan De Meersman wrote:

 Kudos for managing to drag up such an obscure piece of functionality :-) I
 can see where it would be useful, though.

 As to your question, though: given that that page indicates that it will
 reuse deleted sequence numbers, I think your best bet would be select @id :=
 count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should
 be slightly faster than a max(), I think. That in a trigger on your table
 should emulate the behaviour pretty closely.

 Am I mistaken, or does your code try to start from sequence 0 ?

 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Auto Increment in InnoDB

[Carsten Pedersen]

On Thu, 22 Apr 2010 13:12:16 +0200, Johan De Meersman vegiv...@tuxera.be

wrote:

 Kudos for managing to drag up such an obscure piece of functionality :-)

I

 can see where it would be useful, though.

 

 As to your question, though: given that that page indicates that it will

 reuse deleted sequence numbers, I think your best bet would be select

@id

 :=

 count(*)+1 from table where cluster='clusterA' AND file='fileA' ; -

should

 be slightly faster than a max(), I think. That in a trigger on your

table

 should emulate the behaviour pretty closely.



Wouldn't that strategy cause problems if one or more rows have been

deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row

2 has been deleted - new sequence number would be 4).



/ 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: Auto Increment in InnoDB

[Johan De Meersman]

On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dkwrote:

  Wouldn't that strategy cause problems if one or more rows have been
 deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row
 2 has been deleted - new sequence number would be 4).


Yeps. I'm none too sharp today, apparently. Max() it is.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Auto Increment in InnoDB

[mos]

At 12:03 AM 4/22/2010, Aveek Misra wrote:
I have a InnoDB table which contains columns named 'cluster' and 'file' 
('cluster' + 'file' is a primary key). I want to add a new column that 
tracks the revision number of a file for a given cluster and a file. The 
situation is tailor made for a MyIsam table where I can add a new Auto 
Increment column as a secondary column in a multiple column index. How can 
I get the same behavior in an InnoDB table? Given below is a view of how 
the records will look like



| Cluster |File|   Rev   |

| clusterA   |   fileA   |   1|

| clusterA   |   fileA   |   2|
-
| clusterB   |   fileA   |   1|

| clusterB   |   fileB   |   1|



Aveek,
You should be able to add an autoincrement field to a compound key if 
the autoinc field is the first field of the compound key. This probably 
doesn't help you though.


Mike





Thanks
Aveek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto Increment in InnoDB

[Chris W]

Johan De Meersman wrote:

On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dkwrote:

  

 Wouldn't that strategy cause problems if one or more rows have been
deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row
2 has been deleted - new sequence number would be 4).




Yeps. I'm none too sharp today, apparently. Max() it is.

  


That may not be an issue in this case.  Since it sounds like he is 
keeping a revision history, I wouldn't be surprised if he plans on not 
allowing the deleting of records, unless of course all of the revision 
history for a given file Cluster/File are deleted.  If that is the case 
the count would work fine.  If that is not the case, max may not work 
either since if the last revision record has been deleted then using max 
will give faulty data as well.  Seems the only way for something like 
this to work is if you keep the full revision history.  Although I 
suppose that if you were to keep say the most recent  X revisions then 
the last revision would always be in the table and max could work where 
count would not always.


Chris W

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Table level locking when inserting auto-increment PK to InnoDB

[Jaime Crespo Rincón]

2009/12/23 Ryan Chan ryanchan...@gmail.com:
 Hey.

 Back to few years ago, InnoDB require table level locking when
 inserting auto-increment PK to the table, and Heikki said there will
 be a fix.

 Is this problem still exist now?


If you refer to this bug:
http://bugs.mysql.com/bug.php?id=16979

there is a manual page discussing the changes done for the 5.1 version:
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

-- 
Jaime Crespo
MySQL  Java Instructor
Warp Networks
http://warp.es

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Table level locking when inserting auto-increment PK to InnoDB

[Ryan Chan]

Hey.

Back to few years ago, InnoDB require table level locking when
inserting auto-increment PK to the table, and Heikki said there will
be a fix.

Is this problem still exist now?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Auto Increment Problem

[Victor Subervi]

Hi;

mysql insert into categories (Category, Parent) values ('test', NULL);
ERROR 1062 (23000): Duplicate entry '0' for key 1
mysql describe categories;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| ID   | int(3) unsigned | NO   | PRI | NULL|   |
| Category | varchar(40) | YES  | | NULL|   |
| Parent   | varchar(40) | YES  | | NULL|   |
+--+-+--+-+-+---+
3 rows in set (0.00 sec)

Why the error?
TIA,
Victor

Re: Auto Increment Problem

[Victor Subervi]

Oops. Never mind.
V

On Sat, Dec 5, 2009 at 1:19 PM, Victor Subervi victorsube...@gmail.comwrote:

 Hi;

 mysql insert into categories (Category, Parent) values ('test', NULL);
 ERROR 1062 (23000): Duplicate entry '0' for key 1
 mysql describe categories;
 +--+-+--+-+-+---+
 | Field| Type| Null | Key | Default | Extra |
 +--+-+--+-+-+---+
 | ID   | int(3) unsigned | NO   | PRI | NULL|   |
 | Category | varchar(40) | YES  | | NULL|   |
 | Parent   | varchar(40) | YES  | | NULL|   |
 +--+-+--+-+-+---+
 3 rows in set (0.00 sec)

 Why the error?
 TIA,
 Victor

Auto increment?

[Andreas Pardeike]

Hi,

I have a table 'test'

+-+--+--+-+--- 
++
| Field   | Type | Null | Key | Default   |  
Extra  |
+-+--+--+-+--- 
++
| id  | int(11)  | NO   | PRI | NULL  |  
auto_increment |
| foo | varchar(255) | NO   | | NULL   
||
| bar | varchar(255) | NO   | | NULL   
||
| payload | longblob | YES  | | NULL   
||
+-+--+--+-+--- 
++


where 'payload' size usually is quite big!

Now I want to manipulate a number of rows in a non-trivial way and at  
the

same time set 'id' for those rows to a new number in the sequence. I.e.

UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE  
bar ...


My motivation is that this table has several consumers in other  
applications
that keep track of the biggest 'id' they have seen between polls and  
thus

can keep track of new and changed rows.

Right now, I solve this problem by copying the rows to a temporary  
table,
then delete them and insert them from the temporary table but this  
moves my

huge payload around which I really want to avoid.

How can I solve this in a better way?

/Andreas Pardeike

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto increment?

[Steve Edberg]

At 9:35 AM +0200 4/2/09, Andreas Pardeike wrote:

Hi,

I have a table 'test'

+-+--+--+-+---++
| Field   | Type | Null | Key | Default   | Extra  |
+-+--+--+-+---++
| id  | int(11)  | NO   | PRI | NULL  | auto_increment |
| foo | varchar(255) | NO   | | NULL  
||
| bar | varchar(255) | NO   | | NULL  
||
| payload | longblob | YES  | | NULL  
||

+-+--+--+-+---++

where 'payload' size usually is quite big!

Now I want to manipulate a number of rows in a non-trivial way and at the
same time set 'id' for those rows to a new number in the sequence. I.e.

UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ...

My motivation is that this table has several consumers in other applications
that keep track of the biggest 'id' they have seen between polls and thus
can keep track of new and changed rows.

Right now, I solve this problem by copying the rows to a temporary table,
then delete them and insert them from the temporary table but this moves my
huge payload around which I really want to avoid.

How can I solve this in a better way?

/Andreas Pardeike



Add a column of type timestamp which, by default, will be updated 
every time a record is inserted or updated. Then the other 
applications can simply select records with timestamp  
last_poll_time.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Centersbedb...@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto increment?

[Scott Haneda]

On Apr 2, 2009, at 12:51 AM, Steve Edberg wrote:

At 9:35 AM +0200 4/2/09, Andreas Pardeike wrote:

Hi,

I have a table 'test'

+-+--+--+-+--- 
++
| Field   | Type | Null | Key | Default   |  
Extra  |
+-+--+--+-+--- 
++
| id  | int(11)  | NO   | PRI | NULL  |  
auto_increment |
| foo | varchar(255) | NO   | | NULL   
||
| bar | varchar(255) | NO   | | NULL   
||
| payload | longblob | YES  | | NULL   
||
+-+--+--+-+--- 
++


where 'payload' size usually is quite big!

Now I want to manipulate a number of rows in a non-trivial way and  
at the
same time set 'id' for those rows to a new number in the sequence.  
I.e.


UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo)  
WHERE bar ...


My motivation is that this table has several consumers in other  
applications
that keep track of the biggest 'id' they have seen between polls  
and thus

can keep track of new and changed rows.

Right now, I solve this problem by copying the rows to a temporary  
table,
then delete them and insert them from the temporary table but this  
moves my

huge payload around which I really want to avoid.

How can I solve this in a better way?

/Andreas Pardeike



Add a column of type timestamp which, by default, will be updated  
every time a record is inserted or updated. Then the other  
applications can simply select records with timestamp   
last_poll_time.



My same suggestion as well.  I go a far as to have at least, three  
standard fields to any table I make:



CREATE TABLE IF NOT EXISTS `foobar` (
  `id` int(11) NOT NULL auto_increment,
  `udpated` timestamp NOT NULL default '-00-00 00:00:00' on  
update CURRENT_TIMESTAMP,

  `created` timestamp NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

You never know when you want to know time and date, and this makes it  
brainless.  So any new record you add you will set created = NOW();,  
outside of that, never worry about the updated, field, it gets set  
automatically any time there is any change to the record.


I am near certain, there is one small thing to note, which is for  
example, if you update foobar set something = 'test' where id = 1  
and something was already at test, since no real update/change  
happened the time-stamp is not going to get updated.


* There is a version of mysql 4, that going forward, had a change to  
how `timestamp` was defined.  If your old data is in version four, and  
you plan to move to a new version, look out for that issue.


Thanks for welcome :)
--
Scott * If you contact me off list replace talklists@ with scott@ *


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto increment?

[Andreas Pardeike]

Steve  Scott,

Thanks for the suggestions.

The problem with a timestamp is that it's not fine granular. The
consumer application can record last_poll_time and if it is X then
either of the following will not work:

1) select * from table where tstamp = X

- this fails because it will receive rows that we already have
  processed

2) select * from table where tstamp  X

- this fails because if the producer application updates rows
  faster than 1 second, several rows can get the same timestamp
  and in the worst case, the consumer application will run the
  select query in the middle of that updating and thus get only
  a partial result. The next select will thus skip the remaining
  rows with the same timestamp

3) having a 'processed' boolean column

- this fails with several consumers that will clear that flag
  and thus prevent other consumers to see those rows

I once read that if an auto_increment column is set to NULL then it
will become a new number in the sequence but I was not able to get
this to work.

Any other solutions?

/Andreas Pardeike

On 2 apr 2009, at 10.11, Scott Haneda wrote:

Add a column of type timestamp which, by default, will be updated  
every time a record is inserted or updated. Then the other  
applications can simply select records with timestamp   
last_poll_time.



My same suggestion as well.  I go a far as to have at least, three  
standard fields to any table I make:



CREATE TABLE IF NOT EXISTS `foobar` (
`id` int(11) NOT NULL auto_increment,
`udpated` timestamp NOT NULL default '-00-00 00:00:00' on update  
CURRENT_TIMESTAMP,

`created` timestamp NOT NULL default '-00-00 00:00:00',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

You never know when you want to know time and date, and this makes  
it brainless.  So any new record you add you will set created =  
NOW();, outside of that, never worry about the updated, field, it  
gets set automatically any time there is any change to the record.


I am near certain, there is one small thing to note, which is for  
example, if you update foobar set something = 'test' where id = 1  
and something was already at test, since no real update/change  
happened the time-stamp is not going to get updated.


* There is a version of mysql 4, that going forward, had a change to  
how `timestamp` was defined.  If your old data is in version four,  
and you plan to move to a new version, look out for that issue.


Thanks for welcome :)
--
Scott * If you contact me off list replace talklists@ with scott@ *



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto increment?

[Scott Haneda]

I read your other replies about the timestamp not working.  I still  
think adding the updated and created fields is a good idea in general,  
to any table.  I have some questions about the below since the  
original suggestion would not work for you.


On Apr 2, 2009, at 12:35 AM, Andreas Pardeike wrote:

+-+--+--+-+--- 
++
| Field   | Type | Null | Key | Default   |  
Extra  |
+-+--+--+-+--- 
++
| id  | int(11)  | NO   | PRI | NULL  |  
auto_increment |
| foo | varchar(255) | NO   | | NULL   
||
| bar | varchar(255) | NO   | | NULL   
||
| payload | longblob | YES  | | NULL   
||
+-+--+--+-+--- 
++


where 'payload' size usually is quite big!

Now I want to manipulate a number of rows in a non-trivial way and  
at the
same time set 'id' for those rows to a new number in the sequence.  
I.e.


UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo)  
WHERE bar ...


My motivation is that this table has several consumers in other  
applications
that keep track of the biggest 'id' they have seen between polls and  
thus

can keep track of new and changed rows.


I am a little stumped on this, since id is auto_increment, do you  
start to see gaps in your id's?  This is not undesirable to you?


Right now, I solve this problem by copying the rows to a temporary  
table,
then delete them and insert them from the temporary table but this  
moves my

huge payload around which I really want to avoid.

How can I solve this in a better way?


How about changing the initial insert logic.  The above table would  
keep the id but be used as your key.  Add a second id of payload_id.   
Create a new table with id, payload_id, and payload.  Store just the  
payload in a separate table, connected with the id = payload_id.  Now  
you are never touching your payload data, which is too large.


Also, I have been in cases with batch uploads where performance is an  
issue, and used the INSERT DELAYED features of mysql, where the the  
insert is sent in one command, but the server will do it when there is  
idle time to deal with it.  You have to be a little careful to  
anticipate a server crash or connection failure, but rollback can  
solve that easily.


There does not seem to be an UPDATE DELAYED syntax, but I did just  
learn of

The UPDATE statement supports the following modifiers:

	• If you use the LOW_PRIORITY keyword, execution of the UPDATE is  
delayed

  until no other clients are reading from the table. This affects only
  storage engines that use only table-level locking (MyISAM,  
MEMORY, MERGE).


Maybe you can use the LOW_PRIORITY keyword in your update commands to  
your advantage?

--
Scott * If you contact me off list replace talklists@ with scott@ *


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Setting auto increment value in an update statement

[Andreas Pardeike]

Hi,

I have a table 'test'

+-+--+--+-+--- 
++
| Field   | Type | Null | Key | Default   |  
Extra  |
+-+--+--+-+--- 
++
| id  | int(11)  | NO   | PRI | NULL  |  
auto_increment |
| foo | varchar(255) | NO   | | NULL   
||
| bar | varchar(255) | NO   | | NULL   
||
| payload | longblob | YES  | | NULL   
||
+-+--+--+-+--- 
++


where 'payload' size usually is quite big!

Now I want to manipulate a number of rows in a non-trivial way and at  
the

same time set 'id' for those rows to a new number in the sequence. I.e.

UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE  
bar ...


My motivation is that this table has several consumers in other  
applications
that keep track of the biggest 'id' they have seen between polls and  
thus

can keep track of new and changed rows.

Right now, I solve this problem by copying the rows to a temporary  
table,
then delete them and insert them from the temporary table but this  
moves my

huge payload around which I really want to avoid.

How can I solve this in a better way?

/Andreas Pardeike

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Auto increment without uniquness

[Scott Haneda]

I think ideally I would like to create an auto increment column that  
has no requirement for uniqueness.  So if 6 was the last entry, and  
there are 10 of them, 7 would still be the next, is this possible?


I am assuming it is not?

I am working in a case where data is needing to be de-normalized, and  
I have never had to do this before.  In the past, this would be a join  
away.


Now I have one table, it will have a single parent record, with x  
children records.  I start with an `id` primary key auto inc field,  
standard stuff.


Keeping it simple, lets say I have:
id, group_id, foo, bar, baz

I will do a batch of inserts, where the first record is the parent,  
and the rest are children.  The first parent record is what is getting  
me stuck, I need to give it a group_id, but all the children will need  
to have the same group id.


I could do one insert on the parent, get the returned id, and then  
update the group_id on the parent to the same id, but that is an  
insert and an update, I want to avoid the update.


Can I insert into table set foo = 'test', `group_id` = `id`
Or is that too soon in the insert trasaction to be noticed?

I can allow the parent to have an empty group_id, and use the id as  
what I search on, but it seems messy.


Suggestions?
Thank you all
--
Scott * If you contact me off list replace talklists@ with scott@ *


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [PHP] RE: non-auto increment question

[PJ]

Ashley Sheridan wrote:
 On Thu, 2009-02-26 at 11:27 -0500, PJ wrote:
 Jerry Schwartz wrote:

 Being rather new to all this, I understood from the MySql manual that
 the auto_increment is to b e used immediately after an insertion not
 intermittently. My application is for administrators (the site owner 
 designates) to update the database from and administration directory,
 accessed by user/password login... so there's really very little
 possibility of 2 people accessing at the same time.
 By using MAX + 1 I keep the id number in the $idIn and can reuse it in
 other INSERTS

 [JS] Are you looking for something like LAST_INSERT_ID()? If you
 INSERT a
 record that has an auto-increment field, you can retrieve the value
 that got
 inserted with SELECT LAST_INSERT_ID(). It is connection-specific, so
 you'll always have your own value. You can then save it to reuse,
 either
 as a session variable or (more easily) as a hidden field on your form.

 Thanks, Jerry,


 You hit the nail on the head.:)

 To refine my problem (and reduce my ignorance),here's what is happening
 on the form page:

 There is a series of INSERTs. The first inserts all the columns of
 book table except for the id, which I do not specify as it if
 auto-insert.

 In subsequent tables I have to reference the book.id (for transitional
 tables like book_author(refers authors to book) etc.

 If I understand it correctly, I must retrieve (SELECT
 LAST_INSERT_ID()) after the first INSERT and before the following
 insert; and save the id as a string ($id)...e.g. $sql = SELECT
 LAST_INSERT_ID() AS $id
 I need clarification on the AS $id - should this be simply id(does
 this have to be turned into a value into $id or does $id contain the
 value? And how do I retrieve it to use the returned value for the next
 $sql = INSERT ... - in other words, is the id or $id available for the
 next directive or do I have to do something like $id = id?
 I'm trying to figure this out with some trials but my insert does not
 work from a php file - but it works from command-line... that's another
 post.

 Here's how I mostly do it (albeit simplified):

 $query = INSERT INTO `sometable`(`title`,`content`)
 VALUES('$title','$content');
 $result = mysql_query($query);
 $autoId = mysql_insert_id($result);

 $query = INSERT INTO `another_table`(`link_id`,`value`)
 VALUES($autoId,'$value');
 $result = mysql_query($query);

 No need to call another query to retrieve the last inserted id, as it is
 tied to the last query executed within this session.


 Ash
 www.ashleysheridan.co.uk
For some reason or other $autoId = mysql_insert_id($result); just does
not work for me... Yet some of the data is inserted correctly...
I did find that it does not work on tables that are empty... so you
can't start with an empty table. I entered data  it still did not work.
I tried on another duplicate database... doesn't work.
I have checked  double checked the database, I have added checks to see
what is returned and the returns are 0 or null - as I get different
responses for slightly different functions.
sessions is on
mysql is 5.1.28
php5

here's what is parsed:
else { $sql1 = INSERT INTO book
( title, sub_title, descr, comment, bk_cover,
copyright, ISBN, language, sellers )
VALUES
('$titleIN', '$sub_titleIN', '$descrIN',
'$commentIN', '$bk_coverIN', '$copyrightIN',
'$ISBNIN', '$languageIN', '$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
$sql2 = INSERT INTO author (first_name, last_name) VALUES
('$first_nameIN', '$last_nameIN');
$result2 = mysql_query($sql2, $db);
$authorID = mysql_insert_id($result2);
$sql2a = INSERT INTO book_author (authID, bookID, ordinal)
VALUES ( '$authorID', '$autoid', '1');
$result2a = mysql_query($sql2a, $db);
$sql2b = INSERT INTO author (first_name, last_name) VALUES
('$first_name2IN', '$last_name2IN');
$result2b = mysql_query($sql2b, $db);
$author2ID = mysql_insert_id($result2b);
$sql2c = INSERT INTO book_author (authID, bookID, ordinal)
VALUES ( '$author2ID', '$autoid', '2');
$result2c = mysql_query($sql2c, $db);
$sql3 = INSERT INTO publishers (publisher) VALUES
('$publisherIN');
$result3 = mysql_query($sql3, $db);
$publisherID = mysql_insert_id($result3);
$sql3a = INSERT INTO book_publisher (bookID, publishers_id)
VALUES ( '$autoid', '$publisherID' );
$result3a = mysql_query($sql3a, $db);
foreach($_POST['categoriesIN'] as $category){
$sql4 = INSERT INTO book_categories (book_id, categories_id)
VALUES ($autoid, $category);
$result4 = mysql_query($sql4,$db);
}
echo $autoid; // shows: blank
echo $authorID; // shows: blank
echo $author2ID; // shows: blank
echo $publisherID; // shows: blank
echo brautoid

Re: [PHP] RE: non-auto increment question

[PJ]

Jerry Schwartz wrote:

 Being rather new to all this, I understood from the MySql manual that
 the auto_increment is to b e used immediately after an insertion not
 intermittently. My application is for administrators (the site owner 
 designates) to update the database from and administration directory,
 accessed by user/password login... so there's really very little
 possibility of 2 people accessing at the same time.
 By using MAX + 1 I keep the id number in the $idIn and can reuse it in
 other INSERTS

 [JS] Are you looking for something like LAST_INSERT_ID()? If you INSERT a
 record that has an auto-increment field, you can retrieve the value
 that got
 inserted with SELECT LAST_INSERT_ID(). It is connection-specific, so
 you'll always have your own value. You can then save it to reuse, either
 as a session variable or (more easily) as a hidden field on your form.

Thanks, Jerry,


You hit the nail on the head.:)

To refine my problem (and reduce my ignorance),here's what is happening
on the form page:

There is a series of INSERTs. The first inserts all the columns of
book table except for the id, which I do not specify as it if auto-insert.

In subsequent tables I have to reference the book.id (for transitional
tables like book_author(refers authors to book) etc.

If I understand it correctly, I must retrieve (SELECT
LAST_INSERT_ID()) after the first INSERT and before the following
insert; and save the id as a string ($id)...e.g. $sql = SELECT
LAST_INSERT_ID() AS $id
I need clarification on the AS $id - should this be simply id(does
this have to be turned into a value into $id or does $id contain the
value? And how do I retrieve it to use the returned value for the next
$sql = INSERT ... -  in other words, is the id or $id available for the
next directive or do I have to do something like $id = id?
I'm trying to figure this out with some trials but my insert does not
work from a php file - but it works from command-line... that's another
post.

-- 

Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.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: [PHP] RE: non-auto increment question

[Jerry Schwartz]

-Original Message-
From: PJ [mailto:af.gour...@videotron.ca]
Sent: Thursday, February 26, 2009 11:27 AM
To: Jerry Schwartz
Cc: a...@ashleysheridan.co.uk; 'Gary W. Smith'; 'MySql'; php-
gene...@lists.php.net
Subject: Re: [PHP] RE: non-auto increment question

Jerry Schwartz wrote:

 Being rather new to all this, I understood from the MySql manual that
 the auto_increment is to b e used immediately after an insertion not
 intermittently. My application is for administrators (the site owner

 designates) to update the database from and administration directory,
 accessed by user/password login... so there's really very little
 possibility of 2 people accessing at the same time.
 By using MAX + 1 I keep the id number in the $idIn and can reuse it
in
 other INSERTS

 [JS] Are you looking for something like LAST_INSERT_ID()? If you
INSERT a
 record that has an auto-increment field, you can retrieve the value
 that got
 inserted with SELECT LAST_INSERT_ID(). It is connection-specific, so
 you'll always have your own value. You can then save it to reuse,
either
 as a session variable or (more easily) as a hidden field on your form.

Thanks, Jerry,


You hit the nail on the head.:)

[JS] I'm glad to hear it.

To refine my problem (and reduce my ignorance),here's what is happening
on the form page:

There is a series of INSERTs. The first inserts all the columns of
book table except for the id, which I do not specify as it if auto-
insert.

In subsequent tables I have to reference the book.id (for transitional
tables like book_author(refers authors to book) etc.

[JS] Okay.

If I understand it correctly, I must retrieve (SELECT
LAST_INSERT_ID()) after the first INSERT and before the following
insert; and save the id as a string ($id)...e.g. $sql = SELECT
LAST_INSERT_ID() AS $id

[JS] You are confusing database column names with PHP variable names. You
don't need an alias at all, unless you feel like it for reasons of
convenience or style.

Assume that $title is your book title, and that the first column is an
auto-increment field.
The first two queries should look like

  $query_insert = INSERT INTO book VALUES (NULL, '$title', ...);
and
  $query_select_id = SELECT LAST_INSERT_ID();

Of course, you need to actually execute the two queries. The first one
doesn't return anything (check for errors, of course). The second one
retrieves the ID of the record you just inserted.

Now retrieve the value returned by the SELECT statement and put it into a
variable. You'll use something like

  $row_selected = mysql_query($query_select_id) or die($query_select_id
failed);
  $last_id = mysql_fetch_array($row_selected) or die(Unable to fetch last
inserted ID);

and you have what you want. You can now use $last_id anywhere you want,
until your script ends.

This is all very simplified, but I think you can get my drift.

Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


I need clarification on the AS $id - should this be simply id(does
this have to be turned into a value into $id or does $id contain the
value? And how do I retrieve it to use the returned value for the next
$sql = INSERT ... -  in other words, is the id or $id available for the
next directive or do I have to do something like $id = id?
I'm trying to figure this out with some trials but my insert does not
work from a php file - but it works from command-line... that's another
post.

--

Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.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: [PHP] RE: non-auto increment question

[Jerry Schwartz]

Here's how I mostly do it (albeit simplified):

$query = INSERT INTO `sometable`(`title`,`content`)
VALUES('$title','$content');
$result = mysql_query($query);
$autoId = mysql_insert_id($result);

$query = INSERT INTO `another_table`(`link_id`,`value`)
VALUES($autoId,'$value');
$result = mysql_query($query);

No need to call another query to retrieve the last inserted id, as it is
tied to the last query executed within this session.


Ash
www.ashleysheridan.co.uk

[JS] Ashley is absolutely right, I'd forgotten about the mysql_insert_id
shorthand. (I'm a one-man band, and for the last week or two I've been
immersed in VB for Access forms.) Not only is she right, but her way is
better. Presumably a language's internal code is maintained as the specific
database changes. You can make yourself more independent of the specific
database by using the PDO abstraction, although I would save that for a
rainy weekend.

Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.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: [PHP] RE: non-auto increment question

[PJ]

Ashley Sheridan wrote:
 On Thu, 2009-02-26 at 13:44 -0500, Jerry Schwartz wrote:
   
 Here's how I mostly do it (albeit simplified):

 $query = INSERT INTO `sometable`(`title`,`content`)
 VALUES('$title','$content');
 $result = mysql_query($query);
 $autoId = mysql_insert_id($result);

 $query = INSERT INTO `another_table`(`link_id`,`value`)
 VALUES($autoId,'$value');
 $result = mysql_query($query);

 No need to call another query to retrieve the last inserted id, as it is
 tied to the last query executed within this session.


 Ash
 www.ashleysheridan.co.uk
   
 [JS] Ashley is absolutely right, I'd forgotten about the mysql_insert_id
 shorthand. (I'm a one-man band, and for the last week or two I've been
 immersed in VB for Access forms.) Not only is she right, but her way is
 better. Presumably a language's internal code is maintained as the specific
 database changes. You can make yourself more independent of the specific
 database by using the PDO abstraction, although I would save that for a
 rainy weekend.

 Regards,

 Jerry Schwartz
 The Infoshop by Global Information Incorporated
 195 Farmington Ave.
 Farmington, CT 06032

 860.674.8796 / FAX: 860.674.8341

 www.the-infoshop.com
 www.giiexpress.com
 www.etudes-marche.com







 
 I just checked, and yep, I'm definitely still a he ;)

   
I never thought otherwise... but then I was wondering... there are too
many actresses with the same name... ;-)

-- 

Phil Jourdan --- p...@ptahhotep.com
   http://www.ptahhotep.com
   http://www.chiccantine.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: [PHP] RE: non-auto increment question

[Jerry Schwartz]

Sorry, I should know better.

-Original Message-
From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk]
Sent: Thursday, February 26, 2009 1:51 PM
To: Jerry Schwartz
Cc: 'PJ'; 'Gary W. Smith'; 'MySql'; php-gene...@lists.php.net
Subject: RE: [PHP] RE: non-auto increment question

On Thu, 2009-02-26 at 13:44 -0500, Jerry Schwartz wrote:
 Here's how I mostly do it (albeit simplified):
 
 $query = INSERT INTO `sometable`(`title`,`content`)
 VALUES('$title','$content');
 $result = mysql_query($query);
 $autoId = mysql_insert_id($result);
 
 $query = INSERT INTO `another_table`(`link_id`,`value`)
 VALUES($autoId,'$value');
 $result = mysql_query($query);
 
 No need to call another query to retrieve the last inserted id, as it
is
 tied to the last query executed within this session.
 
 
 Ash
 www.ashleysheridan.co.uk

 [JS] Ashley is absolutely right, I'd forgotten about the
mysql_insert_id
 shorthand. (I'm a one-man band, and for the last week or two I've been
 immersed in VB for Access forms.) Not only is she right, but her way
is
 better. Presumably a language's internal code is maintained as the
specific
 database changes. You can make yourself more independent of the
specific
 database by using the PDO abstraction, although I would save that for
a
 rainy weekend.

 Regards,

 Jerry Schwartz
 The Infoshop by Global Information Incorporated
 195 Farmington Ave.
 Farmington, CT 06032

 860.674.8796 / FAX: 860.674.8341

 www.the-infoshop.com
 www.giiexpress.com
 www.etudes-marche.com







I just checked, and yep, I'm definitely still a he ;)


Ash
www.ashleysheridan.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: [PHP] RE: non-auto increment question

[Gary W. Smith]

 Being rather new to all this, I understood from the MySql manual that
 the auto_increment is to b e used immediately after an insertion not
 intermittently. My application is for administrators (the site owner 
 designates) to update the database from and administration directory,
 accessed by user/password login... so there's really very little
 possibility of 2 people accessing at the same time.
 By using MAX + 1 I keep the id number in the $idIn and can reuse it in
 other INSERTS
 --

The statement is confusing at best.  For the casual user auto_increment
is the way to do.  I say for the casual user.  That is typical me and
you.  Basically if you do an insert a unique value is inserted at the
time of the insert.  As mentioned, there are ways to get this value back
in the return.  

Now why I say it's for the casual user is because if you are using
triggers then you can do things prior to this value being used and then
the statement above is correct.  But you are not going to be using
triggers...

So, put an auto_increment on the key field and find one of the 2^16
samples of how this works with PHP.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

non-auto increment question

[PJ]

I want to insert a new table entry 1 number higher than the highest in
the field (id). I cannot use auto-increment.
And I want to show the value of the field to be added in an input field
on the web page:
if (isset($_REQUEST[AddNewBooksRequest])) {
$SQL = SELECT MAX(id) FROM book;
$result = mysql_query($sql, $db);
$bookCount = mysql_num_rows($result);
for ($i=0; $i  $bookCount; $i++) {
$row = mysql_fetch_array($result);
$idIN= $row[id]+1;
}
$idIN= $_POST[idIN];
$titleIN= $_POST[titleIN];

...snip...

td colspan=2
?
echo input type='text' name='titleIN' value='$idIN' disabled size='2';
?
/td

What am I doing wrong? (The query works and returns the right nr. but
what do I have to do to add 1 to that number and then display it in the
on page and post it to the table?

-- 

Phil Jourdan --- p...@ptahhotep.com
   http://www.ptahhotep.com
   http://www.chiccantine.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: non-auto increment question

[Gary W. Smith]

Not sure that this is the problem BUT you should probably qualify the name of 
the variable such that SELECT MAX(id) AS id FROM book. But you don't want 
max(id) as id but rather max(id) + 1 as id.  With that you can then just 
return the final value.  Also, if you don't want to alias the value (or 
whatever it's called) you should use $row[0] to get it by ordinal posistion.
 
As for now wanting to use autoincrement, you can run into a race condition 
where two people are inserting at the same time, thus having the same generated 
id.
 
Hope that helps.
 



From: PJ [mailto:af.gour...@videotron.ca]
Sent: Wed 2/25/2009 2:01 PM
To: MySql; php-gene...@lists.php.net
Subject: non-auto increment question



I want to insert a new table entry 1 number higher than the highest in
the field (id). I cannot use auto-increment.
And I want to show the value of the field to be added in an input field
on the web page:
if (isset($_REQUEST[AddNewBooksRequest])) {
$SQL = SELECT MAX(id) FROM book;
$result = mysql_query($sql, $db);
$bookCount = mysql_num_rows($result);
for ($i=0; $i  $bookCount; $i++) {
$row = mysql_fetch_array($result);
$idIN= $row[id]+1;
}
$idIN= $_POST[idIN];
$titleIN= $_POST[titleIN];

...snip...

td colspan=2
?
echo input type='text' name='titleIN' value='$idIN' disabled size='2';
?
/td

What am I doing wrong? (The query works and returns the right nr. but
what do I have to do to add 1 to that number and then display it in the
on page and post it to the table?

--

Phil Jourdan --- p...@ptahhotep.com
   http://www.ptahhotep.com http://www.ptahhotep.com/ 
   http://www.chiccantine.com http://www.chiccantine.com/ 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com

Re: [PHP] RE: non-auto increment question

[PJ]

Ashley Sheridan wrote:
 On Wed, 2009-02-25 at 14:10 -0800, Gary W. Smith wrote:
 Not sure that this is the problem BUT you should probably qualify the
 name of the variable such that SELECT MAX(id) AS id FROM book. But
 you don't want max(id) as id but rather max(id) + 1 as id. With
 that you can then just return the final value. Also, if you don't
 want to alias the value (or whatever it's called) you should use
 $row[0] to get it by ordinal posistion.

 As for now wanting to use autoincrement, you can run into a race
 condition where two people are inserting at the same time, thus
 having the same generated id.

 Hope that helps.


 

 From: PJ [mailto:af.gour...@videotron.ca]
 Sent: Wed 2/25/2009 2:01 PM
 To: MySql; php-gene...@lists.php.net
 Subject: non-auto increment question



 I want to insert a new table entry 1 number higher than the highest in
 the field (id). I cannot use auto-increment.
 And I want to show the value of the field to be added in an input field
 on the web page:
 if (isset($_REQUEST[AddNewBooksRequest])) {
 $SQL = SELECT MAX(id) FROM book;
 $result = mysql_query($sql, $db);
 $bookCount = mysql_num_rows($result);
 for ($i=0; $i  $bookCount; $i++) {
 $row = mysql_fetch_array($result);
 $idIN = $row[id]+1;
Actually, I am wondering how to get rid of some of the code here as it
seems a little bloated
How do I get rid of the row counting - since there can never be more
than one row returned with this query.
 }
 $idIN = $_POST[idIN];
 $titleIN = $_POST[titleIN];

 ...snip...

 td colspan=2
 ?
 echo input type='text' name='titleIN' value='$idIN' disabled
 size='2';
 ?
 /td

 What am I doing wrong? (The query works and returns the right nr. but
 what do I have to do to add 1 to that number and then display it in the
 on page and post it to the table?

 --

 Phil Jourdan --- p...@ptahhotep.com
 http://www.ptahhotep.com http://www.ptahhotep.com/
 http://www.chiccantine.com http://www.chiccantine.com/


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql?unsub=g...@primeexalia.com



 Yeah, this sort of situation is really what auto increment is for. If
 you get two people visiting the page with this code on at the same time
 then you'll screw up your database.


 Ash
 www.ashleysheridan.co.uk


Being rather new to all this, I understood from the MySql manual that
the auto_increment is to b e used immediately after an insertion not
intermittently. My application is for administrators (the site owner 
designates) to update the database from and administration directory,
accessed by user/password login... so there's really very little
possibility of 2 people accessing at the same time.
By using MAX + 1 I keep the id number in the $idIn and can reuse it in
other INSERTS
-- 

Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.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: [PHP] RE: non-auto increment question

[Jerry Schwartz]

Being rather new to all this, I understood from the MySql manual that
the auto_increment is to b e used immediately after an insertion not
intermittently. My application is for administrators (the site owner 
designates) to update the database from and administration directory,
accessed by user/password login... so there's really very little
possibility of 2 people accessing at the same time.


[JS] Being rather old to all this, I can tell you that if something is even
remotely possible it will happen just before your performance review. Never
depend upon this.

Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com



By using MAX + 1 I keep the id number in the $idIn and can reuse it in
other INSERTS
--

Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
infoshop.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: [PHP] RE: non-auto increment question

[Jerry Schwartz]

Being rather new to all this, I understood from the MySql manual that
the auto_increment is to b e used immediately after an insertion not
intermittently. My application is for administrators (the site owner 
designates) to update the database from and administration directory,
accessed by user/password login... so there's really very little
possibility of 2 people accessing at the same time.
By using MAX + 1 I keep the id number in the $idIn and can reuse it in
other INSERTS
[JS] Are you looking for something like LAST_INSERT_ID()? If you INSERT a
record that has an auto-increment field, you can retrieve the value that got
inserted with SELECT LAST_INSERT_ID(). It is connection-specific, so
you'll always have your own value. You can then save it to reuse, either
as a session variable or (more easily) as a hidden field on your form.

Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com



--

Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
infoshop.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

reset auto increment to a lesser value

[abhishek jain]

Hello friends,
I need to reset auto increment to a lesser value, is there a metod to do so
in any version of mysql.
Pl. help me.
Thanks
abhi

Re: reset auto increment to a lesser value

[abhishek jain]

On Sat, Aug 2, 2008 at 10:49 PM, Nacho Garcia [EMAIL PROTECTED] wrote:

 http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

 mysql *ALTER TABLE tbl AUTO_INCREMENT = 100;*



 On Sat, Aug 2, 2008 at 5:28 PM, abhishek jain [EMAIL PROTECTED] wrote:

 Hello friends,
 I need to reset auto increment to a lesser value, is there a metod to do
 so
 in any version of mysql.
 Pl. help me.
 Thanks
 abhi



Hi Nacho,
Thanks for the reply,
But as per a thread on this link, if the resetted value is less than the
highest value already in table then the effective increment value will start
from highest number and not from 100.
I have deleted some rows and i want the increment to start from those
row-ids.
Pl. reply,
Thanks,
Abhi

Re: reset auto increment to a lesser value

[Mark Goodge]

abhishek jain wrote:
On Sat, Aug 2, 2008 at 10:49 
But as per a thread on this link, if the resetted value is less than the

highest value already in table then the effective increment value will start
from highest number and not from 100.
I have deleted some rows and i want the increment to start from those
row-ids.


That can't be done. An auto-increment value must be higher than the 
highest existing value. Otherwise, it would not be an auto-increment 
value at all - it would be an auto-interpolate.


On a more general note, if the actual value of the primary key matters 
for anything other than simply existing as a primary key, then you 
shouldn't be using auto-increment at all. You should generate the value 
through some other means and insert it with the value that you want it 
to be.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: reset auto increment to a lesser value

[Chris W]

Mark Goodge wrote:
On a more general note, if the actual value of the primary key matters 
for anything other than simply existing as a primary key, then you 
shouldn't be using auto-increment at all. You should generate the 
value through some other means and insert it with the value that you 
want it to be.


Mark



Can you elaborate on that point?  Do you not use auto-increment values 
to link records in a one to many relationship?


Chris W

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: reset auto increment to a lesser value

[Mark Goodge]

Chris W wrote:


Mark Goodge wrote:
On a more general note, if the actual value of the primary key matters 
for anything other than simply existing as a primary key, then you 
shouldn't be using auto-increment at all. You should generate the 
value through some other means and insert it with the value that you 
want it to be.


Can you elaborate on that point?  Do you not use auto-increment values 
to link records in a one to many relationship?


Yes, but the relevant factor here is that in the table where the 
auto-increment value is generated it has no meaning other than as a 
unique id. In the other tables that use it as a reference, then it has 
meaning there and needs to be inserted as a known value.


An auto-increment field can only be used where that value never needs to 
be set by reference to an external value. It can be a value that other 
external values are set to (such as in a one-to-many relationship), but 
in the other tables that use it as a reference then it isn't inserted as 
an auto-increment.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Replication for auto-increment tables

[Brent Baisley]

If you are getting duplicate id's on the slave, then something is not  
setup correctly. The save should have the same ids as the master. Just  
because a field is auto-increment, doesn't mean you can't enter you  
own value. Think of auto-increment as a default value setting.


Just because a database is setup as a slave, that doesn't mean you  
can't use it like a typical database. You can insert, delete, update,  
etc. just like any other DB. Something or someone is likely adding  
records directly to the slave, which is then generating it's own auto- 
increment value.


Brent Baisley
Systems Architect


On Apr 18, 2008, at 11:36 AM, Chanchal James wrote:


Hi,

Has anyone got mysql master-slave replication setup on v4.1. Were  
you able

to get tables with auto_increment update properly to slave ?
If yes, please let me know. I need some advise on how to set it up  
to work
well. I get stuck at duplicate errors quite often, and those are not  
real

duplicates, its just that its id on slave was already occupied by some
previous entry!!

I see mysql 5 has options like: auto-increment-increment 
auto-increment-offset , but with v4.1

Any help is appreciated.
Thanks!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Replication for auto-increment tables

[Chanchal James]

Hi Brent,

Thanks for your response. There was no other application that was writing to
slave directly. I decided to sit down and track the IDs that were being
reported as duplicates. It seems this happens with tables starting at
auto_increment at 0. Slave doesnt get replicated with records corresponding
to 0 id. Instead it tries to insert to the next id. That was causing the
duplicate entry problems.

I first thought when master does a multi row insert to slave, its id
corresponding to each record can change, or get shuffled, depending on the
order in which it gets inserted. Thanks for your advise.

On Sun, Apr 20, 2008 at 11:01 AM, Brent Baisley [EMAIL PROTECTED] wrote:

 If you are getting duplicate id's on the slave, then something is not
 setup correctly. The save should have the same ids as the master. Just
 because a field is auto-increment, doesn't mean you can't enter you own
 value. Think of auto-increment as a default value setting.

 Just because a database is setup as a slave, that doesn't mean you can't
 use it like a typical database. You can insert, delete, update, etc. just
 like any other DB. Something or someone is likely adding records directly to
 the slave, which is then generating it's own auto-increment value.

 Brent Baisley
 Systems Architect



 On Apr 18, 2008, at 11:36 AM, Chanchal James wrote:

  Hi,
 
  Has anyone got mysql master-slave replication setup on v4.1. Were you
  able
  to get tables with auto_increment update properly to slave ?
  If yes, please let me know. I need some advise on how to set it up to
  work
  well. I get stuck at duplicate errors quite often, and those are not
  real
  duplicates, its just that its id on slave was already occupied by some
  previous entry!!
 
  I see mysql 5 has options like: auto-increment-increment 
  auto-increment-offset , but with v4.1
 
  Any help is appreciated.
  Thanks!
 

Replication for auto-increment tables

[Chanchal James]

Hi,

Has anyone got mysql master-slave replication setup on v4.1. Were you able
to get tables with auto_increment update properly to slave ?
If yes, please let me know. I need some advise on how to set it up to work
well. I get stuck at duplicate errors quite often, and those are not real
duplicates, its just that its id on slave was already occupied by some
previous entry!!

I see mysql 5 has options like: auto-increment-increment 
auto-increment-offset , but with v4.1

Any help is appreciated.
Thanks!

Reset a auto increment field?

[Jason Pruim]

Is there away to reset an auto incrementing field count? I have a  
database that currently has 935 records in it but because I have  
deleted a few the current number used for NEW records is 938 :) How  
can I get it to count the records and assign a record number based on  
the total count?


Hope that makes sense! Thanks for looking! :)

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]

Re: Reset a auto increment field?

[dpgirago]

 Is there away to reset an auto incrementing field count? I have a 
 database that currently has 935 records in it but because I have 
 deleted a few the current number used for NEW records is 938 :) How 
 can I get it to count the records and assign a record number based on 
 the total count?

 Hope that makes sense! Thanks for looking! :)

 --

 Jason Pruim
 Raoset Inc.
 Technology Manager
 MQC Specialist
 3251 132nd ave
 Holland, MI, 49424
 www.raoset.com
 [EMAIL PROTECTED]

AFAIK, you need to drop and then recreate the auto-increment field, 
otherwise you'll get holes when you delete a record.

David

RE: Reset a auto increment field?

[emierzwa]

 To change the value of the AUTO_INCREMENT  counter to be used for new
rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;

You cannot reset the counter to a value less than or equal to any that
have already been used. For MyISAM, if the value is less than or equal
to the maximum value currently in the AUTO_INCREMENT column, the value
is reset to the current maximum plus one. For InnoDB, you can use ALTER
TABLE ... AUTO_INCREMENT = value as of MySQL 5.0.3, but if the value is
less than the current maximum value in the column, no error message is
given and the current sequence value is not changed. 


Ed 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 29, 2007 11:02 AM
To: Jason Pruim
Cc: MySQL List
Subject: Re: Reset a auto increment field?

 Is there away to reset an auto incrementing field count? I have a 
 database that currently has 935 records in it but because I have 
 deleted a few the current number used for NEW records is 938 :) How 
 can I get it to count the records and assign a record number based on 
 the total count?

 Hope that makes sense! Thanks for looking! :)

 --

 Jason Pruim
 Raoset Inc.
 Technology Manager
 MQC Specialist
 3251 132nd ave
 Holland, MI, 49424
 www.raoset.com
 [EMAIL PROTECTED]

AFAIK, you need to drop and then recreate the auto-increment field, 
otherwise you'll get holes when you delete a record.

David



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reset a auto increment field?

[Jason Pruim]

If I understand you correctly, if my table is MyISAM, after I did a  
delete query I could just: ALTER TABLE t2 AUTO_INCREMENT=1; and  
that would cause the auto increment value to be set to 901 (Assuming  
900 total current records) on the next insert?



On Aug 29, 2007, at 1:48 PM, [EMAIL PROTECTED]  
[EMAIL PROTECTED] wrote:



 To change the value of the AUTO_INCREMENT  counter to be used for new
rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;

You cannot reset the counter to a value less than or equal to any that
have already been used. For MyISAM, if the value is less than or equal
to the maximum value currently in the AUTO_INCREMENT column, the value
is reset to the current maximum plus one. For InnoDB, you can use  
ALTER
TABLE ... AUTO_INCREMENT = value as of MySQL 5.0.3, but if the  
value is

less than the current maximum value in the column, no error message is
given and the current sequence value is not changed.


Ed

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 29, 2007 11:02 AM
To: Jason Pruim
Cc: MySQL List
Subject: Re: Reset a auto increment field?


Is there away to reset an auto incrementing field count? I have a
database that currently has 935 records in it but because I have
deleted a few the current number used for NEW records is 938 :) How
can I get it to count the records and assign a record number based on
the total count?

Hope that makes sense! Thanks for looking! :)

--



Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]


AFAIK, you need to drop and then recreate the auto-increment field,
otherwise you'll get holes when you delete a record.

David



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]





--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Reset a auto increment field?

[emierzwa]

Yes, for a MyIsam type table.

Ed 

-Original Message-
From: Jason Pruim [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 29, 2007 11:53 AM
To: emierzwa
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: Reset a auto increment field?

If I understand you correctly, if my table is MyISAM, after I did a  
delete query I could just: ALTER TABLE t2 AUTO_INCREMENT=1; and  
that would cause the auto increment value to be set to 901 (Assuming  
900 total current records) on the next insert?


On Aug 29, 2007, at 1:48 PM, [EMAIL PROTECTED]  
[EMAIL PROTECTED] wrote:

  To change the value of the AUTO_INCREMENT  counter to be used for new
 rows, do this:

 ALTER TABLE t2 AUTO_INCREMENT = value;

 You cannot reset the counter to a value less than or equal to any that
 have already been used. For MyISAM, if the value is less than or equal
 to the maximum value currently in the AUTO_INCREMENT column, the value
 is reset to the current maximum plus one. For InnoDB, you can use  
 ALTER
 TABLE ... AUTO_INCREMENT = value as of MySQL 5.0.3, but if the  
 value is
 less than the current maximum value in the column, no error message is
 given and the current sequence value is not changed.


 Ed

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, August 29, 2007 11:02 AM
 To: Jason Pruim
 Cc: MySQL List
 Subject: Re: Reset a auto increment field?

 Is there away to reset an auto incrementing field count? I have a
 database that currently has 935 records in it but because I have
 deleted a few the current number used for NEW records is 938 :) How
 can I get it to count the records and assign a record number based on
 the total count?

 Hope that makes sense! Thanks for looking! :)

 --

 Jason Pruim
 Raoset Inc.
 Technology Manager
 MQC Specialist
 3251 132nd ave
 Holland, MI, 49424
 www.raoset.com
 [EMAIL PROTECTED]

 AFAIK, you need to drop and then recreate the auto-increment field,
 otherwise you'll get holes when you delete a record.

 David



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql? 
 [EMAIL PROTECTED]



--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reset a auto increment field?

[Shawn Green]

Hi Jason,

Jason Pruim wrote:
Is there away to reset an auto incrementing field count? I have a 
database that currently has 935 records in it but because I have deleted 
a few the current number used for NEW records is 938 :) How can I get it 
to count the records and assign a record number based on the total count?


Hope that makes sense! Thanks for looking! :)



Actually, it doesn't make sense and for the very reason you are trying 
to use it. At some point in history you had a record # 936. Because that 
record once existed, there may have been one or several things 
associated with it. Imagine the confusion that would ensue if the Social 
Security administration recycled an already issued number just as soon 
as the person using it died.


The safest thing to do is to pretend that the auto-incrementing field is 
an internal, non-editable field. Should you have gaps in your auto-inc 
values treat them as normal conditions of having an active database.


For another instance, assume that you are auto-incrementing the serial 
numbers to various items in an inventory control system. If an item is 
destroyed or taken out of use, you probably want to move that record 
from an activeitems table to some other location. Would you want to 
re-issue those numbers to newly purchased items just to fill in the gaps 
in the activeitems table? Of course not.


Now, with the understanding that doing this on a regular basis would be 
wrong, here is how to do it anyway: Use the auto_increment= option to 
an ALTER TABLE statement like this


ALTER TABLE mydata AUTO_INCREMENT=936;

(alter table)
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
(for the definition of table option)
http://dev.mysql.com/doc/refman/5.0/en/create-table.html

--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /
  / /|_/ / // /\ \/ /_/ / /__
 /_/  /_/\_, /___/\___\_\___/
___/
 Join the Quality Contribution Program Today!
 http://dev.mysql.com/qualitycontribution.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Reset a auto increment field?

[Jerry Schwartz]

It would not reset to 901 unless the highest numbered record were 900. It
won't fill in holes.

Since autoincrement fields are typically used as keys linking to other
tables, renumbering existing records is not done often. If you REALLY want
to renumber them all, copy the records to a new table but leave off the
autoincrement field. TRUNCATE the original table and copy back the original
records, supplying NULL for the autoincrement field.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


 -Original Message-
 From: Jason Pruim [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, August 29, 2007 1:53 PM
 To: [EMAIL PROTECTED]@micron.com
 Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
 Subject: Re: Reset a auto increment field?

 If I understand you correctly, if my table is MyISAM, after I did a
 delete query I could just: ALTER TABLE t2 AUTO_INCREMENT=1; and
 that would cause the auto increment value to be set to 901 (Assuming
 900 total current records) on the next insert?


 On Aug 29, 2007, at 1:48 PM, [EMAIL PROTECTED]
 [EMAIL PROTECTED] wrote:

   To change the value of the AUTO_INCREMENT  counter to be
 used for new
  rows, do this:
 
  ALTER TABLE t2 AUTO_INCREMENT = value;
 
  You cannot reset the counter to a value less than or equal
 to any that
  have already been used. For MyISAM, if the value is less
 than or equal
  to the maximum value currently in the AUTO_INCREMENT
 column, the value
  is reset to the current maximum plus one. For InnoDB, you can use
  ALTER
  TABLE ... AUTO_INCREMENT = value as of MySQL 5.0.3, but if the
  value is
  less than the current maximum value in the column, no error
 message is
  given and the current sequence value is not changed.
 
 
  Ed
 
  -Original Message-
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
  Sent: Wednesday, August 29, 2007 11:02 AM
  To: Jason Pruim
  Cc: MySQL List
  Subject: Re: Reset a auto increment field?
 
  Is there away to reset an auto incrementing field count? I have a
  database that currently has 935 records in it but because I have
  deleted a few the current number used for NEW records is 938 :) How
  can I get it to count the records and assign a record
 number based on
  the total count?
 
  Hope that makes sense! Thanks for looking! :)
 
  --
 
  Jason Pruim
  Raoset Inc.
  Technology Manager
  MQC Specialist
  3251 132nd ave
  Holland, MI, 49424
  www.raoset.com
  [EMAIL PROTECTED]
 
  AFAIK, you need to drop and then recreate the auto-increment field,
  otherwise you'll get holes when you delete a record.
 
  David
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?
  [EMAIL PROTECTED]
 
 

 --

 Jason Pruim
 Raoset Inc.
 Technology Manager
 MQC Specialist
 3251 132nd ave
 Holland, MI, 49424
 www.raoset.com
 [EMAIL PROTECTED]



 --
 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: Reset a auto increment field?

[Jason Pruim]

On Aug 29, 2007, at 2:30 PM, Shawn Green wrote:


Hi Jason,

Jason Pruim wrote:
Is there away to reset an auto incrementing field count? I have a  
database that currently has 935 records in it but because I have  
deleted a few the current number used for NEW records is 938 :)  
How can I get it to count the records and assign a record number  
based on the total count?

Hope that makes sense! Thanks for looking! :)


Actually, it doesn't make sense and for the very reason you are  
trying to use it. At some point in history you had a record # 936.  
Because that record once existed, there may have been one or  
several things associated with it. Imagine the confusion that would  
ensue if the Social Security administration recycled an already  
issued number just as soon as the person using it died.


The safest thing to do is to pretend that the auto-incrementing  
field is an internal, non-editable field. Should you have gaps in  
your auto-inc values treat them as normal conditions of having an  
active database.


For another instance, assume that you are auto-incrementing the  
serial numbers to various items in an inventory control system. If  
an item is destroyed or taken out of use, you probably want to move  
that record from an activeitems table to some other location.  
Would you want to re-issue those numbers to newly purchased items  
just to fill in the gaps in the activeitems table? Of course not.


Now, with the understanding that doing this on a regular basis  
would be wrong, here is how to do it anyway: Use the  
auto_increment= option to an ALTER TABLE statement like this


ALTER TABLE mydata AUTO_INCREMENT=936;

(alter table)
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
(for the definition of table option)
http://dev.mysql.com/doc/refman/5.0/en/create-table.html


I see what you are getting at with this, and have decided that  
mucking around with auto incrementing values doesn't exactly fit in  
with the way databases were designed to work.


Somehow though, I still need to supply this whether I end up adding a  
Record number field in the database, and then through php (The way  
the database is going to be accessed) assigned a record number to  
that field based on the total rows, and display that number rather  
then the internal record number.


This is getting complicated :)





--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /
  / /|_/ / // /\ \/ /_/ / /__
 /_/  /_/\_, /___/\___\_\___/
___/
 Join the Quality Contribution Program Today!
 http://dev.mysql.com/qualitycontribution.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]





--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reset a auto increment field?

[Shawn Green]

Jason Pruim wrote:


snip

I see what you are getting at with this, and have decided that mucking 
around with auto incrementing values doesn't exactly fit in with the way 
databases were designed to work.


Somehow though, I still need to supply this whether I end up adding a 
Record number field in the database, and then through php (The way the 
database is going to be accessed) assigned a record number to that field 
based on the total rows, and display that number rather then the 
internal record number.


This is getting complicated :)



The concept of sequential numbers only applies to people. If a row has a 
unique identifier that's all you need to know to work with it.


For instance, if you have a list of 26 last names where each name starts 
with a different letter of the alphabet. You could enter them in any 
order you want and their ID values will be in apparently random order. 
However to see the names sorted, you apply an ORDER BY clause to your 
query. Now, the A name is #1 and the Z name is #26. Reverse that with 
ORDER BY ... desc and the A name will be #26 and the Z name will be #1 
in your output.  What if you wanted to list just those names after 'K'? 
Now the L name becomes #1. I hope this makes it clear that the concept 
of sequential numbers is only nice for people. Computers and databases 
don't need them to function well.


When you need to supply sequential numbers (as in popularity ranks) the 
easiest way to do it is to return an ordered list then number them as 
you output them with your client application.


--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /
  / /|_/ / // /\ \/ /_/ / /__
 /_/  /_/\_, /___/\___\_\___/
___/
 Join the Quality Contribution Program Today!
 http://dev.mysql.com/qualitycontribution.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Reset a auto increment field?

[Jerry Schwartz]

Do you mean you want to be able to display the record number as sorted by
the auto-increment field, rather than the auto-increment field itself? Or do
you just want the total number of records? Or do you just want the highest
current value of the auto-increment field?

The latter two are easy:

SELECT COUNT(*) FROM table;

SELECT MAX(auto_inc) FROM table;

Offhand, I do not know how to do the first.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


 -Original Message-
 From: Jason Pruim [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, August 29, 2007 3:16 PM
 To: Shawn Green
 Cc: MySQL List
 Subject: Re: Reset a auto increment field?


 On Aug 29, 2007, at 2:30 PM, Shawn Green wrote:

  Hi Jason,
 
  Jason Pruim wrote:
  Is there away to reset an auto incrementing field count? I have a
  database that currently has 935 records in it but because I have
  deleted a few the current number used for NEW records is 938 :)
  How can I get it to count the records and assign a record number
  based on the total count?
  Hope that makes sense! Thanks for looking! :)
 
  Actually, it doesn't make sense and for the very reason you are
  trying to use it. At some point in history you had a record # 936.
  Because that record once existed, there may have been one or
  several things associated with it. Imagine the confusion
 that would
  ensue if the Social Security administration recycled an already
  issued number just as soon as the person using it died.
 
  The safest thing to do is to pretend that the auto-incrementing
  field is an internal, non-editable field. Should you have gaps in
  your auto-inc values treat them as normal conditions of having an
  active database.
 
  For another instance, assume that you are auto-incrementing the
  serial numbers to various items in an inventory control system. If
  an item is destroyed or taken out of use, you probably want
 to move
  that record from an activeitems table to some other location.
  Would you want to re-issue those numbers to newly purchased items
  just to fill in the gaps in the activeitems table? Of course not.
 
  Now, with the understanding that doing this on a regular basis
  would be wrong, here is how to do it anyway: Use the
  auto_increment= option to an ALTER TABLE statement like this
 
  ALTER TABLE mydata AUTO_INCREMENT=936;
 
  (alter table)
  http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
  (for the definition of table option)
  http://dev.mysql.com/doc/refman/5.0/en/create-table.html

 I see what you are getting at with this, and have decided that
 mucking around with auto incrementing values doesn't exactly fit in
 with the way databases were designed to work.

 Somehow though, I still need to supply this whether I end up
 adding a
 Record number field in the database, and then through php (The way
 the database is going to be accessed) assigned a record number to
 that field based on the total rows, and display that number rather
 then the internal record number.

 This is getting complicated :)



 
  --
  Shawn Green, Support Engineer
  MySQL Inc., USA, www.mysql.com
  Office: Blountville, TN
  __  ___ ___   __
 /  |/  /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
   /_/  /_/\_, /___/\___\_\___/
  ___/
   Join the Quality Contribution Program Today!
   http://dev.mysql.com/qualitycontribution.html
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?
  [EMAIL PROTECTED]
 
 

 --

 Jason Pruim
 Raoset Inc.
 Technology Manager
 MQC Specialist
 3251 132nd ave
 Holland, MI, 49424
 www.raoset.com
 [EMAIL PROTECTED]



 --
 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: auto increment format

[Brent Baisley]

If you are going to implement real security, it shouldn't matter if  
someone nows the unique id of a record. You should be checking if  
they have the right to see that record.


But regardless, there is an easy way to set random ids as your  
unique identifier. Setup 2 fields, one being the random number of  
string that you generate, the other being an auto_increment field.  
You primary key will be the combination of these field (random 
+autoincrement). The auto_increment field will almost always be 1  
until there is a conflict with the random ids, in which case it will  
increment. The downside is that all your queries (joins too) will  
need to reference 2 fields instead of 1.


On Aug 7, 2007, at 8:05 AM, shivendra wrote:



Hi, I'm looking for some basic help. I am developing a MySQL  
database and
want to auto increment a field, but I don't want it to just count  
1,2,3,
etc. I want the field to be a combination of letters and numbers,  
at least 8

digits long, completely random for security porposes, but do this
automatically, everytime a record is added. For example, ord5001,  
ord5002,

ord5003, etc. Does anyone know how to do this in MySQL?
--
View this message in context: http://www.nabble.com/auto-increment- 
format-tf4229677.html#a12032917

Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

auto increment format

[shivendra]

Hi, I'm looking for some basic help. I am developing a MySQL database and
want to auto increment a field, but I don't want it to just count 1,2,3,
etc. I want the field to be a combination of letters and numbers, at least 8
digits long, completely random for security porposes, but do this
automatically, everytime a record is added. For example, ord5001, ord5002,
ord5003, etc. Does anyone know how to do this in MySQL?
-- 
View this message in context: 
http://www.nabble.com/auto-increment-format-tf4229677.html#a12032917
Sent from the MySQL - General mailing list archive at Nabble.com.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Fwd: auto increment format

[Olexandr Melnyk]

You can do that using a before insert trigger, something like (untested) :

CREATE TRIGGER test1bi
BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
NEW.ID = COALESCE( NEW.ID, SHA1(CAST(RAND() AS CHAR)))
  END;


2007/8/7, shivendra [EMAIL PROTECTED]:


 Hi, I'm looking for some basic help. I am developing a MySQL database and
 want to auto increment a field, but I don't want it to just count 1,2,3,
 etc. I want the field to be a combination of letters and numbers, at least
 8
 digits long, completely random for security porposes, but do this
 automatically, everytime a record is added. For example, ord5001, ord5002,
 ord5003, etc. Does anyone know how to do this in MySQL?
 --
 View this message in context:
 http://www.nabble.com/auto-increment-format-tf4229677.html#a12032917
 Sent from the MySQL - General mailing list archive at Nabble.com.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]




-- 
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/

Re: Fwd: auto increment format

[Lucas . CTR . Heuman]

Isn't there a chance that you could get a ID that is the same as one 
already in use? and if this happens what happens on the insert?


Wishing you the best you know you deserve,

__
Lucas Heuman
Web Developer
Ricomm Systems Inc. 
FAA, WJHTC/Bldg 300, 3nd Fl., L33
Atlantic City Int'l Airport, NJ  08405
Phone 609.485.5401



Olexandr Melnyk [EMAIL PROTECTED] 
08/07/2007 08:19 AM

To
mysql@lists.mysql.com
cc

Subject
Fwd: auto increment format






You can do that using a before insert trigger, something like (untested) 
:

CREATE TRIGGER test1bi
BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
NEW.ID = COALESCE( NEW.ID, SHA1(CAST(RAND() AS CHAR)))
  END;


2007/8/7, shivendra [EMAIL PROTECTED]:


 Hi, I'm looking for some basic help. I am developing a MySQL database 
and
 want to auto increment a field, but I don't want it to just count 1,2,3,
 etc. I want the field to be a combination of letters and numbers, at 
least
 8
 digits long, completely random for security porposes, but do this
 automatically, everytime a record is added. For example, ord5001, 
ord5002,
 ord5003, etc. Does anyone know how to do this in MySQL?
 --
 View this message in context:
 http://www.nabble.com/auto-increment-format-tf4229677.html#a12032917
 Sent from the MySQL - General mailing list archive at Nabble.com.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]




-- 
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/

Re: Fwd: auto increment format

[David T. Ashley]

You might not need to do this in the way you are suggesting (depending on
your application).  I'm not sure why you feel you need to combine the
autoincrement with the hash into the same field.  Does it really do harm if
two records have the same hash?

It might work as well to have two separate fields, one that contains the
AUTOINCREMENT value, and a second field containing the SHA1.

The most traditional approach to using cryptographic hashes is to have a bit
of randomness (a string with at least 160 bits of information for SHA1), and
to form the hash as a function of some known quantity plus the randomness.
If + is the concatenation operator, you might use:

hashfield = SHA1(randomness + id + randomness);

As long as the randomness is known only to you, there is no way for an
attacker to make the mapping from id to the hashfield.

What do you mean by security?  What are you trying to protect against?

On 8/7/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:

 Isn't there a chance that you could get a ID that is the same as one
 already in use? and if this happens what happens on the insert?


 Wishing you the best you know you deserve,

 __
 Lucas Heuman
 Web Developer
 Ricomm Systems Inc.
 FAA, WJHTC/Bldg 300, 3nd Fl., L33
 Atlantic City Int'l Airport, NJ  08405
 Phone 609.485.5401



 Olexandr Melnyk [EMAIL PROTECTED]
 08/07/2007 08:19 AM

 To
 mysql@lists.mysql.com
 cc

 Subject
 Fwd: auto increment format






 You can do that using a before insert trigger, something like (untested)
 :

 CREATE TRIGGER test1bi
 BEFORE INSERT ON test1
 FOR EACH ROW BEGIN
NEW.ID = COALESCE( NEW.ID, SHA1(CAST(RAND() AS CHAR)))
 END;


 2007/8/7, shivendra [EMAIL PROTECTED]:
 
 
  Hi, I'm looking for some basic help. I am developing a MySQL database
 and
  want to auto increment a field, but I don't want it to just count 1,2,3,
  etc. I want the field to be a combination of letters and numbers, at
 least
  8
  digits long, completely random for security porposes, but do this
  automatically, everytime a record is added. For example, ord5001,
 ord5002,
  ord5003, etc. Does anyone know how to do this in MySQL?
  --
  View this message in context:
  http://www.nabble.com/auto-increment-format-tf4229677.html#a12032917
  Sent from the MySQL - General mailing list archive at Nabble.com.
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
 
 


 --
 Sincerely yours,
 Olexandr Melnyk
 http://omelnyk.net/

auto increment format

[Boyd Hemphill]

Shivendra:
 
It looks to me based on your example that you are creating a smart field 
where the first three chars are some sort of category and the numeric part is 
random.  If you example is accurate then a char(3) field and a standard 
auto_increment field will do the trick.  If uniqueness is necessary use a 
composite index.
 
If a smart field is truly required then the trigger with hash suggestion is a 
good one, however:  
 
Suggestions to use a hash are a problem because once you have a few million 
rows the likelihood of a collision is quite high if you cannot afford an error 
stopping your application.  This means that if you write a trigger (the obvious 
way to go) you will need to trap any uniqueness violation and try again with 
different salt.  
 
Hope this helps a bit
Boyd
 
 
 
 
 







CONFIDENTIALITY NOTICE: This email  attached documents may contain 
confidential information. All information is intended only for the use of the 
named recipient. If you are not the named recipient, you are not authorized to 
read, disclose, copy, distribute or take any action in reliance on the 
information and any action other than immediate delivery to the named recipient 
is strictly prohibited. If you have received this email in error, do not read 
the information and please immediately notify sender by telephone to arrange 
for a return of the original documents. If you are the named recipient you are 
not authorized to reveal any of this information to any other unauthorized 
person. If you did not receive all pages listed or if pages are not legible, 
please immediately notify sender by phone. 

Re: auto increment format

[David T. Ashley]

On 8/7/07, Boyd Hemphill [EMAIL PROTECTED] wrote:

 Suggestions to use a hash are a problem because once you have a few
 million rows the likelihood of a collision is quite high if you cannot
 afford an error stopping your application.  This means that if you write a
 trigger (the obvious way to go) you will need to trap any uniqueness
 violation and try again with different salt.


Also, I think in the original post you cited security.  I'd be curious to
know what you are protecting against.

Using web browser session identifiers as an example, the most common
approach is to have a fixed part of the session identifier concatenated with
a cryptographic hash.  Typically, the hash is formed based on the index of
the database row concatenated with some secret state known only to the
server, i.e. something like

SHA1(secret_state + row_index + secret_state)

That elminates an attacker's ability to forge a session identifier, because
they can't map from the row index to the hash without possessing the secret
state.

If your application is similar, uniqueness of the hashes may be a
non-issue.  It is true that a hash collision _could_ occur, but it would be
of no consequence because the possibility of collision doesn't help an
attacker.

So, it wasn't clear where your uniqueness requirement came from or if the
hash really needed to be part of a database key.

Dave

need auto increment value

[balaraju mandala]

Hello Everybody,

i want to read the latest value of a autoincrement column from a java
program. How can i do this? i want to do this in a single query insertion,
is it possible?

Re: need auto increment value

[Johan Höök]

Hello,
you can do it in two ways I guess:
one is to do a second select (which you don't want):
SELECT LAST_INSERT_ID().

another way is to use java.sql.Statement.RETURN_GENERATED_KEYS
when you create your Statement, something along these lines:

java.sql.PreparedStatement pstmt =
 myconnection.prepareStatement(INSERT INTO
 mytable(value)VALUES('value'),
 java.sql.Statement.RETURN_GENERATED_KEYS );
pstmt.executeUpdate();
java.sql.ResultSet rs = pstmt.getGeneratedKeys();
while( rs.next())
  System.out.println(My autoincrementid is:  + rs.getInt(1));

/Johan

balaraju mandala skrev:

Hello Everybody,

i want to read the latest value of a autoincrement column from a java
program. How can i do this? i want to do this in a single query insertion,
is it possible?




No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.2/471 - Release Date: 2006-10-10



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Combined Primary Key and Auto Increment Primary Key

[Chris White]

Hi all,

I had somewhat of a performance question.  I have an association table with 2 
unique values which will always be selected by one of the values (never by 
id).  That said, I'm wondering which would be a better gain, having this:

CREATE TABLE association_sample (
   `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
association_id1 INTEGER NOT NULL,
association_id2 INTEGER NOT NULL
)ENGINE=InnoDb;

or

CREATE TABLE association_sample (
association_id1 INTEGER NOT NULL,
association_id2 INTEGER NOT NULL,
PRIMARY KEY(association_id1,association_id2)
)ENGINE=InnoDb;

note that this table will mostly consist of table writes (updates, inserts, 
deletes).
-- 
Chris White
PHP Programmer
Interfuel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Combined Primary Key and Auto Increment Primary Key

[Dan Buettner]

Chris, I'd opt for the first, but with an index on each of
association_id1 and association_id2.

I like always having an identity column to be able to remove or update
an individual entry easily.  But for speed, you'll want indexes on the
other columns.

I would either do no multi-column indexes, or do two - one each way -
since you say you'll be selecting on one or the other.  If you do one
multi-column, but then select only on the 2nd column in the index, the
index will do you no good.

In most cases the overhead of additional indexes isn't too bad.  Only
in extreme cases does it end up being a major consideration, IMHO.

HTH,
Dan

On 10/3/06, Chris White [EMAIL PROTECTED] wrote:

Hi all,

I had somewhat of a performance question.  I have an association table with 2
unique values which will always be selected by one of the values (never by
id).  That said, I'm wondering which would be a better gain, having this:

CREATE TABLE association_sample (
   `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
association_id1 INTEGER NOT NULL,
association_id2 INTEGER NOT NULL
)ENGINE=InnoDb;

or

CREATE TABLE association_sample (
association_id1 INTEGER NOT NULL,
association_id2 INTEGER NOT NULL,
PRIMARY KEY(association_id1,association_id2)
)ENGINE=InnoDb;

note that this table will mostly consist of table writes (updates, inserts,
deletes).
--
Chris White
PHP Programmer
Interfuel

--
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: Combined Primary Key and Auto Increment Primary Key

[Jerry Schwartz]

Unless I completely misunderstand your question, I don't see how the id
field would ever be of use. You said you aren't going to be selecting on
id, only by one or the other of association_id1 or association_id2. If you
are really worried about the importance of inserts / updates / deletes, and
not about retrievals, then don't index anything. A SELECT will have to
serially access the table, while a write will have to shuffle the indices.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: Chris White [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, October 03, 2006 2:20 PM
 To: mysql@lists.mysql.com
 Subject: Combined Primary Key and Auto Increment Primary Key

 Hi all,

 I had somewhat of a performance question.  I have an
 association table with 2
 unique values which will always be selected by one of the
 values (never by
 id).  That said, I'm wondering which would be a better gain,
 having this:

 CREATE TABLE association_sample (
`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
 association_id1 INTEGER NOT NULL,
 association_id2 INTEGER NOT NULL
 )ENGINE=InnoDb;

 or

 CREATE TABLE association_sample (
 association_id1 INTEGER NOT NULL,
 association_id2 INTEGER NOT NULL,
 PRIMARY KEY(association_id1,association_id2)
 )ENGINE=InnoDb;

 note that this table will mostly consist of table writes
 (updates, inserts,
 deletes).
 --
 Chris White
 PHP Programmer
 Interfuel

 --
 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]

Auto increment Primary Index fields in replicated db

[Jeff]

All,

We are using circular replication now on db's that were originally stand
alone.  One problem we have is that all the primary index fields for
most of the tables are auto increment fields.  This prevents us from
writing to both db servers because of confilicting INDEX entries.  Is
there some way to have this work?  Someone on another msg board told me
to look at Auto_increment_increment and Auto_increment_offset to
accompish this but I don't see anything like that in the MySQL docs.

Thanks,

Jeff



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Auto increment Primary Index fields in replicated db

[Kishore Jalleda]

Please refer to these two links, they would give you a clear explaination.
there is also an example in the second link which you can easily test .
http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html ( scroll
down for variables auto_increment_*)

Kishore Jalleda


On 3/13/06, Jeff [EMAIL PROTECTED] wrote:

 All,

 We are using circular replication now on db's that were originally stand
 alone.  One problem we have is that all the primary index fields for
 most of the tables are auto increment fields.  This prevents us from
 writing to both db servers because of confilicting INDEX entries.  Is
 there some way to have this work?  Someone on another msg board told me
 to look at Auto_increment_increment and Auto_increment_offset to
 accompish this but I don't see anything like that in the MySQL docs.

 Thanks,

 Jeff



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Auto-increment indexes

[Adrian Bruce]

Hi, im hoping to solve a problem that is bugging me!

I just moved a database from a development server to production (test), 
several of the fields have auto-increment ticked but as it is mainly a 
data warehouse i thought nothing of it.  I then found that a lot of my 
queries were running slow and the select_full_joins variable was 
incrementing slowly.  EXPLAIN on the queries found that the optimizer 
was ignoring some indexes, when i put the auto-increment back on the 
optimizer suddenly starts to use the index again.


Why would this be??

Thanks
Ade

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]