Mysql error change

Change the database engine to MySQL.

When I enter new users and devices perfect save record in mysql.

But when I receive GPS data, these are not recorded. The log file tells me the following:

WARN: Incorrect string value: '\xEF\xBF\xBD\xEF\xBF\xBD...' for column 'address' at row 1 - SQLException (... < QueryBuilder.java:412 < DataManager.java:329 < DefaultDataHandler.java:27 < ...)

thank you in advance cooperation

Looks like there is some problem with address encoding. Can you show me your database.url parameter for the config file?

<entry key='database.url'>jdbc:mysql://127.0.0.1:3306/traccar?allowMultiQueries=true&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8&sessionVariables=sql_mode=ANSI_QUOTES</entry>

Make sure you escape ampersand character in the config file: &.

escape? you mean remove? so

<entry key='database.url'>jdbc:mysql://127.0.0.1:3306/traccar?allowMultiQueries=true;autoReconnect=true;useUnicode=yes;characterEncoding=UTF-8;sessionVariables=sql_mode=ANSI_QUOTES</entry>

You need to put & instead of &.

default had & amp; and does not work, then just let &, and still have the problem. try other codes. & xxx

Maybe I need to leave & amp; but change some settings of my mysql?

Thank's

I can confirm that I also have this problem. I didn't check how the address is detected from coordinates but it seems that the function that does it returns it with encoding that doesn't allow Latin chars (that's my case).
The difference is that my DB is configured to accept such input and I get � (which is \xEF\xBF\xBD\xEF\xBF\xBD) instead of DB error.

Bartek.

davidaceituno - check if your mySQL collation for traccar db is utf8_general_ci or similar.

http://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors
"In general, this happens when you insert strings to columns with incompatible encoding/collation."

This would confirm that the address is not encoded in UTF8.

Try to use "utf8mb4" encoding in MySQL instead of simply "utf8".

Changing collation to utf8mb4 in db doesn't make difference in my case.

I managed to reproduce davidaceituno's error by forcing utf8mb4 at INSERT INTO in traccar.xml config file like this:

    INSERT INTO position (deviceId, protocol, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other)
    VALUES (:deviceId, :protocol, CURRENT_TIMESTAMP(), :time, :time, :valid, :latitude, :longitude, :altitude, :speed, :course, convert(cast(convert(:address using  utf8mb4) as binary) using utf8), :other);

This way we can convert the input of address field to binary and back to utf8. I just can't find the right encoding of the oryginal :address value (it's not latin1, latin2, cp1250).

Bartek.

Internally Java uses UTF-16 encoding, but I'm not sure what happens to a string when it goes through MySQL JDBC driver.

Hi Anton,

convert(cast(convert(:address using utf16) as binary) using utf8) 

causes:

WARN: Invalid utf8 character string: 'FFFDFF' - SQLException (... < QueryBuilder.java:412 < DataManager.java:329 < DefaultDataHandler.java:27 < ...)

I'm not sure, but maybe this would help?
http://roartindon.blogspot.com/2015/04/hacking-utf16-to-work-around-mysqls.html

Bartek.

Hi Anton,

How does your app get street addresses from lat/long? I think that may be the problem. This has to be some external service (openMaps? http://www.latlong.net/convert-address-to-lat-long.html?) and the address is sent back with some other encoding that seems to be ok unless it uses latin or other extended characters. Your app works fine, otherwise there would be problems in other db fields. Just the address itself is not UTF16 (or UTF8?) when it's returned from the external service.

How do you think?

Bartek.