I get this error message when I for anything in attachments. The thing is it is obviously the wrong table it should be mimo-project.internal_upload

user warning: Table 'mimo-project.upload' doesn't exist query: SELECT SUM(i.score * t.count) AS score FROM internal_search_index i INNER JOIN internal_search_total t ON i.word = t.word INNER JOIN internal_files AS f ON f.fid = i.sid LEFT JOIN upload u USING (fid) LEFT JOIN node n USING (nid) WHERE f.status = 1 AND (i.word = 'test') AND i.type = 'file' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 0, 1 in /var/www/vhosts/mimo-project.eu/httpdocs/modules/search/search.module on line 946.

Comments

mokko’s picture

mokko commented

I changed search_files_attachment_module line 118
$find=do_search ...

and put "upload u" and "node n" in curly brackets without really knowing what I am doing, but it seems to work.

Anonymous’s picture

Anonymous (not verified) commented
Priority: Normal » Critical

I have the same problem here. I changed the priority to 'critical' because this seems to break the whole module.

Unfortunately, your solution is not working for me and I really don't know, why it is working in your system :-)

As far as I understand SQL, this should be something like {upload} AS u USING (fid) LEFT JOIN {node} AS n

But this may be some shortcut or a special drupal thing, so I don't know, what is the correct statement here.

For your interest: the curly bracket seem to be responsible for adding the table prefix at the beginning of the given table name. In your case, {upload} is tramsformed to 'internal_upload'.

mokko’s picture

mokko commented

Thanks for your explanation. That's what I assumed mostly without knowing. I just tried until it worked...

I post the same piece of code again (as above, just more complete) that works for me to exclude any possibility of misunderstanding:

// Do search.
$find = do_search($keys, 'file', 'INNER JOIN {files} AS f ON f.fid = i.sid LEFT JOIN {upload u} USING (fid) LEFT JOIN {node n} USING (nid)'. $join1, $conditions1 . (empty($where1) ? '' : ' AND '. $where1), $arguments1, $select2, $join2, $arguments2);

Feels a bit silly to use this little thing to prepare my first patch...

I would guess that the developer didn't test it on a system which uses prefixed table names.

What the u and n mean I don't know, but I assume it's some kind of variable.

Do you have any idea where this drupal/sql stuff is documented? I googled and found this. Is a bit hard to understand for me. I guess it is finally time to remember my php... http://api.drupal.org/api/drupal/includes--database.inc/6/source

Anonymous’s picture

Anonymous (not verified) commented

I found the following in the mySQL manual:

table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name

That means, that you have to put 'u' and 'n' outside the curly brackets and they are used, as I equivalent to my explanation in #2.

The JOIN syntax in mySQL 5 can be found at http://dev.mysql.com/doc/refman/5.0/en/join.html

jrglasgow’s picture

jrglasgow commented

the curly braces {} are to alias the prefixed table names, so you are correct in that regard, the reason for the u and n aliases is so later on in the query you don't have to keep referring to {upload} this and {node} that, you can just reference u and n.

I have made some changes in the code:

$find = do_search($keys, 'file', 'INNER JOIN {files} AS f ON f.fid = i.sid LEFT JOIN {upload} u USING (fid) LEFT JOIN {node} n USING (nid)'. $join1, $conditions1 . (empty($where1) ? '' : ' AND '. $where1), $arguments1, $select2, $join2, $arguments2);

The changes have been commited, try out the next dev version and let me know if that does the trick.

lucascaro’s picture

lucascaro commented

Hi, I had the same problem, got this message:
user warning: Table 'upload' doesn't exist query: SELECT SUM(i.score * t.count) AS score FROM drp_search_index i INNER JOIN drp_search_total t ON i.word = t.word INNER JOIN drp_files AS f ON f.fid = i.sid LEFT JOIN upload u USING (fid) LEFT JOIN node n USING (nid) WHERE f.status = 1 AND (i.word = 'great') AND i.type = 'file' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 0, 1 in modules/search/search.module on line 946.

and changed upload to {upload} and node to {node} on that line and it worked.

duncanc’s picture

duncanc commented

The underlying problem is in search_files_attachments.module, line 118 in version 6.x-2.0-beta4

      $find = do_search($keys, 'file', 'INNER JOIN {files} AS f ON f.fid = i.sid LEFT JOIN upload u USING (fid) LEFT JOIN node n USING (nid)'. $join1, $conditions1 . (empty($where1) ? '' : ' AND '. $where1), $arguments1, $select2, $join2, $arguments2);

There seems to be similar problems in these files:

search_files.install line 69

	  if (!db_result(db_query("SELECT COUNT(*) FROM pg_proc WHERE proname = 'concat' AND pronargs = 3"))) {

search_files.module line 528

$result = db_query("SELECT data FROM search_dataset WHERE type = '%s'", $type);
lucascaro’s picture

lucascaro commented

FWIW, this seems fixed in 6.x-2.x-dev except for http://drupalcode.org/project/search_files.git/blob?f=search_files.insta...