sed -n -e '/DROP TABLE.*`table_name`/,/UNLOCK TABLES/p' dump.sql > table_name.sql
This morning, I tried to upgrade MySQL 8:
mysql80-server upgraded: 8.0.22_1 -> 8.0.23
But then after upgrading. MySQL didn't start anymore !
It was broken:
2021-02-22T06:20:33.856568Z 4 [System] [MY-013381] [Server] Server upgrade from '80022' to '80023' started. 2021-02-22T06:20:34.351272Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Create slow_log CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), user_host MEDIUMTEXT NOT NULL, query_time TIME(6) NOT NULL, lock_time TIME(6) NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMBLOB NOT NULL, thread_id BIGINT UNSIGNED NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"; ' failed with error code = 13, error message = 'Can't get stat of './mysql/slow_log.CSV' (OS errno 2 - No such file or directory)'. 2021-02-22T06:20:34.354494Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server. 2021-02-22T06:20:34.354946Z 0 [ERROR] [MY-010119] [Server] Aborting
Googling didn't solve my issue directly. So I tried to create this missing CSV file. (in the mysql database directory)
After this error I also received a missing slog_log.CSV.
Did the same thing.
touch ./mysql/general_log.CSV chown mysql:mysql ./mysql/general_log.CSV touch ./mysql/slow_log.CSV chown mysql:mysql ./mysql/slow_log.CSV
Then it works again!
This seems to happen if you skip a certain upgrade. (from now on these CSV files are required). And these CSV files are only created in that particular update.. (not very robust!)
My FreeBSD server is giving a lot of swap errors. Every day the daily reporting email was filled with kernel messages like these:
my.server.com kernel log messages: +swap_pager_getswapspace(31): failed +swap_pager_getswapspace(32): failed ... and many more ...
After figuring out which process was causing this, it seemed MySQL was to blame.
That was odd, because these are the number of mysqltuner is reporting about my configuration:
[--] Physical Memory : 8.0G [--] Max MySQL memory : 845.4M
Max 850 MB
After a lot of googling, reading MySQL, it seemd the performance schema could be to blame. Because is is completely stored in memory.
Now for several days in a row, I don't have the swaperrors ☺️
To disable the MySQL permance schema place the following in
[mysqld] performance_schema = 0
Btw. I'm not sure if I need the performance scheme ..
⚠️ Followup a few weeks later
Unfortunately this isn't the solution. MySQL is still running out of swap space. I simply don't get it, because the memory usage should be limited
Followup more weeks later ;-)
ZFS ARC is taking a lot of memory...
Somehow MySQL checks the 'free' space and decides by itself to use swap. So the ZFS ARC memory is never released and MySQL .
To solve it limit the zfs arc memory
candidates=$(echo "show databases" | mysql -u root | grep -Ev "^(Database|mysql|performance_schema|sys|information_schema)$") mysqldump -u root --databases $candidates &gt; full-backup-without-system-tables-$(date +%F).sql
No I don't like working on Windows, but unfortunately my co-workers haven't seen the light yet.
To make the rails setup a bit easier I needed to document the installation of Ruby on Rails on Windows with a Mysql database.
The description is about Ruby 1.9.?, Rails 3.0, Mysql 5.5 with the mysql2 gem.. and a lot of gems that got installed and compiled via "bundle install"
Here's a raw extract of the document I build:
1. Install MySQL
In our situation MySQL was installed. But you should install MySQL with development files. (C++ headers and Libraries).
Just download the Community Server at http://mysql.com/.
2. Install Ruby
Install the base Ruby version. Download the installer at http://rubyinstaller.org/
Run the installer and check the following items:
- Add Ruby executables to your PATH
- Associate .rb and .rbw files with this Ruby Installation
3. Install DevKit
Because a lot of Ruby plugins make use of Native extensions you need a compiler. For windows there's devkit to the rescue. Download it at rubyinstaller: http://rubyinstaller.org/.
Extract this to (for example) c:/devkit (this text assumes this location)
Open a command prompt: (cmd.exe) and enter the following commands.
cd c:/devkit ruby dk.rb init
DevKit wil probably find your ruby installation. But to make sure check the file c:/devkit/config.yml. It should contain the path of your Ruby installation:
--- - C:/Ruby192
This step installs (or updates) an operating_system.rb file into the relevant directory needed to implement a RubyGems pre_install hook and a devkit.rb helper library file into
\lib\ruby\site_ruby. NOTE: you may need to use the –force option to update (with backup of the originals) the above mentioned files as discussed at the SFX DevKit upgrade FAQ entry.
Test the Devkit installation Confirm your Ruby environment is correctly using the DevKit by running.
gem install rdiscount --platform=ruby
RDiscount should install correctly and you should see:
Temporarily enhancing PATH to include DevKit... in the screen messages.
After this double check if it works by running:
ruby -rubygems -e "require 'rdiscount'; puts RDiscount.new('**Hello RubyInstaller**').to_html"
to confirm that the rdiscount gem is working. (This should generate HTML)
(More info: https://github.com/oneclick/rubyinstaller/wiki/Development-Kit )
4. Install Git
If you not already have installed git, please do it. You will like it!
Git is required for many plugins so you need it.
- download git at http://code.google.com/p/msysgit/ (choose a version I don't know :P, the latest .exe ?? )
- execute the installer, select the following options:
- check “Run Git and included tools from the windows command prompt” (Select the option with the red letters that scream "don't pick me" :P )
- checkout-as-is and commit-as-it.
5. Install Rails with the mysql2 plugin
Next install Ruby on Rails. (We don't install/generate the docs)
gem install rails --no-ri --no-rdoc
6. Install the mysql2 gem
To build mysql2 support do this: (Point the paths to your MySQL install directory!)
NOTE: When using a 64bit version of windows you probably run into issues. Thanks for mentioning Luis! (Read more about it: http://blog.mmediasys.com/2011/07/07/installing-mysql-on-windows-7-x64-and-using-ruby-with-it/ ).
gem install mysql2 -- '--with-mysql-lib="c:\Program Files\MySQL\MySQL Server 5.5\lib" --with-mysql-include="c:\Program Files\MySQL\MySQL Server 5.5\include"'
(Mysql5.1 seems to have the path: c:\Program Files\MySQL\MySQL Server 5.1\lib\opt)
Fix up Bundler (the gem management tool) to know what the mysql2 gem build settings need to be..
Else you're "bundle install" will fail when installing or updating the mysql2 gem!!
bundle config build.mysql2 '--with-mysql-lib="c:\Program Files\MySQL\MySQL Server 5.5\lib" --with-mysql-include="c:\Program Files\MySQL\MySQL Server 5.5\include"'
As soon as you start using this installation you could get errors like 'libmysql.dll not found.
To solve this copy this file from the mysql-installation/lib directory to the ruby/bin directory.
Slightly Unrelated, install the Rails app
Pull the rails app from git. (Or copy it). And run bundler install to install all gems..
cd /rails/app bundle install
I've recieved the following error when doing this:
Resolving deltas: 100% (3434/3434), done. Checking out files: 100% (85/85), done. Fetching source index for http://rubygems.org/ Could not find devise-1.4.0 in any of the sources
I seems that devise was updated and the specific version in Gemfile.lock wasn't available anymore.
You can Force the installation of this updated package
bundle update devise
These were my steps to build a working rails environment under windows...
Enough of windows, Now back to my Mac :P