[mythtv-users] 0.26 upgrade: Database schema upgrade failed. Duplicate entry for primary key.
Jon Whitear
jon at whitear.org
Sat Oct 6 06:50:03 UTC 2012
Bill,
Thanks. Here's what I get
> Next, I'd verify that the time zone conversion is correct by using the
> SELECT statement in the 3rd gray box here (and checking the response):
>
> http://www.mythtv.org/wiki/MySQL_Time_Zone_Tables
That's confirmed OK.
>
> That looks line junk in the recordedseek table. Try:
>
> $ mysql -u mythtv -p mythconverg -e \
> "SELECT COUNT(*) FROM recordedseek WHERE starttime = '0000-00-00 00:00:00'"
jon at media:~$ mysql -u root -p mythconverg -e "SELECT COUNT(*) FROM recordedseek WHERE starttime = '0000-00-00 00:00:00'"
Enter password:
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
jon at media:~$ mysql -u root -p mythconverg -e "SELECT * FROM recordedseek WHERE starttime = '0000-00-00 00:00:00'"
Enter password:
+----------+---------------------+------+--------+------+
| chanid | starttime | mark | offset | type |
+----------+---------------------+------+--------+------+
| 71 | 0000-00-00 00:00:00 | 0 | 0 | 0 |
| 33554432 | 0000-00-00 00:00:00 | 0 | 0 | 0 |
+----------+---------------------+------+--------+------+
So they look like junk to me.
> This would tell you how many chanids below 1000 you have:
>
> $ mysql -u mythtv -p mythconverg -e \
> "SELECT COUNT(*) FROM recordedseek WHERE chanid < 1000"
jon at media:~$ mysql -u root -p mythconverg -e "SELECT COUNT(*) FROM recordedseek WHERE chanid < 1000"
Enter password:
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
jon at media:~$ mysql -u root -p mythconverg -e "SELECT * FROM recordedseek WHERE chanid < 1000"
Enter password:
+--------+---------------------+--------+----------------------+------+
| chanid | starttime | mark | offset | type |
+--------+---------------------+--------+----------------------+------+
| 18 | 2347-36-75 04:28:70 | 589824 | 4326041693624466687 | -50 |
| 18 | @345-40-97 03:14:56 | 589824 | 14378225595496791551 | -63 |
| 71 | 0000-00-00 00:00:00 | 0 | 0 | 0 |
| 71 | 0000-00-16 53:01:78 | 0 | 0 | 0 |
+--------+---------------------+--------+----------------------+------+
That also looks like junk.
>
> Without knowing more, I wouldn't start deleting any records.
>
Figuring that they're junk, I deleted the two records with the zero start times:
mysql -u root -p mythconverg -e "DELETE FROM recordedseek WHERE starttime = '0000-00-00 00:00:00'"
But then it still fails with:
Oct 6 16:32:16 media mythlogserver: mythtv-setup[4831]: E CoreContext dbcheck.cpp:417 (performActualUpdate) DB Error (Performing database upgrade): #012Query was: UPDATE recordedseek SET starttime = CONVERT_TZ(starttime, 'SYSTEM', 'UTC') ORDER BY starttime #012Error was: Driver error was [2/1062]:#012QMYSQL: Unable to execute query#012Database error was:#012Duplicate entry '3355443218-0000-00-00 00:00:00--50-589824' for key 'PRIMARY'#012 #012new version: 1304
So i tried this:-
jon at media:/var/log$ mysql -u root -p mythconverg -e "SELECT * FROM recordedseek WHERE starttime LIKE '%00:00:00%' ORDER BY chanid"
Enter password:
+------------+---------------------+---------+----------------------+------+
| chanid | starttime | mark | offset | type |
+------------+---------------------+---------+----------------------+------+
| 18 | 0000-00-00 00:00:00 | 589824 | 4326041693624466687 | -50 |
| 71 | 0000-00-00 00:00:00 | 0 | 0 | 0 |
| 18177 | 0000-00-00 00:00:00 | 0 | 0 | 0 |
| 19713 | 0000-00-00 00:00:00 | 0 | 0 | 0 |
| 25719691 | 0000-00-00 00:00:00 | 7879938 | 0 | 0 |
| 67108882 | 0000-00-00 00:00:00 | 589824 | 2606528653085113855 | -57 |
| 134217746 | 0000-00-00 00:00:00 | 589824 | 2252274802708708607 | -45 |
| 268435474 | 0000-00-00 00:00:00 | 589824 | 12131923339950423551 | -63 |
| 402653202 | 0000-00-00 00:00:00 | 589824 | 4326041693624466687 | -50 |
| 469762066 | 0000-00-00 00:00:00 | 589824 | 12131923339950423551 | -63 |
| 671088658 | 0000-00-00 00:00:00 | 589824 | 14378225595496791551 | -63 |
| 805306386 | 0000-00-00 00:00:00 | 589824 | 5492390434229714687 | -68 |
| 1879048210 | 0000-00-00 00:00:00 | 589824 | 586848938162909183 | -39 |
| 2181038098 | 0000-00-00 00:00:00 | 589824 | 586848938162909183 | -39 |
| 2281701394 | 0000-00-00 00:00:00 | 589824 | 6318242413945286655 | -56 |
| 2348810258 | 0000-00-00 00:00:00 | 589824 | 14221316490120134399 | -50 |
| 2483027986 | 0000-00-00 00:00:00 | 589824 | 1464773788569960191 | -62 |
| 2483027986 | 0000-00-00 00:00:00 | 589824 | 14221316490120134399 | -50 |
| 2550136850 | 0000-00-00 00:00:00 | 589824 | 5492390434229714687 | -68 |
| 2751463442 | 0000-00-00 00:00:00 | 589824 | 14221316490120134399 | -50 |
| 2751463442 | 0000-00-00 00:00:00 | 589824 | 7025676991349391103 | -44 |
| 2885681170 | 0000-00-00 00:00:00 | 589824 | 8496286987639324159 | -51 |
| 2952790034 | 0000-00-00 00:00:00 | 589824 | 17540385852608478719 | -86 |
| 3087007762 | 0000-00-00 00:00:00 | 589824 | 11669786508168265215 | -56 |
| 3087007762 | 0000-00-00 00:00:00 | 589824 | 8496286987639324159 | -51 |
| 3154116626 | 0000-00-00 00:00:00 | 589824 | 17540385852608478719 | -86 |
| 3154116626 | 0000-00-00 00:00:00 | 589824 | 12131923339950423551 | -63 |
| 3355443218 | 0000-00-00 00:00:00 | 589824 | 12131923339950423551 | -63 |
| 3355443218 | 0000-00-00 00:00:00 | 589824 | 2606528653085113855 | -57 |
| 3355443218 | 0000-00-00 00:00:00 | 589824 | 4326041693624466687 | -50 |
| 3355443218 | 0000-00-00 00:00:00 | 589824 | 14205690230866181631 | -68 |
| 3422552082 | 0000-00-00 00:00:00 | 589824 | 14205690230866181631 | -68 |
| 3489660946 | 0000-00-00 00:00:00 | 589824 | 8496286987639324159 | -51 |
| 3489660946 | 0000-00-00 00:00:00 | 589824 | 4326041693624466687 | -50 |
| 3489660946 | 0000-00-00 00:00:00 | 589824 | 2252274802708708607 | -45 |
| 3556769810 | 0000-00-00 00:00:00 | 589824 | 14205690230866181631 | -68 |
| 3556769810 | 0000-00-00 00:00:00 | 589824 | 2606528653085113855 | -57 |
| 3556769810 | 0000-00-00 00:00:00 | 589824 | 12131923339950423551 | -63 |
| 3690987538 | 0000-00-00 00:00:00 | 589824 | 14378225595496791551 | -63 |
| 3758096402 | 0000-00-00 00:00:00 | 589824 | 12131923339950423551 | -63 |
| 3758096402 | 0000-00-00 00:00:00 | 589824 | 17540385852608478719 | -86 |
| 3825205266 | 0000-00-00 00:00:00 | 589824 | 9078343154616499711 | -62 |
| 3892314130 | 0000-00-00 00:00:00 | 589824 | 4326041693624466687 | -50 |
| 3959422994 | 0000-00-00 00:00:00 | 589824 | 2606528653085113855 | -57 |
| 3959422994 | 0000-00-00 00:00:00 | 589824 | 12131923339950423551 | -63 |
| 4026531858 | 0000-00-00 00:00:00 | 589824 | 9078343154616499711 | -62 |
| 4160749586 | 0000-00-00 00:00:00 | 589824 | 12131923339950423551 | -63 |
| 4160749586 | 0000-00-00 00:00:00 | 589824 | 4326041693624466687 | -50 |
| 4227858450 | 0000-00-00 00:00:00 | 589824 | 9078343154616499711 | -62 |
+------------+---------------------+---------+----------------------+------+
So if there are any two recordings with a zero start time on the same channel, I guess the schema upgrade will fail. Do I have any choice other than to delete them?!
Thanks again for your help.
Cheers,
Jon
More information about the mythtv-users
mailing list