Modify

Opened 7 months ago

Closed 7 months ago

#11484 closed defect (fixed)

Fresh install - SQL Error in Project Admin Page

Reported by: JR Owned by: falkb
Priority: normal Component: SimpleMultiProjectPlugin
Severity: blocker Keywords:
Cc: Trac Release:

Description (last modified by rjollos)

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

Attachments (0)

Change History (24)

comment:1 follow-ups: Changed 7 months ago by falkb

  • Status changed from new to 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 7 months 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 7 months ago by 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.

comment:4 in reply to: ↑ 1 Changed 7 months ago by 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.

comment:5 Changed 7 months 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:
            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()

comment:6 Changed 7 months 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()

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

comment:7 Changed 7 months 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.

comment:8 Changed 7 months ago by 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

Last edited 7 months ago by rjollos (previous) (diff)

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

Thanks,
JR

Last edited 7 months ago by rjollos (previous) (diff)

comment:12 Changed 7 months 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 7 months ago by rjollos (previous) (diff)

comment:13 Changed 7 months 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:

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

Last edited 7 months ago by rjollos (previous) (diff)

comment:14 Changed 7 months ago by rjollos

  • Description modified (diff)

comment:15 in reply to: ↑ 9 ; follow-ups: Changed 7 months ago by rjollos

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.

comment:16 in reply to: ↑ 15 Changed 7 months ago by 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.

comment:17 Changed 7 months ago by falkb

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

comment:18 Changed 7 months ago by rjollos

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 7 months ago by rjollos (previous) (diff)

comment:19 in reply to: ↑ 15 Changed 7 months ago by 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

     
    1414 
    1515# Database schema variables 
    1616db_version_key = 'simplemultiproject_version' 
    17 db_version = 5 
     17db_version = 6 
    1818 
    1919tables = [ 
    2020    Table('smp_project', key = 'id_project') [ 
     
    157157            sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s""" 
    158158            cursor.execute(sqlInsertVersion, [db_version, db_version_key]) 
    159159            db_installed_version = 5 
     160 
     161        if db_installed_version < 6: 
     162            # check for failed step 5 and retry if column 'restrict' doesn't exist 
     163            # (see TH:#11484) 
     164 
     165            cursor.execute("""PRAGMA table_info(smp_project)""") 
     166            column_names = cursor.fetchall() 
     167 
     168            # find out if the column is existing 
     169            has_restrict_column = False 
     170            for entry in column_names: 
     171                if 'restrict' in entry[1]: 
     172                    has_restrict_column = True 
     173 
     174            # Insert new column depending on if the column does not exist 
     175            if not has_restrict_column: 
     176                cursor.execute("""ALTER TABLE smp_project ADD %s text""" 
     177                               % db.quote('restrict')) 
     178             
     179            sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s""" 
     180            cursor.execute(sqlInsertVersion, [db_version, db_version_key]) 
     181            db_installed_version = 6 
Last edited 7 months ago by falkb (previous) (diff)

comment:20 Changed 7 months 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 7 months 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 7 months ago by rjollos

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 7 months 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 7 months ago by falkb (previous) (diff)

comment:24 Changed 7 months ago by falkb

  • Resolution set to fixed
  • Status changed from accepted to 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

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from falkb. Next status will be '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.