Posts Tagged ‘migration’

MySQL crashes after upgrade from MySQL to MariaDB and how to fix it

Tuesday, August 21st, 2018

how-to-fix-crashing-mysql-after-upgrade-to-mariadb-database-mariadb-logo.png

If you have recently upgraded your Debian / Ubuntu / CentOS Linux Server to the latest RPM / DEB packages as part of the upgrade you might have noticed the upgrade of MySQL Community Server  (which was bought by Oracle Corporation few years ago) is automatically upgraded to MariaDB (which is a MySQL fork made by the original developers of MySQL and guaranteed to stay open source. Just to name some of the Notable users include Wikipedia, WordPress.com and Google.).

You might have noticed MariaDB's restart script which is still under /etc/init.d/mysql  won't start and a quick check in /var/log/mysql.err | /var/log/mysql.log
shows errors of /usr/bin/mysqld crashing with errors like:

140502 14:13:05 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Log scan progressed past the checkpoint lsn 108 1057948207
140502 14:13:06  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
InnoDB: Doing recovery: scanned up to log sequence number 108 1058059648
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 15 row operations to undo
InnoDB: Trx id counter is 0 562485504
140502 14:13:06  InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted transactions
140502 14:13:06  InnoDB: Rolling back trx with id 0 562485192, 15 rows to undo
140502 14:13:06  InnoDB: Started; log sequence number 108 1058059648
140502 14:13:06  InnoDB: Assertion failure in thread 1873206128 in file ../../../storage/innobase/fsp/fsp0fsp.c line 1593
InnoDB: Failing assertion: frag_n_used > 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
140502 14:13:06 – mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 345919 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = (nil) thread_stack 0x30000
140502 14:13:06 [Note] Event Scheduler: Loaded 0 events
140502 14:13:06 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
/usr/sbin/mysqld(my_print_stacktrace+0x2d) [0xb7579cbd]
/usr/sbin/mysqld(handle_segfault+0x494) [0xb7245854]
[0xb6fc0400]
/lib/tls/i686/cmov/libc.so.6(abort+0x182) [0xb6cc5a82]
/usr/sbin/mysqld(+0x4867e9) [0xb74647e9]
/usr/sbin/mysqld(btr_page_free_low+0x122) [0xb74f1622]
/usr/sbin/mysqld(btr_compress+0x684) [0xb74f4ca4]
/usr/sbin/mysqld(btr_cur_compress_if_useful+0xe7) [0xb74284e7]
/usr/sbin/mysqld(btr_cur_pessimistic_delete+0x332) [0xb7429e72]
/usr/sbin/mysqld(btr_node_ptr_delete+0x82) [0xb74f4012]
/usr/sbin/mysqld(btr_discard_page+0x175) [0xb74f41e5]
/usr/sbin/mysqld(btr_cur_pessimistic_delete+0x3e8) [0xb7429f28]
/usr/sbin/mysqld(+0x526197) [0xb7504197]
/usr/sbin/mysqld(row_undo_ins+0x1b1) [0xb7504771]
/usr/sbin/mysqld(row_undo_step+0x25f) [0xb74c210f]
/usr/sbin/mysqld(que_run_threads+0x58a) [0xb74a31da]

/usr/sbin/mysqld(trx_rollback_or_clean_all_without_sess+0x3e3) [0xb74ded43]
/lib/tls/i686/cmov/libpthread.so.0(+0x596e) [0xb6f9f96e]
/lib/tls/i686/cmov/libc.so.6(clone+0x5e) [0xb6d65a4e]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Any recommendations?
mysql

I hoped to solve the /usr/bin/mysqld segfault error with server reboot as I though the problem is caused by the fact libc library was updated, but even a reboot did not solve it.

I've investigated online for a solution and found following MySQL corruption and recovery article.

The solution outlined there is very simple and comes to adding the line:
 

innodb_force_recovery = 1


to /etc/mysql/my.cnf

Assuming the mysql server is not running before restarting mariadb server.

1. Make a backup (Dump) of all MySQL tables

mysql:~# mysqldump -A > dump.sql

2. Drop all databases which need recovery.
You can do that from mysql cli or phpmyadmin

3. Stop mysqld.

mysql:~# /etc/init.d/mysql restart

4.  Remove /var/lib/mysql/ib*

mysql:~# rm -rf /var/lib/mysql/ib*

5. Comment out innodb_force_recovery in /etc/mysql/my.cnf

6. Restart mysqld. Look at mysql error log.
If everything is fine and you have problems with broken or missing databases the best thing next is to stop again mariadb and

7. Restore databases from the dump

mysql:~# mysql < dump.sql

 

 

 

Europe will be predominated by muslims 20 years from now / European Union might soon become Eurabia

Tuesday, July 24th, 2012

europe-dangers-of-becoming-eurabia-in-20-years-from-now

I've come across a schoking video, exposing the latest trends that Demographic researches points at. According to researches as of this year. Whole europe without exceptions is suffering from a HUGE DEMOGRAPHIC BREAK DOWN. As of time of writting the rate of muslim births all across european countries is multiplicating exponentiolly. There is no virtually country left in the realms of the European Union to have a exponential fertility rate. At the same time muslim emigrants population and child growth rate is ever increasing. As you can see from below video according to current demographic researches 20 years from 2012 (2032) or so 50% of population of Europe will be muslim. This means without war by faith and migration muslims are slowly taking over "Christian Europe". What makes this demographic catastrophy occur is probably the islamic people communality. The islam faith and the patriarchate which is part of the Islamic laws keeps families and Islamic communities very strong and united, while we Europeans by forgetting the importance of Christian faith and the main rule of love has shifted towards non-communal hard-core materialistic individualism. At most the we the officially Christian population of Europe (be it Orthodox Christians, Roman Catholic or Protestants as well as atheist has put our faith and hope not in God united Church centered communality as it used to be for centuries of bloom in europe but at hands of government social system, technology and money.

Though both good social system, good government structure and good money earnings is a great thing, this cannot produce population (except if soon people cloning is not practiced) …

The moral out of the data provided in video is obvious, we Europeans have to start making and support family and bury empty philosophers individualism currently rulling western and eastern European societies.

Migrate VMWare Linux Virtual Machine to Virtualbox – Convert VMware .VMX to .OVF (Open Virtual Format)

Monday, June 2nd, 2014

migrate_vmware-virtual-machine-toto-virtualbox-vmx-to-ovf-migration

VMWare Virtual Machines are usually stored in OVA Format  which is bundle of OVF(Open Virtualization Format) and in multiple *.VMDK files.
Though other Virtual Machines are supposed to also support these "open virtualization format", different VM vendors implement and support it differently, therefore though in theory VirtualBox and QEMU are said to support OVA Format. Attempts to import such usually will fail. In this little article I will explain how VMWare stored (exported) VirtualMachine machine can be succesfully imported to VirtualBox.
 

1. Locate where is Virtualbox .vmx files from Window search or via dir command

I'm a console guy, so I prefer looking for VMWare's .vmx from command line:
 

C:\Users\GGEORGI7> cd c:\ C:\> dir /s *.vmx

 

Volume in drive C is PC COE
Volume Serial Number is XXXX-XXXX

Directory of C:\Users\ggeorgi7\Documents\Virtual Machines\Debian 7

06/02/14 11:06 AM 2,687 Debian 7.vmx
05/28/14 15:44 PM 370 Debian 7.vmxf
2 File(s) 3,057 bytes


2. Switch to wherever the VMWare virtualmachine .vmx file is located

 

C:\> cd \Users\ggeorgi7\Documents\Virtual Machines\Debian 7
 

N.B. !Make sure the VMWare Virtualmachine is Shutdown (if it is Suspended converted file will not be properly implemented inside VirtualBox!). Whether the migrated virtualhost is suspended, launch VMWare, restore its state and Shut it down properly before starting migration with ovftool.
 

3. Remove Vmware-tools, Reset Display settings and Input D (before exporting to OVF) in VMWare

a) Remove vmware-tools

Vmware-tools kernel modules and stuff compiled specific for VMWare are no longer needed and might pose issues, thus it is always a good idea if previously installed in VMWare to wipe them out with cmd:

vmware-uninstall-tools.pl

uninstall-vmware-tools-linux-screenshot-migrating-vmware-to-virtualbox
b) Reset Display Device and Input Devices

Inside VMWare virtual machine Linux host, before shutting it down run:

mv /etc/X11/xorg.conf /etc/X11/xorg.conf.vmware

c) Remove Incomptable devices

To prevent issues with sound – its necessary to remove VMWare device created for soundcard, in order to let VirtualBox create its soundcard device on 1st boot after migration. To do so click on Virtual Machine and from context menu click Settings then in  Hardware tab select Sound Card and Remove:
 

Settings -> Hardware -> Sound Card (Click Remove)

vmware-migration-remove-soundcard-to-make-work-sound-work-in-virtualbox


4. Make a Clone (Backup) of Virtual Machine

Its always a good idea to create backup of VMWare VM to be migrated just in case something goes wrong during migration, so you have a way to step back to the already working one. Once files are copied it might be a good idea to use use some archiver like PeaZip or 7-Zip to save some disk space.
 

mkdir SomeFolder
copy Path_To_Virtual_Machine Folder_for_Backup_Virtual_Machine

 


In my case this was

 

 

mkdir \Temp\VMWare-machine-backup
C:\>copy "C:\Users\ggeorgi7\Documents\Virtual Machines\Debian 7"\ c:\Temp\VM
ware-machine-backup

Restoring later VMWare VirtualMachines in case of some failure is done by simply copying backuped files to VMWare Working Directory folder

In case if Wondering

Where is stored VMWare Virtual Machines?

 

Check in:
 

VM -> Settings -> Options


vmware-working-directory-folder-where-are-stored-vmware-virtualmachines

5. Use VMWare ovftool.exe to convert .VMX to Open Virtualization Format (.OVF)

 

Though export from .VMX to .OVF it can be also done from VMWare GUI from:
 

File -> Export to OVF

Anyways I think it is better to do it via OVFTool console tool:

The most common location for  VMWare Workstation is:
 

C:\Program Files (x86)\Vmware\Vmware Workstation\OVFTool


For VMware Player it is:

C:\Program Files (x86)\Vmware\Vmware Player\OVFTool

Very important thing to mention here is you have to be cautious, Never to run together VMWare and VirtualBox.
!! Before proceeding to next OVTool .vmx to .ovf migration Shutdown the VirtualMachine and as a best practice close VMware completely.

cd Location-directory-ofOVFTool
ovftool.exe Path-folder-to_VMVM_file.vmx Path-folder_to_VM_exportVM_file_export.ovf

C:\> cd "\Program Files (x86)\VMware\VMware Workstation"\OVFTool
C:\Program Files (x86)\VMware\VMware Workstation\OVFTool>ovftool.exe "\Users\ggeorgi7\Documents\Virtual Machines\Debian 7\Debian 7.vmx" C:\Users\ggeorg i7\vmware-debian-export.ovf

 

vmware-ovftool-convert-vmware-linux-virtual-machine-to-virtualbox


Convertion will take about 30 minutes on a normal computer hardware lets say (Dual Core PC 2Ghz with 4 Gb of RAM).
If you get a “failed to open disk” error during convertion, its likely that the virtual machine is still running or wasn’t shut down properly – boot the virtual machine and perform a shut down.

6. Launch VirtualBox, Install Guest Additions to enable Copy / Paste and USB support in Virtual Machine

After the process completes, you can boot the virtual machine. If you haven't earlier uninstalled vmware-tools  uninstall VMware Tools, and install VirtualBox’s Guest Additions (in order to allow Copy / Paste between VirtualBox and guess OS).
 

7. Launch VirtualBox and Import exported .OVF

Once Virtual Machine is exported, to import it into VirtualBox, launch VirtualBox and choose:
 

File -> Import Appliance


virtualbox-import-vmware-old-virtual-machine-to-new-linux import vmware appliance

virtualbox-import-vmware-previously-installed-virtual-machine-linux import appliance virtualbox
 

Allowing MySQL users access from all hosts – Fixing mysql ERROR 1045 (28000): Access denied for user ‘root’@’remote-admin.com’ (using password: YES)

Friday, June 20th, 2014

mysql_allow_access-from-remote-any-host-fix-access-error-after-sql-migration

I recently migrated MySQL database server from host A to host B (remotesystemadministration.com), because I wanted to have the mysql database server on a separate machine (have separation of server running services and have a dedicated mysql server).

MySQL server host (running on localhost previously was set from my mysql config my.cnf to listen and serve connections on localhost with

bind-address = 127.0.0.1

). MySQL is used by a Tomcat running Java application on localhost and my task was to set the Tomcat to use the MySQL database remotely to MySQL host B (new remote hostname where MySQL is moved is  remotesystemadminsitration.com and is running on IP 83.228.93.76).

Migration from MySQL Db server 1 (host A) to MySQL Db server 2 (host B) is done by binary copying the mysql database directory which in this case is (as it is a Debian server installed MySQL), the standard directory where mysql stores its database data is /var/lib/mysql ( datadir = /var/lib/mysql in /etc/mysql/my.cnf)

Binary copying of data from MySQL db (host A) to MySQL Db (host B) is done with rsync

After migrating and trying to login on migrated mysql  database on remotesystemadministration.net with mysql cli client:

remotesysadmin:~$ mysql -u root -p

I got following error:
 

ERROR 1045 (28000): Access denied for user 'root'@'remotesystemadministration.com' (using password: YES)


To fix the issue I had to login remotely from old migration server mysql (host A) cli:

mysql:~$ mysql -u root -p -h remotesystemadministration.com

and  run SQL commands:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'remotesystemadministration.com' WITH GRANT OPTION;
GRANT USAGE ON *.* TO 'root'@'remotesystemadministration.com' IDENTIFIED BY 'secret-mysql-pass';
FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)


Another way to solve the problem is to add the root user to be able to connect from any host (Enable MySQL root access from all host), to do so issue:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Note: In newer version of MySQL, flush privileges could be omitted.

Another approach if you want to substitute access from localhost for all users and enable all users to be able to authenticate to mysql remotely is to execute SQL Query:

UPDATE USER SET host='%' WHERE host='localhost';

Allowing all users to be able to connect from anywhere on the internet is a very bad security practice anyways, if you already have a tight firewall setup and you can only access the server via specific remote IP addresses allowing MySQL access from all hosts / ips should be ok.

Fixing MySQL server start up “ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)”

Friday, September 7th, 2012

I'm still busy configuring the new Lenovo (Le-novo) 🙂 ThinkCenter server necessery for migration of old machines. I've done a lot yesterday but really moving all this stuff takes time …

I moved the SQL databases from the old MySQL server host to the new Debian Linux host.

In order to move databsaes, I did the usual SQL dump from current working host with:

mysql:~# mysqldump --opt --allow-keywords --add-drop-table --all-databases -u root > dump.sql

After that did the standard move of dump.sql to the new host with sftp

mysql-new:~# sftp root@mysql-host
Connected to mysql-host.
sftp> get dump.sql
....
sftp> exit

and imported dump:

mysql-new:~# mysql -u root -p < dump.sql
Enter password:

Databases dump grow really a lot (7GB)!, so I had to wait for dump.sql to import about 20 minutes – (the host configuration is Dual core 6Ghz 3MB Cache CPU, 4GB DDR3 RAM, 7200 500 GB Hitachi ExcelStor Techno V32O HDD).

The dumps migration was between identical release Debian Linux – (Squeeze 6.0.5) servers running identical versions of MySQL.

mysql-new:~# mysql --version
mysql Ver 14.14 Distrib 5.1.63, for debian-linux-gnu (i486) using readline 6.1

Because of that the whole db import worked like a charm.

Once moved the SQL started re-started normally but there was an on screen warning:

ERROR 1045 (28000): Access denied for user
'debian-sys-maint'@'localhost' (using password: YES)"

The cause of this warning error is because of way /etc/init.d/mysql script is written and in particular the custom MySQL (Debian specific start-up philosophy).

/etc/init.d/mysql is written in a way that on every restart a check of Database consistency is done. There in the script the user debian-sys-maint (a user with mysql administrator root privileges) is used to do the quick consistency check. The debian-sys-maint password which is used on start-up is stored in /etc/mysql/debian.cnf:

mysql-new:~# less /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = pQFM9RetOHFjewwn
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = pQFM9RetOHFjewwn
socket = /var/run/mysqld/mysqld.sock
basedir = /usr

The whole problem is that during, the old SQL import the password set for user debian-sys-maint is different and once SQL starts the init script reads this pass and fails to login to SQL server.

The warning (error):

ERROR 1045 (28000): Access denied for user
'debian-sys-maint'@'localhost' (using password: YES)
hence appears on every SQL start (including on every system boot). The err is generally harmless and SQL seems to work fine with or without it. However since the consistency check is not done at start up, if there are some CORRUPT tables not initiating the start up check is not a good idea.

There are two options to get rid of the warning one and better one is to check in /etc/mysql/debian.cnf for password string and change the pwd with mysql cli e.g.:

new-mysql:~# grep -i pass /etc/mysql/debian.cnf | uniq
password = pQFM9RetOHFjewwn

GRANT SELECT on `mysql`.`user` to 'debian-sys-maint'@'localhost' identified by 'pQFM9RetOHFjewwn';
Query OK, 1 row affected (0.01 sec)
GRANT SELECT ON mysql.user TO 'debian-sys-main'@'localhost';Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Second option (not recommended in terms of security) is to set user/pass to root values in /etc/mysql/debian.cnf.

That's all. N-joy 🙂