pagojo
commented
7 years ago Can you please post your thoth.conf file from your testone project here
Closed gusandrianos closed 7 years ago
pagojo
commented
7 years ago Can you please post your thoth.conf file from your testone project here
gusandrianos
commented
7 years ago #
# thoth.conf - Thoth configuration file.
#
# Customize this file as desired. You must restart Thoth before configuration
# changes will take effect.
#
# This file is YAML, but you may also use Erubis expressions to insert variables
# or run Ruby code.
#
# Settings for live mode.
live:
# Connection URI for the database. Currently SQLite3 and MySQL are supported.
# PostgreSQL may work, but hasn't been tested.
#
# Sample SQLite3 config:
# db: sqlite:////absolute/path/to/database.db
#
# Sample MySQL config:
# db: mysql://user:pass@hostname/database
db: mysql2://user:password@hostname/t1
# General site settings.
site:
# Name of your blog. This will be displayed as the title of your blog's
# index page and in your blog's feeds.
name: New Thoth Blog
# A brief description or subtitle for your blog.
desc: Thoth is awesome.
# Base URL of your site. This is necessary in order for Thoth to know how
# to construct links. Be sure to set this correctly or links may not work.
url: http://localhost:7000/
# URLs of CSS files to load in addition to the default Thoth CSS. You can
# override Thoth's default styles by specifying your own CSS files here.
# These can be relative or absolute URLs.
#
# Example:
# css:
# - /css/foo.css
# - 'http://example.com/bar.css'
#
css: []
# URLs of JS files to load in addition to the default Thoth JS. You can
# extend or override Thoth's JS or add functionality by specifying your own
# JS files here. These can be relative or absolute URLs.
#
# Example:
# js:
# - /js/foo.js
# - 'http://example.com/bar.js'
#
js: []
# Whether or not to allow visitors to post comments in response to blog
# posts.
enable_comments: true
# Whether or not you want to expose a sitemap for your blog. A sitemap is
# an XML file that gives search engines information about all the URLs on
# your site so they can index them more easily.
#
# If enabled, you'll be able to see your sitemap at
# http://yourdomain.com/sitemap and you can submit this URL to search
# engines to improve the indexing of your site (and possibly your pagerank).
enable_sitemap: true
# Gravatar settings.
gravatar:
# Whether or not to enable Gravatar images on comments. Gravatar is a free
# service that allows people to associate profile images with an email
# address so that their postings on blogs and other websites are easily
# identifiable. Learn more at http://www.gravatar.com/.
enabled: true
# Default icon set to use for users who don't have custom Gravatars. See
# gravatar.com for the latest options. As of this writing, the available
# choices are "identicon" (geometric shapes), "monsterids" (cutesy little
# monsters), and "wavatars" (cutesy geometric shapes).
#
# Alternatively, you can specify the URL of your own custom image here and
# that image will be used instead.
default: identicon
# Maximum Gravatar rating to allow. Available ratings are "g", "pg", "r",
# and "x". Just like with movies, "r" and "x"-rated Gravatars may contain
# adult content.
rating: g
# Gravatar size in pixels. This sets both the width and the height.
size: 32
# Administrator settings.
admin:
# Your name. This will be displayed in the copyright notice at the bottom of
# the page, and will be used as the author name for your site's feeds.
name: John Doe
# Your email address. If you leave this blank or comment it out, it won't be
# displayed.
email: ''
# Administrator username. This is the name you'll use to log into Thoth.
user: thoth
# Administrator password. By default this is a random number, so you won't
# be able to log in until you change this!
pass: thoth
# String of random characters to add uniqueness to the admin auth cookie
# hash. Just fill this with some made-up nonsense.
seed: 6d552ac197a862b82b85868d6c245feb
# Plugins that should be loaded when Thoth starts. Usually it's not
# necessary to load a plugin until the first time it's used, but some plugins
# alter core functionality and thus must be loaded at start time.
#
# Example:
# plugins:
# - foo
# - bar
# - baz
#
plugins: []
# Absolute path to a directory where uploaded media files (images, videos,
# etc.) for your blog posts and pages should be stored. This directory needs
# to be readable and writeable by the user running the Thoth server.
media: <%= Thoth::HOME_DIR %>/media
# Server settings.
server:
# Server adapter to use. This can be any adapter Ramaze supports.
adapter: webrick
# IP address on which Thoth should listen for connections. Specify 0.0.0.0
# if you want Thoth to listen on all addresses.
address: 0.0.0.0
# Port on which the Thoth server should listen for connections.
port: 7000
# Whether or not to enable caching. Enabling caching will significantly
# improve Thoth's performance under heavy traffic.
enable_cache: true
# Whether or not to enable automatic, on the fly minification of your blog's
# external CSS and JavaScript files. Enabling minification will reduce
# page weight and is a great way to improve performance, but you'll need to
# restart Thoth in order for CSS and JS changes to take effect.
#
# Note: this setting will have no effect if you're running Thoth under
# Phusion Passenger or any other environment in which static file requests
# are handled by a frontend server without passing through Ramaze.
enable_minify: true
# Filename to which errors should be logged when running in production mode,
# or blank if you don't care about errors.
error_log: <%= Thoth::HOME_DIR %>/log/error.log
# Memcache settings.
memcache:
# Whether or not to enable memcache. When enabled along with the
# server.enable_cache setting above, Thoth will use memcache for all cache
# operations instead of the default per-process Hash-based cache.
#
# This may be beneficial if you have a very high-traffic blog and
# distribute load across multiple Ramaze processes or physical servers,
# but in most cases it's overkill.
#
# Note: This option requires that the memcache-client gem be installed.
enabled: false
# Memcache servers to use. Each entry in this list must contain at least
# a hostname, and optionally a port number and priority.
#
# Example:
# servers:
# - 'localhost:11211:1'
# - 'foo.example.com:11211:2'
#
servers:
- 'localhost:11211:1'
# Timestamp formats.
timestamp:
# Format for long timestamps. For details, see:
# http://www.ruby-doc.org/core/classes/Time.html#M000297
long: '%A %B %d, %Y @ %I:%M %p (%Z)'
# Format for short timestamps. For details, see:
# http://www.ruby-doc.org/core/classes/Time.html#M000297
short: '%Y-%m-%d %I:%M'
# Settings for dev mode. Any setting that you don't explicitly specify here will
# just inherit from live mode.
dev:
db: sqlite:///<%= Thoth::HOME_DIR %>/db/dev.db
server:
enable_cache: false
enable_minify: falseI can see that you are in directory t1 but the exception refers to testone.
Did you do thoth --create t1 or thoth --create testone?
Looks like something is out of sync there. For example the error message refers to testone whereas the thoth.conf refers to t1. Can you do it again from the start and post the steps here please?
Can you see what tables have been generated in MySQL please?
Are the user:password@hostname set to the correct credentials?
Have you created a user in MySQL with these credentials? (are there in the MySQL users table?)
gusandrianos
commented
7 years ago Okay, I created a new one.
Steps.
thoth --create test
cd test
change to mysql2 and enter my credentials, doing /test for the database name.
thoth --migrate
E [2017-07-06 12:39:49 $28409] ERROR | : Unable to connect to database: Mysql2::Error: Unknown database 'test'
In MySQL, no database has been created
+----------------------+
| Database |
+----------------------+
| information_schema |
| ebenezerDB |
| ebenezer_development |
| ebenezer_test |
| finaldb |
| my_accounts |
| my_training |
| mydb |
| mysql |
| performance_schema |
| sys |
| tmp |
| user |
+----------------------+Manually creating a database named test and then running the migration causes this.
Error: Mysql2::Error: Table 'test.posts' doesn't existThe credentials are correct.
pagojo
commented
7 years ago @GusAndrianos it seems from the first error message that you didn't create the database.
However, the last part of your post is relevant to the problem here because it suggests that the database was then created successfully.
Please paste here the steps/commands for how you created the test database and how you granted the credentials to it.
What happens after you create the database and you run the migration?
Does the migration run ok? What does it say?
In MySQL what is the schema of that database after the migration?
gusandrianos
commented
7 years ago I created the database myself through mysql server with create database test;
The credentials I gave were the same I give when launching mysql like mysql -u user -p
I described at the end of my last comment what happens when I run the migration again.
The schema remains the same throughout the process, that is, empty.
pagojo
commented
7 years ago First of all, just to clarify. When not using SQLite3, a database must be created manually by the database admin with the right credentials. Therefore this must always be the case before migrations are run.
Try this.
create a new MySQL database testx with the right credentials and access rights.
then
thoth --create testx
cd testx
thoth --log-sql sqllog --migrate 01
thoth --irbIn the IRB type
Thoth::open_dband please post here what is the result of the open_db as well as the contents of the sqllog.
gusandrianos
commented
7 years ago The result of
thoth --log-sql sqllog --migrate 01
is
Error: Mysql2::Error: Table 'testx.posts' doesn't exist
and the result of
Thoth::open_db
is
=> #<Sequel::Mysql2::Database: "mysql2://user:mypassword@localhost/testx" {:encoding=>"utf8"}>
So I assume that it finds the database, has access to it, but cannot complete the migration.
pagojo
commented
7 years ago Silly questions but does the "mysql2://user:mypassword@localhost/testx" really have user and mypassword as credentials?
Migration 01 only creates the tables, so the error message is a bit odd. Can you please check the sqllog inside the testx folder and copy it here please?
gusandrianos
commented
7 years ago Silly indeed. Have you tried it yourself and got a different result?
The database I create is empty, but, after the migration
+-----------------+
| Tables_in_testx |
+-----------------+
| schema_info |
+-----------------+
1 row in set (0,00 sec)Which implies that the credentials are indeed correct (and they are) and thoth has access to mysql.
Here's the log.
# Logfile created on 2017-07-11 17:04:10 +0300 by logger.rb/56815
I, [2017-07-11T17:04:10.626830 #6470] INFO -- : (0.111593s) CREATE TABLE IF NOT EXISTS `schema_info` (`version` integer NOT NULL DEFAULT 0)
I, [2017-07-11T17:04:10.630201 #6470] INFO -- : (0.003036s) SELECT * FROM `schema_info` LIMIT 1
I, [2017-07-11T17:04:10.630953 #6470] INFO -- : (0.000279s) SELECT 1 AS `one` FROM `schema_info` LIMIT 1
I, [2017-07-11T17:04:10.642361 #6470] INFO -- : (0.011103s) INSERT INTO `schema_info` (`version`) VALUES (0)
I, [2017-07-11T17:04:10.644400 #6470] INFO -- : (0.001733s) SELECT count(*) AS `count` FROM `schema_info` LIMIT 1
I, [2017-07-11T17:04:10.644995 #6470] INFO -- : (0.000264s) SELECT `version` FROM `schema_info` LIMIT 1
I, [2017-07-11T17:04:10.645936 #6470] INFO -- : Begin applying migration version 1, direction: up
E, [2017-07-11T17:04:10.646582 #6470] ERROR -- : Mysql2::Error: Table 'testx.comments' doesn't exist: SELECT NULL AS `nil` FROM `comments` LIMIT 1
E, [2017-07-11T17:04:10.649407 #6470] ERROR -- : Mysql2::Error: Table 'testx.posts' doesn't exist: DESCRIBE `posts`I assume you posted the whole log right?
This is very interesting, we may have stumbled on a bug outside our control.
Please can you list the exact versions of your mysql2 gem, Sequel gem and MySQL server?
gusandrianos
commented
7 years ago Of course I did. Here are the versions.
pagojo
commented
7 years ago It seems that when Sequel tries to check that a table is not there (before it attempts to create it during the migration) the database driver throws an exception, which Sequel cannot handle.
For the testx project that you previously created (as discussed above), please try the following
cd testx
thoth --irb
Thoth::open_db
Thoth.db.table_exists?("schema_info")
Thoth.db.table_exists?("comments")The result is false and false...
pagojo
commented
7 years ago Can you please paste all the output from the IRB session?
gusandrianos
commented
7 years ago Sorry, true for schema_info
gus@main:~/Desktop/testx$ thoth --irb
2.4.1 :001 > Thoth::open_db
=> #<Sequel::Mysql2::Database: "mysql2://USER:PASS@localhost/testx" {:encoding=>"utf8"}>
2.4.1 :002 > Thoth.db.table_exists?("schema_info")
=> true
2.4.1 :003 > Thoth.db.table_exists?("comments")
=> false ok, now please check the following
unless Thoth.db.table_exists?(:comments)
Thoth.db.create_table :comments do
primary_key :id
varchar :author, :null => false
varchar :author_url
varchar :title, :null => false
text :body, :default => ''
text :body_rendered, :default => ''
varchar :ip
datetime :created_at, :null => false
datetime :updated_at, :null => false
foreign_key :post_id, :table => :posts
index :post_id
end
endDoes the above complete ok?
What does Thoth.db.table_exists?("comments") now say?
gusandrianos
commented
7 years ago Hello. This is what happened.
2.4.1 :033 > Thoth::open_db
=> #<Sequel::Mysql2::Database: "mysql2://USER:PASS@localhost/testx" {:encoding=>"utf8"}>
2.4.1 :034 > unless Thoth.db.table_exists?(:comments)
2.4.1 :035?> Thoth.db.create_table :comments do
2.4.1 :036 > primary_key :id
2.4.1 :037?>
2.4.1 :038 > varchar :author, :null => false
2.4.1 :039?> varchar :author_url
2.4.1 :040?> varchar :title, :null => false
2.4.1 :041?> text :body, :default => ''
2.4.1 :042?> text :body_rendered, :default => ''
2.4.1 :043?> varchar :ip
2.4.1 :044?> datetime :created_at, :null => false
2.4.1 :045?> datetime :updated_at, :null => false
2.4.1 :046?>
2.4.1 :047 > foreign_key :post_id, :table => :posts
2.4.1 :048?> index :post_id
2.4.1 :049?> end
2.4.1 :050?> end
Sequel::DatabaseError: Mysql2::Error: Table 'testx.posts' doesn't exist
from /home/gus/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.6/lib/mysql2/client.rb:120:in `_query'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.6/lib/mysql2/client.rb:120:in `block in query'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.6/lib/mysql2/client.rb:119:in `handle_interrupt'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/mysql2-0.4.6/lib/mysql2/client.rb:119:in `query'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/mysql2.rb:141:in `block in _execute'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/database/logging.rb:45:in `log_connection_yield'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/mysql2.rb:136:in `_execute'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/utils/mysql_mysql2.rb:37:in `block in execute'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/database/connecting.rb:301:in `block in synchronize'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/connection_pool/threaded.rb:107:in `hold'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/database/connecting.rb:301:in `synchronize'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/utils/mysql_mysql2.rb:37:in `execute'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/dataset/actions.rb:1073:in `execute'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/mysql2.rb:291:in `execute'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/mysql2.rb:254:in `fetch_rows'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/dataset/actions.rb:153:in `each'
... 2 levels...
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/shared/mysql.rb:493:in `schema_parse_table'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/database/query.rb:163:in `schema'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/shared/mysql.rb:459:in `primary_key_from_schema'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/shared/mysql.rb:394:in `block in create_table_sql'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/shared/mysql.rb:412:in `block in create_table_sql'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/shared/mysql.rb:409:in `each'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/adapters/shared/mysql.rb:409:in `create_table_sql'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/database/schema_methods.rb:697:in `create_table_from_generator'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/sequel-4.47.0/lib/sequel/database/schema_methods.rb:213:in `create_table'
from (irb):35
from /home/gus/.rvm/gems/ruby-2.4.1/gems/rethoth-0.4.1/bin/thoth:182:in `<module:Thoth>'
from /home/gus/.rvm/gems/ruby-2.4.1/gems/rethoth-0.4.1/bin/thoth:34:in `<top (required)>'
from /home/gus/.rvm/gems/ruby-2.4.1/bin/thoth:22:in `load'
from /home/gus/.rvm/gems/ruby-2.4.1/bin/thoth:22:in `<main>'
from /home/gus/.rvm/gems/ruby-2.4.1/bin/ruby_executable_hooks:15:in `eval'
from /home/gus/.rvm/gems/ruby-2.4.1/bin/ruby_executable_hooks:15:in `<main>'
2.4.1 :051 >
This was expected to fail if you have a foreign key constraint on the posts table which it can't find.
Can you provide code to create the posts table first?
Edit: Obviously
2.4.1 :051 > Thoth.db.table_exists?("comments")
=> false Super!
pagojo
commented
7 years ago The migrations are very old and adhere to the default mode of SQLite3 (and of the MySQL MyISAM engine I guess) where foreign key constraints are by default not enforced.
Just for completeness of the defect report can you please disable foreign key checks in MySQL, from the MySQL CLI:
SET GLOBAL FOREIGN_KEY_CHECKS=0;
and then run thoth --log-sql sqllog --migrate and post the sqllog here?
gusandrianos
commented
7 years ago # Logfile created on 2017-07-11 17:04:10 +0300 by logger.rb/56815
I, [2017-07-11T17:04:10.626830 #6470] INFO -- : (0.111593s) CREATE TABLE IF NOT EXISTS `schema_info` (`version` integer NOT NULL DEFAULT 0)
I, [2017-07-11T17:04:10.630201 #6470] INFO -- : (0.003036s) SELECT * FROM `schema_info` LIMIT 1
I, [2017-07-11T17:04:10.630953 #6470] INFO -- : (0.000279s) SELECT 1 AS `one` FROM `schema_info` LIMIT 1
I, [2017-07-11T17:04:10.642361 #6470] INFO -- : (0.011103s) INSERT INTO `schema_info` (`version`) VALUES (0)
I, [2017-07-11T17:04:10.644400 #6470] INFO -- : (0.001733s) SELECT count(*) AS `count` FROM `schema_info` LIMIT 1
I, [2017-07-11T17:04:10.644995 #6470] INFO -- : (0.000264s) SELECT `version` FROM `schema_info` LIMIT 1
I, [2017-07-11T17:04:10.645936 #6470] INFO -- : Begin applying migration version 1, direction: up
E, [2017-07-11T17:04:10.646582 #6470] ERROR -- : Mysql2::Error: Table 'testx.comments' doesn't exist: SELECT NULL AS `nil` FROM `comments` LIMIT 1
E, [2017-07-11T17:04:10.649407 #6470] ERROR -- : Mysql2::Error: Table 'testx.posts' doesn't exist: DESCRIBE `posts`
I, [2017-07-21T16:20:47.454855 #10211] INFO -- : (0.015190s) CREATE TABLE IF NOT EXISTS `schema_info` (`version` integer NOT NULL DEFAULT 0)
I, [2017-07-21T16:20:47.456241 #10211] INFO -- : (0.000915s) SELECT * FROM `schema_info` LIMIT 1
I, [2017-07-21T16:20:47.456572 #10211] INFO -- : (0.000152s) SELECT 1 AS `one` FROM `schema_info` LIMIT 1
I, [2017-07-21T16:20:47.457614 #10211] INFO -- : (0.000903s) SELECT count(*) AS `count` FROM `schema_info` LIMIT 1
I, [2017-07-21T16:20:47.457901 #10211] INFO -- : (0.000155s) SELECT `version` FROM `schema_info` LIMIT 1
I, [2017-07-21T16:20:47.459863 #10211] INFO -- : Begin applying migration version 1, direction: up
E, [2017-07-21T16:20:47.460676 #10211] ERROR -- : Mysql2::Error: Table 'testx.comments' doesn't exist: SELECT NULL AS `nil` FROM `comments` LIMIT 1
E, [2017-07-21T16:20:47.463423 #10211] ERROR -- : Mysql2::Error: Table 'testx.posts' doesn't exist: DESCRIBE `posts`Did you set SET GLOBAL FOREIGN_KEY_CHECKS=0; can you check they have been globally applied please and not only on one session?
what does show global variables say?
gusandrianos
commented
7 years ago Of course I did... Maybe there's a compatibility problem? If I am not mistaken, mysql now uses InnoDB.
| foreign_key_checks | OFF Hmmm this is strange. Can you please switch on the MySQL logging on, repeat the migration and post the logs here?
Also can you post all the results of show global variables please?
gusandrianos
commented
7 years ago The log.
/usr/sbin/mysqld, Version: 5.7.19-0ubuntu0.16.04.1 ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
2017-07-25T22:45:39.031096Z 22 Quit
2017-07-25T22:45:55.746985Z 23 Connect root@localhost on testx using Socket
2017-07-25T22:45:55.747313Z 23 Query SET NAMES utf8
2017-07-25T22:45:55.747568Z 23 Query SET @@wait_timeout = 2147483
2017-07-25T22:45:55.747806Z 23 Query SET SQL_AUTO_IS_NULL=0
2017-07-25T22:45:55.748296Z 23 Query CREATE TABLE IF NOT EXISTS `schema_info` (`version` integer NOT NULL DEFAULT 0)
2017-07-25T22:45:55.748692Z 23 Query SELECT * FROM `schema_info` LIMIT 1
2017-07-25T22:45:55.749164Z 23 Query SELECT 1 AS `one` FROM `schema_info` LIMIT 1
2017-07-25T22:45:55.749549Z 23 Query SELECT count(*) AS `count` FROM `schema_info` LIMIT 1
2017-07-25T22:45:55.749962Z 23 Query SELECT `version` FROM `schema_info` LIMIT 1
2017-07-25T22:45:55.751153Z 23 Query SELECT NULL AS `nil` FROM `comments` LIMIT 1
2017-07-25T22:45:55.751606Z 23 Query DESCRIBE `posts`
2017-07-25T22:45:55.753598Z 23 Quit And the vars
mysql> show global variables
-> ;
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| avoid_temporal_upgrade | OFF |
| back_log | 80 |
| basedir | /usr/ |
| big_tables | OFF |
| bind_address | 127.0.0.1 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| block_encryption_mode | aes-128-ecb |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| check_proxy_users | OFF |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | NO_CHAIN |
| concurrent_insert | AUTO |
| connect_timeout | 10 |
| core_file | OFF |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_authentication_plugin | mysql_native_password |
| default_password_lifetime | 0 |
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| disabled_storage_engines | |
| disconnect_on_expired_password | ON |
| div_precision_increment | 4 |
| end_markers_in_json | OFF |
| enforce_gtid_consistency | OFF |
| eq_range_index_dive_limit | 200 |
| event_scheduler | OFF |
| expire_logs_days | 10 |
| explicit_defaults_for_timestamp | OFF |
| flush | OFF |
| flush_time | 0 |
| foreign_key_checks | ON |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| general_log | ON |
| general_log_file | /var/log/mysql/logfile.log |
| group_concat_max_len | 1024 |
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_owned | |
| gtid_purged | |
| have_compress | YES |
| have_crypt | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_openssl | DISABLED |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_statement_timeout | YES |
| have_symlink | YES |
| host_cache_size | 279 |
| hostname | main |
| ignore_builtin_innodb | OFF |
| ignore_db_dirs | |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| innodb_checksums | ON |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_deadlock_detect | ON |
| innodb_default_row_format | dynamic |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_large_prefix | ON |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_lru_scan_depth | 1024 |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 431 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 1 |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | ON |
| innodb_support_xa | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_native_aio | ON |
| innodb_version | 5.7.19 |
| innodb_write_io_threads | 4 |
| interactive_timeout | 28800 |
| internal_tmp_disk_storage_engine | InnoDB |
| join_buffer_size | 262144 |
| keep_files_on_create | OFF |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_messages | en_US |
| lc_messages_dir | /usr/share/mysql/ |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysql/error.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
| long_query_time | 10.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| master_info_repository | FILE |
| master_verify_checksum | OFF |
| max_allowed_packet | 16777216 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_digest_length | 1024 |
| max_error_count | 64 |
| max_execution_time | 0 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_points_in_geometry | 65536 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| metadata_locks_cache_size | 1024 |
| metadata_locks_hash_instances | 8 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_recover_options | BACKUP |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| mysql_native_password_proxy_users | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| ngram_token_size | 2 |
| offline_mode | OFF |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | 0 |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
| optimizer_trace | enabled=off,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
| parser_max_mem_size | 18446744073709551615 |
| performance_schema | ON |
| performance_schema_accounts_size | -1 |
| performance_schema_digests_size | 5000 |
| performance_schema_events_stages_history_long_size | 1000 |
| performance_schema_events_stages_history_size | 10 |
| performance_schema_events_statements_history_long_size | 1000 |
| performance_schema_events_statements_history_size | 10 |
| performance_schema_events_transactions_history_long_size | 1000 |
| performance_schema_events_transactions_history_size | 10 |
| performance_schema_events_waits_history_long_size | 1000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_hosts_size | -1 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | -1 |
| performance_schema_max_digest_length | 1024 |
| performance_schema_max_file_classes | 80 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | -1 |
| performance_schema_max_index_stat | -1 |
| performance_schema_max_memory_classes | 320 |
| performance_schema_max_metadata_locks | -1 |
| performance_schema_max_mutex_classes | 210 |
| performance_schema_max_mutex_instances | -1 |
| performance_schema_max_prepared_statements_instances | -1 |
| performance_schema_max_program_instances | -1 |
| performance_schema_max_rwlock_classes | 40 |
| performance_schema_max_rwlock_instances | -1 |
| performance_schema_max_socket_classes | 10 |
| performance_schema_max_socket_instances | -1 |
| performance_schema_max_sql_text_length | 1024 |
| performance_schema_max_stage_classes | 150 |
| performance_schema_max_statement_classes | 193 |
| performance_schema_max_statement_stack | 10 |
| performance_schema_max_table_handles | -1 |
| performance_schema_max_table_instances | -1 |
| performance_schema_max_table_lock_stat | -1 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | -1 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | -1 |
| performance_schema_setup_objects_size | -1 |
| performance_schema_users_size | -1 |
| pid_file | /var/run/mysqld/mysqld.pid |
| plugin_dir | /usr/lib/mysql/plugin/ |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 4096 |
| range_optimizer_max_mem_size | 8388608 |
| rbr_exec_mode | STRICT |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log | |
| relay_log_basename | /var/lib/mysql/main-relay-bin |
| relay_log_index | /var/lib/mysql/main-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| require_secure_transport | OFF |
| rpl_stop_slave_timeout | 31536000 |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
| server_id | 0 |
| server_id_bits | 32 |
| server_uuid | 4ab344c0-0a8e-11e7-a426-00225ff60ea2 |
| session_track_gtids | OFF |
| session_track_schema | ON |
| session_track_state_change | OFF |
| session_track_system_variables | time_zone,autocommit,character_set_client,character_set_results,character_set_connection |
| session_track_transaction_info | OFF |
| sha256_password_proxy_users | OFF |
| show_compatibility_56 | OFF |
| show_old_temporals | OFF |
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 60 |
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
| slave_pending_jobs_size_max | 16777216 |
| slave_preserve_commit_order | OFF |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/main-slow.log |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 262144 |
| sql_auto_is_null | OFF |
| sql_big_selects | ON |
| sql_buffer_result | OFF |
| sql_log_off | OFF |
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | 0 |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
| stored_program_cache | 256 |
| super_read_only | OFF |
| sync_binlog | 1 |
| sync_frm | ON |
| sync_master_info | 10000 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
| system_time_zone | EEST |
| table_definition_cache | 615 |
| table_open_cache | 431 |
| table_open_cache_instances | 16 |
| thread_cache_size | 8 |
| thread_handling | one-thread-per-connection |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tls_version | TLSv1,TLSv1.1 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| transaction_write_set_extraction | OFF |
| tx_isolation | REPEATABLE-READ |
| tx_read_only | OFF |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.7.19-0ubuntu0.16.04.1 |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| wait_timeout | 28800 |
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
So your global foreign_key_checks are set to ON . Can you switch them off globally , make sure they are off and repeat the migration please?
gusandrianos
commented
7 years ago I turned them on after running it. I will try it again but I'm sure there will be no change.
gusandrianos
commented
7 years ago WIth these local vars, and foreing key checks DISABLED
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| avoid_temporal_upgrade | OFF |
| back_log | 80 |
| basedir | /usr/ |
| big_tables | OFF |
| bind_address | 127.0.0.1 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| block_encryption_mode | aes-128-ecb |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| check_proxy_users | OFF |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | NO_CHAIN |
| concurrent_insert | AUTO |
| connect_timeout | 10 |
| core_file | OFF |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_authentication_plugin | mysql_native_password |
| default_password_lifetime | 0 |
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| disabled_storage_engines | |
| disconnect_on_expired_password | ON |
| div_precision_increment | 4 |
| end_markers_in_json | OFF |
| enforce_gtid_consistency | OFF |
| eq_range_index_dive_limit | 200 |
| event_scheduler | OFF |
| expire_logs_days | 10 |
| explicit_defaults_for_timestamp | OFF |
| flush | OFF |
| flush_time | 0 |
| foreign_key_checks | OFF |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| general_log | OFF |
| general_log_file | /var/lib/mysql/main.log |
| group_concat_max_len | 1024 |
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_owned | |
| gtid_purged | |
| have_compress | YES |
| have_crypt | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_openssl | DISABLED |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_statement_timeout | YES |
| have_symlink | YES |
| host_cache_size | 279 |
| hostname | main |
| ignore_builtin_innodb | OFF |
| ignore_db_dirs | |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| innodb_checksums | ON |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_deadlock_detect | ON |
| innodb_default_row_format | dynamic |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_large_prefix | ON |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_lru_scan_depth | 1024 |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 431 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 1 |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | ON |
| innodb_support_xa | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_native_aio | ON |
| innodb_version | 5.7.19 |
| innodb_write_io_threads | 4 |
| interactive_timeout | 28800 |
| internal_tmp_disk_storage_engine | InnoDB |
| join_buffer_size | 262144 |
| keep_files_on_create | OFF |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_messages | en_US |
| lc_messages_dir | /usr/share/mysql/ |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysql/error.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
| long_query_time | 10.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| master_info_repository | FILE |
| master_verify_checksum | OFF |
| max_allowed_packet | 16777216 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_digest_length | 1024 |
| max_error_count | 64 |
| max_execution_time | 0 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_points_in_geometry | 65536 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| metadata_locks_cache_size | 1024 |
| metadata_locks_hash_instances | 8 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_recover_options | BACKUP |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| mysql_native_password_proxy_users | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| ngram_token_size | 2 |
| offline_mode | OFF |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | 0 |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
| optimizer_trace | enabled=off,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
| parser_max_mem_size | 18446744073709551615 |
| performance_schema | ON |
| performance_schema_accounts_size | -1 |
| performance_schema_digests_size | 5000 |
| performance_schema_events_stages_history_long_size | 1000 |
| performance_schema_events_stages_history_size | 10 |
| performance_schema_events_statements_history_long_size | 1000 |
| performance_schema_events_statements_history_size | 10 |
| performance_schema_events_transactions_history_long_size | 1000 |
| performance_schema_events_transactions_history_size | 10 |
| performance_schema_events_waits_history_long_size | 1000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_hosts_size | -1 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | -1 |
| performance_schema_max_digest_length | 1024 |
| performance_schema_max_file_classes | 80 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | -1 |
| performance_schema_max_index_stat | -1 |
| performance_schema_max_memory_classes | 320 |
| performance_schema_max_metadata_locks | -1 |
| performance_schema_max_mutex_classes | 210 |
| performance_schema_max_mutex_instances | -1 |
| performance_schema_max_prepared_statements_instances | -1 |
| performance_schema_max_program_instances | -1 |
| performance_schema_max_rwlock_classes | 40 |
| performance_schema_max_rwlock_instances | -1 |
| performance_schema_max_socket_classes | 10 |
| performance_schema_max_socket_instances | -1 |
| performance_schema_max_sql_text_length | 1024 |
| performance_schema_max_stage_classes | 150 |
| performance_schema_max_statement_classes | 193 |
| performance_schema_max_statement_stack | 10 |
| performance_schema_max_table_handles | -1 |
| performance_schema_max_table_instances | -1 |
| performance_schema_max_table_lock_stat | -1 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | -1 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | -1 |
| performance_schema_setup_objects_size | -1 |
| performance_schema_users_size | -1 |
| pid_file | /var/run/mysqld/mysqld.pid |
| plugin_dir | /usr/lib/mysql/plugin/ |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 4096 |
| range_optimizer_max_mem_size | 8388608 |
| rbr_exec_mode | STRICT |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log | |
| relay_log_basename | /var/lib/mysql/main-relay-bin |
| relay_log_index | /var/lib/mysql/main-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| require_secure_transport | OFF |
| rpl_stop_slave_timeout | 31536000 |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
| server_id | 0 |
| server_id_bits | 32 |
| server_uuid | 4ab344c0-0a8e-11e7-a426-00225ff60ea2 |
| session_track_gtids | OFF |
| session_track_schema | ON |
| session_track_state_change | OFF |
| session_track_system_variables | time_zone,autocommit,character_set_client,character_set_results,character_set_connection |
| session_track_transaction_info | OFF |
| sha256_password_proxy_users | OFF |
| show_compatibility_56 | OFF |
| show_old_temporals | OFF |
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_allow_batching | OFF |
| slave_checkpoint_group | 512 |
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 60 |
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
| slave_pending_jobs_size_max | 16777216 |
| slave_preserve_commit_order | OFF |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors | OFF |
| slave_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/main-slow.log |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 262144 |
| sql_auto_is_null | OFF |
| sql_big_selects | ON |
| sql_buffer_result | OFF |
| sql_log_off | OFF |
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | 0 |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
| stored_program_cache | 256 |
| super_read_only | OFF |
| sync_binlog | 1 |
| sync_frm | ON |
| sync_master_info | 10000 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
| system_time_zone | EEST |
| table_definition_cache | 615 |
| table_open_cache | 431 |
| table_open_cache_instances | 16 |
| thread_cache_size | 8 |
| thread_handling | one-thread-per-connection |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tls_version | TLSv1,TLSv1.1 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| transaction_write_set_extraction | OFF |
| tx_isolation | REPEATABLE-READ |
| tx_read_only | OFF |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.7.19-0ubuntu0.16.04.1 |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| wait_timeout | 28800 |
+----------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+I get
Error: Mysql2::Error: Table 'testx.posts' doesn't existAnd...
/usr/sbin/mysqld, Version: 5.7.19-0ubuntu0.16.04.1 ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
2017-07-29T19:03:21.425487Z 7 Quit
2017-07-29T19:03:23.795573Z 8 Connect root@localhost on testx using Socket
2017-07-29T19:03:23.795703Z 8 Query SET NAMES utf8
2017-07-29T19:03:23.796370Z 8 Query SET @@wait_timeout = 2147483
2017-07-29T19:03:23.796415Z 8 Query SET SQL_AUTO_IS_NULL=0
2017-07-29T19:03:23.796727Z 8 Query CREATE TABLE IF NOT EXISTS `schema_info` (`version` integer NOT NULL DEFAULT 0)
2017-07-29T19:03:23.796903Z 8 Query SELECT * FROM `schema_info` LIMIT 1
2017-07-29T19:03:23.797138Z 8 Query SELECT 1 AS `one` FROM `schema_info` LIMIT 1
2017-07-29T19:03:23.797301Z 8 Query SELECT count(*) AS `count` FROM `schema_info` LIMIT 1
2017-07-29T19:03:23.797458Z 8 Query SELECT `version` FROM `schema_info` LIMIT 1
2017-07-29T19:03:23.798461Z 8 Query SELECT NULL AS `nil` FROM `comments` LIMIT 1
2017-07-29T19:03:23.798741Z 8 Query DESCRIBE `posts`
2017-07-29T19:03:23.800383Z 8 Quit Sorry, are the above global or local variables?
pagojo
commented
7 years ago ok, I can now reproduce this.
gusandrianos
commented
7 years ago Obviously global.
pagojo
commented
7 years ago There is nothing obvious in debugging! I'm on it. It looks like the session turns it ON :-/
pagojo
commented
7 years ago The Sequel shared driver for MySQL checks the schema of the referenced tables to the table which is about to create through DESCRIBE. This fails the foreign key checks inside the adaptor, although such checks may have been disabled in the MariaDB/MySQL server.
This happens from create_table_sql which checks for the keys of the referenced table
sequel-4.46.0/lib/sequel/adapters/shared/mysql.rb:394 in create_table_sql
which ends up in
sequel-4.46.0/lib/sequel/adapters/shared/mysql.rb:493 in schema_parse_table
Well, the create_table_sql from the common code (as in the case of few other DBs but not all) ends up in a DB specific create_table_sql (shared/mysql.rb in this case), whereas for Sqlite3 (and few other DBs) it doesn't follow that route.
What happens is that, in the case of Sqlite3, the generic code in sequel-4.46.0/lib/sequel/database/schema_methods.rb:213 in create_table prepares the statement in create_table_sql and ends up in query.rb:execute_dui and then in query.rb:execute and adapters/sqlite.rb:_execute without prior extra checks to the schema of the table (like in the case of the mysql adaptor). This is irrespective of whether one may have enabled foreign key checks in SQLite3 or not.
gusandrianos
commented
7 years ago So it is indeed out of our reach... It's a weird issue really, having no control over your settings for MySQL... We're better off using SQLite anyway, but MySQL support would be a "Nice to have" feature.
pagojo
commented
7 years ago I will have to look at re-working the existing migrations. I need to think about it.
gusandrianos
commented
7 years ago I have plenty of time. I can help if you like.
pagojo
commented
7 years ago Fixed with Release 0.4.2
gusandrianos
commented
7 years ago Oh!!! Nice :)
gusandrianos
commented
7 years ago Actually, we have another problem :/
Furthermore, since my fix isn't in the current version, thoth --migrate still throws an Unable to connect to database: LoadError: cannot load such file -- mysql error, until you change mysql to mysql2.
pagojo
commented
7 years ago Pulled #2 in the mainline. Actually it is not strict that you need mysql2, but it saves me from documenting it :-)
gusandrianos
commented
7 years ago Thanks for merging that. Did you see the error in the screenshot?
Error: Mysql2::Error: BLOB, TEXT, GEOMETRY or JSON column 'body' can't have a default value
I may have a clue what's going on with it, I'll try to fix it and report back.
pagojo
commented
7 years ago If this is a bug, then please first post it as a new issue and then we'll see about the pull request. On my setup I can't see anything. Please open a new issue to track this and report the versions of the server, the mysql2 adapter, Sequel etc.
It will not work at all without the changes in #2 as it cannot find the MySQL installation. But even if it finds the installation this happens,
And if I manually create the db, it throws this.