How About A New MariaDB Adapter?

I’m new to ERPNext and I’m liking it at the moment. I have also contributed a couple of PRs to the Frappe repo.

Frappe makes use of mysql-python at the moment but the development of mysql-python seems to have stalled since 2014. The maintainer of the repo even mentioned that the repo needs a new maintainer (view comment on github ). Hence I think it makes sense for ERPNext to move forward and choose another adapter.

I believe we need to consider python 3 support as I can see there are already efforts by some developers (including me) to make the Frappe ecosystem python 3 compliant. We also need to make sure it is actively being developed and mature.

What does the community think about this? If we should change the database adapter, can we have suggestions?

Present Suggestions:

  1. mysqlclient - See discussion
  2. pymysql - See discussion
5 Likes

searching frappe and erpnext for MySQLdb resulted in 27 matches across 12 files

/home/revant/frappe-bench/apps/frappe/frappe/app.py
/home/revant/frappe-bench/apps/frappe/frappe/core/doctype/communication/email.py
/home/revant/frappe-bench/apps/frappe/frappe/core/doctype/doctype/doctype.py
/home/revant/frappe-bench/apps/frappe/frappe/database.py
/home/revant/frappe-bench/apps/frappe/frappe/desk/query_builder.py
/home/revant/frappe-bench/apps/frappe/frappe/desk/reportview.py
/home/revant/frappe-bench/apps/frappe/frappe/exceptions.py
/home/revant/frappe-bench/apps/frappe/frappe/model/db_schema.py
/home/revant/frappe-bench/apps/frappe/frappe/utils/background_jobs.py
/home/revant/frappe-bench/apps/frappe/frappe/utils/scheduler.py
/home/revant/frappe-bench/apps/erpnext/erpnext/patches/v5_4/cleanup_journal_entry.py
/home/revant/frappe-bench/apps/erpnext/erpnext/patches/v5_7/item_template_attributes.py

pymysql +1

https://wiki.openstack.org/wiki/PyMySQL_evaluation#MySQL_DB_Drivers_Comparison says it’s Actively maintained and popular.

1 Like


This link suggests pymysql usage is the same as for MySQLdb

Yes. Can we get approval for the change from @rmehta? We can create a separate branch for it and merge it once Travis clears it

3 Likes

tried replacing pymysql in database.py resulted in error.

We can always try out on forks. If it works there we’ll send a PR.

File "/home/revant/frappe-bench/apps/frappe/frappe/middlewares.py", line 15, in __call__
return super(StaticDataMiddleware, self).__call__(environ, start_response)
File "/home/revant/frappe-bench/env/lib/python2.7/site-packages/werkzeug/wsgi.py", line 599, in __call__
return self.app(environ, start_response)
File "/home/revant/frappe-bench/env/lib/python2.7/site-packages/werkzeug/wsgi.py", line 599, in __call__
return self.app(environ, start_response)
File "/home/revant/frappe-bench/env/lib/python2.7/site-packages/werkzeug/local.py", line 228, in application
return ClosingIterator(app(environ, start_response), self.cleanup)
File "/home/revant/frappe-bench/env/lib/python2.7/site-packages/werkzeug/wrappers.py", line 291, in application
return f(*args[:-2] + (request,))(*args[-2:])
File "/home/revant/frappe-bench/apps/frappe/frappe/app.py", line 95, in application
frappe.destroy()
File "/home/revant/frappe-bench/apps/frappe/frappe/__init__.py", line 218, in destroy
db.close()
File "/home/revant/frappe-bench/apps/frappe/frappe/database.py", line 849, in close
self._cursor.close()

Hmm. When I get to my computer, I’ll also give it a try.

Can you create a branch that I can push the pymysql changes to? Or should I create on my fork and give you the link?

We did replace MySQLDb with pymysql when we were experimenting with gevent. It takes a few mins to migrate the current connection settings. It worked alright, but we did not do extensive tests.

I too like pymysql. Lets move!

Thanks @tundebabzy and @revant_one for taking the lead for Python 3, lets keep fixing things part by part!

4 Likes

Thanks @rmehta.

@revant_one, I changed all MySQLdb to pymysql and quite alright there were errors. After investigating, I discovered that PyMySQL does not use the _mysql level APIs that mysqldb used as its a pure python implementation.

Presently, the changes work on my computer but fails on Travis

1 Like

I replaced MySQLdb with pymysql! faced errors

Edit: pulled latest from tundebabzy/frappe, It’s working on my system as well!

Awesome!!
@tundebabzy PR!

2 Likes

It’s still balking on Travis and Travis is not even being helpful. Simply say bench command not found. I’m now officially stuck.

frappe.log showing this

Request Error
Traceback (most recent call last):
  File "/home/revant/frappe-bench/apps/frappe/frappe/app.py", line 52, in application
    init_request(request)
  File "/home/revant/frappe-bench/apps/frappe/frappe/app.py", line 114, in init_request
    frappe.local.http_request = frappe.auth.HTTPRequest()
  File "/home/revant/frappe-bench/apps/frappe/frappe/auth.py", line 39, in __init__
    self.set_lang()
  File "/home/revant/frappe-bench/apps/frappe/frappe/auth.py", line 82, in set_lang
    frappe.local.lang = guess_language()
  File "/home/revant/frappe-bench/apps/frappe/frappe/translate.py", line 27, in guess_language
    lang_list = get_all_languages() or []
  File "/home/revant/frappe-bench/apps/frappe/frappe/translate.py", line 82, in get_all_languages
    return frappe.cache().get_value('languages', _get)
  File "/home/revant/frappe-bench/apps/frappe/frappe/utils/redis_wrapper.py", line 70, in get_value
    val = generator()
  File "/home/revant/frappe-bench/apps/frappe/frappe/translate.py", line 81, in _get
    return frappe.db.sql_list('select name from tabLanguage')
  File "/home/revant/frappe-bench/apps/frappe/frappe/database.py", line 211, in sql_list
    return [r[0] for r in self.sql(query, values, debug=debug)]
  File "/home/revant/frappe-bench/apps/frappe/frappe/database.py", line 154, in sql
    self._cursor.execute(query)
  File "/home/revant/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/home/revant/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/revant/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/revant/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/home/revant/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/home/revant/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1014, in _read_packet
    packet.check_error()
  File "/home/revant/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/revant/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)

I dont known how complex is erpnext in this abstraction, but could be a good moment to implement a ‘standard library’ that provide support to other databases, for example sqlalchemy without orm only core (it uses pymysql among others …)

I understand that this could be a lot of work but i think that it was be a great improvement for future of the erp.

hello @meigallodixital, I haven’t gotten a go ahead from any of the core developers but I do have this in mind as something to contribute (with the help of others). However, I think it will be better to first of all finish up python 3 compatibility before adopting the sqlalchemy layer. Sqlalchemy doesn’t come up database adapters.

2 Likes

Lol! I completely forgot about the logs. Thanks for the heads up. Let me check it out

The question is that for example in this company (and I know more cases), the
support of python 3 and postgresql is a limitation when deciding by
erpnext. But we understand that it was not the initial focus of the project and the enormous work that it would take.

So I ran bench update and reinstalled my bench because it was acting up. I tried to restore my database and it gave me AttributeError instead. I’ve submitted an issue on github.com but in the meantime, I’m going to revert the commit that seems to have brought in the problem.

I suspect pymysql is not able to connect to the database.

Sqlalchemy has a severe performance penalty and we will have to resort to direct SQLs for complex queries anyways!

@tundebabzy can you start a PR for this? Maybe I can help. From what I remember from my previous experiment was regarding datatype conversions.

Unfortunately it seems we did not save the branch!

1 Like

OK. I’ll open the PR. I’ll put comments about the implementation there

1 Like

I mean use core part only not orm one: http://docs.sqlalchemy.org/en/rel_1_1/core/tutorial.html. This is a ‘simple’ abstraction based on python dbapi.

If you see this stackoverflow test, using core poart is very similar to raw query: https://stackoverflow.com/questions/11769366/why-is-sqlalchemy-insert-with-sqlite-25-times-slower-than-using-sqlite3-directly/11769768#11769768

The real difference is on bulk insert, in other cases i dont think that the lost of perfomance will be significative.