Urgent: Migrating Database & Errors
Permalink 1 user found helpful
I've been following the documentation moving from localhost to a dreamhost server. I've created a new database, but I'm getting errors when I import the the database from my localhost. Most notably error #1215 checking for foreign keys. I'm not all too familiar with database related terminology just yet. Any help would be greatly appreciated.
Not sure if this solves your error, but as a general principle with 5.7 and v8, always export and import databases using the same tool, ideally the mysql command line.
In both instances I exported using phpmyadmin's export tool. Does doing it though the command line do it any different? This is the error message I'm getting on the site I'm working on:
An exception occurred while executing 'SELECT t0.pkgID AS pkgID_1, t0.pkgHandle AS pkgHandle_2, t0.pkgVersion AS pkgVersion_3, t0.pkgIsInstalled AS pkgIsInstalled_4, t0.pkgAvailableVersion AS pkgAvailableVersion_5, t0.pkgDescription AS pkgDescription_6, t0.pkgDateInstalled AS pkgDateInstalled_7, t0.pkgName AS pkgName_8 FROM Packages t0 WHERE t0.pkgIsInstalled = ? ORDER BY t0.pkgID ASC' with params [1]: SQLSTATE[42S02]: Base table or view not found: 1146 Table '2017pcdwebdb.Packages' doesn't exist
These probably aren't your issues, but...
Database case sensitivity can be a problem if your localhost is Windows.
Some MySQL installations have different defaults/settings for handling nulls or blanks (forgive my vagueness).
Database case sensitivity can be a problem if your localhost is Windows.
Some MySQL installations have different defaults/settings for handling nulls or blanks (forgive my vagueness).
@Gondwana,
Thanks. You're right, that's what it was. I'm running MAMP for my localhost, but I guess the settings were not case sensitive. I had to add a renaming directive to my sql file for the 12 or so tables that were in the wrong case. I deleted those tables from my database and uploaded the new sql file with the renaming directives and ta-da. It worked. It only took a lot of research to figure out how to do it ;)
Thanks. You're right, that's what it was. I'm running MAMP for my localhost, but I guess the settings were not case sensitive. I had to add a renaming directive to my sql file for the 12 or so tables that were in the wrong case. I deleted those tables from my database and uploaded the new sql file with the renaming directives and ta-da. It worked. It only took a lot of research to figure out how to do it ;)
Glad you got it working. I'm surprised that only 12 tables needed changing. For future reference, I think there's an add-on or job or script that renames the tables for you. Plus, there's a MySQL .ini line that can avoid repeat problems in future.
I tried running a script that contained all the table names and it kept error-ing out because the first table name didn't match. So I painstakingly went through and found all the lowercase instances in my localhost database tables and pulled the rename tables script for those individual table names. I thought it was strange too. I'll have to go back and look at the settings for my localhost db to see if I can avoid this headache in the future.
I have had this issue when going from MySQL (WAMP) on my PC to Linux systems (LAMP). WAMP appears to be case insensitive, which is fine util you move it up to a Linux server.
In my.ini, I have
[mysqld] lower_case_table_names = 2
Values for lower_case_table_names in mysql:
0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.
0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.
Values for lower_case_table_names in mysql:
0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.
0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.
Values for lower_case_table_names in mysql:
0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.
0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.
