Spring, JDBC and MySQL

[rlynn]

I am having several strange problems with SqlUpdate and my MySql database. I have not yet implemented transactions, so I have an ordinary datasource declared and use code like:

    public void updateUser(User user) {
    	
          SqlUpdate su = 
            new SqlUpdate(ds, "update user set username = ?, " +
            					"firstname = ?, " +
			                               "middleinit = ?, " +
            					"lastname = ?, " +
            					"address1 = ?, " +
            					"address2 = ?, " +
            					"city = ?, " +
            					"state = ?, " +
            					"zip = ?, " +
            					"where id = ?");

        su.declareParameter(new SqlParameter("username", Types.VARCHAR));
        su.declareParameter(new SqlParameter("firstname", Types.VARCHAR));
        su.declareParameter(new SqlParameter("middleinit", Types.VARCHAR));
        su.declareParameter(new SqlParameter("lastname", Types.VARCHAR));
        su.declareParameter(new SqlParameter("address1", Types.VARCHAR));
        su.declareParameter(new SqlParameter("address2", Types.VARCHAR));
        su.declareParameter(new SqlParameter("city", Types.VARCHAR));
        su.declareParameter(new SqlParameter("state", Types.VARCHAR));
        su.declareParameter(new SqlParameter("zip", Types.VARCHAR));
        su.declareParameter(new SqlParameter("id", Types.INTEGER));
        su.compile();
        
        Object[] oa = new Object[10];
        oa[0] = new String(user.getUsername());
        oa[1] = new String(user.getFirstname());
        oa[2] = new String(user.getMiddleinit());
        oa[3] = new String(user.getLastname());
        oa[4] = new String(user.getAddress1());
        oa[5] = new String(user.getAddress2());
        oa[6] = new String(user.getCity());
        oa[7] = new String(user.getState());
        oa[8] = new String(user.getZip());
        oa[9] = new Integer(user.getId());
        int count = su.update(oa);
    }

Using the above code the change is made, but when I re-retrieve the list that the user is on, the list cuts off at the user I edited (even when I go into MySql and do a select * - although I can get the record by selecting by id in MySql) and this last time I found I had a corrupted table. I have also done alot of inserts where the new item on the list did not appear until I rebooted the machine. Should I be adding some sort of commit to the above code? Is there something else I'm missing? Should I revert back to the old jdbc and forget Spring? I hate to try to figure out transactions when the basic code does not work.

Thanks!

[rlynn]

Here's some more information. I brought up the list of users and selected edit for one of them. I changed a field and clicked update, which runs the statement in my previous post. When I re-retrieved the list, the list cut off the user I edited and everyone after him. Then I went to MySql and checked the table with the following results:

mysql 4.1.12a-nt-max> check table User;
+----------+-------+----------+-------------------------------------------------
--+
| Table | Op | Msg_type | Msg_text
|
+----------+-------+----------+-------------------------------------------------
--+
| cwa.User | check | warning | Size of datafile is: 1360 Should be: 1332
|
| cwa.User | check | warning | Found 22 parts Should be: 21 part
s |
| cwa.User | check | status | OK
|
+----------+-------+----------+-------------------------------------------------
--+
3 rows in set (0.02 sec)

mysql 4.1.12a-nt-max> repair table User
->
-> ;
+----------+--------+----------+----------------------+
| Table | Op | Msg_type | Msg_text |
+----------+--------+----------+----------------------+
| cwa.User | repair | error | 13 when fixing table |
| cwa.User | repair | status | Operation failed |
+----------+--------+----------+----------------------+
2 rows in set (0.00 sec)

mysql 4.1.12a-nt-max> check table User;
+----------+-------+----------+-------------------------------------------------
--+
| Table | Op | Msg_type | Msg_text
|
+----------+-------+----------+-------------------------------------------------
--+
| cwa.User | check | warning | Table is marked as crashed and last repair faile
d |
| cwa.User | check | warning | Size of datafile is: 1360 Should be: 1344
|
| cwa.User | check | error | Keypointers and record positions doesn't match
|
| cwa.User | check | warning | Found 22 parts Should be: 21 part
s |
| cwa.User | check | error | Corrupt
|
+----------+-------+----------+-------------------------------------------------
--+
5 rows in set (0.01 sec)

mysql 4.1.12a-nt-max> repair table User;
+----------+--------+----------+----------------------+
| Table | Op | Msg_type | Msg_text |
+----------+--------+----------+----------------------+
| cwa.User | repair | error | 13 when fixing table |
| cwa.User | repair | status | Operation failed |
+----------+--------+----------+----------------------+
2 rows in set (0.01 sec)

mysql 4.1.12a-nt-max>

Any ideas?? All of my updates and inserts have had unpredictable results.

[trisberg]

This does not seem Spring related. Maybe you have some failing hardware or a corrupt filesystem. You could try a MySQL forum to see if they can help pinpointing the cause of the problem.

[rlynn]

Thanks, I have posted to a MySQL forum. I am also wondering if the 'char' datatypes in the database could be causing trouble - I was using the VARCHAR datatype and Strings for them in my update(?)

[rlynn]

FYI, I reinstalled MySQL and now everything runs fine...