Error: Lists of Families or Individuals

[branb]

Hello,

I'm experiencing errors when trying to access the Lists for either Familie_s or _Individuals.

When I try to access the Families List the following error occurs:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'' in /imedia/users/WEBSITE/htdocs/phpgedview/includes/classes/class_pgv_db.php:830 Stack trace: #0 /imedia/users/WEBSITE/htdocs/phpgedview/includes/classes/class_pgv_db.php(830): PDOStatement->execute() #1 [internal function]: PGV_DBStatement->__call(Array, Array) #2 /imedia/users/WEBSITE/htdocs/phpgedview/includes/classes/class_pgv_db.php(897): PGV_DBStatement->execute('execute', Array) #3 /imedia/users/WEBSITE/htdocs/phpgedview/includes/functions/functions_db.php(280): PGV_DBStatement->fetchOneColumn() #4 /imedia/users/WEBSITE/htdocs/phpgedview/famlist.php(86): get_indilist_salpha() #5 {main} thrown in /imedia/users/WEBSITE/htdocs/phpgedview/includes/classes/class_pgv_db.php  on line 830

And when I try to access the Individuals List I receive this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'' in /imedia/users/WEBSITE/htdocs/phpgedview/includes/classes/class_pgv_db.php:830 Stack trace: #0 /imedia/users/WEBSITE/htdocs/phpgedview/includes/classes/class_pgv_db.php(830): PDOStatement->execute() #1 [internal function]: PGV_DBStatement->__call(Array, Array) #2 /imedia/users/WEBSITE/htdocs/phpgedview/includes/classes/class_pgv_db.php(897): PGV_DBStatement->execute('execute', Array) #3 /imedia/users/WEBSITE/htdocs/phpgedview/includes/functions/functions_db.php(280): PGV_DBStatement->fetchOneColumn() #4 /imedia/users/WEBSITE/htdocs/phpgedview/indilist.php(86): get_indilist_salpha() #5 {main} thrown in /imedia/users/WEBSITE/htdocs/phpgedview/includes/classes/class_pgv_db.php  on line 830

Any assistance would be greatly appreciated.

Thank you for your valuable time,

-BRANB

[Victor H.]

Branb, what version of PGV are you using, and did you recently upgrade?

[Stephen Arnold]

branb
It would appear that you have mixed collations in your database (as you have been told by the error report (Latin1 vs utf8_unicode_ci). This will evident itself in many ways, including slow performance, especially in searching, and errors like you have seen. It will be necessary to use Greg's patch, export the table and reimport them with  the same and correct collation.
https://sourceforge.net/tracker/?func=detail&aid=2318005&group_id=55456&atid=477081

-Stephen

[branb]

Thank all for you quick response.  Before I found the search feature for this forum I posted this support request.

The good news is that after I discovered search and queried on my problem I found similar issues about collations.  So, I simply created a new database and ran the configuration again.  And, it worked! The lists now work correctly!  I'm going to check out Greg's patch as well.

So, thank you again for your assistance. It is greatly appreciated.

-Branb

[macalter]

Steven,
I know my collations in my databases are mixed. If I understand the instructions, I  use MyPHPAdmin to do export (backup), backup PGV too. But not clear on how to change to UTF-8

MySQL connection collation: UTF-8
Databases: latin1_swedish_ci

I'm going to need Polish names using proper characters & search so want to make the change.

[macalter]

Okay, now I'm more confused. I'm looking at the phyMyAdmin page and on that page it shows:

# Server version: 5.0.89-userstats-log
# Protocol version: 10
# Server: pgvdatabase.mydomain via TCP/IP
# User: admin@havok.dreamhost.com
# MySQL charset: UTF-8 Unicode (utf8)
# MySQL connection collation UTF8_unicode_ci

But the databases themselves show the latin1. Are those the ones to change?

[Greg Roach]

MySQL lets you specify collations in many, many different locations.  Each controls a slightly different aspect of the behaviour.

If you already have accented letters, then simply changing the collation will corrupt them.  Use the "import/export" tool from the patches tracker that was mentioned a few posts earlier.

1) export all your tables
2) change the USE_DB_COLLATION setting to "yes" in your site settings
3) import all your tables.
4) enjoy.