Menu
▾
▴
#946 (ok 4.5) Warn before changing collations?
resolved
Normal
2015-05-23
2006-08-16
No
It seems to come up every month or two in the forums
that a user's changed their database collation
(without knowing that it's destructive to their data,
and usually without backups). Most recently, for
example: http://sourceforge.net/forum/forum.php?
thread_id=1555203&forum_id=72909
Perhaps PMA should warn before doing something
potentially destructive like changing collations?
Similar to what's done for database|table drops, etc.
Logged In: YES
user_id=192186
I think we should provide way to change collation without
changing data as this is what most users want.
It has to be done through conversion to blob and back to
string with "correct" collation.
Logged In: YES
user_id=210714
Originator: NO
Isaac,
Seems to me that the problem is only if a user changes a specific column's collation. The db- or table-specific collation is only a default that applies for the future fields to be created.
Last edit: Marc Delisle 2015-03-07
Here is an example in version 4.3.11: a column with collation latin1_swedish_ci and a value of "école" is changed to collation hebrew_bin, which corrupts the "é". Changing back to latin1_swedish_ci does not bring back the corrupted character.
Hi,
So, to summarize, the points to be implemented are:
Add a check before changing the collation for a specific column (like we do while confirming 'Drop') saying 'Do you really want to change this column collation ? This might corrupt the data and data might get lost permanently'.
When collation of a TEXT column is changed, we should not directly change to the new collation. We should first change it to BLOB and then change it to the new collation.
(This would give a option to change back to orignal collation without loss of data and that changing back would also be done in a similar way).
Please suggest corrections if I am wrong.
Implemented with https://github.com/phpmyadmin/phpmyadmin/commit/b09cc73b4962949473e6ae914ea3db32c893b06b