com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException CREATE TABLE

Question

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax

I have a create_table(String table_name) function. There seems to be an SQL syntax error that I'm not able to figure out. I found a lot of threads with similar questions and most of them were left unanswered. Any suggestion would be appreciated.

The complete stack trace:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1006 (Date VARCHAR(10) NOT NULL, Weight FLOAT NOT NULL, Amount FLOAT NOT NULL, P' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
at com.appsmith.card_io.IOstream.create_table(IOstream.java:249)
at com.appsmith.card_io.IOstream$1.actionPerformed(IOstream.java:110)
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:703)
at java.awt.EventQueue.access$000(EventQueue.java:102)
at java.awt.EventQueue$3.run(EventQueue.java:662)
at java.awt.EventQueue$3.run(EventQueue.java:660)
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:676)
at java.awt.EventQueue$4.run(EventQueue.java:674)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:673)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:244)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:163)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:147)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:139)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:97) 

The create table function:

public void create_table(String table_name) {
    StringBuilder sql = new StringBuilder();
    sql.append(String.format("CREATE TABLE %s ", table_name));
    sql.append("(Date VARCHAR(10) NOT NULL, ");
    sql.append("Weight FLOAT NOT NULL, ");
    sql.append("Amount FLOAT NOT NULL, ");
    sql.append("PRIMARY KEY (Date))");
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        stmt.executeUpdate(sql.toString()); //IOstream.java:249
    } catch (SQLException e) {          
        e.printStackTrace();
    }
}

Answer 1

From the docs

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Therefore either quote the table name or rename it to include a letter

sql.append(String.format("CREATE TABLE `%s` ", table_name));