hello!
I’m getting the following error message when I try to edit destinations in admin, this may have something to do with the fact that I added recently all provinces from Spain thanks to a file someone gave me. Before doing that, I had no errors in db.
How can I fix it? Please, help me with this. I need to edit destinations.
Database error: 1267 : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_spanish_ci,IMPLICIT) for operation ‘=’
Invalid query: SELECT a.state_id, b.state, a.code, d.country FROM cscart_states as a LEFT JOIN cscart_state_descriptions as b ON b.state_id=a.state_id AND b.lang_code=‘ES’ LEFT JOIN cscart_destination_elements as c ON c.element_type=‘S’ AND c.element=a.state_id LEFT JOIN cscart_country_descriptions as d ON d.code=a.country_code AND d.lang_code=‘ES’ WHERE c.destination_id=‘7’
--------------------------------------------------------------------------------
Backtrace: File: /home/bestpric/public_html/electrogeneral.com/admin.php
Line: 79
Function: include
File: /home/bestpric/public_html/electrogeneral.com/targets/destinations.php
Line: 37
Function: include
File: /home/bestpric/public_html/electrogeneral.com/include/admin/destinations.php
Line: 180
Function: db_get_array
File: /home/bestpric/public_html/electrogeneral.com/core/fn_database.php
Line: 42
Function: db_query
File: /home/bestpric/public_html/electrogeneral.com/core/db_mysql41.php
Line: 72
Function: db_debug_error
Please, can anyone help me? can you tell me why I’m getting this error when I try to manage destinations?
basically half of your database is written in a different language that the SQL server cannot understand. Try repairing the tables via PHPMyAdmin
Now that you warn me about that, I visit phpmyadmin and I see that the collation is: utf8_general_ci for the 1st 20 lines and the rest is latin1_swedish_ci.
Swedish???
I am so confused, I have now idea how that happned. It was probably like that in the Spanish package I downloaded, I don’t know. There are many tables with that wrong language.
Please, can you tell me if there’s a way I can change all to the correct language with one command? Also, should I write as collation utf8_general_ci or maybe latin1_spanish-ci? My store will be in Spanish and English.
Thanks a lot in advance!
Unfortuantely to my knowledge it’s a manual task.
Changing the collation all to utf8_general.
I know that my own is swedish, however that’s due to my hosts server setup.
If you need this done, let me know.
[quote name=‘JesseLeeStringer’]Unfortuantely to my knowledge it’s a manual task.
Changing the collation all to utf8_general.
I know that my own is swedish, however that’s due to my hosts server setup.
If you need this done, let me know.[/QUOTE]
Thank you, JesseLeeStringer, I know how to do it manually, one by one, but I thought there was some way to do a “select all, change all” I’m such a lazy bone. Anyway, I’ll start changing it right now. I’ll change it all to utf8_general. Thank you so much for your help, you set me on the right track.
I’m sooooo happy!
As I didn’t imagine myself changing more than 300 tables one by one, what I did was the following:
Using an Excel file I copied the 1st part of the query in column A (ALTER TABLE ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
then I copied all the tables' names in column B and the rest of the query in column C ( so when you apply the formula =(A1&B1&C1) you have all the queries that will change all tables.
I was so happy to see that worked!!!
I hope it can help others witht he same issue.