JDBC connectivity issues with predictionIO and MySQL
#P Joshi
output from mysql is as below, so looks like mysql is working fine on my side. It could be the issue with ID/Password as I have never tried a jdbc connection to mysql on my Mac before. Searched Stackoveflow but the issue seems slightly different.
$ mysqld
2016-02-24T23:06:58.190430Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-02-24T23:06:58.222001Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2016-02-24T23:06:58.222090Z 0 [Note] mysqld (mysqld 5.7.10) starting as process 72222 ...
2016-02-24T23:06:58.374383Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2016-02-24T23:06:58.422915Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-02-24T23:06:58.422959Z 0 [Note] InnoDB: Uses event mutexes
2016-02-24T23:06:58.422973Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2016-02-24T23:06:58.422985Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-24T23:06:58.428694Z 0 [Note] InnoDB: Number of pools: 1
2016-02-24T23:06:58.434829Z 0 [Note] InnoDB: Using CPU crc32 instructions
2016-02-24T23:06:58.513699Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2016-02-24T23:06:58.534296Z 0 [Note] InnoDB: Completed initialization of buffer pool
2016-02-24T23:06:58.624120Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-02-24T23:06:59.062544Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2016-02-24T23:06:59.062703Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2016-02-24T23:06:59.219468Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2016-02-24T23:06:59.220985Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016-02-24T23:06:59.221010Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016-02-24T23:06:59.222869Z 0 [Note] InnoDB: Waiting for purge to start
2016-02-24T23:06:59.277108Z 0 [Note] InnoDB: 5.7.10 started; log sequence number 2494928
2016-02-24T23:06:59.278094Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/var/mysql/ib_buffer_pool
2016-02-24T23:06:59.278422Z 0 [Note] InnoDB: not started
2016-02-24T23:06:59.278760Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016-02-24T23:06:59.361741Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2016-02-24T23:06:59.361782Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2016-02-24T23:06:59.435124Z 0 [Note] InnoDB: Buffer pool(s) load completed at 160224 18:06:59
2016-02-24T23:06:59.483897Z 0 [Warning] CA certificate ca.pem is self signed.
2016-02-24T23:06:59.484889Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2016-02-24T23:06:59.532179Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2016-02-24T23:06:59.532332Z 0 [Note] IPv6 is available.
2016-02-24T23:06:59.532359Z 0 [Note] - '::' resolves to '::';
2016-02-24T23:06:59.532382Z 0 [Note] Server socket created on IP: '::'.
2016-02-24T23:07:00.762440Z 0 [Note] Event Scheduler: Loaded 0 events
2016-02-24T23:07:00.763093Z 0 [Note] mysqld: ready for connections.
Version: '5.7.10' socket: '/tmp/mysql.sock' port: 3306 Homebrew
2) Further I tinkered with the database.yml to change Id/password as root/none as below:
ostgres: &postgres
adapter: postgresql
username: pio
password: pio
encoding: utf8
host: localhost
database: tapster_development
sqlite: &sqlite
adapter: sqlite3
database: db/development.sqlite3
pool: 5
timeout: 5000
mysql: &mysql
adapter: mysql2
encoding: utf8
username: root
password:
pool: 5
3) Then I changed the poi-env.sh file to reflect the above change, because the default settings didnt seem to work at all.
# MySQL Example
PIO_STORAGE_SOURCES_MYSQL_TYPE=jdbc
PIO_STORAGE_SOURCES_MYSQL_URL=jdbc:mysql://localhost:3306/pio
PIO_STORAGE_SOURCES_MYSQL_USERNAME=root
PIO_STORAGE_SOURCES_MYSQL_PASSWORD=
4) Then I ran the pio status command and got this output this time. Looks like DB name pio is incorrect.
$ pio status
[INFO] [Console$] Inspecting PredictionIO...
[INFO] [Console$] PredictionIO 0.9.5 is installed at /Users/jessep/PredictionIO
[INFO] [Console$] Inspecting Apache Spark...
[INFO] [Console$] Apache Spark is installed at /Users/jessep/PredictionIO/vendors/spark-1.5.1
[INFO] [Console$] Apache Spark 1.5.1 detected (meets minimum requirement of 1.3.0)
[INFO] [Console$] Inspecting storage backend connections...
[INFO] [Storage$] Verifying Meta Data Backend (Source: MYSQL)...
[ERROR] [Console$] Unable to connect to all storage backends successfully. The following shows the error message from the storage backend.
[ERROR] [Console$] Unknown database 'pio' (com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException)
[ERROR] [Console$] Dumping configuration of initialized storage backend sources. Please make sure they are correct.
[ERROR] [Console$] Source Name: MYSQL; Type: jdbc; Configuration: PASSWORD -> , URL -> jdbc:mysql://localhost:3306/pio, TYPE -> jdbc, USERNAME -> root