Fresh install - SQL Error in Project Admin Page

[JR]

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

[falkb]

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?

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

[anonymous]

Replying to falkb:

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.

[JR]

Replying to falkb:

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.

[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:
            db = self.env.get_read_db()
        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:
            db = self.env.get_read_db()
        cursor = db.cursor()
        cursor.execute(query)
        return  cursor.fetchall()

[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()

Just downloaded build 13551 with:

Line 83 -

   def get_all_projects(self):
        if VERSION < '0.12':
            db = self.env.get_db_cnx()
        else:
            db = self.env.get_read_db()
        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:
            db = self.env.get_read_db()
        cursor = db.cursor()
        cursor.execute(query)
        return  cursor.fetchall()

Ill resintall the plugin and report back.

Thanks JR

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

[JR]

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

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

[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

[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
Upgrading SimpleMultiProject database schema
SimpleMultiProject database schema version is 4, should be 5
OperationalError: (1060, "Duplicate column name 'closed'")

Thanks, JR

[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

[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:

host@comp:/var/lib/trac/x$ trac-admin /var/lib/trac/x/ upgrade
Upgrading SimpleMultiProject database schema
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

[Ryan J Ollos]

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.

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.

[anonymous]

Replying to rjollos:

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.

[falkb]

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

[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​

[falkb]

Replying to rjollos:

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

@@ -14 +14 @@
 
 # Database schema variables
 db_version_key = 'simplemultiproject_version'
-db_version = 5
+db_version = 6
 
 tables = [
     Table('smp_project', key = 'id_project') [
@@ -157 +157 @@
             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

[falkb]

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

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

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

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

[falkb]

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