This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JForum and the fly likes Base table not found Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » JForum
Bookmark "Base table not found" Watch "Base table not found" New topic
Author

Base table not found

Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
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:

don@linode1.vawter.com:/usr/local/catalina/webapps/joanieforum
$ mysql --version
mysql Ver 14.7 Distrib 4.1.8a, for pc-linux-gnu (i386) (production fails)

don@linode1.vawter.com:/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
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
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
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
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.



[originally posted on jforum.net by Anonymous]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
oops forgot to log in. last message was me ops:
[originally posted on jforum.net by donniev]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
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
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
Ok, I'll do that. Thanks.

Rafael
[originally posted on jforum.net by Rafael Steil]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
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
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
hi
Why I can not insert the image into my message?
[originally posted on jforum.net by Anonymous]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
Use the [img] tag

Rafael
[originally posted on jforum.net by Rafael Steil]
Migrated From Jforum.net
Ranch Hand

Joined: Apr 22, 2012
Posts: 17424
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]
 
Don't get me started about those stupid light bulbs.
 
subject: Base table not found
 
Similar Threads
Integration Problem
Internationalization
JForum installation fails with error Message
migration from MySql 4.1 to MySql 5.0
Group Permission Control