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

ERPNext
1

As I run bench update, I get this error. I tried to look for the string 24.10.2018 in the Table Batch, but I can’t seem to find it.

Any ideas?

Thanks

Jay

Migrating site1.local
Updating DocTypes for frappe : [========================================]
Updating DocTypes for erpnext : [======================= ]

There was an issue while migrating the DocType: Batch

Traceback (most recent call last):
File “/usr/lib/python2.7/runpy.py”, line 174, in _run_module_as_main
“main”, fname, loader, pkg_name)
File “/usr/lib/python2.7/runpy.py”, line 72, in _run_code
exec code in run_globals
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 97, in
main()
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 18, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 764, in call
return self.main(*args, **kwargs)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 717, in main
rv = self.invoke(ctx)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 956, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 555, in invoke
return callback(*args, **kwargs)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/decorators.py”, line 17, in new_func
return f(get_current_context(), *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/init.py”, line 25, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py”, line 227, in migrate
migrate(context.verbose, rebuild_website=rebuild_website)
File “/home/frappe/frappe-bench/apps/frappe/frappe/migrate.py”, line 49, in migrate
frappe.model.sync.sync_all(verbose=verbose)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/sync.py”, line 19, in sync_all
sync_for(app, force, verbose=verbose, reset_permissions=reset_permissions)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/sync.py”, line 56, in sync_for
reset_permissions=reset_permissions, for_sync=True)
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/import_file.py”, line 65, in import_file_by_path
ignore_version=ignore_version, reset_permissions=reset_permissions)
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/import_file.py”, line 132, in import_doc
doc.insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 249, in insert
self.run_post_save_methods()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 905, in run_post_save_methods
self.run_method(“on_update”)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 772, in run_method
out = Document.hook(fn)(self, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 1048, in composer
return composed(self, method, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 1031, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 766, in
fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/core/doctype/doctype/doctype.py”, line 243, in on_update
raise e
pymysql.err.InternalError: (1292, u"Incorrect datetime value: ‘24.10.2018’")

2

Might be your machine’s locale settings

1 Like
3

Okay! How do I figure out if it is. Here’s the output:

ubuntu@ip-172-31-24-44:~$ timedatectl
Local time: Mon 2019-06-24 10:13:16 UTC
Universal time: Mon 2019-06-24 10:13:16 UTC
RTC time: Mon 2019-06-24 10:13:15
Time zone: Etc/UTC (UTC, +0000)
Network time on: yes
NTP synchronized: yes
RTC in local TZ: no

Doesn’t look like it, no?

Or how do I correct it?

Thanks

Jay

4

Nope - all looks right in the OS config. I can only guess that it’s a .json file somewhere, but I have no idea where or which one

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