Showing entries 11 to 14
« 10 Newer Entries
Displaying posts with tag: auto_increment (reset)
Ladies and gentlemen, check your assumptions

I spent some time earlier this week trying to debug a permissions problem in Drupal.

After a lot of head-scratching, it turned out that Drupal assumes that when you run INSERT queries sequentially on a table with an auto_increment integer column, the values that are assigned to this column will also be sequential, ie: 1, 2, 3, …

This might be a valid assumption when you are the only user doing inserts on a single MySQL server, but unfortunately that is not always the situation in which an application runs.

I run MySQL in a dual-master setup, which means that two sequential INSERT statements will never return sequential integers.  The value will always be determined by the  auto_increment_increment and auto_increment_offset settings in the configuration file.

In my case, one master will only assign even numbers, the other only uneven ones.

My …

[Read more]
Why You Want to Switch to MySQL 5.1

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

  • online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
  • Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.


Variable's Day Out #7: innodb_autoinc_lock_mode


Applicable To InnoDB
Introduced In 5.1.22
Server Startup Option --innodb-autoinc-lock-mode=<value>
Scope Global
Dynamic No
Possible Values enum(0,1,2)
Value Meaning
0 Traditional
[Read more]
Resetting auto_increment in MySQL

Today we were doing some basic scaling exercises on some code, which resulted in several million extra rows in one of our tables in one of the development environments. Rather than refresh the data, we decided just to prune those new records and reset the auto-increment value. Imagine my surprise when executing the following code hadn’t [...]

Showing entries 11 to 14
« 10 Newer Entries