Dirty Butter Posted April 24, 2013 Share Posted April 24, 2013 I've just upgraded one site to 5.2.2 from 5.1.5 and checking things out. I'm getting this error message when I open the Customer List in Admin and also when I click on a specific customer. [24-Apr-2013 17:53:34] PHP Warning: json_encode() [<a href='http://docs.php.net/manual/en/function.json-encode.php'>function.json-encode.php</a>]: Invalid UTF-8 sequence in argument in /home/estates/public_html/admin/sources/customers.index.inc.php on line 488 Line 488 of customers.index.inc.php doesn't help me any, but maybe it will mean something to someone else: $GLOBALS['smarty']->assign('JSON_STATE', json_encode($jsonArray)); Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 25, 2013 Share Posted April 25, 2013 I don't know why the JSON_STATE is in this template at this time (just viewing a list), but the problem is that one of the state/counties in your database that has accented characters is not coded correctly. Later, when adding/editing a customer, which allows for adding or editing an address, the drop-down selectors for the state/county is populated with the entire geographic database (which they should). But one of those state/county names is not conforming to UTF-8. Any database upgrades from CC515 would go through CC520, CC521, and CC522 scripts. None are adding any new names with accented characters. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 25, 2013 Author Share Posted April 25, 2013 I did a Repair on the Database, hoping that would fix it or maybe give some indication where the trouble is - no luck. So how do I find where the bad one is? I know there is a long list of code number changes in the File Difference report - that must be where it happened. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 25, 2013 Author Share Posted April 25, 2013 I think I found the problem. Comparing my 5.1.4 site database and the newly upgraded 5.2.2 one - country and country_d in the _order_summary tables Structure are different. 5.1.4 has varchar(200) utf8_unicode_ci BUT 5.2.2 has smallint(3) NOTHING in Collation and Unsigned as an Attribute It MAY be significant that I could not get the automatic upgrade process to work. It stayed in a processing state, but never moved to another screen. So I stopped it and went through a manual upgrade process. I still have my Setup folder, if there's anything there you want me to check. Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 25, 2013 Share Posted April 25, 2013 You said you had upgraded a CC515 site. So, if the columns in the CC_summary table did change from VARCHAR to SMALLINT, it would have been at the CC514 to CC515 stage. Anyway, you may need to look at the CC_geo_zone table in phpMyAdmin and look carefully for any name that should reasonably look like it should have an accented character, but instead has a capital A with either a circle or circumflex above it. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 25, 2013 Author Share Posted April 25, 2013 So this error was probably already been there, but I didn't catch it until now. I checked the geo-country and zone tables and there are no odd looking characters. Should I try changing the structure manually back to varchar(200) utf8_unicode_ci to see if that fixes it? Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 25, 2013 Share Posted April 25, 2013 "I could not get the automatic upgrade process to work. It stayed in a processing state." Do you know if your server is running suPHP or some other security addon that would require your chmod value to be 755 instead of 775?The changed structure you noticed was in the CC_order_summary table. We are looking in the CC_geo_zone table. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 25, 2013 Author Share Posted April 25, 2013 No, I'm not running suPHP and have never had trouble with chmod issues before on upgrading. I did not find anything that had odd characters in geo_zone table. Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 26, 2013 Share Posted April 26, 2013 Do you still have any records in CC_geo_zone where the country_id is 255? There is no differences between CC515 and CC522 code that gets country names and their state/county names. So there must be a difference in the data between CC_geo_country and CC_geo_zone. If a CC_geo_zone record has a `country_id` value that does not exist in CC_geo_country: Say that West Yorkshire (should be at the bottom of the CC_geo_zone table) still has a country_id of 255 (should be 225), but, in CC_geo_country there is no id of 255, then $country_format (line 484 in customers.index.inc.php) will be false, and that then is a bad array element in $jsonArray. These are the only currently valid country_id values in CC_geo_zone, sorted by value: 13,14,38,80,103,150,199,206,225,226 In CC_geo_country, the ids range from 1 to 245 with the following missing numbers: 6,11,120,189,240,241,242,243 Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 26, 2013 Author Share Posted April 26, 2013 I checked all the number ranges and missing numbers you listed for geo_zone and country_id, and they are all correct. I'm at a loss. Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 26, 2013 Share Posted April 26, 2013 Where do you see this error message? Does it crash PHP and you get nothing but this in the browser? Or is the message in the Debug section and you still get a page displayed? If you get a page displayed, have the browser show you the HTML source. Near the bottom (but above the debug section), find: <script type="text/javascript"> var county_list If there is no list, then the json_encode() function failed due to bad input. As an experiment, try this: At the bottom of customer.index.inc.php, on a new line after the $page_content statement, add this:// Returns true if $string is valid UTF-8 and false otherwise. function is_utf8($string) { // From http://w3.org/International/questions/qa-forms-utf-8.html return preg_match('%^(?: [x09x0Ax0Dx20-x7E] # ASCII | [xC2-xDF][x80-xBF] # non-overlong 2-byte | xE0[xA0-xBF][x80-xBF] # excluding overlongs | [xE1-xECxEExEF][x80-xBF]{2} # straight 3-byte | xED[x80-x9F][x80-xBF] # excluding surrogates | xF0[x90-xBF][x80-xBF]{2} # planes 1-3 | [xF1-xF3][x80-xBF]{3} # planes 4-15 | xF4[x80-x8F][x80-xBF]{2} # plane 16 )*$%xs', $string); } // function is_utf8 Then, a few lines above, find the smarty->assign statement. The line above that is just a closing brace. Insert this in front of that brace:if ( !is_utf8($country_format) || !is_utf8($state['id']) || !is_utf8($state['name']) ) { echo $country_format.":".$state['id'].":".$state['name']."<br />";} (There are now two closing braces at the end of that line.) Get the Customer List page. If there is something that is not proper UTF-8 encoded, this should reveal it as text at the top of the web page. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 26, 2013 Author Share Posted April 26, 2013 Where do you see this error message? Does it crash PHP and you get nothing but this in the browser? Or is the message in the Debug section and you still get a page displayed? The message is in the error.log, and the Admin Customer list loads and appears to act normally. If you get a page displayed, have the browser show you the HTML source. Near the bottom (but above the debug section), find: <script type="text/javascript"> var county_list I see the lists of countries and states in the Source Files, and did find the var county_list code. I did not see a similar code for the countries, however. Even though I see the lists in the source code, I'm going to try the rest of your code suggestion for customer.index.inc.php, just to see what I get. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 26, 2013 Author Share Posted April 26, 2013 customers.index.inc.php Well, if I followed your directions correctly for the customer.index.inc.php file - no error text showed up, and I did clear the cache before trying it. Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 27, 2013 Share Posted April 27, 2013 The line should be here: $jsonArray[$country_format][] = array('id' => $state['id'], 'name' => $state['name']); if ( !is_utf8($country_format) || !is_utf8($state['id']) || !is_utf8($state['name']) ) { echo $country_format.":".$state['id'].":".$state['name']."<br />";} } $GLOBALS['smarty']->assign('JSON_STATE', json_encode($jsonArray)); Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 27, 2013 Author Share Posted April 27, 2013 OK - fixed that and I DO get a list of what my browser says is German and wants to translate it for me - ALL have the weird diamond character in them. Here's a screenshot, but it repeats for a long list of the same info way down the page. I checked the geo_zone table for Baden-Wortenburg - one of the zones on the list. Instead of a word, it's a set of numbers, and I notice there are more in the list like that. I notice that the FUNCTION is designated as UNHEX in the 5.2.2 table. AND, on checking my 5.1.4 plushcatalog database I find those same zones have odd looks, too - Baden-Württemberg - BUT there is NO designation at all in the FUNCTION column. So now that I know which zones need fixing, what do I change? Is it a structure problem or strictly a matter of looking up the correct spellings and correcting each one individually? I tried manually changing to Baden-Wurttemberg on the estates 5.2.2 geo_zone, but it leaves it blank unless I take off the UNHEX function. Even the zones that are correct have UNHEX in the 5.2.2 table. I am SO far over my head with this! Thank goodness for your help, Bsmither!! Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 27, 2013 Share Posted April 27, 2013 "I get a list of what my browser says is German and wants to translate it for me." What??? What browser are you using that gives you that? It's strange that the list is repeating several times. But I will look into that later. These are the columns of the CC_geo_zone table: id, country_id, abbrev, name If what you say implies there is another column in this table named FUNCTION, then something is seriously wrong. Or, if what you say means that phpMyAdmin is providing to you a drop-down next to the field to either actually change the data, or to present that data (without actually changing it) in a different way, then we are good with that. In the drop-down selector (if what I conclude is true about phpMyAdmin), is there a -none- or a raw, or a choice that says to show the data in the table's encoding. Also, check the encoding of the table to make sure it is utf8. When you are viewing this data, and you see the hex sequence, please copy that hex sequence here in a reply for one of the county's names. I fear that your database tables and/or their contents were not properly, if at all, converted to utf8. Is there a selection in the phpMyAdmin drop-down to convert to utf8? For "Karnten" (not spelled in utf8), the HEX sequence should be: 4B C3 A4 72 6E 74 65 6E If it is not, then your table is definitely not utf8. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 27, 2013 Author Share Posted April 27, 2013 I'm sorry I'm doing such a poor job of explaining what I see. When I go to Edit one of the wrong zones in the geo_zone table I see this structure: Thuringen, Germany WRONG Alabama, USA CORRECT I also noticed that the 5.2.2 table shows varbinary types, but the 5.1.4 table shows varchar types. THERE DOES APPEAR TO BE A WAY TO FORCE THE UTF8 COLLATION- Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 27, 2013 Author Share Posted April 27, 2013 "I get a list of what my browser says is German and wants to translate it for me." What??? What browser are you using that gives you that? Chrome It's strange that the list is repeating several times. But I will look into that later. These are the columns of the CC_geo_zone table: id, country_id, abbrev, name If what you say implies there is another column in this table named FUNCTION, then something is seriously wrong. Not another column, but when you go to edit the data you see an attribute? column called function - that's where the unhex is. Or, if what you say means that phpMyAdmin is providing to you a drop-down next to the field to either actually change the data, or to present that data (without actually changing it) in a different way, then we are good with that. In the drop-down selector (if what I conclude is true about phpMyAdmin), is there a -none- or a raw, or a choice that says to show the data in the table's encoding. Also, check the encoding of the table to make sure it is utf8. geo_zone table SAYS it's utf8 Collation When you are viewing this data, and you see the hex sequence, please copy that hex sequence here in a reply for one of the county's names. I fear that your database tables and/or their contents were not properly, if at all, converted to utf8. Is there a selection in the phpMyAdmin drop-down to convert to utf8? For "Karnten" (not spelled in utf8), the HEX sequence should be: I see 4B C3 A4 72 6E 74 65 6E If it is not, then your table is definitely not utf8. Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 27, 2013 Share Posted April 27, 2013 Well, the table may be utf8, but the contents are not. In the FUNCTION drop-down, is there a "Convert to UTF-8"? I know I saw a routine in one of the CC setup files that created all new tables with UTF-8 characterset (e.g., utf8_CubeCart_history), copied all the contents of all the existing tables to these new tables, deleted the old tables, then renamed the new tables back to the standard names. But I can't seem to find that right now. If no convert function on individual data, then directly editing the hex values will work as there is only a half-dozen or so data items to do. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 27, 2013 Author Share Posted April 27, 2013 There is nothing in the function drop down that looks like convert to utf8. As you can see, I need very detailed directions to follow to make the corrections to the messed up zones. Sorry to be such a dunce about all this. Do I just type in 4B C3 A4 72 6E 74 65 6E for Karnten and leave the UNHEX as is? Do I need the spaces, as the ones in my table don't have any? I can manually edit the names in the 5.1.4 plushcatalog table that have the odd characters, so if I fix those now maybe I can keep this mess from happening when I upgrade that site to 5.2.2. Does that sound safe to do? Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 27, 2013 Share Posted April 27, 2013 There is nothing in the function drop down that looks like convert to utf8. Darn. As you can see, I need very detailed directions to follow to make the corrections to the messed up zones. Sorry to be such a dunce about all this. Do I just type in 4B C3 A4 72 6E 74 65 6E for Karnten and leave the UNHEX as is? Do I need the spaces, as the ones in my table don't have any? I can't give you detailed instructions regarding how to use phpMyAdmin. You can try replacing the e4 with c3a4 without changing the UNHEX and see what happens. Baden-Württemberg: 426164656e2d57c3bc727474656d62657267 Thüringen: 5468c3bc72696e67656e NiederÖsterreich: 4e6965646572c396737465727265696368 OberÖsterreich: 4f626572c396737465727265696368 Graubünden: 4772617562c3bc6e64656e A Coruña: 4120436f7275c3b161 Zürich: 5ac3bc72696368 I can manually edit the names in the 5.1.4 plushcatalog table that have the odd characters, so if I fix those now maybe I can keep this mess from happening when I upgrade that site to 5.2.2. Does that sound safe to do? I have no idea. The odd characters are probably what should be there. If there is a HEX function, see if the hex numbers match what is provided above. Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 27, 2013 Share Posted April 27, 2013 I also want you to make this edit: in the file /classes/db/mysql.class.php, at the bottom. Change: @mysql_query($this->_db_connect_id, "SET NAMES 'utf8'"); @mysql_query($this->_db_connect_id, "SET CHARACTER SET 'utf8'");to: @mysql_query("SET NAMES 'utf8'", $this->_db_connect_id); @mysql_query("SET CHARACTER SET 'utf8'", $this->_db_connect_id); Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 27, 2013 Author Share Posted April 27, 2013 I was able to fix the estates geo_zone table by editing each one showing a number where it should have shown the name. I had to take the UNHEX attribute off, type in the actual word in name, and then save. When done, the name shows on the table. When I went to Edit again - the UNHEX was back on and the word now appeared as the number in the edit screen, but still shows as the word on the table screen. ALL have now been edited, and the debug list no longer showed. So I removed your debug code from customers.index.inc.php, and it works perfectly. NO error message! There is no way to thank you enough for your patient help!!! NOW - What is the purpose of the change to the query code that you've asked me to make? Quote Link to comment Share on other sites More sharing options...
bsmither Posted April 27, 2013 Share Posted April 27, 2013 It's in the wrong format. Devellion denies that this is so. These statements are probably supposed to tell the PHP mysql connector and the database to speak in utf-8 if they don't already. I do not know if there is a glitch waiting to happen, but why take the chance. Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted April 27, 2013 Author Share Posted April 27, 2013 Edit is made and marked as your version. Again, thank you!!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.