Download & Extend
Issues

update 5.7 to 6.0 failed "ALTER TABLE {locales_target} ADD PRIMARY KEY (language, lid, plural)"

Posted by int on February 18, 2008 at 12:47am
Project:Drupal core
Version:6.x-dev
Component:locale.module
Category:bug report
Priority:normal
Assigned:Unassigned
Status:postponed (maintainer needs more info)

Issue Summary

When I update 5.7 to 6.0 show only this error:
user warning: Duplicate entry '0-0' for key 1 query: ALTER TABLE drupal_locales_target ADD PRIMARY KEY (language, lid, plural) in C:\Program Files\VertrigoServ\www\includes\database.mysql-common.inc on line 374.

Failed: ALTER TABLE {locales_target} ADD PRIMARY KEY (language, lid, plural)

In the BD I see that in drupal_locales_target I have almost 55 rows with lid = 0.

AttachmentSizeStatusTest resultOperations
upgrade.jpg73.59 KBIgnored: Check issue status.NoneNone
Login or register to post comments

Comments

#1

Posted by int on February 18, 2008 at 12:58am
Category:bug report» support request

I see that this problem is from 5.7.

How to fix this? Just delete lid=0? Or is better to delete pt language and add again..

#2

Posted by int on February 18, 2008 at 1:09am
Status:active» closed (fixed)

#3

Posted by yraffah on February 18, 2008 at 7:38pm

I have the same problem, how is this fixed?

#4

Posted by int on February 19, 2008 at 1:16am

delete all lid=0 in {locales_target}. and make the alter manual.

#5

Posted by tcconway on June 17, 2008 at 9:14pm

Hi.
I too have this issue. Yet, my {locales_target} has no records.
Any thoughts on what I could do to resolve?
Thanks!

#6

Posted by ryooki on July 27, 2008 at 5:05pm

how does one do this manually? I tried putting ALTER TABLE {locales_target} ADD PRIMARY KEY (language, lid, plural) in the SQL window, and I get a 1064 syntax error. I tried to make the lid column the primary key - which helped me find all the duplicates so I could delete them. However, making language or plural the primary key will drop lid from being the primary key. Any advice on what I'm doing wrong & what I need to do?

#7

Posted by int on July 29, 2008 at 10:19pm

ryooki - change the {} with the real name of the tables.

#8

Posted by ryosaeba on March 8, 2009 at 11:48am

I've the same problem, but my error is

user warning: Duplicate entry '636-0' for key 1 query: ALTER TABLE locales_target ADD PRIMARY KEY (language, lid, plural) in /Applications/MAMP/htdocs/walterfantauzzi/includes/database.mysql-common.inc on line 374.

How I can do?

#9

Posted by TheRec on June 15, 2009 at 2:14pm
Version:6.0» 6.12
Status:closed (fixed)» active

I get the same error, upgrading from 5.18 :

user warning: Duplicate entry '482-0' for key 1 query: ALTER TABLE locales_target ADD PRIMARY KEY (language, lid, plural) in /includes/database.mysql-common.inc on line 374.

Now, the odd part is that if I execute this SQL statement in PHPMyAdmin right after the update.php has finished to run for the first time (during the upgrade procedure), it works, without any error and the primary key is set as those three fields. So I have no idea why it fails when it is used in update.php.

#10

Posted by dww on June 16, 2009 at 3:48am
Component:update.module» locale.module

I know it's confusing, but "update.module" is for the part of core (added in 6.x) that checks for available updates to your modules and themes. You're talking about update.php, which is the "update system" component...

However, more specifically, this is a problem with the locale module's DB updates, so that'd be an even more appropriate place to get help...

#11

Posted by hass on September 25, 2009 at 6:11pm
Category:support request» bug report

#345852: update_6043: Duplicate entry warning when adding primary key to locales_target and #237715: D5 upgrade: ALTER TABLE locales_target fails has been marked as duplicate by catch. This is a bug so marking as this.

#12

Posted by catch on September 26, 2009 at 4:32am
Version:6.12» 6.x-dev
Status:active» needs review

I'm getting this as well.
Marked #345852: update_6043: Duplicate entry warning when adding primary key to locales_target and #237715: D5 upgrade: ALTER TABLE locales_target fails as duplicate.

Patch uses ALTER IGNORE TABLE - which will delete the duplicate records while making the new index.

ALTER IGNORE is MySQL specific - however I'm not sure if postgres will just ignore the keyword (in which case no worse off than now), or throw a syntax error - but this needs to be checked before commit.

AttachmentSizeStatusTest resultOperations
locales_target.patch822 bytesIgnored: Check issue status.NoneNone

#13

Posted by catch on October 27, 2009 at 5:36am

Added check for pgsql, which falls back to current behaviour.

AttachmentSizeStatusTest resultOperations
locales-target.patch1.31 KBIgnored: Check issue status.NoneNone

#14

Posted by masipila on November 29, 2009 at 5:20pm

I had the same problem and I can confirm that the patch in comment #13 worked.

Background information:

  • I am running MySQL
  • Upgrading from D5.20 to D6.14
  • SELECT * FROM drupal_locales_target where lid = 0 showed that there were more than 3600 rows with lid = 0 in the table before upgrading
  • Manually deleting the entries with lid=0 before or after update.php did not help - the original ALTER query (shipped with D6.14) did not work, there were some other duplicate values as well
  • Patching system.install so that it uses ALTER IGNORE for MySQL solved the problem - or at least update.php now runs without errors.

#15

Posted by int on November 30, 2009 at 8:03pm
Status:needs review» reviewed & tested by the community

#16

Posted by Gábor Hojtsy on December 7, 2009 at 1:01pm
Status:reviewed & tested by the community» postponed (maintainer needs more info)

Hm, Drupal strives to keep your data intact. Let's consider that:

1. The lid = 0 rows will not be possible to pair with the source strings (lid is the source string ID), so we should be safe dropping those.

2. Other duplicate issues though come from previous import problems (I bet files with bad plural forms), and we have the source string ID, but have a bad plural ID for it. It would be good to have some sampling of problematic databases to see the duplicate entries, before deciding to throw this data away.

We could also say that plural translations are relatively small amounts of data, and just live with some of them being lost, but it would be good to see some actual data samples first to be able to decide.

#17

Posted by int on December 8, 2009 at 10:26am

I think that upgrading from D5 to D6 is normal to miss lots of translated strings, the administrator will have to update with the most up-to-date D6 translation.

#18

Posted by masipila on December 12, 2009 at 7:11pm

@Gábor Hojtsy: I'll be glad to provdie you samples from my database. What exactly do want?

-Markus