Database 'default' is in an inconsistent state with mysql driver

[prayagupa]

Hi @hilton, (It might not be a bug, rather not proper README :) )

I changed application.conf to use mysql as database,

    1 application.secret="3X>kv4k`Cgq/SIho=fx_c>?N0A9@:`JYSds1Hm6i8<Uo/[PJ>=QRlrK>`cWBF=>L"                                                                                     
    2                                                                                                     
    3 #db.default.driver=org.h2.Driver                                                                     
    4 #db.default.url="jdbc:h2:mem:play"                                                                   
    5 #db.default.user=sa                                                                                  
    6 #db.default.password=""                                                                              
    7 db.default.driver = com.mysql.jdbc.Driver                                                          
~   8 db.default.url="jdbc:mysql://localhost/smartad"                                                    
~   9 db.default.user=root                                                                               
~  10 db.default.pass=mysql55                                                                            
   11                                                                                                     
   12 # Automatically apply evolutions when starting the application on the console.                      
   13 applyEvolutions.default = true                                                                      
   14                                                                                                     
   15 slick.default="models.database.*"                                                                   
   16 db.default.logStatements=true                                                                       
   17                                                                                                     
   18 logger.com.jolbox.bonecp=DEBUG                                                                      
   19 logger.scala.slick=DEBUG

I get following error on running the application,

[warn] c.j.b.BoneCPConfig - releaseHelperThreads has been deprecated -- it tends to slow down your application more.
[debug] c.j.b.BoneCPDataSource - JDBC URL = jdbc:mysql://localhost/smartad, Username = root, partitions = 1, max (per partition) = 30, min (per partition) = 5, idle max age = 10 min, idle test period = 1 min, strategy = DEFAULT
[error] c.j.b.h.AbstractConnectionHook - Failed to obtain initial connection Sleeping for 0ms and trying again. Attempts left: 0. Exception: null
[error] application - 

! @6k6cf2b09 - Internal server error, for (GET) [/] ->

play.api.Configuration$$anon$1: Configuration error[Cannot connect to database [default]]
    at play.api.Configuration$.play$api$Configuration$$configError(Configuration.scala:92) ~[play_2.10.jar:2.2.1]
    at play.api.Configuration.reportError(Configuration.scala:570) ~[play_2.10.jar:2.2.1]
    at play.api.db.BoneCPPlugin$$anonfun$onStart$1.apply(DB.scala:252) ~[play-jdbc_2.10-2.2.0.jar:2.2.0]
    at play.api.db.BoneCPPlugin$$anonfun$onStart$1.apply(DB.scala:243) ~[play-jdbc_2.10-2.2.0.jar:2.2.0]
    at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:244) ~[scala-library.jar:na]
    at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:244) ~[scala-library.jar:na]
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'smartad'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.7.0]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[na:1.7.0]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525) ~[na:1.7.0]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.Util.getInstance(Util.java:386) ~[mysql-connector-java-5.1.18.jar:na]

I understood error is because database named smartad doesn't exist. Even after creating a database with that name, it throws following syntax error,

[warn] c.j.b.BoneCPConfig - releaseHelperThreads has been deprecated -- it tends to slow down your application more.
[debug] c.j.b.BoneCPDataSource - JDBC URL = jdbc:mysql://localhost/smartad, Username = root, partitions = 1, max (per partition) = 30, min (per partition) = 5, idle max age = 10 min, idle test period = 1 min, strategy = DEFAULT
[info] play - database [default] connected at jdbc:mysql://localhost/smartad
[debug] c.j.b.StatementHandle - select id, hash, apply_script, revert_script, state, last_problem from play_evolutions where state like 'applying_%'
[debug] c.j.b.StatementHandle - 
              create table play_evolutions (
                  id int not null primary key, hash varchar(255) not null,
                  applied_at timestamp not null,
                  apply_script text,
                  revert_script text,
                  state varchar(255),
                  last_problem text
              )

[debug] c.j.b.StatementHandle - 
            select id, hash, apply_script, revert_script from play_evolutions order by id

[debug] c.j.b.StatementHandle - select id, hash, apply_script, revert_script, state, last_problem from play_evolutions where state like 'applying_%'
[debug] c.j.b.PreparedStatementHandle - insert into play_evolutions values(1, 'ffddd19571fab8683315ca57e627745e0133dd28', 2014-11-10, 'create table "COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL);

insert into COCKTAIL values (1, 'Margarita');
insert into COCKTAIL values (2, 'Caipirinha');
insert into COCKTAIL values (3, 'Pi��a colada');', 'drop table "COCKTAIL";', 'applying_up', '')
[debug] c.j.b.StatementHandle - create table "COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL)
[debug] c.j.b.PreparedStatementHandle - update play_evolutions set last_problem = '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 '"COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL)' at line 1 [ERROR:1064, SQLSTATE:42000]' where id = 1
[debug] c.j.b.StatementHandle - select id, hash, apply_script, revert_script, state, last_problem from play_evolutions where state like 'applying_%'
[error] play - 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 '"COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL)' at line 1 [ERROR:1064, SQLSTATE:42000]
[error] application - 

! @6k6ddhae8 - Internal server error, for (GET) [/] ->

play.api.db.evolutions.InconsistentDatabase: Database 'default' is in an inconsistent state![An evolution has not been applied properly. Please check the problem and resolve it manually before marking it as resolved.]
    at play.api.db.evolutions.Evolutions$.checkEvolutionsState(Evolutions.scala:193) ~[play-jdbc_2.10-2.2.0.jar:2.2.0]
    at play.api.db.evolutions.Evolutions$.applyScript(Evolutions.scala:277) ~[play-jdbc_2.10-2.2.0.jar:2.2.0]
    at play.api.db.evolutions.EvolutionsPlugin$$anonfun$onStart$1$$anonfun$apply$1.apply$mcV$sp(Evolutions.scala:471) ~[play-jdbc_2.10-2.2.0.jar:2.2.0]
    at play.api.db.evolutions.EvolutionsPlugin.withLock(Evolutions.scala:507) ~[play-jdbc_2.10-2.2.0.jar:2.2.0]
    at play.api.db.evolutions.EvolutionsPlugin$$anonfun$onStart$1.apply(Evolutions.scala:461) ~[play-jdbc_2.10-2.2.0.jar:2.2.0]
    at play.api.db.evolutions.EvolutionsPlugin$$anonfun$onStart$1.apply(Evolutions.scala:459) ~[play-jdbc_2.10-2.2.0.jar:2.2.0]

Am I missing something while generating sql script for mysql?

[vijaykiran]

I'm not sure what's causing the issue - can you try getting rid of the insert Piña Colada statement ?

[prayagupa]

Thanks @vijaykiran for looking into it.

Removing insert into and update on Piña Colada doesn't solve the issue. (scripts after removing Piña Colada as below)

1.sql

  # --- !Ups                                                                                                                                                                    

  create table "COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL);                             

  insert into COCKTAIL values (1, 'Margarita');                                                       
_ insert into COCKTAIL values (2, 'Caipirinha');                                                      

  # --- !Downs                                                                                        

  drop table "COCKTAIL";  

3.sql

  # --- !Ups                                                                                                                                                                    

  alter table COCKTAIL alter column "ID" bigint not null auto_increment;                              
  alter table COCKTAIL add column "PRICE_CURRENCY" varchar(3) not null default 'EUR';                 
  alter table COCKTAIL add column "PRICE_AMOUNT" decimal(13,3) not null default 0.00;                 
  update COCKTAIL set PRICE_CURRENCY = 'EUR';                                                         
  update COCKTAIL set PRICE_AMOUNT = 6.50 where NAME = 'Caipirinha';                                  
_ update COCKTAIL set PRICE_AMOUNT = 7.00 where NAME = 'Margarita';                                   

  # --- !Downs                                                                                        

  alter table COCKTAIL drop column "PRICE_CURRENCY";                                                  
  alter table COCKTAIL drop column "PRICE_AMOUNT";   

Problem seems to be with generated DDL script itself,

expected script that works fine is

mysql> create table COCKTAIL (ID BIGINT NOT NULL,NAME VARCHAR(100) NOT NULL);     
Query OK, 0 rows affected (0.09 sec)                           

actual script in conf/evolutions/default/create-database.sql is

mysql> create table "COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL)  ; 
ERROR 1064 (42000): 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 '"COCKTAIL" ("ID" BIGINT NOT NULL,"NAME" VARCHAR NOT NULL)' at line 1

[vijaykiran]

OK, Looks like the generated SQL is invalid for MySQL.

Unfortunately I don't have time to dig into this now, but you should take a look at the Global.scala which should be generating MySQL compatible scripts.

https://github.com/lunatech-labs/play-slick-examples/blob/master/app/Global.scala#L31

[devYonz]

This may no be related but the evolution likes to start from a fresh DB or have the play_evolutions table populated correctly. When I ran into an inconsistent DB error I just dropped all tables and it worked [except sqlite tab_*]. Once I turned off applyEvolutions I was able to see more messages that led me to debug and conclude it didn't like the existing tables.