I transferred my data from a Windows instance to a Linux one and am receiving a "java.sql.SQLException: Table 'acaa.Constants' doesn't exist" error. What do I do?

In MySQL, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. Details about this can be found at here. Windows is case insensitive, however all table names are forced to lower case. When a database is transferred, the lowercase table names are transferred to Linux which is case sensitive and causes connection to the AT to fail. The recommended solution is to do a search and replace on the sql file and change all references to the mixed case we use for our table names. We know that this is tedious and we are thinking about adding a utility that can do this as part of our Maintenance Program.

Comments

Encountered w/ v2

We encountered the problem outlined above. After migrating from a MySQL db running on Windows to Linux, we ran the following sql script against the database. Providing in the event someone runs into the same problem.

Replace "DATABASE" with your AT db name. Save as a file and pump into MySQL. If copy and pasting, remove line breaks.

execute

cat at_table_mod.sql | mysql -u root -p DATABASE

at_table_mod.sql

USE DATABASE;
ALTER TABLE `DATABASE`.`accessions` RENAME TO `DATABASE`.`Accessions`;
ALTER TABLE `DATABASE`.`accessionslocations` RENAME TO `DATABASE`.`AccessionsLocations`;
ALTER TABLE `DATABASE`.`accessionsresources` RENAME TO `DATABASE`.`AccessionsResources`;
ALTER TABLE `DATABASE`.`archdescphysicaldescriptions` RENAME TO `DATABASE`.`ArchDescPhysicalDescriptions`;
ALTER TABLE `DATABASE`.`archdescriptiondates` RENAME TO `DATABASE`.`ArchDescriptionDates`;
ALTER TABLE `DATABASE`.`archdescriptioninstances` RENAME TO `DATABASE`.`ArchDescriptionInstances`;
ALTER TABLE `DATABASE`.`archdescriptionnames` RENAME TO `DATABASE`.`ArchDescriptionNames`;
ALTER TABLE `DATABASE`.`archdescriptionrepeatingdata` RENAME TO `DATABASE`.`ArchDescriptionRepeatingData`;
ALTER TABLE `DATABASE`.`archdescriptionsubjects` RENAME TO `DATABASE`.`ArchDescriptionSubjects`;
ALTER TABLE `DATABASE`.`assessments` RENAME TO `DATABASE`.`Assessments`;
ALTER TABLE `DATABASE`.`assessmentsaccessions` RENAME TO `DATABASE`.`AssessmentsAccessions`;
ALTER TABLE `DATABASE`.`assessmentsdigitalobjects` RENAME TO `DATABASE`.`AssessmentsDigitalObjects`;
ALTER TABLE `DATABASE`.`assessmentsresources` RENAME TO `DATABASE`.`AssessmentsResources`;
ALTER TABLE `DATABASE`.`atplugindata` RENAME TO `DATABASE`.`ATPluginData`;
ALTER TABLE `DATABASE`.`bibitems` RENAME TO `DATABASE`.`BibItems`;
ALTER TABLE `DATABASE`.`chronologyitems` RENAME TO `DATABASE`.`ChronologyItems`;
ALTER TABLE `DATABASE`.`constants` RENAME TO `DATABASE`.`Constants`;
ALTER TABLE `DATABASE`.`databasefields` RENAME TO `DATABASE`.`DatabaseFields`;
ALTER TABLE `DATABASE`.`databasetables` RENAME TO `DATABASE`.`DatabaseTables`;
ALTER TABLE `DATABASE`.`deaccessions` RENAME TO `DATABASE`.`Deaccessions`;
ALTER TABLE `DATABASE`.`defaultvalues` RENAME TO `DATABASE`.`DefaultValues`;
ALTER TABLE `DATABASE`.`digitalobjects` RENAME TO `DATABASE`.`DigitalObjects`;
ALTER TABLE `DATABASE`.`events` RENAME TO `DATABASE`.`Events`;
ALTER TABLE `DATABASE`.`fileversions` RENAME TO `DATABASE`.`FileVersions`;
ALTER TABLE `DATABASE`.`inlinetagattributes` RENAME TO `DATABASE`.`InLineTagAttributes`;
ALTER TABLE `DATABASE`.`inlinetags` RENAME TO `DATABASE`.`InLineTags`;
ALTER TABLE `DATABASE`.`indexitems` RENAME TO `DATABASE`.`IndexItems`;
ALTER TABLE `DATABASE`.`listdefinitionitems` RENAME TO `DATABASE`.`ListDefinitionItems`;
ALTER TABLE `DATABASE`.`listordereditems` RENAME TO `DATABASE`.`ListOrderedItems`;
ALTER TABLE `DATABASE`.`locationstable` RENAME TO `DATABASE`.`LocationsTable`;
ALTER TABLE `DATABASE`.`lookuplist` RENAME TO `DATABASE`.`LookupList`;
ALTER TABLE `DATABASE`.`lookuplistitems` RENAME TO `DATABASE`.`LookupListItems`;
ALTER TABLE `DATABASE`.`names` RENAME TO `DATABASE`.`Names`;
ALTER TABLE `DATABASE`.`nonpreferrednames` RENAME TO `DATABASE`.`NonPreferredNames`;
ALTER TABLE `DATABASE`.`notesetctypes` RENAME TO `DATABASE`.`NotesEtcTypes`;
ALTER TABLE `DATABASE`.`rdescreen` RENAME TO `DATABASE`.`RDEScreen`;
ALTER TABLE `DATABASE`.`rdescreenpanelitems` RENAME TO `DATABASE`.`RDEScreenPanelItems`;
ALTER TABLE `DATABASE`.`rdescreenpanels` RENAME TO `DATABASE`.`RDEScreenPanels`;
ALTER TABLE `DATABASE`.`recordlocks` RENAME TO `DATABASE`.`RecordLocks`;
ALTER TABLE `DATABASE`.`repositories` RENAME TO `DATABASE`.`Repositories`;
ALTER TABLE `DATABASE`.`repositorynotesdefaultvalues` RENAME TO `DATABASE`.`RepositoryNotesDefaultValues`;
ALTER TABLE `DATABASE`.`repositorystatistics` RENAME TO `DATABASE`.`RepositoryStatistics`;
ALTER TABLE `DATABASE`.`resources` RENAME TO `DATABASE`.`Resources`;
ALTER TABLE `DATABASE`.`resourcescomponents` RENAME TO `DATABASE`.`ResourcesComponents`;
ALTER TABLE `DATABASE`.`sessions` RENAME TO `DATABASE`.`Sessions`;
ALTER TABLE `DATABASE`.`simplerepeatablenotes` RENAME TO `DATABASE`.`SimpleRepeatableNotes`;
ALTER TABLE `DATABASE`.`subjects` RENAME TO `DATABASE`.`Subjects`;
ALTER TABLE `DATABASE`.`users` RENAME TO `DATABASE`.`Users`;
ALTER TABLE `DATABASE`.`instance_sequence` RENAME TO `DATABASE`.`instance_sequence`;
ALTER TABLE `DATABASE`.`name_sequence` RENAME TO `DATABASE`.`name_sequence`;
ALTER TABLE `DATABASE`.`repeating_data_sequence` RENAME TO `DATABASE`.`repeating_data_sequence`;
ALTER TABLE `DATABASE`.`structured_data_sequence` RENAME TO `DATABASE`.`structured_data_sequence`;

Windows/Linux transfer

I still question the inconsistency among table names some are all lower some are upper and lower. I hope this could be fixed in a future release.