Opened 4 years ago

Closed 4 years ago

Fresh install - SQL Error in Project Admin Page

Reported by: Owned by: JR falkb normal SimpleMultiProjectPlugin blocker

Good morning,

Fresh install of trac and Simple Multi Project Plugin First time checking out the plugin via !admin -> "Manage Projects" -> "Projects" I get the following:

Trac detected an internal error: ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'restrict\n FROM\n smp_project' at line 2")

System Information: SimpleMultiProject 0.0.4dev /PATHtoPlugin/SimpleMultiProject-0.0.4dev-py2.7.egg

User Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.63 Safari/537.36

Trac 0.12.5 Babel 0.9.6 Docutils 0.11 Genshi 0.6 (with speedups) GIT 1.8.3.2 mod_python 3.3.1 MySQL server: "5.5.34-0ubuntu0.13.10.1", client: "5.5.34", thread-safe: 1 MySQLdb 1.2.3 Pygments 1.6 Python 2.7.5+ (default, Sep 19 2013, 13:52:09) [GCC 4.8.1] pytz 2012c setuptools 0.6 Subversion 1.7.9 (r1462340) jQuery 1.7.2

Most recent calls:

File "/usr/lib/python2.7/dist-packages/trac/web/main.py", line 522, in _dispatch_request
dispatcher.dispatch(req)
File "/usr/lib/python2.7/dist-packages/trac/web/main.py", line 243, in dispatch
resp = chosen_handler.process_request(req)
File "/usr/lib/python2.7/dist-packages/trac/admin/web_ui.py", line 117, in process_request
path_info)
projects_rows  = self.__SmpModel.get_all_projects()
File "build/bdist.linux-x86_64/egg/simplemultiproject/model.py", line 88, in get_all_projects
cursor.execute(query)
File "/usr/lib/python2.7/dist-packages/trac/db/util.py", line 66, in execute
return self.cursor.execute(sql)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue

Please let me know if you need any further information, Thanks JR

comment:1 follow-ups:  3  4 Changed 4 years ago by falkb

Status: new → accepted

Thanks for your report! Since I have SQLite, I cannot really reproduce this issue. The appropriate ticket is actually already in #11461 which is in state testing after today's commit [13549].

Do you use this SVN revision or newer?

comment:2 Changed 4 years ago by falkb

cursor.execute(query) is in line 98 of model.py here which indicates you still have the old SVN revision containing the bug (which was introduced shortly before Christmas).

comment:3 in reply to:  1 Changed 4 years ago by anonymous

Thanks for your report! Since I have SQLite, I cannot really reproduce this issue. The appropriate ticket is actually already in #11461 which is in state testing after today's commit [13549].

Do you use this SVN revision or newer?

I don't use SVN at all. I'm using git. Unless there is something hiding in trac that im unaware of. Forgive me ahead of time if your referring to something else. haha I'll take a look at that ticket and go from there.

comment:4 in reply to:  1 Changed 4 years ago by JR

Thanks for your report! Since I have SQLite, I cannot really reproduce this issue. The appropriate ticket is actually already in #11461 which is in state testing after today's commit [13549].

Do you use this SVN revision or newer?

I don't use SVN at all. I'm using git. Unless there is something hiding in trac that im unaware of. Forgive me ahead of time if your referring to something else. haha I'll take a look at that ticket and go from there.

comment:5 Changed 4 years ago by falkb

in other words: if you look at model.py you should see the following code. If not, you must update to the latest version of the plugin again, that would mean it's likely already fixed. I'm awaiting your test report now.

the code must be:

def get_all_projects(self):
if VERSION < '0.12':
db = self.env.get_db_cnx()
else:
query = """SELECT
id_project,name,summary,description,closed,%s
FROM
smp_project""" % db.quote('restrict')

if VERSION < '0.12':
db = self.env.get_db_cnx()
else:
cursor = db.cursor()
cursor.execute(query)
return  cursor.fetchall()

comment:6 Changed 4 years ago by JR

I had build 13548 with -

Line 81:

def get_all_projects(self):
cursor = self.__get_cursor()
query = """SELECT
id_project,name,summary,description,closed,restrict
FROM
smp_project"""

cursor.execute(query)
return  cursor.fetchall()

Line 83 -

def get_all_projects(self):
if VERSION < '0.12':
db = self.env.get_db_cnx()
else:
query = """SELECT
id_project,name,summary,description,closed,%s
FROM
smp_project""" % db.quote('restrict')

if VERSION < '0.12':
db = self.env.get_db_cnx()
else:
cursor = db.cursor()
cursor.execute(query)
return  cursor.fetchall()

Ill resintall the plugin and report back.

Thanks JR

comment:7 Changed 4 years ago by falkb

Yes, looks good. You've used the old version which was not db.quoted and so you run into this known issue (reported for MariaDB with #11461) as well. I think it will work for you now and we can close this ticket as duplicate of #11461.

Sorry for the bug, with my SQLite, I didn't expected restrict is a reserved keyword in MySQL and MariaDB.

Thanks,

From Shell:

host@comp:/var/lib/trac/x$trac-admin /var/lib/trac/x/ upgrade Database is up to date, no upgrade necessary. After upgrade im getting error (Admin->Projects) : Trac detected an internal error: OperationalError: (1054, "Unknown column 'restrict' in 'field list'") Most recent call last: File "/usr/lib/python2.7/dist-packages/trac/web/main.py", line 522, in _dispatch_request dispatcher.dispatch(req) File "/usr/lib/python2.7/dist-packages/trac/web/main.py", line 243, in dispatch resp = chosen_handler.process_request(req) File "/usr/lib/python2.7/dist-packages/trac/admin/web_ui.py", line 117, in process_request path_info) File "build/bdist.linux-x86_64/egg/simplemultiproject/admin.py", line 79, in render_admin_panel projects_rows = self.__SmpModel.get_all_projects() File "build/bdist.linux-x86_64/egg/simplemultiproject/model.py", line 98, in get_all_projects cursor.execute(query) File "/usr/lib/python2.7/dist-packages/trac/db/util.py", line 66, in execute return self.cursor.execute(sql) File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue I think I missed a step in the upgrade Thanks JR Last edited 4 years ago by Ryan J Ollos (previous) (diff) comment:9 follow-ups: 10 15 Changed 4 years ago by falkb Do you have access to your trac.db file with a database editor? Can you do a manual fix of the trac.db? I think as you installed the broken plugin version, it counted simplemultiproject_version up to version 5 in database-table system, but missed to create the 2 new columns closed and restrict in database-table smp_project, and so your database became inconsistent. It should work to simply manually decrease to version 4 in table system and reinstall the new version of the plugin. comment:10 in reply to: 9 ; follow-up: 11 Changed 4 years ago by JR Replying to falkb: Do you have access to your trac.db file with a database editor? Can you do a manual fix of the trac.db? I think as you installed the broken plugin version, it counted simplemultiproject_version up to version 5 in database-table system, but missed to create the 2 new columns closed and restrict in database-table smp_project, and so your database became inconsistent. It should work to simply manually decrease to version 4 in table system and reinstall the new version of the plugin. Heading that direction now. Will report back after. Thanks, JR comment:11 in reply to: 10 Changed 4 years ago by JR Replying to JR: Replying to falkb: Do you have access to your trac.db file with a database editor? Can you do a manual fix of the trac.db? I think as you installed the broken plugin version, it counted simplemultiproject_version up to version 5 in database-table system, but missed to create the 2 new columns closed and restrict in database-table smp_project, and so your database became inconsistent. It should work to simply manually decrease to version 4 in table system and reinstall the new version of the plugin. Heading that direction now. Will report back after. Thanks, JR trac.db -> "System" -> "Version (5)" to "Version (4)" Shell: host@comp:/var/lib/trac/x$ trac-admin /var/lib/trac/x/ upgrade
SimpleMultiProject database schema version is 4, should be 5
OperationalError: (1060, "Duplicate column name 'closed'")

Thanks, JR

Last edited 4 years ago by Ryan J Ollos (previous) (diff)

comment:12 Changed 4 years ago by falkb

OK, understood. The db upgrade from 4 to 5 wants to add 'closed' and 'restrict', but only the adding of 'restrict' has failed for you. First, reset from plugin version 5 to 4 again. Then either you manually remove also 'closed' before the reinstall or you hack the code in environmentSetup.py a bit, in a way that it avoids adding the 'closed' column:

if db_installed_version < 5:
# Insert new column
#HACK cursor.execute("""ALTER TABLE smp_project ADD closed integer""")

#HACK sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s"""
#HACK cursor.execute(sqlInsertVersion, [db_version, db_version_key])

# Insert new column
cursor.execute("""ALTER TABLE smp_project ADD %s text"""
% db.quote('restrict'))

sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s"""
cursor.execute(sqlInsertVersion, [db_version, db_version_key])
db_installed_version = 5
Last edited 4 years ago by Ryan J Ollos (previous) (diff)

comment:13 Changed 4 years ago by JR

All seams to be working now (Haven't played around to deep in the plugin). Just a quick recap to help anyone else out in the future:

System: Dedicated internal Ubuntu Server v13.10(Intranet) MySQL 5.5.34 Apache 2.4.7 Python 2.5.7+

Trac: Fresh install of Trac v1.0.1 Fresh install of Git v1.8.3.2 Fresh install of SimpleMultiProject build-13548

localhost -> Trac -> "Admin" -> "Project":

Trac detected an internal error:

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'restrict\n FROM\n smp_project' at line 2")''

Error caused by mysql call in build 13548 of SimpleMultiProject

Download SMP v13551 Install build 13551 per http://trac-hacks.org/wiki/SimpleMultiProjectPlugin#Installation Steps 2 -> 4 localhost -> Trac -> "Admin" -> "Project":

Trac detected an internal error:

OperationalError: (1054, "Unknown column 'restrict' in 'field list'")''

Edit trac.db via MySQL Workbench Table-System "Version (5)" changed to "Version (4)" Attempt to reinstall SMP

Shell:

SimpleMultiProject database schema version is 4, should be 5
OperationalError: (1060, "Duplicate column name 'closed'")''

Edit trac.db via MySQL Workbench Table-SMP_Project -> Remove "Closed" Column Reinstall SMP by Shell -> trac-admin /trac-instance/ upgrade

Good To Go!

Thanks for the help! JR

Last edited 4 years ago by Ryan J Ollos (previous) (diff)

comment:14 Changed 4 years ago by Ryan J Ollos

Description: modified (diff)

comment:15 in reply to:  9 ; follow-ups:  16  19 Changed 4 years ago by Ryan J Ollos

Do you have access to your trac.db file with a database editor? Can you do a manual fix of the trac.db? I think as you installed the broken plugin version, it counted simplemultiproject_version up to version 5 in database-table system, but missed to create the 2 new columns closed and restrict in database-table smp_project, and so your database became inconsistent. It should work to simply manually decrease to version 4 in table system and reinstall the new version of the plugin.

I had wondered while working on #11461 if we might need an additional upgrade step to fix a failed step 5 upgrade. At least now you know what operations might be needed in a "step 6" upgrade if you choose to write one.

comment:16 in reply to:  15 Changed 4 years ago by anonymous

I had wondered while working on #11461 if we might need an additional upgrade step to fix a failed step 5 upgrade. At least now you know what operations might be needed in a "step 6" upgrade if you choose to write one.

Although such step 6 would only make sense for people who updated the plugin between Christmas and yesterday, such auto-repairing sounds like a good idea.

comment:17 Changed 4 years ago by falkb

Is there a code snippet for how to detect if an SQL table column exists?

comment:18 Changed 4 years ago by Ryan J Ollos

You might be able to do something similar to jun66j5's code that checks for table existence: tagsplugin/trunk/tractags/db.py@:112-133#L111

Last edited 4 years ago by Ryan J Ollos (previous) (diff)

comment:19 in reply to:  15 Changed 4 years ago by falkb

I had wondered while working on #11461 if we might need an additional upgrade step to fix a failed step 5 upgrade. At least now you know what operations might be needed in a "step 6" upgrade if you choose to write one.

Is it this what we need? Would it work with MySQL and MariaDB as well?:

• simplemultiprojectplugin/trunk/simplemultiproject/environmentSetup.py

 # Database schema variables db_version_key = 'simplemultiproject_version' db_version = 5 db_version = 6 tables = [ Table('smp_project', key = 'id_project') [ sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s""" cursor.execute(sqlInsertVersion, [db_version, db_version_key]) db_installed_version = 5 if db_installed_version < 6: # check for failed step 5 and retry if column 'restrict' doesn't exist # (see TH:#11484) cursor.execute("""PRAGMA table_info(smp_project)""") column_names = cursor.fetchall() # find out if the column is existing has_restrict_column = False for entry in column_names: if 'restrict' in entry[1]: has_restrict_column = True # Insert new column depending on if the column does not exist if not has_restrict_column: cursor.execute("""ALTER TABLE smp_project ADD %s text""" % db.quote('restrict')) sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s""" cursor.execute(sqlInsertVersion, [db_version, db_version_key]) db_installed_version = 6
Last edited 4 years ago by falkb (previous) (diff)

comment:20 Changed 4 years ago by falkb

Anyway it works for me with SQLite. I've reset to SMP version 5 and removed the 'restrict' column, and then reinstalled.

comment:21 Changed 4 years ago by falkb

rjollos, PRAGMA table_info(tablename) is a SQLite-specific thing. It's different for MySQL and Postgres, and also different for other databases (e.g. MariaDB?). It's collected here.

What would you do if you were me?:

• implement that step 6 with supporting some important ones of those db types (and this may be an installation trap for all who don't use one of the implemented types)
• drop the idea of auto-repairing of failed installations in a step 6 (which may happened during the time window of the last 3 weeks until I fixed it 2 days ago).

comment:22 Changed 4 years ago by Ryan J Ollos

If I had the time I would try to implement a function that works with each supported DB type, however unless you get a lot of reports of users having the issue, and particularly if they aren't capable of manually fixing the issue, you are probably okay helping users repair there installations on a per-instance basis.

Another option would be to just document the steps needed to fix the issue for each DB-type.

In the big scheme of things, probably the best use of your time would be to setup PostgreSQL and MySQL environments that you can test in before pushing major changes.

comment:23 Changed 4 years ago by falkb

From a strategic point of view, I suppose it's better to help the few users (who updated during the bad time window) with repairing (probably by pointing to this ticket). The code is fixed now, all new checkouts don't have the problem anymore. And I rather don't want to bloat environmentSetup.py with things that could become a new trap (because of the non-crossplatform SQL code).

I'll sleep on it, and if noone has serious objections, I'll close this ticket then.

Last edited 4 years ago by falkb (previous) (diff)

comment:24 Changed 4 years ago by falkb

Resolution: → fixed accepted → closed

I assume the sum of all problems is less if we don't introduce automatic fixing of a problem we only had in a small time frame around New Year, because automatic fixing is non-crossplatform SQL code and would probably fail for certain databases.

Instead I recommend the manual fix, excellently described by JR in comment:13

Modify Ticket

Change Properties