Mysql error change

Traccar uses external service to resolve address. By default it uses Google API.

I have tried to debug the result returned from geocoder API and it's in correct encoding.

Hi,

I managed to fix this (I'm on OpenSUSE).

1. Login to console with the user that runs the server (sudo /opt/traccar/bin/traccar start)
   It seems that it's run by root, I didn't try other user.
2. Go to you your home directory (/root in this case)
3. Create or update your .bashrc and .profile with:

export LC_ALL=en_US.UTF-8
export LANG=en_US.UTF-8
export LANGUAGE=en_US.UTF-8

Please note: updating .profile and export LANG=en_US.UTF-8, export LANGUAGE=en_US.UTF-8 may not be necessary but I haven't check that yet.

1. source ~/.bashrc to activate it in current shell
2. sudo /opt/traccar/bin/traccar restart

DONE :)
Other option (I think better but I didn't try it) would be to set default encoding - java app may read it from environment or it may be set directly in app:

System.setProperty("file.encoding", "UTF-8");
http://stackoverflow.com/questions/361975/setting-the-default-java-character-encoding

Bartek.

Please try to set following parameter in the wrapper.conf file:

wrapper.java.additional.1=-Dfile.encoding=UTF-8

If it works, I can include it by default.

Yes, it works.

Thanks for feedback and for the fix suggestion. I have committed the fix into the repository, so the encoding will be set to UTF-8 by default starting from next release.

Hi guys,

I'm also confirming it works as the solution for my problem as described in another topic here.

But I also found another solution for it yesterday:

I added some code into JsonReverseGeocoder.java:

added line:
/addedLine/import java.nio.charset.Charset;

and:

/*addedLine*/Charset charset = Charset.forName("UTF8");
             InputStreamReader streamReader = new InputStreamReader(conn.getInputStream()/*addedStart*/,charset/*addedEnd*/);

I also changed the format of address to use postal_town instead of regions and put the house number after the address like this:
First Street 2, AT-1234 Testgebiet

Thanks guys for this solution, easier to implement.

Best regards

Hi Guys!

I could not solve this problem, not clear to me Bartek's last description, could not find .bashrc and .profile files in my home directory . If I create this files, what should be the content?

I am using Traccar Windows 64 bit 3.1 version with new web interface, mysql database, google geocoding.

If address value contains "Ü Ö Ű Ő Í" characters, the following error message is:

2015-10-30 11:42:58  WARN: Incorrect string value: '\xC5\x91i \xC3\xBA...' for column 'address' at row 1 - SQLException (... < QueryBuilder.java:412 < DataManager.java:329 < DefaultDataHandler.java:27 < ...)

2015-10-30 11:42:58  INFO: [7BE4D8A9] id: 4, time: Fri Oct 30 07:31:09 CET 2015, lat: 47.4675259, lon: 19.1179141, speed: 0.0, course: 0.0

Database url on my config file: jdbc:mysql://127.0.0.1:3306/carinfo?allowMultiQueries=true&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8&sessionVariables=sql_mode=ANSI_QUOTES

MySQL Server version:5.5

Best Regards.

Try solution from this post:

https://www.traccar.org/forums/topic/mysql-error-change/page/2/#post-1614

Hi, thanks for your help!

The real solution was for me, I modified the character encoding (in mysql database address column) from utf8 to utf8mb4_general_ci.

Thanks for everything, good job!

Csatika, so you basically only changed mysql database characterset and collation coding like that (in mysql prompt)?

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8mb4_general_ci;

I'm having the same error as you guys, but in my case solution presented by aspero is not working. I tried to change this locale settings in my user profile as well as in root profile (and .bashrc thereof), with no luck. I'm getting:

-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory

I tried to install language pack:

sudo apt-get install language-pack-en

but it didn't solved the problem.

any ideas how to fix this in 2017?

my exact error:

2017-03-28 20:21:34 WARN: Incorrect string value: '\xC5\x82awa ...' for column 'address' at row 1 - SQLException (... < QueryBuilder:473 < DataManager:296 < DefaultDataHandler:27 < ...)

Best,
Adam

Hi Adam,

the collation change itself is not going to be enough, try this:

ALTER TABLE positions
 CHANGE address address VARCHAR(512) CHARSET utf8mb4 COLLATE utf8mb4_general_ci NULL,
 CHARSET=utf8mb4, COLLATE=utf8mb4_general_ci;

Greetings,

Miro

P.S.: You can also try this:

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

instead of your query:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8mb4_general_ci;

but if any of the tables or fields have different charset and/or collation set up it won0t work.

Thanks for fast reply Miro,

I tried both queries you posted, but when I'm inserting first one I'm getting error:

ERROR 1067 (42000): Invalid default value for 'devicetime'

second one "went through" but didn't changed anything (error still visible in logs)

Any ideas?

Best,
Adam

OK, then just try this:

ALTER TABLE positions
CHANGE address address VARCHAR(512) CHARSET utf8mb4 COLLATE utf8mb4_general_ci NULL;

it will only alter the charset for column 'address' and leave other columns intact

Greetings,
Miro

Great! Thanks a lot Miro!

At first I tried your query and was still getting the same error. I found out however (thanks to this thread) that the problem was because of sql_modes. By default it is set to "NO_ZERO_IN_DATE,NO_ZERO_DATE" in new mysql versions.

Solution was to simply enter:

SET sql_mode = '';

Then I ran your query which worked like a charm!

Also, I'm not getting error in traccar log anymore so changing char encoding of the column 'address' in mysql worked out perfectly :D

Thanks again!

Cheers,
Adam

@atrocs, it sounds like you didn't follow the official documentation:

https://www.traccar.org/mysql/

It sets the "sql_mode" to empty.