How can I restore 1 db name "test" in "test_uat" from all database file in same windows MySQL server?

Version: mariadb Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (aarch64) using EditLine wrapper and mariadb Ver 15.1 Distrib 10.11.8-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper

I get this Error in a Procedure: SQL Error [1690] [22003]: (conn=1171) BIGINT UNSIGNED value is out of range in '`meme_boerse`.`BuyOrder`.`CoinsLeft` - transaction_coin_amount@13'

in this line:

UPDATE BuyOrder SET SharesLeft = SharesLeft - transaction\_share\_amount,  CoinsLeft = CoinsLeft - transaction\_coin\_amount,  CostThreshold = (CoinsLeft - transaction\_coin\_amount) / IF(SharesLeft - transaction\_share\_amount = 0,  1,  SharesLeft - transaction\_share\_amount) WHERE BuyOrderId = buy\_order\_id;

BuyOrder.CoinsLeft is 100 and transaction_coin_amount gets calculated like this:

SET transaction_coin_amount = CEIL((sell_coins_left / sell_shares_left) * transaction_share_amount);

with sell_coins_left = 100, sell_shares_left = 100 and Transaction_share_amount = 100, which should result in 100 for transaction_coin_amount.

All Data is stored as unsigned int.

Simple Visualisation:

Table BuyOrder:

sell_coins_left, sell_shares_left, transaction_share_amount = 100 unsigned int  SET transaction_coin_amount = CEIL((sell_coins_left / sell_shares_left) * transaction_share_amount);

(should be 100 unsigned int)

Error in this Line:

UPDATE BuyOrder SET CoinsLeft = CoinsLeft - transaction_coin_amount WHERE BuyOrderId = buy_order_id;

The error doesnt make sense, because the calculation should be 100-100 which would return 0 which is in range of unsigned int.

If I change the datatype of all variables and columns to int and do the procedure it works with BuyOrder.CoinsLeft beeing 0 at the end.

Is there a reason this isnt working?

Hey newbie here, I wanted a db on my arch instalation and found out that MySQL is not used on arch but instead mariadb .

Now when am trying to connect to the db using c++ (using the soci) I get "MySQL.h not found" , my assumption was that mariadb replaces everything related to MySQL with itself ....

What I want : how would I fix this ? Or can I use another library that allows me to use mariadb ?

Thanks !!

I'm pretty new to databases, but I am using one in a small-scale personal project. Right now I've been importing and exporting to Excel to make changes to the database, but there has to be a better way, right? Without me having to create a whole interface from scratch with PHP or something?

Hello, so I have a main database called X and I have data that I need to replicate it into Y database, what would be the best way to do this, CDC ? Im talking about thousands or records in each table and around 15 tables in DB X, currently I have a trigger in X that says what changed and Y scans every 20min but its moving very slow.

Hey there, so I would like to transfer around 3 to 4 TB of mysql data from my personal server to a cloud mysql server. I cannot create backups as I am lacking harddrive space.

I tried looking for syncronization tools but for a sideproject.. paying 200$ is not really something I would like to do..

I asked chatgpt which usually asked me to create a backup of some form or go with tools which might die during the transfer process which would then result in starting over.

Do you guys have any suggestions?

I’ve been working with MySQL using Workbench and DbSchema, and I ran into something interesting with virtual foreign keys.

Since MySQL doesn’t always enforce FK constraints (like with MyISAM or when using external tools), I’ve been using DbSchema’s virtual FKs to keep things organized and visualize relationships better.

Has anyone else tried this approach? How do you manage relationships when the database itself doesn’t enforce them?

I am getting this error ERROR - Error signing in the user: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))"). this error comes when I do login and when I restart the server it works fine for few hours but after some time my program again got stopped and showed this error. how can I solve this. I am using this code

logging.basicConfig(

level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s"

)

logger = logging.getLogger(__name__)

DATABASE_CONFIG = {

"host": "*******omh.ap-south-1.rds.amazonaws.com",

"user": "admin",

"password": "*****",

"db": "*****_development",

}

def get_conn():

return pymysql.Connection(

host=DATABASE_CONFIG["host"],

user=DATABASE_CONFIG["user"],

passwd=DATABASE_CONFIG["password"],

db=DATABASE_CONFIG["db"],

)

Hi guys,

I'm using mysql 8, I have a table (InfoDetailsTable) which has 10 columns in it and has a PK (InfoDetailID - Unique ID column) in it and a FK (InfoID -> FK to InfoTable)

So, for an InfoID, there are 2 lakh rows in InfoDetailsTable.
For a process, I'm fetching 5000 in each page.

while (true)
{
// code

String sql = "select * from InfoDetailsTable where InfoID = {0} limit 0, 5000"
// assume limit and offset will be updated in every iteration.

// code

}

See in my query I don't have order by. I don't need to order the data.
But Since I'm using limit, should i use order by PK mandatorily? (order by InfoDetailID)
If I don't order by PK, is there any chance of getting duplicate rows in successive iterations.

Indexes:
InfoDetailID is primary key of InfoDetailsTable, hence it is indexed.
InfoID is FK to InfoTable and it is as well indexed.

Any help is appreciated. Thanks.

Just downloaded MYSQL and when I tried starting it up it gives me an error.

21kelvinca@Kelvins-MacBook-Air-2 ~ % mysql -u root -p

dyld[16484]: Library not loaded: u/loader_path/../lib/libssl.3.dylib

  Referenced from: <B6C862D9-100F-3CCC-8048-EE5138B81A60> /usr/local/bin/mysql

  Reason: tried: '/usr/local/bin/../lib/libssl.3.dylib' (no such file)

zsh: abort      mysql -u root -p

Does anyone have any solution to this? Would be very helpful :)

Hello everybody,

I'm quite new to all this, so I apologize if there's something I'm missing. Either way I have a database set up on a local PC, and I'm using the MySQL workbench to work on it, and I want to allow remote connections so that I can connect from external source. How can I go about doing that?

My client wants a new Next.js app for their newspaper site, built from scratch with a fresh database, separate from the previously used WordPress database. I exported the existing data as an SQL file, but I’m overwhelmed by the 100,000+ lines of SQL and unsure how to migrate the data into the new database connected to my Next.js app. Any help ?

Hello, first post here so if something is in the wrong place or way, do what you need to do.

I have multiple DB with multiple tables, most of them with collation latin1_swedish_ci. We had problems with that one, so we started changing some of the tables to utf8mb4_unicode_ci.

Is there a way to do it all at once? Or even Database to database? Anything but table to table, as there are more than 25000 tables to change

Also another question, will changing from latin1_swedish_ci collation to utf8mb4_unicode_ci collation lose any data? I understand is safe to do it but asking doesn't hurt

So I have a java application with about 80 runtime servers which are connecting to MySQL and bombarding it with queries, our MySQL instance has 250GB RAM and 80 threads.

Most of the data we store in MySQL is XML and our queries are mostly READ queries, we are doing about ~240 million queries on average day.

I found that some of the business processes are taking slower due to MySQL performance and I'd like to start optimizing it.

While I cannot replicate production environment traffic in lab I still experimented a bit with mysqlslap and tried changing some configurations with no much success.

I sometimes use MySQL Server Management Studio to extract data from our servers. I have some columns with time data in the format of seconds. I want to convert that to hh:mm:ss. In excel i would easily just use time(hh:mm:ss) like this: time(0;0;ss) where ss is the data in seconds. Ive read that "SEC_TO_TIME()" should work, but MySQL says that its not a built in function. How would i do this the easiest?

I have a mariadb database running in a docker container. The data originates from a live db in a forensic disk image. (Export datadir, mount it under /var/lib/mysql/, engage)

It's all up and working but I can't figure out why one of the tables contains no records, even though when viewing the strings in the table's ibd, I see the data I expect the records to contain.

My under the hood knowledge is extremely limited, is there anyone who might give me some insight why I can see this and how to possibly access it in the table.

Can I use strict_all_tables with strict_trans_tables for sql_mode in MySQL 8.4.3 ?

TL;DR:

• Running MySQL Ver 8.4.3 for Linux on x86_64 (Source distribution).
• Both MySQL and MongoDB store data on a single 1TB NVMe drive.
• Data persists fine with MongoDB, but MySQL mysteriously loses data.
• 15K entries for a customer drop to 9K entries after a daily ETL sync.
• No DELETE, ROLLBACK, or DROP commands appear in MySQL general or binary logs.
• 7 microservices and 2 servers make simultaneous read/write requests to the same database.
• Clarification: the issue is not with data being saved to the database, but existing data within the database disappears without a DELETE command.

---

Details

At first, I thought it was a bug in my application code or ORM library, but after enabling general and binary logging, I confirmed:

• No DELETE, ROLLBACK, or DROP operations are being issued by the application.

I was previously running MySQL 9.1.0 (preview), so I suspected instability. I downgraded to 8.4.3, but the issue persists.

Side Note: Since 9.1.0 and 8.4.3 are not cross-compatible, I backed up and restored the database as follows:

# backup
mysqldump -u <username> -p <database> > /mnt/raid/mysql_backup.sql

# cleanup (w/9.1.0 -> 8.4.3)
rm -rf /usr/local/mysql/data/*
mysqld --initialize --user=mysql

# restore
mysql -u <username> -p <database> < /mnt/raid/mysql_backup.sql

I enabled the general log to monitor all queries to confirm no apps where deleting the data during a sync:

SET GLOBAL general_log = 'ON';

I also checked the bin-logs (/usr/local/mysql/data/binlogs.xxx)

Symptoms:

1. When running a manual sync for a single customer, approximately 99% of the data is successfully saved (e.g., 14,960 / 15,100 entries) and queryable.
2. After the daily ETL sync, only about 50% of the data remains in the database.

What I’m Considering Next:

1. Storage Issue: I’m considering moving the MySQL database to a RAID10 disk setup to rule out potential drive failures.
2. Database Bug: I’m contemplating swapping MySQL for a compatible equivalent to test whether this is a deep-rooted issue with MySQL itself.
3. Other Ideas?: I’m at a loss for other possible causes and would appreciate advice.

Hello everyone I am not able to get the date in the required format the code is:

select order_id, order_date, format (order_date, 'yyyyMMdd') as ddd from Parks_and_Recreation.orders ;

The output which I am getting as ddd is 20,160,418

Kindly help I am very new to MySQL.

how i filter computed and non computed columns

I am unsure how to do this
I have a situation for SQL for tables

some columns are computed like counts ( task_count for example calculated by subquery) and some are noncomputed ( regular columns like id, name )
when filtering those columns
I should use HAVING for computed ones and where for noncomputed ones
afaik.

if I used HAVING for all columns it works but when doing the same query again without sort and pagination the total does not match the correct rows.

using where and having together give me unexpected empty results.
like this

AND (id LIKE ? OR name LIKE ? OR created_at LIKE ? )  HAVING group_count LIKE ? OR list_count LIKE ? OR task_count LIKE ?

sql_found_rows is great to get the count but is also not recommended.

whole query is here for clarification

https://ibb.co/dWTV6WV

Help appreciated

I have a recipe app i'm working on that suggests ingredients based on what other ingredients you've already chosen.

So, I have one table with a list of `ingredients`.

Then I have an "ingredient_pairings" table with rows of ingredients that match with each other. The columns are `ingredient1_id` and `ingredient2_id`

So, if you have 3 `ingredients` in your recipe, I need to query the `ingredient_pairings` table to find common matches with all of the ingredients.

The pairs are unique - there is no row where the combo of ingredients are the same (i.e. ingredient 1 and ingredient 2 are always a different pair)

So if the apple ID, carrot ID, and pineapple ID all have a common match, I want to return those matches.

How can I query where that condition is true?

I am mainly a developer and not an expert in MySQL fine-tuning. I have a MySQL 5.6 server under Oracle Linux 8 with about 30 databases, each database containing ~1000 tables.

This server uses a lot of RAM and I'm trying to understand why (how is this usage broken down?) and especially how to reduce it to ensure that it never uses more than what is available on the system.

If you are knowledgeable on the subject, can you take a look at this status data and variables corresponding to a usage of ~32GB of RAM after about 2 hours of operation and tell me if this RAM usage seems logical and explainable to you and how to reduce it?

SHOW GLOBAL STATUS, SHOW VARIABLES and SHOW ENGINE INNODB STATUS results here : https://pastebin.com/DFT9ncmT

Thanks !

I’m still learning, but due to my disability numbers, and letters can be difficult for me to remember.

I am much better at understanding things like ui, but I’m under the impression that to get the advance feature I will need in the future. I will need to still code a bit.

Hi,

EDIT: It is an IF function, not a statement. Sorry for that.

I have a table with a json column. The user defines a sort field from the JSON object and I need to dynamically build the SQL Query based on that (ofc it uses parameters).

The problem is, that I don't know whether the field contains a string, a number or something else.

If the field is a number it should sort by numeric order. And I have issues with that.

My idea was to use something like that:

SELECT
    *
FROM `TestEntity`
ORDER BY
    IF(
            JSON_TYPE(JSON_VALUE(`Json`, '$.Number')) IN ('INTEGER', 'DOUBLE', 'DECIMAL'), 
            JSON_VALUE(`Json`, '$.Number' RETURNING DECIMAL), 
            JSON_VALUE(`Json`, '$.Number')
    ) DESC;

The weird thing: It does not work for me. But if I replace the ELSE with a constant or just a field name it works fine:

SELECT
    *
FROM `TestEntity`
ORDER BY
    IF(
            JSON_TYPE(JSON_VALUE(`Json`, '$.Number')) IN ('INTEGER', 'DOUBLE', 'DECIMAL'),
            JSON_VALUE(`Json`, '$.Number' RETURNING DECIMAL),
            99
    ) DESC;

The same heppens when Iused nested queries:

SELECT * 
FROM (
    SELECT *, JSON_VALUE(`Json`, '$.Number') as pp0 FROM TestEntity) AS x
ORDER BY
    IF (JSON_TYPE(x.pp0) IN ('INTEGER', 'DOUBLE', 'DECIMAL'), CAST(x.pp0 as DECIMAL), x.pp0) DESC

It seems that it has something to do with the IF statement, which I do not unserstand yet.

EDIT:

I have found the reason, but unfortunately I cannot find the docs anymore. The true_value and the false_value need to have the same type. This is also true for CASE Statement.

So the only solution is to convert the numbers to sortable strings, e.g. with

LPAD(FORMAT(JSON_VALUE(`Json`, '$.Number'), 6), 20, '0')

To give some background: I have some experience with Python but I’m a complete newbie with SQL. I’m using MacOS and I had a previous MySQL version downloaded. I tried to use it for the first time and it worked fine when I was making a database and a table. However, I ran into trouble when I tried to write a table into an outfile. I eventually realized the problem was that my MySQL download didn’t come with a my.cnf file. When I tried looking up how to download or make one though, none of the tutorials made any sense—they kept mentioning files and directories that just didn’t exist in my download.

So, I downloaded a newer version in the hopes that starting from scratch would help me find things easier. However, everything in the new version looks the exact same as the old version. How do I finally get a working my.cnf file so i can make my outfile??

Been thinking of biggest problems as a data analyst and thought I'd write a story up about it to make it more entertaining for others to read! (it's a little cringe, I know)

Please add ur own thoughts and post/dm me on whether any of these resonate with you, trying to learn more about how others experience these to figure out how to solve some of them!

8:30 AM

You sit down at your desk, armed with a coffee and just enough optimism to get through the day. First task: load the customer dataset into MySQL. Easy, right? Wrong. The LOAD DATA INFILE query immediately spits out:

Error Code: 1292. Incorrect date value: '12/31/2022' for column 'date' at row 1

Great. The dataset has dates in three different formats, random “N/A” entries, and customer names that look like they were typed by someone on their fifth drink. After spending the morning wrangling this mess into something MySQL will tolerate, you finally have clean data. It’s not glamorous, but at least now it won’t blow up your queries.

10:30 AM

With the data finally sorted, you start analyzing. Your manager’s instructions? “Analyze customer behavior and let me know what you find.” That’s it. No specifics, no context. You decide to focus on churn rates, figuring it’s a safe bet. It’s fine, but you’re still second-guessing yourself. Should you be looking at spending patterns? Maybe demographic insights? You save the results and move on, hoping your manager will magically confirm your instincts during the next check-in.

12:30 PM

After lunch, marketing hits you up with a new request: they want campaign insights with all the bells and whistles—filters for regions, product categories, and date ranges. You try to tweak your earlier queries, but things quickly spiral into chaos. It works, technically, but they keep asking for more tweaks. “Can we add weekly breakdowns? Can we exclude specific categories?” By the time you’re done, it’s clear your SQL skills aren’t the problem—figuring out exactly what they want is.

3:00 PM

You throw together some charts in Excel showing retention trends and campaign data, thinking they look pretty solid. When you show your manager, though, they just stare at you.

“Okay, but what’s the takeaway?”

And there it is—you’ve got clean data, accurate numbers, and a bunch of graphs, but no real story. Your analysis isn’t actionable, and it shows. You scramble to add some quick suggestions, but you know you’re fumbling. Turns out the data can’t speak for itself unless you tell it what to say.

5:00 PM

The marketing team asks for regional sales data, which should be simple because you’ve already cleaned that part of the dataset. Should be. But when you run your query, the totals look way off. The numbers are suspiciously high. After a frustrating debugging session, you find the problem: duplicate rows caused by bad test data. Classic. You fix it, but it’s a painful reminder that data cleaning is never really over.

6:15 PM

Just as you’re shutting down for the day, your manager drops by with a parting gift:

“We’ll need a churn analysis for tomorrow morning’s meeting.”

You suppress the urge to scream. You already did a basic churn analysis earlier, but it’s nowhere near polished, and now you’ll need to stay late refining it. Still, at least you’ve learned one thing today: bad data and unclear goals are the gift that keeps on giving.

You fire up your laptop again, muttering to yourself, “Maybe tomorrow I’ll finally have a day where everything just works.” But deep down, you know better.

Hello all.

The problem has been solved, but I don't quite know what happened, maybe if someone knows just so that I can learn from that. It happened some time ago.

I have a database on a dedicated server with WHM/Cpanel, MySQL 5.7. I attempted to upgrade to MySQL 8 and the upgrade failed. I know now that I should have backed up the database with mysqldump before that, but well I didn't, I believed that keeping a copy of the data directory was enough. After the failure, I reinstalled MySQL 5.7 and tried to move the data directory back, but MySQL then failed to start. I believe that the ibdata1 file was corrupted.

I tried to recreate the database using mysqlfrm to recreate the tables, discarding the tablespaces and reimporting them using the saved .ibd files. For the most part it worked, but on some tables, especialy those that had dynamic row formats, MySQL crashed during the import, and when it didn't crash the data was kind of broken - when there were null columns, the content of one column was moved to the adjacent column, giving me totally unexpected values when the columns were of different datatypes. I tried many combinations of server encoding, innodb_force_recovery, dropping indexes before the import, dropping columns before the import until I finally could import those tablespaces without crashing MySQL. Then table by table I was able to recreate the database.

Why would the content of some columns be moved to the adjacent columns, and why changing the server encoding and dropping indexes before the import worked'? Asking so that I can better educate myself on how MySQL works.

Thanks in advance.

I had mysql community server installed , but I forgot it's password on my fedora machine, Then I un-installed and re-installed it. But I am not able to set its password on doing below sudo mysql_secure_installation And it is giving me error of access denied . I am following the fedora docs for download. I tried to reset the password but was not able to do it.

Can anyone suggest me ? How do I resolve it. Thanks in advance

For past year my company keeps track of rentability in different places in a Excel book.

The problem is that i am the one who has to make this book every month inserting the data manually, wich leads to error lot of times. With the data that has been updated and also the formulas used that migth change due to human error.

The data that these files have is "userid", "username", "price they pay", "name of service", nothing overcomplicated

The data that i input in this Excel file comes from different sources:
·CSV file.
·TXT that has one entry in each line. The data comes from connecting a device via SSH and then redirect the output to the file.

After i input this data by manually copy/paste in the new file i have to drag the formula. then make sure everything is ok. This usually takes me two to three days.

I thought that maybe it's possible to automate all this and make it more "error proof", where i simply import the files to the DB and then call it a day, those who want to see it just enter the PHP page and if they want download the file, but be sure that there is no chance to fail from month to month.

It is a good idea to make this or i'm just overthinking it?