Unknown column 'i.user_id' in 'on clause'

This error occur when used 4images v1.7 / 1.7.1 (and/or some old mods) and MySQL v5.x
It caused by the changes in MySQL v5 handling multiple tables on one query.
If your 4images is newer then v1.7.1 and you still get these errors, then you must have installed a mod that is not MySQL v5 compatible.

There are three ways fix the problem:
1) Downgrade MySQL to v3 or v4
2) Upgrade your 4images
3) In .php files replace all
FROM table1 ref, table2 ref

with:
FROM (table1 ref, table2 ref)

There are quiet a few places needs to be fixed, so I'd recommend use a text editor which allows use unix regular expressions to search and modify files.

This REGEX string I've used in UltraEdit:

Code: [Select] [Expand] [Hide line numbers]

1	`(FROM\s+)("\.[A-Z_]+\."\s+[a-z]+,\s*"\.[A-Z_]+\."\s+[a-z]+)`

and in replacing string used:

Code: [Select] [Expand] [Hide line numbers]

1 \1(\2)

it worked quiet well. That editor allows search and replace string in files without opening them in the editor, which would be the fastest way, however I'd strongly recommend do a complete backup of all .php files before you attempt doing it.

Quote from: dawolf on November 18, 2008, 12:29:12 PM

I corrected this problem by running a perl command from the shell in the 4images root dir, the \admin dir and the \includes dir:

Code: [Select] [Expand] [Hide line numbers]

1	`perl -pi '-es/(FROM\s+)("\.[A-Z_]+\."\s+[a-z]+,\s"\.[A-Z_]+\."\s+[a-z]+)/$1($2)/g' .php`

This might be useful for people without access to Ultraedit.
Remember to BACK UP everything before you try this!

If you don't have such editor or for any other reasons can't use that REGEX, prepare yourself for a long journey modifying almost all 4images files manually.
The list below is only for non-modified 4images v1.7 / 1.7.1 That means if after fixing all these files you still getting errors there are only two reasons:
1) you missed a modification
2) you've installed a mod that also not MySQL v5 compatible.

In any case you'll need find and fix the problem yourself.

Hopefully after looking at the list below you'll understand what has to be changed.

Good luck.

Find in categories.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)`

Find in details.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)`

Find in index.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)`

Find in lightbox.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)`

Find in member.php (4 times):

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".COMMENTS_TABLE." c, ".IMAGES_TABLE." i`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".COMMENTS_TABLE." c, ".IMAGES_TABLE." i)`

Find in postcards.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)`

Find in postcards.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i)`

Find in search.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m)`

Find in search.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)`

Find in top.php (4 times):

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)`

Find in admin\comments.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".COMMENTS_TABLE." c, ".IMAGES_TABLE." i`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".COMMENTS_TABLE." c, ".IMAGES_TABLE." i)`

Find in admin\home.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".USERS_TABLE." u, ".SESSIONS_TABLE." s`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".USERS_TABLE." u, ".SESSIONS_TABLE." s)`

Find in admin\usergroups.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gm`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gm)`

Find in includes\auth.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m)`

Find in includes\functions.php (2 times):

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)`

Find in includes\sessions.php:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM ".USERS_TABLE." u, ".LIGHTBOXES_TABLE." l`

Replace with:

Code: [Select] [Expand] [Hide line numbers]

1	`FROM (".USERS_TABLE." u, ".LIGHTBOXES_TABLE." l)`