GSoC 2015 : Weekly Report #7 : RFE #1163 & some corrections

Week #7

Task(s) completed: None

Tasks worked upon:

RFE #1163 : PMA not using SET NAMES on using custom charset/ collation

RFE #1060 : Designer settings like ‘Angular/ Direct Links’, ‘Snap to Grid’ etc. should be remembered

Scheduled Deadline: 19th July

Completed on: NA

Details:

1. The RFE#1163 suggests that we should use SET NAMES instead of SET CHARSET while using custom collation/ charset.
On more researching the details provided by the ticket creator as well as looking into the related code we understood that this is actually a kind of bug and not a feature.

In MySQL, there are 3 types of variables : local, session and global.

Now, for some server variables like ‘collation_connection’ , ‘character set results’ etc. , both values are possible i.e. they have separate session and global values.

Now, the global values are the one that are used while initiating a new connection. But you can set session values for these variables even after the connection has been initialized.

With phpMyAdmin
================
Related code at : L1789 – L1826 [0]

We have hardcoded the $default_charset and $default_collation to be utf8mb4(or utf8 for old MySQL versions) and utf8mb4_general_ci(or utf8_general_ci) to be set every time after connection. (L1790-1796)

Now, if $_REQUEST[‘collation_connection’] is not set, (that is if a new value is selected from drop-down on
welcome page), the $_COOKIE[‘collation_connection’] is empty and $cfg[‘DefaultConnectionCollation’] is not set, only then $GLOBALS[‘collation_connection’] is empty.
And if $GLOBALS[‘collation_connection’] is empty, then only we run “SET NAMES ” query.

Otherwise if a value is found in any of above 3 places,
$GLOBALS[‘collation_connection’] is set in [1]
and then first a “SET CHARACTER SET” query is run followed by
“SET collation_connection “.

Difference between “SET NAMES” and “SET CHARACTER SET” – Source [2]
============================================================
SET CHARACTER SET equals the 3 queries:
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET collation_connection = @@collation_database;

SET NAMES equals the 3 queries:
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

Now, this makes it clear why the feature requester says that some variables like ‘character_set_results’ and ‘character_set_client’ are never changed.
This is because most of the times (refer above for the conditions) SET CHARACTER SET query is run followed by SET collation_connection query.

Thus, session values of variables ‘charset_set_results’ and ‘charset_set_client’ are never changed (in any case) once they are set by the query SET CHARACTER SET ‘$default_charset’; on L1799 and are thus stuck at utf8mb4 or utf8 (for old MySQL versions).

Now, to correct this behaviour, we propose 2 approaches:
i. Provide two more ​drop downs for selecting the values for the 2 above mentioned variables and remember them persistently using cookies (which is already done for ‘collation_connection’).

OR
ii. We know that the user can set the global values of `character_set_results` and `character_set_client` from the ‘Variables’ tab (server_variables.php) but,​ currently, that values are never used since they get stuck at ‘utf8mb4’ (as I stated in the analysis).

Now, we can simply run a query: “SET @@session.character_set_results = @@global.character_set_results;” after we have run “SET CHARACTER SET” in postConnect().
Thus, the user may just go edit the global value and whatever the global value is, will be used for every session too.

The decision about choosing which approach is currently underway. We would decide and implement this once we get some more views from the team may be.
Any updates will be posted in next week’s report.

2. The description about RFE#1060 is available in my previous blog post here [3]

The PR[4] was merged at the start of the week, before Madhura pointed out that we should not use the MySQL reserved words like ‘index’ or ‘value’ for names of the columns.
So, I changed it in [5], before Marc pointed out that we were currently using Entity – Attribute – Value (EAV) model (see related #3 in [6]) for this new table `pma__designer_settings` and therefore we decided to shift to a changed EAV model which is currently used in `pma__userconfig` table.

I made this change to the new model in [7].

3. This week while I was having slightly more free time than usual, I decided to revisit my earlier patches for the implementations of earlier RFEs.

While doing so, I fixed some small errors in [8].

Also, related to RFE #657 : Alter Privileges while copying/ renaming/ moving DB, Table, Column, Procedure, I found that we select, insert or update values from the mysql database for adjusting the privileges and also run FLUSH PRIVILEGES after all the changes.
Now, this may not work for all users since they would require the adequate privileges to make changes to `mysql` db and also at least 1 RELOAD privilege for the user to run “FLUSH .. ” query.

I will work on fixing this problem this week and may be pick up the last RFE if time permits.

Screenshots: None

Implemented with: NA

Links:
​[0] : https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/DatabaseInterface.class.php#L1789
[1] : https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/Config.class.php#L1495
[2] : http://dev.mysql.com/doc/refman/5.6/en/charset-connection.html​
[3] : https://devenbansod.wordpress.com/2015/06/15/gsoc-2015-weekly-report-5-rfe-1594-rfe1060/
[4] : https://github.com/phpmyadmin/phpmyadmin/pull/1737
[5] : https://github.com/phpmyadmin/phpmyadmin/pull/1759
[6] : https://www.simple-talk.com/sql/database-administration/five-simple–database-design-errors-you-should-avoid/
[7] : https://github.com/phpmyadmin/phpmyadmin/pull/1765
[8] : https://github.com/phpmyadmin/phpmyadmin/pull/1764

Advertisements

About Deven Bansod

I am a recent graduate with a dual degree in B.E.(Hons.) Computer Science Engg. and M.Sc.(Hons.) Economics from BITS Pilani, Pilani (India). I am interested in and have been contributing to development of free and open source software s (FOSS). More recently, I have been contributing to phpMyAdmin, a web interface to MySQL, written in PHP. I'm looking for opportunities to contribute to interesting open-source softwares.

Posted on June 29, 2015, in GSoC 2015, phpMyAdmin, Weekly Reports and tagged , , , . Bookmark the permalink. 2 Comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: