Incorrect datetime value:

[penguinspeaks]

Hello all

I moved my server to a new VPS box. I am trying to upload the database sql that I exported from the old site but I keep getting errors that I do not know how to remedy.

THis is on mysql database.

Error
SQL query:



--

INSERT INTO `orders` (`id`, `tracking_`, `source_`, `istatus_`, `case_`, `cus_id`, `ip_`, 

`device_id`, `mod1_`, `brand_`, `carrier_`, `model_`, `off_`, `value_`, `first_`, `last_`, `phone_`, `address1_`, `address2_`, `city_`, `state_`, 

`zip_`, `method_`, `payment_`, `email_`, `image_`, `confirm_`, `date_`, `payment_date`, `paypalemail_`, `item_grade_`, `item_comments_`, `istatus2_`, 

`selling_price_`, `profit_`, `imei_`, `imei_status_`, `sell_date_`, `con_dif_`, `update_`, `emails_sent`, `emails_sent2`) VALUES


values not posted due to security


MySQL said: Documentation

#1292 - Incorrect datetime value: '2014-

06-29 20:11:48' for column 'date_' at row 40

This came from the same export so why should the import be any different? The field is set to date to timestamp with current time as the default. I tried changing it to just datetime but it still will not populate the table.

Does anyone have any ideas on what I should do?

 

[jpadie]

i think it would help to see the values.... i.e. the full query. asterisk out field values that are sensitive.

 

[penguinspeaks]

Ok, give me a bit and I will repost

 

[penguinspeaks]

Here are 2 of the entries. All of them have the same format obviously.

(100, '607001725103', '', 'Cancel', 'open', 0, '', 163, 'phone', 'Damaged', NULL, 

NULL, NULL, '2.40', 'Ja***', 'G***', '', '1*** ki** circle apt. **', '', 'chicago', 'IL', '60***', 'kit_', 'check', '***********@gmail.com', 

NULL, 1325721, '2014-06-24 17:34:21', '0000-00-00', '', '', '', '', '0.00', '0.00', '', '', '0000-00-00 00:00:00', 'no', '0000-00-00 00:00:00', 0, 

0),
(103, '', '', 'Cancel', 'open', 0, '', 399, 'phone', 'Good/Used', NULL, NULL, NULL, '0.00', 'D*****', 'H*******', '8328947276', '****** 1960 W.', 

'', '*******', 'TX', '******', 'email_', 'check', 'den********@gmail.com', NULL, 1325722, '2014-06-24 18:08:39', '0000-00-00', '', '', '', '', 

'0.00', '0.00', '', '', '0000-00-00 00:00:00', 'no', '0000-00-00 00:00:00', 0, 0)

hope this helps

 

[jpadie]

intriguing. looks ok to me.

can you post the schema and let us know the mysql version number.

also - how are you exporting/importing this? via mysqldump and the < ?

 

[penguinspeaks]

I have tried inporting the sql file as well as just pasting the sql.

let me know if this is not what you are looking for

-- phpMyAdmin SQL Dump
-- version 3.5.6
-- [URL unfurl="true"]http://www.phpmyadmin.net[/URL]
--
-- Host: localhost:3306
-- Generation Time: Aug 05, 2014 at 08:54 AM
-- Server version: 5.1.56-community
-- PHP Version: 5.3.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!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 utf8 */;

--
--

 

[penguinspeaks]

here is the rest of the sql that I left off

Table structure for table `orders`
--

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `tracking_` varchar(40) NOT NULL,
  `source_` varchar(20) NOT NULL,
  `istatus_` varchar(30) NOT NULL DEFAULT 'New Order',
  `case_` varchar(100) NOT NULL DEFAULT 'open',
  `cus_id` int(11) NOT NULL,
  `ip_` varchar(20) NOT NULL,
  `device_id` int(11) NOT NULL,
  `mod1_` varchar(20) NOT NULL,
  `brand_` varchar(14) DEFAULT NULL,
  `carrier_` varchar(8) DEFAULT NULL,
  `model_` varchar(40) DEFAULT NULL,
  `off_` varchar(3) DEFAULT NULL,
  `value_` decimal(5,2) DEFAULT NULL,
  `first_` varchar(100) NOT NULL,
  `last_` varchar(100) NOT NULL,
  `phone_` varchar(20) NOT NULL,
  `address1_` varchar(100) DEFAULT NULL,
  `address2_` varchar(100) DEFAULT NULL,
  `city_` varchar(100) NOT NULL,
  `state_` varchar(100) NOT NULL,
  `zip_` varchar(5) NOT NULL,
  `method_` varchar(10) NOT NULL,
  `payment_` varchar(10) NOT NULL,
  `email_` varchar(100) NOT NULL,
  `image_` int(10) DEFAULT NULL,
  `confirm_` int(11) NOT NULL,
  `date_` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `payment_date` date NOT NULL,
  `paypalemail_` varchar(100) NOT NULL DEFAULT 'none',
  `item_grade_` varchar(30) NOT NULL,
  `item_comments_` text NOT NULL,
  `istatus2_` varchar(30) NOT NULL,
  `selling_price_` decimal(5,2) NOT NULL,
  `profit_` decimal(5,2) NOT NULL,
  `imei_` varchar(30) NOT NULL,
  `imei_status_` varchar(30) NOT NULL,
  `sell_date_` datetime NOT NULL,
  `con_dif_` varchar(10) NOT NULL DEFAULT 'no',
  `update_` datetime NOT NULL,
  `emails_sent` int(11) NOT NULL DEFAULT '0',
  `emails_sent2` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=486 ;

--
-- Dumping data for table `orders`

 

[jpadie]

i have recreated the database and the table. the string imports just fine on my machine.

 

[penguinspeaks]

Seriously weird man!

I do not know what else to do at this point.

 

[vacunita]

I have to agree with jpadie.

It imports without any errors from the command line:

mysql test -u username -p < /path/to/file/sqlinsert.sql

I can also insert it fine directly in mysql. Are you using a GUI to do this, or directly on the command prompt?

Also is what you posted what's on line 40 of your dump? If not what exactly is there?





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[penguinspeaks]

Weirdest thing. I went home, ate, slept.
Came back this morning and put the SQL in for the population of the table and it went in without a hitch.

Not sure what was different or why it worked.

Thanks everyone for the help and sorry I cannot give an explaination.