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

CREATE TABLE product
(
pid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
pname VARCHAR(200),
pcode VARCHAR(20),
pdesc TEXT,
FULLTEXT (pname,pdesc)
) ENGINE=MyISAM;

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

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

Rebuild indexes in SQL Server 2005

If you have a database with millions of records on which you provide search facility to customers then you will need to maintain indexes so that the visitor experience is enhanced.

SQL Server 2005 allows you to create indexes. Its up to you to decide which columns should be indexed. Most probably those columns should be indexed which serve as the search criteria for the visitor for e.g. name, address, phone etc.

Now you may say that you had created indexes once then why should you rebuild them?
You should remember that as new data is inserted into the table the index is not updated. So after some time you will start to see performance drop when searching. This is the time when your index requires you to rebuild it.

Use the following SQL statement in SQL Server 2005 to rebuild the index

ALTER INDEX [ALL|] ON

REBUILD

If you are an experienced DBA then you can also specify the FILLFACTOR after the REBUILD. If you are not then the above statement will do the trick.

You will see a marked improvement in the performance.

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

Grant EXEC permission on all stored procedures in SQL Server 2005

If you have a large number of stored procedures in your SQL Server 2005 and you have to give EXEC permission to a database user on all of them then the best way to do it is by running the following SQL

 
SELECT ' GRANT EXEC ON '+ name +' TO <database_user> ' from sys.procedures

The above statement will generate your GRANT statements and you will just have to copy and run them. That’s it really.

Hope the above helped