MySQL Forums
Forum List  »  Data Recovery

Re: Problems after dropping table
Posted by: Aigini Navaneethan
Date: August 13, 2020 01:13AM

Actually, I just checked the table structure, and there are no foreign keys, but there are keys, which I believe is also called indexes. This is the structure of the table :

mysql> SHOW CREATE TABLE lz_chat_archive\G
*************************** 1. row ***************************
Table: lz_chat_archive
Create Table: CREATE TABLE `lz_chat_archive` (
`time` int(11) unsigned NOT NULL DEFAULT '0',
`endtime` int(11) unsigned NOT NULL DEFAULT '0',
`closed` int(11) unsigned NOT NULL DEFAULT '0',
`chat_id` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`external_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`fullname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`internal_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`group_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`area_code` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`html` longtext COLLATE utf8_bin NOT NULL,
`plaintext` longtext COLLATE utf8_bin NOT NULL,
`transcript_text` text COLLATE utf8_bin NOT NULL,
`transcript_html` text COLLATE utf8_bin NOT NULL,
`email` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`company` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`phone` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`call_me_back` tinyint(1) unsigned NOT NULL DEFAULT '0',
`iso_language` varchar(8) COLLATE utf8_bin NOT NULL DEFAULT '',
`iso_country` varchar(5) COLLATE utf8_bin NOT NULL DEFAULT '',
`host` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`ip` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`gzip` tinyint(1) unsigned NOT NULL DEFAULT '0',
`transcript_sent` tinyint(1) unsigned NOT NULL DEFAULT '1',
`transcript_receiver` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`question` text COLLATE utf8_bin NOT NULL,
`customs` text COLLATE utf8_bin NOT NULL,
`subject` text COLLATE utf8_bin NOT NULL,
`voucher_id` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`wait` int(11) unsigned NOT NULL DEFAULT '0',
`duration` int(11) unsigned NOT NULL DEFAULT '0',
`accepted` tinyint(1) unsigned NOT NULL DEFAULT '0',
`ended` tinyint(1) unsigned NOT NULL DEFAULT '0',
`chat_type` tinyint(1) unsigned NOT NULL DEFAULT '1',
`ref_url` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (`chat_id`),
KEY `closed` (`closed`),
KEY `chat_type` (`chat_type`),
KEY `endtime` (`endtime`),
KEY `transcript_sent` (`transcript_sent`),
KEY `duration` (`duration`),
KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

I need more advise on the inner works/functions of mysql tables and indexes, etc as I am a newbie, and not sure of the consequences of certain actions.

In addition to dropping this table, I also deleted some rows in several other tables, and inserted new rows. So I am not sure if this is also a fatal error.

Now, since I am going to restore the database again, from scratch, I believe it should be ok, but I will also need to insert rows from the dates from 17th onwards into the restored database.

These are the statements I will use for the insertion of the new rows :

To get information from the database tables from 17th onwards :

select * from lz_chat_archive_update where time between 1594857600 and 1595937600 INTO OUTFILE '/var/opt/rh/rh-mysql57/lib/mysql-files/lz_chat_archive2807.csv' FIELDS TERMINATED BY ',';

To restore rows into the just restored database tables :

LOAD DATA INFILE '/usr/local/LivezillaDB/mysql/lz_chat_archive2807.csv' INTO TABLE lz_chat_archive FIELDS TERMINATED BY ',';

Will this be ok, or will cause problems again?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Problems after dropping table
83
August 13, 2020 01:13AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.