Modify

Opened 10 years ago

Closed 10 years 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 Ryan J Ollos)

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 Changed 10 years ago by falkb

Status: newaccepted

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 10 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 10 years 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 10 years 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 10 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:
            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 10 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()

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

comment:8 Changed 10 years 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 10 years ago by Ryan J Ollos (previous) (diff)

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

Thanks, JR

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

comment:12 Changed 10 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 10 years ago by Ryan J Ollos (previous) (diff)

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

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 10 years ago by Ryan J Ollos (previous) (diff)

comment:14 Changed 10 years ago by Ryan J Ollos

Description: modified (diff)

comment:15 in reply to:  9 ; Changed 10 years ago by 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.

comment:16 in reply to:  15 Changed 10 years 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 10 years ago by falkb

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

comment:18 Changed 10 years ago by Ryan J Ollos

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

Version 0, edited 10 years ago by Ryan J Ollos (next)

comment:19 in reply to:  15 Changed 10 years 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 10 years ago by falkb (previous) (diff)

comment:20 Changed 10 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 10 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 10 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 10 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 10 years ago by falkb (previous) (diff)

comment:24 Changed 10 years ago by falkb

Resolution: fixed
Status: acceptedclosed

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
Set your email in Preferences
Action
as closed The owner will remain falkb.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


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

 
Note: See TracTickets for help on using tickets.