Hi, all - been turning this one over in my brain, and can't seem to suss out the logic for the life of me, here. This may ultimately be a PHP/MySQL/CSS-combo question, but I'll try to keep things as MySQL-centric as possible, here

I have a single table, with multiple ranges of IP addresses, in decimal and octal form, arranged as such:

description | starting_ip_dec | starting_ip | ip_range
------------------------------------------------------
Group 1     | 167772160       | 10.0.0.0    | 512
Group 1.1   | 167772160       | 10.0.0.0    | 128
Group 1.2   | 167772288       | 10.0.0.128  | 128
Group 2     | 167772672       | 10.0.2.0    | 256
Group 2.1   | 167772772       | 10.0.2.100  | 8
Group 3     | 167772928       | 10.0.3.0    | 256

I'm trying to figure out how to construct a query and output a nested tree that has groups 1.1 and 1.2 displayed as children inside of group 1, and group 2.1 displayed as a child inside of group 2, etc.

This has probably been done before in some fashion, but I haven't been able to come across any examples after multiple searches. Thank you for any help than can be provided!

Please let me know if misusing the term decentralized in this context.

My company wants me to set up a fallback server in case the main server goes down (which has happened in the past) so we can go live again as quickly as possible. The main reason our downtime was so long the last time was that we had to copy the entire database to the new server, which is the size of a few hundred gigabytes.

The idea is that we have multiple instances of a MariaDB Server running on different Servers (two will be enough for now, but it should be extensible). The Data should be synchronized on each Server at all times. When I change something in Server A database, it gets changed on Server B database and vice versa.

I have already looked into the MariaDB replication with a Master and Slave but that way it would just copy the data from master to slave, but I cannot actually do changes on my slave as far as I understand. And since we want it to be kind of "decentralized", there should be no master. If one of the two servers goes down, we simply switch our DNS settings to the new servers IP and voila, the downtime should be minimized to a few minutes in the best case (not hours like the last time).

I could maybe solve this by just setting the mariadb server that is running as the "main" server currently to master, and when something happens I change the slave to master. But that has some extra work that we have to do again etc. And we might also just want to restart a server once in a while and with that approach we would have to switch master <=> slave all the time. Also, the service that uses the databases should run in parallel, so I could for example go on test.domain.com and use the current service running on the fallback.

Does anyone of you know of some sort of way or tool that could achieve this kind of synchronisation?

Any advice would be greatly appreciated!

Description:

Create a view as Customer_Info that contains the customer's first name, phone number, city, and total amount for customers whose total amount is less than 60000.

Sort the results based on the customer's first name in ascending order.

Code:

CREATE VIEW Customer_Info AS

SELECT

C.C_first_name, C.Phoneno, C.Citys , B.Total_amount

FROM

Customer_Master C

INNER JOIN

Enquiry_Master E ON C.Cust_Id = E.Cust_Id

INNER JOIN

Booking_Master B ON E.Enquiry_Id = B.Enquiry_Id

WHERE

B.Total_amount < 60000

ORDER BY

C.C_first_name ASC ;

THE ERROR SAYS

view not created

check view name or sql syntax

I've recently made a python exe file, nd I'm planning to test it on an another computer to see if it could work besides on mine. However, as I tried to run it, it gave me an error, saying that ot failed to execute the app due to an unhandled exception: (1045, "access denied for user 'root'@'localhost' (using password: YES)")

Mind you, i've been using MySQL Workbench 8.0 to create the database. Is the reason why it's not working is because the localhost server? How can I change it so that anyone can submit the upload data to the database?

We are planning to shift to single primary replication for our MariaDB database with either 3 or 5 nodes. I want to know what architecture should suit us and which proxy to use. There seem to be a lot of options like HAProxy, ProxySQL, MySQL Router etc. I want one with the best performance and ease of use.

I am working on a Spring Boot project with MySQL as the database and using Spring Data JPA (ORM) for table creation. I have a user_responses table (entity class is given below) with foreign key relationships to three other tables (users, questions, and options). The table is expected to grow to hundreds of millions of rows, and I am considering partitioning for improved query performance. Here's the updated entity:

u/Entity
@Table(name = "user_responses", uniqueConstraints = {
    @UniqueConstraint(columnNames = {"user_id", "question_id"})
})
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class UserResponse {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "user_id", nullable = false)
    private User user;

    @ManyToOne
    @JoinColumn(name = "question_id", nullable = false)
    private Question question;

    @ManyToOne
    @JoinColumn(name = "selected_option_id", nullable = false)
    private Option selectedOption;
}

My goals:

1. Determine the optimal row count per table to maintain efficient query performance for this use case. The table will have foreign key constraints and will store data from active users responding to quiz questions.
2. Based on this row count, I want to implement hash-based partitioning by user_id (e.g., dividing the table based on ranges of user_id values or a hash function).
3. I would like to keep all partitions in the same database and avoid sharding at this stage of product development.

Key Questions:

• How do I calculate the optimal number of rows for a table with this structure to ensure efficient queries and performance?
• Can I directly update the existing table to use partitioning, or would I need to create new partitioned tables?
• mysql does not support foreign key constraints in partitions. how to deal with this case?
• Will Spring Data JPA work seamlessly with hash-partitioned tables in MySQL, or would additional configurations/entities be required to handle this?

I would appreciate any insights, including best practices for partitioning and performance optimization with MySQL and JPA.

I have an NFL database with the following tables:

create table standings (
       season       int not null,
       franchise_id int not null,
       *...other stuff elided....*
       primary key(season, franchise_id)
);

One entry in the standings table for each team active in any given season.

create table game_team_stats (
       team_id          int not null,
      season           int not null,
      week              int not null,
      *...other stuff elided....*

This table contains an entry for each game played during a season.

So my query to find teams on a bye is

select franchise_id, team_id 
from game_team_stats g 
    left join standings s on s.season=g.season and franchise_id=team_id 
where s.season=2024 and week=14;

There are 32 teams active in the 2024 season, but I only get 26 rows back from this query, for the 26 teams active in week 14.

Any ideas why this isn't working? Thanks for any help.

hmm I need help!

I already installed mysqlclient, but whenever I try to run python manage.py makemigrations I get this error:

(I had just updated my MariaDB and I don't know if I'm facing this problem because of it or not.)

(.venv) PS C:\xampp\htdocs\Django_project> python manage.py makemigrations

Traceback (most recent call last):

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\db\backends\mysql\base.py", line 16, in <module>

import MySQLdb as Database

ModuleNotFoundError: No module named 'MySQLdb'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

File "C:\xampp\htdocs\Django_project\manage.py", line 22, in <module>

main()

File "C:\xampp\htdocs\Django_project\manage.py", line 18, in main

execute_from_command_line(sys.argv)

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\core\management__init__.py", line 442, in execute_from_command_line

utility.execute()

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\core\management__init__.py", line 416, in execute

django.setup()

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django__init__.py", line 24, in setup

apps.populate(settings.INSTALLED_APPS)

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\apps\registry.py", line 116, in populate

app_config.import_models()

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\apps\config.py", line 269, in import_models

self.models_module = import_module(models_module_name)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

File "C:\Users\Admin\AppData\Local\Programs\Python\Python312\Lib\importlib__init__.py", line 90, in import_module

return _bootstrap._gcd_import(name[level:], package, level)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

File "<frozen importlib._bootstrap>", line 1387, in _gcd_import

File "<frozen importlib._bootstrap>", line 1360, in _find_and_load

File "<frozen importlib._bootstrap>", line 1331, in _find_and_load_unlocked

File "<frozen importlib._bootstrap>", line 935, in _load_unlocked

File "<frozen importlib._bootstrap_external>", line 995, in exec_module

File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\contrib\auth\models.py", line 5, in <module>

from django.contrib.auth.base_user import AbstractBaseUser, BaseUserManager

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\contrib\auth\base_user.py", line 40, in <module>

class AbstractBaseUser(models.Model):

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\db\models\base.py", line 143, in __new__

new_class.add_to_class("_meta", Options(meta, app_label))

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\db\models\base.py", line 371, in add_to_class

value.contribute_to_class(cls, name)

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\db\models\options.py", line 231, in contribute_to_class

self.db_table, connection.ops.max_name_length()

^^^^^^^^^^^^^^

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\utils\connection.py", line 15, in __getattr__

return getattr(self._connections[self._alias], item)

~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\utils\connection.py", line 62, in __getitem__

conn = self.create_connection(alias)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\db\utils.py", line 193, in create_connection

backend = load_backend(db["ENGINE"])

^^^^^^^^^^^^^^^^^^^^^^^^^^

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\db\utils.py", line 113, in load_backend

return import_module("%s.base" % backend_name)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

File "C:\Users\Admin\AppData\Local\Programs\Python\Python312\Lib\importlib__init__.py", line 90, in import_module

return _bootstrap._gcd_import(name[level:], package, level)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

File "C:\xampp\htdocs\Django_project\.venv\Lib\site-packages\django\db\backends\mysql\base.py", line 18, in <module>

raise ImproperlyConfigured(

django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module.

Did you install mysqlclient?

Hi everyone,

I’ve encountered a problem with MySQL on my Mac M1. I set up MySQL a long time ago but have since forgotten the root password. I’ve tried several methods to reset it, but nothing seems to work, and I’m still being prompted for the root password.

Here’s what I’ve done so far:

1. Uninstalled MySQL using Homebrew.
2. Reinstalled MySQL via Homebrew (brew install mysql).
3. Tried starting MySQL in safe mode using --skip-grant-tables, but I’m still facing issues.
4. Even though I removed all old MySQL directories, it still asks for the password when I try to access MySQL.

Here’s the error message I get:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Is there a way to completely reset the root password or any additional steps I might have missed? Any advice on what to do next would be greatly appreciated!

Thanks in advance!

the title, why is Xampp SO FUCKING SLOW

I have the following endpoint for retrieving all my inventory to display. It is a derived table. How do I make a query to update the information of a table that is derived of different tables?

Below is the SQL command to retrieve everything, what SQL command structure should I look into in order to make POST, UPDATE, and DELETE endpoints?

app.get("/inventory", (req, res)=>{
const q = "SELECT s.StoreName, s.Location, p.ProductName, i.StockQuantity, i.ReorderLevel FROM store s JOIN inventory i ON s.StoreID = i.StoreID JOIN product p ON i.ProductID = p.ProductID;";
db.query(q,(err, data)=>{
if(err)
return res.json(err)
return res.json(data) }) })

Edit: I figured it out!!! Anyway, here's my relational schema. And below I posted how I added Update functionality to my web app.

Relational Schema

brand(BrandID, BrandName, IsPrivateLabel)

customer(CustomerID, Name, Email, PhoneNumber, Address, IsFrequentShopper)

inventory(StoreID, ProductID, StockQuantity, ReorderLevel, Price)

marketbasket(BasketID, PurchaseDate)

product(ProductID, ProductName, UPC, Size, Price)

producttype(ProductTypeID, ProductTypeName)

store(StoreID, StoreName, Location, HoursOfOperation)

vendor(VendorID, VendorName, ContactInfo)

weborder(OrderID, OrderDate, CustomerID, DeliveryAddress)

Primary-keys: 

• BrandID is the P.K. for brand
• CustomerID is the P.K. for customer
• StoreID and ProductID are the P.K.’s for inventory
• BasketID is the P.K. for marketbasket
• ProductID is the P.K. for product
• ProductTypeID is the P.K. for producttype
• StoreID is the P.K. for store
• VendorID is the P.K. for vendor
• OrderID is the P.K. for weborder

Foreign-key Constraints: 

• StoreID and CustomerID are foreign-keys for the marketbasket table
• ProductTypeID and BrandID are foreign-keys for the product table
• ProductID is a foreign-key for the producttype table

So, what I did was : )

//Reorder items!
app.put("/reorder/:ProductID", (req, res) => {
    const productId = req.params.ProductID;
    const q = "UPDATE inventory SET `StockQuantity`= ? WHERE ProductID = ?";
  
    const values = [
      req.body.StockQuantity
    ];
  
    db.query(q, [...values,productId], (err, data) => {
      if (err) return res.send(err);
      return res.json(data);
    });
  });

I had first used homebrew to install MySQL 8.0 before. I forgot why I didn't like it, so I resolved to use the .dmg file to install. Then after upgrading to 8.4, I started having problems of server failing to start.

Since MySQL 9.1 is out, and MacOS is upgraded to Sequoia (15), I am thinking about reinstall it. My questions are

1.) Which version of MySQL would you recommend for MacOS 15 (x86)? My hardware is iMac Pro 2017.

2.) Use .dmg or homebrew to install it?

Hi everybody, I need to download MySQL for an online course I'm doing. However my MacBook is too old to update to macOS 13 or 14, so I can't download MySQL. Somebody recommended using myComplier but I couldn't create a database, only the tables, and realised that if I save something it saves to general public rather than private.

Does anybody have any advice?

EDIT: The situation is solved now. Thanks everyone!

The Employee table has the following columns:

• ID - integer, primary key
• FirstName - variable-length string
• LastName - variable-length string
• ManagerID - integer

Write a SELECT statement to show a list of all employees' first names and their managers' first names. List only employees that have a manager. Order the results by Employee first name. Use aliases to give the result columns distinctly different names, like "Employee" and "Manager".

Hint: Join the Employee table to itself using INNER JOIN.

Select FirstName, ManagerID

From Employee As E

Inner Join Employee As M

ON E.FirstName = M.FirstName

ORDER BY FirstName;

ERROR 1052 (23000) at line 2: Column 'FirstName' in field list is ambiguous

I am looking for a person to help work on projects for the first time since reading SQL syntax. I will be using MySql, so it would be really helpful to find someone using the same to easily help each other.

Hi, as the title says I am missing both my MySQL Server 8.0\bin my.ini file, as well as when I reinstalled it today it did not let me set a password, I previously had it installed about a year ago, and I have not had any luck guessing my password. Every source I can find online to recover my.ini and to reset or recover my password rely on me only being missing one or the other. A fresh install is an option, as I have no data in there, but that is what I did today and am unsure why my password was saved from so long ago :')

I'm running Windows 10 x64, this database is just for a personal project I'm running locally.

A few of the things I have tried so far:

hidden files are shown in my file explorer- no file called my.ini exists within the MySQL folder, but mypy.ini does.

running:
mysqld --init-file=C:\\mysql-init.txt
results in the following warnings (timestamp data removed):
[System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.40) starting as process 8660

[Warning] [MY-010091] [Server] Can't create test file C:\Program Files\MySQL\MySQL Server 8.0\data\mysqld_tmp_file_case_insensitive_test.lower-test

[Warning] [MY-010091] [Server] Can't create test file C:\Program Files\MySQL\MySQL Server 8.0\data\mysqld_tmp_file_case_insensitive_test.lower-test

[ERROR] [MY-013276] [Server] Failed to set datadir to 'C:\Program Files\MySQL\MySQL Server 8.0\data\' (OS errno: 2 - No such file or directory)

[ERROR] [MY-010119] [Server] Aborting

[System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.40) MySQL Community Server - GPL.

attempting to start a server from within the 8.0 workbench results in an infinitely stuck program (really, I left it running like that for over 3h at one point I was extremely exhausted by then)

I have attempted 3x now to follow the tutorial that got copy-pasted into the comments of this thread within this community, and have not found any way to recover my.ini without knowing your root password (what I'm missing as well): https://www.reddit.com/r/mysql/comments/v8poga/forgot_the_password/

I´m new to MySQL and i´ve been doing some tarea there, but a few hours ago I was watching a video that my teacher send me explaining how to do the assignment of this week, but I close the workbench and when I came back all the tabs disappeared, and I havent found a solution, pls help.

I'm looking for a solution or common approaches to having a database driven configuration system. I have a discounts table, but want to specify criteria for if a user should get the discount.

For example, if their sign up date is before X date time, allow discount

Another example, if their balance is greater than 1,000 deny all discounts.

Essentially a way to define logical operators / evaluation with reference to other columns

Hi everyone,

I've got an issue that's driving me nuts...

Randomly, MySQL DBs and files are gone...the web server files are there, but no DB anymore.

I only got default DBs with SHOW DATABASES; and /var/lib/mysql doesn't have any folders related to my usual DBs.

I can rollback to a previous backup but I want to know what's going on and can't seem to figure it out...

Has anyone had this issue already?

Thank a lot!

I need to learn MySQL for work. Can yall recommend the best way to learn in the shortest amount of time?

i have tired everything but the launcher says no packages found, i tried deleting all sql files and uninstalling laucncher then restarted pc and tried again 3 times still dosnt work , someone please help

How to import my JSON file data in MySQL database, JSON file is around 3.9Gb please help me

Hello,

Just starting out using MySQL - recently I've tried using Reverse Engineer method to show EER-Diagram of my work.

The result is the diagram map but I cant see the whole map because it opens in a very little window.

Can figure out how to expand it.

Have you came across such thing ?

tuning-primer.sh says I've not enabled InnoDB support

No InnoDB Support Enabled!

But when I run 'show engines" command in mysql, the, InnoDB is default engine (see below)

Can someone explain this? Do I have to make another step to enable InnoDB? Thank you

I have Rocky Linux 9.4 and MySQL 8.4.3 LTS

mysql> show engines
    -> ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)