0

I am trying to import data from pipe delimited file to mysql. Creating the table works. However, when i try to import data i get this error "ERROR 1467 (HY000): Failed to read auto-increment value from storage engine". I have checked on website for possible errors but any answers. Below is the script I am using. I am quite new to MySql.

SELECT 'Changing database..' as '';
use test

SELECT 'Droing table if it exists' as '';
DROP TABLE IF EXISTS table1;

CREATE TABLE IF NOT EXISTS table1
(
   id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   infohash VARCHAR(100) NOT NULL,
   categories VARCHAR(100) DEFAULT NULL,
   info_url VARCHAR(300) NOT NULL,
   download_url VARCHAR(300) DEFAULT NULL,
   PRIMARY KEY (id)
);

SHOW TABLES;

LOAD DATA  INFILE '/usr/Software/sample.txt'

    INTO TABLE table1
    CHARACTER SET utf8
    COLUMNS
        TERMINATED BY '|'
    LINES
        TERMINATED BY '\n'
    ;
SELECT 'Total rows in table' as '';
select count(*) from table1;

Here is the output i am getting.

mysql> source table_creattion.sql
+---------------------+
|                     |
+---------------------+
| Changing database.. |
+---------------------+
1 row in set (0.00 sec)

Database changed
+---------------------------+
|                           |
+---------------------------+
| Droing table if it exists |
+---------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

+----------------+
| Tables_in_test |
+----------------+
| table1         |
+----------------+
1 row in set (0.00 sec)

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
+---------------------+
|                     |
+---------------------+
| Total rows in table |
+---------------------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

I am not sure why this error is coming. Anyone?

Russell
  • 361
  • 1
  • 8
  • 24
  • Possible duplicate of [mysql Failed to read auto-increment value from storage engine](http://stackoverflow.com/questions/7346934/mysql-failed-to-read-auto-increment-value-from-storage-engine) – alexander.polomodov Mar 15 '16 at 18:28

3 Answers3

0

Try adding your fields name to insert statement as in the example below. As I see it, on sample.txt, you don have to have a value for id column. Please share some sample lines from sample.txt for better assiteance.

LOAD DATA  INFILE '/usr/Software/sample.txt'

    INTO TABLE table1 
(infohash,categories, info_url, download_url )
    CHARACTER SET utf8
    COLUMNS
        TERMINATED BY '|'
    LINES
        TERMINATED BY '\n'
    ;
Juan Pablo
  • 1,213
  • 10
  • 15
  • INTO TABLE table1 CHARACTER SET utf8 FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' (name,infohash,categories, info_url, download_url ) – Russell Mar 17 '16 at 05:36
0

The auto_increment column increase to its up limit 18446744073709551615.

Dylan Su
  • 5,975
  • 1
  • 16
  • 25
0

This is a bug, when the user trys to insert a duplicate value on a uniqe key. My solution is, to insert a new dummy-record with defined ID (and the other required columns) and to delete it afterwards. 

select max(`ID`) from `table1` into @IDmax;
set @IDmax = @IDmax + 1;
insert into `table1` set `ID` = @IDmax;
delete from `table1` where `ID` = @IDmax;
Viktor
  • 1