search

rodrigogs / mysql-events

A node package that watches a MySQL database and runs callbacks on matched events.
BSD 3-Clause "New" or "Revised" License
136 stars 52 forks source link

Column names are changed #9

Closed MInesGomes closed 5 years ago

MInesGomes commented 5 years ago

Great package! Thanks Rodrigo!

Maybe I am doing something wrong... but I get the columns names changed. It's not aleatory, so when I want to use Id I know I can use ev.affectedRows[0].after.addition

But It would be nice to use ev.affectedRows[0].after.id My table has these columns:

'id'
'alert_begin'
'alert_sent'
'alert_answered'
'text'
'addition'
'updated'
'alert_type_id'
'call_rule_id'
'user_attended_id'

And when I print the results are changed:

console.log("Id:" + ev.affectedRows[0].after.addition);

I get the Id printing ev.affectedRows[0].after.addition and so on ...

console.log("updated:" + ev.affectedRows[0].after.id);
console.log("alert_type_id:" + ev.affectedRows[0].after.text); 
console.log("alert_begin:" + ev.affectedRows[0].after.alert_answered);
console.log("alert_answered:" + ev.affectedRows[0].after.alert_begin); /
console.log("alert_sent:" + ev.affectedRows[0].after.alert_sent); 
console.log("text:" + ev.affectedRows[0].after.alert_type_id);
console.log("addition:" + ev.affectedRows[0].after.call_rule_id);
console.log("call_rule_id:" + ev.affectedRows[0].after.updated);

Only this field is the same console.log("user_attended_id:" + ev.affectedRows[0].after.user_attended_id);

console.log("AF:" + ev.affectedColumns); - This is also changed
rodrigogs commented 5 years ago

This is very strange. I don't know what could be the problem, the lib just reads what's in MySQL binary logs... so if column names are changed, probably they are changed there too.

MInesGomes commented 5 years ago

I need help ... MySql Version: 8.0.13MySQL Community Server - GPL mysql-8.0.13-macos10.14-x86_64 I tried mysqlbinlog binlog.000x and gives lots of things like...

#190110 10:47:44 server id 1  end_log_pos 198048 CRC32 0x6740f07e   Query   thread_id=1968  exec_time=0 error_code=0
SET TIMESTAMP=1547113664/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
BEGIN
/*!*/;
# at 198048
#190110 10:47:44 server id 1  end_log_pos 198131 CRC32 0xd0675eeb   Table_map: `nmsprod`.`databasechangeloglock` mapped to number 61
# at 198131
#190110 10:47:44 server id 1  end_log_pos 198267 CRC32 0x621c227b   Update_rows: table id 61 flags: STMT_END_F
BINLOG '
...
'/*!*/;
# at 198267
#190110 10:47:44 server id 1  end_log_pos 198298 CRC32 0x5f54e47d   Xid = 29533
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file

Where can I see if is the name of the columns are good configure?

rodrigogs commented 5 years ago

Plase, check how it is named on the information_schema: https://dev.mysql.com/doc/refman/8.0/en/tables-table.html

MInesGomes commented 5 years ago

Thanks Rodrigo, Could you tell me where you read the column names?

The problem in production SMP Debian 4.9.110-3+deb9u6 x86_64 Server MySql version: 5.7.14-google-log (Google) They are ok, the names are not changed.

But here in development the Server version: 8.0.13 MySQL Community Server - GPL

In the link you mentioned I did: select * from INFORMATION_SCHEMA.TABLES; and get

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS

| def | nmsprod | nms_alert | BASE TABLE | InnoDB | 10 | Dynamic | 59 | 277 | 16384 | 0 | 49152 | 0 | 61 | 2018-12-19 17:17:34 | 2019-01-09 18:43:35 | NULL | utf8mb4_0900_ai_ci | NULL |

I do describe nms_alert; and get nothing strange ... the id is ok for example ...

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| alert_begin      | datetime     | YES  |     | NULL    |                |
| alert_sent       | datetime     | YES  |     | NULL    |                |
| alert_answered   | datetime     | YES  |     | NULL    |                |
| text             | varchar(255) | YES  |     | NULL    |                |
| addition         | varchar(255) | YES  |     | NULL    |                |
| updated          | int(11)      | YES  |     | NULL    |                |
| alert_type_id    | bigint(20)   | YES  | MUL | NULL    |                |
| call_rule_id     | bigint(20)   | YES  | MUL | NULL    |                |
| user_attended_id | bigint(20)   | YES  | MUL | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

Do you know what can I compare between 8.0.13 MySQL and 5.7.14-google-log ?

rodrigogs commented 5 years ago

It's in another lib: https://github.com/rodrigogs/zongji/blob/master/index.js#L187

MInesGomes commented 5 years ago

Thanks Rodrigo! The describe and this select from information_schema.columns differs in the order ... And is exactly the changes I see in ev.affectedRows[0].after...

Select ... from information_schema.columns where ... table_name='nms_alert';

+------------------+--------------------+--------------------+----------------+--------------+
| COLUMN_NAME      | COLLATION_NAME     | CHARACTER_SET_NAME | COLUMN_COMMENT | COLUMN_TYPE  |
+------------------+--------------------+--------------------+----------------+--------------+
| addition         | utf8mb4_0900_ai_ci | utf8mb4            |                | varchar(255) |
| alert_answered   | NULL               | NULL               |                | datetime     |
| alert_begin      | NULL               | NULL               |                | datetime     |
| alert_sent       | NULL               | NULL               |                | datetime     |
| alert_type_id    | NULL               | NULL               |                | bigint(20)   |
| call_rule_id     | NULL               | NULL               |                | bigint(20)   |
| id               | NULL               | NULL               |                | bigint(20)   |
| text             | utf8mb4_0900_ai_ci | utf8mb4            |                | varchar(255) |
| updated          | NULL               | NULL               |                | int(11)      |
| user_attended_id | NULL               | NULL               |                | bigint(20)   |
+------------------+--------------------+--------------------+----------------+--------------+

describe nms_alert;

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| alert_begin      | datetime     | YES  |     | NULL    |                |
| alert_sent       | datetime     | YES  |     | NULL    |                |
| alert_answered   | datetime     | YES  |     | NULL    |                |
| text             | varchar(255) | YES  |     | NULL    |                |
| addition         | varchar(255) | YES  |     | NULL    |                |
| updated          | int(11)      | YES  |     | NULL    |                |
| alert_type_id    | bigint(20)   | YES  | MUL | NULL    |                |
| call_rule_id     | bigint(20)   | YES  | MUL | NULL    |                |
| user_attended_id | bigint(20)   | YES  | MUL | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

Do you think I should put an issue in Zonji instead?

rodrigogs commented 5 years ago

You could. But I cant warrant you that I'll be able to fix it, since I don't even have an example dump.

Maybe if you could attach the two dumps in the issue. I don't know if it involves any sensitive information, so, I'm just asking 😋

MInesGomes commented 5 years ago

If I do SELECT COLUMN_NAME, COLLATION_NAME, CHARACTER_SET_NAME, COLUMN_COMMENT, COLUMN_TYPE FROM information_schema.columns WHERE table_schema='nmsprod' AND table_name='nms_alert' order by ORDINAL_POSITION;

+------------------+--------------------+--------------------+----------------+--------------+
| COLUMN_NAME      | COLLATION_NAME     | CHARACTER_SET_NAME | COLUMN_COMMENT | COLUMN_TYPE  |
+------------------+--------------------+--------------------+----------------+--------------+
| id               | NULL               | NULL               |                | bigint(20)   |
| alert_begin      | NULL               | NULL               |                | datetime     |
| alert_sent       | NULL               | NULL               |                | datetime     |
| alert_answered   | NULL               | NULL               |                | datetime     |
| text             | utf8mb4_0900_ai_ci | utf8mb4            |                | varchar(255) |
| addition         | utf8mb4_0900_ai_ci | utf8mb4            |                | varchar(255) |
| updated          | NULL               | NULL               |                | int(11)      |
| alert_type_id    | NULL               | NULL               |                | bigint(20)   |
| call_rule_id     | NULL               | NULL               |                | bigint(20)   |
| user_attended_id | NULL               | NULL               |                | bigint(20)   |
+------------------+--------------------+--------------------+----------------+--------------+

They are ok!!!

Do you think is possible to change this

var tableInfoQueryTemplate = 'SELECT ' +
  'COLUMN_NAME, COLLATION_NAME, CHARACTER_SET_NAME, ' +
  'COLUMN_COMMENT, COLUMN_TYPE ' +
  'FROM information_schema.columns ' + "WHERE table_schema='%s' AND table_name='%s'";

and add

order by ORDINAL_POSITION;

Do you think it would solve the problem?

rodrigogs commented 5 years ago

That's a nice catch! I believe it could work.

Can you open this issue on the zongji repo, since it's not a mysql-events problem?

rodrigogs commented 5 years ago

Nevermind, I'm already patching it :)

MInesGomes commented 5 years ago

Uau! that was fast! Thanks!

rodrigogs commented 5 years ago

Bump it to version 0.5.3. That should solve your situation :)