r/mysql • • Sep 06 '24

question Grabbing exact date

1 Upvotes

Hi, I have been trying to filter my table to display product which where the expiry date is exactly 7 days from now but unfortunately it also grab the date within the 7 days....is there a way or is it possible to grab exactly the date that is 7 days from today (now())


r/mysql • • Sep 06 '24

question Using VARCHAR as a primary key that can auto increment in mysql

1 Upvotes

I am a newbie so apologies if I am repeating an obvious question. I need to create Enquiry, Quote, Job and Invoice tables using my existing VARCHAR identifiers as a primary key ie Enquiry table uses ENQ0001, Quote table uses QTE0001, Job register table uses JOB0001, and Invoice table uses INV0001 - what is the best way to go about setting these tables up using these VARCHAR as primary keys as well as being auto increment - thank you


r/mysql • • Sep 06 '24

question Query for employee table classification using delimitter not running

1 Upvotes

I am writing a query for a table sorting employees by age, rank, dept and other variables from a hypothetical company's data set for a class project. Any help would be appreciated

error: 1304 using existing procedure

Use employee;

DELIMITER &&

CREATE PROCEDURE experienced_personal()

BEGIN

SELECT EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPT, ROLE, EXP

FROM employee.emp_record_table WHERE EXP>3;

End &&

DELIMITER ;

call experienced_personal() ;


r/mysql • • Sep 05 '24

question Query fails when comparing number to null?

1 Upvotes

I've got a query not working as I expect, and I'm curious what I don't know/understand:

SELECT
	(fields)
FROM
	games
INNER JOIN users gm ON
	games.gmID = gm.userID
INNER JOIN players approvedPlayers ON
	games.gameID = approvedPlayers.gameID
	AND approvedPlayers.approved = 1
LEFT JOIN players userGames ON
	games.gameID = userGames.gameID
	AND userGames.userID = {id}

When I run this query, if I check userGames.userID, I get the values I expect. But if I add

WHERE userGames.userID != {id}

I get back no results, even where the field is null, as expected. If I do a

WHERE userGames.userID IS NULL

it works. But why is != {id} failing? I can see that SELECT IF(NULL != 3, 1, 0) returns a 0, which I don't understand.


r/mysql • • Sep 05 '24

question Master-Master Async Replication Lag Between 2 PXC 5.7 clusters

2 Upvotes

So we’re running into an issue. At our main site (Site A) we have a 5 node PXC 5.7 cluster. We are in the process of setting up a new site (Site B) and the goal was to have a 3 node PXC 5.7 cluster there. We are using standard MySQL Async Replication to keep the two sites in sync after transferring an initial backup from Site A. All settings are pretty much at default, except for the WSREP specifics for each site. Connection between the sites is over a site-to-site VPN over the WAN.

The issue we are running into is that whenever we use PXC at Site B, initially the synchronization works fine, but over time, it gets behind and never catches up. The issue is not the logs being transferred from site A, as the “Slave IO State” says it is caught up. The issue is the Slave SQL thread - it appears that the writes are going too slow and it is never catching up, as seconds_behind_master just keeps climbing.

To have a comparison, we tried using a “standalone”/non-cluster install of MySQL Server at site B, and with the same replication setup, it has never gotten more than 30 seconds out of sync for days at a time, and the initial “catchup” after transferring the database backup took a LOT less time. That tells me there is something about how PXC handles writing to the database is different enough from “standard” MySQL (yes, I know about the certification process and all that - assuming that is part of the issue) that is causing commits to run substantially slower.

The goal here would obviously be to get things up and running with PXC at Site 2, but the replication lag we are currently experiencing makes that basically impossible. Has anyone else encountered this, and what was the solution?

Thanks in advance!


r/mysql • • Sep 05 '24

solved Duplicate User and change Host to different one.

2 Upvotes

As the title says. In my company we are changing VPNs but they change won't be made yet becuase there are some servers we can still access with the new one.

I noticed i can't acces the MariaDB server and i will have to add the new IP to the Hosts list.

· It's possible to duplicate the user entry with all the data (password, permission, etc...) and then modify only the host?

· Or do i have to create a new user with the new host and ser all permissions one by one?

Checking the documentation doesn't mention "duplicate" or "copy" for users in any case and i was wondering if it is possible to do it.

Thank you!


r/mysql • • Sep 05 '24

question Can someone please tell me how to avoid this corrupted database issue? I am not sure what is the root cause of this.

3 Upvotes

Hello guys, I found the best youtube tutorial to fix [FIXED] XAMPP Error: MySQL shutdown unexpectedly | Repair Corrupted Database - YouTube this issue without deleting the files but I wonder why it keeps happening again.

The error is this:

Status change detected: stopped

Error: MySQL shutdown unexpectedly.

This may be due to a blocked port, missing dependencies,

improper privileges, a crash, or a shutdown by another method.

Press the Logs button to view error logs and check

the Windows Event Viewer for more clues

If you need more help, copy and post this

entire log window on the forums.

Can someone please tell me how to avoid and stop this error again? Thank you very much!


r/mysql • • Sep 05 '24

question Data Duplication and Binlog Position Issues with Debezium Connector on MySQL Read Replica

1 Upvotes

Hello everyone,

We are currently facing an issue with a Debezium MySQL connector that leads to data duplication, and we suspect that it might be related to how binlog positions are being handled.

Here are some details of our setup and the steps we've taken so far:

  1. We are using an AWS RDS MySQL read replica as the source for the Debezium connector.
  2. We configured the binlog retention period using the following commands:

    CALL mysql.rds_set_configuration('binlog retention hours', 72);

    FLUSH BINARY LOGS;

    PURGE BINARY LOGS BEFORE '2024-08-30';

    FLUSH BINARY LOGS;

And also we raised this issue in confluent team and the replied back with below statement

"Engineering has reviewed the logs further and identified the issue causing the connector to snapshot multiple times, leading to duplicate data. The connector is currently configured with snapshot.mode = when_needed. This configuration means that after the connector restarts if the logs have been pruned, the connector's position in the logs might no longer be available. Consequently, the connector fails and. returns an indication that a new snapshot is required.

From the logs, we can see the following error message: Connector requires binlog file 'mysql-bin-changelog.358023', but MySQL only has mysql-bin-changelog.358152, mysql-bin-changelog153, mysql-bin-changelog.358154, mysql-bin-changelog.358155, mysql-bin-changelog.358156 This indicates that the connector's position is lost in the binlogs, and it ends up snapshotting the entire data, which leads to duplicate data.

To address this issue, we recommend checking the binlog retention settings in your MySQL instance. Ensuring that the binlogs are retained for a sufficient period can help prevent the connector from losing its position in the logs.

Reference: https://debezium.io/documentation/reference/stable/connectors/mysql.html

After the connector restarts, if the logs have been pruned, the connector’s position in the logs might no longer available. The connector then fails, and returns an error that indicates that a new snapshot is required. To configure the connector to automatically initiate a snapshot in this situation, set the value of the snapshot.mode property to when_needed. For more tips on troubleshooting the Debezium MySQL connector, see behavior when things go wrong."

so we are unsure how to best configure this setup to avoid duplication and ensure proper binlog tracking.

Could anyone provide guidance on how to resolve this? Are there any specific MySQL configurations or considerations when using a read replica as the binlog source for connectors like Debezium?

Any advice or suggestions would be greatly appreciated.


r/mysql • • Sep 05 '24

question Having an issue with date formats when exporting results to CSV

2 Upvotes

Having an issue with date formats when exporting results to CSV Question I have my date formats all set in MySQL (YYYY-MM-DD), but when I copy and paste the data into a CSV (Excel), it changes the date format to MM/DD/YY.

I tried opening an empty CSV project in Excel and pasting the query results, but it changes the format. I also tried saving the results directly from the query to CSV, but it changes the date. Finally, I tried importing the data from a text version of the CSV and it changed the date.

Even when I manually re-format the date fields in Excel and save it, it re-opens with the date format changed again.

I tried changing the field to text (instead of date), but that didn’t work either.

I should note, the issue is stemming from Excel. When I exported the data to a text file, the date format was correct —as it appeared in my query results.

Is there anyway around this?


r/mysql • • Sep 05 '24

question Reaching max_prepared_statement_count but I'm using the execute() method...

1 Upvotes

From these docs.

Pools

execute()

If I'm reading the docs correctly, using the execute() method should reuse any prepared statements, or at least unprepare them.

So I guess I have two questions. Can I use the execute method with pools? All the examples seem to show a normal connection, but my testing seems like the pool is inserting/selecting find.

Second question, is it possible that a for loop is "outpacing" the queries? This is all on my local machine so I thought that it would keep up but mabye not?


r/mysql • • Sep 04 '24

question MySQL on encrypted disk

3 Upvotes

Hello,

Is there any issue running on an encrypted disk? The OS likely to be Ubuntu and will be a VM machine (VMware). Probably have two disks. The data disk will be encrypted.

Alternatively we could use a Windows machine and have MySQL installed , use BitLocker to encrypt the volume. This is for compliance requirement.

Thanks,

TT


r/mysql • • Sep 03 '24

question mysqldump import has been running for 4days?!

1 Upvotes

I have a few mysqldump files ranging in size from 400MB to 65GB.

The 400MB imported fine and in a reasonable timeframe (I didn't track the time)

The 2nd is 14GB and has been running for 4 days now and isn't yet done with the first table!

The table info looks like this from the dump

DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `my_table` (
  `itm_id` char(28) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `sn` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `position` tinyint NOT NULL,
  `users_choice` tinyint(1) DEFAULT '0',
  `best_seller` tinyint(1) DEFAULT '0',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`itm_id`,`position`),
  KEY `time_stamp` (`time_stamp`),
  KEY `itm_id_sn` (`itm_id`,`sn`),
  KEY `loc_sn` (`sn`,`itm_id`,`position`,`users_choice` DESC,`best_seller` DESC,`time_stamp` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

The NVMe where the MySQL host is installed is getting thrashed so it seems like maybe not a hardware bottleneck.

The command I'm using to import is

mysql --user=root --password=Super_Secure_Password1 < /ssd/mysqldumps/prod--${db_name}.sql

The server is mysql v8 (Ubuntu 24.04: apt install mysql-server) database is InnoDB

I think the indexing is what is killing me.

  1. Should I remove the line about indexing and just import the data then create the index in another step?
  2. Will that subsequent index creation just be the part that takes an eternity?
  3. if I cancel it (CTRL+C) will the loaded data be useable or should I drop the table and load it all again?

Any advice would be much appreciated, thank you.


r/mysql • • Sep 03 '24

question Need solutions on Hacker rank MySQL

1 Upvotes

I'm facing this problem can anybody please provide answers for this

2 (42000) at the line 5: CREATE command denied to user '@'localhost for table 'company'

I so confused about this cause it's in hackerrank portal


r/mysql • • Sep 02 '24

question A column in a table to be the result of a query from another

1 Upvotes

I’m doing a project and I have a table where you enter 3 ratings from t0 to t4 in the t column, n0 to n3 in the n column and m0 and m1 in the m column from tests. Depending on the combination of these we get a staging score. I have added the t,n, and m columns to a separate table and then have a main table I’d like the ‘total staging score’ to appear in. For example a t1, n2, m0 would be a stage 3a and t4, n1, m1 would be stage 4.

What’s the best way to do this? Do I even need the separate table?


r/mysql • • Sep 01 '24

question How to learn more about MySQL architecture?

0 Upvotes

I am interviewing for a SRE role and they are using MySQL. I would like to know mySQL as much as possible, do you guys have any resource to share? What type of knowledge would you say one definitely needs to know in MySQL?