lb4 discover unable to recognize MariaDB

[mbnoimi]

Steps to reproduce

1. Create a new project using lb4 todolist-mariadb
2. Create a new datasource using lb4 datasource

mariadb.datasource.ts

import {inject, lifeCycleObserver, LifeCycleObserver} from '@loopback/core';
import {juggler} from '@loopback/repository';

const config = {
  name: 'mariadb',
  connector: 'mysql',
  url: 'mysql://node:123@localhost/todolist',
  host: 'localhost',
  port: 3306,
  user: 'node',
  password: '123',
  database: 'todolist',
};

@lifeCycleObserver('datasource')
export class MariadbDataSource
  extends juggler.DataSource
  implements LifeCycleObserver {
  static dataSourceName = 'mariadb';
  static readonly defaultConfig = config;

  constructor(
    @inject('datasources.config.mariadb', {optional: true})
    dsConfig: object = config,
  ) {
    super(dsConfig);
  }
}

3. Run lb4 discover but doesn't show any models!

$ lb4 discover
? Select the connector to discover mariadb
? Select the models which to discover (Press <space> to select, <a> to toggle all, <i> to invert selection)
$

Current Behavior

Unable to create models from MariaDB database

Additional information

• SELECT VERSION();: 10.4.16-MariaDB
• $ lb4 --version:

@loopback/cli version: 2.17.1

@loopback/* dependencies:
  - @loopback/authentication: ^7.0.4
  - @loopback/boot: ^3.1.1
  - @loopback/build: ^6.2.7
  - @loopback/context: ^3.13.1
  - @loopback/core: ^2.13.0
  - @loopback/metadata: ^3.0.4
  - @loopback/openapi-spec-builder: ^3.0.2
  - @loopback/openapi-v3: ^5.1.1
  - @loopback/repository-json-schema: ^3.1.1
  - @loopback/repository: ^3.2.1
  - @loopback/rest: ^9.1.0
  - @loopback/testlab: ^3.2.9
  - @loopback/docs: ^3.19.0
  - @loopback/example-hello-world: ^2.1.14
  - @loopback/example-log-extension: ^2.1.14
  - @loopback/example-rpc-server: ^2.1.14
  - @loopback/example-todo: ^3.9.1
  - @loopback/example-soap-calculator: ^2.4.7
  - @loopback/service-proxy: ^3.0.4
  - @loopback/http-caching-proxy: ^2.1.18
  - @loopback/http-server: ^2.3.2
  - @loopback/example-todo-list: ^3.6.2
  - @loopback/dist-util: ^0.4.0
  - @loopback/rest-explorer: ^3.0.4
  - @loopback/eslint-config: ^10.0.3
  - @loopback/example-express-composition: ^2.5.2
  - @loopback/example-greeter-extension: ^2.1.14
  - @loopback/booter-lb3app: ^2.2.14
  - @loopback/example-lb3-application: ^2.5.2
  - @loopback/example-greeting-app: ^2.2.12
  - @loopback/example-context: ^2.1.14
  - @loopback/repository-tests: ^0.14.2
  - @loopback/health: ^0.6.1
  - @loopback/authorization: ^0.7.4
  - @loopback/rest-crud: ^0.8.18
  - @loopback/security: ^0.3.4
  - @loopback/authentication-passport: ^3.0.4
  - @loopback/example-metrics-prometheus: ^0.4.4
  - @loopback/metrics: ^0.5.1
  - @loopback/model-api-builder: ^2.1.18
  - @loopback/logging: ^0.4.4
  - @loopback/example-access-control-migration: ^2.3.2
  - @loopback/example-file-transfer: ^1.5.7
  - @loopback/example-rest-crud: ^1.7.2
  - @loopback/apiconnect: ^0.5.4
  - @loopback/example-validation-app: ^1.8.2
  - @loopback/cron: ^0.3.4
  - @loopback/example-multi-tenancy: ^0.7.2
  - @loopback/example-passport-login: ^1.8.1
  - @loopback/authentication-jwt: ^0.7.4
  - @loopback/context-explorer: ^0.2.4
  - @loopback/express: ^3.1.0
  - @loopback/example-todo-jwt: ^1.4.2
  - @loopback/mock-oauth2-provider: ^0.1.12
  - @loopback/pooling: ^0.3.4
  - @loopback/typeorm: ^0.2.4
  - @loopback/example-graphql: ^0.1.4
  - @loopback/graphql: ^0.2.2
  - @loopback/filter: ^1.2.1
  - @loopback/rest-msgpack: ^0.2.3
  - @loopback/example-binding-resolution: ^0.1.2
  - @loopback/example-webpack: ^0.2.1
  - @loopback/example-socketio: ^0.1.1
  - @loopback/socketio: ^0.1.1
  - @loopback/monorepo: ^0.1.1

[mbnoimi]

database.sql

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.16-MariaDB - Source distribution
-- Server OS:                    Linux
-- HeidiSQL Version:             11.0.0.5919
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


-- Dumping database structure for todolist
DROP DATABASE IF EXISTS `todolist`;
CREATE DATABASE IF NOT EXISTS `todolist` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `todolist`;

-- Dumping structure for table todolist.list
DROP TABLE IF EXISTS `list`;
CREATE TABLE IF NOT EXISTS `list` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `desc` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- Data exporting was unselected.

-- Dumping structure for table todolist.todo
DROP TABLE IF EXISTS `todo`;
CREATE TABLE IF NOT EXISTS `todo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `desc` text DEFAULT NULL,
  `list_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `todo_FK` (`list_id`),
  CONSTRAINT `todo_FK` FOREIGN KEY (`list_id`) REFERENCES `list` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- Data exporting was unselected.

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

[dhmlau]

@mbnoimi, could you please try to use --schema (to specify the schema name) or --all (to show all tables)? https://loopback.io/doc/en/lb4/Discovering-models.html

[mbnoimi]

Worked with lb4 discover --schema todolist
I think this is a bug, so I'll keep it opened; does it right?

[dhmlau]

I think it's working as designed. If --schema option is not provided, it uses the schema name that's same as your user name provided in the datasource (at least that's what happens for DB2). But @bajtos @jannyHou @raymondfeng can confirm.

[mbnoimi]

it uses the schema name that's same as your user name

It expect to use the same database not the same username!

[jannyHou]

@mbnoimi @dhmlau For mysql, yes it's same as DB2, see code in https://github.com/strongloop/loopback-connector-mysql/blob/6e06fe47306ba5beede328342b8123f36c66bba2/lib/discovery.js#L78.
I don't think people usually name their lb datasource the same as the back-end schema. For example, appending DS to the connector name like MysqlDS, but it won't map to the mysql schema, therefore we could not make that assumption.
Guessing the schema name is hard, treating user as the default one is the best we could do.

[mbnoimi]

@jannyHou Forgive me but I'm unable to understand how it hard to guess schemas in MariaDB/MySQL !
It's very easy using SQL:

select schema_name as database_name
from information_schema.schemata
order by schema_name;

or just by using:

show schemas;

[dkislov]

I faced the same issue. When I tried to run lb4 discover against MySQL 5.6 I got lb4 "frozen" on second prompt question, like
lb4 discover
? Select the connector to discover my_datasource
? Select the models which to discover

[dkislov]

When I tried to find workaround for this issue, made little piece of TypeScript code (in file discover.ts):

import fs from 'fs';
import {MyDataSource} from './datasources/my_datasource.datasource';

export async function discover(args: string[]) {
  const dataSource = new MyDataSource();
  const tableNames = args.slice(2);
  dataSource.on('connected', async () => {
    for (const tableName of tableNames) {
      const tableSchemas = await dataSource.discoverSchemas(
        tableName, {relations: true},
      );
      fs.writeFile('./src/models/schemas/' + tableName + '.json', JSON.stringify(tableSchemas, null, 2), function (err) {
        if (err) return console.log(err);
        console.log(tableName, ' is discovered...');
      });
    }
    process.exit(0);
  });
}

discover(process.argv).catch(err => {
  console.error('Cannot discover database table', err);
  process.exit(1);
}); 

and got list of JSON table schema definitions, like this:

{
  "test.user": {
    "name": "User",
    "options": {
      "idInjection": false,
      "mysql": {
        "schema": "test",
        "table": "user"
      },
      "relations": {}
    },
    "properties": {
      "userId": {
        "type": "Number",
        "required": true,
        "length": null,
        "precision": 10,
        "scale": 0,
        "id": 1,
        "mysql": {
          "columnName": "user_id",
          "dataType": "int",
          "dataLength": null,
          "dataPrecision": 10,
          "dataScale": 0,
          "nullable": "N"
        }
      },
      "userName": {
        "type": "String",
        "required": true,
        "length": 50,
        "precision": null,
        "scale": null,
        "mysql": {
          "columnName": "user_name",
          "dataType": "varchar",
          "dataLength": 50,
          "dataPrecision": null,
          "dataScale": null,
          "nullable": "N"
        }
      }
    }
  }
}

My question: How can I convert this Json file to Model code user.model.ts?

[dkislov]

On my case the issue is solved. I found that I have to run lb4 discover with option --schema, but without option --dataSource, so than it works good. But it was interesting journey.

[djensen47]

I'm having a similar issue but the suggestions above did not work. Instead, on Mysql 5.6, the dicover command only shows views and shows none of the tables.

I'm not even sure if the --views option is working. I've tried --views false and --views=false and the views still show up and no tables.

[djensen47]

@dkislov How did you run that typescript script you wrote? Did you have to transpile it first?

I used $ npx ts-node discover.ts

[dkislov]

@dkislov How did you run that typescript script you wrote? Did you have to transpile it first?

I used $ npx ts-node discover.ts

Did you get some results from the running script? BTW I run lb4 under debugger in Visual Studio Code, this was a way I found successful combination of parameters. :-)

[djensen47]

Well, the problem is that the json file is not writing to disk. It creates the file but writing the JSON fails for some reason.

I've also tried

$ lb4 discover --schema=table_name

but nothing happens. I'm runnin MySQL 5.6, which is kinda old, I have no idea if this is the problem or not.