#4420 UIprefs not working

[timo]

table 'pma__table_uiprefs' is not updated properly.

I solved this with the following:

file: libraries/Table.class.php
line 1512
. PMA_Util::sqlAddSlashes(json_encode($this->uiprefs)) . "', NULL)";
. PMA_Util::sqlAddSlashes(json_encode($this->uiprefs)) . "', NOW())";

This is not limited to phpmyadmin version 4.2.1

[Marc Delisle]

I cannot reproduce this problem under MySQL 5.5.37. Which MySQL version are you using?

[timo]

I am using MySQL version 5.6.17.

For example, column sort order is not saved in Uiprefs. In this case, there is no error message.

If you adjust visibility of a column (see attachment liite.jpg), you get error message "Could not save table UI preferences!"

To: 4420@bugs.phpmyadmin.p.re.sf.net
From: lem9@users.sf.net
Subject: [phpmyadmin:bugs] #4420 UIprefs not working
Date: Sun, 18 May 2014 19:43:47 +0000

I cannot reproduce this problem under MySQL 5.5.37. Which MySQL version are you using?

[bugs:#4420] UIprefs not working

Status: open

Group: 4.2.1

Labels: uiprefs

Created: Sat May 17, 2014 06:02 PM UTC by timo

Last Updated: Sat May 17, 2014 06:02 PM UTC

Owner: nobody

table 'pma__table_uiprefs' is not updated properly.

I solved this with the following:

file: libraries/Table.class.php

line 1512

. PMA_Util::sqlAddSlashes(json_encode($this->uiprefs)) . "', NULL)";

. PMA_Util::sqlAddSlashes(json_encode($this->uiprefs)) . "', NOW())";

This is not limited to phpmyadmin version 4.2.1

Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/phpmyadmin/bugs/4420/

To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

[Marc Delisle]

For me, saving column sort order and column visibility work correctly. I switched to MySQL 5.6.15 to be near your MySQL version.

Are you sure that your phpMyAdmin configuration storage, including the control user, are correctly defined?

Which PHP version are you using? Also, which extension (mysql? mysqli?).

[timo]

PHP version 5.5.12
extension : mysqli

For any phpMyAdmin configuration issues, I have checked it and I believe it is OK, otherwise I would expect to see also several other problems. If you have some specific question about this, please advise.

I know for certain that on the past UIPREFS has been working. After all updates with MySQL, phpMyAdmin and PHP this behaviour has changed, but I can't pin-point which update is the cause.

To give some more information,
I have made some tests with a test table, testing the operation of TIMESTAMP according to http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html (11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME)
It worked as expected with a NULL value in column that is the timestamp.
This makes me believe that MySQL is OK.

After that, I made a test with phpMyAdmin. Using table pma__table_uiprefs I made a manual SQL query as follows (I have replaced real values with xxx on some columns):
REPLACE INTO pma__table_uiprefs VALUES ('xxx', 'xxx', 'xxx', '{"sorted_col":"sort ASC","CREATE_TIME":"2014-05-15 20:10:43","col_order":["0","1","2","3","4","5"],"col_visib":["1","1","1","1","1","1"]}', NULL);

result: MySQL said: Documentation
#1048 - Column 'last_update' cannot be null

This is unexpected result.

[timo]

Further notes from my own test.

Structure of test table:
CREATE TABLE IF NOT EXISTS testi (
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
aaa int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

With MySql command window, for example this gives correct behaviour:
UPDATE laskurit.testi SET aaa = '22' WHERE testi.aaa = 23;
Column ts is updated with CURRENT_TIME

This gives error:
REPLACE INTO testi VALUES(NULL, '23');
ERROR 1048 (23000): Column 'ts' cannot be null

So, is it after all the case that MYSQL is not working as expected?

[timo]

And finally, I found that in my.cnf there is a line
explicit_defaults_for_timestamp = TRUE

If I disable it, the problem does not exist any more.

I suggest status Solved

[Marc Delisle]

Thanks for testing.