On Wed, Jul 9, 2014 at 10:58 PM, Isaac Bennetch bennetch@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/DatabaseInter... [2] : https://github.com/phpmyadmin/phpmyadmin/blob/QA_4_2/libraries/DatabaseInter...

2014-07-09 19:37 GMT+02:00 Hugues Peccatte hugues.peccatte@gmail.com:

2014-07-09 17:34 GMT+02:00 Isaac Bennetch bennetch@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.

2014-07-09 23:52 GMT+02:00 Chirayu Chiripal chirayu.chiripal@gmail.com:

On Thu, Jul 10, 2014 at 3:10 AM, Hugues Peccatte < hugues.peccatte@gmail.com> wrote:

...

2014-07-09 19:37 GMT+02:00 Hugues Peccatte hugues.peccatte@gmail.com:

...

2014-07-09 17:34 GMT+02:00 Isaac Bennetch bennetch@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.

On 7/9/14, 1:37 PM, Hugues Peccatte wrote:

2014-07-09 17:34 GMT+02:00 Isaac Bennetch <bennetch@gmail.com mailto:bennetch@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;

I go through the 'else' portion here (it loops five times), but I haven't yet found a way to get any more complete picture of what's going on.

Then, you'll know the query which failed.

We're waiting for your feedback. (I'll try… to try…) Hugues.

---

Open source business process management suite built on Java and Eclipse Turn processes into business applications with Bonita BPM Community Edition Quickly connect people, data, and systems into organized workflows Winner of BOSSIE, CODIE, OW2 and Gartner awards http://p.sf.net/sfu/Bonitasoft

---

Phpmyadmin-devel mailing list Phpmyadmin-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/phpmyadmin-devel