GSoC 2015 : Weekly Report #2 : RFE #657 & RFE#946
RFE #657 : Alter privileges when renaming database/ tables/ fields etc.
Scheduled Deadline: 7th June, 2015
Completed on: 23rd May, 2015
1. The RFE#657 mentions that we should provide an option to ‘Alter Privileges’ while renaming the database / table/ fields/ procedures etc.
MySQL, on its own, does not adjust the privileges related to the database, its tables, fields, procedures while renaming the database. Being a tool for database administration, phpMyAdmin should provide an option to its users to save them from doing it manually for such cases.
MySQL stores its privileges in `mysql` database as follows : database-related -> `db`, tables-related -> `tables_priv`, columns-related -> `columns_priv` and procedure-related -> `procs_priv`. Thus, after renaming/ moving, you just have to update the db_name with the new db_name in all these tables.
Similar updates are required to be done while tables renaming/moving and procedures and columns renaming.
While copying a database/ table, the way is slightly different. You have to replicate the existing privileges but with a diffrent db_name/ table_name but without removing the original ones. So, first we select all the rows related to the concerned db/ table and then re-insert them changing the db_name/ table_name.
At first, I implemented it as a checkbox in all the move/ copy/ rename of db/table/columns/procedures with a label ‘Realign Privileges’ but the reporter of the ticket responded saying that the feature needed to be more intuitive. So with help and suggestions from Marc, Madhura and of course Isaac, we changed the label to ‘Adjust Privileges’ and added documentation regarding the use of the feature and provided a link to this documentation near the original label of the checkbox.
2. The RFE#946 mentions that if a certain character is not present in the a collation and if the original collation is changed to that new one, you may experience a loss of data. And even if you revert back to the original collation, you may not be able to retrieve it.
So, we should warn the user of the consequences before changing the collation. Also, we should provide a way to prevent the loss of data while conversion. This is done by first converting the datatype to BLOB and then re-converting to the requested collation and datatype.
Only tricky part to handle was that whenever a request to ‘change’ the column(s) was recieved, I would automatically change it to BLOB and thus if the conversion fails later, the column would get stuck in BLOB datatype only. To avoid this, I had to handle this situation that whenever an error occurs we change back to the original configuration.
Isaac and Marc helped formulate the final Warning Message to be shown when the user tries to change the column collation.
Implemented with: 1. RFE#657 –  and 2. RFE#946 – 
Overall workflow after implementation:
1. RFE#657 : User checks the ‘Adjust Privileges’ checkbox provided in all the Rename/ Copy/ Move Database/ Table/ Column/ Procedure forms. The privileges related to these objects are appropriately adjusted.
2. RFE#946 : User tries to change column collation of a existing table. He/she is warned about the consequences and also he/she is let know how to recover the data if it’s corrupted after the change.
Key Tasks stalled: None
Tasks for upcoming week: RFE#701