Learn about Centmin Mod LEMP Stack today
Become a Member

Auto database converter

Discussion in 'System Administration' started by Jon Snow, Apr 9, 2018.

  1. Jon Snow

    Jon Snow Active Member

    333
    54
    28
    Jun 30, 2017
    Ratings:
    +77
    Local Time:
    12:41 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    Is there some tool/program out there that we can use to convert MariaDB to MySQL (and the other way around) instead of manually reviewing and editing the file to fix up syntax errors?

    I have little hope for such a tool but it doesn't hurt to ask. Could save me so much time.
     
  2. eva2000

    eva2000 Administrator Staff Member

    33,688
    7,459
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,470
    Local Time:
    1:41 PM
    Nginx 1.13.x
    MariaDB 5.5
    what do you mean ? mysql queries or mysql data ? mysqldump to sql file backup and import sql in will 99.99%% work for moving data from MariaDB server to Oracle MySQL server and vice versa.
     
  3. Jon Snow

    Jon Snow Active Member

    333
    54
    28
    Jun 30, 2017
    Ratings:
    +77
    Local Time:
    12:41 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    MySQL data. That's exactly what I did and it was throwing a bunch of syntax errors. I couldn't import the sql file into a new database. I had to use a text editor to manually update some things to fix the issues.
     
  4. Jon Snow

    Jon Snow Active Member

    333
    54
    28
    Jun 30, 2017
    Ratings:
    +77
    Local Time:
    12:41 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    "ERROR ???? (??????): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use"

    Import was always failing until the issues were fixed. Dump was made from command line.
     
  5. eva2000

    eva2000 Administrator Staff Member

    33,688
    7,459
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,470
    Local Time:
    1:41 PM
    Nginx 1.13.x
    MariaDB 5.5
    post the exact mysqldump and import commands you used.. you can mask actual databasenames and user/pass

    also post exact errors and fixes you did
     
  6. Jon Snow

    Jon Snow Active Member

    333
    54
    28
    Jun 30, 2017
    Ratings:
    +77
    Local Time:
    12:41 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    Dump: mysqldump -u user -p -h localhost dbname > /path-to/database.sql
    Import: mysql -u user -p dbname < /path-to/database.sql

    There were a bunch.

    One was something like (can't remember if the numbers are the same) :

    ERROR 1064 (42000) at line 1: 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 ''
    Can't really remember how I fixed this one.

    The top of the SQL file just had information about the database name, MariaDB version, etc.

    Another was like (I just copy/pasted the number from the error above) :

    ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the ma
    nual that corresponds to your MySQL server version for the right syntax to use near ''Some-text-in-the-database'

    ^ This one I never solved.

    Another had something like the below at the top and bottom of some table table (I think it was table data):
    Code (Text):
    /*!40101 SET character_set_client = utf8 */;

    The bottom part had to change to "utf8" to be fixed. The top part already had it.

    Another one that I encountered long long ago was related to InnoDB. I had to run an SQL query to mass find specific data to manually remove so that the database import could work.
     
  7. eva2000

    eva2000 Administrator Staff Member

    33,688
    7,459
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,470
    Local Time:
    1:41 PM
    Nginx 1.13.x
    MariaDB 5.5
    does the sql file import properly into another MariaDB server or results in same errors ? what Oracle MySQL version ? what is database for ? which script/app ?
     
  8. eva2000

    eva2000 Administrator Staff Member

    33,688
    7,459
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,470
    Local Time:
    1:41 PM
    Nginx 1.13.x
    MariaDB 5.5
    exact error messages will help
     
  9. Jon Snow

    Jon Snow Active Member

    333
    54
    28
    Jun 30, 2017
    Ratings:
    +77
    Local Time:
    12:41 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    It worked fine when importing into a database running on the same MariaDB version of the MariaDB database I exported. I think it was MySQL 5.5 (I know for certain it wasn't 5.6 because I ended up upgrading to that version). It was an xenForo database. The InnoDB one might have been Wordpress and/or xenForo.

    I'm no longer trying to import the database btw. Just looking for a simple way (if it's possible) to convert in the future.
     
  10. eva2000

    eva2000 Administrator Staff Member

    33,688
    7,459
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,470
    Local Time:
    1:41 PM
    Nginx 1.13.x
    MariaDB 5.5
..