mariadb problem

fakiradam · August 26, 2014, 8:57am

My mysql service doesn’t start. When I write the command “rcmysql status” , I get this message.

ql.service - MySQL server
Loaded: loaded (/usr/lib/systemd/system/mysql.service; disabled)
Active: failed (Result: exit-code) since Tue 2014-08-26 09:43:09 EEST; 5s ago
Process: 10001 ExecStart=/usr/lib/mysql/rcmysql start (code=exited, status=1/FAILURE)

Aug 26 09:43:08 linux-m2fv rcmysql[10001]: maximumevler_com.me14_user_usergroup_map           OK
Aug 26 09:43:08 linux-m2fv rcmysql[10001]: maximumevler_com.me14_usergroups                   OK
Aug 26 09:43:08 linux-m2fv rcmysql[10001]: maximumevler_com.me14_users                        OK
Aug 26 09:43:08 linux-m2fv rcmysql[10001]: maximumevler_com.me14_viewlevels                   OK
Aug 26 09:43:08 linux-m2fv rcmysql[10001]: maximumevler_com.me14_weblinks                     OK
Aug 26 09:43:08 linux-m2fv rcmysql[10001]: metalmuhendislik
Aug 26 09:43:08 linux-m2fv rcmysql[10001]: metalmuhendislik.admins                            OK
Aug 26 09:43:08 linux-m2fv rcmysql[10001]: metalmuhendislik.bulletin                          OK
Aug 26 09:43:08 linux-m2fv rcmysql[10001]: metalmuhendislik.childmoduls                       OK
Aug 26 09:43:08 linux-m2fv rcmysql[10001]: metalmuhendislik.haberler                          OK
Aug 26 09:43:09 linux-m2fv systemd[1]: mysql.service: control process exited, code=exited status=1
Aug 26 09:43:09 linux-m2fv systemd[1]: Failed to start MySQL server.
Aug 26 09:43:09 linux-m2fv systemd[1]: Unit mysql.service entered failed state.

So I thought the error related to last database table and I removed it but it didn’t work and I got same message with next database name.

Please help me.

Miuku · August 26, 2014, 9:09am

We need to know more information;

What distribution version?
What MariaDB version?
What did you do before? Did you upgrade the OS or MariaDB from a previous version?

Also take a peek at /var/lib/mysql/*.err files, there might be more than one there.

wolfi323 · August 26, 2014, 9:10am

There is no error shown. In particular the last table is shown as “OK”.

What openSUSE version are you using?
How did you install mariadb?

Loaded: loaded (/usr/lib/systemd/system/mysql.service; disabled)
…
Process: 10001 ExecStart=/usr/lib/mysql/rcmysql start (code=exited, status=1/FAILURE)

/usr/lib/mysql/rcmysql and /usr/lib/systemd/system/mysql.service do not even exist on my (13.1) system.

Try to use “sudo systemctl start mysql”.

Then post the output of “sudo systemctl status mysql”.

If that doesn’t work either, maybe mysql/mariadb’s logs contain a clue. They should be in /var/log/mysql/.

fakiradam · August 26, 2014, 9:37am

OS Version : Linux 3.11.10-21-desktop openSUSE 13.1 (Bottle)(x86_64)

My system had worked very well until this morning. I didn’t make any special thing. I wrote the command “service mysql start” and didn’t work.
I installed the mariadb with “Web and LAMP Server” pattern.

Try to use “sudo systemctl start mysql”.

Then post the output of “sudo systemctl status mysql”.

If that doesn’t work either, maybe mysql/mariadb’s logs contain a clue. They should be in /var/log/mysql/.

I got the same response with systemctl commands.

I looked the log files and I found many lines similar below in mysql-upgrade.log

 cku0831707455121.jos_jforms_4054b
   Error    : Table 'cku0831707455121.jos_jforms_4054b' doesn't exist in engine
   status   : Operation failed
   cku0831707455121.jos_jforms_fields
   Error    : Table 'cku0831707455121.jos_jforms_fields' doesn't exist in engine
   status   : Operation failed
   cku0831707455121.jos_jforms_forms
   Error    : Table 'cku0831707455121.jos_jforms_forms' doesn't exist in engine
   status   : Operation failed
   cku0831707455121.jos_jforms_parameters
   Error    : Table 'cku0831707455121.jos_jforms_parameters' doesn't exist in engine
   status   : Operation failed
   cku0831707455121.jos_jforms_tparameters
   Error    : Table 'cku0831707455121.jos_jforms_tparameters' doesn't exist in engine
   status   : Operation failed

And I found these lines in msyqld-upgrade-run.log

140608  4:01:35 [ERROR] Column count of mysql.events_waits_current is wrong. Expected 19, found 16. Created with MariaDB 50528, now running 100011. Please use mysql_upgrade to fix this error.
140608  4:01:35 [ERROR] Column count of mysql.events_waits_history is wrong. Expected 19, found 16. Created with MariaDB 50528, now running 100011. Please use mysql_upgrade to fix this error.
140608  4:01:35 [ERROR] Column count of mysql.events_waits_history_long is wrong. Expected 19, found 16. Created with MariaDB 50528, now running 100011. Please use mysql_upgrade to fix this error.
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_thread_by_event_name: expected column 'THREAD_ID' at position 0 to have type bigint(20), found type int(11).
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Column count of mysql.file_summary_by_event_name is wrong. Expected 23, found 5. Created with MariaDB 50528, now running 100011. Please use mysql_upgrade to fix this error.
140608  4:01:35 [ERROR] Column count of mysql.file_summary_by_instance is wrong. Expected 25, found 6. Created with MariaDB 50528, now running 100011. Please use mysql_upgrade to fix this error.
140608  4:01:35 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
140608  4:01:35 [ERROR] Incorrect definition of table performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found type int(11).
140608  4:01:35 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_type' has the wrong structure
140608  4:01:35 [ERROR] Incorrect definition of table performance_schema.rwlock_instances: expected column 'WRITE_LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found type int(11).
140608  4:01:35 [ERROR] Native table 'performance_schema'.'setup_actors' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'setup_objects' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_index_usage' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_table' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'table_lock_waits_summary_by_table' has the wrong structure
140608  4:01:35 [ERROR] Column count of mysql.threads is wrong. Expected 14, found 3. Created with MariaDB 50528, now running 100011. Please use mysql_upgrade to fix this error.
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_stages_current' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_stages_history' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_stages_history_long' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_thread_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_account_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_user_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_host_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_stages_summary_global_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_statements_current' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_statements_history' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_statements_history_long' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_thread_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_account_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_user_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
140608  4:01:35 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure

END OF LOG FILE

140826 10:14:44 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
140826 10:14:44 [ERROR] Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
140826 10:14:44 [Note] /usr/sbin/mysqld: Normal shutdown

140826 10:14:44 [Note] InnoDB: FTS optimize thread exiting.
140826 10:14:44 [Note] InnoDB: Starting shutdown...
140826 10:14:45 [Note] InnoDB: Shutdown completed; log sequence number 991607708
140826 10:14:45 [Note] /usr/sbin/mysqld: Shutdown complete

140826 10:14:45 mysqld_safe mysqld from pid file /var/tmp/mysql-protected.DpPFcX/mysqld.pid ended

wolfi323 · August 26, 2014, 9:45am

And which repos do you have?

zypper lr -d

What actual mariadb package do you have installed?

rpm -qi mariadb

I looked the log files and I found many lines similar below in mysql-upgrade.log

 cku0831707455121.jos_jforms_4054b
   Error    : Table 'cku0831707455121.jos_jforms_4054b' doesn't exist in engine
   status   : Operation failed
...

Sounds like the tables are created with an engine that is not loaded/does not exist any more.

And I found these lines in msyqld-upgrade-run.log

140608  4:01:35 [ERROR] Column count of mysql.events_waits_current is wrong. Expected 19, found 16. Created with MariaDB 50528, now running 100011. Please use mysql_upgrade to fix this error.
...

Well, isn’t the message clear?
You created the tables with MariaDB 5.5 (as shipped in openSUSE 13.1), but are now trying to start MariaDB 10 (probably from server:database).

Try downgrading your mariadb packages to the 5.5 versions, or run mysql_upgrade (which probably won’t work because of the above errors).

Btw, if you don’t care about your tables, you could just remove the content of /var/lib/mysql/ and mariadb should start.

fakiradam · August 26, 2014, 7:35pm

wolfi323:

And which repos do you have?
zypper lr -d
What actual mariadb package do you have installed?
rpm -qi mariadb
Sounds like the tables are created with an engine that is not loaded/does not exist any more.

Well, isn’t the message clear?
You created the tables with MariaDB 5.5 (as shipped in openSUSE 13.1), but are now trying to start MariaDB 10 (probably from server:database).

Try downgrading your mariadb packages to the 5.5 versions, or run mysql_upgrade (which probably won’t work because of the above errors).

Btw, if you don’t care about your tables, you could just remove the content of /var/lib/mysql/ and mariadb should start.

My repositories


| Packman Repository        | Packman Repository                 | Yes     | Yes     |   99     | rpm-md | http://ftp.gwdg.de/pub/linux/packman/suse/openSUSE_13.1/                           |        
 2 | devel:languages:haskell   | devel:languages:haskell            | Yes     | Yes     |   99     | rpm-md | http://download.opensuse.org/repositories/devel:/languages:/haskell/openSUSE_13.1/ |        
 3 | devel:languages:pascal    | devel:languages:pascal             | No      | No      |   99     | rpm-md | http://download.opensuse.org/repositories/devel:/languages:/pascal/openSUSE_13.1/  |        
 4 | filesystems               | filesystems                        | Yes     | Yes     |   99     | rpm-md | http://download.opensuse.org/repositories/filesystems/openSUSE_Factory/            |        
 5 | games                     | games                              | Yes     | Yes     |   99     | rpm-md | http://download.opensuse.org/repositories/games/openSUSE_13.1/                     |        
 6 | google-chrome             | google-chrome                      | Yes     | Yes     |   99     | rpm-md | http://dl.google.com/linux/chrome/rpm/stable/x86_64                                |        
 7 | home:Superpeppo89         | home:Superpeppo89                  | Yes     | Yes     |   99     | rpm-md | http://download.opensuse.org/repositories/home:/Superpeppo89/openSUSE_13.1/        |        
 8 | jftp.inai.de/             | Ben ekledim - jftp.inai.de/        | No      | No      |   99     | rpm-md | http://jftp.inai.de/openSUSE_13.1/                                                 |        
 9 | libdvdcss repository      | libdvdcss repository               | Yes     | Yes     |   99     | rpm-md | http://opensuse-guide.org/repo/13.1/                                               |        
10 | nVidia Graphics Drivers   | nVidia Graphics Drivers            | Yes     | Yes     |   99     | rpm-md | http://download.nvidia.com/opensuse/13.1/                                          |        
11 | openSUSE-13.1-1.10        | openSUSE-13.1-1.10                 | No      | No      |   99     | yast2  | cd:///?devices=/dev/disk/by-id/ata-HL-DT-ST_DVDRAM_GH24NSB0_KBJDAJ45204,/dev/sr0   |        
12 | repo-debug                | openSUSE-13.1-Debug                | No      | No      |   99     | NONE   | http://download.opensuse.org/debug/distribution/13.1/repo/oss/                     |        
13 | repo-debug-update         | openSUSE-13.1-Update-Debug         | No      | No      |   99     | NONE   | http://download.opensuse.org/debug/update/13.1/                                    |        
14 | repo-debug-update-non-oss | openSUSE-13.1-Update-Debug-Non-Oss | No      | No      |   99     | NONE   | http://download.opensuse.org/debug/update/13.1-non-oss/                            |        
15 | repo-non-oss              | openSUSE-13.1-Non-Oss              | No      | Yes     |   99     | yast2  | http://download.opensuse.org/distribution/13.1/repo/non-oss/                       |        
16 | repo-oss                  | openSUSE-13.1-Oss                  | Yes     | Yes     |   99     | yast2  | http://download.opensuse.org/distribution/13.1/repo/oss/                           |        
17 | repo-source               | openSUSE-13.1-Source               | No      | No      |   99     | yast2  | http://download.opensuse.org/source/distribution/13.1/repo/oss/                    |        
18 | repo-update               | openSUSE-13.1-Update               | Yes     | Yes     |   99     | rpm-md | http://download.opensuse.org/update/13.1/                                          |        
19 | repo-update-non-oss       | openSUSE-13.1-Update-Non-Oss       | Yes     | Yes     |   99     | rpm-md | http://download.opensuse.org/update/13.1-non-oss/                                  |        
20 | server:database           | server:database                    | Yes     | Yes     |   99     | rpm-md | http://download.opensuse.org/repositories/server:/database/openSUSE_13.1/          |        
21 | server:php:extensions     | server:php:extensions              | Yes     | Yes     |   99     | rpm-md | http://download.opensuse.org/repositories/server:/php:/extensions/openSUSE_13.1/   |


rpm -gi mariadb

I got this message

error: open of mariadb failed: No such file or directory

I tried to downgrade version to 5.5 but I got message

"mysql.service - LSB: Start the MySQL database server
   Loaded: loaded (/etc/init.d/mysql)
   Active: failed (Result: exit-code) since Tue 2014-08-26 10:58:09 EEST; 12s ago
  Process: 2247 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Aug 26 10:58:09 linux-m2fv mysql[2247]: You are upgrading from different stable version of MySQL!
Aug 26 10:58:09 linux-m2fv mysql[2247]: Make sure you to make backup of your data (mostly /var/lib/mysql)!
Aug 26 10:58:09 linux-m2fv systemd[1]: mysql.service: control process exited, code=exited status=1
Aug 26 10:58:09 linux-m2fv systemd[1]: Failed to start LSB: Start the MySQL database server.
Aug 26 10:58:09 linux-m2fv systemd[1]: Unit mysql.service entered failed state.

"

After I again updated the maridb 10.0.13-3.1 and I tried “mysql_upgrade” command but I got this message.

Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2 "No such file or directory")
FATAL ERROR: Upgrade failed

I need databases because all of them are my websites.

fakiradam · August 26, 2014, 8:07pm

I made a backup and removed all files of database and mysql starts to work very well. But How can I import from my backup.

wolfi323 · August 26, 2014, 8:47pm

fakiradam:

My repositories


...
20 | server:database           | server:database                    | Yes     | Yes     |   99     | rpm-md | http://download.opensuse.org/repositories/server:/database/openSUSE_13.1/          |        
...

I suppose you got mariadb from there.

Well, sorry, but I have to say, why do you upgrade to some testing/development version, when you don’t know what you are doing and don’t have a backup?

rpm -gi mariadb

I got this message

error: open of mariadb failed: No such file or directory

It would have been “rpm -qi mariadb”, but I think it’s obvious what mariadb package you have by now.

I tried to downgrade version to 5.5 but I got message

"mysql.service - LSB: Start the MySQL database server
   Loaded: loaded (/etc/init.d/mysql)
   Active: failed (Result: exit-code) since Tue 2014-08-26 10:58:09 EEST; 12s ago
  Process: 2247 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Aug 26 10:58:09 linux-m2fv mysql[2247]: You are upgrading from different stable version of MySQL!
Aug 26 10:58:09 linux-m2fv mysql[2247]: Make sure you to make backup of your data (mostly /var/lib/mysql)!
Aug 26 10:58:09 linux-m2fv systemd[1]: mysql.service: control process exited, code=exited status=1
Aug 26 10:58:09 linux-m2fv systemd[1]: Failed to start LSB: Start the MySQL database server.
Aug 26 10:58:09 linux-m2fv systemd[1]: Unit mysql.service entered failed state.

"

Right.
Apparently at least some of your tables got upgraded to version 10.
The downgrade of the databases didn’t work any more.

After I again updated the maridb 10.0.13-3.1 and I tried “mysql_upgrade” command but I got this message.

Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2 "No such file or directory")
FATAL ERROR: Upgrade failed

Random downgrading and upgrading won’t work too well either, I guess.
I never suggested to do so.

I need databases because all of them are my websites.

Ok. Then you should hopefully have a backup.
Or not? Well. Always backup important data.
And don’t experiment with different database versions if your tables are important, unless you have a backup/fallback plan.

So with which version did you create them in the first place?
Why did you delete random tables then? Or did you keep a copy at least?

Well, if you could export them from a running MariaDB/MySQL server with mysqldump e.g., you could import them again now easily.
But that’s no option now for you AFAICT.

I would try this:

stop mariadb if it is running:

sudo systemctl stop mysql

downgrade to 5.5 again
remove /var/lib/mysql completely
copy back your backup of /var/lib/mysql/
remove the file “/var/lib/.run-mysql_upgrade” if it exists

sudo rm /var/lib/mysql/.run-mysql_upgrade

then try to start mariadb again:

sudo systemctl start mysql

If you are lucky it works then.
If not, please post the output of “sudo systemctl status mysql” for a start, and we will think about further actions.

fakiradam · August 26, 2014, 10:45pm

]Well, sorry, but I have to say, why do you upgrade to some testing/development version, when you don’t know what you are doing and don’t have a backup?

Actually I don’t remember when I added this testing/development version

downgrade to 5.5 again

remove /var/lib/mysql completely

copy back your backup of /var/lib/mysql/

remove the file “/var/lib/.run-mysql_upgrade” if it exists

I did it but mysql service didn’t start.
I firstly tried copy from data two weeks ago.
Then I tried copy back from data today backup ( after crashed )
Then I removed again all of the data and wrongly I started mysql service and I stopped service. I copied all of the database files from latest backup but only different ones and mysql service started successfully. I connected with phpmyadmin and I listed all tables but I can enter inside only MyIsam and memory type tables. I get error #1146 table " …" doesn’t exist for other types of tables.

Sorry for my english.

Can I recovery from this point?

wolfi323 · August 27, 2014, 8:04am

Well, no idea.

So you do not know with which MariaDB version you created those tables, right?
Do you at least know which engine you used?

Which mariadb packages do you have installed now?

rpm -qa mariadb*

What about the logfiles?
Any errors in /var/log/mysql/mysqld.log when you try to access the tables?

Maybe you forgot to copy some files?
For InnoDB you need the file ibdata1 in addition to the database schema in the folder named like your table.
And check that the files in /var/lib/mysql have the correct owner (mysql:mysql) and permissions (-rw-rw----).
Maybe post a directory listing:

ls -la /var/lib/mysql

Probably you find something that could help you here: MySQL > Table doesn't exist. But it does (or it should) - Stack Overflow

Miuku · August 27, 2014, 10:43am

If I were to chime in with a random suggestion, most likely the database tables have been upgraded to 10.x (which is stable GA now btw) and the only way to recover them would be to have 10.x, then use innodb recovery parameter in my.cnf ( innodb_force_recovery = 1 - starting from the lowest until the SQL server starts, it’s progressively more “forgiving” - read more at http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html ), then mysqldump all the data, empty databases and re-import them.

However not knowing how badly the databases have been mangled in the process I’m left to guessing. I’d literally have to see it.