Stack Exchange Network

Stack Exchange network consists of 175 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
1answer
23 views

I need to run a procedure that goes through all schemas tables in MySQL server [on hold]

I am new in creating procedures ,Actually I need to run a procedure that will run on all schemas tables in my server when am calling the procedure it gives me this error ERROR 1046 (3D000): No ...
0
votes
0answers
45 views
+50

MySQL 5.6 too much write after enabling compression

Recently I have enabled table compression (i.e. ROW_FORMAT=COMPRESSED) on half of the tables in a MySQL 5.6 server. I am observing: much higher write IOPS (~700 -> ~3000) dangerously lower dirty ...
0
votes
0answers
10 views

Referencing multiple user id's in a single field

I'm creating a system where a user can make a prediction about an event, and if they get all the predictions correct they win. I have three tables: users, games and predictions. The structure of each:...
0
votes
0answers
14 views

libmysqlclient.so crash - yaSSL

I'm using MySQL as backend. I've a master and slave Qt application running on 2 systems. Slave application connects to master db remotely. I'm on MySQL 5.7.26. I'm using mysql-community-server on ...
0
votes
2answers
31 views

SQL switching values on a column

In interviews for software developer roles SQL questions can sometimes come up. There is a classic question around switching values in a column, typically swapping the gender of people in a table for ...
2
votes
1answer
57 views

Simple query returning < 10000 rows taking 3-4 seconds

I'm having some trouble diagnosing this very simple query which seems quite slow to me, any help would be much appreciated. The table of relevance for the query at issue is defined as follows: ...
0
votes
0answers
18 views

error while restoring database from mysql: ERROR 1006 (HY000)

I made a mysqldump from all my databases prior to fully deinstall and reinstall MariaDB. while restoring the databases with the following command, logged in as root: MySQL -u root -p < /mnt/...
1
vote
1answer
31 views

Optimize MySQL query retrieval from audit log table

I am running MySQL InnoDB 5.6. This is my first post, so please bare with me of any mistakes. I have this schema: I have a list of invoices in the invoice table, and if I want to see all the ...
2
votes
0answers
34 views

check if MySQL is using the Henry Spencer implementation for REGEX

Prior to MySQL 8.0.4, MySQL used the Henry Spencer implementation for REGEX and you could use this for word boundaries: '[[:<:]]' and '[[:>:]]' From 8.0.4 MySQL is using the ICU implementation ...
0
votes
0answers
22 views

How to select random name? [closed]

i got an issue. I'm making a airline software for a game and require database. Now, im trying to make my pilot select options. I have example 2 hours, 3 hours etc. So when my pilot select 4 hours, the ...
0
votes
1answer
28 views

MariaDB replication failure: Fatal error 1236 - binlog shows 'Event truncated'

Disclaimer: I am not a certified DBA I have configured MariaDB Master - Slave replication and everything was working but now have an error that I cannot get past. I have run the following on the ...
0
votes
0answers
24 views

Making a trigger that checks current table's and another table's enteries

I have 2 tables TABLE 1: Schedule FIELD: "course_code" FIELD: "course_time" FIELD: "course_day" TABLE 2: Courses FIELD: "course_code" FIELD: "semester" I want a trigger that gives an ...
0
votes
1answer
25 views

Best way to back-up MySQL production database to another database [closed]

The case: I have a very large MySQL production database. Due to performance problems I want to periodically clean-up this database automatically. Before cleaning-up I want to do an incremental backup ...
0
votes
0answers
7 views

SQL ERROR [ mysqli ] Can't find FULLTEXT index matching the column list [1191]

I faced this problem after importing the DB, recently i had to setup the fresh one so i used the already taken backup of files & db after importing db this following error shows, General Error ...
0
votes
0answers
17 views

operand should contain 1 column Use Variables in SQL statement in Python

Trying to insert four columns and multiple rows with variables. The table in mysql has 8 columns in total. Four columns to insert is as following: report_type= tuple(("ada","asda","frfe","adaaa","...
0
votes
1answer
17 views

Force index using the unused index columns having effect as order by for the result set?

if I have a index for (col1, col2, col3, col4), my query put col1 and col2 in the query where clause and force MySQL to use the above index. Would my query result be sorted by col3 and col4 ...
0
votes
0answers
24 views

phpMyAdmin - Cannot log in to the MySQL server

Setup a new instance of phpMyadmin and I am able to sign with root and password, but when I do the following I am unable to login. Also able to sign into mysql via command line. create user 'user'@'...
0
votes
1answer
31 views

Results not returning rows where second parameter should match null

I'm trying to left join three tables using columns from the first and third tables as the filters. The second and third tables could have null values where I am trying to join. I want to be able to ...
0
votes
1answer
14 views

In a MySQL cursor, will it be possible to skip an iteration or row of the cursor?

Before asking this question, I was checking if there were any similar questions, but I did not find what I'm looking for. Here we go!. The problem is that I have a cursor that is crossed, but believe ...
0
votes
0answers
30 views

MariaDB pipped querys not executed

I experience a strange problem on our 3 node MariaDB cluster (10.2.24). Queries send over the command line are received by the cluster but don't get executed. MySQL does not return any errors or ...
-2
votes
0answers
18 views

AWS Aurora Severless [closed]

I am building a new app that I plan to put on the App stores. I was thinking of using the AWS Aurora Serverless. Since this is relatively new and I wanted to ask what have some of the experiences of ...
0
votes
1answer
25 views

change master to cause 1364

ip addr|grep inet inet 192.168.8.47/24 brd 192.168.8.255 scope global ec1 inet 192.168.8.45/24 brd 192.168.8.255 scope global secondary ec1:1 I got like this mysql> show slave status\G; ******...
0
votes
0answers
27 views

Different Permissions Behavior For Information Schema on Views

I have a view which have following definition Create view view1 as select * from information_schema.events where event_schema="database1" I have granted select permissions on the view1 to ...
1
vote
2answers
31 views

MySQL: Is it safe to set `key_buffer_size` to 0 if the db doesn't have any MyISAM table?

In our system, we don't use MyISAM - only InnoDB (and occasionally, MEMORY). Is it safe to set key_buffer_size to 0? Or there are unexpected side effects? Based on the manual, it seems it's safe.
0
votes
0answers
23 views

Bring Match and unmatch record in join [closed]

I have this query with multiple joins. SELECT pro.PK, pro.Name AS ProjectName, pro.TeamLead, pro.Status, pro.StatusMode, /*Project Table*/ emp.Employee_Name, emp....
0
votes
1answer
54 views

How to implement many-to-many relationship with one table

I have a table: CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE IF NOT EXISTS emp_list( id INT UNSIGNED NOT NULL AUTO_INCREMENT primary key, first_name VARCHAR(30) NOT NULL, last_name ...
2
votes
0answers
60 views

What's the more efficient way of selecting with multi left joins? [closed]

I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities. All tables have a id column as the primary key. student.id is varchar(64) (similar ...
0
votes
0answers
21 views

Database choices [closed]

I am building an app that allows people to share items with other people in the community. I wanted to use AWS as my platform. My idea was to use react Native for the app. AWS Cognito for the ...
2
votes
0answers
30 views

Need to update a table with an iterative number based on a subquery

Massive edit to follow procedure and enable reproducing this in a fiddler somewhere. Table 1, schedule: CREATE TABLE `schedule` ( `scheduleid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `...
0
votes
1answer
20 views

How to efficiently extract data from a csv file and save it in mysql

I have a csv file, maybe 1000 or more rows, and echo row’s data is related to two table in mysql. Example, table `book`(id, title, …) table `scene`(id, book_id, short_content, position…) And the ...
-2
votes
1answer
22 views

Get Highest Granularity of columns with NULLS [closed]

I have table with columns like Name, City, State, Country. What I wanted is the top granularity of the combination of Name, City, State and Country. Example: Name City State Country ----- ---- ---...
0
votes
2answers
53 views

innodb high i/o , some parameters make mysql crash

i have a windows 2012 server with 16 core cpu and 64 gig of ram and 3*146gig sas 15k that have been used as a database server and just have mysql installed as a part of xampp. the mysql version is 5....
-4
votes
0answers
21 views

How backend and database of a online multiplayer gaming website works [closed]

I learned that in the database usually we have tables and websites interact with databases to show on their templates. I want to know how it works with gaming website. What happens when someone opens ...
0
votes
1answer
49 views

mysql join tables query [closed]

I have 2 tables job and dn (layout below) and would like to join the tables if i use SELECT part, description , GROUP_CONCAT(job_number ORDER BY job_number desc) AS job_numbers, ...
-1
votes
0answers
23 views

SQL Trigger how to compare two values [closed]

I want to avoid that the same values occur in my table. Here is the part in the trigger that is not working IF (NEW.user_id = NEW.suggestion_user_id) THEN SIGNAL SQLSTATE '45000' SET ...
0
votes
1answer
15 views

MySQL 5.7 statistics - Change db

I have a MySQL 5.7 database. I was looking in statistics in server status that shows type of queries / commands / tasks executed in the database. It shows that the query command "change db" by far was ...
1
vote
1answer
22 views

How to order by then start at first non null value and select a limit for number of values

I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to ...
1
vote
1answer
38 views

Optimize MySQL query and index for multiple column range query

I have a table like this: CREATE TABLE IF NOT EXISTS `jobs` ( `job_id` varchar(36) NOT NULL, `job_status` varchar(30) NOT NULL, `created_at` datetime NOT NULL, `lease_date` datetime, ...
0
votes
2answers
71 views
+200

Why are the system tables in MySQL and MariaDB utf8 and not utf8mb4?

I know the general advice is never to use utf8, but instead to use utf8mb4. If that's the advice, then why do the system tables themselves use utf8? You can see this in the MariaDB test suite, and ...
1
vote
2answers
35 views

MySQL and MariaDB: COLLATE on table vs COLLATE on all columns?

Will the following table behave or perform any differently CREATE TABLE t1 ( foo char(60) COLLATE utf8_bin, bar char(64) COLLATE utf8_bin, baz char(80) COLLATE utf8_bin ) DEFAULT CHARSET=utf8; ...
0
votes
0answers
27 views

IP address ** could not be resolved: Name or service not known [closed]

I am trying to understand what is going on with my MySQL server. I am seeing errors frequently like the following: IP address '104.239.141.16' could not be resolved: Name or service not known or ...
1
vote
1answer
25 views

mysql slave restore database ERROR 3079

Master A mysql 8 Master B mysql 5.7 Master C mysql 5.7 Slave mysql 8 i create 4 channels on my slave and i can not restore backup from master. [root@CentOS-76-64-minimal hex]# gunzip < db.gz | ...
0
votes
1answer
12 views

Using MySQL Workbench 8 on RHEL7 gui - When I highlight text in the SQL editor it turns black

I'm assuming this is a config issue in MySQL Workbench or in RHEL. How can I change this to a readable color?
0
votes
1answer
28 views

One column link to others

I always search my problem with different search terms but the problem I am facing now I can't express this in search keyword. So instead I came here to ask directly from humans as machines were ...
1
vote
1answer
16 views

Master mysq8 slave Mariadb 10.3 Error 22

Master (mysql Ver 8.0.16) Slaventer code heree (mysql Ver 15.1 Distrib 10.3.15-MariaDB) I have this error Error 'Character set '#255' is not a compiled character set and is not specified in the '/...
0
votes
0answers
16 views

MySQL and SQL 99

Is MySQL SQL 99 compatible? I've been searching on the net in vain. I found, for example, the expressions SQL 1999, SQL 999 but not the answer to my question.
0
votes
0answers
120 views

same query gets slower after other queries get called (using a lot of prepared statements inside stored procedures)

I have a query that, when i run it first after i restart mysql, takes 2 seconds, but when I run a sequence of other queries before it (this query belongs to a procedure between other procedures) ...
0
votes
0answers
21 views

Optimizing the read of child records of many parents

The main table has a complex query that selects the jobs that are to be displayed on the current workstation. In addition to the main job record there are a couple of child tables with a few dozen ...
1
vote
1answer
33 views

Inheritance - What's better: three “equal” tables or one with a type column?

I'm having an existencial doubt: I have three conceptual entity types on my logic: - distributors - business_groups - clients All of three are "companies", and will have the same columns. The ...
1
vote
1answer
34 views

Limit left join for multiple rows

Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables. Let's overview simple example. ...