Active Member ReFresh 2340 Posted January 21 Active Member Share Posted January 21 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 I'll be always helpful! Link to comment Share on other sites More sharing options...
Forum Moderator Gurgarath 2514 Posted January 21 Forum Moderator Share Posted January 21 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 Gurgarath coming soon Link to comment Share on other sites More sharing options...
Management AZICKO 7347 Posted January 21 Management Share Posted January 21 If can help (for column reserved name and password function) 1 Scamming ? Reselling ? metin2.download | metin2.dev | fr.metin2.dev | metin2dev.org | metin2.top | top-metin2.org Link to comment Share on other sites More sharing options...
Active Member ReFresh 2340 Posted January 21 Author Active Member Share Posted January 21 (edited) 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 I'll be always helpful! Link to comment Share on other sites More sharing options...
Forum Moderator Gurgarath 2514 Posted January 21 Forum Moderator Share Posted January 21 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). Gurgarath coming soon Link to comment Share on other sites More sharing options...
Active Member ReFresh 2340 Posted January 21 Author Active Member Share Posted January 21 (edited) 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 I'll be always helpful! Link to comment Share on other sites More sharing options...
Forum Moderator Gurgarath 2514 Posted January 21 Forum Moderator Share Posted January 21 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 1 Gurgarath coming soon Link to comment Share on other sites More sharing options...
Active Member ReFresh 2340 Posted January 21 Author Active Member Share Posted January 21 (edited) 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 I'll be always helpful! Link to comment Share on other sites More sharing options...
Ekinox 20 Posted January 26 Share Posted January 26 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now