Failed to read auto-increment value from the storage engine in MySQL

Question

My Why this exception occur?

public String setIDAndInsert(String fName, String lName, String gender, String date) {
    String id = null;
    ResultSet res;
    try {
        Connection con;
        PreparedStatement pStatement;
        String query = "insert into Users(FirstName,LastName,Gender,Date) " +
                "values ( '" + fName + "' , '" + lName + "' , '" + gender + "', '" + date + "' ) ";
        con = DriverManager.getConnection(...);
        pStatement = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        pStatement.execute(query);
        res = pStatement.getGeneratedKeys();
        while (res.next()) {
            id = String.valueOf(res.getInt(1));
        }

    } catch (SQLException sqle) {
        sqle.printStackTrace();
    }
    return id;
}

Previously this method works correctly, I don't know why now hasn't work!

My table in console:

enter image description here

java.sql.SQLException: Failed to read auto-increment value from storage engine
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:734)
    at Project.UserPage_Admin.setIDAndInsert(UserPage_Admin.java:145)
    at Project.UserPage_Admin.addAction(UserPage_Admin.java:127)
    at Project.UserPage_Admin.actionPerformed(UserPage_Admin.java:114)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6505)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at java.awt.Component.processEvent(Component.java:6270)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4861)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4687)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2719)
    at java.awt.Component.dispatchEvent(Component.java:4687)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:723)
    at java.awt.EventQueue.access$200(EventQueue.java:103)
    at java.awt.EventQueue$3.run(EventQueue.java:682)
    at java.awt.EventQueue$3.run(EventQueue.java:680)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:696)
    at java.awt.EventQueue$4.run(EventQueue.java:694)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:693)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

See if this link helps you: http://ask.amoeba.co.in/failed-to-read-auto-increment-value-from-storage-engine-mysql/ — Jorge Campos, Nov 21 '13 at 15:56
`int(12)` is a rather weird datatype (it is +/- a 39 bits integer), are you sure the value still fits a 32 bit (signed) integer? A java integer can take up to an int(10) (or a 'normal' SQL INTEGER). See also http://stackoverflow.com/questions/7346934/mysql-failed-to-read-auto-increment-value-from-storage-engine — Mark Rotteveel, Nov 21 '13 at 16:40
@MarkRotteveel I changed it to `12` for training the `alter` statement is `sql`. what number should be it? — Sajad, Nov 21 '13 at 19:40

score 0 · Accepted Answer · answered Nov 21 '13 at 19:46

0

Try this:

ALTER TABLE `table_name`  AUTO_INCREMENT = 1

answered Nov 21 '13 at 19:46

Sajad

2,273
11
49
92

Failed to read auto-increment value from the storage engine in MySQL

1 Answers1

Linked