Multitenancy Error: ER_NO_DB_ERROR: No database selected

[philippefutureboy]

Versions

Node.js: ^12
npm (local): 6.9.0
npm (docker): 6.10.0
@cubejs-backend/mysql-driver: ^0.9.14
@cubejs-backend/server: ^0.9.24
@cubejs-client/core: ^0.9.12

Describe the bug

Error: Error: ER_NO_DB_ERROR: No database selected
Trace: https://gist.github.com/philippefutureboy/9d012684f9f8008903dcfd2059e5687d

Error when attempting to use multitenancy in my test setup. I couldn't find the source of the error. Cube.js seems to forget to use the database passed to the MySQLDriver instance created in driverFactory (see step 4, you might find right away the error).

Important details to assess the situation:

• The specified database doesn't exist when the Cube.js instance first connects to the database. It is only created in the setup step of the test in __e2e__/schemas/Orders.test.js. It is created before doing the request, and before signing the jwt token for the client-core instance.

To Reproduce

1. Clone the repository @ https://github.com/arthurintelligence/cubejs-test.git
2. Create your local .env files:

touch .env.development.local
touch .env.test.local

3. Fill both of the local env files with:

# CubeJS
CUBEJS_APP=arthur-cubejs_dev
CUBEJS_API_SECRET=<>

Where CUBEJS_API_SECRET can be generated with the following function:

function genCubeJSSecret() {
  const chars = '0123456789abcdef';
  let secret = '';
  for(let i = 0; i < 128; i+= 1) {
    secret += chars.charAt(Math.floor(Math.random() * 16))
  }
  return secret;
}

4. Replace the content of cubejsOptions.js with:

const MySQLDriver = require("@cubejs-backend/mysql-driver");

module.exports = {
  contextToAppId: ({ authInfo }) =>
    `${process.env.CUBEJS_APP}_${authInfo.databaseName}`,
  driverFactory: ({ authInfo }) =>
    new MySQLDriver({
      database: authInfo.databaseName,
    }),
};

5. Install the dependencies:

npm i

6. Start the docker-compose setup, twice.

docker-compose -f docker-compose.development.yml build
docker-compose -f docker-compose.development.yml up
# cube_mysql container will crash ~
docker-compose -f docker-compose.development.yml up
# now you are good to go!

7. Start the e2e tests in another terminal:

npm run e2e

8. Now you should get the expected error (I've tested this exact steps, and I was able to reproduce. I made sure to delete all of my docker images related to this project prior to step 6).

Expected behavior

Cube.js connects to the database specified by the JWT and executes the query.

Final Notes

Let me know if you need any extra information to reproduce the issue :)

[paveltiunov]

@philippefutureboy Hey Philippe! Thanks for posting this! Could you please try to set CUBEJS_DB_HOST and CUBEJS_DB_PORT? Do you have mysql outside of your container?

[philippefutureboy]

@paveltiunov Heya! Thanks for the quick answer :) To answer your question, both are already specified as the default environment in the docker-compose.development.yml file.
The Cube.js instance would probably not start otherwise.

[philippefutureboy]

Also, important information to add - the Cube.js instance run in production even in the docker-compose.development.yml configuration. This is to ensure that authMiddleware and other auth related functions are run.

Quick question: Exactly which configuration points are skipped for development build? The message in dev doesn't say much

[paveltiunov]

@philippefutureboy Have you resolved this one? Do you think your mysql instance contains example database?

[philippefutureboy]

@paveltiunov Unfortunately I wasn't able to make much progress on my side.
As for the example database, I do not specify a default database anywhere in the .env files or the docker-compose.development.yml#cube_mysql.environment / #cube.environment blocks.
I create the database dynamically for each tests in the beforeAll:

__e2e__/schema/Orders.test.js:

beforeAll(async () => {
  const result = await db.createDatabase();
  databaseName = result.databaseName;
  cubejsApi = cube({ databaseName });
  return db.query(insertTemplate({ databaseName }));
});

Where the cube function is defined as follow:

__e2e__/__fixtures__/cube.js

function cube(payload = {}) {
  return cubejs(
    jwt.sign(payload, process.env.CUBEJS_API_SECRET, { expiresIn: '30d' }),
    { apiUrl: 'http://localhost:4000/cubejs-api/v1' }
  );
}

So the JWT contains the databaseName parameter, which is used for the cubejsOptions:

cubejsOptions.js

const MySQLDriver = require("@cubejs-backend/mysql-driver");

module.exports = {
  contextToAppId: ({ authInfo }) =>
    `${process.env.CUBEJS_APP}_${authInfo.databaseName}`,
  driverFactory: ({ authInfo }) =>
    new MySQLDriver({
      database: authInfo.databaseName
    })
};

So my understanding is:

• No default database is specified at initialization time
• Database is created before issuing a request
• Database name is saved in the JWT
• Database name is used to instantiate the MySQLDriver
• MySQLDriver should attempt to connect to the specified database without any issues.

Where am I falling short of reality?

[paveltiunov]

@philippefutureboy Seems like DB isn't created before test is run or it's a permission problem.

[philippefutureboy]

@paveltiunov:

I tried to address the points you brought up, but no avail:

1. Permission Problem:

I changed the createDatabase function to include a GRANT ALL statement to add the rights to the newly created DB to the root user:

__e2e__/__fixtures__/database.js

async function createDatabase({ namespace, databaseName } = {}) {
  try {
    namespace = namespace || uuidv4();
    databaseName = 
      databaseName ||
      uuidv5(
        path.basename(__filename),
        namespace
      ).replace(/-/g, '');
    await query(`
      CREATE DATABASE IF NOT EXISTS \`${databaseName}\`;
      GRANT ALL PRIVILEGES ON \`${databaseName}\`.* TO '${process.env.CUBEJS_DB_USER}'@'localhost';
      FLUSH PRIVILEGES;
      USE \`${databaseName}\`;
    `);
    return { databaseName, namespace };
  } catch (err) {
    throw new Error(`Failed to create database: ${err.message}`);
  }
}

No change.

2. DB isn't created before test is run

I created a new util function for the database fixture that checks if the database exists:

__e2e__/__fixtures__/database.js

async function checkIfDatabaseExists(databaseName) {
  const exists = await query(`
    SELECT SCHEMA_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE SCHEMA_NAME = '${databaseName}';
  `)
  console.log(exists);
  return exists !== '';
}

And I call the function right before doing the cubeJsApi.load call:

__e2e__/schema/Orders.test.js

test('should return the correct totalAmounts', async () => {
  await db.checkIfDatabaseExists(databaseName);
  const { loadResponse: { data } } = await cubejsApi.load({ /* ... */ });
  /* ... */
});

Which prints

  SCHEMA_NAME
    3aa975a009505b1faf1a1e5453a7d934

and returns true.

So the database has been created before the cubeJsApi.load call.

For this part, did you mean that the database has to be created before the cubejs instance is booted up, or before the test in the test suite is run?

Cheers 🚀

[philippefutureboy]

@paveltiunov

Welp I found it. 🤦‍♂️

index.js

const CubejsServer = require('@cubejs-backend/server');
const cubejsOptions = require('./cubejsOptions');

console.log(cubejsOptions);

const server = cubejsOptions
- ? new CubejsServer() 
- : new CubejsServer(cubejsOptions);
+ ? new CubejsServer(cubejsOptions) 
+ : new CubejsServer();

server.listen().then(({ port }) => {
  console.log(`🚀 Cube.js server is listening on ${port}`);
});

Found it because I tried logging some stuff from the driverFactory, and it would not print.

Thanks for your help, it allowed to narrow the potential domain of issues 🚀

[philippefutureboy]

The good news is that now I have a proof of concept of how I can build the @cubejs-backend/test package ;)