WampServer

I am developing a site locally using wampserver 2.5 & wordpress 4.3.1. At the time of uploading the almost completed site to live server for final checkup, MySQL database uploading failed showing error #1273 - unknown collation: 'utf8mb4_unicode_ci' which implies that the MySQL in server is of lower version than the development PC's MySQL version. I checked it & found that the live server has MySQL 5.1.16 while my dev.pc has MySQL 5.6.17.

Is it any way possible to downgrade the MySQL version in Wamp from 5.6.17 to 5.1.16? I just cannot change my hosting service now - I have to change the MySQL version in my dev.pc. Any suggestion is welcome.

Hi,

'utf8mb4_unicode_ci' is supported by MySQL as of version 5.5.3.

Your databases are created with collation 'utf8_unicode_ci', it is not possible to downgrade the mysql version as the version will not support 'utf8mb4_unicode_ci'.

The solution is to modify the export SQL file before import with replacement of all occurences of 'utf8mb4' by 'utf8'

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Can you please explain the following as I am not very much conversant with all the internal details.

The solution is to modify the export SQL file before import with replacement of all occurences of 'utf8mb4' by 'utf8'

If you can point me to some reference, I shall be able to manage it. Thanks for the prompt help.

I think what Oto is saying, even if you downgrade the MYSQL version in your local WAMPServer, you are still going to have to edit that backup so that you can load the MYSQL5.6.17 data into the MYSQL5.1.16 version you install on your local server.


So simply use an editor to find 'utf8mb4' and replace it with 'utf8' and then upload the amended backup file

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.3.4 64bit) Aestan Tray Menu 3.2.5.4
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.23>
Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Hi,

To transfert your database from the local site to the live server you "export" the database to a SQL file.

Edit the SQL file and replace all occurrences of utf8mb4 by utf8, save the modified SQL file then "import" this file to the database of the live server.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Thank you for the response RiggsFolly. I actually tried to compare the backups. I have created a fresh wordpress installation in my local dev.pc and another in live server. I didn't enter any thing extra and took backup of both the databases uncompressed. I found in many places in the data from local pc has the following extra terms occuring:

COLLATE utf8mb4_unicode_ci

But in some other places there are chuncks of data mismatching. However, let me try this method and see what happens.

In my original post I wanted to know if I can "downgrade" the MySQL version. I meant to say is it possible to remove MySQL version 5.6.17 and install version 5.1.16? Ofcourse I understand that existing databases cannot be downgraded but I can re-develop my pending projects in a lower version of MySQL and upload it to the live server. Wampserver provides a easy & reliable way of offline hosting for development purpose. Creating an equivalent environment with manual installation of Apache, MySQL & PHP is quit a hassle. Kindly suggest a possible way out. Thanks again.

Hi,
As you can see in my signature, I use many versions of MySQL
To morrow I'll explain how you can do because now I use Smartphone and it is not simple to write.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Thank you Otomatic. It will be a pleasure to learn this thing. I will be eagerly waiting for your post. Thanks again for the wonderful support.

Hi,

I have tried the SQL backup editing method & it worked out. I have used the following search & replace method:

Replace COLLATE utf8mb4_unicode_ci with '' (replace with null)
Replace utf8mb4_unicode_ci with utf8mb4
Replace COLLATE=utf8mb4 with '' (replace with null)
Replace utf8mb4 with utf8

Set of commands may look like redundant but worked out well for me. However, I still want to learn how multiple MySQL / PHP version can be installed in one single workstation.

Thanks again.

HowTo: Install a new version of MySQL into wamp and migrate your data properly.


Please read the whole post before starting this process. Mistakes can cause untold hassle.

Also take a copy of your WHOLE C:\WAMP folder so you can replace it if necessary if something goes wrong.

A backup of your databases is also a good idea.


Assumption: That you are using the default wamp config where all your databases are stored in the mysql.x.y.x\data\ folder.
If thats the case no backup of your databases is necessary, but it would not hurt, just in case.
This should also work if you are using innodb databases, stored in the mysql.x.y.x\data\ folder.


This will take your complete MySQL environment, including users, passwords and privilages so they do not need to be re-created on the new release.


I have tried to keep it simple here using c:\wamp, but you may have wamp on another drive, just change c: to whatever you use.

Before you start read the upgrade notes on the mysql.com site for information on anything you may have to do to your existing data before you start the upgrade.
There should be a page devoted to major upgrades, you will have to read between the lines if you are doing a minor upgrade.

A glance at the changelog is also a good idea as it will give you a clue as to what has changed and how that may effect your SQL and or data.


So here is the process:

1. Download the ZIP version of the mysql you want to run ( not the msi installer, only because I dont know what it may do or assume when it installs )
Suggested source [www.mysql.com]

2. From wamp manager, Stop all services.

3. Go to c:\wamp\bin\mysql\ and create a new folder named appropriately i.e mysql5.5.28

4. Unzip the mysql and copy the contents of the MySQL folder into your newly created folder. Make sure the directory levels are the same as your existing MySQL folder.

5. Copy all the contents of your previous mysql\mysql5.5.10\data folder into the new data folder under your newly created version folder.
Thats EVERYTHING not just your databases, including the ibdata1, ib_logfile0, ib_logfile1 and all the mysql-bin.0000xx files.
We are going to use the recommended mysql-upgrade tool so this is ok to do.
This will also ensure that you keep all your Users and password and granted privilages intact when the upgrade is complete.

6. Copy c:\wamp\bin\mysql\mysql5.5.10\wampserver.conf to c:\wamp\bin\mysql\mysql5.5.28\wampserver.conf
All the folders in here are relative to where this file lives, so normally nothing need changing in here.

7. RIGHT CLICK wampmanager -> refresh
This will make wamp manager find your new version.
Check this by Left click wampmanager -> MySQL -> Versions Your new version should be available in the list.

8. From wamp manager -> MySQL -> Version click on your new version.
Normally you would have to uninstall the old wampmysql service and re-install the new wampmysql service, but wamp takes care of all that for you by just clicking the new version number.
This should also start the new mysql service, so now the new mysql is running.
BUT DONT DO ANYTHING YET!!!

9. Launch a command window ( using Run As Administrator ).
CD into c:\wamp\bin\mysql\mysql5.5.28\bin folder ( the new releases bin folder ).
And run
mysql_upgrade -u[your MySQL admin userid] -p > upgrade_results.txt

It will ask you for the password for the admin user you provided on the -u parameter.
enter the password.

Wait while it runs, If you have very large databases this may take some time but normally it will take anything from a few seconds to a minute or two.
This will look at your databases and do any upgrades that are required and also any upgrades/maintenace of the mysql database that MySQL uses internally.
Thats the tables that contain your userids and password and privilages and other things.

10. Check for errors in the upgrade_results.txt file, fix if there are any.
You can repeat this as often as you like, just refresh the data from the old versions data folder each time.

11. LEFT Click wamp manager -> MySQL -> Service -> Stop Service.
Then again and Start Service. This will load the changed mysql database with any new/changed config info.

12. At this point assuming you are staying on the newly installed version of MySQL its a good idea to run:
wampmanager -> MySQL -> MySQL Console
and run a
> reset master;

To reset the log files.



Thats it your are ready to go and all your userid's and data are converted to the new MySQL version.

TROUBLESHOOTING

The Beauty of WAMP
If you find you have a problem with one of your databases you can just switch the MySQL Version back to the old version, fix the databases using MySQL Workbench or phpMyAdmin.
The copy all the contents of the data folder into your new release and re-run the mysql_upgrade.exe as many times as it takes to fix your issues.

And of course if it all goes bad in a big way, you can switch the MySQL version back to whatever you were using before and all your data is still there UNTOUCHED.


Now this may seem obvious but I am going so say it anyway:
1. If everything goes smoothly you do have to remember that you now have 2 versions of the same database(s) that are being maintained SEPERATELY.
2. It is not possible to use this process to DOWNGRADE your databases!!!

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.3.4 64bit) Aestan Tray Menu 3.2.5.4
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.23>
Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Hi RiggsFolly,

I have already gone through this process you mentioned. It was published earlier. Before publishing my post I have thoroughly gone through all the previous posts and their I found it. The last line mentioned in the article may have skipped your view.

"2. It is not possible to use this process to DOWNGRADE your databases!!!"

I tried the procedure and it didn't work out. As I mentioned in my last post that this time I managed to upload my database by manually editing, but probably it was a good luck. Because the changes in collation made additional 4 bytes to be added to the records. This time probably I did not have any such record otherwise it will get truncated at the time of importing data to the live server & may have made the live site a mess. The site you may check has a minimalist design [comcomsol.in].

However I am waiting for Otomatic's article of how to install multiple Apache / MySQL / PHP in a single PC / Wamp installation.

Hi manas


When I said "2. It is not possible to use this process to DOWNGRADE your databases!!!" It was supposed to imply that you cannot use MYSQL_UPGRADE to DOWNGRADE a database FOR OBVIOUS REASONS i.e. Look at the processors name!!!!!!!


However you should be able to use your BACKUP FILE ( with edits ) to RESTORE that database to the OLDER versions of MYSQL. Just like you restored it to your live site.

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.3.4 64bit) Aestan Tray Menu 3.2.5.4
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.23>
Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Hi,

> I am waiting for Otomatic's article of how to install multiple Apache / MySQL / PHP in a single PC / Wamp installation.

No, the purpose of this discussion is to achieve only what you can use your database created with MySQL 5.6.17 to a host that uses MySQL 5.1.16. But especially not to write a procedure to several versions of Apache, PHP and MySQL.

The critical point is that it will - in every way - can not use mysql 5.1.16 if the structure of your tables includes statements utf8mb4.

Also, if the fields in your tables include utf8mb4 characters, that is to say, Unicode characters more than three bytes, it will also be impossible to alter the tables in any short utf8, so not use mysql 5.1.16.

So before I start writing a procedure to add MySQL 5.1.16 (In fact, I never give procedures that I have not actually worked), it is essential that you look if the structures your tables include collation or charset utf8mb4.

Nevertheless, I repeat, the best solution is that your host uses a MySQL equal to or after 5.5.3.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

So it looks like your only option is to compare collations that exist on MYSQL5.1 and MYSQL5.5

Alter your schema on your MYSQL5.5 database to use only collations etc that existed in MYSQL5.1 or compatible collations. Ensureing that that does not compromise you data in any way!! If that is in fact possible.

Backup your amended 5.5 database and restore it to your new 5.1 database.

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.3.4 64bit) Aestan Tray Menu 3.2.5.4
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.23>
Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-