NodeJS MySQL server - MJoin not working

djagercd

Hi all, I am having a problem where an MJoin on a link table is not allowing the insert to take place and throws a foreign key constraint error.

The tables I have are:

*CREATE TABLE `users` (
  `uuid` char(36) COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `reset_password` tinyint(1) NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;*

*CREATE TABLE `roles` (
  `uuid` char(36) COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `description` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;*

*CREATE TABLE `user_roles` (
  `role_uuid` char(36) COLLATE utf8mb4_bin NOT NULL,
  `user_uuid` char(36) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`role_uuid`,`user_uuid`),
  KEY `user_uuid` (`user_uuid`),
  CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`role_uuid`) REFERENCES `roles` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`user_uuid`) REFERENCES `users` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;*

I use NodeJS and this is my user-api.js file

//-------------------------------------------------------------
'use strict';

const
    {
        Editor,
        Field,
        Validate,
        Format,
        Options,
        Mjoin
    } = require('datatables.net-editor-server'),
    db = require('../db'),
    bcrypt = require('bcryptjs'),
    uuidv4 = require('uuid/v4'),
    {User} = require('../models');

exports.UserAPI = (req, res, next) => {


    const editor = new Editor(db, 'users', 'uuid')
        .debug(true)
        .fields(
            new Field('users.uuid')
                .set(Field.SetType.Create)
                .setValue(uuidv4()),
            new Field('users.name')
                .validator(Validate.required(new Validate.Options(
                    {message: 'The user name is required'}
                ))),
            new Field('users.email')
                .validator(Validate.required(new Validate.Options(
                    {message: 'The user email is required'}
                )))
                .validator(Validate.dbUnique(new Validate.Options(
                    {message: 'The user email must be unique'}
                )))
                .validator(Validate.email(new Validate.Options(
                    {message: 'The user email must be a valid email'}
                ))),
            new Field('users.password')
                .validator(Validate.required(new Validate.Options(
                    {message: 'The user password is required'}
                ))),
            new Field("users.reset_password")
                .getFormatter( (val, data) => val === 1)
        )
        .join(
            new Mjoin('roles')
                .link('users.uuid', 'user_roles.user_uuid')
                .link('roles.uuid', 'user_roles.role_uuid')
                .fields(
                    new Field('uuid')
                        .validator(Validate.required(new Validate.Options(
                            {message: 'You must assign a role to the user'}
                        )))
                        .options(
                            new Options().table('roles').value('uuid').label('name')
                        ),
                    new Field('name')
                        .options(
                            new Options()
                                .table('roles')
                                .value('roles.uuid')
                                .label('name')
                        )
                )
        );

    editor.on('preCreate', (editor, values) => {
        if (values.users.password) {
            const hashedPassword = bcrypt.hashSync(values.users.password, 12);
            values.users.password = hashedPassword;
            values.users.reset_password = true;
        }
    });


    editor.on('preEdit', (editor, id, values) => {
        User.findByPk(values.users.uuid)
            .then(user => {
                if (user.password !== values.users.password) {
                    const hashedPassword = bcrypt.hashSync(values.users.password, 12);
                    values.users.password = hashedPassword;
                }
            })
            .catch(err => {
                console.log(err);
            })

    });

    editor.process(req.body)
        .then(function () {
            res.send(JSON.stringify(editor.data()));
        })
        .catch(err => {
            console.log(err);
        });

};
//-------------------------------------------------------------

and finally my page javascript

//-------------------------------------------------------------
editor = new $.fn.dataTable.Editor({
            ajax: {
                url: "/api/user",
                headers: {
                    'CSRFToken': "<%= csrfToken %>",
                },
                data: function (d) {
                    d._csrf = "<%= csrfToken %>"
                }
            },
            // template: "#customForm",
            fields: [
                {
                    label: "uuid",
                    name: "users.uuid",
                    type: "hidden"
                },
                {
                    label: "Name",
                    name: "users.name",
                    fieldInfo: "The users full name."
                },
                {
                    label: "Email",
                    name: "users.email",
                    fieldInfo: "The users email address.  This must be unique."
                },
                {
                    label: "Password",
                    name: "users.password",
                    type: "password",
                    fieldInfo: "The users password."
                },
                {
                    // label: "",
                    name: "users.reset_password",
                    type: "checkbox",
                    options: [
                        {label: "Reset Password", value: true}
                    ],
                    separator: '',
                    unselectedValue: false,
                    fieldInfo: "User must reset password on next login."
                },
                {
                    label: "Roles",
                    type: "checkbox",
                    name: "roles[].uuid",
                    // separator: ', ',
                    fieldInfo: "Roles assigned to the user"
                }
                // {
                //     label: "Email",
                //     name: "users.email",
                //     type: "email",
                //     fieldInfo: 'The users email address.  This must be unique.'
                // },

            ]
        });
//-------------------------------------------------------------

here is a debug from Knex:

*{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 'd@d.com' ],
  __knexQueryUid: 'cbcbaf42-824a-4a1c-be62-04718cf751fb',
  sql:
   'select `users`.`email` from `users` where `users`.`email` = ?' }
{ method: 'insert',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings:
   [ 'd@d.com',
     'd',
     '$2a$12$7pztTvnfygH24oLmScDr4enYq22hny2wNYSJPBKdG1hzTZi1WII4.',
     true,
     'c1396b1b-6eb8-48ca-a8f5-e8d763db0b43' ],
  __knexQueryUid: '87c2ff5d-9589-40e0-ba3c-6c3c0526f808',
  sql:
   'insert into `users` (`email`, `name`, `password`, `reset_password`, `uuid`) values (?, ?, ?, ?, ?)' }
{ method: 'insert',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ '49107101-42e3-4220-b16c-0de862706254', '0' ],
  __knexQueryUid: '75a6cb0c-bf91-4e5f-a5c2-7f697b6af474',
  sql:
   'insert into `user_roles` (`role_uuid`, `user_uuid`) values (?, ?)' }
{ Error: Cannot add or update a child row: a foreign key constraint fails
    at Packet.asError (/Users/charlesdejager/dev/gdskills/node_modules/mysql2/lib/packets/packet.js:684:17)
    at Query.execute (/Users/charlesdejager/dev/gdskills/node_modules/mysql2/lib/commands/command.js:28:26)
    at Connection.handlePacket (/Users/charlesdejager/dev/gdskills/node_modules/mysql2/lib/connection.js:449:32)
    at PacketParser.Connection.packetParser.p [as onPacket] (/Users/charlesdejager/dev/gdskills/node_modules/mysql2/lib/connection.js:72:12)
    at PacketParser.executeStart (/Users/charlesdejager/dev/gdskills/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.Connection.stream.on.data (/Users/charlesdejager/dev/gdskills/node_modules/mysql2/lib/connection.js:79:25)
    at Socket.emit (events.js:188:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:145:17)
  code: 'ER_NO_REFERENCED_ROW',
  errno: 1216,
  sqlState: '23000',
  sqlMessage:
   'Cannot add or update a child row: a foreign key constraint fails' }*

The funny thing is that if I use Postgres this works perfectly. With MySQL this fails.

Any help will be appreciated.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Hi,

Thanks for the debug trace! It's failing on the insert because its trying to enter 0 as the user_uuid for the link table. That suggests to me that for some reason the setValue uuid isn't being detected when using MySQL.

Could you try setting the third parameter of the Editor constructor to be user.uuid please? Editor does a full match on the name which I think is what is tripping it up here.

I suspect Postgres is working because it is returning the newly created uuid in the automatic primary key retrieval while MySQL isn't.

Allan

djagercd

Hi Allan,

I changed the constructor to id from uuid to users.uuid. That fixed the problem.

Thanks.

allan

Perfect - good to hear .

Allan