#4097 closed defect (fixed)
SQL error on environment upgrade (postgresql)
Reported by: | Oliver Metz | Owned by: | Martin Scharrer |
---|---|---|---|
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 9 years ago by
Status: | new → assigned |
---|
comment:2 Changed 9 years ago by
comment:3 Changed 9 years ago by
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 9 years ago by
comment:5 Changed 9 years ago by
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 9 years ago by
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 9 years ago by
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 9 years ago by
Could you try the following patch for the 'integer = text' problem:
-
tracwatchlist/plugin.py
156 156 ticketlist = [] 157 157 cursor.execute( 158 158 "SELECT id,type,time,changetime,summary,reporter FROM ticket WHERE id IN " 159 "(SELECT residFROM watchlist WHERE wluser='%s' AND realm='ticket') "159 "(SELECT CAST(resid AS integer) FROM watchlist WHERE wluser='%s' AND realm='ticket') " 160 160 "GROUP BY id,type,time,changetime,summary,reporter " 161 161 "ORDER BY changetime DESC;" % user ) 162 162 tickets = cursor.fetchall()
comment:9 Changed 9 years ago by
I checked the patch now into subversion.
Could you please confirm that the last changes are also fixing #4099?
comment:10 Changed 9 years ago by
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 9 years ago by
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 9 years ago by
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 9 years ago by
I now [5314] replaced all str()
calls with unicode()
. This might fix your issue reported in comment:10. Please verify.
comment:14 Changed 9 years ago by
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 9 years ago by
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:17 follow-ups: 18 22 Changed 9 years ago by
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 follow-up: 19 Changed 9 years ago by
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 follow-up: 20 Changed 9 years ago by
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 follow-up: 21 Changed 9 years ago by
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 Changed 9 years ago by
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 Changed 9 years ago by
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 9 years ago by
Priority: | normal → high |
---|
comment:24 Changed 9 years ago by
comment:25 Changed 9 years ago by
(In [6825])
- tracwatchlist/plugin.py
- Added rollback for postgressql DB backends. See #4097 comment:19 comment:20 comment:21.
comment:26 Changed 9 years ago by
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 9 years ago by
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
The DB upgrade code is now ([6834]) stable again. Please install this version and reopen this ticket if you still have problems.
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.