Modify

Opened 6 years ago

Closed 5 years ago

Last modified 5 years ago

#4097 closed defect (fixed)

SQL error on environment upgrade (postgresql)

Reported by: olistudent Owned by: martin_s
Priority: high Component: WatchlistPlugin
Severity: normal Keywords:
Cc: Trac Release: 0.11

Description

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 (

Attachments (0)

Change History (28)

comment:1 Changed 6 years ago by martin_s

  • Status changed from new to assigned

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.

comment:2 Changed 6 years ago by martin_s

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

doesn't work with PostgresSQL. See #4097.

comment:3 Changed 6 years ago by olistudent

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...

comment:4 Changed 6 years ago by martin_s

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

comment:5 Changed 6 years ago by martin_s

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.

comment:6 Changed 6 years ago by olistudent

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.

comment:7 Changed 6 years ago by martin_s

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.

comment:8 Changed 6 years ago by martin_s

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

  • tracwatchlist/plugin.py

     
    156156                ticketlist = [] 
    157157                cursor.execute( 
    158158                    "SELECT id,type,time,changetime,summary,reporter FROM ticket WHERE id IN " 
    159                     "(SELECT resid FROM watchlist WHERE wluser='%s' AND realm='ticket') " 
     159                    "(SELECT CAST(resid AS integer) FROM watchlist WHERE wluser='%s' AND realm='ticket') " 
    160160                    "GROUP BY id,type,time,changetime,summary,reporter " 
    161161                    "ORDER BY changetime DESC;" % user ) 
    162162                tickets = cursor.fetchall() 

comment:9 Changed 6 years ago by martin_s

I checked the patch now into subversion.

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

comment:10 Changed 6 years ago by olistudent

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

comment:11 Changed 6 years ago by martin_s

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

comment:12 Changed 6 years ago by olistudent

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)?

comment:13 Changed 6 years ago by martin_s

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

comment:14 Changed 6 years ago by olistudent

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)

comment:15 Changed 6 years ago by 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.

comment:16 Changed 6 years ago by martin_s

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

comment:17 follow-ups: Changed 6 years ago by 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?

comment:18 in reply to: ↑ 17 ; follow-up: Changed 6 years ago by martin_s

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>


comment:19 in reply to: ↑ 18 ; follow-up: Changed 5 years ago by 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.

comment:20 in reply to: ↑ 19 ; follow-up: Changed 5 years ago by 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

comment:21 in reply to: ↑ 20 Changed 5 years ago by 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.

comment:22 in reply to: ↑ 17 Changed 5 years ago by 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'?

comment:23 Changed 5 years ago by anonymous

  • Priority changed from normal to high

comment:24 Changed 5 years ago by martin_s

(In [6824])

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

comment:25 Changed 5 years ago by martin_s

(In [6825])

tracwatchlist/plugin.py
Added rollback for postgressql DB backends. See #4097 comment:19 comment:20 comment:21.

comment:26 Changed 5 years ago by martin_s

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.

comment:27 Changed 5 years ago by martin_s

  • Resolution set to fixed
  • Status changed from assigned to closed

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

comment:28 Changed 5 years ago by martin_s

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

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.