#1163 PMA not using SET NAMES on Custom Collation / Charset

[Chris Williams]

Hello all,

A quick thank you for all the awesome work on this project throughout the years :-)

I was having issues exporting data from one machine to another via PMA, UTF-8 chars were turning to ??? and whatnot.

I did some poking around and found that only a few of the character_set_% variables were being set, and character_set_results in particular wasn't changing at all [it was stuck as utf8 even though my collation was latin1], no matter which collation / charset I set PMA to.

My website connects using latin1, for backwards compatibility, so I use: SET NAMES 'latin1'

PMA doesn't use SET NAMES when a user collation is specified, only when the user has not specified a collation... maybe it should?

I changed ./libraries/database_interface.lib.php ~ line 853 to:
# PMA_DBI_query("SET CHARACTER SET 'utf8';", $link, PMA_DBI_QUERY_STORE);
# $mysql_charset = explode('_', $GLOBALS['collation_connection']);
# PMA_DBI_query("SET collation_connection = '" . PMA_sqlAddslashes($GLOBALS['collation_connection']) . "';", $link, PMA_DBI_QUERY_STORE);
$mysql_charset = explode('_', $GLOBALS['collation_connection']);
PMA_DBI_query("SET NAMES '" . $mysql_charset[0] . "' COLLATE '" . $GLOBALS['collation_connection'] . "';", $link, PMA_DBI_QUERY_STORE);

... and it worked like a charm :-)

I understand this is for the CONNECTION collation, but maybe there should be a better way to control all the character_set_% charsets / collations, like through through SET NAMES.

[Marc Delisle]

I admit I did not test extensively what you're talking about, however don't you wonder why there are no general complaints about this? Maybe there is something non-standard about your data?

Could this explanation help you:
http://wiki.phpmyadmin.net/pma/garbled_data

[Chris Williams]

Thanks for the link, though it isn't helpful.

I guess I was just hoping to see another drop down with something like: SET NAMES [Drop down: latin1, latin2, .... utf8] ... maybe ever right under the connection collation drop down now.

I could easily write the code, but I'm not familiar with the PMA environment so I'm sure it would be rejected.

If not, from now on I can just add in the patch when downloading new versions on PMA, so no worries :-)

Thanks for your time.

[Deven Bansod]

Hi,

I want to make sure what the problem is before moving to the implementation. So, please correct me if I am wrong.
I did a little research about the variables and these SET NAMES and SET CHARACTER SET statements along with their use in PMA. (Difference between these two is here [0])

On researching PMA, I found (here) [1] that we have set a default charset and collation as 'utf8mb4' and 'utfmb4_general_ci'.
If we have persistently stored collation_connection beforehand, then we run 2 queries : "SET CHARACTER SET" with the above mentioned values, followed by "SET collation_connection" with stored value.
Else, we use SET NAMES with default values mentioned above.

Now, I found that values for character_set_results and character_set_client are always stuck with the above mentioned default values, even if global values are set to anything, since we run the above mentioned queries after every successful connection(session).

Possible Solution:
One way we can do is provide 2 more drop-downs to select the values for character_set_results and character_set_client which will be persistently stored and set after each successful connection (as currently is done for collation_connection).

[Isaac Bennetch]

And to be clear, Deven and I discussed this idea and it seems to me a decent solution, but then again I'm not the one with the problem so I'm not completely confident of my own understanding of the situation.

[Isaac Bennetch]

Pull request with the proposed implementation: https://github.com/phpmyadmin/phpmyadmin/pull/1781

[Isaac Bennetch]

Without enough feedback and testing, we're not comfortable merging this pull request so we're closing for now.