ERROR 1062 (23000) at line 21424: Duplicate entry '0-2198' for key 'PRIMARY'

Started by boinc-australia, August 30, 2020, 02:18:59 AM

Previous topic - Next topic

boinc-australia

Background:
I did a complete new installation on a new Ubuntu 18-04 dedicated server following these instructions.
hxxps://websiteforstudents.com/install-simple-machine-forum-on-ubuntu-17-04-17-10-with-apache2-mariadb-and-php/

  The only difference is that I used the newest version smf_2-0-17_install.zip.  I also had to install php-mcrypt as it is not in Ubuntu 18.

As I did not use phpmyadmin in the installation so I installed it and when I enter hxxp:158.69.23.50/phpmyadmin [nonactive] I get the login screen so I assume it is OK ??

PROBLEM:

I backed up the forum in the old location before it was gone and it was version smf_2-0-15
When I tried to apply the backup to the new version smf_2-0-17 I got the error below.

root@dingo1:/tmp# mysql -u root -p smf < /tmp/yoda_smf-complete_2020-05-30.sql
Enter password:

ERROR 1062 (23000) at line 21424: Duplicate entry '0-2198' for key 'PRIMARY'

Any help appreciated.

PSS:

I found a more recent backup but get the same error on a different line number:

root@dingo1:/tmp# mysql -u root -p smf < /tmp/yoda_smf-complete_2020-08-29.sql
Enter password:
ERROR 1062 (23000) at line 23328: Duplicate entry '0-2198' for key 'PRIMARY'

Line 23328 is the BOLD line below:

[b]INSERT INTO `smf_log_search_subjects`[/b]
(`word`, `id_topic`)
VALUES ('#', 928),
('#1', 1992),
('&#8207;', 1280),
('&#8207;', 1312),
('&#8207;', 1391),
('&#8207;', 1482),
('&#8207;', 1498),
('&#8207;', 1512),
(0, 1279),
(0, 2198),
(00, 2198),
(00, 2462),
(000, 884),
(000, 2022),
(01, 1765),
(01, 2198),
(01, 2334),
('01am', 1245),
(02, 637),
(04, 150),
(04, 269),
(04, 317),
(05, 1765),
(06, 92),
(08, 149),
('0ghz', 1499),
(1, 476),
(1, 660),
(1, 734),
(1, 757),
(1, 897),
(1, 924),
(1, 925),
(1, 1043),
(1, 1256),
(1, 1279),
(1, 1362),
(1, 1486),
(1, 1544),
(1, 1871),
(1, 1969),
(1, 2079),
(1, 2163),
(1, 2470),
('1&#8207;', 1696),
(10, 71),
(10, 455),
(10, 779),
(10, 1245),
(10, 1797),
(10, 1889),
(10, 1909),
(10, 1912),
(10, 1979),
(10, 2027),
(10, 2137),
(10, 2143),
(10, 2359),
(10, 2372),
(10, 2388),
(10, 2434),
(100, 455),
(100, 616),
(100, 788),
(100, 1620),
(100, 1973),
(100, 1974),
(100, 2215),
(100, 2413),
('100k', 22),
(1060, 1908),
('10core', 1856),
('10m', 1739),
(11, 907),
(11, 1374),
(11, 1697),
(11, 2334),
(12, 964),
(12, 1210),
(12, 1245),
(12, 1374),
(12, 1407),
(12, 2163),
(12, 2371),
(128, 2446),
(13, 846),
(13, 1548),
(13, 1942),
(13, 2235),
(1366, 714),
(14, 486),
(14, 819),
(14, 884),
(14, 1020),
(14, 1279),
(14, 2193),
(14, 2195),
(14, 2198),
(142, 1484),
(15, 148),
(16, 2422),
('16-21', 837),
(1650, 2256),
(1660, 2234),
(1660, 2237),
(1660, 2246),
(1700, 168),
(18, 467),
(18, 511),
(182, 92),
(182, 149),
(182, 229),
(185, 359),
(186, 511),
(19, 2334),
(19, 2371),
(19, 2462),
('19-beam', 1893),
(190, 546),
(1909, 2372),
(1960, 1667),
('19th', 1539),
('19th', 1908),
('1gb', 663),
('1gb', 1900),
('1hpv', 341),
('1st', 1983),
('1u', 538),
(2, 5),
(2, 432),
(2, 508),
(2, 557),
(2, 609),
(2, 748),
(2, 754),
(2, 936),
(2, 1001),
(2, 1043),
(2, 1252),
(2, 1596),
(2, 2057),
(2, 2064),
(2, 2119),
(2, 2143),
(2, 2195),
(2, 2327),
(2, 2371),
(20, 144),
(20, 304),
(20, 481),
(20, 558),
(20, 610),
(20, 692),
(20, 759),
(20, 836),
(20, 896),
(20, 953),
(20, 1059),
(20, 1109),
(20, 1157),
(20, 2253),
(20, 2322),
(20, 2462),
('20-kashif-hipvr-twom', 507),
('20-series', 2273),
(2005, 1765),
(2007, 870),
(2009, 47),
(2009, 71),
(2009, 712),
('2009-06-18', 506),
(2010, 655),
(2010, 757),
(2010, 833),
(2010, 974),
(2011, 1041),
(2011, 1124),
(2011, 1169),
(2012, 1161),
(2012, 1224),
(2012, 1235),
(2012, 1239),
(2012, 1388),
('2012&#8207;', 1225),
('2012&#8207;', 1376),
(2013, 1353),
(2013, 1451),
('2013&#8207;', 1561),
(2014, 1598),
(2014, 1637),
(2014, 1676),
(2014, 1686),
(2015, 1705),
(2015, 1766),
(2015, 1847),
(2016, 1883),
(2016, 1908),
(2016, 1911),
(2017, 2040),
(2017, 2047),
(2017, 2049),
(2017, 2051),
(2017, 2059),
(2018, 2121),
(2018, 2130),
(2018, 2147),
(2018, 2148),
(2018, 2158),
(2018, 2166),
(2018, 2179),
(2018, 2200),
(2019, 2226),
(2019, 2227),
(2019, 2235),
(2019, 2251),
(2019, 2263),
(2019, 2267),
(2019, 2276),
(2019, 2291),
(2019, 2298),
(2019, 2301),
(2020, 2282),
(2020, 2384),
(2020, 2385),
(2020, 2392),
(2020, 2393),
(2020, 2405),
(2020, 2406),
(2020, 2421),
(2020, 2477),
('20th', 2312),
('20thread', 1856),
(21, 1374),
(21, 1376),
('21-22', 1210),
('21st', 1857),
(22, 813),
(22, 836),
(22, 2147),
('22nd', 537),
(23, 1548),
('23-25', 1651),
('23rd', 501),
('23rd', 537),
(24, 2462),
('24th', 494),
('24th', 501),
('24th', 2465),
(25, 494),
(25, 830);






shawnb61

I have a theory -

I saw this a few times in the past, when I used to break up my exports into smaller, more manageable sizes.  The problem was that my log_search_* tables were more recent than my messages/topics/boards tables.  Everything looked ok, but adding new messages later could result in collisions when adding new message content to the search tables, as there were already entries for those topics/msgs.

Two things to try:
- don't restore the log_search_* tables' data; create them empty, then rebuild the search index once the forum is up
- find the highest topic & message ids in the topics & message tables, then delete rows in the log_search_* tables with higher values that were added later

The first option, rebuilding the index, is easier & cleaner.  I would only do the second option if you have a massive DB and rebuilding the index is a problem.

Hope this helps...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

boinc-australia

I tried your first option and deleted all the data related to log_search_* tables'.  I could not work out what to insert so I deleted the insert part and it progressed past that bit but got another error:

ERROR 1071 (42000) at line 50046: Specified key was too long; max key length is 1000 bytes

Line 50046 is the following:

CREATE TABLE `smf_messages` (
`id_msg` int(10) unsigned NOT NULL auto_increment,
`id_topic` mediumint(8) unsigned NOT NULL default 0,
`id_board` smallint(5) unsigned NOT NULL default 0,
`poster_time` int(10) unsigned NOT NULL default 0,
`id_member` mediumint(8) unsigned NOT NULL default 0,
`id_msg_modified` int(10) unsigned NOT NULL default 0,
`subject` varchar(255) NOT NULL default '',
`poster_name` varchar(255) NOT NULL default '',
`poster_email` varchar(255) NOT NULL default '',
`poster_ip` varchar(255) NOT NULL default '',
`smileys_enabled` tinyint(4) NOT NULL default 1,
`modified_time` int(10) unsigned NOT NULL default 0,
`modified_name` varchar(255) NOT NULL default '',
`body` mediumtext NOT NULL,
`icon` varchar(16) NOT NULL default 'xx',
`approved` tinyint(3) NOT NULL default 1,
PRIMARY KEY (`id_msg`),
UNIQUE `topic` (`id_topic`, `id_msg`),
UNIQUE `ID_BOARD` (`id_board`, `id_msg`),
UNIQUE `ID_MEMBER` (`id_member`, `id_msg`),
KEY `participation` (`id_member`, `id_topic`),
KEY `showPosts` (`id_member`, `id_board`),
KEY `ID_TOPIC` (`id_topic`),
KEY `ipIndex` (`poster_ip`(15), `id_topic`),
KEY `approved` (`approved`),
KEY `id_member_msg` (`id_member`, `approved`, `id_msg`),
KEY `current_topic` (`id_topic`, `id_msg`, `id_member`, `approved`),
KEY `related_ip` (`id_member`, `poster_ip`, `id_msg`)
) ENGINE=MyISAM;


I found a post about this topic but there was no solution other than changing the character set.  I ran the script below but I still get the same error:

#!/bin/bash

DATABASE=some_db

#CHARACTER_SET=utf8
#COLLATE=utf8_unicode_ci
CHARACTER_SET=utf8mb4
COLLATE=utf8mb4_unicode_ci

TABLES=$(echo SHOW TABLES | mysql -s $DATABASE)

for TABLE in $TABLES ; do
    echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARACTER_SET COLLATE $COLLATE;"
    echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARACTER_SET COLLATE $COLLATE" | mysql $DATABASE
done


This was the output of the query:

QuoteALTER TABLE smf_admin_info_files CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_approval_queue CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_attachments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_ban_groups CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_ban_items CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_board_permissions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_boards CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_calendar CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_calendar_holidays CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_categories CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_collapsed_categories CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_custom_fields CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_group_moderators CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_actions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_activity CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_banned CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_boards CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_digest CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_errors CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_floodcontrol CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_group_requests CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_karma CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_mark_read CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_member_notices CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_notify CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_online CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_packages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_polls CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_reported CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_reported_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_scheduled_tasks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_search_messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_search_results CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_search_subjects CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_search_topics CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_spider_hits CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_spider_stats CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_subscribed CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_log_topics CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_mail_queue CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_membergroups CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_members CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_message_icons CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_moderators CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_openid_assoc CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_package_servers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_permission_profiles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_permissions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_personal_messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_pm_recipients CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_pm_rules CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_poll_choices CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_polls CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_scheduled_tasks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_sessions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_settings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_smileys CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_spiders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_subscriptions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_themes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE smf_topics CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I looked around but could not see if there was a place to increase this ???




shawnb61

Lets try this another way...

Do the restores.  Use the app to delete the search index, then rebuild it.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

boinc-australia

I can't login to smf because there is not a smf_messages table if that is what you are saying. 

I am not sure what restroe you are talking about.  The backup I have was not created by the cpanel or phpMyAdmin.  I used the smf backup from the admin page to backup the database.  Is there a restore on that menu ??  I can't login.

Advertisement: