Question SOLVED:Invalid datetime format: 1366 Incorrect ... w/ new install webtrees 2.2.1

I am able to update my v2.1.22 to v2.2.1 but as mentioned by @fisharebest, that does not solve the 4-byte problem.
When I do a new install of v2.2.1 and try to import a GEDCOM from v2.1.22, (which cannot not have any 4-byte characters) I get the error: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value:

Can someone advise how I can trouble shoot this? Attached is the full error message.

 [attachment=undefined]SQLSTATE22007Invaliddatetime.txt[/attachment]
 

a pdf-file does is not helpful. --> pack your gedcom - file into a zip and upload.

And to solve your 4Byte issue, you also have to create a new database (not only use the current wt - version)

@fisharebest post=107854 userid=525 response to @xmlf question 
Yes and no.
New installations using 2.2.0 will use utf8mb4.
There is still no automatic upgrade for old sites.
The difficult problem to solve is that changing existing tables can take a long time - longer than web-server timeout limits.

Thanks Franz for your response.
Yes, this was a new installation and the new database was generated using C-Panel's MySQL module.I am unsure if I am missing another step. The initial attached pdf was the error message. As requested, the GEDCOM I cannot upload to this new installation of v.2.2.1 is attached.

there is no error message when I import that gedcom.

ok. Thanks for checking. Let me work on various collations for the new databases and manually reinstall v.2.2.1.

Thanks for the follow-up

Franz,
Since you were able to install my working v.2.1.22 gedcom into a newly installed v.2.2.1 site, my problem is do these two Chinese characters work on the newly installed v2.2.1? Just replaced one of the given name characters with either of these old classical Chinese characters. The Unicode is in parenthesis:
𡖖 code=(U+21596)
𪊴 code=(U+2A2B4)

This is a test of concept that a newly installed v2.2.1 resolves the 4-byte issue.
FWIW I added these characters to the Royal Family Demo and get:

SQLSTATE[HY000]: General error: 3988 Conversion from collation utf8mb4_0900_ai_ci into utf8mb3_unicode_ci impossible for parameter (Connection: default, SQL: insert into `wtstable_change` (`gedcom_id`, `xref`, `old_gedcom`, `new_gedcom`, `status`, `user_id`) values (1, X242, , 0 @X242@ INDI
1 SEX U
1 NAME /廷英/𪊴𡖖
2 TYPE BIRTH
2 GIVN 𪊴𡖖
2 SPFX 廷英
1 CHAN
2 DATE 28 DEC 2024
3 TIME 15:28:12
2 _WT_USER moderator, pending, 5)) …/vendor/illuminate/database/Connection.php:825
#0 …/vendor/illuminate/database/Connection.php(779): Illuminate\Database\Connection->runQueryCallback()
Thanks in advance.

Ok, on trying to change the surname given name, there is the above error message:
SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\xAA\x8A\xB4\x0A2...' for column `dbs13580456`.`wt_change`.`new_gedcom` at row 1

I suppose that message willl also be thrown, for entering data into an empty database.

I am using a MariaDB 10.11 with  charset/Kollation der MySQL utf8mb4_unicode_ci .
(I can select other charset ...)
 

Even a manual change of a surname  given name to one of the chars gives:
Warning: #1366 Falscher string-Wert: '\x00\x02\x15\x96' für Feld '`dbs13580456`.`wt_name`.`n_givn` in Zeile 1  

Yes, I also tried to upgrade from v2.1.22 to v2.2.1, but I still can't solve the problem of characters like "𪊴𡖖" being included in the library. But a fresh install of v2.2.1 works.
I'm also waiting for the official upgrade solution.

> I'm also waiting for the official upgrade solution.

Here is an "unoffical" upgrade solution.

Can you make a SQL export of your database? e.g. use mysqldump or phpMyAdmin.

1) check the export is OK, by importing it into a new/empty database.

2) edit the .SQL file with a text editor.

Search/replace "utf8mb3" with "utf8mb4".

3) Import this SQL file into your original database.

If your SQL export does not include "drop table" commands, then you may need to delete all your tables first.

If your SQL export does not disable foreign key checks, you may need to add the line "SET FOREIGN_KEY_CHECKS := 0;" at the start of the file.

Hello,

I just checked my SQL-File to compare - and there I have those entries:

-- MariaDB dump 10.18 Distrib 10.5.8-MariaDB, for Linux ()
--
-- Host: localhost Database: Webtrees
--

---

-- Server version 10.5.8-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `wt_block`
--

DROP TABLE IF EXISTS `wt_block`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wt_block` (....................

Are all those ".........SET @OLD_........" correct?

My wife is chinese and I added her name in chinese characters - everything works.
But for testing purposes I tried the above signs 
𡖖 code=(U+21596) and
𪊴 code=(U+2A2B4)

And I get the same error:SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\xA1\x96\x96 \xF0...' for column `Webtrees`.`wt_change`.`new_gedcom` at row 1 (Connection: default, SQL: insert into `wt_change` (`gedcom_id`, `xref`, `old_gedcom`, `new_gedcom`, `status`, `user_id`) values (1, X10, 0 @X10@ INDI

If I try a new name like "also known as" and name it "Test /孙/冕" it will be stored correctly without error.
So somehow it seems to be related to those two signs given from the thread starter.I am not sure if this maybe can help somehow?
 

I did as Greg proposed and here you can see the result (as surname):
it-frese.de/webtrees/index.php?route=%2F...idual%2FXI57%2FMagda

Sorry the Link is no longer working, cause I had to make another site make running correctly.

But I have experianced that Gregs proposals give the solution.

Thank you @Franz. You give me hope. I just have trepidation in the ability to follow the steps.

Again, when I entered the two included characters 𡖖 and 𪊴 into the Current Release demo and the Latest Beta demo, I still get: 0]: General error: 3988 Conversion from collation utf8mb4_0900_ai_ci into utf8mb3_unicode_ci impossible for parameter (Connection: default, SQL: insert into `wtsvn_change` (`gedcom_id`, `xref`, `old_gedcom`, `new_gedcom`, `status`, `user_id`) values (1, i1, 0 @i1@ INDI

@WeT-Klb Most Chinese characters work fine. Just a few unusual utf8mb3 characters like I have shared don't work.

I need to 'steel' myself to try to follow Greg's unofficial fix. Like I said, I am glad @Franz was successful. Gives me encouragement.

Hi Doug. I just ran the "unofficial fix" on the demo site.

The result:

Thank you. I just need to 'geek' up and get a new install of 2.2.1 working and then apply the unofficial fix.

joeysun wrote:

Thank you. I just need to 'geek' up and get a new install of 2.2.1 working and then apply the unofficial fix.

Hi, did you succeed?

xiao wrote:

Hi, did you succeed?

Yes, it works. What took me so long was that my databases had latin1_swedish_ci collation. So I could not do what you and @Franz accomplished the new installations of 2.2.1 to even accept a  2.1.22 working GEDCOM. Because I didn't know mysql definitions well, it took a while to figure out what I needed to search and replace.

Now I feel comfortable fixing my databases after the automatic update of 2.1.22 => webtrees 2.2.1. I manage the back end for a group of non geeky genealogists whom I have introduced webtrees. This is NOT something they can do. 

Everything the many forum users here have contributed has been helpful. When @WeT-Klb posted his text output of his .sql file, I was able to cross check out outputs.

@fisharebest

I took a look on my database - seems I am quite far away from UTF8MB4, right?



I am not sure how to handle your "inofficial trick" in my case.
Can you tell my how to change everything through phpMyAdmin?
I am also unsure about some entries i sent some posts above.
If it is helpful I could send my SQL-Dump, too.
I am not really familiar with working in databases - especially when reading about primary key and foreign key things...
 

Wähle Exportieren / Choose Export (.sql)

Bearbeite die erzeugte Export-Datei / Edit the created export-file:
Ersetze alle UFT3 durch UFT4 / Replace all UFT3 with UTF4
Schreibe "SET FOREIGN_KEY_CHECKS := 0;" an den Anfang der sql-Datei (ohne ") / write "SET FOREIGN_KEY_CHECKS := 0;" at the beginning of the file, without "
In meiner sql-Datei war kein Drop Table aber das konnte beim Import angegeben werden. / There was no drop table, but possible on import.

Importieren / Import the file
Import und ausführen / Import and run

 

Hallo Franz,

das ist genau das, was ich nicht verstehe. In meiner SQL-Datei gibt es nicht mit UTF3 oder UTF8mb3.
Es ist schwer, sowas umzusetzen, wenn man keinen Bezug dazu hat.

Wie gesagt, meine Datei beginnt so:

-- MariaDB dump 10.18 Distrib 10.5.8-MariaDB, for Linux ()
--
-- Host: localhost Database: Webtrees
--

---

-- Server version 10.5.8-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `wt_block`
--

DROP TABLE IF EXISTS `wt_block`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wt_block` (
`block_id` int(11) NOT NULL AUTO_INCREMENT,
`gedcom_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`xref` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`location` enum('main','side') COLLATE utf8_unicode_ci DEFAULT NULL,
`block_order` int(11) NOT NULL,
`module_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`block_id`),
KEY `wt_block_module_name_index` (`module_name`),
KEY `wt_block_gedcom_id_index` (`gedcom_id`),
KEY `wt_block_user_id_index` (`user_id`),
CONSTRAINT `wt_block_gedcom_id_foreign` FOREIGN KEY (`gedcom_id`) REFERENCES `wt_gedcom` (`gedcom_id`),
CONSTRAINT `wt_block_module_name_foreign` FOREIGN KEY (`module_name`) REFERENCES `wt_module` (`module_name`),
CONSTRAINT `wt_block_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `wt_user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=134 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `wt_block`
--

LOCK TABLES `wt_block` WRITE;
/*!40000 ALTER TABLE `wt_block` DISABLE KEYS */;
INSERT INTO `wt_block` VALUES (1,-1,NULL,NULL,'main',0,'gedcom_stats'),(2,-1,NULL,NULL,'main',1,'gedcom_news'),(3,-1,NULL,NULL,'main',2,'gedcom_favorites'),(4,-1,NULL,NULL,'main',3,'review_changes'),(5,-1,NULL,NULL,'side',0,'gedcom_block'),(7,-1,NULL,NULL,'side',1,'todays_events'),(8,-1,NULL,NULL,'side',3,'logged_in'),(10,1,NULL,NULL,'main',0,'gedcom_news'),(11,1,NULL,NULL,'main',1,'gedcom_favorites'),(12,1,NULL,NULL,'main',2,'review_changes'),(13,1,NULL,NULL,'side',0,'gedcom_block'),(15,1,NULL,NULL,'side',1,'todays_events'),(16,1,NULL,NULL,'side',2,'logged_in'),(24,NULL,-1,NULL,'main',0,'todays_events'),(25,NULL,-1,NULL,'main',1,'user_messages'),(26,NULL,-1,NULL,'main',2,'user_favorites'),(27,NULL,-1,NULL,'side',0,'user_welcome'),(29,NULL,-1,NULL,'side',1,'upcoming_events'),(30,NULL,-1,NULL,'side',2,'logged_in'),(31,NULL,1,NULL,'main',0,'todays_events'),(32,NULL,1,NULL,'main',1,'user_messages'),(34,NULL,1,NULL,'side',0,'user_welcome'),(36,NULL,1,NULL,'side',1,'upcoming_events'),(37,NULL,1,NULL,'main',2,'logged_in'),(45,NULL,3,NULL,'main',1,'todays_events'),(46,NULL,3,NULL,'main',2,'user_messages'),(47,NULL,3,NULL,'main',3,'user_favorites'),(48,NULL,3,NULL,'side',1,'user_welcome'),(49,NULL,3,NULL,'side',2,'random_media'),(50,NULL,3,NULL,'side',3,'upcoming_events'),(51,NULL,3,NULL,'side',4,'logged_in'),(52,NULL,4,NULL,'main',1,'todays_events'),(53,NULL,4,NULL,'main',2,'user_messages'),(54,NULL,4,NULL,'main',3,'user_favorites'),(55,NULL,4,NULL,'side',1,'user_welcome'),(56,NULL,4,NULL,'side',2,'random_media'),(57,NULL,4,NULL,'side',3,'upcoming_events'),(58,NULL,4,NULL,'side',4,'logged_in'),(59,NULL,6,NULL,'main',1,'todays_events'),(60,NULL,6,NULL,'main',2,'user_messages'),(61,NULL,6,NULL,'main',3,'user_favorites'),(62,NULL,6,NULL,'side',1,'user_welcome'),(63,NULL,6,NULL,'side',2,'random_media'),(64,NULL,6,NULL,'side',3,'upcoming_events'),(65,NULL,6,NULL,'side',4,'logged_in'),(66,NULL,8,NULL,'main',1,'todays_events'),(67,NULL,8,NULL,'main',2,'user_messages'),(68,NULL,8,NULL,'main',3,'user_favorites'),(69,NULL,8,NULL,'side',1,'user_welcome'),(70,NULL,8,NULL,'side',2,'random_media'),(71,NULL,8,NULL,'side',3,'upcoming_events'),(72,NULL,8,NULL,'side',4,'logged_in'),(74,-1,NULL,NULL,'side',2,'random_media'),(76,NULL,9,NULL,'main',0,'todays_events'),(77,NULL,9,NULL,'main',1,'user_messages'),(78,NULL,9,NULL,'main',2,'user_favorites'),(79,NULL,9,NULL,'side',0,'user_welcome'),(80,NULL,9,NULL,'side',1,'upcoming_events'),(81,NULL,9,NULL,'side',2,'logged_in'),(83,NULL,10,NULL,'main',0,'todays_events'),(84,NULL,10,NULL,'main',1,'user_messages'),(85,NULL,10,NULL,'main',2,'user_favorites'),(86,NULL,10,NULL,'side',0,'user_welcome'),(87,NULL,10,NULL,'side',1,'upcoming_events'),(88,NULL,10,NULL,'side',2,'logged_in'),(89,NULL,7,NULL,'main',0,'todays_events'),(90,NULL,7,NULL,'main',1,'user_messages'),(91,NULL,7,NULL,'main',2,'user_favorites'),(92,NULL,7,NULL,'side',0,'user_welcome'),(93,NULL,7,NULL,'side',1,'upcoming_events'),(94,NULL,7,NULL,'side',2,'logged_in'),(96,NULL,11,NULL,'main',0,'todays_events'),(97,NULL,11,NULL,'main',1,'user_messages'),(98,NULL,11,NULL,'main',2,'user_favorites'),(99,NULL,11,NULL,'side',0,'user_welcome'),(100,NULL,11,NULL,'side',1,'upcoming_events'),(101,NULL,11,NULL,'side',2,'logged_in'),(102,NULL,13,NULL,'main',0,'todays_events'),(103,NULL,13,NULL,'main',1,'user_messages'),(104,NULL,13,NULL,'main',2,'user_favorites'),(105,NULL,13,NULL,'side',0,'user_welcome'),(106,NULL,13,NULL,'side',1,'upcoming_events'),(107,NULL,13,NULL,'side',2,'logged_in'),(108,NULL,14,NULL,'main',0,'todays_events'),(109,NULL,14,NULL,'main',1,'user_messages'),(110,NULL,14,NULL,'main',2,'user_favorites'),(111,NULL,14,NULL,'side',0,'user_welcome'),(112,NULL,14,NULL,'side',1,'upcoming_events'),(113,NULL,14,NULL,'side',2,'logged_in'),(115,NULL,15,NULL,'main',0,'todays_events'),(116,NULL,15,NULL,'main',1,'user_messages'),(117,NULL,15,NULL,'main',2,'user_favorites'),(118,NULL,15,NULL,'side',0,'user_welcome'),(119,NULL,15,NULL,'side',1,'upcoming_events'),(120,NULL,15,NULL,'side',2,'logged_in'),(121,NULL,16,NULL,'main',0,'todays_events'),(122,NULL,16,NULL,'main',1,'user_messages'),(123,NULL,16,NULL,'main',2,'user_favorites'),(124,NULL,16,NULL,'side',0,'user_welcome'),(125,NULL,16,NULL,'side',1,'upcoming_events'),(126,NULL,16,NULL,'side',2,'logged_in');
/*!40000 ALTER TABLE `wt_block` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `wt_block_setting`
--

DROP TABLE IF EXISTS `wt_block_setting`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wt_block_setting` (
`block_id` int(11) NOT NULL,
`setting_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`setting_value` longtext COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`block_id`,`setting_name`),
CONSTRAINT `wt_block_setting_block_id_foreign` FOREIGN KEY (`block_id`) REFERENCES `wt_block` (`block_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `wt_block_setting`
--

LOCK TABLES `wt_block_setting` WRITE;
/*!40000 ALTER TABLE `wt_block_setting` DISABLE KEYS */;
/*!40000 ALTER TABLE `wt_block_setting` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `wt_change`


Ich habe keine Ahnung, was z.B. dieses
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
bedeutet und was ich mit den Drop-Einträgen usw. tun muß.

Würde es auch funktionieren, wenn ich in phpMyAdmin einfach die Kollation auf utf8mb4_unicode_ci ändere und auf alle columns usw. anwende?