An alternative way to check mySQL version in the Magento check script

Magento, like most other open source softwares, provides a list of requirements which need to be fulfilled before it can be installed on your machine. If you are installing Magento for the first time then I would recommend that you read the server requirments. on the other hand there may be some who would like to know which server requirements are fulfilled by their server so that they can see at a glance what they need to ask their hosting providers to install/setup for them.

Magento provides a script for this purpose which can be viewed on this page. You can download the actual script from here. Unzip the downloaded zip file and you will see a PHP script. Copy the script to the location where you would like to install Magento and execute it.

Once executed you will see the output on the page as follows:

The above snap shot shows that the mySQL version installed does not fulfill the version which is funny as we have mySQL community version 5.1 installed which is more than what is required. I looked through the code and found the following code on line 30 which checks the mySQL version

preg_match('/[0-9]\.[0-9]+\.[0-9]+/', shell_exec('mysql -V'), $version);

Note:The shell_exec() command is not allowed on shared hosting servers as it could allow a coder to run code which may compromise the security and stability of the server.

This could be the reason why the $version was coming up empty and due to this the script was flagging that the mySQL version was incompatible. The shell_exec() command may run correctly on some computers but it did not run as expected on ours so I thought of altering it to show the correct mySQL version.

mySQL provides a function mysql_get_server_info() which returns the mySQL version number. The only thing it needs is a connection to be opened to mySQL so it could then return the information. I commented out line 30 and added the following lines of code:

$conn = mysql_connect('server','username','password');
preg_match('/[0-9]\.[0-9]+\.[0-9]+/', mysql_get_server_info(), $version);

You should know the details to connect to your mySQL server for the above to work. After making the above changes I ran the script again and this time it gave the following output:

Please note that the shell_exec() is not wrong but it does not work on some servers due to restrictions. The method I have provided is a workaround should the first method not work.

Comments?

Sign the petition to save mySQL

After Oracle announced in mid 2009 to acquire Sun there has been much apprehension in the open source community over the future of mySQL. Sun had acquired MySQL AB (the company) and kept it open source however once Oracle acquires it things may not remain the same as mySQL is a competitor to Oracle.

Save mySQL today and sign the petition online other wise people running their websites and stores on mySQL would have to pay a hefty bill.

What is the minimum mySQL version requirement for Zencart?

A client of mine developed his website in Zencart and after careful scrutiny approved the website for deployment to his production machine. He provided the FTP and mySQL information for us to deploy the website.

As is the practice, I check for any version conflicts that may conflict with our system before I give the signal to my team for deployment. Howsoever was I in for a shock. When I checked the mySQL version using the following statement

SELECT VERSION()

I got the result 4.1.x which was not compatible with our system. The minimum mySQL version supported by the latest Zencart version 1.3.8 is 4.3.x.

I at once updated the client of this and realizing his mistake he contacted his hosting support to have the issue resolved.

This just goes to show you that you need to check everything and not leave anything to others no matter who they are because mistakes can happen at any time and at any time. They don’t need a reason to come up so DON’T GIVE THEM THAT REASON.

Hope the above helped

How to add number of days in a date in mySQL?

A client was running a membership driven website for some time now. He had been offering free membership and no membership expiry up till now. However, some where along the way he was bit by the commercial bug 🙂 and he decided on ending free membership for all members. He wanted his members to pay for the membership (6 months). So he asked me to make the necessary adjustments in the code to support his new business requirement.

I made the necessary adjustments in the code and database structure. After making the changes I had to update the new field I had added for the expiry date in the membership table. I could have done it by writing a script and updating all the records but I said hey lets do it in mySQL.

I wrote the following query

update members set date_expiry = adddate(date_created,180);

and ran it in mySQL and it updated all the records.

Hope the above helped.

What to do if you got the “Got error 28 from storage engine” in mySQL?

I had logged into PhPMyAdmin and was trying to add some fields into a table when
I got the error “Got error 28 from storage engine”. I tried to refresh the page but
it kept coming. I closed the browser and then tried to run my website and found that
the same error message was coming on the website. Funny thing was that the message was coming sometimes and other times it worked fine. I Googled around and found that this error comes when there is very little space on the storage drive. I scanned the drive and voila found that the drive had a couple of MB’s free. I cleaned the drive of useless programs and files and the error went away.

How to connect .NET with mySQL?

mySQL is one of the most widely used open source databases around the world. Today this is the database engine driving up to 90% of PHP driven websites on the web. PHP has been the first love of mySQL and it will always have a special place. However, with other technologies coming to the fore like .NET mySQL has come up with its own data provider to help connect .NET applications with mySQL easier.

Most .NET developers would answer that they would use ODBC to connect with mySQL. For that they would most probably download the ODBC driver from the mySQL website, install the driver and then connect the .NET application through it thereby making their application have to communicate with the ODBC and then the ODBC communicate with the mySQL driver which will only slow down performance.

mySQL now provides the Connector/NET drivers for .NET applications which can be downloaded directly from their website. Download the MSI and run it. It will register the MySQL.Data assembly into the Global Assembly Cache. ASP.NET developers would most probably have to place the assembly into their Bin folder and use it.

I have used .NET Connector 5.2 and it worked fantastic with ASP.NET. No performance issues nothing. The best part is that the syntax is similar to the SqlClient namespace so there’s no learning curve and you can start development at once.

Download the .NET Connector 5.2 driver from here

Hope the above helps

Modify the auto increment value in mySQL

If you want to modify the auto increment column to start inserting records from a specific value then you will need to run the following statement.

ALTER TABLE <table_name> AUTO_INCREMENT = 12345;

After running the above statement the insertion will start from that value. However if there are any records which have a value greater than 12345 then the next record will start from 12345 + 1 = 12346

Hope the above helps

Generate mySQL dump for mySQL 4.x and below

If you have developed a website in mySQL 5.x and your client somehow manages to purchase a hosting which supports mySQL 4.x (don’t know how this could happen but it happened to me once) then you can do the following to generate SQL script in the right version by using

mySQLDump
Go to the command prompt. Log into mySQL using the command

mysql –user=<user_name> –password=<pass_word> <db_name>

After logging in issue the command

mysqldump –compatible=mysql40 <db_name>

If you want to learn more about the other options of this great utility then visit mysql.com website.

PHPmyAdmin
This is the easier method. Login into PHPmyAdmin. Select the database. Click on Export. On the Export page you will be provided with a drop down from which you can select the format in which you want to export the dump. After selecting the desired option click on Export.

Hope the above helps.

How to move large sized mySQL database

We have all moved databases from one hosting machine to another by creating SQL dumps with PhpMyAdmin or any other mySQL desktop client on the source machine and then running those SQL scripts on the destination machine. This is fine when the size of the database is a few MBs. However when the size is several hundred MBs then it requires some other procedure. Below is one such procedure for moving a large sized mySQL database.

The following technique applies to mySQL database in which all tables store data in the MyISAM format.

1.  Go to the physical location where the mySQL database maintains its FRM and MDY files on the source machine.

2. Download all the files on which the database is based on.

3. Create database with same name on the destination machine.

4. Upload the files which you have downloaded.

5. Copy the files to the location where the mySQL database maintains the FRM and MDY files on the destination machine. PLEASE STOP mySQL SERVICE ON THE DESTINATION MACHINE BEFORE COPYING THE FILES.

6. Start the mySQL database.

You can start using the database as you normally would.