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
1
vote
1answer
28 views

MySQL Select previous row that matches condition

I am using MySQL 5.7 and I have 2 tables where relevant data looks like this: SQL was exported using mysql workbench, let me know if it doesn't work or if it doesn't make sense: CREATE DATABASE IF ...
0
votes
1answer
20 views

How to set up mysql docker container with existing ib* files?

Related to a previous issue, I'm trying to recover some data from existing ib* files from a crashed server. The database version is 5.1.69, so quite old. One of the responses was to install MySQL 5.5, ...
0
votes
2answers
28 views

mysql - adding alias columns as a new column

I have the following mysql query... select x.timest, max(case when x.devloc='outside' then x.value end) as outside, max(case when x.devloc='hvac_main_return' then x.value end) as ...
1
vote
2answers
54 views

How to record prices for the product sales?

I'm building a database for daily sales report. For example: 29/01/2021 -> 50 apples were sold 30/01/2021 -> 46 apples were sold I have these tables: products id INT AUTO_INCREMENT PRIMARY KEY ...
0
votes
1answer
42 views

MySQL - GROUP BY with JOIN query

I am using MySQL 8.0.23 I have 4 tables: houses, intervals, discounts, and availability. Houses: create table houses ( id int auto_increment primary key, name varchar(255) null, ...
1
vote
1answer
36 views

How can I connect Microsoft SQL Server to MySQL

I have a SQL Server running on a local server that is connected to a POS (Point of Sale) system. I want to create an online application to enable online sales of the products in the POS system. I'm ...
1
vote
1answer
30 views

Query to compute unspecified values in “Other” category

The problem that we are trying to solve is explained herein using a simple (and deliberately sub-normalized) table. The table is defined as follows: create table expense_profile ( id INT UNSIGNED ...
1
vote
1answer
65 views

Does PostgreSQL handle case-insensitive database as good as others? [closed]

First things first, please forgive me if my question seems so naive (I am not a DBA). I look up to see how can I make PostgreSQL case-insensitive and I found the Nondeterministic Collations section of ...
-2
votes
0answers
10 views

MySQL update two columns via private/foreign key [migrated]

I have a table to log activities of users, and another table for users to track their current status. Table AccessLog has two fields from the users; as sometimes I get UserID sometimes UserEmail. Is ...
0
votes
0answers
11 views

Performance Schema results display intermittently

Running queries against the performance_schema MySql tables and I'm finding that query results display intermittently. The user I am logged in with has select permissions. Another developer, with a ...
1
vote
0answers
13 views

Query optimalization - how create proper composite index

I have mysql 5.5 and table item have around 1-3 milions rows. CREATE TABLE `item` ( `Id_item` int(11) NOT NULL DEFAULT '0', `item_Kod` varchar(15) DEFAULT NULL, `Date1` datetime DEFAULT NULL, `...
-1
votes
2answers
17 views

Create a trigger for a table whose values are dependent on another table

I have a hospital database and in that database I have an Appointments table and a Visits table. In the Appointments table I have a column named Validation that can have values 0 and 1. I want to ...
1
vote
2answers
27 views

Is it possible to have 1 slave server with multiple master?

Here is my set of master server: DbServer1 - 10.1.0.1 DbServer2 - 10.1.0.2 DbServer3 - 10.1.0.3 DbServer4 - 10.1.0.4 and my replica server would be on 10.1.0.5 Is this doable if I create 4 instances ...
0
votes
0answers
27 views

Should a mysql slave answer queries much slower then the master?

I am seeing a slight issue on a mysql slave server, or at least on the surface it appears there might be an issue. Both mysql's are virtual machines The master and slave are the same OS (debian10) and ...
1
vote
1answer
54 views

MySQL database backup stuck on 'checking permissions'

I'm trying to backup a database on local machine. But the file is only 30MB after hours. Then I thought the hard drive corrupted or something that I tried this on another table and it worked fine. ...
0
votes
1answer
19 views

Speed-up lookup of a row with prefix pattern

Say I have these rows in one UNIQUE column: 202101XXXXX 202101XXXXX 202102XXXXX 202102XXXXX 202103XXXXX 202103XXXXX The first 6 digits are year and month (YYYYMM), and the XXXXX part are random ...
0
votes
2answers
35 views

Database design for bigger tracking table

I am creating a database for a prayer tracker. There are 5 types of prayer for each day. I have a table called "Types" id, name 1, Type 1 2, Type 2 3, Type 3 4, Type 4 5, Type 5 I want to ...
-2
votes
2answers
31 views

Filtering Condition in n-Table Joins

We seem to have a need for a multi-table JOIN operation and I am referring to some notes from an RDBMS class that I took several years ago. In this class the instructor graphically depicted the ...
0
votes
1answer
42 views

Amazon RDS Slow Query Log Does Not Show SQL Statement

I enabled the slow query log on Amazon RDS and I executed a "select sleep(5)". But when I look at this table, how is it that sql_text does not show the actual SQL statement? mysql> select ...
-1
votes
1answer
50 views

How can I model this schema in MySQL?

I asked this in the DevOps forum, but this is probably a better one for this question. I want to create a schema that reflects the following relationships, and I'm trying to do this in MySQL Workbench....
0
votes
0answers
6 views

Restore specific date from mysql dump file

i have a big mysql dump-file since 2015 i want to restore all data form 2020 and 2021 Note : schema have a lot of tables and every table have it's own data and we didn't want all table we need only 3 ...
0
votes
2answers
48 views

MySQL server will not start/stop

I am currently in the process of setting up a database in MySQL for an application where I will be running PowerBI to do analyses on the data in the database and in a further stage to design an ...
0
votes
1answer
10 views

mysql master slave replication on same machine - Can log_bin need point to the same location on both instances?

I have been trying to setup a mater-slave replication on the same machine using mysqld_multi and it does appear to be working, but I have a few questions which have never really been an issue before ...
0
votes
1answer
14 views

Powershell script while executing mysqldump script

I'm having an error 1045: Access denied for user 'mysqlsuperuser'@'test.internal.cloudapp.net' (using password: YES) when trying to connect Here is the mysqldump command on my ps1 file. $command = [...
0
votes
3answers
35 views

Get three last movements of the per ID with LAG and GROUP BY in MySQL

I hope someone can help with this query, I have a requirement for a query that groups the id of the last date, but I want to have three columns with the 3 last movements. So I tried grouping by the id ...
0
votes
2answers
28 views

can I select columns based on their name in Mysql?

is there a way to select columns using '%' or 'like'? for example: I have a table with 20 columns , and 4 of them are starting with "GPS_"(GPS_long,GPS_Lat,GPS_sat,GPS_Speed) I want to run ...
1
vote
0answers
45 views

Syncing to a database behind a gateway

On our network we have a tunnel to a NAT gateway host. From the gateway host we are able to access a private network. On this network we will be running jobs that log to a database. The database ...
2
votes
1answer
70 views

Server quit without updating PID file when trying to start MySQL server?

So I created my .bash_profile, opened it in a text editor and added: export PATH=$PATH:/usr/local/mysql/bin and then I saved it. Next, I tried running sudo /usr/local/mysql/support-files/mysql.server ...
1
vote
2answers
81 views

SQL Error [1292] “Truncated incorrect INTEGER value” - with Varchar columns and CASE. Why?

In a MySQL 5.7 environment, when trying to insert a varchar value into a varchar field, I get the error: SQL Error [1292] [22001]: Data truncation: Truncated incorrect INTEGER value: '1876301/347' ...
0
votes
0answers
24 views

azure mysql database replication with on-prem mysql master is failing with 2003 error

I have setup azure mysql master database and I would like to do data-in replication with an on-prem mysql database but the replication is failing. Can someone tell me the reason ? I am able to connect ...
0
votes
0answers
10 views

MySQL Performance schema : CURRENT_NUMBER_OF_BYTES_USED high for disconnected user

I'm investigating memory usage on one of my MySQL servers. I can see a continually increasing memory usage according to the stats in performance_schema when looking at the data in the ...
1
vote
3answers
46 views

Many MYSQL processes on Linux

Recently I deploy my parser on server and sometime server start reboot from out of memory, i spectate for memory and find something, exactly after every using parser, (one time on day) ram memory ...
0
votes
1answer
31 views

Getting only 4 rows per idea from ideas table [closed]

i have a table of ideas that stores responses from evaluators and stores as one row per evalautor For ex : Evalautor 1 evaluated idea A then in table record gets added as IDEA A, COMMENTS , EVALUATOR ...
0
votes
3answers
15 views

Get the ID of a next greater/less Value

imagine you have follwing table given id value 1 5 2 3 3 7 4 1 Depends on your case you need the ID of the next smaller or greater value. So the select of ID=2 would give u either 4 or 1. How ...
0
votes
0answers
23 views

TradeDate import tinytext how to show Date Format 'yyyymmdd' of 20210111 or 2021-01-11

OS: openSUSE 15.2 Wish to use TradeDate in Date Format 'yyyymmdd' eg 20210111 or 2021-01-11 Column TradeDate imports ok as tinytext When select find TradeDate column not found how to sort into rising/...
0
votes
1answer
23 views

Troubleshooting “Can't connect …” from MySQL slave to master

Although I can connect from slave to master via a command-line mysql client, my slave can't connect to the master as part of its replication task. SHOW SLAVE STATUS cites a "Permission denied&...
-1
votes
1answer
41 views

Strange COUNT(*) issue

I have the following SQL statement: SELECT COUNT(J.id) as total, (3959 * acos (cos ( radians(J.lat) )* cos( radians( UserM.userLat ) )* cos( radians( UserM.userLng ) - radians(j.lng) )+ sin ( ...
1
vote
3answers
92 views

Why MySQL 5.7 replication is verry slow on my new server

I have a problem with MySQL replication and I wanted to find advice or information on it. Let me show you the infrastructure. Master: This is our main MySQL server. I want to replace it by Slave B. ...
-1
votes
1answer
38 views

why is mysql not using the composite index on my query?

Here is my query. select user_id, sum(user_points.points) overallpts, username from user inner join user_points on user.user_id = user_points.user_id where user_points.date >= '2020-01-01 00:00:00' ...
-1
votes
1answer
21 views

Hello I want to create a before insert that looks if the value is on another table before inserting [closed]

I have a database for hospitals and I have a table for patients and one table for appointments. I need to create a trigger that if someone wants to add a person on the appointments table that person ...
0
votes
2answers
99 views

MySQL too many connections on shared server

We have run a PHP/MySQL blog site on a shared server for years with no problem (my code not Wordpress). I'm not an system or database guy except for what I need. Our host (BlueHost) moved our site to ...
0
votes
2answers
34 views

Anonymous and Public User Table Design in twitter like application

I have schema design problem which I am not 100% sure if my assumptions are correct. So the requirements are: I need to have 2 different type of user, anonymous and public. A user can have both ...
1
vote
1answer
25 views

Mysql get records more then 3 in interval of 1 minute

I currently developed a cron that runs every 1 minute to analyze the last 60 seconds of the bot records in database, I need to group the conversation ID's that have more than 3 records within 60 ...
0
votes
1answer
17 views

is it possible to generate create index sql in MySQL 5.7

I have a table have many index, now I want to move the index to another table. Is it possible to auto generate the index create sql using SQL command? so I do not need to generate index by myself(...
0
votes
3answers
38 views

Postgres psql requires “ FROM database.table” in sql statement [duplicate]

As a beginning learner of of Postgres I have created two different installations of a database where one requires a select statement to use ...FROM database.table... and the other only requires ......
-4
votes
1answer
37 views

Query Optimization for Subquery

Please help to optimize below MySQL query SELECT * FROM ( SELECT u.name,u.avatar,u.occupation, (SELECT GROUP_CONCAT(revenue)FROM LOGS WHERE user_id = u.id AND TYPE ='conversion') AS ...
0
votes
1answer
21 views

Insert INTO a table combining * with a column value

In MySQL, I am trying to insert all values from table A to table B, but I need to change the structure of table B and a column where all the values would be 0. Is it possible to mix and create such a ...
0
votes
1answer
40 views

Why do I have to set MySQL user password with mysql_native_password to connect with Workbench?

I've noticed if I set the mysql user's password like below, I cannot connect with MySQL Workbench. ALTER USER 'root'@'localhost' IDENTIFIED BY 'myPassword' If I use this below I can connect to it: ...
0
votes
2answers
61 views

How should I model my database - cannot decide on multiple options

I apologize if this might be a rather beginner-level-question. For my database design, I have worked out multiple options but cannot decide on which one I should implement. I am using 10.4.17-MariaDB. ...
0
votes
0answers
22 views

mysql partition by date and id

At the moment I'm partitioning my table daily by PARTITION BY RANGE COLUMNS(date) On a daily basis I load data into that table, I have billions of rows in that table. I was thinking on switch to ...