OperationalError: no such table: smp_project

[nancyshipley@…]

After working to install SimpleMultiProjectPlugin, clicking on the Manage project always displays the error "OperationalError: no such table: smp_project". What part of the install creates the table? How can this error be reseolved?

[Christopher Paredes]

Hi Nancy,

this table should be created at the time you place an "upgrade" trac instance in which you installed the plugin.

trac-admin / path_your_instance_trac / upgrade

that would be enough to create the table above.

if you do this and the problem persists, it may be a problem with the database. Tell me more about: version, database engine and version you use.

regards

[nancyshipley@…]

Hi, Thanks for the quick response. We are using BitNami Trac 0.12.2 with mysql. Also, below is some is the full error.

2012-01-20 14:42:14,378 Trac[main] ERROR: Internal Server Error: 
Traceback (most recent call last):
  File "build\bdist.win32\egg\trac\web\main.py", line 511, in _dispatch_request
    dispatcher.dispatch(req)
  File "build\bdist.win32\egg\trac\web\main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "build\bdist.win32\egg\trac\admin\web_ui.py", line 116, in process_request
    path_info)
  File "C:\Program Files\BitNami Trac Stack\python\lib\site-packages\simplemultiproject-0.0.1-py2.6.egg\simplemultiproject\admin.py", line 47, in render_admin_panel
    projects_rows  = self.__SmpModel.get_all_projects()
  File "C:\Program Files\BitNami Trac Stack\python\lib\site-packages\simplemultiproject-0.0.1-py2.6.egg\simplemultiproject\model.py", line 27, in get_all_projects
    cursor.execute(query)
  File "build\bdist.win32\egg\trac\db\util.py", line 66, in execute
    return self.cursor.execute(sql)
  File "build\bdist.win32\egg\trac\db\sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "build\bdist.win32\egg\trac\db\sqlite_backend.py", line 56, in execute
    args or [])
  File "build\bdist.win32\egg\trac\db\sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: no such table: smp_project

Most likely the installed failed somehow. Any help would be great!

Thanks again,

Nancy

[Christopher Paredes]

was not tested with mysql so maybe the problem. I will investigate in detail the error to see if I can replicate.

Meanwhile, you might check the following:

- Check if the tables are created in the mysql database.
- Try using the postgres or sqlite engine to see if the problem persists.

The first thing that comes to mind is that the command: trac-admin "you path environment" upgrade tube no effect on the database. Perhaps by a problem with mysql.

Tables are created in the file environmentSetup.py. If we conclude that it is not possible to automatically load the tables maybe we should charge them manually.

attention to the error says sqlite_backend.py

I remain attentive to their responses

regards

[anonymous]

Hi, Thanks again for your support. Yes, there must have been an issue during the install. What are the steps to re-run the environmentsetup.py? Will that install the tables manually? Thanks so much, Nancy

[Christopher Paredes]

To set starting SQL database plugin

[Christopher Paredes]

Error

[Christopher Paredes]

Hi I think I could reproduce the problem, nose and may have occurred following a normal installation. Perhaps due to the way they are installed BitNami plugin.

To replicate the issue by performing the following:

1. Load the plugin with a new instance
2. run the command "trac-admin /path_to_instance_trac/ upgrade". This creates the tables smp_project me, smp_milestone_project and add a record to the table system trac instance.
3. Remove tables and registry in step 3.

After performing these steps, when entering the admin panel displays the following error:

OperationalError: no such table: smp_project

see figure: Error.png

This way you can replicate the problem somehow, unknown how this could happen.

This problem can be solved by trying one of two ways:

1. Restart the instance of trac and from the command line run "trac-admin /path_to_instance_trac/ upgrade".

2. If not resolved to the previous point, you might try the following:

from the command line you should try to load the tables manually by running the sentences found in the file: load_db.sql linux is performed as follows:

cd /path_to_instance_trac/
sqlite3 db/trac.db

in the sqlite shell prompt run:
read /path_to_file_sql/load_db.sql

windows should be similar

luck

Regards

[Christopher Paredes]

Sorry the sqlite command interpreter must execute the following command:

".read /path_to_file_sql/ load_db.sql;"

attention to the point before the word "read"

[nancyshipley@…]

Thanks again for hanging in there!

Here is the message after running the upgrade statement.

C:\Program Files\BitNami Trac Stack\python\Scripts>trac-admin "C:\Users\DEN-Admi
n\BitNami Trac Stack projects\DEN" upgrade
SimpleMultiProject database schema version: 1 initialized.
SimpleMultiProject database schema is out of date: False
Database is up to date, no upgrade necessary.

Trying again to load the Muliple Projects - the same error above continues.

Traceback (most recent call last):

  File "build\bdist.win32\egg\trac\web\main.py", line 511, in _dispatch_request
    dispatcher.dispatch(req)
  File "build\bdist.win32\egg\trac\web\main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "build\bdist.win32\egg\trac\admin\web_ui.py", line 116, in process_request
    path_info)
  File "C:\Program Files\BitNami Trac Stack\python\lib\site-packages\simplemultiproject-0.0.1-py2.6.egg\simplemultiproject\admin.py", line 47, in render_admin_panel
    projects_rows  = self.__SmpModel.get_all_projects()
  File "C:\Program Files\BitNami Trac Stack\python\lib\site-packages\simplemultiproject-0.0.1-py2.6.egg\simplemultiproject\model.py", line 27, in get_all_projects
    cursor.execute(query)
  File "build\bdist.win32\egg\trac\db\util.py", line 66, in execute
    return self.cursor.execute(sql)
  File "build\bdist.win32\egg\trac\db\sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "build\bdist.win32\egg\trac\db\sqlite_backend.py", line 56, in execute
    args or [])
  File "build\bdist.win32\egg\trac\db\sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: no such table: smp_project

When trying to run the command .read /path_to_file_sql/ load_db.sql; There is no load_db.sql anywhere. Where can I find that file?

THANK YOU!

[Christopher Paredes]

Replying to nancyshipley@hotmail.com:

Thanks again for hanging in there!

Here is the message after running the upgrade statement. ... Database is up to date, no upgrade necessary.

According to this database should be updated, in your trac.ini file check that your trac instance points to the appropriate database.

Trying again to load the Muliple Projects - ...

sqlite then the interpreter must execute the command as follows: ". read [replace this with the full path to your file load_db.sql stored on the server]"

[nancyshipley@…]

Awesome! That worked. Thanks so much!

[anonymous]

I have the same problem. The workaround with the load_db.sql is working, but in the next step I'm getting the next error: trac-admin [path to trac] upgrade OperationalError: no such column: summary

I have already tried the same with a new trac installation, but I get the same error :-(

[anonymous]

I have now tried to create all tables manually.

But now I have the next problem: If I try to create a new ticket, the following error occurs: OperationalError: no such column: m.version

The same if I try to access "Version" (in Administration/Ticket-System): OperationalError: no such column: version

It doesn't matter if versions are created or not.

[Cinc-th]

The script load_db.sql is way to old. It doesn't create the necessary tables the current plugin version needs. Thus your error about m.version.

All your troubles look to me like a missing environment upgrade which should create the tables.

Don't enable the plugin from within the plugin section of the admin panels. I'm not sure that will work at all (you may disable some parts later using this panel though).

Try the following with a new environment (just for testing):

1. Stop your Trac server stuff
2. Add the plugin to the plugins dir
3. Enable the plugin in trac.ini
4. run trac-admin <path/to/env> upgrade
5. Start your Trac-server and test

Which database are you using?

[rafael.fellow@…]

Hello,

It seams that for some unknown reason the script for database creation was not running. That is a bug.

I manage to build an up to date script that should be run via sqlite command line prior to running the upgrade comamand, based on the script posted by crossroad.

DROP TABLE IF EXISTS smp_milestone_project;
DROP TABLE IF EXISTS smp_project;
DELETE FROM system WHERE name='simplemultiproject_version';

INSERT INTO system ( name, value ) VALUES ( 'simplemultiproject_version', '6' );
CREATE TABLE smp_milestone_project ( id integer PRIMARY KEY, milestone varchar, id_project integer );
CREATE TABLE smp_project ( id_project integer PRIMARY KEY, name varchar(255), description varchar, summary varchar(255), closed integer, restrict_to text);

---

Another issue was the following line of code at environmentSetup.py

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

it should be

cursor.execute("""ALTER TABLE smp_project ADD %s text""" % db.quote('restrict_to'))

As that is the reference that is made throughout the code.

[Rafael Campos <rafael.fellow@…>]

The above script is not totally correct.

Found the correct structure on environmentSetup.py

INSERT INTO system ( name, value ) VALUES ( 'simplemultiproject_version', '6' );
CREATE TABLE smp_milestone_project ( id integer PRIMARY KEY AUTOINCREMENT, milestone varchar(255), id_project integer );
CREATE TABLE smp_project ( id_project integer PRIMARY KEY AUTOINCREMENT, name varchar(255), description varchar(255), summary varchar(255), closed int64, restrict_to text);

That should work better.

Check if all tables exist doing this queries:

#select id_project, name, description from smp_project #select id, milestone, id_project from smp_milestone_project #select id,version,id_project from smp_version_project #select id, component, id_project from smp_component_project #select id_project, name, description, summary, closed, restrict_to from smp_project

It it still doesn't work take a look at environmentSetup.py and create all the tables from the structure present in that file.

[kelly.a.campbell@…]

I'm having the same issue on a brand new install. I've debugged it down to model.py's constructor where it does self.get_all_projects(). This query fails as the table doesn't exist yet. However, commenting the line out and trying again, it seems to think no db upgrade is necessary and it does nothing.

Stack is

trac/admin/api.py(121)execute_command()
trac/core.py(78)extensions()
trac/core.py(204)__getitem__()
trac/core.py(140)__call__()
simplemultiproject/admin_command.py(63)__init__()
trac/core.py(140)__call__()
simplemultiproject/model.py(59)__init__()
simplemultiproject/model.py(96)get_all_projects()

[kelly.a.campbell@…]

Workaround:

svn update -r13786

Build, install, run upgrade. Then sync to latest code and run upgrade again.

[falkb]

Please, open a new ticket which describes the symptoms and insight of today. The history of this ticket is too old, and many comments and hints of this ticket refer to old code which may be changed, and we just get confused between the mixup of old and new stuff.