Page MenuHomePhabricator

missing database on replica server
Closed, ResolvedPublic

Description

I cannot see my user database on c2.

Database name: s51698__yetkin

Thanks.

Event Timeline

Superyetkin raised the priority of this task from to High.
Superyetkin updated the task description. (Show Details)
Superyetkin added a project: Toolforge.
Superyetkin added a subscriber: Superyetkin.
Restricted Application added a project: Cloud-Services. · View Herald TranscriptJul 13 2015, 4:58 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Andrew assigned this task to jcrespo.Jul 13 2015, 5:02 PM
Andrew set Security to None.
coren added a subscriber: coren.Jul 14 2015, 9:22 PM

It is possible that you might be connecting implicitly to a different server than you were previously - if you use a project name alias (like enwiki.labsdb) it'll get you a connection to /a/ database - but not necessarily always the same one. (We try to keep them stable, but operational constraints may necessitate moving there around).

If you rely on a database being present alongside other databases to use them together, it's generally better to connect to an explicitly specified server (one of c1.labsdb, c2.labsdb, or c3.labsdb). Chances are, your database is still there on one of them, you're just not connecting to the same one at the moment.

Hmmm. On rereading, I note you specify c2 explicitly? If that is the case, I cannot think of a reason why your DB would be unavailable. Adding our DBAs to the ticket so they can look into it.

coren added a comment.Jul 14 2015, 9:29 PM

As I note, I do see a database by that name, but on tools.labsdb (the general purpose database for tools, not one of the replicas) and not on c2.labsdb

As I note, I do see a database by that name, but on tools.labsdb (the general purpose database for tools, not one of the replicas) and not on c2.labsdb

This worked fine until last week's outage and replication issue. My tool here does make a connection to c2 and it did not change at all.

So, I need to get some more information from Jaime about what occurred on the weekend (he is unwell and on leave since then), but looking over the logs I see:

150711  2:00:17 [ERROR] mysqld: Table './s51698__yetkin/wanted_items' is marked as crashed
... lots of duplicate entries, until last one 17 hours later:
150711 19:46:19 [ERROR] mysqld: Table './s51698__yetkin/wanted_items' is marked as crashed

Jaime logged the following in SAL at that time:

19:48 jynus: stopping labsdb1002 after table corruption has been detected

We'll have to gather more info on what that action entailed. It's possible the data was not recoverable.

Do we know if the tables were MyISAM or ARIA?

Do we know if the tables were MyISAM or ARIA?

I recall creating the database via MySQL Workbench, so the storage engine should be the default value it uses. The data is not so important at the moment since I refresh it through a cron task eeery day. It would be ideal if we had a chance to restore the database and table structure so as not to start over and create everything from scratch.

Superyetkin updated the task description. (Show Details)Jul 15 2015, 2:58 PM

Hello, back from being sick.

Your table was creating huge corruption issues on the replica host, so it was "soft-removed". It should be available on /srv/tmp @Springle, but I would like to not copy back directly as corruption would return.

  • I can provide you the original files or anything that may help you, but please note that those would probably be corrupted in the first place.
  • Never store anything valuable on the replica servers, consider those scratch data to work with
  • I do not recommend MyISAM or Arya as store engines if corruption is not a possibility (they are ok for temporary data, though)

@Superyetkin If I create the database and table structure for you, is that ok with you?

Superyetkin added a comment.EditedJul 17 2015, 10:01 AM

@Superyetkin If I create the database and table structure for you, is that ok with you?

Yes, restoring the database structure would be of help.

Thanks.

I've recreated the structure as it was:

MariaDB LABS localhost s51698__yetkin > use s51698__yetkin
Database changed
MariaDB LABS localhost s51698__yetkin > SHOW TABLES;
+--------------------------+
| Tables_in_s51698__yetkin |
+--------------------------+
| wanted_items             |
+--------------------------+
1 row in set (0.00 sec)

MariaDB LABS localhost s51698__yetkin > SHOW CREATE TABLE wanted_items\G
*************************** 1. row ***************************
       Table: wanted_items
Create Table: CREATE TABLE `wanted_items` (
  `wi_namespace` int(11) NOT NULL,
  `wi_title` varbinary(300) DEFAULT NULL,
  `wi_count` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=binary

Content, however, is non-recoverable.

If you can access it with no problem, I will close the ticket after your confirmation.

Superyetkin closed this task as Resolved.Jul 17 2015, 3:40 PM

Just wanted to say that this happened recently again- I had to drop the table due to corruption. Ping me if you want me to recreate as empty.

Could you please do it?

I think someone beat me to do it?

Beat? Could you please recreate the structure?

'beat me to it' = 'someone already created the table'.

As far as I can see, the table is already there, at least on c2.labsdb.

MariaDB [(none)]> use s51698__yetkin;
MariaDB [s51698__yetkin]> SHOW TABLES\G
*************************** 1. row ***************************
Tables_in_s51698__yetkin: wanted_items
1 row in set (0.00 sec)

MariaDB [s51698__yetkin]> SHOW CREATE TABLE wanted_items\G
*************************** 1. row ***************************
       Table: wanted_items
Create Table: CREATE TABLE `wanted_items` (
  `wi_namespace` int(11) NOT NULL,
  `wi_title` varbinary(300) DEFAULT NULL,
  `wi_count` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.02 sec)

The same needs to be done for s51698__yetkin2.wanted_items.

Assuming the same table structure as for s51698__yetkin:

tools.superyetkin@tools-bastion-01:~$ mysql --defaults-file=replica.my.cnf -h s2.labsdb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7782276
Server version: 10.0.22-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use s51698__yetkin2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [s51698__yetkin2]> CREATE TABLE `wanted_items` (
    ->   `wi_namespace` int(11) NOT NULL,
    ->   `wi_title` varbinary(300) DEFAULT NULL,
    ->   `wi_count` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=binary;
Query OK, 0 rows affected (0.01 sec)