Connecting Express server and mariadb

huangapple
go评论106阅读模式
英文:

Connecting Express server and mariadb

问题

Express服务器抛出的错误是数据库连接池超时。你可以尝试以下几种方法来解决这个问题：

  1. 增加连接池的限制： 你可以尝试增加连接池的限制，以允许更多的并发连接。在你的Express服务器代码中，connection对象的connectionLimit属性设置了连接池的最大连接数。你可以尝试增加这个值，例如设置为更高的数字，以确保有足够的数据库连接可用。

    1. var connection = mariadb.createPool({
    2.   host: "localhost",
    3.   user: "user",
    4.   password: "pass",
    5.   database: "users",
    6.   connectionLimit: 10 // 例如，将连接限制增加到10
    7. });

  2. 检查数据库连接配置： 确保数据库主机地址、用户名和密码等数据库连接配置是正确的。你已经提到可以从控制台成功连接到数据库，但仍然需要确保这些配置正确。

  3. 检查数据库状态： 检查数据库服务器的状态，确保它正常运行，没有资源不足或其他问题。你可以尝试从命令行或其他数据库客户端连接并执行一些查询，以确保数据库能够正常响应。

  4. 检查数据库连接池配置： 除了连接限制外，还可以检查其他连接池配置项，例如最小空闲连接数、连接超时等。根据你的需求进行适当的配置。

  5. 查看数据库错误日志： 如果问题仍然存在，查看数据库服务器的错误日志，可能会提供更多关于连接超时的详细信息，以帮助诊断问题。

请注意，连接超时通常是由于数据库服务器资源不足或配置问题引起的。根据你的服务器环境和需求，可能需要调整连接池配置和数据库服务器配置来解决这个问题。

英文:

I am trying to make a very simple login webpage, no security or anything like that. My system right now consists of an OpenSuse server with a mariadb database and an Express server and an HTML file for the client.

Express server:

<!-- language: lang-js -->

  1. const mariadb = require(&#39;mariadb&#39;);
  2. const express = require(&#39;express&#39;);
  3. const session = require(&quot;express-session&quot;);
  4. const http = require(&#39;http&#39;);
  6. const app = express();
  8. app.use(session({
  9.   secret: &#39;secret&#39;,
  10.   resave: true,
  11.   saveUninitialized: true
  12. }));
  13. app.use(express.json());
  14. app.use(express.urlencoded({
  15.   extended: true
  16. }));
  17. const expresServer = http.createServer(app);
  19. var connection = mariadb.createPool({
  20.   host: &quot;localhost&quot;,
  21.   user: &quot;user&quot;,
  22.   password: &quot;pass&quot;,
  23.   database: &quot;users&quot;,
  24.   connectionLimit: 2
  25. });
  27. app.use(express.static(__dirname + &#39;/client&#39;))
  28. app.get(&quot;/&quot;, (req, res) =&gt; {
  29.   res.sendFile(__dirname + &#39;/client/login.html&#39;)
  30. })
  32. app.post(&#39;/auth&#39;, function(request, response) {
  33.   // Capture the input fields
  34.   let username = request.body.username;
  35.   let password = request.body.password;
  36.   // Ensure the input fields exists and are not empty
  37.   if (username &amp;&amp; password) {
  38.     // Execute SQL query that&#39;ll select the account from the database based on the specified username and password
  39.     connection.query(&#39;SELECT * FROM USERS WHERE User = ? AND Pass = ?&#39;, [username, password], function(error, results, fields) {
  40.       // If there is an issue with the query, output the error
  41.       if (error) throw error;
  42.       // If the account exists
  43.       if (results.length &gt; 0) {
  44.         // Authenticate the user
  45.         request.session.loggedin = true;
  46.         request.session.username = username;
  47.         // Redirect to home page
  48.         response.redirect(&#39;/main&#39;);
  49.       } else {
  50.         response.send(&#39;Incorrect Username and/or Password!&#39;);
  51.       }
  52.       response.end();
  53.     });
  54.   } else {
  55.     response.send(&#39;Please enter Username and Password!&#39;);
  56.     response.end();
  57.   }
  58. });
  60. app.get(&#39;/main&#39;, function(request, response) {
  61.   // If the user is loggedin
  62.   if (request.session.loggedin) {
  63.     // Output username
  64.     response.send(&#39;Welcome back, &#39; + request.session.username + &#39;!&#39;);
  65.   } else {
  66.     // Not logged in
  67.     response.send(&#39;Please login to view this page!&#39;);
  68.   }
  69.   response.end();
  70. });
  72. expresServer.listen(3000, () =&gt; {
  73.   console.log(&quot;Listening on 3000&quot;);
  74. })

HTML login:

<!-- language: lang-html -->

  1. &lt;!DOCTYPE html&gt;
  2. &lt;html lang=&quot;es&quot;&gt;
  4. &lt;head&gt;
  5.   &lt;meta charset=&quot;UTF-8&quot;&gt;
  6.   &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width,initial-scale=1&quot;&gt;
  7.   &lt;title&gt;UAV5G&lt;/title&gt;
  8.   &lt;link rel=&quot;shortcut icon&quot; href=&quot;/imgs/Logo.png&quot; /&gt;
  9.   &lt;link rel=&quot;stylesheet&quot; href=&quot;css/login.css&quot; media=&quot;screen&quot; /&gt;
  10.   &lt;link rel=&quot;stylesheet&quot; href=&quot;css/all.min.css&quot; /&gt;
  11. &lt;/head&gt;
  13. &lt;body&gt;
  14.   &lt;div class=&quot;elem-box&quot;&gt;
  15.     &lt;div class=&quot;login-box&quot;&gt;
  16.       &lt;h2&gt;Login&lt;/h2&gt;
  17.       &lt;form action=&quot;/auth&quot; method=&quot;post&quot;&gt;
  18.         &lt;div class=&quot;user-box&quot;&gt;
  19.           &lt;input type=&quot;text&quot; name=&quot;username&quot; id=&quot;username&quot; required&gt;
  20.           &lt;label for=&quot;username&quot;&gt;Username&lt;/label&gt;
  21.         &lt;/div&gt;
  22.         &lt;div class=&quot;user-box&quot;&gt;
  23.           &lt;input type=&quot;password&quot; name=&quot;password&quot; id=&quot;password&quot; required&gt;
  24.           &lt;label for=&quot;password&quot;&gt;Password&lt;/label&gt;
  25.         &lt;/div&gt;
  26.         &lt;input class=&quot;login&quot; type=&quot;submit&quot; value=&quot;Login&quot;&gt;
  27.       &lt;/form&gt;
  28.     &lt;/div&gt;
  29.     &lt;img src=&quot;/imgs/Logo.png&quot; class=&quot;logo&quot;&gt;
  30.   &lt;/div&gt;
  31. &lt;/body&gt;
  33. &lt;/html&gt;

I do not think CSS is needed.

The problem here is that the Express server throws this error:

  1. /home/node/Server/node_modules/mariadb/lib/misc/errors.js:61
  2.   return new SqlError(msg, sql, fatal, info, sqlState, errno, additionalStack, addHeader);
  3.          ^
  5. SqlError: (conn=-1, no: 45028, SQLState: HY000) retrieve connection from pool timeout after 10010ms
  6.     (pool connections: active=0 idle=0 limit=2)
  7.     at module.exports.createError (/home/node/Server/node_modules/mariadb/lib/misc/errors.js:61:10)
  8.     at Pool._requestTimeoutHandler (/home/node/Server/node_modules/mariadb/lib/pool.js:344:26)
  9.     at listOnTimeout (node:internal/timers:569:17)
  10.     at process.processTimers (node:internal/timers:512:7) {
  11.   sqlMessage: &#39;retrieve connection from pool timeout after 10010ms\n&#39; +
  12.     &#39;    (pool connections: active=0 idle=0 limit=2)&#39;,
  13.   sql: null,
  14.   fatal: false,
  15.   errno: 45028,
  16.   sqlState: &#39;HY000&#39;,
  17.   code: &#39;ER_GET_CONNECTION_TIMEOUT&#39;
  18. }

I do not know why, I can connect to the database from console using that username and password and adding connectTimeout: 10000 (or higher) does not help.

答案1

得分: 0

我认为在获取数据后，你必须释放连接。可能会导致你遇到所有的问题。

  1. connection.query('SELECT * FROM USERS WHERE User = ? ...', function (error, results, fields) {
  3. // 完成连接后，释放它。
  4. connection.release();
英文:

I think you must have to release the connection after you get your data. Might be causing all the troubles for you.

  1. connection.query(&#39;SELECT * FROM USERS WHERE User = ? ...&#39;, function (error, results, fields) {
  3. // When done with the connection, release it.
  4. connection.release();

答案2

得分: 0

使用mariadb连接器时出现错误：
有两种不同的实现方式：promise 和 callback。

  • promise：const mariadb = require('mariadb');
  • callback：const mariadb = require('mariadb/callback');

问题在于你使用了promise实现，然后调用了一个回调方法：

  1. connection.query('SELECT * FROM USERS WHERE User = ? AND Pass = ?', [username, password], function(error, results, fields) {

所以你要么将其改为promise，要么使用callback实现。

英文:

There is an error using mariadb connector:
there is 2 different implementation : promise and callback.

  • promise : const mariadb = require(&#39;mariadb&#39;);
  • callback : const mariadb = require(&#39;mariadb/callback&#39;);

problem here is that you use promise implementation then call a callback method:

  1. connection.query(&#39;SELECT * FROM USERS WHERE User = ? AND Pass = ?&#39;, [username, password], function(error, results, fields) {

so either you change that to promise, or use callback implementation

huangapple
  • 本文由 发表于 2023年6月26日 18:59:08
  • 转载请务必保留本文链接：https://go.coder-hub.com/76556062.html
  • express
  • html
  • mariadb
  • node.js
  • post
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定