Last modified: 2010-05-15 14:36:00 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 4748 - Script to delete old revisions in database
Script to delete old revisions in database
Status: CLOSED FIXED
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
unspecified
All All
: Normal enhancement (vote)
: ---
Assigned To: Rob Church
http://meta.wikimedia.org/wiki/Help:R...
:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2006-01-25 15:21 UTC by Sider
Modified: 2010-05-15 14:36 UTC (History)
1 user (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments

Description Sider 2006-01-25 15:21:50 UTC
Referring to Bug 3612, I request an enhancement for a delete-old-revisions
script. This would reduce the size of database and the costs for a server to run
the software. Refer for future descriptions here:
http://meta.wikimedia.org/wiki/Help:Reduce_size_of_the_database#Mediawiki_1.5beta3_and_younger_versions
Comment 1 Rob Church 2006-01-25 21:10:04 UTC
deleteOldRevisions maintenance script added to CVS HEAD. Will check with Brion
and then backport to 1.5 branch.
Comment 2 Sider 2006-01-29 13:58:24 UTC
I checked it with 1.6 devel upgraded via CVS and got this error:

Function:
Error: 1146 Table 'testdb.text' doesn't exist (localhost)

Backtrace:
GlobalFunctions.php line 606 calls wfBacktrace()
Database.php line 463 calls wfDebugDieBacktrace()
Database.php line 413 calls DatabaseMysql::reportQueryError()
purgeOldText.inc line 40 calls DatabaseMysql::query()
deleteOldRevisions.inc line 55 calls PurgeRedundantText()
deleteOldRevisions.php line 20 calls DeleteOldRevisions()

I think it refers to the table prefix. My structure is the following: 
$wgDBname           = "testdb";
$wgDBprefix         = "wiss";

Comment 3 Rob Church 2006-01-29 17:39:39 UTC
Having checked the code, it appears I hard-coded the name of the text table in
one particular call and overlooked it. Should now be fixed in CVS HEAD and 1.5
branch.
Comment 4 Sider 2006-02-01 19:53:30 UTC
I updated via CVS and got this:

Function:
Error: 1064 You have an error in your SQL syntax; check the manual that correspo
nds to your MySQL server version for the right syntax to use near 'WHERE old_id
NOT IN ( 2, 6, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 23, 24' at line 1 (
localhost)

Backtrace:
GlobalFunctions.php line 604 calls wfBacktrace()
Database.php line 463 calls wfDebugDieBacktrace()
Database.php line 413 calls DatabaseMysql::reportQueryError()
purgeOldText.inc line 40 calls DatabaseMysql::query()
deleteOldRevisions.inc line 55 calls PurgeRedundantText()
deleteOldRevisions.php line 20 calls DeleteOldRevisions()
Comment 5 Rob Church 2006-02-02 07:40:18 UTC
I can't see how that can occur. Both HEAD and REL_1_5 contain all the right
parentheses, and this error would have manifested itself during testing. Please
open the file purgeOldText.inc and ensure that line 40 appears as follows:

$res = $dbw->query( "SELECT old_id FROM $tbl_text WHERE old_id NOT IN ( $set )" );

If, for some reason, the parenthesis after $set is missing, add one in and check
again. I can't understand how this would arise, however; as I said, both HEAD
and REL_1_5 contain scripts that work fine, and have now been extensively tested.
Comment 6 Sider 2006-02-04 15:39:27 UTC
I checked line 40 but its the same line you wrote here. I give some extra data to you now, perhaps it 
helps to find the problem:

php deleteOldRevisions.php --delete

Delete Old Revisions

Searching for active revisions...done.
Searching for inactive revisions...done.
3982 old revisions found.
Deleting...done.
Searching for active text records in revisions table...done.
Searching for active text records in archive table...done.
Searching for inactive text records...A database error has occurred
Query: SELECT old_id FROM  WHERE old_id NOT IN ( 2, 3, 6, 8, 9, 10, 11, 12, 13,
14, 15, 16, 17, 18, 19, 20, 23, 24, 25, 29, 32, 35, 38, 40, 41, 42, 43, 44, 45,
46, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66,
67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 105, 106
, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 121, 122, 123
, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139
, 140, 141, 143, 146, 147, 148, 149, 150, 151, 152, 153, 156, 157, 158, 159, 160
, 161, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177
, 178, 179, 180, 181, 182, 183, 184, 185, 186, 188, 189, 190, 191, 192, 193, 194
, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210
, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226
, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242
, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258
, 259, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 270, 271, 272, 273, 274
, 275, 276, 277, 278, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 290
, 291, 292, 293, 294, 295, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306
, 307, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322
, 323, 324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 338
, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354
, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370
, 371, 372, 373, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386
, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402
, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418
, 419, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 431, 432, 433, 434
, 435, 436, 437, 438, 439, 440, 441, 442, 443, 444, 445, 446, 447, 448, 449, 450
, 451, 452, 453, 454, 455, 456, 457, 458, 459, 460, 461, 462, 463, 464, 465, 466
, 467, 468, 469, 470, 471, 472, 473, 474, 475, 476, 477, 478, 479, 480, 481, 482
, 483, 484, 485, 486, 487, 488, 489, 490, 491, 492, 493, 494, 495, 496, 497, 498
, 499, 500, 501, 502, 503, 504, 505, 506, 507, 508, 509, 511, 512, 513, 514, 515
, 516, 517, 518, 519, 520, 521, 522, 523, 524, 525, 526, 527, 528, 529, 530, 531
, 532, 533, 534, 535, 536, 537, 539, 540, 541, 542, 543, 544, 545, 546, 547, 548
, 550, 551, 552, 553, 554, 555, 556, 557, 558, 559, 561, 562, 563, 564, 565, 566
, 567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581, 582
, 583, 584, 585, 586, 587, 588, 589, 590, 591, 592, 593, 594, 595, 596, 597, 598
, 599, 600, 601, 602, 603, 604, 605, 606, 607, 608, 609, 610, 611, 612, 614, 615
, 616, 617, 618, 619, 620, 621, 622, 623, 624, 625, 626, 627, 628, 629, 630, 631
, 632, 633, 634, 635, 636, 637, 638, 639, 640, 641, 642, 643, 645, 646, 648, 649
, 650, 651, 652, 653, 654, 655, 657, 658, 659, 660, 661, 662, 663, 664, 665, 666
, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 680, 681, 682
, 683, 684, 685, 686, 687, 689, 690, 691, 692, 694, 695, 696, 697, 698, 699, 700
, 701, 702, 704, 705, 706, 707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717
, 718, 719, 720, 721, 722, 723, 724, 725, 726, 727, 728, 729, 730, 731, 732, 733
, 734, 735, 736, 737, 738, 739, 740, 741, 742, 743, 744, 745, 746, 747, 748, 749
, 750, 751, 752, 753, 754, 755, 756, 757, 758, 759, 760, 761, 762, 763, 764, 765
, 766, 767, 768, 770, 771, 772, 773, 774, 775, 776, 777, 778, 779, 780, 781, 782
, 783, 784, 785, 786, 787, 789, 790, 791, 792, 793, 794, 795, 796, 797, 798, 799
, 800, 801, 802, 803, 804, 805, 806, 807, 808, 809, 810, 811, 812, 813, 814, 815
, 816, 817, 818, 819, 820, 822, 823, 824, 825, 826, 827, 828, 829, 830, 831, 833
, 834, 835, 837, 838, 839, 840, 841, 842, 843, 844, 845, 846, 847, 848, 849, 850
, 851, 852, 853, 854, 855, 856, 857, 858, 859, 860, 861, 862, 863, 864, 865, 866
, 867, 868, 869, 870, 871, 872, 873, 874, 875, 876, 878, 879, 880, 881, 882, 883
, 884, 885, 886, 888, 889, 890, 891, 892, 893, 894, 895, 896, 897, 898, 899, 900
, 901, 903, 905, 906, 907, 908, 909, 910, 911, 912, 913, 914, 915, 916, 917, 918
, 919, 920, 921, 922, 923, 924, 925, 926, 927, 928, 929, 930, 931, 932, 933, 935
, 936, 937, 938, 939, 940, 941, 942, 943, 944, 945, 946, 947, 948, 949, 950, 951
, 952, 953, 954, 955, 956, 957, 958, 959, 960, 961, 962, 963, 964, 965, 966, 967
, 968, 969, 970, 971, 972, 973, 974, 975, 976, 977, 978, 979, 980, 982, 983, 984
, 985, 986, 988, 989, 991, 992, 993, 994, 995, 996, 997, 998, 999, 1000, 1001, 1
002, 1003, 1004, 1005, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 101
7, 1018, 1019, 1020, 1021, 1022, 1023, 1024, 1025, 1027, 1028, 1029, 1030, 1031,
 1032, 1033, 1034, 1035, 1036, 1037, 1038, 1039, 1040, 1041, 1042, 1043, 1044, 1
045, 1046, 1047, 1048, 1049, 1050, 1051, 1052, 1053, 1054, 1055, 1056, 1057, 105
8, 1059, 1060, 1061, 1062, 1063, 1064, 1065, 1066, 1067, 1068, 1069, 1070, 1071,
 1072, 1073, 1074, 1075, 1076, 1077, 1078, 1079, 1080, 1081, 1082, 1083, 1084, 1
085, 1086, 1087, 1088, 1089, 1090, 1091, 1092, 1093, 1094, 1095, 1096, 1097, 109
8, 1099, 1100, 1101, 1102, 1103, 1104, 1105, 1106, 1107, 1108, 1109, 1110, 1111,
 1112, 1113, 1114, 1115, 1116, 1117, 1118, 1119, 1120, 1121, 1122, 1123, 1124, 1
125, 1126, 1127, 1128, 1129, 1130, 1131, 1132, 1133, 1135, 1136, 1137, 1138, 114
0, 1141, 1142, 1143, 1144, 1145, 1146, 1147, 1148, 1149, 1151, 1152, 1153, 1154,
 1155, 1156, 1157, 1158, 1160, 1161, 1162, 1167, 1168, 1171, 1193, 1195, 1202, 1
209, 1210, 1211, 1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 122
2, 1223, 1225, 1226, 1227, 1228, 1229, 1230, 1231, 1232, 1233, 1234, 1235, 1236,
 1237, 1238, 1239, 1240, 1241, 1242, 1243, 1244, 1245, 1246, 1247, 1248, 1249, 1
250, 1252, 1253, 1254, 1256, 1258, 1259, 1260, 1261, 1262, 1263, 1264, 1265, 126
6, 1267, 1268, 1269, 1270, 1278, 1279, 1280, 1281, 1317, 1318, 1319, 1320, 1321,
 1322, 1437, 1459, 1470, 1479, 1485, 1488, 1543, 1593, 1621, 1659, 1672, 1674, 1
717, 1726, 1859, 1955, 1964, 1968, 2024, 2239, 2251, 2297, 2335, 2337, 2385, 239
0, 2401, 2402, 2412, 2413, 2414, 2430, 2446, 2455, 2468, 6607, 6608, 2486, 4583,
 4551, 5131, 2548, 2766, 2558, 2763, 2561, 2762, 2761, 2760, 2759, 2758, 4571, 6
386, 6527, 2591, 2592, 2595, 2596, 2631, 2632, 6545, 2639, 2640, 6530, 2658, 513
3, 5132, 6484, 6337, 4190, 5134, 2812, 2813, 2818, 6417, 6453, 2855, 2886, 2889,
 2892, 2894, 2910, 2935, 2939, 2942, 2949, 2955, 2979, 2981, 2985, 2990, 2992, 2
993, 2994, 2995, 2996, 3003, 3006, 3011, 3021, 3022, 3024, 3025, 3029, 3031, 303
2, 3034, 3036, 3039, 3040, 3054, 3056, 3058, 3059, 3094, 3097, 3098, 6414, 3102,
 3107, 5139, 6483, 3151, 6482, 6481, 6480, 6405, 6479, 3213, 3214, 3216, 3217, 3
221, 3222, 3224, 3225, 3226, 3228, 3229, 3232, 3234, 3235, 3236, 3238, 3239, 324
4, 3246, 3248, 3249, 3250, 3251, 3252, 3253, 3254, 3256, 3257, 3258, 3283, 3454,
 3302, 3303, 3304, 3305, 3306, 3307, 3308, 3309, 3310, 3311, 3312, 3313, 3314, 3
331, 3332, 3333, 3334, 3360, 3362, 3364, 3366, 3367, 3368, 3373, 3436, 3437, 344
2, 3433, 3444, 3445, 3446, 3447, 3459, 3468, 5155, 5154, 6389, 5185, 5141, 5138,
 3537, 3538, 3539, 4550, 3550, 3551, 3552, 3554, 3556, 6570, 6422, 3629, 6478, 6
469, 6471, 6474, 6475, 6476, 6477, 6627, 6628, 6629, 6632, 6634, 3736, 3748, 375
5, 3860, 3861, 6751, 3892, 3894, 3915, 3947, 3950, 3951, 3964, 4001, 4002, 4004,
 4016, 4020, 4048, 4049, 4050, 4051, 4052, 4053, 4054, 4055, 4056, 4060, 4068, 6
568, 6569, 6452, 6412, 6415, 5130, 4097, 5162, 5142, 5140, 5136, 5128, 4183, 645
0, 6451, 6462, 5188, 5164, 4257, 4266, 4289, 4293, 4301, 4308, 6750, 4348, 6684,
 6683, 6393, 6449, 4402, 6382, 6460, 6410, 6409, 6408, 6461, 6380, 6459, 4459, 4
460, 6756, 6749, 6680, 6378, 4548, 6605, 6604, 6602, 6599, 6598, 6591, 6589, 461
7, 6575, 6743, 4673, 6742, 6421, 6567, 6377, 5135, 6431, 5137, 6376, 4791, 4809,
 6741, 6740, 6739, 6738, 6737, 6736, 6735, 6734, 6733, 6732, 6731, 6730, 6729, 6
728, 6727, 6726, 6725, 6724, 6723, 6722, 6721, 6720, 6719, 6407, 6458, 6457, 645
6, 6396, 6455, 5025, 5037, 5038, 5041, 5063, 5065, 5066, 5067, 5068, 5071, 6406,
 5243, 5254, 6429, 6718, 6717, 6716, 6715, 6711, 6710, 6709, 5286, 5287, 5288, 5
289, 5316, 5325, 5326, 5329, 6562, 6403, 6563, 5392, 5393, 6702, 5403, 6418, 543
7, 6402, 5446, 5457, 6454, 6395, 6639, 5505, 5514, 5518, 5520, 5521, 5522, 5523,
 5532, 5533, 5534, 5535, 5537, 5540, 5543, 5644, 5606, 5620, 5621, 5625, 5645, 5
652, 5656, 5664, 5665, 5667, 5713, 6391, 5703, 6374, 5709, 5711, 6373, 5716, 571
7, 5718, 5724, 5727, 5734, 5749, 6388, 5754, 5758, 6384, 5767, 5772, 5774, 5777,
 5796, 5797, 5798, 5799, 5802, 5807, 5833, 5848, 5858, 5859, 5876, 5877, 5888, 6
520, 6519, 5908, 5928, 4663, 5930, 5931, 5933, 6399, 6020, 6023, 6035, 6058, 606
9, 6070, 6071, 6072, 6092, 6093, 6094, 6095, 6096, 6099, 6101, 6102, 6107, 6110,
 6111, 6112, 6113, 6504, 6080, 6497, 6496, 6493, 6492, 6490, 6487, 6486, 6485, 6
174, 6178, 6184, 6115, 6185, 6196, 6211, 6212, 6242, 6245, 6246, 6247, 6248, 624
9, 6257, 6260, 6261, 6262, 6264, 6265, 6266, 6267, 6268, 6273, 6274, 6275, 6294,
 6300, 5936, 6302, 6303, 6304, 5922, 6305, 5923, 6306, 5924, 6307, 5925, 6308, 6
223, 6309, 6310, 6297, 6311, 6314, 6315, 6317, 6321, 6324, 6326, 6329, 6330, 633
1, 6332, 6333, 6334, 6354, 6355, 6356, 1282, 1551, 1740, 1742, 1743, 1744, 1745,
 1746, 1747, 1748, 1749, 1750, 1751, 1654, 1855, 1960, 1977, 1978, 1980, 1981, 1
982, 1985, 1535, 2056, 1800, 2057, 2059, 2060, 2063, 2055, 2061, 2070, 2058, 206
4, 2065, 2066, 2069, 2074, 2075, 2120, 2121, 2122, 2216, 2218, 2484, 2506, 2507,
 2508, 2594, 2711, 2712, 2713, 2714, 2715, 2716, 2727, 2539, 2540, 2541, 2542, 2
543, 2544, 2545, 2546, 2547, 2549, 2550, 2551, 2552, 2553, 2554, 2555, 2556, 255
7, 2559, 2560, 2562, 2563, 2564, 2565, 2566, 2567, 2568, 2569, 2570, 2571, 2572,
 2578, 2579, 2580, 2581, 2582, 2583, 2584, 2585, 2586, 2587, 2588, 2589, 2590, 2
593, 2598, 2599, 2600, 2601, 2602, 2603, 2609, 2612, 2651, 2652, 2654, 2726, 277
2, 2781, 2773, 2782, 2871, 3033, 2881, 3421, 3417, 3418, 3419, 3420, 3422, 3423,
 3427, 3431, 3424, 3425, 3426, 3288, 3289, 3392, 3451, 1361, 1362, 1365, 1366, 1
367, 1368, 1369, 1370, 1371, 1372, 1373, 3430, 3889, 3890, 4021, 4094, 4098, 284
0, 2843, 2846, 2848, 4123, 1498, 4187, 4167, 4185, 4166, 4186, 4169, 4093, 4168,
 1497, 3526, 4139, 4174, 4175, 3527, 4140, 4179, 3530, 4143, 2845, 4134, 2823, 3
750, 4131, 4132, 4133, 4149, 3532, 4145, 4188, 4176, 4182, 4100, 4095, 4171, 417
7, 4156, 3524, 4137, 4173, 4181, 3525, 4138, 3531, 4144, 3528, 4141, 3529, 4142,
 2937, 4135, 3533, 4146, 4101, 3534, 4147, 1536, 1537, 4170, 4108, 4111, 4114, 4
117, 4105, 4109, 4089, 4165, 4160, 4157, 4154, 4151, 2938, 4136, 4118, 4161, 415
8, 4155, 4152, 4075, 4090, 4162, 4159, 4082, 4153, 4150, 4086, 4083, 4079, 4076,
 4087, 4081, 4077, 4085, 4080, 4148, 4178, 4172, 4088, 4084, 4078, 4164, 4106, 4
112, 4116, 4073, 4107, 4180, 4074, 4110, 2723, 2728, 4129, 4113, 2724, 2729, 413
0, 4115, 4104, 4184, 4163, 4391, 2470, 2471, 2472, 2473, 2474, 2475, 2476, 2477,
 2478, 2479, 2480, 2483, 2485, 2487, 2488, 2492, 2493, 2494, 2495, 2504, 2505, 2
509, 2511, 2512, 2513, 2516, 2519, 2573, 2574, 2575, 2576, 2577, 2867, 2868, 286
9, 2870, 3154, 3155, 3156, 3157, 3158, 3170, 3171, 3172, 4544, 4545, 2514, 2515,
 2517, 2518, 4546, 2510, 3636, 3637, 4218, 3634, 4217, 2850, 4216, 3638, 4219, 3
635, 4666, 4679, 4680, 4682, 3501, 3126, 3475, 2771, 2849, 3494, 3495, 3498, 383
7, 3836, 3838, 4825, 4826, 4827, 4830, 4831, 4832, 4833, 4834, 4836, 4837, 4838,
 4839, 4840, 4841, 4842, 1858, 1860, 1861, 3133, 4745, 3502, 3503, 4744, 5019, 5
020, 5030, 3128, 3130, 3131, 3132, 3193, 3403, 3404, 3405, 3406, 3407, 3408, 340
9, 3410, 3411, 3412, 3413, 4224, 4225, 4226, 4229, 4743, 3476, 3477, 3478, 3479,
 3480, 3481, 3482, 3483, 3484, 3485, 3486, 3487, 3488, 3489, 3490, 3491, 3492, 3
626, 3711, 4742, 3496, 3497, 3602, 4739, 3127, 3129, 3194, 3414, 3415, 4746, 313
4, 3136, 4738, 4748, 4758, 4754, 4756, 4750, 4755, 4385, 4695, 2744, 2745, 2780,
 4381, 4124, 4125, 2717, 2718, 2719, 2720, 2721, 2722, 2732, 2734, 2746, 2777, 2
778, 2779, 2841, 2842, 4122, 4274, 5023, 2747, 2748, 2750, 3667, 3669, 4212, 470
1, 4702, 4747, 5079, 5103, 5104, 4704, 4757, 5081, 4096, 4102, 4103, 4703, 4749,
 5080, 2832, 2835, 2836, 4752, 3857, 4019, 5493, 5529, 5528, 5478, 5579, 3135, 5
581, 5669, 5699, 5700, 5805, 5806, 5872, 5873, 4731, 5116, 5149, 5165, 5166, 535
3, 5159, 5167, 5350, 5156, 5349, 5351, 5352, 5439, 5442, 5443, 5448, 5705, 5740,
 5746, 5751, 6077, 5189, 5190, 5355, 5127, 5354, 5358, 5445, 5447, 4453, 4714, 5
117, 5177, 5712, 5743, 6073, 4429, 4433, 4720, 4723, 5144, 5596, 5692, 5808, 581
0, 6074, 4443, 4716, 5119, 5123, 5179, 5708, 5741, 5747, 5752, 6075, 4406, 4408,
 4410, 4412, 4413, 4414, 4415, 4423, 4434, 4439, 4717, 5120, 5124, 5704, 5719, 5
762, 5763, 5764, 6076, 4401, 4404, 4405, 4407, 4409, 4411, 4416, 4417, 4435, 443
7, 4491, 4492, 4530, 4531, 4532, 4719, 5122, 5126, 5181, 5255, 5256, 5706, 5707,
 5714, 5715, 6079, 5115, 5356, 5357, 5359, 5095, 5102, 5343, 2538, 2731, 2733, 2
735, 2770, 5099, 5191, 5341, 5342, 2062, 2067, 2068, 2071, 2072, 2073, 2076, 207
7, 2078, 2079, 2084, 2085, 2086, 2098, 2099, 2100, 2102, 2103, 2104, 2105, 2106,
 2113, 2149, 2188, 2194, 2227, 2244, 2245, 2825, 5092, 5097, 5689, 5690, 5691, 5
812, 5813, 5814, 6056, 2797, 2798, 2799, 2800, 2801, 2802, 2803, 2804, 2805, 280
6, 2814, 2815, 2816, 2817, 2819, 2820, 2828, 2854, 2856, 2857, 2858, 2859, 2860,
 2861, 2862, 3143, 3144, 3263, 3264, 3677, 3678, 3679, 3680, 3681, 3682, 3683, 3
684, 3685, 3686, 3687, 3688, 3693, 3694, 3695, 3696, 3697, 3698, 3699, 3700, 390
7, 3908, 3916, 3917, 3918, 3919, 3920, 3921, 3922, 3923, 3924, 3925, 3926, 3927,
 4279, 4280, 4281, 4282, 4283, 4284, 4285, 4286, 4287, 4288, 4292, 4294, 4295, 4
300, 4305, 4306, 4307, 4366, 4367, 4456, 4457, 4458, 4461, 4462, 4463, 4464, 446
5, 4466, 4467, 4469, 4470, 4493, 4494, 4495, 4496, 4497, 4498, 4499, 4500, 4501,
 4502, 4503, 4520, 4624, 4646, 4647, 4648, 4656, 4657, 4658, 4659, 4687, 4923, 4
924, 4926, 4927, 4928, 4930, 4931, 4932, 4947, 4948, 4950, 4952, 4968, 4969, 497
0, 4971, 4972, 4973, 4974, 4975, 4976, 4978, 4979, 4980, 5172, 5262, 5416, 6039,
 5263, 5264, 5265, 5266, 5267, 5268, 5269, 5270, 5271, 5273, 5339, 6040, 6043, 5
332, 5383, 5384, 5385, 5399, 5400, 5401, 5402, 5404, 5405, 5406, 5407, 5408, 540
9, 5410, 5411, 5415, 5989, 6042, 3567, 3612, 3689, 3690, 4369, 4370, 4371, 4372,
 4373, 4374, 4375, 4376, 4504, 4521, 4522, 4523, 4524, 4525, 4526, 4528, 4529, 4
688, 5412, 5988, 6044, 2940, 3141, 3642, 3701, 3702, 3703, 3704, 3904, 3906, 390
9, 3910, 3911, 3912, 3913, 3914, 3932, 3933, 3934, 3935, 3936, 3939, 3940, 3941,
 3944, 3945, 3948, 3949, 3952, 3953, 3954, 4239, 4240, 4241, 4510, 4689, 5174, 5
276, 5277, 5278, 5417, 5481, 6050, 3622, 3623, 3645, 3646, 3647, 3691, 3692, 450
5, 4506, 4507, 4587, 4588, 4589, 4590, 4591, 4592, 4593, 4594, 4595, 4596, 4597,
 4598, 4599, 4600, 4601, 4602, 4603, 4604, 4605, 4606, 4607, 4608, 4609, 4610, 4
611, 4612, 4613, 4614, 4615, 4618, 4619, 4620, 4621, 4622, 4623, 4625, 4626, 462
7, 4628, 4629, 4630, 4631, 4632, 4634, 4635, 4690, 5418, 5424, 5426, 5427, 5428,
 5567, 5987, 6052, 6278, 2821, 2824, 4213, 5158, 5163, 5347, 2826, 3100, 4699, 5
082, 5100, 5345, 5346, 5098, 5340, 5344, 5438, 5440, 5441, 5444, 3614, 4230, 423
1, 4232, 4233, 4426, 4427, 4428, 4430, 4431, 4432, 4707, 4989, 4990, 5108, 5650,
 6046, 5105, 5348, 3613, 4454, 4706, 5107, 5173, 5371, 5686, 6045, 6404, 3621, 4
445, 4447, 4711, 5004, 5005, 5006, 5007, 5008, 5009, 5010, 5011, 5012, 5013, 501
4, 5015, 5016, 5017, 5112, 5458, 5459, 5460, 5461, 5462, 5463, 5685, 6051, 6413,
 2852, 4387, 4388, 4389, 4392, 4393, 4394, 4710, 4984, 5111, 5683, 6049, 6411, 2
784, 3125, 4390, 4740, 5094, 5373, 6063, 6375, 2613, 2614, 2615, 2616, 2617, 261
8, 2619, 2620, 2621, 2622, 2623, 2624, 2625, 2626, 2627, 2628, 2629, 2630, 2633,
 2634, 2635, 2636, 2637, 2638, 2641, 2642, 2643, 2644, 2645, 2646, 2647, 2648, 2
649, 2650, 2653, 2725, 3147, 3148, 3149, 3150, 3152, 3153, 3159, 3160, 3161, 316
2, 3163, 3164, 3165, 3166, 3167, 3168, 3169, 3648, 3649, 3650, 3674, 3675, 3676,
 4237, 4238, 4515, 4737, 6065, 6387, 6398, 4448, 4718, 5121, 5125, 5180, 5710, 5
894, 5895, 5896, 5897, 5898, 5899, 5900, 5901, 5902, 5903, 5904, 5905, 5911, 591
5, 5939, 5940, 5941, 5942, 6078, 6120, 6121, 6122, 6123, 6124, 6125, 6126, 6127,
 6128, 6129, 6143, 6144, 6145, 6150, 6151, 6152, 6157, 6158, 6159, 6160, 6161, 6
162, 6163, 6164, 6165, 6166, 6167, 6367, 6529, 6600, 3862, 3864, 4332 )
Function:
Error: 1064 You have an error in your SQL syntax; check the manual that correspo
nds to your MySQL server version for the right syntax to use near 'WHERE old_id
NOT IN ( 2, 3, 6, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,' at line 1 (
localhost)

Backtrace:
GlobalFunctions.php line 604 calls wfBacktrace()
Database.php line 463 calls wfDebugDieBacktrace()
Database.php line 413 calls DatabaseMysql::reportQueryError()
purgeOldText.inc line 40 calls DatabaseMysql::query()
deleteOldRevisions.inc line 55 calls PurgeRedundantText()
deleteOldRevisions.php line 20 calls DeleteOldRevisions()

<!-- Served by  in 1139067083.00 secs. -->
Comment 7 Rob Church 2006-02-04 18:08:13 UTC
Aha, found it. Well, that's a damn embarassing error. Effectively, the first
check on the text table was addressing $tbl_text, which has no value; it should
have been addressing $tbl_txt. It's bizarre that this hasn't shown up in the
tests I ran on it, however. Fixed in CVS HEAD and 1.5 branch. Thanks for that.
Comment 8 Zai Jian 2006-02-05 17:01:33 UTC
Hi,

I've written an ugly script that cleans up the database from old revisions.

It worked for me, hope it'll help you (if needed)...

<?php

//Descritption : Ugly script to remove all old revisions in mediawiki...

// !!! YOU NEED TO PUT THIS SCRIPT SOMEWHERE IT'LL BE USABLE SUCH AS THE ROOT
DIRECTORY OF YOUR SITE !!!
// otherwise you'll get a 403 error

$DBserver         = "DB server";
$DBname           = "DB name";
$DBuser           = "DB user";
$DBpassword       = "DB password";
$DBprefix         = "mediawiki prefix for tables";

$dbUser = $DBprefix . "user";
$dbRevision = $DBprefix . "revision";
$dbText = $DBprefix . "text";
$dbArchive = $DBprefix . "archive";
$dbRecent = $DBprefix . "recentchanges";


//connect to the DB
mysql_connect($DBserver,$DBuser,$DBpassword) or die("Unable to connect to mysql");
@mysql_select_db($DBname) or die( "Unable to select database");

//transform a result of a query into an array usable in another query
function get_in_array($result,$col){
	$arr = "(" ; 
	while ($row = mysql_fetch_assoc($result)) {
		$arr .= "\"" . $row[$col] . "\",";
	}
	$arr{strlen($arr) - 1} = ")";
	print $arr."<br/>";
	return $arr;
}
//delete old elements
function del_old($table, $id, $list){
	if (strlen($list) <= 1){
		return "Nothing old in " . $table . "<br/>";
	}
	$query = "DELETE FROM `" . $table . "` WHERE `" . $id . "` IN " . $list;
	$result = mysql_query($query) or die("request problem in " . $query);
	return $table . ": cleaned up<br/>"; 	
} 

//get the users 
$query = "SELECT DISTINCT `user_name` FROM `" . $dbUser . "`";
$result = mysql_query($query) or die("request problem");
$users = get_in_array($result,"user_name");

//get pages
$query = "SELECT DISTINCT `rev_page` FROM `" . $dbRevision . "` WHERE
`rev_user_text` IN " . $users . " ORDER BY `rev_page`";
$result = mysql_query($query) or die('request problem');

//for each page written by a user get the younger revision id (supposed the
higher rev_id is the younger version)
$rev_ids = "("; 
while ($row = mysql_fetch_assoc($result)) {
	$squery = "SELECT MAX(`rev_id`) AS c1 FROM `" . $dbRevision . "` WHERE
`rev_page` = " . $row["rev_page"];
	$sresult = mysql_query($squery) or die('request problem');
	$max = mysql_fetch_assoc($sresult);		
	$rev_ids .= "\"" . $max["c1"] . "\",";
}
$rev_ids{strlen($rev_ids) - 1} = ")";
print $rev_ids."<br/>";

//get the old revisions
$query = "SELECT * FROM `" . $dbRevision . "` WHERE `rev_id` NOT IN " . $rev_ids
. " AND `rev_user_text` IN " . $users . " ORDER BY `rev_page`";
$result = mysql_query($query) or die('request problem');
$rev_text_ids = get_in_array($result, "rev_text_id");

//cleaning up
echo del_old($dbRevision, "rev_text_id", $rev_text_ids);
echo del_old($dbText, "old_id", $rev_text_ids);
echo del_old($dbRecent, "rc_last_oldid", $rev_text_ids);
echo del_old($dbArchive, "ar_text_id", $rev_text_ids);

//done
echo "done<br/>";
mysql_close();

?>
Comment 9 Sider 2006-02-05 23:13:33 UTC
Rob Church, it seems to work now. I have to thank you. ;-)
Comment 10 Sider 2006-02-05 23:28:10 UTC
I just checked it with MW 1.5beta 3, because i have special features there, and it seems to work 
here too. :-)
Comment 11 Robert Glover 2006-03-02 20:31:34 UTC
There seems to be a bug in the script posted in #8. Most pages were intact, but
I lost at least one template (luckily I had a backup).

I don't believe that rev_id is supposed to be the foreign key for joining to the
"text" table. I believe it's supposed to be rev_text_id --> text.old_id.

Here is the SQL (based on #8) that I used for my own wiki. Of course, if you use
prefixes, then you'll need to adjust to suit. YMMV.

DROP TABLE IF EXISTS temp_newest_rev;
CREATE TABLE temp_newest_rev
AS
        (SELECT MAX(rev_id) AS rev_id
         FROM   revision
         GROUP BY rev_page, rev_user);

DROP TABLE IF EXISTS temp_newest_text;
CREATE TABLE temp_newest_text
AS
        (SELECT MAX(rev_text_id) AS rev_text_id
         FROM   revision
         GROUP BY rev_page, rev_user);

DELETE
FROM    text
WHERE   old_id NOT IN   (SELECT rev_text_id
                         FROM   temp_newest_text);

DELETE
FROM    revision
WHERE   rev_id NOT IN   (SELECT rev_id
                         FROM   temp_newest_rev);

DELETE
FROM    recentchanges
WHERE   rc_id NOT IN    (SELECT rev_id
                         FROM   temp_newest_rev);

DROP TABLE temp_newest_rev;
DROP TABLE temp_newest_text;
Comment 12 Sider 2006-03-03 09:30:12 UTC
deleteOldRevisions maintenance script is working now, included in MediaWiki CVS
and 1.5 branch. Use this one instead and you wont have errors.
Comment 13 Uwe Scheffel 2006-05-23 07:42:38 UTC
I get this. Any ideas?

...
1149, 1551150, 1551151, 1551152, 1551153, 1551154, 1551155, 1551156, 1551157,
1551158, 1551159, 1551160, 1551161, 1551162, 1551163, 1551164 )
Function: 
Error: 1153 Got a packet bigger than 'max_allowed_packet' (www.myserver.com)

Backtrace:
GlobalFunctions.php line 602 calls wfbacktrace()
Database.php line 473 calls wfdebugdiebacktrace()
Database.php line 419 calls databasemysql::reportqueryerror()
purgeOldText.inc line 40 calls databasemysql::query()
purgeOldText.php line 20 calls purgeredundanttext()
Comment 14 Sider 2006-05-23 11:06:07 UTC
This is not a problem of MW, but of your MySQL Server. Check this:
http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html
Comment 15 Marc 2006-06-30 07:53:09 UTC
This works with mysql 5; but dont work with mysql 4.

DROP TABLE IF EXISTS temp_newest_rev;
CREATE TABLE temp_newest_rev
AS
(SELECT MAX(rev_id) AS rev_id
FROM revision
GROUP BY rev_page, rev_user);

DROP TABLE IF EXISTS temp_newest_text;
CREATE TABLE temp_newest_text
AS
(SELECT MAX(rev_text_id) AS rev_text_id
FROM revision
GROUP BY rev_page, rev_user);

DELETE
FROM text
WHERE old_id NOT IN (SELECT rev_text_id
FROM temp_newest_text);

DELETE
FROM revision
WHERE rev_id NOT IN (SELECT rev_id
FROM temp_newest_rev);

DELETE
FROM recentchanges
WHERE rc_id NOT IN (SELECT rev_id
FROM temp_newest_rev);

DROP TABLE temp_newest_rev;
DROP TABLE temp_newest_text;

What can I do ?

thanks.

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links