[Phpmyadmin-devel] About the transformation feature

[Chirayu Chiripal]

On Wed, Jul 9, 2014 at 10:58 PM, Isaac Bennetch <bennetch at gmail.com> wrote:

>
>
> On 7/9/14, 1:22 PM, Marc Delisle wrote:
> > Le 2014-07-09 11:34, Isaac Bennetch a écrit :
> >> Hello,
> >>
> >> In the course of my testing the new input transformation feature, I've
> >> encountered some trouble that Chirayu is unable to reproduce. We've had
> >> a bit of discussion privately about it, but wanted to move discussion
> >> back to the mailing list.
> >>
> >> Note that running his branch[1] will change your pma__column_info table
> >> structure; so you may wish to back it up for testing purposes.
> >>
> >> The problem at the moment is the automatic table upgrade feature. What
> >> is supposed to happen is that, when logging in,
> >> PMA_checkRelationsParam() verifies that the correct table structure
> >> exists for pma__column_info. If it doesn't, it attempts to run
> >> ./examples/upgrade_column_info_4_3_0+.sql (after substituting the
> >> correct database and table names if needed). This is all quite good.
> >
> > On my Debian wheezy 64-bit machine (7.5), the auto upgrade went fine. Do
> > you have a control user explicitely defined?
>
> Thanks for checking. Yes, I do. I'm also on Wheezy, but 32-bit. I'll
> have to do some further testing, perhaps tomorrow.
>

Hi,

I intentionally manipulated intermediate queries and made them to fail to
reproduce this problem and noticed few things.

1) multi_query returns the result of the first query i.e. in our case 'USE
`phpmyadmin`'. If this first query is executed successfully and if next
alter table query fails then it will return true causing the problem you
have mentioned. So, we have to loop through all query results and return
true only if all query gets successfully executed. After first failure,
further queries are skipped.

2) This might not be the reason causing this issue but this needs to be
fixed before this issue can be fixed. See [1] & [2]. One of those is
already fixed in master. I am not sure whether fix should go in QA_4_2 or
master.

3) I don't know why $GLOBALS['dbi']->storeResult($GLOBALS['controllink'])
is returning false everytime for all the queries in the loop. This means we
can't fetch error or result of each query.

Note: I also tried using mysqli_multi_query(), mysqli_store_result(),
mysqli_use_result() functions with link as $GLOBALS['controllink'] but
result was same. So, it may be the problem with controllink or with
php/mysql itself.

**Possible Workaround:** However number of result sets returned are equal
to the queries executed so we can count it in loop and see if it matches
the total (we have to count queries in sql file and use that in code which
means if queries are added/deleted from file then we have to change the
total in code also).

Also, as queries may fail in between after executing some of them so we
need to combine the alter queries into single query to ensure that either
both columns are added or none.

Also, we need to move the update queries before alter queries (they should
be done at last) to ensure that current data is updated before altering the
table to avoid a case when alter query gets executed and update query fails
leaving the old transformations unusable as any further logins will see
that columns are present and will not update it anymore.

After moving update query before alter query we need to change our update
query because currently we are prepending 'output/' to plugins using NOT IN
clause and if after executing update query, following alter query fails
then next time it will try to update then it will change the column content
to something like this 'output/output/' (multiple prepend). So, instead of
NOT IN we need to use IN clause.

Doing these changes will make sure that old transformation data is updated
before altering table and multiple update queries does not produce any side
effects.

Is this good enough?

[1] :
https://github.com/phpmyadmin/phpmyadmin/blob/QA_4_2/libraries/DatabaseInterface.class.php#L2164
[2] :
https://github.com/phpmyadmin/phpmyadmin/blob/QA_4_2/libraries/DatabaseInterface.class.php#L2176

-- 
Regards,
Chirayu Chiripal
phpMyAdmin Intern - Google Summer of Code 2014
https://chirayuchiripal.wordpress.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20140710/3eeb43b2/attachment.html>