While creating new-site (MySQL username too long error has occured)

Mangesh_Bhuskute · November 1, 2018, 8:50am

Hello Team,

Error/ Cause
While running the new site command following error has occurred.

bench new-site sitenam
pymysql.err.InternalError: (1470, u"String ‘_62d8d83e1652222d’ is too long for user name (should be no longer than 16)")

Our local configuration,

Latest Frappe repo cloned
Bench version is 4.0.1

$ mysql --version
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
With above configuration we are receiving

with above configuration we are getting below error, which is related to MySQL username characters limitation.

** Track-trace => **

$ bench new-site new.local

MySQL root password:
Traceback (most recent call last):
File “/usr/lib64/python2.7/runpy.py”, line 162, in _run_module_as_main
“main”, fname, loader, pkg_name)
File “/usr/lib64/python2.7/runpy.py”, line 72, in _run_code
exec code in run_globals
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 97, in
main()
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 18, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/click/core.py”, line 764, in call
return self.main(*args, **kwargs)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/click/core.py”, line 717, in main
rv = self.invoke(ctx)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/click/core.py”, line 956, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/click/core.py”, line 555, in invoke
return callback(*args, **kwargs)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/apps/frappe/frappe/commands/site.py”, line 33, in new_site
db_type = db_type)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/apps/frappe/frappe/commands/site.py”, line 66, in _new_site
source_sql=source_sql,force=force, reinstall=reinstall, db_type=db_type)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/apps/frappe/frappe/installer.py”, line 36, in install_db
setup_database(force, source_sql, verbose)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/apps/frappe/frappe/database/init.py”, line 16, in setup_database
return frappe.database.mariadb.setup_db.setup_database(force, source_sql, verbose)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py”, line 14, in setup_database
dbman.delete_user(db_name)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/apps/frappe/frappe/database/db_manager.py”, line 31, in delete_user
self.db.sql(“DROP USER ‘%s’@’%s’;” % (target, host))
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/apps/frappe/frappe/database/database.py”, line 165, in sql
self._cursor.execute(query)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/pymysql/connections.py”, line 516, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/pymysql/connections.py”, line 727, in _read_query_result
result.read()
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/pymysql/connections.py”, line 1066, in read
first_packet = self.connection._read_packet()
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/pymysql/connections.py”, line 683, in _read_packet
packet.check_error()
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/pymysql/protocol.py”, line 220, in check_error
err.raise_mysql_exception(self._data)
File “/home/osboxes/Projects/ERPNext_v10/frappe-bench/env/lib/python2.7/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
raise errorclass(errno, errval)

pymysql.err.InternalError: (1470, u"String ‘_62d8d83e1652222d’ is too long for user name (should be no longer than 16)")

Mangesh_Bhuskute · November 13, 2018, 12:42pm

Hello Team,

Any update ERPNext Forum team?

I am tried few tricks suggested by members but none of them solve the problem.

Iebeling_Kaastra · November 21, 2018, 10:01pm

Received same error today installing on Centos 7.5 / Maria. Any updates on this?

Mangesh_Bhuskute · November 22, 2018, 5:34am

You have to perform 3 steps:

Upgrade MariaDB version to 10.+ (We have refer 10.3)
Comment some MySQL configuration file (If you have installed old versions)
Update MariaDB table configuration manually

To upgrade MariaDB :
Download MariaDB Server - MariaDB.org

edit /etc/my.cnf file – just comments first 3 lines as below (If you already have old mysql version installed)

[mysqld]
#innodb-file-format=barracuda
#innodb-file-per-table=1
#innodb-large-prefix=1
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_force_recovery = 1

MariaDB table update configurations:
CREATE USER - MariaDB Knowledge Base

Enter (ALTER) commands to MariaDB console.

I hope this will help you out.

PrashantPatil · November 22, 2018, 6:00am

try below commands…
sudo mysqld --tc-heuristic-recover=ROLLBACK
sudo service mysqld restart
sudo service mysqld status

anastes · April 26, 2019, 1:51pm

Guys, Any solution to this bug?

clarkej · April 26, 2019, 2:09pm

What have you tried and checked for clues? [March 2019] ERPNext installation bug - #12 by bkm

Mangesh_Bhuskute · May 13, 2019, 6:51am

@anastes I have perform above mentioned steps manually and it help me out.
If you are still facing the issue let us know, may be I can help you.

anastes · May 13, 2019, 9:47am

Thanks for your reply.
I’ve solved the problem by upgrading Mariadb

neiltancock · November 1, 2019, 12:09pm

Hey @Mangesh_Bhuskute, I’m seeing this issue on a CentOS 7 install.

I have upgraded MaridDB to 10.3 and amended my.cnf, but I am not sure what the 3rd step is, MariaDB table update configurations. Can you offer any pointers at all?

Neil

Mangesh_Bhuskute · November 5, 2019, 6:38am

Hi,

I am not sure about the sequences of command which I have executed but following are the commands which help me

ALTER TABLE mysql.user         MODIFY User         CHAR(80)  BINARY NOT NULL DEFAULT '';
ALTER TABLE mysql.db           MODIFY User         CHAR(80)  BINARY NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv  MODIFY User         CHAR(80)  BINARY NOT NULL DEFAULT '';
ALTER TABLE mysql.columns_priv MODIFY User         CHAR(80)  BINARY NOT NULL DEFAULT '';
ALTER TABLE mysql.procs_priv   MODIFY User         CHAR(80)  BINARY NOT NULL DEFAULT '';
ALTER TABLE mysql.proc         MODIFY definer      CHAR(141) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event        MODIFY definer      CHAR(141) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.proxies_priv MODIFY User         CHAR(80)  COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.proxies_priv MODIFY Proxied_user CHAR(80)  COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.proxies_priv MODIFY Grantor      CHAR(141) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.servers      MODIFY Username     CHAR(80)                   NOT NULL DEFAULT '';
ALTER TABLE mysql.procs_priv   MODIFY Grantor      CHAR(141) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv  MODIFY Grantor      CHAR(141) COLLATE utf8_bin NOT NULL DEFAULT '';

FLUSH PRIVILEGES;

darwin_perez · May 24, 2020, 1:49am

Hello @neiltancock, were you able to complete step 3 mentioned by @Mangesh_Bhuskute? Would you mind sharing what you’ve done? Thank you.