Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [mysql]

All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

1
vote
1answer
34 views

Performance of mysql equi-join observed in HDD and SSD

I experimented performance of queries having equi-joins. I have simplified version of EXPLAINs of the queries. Mysql version : 5.7.18 Engine : Innodb Cache state : Mysql restarted before experiment ...
0
votes
1answer
28 views

MySQL/MariaDB Read only except for a single table

I Have a database with lots of tables and read_only enabled in the config, is it possible to set a single table to be writable?
0
votes
4answers
121 views

How to tell if a project needs a NoSQL database solution? [closed]

How to tell if a project needs a NoSQL database solution? I think that SQL limits define it. If the project needs bigger database or bigger fields then it needs NoSQL. Am I right? I looked into ...
0
votes
1answer
34 views

Default value of a column based on other column in MySQL

I have a mytable table in my MySQL database. It contains the following columns: id (INT) PRIMARY KEY - AUTO INCREMENT - DEFAULT NONE name (VARCHAR (220)) - DEFAULT NULL code (VARCHAR (220)) - ...
3
votes
3answers
862 views

Database Connection vs Raw TCP Connection [closed]

I have some fundamental questions about how database clients & database interact Do databases support multiple transactions simultaneously on a single database connection from client? If not, why ...
0
votes
1answer
34 views

Application considerations when using a multi-primary innodb cluster

I'm developing a scalable cloud application and I wish to use a multi-primary cluster to be better able to scale horizontally when needed. I have experience with a single primary cluster configuration....
0
votes
2answers
38 views

How to get rows only contains multiple ID using SQL?

Let say I have this table: id | foo 1 | 100 1 | 200 1 | 200 2 | 300 2 | 300 3 | 200 4 | 100 I am expecting to have this result: id | foo 1 | 100 1 | 200 1 | 200 2 | 300 2 | 300 I want ...
0
votes
1answer
28 views

How does MySQL evict pages from the buffer pool?

I am trying to understand the internals of MySQL. I do know that MySQL's buffer pool management has an old/young page list as shows here. And I do know about the flushing of old dirty pages, such as ...
0
votes
2answers
50 views

WHERE clause on a TIMESTAMP column causes serious performance penalty

I have a pages and a page_views tables with 100k~ & 4M~ records respectively. I run a query to get the top pages that have the most page_views: select `pages`.`id`, `pages`.`host`, `pages`.`...
1
vote
0answers
51 views

Slow mysqldump on big table

I am running MySql 5.6 on Google cloud as VM instance, one of the tables contains 1500,000K rows with 676G .ibd file. using mysqldump --opt --add-drop-table --quick DATABASE BIG-TABLE >DATABASE....
0
votes
0answers
22 views

What does `queries inside InnoDB` mean in the report of show engine InnoDB status?

> show engine innodb status ===================================== 2018-12-26 05:24:21 7f0c43bfb700 INNODB MONITOR OUTPUT ... ... ... ... -------------- ROW OPERATIONS -------------- 64 queries ...
0
votes
1answer
31 views

How to name tables such as “Suppliers Categories” [closed]

I am refactoring a very old DB (< 2001) migrating from MyISAM to InnoDB and I experience some issues with the CamelCase notation. I don't know why but with the use of MySQL Workbench, sometime, my ...
0
votes
1answer
23 views

A query for results based on date range

I have a table called invoice and I have a query that will give me the sum of all invoice amounts for a given account where the invoice has a status of PAID between a certain date range. Below is the ...
0
votes
0answers
24 views

I have created a website using SQL. I want to know whether my website is safe from the SQL injections or not?

The basic code which I used for the admin login and admin password is: </style> <div id="login-form"> <div> </div> <div > <h2 class="text-center">Login<...
0
votes
1answer
25 views

When I try to update/set a datetime I get an invalid mysql syntax 1064

I copy a value from the pre-existing values. I update the date. I do an update query. It has an issue and the issue seems to be the time. Here's my query: UPDATE xxxxx_eb_events Set ...
0
votes
1answer
28 views

Percona PAM with AD authentication using SSSD

I have installed percona PAm plugin on my Percona server as shown below: mysql> show plugins; ... | auth_pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL | | ...
1
vote
1answer
30 views

How to obtain all distinct items but limit each distinct to max n and ordered by date?

I have a table where I have uuid as PK, command_name string, count_successful integer and command_start date. I can grab all records by: select uuid, command_name, count_successful, ...
0
votes
0answers
27 views

Set up table for relationship type with both users in a different visitor table

I am a newbie in database driven application, and I just started working on a web app for my brother and parents clinic to manage patient records. So for their clinic I have one table Visitor that ...
0
votes
1answer
22 views

How to active mariadb log?

I want active log file in mariadb for check last query used in php, i find mysql config in internet like as blow: general_log_file = /var/log/mysql/mysql.log general_log = 1 or ...
1
vote
2answers
52 views

MySQL 5.7 intermittent max connections errors

I’m having a problem with MySQL 5.7 with « too many connections » causing services crashes. The max_connections system variable is set at 1000 and on average there are +/- 250 sessions/thread, so it’s ...
0
votes
1answer
21 views

pulling multiple entries from a match of two tables

I have two tables, attributes and attributes_entries attributes hold the title and attribute_entries hold the value of that title. if I do: SELECT attribute_entries.title AS ent_title, ...
1
vote
1answer
21 views

Multiple m:m relationships LEFT JOIN giving duplicate records

DB noob here. I have a table named invoice with a m:m relationship with a few other tables via junction tables: invoice_customer invoice_creator invoice_deposit invoice_withdrawal ...
2
votes
0answers
23 views

ranking using find_in_set

Trying to use this method to get the rank position of $associateID. Everything works except FIND_IN_SET. It's not reading alias1 or alias2. Does FIND_IN_SET work with alias's? How can I get this to ...
1
vote
1answer
17 views

Audit Plug-in for MYSQL 5.7.24 error

I have tried installing MariaDB audit plugin for MySQL 5.7.24. I have downloaded the 'server_audit.so' from MariaDB-10.3.11 and I think it is compatible with MySQL 5.7.24. But I have encountered this ...
0
votes
0answers
14 views

How can I connect the mysql enterprise monitor to its repository database with a socket?

By default the Oracle-MySQL-Monitor MEM (used to monitor MySQL Enterprise Instances) connects to its own repository database through localhost. Since we want to reduce the bindings of that database ...
3
votes
0answers
42 views

Query for getting reserved seats in a cinema

I have this cinema project for a course. And I have to visually show the seats to user so they can reserve their preferred seats. I need to get the reserved seats from the database of that particular ...
0
votes
0answers
19 views

MySQL 5.5.2 community version performance and hang issue

We have a 64GB RAM server with HP-UX OS and installed MySQL version 5.5.2. We observe frequent issues with the database and we must restart the database every 2 or 3 days which is impacting the ...
1
vote
2answers
27 views

InnoDB: will uncommitted transaction produce redo log?

Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log. start transaction; update users set uuid = UUID() from user where id = 1 update ...
0
votes
2answers
59 views

Which column to index for this query?

select DISTINCT id_request,username_request from darkhast_follower where darkhast_follower.id_request != '9762952594' AND darkhast_follower.id_request != 'null' AND darkhast_follower.status !=1 ...
0
votes
1answer
30 views

Best mysql config for my server

I have dedicated server, CPU is too high (80%-100%) and memory only use 9gb RAM 32 gb and CPU Dual Xeon E5-2670(8core and 16 thread) I have 25000 users that use my android application My database ...
0
votes
0answers
26 views

Database design of beauty center

I'm planning a database to manage the reservations of a beauty center. I'm having some trouble creating his conceptual scheme. I have 4 customers, booking, beautician and room. customers can book a ...
0
votes
0answers
23 views

Equivalent of DBCC page command of SQL server in MySQL?

I couldn't find any documentation of MySQL website about this so DBCC page in SQL server shows info about a page inside the database my question is how to do this in MySQL? (if its not possible in ...
0
votes
0answers
30 views

Mysql How to get joined tables data horizontally

I'm trying to get the addresses with that compairing amounts horizontally which found twice in address table. I'm getting the address name and product name but couldn't figure out how to get amounts ...
1
vote
1answer
47 views

CentOS 6.9 mysqld start fails without innodb_force_recovery = 3

Please help me. I've googled and searched here, but I couldn't solve my problem. The problem is that service mysqld start fails. But if I write innodb_force_recovery = 3 in my.cnf, mysqld starts. ...
0
votes
2answers
38 views

Safe backup of PXC / Galera cluster

I have 3 nodes 'master master' cluster and using PXC 5.6 But I am only using a single node for both read and write. Before using pxc, i was using standalone Mariadb 5.5 and I was taking backup using ...
0
votes
1answer
23 views

System memory behavior during huge mysql import

I am importing a huge (~900GB ) dump in a mysql 5.5 server; The system has 48GB of RAM. Here are my InnoDB settings innodb_read_io_threads = 16 innodb_write_io_threads = 16 #To ...
0
votes
3answers
52 views

SQL Query to select rows based on specific condition [duplicate]

Below is an example of childs daily activity. I want to select the next rows after the last 'Play' activity based on user id. id timestamp activity 567 1541300537817000 ...
0
votes
1answer
40 views

Relation between Faculty, Student and Course tables [closed]

What should be the relation between following tables. Confuse whether i create an enroll table with student and course or courses_assigned table using faculty and course and then create an enroll ...
0
votes
1answer
8 views

Setting model title in MySQL Workbench for Forward Engineering

In MySQL Workbench, when viewing a model, you can select File->Export->Forward Engineer SQL Create Script. The first four lines of the generated file are a comment, something like this: -- ...
-1
votes
1answer
18 views

Retrieve a list of Ids from a table where all the records have a given status [closed]

I have a table with data related to books. Schema: BookKey PRIMARY KEY, BookID, Status The primary key of the table is a BookKey and each BookKey belongs to one BookId. Each row also has a status ...
0
votes
1answer
14 views

Convert MyISAM to InnoDB in PXC master master

I have 3 nodes PXC 5.6 master master cluster. I have few tables that are using MyISAM. Total MyISAM table = 120 Total MyISAM table size = 80MB I have also read few recommendations where it suggests ...
1
vote
2answers
69 views

MySQLTuner request - high CPU problems

I've been running a pretty high pop website for a few months new and as its growing we've been having some problems lately. Its a 2 core Xeon, 8GB, SSD server. We're pulling +- 150k people a day. ...
1
vote
1answer
26 views

A low cardinality column index and SELECT

Suppose that a long (1000000 entries for example) MySQL table t has a column c with less than 10 distinct TINYINT values. Suppose also that most (say 99% or even 99.9%) of these values are equal to ...
0
votes
2answers
52 views

How can i filter by gender from a subquery

I have the following query: SELECT DISTINCT name, surname, id_number, gender FROM table1; How do I filter by gender to get the gender breakdown of the results of the above query? Do I use a subquery?...
0
votes
1answer
25 views

Slow query with multiple group by

I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user ...
-1
votes
0answers
31 views

Error 1215: Cannot add foreign key constraint

Executing SQL script in server ERROR: Error 1215: Cannot add foreign key constraint SQL Code: -- ----------------------------------------------------- -- Table `Proj`.`Rrepresent` -- ---------------...
0
votes
0answers
16 views

Mariadb / mysql table partitionning column usage

I read mariadb documention :https://mariadb.com/kb/en/library/partition-maintenance/ And it sayed : The partition field should not be the field first in any key But in mysql documentation :https:...
-2
votes
1answer
41 views

mysql multiple id sum values group by id within date range

where is sql query mistake? I want to sum values for multiple id within date ranges. SELECT date,ledger_name, sum(credit),sum(debit) from tbl_account WHERE ledger_name in(19,20,25,29) group by ...
0
votes
1answer
30 views

Copy Table from one MySQL server to another

Is there a way to copy or export a large table from MySQL server to postgres without affecting servers performance. I mean the table is very large and using mysqldump or any select query has bad ...
-1
votes
0answers
20 views

Data in MySQL is changed, any way to find what happened?

I have a two tables: Customers Sales (has a "customer_id" foreign key) On the saturday, I saw that all sales in the past has a same customer ID and only who has access to the DB were my worker and ...