Error: duplicate entry 414 (INSERT INTO menu_per_role ...)

Hi Gillypots,

I have never been able to duplicate this one... Other people had the error for a while and it disappeared. And now you report it...

Where you using version 1.4 before, or is that a new install?

Thank you.
Alexis Wilke

The error means that the database system did an attempt at saving the same thing twice and the system prevented the 2nd save. You can always edit the menu to verify that it says what you meant it to say, then do something (i.e. don't click Save to not see the error.)

There are actually many such errors in the Core of Drupal 6.x. It has slowly been fixed. The worst one is in the Themes implementation. It can generate pages and pages of errors. (I have a fix in #80 of the thread talking about that problem and it is likely going to be fixed in the next version of Drupal.)

Now, I added a small fix to lock the table so you should not get the error any more.

It will be in the -dev version within the next 12h (if you can't wait: get the .module directly from the CVS).

If you would let us know whether you can reproduce that problem once you installed the fix... it would be great! I tried to reproduce the problem with a new site and it failed miserably (i.e. no failure whatsoever.)

Thank you.
Alexis Wilke

P.S. Another note... This module would not, in any way, generate an unsafe link. It may show it when you intended the link to be hidden, but since the users can still go to the destination, whether you show it or not, there is no security issues here.

This is in 1.6

Thank you.
Alexis

Hi,

I just came across this same error warning myself after upgrading to php 5.3. I was using Menu per Role V1.5.

To hopefully correct it I upgraded to Menu per Role V1.6 and then had this WSOD fatal error...

"Warning: Table 'watchdog' was not locked with LOCK TABLES query: INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', '%message in %file on line %line.', 'a:4:{s:6:\"%error\";s:12:\"user warning\";s:8:\"%message\";s:139:\"Duplicate entry '70800' for key 1\nquery: INSERT INTO menu_per_role (mlid, rids, hrids) VALUES (70800, '', '')\";s:5:\"%file\";s:84:\"/MYSITE/sites/all/modules/menu_per_role/menu_per_role.module\";s:5:\"%line\";i:138;}', 3, '', 'http://MYSITE/node/422/edit', 'http://MYSITE/node/422/edit', '94.9.241.194', 1272311555) in /MYSITE/includes/database.mysqli.inc on line 128"

I rolled back to 1.5 as an occasional warning is better than a WSOD!

Victoria

This does not make sense! I'm protecting the only place where I change the table...

However, I'm using PostgreSQL and you are using MySQL. I'm wondering if by any chance the db_affected_row() would return 0 if the row does not require any modification... which would be totally wrong, but I cannot otherwise explain why we'd get an error here.

      db_lock_table('menu_per_role');
      db_query("UPDATE {menu_per_role} SET rids = '%s', hrids = '%s' WHERE mlid = %d", $rids_str, $hrids_str, $mlid);
      if (db_affected_rows() == 0) {
        // if nothing was affected, the row did not exist yet
        db_query("INSERT INTO {menu_per_role} (mlid, rids, hrids) VALUES (%d, '%s', '%s')", $mlid, $rids_str, $hrids_str);
      }
      db_unlock_tables();

I'll try in one of my install that uses MySQL.

Thank you.
Alexis

Okay, I see that's the problem. Who is the moron who thought of such an idiotic feature?! MySQL is broken, you need to switch to PostgreSQL...

mysql> update role set name = 'anonymous user' where rid  = 1;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Okay, anyway, I'll look into what other people do in this case.

Thank you.
Alexis

Okay, I thought I had seen that in other modules. For instance, the search.module around line 290:

    db_query("UPDATE {search_total} SET count = %f WHERE word = '%s'", $total, $word);
    if (!db_affected_rows()) {
      db_query("INSERT INTO {search_total} (word, count) VALUES ('%s', %f)", $word, $total);
    }

This issue shows how it was before and how it is now #22786: db_affected_rows code cleanup (note that was in 2005).

There is a link in there that shows the change to the MySQL connection so the db_affected_row() works as expected instead of being broken. There it is: matched instead of only changed.

Therefore, this sounds like you have a MySQL setup glitch. It either ignores the 2 in the connect or it cannot be changed programmatically (i.e. you'd need to make changes to the MySQL initialization file or something like that...)

Let us know if you find something on your end. On my end, I could remove the table lock since I thought it should anyway not be necessary...

Thank you.
Alexis Wilke

P.S. At some point, I will add a test in the mini module so by going to the Report screen, one will be able to see whether there is this problem with their MySQL installation. See #793330: Add a test on MySQL affected rows....

Hi,

it seems to me that the inconvenience is the same as the one reported in

http://drupal.org/node/759302

Would it be possible to have a similar patch?

flatline2010,

I'm glad that following this link: http://www.palantir.net/blog/beware-mysql-51-my-son the author says clearly that MySQL 5.1 is totally broken. Contrary to PostgreSQL which follows set rules and works. Just switch to a solid database! 8-)

Now, this being said, that post mentions the idea/concept of NOT NULL + DEFAULT combination as being the source of failures. Looking at the menu_per_role table, there is a default value on the mlid column. That's wrong according to that post. So, what you want to do is remove the DEFAULT. It is not a good idea to have a default on a PRIMARY KEY anyway since each entry should be distinct.

I'm posting a patch to the CVS soon.

Thank you for the pointers!
Alexis

Patch in CVS. New -dev within 12h.

Thank you.
Alexis

user warning: Duplicate entry '371' for key 'PRIMARY' query: INSERT INTO menu_per_role (mlid, rids, hrids) VALUES (371, '', '') in /home/users/rad/blablabla/web/sites/all/modules/menu_per_role/menu_per_role.module on line 140.

powery,

Look at the code and tell me what's wrong...

Did you run update.php?

Thank you.
Alexis

Since it looks like it fails when both strings are empty (''), maybe if we delete the row in that specific case will it fix this problem; and it is a good optimization.

Let me know whether that helps or not.

Thank you.
Alexis

Yes, update.php was executed. I deleted the row from table menu_per_role and saved the content. There was no error after saving content. But after next saving the same error as before. So deleting the row with did not help.

Sorry, I meant that I applied a change in the -dev version, which should be available within 12h. That change will delete "empty" rows (i.e. those where the rids and hrids strings are empty.)

Also, would you mind to install and run mini_test? I just made an update for that module. It will appear in the -dev version within 12h (it is in the CVS if you know how to handle that.)

If that test works, I'm even more at a loss, but if it fails then I know that your db_affected_rows() does not work properly which is a known issue with MySQL 5.1. They have a fix when opening the database they use a "special" flag that will ensure that the database returns the correct value, but I'm wondering whether in some rare cases it still does not work.

The other possibility would be that the count fails whenever the rows are empty. Yet, I tried in the one Drupal install I have with MySQL and it worked as expected. So I have no clue why it would fail for you.

Thank you.
Alexis

The dev version from 8.6. works well and there is no error (from #14) after saving content.

The dev Mini test module:
Test affected rows.
Database Failed
The test failed. The UPDATE returned 0 instead of 1.
Return messages
CREATE TABLE {mini_test_table} ( `id` INT NOT NULL auto_increment, `a` INT NOT NULL, `b` TEXT NOT NULL, PRIMARY KEY (id) ) /*!40100 DEFAULT CHARACTER SET UTF8 */
INSERT INTO {mini_test_table} (id, a, b) VALUES (1, 0, '')
UPDATE {mini_test_table} SET a = 0, b = '' WHERE id = 1
DROP TABLE {mini_test_table}

powery,

Okay, the UPDATE is expected to return 1, not 0. Your install returns 0. My installs returns 1, with MySQL and PostgreSQL. What's wrong with your install? I do not know. But as long as that test says The test failed. The UPDATE returned 0 instead of 1., Menu per Role will fail for you.

I guess I don't have the same version of MySQL as you do. I tried with the following which says 5.1.x

mysql Ver 14.14 Distrib 5.1.31, for debian-linux-gnu (i486) using EditLine wrapper

Thank you.
Alexis

My Mysql version is 5.1.46, PHP version 5.3.2, Drupal 6.17.

Yeah... MySQL is definitively broken.

Everyone is having this issue everywhere... For instance: #638702: insertion errors

And there doesn't seem to be a good solution at this moment.

I guess I should check with a SELECT if the DB is MySQL... so MySQL users will have a small penalty.

More later.
Alexis

Okay, the core people refused to do anything about the function that all of a sudden does not return the right value saying that would be changing something in the functionality. It seems to me that would be the opposite, but I guess my brain doesn't work right! 8-)

Anyway, I added a SELECT in case you are using MySQL to test whether the row already exists and if so, skip the INSERT.

The new -dev-1.x should be out within 12h.

Thank you.
Alexis Wilke

Hi,
I'm getting this warning, which I guess has the same origin

user warning: Duplicate entry '524' for key 1 query: INSERT INTO menu_per_role (mlid, rids, hrids) VALUES (524, '2,3,4', '1') in /var/www/blabla/sites/all/modules/menu_per_role/menu_per_role.module on line 140.

I'm using D6.19, menu per role 6.x-1.7 and I just downgraded mysql from 5.1.49 to 5.1.30 for this same warning, didn't solve the problem.
The dev module solved this?

sapox,

Yes, I thought I checked all my projects and updated them, but I guess I missed this one... 8-}

You can see in the CVS that the -dev has the changes.

http://drupal.org/project/cvs/107330

Thank you.
Alexis

Hi Alexis,

Which is the procedure to switch to the dev version?
I mean, do I have to uninstall first the standard module and then install the dev?
Or I just simply replace the files of the module?
I will lose all the configuration that I already have for all my nodes?

Thx.

Just overwrite the files and execute any update.php that is in the module.

If you want to be able to go back and there are update.php, make sure to get a copy of your database first. That way when you go back you restore the database and the older version.

Deleting the module folder won't be necessary for this module. It is a requirement for some other modules such as the nodeword module which changes their files all the time...

Thank you.
Alexis

Okay, I created a version 1.8 with all the changes I made earlier so that way most everyone will have them! 8-)

Thank you.
Alexis

Hello,

Which version should I install in order to avoid the MySQL error, the latest stable release or the latest dev release of this module?

Thanks in advance,

1.8 and following include the fix. Although using the 1.x-dev is good if you have time to report any problem encountered. 8-)

Thank you.
Alexis

@AlexisWilke

Thanks for the information. I may indeed use the development version. I am thinking of testing this module to see if it can enable me to use the classical administration menu module (admin_menu dev 6.x-3.x) to show primary links for the anonymous users. Maybe you have some piece of information in this regard?!

Hi Geir19,

As far as I know, the menu_per_role is compatible with the Admin menu. However, the Admin menu only shows the Navigation menu, as far as I know. So it may not really be appropriate for anonymous users?

Thank you.
Alexis