Closed Bug 1023555 Opened 10 years ago Closed 10 years ago

Please create a new mysql database for production mapper


(Data & BI Services Team :: DB: MySQL, task)

Not set


(Not tracked)



(Reporter: pmoore, Assigned: mpressman)



We would like a new mysql database for mapper, which we hope to deploy to production in bug 1023541.

Database size is likely to be ~ 1GB or less.

Backup requirements are not very strict - it should be possible to create database from scratch relatively automatically within an hour.

It will be accessed primarily from production relengapi application cluster ( and

Please let me know if you need any further details.
Assignee: nobody → server-ops-database
Component: Tools → Server Operations: Database
Product: Release Engineering →
QA Contact: hwine → scabral
Is this db a tree-closing one? should it be on its own db instance or a shared one? What's the approximate volume of querying expected? What's the expected data size/growth rate?
(In reply to Sheeri Cabral [:sheeri] from comment #1)

> Is this db a tree-closing one?

Yes; the mapper application is a dependency of the python script in mozharness that builds b2g. If the mapper database goes down, it will prevent new b2g builds from running, and b2g trees will need to be closed.

> Should it be on its own db instance or a shared one?

I would say it can be on the already existing relengapi db instance (mysql://, since both the relengapi database and the (new) mapper database will be required by mapper application (since mapper is written in the relengapi framework as a relengapi blueprint) - therefore if either of these two databases go down, then mapper will fail. Therefore the two databases naturally sit together in terms of availability requirements, and therefore I would be happy for them to be on the same physical instance.

> What's the approximate volume of querying expected?

The querying level I expect to be extremely low. Developers *may* query it from time to time, and pull back all rows of the hashes table, which could be around 500,000 rows - but that is likely to be only a handful of devs, and I think at most once per hour (if they do more than this, we should block them, since there are much more efficient ways to pull back this data by only getting new rows).

The b2g build script for building b2g will only pull back a row at a time, maybe once per 5 mins(?) - basically one query for every b2g build that is run, which I think is usually every few minutes on average.

VCS Sync is the only other client of the database - but it will be populating the database rather than reading from it. The activity for this I have included in the answer to the following question.

> What's the expected data size/growth rate?

The schemas has two tables: hashes and projects.

hashes table

CREATE TABLE `hashes` (
  `hg_changeset` varchar(40) NOT NULL,
  `git_commit` varchar(40) NOT NULL,
  `project_id` int(11) NOT NULL,
  `date_added` int(11) NOT NULL,
  UNIQUE KEY `project_id__hg_changeset` (`project_id`,`hg_changeset`),
  UNIQUE KEY `project_id__git_commit` (`project_id`,`git_commit`),
  KEY `project_id` (`project_id`),
  KEY `hg_changeset` (`hg_changeset`),
  KEY `project_id__date_added` (`project_id`,`date_added`),
  KEY `git_commit` (`git_commit`),
  CONSTRAINT `hashes_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`)

This table will initially have around 500,000 rows, and should grow at around 200 rows per day.

projects table

CREATE TABLE `projects` (
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)

This table will start with around 30 rows, and grow with maybe 50 rows per year (probably in bursts).

I hope I haven't been too verbose - I wanted to make sure I provided all information, in case it is useful. Let me know if you have other questions though.

Many thanks in advance, and many thanks for all your help thus far! :)

Assignee: server-ops-database → mpressman
prod database named mapper created on same host as existing releng db instance
Great, thanks!
Ok, credentials created, but I wasn't able to test due to the vip listed in Comment 2 ( is unresolved

[mpressman@backup3.db.scl3 ~]$ host

However, I am fairly confident as I used the same grants as the relengapi user connecting to relengapi database switching only the user, database and password.
Thanks Matthew. I'm testing now. :)
Hi Matthew, Dustin,

I've hit a problem that is probably related to comment 5 above - *but* it is not so straightforward to understand, because it seems that the relengapi framework is (at least, partly) running in production (, and this is also using the same mysql host... So I haven't quite got my head around this yet, since if the mysql host was not available, I would have thought relengapi app would not be able to run (however it is running as you see from the link).

The log files for relengapi show some celery problems (which is something I'm not using, so might be harmless):


2014-05-19 09:19:26,794 [] Exception on /base/temp/test-celery [GET]
Traceback (most recent call last):
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/", line 1379, in handle_user_exception
    return handler(e)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/lib/", line 96, in exc_handler
    return h.handle_exception(exc_type, exc_value, tb)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/flask/", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/blueprints/base/", line 82, in test_celery
    answer = add.delay(1, 2, 3).get()
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/celery/", line 152, in get
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/celery/backends/", line 153, in wait_for
    meta = self.consume(task_id, timeout=timeout)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/celery/backends/", line 222, in consume
    no_ack=True, accept=self.accept) as consumer:
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/", line 359, in __init__
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/", line 371, in revive
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/", line 381, in declare
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/", line 504, in declare
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/kombu/", line 166, in declare
    nowait=nowait, passive=passive,
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/amqp/", line 613, in exchange_declare
    self._send_method((40, 10), args)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/amqp/", line 56, in _send_method
    self.channel_id, method_sig, args, content,
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/amqp/", line 221, in write_method
    write_frame(1, channel, payload)
  File "/data/www/relengapi/virtualenv/lib/python2.7/site-packages/amqp/", line 173, in write_frame
    frame_type, channel, size, payload, 0xce,
  File "/usr/lib/python2.7/", line 224, in meth
    return getattr(self._sock,name)(*args)
error: [Errno 32] Broken pipe

So I can't work out if relengapi is:
    a) surviving despite its database not being available; or
    b) is able to connect to its own database.

I tried from relengwebadm and also from the production web heads directly, e.g.:

From a web head
[root@web1.releng.webapp.scl3 ~]# mysql -u mapper -pXXXXXXXXXXXX -h mapper
ERROR 2005 (HY000): Unknown MySQL server host '' (1)

From admin node
[root@relengwebadm.private.scl3 relengapi]# mysql -u mapper -pXXXXXXXXXXXX -h mapper
ERROR 2005 (HY000): Unknown MySQL server host '' (1)

also when trying to generate the schema for production mapper from the admin node:

[root@relengwebadm.private.scl3 relengapi]# RELENGAPI_SETTINGS=/data/releng/src/relengapi/ /data/releng/src/relengapi/virtualenv/bin/relengapi createdb
2014-06-13 04:59:54,981 registering blueprint docs
2014-06-13 04:59:55,027 registering blueprint base
2014-06-13 04:59:55,028 registering blueprint tokenauth
2014-06-13 04:59:55,029 registering blueprint auth
2014-06-13 04:59:55,029 registering blueprint mapper
2014-06-13 04:59:55,032 registering blueprint docs
2014-06-13 04:59:55,033 creating tables for database mapper
Traceback (most recent call last):
  File "/data/releng/src/relengapi/virtualenv/bin/relengapi", line 9, in <module>
    load_entry_point('relengapi==0.2.1', 'console_scripts', 'relengapi')()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/", line 56, in main, args)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/relengapi/blueprints/base/", line 49, in run
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/sql/", line 3291, in create_all
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/", line 1546, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "/usr/lib/python2.7/", line 17, in __enter__
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/", line 1539, in _optional_conn_ctx_manager
    with self.contextual_connect() as conn:
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/", line 1729, in contextual_connect
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/", line 332, in connect
    return _ConnectionFairy._checkout(self)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/", line 626, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/", line 433, in checkout
    rec = pool._do_get()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/", line 945, in _do_get
    return self._create_connection()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/", line 278, in _create_connection
    return _ConnectionRecord(self)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/", line 404, in __init__
    self.connection = self.__connect()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/", line 527, in __connect
    connection = self.__pool._creator()
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/", line 95, in connect
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/util/", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/", line 89, in connect
    return dialect.connect(*cargs, **cparams)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/sqlalchemy/engine/", line 376, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/MySQLdb/", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/data/releng/src/relengapi/virtualenv/lib/python2.7/site-packages/MySQLdb/", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (OperationalError) (2005, "Unknown MySQL server host '' (1)") None None
[root@relengwebadm.private.scl3 relengapi]# 

So the mystery to me is how relengapi is running at all in production, if this host can't be reached or is not known about, since the same database host is used by mapper and relengapi, i.e. (with passwords hidden):

[root@relengwebadm.private.scl3 relengapi]# cat 
    'relengapi': 'mysql://',
    'mapper': 'mysql://',

## Celery
# Any Celery configuration option can be included here; see



    'type': 'external',
    'environ': 'AUTHENTICATE_MAIL',

admin = [
# support for ldap-groups isn't landed yet
    'type': 'ldap-groups',
    'group-actions': {
        'team_relops': admin,
        'team_releng': admin,
    # Base LDAP URI
    'uri': "ldap://",

    # This needs to be a user that has sufficient rights to read users and groups
    'login_dn': "uid=bind-relengapi,ou=logins,dc=mozilla",
    'login_password': "XXXXXXXXXXXXXX",

    # The search bases for users and groups, respectively
    'user_base': 'dc=mozilla',
    'group_base': 'dc=mozilla',

    # set this to True for extra logging
    #'debug': True,
   'type': 'static',
   'actions': {
       '': [

P.S. (For Dustin) I still have to migrate some of the other settings from staging to production - but that is independent from this problem.
Flags: needinfo?(dustin)
Looks like I didn't put the full output for the vip, only the command, here it is in full:

[mpressman@admin1a.private.scl3 ~]$ host host
host: couldn't get address for '': not found
Let's try this one last time, third times a charm:

[mpressman@admin1a.private.scl3 ~]$ host
Host not found: 3(NXDOMAIN)

Are you sure the mysql host is correct in the releng production config ('')?

    'relengapi': 'mysql://',

It's wrong :)

Some DB vips are named foo-rw-vip, some are named foo-rw-zeus, and some are named foo-rw-zlb.  I probably copied the config from staging (where we've been doing most of our work, and where the vip is named staging-rw-vip), changed the name, and never looked at the logs.

Relengapi will happily start up and, since most of the stuff it does is not DB-related, serve pages, even if its DB is misconfigured.

Anyway, try it with
Thanks Dustin, unfortunately I can't seem to access either. :(

[root@relengwebadm.private.scl3 relengapi]# mysql -u mapper -pXXXXXXXXXXX -h mapper
ERROR 2003 (HY000): Can't connect to MySQL server on '' (110)
[root@relengwebadm.private.scl3 relengapi]#
Seems to exist this time, though, so maybe either a flow problem, or service is down?

[root@web1.releng.webapp.scl3 ~]# host has address
Indeed, we need a flow.  I could swear we were already using DBs on the generic cluster..
Thanks Dustin!
Flags: needinfo?(dustin)
Hey Dustin,

Should we also ask for a flow from relengwebadm too (e.g. to run relengapi createdb from there)?

Thanks Dustin!
Oh, good point.
By the way, I also noticed we have mysql client installed on production web heads, but not on staging webhead - should we add that to the IT puppet repo? Although maybe no need to have it - just thought it good to be consistent.
Sorry, wrong bug! Ignore comment above.
Closing this bug, as the database has been created, and is working correctly! Thanks Matt and Dustin!
Closed: 10 years ago
Resolution: --- → FIXED
No longer depends on: 1026117
Product: → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.