Selecting Data using Sequelize from existing mysql db

[VovanSuper]

Trying to work with existing MySql with node.js database using generated models (sequelize-auto) returns no data (empty array). Even though with sql driver there's resulting set:

 var conn = mysql.createConnection({host: params.host, port: params.port,
    database: params.dbname,user:params.username, password: params.password
    });
    con.query('select * from User where username like \'%some%\' limit 30 ').on('result', function(res){
        console.log(++i + 'th item is ' + res.username );
    });

-returns ok. But I try to use sequelize's auto-generated model (sequelize-auto):

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('User', { 
    id: {
      type: DataTypes.INTEGER(11),
      allowNull: false
    },
    info_id: {
      type: DataTypes.INTEGER(11),
      allowNull: true
    },
    username: {
      type: DataTypes.STRING,
      allowNull: false
    },
    salt: {
      type: DataTypes.STRING,
      allowNull: false
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    },
    verified: {
      type: DataTypes.BOOLEAN,
      allowNull: true,
      defaultValue: '1'
    },
    score: {
      type: DataTypes.INTEGER(11),
      allowNull: true,
      defaultValue: '0'
    },
    avatar: {
      type: DataTypes.STRING,
      allowNull: true
    },
    registration: {
      type: DataTypes.DATE,
      allowNull: true,
      defaultValue: 'CURRENT_TIMESTAMP'
    },
    role: {
      type: DataTypes.STRING,
      allowNull: true
    },
    referer: {
      type: DataTypes.STRING,
      allowNull: false
    },
    usercode: {
      type: DataTypes.STRING,
      allowNull: false
    },
    autosharebuy: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      defaultValue: '0'
    },
    autoshareaction: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      defaultValue: '0'
    },
    nickname: {
      type: DataTypes.STRING,
      allowNull: true
    }
  });
};

using sequelize v1.7 and v3.0 : -connection:

var Sequelize = require('sequelize');
// var params = require('./configs.js').params;

    module.exports.connect = function(params, callback){
        var con = new Sequelize(params.dbname, params.username, params.password,
            {host: params.host, dialect: params.dialect, port: params.port}
        );
        // con.authenticate().done(function(err, res){
        //   if(err) {
        //       console.log('Error while trying to authenticate on the DB server', err);
        //       callback(err, null);
        //       return;
        //   }
        //     console.log('Successfully authenticated on the Db server');

        // });
        if(con) {
            console.dir('Connection is established in connectin.js');
            callback(null, con);
        }else {
            console.log('Error, not connected to DB in connection.js');
            callback(new Error('Error Autthenticating to the model'), null);
        }  

};
....

con = require(path.join(__dirname, '/connection'));
User.sync({force: false}).then(  //sequelize v3.0
    function(TInstance){
        console.log('Connected and Synced DB');

        TInstance.findAll({where: {username: 'some Name'}}).then(
          function(records){
               .forEach(function(item, i){
                   console.log(i + 'th Item is ' + item);
             });
          },
          function(err) { console.log('No data found, Error', err);   }
        );
...


User.findAll({where: {id: 1}}).success(   // v1.7
            function(records){
                console.log('Some data found 2');
                records.forEach(function(item, i) {
                    console.log(item + ' id of ' + i);
                });
                console.log('Returned ' + records.length + ' items');
            }).....
   );

It shows select * from user where \usernamelike \'%com%\' limit 10 and times-out after some seconds with no results

[VovanSuper]

'Connection is established in connectin.js'
Executing (default): CREATE TABLE IF NOT EXISTS `Users` (`id` INTEGER(11) NOT NULL, `info_id` INTEGER(11), `username` VARCHAR(255) NOT NULL, `salt` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, `verified` TINYINT(1) DEFAULT '1', `score` INTEGER(11) DEFAULT '0', `avatar` VARCHAR(255), `registration` DATETIME DEFAULT 'CURRENT_TIMESTAMP', `role` VARCHAR(255), `referer` VARCHAR(255) NOT NULL, `usercode` VARCHAR(255) NOT NULL, `autosharebuy` INTEGER(11) NOT NULL DEFAULT '0', `autoshareaction` INTEGER(11) NOT NULL DEFAULT '0', `nickname` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL) ENGINE=InnoDB;
Error while trying to Sync DB { [Error: ER_INVALID_DEFAULT: Invalid default value for 'registration']
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sqlState: '42000',
  index: 0,
  sql: 'CREATE TABLE IF NOT EXISTS `Users` (`id` INTEGER(11) NOT NULL, `info_id` INTEGER(11), `username` VARCHAR(255) NOT NULL, `salt` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, `verified` TINYINT(1) DEFAULT \'1\', `score` INTEGER(11) DEFAULT \'0\', `avatar` VARCHAR(255), `registration` DATETIME DEFAULT \'CURRENT_TIMESTAMP\', `role` VARCHAR(255), `referer` VARCHAR(255) NOT NULL, `usercode` VARCHAR(255) NOT NULL, `autosharebuy` INTEGER(11) NOT NULL DEFAULT \'0\', `autoshareaction` INTEGER(11) NOT NULL DEFAULT \'0\', `nickname` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL) ENGINE=InnoDB;' }

[mickhansen]

Please make the effort to actually format your code.
Please show the model definition for the relevant model, especially the registration attribute since that's the one that errors.

[VovanSuper]

So, could you please recheck the thing

[mickhansen]

defaultValue: 'CURRENT_TIMESTAMP' should be defaultValue: DataTypes.NOW

[VovanSuper]

Ok, so first it's a bug in sequelize-auto then (as the model are sequelize-auto generated).
Second is the problem itself, return is empty array (though there are data in the db):

Executing (default): CREATE TABLE IF NOT EXISTS `Users` (`id` INTEGER(11) NOT NULL, `info_id` INTEGER(11), `username` VARCHAR(255) NOT NULL, `salt` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, `verified` TINYINT(1) DEFAULT '1', `score` INTEGER(11) DEFAULT '0', `avatar` VARCHAR(255), `registration` DATETIME, `role` VARCHAR(255), `referer` VARCHAR(255) NOT NULL, `usercode` VARCHAR(255) NOT NULL, `autosharebuy` INTEGER(11) NOT NULL DEFAULT '0', `autoshareaction` INTEGER(11) NOT NULL DEFAULT '0', `nickname` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL) ENGINE=InnoDB;
Connected and Synced DB
Executing (default): SELECT * FROM `Users` WHERE `Users`.`username`='tc@tc-holding.com';
Executing (default): SELECT * FROM `Users` WHERE `Users`.`id`=1;
Some data found
[]
Some data found 2
Returned 0 items

[janmeier]

Are you sure the table name (Users) matches your current table exactly? Perhaps sequelize is creating a table named users, while your table is called user?

[VovanSuper]

yes, indeed, table name is User and sequzelize-auto made it user, but I tried manually fixing the models (names, and additionaly removing some extra commas sequelize-auto produces in the end of js-objects = another bug) and it still didn't work

[mickhansen]

@VovanSuper Start by verifying your models. sequelize-auto hasnt been updated as far as i can tell and no one from the Sequelize team knows anything about it.