0

I maintain a pretty old PHP system, which is interacting with a mysql database table Styles, which has latin1 encoding. The create syntax (with only the relevant column shown) is below:

CREATE TABLE `StylesTbl` (
  `styleName` varchar(40) COLLATE latin1_danish_ci NOT NULL,
) ENGINE=MyISAM AUTO_INCREMENT=21976 DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

As an example, an entry in the database for styleName is:

"Cityscape 20” black + Cityscape 24” wine".

Which on the production server appears as:

"Cityscape 20� black + Cityscape 24� wine"

The text however appears absolutely fine on my local instance of the server, which is running the same code. So I assume from this that the differences must lie in either the

  1. PHP version (But both server and my local instance are using 5.6.30)
  2. Mysql Version (Server uses 5.5, My local is 5.6 -- could this be affecting it??)

Other than the above, the code that is being run is the same, and the database content is the same (I define my local version of the production database using a mysqldump of the production database when I test).

I believe the reaspm issue begins in the first place is that we have a second database which has the names in UTF-8, and when a new style is made in the old database, information is retrieved from the newer database in UTF-8 and saved.

k4kuz0
  • 1,045
  • 1
  • 10
  • 24
  • Do you have different OS at Production & Local? – Rohit.007 May 12 '18 at 11:18
  • Maybe something goes wrong in local? Production server acts properly. – revo May 12 '18 at 11:31
  • Locally I am using MAMP with an Apache server, and the server is Ubuntu 14 with an Apache server. So yes, do you have an idea where this discrepancy could be occurring in relation to the OS? – k4kuz0 May 12 '18 at 11:31
  • @revo what do you mean? – k4kuz0 May 12 '18 at 11:40
  • Latin1 character set doesn't include `”`. So what you see in production server is right. – revo May 12 '18 at 11:44
  • @revo Then I'm not entirely sure what is happening on my local machine if I'm honest – k4kuz0 May 12 '18 at 11:49
  • @revo update: if I run mb_detect_encoding($string) on the name, I get "UTF-8" on my local machine but false on the server. Still not quite sure why my local machine is assuming it's UTF-8 though! – k4kuz0 May 12 '18 at 12:01
  • There is an hierarchy of defining character sets within DBMS or connection that one may override the other. Check for those here https://stackoverflow.com/a/24356370/1020526 – revo May 12 '18 at 12:09
  • See "black diamonds" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James May 16 '18 at 14:06

1 Answers1

1

Despite there being more problems with this database than was in the original question, my original question was answered when I checked the character_set_*variables on both the server and the local instance of MYSQL.

On the server almost all, for example, character_set_connectionwas set to latin1, whereas my local instance had character_set_connectionset to utf-8. This is where the discrepancy arose. Thank you for the comment replies.

k4kuz0
  • 1,045
  • 1
  • 10
  • 24