How to resolve the Zen Cart Illegal mix of collations error

We had deployed a Zen Cart website and we came up with the following error in the Zen Cart admin panel when clicking on the Tools -> Send Email link.

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation 'locate'

After checking the table collation in PHPmyAdmin I found that the subscribers table (created when the Newsletter Subscribe module was installed) collation was different to the configuration table.

Most people on the internet advised that the collation for the subscribers table and email_address field should be changed to that of the remaining tables in the database. I tried to change the collation of the subscribers table and the email_address field to that of the configuration table but met with no success.

I then changed it to ‘utf8_unicode_ci‘ and the page displayed without any problem or error.

The exact SQL I ran in mySQL was

ALTER TABLE subscribers CHANGE email_address email_address varchar( 96 ) NOT NULL default '' UNIQUE COLLATE 'utf8_unicode_ci';
ALTER TABLE customers COLLATE 'utf8_unicode_ci';
ALTER TABLE subscribers COLLATE 'utf8_unicode_ci';
ALTER TABLE customers CHANGE customers_email_address customers_email_address varchar( 96 ) NOT NULL default '' UNIQUE COLLATE 'utf8_unicode_ci';

The first statement in the SQL above is thanks to DrByte.

Hope the above helped someone.

Steps to implement mySQL FULLTEXT

mySQL has full text indexing and searching support from version 5.0 onwards however there are some limitations. If you need to implement full text indexing then please review points given below:

1. mySQL allows full text indexing on MyISAM tables and not any other table type. If you try to create a full text index on an InnoDB table for example you will receive the following error message

Error Code : 1214
The used table type doesn't support FULLTEXT indexes

If you plan on using a table with InnoDB for full text indexing then you will first need to change it to MyISAM. Run the following statement to change the table to MyISAM

alter table <table name> engine = MyISAM;

2. You can only use the full text index on CHAR, VARCHAR and TEXT data type. If you try to create it on a column with a data type other than he ones specified then you will receive the following error message

Error Code : 1283
Column '<column name>' cannot be part of FULLTEXT index

3. You can specify the full text index in the CREATE table statement or ALTER table statement. Example of specifying full text index in the CREATE table statement is as follows

pname VARCHAR(200),
pcode VARCHAR(20),
pdesc TEXT,
FULLTEXT (pname,pdesc)

Example of specifying full text index in the ALTER table statement is as follows

ALTER TABLE product ADD FULLTEXT(pname, pdesc);

After completing the above steps you can test whether the full text index is working or not by inserting some data into the table. Please note you should enter at least 10 or more records and the same data should not be copied in each record otherwise it will not work.

The following statement will try to return result using the MATCH()..AGAINST syntax which is how full text index searches through the records in the table.

SELECT * from product where MATCH(pname, pdesc) AGAINST('test');

Please note that full text indexing will only run on the columns in which the full text index has been created. If you try to run the above statement on a non full text index column then it will not run.

If we take the above table for example we have not created full text index on the “pcode” field. If we run our SELECT statement as

SELECT * from product where MATCH(pname, pcode) AGAINST('test');

we will receive the following error

Error Code : 1191
Can't find FULLTEXT index matching the column list

Hope the above helped. Let me know if you have any questions/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


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

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 website.

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.