Page MenuHomePhabricator

Query failed: ERROR: syntax error at or near "smw_tmp_unusedprops" LINE 1: ...iesSpecial Mioze7Ae */ OR REPLACE FUNCTION create_"smw_tmp_u... ^
Closed, ResolvedPublic

Description

Author: anton.kochkov

Description:
Error on Special:CreateTemplate page

Warning: pg_query() function.pg-query: Query failed: ERROR: syntax error at or near "smw_tmp_unusedprops"
LINE 1: ...iesSpecial Mioze7Ae */ OR REPLACE FUNCTION create_"smw_tmp_u...

^ in /www/includes/db/DatabasePostgres.php on line 254

Version of MediaWiki: 1.19alpha
SemanticMediaWiki: Version 1.7.1 alpha3

http://droid-developers.org/wiki/Special:Version


Version: unspecified
Severity: normal

Details

Reference
bz34855

Event Timeline

bzimport raised the priority of this task from to Needs Triage.Nov 22 2014, 12:15 AM
bzimport set Reference to bz34855.
bzimport added a subscriber: Unknown Object (MLST).

andrew wrote:

Fix up the table name.

Attached:

andrew wrote:

The Postgres logs have:

ser=wikiuser,db=wikidb ERROR: syntax error at or near ""smw_tmp_unusedprops"" at character 89
ser=wikiuser,db=wikidb STATEMENT: CREATE /* SMWSQLStore2::getUnusedPropertiesSpecial Andrew */ OR REPLACE FUNCTION create_"smw_tmp_unusedprops"() RETURNS void AS $$ BEGIN IF EXISTS(SELECT NULL FROM pg_tables WHERE tablename='"smw_tmp_unusedprops"' AND schemaname = ANY (current_schemas(true))) THEN DELETE FROM "smw_tmp_unusedprops"; ELSE CREATE TEMPORARY TABLE "smw_tmp_unusedprops" ( title text ); END IF; END; $$ LANGUAGE 'plpgsql'; SELECT create_"smw_tmp_unusedprops"();

The patch I've attached resolves this issue.

sumanah wrote:

Adding keywords to signal that this patch awaits review. Thank you for the patch, Andrew.

anton.kochkov wrote:

After applied patch i can see:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at end of input LINE 1: ...N "smw_ids" ON p_id=smw_id AND title=smw_title AND smw_iw='' ^ in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 254

Warning: pg_query() [function.pg-query]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block in /home/droid-dev/www/includes/db/DatabasePostgres.php on line 254

anton.kochkov wrote:

forgot this line too:

Warning: Invalid argument supplied for foreach() in /home/droid-dev/www/includes/resourceloader/ResourceLoaderWikiModule.php on line 201

sumanah wrote:

Marking patch as reviewed.

(In reply to comment #6)

Marking patch as reviewed.

Wrong bug/patch??

(In reply to comment #2)

The patch I've attached resolves this issue.

Thanks for the patch. I have some concerns with it though, and only applied a part of it in r113147. Can you tell me if this fixes the issue?

anton.kochkov wrote:

(In reply to comment #8)

(In reply to comment #2)

The patch I've attached resolves this issue.

Thanks for the patch. I have some concerns with it though, and only applied a
part of it in r113147. Can you tell me if this fixes the issue?

See comment #4 - patch applied solves issue only partially - i have now another error.

Ok, so the original issue has been fixed - great.

I don't know how to fix the error reported in comment #4, it does not give me enough info.

andrew wrote:

The error in comment #4 was fixed in r111256.

Jeroen, did you test r113147 against PostgreSQL? It doesn't work for me. The error from Pg is:

user=wikiuser,db=wikidb ERROR: column "smw_tmp_unusedprops" does not exist at character 59
user=wikiuser,db=wikidb QUERY: SELECT EXISTS(SELECT NULL FROM pg_tables WHERE tablename="smw_tmp_unusedprops" AND schemaname = ANY (current_schemas(true)))

user=wikiuser,db=wikidb CONTEXT: PL/pgSQL function "create_smw_tmp_unusedprops" line 1 at IF
user=wikiuser,db=wikidb STATEMENT: CREATE /* SMWSQLStore2::getUnusedPropertiesSpecial Andrew */ OR REPLACE FUNCTION create_smw_tmp_unusedprops() RETURNS void AS $$ BEGIN IF EXISTS(SELECT NULL FROM pg_tables WHERE tablename="smw_tmp_unusedprops" AND schemaname = ANY (current_schemas(true))) THEN DELETE FROM "smw_tmp_unusedprops"; ELSE CREATE TEMPORARY TABLE "smw_tmp_unusedprops" ( title text ); END IF; END; $$ LANGUAGE 'plpgsql'; SELECT create_smw_tmp_unusedprops();

This is because single quotes and double quotes have different meanings in PostgreSQL. In this case smw_tmp_unusedprops needs to have single quotes around it to refer to be treated as a string, double quotes means it is a column. The patch as I supplied does the right thing "TM". :)

I'd suggest that the error in comment #5 is an unrelated error.

I committed r114764 which should fix the error above. I will fix other bugs as I run into them, as I've just started using SMW.

P.S. Any plans to move SMW* to git, or should I assume svn access for a while?

(In reply to comment #12)
...

P.S. Any plans to move SMW* to git, or should I assume svn access for a while?

SMW and most extensions should be in git *very* soon (maybe they already are while I am writing this).

plewe wrote:

This is an incomplete patch, because the same error also occurs in getCreateTempIDTableSQL() which has the same PostgreSQL temporary table workaround. The problem is the quotes, as added in the Database tableName function (this is new in MW 1.19). Since I assume the quotes were added for a good reason (but in keeping with SQL standard, shouldn't they be single quotes?), I just took them back out at the beginning of the getCreateTempIDTableSQL() function:

$tablename = str_replace(array('"',"'"),"",$tablename);

I know that's a clumsy fix, but it got my hierarchical query working. Hope someone can find a more elegant solution.