MYSQL LOAD DATA INFILE giving ERROR 1265

I’m getting a "Error Code: 1265. Data truncated for column ‘value’ at row 1".

At a complete loss as to the source of the error.

Here is the SQL

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/num.csv'  INTO TABLE sec_financials.num FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Ignore 1 LINES (adsh, tag, version, coreg, ddate, qtrs, uom, value); 

and this is the first fews rows of the csv I’m trying to load

adsh tag version coreg ddate qtrs uom value
0001640334-21-000798 AccountsPayableAndAccruedLiabilitiesCurrent us-gaap/2019 20210131 0 USD 10010.0000
0001640334-21-000798 AccountsPayableAndAccruedLiabilitiesCurrent us-gaap/2019 20201031 0 USD 10913.0000
0001477932-21-002126 AccountsPayableAndAccruedLiabilitiesCurrent us-gaap/2019 20201231 0 USD 2372072.0000
0001477932-21-002126 AccountsPayableAndAccruedLiabilitiesCurrent us-gaap/2019 20191231 0 USD 1018145.0000
0001640334-21-000810 AccountsPayableAndAccruedLiabilitiesCurrent us-gaap/2019 20210228 0 USD 2974.0000
0001640334-21-000810 AccountsPayableAndAccruedLiabilitiesCurrent us-gaap/2019 20200531 0 USD 4225.0000
0001477932-21-002140 AccountsPayableAndAccruedLiabilitiesCurrent us-gaap/2019 20191231 0 USD 1039000.0000

and this is the schema:

  CREATE TABLE `num` (   `id` int NOT NULL AUTO_INCREMENT,   `adsh` varchar(20) DEFAULT NULL,   `tag` varchar(256) DEFAULT NULL,   `version` varchar(20) DEFAULT NULL,   `ddate` int DEFAULT NULL,   `qtrs` int DEFAULT NULL,   `uom` varchar(25) DEFAULT NULL,   `coreg` varchar(256) DEFAULT NULL,   `value` float DEFAULT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `idx_unique_num` (`adsh`,`tag`,`version`,`ddate`,`qtrs`,`uom`,`coreg`)   )  

Turning Mysql values row into columns by form_id and entry_id

I would like to understand how I can turn these rows into columns in MySql

This is how the DB is saved:

id form_id entry_id meta_value
1 7 18 Name
2 7 18 Date
3 7 18 Phone
4 7 18 Email
5 7 18 Status
6 8 19 DOB
7 8 19 Address
8 8 19 Post Code
9 8 19 Country
0 8 19 Notes
11 7 20 Name
12 7 20 Date
13 7 20 Phone
14 7 20 Email
15 7 20 Status
16 8 21 DOB
17 8 21 Address
18 8 21 Post Code
19 8 21 Country
20 8 21 Notes

That is how the table is currently, the idea is combining both form_id (7 & 8) and as a row:

column 1 column 2 column 3 column 4 column 5 column 6 column 7 column 8 column 9 column 10
Name Date Phone Email Status DOB Address Post Code Country Notes
Name Date Phone Email Status DOB Address Post Code Country Notes

I have tried with INNER JOIN and Pivot but I stuck all day on that. If you guys could help would be amazing.

Thanks in advanced

Staff to head one section at a time using MySQL

I have 3 tables namely:

1. department_heads

  • DepartmentID
  • StaffID

2. sub_department_heads

  • SubDepartmentID
  • StaffID

3. branch_managers

  • BranchID
  • StaffID

Now what I want is not have staff being a department head as well as a branch manager or sub department head and vise verse.

Staff can only belong in only one table.

Kindly advice on how i can structure my tables. Thanks in advance.

Altering mysql database column causes error in another column

First of all sorry everybody if this question sounds too basic.

I have a WordPress database and a table wp_comments.

In this table, I have two DATETIME fields, whose default value is 0000-00-00 00:00:00.

I need to change this value to CURRENT_TIMESTAMP, so for example I run the following query:

ALTER TABLE `wp_comments` MODIFY `comment_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP; 

So, comment_date (NOT comment_date_gmt) is the column to modify.

Now, when I run this query I get the following error:

Error 1067: Invalid default value for comment_date_gmt 

Why is this happening? What could I do?

[UPDATE]

This is the table:

CREATE TABLE `wp_comments` (   `comment_ID` bigint(20) UNSIGNED NOT NULL,   `comment_post_ID` bigint(20) UNSIGNED NOT NULL DEFAULT '0',   `comment_author` tinytext NOT NULL,   `comment_author_email` varchar(100) NOT NULL DEFAULT '',   `comment_author_url` varchar(200) NOT NULL DEFAULT '',   `comment_author_IP` varchar(100) NOT NULL DEFAULT '',   `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `comment_content` text NOT NULL,   `comment_karma` int(11) NOT NULL DEFAULT '0',   `comment_approved` varchar(20) NOT NULL DEFAULT '1',   `comment_agent` varchar(255) NOT NULL DEFAULT '',   `comment_type` varchar(20) NOT NULL DEFAULT 'comment',   `comment_parent` bigint(20) UNSIGNED NOT NULL DEFAULT '0',   `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  ALTER TABLE `wp_comments`   ADD PRIMARY KEY (`comment_ID`),   ADD KEY `comment_post_ID` (`comment_post_ID`),   ADD KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),   ADD KEY `comment_date_gmt` (`comment_date_gmt`),   ADD KEY `comment_parent` (`comment_parent`),   ADD KEY `comment_author_email` (`comment_author_email`(10)); 

MySQL version is 5.7.33

[UPDATE]

I don’t know WHY but I know HOW I solved this issue:

ALTER TABLE `wp_comments` MODIFY `comment_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFY `comment_date_gmt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 

This way, by modifying both fields together, I get no errors.

It works, but I’m still quite curious, so if have any idea to share… 🙂

Recover MySQL from copied /var/lib/mysql

I have spent most of the day trying to recover from a crash followed by a couple of bad decisions.

While copying a table from DB1 to DB2, using phpmyadmin, MySQL crashed and from that point, I just was not able to access MySQL in any way.

Heart started to beat faster than usual and after much searching online, I followed a suggestion that some said had worked for them – Well, it did not for me.

Faced with not being able to access MySQL nor run any command (they all failed with denied … using password YES or NO) – I tried my passwords that I know are correct, I used them in my PHP code but nothing worked.

BAD MOVE: I proceeded to purge phpmyadmin and mysql.

Before doing so, I copied /var/lib/mysql to ~/mysql so I have a copy of the entire directory as it was as of the crash and prior to purge/resinstall.

I tried this: Recover MySQL /var/lib/mysql after update

but I am getting this error as I try to restart mysql

Starting mysql (via systemctl): mysql.serviceJob for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details. failed!

systemctl status mysql.service shows

● mysql.service – MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: failed (Result: exit-code) since Sat 2021-08-21 06:20:09 UTC; 6min ago Process: 23904 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Process: 23912 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE) Main PID: 23912 (code=exited, status=1/FAILURE) Status: "Server startup in progress"

Aug 21 06:20:09 mail.shipsuites.com systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5. Aug 21 06:20:09 mail.shipsuites.com systemd[1]: Stopped MySQL Community Server. Aug 21 06:20:09 mail.shipsuites.com systemd[1]: mysql.service: Start request repeated too quickly. Aug 21 06:20:09 mail.shipsuites.com systemd[1]: mysql.service: Failed with result ‘exit-code’. Aug 21 06:20:09 mail.shipsuites.com systemd[1]: Failed to start MySQL Community Server.

Any suggestion will be greatly appreciated – I am at a loss here and nothing I find seems to work for me.

OS: Ubuntu 20 MySQL 8.0 (fresh install)

MySQL Alter Table Drop Column INPLACE NoLock – Until which point the column being dropped is accessible?

I am trying to run MySQL Alter INPLCAE command to drop few columns from a very large 90GB table. While the Alter is running I am able to run the Select statement on the same table to ensure that the table is not locked.

MySQL Version 5.7 with innodb

Questions:

  1. While the alter command is running with in place algorithm and nolock, up to what point the data can be accessed in the columns being dropped? e.g. at the point when the columns are almost being dropped? I need to make this change in prod so need to make sure of this.

  2. Can the application still update the table while the alter to drop columns is running? Currently the columns are stored and after dropping them we will be converting them Virtual.

  3. Will there be any downtime at all, I read somewhere that the table will be locked shortly at the end, correct me if I am wrong.

Thanks!

Removing large amounts of rows from MySQL 5.7 table + ibdata increase question

I’m working with MySQL 5.7 on Windows.

I’ve got several tables to clean data from. Largest table’s ibd file is 300GB in size and it has almost 1.5 billion lines. I need to leave about 290 million rows in it. So a very large chunk needs to be removed.

From reading the docs there are 2 ways

  1. DELETE statement and OPTIMIZE TABLE after that
  2. copying data to a new table, dropping the old one and renaming the new one.

Second option seems much better in this case, but it there any potential issues to look our for? Downtime is not really an issue.

Another question, I saw that delete statement on big tables (deleting 1 million+ rows) can get stuck and then cause ibdata to grow (hundreds of MB), even though innodb_file_per_table option is on.

I assume that has to do with temporary tables somehow, but can’t find an explanation. Any ideas?