AW: mySQL 8 destroyes umlaute

[toolbook at kestner.de]

Thank you Matthias,

because it is an existing db and years old LC programs in different versions out at the customers, I can't modify my old LC programs out there.

I now have read that uft8 gets deprecated in mySQL 8 at some time, so that all new and migrated mySQL 8 databases gets by default uft8mb4 (4 byte chars), which LiveCode obviously doesn't like and can't display anymore the umlaute of my existing db. Perhaps everything would work with LC, when creating a new db and storing the data from the beginning in utf8mb4, but that’s not my use case. My live db has as the server character set "latin1" and as the db character set "utf8", which were both switched to utf8mb4 on mySQL 8.

Now I am looking for a solution either in the PHPs, which are called from LC and connect to the db, or changing the db itself.
Overwriting the charset in the PHP by: header ( 'Content-Type: text/html; Charset=latin1' ); doesn't affect the output to LiveCode at all, the umlaute stay corrupted. It seems that the settings of the db have priority.

I have read that you can alter the charset of a database and/or tables (ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;) . I am not good in handling databases, so I am not sure yet
- if this changes just the default value for new db entries or if that changes also the existing db content
- even if this would work, this wouldn't be a long lasting solution, because uft8 gets deprecated in future and I need again another solution.

Anybody out here who is good in working with databases / mysql and has any idea how to repair this situation?

Thanks
Tiemo



-----Ursprüngliche Nachricht-----
Von: use-livecode <use-livecode-bounces at lists.runrev.com> Im Auftrag von matthias rebbe via use-livecode
Gesendet: Mittwoch, 7. Juli 2021 20:55
An: How to use LiveCode <use-livecode at lists.runrev.com>
Cc: matthias_livecode_150811 at m-r-d.de
Betreff: Re: mySQL 8

Tiemo,

if i remember right this should be solving your issue.

Before you write to or read from the db execute the following command

revExecuteSQL  databaseID, "SET NAMES 'utf8'"
After that try to fetch data.

The retrieved data has then to be decoded with

textDecode(tRetrievedData,”UTF8”) 

e.g.
put textDecode(tRetrievedData,"UTF8") into fld "Name"


Regards,

Matthias

> Am 07.07.2021 um 12:02 schrieb Tiemo via use-livecode <use-livecode at lists.runrev.com>:
> 
> Hello folks,
> 
> 
> 
> I have a DB hosted on AWS and need to move it from mySQL 5.6 to 5.7 
> because of AWS restrictions.
> 
> In this context I am doing tests and also tried to see what my 
> programs (LiveCode > PHP > mySQL) say about mySQL 8.
> 
> For this I imported a current 5.6 dump into a mySQL 5.7 and into a 8.0 
> test DB.
> 
> 
> 
> With mySQL 5.7 everything looks so far well.
> 
> With mySQL 8 all umlauts come back destroyed in DB queries in my 
> LiveCode program.
> 
> 
> 
> When I look into the DB with mySQL Workbench, the umlauts are ok with 
> 5.7 as well as with 8.0. Only as soon as I get them into my LC program, they break.
> 
> 
> 
> Is here anybody, who has read about changes of handling the charset or 
> collations in mySQL databases with mySQL 8?
> 
> And any idea, where I should try to look for a fix? DB-collations/charsets?
> PHP? LiveCode?
> 
> 
> 
> We are writing year 2021! and this is the third break of existing 
> functions handling umlauts within months after LC, macOS and now mySQL
> 
> 
> 
> Thanks for any ideas
> 
> 
> 
> Tiemo
> 
> 
> 
> 
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
use-livecode at lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode