1

I have some MySQL scripts that are needed for recreating a database. They work fine when I execute them on the command line using the mysql command.

Now I wrote a Java class that should execute these scripts using a JDBC connection to the MySQL database.

One line in a "create table"-statement in the script is:

registration_date DATETIME DEFAULT CURRENT_TIMESTAMP

This line however won't be executed using the JDBC-MySQL connection. I get the error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Invalid default value for 'registration_date'

The relevant method is shown below. sqlScriptPathpoints to the folder containing the sql scripts. The connectionString has this content: "jdbc:mysql://localhost:3306/testDb?useUnicode=yes&characterEncoding=UTF-8&allowMultiQueries=true"

    public static void recreate(String connectionString, String dbUser, String dbPass, String sqlScriptPath) throws Exception {
    // Find and filter sql scripts
    File file = new File(sqlScriptPath);
    File[] scripts = file.listFiles(new FileFilter() {
        @Override
        public boolean accept(File file) {
            return file.getName().endsWith(".sql");
        }
    });

    List<File> scriptsList = Arrays.asList(scripts);
    Collections.sort(scriptsList);

    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Connection conn = DriverManager.getConnection(connectionString, dbUser, dbPass);

    // Load each script and apply it
    for (File f : scriptsList) {
        System.out.println("Importing script: " + f);
        List<String> lines = Files.readAllLines(Paths.get(f.getAbsolutePath()), Charset.forName("UTF-8"));

        StringBuilder sb = new StringBuilder();
        for (String line : lines) sb.append(line).append("\n");
        String sqlStatement = sb.toString();
        System.out.print(sqlStatement);

        Statement st = conn.createStatement();
        st.execute(sqlStatement);
        st.close();
    }
}

And the relevant part of the script:

CREATE TABLE user
(
  id                     INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  username               VARCHAR(255),
  password               VARCHAR(255),
  age_group              INT,
  registration_date      DATETIME DEFAULT CURRENT_TIMESTAMP
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

What is the problem here?

Björn Jacobs
  • 4,033
  • 4
  • 28
  • 47
  • possible duplicate of [How do you set a default value for a MySQL Datetime column?](http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column) – Luiggi Mendoza Apr 28 '14 at 22:21
  • 1
    @LuiggiMendoza My problem is that MySQL behaves different when using a JDBC connection than using the mysql commandline tool. My scripts work using the tool but they don't using Java JDBC. The MySQL-server is the same in both cases. – Björn Jacobs Apr 29 '14 at 09:11
  • @LuiggiMendoza Your linked question has a different problem. So closing this question is not justified. – Björn Jacobs Apr 29 '14 at 09:39
  • Isn't MySQL method `CURRENT_TIMESTAMP()` (note the parenthesis usage). – Luiggi Mendoza Apr 29 '14 at 14:24
  • @LuiggiMendoza With parenthesis it is the application of the function. Without the parenthesis it's the function itself which is applied on an insert statement. – Björn Jacobs Apr 29 '14 at 14:44
  • How are you executing the statement through JDBC? – Luiggi Mendoza Apr 29 '14 at 14:46
  • I have a `Connection` object to my MySQL Database. Then I read each SQL script from file into a String called sqlStatment. Afterwards I create a new `Statement` and execute it. (Statement st = conn.createStatement(); st.execute(sqlStatement); st.close();) – Björn Jacobs Apr 29 '14 at 14:50
  • Post the relevant code in the question. – Luiggi Mendoza Apr 29 '14 at 14:52

1 Answers1

-1

I inherited a Java test harness for a MySQL database that was failing with this error on a datetime column defined as NOT NULL with no default defined. I added DEFAULT NOW() and it worked fine after that.

user4239806
  • 1