MySQL error during config

[Migrated From Jforum.net]

When I perform a regular configuration (using the wizard) during install, I get the following message from MySQL:

Syntax error or access violation message from server: "You have an error in your SQL syntax near '; ' at line 1"
INSERT INTO jforum_groups ( group_name, group_description ) VALUES ('General', 'General Users');

Before I started the installation, I have created a database, and it seems to have been populated:

mysql> use jforum;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_jforum |
+-------------------------+
| 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)

mysql>

This is a Linux box running Tomcat via Apache.

The same software was installed successfully on a Windows XP box running Tomcat.

What's wrong?


Cheers,
Per

[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Hm, hard to say. Is the same mysql version?
You can bypass the installer configuring jforum manuall, as described at http://www.jforum.net/install_no_wizard.htm. Now, if even when running mysql_db_dump.sql from the command line you get the error, then that's bad.

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

[Migrated From Jforum.net]

Btw, the version of the complaining MySQL is 3.23.58
Is that too old?

Cheers,
Per

[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

(And the version of MySQL on my Windows box, where the installation went smoothly, is 4.0.20a-debug)

Per

[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

I don't think so. I have one jforum running on linux with mysql 3.23.58-log.

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

[Migrated From Jforum.net]

Btw: I tried to run the failing SQL statement manually, on the command line, and that worked. Something wrong with the mySQL login in some script?

Per

[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

hhmm.. maybe some problem with character encoding? try this: ctrl+c the contents of dump.sql and ctrl+v in into another file, and save it ( all from your linux machine ) and try importing the new file you saved. ( don't forget to run db_struct.sql first, otherwise you'll get duplicated data ).

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

[Migrated From Jforum.net]

Rafael Steil wrote: You can bypass the installer configuring jforum manuall, as described at http://www.jforum.net/install_no_wizard.htm. Now, if even when running mysql_db_dump.sql from the command line you get the error, then that's bad.

OK, I tried the manual install instructions.

The MySQL user has no password (yeah, baad, but..). So in the wizard, I left it blank. When I edited the file mysql.properties, it seems that the wizard added a password, equal to the username. Bug? That could perhaps explain the error message that I got.

I ran the SQL scrpits manually, and they worked without a hitch.

You may want to add these example lines to the manual install page:

Fur user 'whatever', database name 'mysql':

cd .../WEB-INF/config/database/mysql
mysql -u whatever -p
mysql> use mysql;
mysql> source mysql_db_struct.sql;
mysql> source mysql_data_dump;

I also did all the other items on the list.

Question: isn't a restart of the webapp necessary after that?

Still, when I direct my web browser to http://foo.com/jforum
I get redirected to the setup wizard. That's because index.htm
still redirects me there.

What's missing?

Per

[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

I tried of course to manually copy the file new_rename.htm, which contains the right redirect, to index.htm. No luck. I get a JForum page with this message:

java.lang.reflect.InvocationTargetException: Illegal operation on empty result set

Cheers,
Per

[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Hm, as connecting to mysql via jdbc without an username may result in problems with authentication, it is possible that the wizard has some bug related to this issue.
In your case I don't recommend using the installer.

You dont' need to restart tomcat after you run the sql scripts IF you alredy didn't tried to access the application. That's because jforum puts a lot of data in memory, and running the sql scripts after the jforum's servlet was loaded may result in not found data ( and then a restart is needed. ).

IF you use the installer you don't need to restart tomcat, since I manage all the cache by code.

The empty resultset thing may be related to no populated data. I saw you inserted into database "mysql". Have you changed the "dbName" key to use "mysql" instead of the default "jforum"?

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

[Migrated From Jforum.net]

Rafael Steil wrote:
The empty resultset thing may be related to no populated data. I saw you inserted into database "mysql". Have you changed the "dbName" key to use "mysql" instead of the default "jforum"?

Oopsie, no, that should read "jforum". My bad.

Rafael Steil wrote:Hm, as connecting to mysql via jdbc without an username may result in problems with authentication, it is possible that the wizard has some bug related to this issue.
In your case I don't recommend using the installer.

No, no, I am connecting with a username. It's just that it has no password:

per wrote: The MySQL user has no password (yeah, baad, but..). So in the wizard, I left it blank. When I edited the file mysql.properties, it seems that the wizard added a password, equal to the username. Bug? That could perhaps explain the error message that I got.

Rafael Steil wrote:You dont' need to restart tomcat after you run the sql scripts IF you alredy didn't tried to access the application. That's because jforum puts a lot of data in memory, and running the sql scripts after the jforum's servlet was loaded may result in not found data ( and then a restart is needed. ).

I forgot to mention that I did restart Tomcat. But the installation/configuration isn't quite finished. For example, index.htm still points at the wrong URL. That's only fixed by the wizard, right? So the manual install instructions should tell the user to do some more things, like fixing that?

Cheers,
Per

[originally posted on jforum.net by per]

[Migrated From Jforum.net]

Sorry, either a lack of username or password may result in connection problems.

All the installer does you can do manually. Edit index.htm and change the javascript to point to

document.location = "forums/list.page";

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

[Migrated From Jforum.net]

Rafael Steil wrote:Sorry, either a lack of username or password may result in connection problems.

All the installer does you can do manually. Edit index.htm and change the javascript to point to

document.location = "forums/list.page";

Rafael

I did that:

per wrote:I tried of course to manually copy the file new_rename.htm, which contains the right redirect, to index.htm. No luck. I get a JForum page with this message:

java.lang.reflect.InvocationTargetException: Illegal operation on empty result set

Is there something more that I must do (that the wizard does) to get it running?

Per

[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Nope. Just:

:arrow: Unzip jforum in the webapps dir
:arrow: Open WEB-INF/config/database/mysql/mysql.properties and set the dbName, password and username
:arrow: Import WEB-INF/config/database/mysql/mysql_db_struct.sql
:arrow: import WEB-INF/config/database/mysql/mysql_data_dump.sql
:arrow: Edit WEB-INF/config/SystemGlobals.properties and add the key installed = true
:arrow: Remove the key install from WEB-INF/config/modulesMapping.prorperties
:arrow: Copy new_index.htm over index.htm

:arrow: Start Tomcat and use.

These are the steps. With them you should get jforum running.

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

[Migrated From Jforum.net]

Rafael Steil wrote:Just:
...
:arrow: Edit WEB-INF/config/SystemGlobals.properties and add the key installed = true
...

Aha! This item is missing on the instruction page for manual install.
(http://www.jforum.net/install_no_wizard.htm)

:arrow: Copy new_index.htm over index.htm

(new_rename.htm, to be precise.) This item is also missing. I presume that you want to add them to the instruction page.

:arrow: Start Tomcat and use.

Does this imply that a restart is necessary, if Tomcat is already running?

These are the steps. With them you should get jforum running.

Well, now I get a blank web page... :?

(I haven't restarted Tomcat).

Per

[originally posted on jforum.net by per]

[Migrated From Jforum.net]

Blank pages generallly means database connection problems.
You can check the tomcat logs for a more acurate message.

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

[Migrated From Jforum.net]

We have found the problem!!

All previous theories have been red herrings. Forget them. Here's the real problem that causes a Mysql error in the install wizard:

The file ...install/mysql_dump.dat contains a blank after each semicolon.

That is not accepted by Mysql 3.23.58.

This misfeature is fixed in Mysql 4.0.20.

I strongly suggest that the JForum distribution is updated with a new version of mysql_dump.dat that does not contain a blank after each SQL statement.

Rafael: I could mail you a copy of our fixed version of mysql_dump.dat. Or do you prefer to generate a new one yourself?

Cheers,
Per

[originally posted on jforum.net by per]

[Migrated From Jforum.net]

Please send it to my email. I can generate a new one of course, but I would like to compare my generated version with yours, to make sure I didn't make any mistake.

Thanks a lot for finding this bug.

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