[Phpmyadmin-devel] About the transformation feature
Hugues Peccatte
hugues.peccatte at gmail.com
Thu Jul 10 00:02:00 CEST 2014
2014-07-09 23:52 GMT+02:00 Chirayu Chiripal <chirayu.chiripal at gmail.com>:
> On Thu, Jul 10, 2014 at 3:10 AM, Hugues Peccatte <
> hugues.peccatte at gmail.com> wrote:
>
>> 2014-07-09 19:37 GMT+02:00 Hugues Peccatte <hugues.peccatte at gmail.com>:
>>
>>> 2014-07-09 17:34 GMT+02:00 Isaac Bennetch <bennetch at gmail.com>:
>>>
>>> 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 system, what happens, however, is that
>>>>
>>>> $GLOBALS['dbi']->tryMultiQuery($query, $GLOBALS['controllink'])
>>>>
>>>> doesn't work. It returns 1 (which I presume is just how print_r
>>>> represents TRUE), but the query doesn't execute ($query in this case is
>>>> essentially the contents of the .sql file; several SQL statements with
>>>> semicolons and newline characters and a few comments). It works properly
>>>> on Chirayu's system but silently fails on mine.
>>>>
>>>> I'm hoping to get additional testing on the "silent upgrade" feature
>>>> (or, for that matter, thoughts about the problem). At this point, I'm
>>>> the only one for whom it fails, which is quite interesting. To test,
>>>> simply checkout the branch and open phpMyAdmin, then check your
>>>> pma__column_info table; it should have two new columns at the end
>>>> (input_*).
>>>>
>>>> Thanks!
>>>>
>>>> Some more detailed information if you really want to read on:
>>>>
>>>> Around line 610, instead of directly testing the result of the
>>>> tryMultiQuery in the if statement, I assign it to a variable then
>>>> print_r that variable and get a message about "Commands out of sync; you
>>>> can't run this command now".
>>>>
>>>> $ipb_test = $GLOBALS['dbi']->tryMultiQuery($query,
>>>> $GLOBALS['controllink'] );
>>>> die("<pre>" . print_r($GLOBALS['controllink']) . "</pre>");
>>>> if ($ipb_test !== false ) {
>>>> return true;
>>>>
>>>> mysqli Object ( [affected_rows] => 0 [client_info] => 5.5.37
>>>> [client_version] => 50537 [connect_errno] => 0 [connect_error] =>
>>>> [errno] => 0 [error] => [error_list] => Array ( ) [field_count] => 0
>>>> [host_info] => Localhost via UNIX socket [info] => [insert_id] => 0
>>>> [server_info] => 5.5.37-0+wheezy1 [server_version] => 50537 [stat] =>
>>>> Commands out of sync; you can't run this command now [sqlstate] => HY000
>>>> [protocol_version] => 10 [thread_id] => 6069 [warning_count] => 0 )
>>>>
>>>> I'm not familiar enough with tryMultiQuery to know why [stat] contains
>>>> this message.
>>>>
>>>> According to [2], it appears as if there is a problem with executing the
>>>> code because either we haven't used mysql_free_result() or
>>>> mysql_use_result() or mysql_store_result() since a previous query.
>>>>
>>>> We've looked at mysqli_multi_query()
>>>> (http://www.php.net/manual/en/mysqli.multi-query.php), which
>>>> is called by tryMultiQuery() depending on your DBI Extension (mine
>>>> is mysqli). It says that it returns false when First statement is
>>>> failed. See the return values section of it.
>>>>
>>>> Note that my mysqli object claims 0 rows were affected but also didn't
>>>> return false. I expect that the 87 rows in my pma__column_info would
>>>> have been affected by the first line (ALTER...).
>>>>
>>>> 1 - https://github.com/D-storm/phpmyadmin/commits/FR-637
>>>> 2 - https://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html
>>>>
>>>
>>> Hi,
>>>
>>> It seems that I found the same information while reading your email and
>>> doing some research on internet about mysqli_multi_query and its return.
>>> So maybe we need to improve PMA_DBI_Mysqli::realMultiQuery to return the
>>> real error status.
>>>
>>> But first maybe, you can try something like this in this method (I
>>> didn't test it…):
>>> if ($r = mysqli_multi_query($link, $query)) {
>>> do {
>>> if ($result = mysqli_store_result($link)) {
>>> echo 'ok.<br />';
>>> mysqli_free_result($result);
>>> } else {
>>> $r = false;
>>> echo 'KO<br />'; //Do you know a way get the query here? I
>>> didn't find one…
>>> }
>>> } while (mysqli_next_result($link));
>>> }
>>> return $r;
>>>
>>> Then, you'll know the query which failed.
>>>
>>> We're waiting for your feedback. (I'll try… to try…)
>>> Hugues.
>>>
>>
>> Sorry, I just did some research and it can work. mysqli_store_result
>> can''t be used with INSERT, UPDATE, DELETE, ALTER statement. But we may try
>> to use mysqli_error to really if there is an error or not.
>>
>> I'll try to do other tests with mysqli_error, maybe.
>>
>> Hugues.
>>
>
>
> Yeah, you are right. I tested it using this code,
>
> mysqli_multi_query(
> $GLOBALS['controllink'], $query
> );
> do {
> var_dump(mysqli_error($GLOBALS['controllink']));
> } while (mysqli_next_result($GLOBALS['controllink']));
> var_dump(mysqli_error($GLOBALS['controllink']));
>
> and for the last var_dump() I got the error which I intentionally did and
> for other queries which were executed successfully it returned an empty
> string.
>
> --
> Regards,
> Chirayu Chiripal
> phpMyAdmin Intern - Google Summer of Code 2014
> https://chirayuchiripal.wordpress.com/
>
Oh thanks! I was about giving up!
So I defined the following method (and I think that should be in master) :
public function realMultiQuery($link, $query)
{
if (false === mysqli_multi_query($link, $query)) {
return false;
}
while (mysqli_next_result($link));
return('' === mysqli_error($link));
}
And it seems to be ok.
The only thing that I don't like is that the script is half executed but
can't be reverted… But I'm not sure there is a way to manage it… There are
some DB structure modifications and we can't revert this, so it would need
to write a specific "undo" script. But even with this, how to automatically
know if all the undo script has to be executed are only a part and which
one ?
(If we know the number of queries, we might imagine, but not sure that
would be a good solution…)
Hugues.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20140710/52fa6419/attachment.html>
More information about the Developers
mailing list