• State Not Answered
  • Replies 6 replies
  • Subscribers 9 subscribers
  • Views 6565 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Administration

MySQLSyntaxErrorException: Table already exists

Jim Schweitzer
Certified Lead Developer

I'm attempting to migrate from a local 7.5 windows installation to a (new) local 7.6 linux installation. I am also migrating the mysql database from the same previous windows host to the new linux host. I get the following error in the jboss log when trying to start the server for the first time:

17:03:09,825 INFO [stdout] (ServerService Thread Pool -- 57) 2014-09-03 17:03:09,822 [ServerService Thread Pool -- 57] INFO com.appiancorp.rdbms.config.PrimaryDataSourceConfig - Validating and initializing the primary data source: jdbc/app72primary
17:03:12,636 INFO [stdout] (ServerService Thread Pool -- 57) 2014-09-03 17:03:12,634 [ServerService Thread Pool -- 57] INFO com.appiancorp.rdbms.hb.DataSourceManagerHbImpl - [jdbc/app72primary] Checking schema and migrating if necessary (appian/db/changelog/db-changelog-master.xml)...
17:03:16,963 INFO [stdout] (ServerService Thread Pool -- 57) 2014-09-03 17:03:16,962 [ServerService Thread Po...

OriginalPostID-121114

OriginalPostID-121114

  Discussion posts and replies are publicly visible

  • 0
    Certified Lead Developer
    ...ol -- 57] ERROR liquibase - Error executing SQL CREATE TABLE `usr` (`id` BIGINT AUTO_INCREMENT NOT NULL, `username` VARCHAR(255) NOT NULL, CONSTRAINT `PK_USR` PRIMARY KEY (`id`), CONSTRAINT `usr_username_uc` UNIQUE (`username`)) engine=InnoDB
    17:03:16,963 INFO [stdout] (ServerService Thread Pool -- 57) com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'usr' already exists
    ... and so on for a bunch more tables like that.

    I did export/import the appian primary database, so all tables to exist with the migrated data from the old instance.
    It doesn't seem to detect that the db tables already exist.

    One odd thing is that is see both `DATABASECHANGELOG` and `databasechangelog` extant in the database after attempting the startup. same for 2 other tables DATABASECHANGELOGLOCK and DT_MODEL but not any others. Looks something related to case sensitivity. Tried setting lower_case_table_names on the linux box and no change. The old windows instance was ori...
  • 0
    Certified Lead Developer
    ...ginally installed as 7.2, then migrated to 7.5.

  • 0
    Certified Lead Developer
    Looks like the mysql lower_case_table_names setting didn't actually get applied the first time I tried. Did a hard restart of mysql with that setting, and I was able to start the application properly.
  • 0
    Might try this, create a new schema on the new sql server by letting appian startup etc. Then truncate the data leaving the tables, then migrate the data from the old database to the new, truncate the changelog table, import the data from the newly created database \\ changelog and start appian again, see if that works. you will need two new databases to do this. One for a blank install as a new instance, then a second one to move data from the old. lets say appian1 is the new instance, appian 2 is new instance on the new server but is a duplicate of the old windows server sql data.
    Bring appian up on appian 1.... then truncate all tables except dbchangelog or changelog. then copy in the old data from appian2 database not overwritting dbchangelog and then bootup appian2. have to change the appian-ds.xml files and standalone.xml for databases.. luckily you only have to do one driver on linux.. We went from sql to oracle. Its been a real nightmare.
  • 0
    It should then bootup and do an "upgrade" to the existing data and tables. DT_Model should also get some new data and grow a bit.
  • 0
    Certified Lead Developer
    Thanks, I ended up being able to fix my mysql problem with the lower_case_table_names setting on the linux - my first attempt to restart mysqld apparently didn't apply the change
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.