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.

0
votes
0answers
35 views

MySQL won't start on ubuntu 16.04 - apparmor=“DENIED”

When tried to log in to the MySQL server yesterday I received the following error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) When I ...
1
vote
2answers
66 views

MySQL Select Union; Filter by Shared Relation?

Currently, I have 3 tables (see structure below), speakers, venues, and events. I'm trying to do a relevance search on the names of the speakers and venues. However, on the Search Results page, if the ...
1
vote
1answer
22 views

Trigger error in MySQL

I'm using Toad for MySQL, and I have to make a trigger so that: When the rent of a car is <=15 and >= 25, I insert a letter "A" in the field category When the rent is > 25 and <= 35, insert the ...
1
vote
1answer
16 views

Select n random rows for each language ID in one table

I have a table which holds many text entries for many languages, the structure is like this: `entry` varchar(255), `language` tinyint, The language column is an integer type and I have the supported ...
-2
votes
1answer
31 views

Show all children per parent

I have 2 Tables. T1:questions question_ID is_active question timestamp T2:answers answerID answer questionid I want to make a query that shows: question 1 answer 1 answer 2 answer 3 question 2 ...
0
votes
1answer
15 views

mysql 5.5 repartition

With MySQL 5.5, I want to change the partitioning scheme of a very large InnoDB table from "hash" to "range". Can I do it while the table is accepting DML? If not, what is the best-practice work-...
0
votes
0answers
13 views

Ignore keys while mysql command line export

I am writing a bash script to export a few tables from the MySQL DB and import in the backup DB. I will repeat this process on an hourly basis and will keep all the tables by changing the table name. ...
0
votes
1answer
26 views

How to enable NULL values in MySQL timestamp fields?

I need to restore backup from one MariaDB server (version 10.1.23) to another MariaDB server (version 10.1.37). But this version has a much stricter mode by default and does not allow NULL values in ...
0
votes
0answers
9 views

Can not move MySQL data folderto external storage

OS Ubuntu 18.04, laptop, ssd partitioned for two OS (win8 and linux), second drive mounted with permissions. Line from fstab: UUID={UUID} /media/iam/storage ntfs-3g permissions 0 1 I created dir db/ ...
0
votes
1answer
15 views

mysql trigger not working with if statement

CREATE TRIGGER updateNewEmp AFTER UPDATE ON BasicDetails FOR EACH ROW BEGIN if(NEW.MiddleName<>OLD.MiddleName) then insert into BasicDetails_audit(MiddleName,`State`,Country,Community)...
0
votes
1answer
30 views

Need help in optimising the MySql stored procedure

I have a stored procedure which is used to delete table entries. I have the input to the procedure as an array so that I can pass them as is for the delete queries [For ex: Use the IN clause to submit ...
0
votes
1answer
43 views

Need to join 5 tables and produce output in MySQL

I need help to join 5 tables and produce output in MySQL. Below are the details: Supposedly if the user is associated with 2 Devices and 2 Groups, the output should be as follows: Some user name 1, ...
0
votes
1answer
14 views

How to lock all tables except some specific table?

I am trying to set a stage server which replicate some tables like articles, user etc from master. If I start the slave server as read_only I am not able to my stage site up because there are some ...
0
votes
1answer
26 views

Loss of 20+Gb of space and cannot find out why (amazon mysql rds)

I am trying to figure out why one of my MYSQL RDS DB instances lost 20+GB of storage space and I cannot figure out why. I have 2 RDS instances and am using some queries to compare data + free data ...
0
votes
1answer
22 views

using tmpfs for mariadb tmp tables

My server is writing about 50% of temp tables to disk, even when max_heap_table_size and tmp_table_size are ridiculously high (500M). Yesterday I ran a script all day that monitored the tmp folder and ...
0
votes
0answers
13 views

Disable Binary Logs in MySQL 8.0

How can I disable binary logging on MySQL 8.0? I am needing to import a large MySQLDump file, and don't want to fill the Binary logs with many GB of data. In the past I would have set log_bin=0 in ...
0
votes
1answer
49 views

MYSQL ONLY RIGHT JOIN WHEN LEFT SIDE NULL

my English is not that good, so translated with Google Translate. Have been able to fix many problems with this site anyway, thanks to all active users here. Unfortunately I have to ask my first ...
0
votes
0answers
25 views

Error 'Unknown Collation: '2048' on query

I'm doing MYSQL Replication for the first time. Master mysql version: mysql Ver 14.14 Distrib 5.1.52, for pc-linux-gnu (i686) using readline 5.1 Slave Version: mysql Ver 14.14 Distrib 5.1.73, for ...
0
votes
1answer
30 views

How to convert timestamp to date & time

I am currently working on bigquery(standard sql), where I have a timestamp field which I want to convert to date and time in 2 separate columns. I tried doing this select EXTRACT(DATE FROM timestamp) ...
0
votes
1answer
45 views

CROSS JOIN of list of dates and another table

I have a query for getting data from a certain table by date range and grouping by week. My CROSS JOIN intends to fill in a default value for each week where there are no results for the date range. ...
0
votes
0answers
28 views

Dose galera cluster perform read load balancing?

Hi Now I'm doing benchmark test with several type mariadb replication with sysbench. 1 Master - 2 Slave MariaDB on kubernetes with ceph stroage. 3 Master with Galera Cluster on kubernetes with ceph ...
0
votes
1answer
19 views

Why was default of innodb_file_per_table changed?

Is there a disadvantage in using file per table storage? I wonder why the default of the value innodb_file_per_table was changed with MySQL 5.7. Documentation shows that the default value changed (...
0
votes
0answers
12 views

mysql drop user has broken the replication

I was dropping a user on master. But that didn't execute on Slave. Instead its broken the replication. GTID enabled Parallel replication enabled. On Master: Binlog-do-db: prod_db binlog-ignore-db: ...
1
vote
0answers
9 views

virtual MySql server with mounted RAID array

I have a Ubuntu 16.04 server with an 18TB RAID array. I am running virtual servers on the server using Wok/Kimchi and am wondering if it is reasonable to run a MySQL virtual server and set the data ...
0
votes
0answers
12 views

GTID ignore error on parallel replication

Im getting an error in replication where GTID and parallel replications are enabled. Now I want to skip one transaction. I didn't find a way to do that. Last_SQL_Error: Worker 48 failed executing ...
0
votes
1answer
18 views

innodb cache duplication due to os page cache and buffer pool

We have set our innodb buffer pool 80% of total memory. Mysql page allocation gets stalled sometimes when page cache is occupying memory. Now since mysql is caching table rows and indexes and follows ...
0
votes
2answers
26 views

best column type for hashed data in mysql

In my use case I have a table with a column containing hashed (sha256) IDs. I wonder which data type is better considering that It must be unique and I need to search on it. The sha algorithm ...
0
votes
0answers
16 views

Saving records in MySQL that does not pre-exist

I have asked this question on SO but believe this is the right forum to ask, so here it is Let say, I have a pre-defined table called cities, with almost all the cities in my country. When a user ...
0
votes
1answer
34 views

is vertical scaling good idea for a mysql database server?

I operate a mariadb/mysql database server and i do not want to get into the problems with managing a multi-cluster database server i have access to huge CPU and RAM and STORAGE when needed so my ...
0
votes
1answer
8 views

How to set mariadb read_write for some specific tables while starting as read_only=1

I am trying to set a mariadb slave which is exact replica of master db. As per the suggestion I start my slave with command --relay-log=mysql-relay-bin" "--log-slave-updates=1" "--read-only=1" . But ...
0
votes
0answers
8 views

Bugs of derived merge resolved in mysql8

All bugs of derived_merged resolved in mysql8? Is it optimal if i use derived_merged=on in mysql 8.0.11? By derived_merged=on it is giving invalid output of query when it will uaed with order by or ...
0
votes
1answer
18 views

MySql Not Logging Slow Queries

I've set up slow query logging for MySQL 5.7: [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow_query_log.log long_query_time = 10 log_queries_not_using_indexes = 0 Then to test ...
0
votes
1answer
47 views

mysql, performance issue with FOR UPDATE query

I have a query like this: SELECT id,domain_name FROM domains WHERE test_stage=1 order by id LIMIT 200 FOR UPDATE This is happening up to 500 times multithreaded within a second, so quite fast and it'...
1
vote
2answers
19 views

How to generate a dynamic file name for a mysqldump on Linux?

I want to schedule my MySql backs to happen on an hourly basis, but the problem is I want to customize the name of the file that is outputs but not to sure how to? This is the command I am using now ...
0
votes
0answers
9 views

amazon mysql 5.6 rds innodb_file_per_table 0 drop then create same database side effects of storage

I had an event last night where I dropped a database and then created the same database right after each other (then populated it with minimal data). Then about 2 hours later I got a disk alert that ...
0
votes
2answers
38 views

INNER JOIN is extremly slow on some rows and create temp table

The MySQL query SELECT `p`.*, `p.Author`.* FROM `post` AS `p` INNER JOIN `user` AS `p.Author` ON `p`.`userid` = `p.Author`.`userid` WHERE ( ( `p`.`threadid` = 29780 ) AND ( `...
1
vote
0answers
16 views

How to find out if entity is used in relations somewhere in database?

I have an entity/table of employees. Somewhere in database (in many places actually) this entity can be used in relations/foreign keys. The task is simple: if the employee is used somewhere, I ...
1
vote
1answer
14 views

Get parent/child or parent/siblings depending on the ID passed

This is similar to my table: | id | parent_id | title | |----|-----------|--------------| | 61 | NULL | title of 61 | | 62 | 61 | another title| | 68 | 61 | more text | |...
0
votes
2answers
41 views

Tuning mysql 5.7.1 for predominantly write workload on huge partitioned innodb tables

I recently got a task to tune MySQL for predominantly write workload on multiple innodb tables which are huge and partitioned. About the application: It's Nimsoft suite, so the SQL queries are not ...
0
votes
0answers
39 views

I have a strange DB structure is this correct?

I have the below scenario, i want to confirm that is there any better way then this db structure or is there any need of improvement for my current database table strucutred? I have 3 modules 1) ...
1
vote
0answers
15 views

MySQL not starting when shut down with mysqladmin

MySQL server 5.5 fails to start when I use the mysqladmin shutdown command to stop the server. The only option to start MySQL server is to reboot the machine. Is there is any option so that I can ...
0
votes
0answers
14 views

Default table filters

Quick background information: I have a MySQL production-replica database, which includes 'soft-deleted' records - that is values which should not be shown to the end user, but can at some point be ...
0
votes
1answer
23 views

#1215 - Cannot add foreign key constraint ERROR even though the columns are exactly the same

Last night I ran SQL commands in phpMyAdmin very similar to the following to setup a players table and a games table linked by a foreign key. It worked without errors, making google_id in my games ...
0
votes
2answers
19 views

Single address table for company, company_branch, users

I'm a database noob and i'm asking this simple question because i found no answer to my question. I'm using MySQL. I have 3 tables Company, Company Branches, Users and these 3 tables have similar ...
0
votes
2answers
28 views

query to replace text with sequential numbers

I have an images database, the default sort is the "name" column, and the "description" column content is the exact same in all files in the table. what I need is to replace certain text in the "...
0
votes
2answers
32 views

MYSQL search for each name in 2 columns and add values associated for each player

I have a database "game_results". It contains the following: PlayerID (int not null primary key auto increment) player1 (varchar) p1destroyed (int) player2 (varchar) p2destroyed (int) winner (...
3
votes
1answer
42 views

MySQL and PostgreSQL has JSON data types, is it the same as NoSQL database? [closed]

MySQL and PostgreSQL introduced JSON data types, is it the same as NoSQL database? Why? NoSQL here is a class of data bases similar to MongoDb and DynamoDB.
1
vote
2answers
30 views

How to group same columns from same two tables, but from two different result sets and/or conditions

I am having troubles coming up with a query for something that should be quite simple. Initially, I had two tables of data that I generated joint result sets from; an events table, and a groups table ...
0
votes
1answer
31 views

opened_files large, open_files tiny

on my mariadb server OPENED_FILES is currently 2,775,453 and OPEN_FILES never seems to exceed 65. I understand that opened_files is cumulative and is meaningless without a time period. ...
1
vote
1answer
28 views

Unable to use cyrillic characters in mysql docker console

I'm running a MySQL docker container (latest - 8.0) with the custom configuration file containing: ====================================== [client] default-character-set=utf8mb4 [mysqld] ...