Re: Database migration failing.
Posted by: Martin Haworth
Date: June 08, 2011 10:44AM

I tried that, and still get the message ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'.

The only way I get seem to get the defining sql out, is by using Mysql Administrator. Here are the definitions……….

I’ll list below what I’ve done.

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`sqlaccess`@`%` SQL SECURITY DEFINER VIEW `reportbydir` AS select `dir`.`month` AS `Month`,`dir`.`directorate` AS `Directorate`,NULL AS `Division`,ifnull(`c`.`createTotal`,0) AS `Total Requests Raised`,ifnull(`a`.`ackTotal`,0) AS `Total Requests due for Acknowledgement`,ifnull(`a`.`ackMet`,0) AS `Requests Acknowledged on time`,ifnull(`a`.`ackMissed`,0) AS `Requests not Acknowledged on time`,ifnull(`a`.`ackUnknown`,0) AS `Requests Acknowledgement not yet overdue`,`a`.`ackMetPC` AS `% Requests Acknowledged on time`,`a`.`ackMissedPC` AS `% Requests not Acknowledged on time`,`a`.`ackUnknownPC` AS `% Acknowledgement not yet overdue`,ifnull(`d`.`dueTotal`,0) AS `Total Requests due for completion`,ifnull(`d`.`dueMet`,0) AS `Requests Completed on time`,ifnull(`d`.`dueMissed`,0) AS `Requests not Completed on time`,ifnull(`d`.`dueUnknown`,0) AS `Requests completion not yet overdue`,`d`.`dueMetPC` AS `% Requests Completed on time`,`d`.`dueMissedPC` AS `% Requests not Completed on time`,`d`.`dueUnknownPC` AS `% Requests completion not yet overdue`,ifnull(`o`.`reopenTotal`,0) AS `Total Requests Reopened` from ((((`monthsdirectorates` `dir` left join `reportjobscreatedbydir` `c` on(((`dir`.`directorate` = `c`.`directorate`) and (convert(`dir`.`month` using utf8) = (`c`.`createdMonth` collate utf8_general_ci))))) left join `reportacktargetsbydir` `a` on(((`dir`.`directorate` = `a`.`ackDir`) and (convert(`dir`.`month` using utf8) = (`a`.`ackDueMonth` collate utf8_general_ci))))) left join `reportduetargetsbydir` `d` on(((`dir`.`directorate` = `d`.`dueDir`) and (convert(`dir`.`month` using utf8) = (`d`.`dueMonth` collate utf8_general_ci))))) left join `reportjobsreopenedbydir` `o` on(((`dir`.`directorate` = `o`.`directorate`) and (convert(`dir`.`month` using utf8) = (`o`.`reopenMonth` collate utf8_general_ci))))) order by `dir`.`monthOrder`,`dir`.`directorate`

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`sqlaccess`@`%` SQL SECURITY DEFINER VIEW `reportbydiv` AS select `division`.`month` AS `Month`,`division`.`directorate` AS `Directorate`,`division`.`division` AS `Division`,ifnull(`c`.`createTotal`,0) AS `Total Requests Raised`,ifnull(`a`.`ackTotal`,0) AS `Total Requests due for Acknowledgement`,ifnull(`a`.`ackMet`,0) AS `Requests Acknowledged on time`,ifnull(`a`.`ackMissed`,0) AS `Requests not Acknowledged on time`,ifnull(`a`.`ackUnknown`,0) AS `Requests Acknowledgement not yet overdue`,`a`.`ackMetPC` AS `% Requests Acknowledged on time`,`a`.`ackMissedPC` AS `% Requests not Acknowledged on time`,`a`.`ackUnknownPC` AS `% Acknowledgement not yet overdue`,ifnull(`d`.`dueTotal`,0) AS `Total Requests due for completion`,ifnull(`d`.`dueMet`,0) AS `Requests Completed on time`,ifnull(`d`.`dueMissed`,0) AS `Requests not Completed on time`,ifnull(`d`.`dueUnknown`,0) AS `Requests completion not yet overdue`,`d`.`dueMetPC` AS `% Requests Completed on time`,`d`.`dueMissedPC` AS `% Requests not Completed on time`,`d`.`dueUnknownPC` AS `% Requests completion not yet overdue`,ifnull(`o`.`reopenTotal`,0) AS `Total Requests Reopened` from ((((`monthsdivisions` `division` left join `reportjobscreatedbydiv` `c` on(((`division`.`directorate` = `c`.`directorate`) and (`division`.`division` = `c`.`division`) and (convert(`division`.`month` using utf8) = (`c`.`createdMonth` collate utf8_general_ci))))) left join `reportacktargetsbydiv` `a` on(((`division`.`directorate` = `a`.`ackDir`) and (`division`.`division` = `a`.`ackDiv`) and (convert(`division`.`month` using utf8) = (`a`.`ackDueMonth` collate utf8_general_ci))))) left join `reportduetargetsbydiv` `d` on(((`division`.`directorate` = `d`.`dueDir`) and (`division`.`division` = `d`.`dueDiv`) and (convert(`division`.`month` using utf8) = (`d`.`dueMonth` collate utf8_general_ci))))) left join `reportjobsreopenedbydiv` `o` on(((`division`.`directorate` = `o`.`directorate`) and (`division`.`division` = `o`.`division`) and (convert(`division`.`month` using utf8) = (`o`.`reopenMonth` collate utf8_general_ci))))) order by `division`.`monthOrder`,`division`.`directorate`,`division`.`division`

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`sqlaccess`@`%` SQL SECURITY DEFINER VIEW `reportbycouncil` AS select `m`.`month` AS `Month`,_utf8'COUNCIL' AS `Directorate`,NULL AS `Division`,ifnull(`c`.`createTotal`,0) AS `Total Requests Raised`,ifnull(`a`.`ackTotal`,0) AS `Total Requests due for Acknowledgement`,ifnull(`a`.`ackMet`,0) AS `Requests Acknowledged on time`,ifnull(`a`.`ackMissed`,0) AS `Requests not Acknowledged on time`,ifnull(`a`.`ackUnknown`,0) AS `Requests Acknowledgement not yet overdue`,`a`.`ackMetPC` AS `% Requests Acknowledged on time`,`a`.`ackMissedPC` AS `% Requests not Acknowledged on time`,`a`.`ackUnknownPC` AS `% Acknowledgement not yet overdue`,ifnull(`d`.`dueTotal`,0) AS `Total Requests due for completion`,ifnull(`d`.`dueMet`,0) AS `Requests Completed on time`,ifnull(`d`.`dueMissed`,0) AS `Requests not Completed on time`,ifnull(`d`.`dueUnknown`,0) AS `Requests completion not yet overdue`,`d`.`dueMetPC` AS `% Requests Completed on time`,`d`.`dueMissedPC` AS `% Requests not Completed on time`,`d`.`dueUnknownPC` AS `% Requests completion not yet overdue`,ifnull(`o`.`reopenTotal`,0) AS `Total Requests Reopened` from ((((`reportmonths` `m` left join `reportjobscreated` `c` on((convert(`m`.`month` using utf8) = (`c`.`createdMonth` collate utf8_general_ci)))) left join `reportacktargets` `a` on((convert(`m`.`month` using utf8) = (`a`.`ackDueMonth` collate utf8_general_ci)))) left join `reportduetargets` `d` on((convert(`m`.`month` using utf8) = (`d`.`dueMonth` collate utf8_general_ci)))) left join `reportjobsreopened` `o` on((convert(`m`.`month` using utf8) = (`o`.`reopenMonth` collate utf8_general_ci)))) where (`m`.`report` = 1) order by `m`.`monthOrder`


So I tried to create a new view based on the first view (same definition but just have it a new name) and if I read right, changed the collation value from utf8_general_ci to latin1_general_ci and got the following error.



ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_general_ci,EXPLICIT) for operation '='

If I issue
SET NAMES 'utf8' COLLATION 'utf8_general_ci';
I get ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATION 'utf8_general_ci'' at line 1
on both the source and destination servers.

The servers are as follows:
Source: 5.1.25-rc
mysql> show variables like 'character_set%';
+--------------------------+--------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/coolstack/mysql/share/mysql/charsets/ |



mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_general_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)


Destination: (5.5.8)

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/mysql/mysql/share/charsets/ |


mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+

I’m getting very confused on this. Don’t know why there are the differences on the sefvers, I didn’t build them.

Any help gratefully received.

Options: ReplyQuote


Subject
Views
Written By
Posted
6084
June 02, 2011 04:28AM
1944
June 06, 2011 10:06PM
2076
June 07, 2011 02:49PM
2038
June 07, 2011 08:27PM
Re: Database migration failing.
2979
June 08, 2011 10:44AM
1995
June 08, 2011 09:00PM
1969
June 09, 2011 09:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.