0

Hi i create a new table using phpmyadmin create table not query and suddenly all my tables are recreated by itself and lost all data i tried to check last binlog and i found some strange lines like this :

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 57806
#220512 10:27:48 server id 1  end_log_pos 57965 CRC32 0xb94d36de    Query   thread_id=119584    exec_time=0 error_code=0    Xid = 4297955
SET TIMESTAMP=1652351268/*!*/;
DROP TABLE IF EXISTS `team_members` /* generated by server */
/*!*/;
# at 57965
#220512 10:27:48 server id 1  end_log_pos 58044 CRC32 0x043cb326    Anonymous_GTID  last_committed=100  sequence_number=101 rbr_only=no original_committed_timestamp=1652351268841270   immediate_commit_timestamp=1652351268841270 transaction_length=778
# original_commit_timestamp=1652351268841270 (2022-05-12 10:27:48.841270 UTC)
# immediate_commit_timestamp=1652351268841270 (2022-05-12 10:27:48.841270 UTC)
/*!80001 SET @@session.original_commit_timestamp=1652351268841270*//*!*/;
/*!80014 SET @@session.original_server_version=80029*//*!*/;
/*!80014 SET @@session.immediate_server_version=80029*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 58044
#220512 10:27:48 server id 1  end_log_pos 58743 CRC32 0xebf54ace    Query   thread_id=119584    exec_time=0 error_code=0    Xid = 4297956
SET TIMESTAMP=1652351268/*!*/;
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `team_members`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tournament_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `created_at` timestamp(0) NULL DEFAULT NULL,
  `updated_at` timestamp(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 80 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic
/*!*/;

what does this mean ? and can i get my old data back from binlog ? I tried to get data from ibd but it seems the creation for tables again overwrite the old files

Notes that i create another table and the log contain same lines for all tables

Aref Makke
  • 1
  • 3
  • *what does this mean ?* See https://stackoverflow.com/questions/2760460/what-does-the-add-drop-table-view-procedure-function-checkbox-do-in-phpm - you must uncheck this checkbox. *can i get my old data back from binlog ?* I doubt... – Akina May 12 '22 at 13:30
  • If your binlog contains all changes to the data, from the very first CREATE TABLE to the current day, you may be able to replay it all (except for the DROP TABLE you show above) to recover your data. But if some of the binlogs have expired, you must restore your most recent database backup, if you have one. If you don't have a backup, then I'm afraid you are out of luck. – Bill Karwin May 12 '22 at 13:41
  • @Akina this link has nothing to do with what i'm asking for as i mention in my question that i created a new table not exporting a table does this happen also with creation ? – Aref Makke May 12 '22 at 13:54
  • Do you mean that you have created the table whereas a table with the same name have been present in the database, and this existing table was dropped and recreated? if so then look for similar checkbox which allows to replace existing table with newly created one. And disable it. – Akina May 12 '22 at 13:59
  • No i added a note in my question i create a new table called `refer_table` then suddenly phpmyadmin logout all users i re-enter the database and all tables was there including the new table but no data i checked binlog and i found that all tables have been droped and recreated again – Aref Makke May 12 '22 at 14:03

0 Answers0