SQL duplicate entry error trying to INSERT in designer_settings table

[jesucarr]

I'm getting this error when going to the Designer page:

#1062 - Duplicate entry 'myuser' for key 'PRIMARY'

The SQL that is trying to execute is:

INSERT INTO `phpmyadmin`.`pma__designer_settings` (username, settings_data) VALUES('myuser', '{"relation_lines":"true"}');

In the table settings there is already one row with that username, and the setting {"snap_to_grid":"off"}

As the table has the username as primary key, I guess the query should try to UPDATE and add the new setting value, instead of trying to INSERT.

When I try to add settings to the pma__userconfig table, it works as expected.

I'm using version 4.5.2 and PHP 5.6.16

[devenbansod]

Actually it should and was meant to UPDATE only and the code was originally written to do update.
Can you reproduce this on our demo server ?

Or may be try deleting the row for the user manually first and then open up designer, change some settings (so that a row is inserted) into the pma__designersettings table, close the designer and open it again to change a few more settings and check if you can reproduce the issue ?

Also, could anyone confirm if they can also reproduce this issue ?

[lem9]

Cannot reproduce. To debug this, we need some tracing from you. In libraries/pmd_common.php, function PMA_saveDesignerSetting(), the decision to UPDATE or INSERT is based on the results of a SELECT. Please tell us the content of $orig_data_query and $orig_data.

[jesucarr]

$orig_data_query =

SELECT settings_data FROM `phpmyadmin`.`pma__designer_settings` WHERE username = 'myuser';

$orig_data =

It seems that $orig_data is empty, so it doesn't go to the UPDATE query. I noticed that when printing $GLOBALS['dbi'] and $GLOBALS['controllink'] are also empty. Not sure if that is the issue.

[devenbansod]

Just a few questions.

1. Is there a row for the same username that $orig_data_query has, in the pma_designer_settings table ?
2. If you run the same query manually through the SQL tab, are the results empty ?

[jesucarr]

1. yes
2. no

[devenbansod]

If that is so, then I am not sure what exactly might be the issue.
The normal query is returning results, but still $orig_data is empty. Any ideas/help @lem9 ?

[lem9]

@jesucarr The fact that $GLOBALS['dbi'] is empty is a Bad Thing. When I trigger this code by clicking on "Toggle relation lines" in the Designer, $GLOBALS['dbi'] is not empty at this point. Please tell us if the error message appears when you click on the Designer menu tab or when you try some action in the Designer (which one?)

[jesucarr]

The error message appears as soon as I go to the Designer page (without doing any action), and also when I do any action, for example clicking in "Toggle relation lines".

$GLOBALS['dbi'] is empty in both situations. What I'm doing to see it is comment out the two PMA_queryAsControlUser calls and add a die('dbi:'.$GLOBALS['dbi']); before the return, and I see in the db_designer.php request that it returned {"message":"dbi:","success":true, ....

[lem9]

Ok, keep us posted. Meanwhile, are you sure you did not install phpMyAdmin over a previous installation?

[devenbansod]

Hi @jesucarr, I realised that $GLOBALS['dbi'] should not be concatenated with string as it is an object not a string.
So, I think that is why it gave blank output for you.

I think if you instead do
print_r($GLOBALS['dbi']);
print_r($orig_data[0]);
exit;
then it might be non-empty for you as well.

My output ({message: ..}) after putting the above code on lines 734-736 is :

PMA_DatabaseInterface Object\n(\n [_extension:PMA_DatabaseInterface:private] => PMA_DBI_Mysqli Object\n (\n )\n\n [_table_cache:PMA_DatabaseInterface:private] => Array\n (\n )\n\n)\n {"snap_to_grid":"off","relation_lines":"true","angular_direct":"direct","small_big_all":"v"}

[jesucarr]

That's right @devenbansod it wasn't empty. I get the same as you for $GLOBALS['dbi'], but $orig_data[0] is empty.

[lem9]

After $orig_data is set, try this:

print_r($GLOBALS['dbi']->getError($GLOBALS['controllink']));

[jesucarr]

that's empty. $GLOBALS['controllink'] is empty too.

[lem9]

Did you log in (assuming your auth_type is 'cookie') with your control user?

[jesucarr]

auth_type is 'cookie', but I'm not logging in with the control user

[lem9]

What does "mypmacontroluser" mean then?

[jesucarr]

Sorry that was supposed to be "myuser"

[devenbansod]

Okay. So just confirming. You have a user named 'myuser' and have a row with 'myuser' already in the pma__designer_settings table.
Now, when you login as 'myuser' and open designer, you get the above mentioned error about duplicate entry.

Any corrections ?

[jesucarr]

Yes that's it

[lem9]

@jesucarr Did you get phpMyAdmin from http://phpmyadmin.net ? Could you attach here your config.inc.php, obfuscating sensitive info?

[jesucarr]

I installed it in Centos 7 using yum, from the remi-test repository (4.5.2-1.el7.remi). Here is the config.inc.php

<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
 * phpMyAdmin sample configuration, you can use it as base for
 * manual configuration. For easier setup you can use setup/
 *
 * All directives are explained in documentation in the doc/ folder
 * or at <http://docs.phpmyadmin.net/>.
 *
 * @package PhpMyAdmin
 */

/*
 * This is needed for cookie based authentication to encrypt password in
 * cookie
 */
$cfg['blowfish_secret'] = 'xxxxx'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

/*
 * Servers configuration
 */
$i = 0;

/*
 * First server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/* Permissions */
$cfg['Servers'][$i]['AllowDeny']['order'] = 'deny,allow';
$cfg['Servers'][$i]['AllowDeny']['rules'] = array(
            "deny root from all",
            );

/*
 * phpMyAdmin configuration storage settings.
 */

/* User used to manipulate with storage */
$cfg['Servers'][$i]['controlhost'] = 'localhost';
// $cfg['Servers'][$i]['controlport'] = '';
$cfg['Servers'][$i]['controluser'] = 'mycontroluser';
$cfg['Servers'][$i]['controlpass'] = 'xxxxx';

/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';
/* Contrib / Swekey authentication */
// $cfg['Servers'][$i]['auth_swekey_config'] = '/etc/swekey-pma.conf';

/*
 * End of servers configuration
 */

/*
 * Directories for saving/loading files from server
 */
//$cfg['UploadDir'] = '/var/lib/phpMyAdmin/upload';
//$cfg['SaveDir'] = '/var/lib/phpMyAdmin/save';

/**
 * Whether to display icons or text or both icons and text in table row
 * action segment. Value can be either of 'icons', 'text' or 'both'.
 */
//$cfg['RowActionType'] = 'both';

/**
 * Defines whether a user should be displayed a "show all (records)"
 * button in browse mode or not.
 * default = false
 */
//$cfg['ShowAll'] = true;

/**
 * Number of rows displayed when browsing a result set. If the result
 * set contains more rows, "Previous" and "Next".
 * default = 30
 */
//$cfg['MaxRows'] = 50;

/**
 * disallow editing of binary fields
 * valid values are:
 *   false    allow editing
 *   'blob'   allow editing except for BLOB fields
 *   'noblob' disallow editing except for BLOB fields
 *   'all'    disallow editing
 * default = blob
 */
//$cfg['ProtectBinary'] = 'false';

/**
 * Default language to use, if not browser-defined or user-defined
 * (you find all languages in the locale folder)
 * uncomment the desired line:
 * default = 'en'
 */
//$cfg['DefaultLang'] = 'en';
//$cfg['DefaultLang'] = 'de';

/**
 * How many columns should be used for table display of a database?
 * (a value larger than 1 results in some information being hidden)
 * default = 1
 */
//$cfg['PropertiesNumColumns'] = 2;

/**
 * Set to true if you want DB-based query history.If false, this utilizes
 * JS-routines to display query history (lost by window close)
 *
 * This requires configuration storage enabled, see above.
 * default = false
 */
//$cfg['QueryHistoryDB'] = true;

/**
 * When using DB-based query history, how many entries should be kept?
 *
 * default = 25
 */
//$cfg['QueryHistoryMax'] = 100;

/**
 * Should error reporting be enabled for JavaScript errors
 *
 * default = 'ask'
 */
//$cfg['SendErrorReports'] = 'ask';

/*
 * You can find more configuration options in the documentation
 * in the doc/ folder or at <http://docs.phpmyadmin.net/>.
 */

// Setup

$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

$cfg['SuhosinDisableWarning'] = true;
$cfg['NavigationTreeEnableGrouping'] = false;
$cfg['ForceSSL'] = true;
$cfg['CaptchaLoginPublicKey'] = 'xxxxx';
$cfg['CaptchaLoginPrivateKey'] = 'xxxxx';

?>

[lem9]

Just to be sure, the error message you get (duplicate entry) does not show 'mycontroluser' but 'myuser', correct?

[jesucarr]

correct

[jesucarr]

Just tested that if I don't give access permissions to the user serving the app to the /etc/phpMyAdmin directory, it uses the default config and the Designer works. However when the app has access to that folder the Designer fails as described also when using the default config.inc.php in that folder (just adding the right controluser/password).

[lem9]

@jesucarr Isn't this a CentOS issue then?

[jesucarr]

What do you mean? If I don't give access to the folder it is supposed to not be able to access the file, so CentOS is working as expected.

[lem9]

Do you still consider this to be a phpMyAdmin bug? I don't know what you mean by "give access permissions to the user serving the app". IMO giving proper permissions at the web server level is out of scope for phpMyAdmin.

[devenbansod]

Hi, @jesucarr :
Please correct me if I have understood something wrong from your comments:

1. If you don't give the mentioned permissions, it works fine.
2. If you give the access (i.e. the mentioned permissions), it fails, even when you use the exact same values as in config.default.php (of course with slight changes).

If the above behavior describes your problem exactly, then I would agree with Marc that it should not be a phpMyAdmin bug since it is dealing with the permissions on the webserver's app.

But please correct me if I explained the behavior incorrectly somewhere.

[jesucarr]

Sorry guys I think I confused you rather than help to debug. The point is that if it doesn't load any config.inc.php it works, but if it loads a config.inc.php (even the default one) it gets the error. Obviously I want to be able to load my custom config.inc.php.

[lem9]

@jesucarr Please confirm whether or not Deven correctly summarized your problem. The reason we are asking, is to determine if phpMyAdmin has a bug affecting you, or if it's a permission problem at the web server's level (which we are not responsible of).

[jesucarr]

I'm not talking about any weird permissions configuration. I just wanted to test if it was an issue with my config.inc.php. How am I supposed to load my custom config.inc.php if I don't give it read permissions to the webserver running phpMyAdmin?

[lem9]

@jesucarr To help me help you, please reply by just Yes or No: do you consider this to be a phpMyAdmin bug? Before replying, note that placing config.inc.php under /etc/phpMyAdmin is not the decision of the phpMyAdmin project, but of CentOS.

[jesucarr]

I suppose it is a phpMyAdmin bug, unless the the config.inc.php need to be in the top level directory directory being served by the webserver in order for the Designer to work? Would that explain why $orig_data[0] is empty?

[lem9]

I just made a test: moving config.inc.php to another directory (that the web server is allowed to read) and define this other directory in libraries/vendor_config.php like this:

define('CONFIG_DIR', '/var/www/marc/');

With that in place, I have no problem using the Designer. Disclaimer: I'm not using CentOS but Debian 8.

[jesucarr]

Yes, the package defines it like this:

define('CONFIG_DIR', '/etc/phpMyAdmin/');

So it should work too. The real question is: what can cause $orig_data[0] and $GLOBALS['controllink'] to be empty?

[lem9]

Please do some more tracing. In librairies/common.inc.php there are 3 places where $controllink can be initialized, so we need to know where it's initialized and whether it's empty or not at this point.

[jesucarr]

it is initialized in line 974, and the content is

mysqli Object
(
    [affected_rows] => 0
    [client_info] => mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $
    [client_version] => 50011
    [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.44-MariaDB
    [server_version] => 50544
    [stat] => Uptime: 309874  Threads: 2  Questions: 417620  Slow queries: 0  Opens: 12426  Flush tables: 2  Open tables: 64  Queries per second avg: 1.347
    [sqlstate] => 00000
    [protocol_version] => 10
    [thread_id] => 29172
    [warning_count] => 0
)

[lem9]

Thanks. This is the first hint we get (apart from your CentOS comment) that this is happening on MariaDB 5.5.44.

[lem9]

I compared the results of print_r with mine at initialization time, and they are similar (but mine is to MySQL 5.7.8). I did a print_r further in the logic:

function PMA_saveDesignerSetting($index, $value)
{
error_log('C');
error_log(print_r($GLOBALS['controllink'], true));

and get similar valid info for the control link. Can you confirm that you still don't get this info at this point?

[jesucarr]

I get:

[Sat Jan 02 17:03:01.505978 2016] [proxy_fcgi:error] [pid 26339:tid 139719111309056] [client x.x.x.x:21925] AH01071: Got error 'PHP message: C\nPHP message: mysqli Object\n(\n    [affected_rows] => 0\n    [client_info] => mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $\n    [client_version] => 50011\n    [connect_errno] => 0\n    [connect_error] => \n    [errno] => 0\n    [error] => \n    [error_list] => Array\n        (\n        )\n\n    [field_count] => 0\n    [host_info] => Localhost via UNIX socket\n    [info] => \n    [insert_id] => 0\n    [server_info] => 5.5.44-MariaDB\n    [server_version] => 50544\n    [stat] => Uptime: 91590  Threads: 2  Questions: 879861  Slow queries: 0  Opens: 1285  Flush tables: 2  Open tables: 64  Queries per second avg: 9.606\n    [sqlstate] => 00000\n    [protocol_version] => 10\n    [thread_id] => 9610\n    [warning_count] => 0\n)\n\n', referer: https://myphpmyadmindomain/db_designer.php?db=mydb&token=xxxxxx
[Sat Jan 02 17:03:01.541914 2016] [proxy_fcgi:error] [pid 26424:tid 139719257114368] [client x.x.x.x:36104] AH01071: Got error 'PHP message: C\nPHP message: mysqli Object\n(\n    [affected_rows] => 0\n    [client_info] => mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $\n    [client_version] => 50011\n    [connect_errno] => 0\n    [connect_error] => \n    [errno] => 0\n    [error] => \n    [error_list] => Array\n        (\n        )\n\n    [field_count] => 0\n    [host_info] => Localhost via UNIX socket\n    [info] => \n    [insert_id] => 0\n    [server_info] => 5.5.44-MariaDB\n    [server_version] => 50544\n    [stat] => Uptime: 91590  Threads: 2  Questions: 879868  Slow queries: 0  Opens: 1286  Flush tables: 2  Open tables: 64  Queries per second avg: 9.606\n    [sqlstate] => 00000\n    [protocol_version] => 10\n    [thread_id] => 9612\n    [warning_count] => 0\n)\n\n', referer: https://myphpmyadmindomain/db_designer.php?db=mydb&token=xxxxxx
[Sat Jan 02 17:03:01.558226 2016] [proxy_fcgi:error] [pid 26338:tid 139719052560128] [client x.x.x.x:46275] AH01071: Got error 'PHP message: C\nPHP message: mysqli Object\n(\n    [affected_rows] => 0\n    [client_info] => mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $\n    [client_version] => 50011\n    [connect_errno] => 0\n    [connect_error] => \n    [errno] => 0\n    [error] => \n    [error_list] => Array\n        (\n        )\n\n    [field_count] => 0\n    [host_info] => Localhost via UNIX socket\n    [info] => \n    [insert_id] => 0\n    [server_info] => 5.5.44-MariaDB\n    [server_version] => 50544\n    [stat] => Uptime: 91590  Threads: 2  Questions: 879875  Slow queries: 0  Opens: 1286  Flush tables: 2  Open tables: 64  Queries per second avg: 9.606\n    [sqlstate] => 00000\n    [protocol_version] => 10\n    [thread_id] => 9614\n    [warning_count] => 0\n)\n\n', referer: https://myphpmyadmindomain/db_designer.php?db=mydb&token=xxxxxx

[lem9]

So, $GLOBALS['controllink'] is no longer empty? (You said previously that it was empty at this point).

[jesucarr]

Sorry I think I wasn't debugging properly, error_log works better. The one empty is $orig_data after the fetch at line 730, that's why it goes to the INSERT instead of the UPDATE.

If I do print_r($GLOBALS['dbi']->getError($GLOBALS['controllink'])); I get 1.

[lem9]

Any chance you could install phpMyAdmin downloaded from http://phpmyadmin.net?

[jesucarr]

I get the same error when installing phpMyAdmin from http://phpmyadmin.net

[devenbansod]

@jesucarr : Does the change suggested in this comment (#11808 (comment)) work for you too ?

[jesucarr]

Yes that seems to work!

[devenbansod]

Thanks @jesucarr for all the tests and feedback help.