Hi all there,
Is pend three evenings getting this it up and running, without result ;-(
I followed the JDBC Persistence threat on the wiki, checked all the topics in this forum…
Have Mysql 5.5.50 / mysql-connector-java-5.1.39-bin.jar /OH 1.8.3 / pi with minibian
Have made a user and a db in mysql with all rights on that db from anywhere.
I can create a table and a record from Mysqlworkbench. Dropped the table and restarted de rpi
Below you will find the requested files. I’m still trying to find a solution, but till now not successful.
Can you confirm that setting up an empty db schema with enough access rights is sufficient. The necessary tables are setup during the first write action to the database…
Grtz Jan
The mysqlpart from the openhab.cfg
############################ jdbc:mysql://localhost:3306/openhabSQL Persistence Service ##################################
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
# mysql:url=jdbc:mysql://127.0.0.1:3306/openhab
jdbc:url=jdbc:mysql://192.168.0.5/openhab
# the database user
jdbc:user=openhab
# the database password (pw will be changed when working ;-)
jdbc:password=openhab
# the reconnection counter
jdbc:reconnectCnt=1
# the connection timeout (in seconds)
#mysql:waitTimeout=
persistence:default=jdbc
# for Migration from MYSQL-Bundle set to 'Item'.
# jdbc:tableNamePrefix=Item
#
# for Migration from MYSQL-Bundle do not use real names.
jdbc:tableUseRealItemNames=true
#
# for Migration from MYSQL-Bundle set to 0.
jdbc:tableIdDigitCount=0
My jdbc.persist file
// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
everyHour : "0 0 * * * ?"
everyDay : "0 0 0 * * ?"
// if no strategy is specified for an item entry below, the default list will be used
default = everyChange
}
Items {
// persist all items once a day and on every change and restore them from the db at startup
// careful, this might result in huge databases - that's why it is in comment here ;-)
* : strategy = everyChange, everyDay, restoreOnStartup
// persist all temperature and weather values at every change and every hour
// * : strategy = everyChange, everyHour
}
/etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file = /var/log/mysql/mysql.log
general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#slow_query_log = 1
#long_query_time = 2
#log_queries_not_using_indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
Made the same setup on windows 10, OH and MySQL running on this host. Copied the configuration, and de jdbc driver file This results in the same trouble. Also tried some earlier jdbc drivers.
seems you are missing the port in your openhab.config:
jdbc:url=jdbc:mysql://192.168.0.5/openhab
but should be:
jdbc:url=jdbc:mysql://192.168.0.5:3306/openhab
Just out of curiosity: if you have a mysql server, why not just use the mysql binding? It’s much more straightforward
Regards,
-OLI
Hi Oliver, thanks for reply.
Already tried with and without the port number, without any difference. Back from holiday i will try de direct mysql binding. Let you know the result!
Hi there,
back from holiday… again started my OH server. Moved from jdbc to mysqsl in de config file, removed de jdbc.persistence and the jdbc.persistence addon.
Now its working as expected.
It’s not clear for me why it wouldn’t work this way the first time I started with the mysql solution ;-(
