Menu
I've created a table with a primary key and enabled AUTO_INCREMENT, how do I get MYSQL use AUTO_INCREMENT?
CREATE TABLE IF NOT EXISTS test.authors ( hostcheck_id INT PRIMARY KEY AUTO_INCREMENT, instance_id INT, host_object_id INT, check_type INT, is_raw_check INT, current_check_attempt INT, max_check_attempts INT, state INT, state_type INT, start_time datetime, start_time_usec INT, end_time datetime, end_time_usec INT, command_object_id INT, command_args VARCHAR(25), command_line VARCHAR(100), timeout int, early_timeout INT, execution_time DEC(18,5), latency DEC(18,3), return_code INT, output VARCHAR(50), long_output VARCHAR(50), perfdata VARCHAR(50) ); Here is the query I used, I've tried "" and "1" for the first value but it doesn't work.
INSERT INTO test.authors VALUES ('1','1','67','0','0','1','10','0','1', '2012-01-03 12:50:49','108929','2012-01-03 12:50:59','198963','21','', '/usr/local/nagios/libexec/check_ping 5','30','0','4.04159','0.102','1', 'PING WARNING -DUPLICATES FOUND! Packet loss = 0%, RTA = 2.86 ms','', 'rta=2.860000m=0%;80;100;0'); 
597
One of the important tasks while creating a table is setting the Primary Key. The Auto Increment feature allows you to set the MySQL Auto Increment Primary Key field. This automatically generates a sequence of unique numbers whenever a new row of data is inserted into the table.
To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.
Basically this is a bug in MySQL that causes the problem but a work around is simple. The problem happens when the Auto-Increment value of a table grows beyond the limit. Just run this SQL query in MySQL to fix the bug. Table_name is the name of the table where you found the error while inserting data into.
A primary key is by no means required to use the auto_increment property - it just needs to be a unique, not-null, identifier, so the account number would do just fine.
Set the auto increment field to NULL or 0 if you want it to be auto magically assigned...
193
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
