Jump to content

Resolved - 5.2.2 Error Log on Accessing Customer List


Dirty Butter

Recommended Posts

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));
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

"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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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));
Link to comment
Share on other sites

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!!

Link to comment
Share on other sites

"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.

Link to comment
Share on other sites

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- 

 

Link to comment
Share on other sites

"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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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);
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...