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.

Filter by
Sorted by
Tagged with
0
votes
0answers
4 views

Unable to change root user unix_socket to mysql_native_password

I have installed a LAMP stack on my Debian 9 machine and can not log in with the root user. From my searching, it seems that it's because the install commonly creates a root user without a password, ...
0
votes
3answers
13 views

How to SELECT from three different tables using multiple foreign keys

I have 3 tables: languages id | tag | region | language -- ----- ------------- -------- 1 en-US United States English 2 es-ES Spain Spanish ... modules id | ...
0
votes
0answers
14 views

Mysql : how to use column name as a filter in subquery?

I cannot find the right syntax to use a column from a table in a subquery. In the following query, I want to make a sum of DISTINCT value from a subquery. That results in an error : Unknown column wo....
0
votes
0answers
12 views

How is it possible to model in a database many different products?

For example, a smartphone might have attributes such as RAM and camera resolution, whereas a fridge has way different attributes. Is there a way to store these data in a relational database?
0
votes
1answer
17 views

MySQL: why can't I insert this value using timestamp but can with datetime?

I want to insert this value: 2020-03-29 01:00:00 If I set up a column called date with data type datetime I can insert the value. But if I use the data type timestamp I cannot insert the value and ...
0
votes
0answers
10 views

mariadb 5.5 innodb keeps opening files up to 200k until system hangs completely

Mariadb 5.5 is installed on CentOS and serves a database in the total volume of around 40GBs (as measured per database files on a disk) and 506 tables. This database is being queried by php-fpm, and ...
-1
votes
1answer
25 views

How to insert data in column based on data from other two column in mysql

I have 3 columns on table named loan. first column named approved_by_asm which holds integer value 0,1 or 2 second column named approved_by_rsm which holds integer value 0,1 or 2 and third column ...
0
votes
0answers
9 views

Structure of a project management database

I will do my best to explain my problem as precisely as possible but please feel free to ask questions if anything is unclear. I want to create a DB where Projects are billed. In general there are 2 ...
-2
votes
1answer
22 views

Merge Multiple Selects in one request

I have to extract a data using selects statements based on same conditions with a little change, I would like to make union for all my select in one statement: select Date_, application_name,count(...
-1
votes
0answers
11 views

My SQL Query getting hanged in linux setup of mysql [closed]

ALTER TABLE `table_name` ADD `Table_Column_2` INT NOT NULL DEFAULT 1 AFTER `Table_Column_1`; I am working with Alters, Executed this query on Localhost (windows setup MySql) successfully, When it ...
-1
votes
0answers
7 views

View individual tables by iterating a table list [closed]

Retrieve the tables with: #+begin_src sql :engine mysql :dbuser org :database grocer show tables; #+end_src #+RESULTS: | Tables_in_grocer | |------------------| | Customers | | OrderItems ...
0
votes
1answer
28 views

One query should be better but I can't see the performance improvements

I've been tasked with optimizing queries. I have the query below SELECT vd.video_id, vd.video_name, vd.video_title, vd.video_description, vd.video_duration, vd.logo, vs.balance_mobile ...
0
votes
0answers
24 views

DML vs DDL for Live Databases [closed]

I have been trying to play catch up to make sure that I am making changes to my databases/tables correctly for live environments. My concerns stem from attempting to add a column to a live/online ...
0
votes
0answers
13 views

Galera cluster problem after full crash with mariadb

I have a galera cluster with 2 mariadb nodes running. I know that this isn't best practise but I hadn't a chance to do it in another way. I had a crash and needed to reinitiate the cluster on one ...
-1
votes
1answer
20 views

Inconsistent Mysql Query Result on left join [closed]

I combine 2 tables with left join. Main table is tbl_agro_exploatare and following is the list of the rows ID PARCELA_ID CAMPANIA_ID CULTURA_ID FARMER_ID 1 183 3 1 ...
0
votes
2answers
16 views

Ram & Concurrency: MySQL InnoDB LOAD INLINE FILE on 50 30GB CSV files

As the title suggests I'm moving around a lot of data. This is in an environment with a 24 core CPU and 2TB of M.2 storage. My first question is: given I have 16GB of ram, does that mean it's ...
0
votes
1answer
23 views

Inserting data into another table and getting it back into an array field in mysql

I have two tables Products table Products Table CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `description` varchar(255) NOT NULL, `created` ...
2
votes
3answers
46 views

MySQL query very slow with multiple concurrent users in a short time

I have a site with a MySQL 5.5 database where most of the traffic happens at a fixed time every single day when thousands of users are loading the site in a period of 2-3 hours. My slow query log (...
0
votes
1answer
39 views

Designing an ad banner tracker

I'm creating an ad banner tracker for the ad banners on my website by using PHP. The tracker would track unique impressions, total impressions, unique clicks and total clicks of each banner. The ...
2
votes
1answer
29 views

Get first part of result faster in sorting query on hundreds of millions of rows?

I have a single table containing around 280 million rows in MariaDB 10.4.10. I need to process this full table in an external program, sorted by timestamp. The external program is fast; the largest ...
1
vote
2answers
21 views

How to get sum of 3 sub count queries and group them using an attribute

I have 3 queries as follows: For the date 2019-12-02, I am calculating 3 type of values. The total value for 2019-12-02 must be the sum of these 3 queries. Q1 SELECT count(*) FROM ...
0
votes
1answer
22 views

Converting a subquery to a join

From this page - https://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html - it states that the following query: SELECT * FROM t1 WHERE id IN (SELECT id FROM t2); can be converted to an ...
-3
votes
1answer
42 views

From a backup strategy perspective, would it be efficient to use a relational DB (as MySql) and a document oriented DB (as MongoDB) in the same app?

I am developing an app where I need to store an undetermined number of properties for any given entity, for example: Entity || # of properties** Apples || 14 Oranges || 4 Pears |...
-1
votes
2answers
32 views

Document vs Relational Database for storing large amounts of low usage data [closed]

I have about 10,000 different datasets. These datasets vary in size and columns, they don't have a command structure. The datasets have between 500 to 10 million entries, each entry has about 20-40 ...
0
votes
1answer
30 views

Find employees latest activity is slow when adding ORDER BY

I am working on a legacy system in Laravel and I am trying to pull the latest action of some specific types of actions an employee has done. Performance is good when I don't add ORDER BY. When adding ...
0
votes
0answers
15 views

migrating data from mysql to postgres pgloade IRONCLAD::SIMPLE-OCTET-VECTOR

I am migrating data from MySQL to Postgres using pgloader. My script works fine on my local machine. But as I am running pgloader to prod database it's giving me following error The value of SEQUENCE ...
0
votes
0answers
29 views

How does MySql database handles multiple update operation

I am new to database and the way they work so my question might be a bit dumb, please be nice. I have always been thinking about how database update work in general and I think I understand the ...
0
votes
1answer
25 views

Capturing errors in a transaction and rollback - declare not valid at this position

Trying to craft a transaction from the code to send to MySQL. START TRANSACTION INSERT INTO EMPLOYEE (`firstname`,`lastname`) VALUES('john','Doe'); SET EMP_ID = last_insert_id(); //do some ...
0
votes
0answers
19 views

How to export MySQL Routine Information? 3rd party tool? [closed]

So apparently only the $2,000 SE version of MySQL Workbench has built-in technical documentation exportation, so using the CE version of MySQL I need to find an alternative solution that will export ...
-6
votes
0answers
28 views

Whar is db user and db password? [closed]

I find root as username many a times. Is it a global user? How to create a user for sql ?
-1
votes
0answers
29 views

Error running mysql_upgrade. Version check failed. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [closed]

I'm logged in using WinSCP with private key. I'm running mysql Ver 15.1 Distrib 10.4.10-MariaDB, for Linux (x86_64) using readline 5.1. I'm getting the error, Version check failed. Got the ...
-3
votes
1answer
28 views

Transfer Mysql table data to client Server via TCP/IP by using Trigger [closed]

i Have Database on MySQL Server. I have one Table so called "log" in Database. Data has been insert in Database table automatic. I would like to transfer table via TCP/IP to the another Software. ...
0
votes
2answers
28 views

Worth Splitting MySQL Server Into 2 MySQL Servers To Increase Performance

Context I am using an AWS RDS server to host my MySQL DB. I work for a footfall company, we have our system installed all over the UK and every time a visitor is seen we write to the DB. We also ...
0
votes
0answers
6 views

Release_Lock return 0 even when the session has it

I have a strange issue in my application. I develop an application that uses a server lock in a MySQL database. I obtained the lock in the database but when I am going to release the lock, sometimes ...
0
votes
2answers
31 views

How to compare one field in mysql with all fields in database? [closed]

How to compare df1 with all the fields in database order by "id" to check whether df1 is equals to df2 or not? import MySQLdb import datetime df1= (39, 'black', '34533545645667786', '234', 5.0, ...
0
votes
1answer
28 views

scheduling medical appointment challenge

I'm working on a medical appointment scheduling system project. What is the best way to get the first free time of a doctor and schedule it for the patient who just asked for an appointment? ...
0
votes
1answer
20 views

How to store JSON data from APIs and be able to search efficiently

I am displaying documentation using Swagger, but the documentations are long so I made a side menu and split up the data in the database. Splitting it up, it allows me to search for specific areas and ...
2
votes
0answers
39 views

MySQL performance degradation with heavy inserts

I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
0
votes
2answers
73 views

High RAM memory usage

I'm currently testing a new MySQL server, but I'm new in this so I'm not sure about my parameters, because the RAM is always above 90% of usage. The server will be setup for a CRM environment of 100 ...
1
vote
0answers
24 views

Can You Verify the Health of an Offline MySQL Database Before Starting Services During a Version Upgrade?

Our product has MySQL embedded within it which is deployed by our installer. During upgrades of our software the version of MySQL may be updated as well. Recently we had a defect in our stack where ...
0
votes
1answer
30 views

MySQL Trigger: Insert depending on NEW.column of type enum

I am trying to add a trigger to one of my tables. Depending on a enum-value of the newly inserted row it should execute a different insert-query. I am using phpMyAdmin and am trying to execute my ...
0
votes
2answers
37 views

How to write SQL queries to populate the tables while synchronizing transactions and account balance?

I have two tables having the following schema. CREATE TABLE users( `id` INT AUTO_INCREMENT, `balance` DECIMAL(10, 2), PRIMARY KEY(`id`) ); CREATE TABLE user_transactions( `id` INT ...
-1
votes
4answers
35 views

Difference between WHERE clause and HAVING clause in MySQL

How do understand if I should be using a WHERE clause or a HAVING clause with a GROUP BY clause? I've read that WHERE clause is used when we want to place conditions on individual rows and HAVING ...
0
votes
2answers
17 views

How do retrieve unique rows where all all columns are the same except id

So it looks like for some entries to the table the data is being written multiple times from our system, so all the rows entered for a specific data item are written 3 times, with exactly the same ...
-1
votes
3answers
27 views

I need to migrate a db to a new db with foreign key. How do I check for constraint errors?

I need to migrate a lot of mysql MyISAM table to a new InnoDb version, adding foreign key constraints. I can dump and import, disabling first foreign key checks. But there is a way to check which ...
1
vote
1answer
17 views

Insert as many rows as count on other table returns

Hello I have three tables in my MySQL-DB Project: id | name 1 | project1 2 | project2 KPI: id | name 1 | build_status 2 | satisfaction ProjectKPIData: id | kpi_id | project_id | value 1 | 1 ...
0
votes
0answers
15 views

Re-Generate cumalative totals for double entry accounting records stored in a table

I have a customer_transaction table as below. CREATE TABLE `customer_transactions` ( `id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `transaction_type` varchar(25) NOT NULL, `...
0
votes
1answer
21 views

What is “Init for Update” thread state in mariadb?

Our production server gets stuck on Init for update state whenever we start a query like update <some_big_table> set <primary_key> = <some_sequence>.nextval order by ...
0
votes
1answer
16 views

Does range improve match operation and which runs first, range or match

I am using MySQL version 5.6.10 I have a very very large table large_table and a relatively small table small_table The small_table has only the records of the date 2019-11-20 There is a column I ...
1
vote
1answer
37 views

MySQL Workbench, Windows Server 2019, MySQL not showing Performance, Information of MySQL Schemas!

This evening I installed MySQL on a new clean install of Windows 2019 server. When the install finished, the installer offered the possibility of opening Workbench so I said yes. Then I noticed that ...