Closed
Bug 1199301
Opened 10 years ago
Closed 10 years ago
RelengAPI DB backend is using latin1 encoding
Categories
(Infrastructure & Operations Graveyard :: WebOps: Other, task)
Infrastructure & Operations Graveyard
WebOps: Other
Tracking
(Not tracked)
RESOLVED
FIXED
People
(Reporter: dustin, Assigned: cliang)
References
Details
(Whiteboard: [kanban:https://webops.kanbanize.com/ctrl_board/2/1681] )
[Thu Aug 27 08:40:59 2015] [error] 2015-08-27 08:40:59,732 [relengapi.app] Exception on /tokenauth/tokens [POST]
[Thu Aug 27 08:40:59 2015] [error] Traceback (most recent call last):
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
[Thu Aug 27 08:40:59 2015] [error] rv = self.dispatch_request()
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/app.py", line 1461, in dispatch_request
[Thu Aug 27 08:40:59 2015] [error] return self.view_functions[rule.endpoint](**req.view_args)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/newrelic-2.46.0.37/newrelic/hooks/framework_flask.py", line 40, in _nr_wrapper_handler_
[Thu Aug 27 08:40:59 2015] [error] return wrapped(*args, **kwargs)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/lib/api.py", line 107, in replacement
[Thu Aug 27 08:40:59 2015] [error] result = wrapped(*args, **kwargs)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/blueprints/tokenauth/__init__.py", line 282, in issue_token
[Thu Aug 27 08:40:59 2015] [error] token = token_issuers[typ](body, requested_permissions)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/blueprints/tokenauth/__init__.py", line 235, in issue_usr
[Thu Aug 27 08:40:59 2015] [error] session.commit()
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 150, in do
[Thu Aug 27 08:40:59 2015] [error] return getattr(self.registry(), name)(*args, **kwargs)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 790, in commit
[Thu Aug 27 08:40:59 2015] [error] self.transaction.commit()
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 392, in commit
[Thu Aug 27 08:40:59 2015] [error] self._prepare_impl()
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 372, in _prepare_impl
[Thu Aug 27 08:40:59 2015] [error] self.session.flush()
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2004, in flush
[Thu Aug 27 08:40:59 2015] [error] self._flush(objects)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2122, in _flush
[Thu Aug 27 08:40:59 2015] [error] transaction.rollback(_capture_exception=True)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
[Thu Aug 27 08:40:59 2015] [error] compat.reraise(exc_type, exc_value, exc_tb)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2086, in _flush
[Thu Aug 27 08:40:59 2015] [error] flush_context.execute()
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
[Thu Aug 27 08:40:59 2015] [error] rec.execute(self)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
[Thu Aug 27 08:40:59 2015] [error] uow
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj
[Thu Aug 27 08:40:59 2015] [error] mapper, table, insert)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 761, in _emit_insert_statements
[Thu Aug 27 08:40:59 2015] [error] execute(statement, params)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
[Thu Aug 27 08:40:59 2015] [error] return meth(self, multiparams, params)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
[Thu Aug 27 08:40:59 2015] [error] return connection._execute_clauseelement(self, multiparams, params)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
[Thu Aug 27 08:40:59 2015] [error] compiled_sql, distilled_params
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
[Thu Aug 27 08:40:59 2015] [error] context)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1342, in _handle_dbapi_exception
[Thu Aug 27 08:40:59 2015] [error] util.reraise(*exc_info)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
[Thu Aug 27 08:40:59 2015] [error] context)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute
[Thu Aug 27 08:40:59 2015] [error] cursor.execute(statement, parameters)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/newrelic-2.46.0.37/newrelic/hooks/database_dbapi2.py", line 22, in execute
[Thu Aug 27 08:40:59 2015] [error] *args, **kwargs)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/MySQLdb/cursors.py", line 187, in execute
[Thu Aug 27 08:40:59 2015] [error] query = query % tuple([db.literal(item) for item in args])
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/MySQLdb/connections.py", line 278, in literal
[Thu Aug 27 08:40:59 2015] [error] return self.escape(o, self.encoders)
[Thu Aug 27 08:40:59 2015] [error] File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/MySQLdb/connections.py", line 208, in unicode_literal
[Thu Aug 27 08:40:59 2015] [error] return db.literal(u.encode(unicode_literal.charset))
[Thu Aug 27 08:40:59 2015] [error] UnicodeEncodeError: 'latin-1' codec can't encode character u'\\u2122' in position 81: ordinal not in range(256)
so the things to figure out are:
* how to configure it to use utf-8 instead
* how to manage the transition (do I need to re-encode everything?)
|
Reporter | |
Comment 1•10 years ago
|
||
Sheeri, can you shed a little light on that?
Flags: needinfo?(scabral)
|
||
Comment 2•10 years ago
|
||
This explains the phenomenon and how to change it, if I'm understanding the issue correctly:
http://www.pythian.com/blog/beware-default-charset-for-mysqldump-is-utf8-regardless-of-server-default-charset-2/
Managing the transition should be a mysqldump and then restore, and I'm pretty sure this can be done one server at a time (e.g. a master and slave can replicate even if they're different charsets, I'm 90% confident of that)
Flags: needinfo?(scabral)
|
|
Reporter | |
Comment 3•10 years ago
|
||
mysql> show create table auth_tokens \G
*************************** 1. row ***************************
Table: auth_tokens
Create Table: CREATE TABLE `auth_tokens` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` text NOT NULL,
`_permissions` text NOT NULL,
`typ` varchar(4) NOT NULL,
`user` text,
`disabled` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
yet I can confirm this error occurs when inserting into that table. And doesn't occur when inserting into a table on my local MySQL (5.6.26 against generic1's 5.6.17). So, where's the latin1 coming from?
|
||
Comment 4•10 years ago
|
||
I theorized that it was the python client itself:
After connecting to the staging relengapi instance and setting up DB's and a session connected to relengapi itself
>>> res = sess.execute("show create table auth_tokens;")
>>> for r in res:
... print r
...
(u'auth_tokens', u'CREATE TABLE `auth_tokens` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `description` text NOT NULL,\n `_per
missions` text NOT NULL,\n `typ` varchar(4) NOT NULL,\n `user` text,\n `disabled` tinyint(1) NOT NULL,\n PRIMARY KEY (`id`)\n
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8')
>>> res = sess.execute("show variables like '%char%';")
>>> for r in res:
... print r
...
(u'character_set_client', u'latin1')
(u'character_set_connection', u'latin1')
(u'character_set_database', u'utf8')
(u'character_set_filesystem', u'binary')
(u'character_set_results', u'latin1')
(u'character_set_server', u'utf8')
(u'character_set_system', u'utf8')
(u'character_sets_dir', u'/usr/share/mysql/charsets/')
|
|
Reporter | |
Comment 5•10 years ago
|
||
Good find, thanks! BTW, you can use 'relengapi.sh repl' to get that stuff set up.
|
|
Reporter | |
Comment 6•10 years ago
|
||
Further reducing that to just using the MySQLdb module, I still see the difference:
>>> import x
>>> x.run()
('character_set_client', 'latin1')
('character_set_connection', 'latin1')
('character_set_database', 'utf8')
('character_set_filesystem', 'binary')
('character_set_results', 'latin1')
('character_set_server', 'utf8')
('character_set_system', 'utf8')
('character_sets_dir', '/usr/share/mysql/charsets/')
on the stage webhead, vs. all utf8's on my host at home. So that rules out SQLAlchemy. The versions of MySQLdb are the same in either case. In fact, even the MySQL command-line client does it:
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
|
|
Reporter | |
Comment 7•10 years ago
|
||
From my laptop:
dustin@ramanujan ~ $ mysql -h stage-rw-vip.db.scl3.mozilla.com -p -u relengapi_stage
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5612705
Server version: 5.6.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.08 sec)
So this is something in the client-side MySQL configuration.
|
|
Reporter | |
Comment 8•10 years ago
|
||
mysql> show global variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> show session variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
so it's specifically session variables.
|
|
Reporter | |
Comment 9•10 years ago
|
||
The webheads have
mysql-5.1.73-3.el6_5.x86_64
which is "old". So we'll need to see about upgrading that to 5.6.
|
|
Reporter | |
Comment 10•10 years ago
|
||
OK, I've failed pretty miserably. I tried setting $mysql_package_type = "mysql56" on the node, and including mysql2::client in modules/webapp/manifests/releng/stage.pp, but it failed with errors about PHP versions. I tried removing php and php-devel, assuming that puppet would reinstall the versions it wanted, but now they won't install at all, and in fact
[root@web1.stage.releng.webapp.scl3 dmitchell]# yum-wrapper install php-devel
Pinned packages detected, excluding the following from yum install php-devel:
facter 2.0.2-1.el6
perl-DBD-MySQL 4.013-3.el6
puppet 3.2.4-1.el6
Command string: yum -x facter -x perl-DBD-MySQL -x puppet install php-devel
Loaded plugins: rhnplugin, security
This system is receiving updates from RHN Classic or RHN Satellite.
Setting up Install Process
No package php-devel available.
Error: Nothing to do
---
The objective here is to install MySQL-5.6 client on web1.stage.webapp.scl3.mozilla.com, celery1.stage.srv.releng.scl3.mozilla.com, then web*.releng.scl3.mozilla.com and celery*.srv.releng.scl3.mozilla.com, being cautious about downtime on the latter (production) nodes.
Assignee: dustin → server-ops-webops
Component: General Automation → WebOps: Other
Product: Release Engineering → Infrastructure & Operations
QA Contact: catlee → smani
Version: unspecified → other
|
|
Reporter | |
Comment 11•10 years ago
|
||
I managed to work around both issues I was having with the 5.1 client. Since this upgrade will affect all of the services on this cluster, the risk is reasonably high. Since this doesn't appear to be a "normal" upgrade for web apps, I'm thinking maybe we should not do the upgrade at all?
We're planning to move RelengAPI (and, eventually, everything else) to some kind of cloud-based continuous-deployment strategy over the winter, so we will likely end up with an updated MySQL client library automatically in that process, in a more controllable fashion.
|
|
Reporter | |
Comment 12•10 years ago
|
||
Well! It seems when I was trying to get this to work on Aug 31, I accidentally upgraded everything (including production) to mysql-community-client 5.6.14. That's not quite the library we want (we want MySQL-shared-compat-5.6.17) but it is MySQL-5.6. And nothing broke.
Fox2mike's got staging upgraded to the right package, and given the evidence from my accident, I think we're safe to upgrade the rest of it.
|
|
||
Comment 13•10 years ago
|
||
So would you like us to do that? or can you do that yourself? (it's a couple of lines in puppet). Let me know?
Flags: needinfo?(dustin)
|
|
Assignee | |
Updated•10 years ago
|
Assignee: server-ops-webops → cliang
|
|
Assignee | |
Comment 15•10 years ago
|
||
The releng webheads (web[12].releng.webapp.scl3) should now be using the same mysql client as stage (MySQL-client 5.6.17-1.el6). Does this also need to be done on the releng admin node?
|
|
Reporter | |
Comment 16•10 years ago
|
||
Preferably, yes. The more important bit is to do so on the celery nodes (celery*.{stage.,}srv.releng.scl3.mozilla.com). These are still infra-puppet hosts, despite being in the releng BU.
|
|
Assignee | |
Comment 17•10 years ago
|
||
Mysql updated on the celery and releng admin nodes. Leaving this bug open a few days on the off-chance that I've missed something or the update produces unwanted side effects.
|
|
Assignee | |
Updated•10 years ago
|
Status: NEW → RESOLVED
Closed: 10 years ago
Resolution: --- → FIXED
|
||
Updated•7 years ago
|
Product: Infrastructure & Operations → Infrastructure & Operations Graveyard
You need to log in
before you can comment on or make changes to this bug.
Description
•