Problem when line feed code is mixed when csv in import into varchar column with AWS Aurora.

yoshidaagri — Wed, 04 Sep 2019 08:05:43 +0000

[Environment]

AWS Aurora or Mysql

[Problem]

When csv file is imported to string column,If the criteria are enclosed in single quotes, will not produce the desired result.

where target_column = '31000000000';

If the criteria are not enclosed in single quotes,ok.

where target_column = 31000000000;

[Cause]

If you import a csv file without double quotes in the loader,Imports up to unnecessary line feed codes.
Line feed code is usually invisible in database.It is necessary to look in hexadecimal.

value	memo	hex(value)
31000000000	csv	33313030303030303030300D
31000000000	DML	3331303030303030303030

’0D’ is Line feed code of UTF-8.

[point]rule of mysql.

For conditional expression [string = number],Compare strings on left side as decimal numbers.line feed code is invalid.
For conditional expression [string = string],The character strings on the left side are compared as they are.line feed code is valid.

[How to]

To see the line feed code, convert it to hexadecimal.my case is LF.In hexadecimal, it is “0d”.
This DML deletes only the line feed code.

update target_table_name set target_column_name = TRIM( UNHEX('0D') FROM target_column_name );

How to solve Big log capture errors in Fluentd.

yoshidaagri — Fri, 30 Aug 2019 08:53:14 +0000

Summary:

This memo is when building a LogMonitoringSystem with Fluentd.

Construction:

See the figure above.

error message of my Fluentd:

2019-08-30 09:44:20 +0900 [info]: #0 bulk insert values size (table: access_log) => 37207
2019-08-30 09:44:20 +0900 [warn]: #0 failed to flush the buffer. 
retry_time=1 
next_retry_seconds=2019-08-30 09:44:21 +0900 chunk="5914aebbe44b1ad68c3f180a99706368" error_class=Mysql2::Error::ConnectionError 
error="MySQL server has gone away"

...gone away?

Cause of the error：

The cause of the error is my Mysql setting.
Seting name is max_allowed_packet.defualt setting 4M.
I was using the standard settings.However, the log file to capture is 9M...

Correspondence:

Fixed mysql settings.
- before: max_allowed_packet:4M
- after : max_allowed_packet:16M

result:

2019-08-30 16:10:29 +0900 [info]: #0 detected rotation of /var/log/nginx/access.log; waiting 5 seconds
2019-08-30 16:10:35 +0900 [info]: #0 following tail of /var/log/nginx/access.log
2019-08-30 16:10:39 +0900 [info]: #0 bulk insert values size (table: access_log) => 37207
2019-08-30 16:10:47 +0900 [info]: #0 bulk insert values size (table: access_log) => 13854

Great!!

Thenk you!

DEV Community: yoshidaagri

Problem when line feed code is mixed when csv in import into varchar column with AWS Aurora.

[Environment]

[Problem]

[Cause]

[point]rule of mysql.

[How to]

How to solve Big log capture errors in Fluentd.

Summary:

Construction:

error message of my Fluentd:

Cause of the error：

Correspondence:

result: