SQL error on environment upgrade (postgresql)

[Oliver Metz]

database-backend: postgresql

  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
psycopg2.ProgrammingError: ERROR:  Syntaxerror near »NOT«
LINE 2:             CREATE TABLE IF NOT EXISTS watchlist (

[Martin Scharrer]

I checked this now and after the ​postgres documentation I found the CREATE TABLE IF NOT EXISTS syntax isn't supported by postgres. I'm quite new to SQL and thought it would be a standard feature.

I plan anyway to change the table creation soon to the API trac profides. This should help. In the meantime I just remove the IF NOT EXISTS which isn't really necessary anyway.

[Martin Scharrer]

(In [4823]) plugin.py:: Removed IF NOT EXISTS from table creation SQL command because it

doesn't work with PostgresSQL. See #4097.

[Oliver Metz]

with newest version I get:

# trac-admin /var/www/trac upgrade --no-backup
Traceback (most recent call last):
  File "/usr/bin/trac-admin", line 8, in <module>
    load_entry_point('Trac==0.11stable-r7667', 'console_scripts', 'trac-admin')()
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/admin/console.py", line 1294, in run
    return admin.onecmd(command)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/admin/console.py", line 123, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib/python2.5/cmd.py", line 219, in onecmd
    return func(arg)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/admin/console.py", line 1144, in do_upgrade
    self.__env.upgrade(backup=do_backup)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/env.py", line 449, in upgrade
    participant.upgrade_environment(db)
  File "build/bdist.linux-i686/egg/tracwatchlist/plugin.py", line 338, in upgrade_environment
  File "build/bdist.linux-i686/egg/tracwatchlist/plugin.py", line 319, in _create_db_table
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
psycopg2.ProgrammingError: FEHLER:  Syntaxfehler bei »user«
LINE 3:                 user  text,

I dropped old watchlist table.

Some errors I found:

• It seems to me that user is not allowed as column name.
• You have to put all columns in Group By clause, e.g.
  • "GROUP BY id,type,time,changetime,summary,reporter ORDER BY changetime DESC;" % { 'user':user, 'realm':'ticket' }
• ProgrammingError: ERROR: Operator doesn't exist: integer = text (line 160)
  • I think this IN doesn't work because id is text in watchlist table
  • If id is changed to int:

      File "build/bdist.linux-i686/egg/tracwatchlist/plugin.py", line 252, in is_watching
        % (realm, id, user)
    ...
    DataError: ERROR:  ungültige Eingabesyntax für ganze Zahl: »WikiStart«
    

Sorry for german language error but db is not on my server so I can't change...

[Martin Scharrer]

(In [4826]) Renamed DB table columns from 'user','id' to 'wluser','resid' because of problems with postgressql backend. See #4097.

[Martin Scharrer]

I change the DB table according to your suggestions. Please try the latest revision. I hacked some column rename SQL code in but if it doesn't work you need to drop the old watchlist table again. Sorry for that.

PS: The German error messages are fine with me, I'm German, too.

[Oliver Metz]

Okay. Then I have another german error for you. ;-) This is related to my previous comment on "IN".

2008-11-16 16:15:09,059 Trac[main] ERROR: FEHLER:  Operator existiert nicht: integer = text
HINT:  Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche
Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/web/main.py", line 432, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/web/main.py", line 204, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-i686/egg/tracwatchlist/plugin.py", line 160, in process_request
    "GROUP BY id,type,time,changetime,summary,reporter ORDER BY changetime DESC;" % { 'user':user, 'realm':'ticket' }
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
ProgrammingError: FEHLER:  Operator existiert nicht: integer = text
HINT:  Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche

And You fixed only one GROUP BY but there are at least two.

[Martin Scharrer]

I'm trying to build in a cast to integer in the SQL query.

About the GROUP BY: I can't GROUP BY anything else then 'name' because I have the MAX(version) in the query, otherwise I get all wiki versions. Maybe you know how to fix it.

[Martin Scharrer]

Could you try the following patch for the 'integer = text' problem:

tracwatchlist/plugin.py

@@ -156 +156 @@
                 ticketlist = []
                 cursor.execute(
                     "SELECT id,type,time,changetime,summary,reporter FROM ticket WHERE id IN "
-                    "(SELECT resid FROM watchlist WHERE wluser='%s' AND realm='ticket') "
+                    "(SELECT CAST(resid AS integer) FROM watchlist WHERE wluser='%s' AND realm='ticket') "
                     "GROUP BY id,type,time,changetime,summary,reporter "
                     "ORDER BY changetime DESC;" % user )
                 tickets = cursor.fetchall()

[Martin Scharrer]

I checked the patch now into subversion.

Could you please confirm that the last changes are also fixing #4099?

[Oliver Metz]

I tried newest version and got

# trac-admin /var/www/trac upgrade --no-backup

Traceback (most recent call last):
  File "/usr/bin/trac-admin", line 8, in <module>
    load_entry_point('Trac==0.11stable-r7667', 'console_scripts', 'trac-admin')()
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/admin/console.py", line 1294, in run
    return admin.onecmd(command)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/admin/console.py", line 123, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib/python2.5/cmd.py", line 219, in onecmd
    return func(arg)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/admin/console.py", line 1146, in do_upgrade
    msg = unicode(e)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7667-py2.5.egg/trac/core.py", line 42, in __unicode__
    return unicode(self.message)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc2 in position 60: ordinal not in range(128)

2008-11-26 23:59:46,734 Trac[env] WARNING: Component <tracwatchlist.plugin.WatchlinkPlugin object at 0x8a32fac> requires environment upgrade
2008-11-26 23:59:46,740 Trac[init] DEBUG: SELECT value FROM system WHERE name='downloads_version'
2008-11-26 23:59:46,747 Trac[init] DEBUG: SELECT value FROM system WHERE name='screenshots_version'
2008-11-26 23:59:46,751 Trac[plugin] DEBUG: Updating DB table.

I don't want to spend more time on hacking around without knowing what to do. So I can't provide further information... Perhaps this error occurs because the table exists and your

[Martin Scharrer]

Mmm, this last error seems no be unicode related. You have non-ascii chars in your username, haven't you? I will check for some missing conversion.

If I find the time I will set up a PostgresSQL backend and test the plugin with it.

Could you repost your unfinished comment:

Perhaps this error occurs because the table exists and your

[Oliver Metz]

Sorry, messed it up last time. This error didn't occur when I deleted the table before installing the plugin only on DB update. But i don't want to hack the code again. I looked over my 314 users but I couldn't find anything. Some time ago I had someone with the greek letter "my" but I deleted him out of the user list. I'm searchin for 0x2c (dec: 194)?

[Martin Scharrer]

I now [5314] replaced all str() calls with unicode(). This might fix your issue reported in comment:10. Please verify.

[Oliver Metz]

Tried newest version:

# trac-admin /var/www/trac upgrade --no-backup
Traceback (most recent call last):
  File "/usr/bin/trac-admin", line 8, in <module>
    load_entry_point('Trac==0.11stable-r7923', 'console_scripts', 'trac-admin')()
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7923-py2.5.egg/trac/admin/console.py", line 1317, in run
    return admin.onecmd(command)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7923-py2.5.egg/trac/admin/console.py", line 133, in onecmd
    rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib/python2.5/cmd.py", line 219, in onecmd
    return func(arg)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7923-py2.5.egg/trac/admin/console.py", line 1154, in do_upgrade
    self.__env.upgrade(backup=do_backup)
  File "/usr/lib/python2.5/site-packages/Trac-0.11stable_r7923-py2.5.egg/trac/env.py", line 458, in upgrade
    participant.upgrade_environment(db)
  File "build/bdist.linux-i686/egg/tracwatchlist/plugin.py", line 381, in upgrade_environment
  File "build/bdist.linux-i686/egg/tracwatchlist/plugin.py", line 342, in _update_db_table
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc2 in position 26: ordinal not in range(128)

[anonymous]

I googled a little (again) to find some hints about the UnicodeDecodeError which bugs me already for a while. I now ([5316]) using the to_unicode() method from trac.util.text which should fix this.

[Martin Scharrer]

My login went away! comment:15 above is by me.

[Oliver Metz]

Okay, next error:

# trac-admin /var/www/trac upgrade --no-backup
Command failed: Couldn't rename DB table columns: FEHLER:  Syntaxfehler bei »user«
LINE 1: ALTER TABLE watchlist RENAME COLUMN user TO wluser;

There is already a watchlist table? Perhaps I should delete it before?

[Martin Scharrer]

Replying to olistudent:

Okay, next error:

That's now not a programming error, but a DB error, i.e. the code is correct but the DB reports a PostgresSQL specific syntax error. I could program in that the DB table is dropped in this case but I try to avoid such things in automatic code. It should work when you delete the old non-PostgresSQL-conform table and let the plugin create a new one.

You can delete the table e.g. using:

linux# sqlite3 $TRACPATH/db/trac.db
sqlite3> DROP watchlist;
sqlite3> <CTRL-D>

[anonymous]

Hi - just following this thread. I am using postgre backend and have similar problems. For me it fails in create_db_table. The output from trac-admin seems unhelpful. It fails with

psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

I'm going to try patching the db myself instead.

[anonymous]

executing the same create table query that is created by _create_db_table works for me using pgAdmin. Now I can get trac to load but when I watch a ticket I get

Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/trac/web/api.py", line 339, in send_error
    'text/html')
  File "/usr/lib/python2.5/site-packages/trac/web/chrome.py", line 708, in render_template
    if not req.session or not int(req.session.get('accesskeys', 0)):
  File "/usr/lib/python2.5/site-packages/trac/web/api.py", line 168, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/lib/python2.5/site-packages/trac/web/main.py", line 257, in _get_session
    return Session(self.env, req)
  File "/usr/lib/python2.5/site-packages/trac/web/session.py", line 142, in __init__
    self.get_session(req.authname, authenticated=True)
  File "/usr/lib/python2.5/site-packages/trac/web/session.py", line 156, in get_session
    super(Session, self).get_session(sid, authenticated)
  File "/usr/lib/python2.5/site-packages/trac/web/session.py", line 56, in get_session
    (sid, int(authenticated)))
  File "/usr/lib/python2.5/site-packages/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
InternalError: current transaction is aborted, commands ignored until end of transaction block

[anonymous]

if it's any help, the watch appears in the watchlist table so it is something to do with displaying the watched items rather than saving them i guess.

[anonymous]

Replying to olistudent:

Okay, next error:

# trac-admin /var/www/trac upgrade --no-backup
Command failed: Couldn't rename DB table columns: FEHLER:  Syntaxfehler bei »user«
LINE 1: ALTER TABLE watchlist RENAME COLUMN user TO wluser;

There is already a watchlist table? Perhaps I should delete it before?

Perhaps including the illegal column name using single quotes would work, e.g. 'user'?

[Martin Scharrer]

(In [6824])

plugin.py

Improved update sequence of watchlist table. This hopefully fixes issues with PostgresSQL. See #4097.

[Martin Scharrer]

(In [6825])

tracwatchlist/plugin.py

Added rollback for postgressql DB backends. See #4097 comment:19 comment:20 comment:21.

[Martin Scharrer]

The latest changes might fix all mentioned issues here. Please keep in mind that the plugin is currently in a state of development - email notification is added - so it might not be fully stable. I might backport this fixes to a stable version.

[Martin Scharrer]

The DB upgrade code is now ([6834]) stable again. Please install this version and reopen this ticket if you still have problems.

[Martin Scharrer]

(In [6950]) Changed SQL code to support MySQL and PostgresSQL backends better. See #6090, #4097.