Re: Database migration failing.
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.