Base table not found

[Migrated From Jforum.net]

java.lang.reflect.InvocationTargetException: Base table or view not found message from server: "Unknown table 'jforum_roles' in MULTI DELETE"

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: Base table or view not found message from server: "Unknown table 'jforum_roles' in MULTI DELETE"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)

I have a couple of forums on a staging server. when I export the databases to the production server and redeploy the web app I get this error
The table is certianly there.

I have even tried a fresh install with the same results.

Is this possibly an MySQL versioning issue:

[e-mail]:/usr/local/catalina/webapps/joanieforum
$ mysql --version
mysql Ver 14.7 Distrib 4.1.8a, for pc-linux-gnu (i386) (production fails)

[e-mail]:/usr/local/catalina/webapps/joanieforum
$ exit
logout
Connection to linode closed.
don@blackbox:~$ mysql --version
mysql Ver 12.22 Distrib 4.0.23, for pc-linux-gnu (i386) (staging works)


+-------------------------+
| Tables_in_jforum_joanie |
+-------------------------+
| jforum_banlist |
| jforum_categories |
| jforum_config |
| jforum_forums |
| jforum_groups |
| jforum_posts |
| jforum_posts_text |
| jforum_privmsgs |
| jforum_privmsgs_text |
| jforum_ranks |
| jforum_role_values |
| jforum_roles |
| jforum_search_results |
| jforum_search_topics |
| jforum_search_wordmatch |
| jforum_search_words |
| jforum_sessions |
| jforum_smilies |
| jforum_themes |
| jforum_topics |
| jforum_topics_watch |
| jforum_user_groups |
| jforum_users |
| jforum_vote_desc |
| jforum_vote_results |
| jforum_vote_voters |
| jforum_words |
+-------------------------+
27 rows in set (0.00 sec)

[originally posted on jforum.net by donniev]

[Migrated From Jforum.net]

I *guess* that the problem in fact occurs due mysql version imcompatibilities. Since version 4.1, mysql ab has changed some architectural points of mysql, and one of them is related to unicode. You will have to do some more stepts in order to migrate a base from 4.0.x to 4.1.
Take a look at http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Are you saying that JForum out of the box will NOT install with mysql 4.1? My experience is that the sql scripts run and the app installs and you can acess messages, add messages etc but when you try to change permissions you get the file not found exception jforum_roles. You can see from the following interactive mysql session the it has no trouble accessing/reading the table;
From within mysql i can read the table edit rows etc without issue. It is just from JForum that this fails.

mysql> use joanie
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> describe jforum_roles;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| role_id   | int(11)      |      | PRI | NULL    | auto_increment |
| group_id  | mediumint(8) | YES  | MUL | 0       |                |
| user_id   | mediumint(8) | YES  | MUL | 0       |                |
| name      | varchar(255) |      | MUL |         |                |
| role_type | tinyint(1)   | YES  |     | 1       |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> select * from jforum_roles;
+---------+----------+---------+----------------------------------------+-----------+
| role_id | group_id | user_id | name                                   | role_type |
+---------+----------+---------+----------------------------------------+-----------+
|       1 |        1 |       0 | perm_administration                    |         0 |
|       2 |        1 |       0 | perm_moderation                        |         0 |
|       3 |        1 |       0 | perm_moderation_post_remove            |         0 |
|       4 |        1 |       0 | perm_moderation_post_edit              |         0 |
|       5 |        1 |       0 | perm_moderation_topic_move             |         0 |
|       6 |        1 |       0 | perm_moderation_topic_lockUnlock       |         0 |
|       7 |        1 |       0 | perm_read_only_forums                  |         0 |
|       8 |        1 |       0 | perm_anonymous_post                    |         0 |
|       9 |        1 |       0 | perm_forum                             |         0 |
|      10 |        1 |       0 | perm_category                          |         0 |
|      11 |        1 |       0 | perm_html_disabled                     |         0 |
|      12 |        2 |       0 | perm_administration                    |         1 |
|      13 |        2 |       0 | perm_moderation                        |         1 |
|      14 |        2 |       0 | perm_moderation_post_remove            |         1 |
|      15 |        2 |       0 | perm_moderation_post_edit              |         1 |
|      16 |        2 |       0 | perm_read_only_forums                  |         0 |
|      17 |        2 |       0 | perm_anonymous_post                    |         0 |
|      18 |        2 |       0 | perm_forum                             |         0 |
|      19 |        2 |       0 | perm_create_sticky_announcement_topics |         1 |
|      20 |        2 |       0 | perm_moderation_topic_move             |         1 |
|      21 |        2 |       0 | perm_category                          |         0 |
|      22 |        2 |       0 | perm_moderation_topic_lockUnlock       |         1 |
|      23 |        2 |       0 | perm_moderation_forums                 |         0 |
|      24 |        2 |       0 | perm_html_disabled                     |         0 |
|      25 |        3 |       0 | perm_read_only_forums                  |         0 |
|      26 |        3 |       0 | perm_anonymous_post                    |         0 |
|      27 |        3 |       0 | perm_moderation_post_remove            |         0 |
+---------+----------+---------+----------------------------------------+-----------+
27 rows in set (0.00 sec)

I can directly manipulate jforum_roles from mysql
mysql> update jforum_roles set role_type=1 where role_id=9;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from jforum_roles;
+---------+----------+---------+----------------------------------------+-----------+
| role_id | group_id | user_id | name                                   | role_type |
+---------+----------+---------+----------------------------------------+-----------+
|       1 |        1 |       0 | perm_administration                    |         0 |
|       2 |        1 |       0 | perm_moderation                        |         0 |
|       3 |        1 |       0 | perm_moderation_post_remove            |         0 |
|       4 |        1 |       0 | perm_moderation_post_edit              |         0 |
|       5 |        1 |       0 | perm_moderation_topic_move             |         0 |
|       6 |        1 |       0 | perm_moderation_topic_lockUnlock       |         0 |
|       7 |        1 |       0 | perm_read_only_forums                  |         0 |
|       8 |        1 |       0 | perm_anonymous_post                    |         0 |
|       9 |        1 |       0 | perm_forum                             |         1 |
|      10 |        1 |       0 | perm_category                          |         0 |
|      11 |        1 |       0 | perm_html_disabled                     |         0 |
|      12 |        2 |       0 | perm_administration                    |         1 |
|      13 |        2 |       0 | perm_moderation                        |         1 |
|      14 |        2 |       0 | perm_moderation_post_remove            |         1 |
|      15 |        2 |       0 | perm_moderation_post_edit              |         1 |
|      16 |        2 |       0 | perm_read_only_forums                  |         0 |
|      17 |        2 |       0 | perm_anonymous_post                    |         0 |
|      18 |        2 |       0 | perm_forum                             |         0 |
|      19 |        2 |       0 | perm_create_sticky_announcement_topics |         1 |
|      20 |        2 |       0 | perm_moderation_topic_move             |         1 |
|      21 |        2 |       0 | perm_category                          |         0 |
|      22 |        2 |       0 | perm_moderation_topic_lockUnlock       |         1 |
|      23 |        2 |       0 | perm_moderation_forums                 |         0 |
|      24 |        2 |       0 | perm_html_disabled                     |         0 |
|      25 |        3 |       0 | perm_read_only_forums                  |         0 |
|      26 |        3 |       0 | perm_anonymous_post                    |         0 |
|      27 |        3 |       0 | perm_moderation_post_remove            |         0 |
+---------+----------+---------+----------------------------------------+-----------+
27 rows in set (0.00 sec)

mysql> update jforum_roles set role_type=0 where role_id=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

oops forgot to log in. last message was me ops:
[originally posted on jforum.net by donniev]

[Migrated From Jforum.net]

Solved:

In mysql.sql you have

PermissionControl.deleteUserRoleValueByGroup = DELETE jforum_role_values \
FROM jforum_role_values rv, jforum_roles r, jforum_user_groups ug \
WHERE r.role_id = rv.role_id \
AND ug.user_id = r.user_id \
AND ug.group_id = ? \
AND r.name = ? \

In mysql 4.1 you cannot refer to a table by its name once you have given it an alias (actually I don't think you ever can in ANSI standard SQL)

changing to :
PermissionControl.deleteUserRoleValueByGroup = DELETE rv \
FROM jforum_role_values rv, jforum_roles r, jforum_user_groups ug \
WHERE r.role_id = rv.role_id \
AND ug.user_id = r.user_id \
AND ug.group_id = ? \
AND r.name = ? \

and everything works. You need to change all the queries in mysql.sql


[originally posted on jforum.net by donniev]

[Migrated From Jforum.net]

Ok, I'll do that. Thanks.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

You have an excellent site easy on the eye and very easy to navigate, keep up the good work. Best Wishes.
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

hi
Why I can not insert the image into my message?
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Use the [img] tag

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Greetings to all! Excuse for this message, but at you excellent design of a site! Very much it was pleasant to me, I shall come here very often!
[originally posted on jforum.net by Anonymous]