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
33 views

How can I reset Aborted_connects in MySQL?

A recent issue in our network caused a large number of aborted connects, which is now being displayed on the health dashboard of our web application as a potential issue. Aside from restarting MySQL, ...
0
votes
1answer
35 views

Complex join 3 tables with sum and group by

I'm trying to generate a report for one of our departments that pulls data from 3 tables. The basic structure is below: CREATE TABLE `evd_us_county_mio_2018q4` ( `state_code` INT(11) NULL DEFAULT ...
0
votes
1answer
30 views

MySQL shuts downs unexpectedly and can't restart

please I need assistance with this error. My MySQL stopped working and each time I start MYSQL on the XAMPP Control Panel, it initiates an error as shown on the screenshot below. I just reinstalled ...
0
votes
0answers
24 views

Error Code: 1172. Result consisted of more than one row [closed]

create procedure incremental() begin declare id2 int; declare title2 int; select sid,stitle into id2,title2 from stbl; insert into dtbl (did, dtitle) values (id2, title2) on duplicate key update sid = ...
0
votes
1answer
21 views

Planning and Optimizing the table structure InnoDB MySql

I am planning to have an InnoDB MySql table with tens of millions of rows. Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or ...
0
votes
1answer
25 views

Export data from a select join for testing [closed]

I have a Select statment that joins two tables. SELECT [Config].[Id], [Version], [ConfigSchemaId], [ConfigType], [ConfigSubType], [ConfigData].[Data] FROM [dbo].[Config] INNER JOIN [dbo].[...
1
vote
1answer
36 views

UNION or conditional JOIN?

I have two queries that I am joining using UNION. See below query. SELECT * FROM ( SELECT so.`so-no`, so.`so-date`, po.`po-no`, po.`po-date`, si.`si-no`, si.`si-date`, pay.`or-no`, pay.`or-date`,...
0
votes
1answer
30 views

Database Design for Product Promotions

Assume I have a products table with the following columns: id (PK) category_id (FK) color_id (FK) material_id (FK) seller_id (FK) size_id (FK) name price Further assume that there could be 0 or more ...
1
vote
2answers
22 views

having problem in insering date in mysql database

On inserting date in table date is being displayed as 0000-00-00 $content = file_get_contents("http://api.themoviedb.org/3/movie/$id?api_key=c1259239b41c48480d727f85c677fbf6"); $moviedetailsArray = ...
0
votes
2answers
39 views

Composite index of three columns in MySQL and WHERE by 1st and 3rd column. Index usage

Let's consider a table with 3 varchar columns and a composite index on all of them in the order of (a,b,c): CREATE TABLE `test` ( `a` VARCHAR(255) NOT NULL, `b` VARCHAR(255) NOT NULL, ...
0
votes
0answers
11 views

MyBatis nested select composed id with nested clases

I've a complex nested object hosted in multiple tables inside a SQL database (MariaDB, although it is not important for the question). I'm using Tomcat 8.5 and the latest mybatis release to perform ...
1
vote
1answer
40 views

MySQL Triggers not working from PHP page but work for manual DB updates via phpMyAdmin

I have the following two Triggers in my MySQL DB that fire BEFORE/UPDATE and AFTER/UPDATE on the house_report_details table. The first deletes all records in my scratch table tmp_house_rpt_details and ...
0
votes
1answer
37 views

Does this trigger make sense?

I've written a trigger for my database which is supposed to conduct the following business rule: if a customer doesn't have a valid driving licence, they are not allowed to rent a car. They are ...
0
votes
1answer
22 views

MySQL to SQL Server, How to replicate data in a set interval?

What would be the best way to replicate a database with several tables from a MySQL server to a MSSQL server, seeing as they are in different devices on the same network with a set interval of 10 ...
1
vote
0answers
22 views

How to automatically back up local MySQL database to a remote MySQL database [closed]

I have a local instance of a mysql database and a remote mysql database. I want to back up the localhost server to the remote server automatically every night. My applications will be running from ...
2
votes
1answer
41 views

An entitiy with at least one weak entity

I have the following design: There is a list of parents in my database. A parent should have at least one child, and a child must belong to a parent (forget about the husband/wife relations). (a ...
0
votes
1answer
11 views

Find IDs in table where at least two entries exist with that ID and an additional field is A for one and B for the other entry

I have a game where we store user results in a table, similar to this structure: game_id, user_id, score, .... Now our service team would like to find all game_ids where user A and user B played ...
0
votes
0answers
23 views

Encrypting a Database (mariadb/mysql) With Veracrypt

as explained in this question I created a mysql (mariadb) database in a separate volume. In my case it is a veracrypt container. Even after changing ownership of the mounted folder to mysql, and ...
0
votes
0answers
23 views

Accessing contents of a table using primary key of another table [closed]

I have 3 tables -table 1, table 2 and table 3. table 1 contains 03 fields - 'ID', 'Question' and 'Answer' where ID is the primary key. table 2 also contains 03 fields - 'ID', 'Question' and 'Answer' ...
0
votes
0answers
17 views

How to change the Aurora MySQL instance role

I created a Aurora MySQL cluster a few weeks ago. There are two instances in the cluster. One has a writer role and the other has a reader role. AS-IS +----------------...
0
votes
2answers
24 views

create specific DB-Admin Account

is it possible to create in Mysql an admin-user that can modify everything exept the mysql-root user? Thanks, starflighter
0
votes
1answer
25 views

change mysql datatype column from string to json

I have an database column "image" with links to the image of the user on the server. In the next update I need to obtain multiple images, so I'm trying to change the datatype of the column to JSON. I ...
0
votes
0answers
21 views

User host: % vs. blank

I'm currently studying db (MySQL). We were asked to create a user that can access a database from any host. I tried the following: CREATE USER "user"@"" IDENTIFIED BY="pwd"; CREATE USER "user" ...
0
votes
2answers
40 views

MySQL setting that affects memory

I'm trying to setup a linux server with low memory < 1G+- available, so the goal is not to exceed 512mb of total mysql usage. I have below simple setup: [mysqld] datadir=/var/lib/mysql socket=/var/...
0
votes
1answer
20 views

Migration from MySQL to Oracle generates empty tables

I'm using SQL Developer to migrate a database from MySQL 5.7 to Oracle 18c XE. The problem is that the tables are generated but empty, even though I'm specifying that the data should move online. I ...
0
votes
0answers
22 views

MySQL pooled connections and crontab jobs (not reusing connections)

I have several cron jobs running and the task that cron has to do is execute a file that in turn checks and compares crawled data with data in the database, if it exists then the script ends and if it ...
-1
votes
0answers
15 views

Is there any other reason why I can't login to MySQL using SSH on my VPS? [duplicate]

I'm not sure why I can't log to MySQL using SSH credentials. I tried to use the solution in this article but it didn't work. I have already changed the username 'root' to 'jed' and set password. I ...
1
vote
2answers
40 views

User rank based on cumulative rows

UPDATE: Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1 Table structure (some fields are hidden): +---------+---------+------------+-------------+-------+-------+ | ...
0
votes
1answer
21 views

Error Code: 1239. Incorrect foreign key definition for 'foreign key without name': reference and table don't match

I'm making a new database in MySQL in which after making the Department table I receive an error when adding other tables. CREATE TABLE Department (dept_name varchar(20), building varchar(15), budget ...
0
votes
3answers
37 views

How DB index works internally at high level?

I have around 500 GB of data in one table of MySQL which has around 5 billion records. It has around 15 columns. It has index on all the required columns. When I do select * from big_table where ...
-2
votes
0answers
19 views

We want to import a csv file into neo4j. How to go for it? [closed]

We tried importing csv file in neo4j desktop but its not working.
0
votes
1answer
23 views

Upgrading from mysql 5.7 on FreeBSD to mariadb or MySQL8

I am looking into upgrading some major components of a webserver (php, apache, mysql). I've done the update to 5.7 from 5.6. I've updated to php7.3. What I wonder now is: I see that a mysql ...
0
votes
1answer
12 views

List population of provices (SQL Query)

I was asked this question in an interview and I could not get my mind around it, and now it is bothering me a lot! If you have a table CANADA(PROVINCE, CITY, POPULATION). - Population is correlated ...
0
votes
2answers
30 views

Posts, comments, replies, and likes database schema

I have a site where a users can comment on posts or reply to a comment. The user can also like replies or comments. Replies are not nested, so a reply doesn't have a reply. However, there is another ...
1
vote
2answers
37 views

If count >= row then insert row (substitute for variables and else/if)

I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense. So I've got a table that looks something like this: post_id | meta_key | ...
0
votes
1answer
13 views

MySQL - The DBMS must maintain a separate execution domain for each executing process

I'm trying to get a government system hardened and during that process I have to answer certain criteria. One of them is the below:  Rule Title: The DBMS must maintain a separate execution domain ...
0
votes
1answer
37 views

MySQL is not releasing memory after memory tables are dropped

After creating several tables of memory engine and dropping them, mysqld is not releasing all the corresponding memory. Below are the exact steps to reproduce the problem. Memory usage is observed ...
0
votes
1answer
22 views

how the mysql server settings replicatd in innodb cluster

I just started learning about cluster so I don't have much knowledge if question is not appropriate please forgive me. I have a 3 node InnoDB cluster running .suppose if I want to tune my server by ...
1
vote
1answer
31 views

Mysql and money: precision problems with FLOAT(10,2)

I have a table like this: mysql> describe transactions; +-----------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
0
votes
1answer
19 views

Insertion join on two tables [closed]

I'm new to php and mysql, and I created a database where I inserted 02 tables: role (# id_role, slug, name, level) and users (id_user (pk), name, first name, login, password, role_id (fk)). I ...
0
votes
1answer
16 views

Get percentage from results from two case results

How i can get percentage from two resuts from case staments? SELECT *, COUNT(CASE WHEN estado_rep >=11 and RELATION=1 THEN 1 ELSE NULL END) as pendiente, COUNT(CASE WHEN estado_rep >=...
-1
votes
1answer
25 views

How to Insert 0 in the Existing table

table studentinfo: TID BADGEID NAME Location Addr 122 233.01 Katy GA GA-54 123 234.02 tery GA GA-54 I want to insert 0 ...
0
votes
2answers
33 views

Upgrade Antelope to Barracuda on Slave

I received a Last_Errno: 1118, Last_Error: Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row ...
0
votes
1answer
20 views

How to read the MySQL Audit.log file in a SQL table?

I tried to read the Audit.log file of the MySQL Enterprise using a table or other more friendly format and easy to access and understand, but the xml format with which it is written has an irregular ...
1
vote
1answer
18 views

Moving MAX and MIN calculation into a sub query to preserve the full result set in the outer query

The query's objective in brief is to list all the posts according to its score, which is calculated according to a calculate scores of date, views, and rating The problem with the query is that it ...
2
votes
1answer
27 views

How to query all entities associated with all children (recursive) of a node in SQL?

This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary. Ok, I’m having trouble putting this into words… so bear with me. Say I have a tree of ...
0
votes
0answers
7 views

MySql slaves on Amazon EC2 RHEL 7.6 server not getting connected automatically after mysql service restart on master

I have setup MySQL Master - slave replication on Amazon EC2 instance. OS : RHEL 7.6 MySQL Version: 8.0.13 Master server IP : 192.168.1.128 Slave server IP : 192.168.1.129 I have two EC2 instances on ...
0
votes
1answer
18 views

SQL enforce value of a column if other column is NOT NULL

I'm having a hard time to explain this so let me illustrate it with an exemple. I have a table for comments and it looks like so: CREATE TABLE comments ( id INT UNSIGNED AUTO_INCREMENT, ...
0
votes
0answers
15 views

Row level user privileges help with design [closed]

Say there are 2 tables Project and Task. How can I design Privileges table and assign permission like read/write/update/delete for each project/task from the above tables. Should I create two ...
0
votes
1answer
30 views

CASE in WHERE clause in MySQL

I'm working in a project with MySQL and using stored procedures to make my SELECT queries, so all of them have this same structure: DELIMITER $$ CREATE PROCEDURE `case_in_where`(IN `column_selector` ...