Initialize database tables in puppet manifest

[jeff1evesque]

We will replace our python database scripts with a puppet manifest database.pp. This file will be called by the corresponding Vagrantfile.

Note: we will need to create a corresponding sql user account.

[jeff1evesque]

We notice that our Ubuntu Server 14.04 installs Mariadb 5.5.44 via apt-get:

vagrant@vagrant-ubuntu-trusty-64:~$ dpkg -l mariadb-server
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name           Version      Architecture Description
+++-==============-============-============-=================================
ii  mariadb-server 5.5.44-1ubun all          MariaDB database server (metapack

Since we've installed puppetlabs-mysql, we need to compare the difference with an installation of the above MariaDB. This can be done by implementing SHOW GLOBAL VARIABLES on the MariaDB instance to check for specific variables that are desired. Specifically, we will be implementing additional features (variables) from MariaDB that vanilla MySQL does not support (in the puppet module).

We can approach comparing the difference between MySQL 5.6, and MariaDB 5.5 by looking at the changelogs between current, and previous versions; checking the GLOBAL VARIABLES; and, checking the documentation to ensure the puppet module can be adjusted for missing features.

[jeff1evesque]

We still need to properly define our python-mariadb bindings. Also, the above comment regarding the comparison of vanilla MySQL, and MariaDB is no longer relevant.

[jeff1evesque]

We need to remove setup.py.

[jeff1evesque]

We need to ensure that app.py is implemented in setup_database.pp as follows:

...
## define database tables
exec {'create-database-tables':
    command => 'python ../../app.py && python setup_tables.py',
    cwd => '/vagrant/puppet/scripts/',
    require => Class['::mysql::server'],
}
...

Otherwise, setup_tables.py will not have scope to execute the following import statement:

...
from brain.database.db_query import SQL
...

[jeff1evesque]

We need to resolve the following vagrant up traceback:

...
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_grant[authenticated@localhost/*.*]/options: defined 'options' as 'GRANT'
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: Traceback (most recent call last):
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "../../app.py", line 3, in <module>
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:
 from web_interface import app
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/vagrant/web_interface/__init__.py", line 13, in <module>
==> default:
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:
 import web_interface.views
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/vagrant/web_interface/views.py", line 8, in <module>
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:
 from brain.load_data import Load_Data
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/vagrant/brain/load_data.py", line 7, in <module>
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:
 from brain.session.data_append import Data_Append
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/vagrant/brain/session/data_append.py", line 11, in <module>
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:
 from brain.session.base_data import Base_Data
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/vagrant/brain/session/base_data.py", line 9, in <module>
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:
 from brain.database.save_entity import Save_Entity
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/vagrant/brain/database/save_entity.py", line 6, in <module>
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:
 from brain.database.db_query import SQL
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/vagrant/brain/database/db_query.py", line 5, in <module>
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:
 import MySQLdb as DB
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: ImportError: No module named MySQLdb
==> default: Error: python ../../app.py && python setup_tables.py returned 1 ins
tead of one of [0]
==> default: Error: /Stage[main]/Main/Exec[create-database-tables]/returns: change from notrun to 0 failed: python ../../app.py && python setup_tables.py returned 1 instead of one of [0]
==> default: Notice: /Stage[main]/Mysql::Bindings::Python/Package[python-mysqldb]/ensure: ensure changed 'purged' to 'present'
==> default: Notice: Finished catalog run in 23.28 seconds
The SSH command responded with a non-zero exit status. Vagrant
assumes that this means the command failed. The output for this command
should be in the log above. Please read the output to determine what
went wrong.

[jeff1evesque]

We need to make a minor comment change.

[jeff1evesque]

notify does not execute from within a class, if not nested in another structure (exec, file, package).

[jeff1evesque]

Currently the SQL user authenticated does not have the privileges to create database tables. Also, the flask server python app.py will run indefinitely, which may cause the latter command python setup_tables.py from the following (in setup_database.py) to never execute:

...
    command => 'python ../../app.py && python setup_tables.py',
...

[jeff1evesque]

7b85805: we've removed python ../../app.py from setup_database.pp, since start_webserver.pp will be responsible for starting app.py, when the system starts up.

[jeff1evesque]

0fed2b7, 095619d, 9013a0d, 856a3d9: were all committed incorrectly to this issue, and have been removed via git reset --hard HEAD~3, and git commit --amend -m "#1963: ...".

[jeff1evesque]

We need to move the sequence of setup_database.pp, after start_webserver.pp in our Vagrantfile. Otherwise, setup_tables.py will not have reference to db_query.py, relative to the flask server, app.py.

[jeff1evesque]

Currently our vagrant build, has the following error traceback:

...
==> default: Running provisioner: puppet...
==> default: Running Puppet with setup_database.pp...
==> default: stdin: is not a tty
==> default: Warning: Setting templatedir is deprecated. See http://links.puppetlabs.com env-settings-deprecations
==> default:    (at /usr/lib/ruby/vendor_ruby/puppet/settings.rb:1139:in `issue_deprecation_warning')
==> default: Warning: Could not retrieve fact fqdn
==> default: Warning: Config file /etc/puppet/hiera.yaml not found, using Hiera defaults
==> default: Notice: Compiled catalog for vagrant-ubuntu-trusty-64 in environment production in 4.37 seconds
==> default: Notice: /Stage[main]/Mysql::Server::Install/Package[mysql-server]/ensure: ensure changed 'purged' to 'present'
==> default: Notice: /Stage[main]/Mysql::Server::Config/File[mysql-config-file]/content: content changed '{md5}77f15d6c87f9c136c4efcda072017f71' to '{md5}0574089359c965e31cf588a39c0d446d'
==> default: Notice: /Stage[main]/Mysql::Server::Service/File[/var/log/mysql/error.log]/group: group changed 'adm' to 'mysql'
==> default: Notice: /Stage[main]/Mysql::Server::Root_password/Mysql_user[root@localhost]/password_hash: defined 'password_hash' as '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
==> default: Notice: /Stage[main]/Mysql::Server::Root_password/File[/root/.my.cnf]/ensure: defined content as '{md5}6bfac0e7e9decf8667dfc388be899b33'
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_user[provisioner@localhost]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_database[db_machine_learning]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_user[authenticated@localhost]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_grant[authenticated@localhost/db_machine_learning.*]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Server::Providers/Mysql_grant[provisioner@localhost/db_machine_learning.*]/ensure: created
==> default: Notice: /Stage[main]/Mysql::Client::Install/Package[mysql_client]/ensure: ensure changed 'purged' to 'present'
==> default: Notice: /Stage[main]/Mysql::Bindings::Python/Package[python-mysqldb]/ensure: ensure changed 'purged' to 'present'
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: Traceback (most recent call last):
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "setup_tables.py", line 71, in <module>
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: cur.execute(sql_statement)
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: self.errorhandler(self, exc, value)
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns:   File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: raise errorclass, errorvalue
==> default: Notice: /Stage[main]/Main/Exec[create-database-tables]/returns: _mysql_exceptions.OperationalError: (1142, "REFERENCES command denied to user 'prov
isioner'@'localhost' for table 'tbl_dataset_entity'")
==> default: Error: python setup_tables.py returned 1 instead of one of [0]
==> default: Error: /Stage[main]/Main/Exec[create-database-tables]/returns: change from notrun to 0 failed: python setup_tables.py returned 1 instead of one of [0]
==> default: Notice: Finished catalog run in 14.67 seconds
The SSH command responded with a non-zero exit status. Vagrant
assumes that this means the command failed. The output for this command
should be in the log above. Please read the output to determine what
went wrong.

We also know that the creation of a foreign key constraint requires at least one of the SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges for the parent table. However, we are not using foreign keys as intended. Rather we are linking tables by common table columns. Therefore, we can remove any creation of foreign keys in setup_tables.py.