JBoss-4.0.0 and MySQL-4.0.18

[chrismalan]

I have used JBoss-3.2.0 and MySQL-4.0.18 with minimal problems up to now. Things move on and I suppose I have to change, little as I like change.

To test things I created a small web-app with one CMP EJB - username, password and fullname are the fields. I set up the mysql-ds.xml file, created the database fired up Jboss-4.0.0 and deployed the web-app. The following happens:

15:35:56,115 ERROR [EntityContainer] Starting failed jboss.j2ee:jndiName=PrincipalCMPLocal,
service=EJB
org.jboss.deployment.DeploymentException: Error while creating table principal_tbl;
- nested throwable:
(java.sql.SQLException: Syntax error or access violation, message from server:
"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server
version for the right syntax to use near 'NOT NULL, password VARCHAR, full_name VARCHAR)' at line 1")


I tried this and that and even replaced standardjbosscmp-jdbc.xml with the version from JBoss-3.2.0. The create statement can then not even start to run because certain classes cannot be loaded..

The connector is mysql-connector-java-3.0.14-production-bin.jar

The SQL syntax for creating a simple table is really very simple. Has MySQL changed that much from 4.0 to 4.1? Is JBoss-4.0.0 so incompatible with any version of MySQL less than 4.1? Will some changes to standardjbosscmp-jdbc.xml fix this problem? If so, which ones?

Looking thngs over again it almost seems as if the created SQL statement read something like this:

create table principal_tbl(
username VARCHAR, NOT NULL,
password VARCHAR,


with and offending comma before NOT NULL How can I see what SQL is created? Not that this will help if I can't change the created SQL.

I will apprecite any help.

Thanks,

Chris

[chrismalan]

Here is the cause and solution:
I use Eclipse/Lomboz as an IDE. The EJBWizard (in the case of CMP beans) takes VARCHAR as the SQL type, not VARCHAR(xy). It complains for VARCHAR(xy). The XDoclet tag generated then only specifies VARCHAR as the SQL type. In the generated JBossCMPJDBC.xml only VARCHAR appears as the SQL type in the 3.1 version of Eclipse. In 2.1 there was not even a <sql-type> tag, unluess I wanted it to be TEXT.

MySQL demands VARCHAR(xy). I've tried it on the command line with SQL.

After adding the (xy) to every appearance of VARCHAR as the SQL type (not the JDBC type - that is left as it is) the tables were created with no problems.

So, obviously JBoss-3.2.0 filled in (255) where no value was supplied for (xy) and JBoss-4.0.0 does not, which makes MySQL unhappy.

I wonder how many additional problems there are going to be - what about relationships, for starters. Is it worth the bother? Should I not stick with JBoss-3.2.0 and Eclipse/Lomboz 2.1? At least they worked.

[breaston]

Look at your create statement:

create table principal_tbl(
username VARCHAR, NOT NULL,
password VARCHAR,

You have a comma between VARCHAR and NOT NULL.