Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL error in tour 2.9 #663

Closed
e-dschungel opened this Issue May 2, 2017 · 11 comments

Comments

Projects
None yet
6 participants
@e-dschungel
Copy link

e-dschungel commented May 2, 2017

After upgrading my 2.8.6 to 2.9.0 I took the tour. On the page admin.php?page=batch_manager&filter=prefilter-duplicates-checksum I got the following error so I can't complete the tour or visit another admin page.

PHP 5.6
MySQL 5.7

Warning:  [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
    AND (
      ic.category_id != i.storage_category_id
      OR i.storage_cat' at line 5

SELECT
    DISTINCT(category_id) AS id
  FROM piwigo_image_category AS ic
    JOIN piwigo_images AS i ON i.id = ic.image_id
  WHERE ic.image_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,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,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,1 in /xxx/include/dblayer/functions_mysqli.inc.php on line 845


Fatal error: Call to a member function fetch_assoc() on boolean in /xxx/include/dblayer/functions_mysqli.inc.php on line 893
@plegall

This comment has been minimized.

Copy link
Member

plegall commented May 4, 2017

Hi @e-dschungel

can you edit file admin/batch_manager_global.php line 549, after:

if (count($page['cat_elements_id']) > 0)
{

insert

echo '<pre>'; print_r($page['cat_elements_id']); echo '</pre>';

and go again on admin.php?page=batch_manager&filter=prefilter-duplicates-checksum to tell us what this print_r outputs? I think an id is null or empty, which breaks the query.

@chadwik

This comment has been minimized.

Copy link

chadwik commented May 4, 2017

Hi,
I also have this error after upgrading to 2.9.0 and following the tour. I have attached the output you asked for.
output.txt
I did have some errors while upgrading. This is the error log:
error.log.txt

@plegall

This comment has been minimized.

Copy link
Member

plegall commented May 4, 2017

@chadwik thank you for these details. Very useful! Your error log shows that the upgrade was not successful at all. Please use the manual procedure, ie download Piwigo 2.9.0, unzip it and overwrite your Piwigo with files from Piwigo 2.9.0.

@chadwik

This comment has been minimized.

Copy link

chadwik commented May 4, 2017

Hi, thanks for your help. I fixed my file permissions and followed the instructions for manually upgrading. Copying the files went ok but the db upgrade reported 'No upgrade required, the database structure is up to date'. The page '/piwigo/admin.php?page=batch_manager&filter=prefilter-duplicates-checksum' is still throwing the same error msg:
error_msg.txt

@e-dschungel

This comment has been minimized.

Copy link
Author

e-dschungel commented May 4, 2017

Hi,
@plegall I also did the automatic update to 2.9.0. My output is here: output.txt
I don't think it is an upgrade problem. I did a little debugging:
The string returned in

$array_of_ids_string = query2array($query, null, 'ids');
has an ',' at the end. This leads to an empty last element in this line
$ids = array_merge($ids, explode(',', $ids_string));

Something like $ids_string = rtrim($ids_string,", "); added before line 422 quick-fixes it but a better solution should be found.

@jdeluyck

This comment has been minimized.

Copy link

jdeluyck commented May 11, 2017

I have the same error. Upgrade was done through the web interface.

Warning:  [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
    AND (
      ic.category_id != i.storage_category_id
      OR i.storage_cat' at line 5

SELECT
    DISTINCT(category_id) AS id
  FROM piwigo_image_category AS ic
    JOIN piwigo_images AS i ON i.id = ic.image_id
  WHERE ic.image_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,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,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,1 in /srv/data/web/vhosts/sadevil.org/htdocs/piwigo/include/dblayer/functions_mysqli.inc.php on line 845


Fatal error: Call to a member function fetch_assoc() on boolean in /srv/data/web/vhosts/sadevil.org/htdocs/piwigo/include/dblayer/functions_mysqli.inc.php on line 893

No errors were reported during the upgrade.

@plegall

This comment has been minimized.

Copy link
Member

plegall commented May 12, 2017

I need someone among you to send me a mysql dump of his/her database if (s)he meets this issue. I need to investigate deeper to understand what the problem is. Don't worry, I can't do anything with hash passwords. Send it to "plg" /at/ "piwigo.org"

plegall added a commit that referenced this issue May 15, 2017

(cp 686c2f7) fix #663, batch manager, avoid SQL error with duplicates
* when searching duplicates on md5sum, only consider md5sum not null
* in case the GROUP_CONCAT returns on truncated string, remove the trailing ","
* add a TODO to find a better algorithm, avoiding the GROUP_CONCAT limit to 1024 chars

@plegall plegall closed this in 686c2f7 May 15, 2017

@plegall plegall self-assigned this May 19, 2017

@plegall plegall added this to the 2.9.1 milestone May 19, 2017

@plegall plegall added the Type: Bug label May 19, 2017

@V-Z

This comment has been minimized.

Copy link

V-Z commented Jun 9, 2017

I have the same issue. Upgrade was done through web interface (without error). I have Apache 2.4, PHP 5.6.30 and MySQL 14.14 Distrib 5.6.36.

After suggested insertion into admin/batch_manager_global.php I see:

Array
(
    [0] => 1
    [1] => 2
    [2] => 3
    [3] => 4
    [4] => 5
    [5] => 6
    [6] => 7
    [7] => 8
    [8] => 9
    [9] => 10
    [10] => 11
    [11] => 12
    [12] => 13
    [13] => 14
    [14] => 15
    [15] => 16
    [16] => 17
    [17] => 18
    [18] => 19
    [19] => 20
    [20] => 21
    [21] => 22
    [22] => 23
    [23] => 24
    [24] => 25
    [25] => 26
    [26] => 27
    [27] => 28
    [28] => 29
    [29] => 30
    [30] => 31
    [31] => 32
    [32] => 33
    [33] => 34
    [34] => 35
    [35] => 36
    [36] => 37
    [37] => 38
    [38] => 39
    [39] => 40
    [40] => 41
    [41] => 42
    [42] => 43
    [43] => 44
    [44] => 45
    [45] => 46
    [46] => 47
    [47] => 48
    [48] => 49
    [49] => 50
    [50] => 51
    [51] => 52
    [52] => 53
    [53] => 54
    [54] => 55
    [55] => 56
    [56] => 57
    [57] => 58
    [58] => 59
    [59] => 60
    [60] => 61
    [61] => 62
    [62] => 63
    [63] => 64
    [64] => 65
    [65] => 66
    [66] => 67
    [67] => 68
    [68] => 69
    [69] => 70
    [70] => 71
    [71] => 72
    [72] => 73
    [73] => 74
    [74] => 75
    [75] => 76
    [76] => 77
    [77] => 78
    [78] => 79
    [79] => 80
    [80] => 81
    [81] => 82
    [82] => 83
    [83] => 84
    [84] => 85
    [85] => 86
    [86] => 87
    [87] => 88
    [88] => 89
    [89] => 90
    [90] => 91
    [91] => 92
    [92] => 93
    [93] => 94
    [94] => 95
    [95] => 96
    [96] => 97
    [97] => 98
    [98] => 99
    [99] => 100
    [100] => 101
    [101] => 102
    [102] => 103
    [103] => 104
    [104] => 105
    [105] => 106
    [106] => 107
    [107] => 108
    [108] => 109
    [109] => 110
    [110] => 111
    [111] => 112
    [112] => 113
    [113] => 114
    [114] => 115
    [115] => 116
    [116] => 117
    [117] => 118
    [118] => 119
    [119] => 120
    [120] => 121
    [121] => 122
    [122] => 123
    [123] => 124
    [124] => 125
    [125] => 126
    [126] => 127
    [127] => 128
    [128] => 129
    [129] => 130
    [130] => 131
    [131] => 132
    [132] => 133
    [133] => 134
    [134] => 135
    [135] => 136
    [136] => 137
    [137] => 138
    [138] => 139
    [139] => 140
    [140] => 141
    [141] => 142
    [142] => 143
    [143] => 144
    [144] => 145
    [145] => 146
    [146] => 147
    [147] => 148
    [148] => 149
    [149] => 150
    [150] => 151
    [151] => 152
    [152] => 153
    [153] => 154
    [154] => 155
    [155] => 156
    [156] => 157
    [157] => 158
    [158] => 159
    [159] => 160
    [160] => 161
    [161] => 162
    [162] => 163
    [163] => 164
    [164] => 165
    [165] => 166
    [166] => 167
    [167] => 168
    [168] => 169
    [169] => 170
    [170] => 171
    [171] => 172
    [172] => 173
    [173] => 174
    [174] => 175
    [175] => 176
    [176] => 177
    [177] => 178
    [178] => 179
    [179] => 180
    [180] => 181
    [181] => 182
    [182] => 183
    [183] => 184
    [184] => 185
    [185] => 186
    [186] => 187
    [187] => 188
    [188] => 189
    [189] => 190
    [190] => 191
    [191] => 192
    [192] => 193
    [193] => 194
    [194] => 195
    [195] => 196
    [196] => 197
    [197] => 198
    [198] => 199
    [199] => 200
    [200] => 201
    [201] => 202
    [202] => 203
    [203] => 204
    [204] => 205
    [205] => 206
    [206] => 207
    [207] => 208
    [208] => 209
    [209] => 210
    [210] => 211
    [211] => 212
    [212] => 213
    [213] => 214
    [214] => 215
    [215] => 216
    [216] => 217
    [217] => 218
    [218] => 219
    [219] => 220
    [220] => 221
    [221] => 222
    [222] => 223
    [223] => 224
    [224] => 225
    [225] => 226
    [226] => 227
    [227] => 228
    [228] => 229
    [229] => 230
    [230] => 231
    [231] => 232
    [232] => 233
    [233] => 234
    [234] => 235
    [235] => 236
    [236] => 237
    [237] => 238
    [238] => 239
    [239] => 240
    [240] => 241
    [241] => 242
    [242] => 243
    [243] => 244
    [244] => 245
    [245] => 246
    [246] => 247
    [247] => 248
    [248] => 249
    [249] => 250
    [250] => 251
    [251] => 252
    [252] => 253
    [253] => 254
    [254] => 255
    [255] => 256
    [256] => 257
    [257] => 258
    [258] => 259
    [259] => 260
    [260] => 261
    [261] => 262
    [262] => 263
    [263] => 264
    [264] => 265
    [265] => 266
    [266] => 267
    [267] => 268
    [268] => 269
    [269] => 270
    [270] => 271
    [271] => 272
    [272] => 273
    [273] => 274
    [274] => 275
    [275] => 276
    [276] => 277
    [277] => 278
    [278] => 279
    [279] => 280
    [280] => 281
    [281] => 282
    [282] => 283
    [283] => 
)
Warning:  [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
    AND (
      ic.category_id != i.storage_category_id
      OR i.storage_cat' at line 5
SELECT
    DISTINCT(category_id) AS id
  FROM image_category AS ic
    JOIN images AS i ON i.id = ic.image_id
  WHERE ic.image_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,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,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188 in /.../include/dblayer/functions_mysqli.inc.php on line 845

Fatal error: Call to a member function fetch_assoc() on boolean in /.../include/dblayer/functions_mysqli.inc.php on line 893

@plegall, do You still need the database? Hot-fix solution suggested by @e-dschungel fixes the issue.

@flop25

This comment has been minimized.

Copy link
Member

flop25 commented Jun 9, 2017

don't use the debug code suggested but apply the official fix 686c2f7

@V-Z

This comment has been minimized.

Copy link

V-Z commented Jun 9, 2017

Done, it works fine.

@flop25

This comment has been minimized.

Copy link
Member

flop25 commented Jun 9, 2017

thank you

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.