I am unable to directly move the database from my skeleton template on my staging server to a new target server. This is due to my staging server being newer and using MariaDB than the target server and that the default table type when using MariaDB is 'Aria' which the target server does not support.
Export methods tried
- cPanel Database Backup Wizard
- phpMyAdmin Database export (phpMyadmin-->Home-->Export)
- phpMyAdmin table export (phpMyadmin-->skeleton_table-->Home-->Export)
Results
All of these exported files fail to import on the target with the following SQL error when importing in phpMyAdmin
Error SQL query: CREATE TABLE `xxxx_imageshow_external_source_picasa` ( `external_source_id` int( 11 ) unsigned NOT NULL AUTO_INCREMENT , `external_source_profile_title` varchar( 255 ) DEFAULT NULL , `picasa_username` varchar( 255 ) DEFAULT '', `picasa_thumbnail_size` char( 30 ) DEFAULT '144', `picasa_image_size` char( 30 ) DEFAULT '1024', PRIMARY KEY ( `external_source_id` ) ) ENGINE = Aria DEFAULT CHARSET = utf8 PAGE_CHECKSUM =1; MySQL said: Documentation #1064 - 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 'PAGE_CHECKSUM=1' at line 8
Solution
If you use an intermediate server such as xampp you can convert the 'Aria' tabels in to 'MyISAM' which will then allow for a successful import.
Instructions
- Export the databse on the source/staging server using the cPanel Database Backup wizard
- Create a table in xampp called 'skellyimport'
- Import the cPanel database backup into 'skellyimport'
- At this point tables with 'Aria' table type still exist
- Goto phpMyAdmin --> Home --> 'skellyimport' table --> Export
- Select all of the tables and export them (this option is at the bottom of the page but make sure you get all of the tables)
- Goto phpMyAdmin on the target server and import the database
The skeleton database is now migrated and there are no 'Aria' type tables as the have all been converted to 'MYISAM'. You should also note that when using xampp on Windows all table names are changed to lowercase.
Notes
If you export the database in phpMyAdmin rather than exporting the tables there will be a database create rule and some header code is missing.
Create Database Rule Error
Error SQL query: -- -- Database: `skellyimport` -- CREATE DATABASE IF NOT EXISTS `skellyimport` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; MySQL said: Documentation #1044 - Access denied for user 'futured1'@'localhost' to database 'skellyimport'
To fix this, edit the SQL file and remove the following before importing
-- -- Database: `skellyimport` -- CREATE DATABASE IF NOT EXISTS `skellyimport` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE `skellyimport`; -- --------------------------------------------------------
Variable 'character_set_client' can't be set to the value of 'NULL'
Error SQL query: /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; MySQL said: Documentation #1231 - Variable 'character_set_client' can't be set to the value of 'NULL'
The missing headers causes this error when importing into the target server. I am not sure what they do. But the difference between the phpMyAdmin Database and Table exports seems to be:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */;
I also did a quick google search and found the following articles on the subject saying the same thing that the missing headers is what causes this particular error
Links