Getting ResultSetHeader { serverStatus: 2, ...} when running CREATE TEMPORARY TABLE query. Why?-CodePudding

I am using the mysql2 library for deno/node to try and run a CREATE query for a TEMPORARY table but results only contain the ResultSetHeader showing a serverStatus of 2. After some research it appears this status code means SERVER_STATUS_AUTOCOMMIT flag is enabled.

The table is never created and I never get a real error..

Here is the code:

// deno run --allow-env --allow-net z.ts

import * as mysql2 from "https://deno.land/x/mysql2/mod.ts";

const pool = mysql2.createPool({
  host: "db.ondigitalocean.com",
  port: 12345,
  user: "devuser",
  password: "letmein",
  database: "devdb",
  connectionLimit: 4,
  timezone: " 00:00" // Sync app timezone with MySQL server UTC timezone
});

/* CREATE temp table tblTodos in database memory */
const sqlCREATE1 = 
`CREATE TEMPORARY TABLE IF NOT EXISTS tblTodos (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  userId BIGINT UNSIGNED DEFAULT NULL,
  CHECK (userId >= 0),
  todoCreated TIMESTAMP DEFAULT NULL,
  todoModified TIMESTAMP DEFAULT NULL,
  todoStatus VARCHAR(16) DEFAULT NULL,
  todoTitle VARCHAR(128) DEFAULT NULL, 
  todoBody VARCHAR(1024) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE = INNODB
  AUTO_INCREMENT=2001
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;`;

  const create1Result = await pool.execute(sqlCREATE1);
  
  if (create1Result) {
  
    console.log(create1Result);
  
  }

  await pool.end();

This is what create1Results contains even though I'm not certain this is an error.

[
  ResultSetHeader {
    fieldCount: 0,
    infileName: undefined,
    affectedRows: 0,
    insertId: 0,
    info: "",
    serverStatus: 2,
    warningStatus: 0,
    stateChanges: undefined,
    changedRows: undefined
  },
  undefined
]

CodePudding user response：

Temporary tables exist only in the same session where they were created, and the table is automatically removed when the session ends.

I'm not a Node.js developer, but I notice in the documented examples:

pool.query("SELECT field FROM atable", function(err, rows, fields) {
   // Connection is automatically released when query resolves
})

So your temporary table is dropped almost immediately after it is created.

You need to get a connection and use that connection for creating the temporary table, and run all subsequent queries that reference that temporary table.

Again, an example from the documentation:

pool.getConnection(function(err, conn) {
   // Do something with the connection
   conn.query(/* ... */);
   // Don't forget to release the connection when finished!
   pool.releaseConnection(conn);
})

CodePudding user response：

Example of desired outcome based on Bill's answer here for completion.

Console Output:

Table tblToDos created.
Table tblToDos contains 0 records.
1 record(s) inserted. id: 2001
[
  {
    id: 2001,
    userId: 1001,
    todoCreated: 2022-09-09T21:24:24.000Z,
    todoModified: 2022-09-09T21:24:24.000Z,
    todoStatus: "Pending",
    todoTitle: "Shopping List #1",
    todoBody: "Apples & Oranges"
  }
]
1 record(s) updated.
[
  {
    id: 2001,
    userId: 1001,
    todoCreated: 2022-09-09T21:24:24.000Z,
    todoModified: 2022-09-09T21:24:24.000Z,
    todoStatus: "Complete",
    todoTitle: "Shopping List #1",
    todoBody: "Apples & Oranges"
  }
]

Code:

// deno run --allow-env --allow-net z.ts

import * as mysql2 from "https://deno.land/x/mysql2/mod.ts";

const pool = mysql2.createPool({
  host: "db.ondigitalocean.com",
  port: 12345,
  user: "devuser",
  password: "letmein",
  database: "devdb",
  connectionLimit: 4,
  timezone: " 00:00" // Sync app timezone with MySQL server UTC timezone
});

/* CREATE temp table tblTodos in database memory */
const sqlCREATE1: string = 
`CREATE TEMPORARY TABLE IF NOT EXISTS tblTodos (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  userId BIGINT UNSIGNED DEFAULT NULL,
  CHECK (userId >= 0),
  todoCreated TIMESTAMP DEFAULT NULL,
  todoModified TIMESTAMP DEFAULT NULL,
  todoStatus VARCHAR(16) DEFAULT NULL,
  todoTitle VARCHAR(128) DEFAULT NULL, 
  todoBody VARCHAR(1024) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE = INNODB
  AUTO_INCREMENT=2001
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;`;

/* SELECT 1 shows an empty table */
const sqlSELECT1: string = 
`SELECT 
  * 
FROM 
  tblTodos;`;

/* INSERT 1 adds a Pending todo record to the table */
const sqlINSERT1: string = 
`INSERT INTO tblTodos (
  id, userId,
  todoCreated, todoModified, todoStatus,
  todoTitle, todoBody
) 
VALUES 
  (
    NULL, 1001, 
    NOW(), NOW(), 'Pending', 
    'Shopping List #1', 'Apples & Oranges'
  );`;

/* SELECT 2 shows the Pending todo record */
const sqlSELECT2: string = 
`SELECT 
  * 
FROM 
  tblTodos 
WHERE 
  todoStatus = 'Pending';`;

/* UPDATE 1 changes todo status from Pending to Complete */
const sqlUPDATE1: string = 
`UPDATE 
  tblTodos 
SET 
  todoModified = NOW(), 
  todoStatus = 'Complete'
WHERE 
  id = 2001
  AND userId = 1001;`;

/* SELECT 3 shows the Complete todo record */
const sqlSELECT3: string = 
`SELECT 
  * 
FROM 
  tblTodos 
WHERE 
  todoStatus = 'Complete'
  AND userId = 1001;`;

/* DELETE 1 deletes todo from table */
const sqlDELETE1: string = 
`DELETE FROM 
  tblTodos 
WHERE 
  id = 2001
  AND userId = 1001;`;

/* SELECT 4 once again shows an empty table */
const sqlSELECT4: string = 
`SELECT 
  * 
FROM 
  tblTodos;`;

/* DROP 1 deletes table tblTodos from database */
const sqlDROP1: string = 
`DROP 
  TEMPORARY TABLE IF EXISTS tblTodos;`;

const connection = await pool.getConnection();

try {

  const create1Result = await connection.execute(sqlCREATE1);
  if (create1Result) console.log("Table tblToDos created.");

  const select1Result = await connection.execute(sqlSELECT1);
  if (select1Result) console.log("Table tblToDos contains", select1Result[0].length, "records.");

  const insert1Result = await connection.execute(sqlINSERT1);
  if (insert1Result) console.log(insert1Result[0].affectedRows, "record(s) inserted.", "id:", insert1Result[0].insertId);

  const select2Result = await connection.execute(sqlSELECT2);
  if (select2Result) console.log(select2Result[0]);

  const update1Result = await connection.execute(sqlUPDATE1);
  if (update1Result) console.log(update1Result[0].affectedRows, "record(s) updated.");

  const select3Result = await connection.execute(sqlSELECT3);
  if (select3Result) console.log(select3Result[0]);

  const delete1Result = await connection.execute(sqlDELETE1);
  if (delete1Result) console.log(delete1Result[0].affectedRows, "record(s) deleted.");

  const select4Result = await connection.execute(sqlSELECT4);
  if (select4Result) console.log("Table tblToDos contains", select1Result[0].length, "records.");

  const drop1Result = await connection.execute(sqlDROP1);
  if (drop1Result) console.log("Table tblToDos droped.");

} finally {

  connection.release();

}

await pool.end();