Issue Details#650 mysql tables need to set a default character encoding

  • Priority: Normal
  • Number: 650
  • Type: Bug
  • Status: Resolved
  • Age: 2038 Days
  • Assignee: botsko

Description

The plugin appears to function 100%, but upon doing any lookup, even lookups that should most definitely have records, 0 records are displayed. System is:

  • Ubuntu Server 12.04.
  • Core2Duo
  • 8GB DDR3
  • MySQL (localhost) v5.5.31 for debian
  • Bukkit 1.5.2-R0.1 (2771)
  • Prism 1.6.3 (Jun 08, 2020 build)

I have attempted with and without plugins. With and without PermissionsEx. The result is always the same. The attempted queries while in the client were "/pr l t:20m" which should have retrieved many records. The last server.log is posted here. At the request of h0us3cat, I am submitting this ticket. Will continue testing on a previous build to confirm if the issue is version-spanning.

History

tyraen June 12, 2020 5:22 AM

Tested May 15th release of Prism 1.6.2 for 1.5.2-R0.1 with the same results. Plugin successfully writes to database, but returns 0 records.

botsko Gravatar

botsko June 12, 2020 5:35 PM

I can't reproduce this, and no one else has reported it. My only thought is that the timezone settings between your server and mysql server are different enough to cause the server to query for the incorrect times. Is the mysql server remote?

tyraen June 12, 2020 6:39 PM

It is on the same machine. I have just checked and you can see the console output in the included photo. I'm certainly not saying it is in any way the plugin, but I have checked and cannot figure out what the cause could be. Since my setup is different than what I am suspecting is your average user's, I figured getting some additional input from you guys in case this IS a new bug with running on Linux can't hurt. Besides, having additional wiki information on how to setup for Linux can't hurt. ;)

tyraen June 12, 2020 7:02 PM

It is on the same machine. I have just checked and you can see the console output in the included photo. I'm certainly not saying it is in any way the plugin, but I have checked and cannot figure out what the cause could be. Since my setup is different than what I am suspecting is your average user's, I figured getting some additional input from you guys in case this IS a new bug with running on Linux can't hurt. Besides, having additional wiki information on how to setup for Linux can't hurt. ;)

botsko Gravatar

botsko June 12, 2020 7:08 PM

The only other possibility I could think of, is if somehow the results being returned aren't being read properly. Prism has some internal logic to skip loading the results if certain pieces of information are invalid:

https://github.com/prism/Prism/blob/master/src/main/java/me/botsko/prism/actionlibs/ActionsQuery.java#L106

If the action_type column is either empty, or invalid it will skip loading the result. If all records somehow had an invalid action type, this would cause the lookups to show zero results while mysql had actually returned some - however, I don't see how the action types could possibly be invalid - those are recorded by prism and I'm fairly certain you didn't modify them.

Also, be sure to try the latest build, just in case:

http://botsko.s3.amazonaws.com/Prism/Prism-v1.6.3.jar

tyraen June 12, 2020 7:57 PM

I went ahead and installed MySQL 5.6 for Windows on a remote machine and configured Prism to connect to it. Prism was able to connect AND pull information from MySQL. This narrows it down to my Linux machine's MySQL server implementation, but I'm still not sure why it would be able to write to the database but not retrieve successfully. More investigation is required. Any thoughts? Is MySQL reporting the data in a format Prism can't use?

tyraen June 12, 2020 8:00 PM

Hmm. Well, the issue certainly doesn't involve Prism. I'm wondering if it is an implementation issue of MySQL 5.5 causing this effect. I'll have to do further research and see. Would you like me to update this thread with my findings?

botsko Gravatar

botsko June 12, 2020 8:11 PM

Please do. Possibly some incompatibility between java and mysql, something with the jdbc driver or the tomcat-jdbc-pool technology we're using.

tyraen June 12, 2020 10:50 PM

Hmm. Well, the issue certainly doesn't involve Prism. I'm wondering if it is an implementation issue of MySQL 5.5 causing this effect. I'll have to do further research and see. Would you like me to update this thread with my findings?

tyraen June 14, 2020 3:41 AM

Good news everyone!

I'm narrowing in on the problem. I've got the project downloaded and maven is compiling. I don't have any fancy environments, so I can't do any sort of nifty breakpoints or anything, but I'm doing some investigating on my own to see what is up. I've got it printing out the resultset entries and it DOES find several (like it is supposed to), but the action_type string always comes out null. I'm investigating further to figure out why.

tyraen June 14, 2020 4:15 PM

It WORKS! I finally figured out what the problem is! Apparently, in Linux, when the mysql server is left up to its own to determine what character set to use for the tables, it uses the default databases and/or server default for character set. In mine, it used utf8_bin instead of utf8_unicode_ci. The binary character set wasn't returning the expected strings on the resultSet.getString() method. I altered the prism_actions table to change the charset to utf8_unicode_ci and things are running perfectly!

I'm going to take a look at the initial setup code and see if it is specifying utf8_unicode_ci during table creation. If not, it may be a good idea to specify to eliminate issues when idiots like me don't ensure a proper generic charset for their mysql server.

tyraen June 14, 2020 4:31 PM

My suspicions were correct. While not necessarily a bug, line 481 of Prism.java where the setupDatabase method is, the query string to create the initial tables does not include a default character set for prism_actions. I suggest implementing a default character set. Not necessarily utf8 (possibly latin as it's only one byte per char), but this would prevent any confusion getting data back into Prism.

I sincerely hope this helps you. It's been a ride for me, but at least now I know why! Never again will I leave a charset unchecked! :p

botsko Gravatar

botsko August 4, 2020 3:41 AM

  • Assignee → botsko
  • Milestone → 2.0
  • Status → Assigned
  • Summary → mysql tables need to set a default character encoding
botsko Gravatar

botsko November 10, 2020 4:37 PM

Prism 2 betas have proper utf8 encoding.

  • Status → Resolved

Update Issue

Add your comments to this issue!

Creating a free account will let you immediately report issues on this project, as well as comment on existing issues, save your filters, and more!!