MySQL Query mutates on huge instances:

Question

I am currently calculating values to fill a database of 15 milion records. The first 7 mill went just fin,e however now my update query starts giving problems : Now & then a random letter changes into some jibberish. In java I generate the query doing :

String updateSql = "UPDATE VanNaar SET time = CASE ID ";
        for (int i = 0; i < routes.size(); i++) {
            updateSql += " WHEN " + routes.get(i).ID + " THEN  " + routes.get(i).driveTime;
        }
        updateSql += " END, ";
        updateSql += " distance = CASE ID ";
        for (int i = 0; i < routes.size(); i++) {
            updateSql += " WHEN " + routes.get(i).ID + " THEN  " + routes.get(i).distance;
        }
        updateSql += " END WHERE id IN (";
        for (int i = 0; i < routes.size(); i++) {
            updateSql += routes.get(i).ID + ",";
        }
        updateSql = updateSql.substring(0, updateSql.length() - 1);
        updateSql += ");";

Which works just fine, as mentioned before. Here is what Java trows at me now:

...MySQL server version for the right syntax to use near '×HEN 8284022 THEN  999.999 WHEN 8284023 THEN  3791.0 WHEN 8284024 THEN  378...

Or

...MySQL server version for the right syntax to use near 'WÈEN 7468574 THEN  2273.0 WHEN 7468575 THEN  2410.0 WHEN 7468576 THEN  2472.0 W' at line 1

Notice the weirdisch Ã^ or Ã- , a final exmpale, mind you the bold tekst:

...MySQL server version for the right syntax to use near **'Â** WHEN 7228125 THEN 48590.0 WHEN 7228126 THEN 47910.0 WHEN 7228127 THEN...

...

Update: It seems to be getting worse..:

 Unknown column '9°22331' in 'where clause'

Answer 1

I'm no Java expert but shouldn't you be using a StringBuilder in the first place? Possibly even use a prepared statement? You could buld the prepared statement with a stringbuilder but instead of

updateSql += " WHEN " + routes.get(i).ID + " THEN  " + routes.get(i).driveTime;

everywhere you'd do something like

myStrngBldr.append(" WHEN ? THEN  ?");

or

myStrngBldr.append(" WHEN @foo1 THEN @foo2");

if named parameters are supported (don't know) and later add the actual parameters:

myPrepdStmt = myConn.prepareStatement(myStrngBldr.toString());

myPrepdStmt.setInt(1, routes.get(i).ID);
myPrepdStmt.setFloat(2, routes.get(i).driveTime);
...
myPrepdStmt.executeUpdate();

This page should help you.

What is actually causing the 'strange malformed strings': I don't know. My best guess would be the you'd have to use something like .ToString() on all those ID's and other non-string values since you're concatenating a string. Maybe, somehow, the values are interpreted as charcodes (because they're not explicitly casted as string) and thus causing weird characters.

Another guess would be: are you actually building 15 million queries in-memory before sending them to the database? Or does each query get sent to the DB seperately? Maybe the fact that you're trying to store a huge-ass string in-memory causes some problems (although it shouldn't cause the problem you're describing here).