View Issue Details

IDProjectCategoryView StatusLast Update
15912Bug reportsInstallationpublic2020-03-24 11:29
Reporterrustam82 Assigned Toc_schmitz  
PrioritynoneSeverityminor 
Status closedResolutionfixed 
Product Version4.1.6 
Fixed in Version4.1.12 
Summary15912: Error upgrading from 3.22.5+ to 4.1.6
DescriptionI get an error by upgrating from 3.22 to 4.1.6

"Invalid datetime format: 1366 Incorrect string value: '\xE2\x86\x94 5=...' for column `limesurv_test`.`lime_question_l10ns`.`help` at row 839."


Web server
cpsrvd 11.84.0.21
Database client version: libmysql - 5.6.43
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 7.3.6
Steps To ReproduceIn the begining of installation

when I click on 'Start database upgrade' I get this error
"Invalid datetime format: 1366 Incorrect string value: '\xE2\x86\x94 5=...' for column `limesurv_test`.`lime_question_l10ns`.`help` at row 839."

TagsNo tags attached.
Complete LimeSurvey version number (& build)Version 3.22.5+200218
I will donate to the project if issue is resolvedNo
Browser
Database & DB-VersionDatabase client version: libmysql - 5.6.43
Server OS (if known)
Webserver software & version (if known)
PHP VersionPHP version: 7.3.6

Activities

rustam82

rustam82

2020-02-24 16:36

reporter  

image.png (35,383 bytes)   
image.png (35,383 bytes)   
image-2.png (41,887 bytes)   
image-2.png (41,887 bytes)   
LimeSurvey VIVES.mhtml (27,064 bytes)
ollehar

ollehar

2020-02-24 16:38

administrator   ~56176

Thanks for reporting, we'll have a look.
rustam82

rustam82

2020-02-25 11:14

reporter   ~56197

I can upgrade to new(empty) DB but not to my DB which has about 1400 surveys
ollehar

ollehar

2020-02-25 13:17

administrator   ~56198

Yes, one of the values does not fit.
DenisChenu

DenisChenu

2020-02-27 10:01

developer   ~56219

Ā«Invalid datetime formatĀ» ? For i10n->help ????
robaato

robaato

2020-03-09 15:04

reporter   ~56371

Same here. Error is similar:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xC4\x85ca l...' for column `lsurvey`.`lime_question_l10ns`.`question` at row 1

After an unsuccessful upgrade, I have a lime_question_l10ns table left (expected) with incorrect encoding (latin1). This may be the source of the problem.

> -- Table structure for table `lime_question_l10ns`
> --
>
> DROP TABLE IF EXISTS `lime_question_l10ns`;
> /*!40101 SET @saved_cs_client = @@character_set_client */;
> /*!40101 SET character_set_client = utf8 */;
> CREATE TABLE `lime_question_l10ns` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `qid` int(11) NOT NULL,
> `question` text NOT NULL,
> `help` text DEFAULT NULL,
> `language` varchar(20) NOT NULL,
> PRIMARY KEY (`id`),
> UNIQUE KEY `lime_idx1_question_l10ns` (`qid`,`language`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
> /*!40101 SET character_set_client = @saved_cs_client */;
>
robaato

robaato

2020-03-09 15:32

reporter   ~56372

Ok. My bad - database was from very old lime upgraded year by year - the error can be fixed by properly setting database-wide character set and collation (which will be applied for any new table).

ALTER DATABASE lsurvey CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Upgraded without further problems.
DenisChenu

DenisChenu

2020-03-09 15:36

developer   ~56373

> ALTER DATABASE lsurvey CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Then must be done before upgrade, or must be forced in upgrade system.

Thanks for the information.
rustam82

rustam82

2020-03-10 10:03

reporter   ~56389

Thanks a lot! It is OK now.
I had also old DB from 1.x version allways upgraded to next version until 3.x , so the reason in that
c_schmitz

c_schmitz

2020-03-17 00:03

administrator   ~56586

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=29707
lime_release_bot

lime_release_bot

2020-03-24 11:29

administrator   ~56751

Fixed in Release 4.1.12+200324

Related Changesets

LimeSurvey: master 5ce4587b

2020-03-17 00:02:48

c_schmitz

Details Diff
Fixed issue 15912: Error updating database on upgrade from 3.x
Dev Removed references to obsolete DB driver mysqli
Affected Issues
15912
mod - application/helpers/update/updatedb_helper.php Diff File

Issue History

Date Modified Username Field Change
2020-02-24 16:36 rustam82 New Issue
2020-02-24 16:36 rustam82 File Added: image.png
2020-02-24 16:36 rustam82 File Added: image-2.png
2020-02-24 16:36 rustam82 File Added: LimeSurvey VIVES.mhtml
2020-02-24 16:38 ollehar Note Added: 56176
2020-02-25 11:14 rustam82 Note Added: 56197
2020-02-25 13:17 ollehar Note Added: 56198
2020-02-27 10:01 DenisChenu Note Added: 56219
2020-03-09 15:04 robaato Note Added: 56371
2020-03-09 15:32 robaato Note Added: 56372
2020-03-09 15:36 DenisChenu Note Added: 56373
2020-03-10 10:03 rustam82 Note Added: 56389
2020-03-17 00:03 c_schmitz Changeset attached => LimeSurvey master 5ce4587b
2020-03-17 00:03 c_schmitz Note Added: 56586
2020-03-17 00:03 c_schmitz Assigned To => c_schmitz
2020-03-17 00:03 c_schmitz Resolution open => fixed
2020-03-17 00:06 c_schmitz Status new => resolved
2020-03-17 00:06 c_schmitz Fixed in Version => 4.1.12
2020-03-24 11:29 lime_release_bot Note Added: 56751
2020-03-24 11:29 lime_release_bot Status resolved => closed