Hey there! Let's work through your problems one by one—first the frustrating ECONNREFUSED error, then the SQL syntax and logic bugs in your code.

1. Resolving the ECONNREFUSED 127.0.0.1:3306 Error #

This error means your bot can't reach the MySQL server on your local machine. Here are the most common fixes:

• Verify MySQL is actually running
  You ran service mysql start, but double-check the service status with:

  sudo service mysql status
  

  If it's not active, restart it with sudo service mysql restart (the sudo might be needed on Raspberry Pi systems).

• Check if MySQL is listening on port 3306
  Use this command to see which ports MySQL is using:

  netstat -tulpn | grep 3306
  

  You should see a line like 0.0.0.0:3306 or 127.0.0.1:3306. If not, edit your MySQL config file (usually /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf) and make sure the bind-address is set to 127.0.0.1 (for local connections) or 0.0.0.0 (if you need remote access). Restart MySQL after changing this.

• Confirm your MySQL user has local access
  Log into the MySQL console:

  mysql -u root -p
  

  Then run this query to check user permissions:

  SELECT user, host FROM mysql.user;
  

  Make sure there's an entry for root with host set to localhost or % (wildcard). If not, grant access with:

  GRANT ALL PRIVILEGES ON PinkCraft.* TO 'root'@'localhost' IDENTIFIED BY 'your_password_here';
  FLUSH PRIVILEGES;
  

  Replace your_password_here with your actual root password.

• Double-check your config credentials
  Ensure the password in config.json matches your MySQL root password exactly—watch for typos, capitalization, and special characters.

2. Fixing SQL Syntax & Logic Errors in Your Code #

Your current SQL query has several issues (like inserting a Message object directly and SQL injection risks). Here's the revised code with explanations:

First, fix the typo in config.json #

Change ComumnAttachment to ColumnAttachment (you had a typo in the column name):

{
  "token":"bot token",
  "LogChannelId":"405382594645983233",
  "StartupMessage":"Bot has started",
  "host":"localhost",
  "user":"root",
  "password":"password",
  "database":"PinkCraft",
  "Table":"log",
  "ColumnName":"Name",
  "ColumnMessage":"Message",
  "ColumnId":"id",
  "ColumnAttachment":"attachment"
}

Revised index.js Code #

const Discord = require("discord.js");
const client = new Discord.Client();
const config = require("./config.json");
const mysql = require('mysql');

// Create MySQL connection
const con = mysql.createConnection({
  port: 3306, // Use number instead of string for port (more standard)
  host: config.host,
  user: config.user,
  password: config.password,
  database: config.database
});

// Handle connection errors gracefully
con.connect(err => {
  if (err) {
    console.error("Failed to connect to MySQL:", err);
    return; // Avoid crashing the bot on connection failure
  }
  console.log("Connected to MySQL successfully!");
});

console.log(config.StartupMessage);

client.on("message", message => {
  // Skip bot messages and non-target channels
  if (message.author.bot || message.channel.id !== config.LogChannelId) return;

  // Process attachments: extract URLs and store as JSON string
  const attachmentUrls = message.attachments.map(attachment => attachment.url);
  const attachmentData = JSON.stringify(attachmentUrls);

  // Use parameterized queries to avoid SQL injection and syntax errors
  const sql = `INSERT INTO ${config.Table} (${config.ColumnName}, ${config.ColumnMessage}, ${config.ColumnAttachment}) VALUES (?, ?, ?)`;
  const queryValues = [
    message.author.tag,
    message.content, // Use message.content instead of the whole Message object
    attachmentData
  ];

  con.query(sql, queryValues, (err, result) => {
    if (err) {
      console.error("Error inserting record:", err);
      return;
    }
    console.log("Successfully logged message to database!");
  });
});

client.login(config.token);

Key Changes Made: #

• Parameterized Queries: Using ? placeholders and a values array prevents SQL injection and fixes syntax errors from special characters in messages/usernames.
• Message Content Fix: Replaced message with message.content—the original code was trying to insert the entire Message object (which would store [object Object] instead of the actual message text).
• Attachment Handling: Converted attachments to a JSON string of URLs, making them usable in the database instead of inserting the raw AttachmentCollection object.
• Graceful Error Handling: Avoided throw err on MySQL connection failure so the bot doesn't crash immediately.
• Typo Fix: Matched the config's corrected ColumnAttachment name.

Once you fix the MySQL connection issue and update your code, your bot should start logging messages to the PinkCraft.log table correctly!

内容的提问来源于stack exchange，提问作者TheMole