Nightmare Database Server Migration

  • Resolved Soprano

    (@soprano)


    15 years, 1 month ago

    Hey,

    For the last couple of days I’ve been trying to move my blog from one server to another (bluehost to justhost), I moved the files successfully and changed the dns’s of the domain name but the database is turning out to be the worst part.

    First I tried the DB Backup plugin but it kept freezing after a few hours and took ages everytime.I then decided to try directly through PhpMyAdmin, it exported successfully (3 and then 11 mb’s were the sizes of the first two attempts but reading the codex and with the right settings the next export attemps were 37, 36, 40 mb’s, always variating in size even though the site is completely closed).

    The problem comes when I attempt to import the database, the first few times when I tried the file in .sql format (also zip and gzip, I kept getting errors like this:

    Error
SQL query: 

Warning: Cannot modify header information - headers already sent by (output started at /usr/local/cpanel/base/3rdparty/phpMyAdmin/export.php:190) in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/ob.lib.php on line 83

Warning: Cannot modify header information - headers already sent by (output started at /usr/local/cpanel/base/3rdparty/phpMyAdmin/export.php:190) in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/header_http.inc.php on line 22

Warning: Cannot modify header information - headers already sent by (output started at /usr/local/cpanel/base/3rdparty/phpMyAdmin/export.php:190) in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/header_http.inc.php on line 23

Warning: Cannot modify header information - headers already sent by (output started at /usr/local/cpanel/base/3rdparty/phpMyAdmin/export.php:190) in /usr/local/cpanel/base/3rdparty/phpMy[...] 

MySQL said:  

#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 '<br />
<b>Warning</b>:  Cannot modify header information - headers already sent ' at line 1
    ERROR 1064 (42000) at line 115161: 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 '<br />
<b>Warning</b>:  Cannot modify header information - headers already sent ' at line 1

    I tried to look the above line up in my code editor but there was only a <br /> on that line.

    Then after a few more attempts I kept getting these errors:

    Script timeout passed, if you want to finish import, please resubmit same file and import will resume.

    I thought maybe I could try a script to help me import the database so I used MYSQLDumper and BigDump but when I point the browser to the config files, it just downloads the file instead of executing it.

    I contacted JustHost and they sayd if I cant fix the syntax problem I would just have to re install wordpress and lose all my posts.

    These are the tables that I have in my database:

    sk2_blacklist
 wpau_active_plugins_info
 wpau_upgrade_log
 wp_ak_domains
 wp_comments
 wp_contact_form_7
 wp_email
 wp_links
 wp_options
 wp_pollsa
 wp_pollsip
 wp_pollsq
 wp_post2tag
 wp_postmeta
 wp_posts
 wp_py_jobs
 wp_py_jobs_data
 wp_ratings
 wp_redirection_groups
 wp_redirection_items
 wp_redirection_logs
 wp_redirection_modules
 wp_search_phrases
 wp_seo_title_tag_category
 wp_seo_title_tag_tag
 wp_seo_title_tag_url
 wp_similar_posts
 wp_sk2_logs
 wp_sk2_spams
 wp_TABLE_STATISTICS
 wp_TABLE_STATISTICS_RAW
 wp_tags
 wp_tag_synonyms
 wp_terms
 wp_term_relationships
 wp_term_taxonomy
 wp_tla_data
 wp_tla_rss_map
 wp_todolist
 wp_usermeta
 wp_users

    The ones that always give me the timeout problems are “wp_redirection_logs” (which has 1,599,028 records) and if I try to export the database without that one (idk if its a wordpress installation table or a plugins), but I then get the msql syntax errors with “wp_TABLE_STATISTICS_RAW” (with 114,050 records).Those are the two biggest tables with records I have.

    Any suggestions?

Viewing 7 replies - 1 through 7 (of 7 total)
  • Thread Starter Soprano

    (@soprano)

    no solutions?

    Are you still using the WP Redirection plugin? If not, delete the wp_redirection tables. if you’re still using the plugin, clear the log table. Check at the plugin site Redirection – Manage 301 redirections without modifying Apache | Urban Giraffe for help with problems with the plugin and log sizes.

    Then optimize the database in PHPMyAdmin to further clean it up. And think about Cleaning options, too: Clean Options ? WordPress Plugins to get rid of other unused plugin options and RSS data.

    Then you should be able to export the databse from Bluehost and import it without timeouts.

    Just some additional info:
    for large files you may need to increase memory allocated to PHP

    Another option might be to backup your core WordPress tables and then restore those, then go back and get other tables.

    MichaelH, Good points….

    Increasing the memory for PHP and WordPress involves putting this line in your .htaccess file

    php_value memory_limit 64M

    or this line near the top of your wp-config.php file

    define('WP_MEMORY_LIMIT', '64M');

    Thread Starter Soprano

    (@soprano)

    Thanks for the replies,

    I dropped the wp_redirection tables and uploaded all the files and folders of the site, I optimized all the tables, then did as Michael suggested and only exported the core tables successfully followed by the rest of the tables successfully but now I get this error on every page not only on my addon domain but my main domain (another different blog):

    Internal Server Error

    The server encountered an internal error or misconfiguration and was unable to complete your request.

    Please contact the server administrator, [email protected] and inform them of the time the error occurred, and anything you might have done that may have caused the error.

    More information about this error may be available in the server error log.

    Additionally, a 500 Internal Server Error error was encountered while trying to use an ErrorDocument to handle the request.

    What could it be?

    Thread Starter Soprano

    (@soprano)

    I fixed the above error (I incorrectly had inserted the memory limit in the .htaccess file) so my main blog is working, but the one I’m trying to migrate now gets the following errors:

    Warning: include_once(/home/sapient2/public_html/triponic.com/wp-includes/pomo/mo.php) [function.include-once]: failed to open stream: No such file or directory in /home/sapient2/public_html/triponic.com/wp-settings.php on line 307

Warning: include_once() [function.include]: Failed opening '/home/sapient2/public_html/triponic.com/wp-includes/pomo/mo.php' for inclusion (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/sapient2/public_html/triponic.com/wp-settings.php on line 307

Fatal error: Class 'Translations' not found in /home/sapient2/public_html/triponic.com/wp-includes/l10n.php on line 407
    Mark Ratledge

    (@songdogtech)

    Sounds like your file upload got cut off before it finished; reupload the core WP directories, like wp-admin. Be sure you’re uploading the correct version of WP, if it’s not 2.8.5.

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Nightmare Database Server Migration’ is closed to new replies.