Opened 4 years ago

Closed 4 years ago

# Can't close a project?

Reported by: Owned by: endquote falkb normal SimpleMultiProjectPlugin normal 1.0

### Description

If I check the "closed" box on a project and then submit the form, I'm taken back to the projects list -- but no projects are shown! Scary! If I reload it, they come back, but the project I tried to close is still not closed.

### comment:1 Changed 4 years ago by falkb

This is strange. I saw such misbehaviour before my big svn commit of model.py repairing the db access routines, but I think you're already on the latest revision. The reason was that db writing did not work because of some mistakes in model.py. Uhmm... all projects I've closed lately didn't show that bug anymore. Can you see something in trac.log?

### comment:2 Changed 4 years ago by endquote

I removed and reinstalled the plugin from the latest source and the behavior remains.

I did see this in the log following the pressing of the "modify" button after checking the "closed" box.

2014-02-09 12:47:41,808 Trac[admin] INFO: Simple Multi Project: Modify project SST
2014-02-09 12:47:41,823 Trac[admin] ERROR: Modify Project Error: integer out of range


### comment:3 Changed 4 years ago by anonymous

What is your db type (SQLite, MySQL, ...)?

postgres 9.3.

### comment:5 Changed 4 years ago by anonymous

I wonder if function update_project() has that exception in self.__SmpModel.update_project(). Could you put self.log.info("%s modified." % (name)) after self.__SmpModel.update_project() and tell me if this logging appears in trac.log then?

### comment:6 Changed 4 years ago by anonymous

should be line 65 in admin.py

### comment:7 Changed 4 years ago by endquote

I downloaded the zipped source again, added that line to admin.py, re-compiled and re-installed the egg, restarted trac, and tried to close the project again.

2014-02-10 09:55:46,335 Trac[main] DEBUG: Dispatching <RequestWithSession "POST '/admin/projects/simplemultiproject/5'">
2014-02-10 09:55:46,335 Trac[webui] DEBUG: T&E matched: /admin/projects/simplemultiproject/5  None
2014-02-10 09:55:46,338 Trac[session] DEBUG: Retrieving session for ID u'josh'
2014-02-10 09:55:46,348 Trac[admin] INFO: Simple Multi Project: Modify project SST
2014-02-10 09:55:46,349 Trac[admin] ERROR: Modify Project Error: integer out of range
2014-02-10 09:55:46,349 Trac[chrome] DEBUG: Prepare chrome data for request


So the new log line is not appearing, which does seem to indicate an exception in self.__SmpModel.update_project().

### comment:8 Changed 4 years ago by Ryan J Ollos

You might want to try enabling SQL debugging. See TracIni#trac-section: [trac] debug_sql.

### comment:9 Changed 4 years ago by endquote

It looks like the closed field is being set to 1392068154000000L, which I'm guessing isn't what postgres wants.

Log with debug_sql on.

2014-02-10 13:36:10,612 Trac[session] DEBUG: Retrieving session for ID u'josh'
2014-02-10 13:36:10,612 Trac[util] DEBUG: SQL:
SELECT last_visit FROM session
WHERE sid=%s AND authenticated=%s

2014-02-10 13:36:10,612 Trac[util] DEBUG: args: (u'josh', 1)
2014-02-10 13:36:10,612 Trac[util] DEBUG: SQL:
SELECT name, value FROM session_attribute
WHERE sid=%s and authenticated=%s

2014-02-10 13:36:10,612 Trac[util] DEBUG: args: (u'josh', 1)
2014-02-10 13:36:10,628 Trac[util] DEBUG: args: ('766967c4a0f40ab830568043280825ce',)
2014-02-10 13:36:10,628 Trac[api] DEBUG: action controllers for ticket workflow: ['ConfigurableTicketWorkflow']
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT id, generation FROM cache
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT generation FROM cache WHERE id=%s
2014-02-10 13:36:10,628 Trac[util] DEBUG: args: (1722364385L,)
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT username, action FROM permission
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT
id_project,name,summary,description,closed,"restrict"
FROM
smp_project
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT
name
FROM
smp_project
WHERE
id_project=%s
2014-02-10 13:36:10,628 Trac[util] DEBUG: args: ['5']
2014-02-10 13:36:10,628 Trac[admin] INFO: Simple Multi Project: Modify project SST
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: UPDATE
smp_project
SET
name = %s, summary = %s, description = %s, closed = %s, "restrict" = %s
WHERE
id_project = %s
2014-02-10 13:36:10,628 Trac[util] DEBUG: args: [u'SST', u'xxx', u'', 1392068154000000L, u'employees, SST-external', u'5']
2014-02-10 13:36:10,628 Trac[util] DEBUG: execute exception: DataError('integer out of range\n',)
2014-02-10 13:36:10,628 Trac[admin] ERROR: Modify Project Error: integer out of range



The contents of the smp_project table:

1;"DOT";"''";"xxx";0;"employees, DOT-external"
2;"MVI3";"''";"xxx";0;"employees"
3;"QCC2";"''";"xxx";0;"employees"
4;"LAB2";"''";"xxx ";0;"employees"
5;"SST";"''";"xxx";0;"employees, SST-external"


### comment:10 Changed 4 years ago by anonymous

The question is how timestamp integer values must be saved in the right way. Any idea?

### comment:11 Changed 4 years ago by endquote

Nice sleuthing. I changed smp_project.closed to a bigint and was able to close the project.

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

### comment:12 follow-up:  26 Changed 4 years ago by Ryan J Ollos

Trac has a database abstraction that allows a large integer to be declared at type=int64: browser:/branches/1.0-stable/trac/db_default.py@11490:114-115#L110.

It looks like the issue is that directly altering the table is not compatible across database backends: simplemultiprojectplugin/trunk/simplemultiproject/environmentSetup.py@13590:148#L146. Compare that to what is done in various Trac upgrade steps, such as browser/branches/1.0-stable/trac/upgrades/db23.py.

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

### comment:13 Changed 4 years ago by falkb

Status: new → accepted

Thanks a lot for this excellent analysis. An update is in work now.

### comment:14 Changed 4 years ago by falkb

It's like a curse, changing the column type of a table is not possible with SQLite. :( The problem is described here. It seems to me, I must solve it in the way as Rajesh suggested there, which means to copy the table to a new one.

### comment:15 follow-up:  16 Changed 4 years ago by Ryan J Ollos

I'm not sure it would help even if you could change the column type for SQLite, because the database backends have different keywords for defining the datatypes.

Copying the table, so to speak, seems like the right approach, because that's how it is done in the Trac core when tables are upgraded. Make a temporary table to hold the data, drop the table, insert a new schema using the database abstraction, and then copy the data from the temporary table into the new table.

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

Copying the table, so to speak, seems like the right approach,

This looks like a bigger patch. If anybody wants to help out, a patch is welcome. Likely I wont find the time for the next 2 weeks.

... insert a new schema using the database abstraction...

you mean to use just 'int64' but the rest remains the same as in the earlier upgrade, right?

### comment:17 Changed 4 years ago by falkb

maybe it's just necessary to fix the plugin with another db upgrade for PostgreSQL, and maybe changing the type by altering the db table isn't a problem there...

### comment:18 Changed 4 years ago by Ryan J Ollos

The idea with using the classes in trac.db.schema is that they handle cross-db compatibility for you. You could probably find a way to fix this by modifying the schema for PostgreSQL, but then you may need to do the same for MySQL as well. It seems like more work, and a riskier approach, then just studying what has been done in trac:/browser/trunk/trac/upgrades and following the same approach.

### comment:19 Changed 4 years ago by falkb

Hi! Please, review and test my fix. (Note: I also used the opportunity to fix the bad table column name 'restrict' as well):

• ## 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: sql = [ # TH ticket 11553 (problems with int range in PostgreSQL): # fix: make field 'closed' of table 'smp_project' which is int a int64 """CREATE TEMPORARY TABLE smp_project_old AS SELECT * FROM smp_project""", """DROP TABLE smp_project""", """CREATE TABLE smp_project ( id_project      integer PRIMARY KEY, name            varchar(255), description     varchar(255), summary         varchar(255), closed          int64, restrict_to     text, UNIQUE(name) )""" ] for s in sql: cursor.execute(s) cursor.execute("""INSERT INTO smp_project (id_project,name,description,summary,closed,restrict_to) SELECT id_project,name,description,summary,closed,%s FROM smp_project_old""" % db.quote('restrict')) cursor.execute("""DROP TABLE smp_project_old""") sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s""" cursor.execute(sqlInsertVersion, [db_version, db_version_key]) db_installed_version = 6
• ## model.py

 else: db = self.env.get_read_db() query = """SELECT id_project,name,summary,description,closed,%s id_project,name,summary,description,closed,restrict_to FROM smp_project WHERE name = %%s""" % db.quote('restrict') name = %s""" if VERSION < '0.12': db = self.env.get_db_cnx() else: db = self.env.get_read_db() cursor = db.cursor() cursor.execute(query, [name]) cursor.execute(query, [str(name)]) return  cursor.fetchone() def get_all_projects(self): else: db = self.env.get_read_db() query = """SELECT id_project,name,summary,description,closed,%s id_project,name,summary,description,closed,restrict_to FROM smp_project""" % db.quote('restrict') smp_project""" if VERSION < '0.12': db = self.env.get_db_cnx() else: db = self.env.get_read_db() query    = """INSERT INTO smp_project (name, summary, description, closed, %s) VALUES (%%s, %%s, %%s, %%s, %%s);""" % db.quote('restrict') smp_project (name, summary, description, closed, restrict_to) VALUES (%s, %s, %s, %s, %s);""" if VERSION < '0.12': db = self.env.get_db_cnx() query    = """UPDATE smp_project SET name = %%s, summary = %%s, description = %%s, closed = %%s, %s = %%s name = %s, summary = %s, description = %s, closed = %s, restrict_to = %s WHERE id_project = %%s""" % db.quote('restrict') id_project = %s""" if VERSION < '0.12': db = self.env.get_db_cnx()

### comment:20 follow-up:  21 Changed 4 years ago by endquote

I installed the new version and was still able to close and open projects. I'm not sure that's really testing it, though.

### comment:21 in reply to:  20 Changed 4 years ago by falkb

I installed the new version and was still able to close and open projects. I'm not sure that's really testing it, though.

If you've applied the patch of comment:19, table smp_project should contain a column 'restrict_to' and column 'closed' should be of type in64. After all the table shouldn't have lost any entries during the conversion. And table 'system' should contain version 6 for the SMP plugin entry.

### comment:22 Changed 4 years ago by endquote

I'm not having any luck applying the patched. TortoiseMerge just complains about "rejected patch hunks". If you can supply the patched file I'll give it a shot.

### comment:23 Changed 4 years ago by falkb

endquote, would be cool if you tried with patchedFiles.zip

### comment:24 Changed 4 years ago by endquote

I installed the new plugin.

C:\Users\trac\Downloads\simplemultiprojectplugin\trunk>trac-admin c:\trac\stimulant upgrade
SimpleMultiProject database schema version is 5, should be 6

ProgrammingError: type "int64" does not exist
LINE 6:                      closed          int64,                                         ^


### comment:25 Changed 4 years ago by Ryan J Ollos

The patch isn't cross-DB compatible because the DB abstraction in trac.db.api isn't being utilized. See trac:browser:/branches/1.0-stable/trac/db_default.py?rev=12533#L32 for examples.

### comment:26 in reply to:  12 Changed 4 years ago by falkb

Trac has a database abstraction that allows a large integer to be declared in the schema using the keyword type=int64: browser:/branches/1.0-stable/trac/db_default.py@11490:114-115#L110.

I'm quite confused now because I just was doing what rjollos suggested, I used that int64.

### comment:27 Changed 4 years ago by falkb

I understood int64 as the cross-platform alias for long integers. Not sure how my patch must look like to be correct.

### comment:28 follow-up:  29 Changed 4 years ago by Ryan J Ollos

You need to use the Table, Column and Index classes when constructing your SQL in order to get cross-compatibility.

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

### comment:29 in reply to:  28 Changed 4 years ago by falkb

You need to use the Table, Column and Index classes when constructing your SQL in order to get cross-compatibility.

???

The idea with using the classes in trac.db.schema is that they handle cross-db compatibility for you. You could probably find a way to fix this by modifying the schema for PostgreSQL, but then you may need to do the same for MySQL as well. It seems like more work, and a riskier approach, then just studying what has been done in trac:/browser/trunk/trac/upgrades and following the same approach.

Following this I have used trac:/browser/trunk/trac/upgrades/db5.py as template since you stated this is cross-db compatible.

### comment:30 follow-ups:  31  32 Changed 4 years ago by Ryan J Ollos

The upgrade step db5.py is very old, from before PostgreSQL and MySQL were supported. It is not cross-db compatible.

​The code in trac:browser:/branches/1.0-stable/trac/db_default.py@11490:114-115#L110 shows how to construct SQL using the Table, Column and Index classes. I can't think of a better way to show you what needs to be done other than writing the code myself, which I don't have time to do at the moment.

(I'm not sure why the text in the previous paragraph is indented. It seems to be wrapped in a blockquote).

### comment:31 in reply to:  30 Changed 4 years ago by Ryan J Ollos

The upgrade step db5.py is very old, from before PostgreSQL and MySQL were supported. It is not cross-db compatible.

Actually, what I said about PostgreSQL and MySQL not having been supported at that time may not be correct. However, if you write raw SQL, then you need to ensure it is cross-db compatible. If you use the classes I suggested, the API will handle it for you.

The problem is that you've only half-implemented what I suggested (comment:12). I didn't say you could use int64 in SQL. I directed you to code showing how it can be used to construct SQL using the classes in trac.db.api.

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

### comment:32 in reply to:  30 ; follow-up:  34 Changed 4 years ago by falkb

The upgrade step db5.py is very old, from before PostgreSQL and MySQL were supported. It is not cross-db compatible.

I've just seen it's from 04/09/2013.

​The code in trac:browser:/branches/1.0-stable/trac/db_default.py@11490:114-115#L110 shows how to construct SQL using the Table, Column and Index classes.

I just see an assignment of a table construct to a variable called schema. No context how to put that together with the quite clear way of db5.py.

I can't think of a better way to show you what needs to be done other than writing the code myself, which I don't have time to do at the moment.

I still have hope to get a sudden enlightment but it's time-consuming to find the necessary info between the lines.

### comment:33 Changed 4 years ago by falkb

thanks a lot anyway for your patience and help

### comment:34 in reply to:  32 Changed 4 years ago by Ryan J Ollos

I still have hope to get a sudden enlightment but it's time-consuming to find the necessary info between the lines.

Ah, the life of an open-source developer ;)

Here is a simpler example: tagsplugin/trunk/tractags/upgrades/db4.py.

### comment:35 Changed 4 years ago by falkb

Looking twice at trac:/browser/branches/1.0-stable/trac/upgrades/db23.py again I can slightly imagine now this may help me as template as well... stay tuned.

### comment:36 Changed 4 years ago by falkb

don't know how I get to db5.py, likely I was looking for an easier one to use as template. Anyway it's clear now that the point is to use those 3 classes on creating new tables. Thanks for that hint, Ryan.

### comment:37 Changed 4 years ago by Ryan J Ollos

Yeah db23.py looks like a good example.

### comment:38 follow-up:  39 Changed 4 years ago by falkb

New trial for environmentSetup.py! :-D

The file model.py is the same as from the previous patch. I wonder if I should also take the chance to add the two new desired columns 'default_cc' and 'default_milestone' of #11543 here, I would at least save one more need to upgrade.

• ## 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') [ ], ] tables_v6 = [ Table('smp_project', key = 'id_project') [ Column('id_project', type = 'integer', auto_increment = True), Column('name',type = 'varchar(255)'), Column('description',type='varchar(255)'), Column('summary',type='varchar(255)'), Column('closed',type='int64'), Column('restrict_to') ], ] """ Extension point interface for components that need to participate in the creation and upgrading of Trac environments, for example to create 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: # TH ticket 11553 (problems with int range in PostgreSQL): # fix: make field 'closed' of table 'smp_project' which is int a int64 cursor.execute("""CREATE TEMPORARY TABLE smp_project_old AS SELECT * FROM smp_project""") cursor.execute("""DROP TABLE smp_project""") # Create tables for table in tables_v6: for statement in db_connector.to_sql(table): cursor.execute(statement) cursor.execute("""INSERT INTO smp_project (id_project,name,description,summary,closed,restrict_to) SELECT id_project,name,description,summary,closed,%s FROM smp_project_old""" % db.quote('restrict')) cursor.execute("""DROP TABLE smp_project_old""") sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s""" cursor.execute(sqlInsertVersion, [db_version, db_version_key]) db_installed_version = 6

P.S.: In case you already tried the patch of comment:19 you'll need to reset your trac.db to version 5 and probably rename column 'restrict_to' back to 'restrict' to get a consistent starting point.

second version

### comment:39 in reply to:  38 ; follow-up:  40 Changed 4 years ago by endquote

P.S.: In case you already tried the patch of comment:19 you'll need to reset your trac.db to version 5 and probably rename column 'restrict_to' back to 'restrict' to get a consistent starting point.

How would I do that? I'm not sure where trac.db is stored.

### comment:40 in reply to:  39 Changed 4 years ago by falkb

P.S.: In case you already tried the patch of comment:19 you'll need to reset your trac.db to version 5 and probably rename column 'restrict_to' back to 'restrict' to get a consistent starting point.

How would I do that? I'm not sure where trac.db is stored.

A trac instance consists of conf\trac.ini and in parallel is db\trac.db, you'll just need to open that trac.db with a database browser and change the version value of simplemultiprojectplugin in table 'system' from 6 back to 5. Also rename db table column 'restrict_to' back to 'restrict' in db table 'smp_project'. Then you've "reset" to the state before an upgrade from version 5 to 6.

### comment:41 Changed 4 years ago by endquote

Except for my database_version is currently 29. Here is the contents of the system table:

"custom_report_manager_version";"1"
"database_version";"29"
"initial_database_version";"29"
"mastertickets";"2"
"simplemultiproject_version";"5"
"subtickets";"2"
"T&E-statuses";"assigned,accepted,closed,new,reopened"
"TimingAndEstimationPlugin_Db_Version";"6"
"tt_version";"4"
"";""


### comment:42 Changed 4 years ago by falkb

OK, good. Then just check db table 'smp_project' if there's a column 'restrict' or 'restrict_to'. In case of the latter rename it to 'restrict'. Then you're ready to try the patched files model.py of attachment:patchedFiles.zip plus environmentSetup.py of attachment:patchedFiles.2.zip . Rebuild the plugin with it, reinstall that .egg file and restart Trac. Then it'll force you to upgrade which should work correctly now.

### comment:43 Changed 4 years ago by endquote

The smp_project field was already "restrict".

I checked out from source, applied patches, compiled, shut down trac, installed, upgraded:

C:\Users\trac>trac-admin c:\trac\stimulant upgrade
SimpleMultiProject database schema version is 5, should be 6


Restarted trac and didn't see anything obvious wrong.

The smp_project field is now "restrict_to", the "closed" field is a bigint (though I had made that change myself before), and the simplemultiproject_version is 6.

Looks like everything is good?

### comment:44 Changed 4 years ago by falkb

endquote, thanks a lot, you're great! Yeah, it works now. :-)

### comment:45 Changed 4 years ago by falkb

Resolution: → fixed accepted → closed

In 13735:

• fixed #11553: timestamps need to be bigint with PostgreSQL
• get rid of table 'smp_project' column 'restrict' because that is a db keyword and therefore not suitable as column name, renamed it to 'restrict_to' (see #11461)
• => plugin db schema version increased to 6

### Modify Ticket

Change Properties