Strange mysql error:Out of range value for column Need Help

Frappe Framework App Development
1

Hi,

I am facing a strange error where in when I am trying to save a file with save_file function in frappe I am getting the out of range value for column file_size now I have the following details as below:

Error Code:

Traceback (most recent call last):
  File "/home/aditya/frappe-bench/apps/frappe/frappe/app.py", line 62, in application
    response = frappe.handler.handle()
  File "/home/aditya/frappe-bench/apps/frappe/frappe/handler.py", line 22, in handle
    data = execute_cmd(cmd)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/handler.py", line 53, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/__init__.py", line 940, in call
    return fn(*args, **newargs)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/handler.py", line 81, in runserverobj
    frappe.desk.form.run_method.runserverobj(method, docs=docs, dt=dt, dn=dn, arg=arg, args=args)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/desk/form/run_method.py", line 36, in runserverobj
    r = doc.run_method(method)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 755, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 1029, in composer
    return composed(self, method, *args, **kwargs)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 1012, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 749, in <lambda>
    fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
  File "/home/aditya/frappe-bench/apps/rigpl_erpnext/rigpl_erpnext/rigpl_erpnext/doctype/carrier_tracking/carrier_tracking.py", line 118, in book_shipment
    from_country_doc, to_country_doc, transporter_doc, contact_doc)
  File "/home/aditya/frappe-bench/apps/rigpl_erpnext/rigpl_erpnext/rigpl_erpnext/doctype/carrier_tracking/carrier_tracking.py", line 165, in create_shipment_service
    self.store_label(self, shipment, tracking_id, self.doctype, self.name)
  File "/home/aditya/frappe-bench/apps/rigpl_erpnext/rigpl_erpnext/rigpl_erpnext/doctype/carrier_tracking/carrier_tracking.py", line 435, in store_label
    self.store_file('FEDEX-ID-{0}.pdf'.format(tracking_id), label_data, ps_doctype, ps_name)
  File "/home/aditya/frappe-bench/apps/rigpl_erpnext/rigpl_erpnext/rigpl_erpnext/doctype/carrier_tracking/carrier_tracking.py", line 443, in store_file
    save_file(file_name, image_data, ps_doctype, ps_name, is_private=1)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/utils/file_manager.py", line 157, in save_file
    f.insert()
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 239, in insert
    self.run_method("after_insert")
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 755, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 1029, in composer
    return composed(self, method, *args, **kwargs)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 1012, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 749, in <lambda>
    fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/core/doctype/file/file.py", line 57, in after_insert
    self.update_parent_folder_size()
  File "/home/aditya/frappe-bench/apps/frappe/frappe/core/doctype/file/file.py", line 132, in update_parent_folder_size
    frappe.get_doc("File", self.folder).set_folder_size()
  File "/home/aditya/frappe-bench/apps/frappe/frappe/core/doctype/file/file.py", line 114, in set_folder_size
    self.db_set('file_size', self.file_size)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/model/document.py", line 962, in db_set
    self.modified, self.modified_by, update_modified=update_modified)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/database.py", line 685, in set_value
    values, debug=debug)
  File "/home/aditya/frappe-bench/apps/frappe/frappe/database.py", line 173, in sql
    self._cursor.execute(query, values)
  File "/home/aditya/frappe-bench/env/lib/python3.5/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/home/aditya/frappe-bench/env/lib/python3.5/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/aditya/frappe-bench/env/lib/python3.5/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/aditya/frappe-bench/env/lib/python3.5/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/home/aditya/frappe-bench/env/lib/python3.5/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/home/aditya/frappe-bench/env/lib/python3.5/site-packages/pymysql/connections.py", line 1018, in _read_packet
    packet.check_error()
  File "/home/aditya/frappe-bench/env/lib/python3.5/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/aditya/frappe-bench/env/lib/python3.5/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.DataError: (1264, "Out of range value for column 'file_size' at row 1")

Now my file_size value which I got from debug was 146553 and also when I checked my backend mysql database I got this as below:

rigpl.local> DESCRIBE `tabFile`;
+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| name                  | varchar(255) | NO   | PRI | <null>  |       |
| creation              | datetime(6)  | YES  |     | <null>  |       |
| modified              | datetime(6)  | YES  |     | <null>  |       |
| modified_by           | varchar(255) | YES  |     | <null>  |       |
| owner                 | varchar(255) | YES  |     | <null>  |       |
| docstatus             | int(1)       | NO   |     | 0       |       |
| parent                | varchar(255) | YES  | MUL | <null>  |       |
| parentfield           | varchar(255) | YES  |     | <null>  |       |
| parenttype            | varchar(255) | YES  |     | <null>  |       |
| idx                   | int(8)       | YES  |     | <null>  |       |
| file_name             | varchar(140) | YES  |     | <null>  |       |
| _user_tags            | text         | YES  |     | <null>  |       |
| file_url              | longtext     | YES  |     | <null>  |       |
| attached_to_name      | varchar(140) | YES  | MUL | <null>  |       |
| file_size             | int(11)      | NO   |     | 0       |       |
| attached_to_doctype   | varchar(140) | YES  | MUL | <null>  |       |
| content_hash          | varchar(140) | YES  | MUL | <null>  |       |
| rgt                   | int(11)      | NO   |     | 0       |       |
| old_parent            | varchar(140) | YES  |     | <null>  |       |
| lft                   | int(11)      | NO   |     | 0       |       |
| is_home_folder        | int(1)       | NO   |     | 0       |       |
| is_attachments_folder | int(1)       | NO   |     | 0       |       |
| is_folder             | int(1)       | NO   |     | 0       |       |
| folder                | varchar(140) | YES  |     | <null>  |       |
| thumbnail_url         | text         | YES  |     | <null>  |       |
| is_private            | int(1)       | NO   |     | 0       |       |
| _comments             | text         | YES  |     | <null>  |       |
| _liked_by             | text         | YES  |     | <null>  |       |
| _assign               | text         | YES  |     | <null>  |       |
| attached_to_field     | varchar(140) | YES  |     | <null>  |       |
+-----------------------+--------------+------+-----+---------+-------+
30 rows in set
Time: 0.097s
rigpl.local>

The error is gone if I change the file_size to varchar(11) which I think is wrong but just did not want to get stuck on this issue for long.
What could be issue since its quite perplexing to get this error out of nowhere?

2

The problem may be you are running python 3.5

3

Python 3.6 is only for Ubuntu 16.04!

Be carefull if you on 14.04!

Hope this helps.

4

Well here is my python version:

aditya@ubuntu:~/frappe-bench/apps/rigpl_erpnext$ python --version
Python 2.7.12

Well I guess even this is not true:

aditya@ubuntu:~/frappe-bench/apps/rigpl_erpnext$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.4 LTS
Release:        16.04
Codename:       xenial
5

But according to your traceback, the database query portion refers to python 3.5.

So your environment is confused it seems?

3 Likes
6

I believe you can install python 3.x and 2.7 though python 3 is the default.

8

Well I again got this error and this time I had setup my test machine to be on master branch so that there was no question of python 3.5 as was the case with the develop branch.

Now this time around I resolved the issue by doing a hack on my test server to the DB as below:

ALTER TABLE tabFile MODIFY file_size VARCHAR(140);

Now I know this is not the correct way of approaching the problem but definitely it resolves the issue.

EDIT: Strangely this problem is not coming on my production server.

3 Likes
9

What exactly does this do ? I had the same problem on my local ERPNext server but the production server had no problems. I am using v8

10

@adityaduggal i am facing the same issue when i am attaching files,

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 62, in application
    response = frappe.handler.handle()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 22, in handle
    data = execute_cmd(cmd)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 53, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 939, in call
    return fn(*args, **newargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/form/utils.py", line 17, in remove_attach
    return frappe.utils.file_manager.remove_file(fid)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/file_manager.py", line 245, in remove_file
    frappe.delete_doc("File", fid, ignore_permissions=ignore_permissions)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 673, in delete_doc
    ignore_permissions, flags, ignore_on_trash, ignore_missing)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/delete_doc.py", line 92, in delete_doc
    doc.run_method("after_delete")
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 758, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 1027, in composer
    return composed(self, method, *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 1010, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 752, in <lambda>
    fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/core/doctype/file/file.py", line 227, in after_delete
    self.update_parent_folder_size()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/core/doctype/file/file.py", line 131, in update_parent_folder_size
    frappe.get_doc("File", self.folder).set_folder_size()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/core/doctype/file/file.py", line 114, in set_folder_size
    self.db_set('file_size', self.file_size)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py", line 960, in db_set
    self.modified, self.modified_by, update_modified=update_modified)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 678, in set_value
    values, debug=debug)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 166, in sql
    self._cursor.execute(query, values)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 893, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1103, in _read_query_result
    result.read()
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1396, in read
    first_packet = self.connection._read_packet()
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1059, in _read_packet
    packet.check_error()
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
DataError: (1264, u"Out of range value for column 'file_size' at row 1")

Can you please share how you solved this issue?
Should i run this command?
ALTER TABLE tabFile MODIFY file_size VARCHAR(140);

11

Maybe try that or this suggests sql - MySQL Error 1264: out of range value for column - Stack Overflow another option is this

ALTER TABLE tabFile MODIFY file_size BIGINT;

Here are mysql integer value limits https://dev.mysql.com/doc/refman/8.0/en/integer-types.html so you have exceeded limit 2147483647

file_size apparently accumulates the total size (in bytes) of all the files contained in a folder and its subfolder children
frappe/file.py at develop · frappe/frappe · GitHub

12

@clarkej thanks for your reply,
getting the same error after running the above query.

13

you have tried both queries and restarted mysqld perhaps?

Possibly this error is for a similar reason Error in attaching files

14

Yes i’ve tried both queries & restarted the mysqld,
Yes that is for the same.

15

Hey all!

May be related:

https://github.com/frappe/erpnext/issues/14627

16

Thanks it works for me, i was having same issue.

17

المشكلة في الطول/القيمة عند انشاء جدول Sql عليك تحديد الطول/القيمة

18

How does this affect updates. Any expected issues?

19

Yes good question - since ./apps/frappe/frappe/core/doctype/file/file.json defines the schema for that table, the question is whether say a ‘bench migrate’ will reapply that spec and revert the fieldtype to Int from BIGINT (unless that file is modified to reflect that change.)

I have not tested what happens in that case - perhaps someone has experience with that expected behaviour?

20

I actually tried running migrate once and didn’t see any effect. In my case the issue was stock qty in sales invoice item. I had to change all the qty columns from decimal to BIGINT. Im not sure which figure was causing this, been thinking about it after making the fix, probably a long decimal figure.

Thank you @clarkej