Incorrect date time value

[jamestripp]

Good afternoon,

I'm getting the following error message when importing a data set via the import.php script:

"ERROR 1292 (22007) at line 10719: Incorrect datetime value: '0000-00-00 00:00:00' for column 'endtime' at row 1. There was a problem with importing data into TCAT."

I've exported it from two different servers and have gotten the same error message when trying to import. The data set was exported from the servers using the export.php script. All except 500 tweets are imported.

However, on the server showing the error, I can see the bin in the /analysis/ interface, but not the /capture/ interface. I intend to keep collecting data so being unable to view the bin in the /capture/ interface is a problem for me.

I would very much appreciate any help you can offer.

Best,

James

[dentoir]

Hi @jamestripp

TCAT uses the zero date to signify a bin in still running. As of MySQL 5.7, the database server does not allow these types of dates by default. We have to modify sql_mode to maintain the behavior. There is already some code in TCAT to handle this issue, but not yet sufficient for dumps apparantly.

Could you edit your dump (if it is not too huge!) and modify the header thus:

Change:

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

Into

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ALLOW_INVALID_DATES' */;

If it works as I suspect, we should modify export.php to modify the header lines.

[jamestripp]

Hi @dentoir ,

Thanks for getting back to me.

Editing the top of the file gives the following error when importing

ERROR 1292 (22007) at line 954: Incorrect datetime value: '0000-00-00 00:00:00' for column 'endtime' at row 1

Best,

James

[laurieresearch]

Hi @dentoir

I'm currently trying to migrate TCAT between two servers and am experiencing the same problems as James. Here's the error message when I try importing:

ERROR 1292 (22007) at line 337: Incorrect datetime value: '0000-00-00 00:00:00' for column 'endtime' at row 1

Any advice appreciated.

Best Laurie

[jamestripp]

Hi both,

I ended up with a sort of solution by backup up and restoring the data using pgadmin. Though it's not an ideal solution (as it might cause issues in the future...) it seems to have worked. Though I'd much prefer a solution using the TCAT script.

Best,

James

[dentoir]

Hi @jamestripp

I'm still convinced this is caused by the sql_mode. Could you try another method. Again, edit the SQL dump but modify it slightly differently. Before the first block of --- Table structure for table add the following SQL instruction:

SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

Related Stackoverflow issue: https://stackoverflow.com/questions/12932760/mysql-allow-invalid-dates-on-select/15960087

If this works I'll update the export script. There is also an option to overwrite the SQL_MODE using a (user) my.cnf file. I'm not sure which option takes precedence. If my solution does not work, perhaps your OS is overwriting the SQL_MODE. Then I'm curious what OS you are running.

[jamestripp]

Hi @dentoir

Should that be

/*!40101 SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

Placed before

-- -- Table structure for table LondonArea_tweets

Best,

James

[dentoir]

Hi @jamestripp

No, without the /*!40101 part. So on a single row just put this below.

SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

The place where you put it is correct.

[jamestripp]

Hi @dentoir

I got the following error

james@sisko:~$ sudo php /var/www/dmi-tcat/helpers/import.php LondonArea-import_tra-all-2018-04-03_09\:27.sql.gz [sudo] password for james: Query bin: LondonArea Now importing... ERROR 1292 (22007) at line 15719: Incorrect datetime value: '0000-00-00 00:00:00' for column 'starttime' at row 1 There was a problem with importing data into TCAT.

I'm running Ubuntu 16

No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 16.04.4 LTS Release: 16.04 Codename: xenial

And the head of my sql file is

-- Export DMI-TCAT: begin (2018-04-03_09:27) all -- Query Bins: -- 1. LondonArea

-- Export DMI-TCAT query bin: begin: LondonArea (import tra) -- MySQL dump 10.13 Distrib 5.6.31, for debian-linux-gnu (x86_64)

-- Host: localhost Database: twittercapture

---

-- Server version 5.6.31-0ubuntu0.15.10.1

/!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 LondonArea_tweets

/!40101 SET @saved_cs_client = @@character_set_client /; /!40101 SET character_set_client = utf8 /; SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; CREATE TABLE LondonArea_tweets ( id bigint(20) NOT NULL, created_at datetime NOT NULL, from_user_name varchar(255) NOT NULL,

[laurieresearch]

Hi @dentoir

As suggested I tried inserting: SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

Unfortunately, I have the same experience as James. The error message: ERROR 1292 (22007) at line 338: Incorrect datetime value: '0000-00-00 00:00:00' for column 'endtime' at row 1

I'm also running TCAT on Ubuntu 16.04.4 LTS.

Do you have any other suggestions?

Best Laurie

[oxygala]

Hi @dentoir, Is there a solution for this problem?

Thanks

[dentoir]

We need to modify the auto installer script to set this SQL_MODE in MySQL by default.

[dentoir]

I believe this is now fixed, at least on the auto-installer side. To fix this for existing installations, add the following lines to the [mysqld] section of your MySQL configuration file:

sql-mode="NO_AUTO_VALUE_ON_ZERO,ALLOW_INVALID_DATES"

Please feel free to re-open this issue if you feel this solution is incomplete.