So I'm new to SQL in general and as a way to learn more about it, I'm planning on making a personal finance database to track where my money goes. The way its gonna go is that I have a starting value corresponding to the current money I have in a bank, and then I have different transaction types (purchased, deposited, withdrew, etc.) that will either add or subtract to that value depending on the type of transaction.

The first problem I've encountered is with regards to setting the starting value of my financial records. Do I just make a column where I set the first row as that starting value, then apply all my calculations on that column (e.g. subtract XXXX to the previous value)? Or is there another way to do this?

Another problem I have is that I have more than 3 banks from which I will do my transactions. How am I going to set it so the transactions will only apply to the bank I transacted with? Should I have 3 different columns that represent the current amount I have in each bank?

Thank you for your help.

Hey,

im running a Docker MySQL Server that contains data for a FileRun docker.
My MySQL docker and the files got deteted but I have an old Backup of the Database folder, that I want to restore if possible.

Sadly I don't have much knowledge with SQL since it all worked fairly straight out of the box.

I already tried creating a new database with the same same etc. and copying the files into the Database folder. If I restart the MySQL Docker and use adminer to look into the Database, it shows 0 Tables.

What's the best way to recover or is there one at all?
Creating a new table in adminer always seems to end with errors though.

Thank you guys a lot in advance!

Hello !

Does anyone know if it is possible to split a large .sql file into several ones, to make it easier to maintain ?

For instance, a lot of stored procedures are written in the same .sql file at the moment.

And it becomes weird to update or add something, even with good comments and sections.

The SOURCE keyword does not seem to work inside a sql file or a stored procedure.

The LOAD FILE keywords seems to load data from files, not script.

It needs to run on mysql server 5.7 , is it even possible or is this a limit of this scripting language ?

Thanks for your insights !

i installed mysql but with doing python i had an error with connectivity and bymistake chnaged the password of the module. now i cant open mysql without root password and cant download some features too. wht do i do?

Hey y'all! I'm working on a project where I'm taking data from this site: https://download.bls.gov/pub/time.series/ap/ The issue is that I don't know how to properly import the tables in a usable format. If anyone could help guide or has a good resource I'd be incredibly grateful.

I've installed XAMPP on a Windows 10 machine. When I run CMD in administrator mode, migrate to the mysql/bin directory, and type in 'mysql', I get the following message.

ERROR 1045 (28000): Access denied for user 'allen'@'localhost' (using password: NO)

I'd like to be able to execute mysql commands directly, and from scripts, all without using the XAMPP control panel. How do I get started?

I appreciate the need for security once I put my website online, but for now, the security features are just in my way.

Hello! I am trying to run 2 commands on MySQL on a Ubuntu VM and I keep getting errors when trying to edit or add users names. I looked up to see what the error was and an article said something about removing the quotes at the top where the password is but that didn't help. I just installed the latest version of MySQL as well just an FYI. Any help or ideas are greatly appreciated. Thank you!

*Manually typing the command in question as I dont seem to be able to attach a helpful screenshot for some reason.*

What I'm typing:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

GRANT ALL ON WordPressDB.* TO 'username'@'localhost' IDENTIFIED BY 'password’;

What I'm getting: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MYSQL server version for the right syntax to use near 'password' at line 2

Guys I was able to finally set up Mysql on my Mac M2, after hours and hours of fighting trying to make it work with the “not connection to the server establish” problem with the last version 8.4.0; All I had to do was to install instead the 8.0.37. I installed this version and re-installed again Mysql Workbench and everything started to work as it supposed to, no issues at all.

Hope this helps some of the people experiencing the same issue I had.

I've been using MySQL workbench for years to connect to an AWS RDS server and suddenly today it won't connect saying can't connect to local host. I have checked that my IP hasn't changed and all my security setting are still in place. The website the database feeds is working just fine. Any ideas?

Update: Issue was the writer had its setting change to private from public. I'm not sure why this changed but after changing back its working.

Hello guys,

I have .sql file backup and i want to restore it.. when i try to execute the command mysql -h ..... < mysql.sql It just gives me an error with access denied on line 18. Eventhough i executed everything as root

I have checked for DEFINER clause but there are none.

And my user has all the appropriate rights.

HI I have made a script to pull all my part numbers what state they are in and the date/time they were created, I can not find out for the life of me how to show how many were made per hour so I would like to set a date range and it return for every hour in that date range how many parts were created

I am starting this SQL class online at college and I got to the module where I do have to install mysql.

So I have a Mac OS M2, I followed the steps, downloaded the MySQL also the workbench, and when I go to System preferences and click MySQL it shows the red dots as inactive, I don’t know why they are not green, I tried doing stuff on the terminal and nothing. I am not able to do anything on MySQLworkbench because it says, “Connection not established” something like that

Do you guys know how ti fix this so I can finally do my assignments:) ?

Thanks by the way!

I am brand new to MySQL and programming as a whole and tried downloading MySQL to follow along on a Youtube tutorial. The download was successful, I got the Workbench file and the Community file, however when I tried creating a server connection and testing the connection I get a message saying "Failed to Connect to MySQL at 127.0.0.1:3306 with user root". I ensured my password, hostname, port, etc. were all input correctly. I've also gone into my settings and tried initializing the database and starting the server, however the red light under "Active Instance" and "Installed Instances" flashes green for a second then turns back to red. I've also tried re-installing all files and am still getting the same result. Please help!

Hello all, I have a kubernetes pod running version 8.3.0, due to an error, the Mysql version was updated to 8.4.0, which however we don't want for now.

I have rolled-back to the the 8.3.0 container version, but still the database is not starting:

2024-05-09T09:06:11.441067Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-05-09T09:06:11.688609Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-05-09T09:06:11.688650Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.3.0) starting as process 43
2024-05-09T09:06:11.709310Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-09T09:06:12.722862Z 1 [ERROR] [MY-014061] [InnoDB] Invalid MySQL server downgrade: Cannot downgrade from 80400 to 80300. Downgrade is only permitted between patch releases.
mysqld: Can't open file: 'mysql.ibd' (errno: 0 - )
2024-05-09T09:06:13.048929Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2024-05-09T09:06:13.049275Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-05-09T09:06:13.049301Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-05-09T09:06:13.051100Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.3.0)  MySQL Community Server - GPL.
2024-05-09T09:06:13.051113Z 0 [System] [MY-015016] [Server] MySQL Server - end.

This is a test enviroment and we have backups from production, so it's not a big of a deal to restore the database, but the problem is, the Mysql daemon is not even starting, so I can't restore the dump:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Is there a way to start Mysql and restore from the production dump I have?

Thanks for any insight and for your time!

Trying to get Mariadb set up on my VPS and I'm running into an issue where I'm unable to add a user (error 1396). The thing is, the only time I encounter this problem is after securing Mariadb. When it's unsecured, I don't have any issues. Here's how I configured the security script for mariadb:

• set a root password (no)

remove anonymous users (yes)

• disallow root login remotely (no)
• remove the test database (yes)
• reload privilege tables (yes)

I didn't set a root password because it said not to if you already have a password for root, so I just used my current root password.

After logging into Mariadb and creating a database, I'm unable to create a user by running:

create user 'username'@'localhost' identified by 'password';

Any help would be appreciated because I'm seriously stumped.

Thanks!

Hi all,

Many thanks for taking the time to assist. I have an issue on a test database called licences and table called hr. The table was created by:

CREATE TABLE hr (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
first_name VARCHAR(50),
last_name VARCHAR(50),
staff_number INT,
email VARCHAR(50)

);

I have a randomly generated staff list from a small python script that I use (by importing faker). This is imported using the following:

LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE hr FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS (username, first_name, last_name, staff_number, email);

This all works correctly and shows as expected within DBGate and Mysql Workbench, however, for some reason, when I use the terminal with any command that includes "email", it causes the formatting and reporting of the "id" field to corrupt:

mysql> select id, username, staff_number, email from hr where id < 21;
+----+---------------+--------------+-------------------------------+ | id | username | staff_number | email | +----+---------------+--------------+-------------------------------+ | lopezd | 10959853 | dennis.lopez@jorj.net |2 | nelsonk | 15299188 | kimberly.nelson@jorj.net |xr | 19548511 | randy.fox@jorj.net |garciac | 12272330 | cody.garcia@jorj.net || hughesd | 19862339 | donald.hughes@jorj.net |6 | cisnerosj | 18264774 | joseph.cisneros@jorj.net

Finally this is the hr file as a csv:

[~/Documents/cscode]$ cat users.csv | head
Username,First Name,Last Name,Staff Number,Email lopezd,Dennis,Lopez,10959853,dennis.lopez@jorj.net nelsonk,Kimberly,Nelson,15299188,kimberly.nelson@jorj.net foxr,Randy,Fox,19548511,randy.fox@jorj.net garciac,Cody,Garcia,12272330,cody.garcia@jorj.net hughesd,Donald,Hughes,19862339,donald.hughes@jorj.net cisnerosj,Joseph,Cisneros,18264774,joseph.cisneros@jorj.net

Any help or feedback is appreciated.

Regards

Kartibok

Hey, all. I am in a bit of a pickle. I need to do the following:

1. Search wordpress database and find all image tags <img....> and remove them.

2. The search should only include a certain category and ONLY posts from before 1/1/2024.

For example, I want to remove all image tags from post content that were posted before January 1, 2024 in the JOBS category.

I have never done anything like this before so I am a total beginner and have no idea where to start. Thanks for any help.

Can't find many resources online for this, especially when commit fails due to hardware or network issues.

Should there be a cronjob to re commit (or rollback) recovered transactions? And how's the performance like?

I got like 6 tables to update across several servers in 1 transaction. While the failure rate is low, it still should be handled properly.

MySQL said: Cannot connect: invalid settings.

Packets out of order. Expected 0 received 1. Packet size=%Id

mysqli::real_connect(): Error while reading greeting packet. PID=14632

mysqli::real_connect(): (HY000/2006): MySQL server has gone away

Connection for controluser as defined in your configuration failed.

Packets out of order. Expected 0 received 1. Packet size=%Id

mysqli::real_connect(): Error while reading greeting packet. PID=14632

mysqli::real_connect(): (HY000/2006): MySQL server has gone away

phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in your configuration and make sure that they correspond to the information given by the administrator of the MySQL server.

​

I've searched around on stackoverflow, youtube to find solutions but none of them really solved anything so any help would be appreciated

EDIT: Reinstalling XAMPP fixed it but I kinda have a hunch it will happen again

Hello, I get this error when I want to connect to mysql via cmd : ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

In reddit,I read messages relating to my SQL versus postgreSQL and most of the people said you should learn postgreSQL because of various reasons. I have already used my SQL for simple db in some of my small apps and I haven’t encountered any trouble yet. So considering that I know the basics of mySQL should I really switch to postgreSQL?is in thr long run , postgreSQL better?

Guys I’ve download MySQL community server 8.4.0 and workbench 8.0.36 on my intel chip macbook (2019) whenever I open system settings and go to MY SQL the light in blinking on and off, may I know what could be the problem and please explain me in lay man cuz I’ve started to learn coding for the first time

I had previously spent a lot of my web dev career building web apps and going with the idea that we need fast queries because the user experience is affected by it. So I guess I've been conditioned to always feel like queries that take more than a couple seconds need to be examined.

But now I'm dealing with much bigger datasets as part of a data warehouse, and these queries don't affect the user experience. At first, I was worried that these queries take some time. But we're talking about tens or hundreds of millions of records that are queried and potentially aggregated, depending on the use case.

My question is, assuming a query is optimized based on an EXPLAIN statement, what do I need to consider when a query runs for a long time? I want to understand when I can let it run simply because the volume of data requires it vs when to break queries up into chunks.

And really, I don't even a real number on what a "long time" is. In previous tasks where I did some data copies between tables, I let queries run for hours. What would you consider a long running query?

Hi guys, 'm looking for specific information on some OVHcloud settings. Let me start by saying that I have never used OVH. I have to work on the design of a site hosted on ovh and as per routine I first of all backup the database mysql and the site files, but when accessing the database by clicking on access via phpmyadmin, it asks for the access credentials. 1. Is there any way I can find this password somewhere on OVH (avoiding compromising the database password) without disturbing the customer or do I necessarily have to ask for it? 2. Changing the password from there (see screenshot https://imgur.com/e49geyq) will only change the password of the phpmyadmin login screen or that of the database? Furthermore, is there a risk that changing it will block access to some sections of the site? 3. Does OVHcloud provide a control panel such as cPanel for managing the site or will I have to access it via ftp/sftp to make backups? Thanks in advance for your time!

I need to access in database to make backup, but ask for a credentials that I don't have.

Anyone have any idea where to start with this?

I received a file, let’s call it database.tar.gz

Extracting that file, there are about 4000 files in the extracted folder that appear to represent scripts and data/tables. But each of these files is a .sql.gz file.

There is also a metadata file with the dump start and end time.

I’ve never seen a mysqldump export look like this. Does this type of export/dump sound familiar to anyone? I need to rebuild this database pretty quickly and wanted to know how it may have been exported so I can reverse the process.

Thanks.

I want to start learning SQL and Python. So what to start and how ?

I have a experience of 4 years in operations and management.

1. Online Class
2. Recorded Class
3. One to one
4. Offline Coaching
5. YouTube

Any suggestions

Thanks in advance!

Hello chat , I have in mysql a table with the following fields : id , bandId, freq, lvl, bw, pass and timestamp
, and I want to create an CTE where should keep all my rows but where bandId it is equal and pass is equal , and diference between two frequencys it is lower 10k , in the row of grater frequency to display the value of lower frequency but al the other fileds from that row to remain unchanged.

Until now I don't found a solution to this problem,

Has anybody tried/used a solution which can give Materilised view capability for MySQL. I do know that Oracle used to have it and discouraged due to performance overheads.

MySQL natively dont support MVs and ppl suggest implementing own. I am worried that implementaion cna be come buggy if there are different Where clause conditions that result in storage of multiple result-sets inefficiently

Just got hosting at HostGator. The MySql Database says that it is 8.1.27.

I need to set up user logins for this website I am going to start building. So anyway,

SELECT Password('Qwerty123') FROM DUAL;

This works, but I get the message that "Warning: #1681 'PASSWORD' is deprecated and will be removed in a future release."

I tried bcrypt.hash, but, apparently either is either not installed or not available.

What can I use instead?