PS tourney HH import error: invalid "UTF8": 0xb6

[drgfire]

Hi, I'm getting the attached error when trying to import Stars tournament hand histories. I think these errors started before the 04g patch, and I thought the patch would fix it. (Or it might be an unrelated issue?)

I'm using Postgresql 8.2.7 on Gentoo Linux (same configuration for years). Has always worked flawlessy, well up until now. Note that it's only Stars failing, importing other sites still works.

Anyone has an idea? Does the problem lie within the HH-file or is there really an invalid byte in the database? How can I find out which record it is?
What I find strange is that "select * from players" works without complaining (psql shell on server)...

Thanks for any help!

*********************************
PT Version: 2.17.04g
Error Date: 08/07/2009 22:53
Database Error: 7
Error Text: Select error: SQLSTATE = 22021
ERROR: invalid byte sequence for encoding "UTF8": 0xb6;
Error while executing the query
DataObject: d_player_names
Syntax In Error: SELECT players.screen_name,
players.location,
0 as handle,
players.treeview_icon,
players.player_id,
players.main_site_id,
players.site_id,
players.alias_id,
players.hide_ind,
players.ring_player,
players.tourney_player,
upper(players.screen_name) as upper_name
FROM players
WHERE ( players.main_site_id = 2 ) OR
( players.player_id = 0 )

*********************************

*********************************
PT Version: 2.17.04g
Error Date: 08/07/2009 22:53
Database Error: 7
Error Text: SQLSTATE = 23505
ERROR: duplicate key violates unique constraint "player_idx_01";
Error while executing the query

No changes made to database.

INSERT INTO players ( screen_name, treeview_icon, player_id, main_site_id, hide_ind, ring_player, tourney_player ) VALUES ( 'uckma', 1, 270838, 2, 0, 0, 1 )
DataObject: d_player_names
Syntax In Error: INSERT INTO players ( screen_name, treeview_icon, player_id, main_site_id, hide_ind, ring_player, tourney_player ) VALUES ( 'uckma', 1, 270838, 2, 0, 0, 1 )
*********************************

[drgfire]

Hmm, it's upper() that provokes the error. "select upper(screen_name) from players;" fails...

[ptrack pat]

If this just started, it's probably the result of one of the most recent names added to the players table. I don't know how to tell which record it is though that's causing it...it's probably one of the names that has a strange character in it. Can you backup your database and let me know the size of the .backup file?

[drgfire]

Hi Pat, the dump is around 91 MiB (compressed).

Meanwhile I've found the records causing this. There are a few screen_names containing 0xb6. Should I just delete them? Which other tables have to be touched?

The more I think on it, it's more like a postgresql bug in upper(). Or at least it shouldn't have permitted inserting these UTF8 strings for they cannot be handled by the string functions...

[ptrack pat]

There are a bunch of other tables that could be affected. It might be better if you just used a SQL update statement to change the screen_name field for those particular players. Can you give me an example of some of the names that cause this? I may also just remove the use of UPPER in that SQL for PokerStars retrieves but I need to investigate that some more.

[drgfire]

Hmmm, what I now did was to export and import everything back into a new database and that seems to have fixed it. The player names still are (at least they look) the same and it works. Wonder what that was...

One thing I had to do however was to re-import all PS HHs from the first time the error appeared (was 2nd of August) onwards. Before that the tournament games display screwed up with the date filter. It hadn't shown anything before August.

However, it seems to work again. :)

[drgfire]

Sorry, back again. I might have been to quick.

As said, I've imported the HHs, everything looked ok, I posted the above. Then I have imported the results and it complained for a few player names (has always happend occasionaly) but finished. And now the old error is back. It seems as if the invalid UTF8 char (0xb6) comes from importing the tournament summary. Is this an email encoding issue? What can I do about it?

Pat, do you want the summary email?

[ptrack pat]

Yes, please send me the file you used to import using the online support system...
http://www.pokertracker.com/support/?cat_id=4

I'm guessing that PT is actually inserting a player name from that file that is causing the problem. It's either a player that isn't in the db yet, or it may be the player is in the DB but PT isn't finding it when it's searching to see if the player name already exists.

[drgfire]

Ok, opened a ticket and attached the tournament summary file. I have done some more tests and am very certain now that this file causes the error. I cannot understand it though - the file doesn't contain the 0xb6 at all, very strange. Hope you can find out something.

At least I've got my db up and working correctly again. Replacing the invalid screen_name chars was rather tricky. There are two valid UTF8 chars containg 0xb6 after all. These are 0xc2b6 (PILCROW SIGN) and 0xc3b6 (LATIN SMALL LETTER O WITH DIAERESIS), and from these the 0xb6 mustn't be replaced...

[ptrack pat]

After doing some investigation into my code, I see the only reason I use the UPPER SQL command was as a workaround for some Access database issues I was having (Access uses UCASE but the SQL is changed before retrieval to use the proper syntax for PostgreSQL) so that isn't even needed for PostgreSQL databases.

I created a 2.17.04H patch that removes the UPPER from the SQL. Give this a try...
http://s217900936.onlinehome.us/patch21704h.exe