Setup on Windows 2012 R2

general
1

Hi Justin,
I am trying to run a new test installation of OpenBoxes and Windows Server 2012. I successfully installed OpenBoxes following the instructions in the Windows installation video.
With little difficulty I get to the Login screen and logged in with the default username and password successfully.

I’m having trouble saveing Cyrillic letters in OpenBoxes form fields. I am doing tests with the “Profile → First name” field. I try to type “Иван” but I get “???” after [Save] at the same screen, as well as in all places where the user’s first name is printed. It is the same with other fields.

In the last few days, I tried to find a solution to situation myself, but without success. I tried the following ways:

  • I changed in Windows → Control Panel → Region and Language → Administrative tab → [Language for non-Unicode programs] I switched from English (United States) to Bulgarian and vice versa(Edited)Restore original;

  • Configure Tomcat [Apache TomCat 7.0 Propertyes] → Java [Tab] → Java Options:
    Add at the end:
    -XX:MaxPermSize=256m
    -Dfile.encoding=UTF-8
    -Dsun.jnu.encoding=UTF-8
    -Dstringchararrayaccessor.disabled=true

  • Checked MySql 5.7 OpenBoxes database encoding at:
    db.opt

default-character-set=utf8
default-collation=utf8_general_ci
  • tomcat7-stderr.log kept giving the following error until I added the option “-Dstringchararrayaccessor.disabled=true”
Unable to use direct char[] access of java.lang.String
java.lang.NoSuchFieldException: count
	at java.lang.Class.getDeclaredField(Class.java:1961)
	at org.codehaus.groovy.grails.web.util.StringCharArrayAccessor.<clinit>(StringCharArrayAccessor.java:53)
	at org.codehaus.groovy.grails.web.util.StreamCharBuffer.readAsString(StreamCharBuffer.java:531)
        171 more lines to same error...

No other errors except the above.
after i added “-Dstringchararrayaccessor.disabled=true” it doesn’t give it anymore. But I have not tried to go through new DB generation after that.

The configuration I’m testing with is a VM:

  • Windows Server 2012 R2;
  • tomcat-7.0.109
  • zulu7.56.0.11-ca-jdk7.0.352-win_x64
  • mysql-5.7.40.0
  • OpenBoxes 0.8.20

Any suggestions are greatly appreciated.

Regards,
Ivan

2

Hey Ivan

Great question.

It should work as long as everything is configured properly. The problem is … I’m not exactly sure what “configured properly” entails at the moment.

Cyrillic characters work in my local development environment.

image
image1920×983 72.4 KB

But we have a few enironments where it’s not working properly.

image
image1920×983 69.4 KB

And I have no idea what the difference is between the environments. We have an open ticket to investigate this and should be working on that at some point in the next few sprints.

To help with debugging, run the following queries and post the results here. My guess is that one of these queries is going to return latin1. I’m just not sure which one it’s going to be.

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'openboxes';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8                       | utf8_general_ci        |
+----------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SELECT TABLE_NAME, ENGINE, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'openboxes' and TABLE_NAME = 'user';
+------------+--------+-----------------+
| TABLE_NAME | ENGINE | TABLE_COLLATION |
+------------+--------+-----------------+
| user       | InnoDB | utf8_general_ci |
+------------+--------+-----------------+
1 row in set (0.00 sec)

If it’s all working properly, you should be able to set the first name and last name on the Edit Profile page and then query the database to see the correct values in the user table.

mysql> select username, first_name, last_name from user join person on person.id = user.id where username = 'jmiranda';
+----------+----------------+----------------+
| username | first_name     | last_name      |
+----------+----------------+----------------+
| jmiranda | Джастин        | Миранда        |
+----------+----------------+----------------+
1 row in set (0.00 sec)
3

Thank you for solving that one. I’ve just lived with that error for the last 10+ years. I was told by one of the Grails developers that it’s innocuous and can be safely ignored.

4

Hi Justin,

Thanks for your reply.
Here are the entire contents of “openboxes-config.properties”

dataSource.url=jdbc:mysql://localhost:3306/openboxes?useSSL=false
dataSource.username=***********
dataSource.password=***********

Here is the output of the SQL statements:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ‘openboxes’;
[{
“DEFAULT_CHARACTER_SET_NAME” : “utf8”,
“DEFAULT_COLLATION_NAME” : “utf8_general_ci”
}]

SELECT @@character_set_database, @@collation_database;
[{
“@@character_set_database” : “utf8”,
“@@collation_database” : “utf8_general_ci”
}]

SELECT TABLE_NAME, ENGINE, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘openboxes’ and TABLE_NAME = ‘user’;
[{
“TABLE_NAME” : “user”,
“ENGINE” : “InnoDB”,
“TABLE_COLLATION” : “utf8_general_ci”
}]

select username, first_name, last_name from user join person on person.id = user.id where username = ‘grifi’;
[{
“username” : “grifi”,
“first_name” : “Ivan ???”,
“last_name” : “???”
}]

I tried manually editing the SQL-DB-person-first_name: “Ivan Иван” entry and the Cyrillic was saved successfully. OpenBoxes showed the correctly written Cyrillic on all screens places, including the form field First name in “Profile”, but if resubmit form “Profile” the Cyrillic is again replaced by “?” symbols.

Meanwhile, I did a parallel test with a new installation on Windows 10 with the same result, Cyrillic not working:

  • Windows 10 Pro
  • zulu7.56.0.11-ca-jdk7.0.352-win_x64
  • OpenBoxes 0.8.20
  • remote access ot MySql instaled on Windows Server 2012

I changed in Windows → Control Panel → Region and Language → Administrative tab → [Language for non-Unicode programs] Enabled:Beta-Use Unicode UTF-8 for worldwide language support.
The only common thing in both test environments was MySql instaled on VM Windows Server 2012

Looking forward to suggestions hunting for a solution.

Regards,
Ivan

5

Yeah this is so weird. I figured it was just going to be one of those.

I just remembered that first name and last name columns are on the person table, not the user table.

So let’s check there as well.

SELECT TABLE_NAME, ENGINE, TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = ‘openboxes’ 
AND TABLE_NAME = ‘person’;

You can also see if one of the columns has a different character set.

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.`COLUMNS` 
WHERE table_schema = "openboxes" 
AND table_name = "person";
6

Sorry for the delay. Here is the SQL output:

SELECT TABLE_NAME, ENGINE, TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = "openboxes" 
AND TABLE_NAME = "person";
[{
		"TABLE_NAME" : "person",
		"ENGINE" : "InnoDB",
		"TABLE_COLLATION" : "utf8_general_ci"
}]

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.`COLUMNS` 
WHERE table_schema = "openboxes" 
AND table_name = "person";
[
	{
		"TABLE_NAME" : "person",
		"COLUMN_NAME" : "id",
		"CHARACTER_SET_NAME" : "utf8",
		"COLLATION_NAME" : "utf8_general_ci"
	},
	{
		"TABLE_NAME" : "person",
		"COLUMN_NAME" : "version",
		"CHARACTER_SET_NAME" : null,
		"COLLATION_NAME" : null
	},
	{
		"TABLE_NAME" : "person",
		"COLUMN_NAME" : "date_created",
		"CHARACTER_SET_NAME" : null,
		"COLLATION_NAME" : null
	},
	{
		"TABLE_NAME" : "person",
		"COLUMN_NAME" : "email",
		"CHARACTER_SET_NAME" : "utf8",
		"COLLATION_NAME" : "utf8_general_ci"
	},
	{
		"TABLE_NAME" : "person",
		"COLUMN_NAME" : "first_name",
		"CHARACTER_SET_NAME" : "utf8",
		"COLLATION_NAME" : "utf8_general_ci"
	},
	{
		"TABLE_NAME" : "person",
		"COLUMN_NAME" : "last_name",
		"CHARACTER_SET_NAME" : "utf8",
		"COLLATION_NAME" : "utf8_general_ci"
	},
	{
		"TABLE_NAME" : "person",
		"COLUMN_NAME" : "last_updated",
		"CHARACTER_SET_NAME" : null,
		"COLLATION_NAME" : null
	},
	{
		"TABLE_NAME" : "person",
		"COLUMN_NAME" : "phone_number",
		"CHARACTER_SET_NAME" : "utf8",
		"COLLATION_NAME" : "utf8_general_ci"
	},
	{
		"TABLE_NAME" : "person",
		"COLUMN_NAME" : "active",
		"CHARACTER_SET_NAME" : null,
		"COLLATION_NAME" : null
	}
]

I don’t have any experience with Grails, I have experience programming under Windows, and such a problem would occur when assigning a UNICODE string to ANSY unattended, where the user’s current “System Locale LCID” is used to convert the characters. When converting to ANSY, all characters that are outside the range of the current LCID are replaced with Chr(63)="?". A subsequent assignment from ANSY string to UNICODE is left unchanged with a “?” in it.

I’m just guessing, I have no experience, I’m not judging

7

My guess is that all form fields that are submitted from Tomcat → Java → Grails go through a generic code that causes uncontrollable conversion before being sending to SQL. I assume the problem only happens on Windows hosts.

Cyrillic on the OpenBoxes demo server works without a problem. There OS is Linux.

Here is a picture of my Windows test environment with OpenBoxes → Create Location → [Save Button]

Untitled
Untitled779×588 22.1 KB

8

Thank you for the added context. I’m going to need to set up a Windows environment to troubleshoot this further so this might take more time than I have at the moment.

In the meantime, here’s another resource that might be helpful.

I would recommend adding the Tomcat configuration from Step 2.

And if that doesn’t work, you can add the following parameters to the data source URL.

dataSource.url=jdbc:mysql://localhost:3306/openboxes?useSSL=false&useUnicode=yes&characterEncoding=UTF-8

And actually, before we do either of those things, let’s try to narrow down where we think the problem is occurring. There’s at least one place in the app where we can do this without having to set up a debugger / request analyzer.

  1. Go to Products > Categories

  2. Create a new category (or save an existing one) with some Cyrillic characters.

     Category Б б

  3. Check the logs to see whether the characters were passed from the browser to Tomcat (and to the application) without a problem.

     2022-12-13 19:52:10,783 [ajp-bio-127.0.0.1-8009-exec-2] INFO  product.CategoryController  - ["id":"cab2b48e7f6ade12017f6e402ab60142", "parentCategory.id":"cab2b48e7f6ade12017f6e4012350018", "parentCategory":["id":"cab2b48e7f6ade12017f6e4012350018"], "save":"", "_isRoot":"", "sortOrder":"0", "name":"Category Б б", "action":"saveCategory", "controller":"category"]

If those characters are fine, then we probably still have an issue with the database. If the values are also stored in the database fine, then we might have a problem with how Tomcat is rendering utf8 characters in the response.

9

It worked, hooray!

Thank you Justin, the next line made the Cyrillic alphabet to be written correctly in OpenBoxes & DB

I followed the instruction from step.2, alone does not help. It also works without it, only with the above SQL command.
I haven’t tried the other steps you suggested.

This problem is solved for me! Thank you very much for your help.
Regards,
Ivan

1 Like