Possibility of upgrading MySQL database from 5.6.51 to MySQL 8.0?

[ReFresh 2277]

Hey guys,

I want to ask, if it's possible to upgrade MySQL database from 5.6.51 to MySQL 8.0 or to MariaDB, depends on what would be easier, if possible. I want to keep all my data + structure. I saw some tutorials here on the forum, but I'm confused from them. They're not clear for me. I was trying to manage the upgrade whole day, but I wasn't successful and that's really heart breaking for me, because now it's not possible to download an old packages from FreeBSD trees, they're all removed and forever gone   So I cannot do my hobby in my free time.

I would be really glad, if someone can help me with that.

Thanks for possible answers!

Sincerely,

ReFresh

[Gurgarath 2450]

Hello, fairly easy. It is a drop-in replacement basically and once you have compiled (which should only eventually trigger deprecations warnings), you will have to edit all the queries containing "reserved keywords" to enclose them with backticks `. One of the new one is "Window" as you can see and this is a keyword we use in our queries, notably in item.sql.

https://dev.mysql.com/doc/refman/8.0/en/keywords.html 

 

 

 

[AZICKO 7139]

If can help (for column reserved name and password function)

 

[ReFresh 2277]

Thanks guys, but your answers aren't clear. It won't help me with anything. There are tons of things, which I need to know to do the "simple" database version change and that's a big problem for me.

Firstly, I'll need to know this:

1. How to export databases with data to make it much compatible as possible? Is possible to export databases from MySQL 5.6.51 and then import them to MySQL 8.0 without errors?

What I tried:

1. I downloaded some last .vdi file with preinstalled MySQL 5.6.51 to successfully run the queries to the database without errors.

2. What now? I tried to export account, common, hotbackup, log, player databases to .sql files one by one containing data + structure. Then I went to server where I had MySQL 8.0 installed and tried to import those .sql files. What happened? Tons of errors appeared and tons of tables were empty.

3. I tried to use Navicat function named Reverse Database to Model, then I clicked on File --> Convert Model to --> Model type -->Physical --> Target Database --> Database --> MySQL --> Version 8.0 --> Export SQL or synchronize to database (I tried both). Again what happened? Only structure of the database without data was exported.

Second thing, which as I can see, it'll be needed to change some things in server source code. But I'll ask later for that. As first I need to successfully import the tables to MySQL 8.0 without errors and with all the data.

Hm... Just managed to fix the error with mob proto: 1265 - Data truncated for column 'size' at row 1 by replacing INSERT INTO to INSERT IGNORE INTO in mob_proto query. Is that simple to solve these errors or should I take a care of them?

Edited January 21 by ReFresh

[Gurgarath 2450]

Our answers cannot be clearer. It is basically a full tutorial on sources changes from MySQL <= 5.7 to MySQL 8. For the database export, basically read the errors and fix them. Most of them can be fixed by adding "INSERT IGNORE INTO" and by disabling MySQL strict mode in my.cnf (or session-wide, but prone to issues).

[ReFresh 2277]

So all I need to do is what Asikoo said in the topic, which he mentioned?

1. You must compile your "db" and your "game" with the MySQL 8.0 includes.

- That means I need to replace some MySQL includes in server source and then compile game and db again? And surely I'll need to edit makefile too right? And where I'll find the includes for example for 64 bit machine? Or can I compile it on 32 bit and then run the server on 64 bit?

2. Change the password encryption method or imitate the PASSWORD() function (it has been removed)

3. Some columns must be wrapped (in the cpp file) ("window", "rank" for example...)

- window, rank are all columns, which I need to edit or there're more?

And that's all? It's that simple?

Edited January 21 by ReFresh

[Gurgarath 2450]

1 minute ago, ReFresh said:

So all I need to do is what Asikoo said in the topic, which he mentioned?

1. You must compile your "db" and your "game" with the MySQL 8.0 includes.

- That means I need to replace some MySQL includes in server source and then compile game and db again? And surely I'll need to edit makefile too right?

2. Change the password encryption method or imitate the PASSWORD() function (it has been removed)

3. Some columns must be wrapped (in the cpp file) ("window", "rank" for example...)

- window, rank are all columns, which I need to edit or there're more?

And that's all? It's that simple?

This is what I said in my own topic, except the PASSWORD() which I actually forgot (I already did not use MySQL5 hashing when I upgraded).

This is this simple, indeed, drop-in replacement, just enclose reserved keywords from queries with backticks `, use another password encryption and execute queries with disabled strict mode

[ReFresh 2277]

Where I'll find the MySQL includes for example for 64 bit machine? And can I compile db and game on 32 bit machine and then run the server on 64 bit machine? And the makefile need to be edited too right? That's the last thing I need to know.

Edited January 21 by ReFresh

[Ekinox 15]

Hello

To use mysql 8.0 you simply need to include ' ' in mysql database server side queries on mysql 8.0 reserved words.
You can find the list of reserved swords here:
https://dev.mysql.com/doc/refman/8.0/fr/keywords.html

For example for account
your SQL queries containing the word account on the database side should look like 'account' just add the includes ' ' around these words

Concerning the PASSWORD function, for example, simply replace it with MD5

You don't have to change anything else

Edited January 26 by Ekinox