Bench Update error - pymysql.err.InternalError: (1292, u"Incorrect datetime value: '24.10.2018'")

ERPNext
5

@JayRam I have check batch.json file both in hotfix and develop branch, but could not found any issue with modified timestamp. Please check the modified timestamp in your local batch.json file.

It should be in the format “2018-08-29 06:28:57.985997”

1 Like
6

Thanks, really appreciate it. Running ls -l gives me this view. Seems like there is a problem, huh? How to correct?

BatchFiles

Thanks

Jay

7

ls -l just shows all files in a directory. Run git status from apps/erpnext directory, it will show the files with local changes. If you have not made any local changes, you can discard the changes using git stash and then run bench update again.

8

I do git stash and it says no local changes to save. I run bench update after that and same error.

But I think I got it figured out. One of the Date entries in the Batch DocType was 24.10.2018 and I managed to change that. I’m hoping the update will go through this evening.

Thanks

Jay

9

In such cases, I normally do a backup and then run

bench update --reset

(Though, thus far and touch wood, I have not ever had to use the backup)

10

And it sure did! Thanks @nabinhait and @trentmu for chipping in.

Cheers

Jay

11

I got a similar error while updating the erpnext today.

There was an issue while migrating the DocType: Sales Invoice Item
pymysql.err.OperationalError: (1292, “Incorrect datetime value: ‘’”)

Can you please help me how did you find the date entries of batch doctype. I would want to do the same with doctype Sales Invoice Item and rectify the error.

Thanks,
Touseef

12

Hey @JayRam
Sorry to tag you but looks like only you could help me out with this as my issue is also similar one.

Thanks

Touseef

13

Try logging in to mysql and find the offending record - A Sales Invoice Item that has the Incorrect datetime value: ‘’

Not sure what that means, but it could be that it it is a custom field, you made that field mandatory after a few transactions, and now during the upgrade the blank values prior to making that field mandatory is coming back to haunt you.

If my guess is right you can try two options: Using database command try turning off the mandatory requirement for this field. Or try populating an acceptable value for the records that have datetime value: ‘’

There are Paid Support channels on Telegram and you can perhaps post this there and somebody that knows these things very can help you.

Hope this helps.

Thanks

Jay

14

Thanks for a prompt reply @JayRam.
I am a novice user. Not much idea about how do you query mysql database. As far as i remember no recent changes were made in the entire erpnext after V 13.15.0 update. So the question of mandatory field is out. My only concern is if any of the recent sales invoices had a bad datetime entry. I wish there was a way to find out using database query for Sales Invoices with datetime.

How did you figure out the Date entries in the Batch DocType was 24.10.2018 ?

15

Hi Touseef,

It’s not too hard. Just google and follow instructions. Be a bit bold, but don’t be reckless. Try and take a backup of the server just to be sure. Typically mysql find replace values in tables should give you enough pointers about how to be able to do that. Login into the database and list the existing values in that field of the Sales Invoice Item table and you will see what format of values ERPNext is expecting in that field.

Hope this helps.

Thanks

Jay

16

Yeah i did a bit google and tried to get the Sales Invoice Item table from mysql. But to my surprise i get syntax error for Only Sales Invoice/Purchase invoice tables. I can see rest all tables.

I used SELECT * FROM tabSales Invoice Item;
Gives me ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘Item’ at line 1

But i could see other tables like quotation, accounts. I could not see everything related to Sales and Purchase.

Database%20Tables
Database%20Tables563×840 8.83 KB

17

In tabSales Invoice Item you should look for datetime field and list the values in that field. Seems like you have trouble there.

Thanks

Jay

18

But i can’t as i cannot open tabSales Invoice Item. Gives me syntax error
Database%20Tables

19

Try using ?tabSales Invoice Item?.

Replace ? with ` in that line.

Thanks

Jay

1 Like
20

if ` gives an error try '.

Thanks

Jay

1 Like
21

` did the trick. I could now see it. Will dig in and see if i can find any issue. Thanks a lot mate.

22

I checked all the datetime values Sales invoice item and Sales Invoice but couldn’t find any null values. Took 2 days to check the data.

Nevertheless i got fed up with it and used the following commands to change the time stamp using the codes

SET sql_mode = '';
SET GLOBAL sql_mode = '';

I was then able to proceed and everything went smooth to be updated to V 13.16.0

1 Like
23

You da man!

Congratulations! Your technical journey to understand the innards of ERPNext is off to a rocking start.

Thanks

Jay

24

Yeah it has been good one to say. Thanks to guys like you who help the Erpnext community and makes it easier for us.