I am having a table with one column of primary key and auto increment of int(11) data type.
When I am trying to insert into that table, I am getting following error. Failed to read auto-increment value from storage engine Query
When I restart MySQL service, it is working fine.
I am sure that this is not problem with "Auto Increment Number Exceeded", as when I try to insert from outside it is getting inserted properly.
Is this problem with Auto Increment or some other one.
We have a MySQL table that has an auto-incrementing field set as an INT (11). This table pretty much stores a list of jobs that are running in an application. At any given moment during the lifetime of the application, the table could well contain thousands of entries or be completely empty (i.e. everything has finished).
The field is not foreign-keyed to anything else.
The auto-increment seems to randomly reset itself to zero, although we have never actually been able to trap the reset.
The problem becomes evident because we see the auto-increment field get up to, say, 600,000 records or so and then a while later the auto-increment field seems to be running in the low 1000's.
It is almost as if the auto-increment resets itself if the table is empty.
Is this possible and if it is, how do I turn it off or change the manner in which it resets?
If it isn't, does anyone have an explanation of why it might be doing this?
Thanks!
Say I have a MySQL table:
CREATE TABLE tweets (
tweet_id INT NOT NULL AUTO_INCREMENT,
author_id INT NOT NULL,
text CHAR(140) NOT NULL,
PRIMARY KEY (tweet_id)
)
PARTITION BY HASH(tweet_id)
PARTITIONS 12;
All is good. The table lives on a single server - Server1. But eventually I may want to scale out. So I'd want to shard the table and move 6 of the 12 partitions onto a new server - Server2.
1) Is there any quick & easy way to move those partitions from Server1 to Server2?
2) Now that I have 2 servers, how do I make sure the auto-increment tweet_id's generated by the 2 servers don't have the same value? I'd also need to make sure the tweet_id on each partition stays consistent, i.e. on Partition k every tweet_id's modulo 12 equals to k.
3) Ideally I'd like to continue this scale out process. So later on I'd want to add a 3rd server - Server3. I'd want to re-balance the partitions so that there're 4 partitions on each server. Again how do I make sure the auto-increment tweet_id's generated by the 3 servers are distinct and that the hash of tweet_id's stay consistent within each partition?
I just want to have a table in which a first column is a monotonically increasing unique integer number. For example, if I have a table with 5 rows the indexes of the rows should be 1,2,3,4,5. And if I add a new row it gets index 6.
I am alway confused with this things because there are many related options: "auto_increment", "primary key", "unique", "index". What should I use?
Long story short, I have a SQL file that I want to import as a skel style file, so this will be done repeatedly, programmatically. I can edit the SQL file however I want, but I'd rather not touch the application itself.
This application uses userid = 0 to represent the anonymous user. It also has a relevant (blank) entry in the database to represent this 'user'. Hence, the line in my skel.sql looks something like this:
INSERT INTO `{{TABLE_PREFIX}}users` VALUES (0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, '', '', '', NULL);
The problem with this is that uid is a auto_increment field, for which, technically, 0 is an invalid value. Or atleast, if you set it to 0, you're basically telling MySQL, "Please insert the next id into this field."
Now, I suppose I could put an INSERT then an UPDATE query into my SQL file, but is there a way of telling MySQL in general that yes, I actually want to insert 0 into this field?