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

Extreme performance loss due to moving from Mysql 5.7.23 to Mysql 8.0 (status, config included)

Been sitting on this for 12 hours straight (it's noon now, so I will read/respond when I woke up again). I have made the big fault of recommending a database upgrade of our productive environment to ...
0
votes
2answers
10 views

Select first and last row based on each user id mysql

I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead. Input: ...
0
votes
0answers
9 views

Sum multiple rows and return sorted result

I have a database that holds players wins on the game, but as it has multiple arenas, there are multiple win variables too, I tried the following: SELECT winsdm + winsodm + winsrace + winsshooter AS ...
0
votes
0answers
5 views

Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE

I upgraded to MySQL 8.0.13 and tried to add a new field using ' ALGORITHM=INSTANT ' but got the following error " Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=...
0
votes
0answers
14 views

MYSQL - Database check

We just downloaded and installed Mysql 8.0 Community edition. what I am trying to do is check all the tables in the database for errors. I only want output if there are errors. It's like DBCC('...
0
votes
1answer
11 views

Upgrade/Transition Path from MySQL Community 5.6.31 to Enterprise

Full disclosure. I have never upgraded a database. I have a MySQL Community that is not the latest @5.6.31 ( latest is 8 ?) to the paid, Enterprise version. We will ask Tech Support once purchase of ...
0
votes
0answers
25 views

Error #1064 when executing a SELECT query

I'm new to PHP and MariaDB and ran into a little trouble with a learning project I'm working on. Whenever I try to execute the query SELECT c.complaint_id, GROUP_CONCAT (DISTINCT ...
0
votes
0answers
16 views

Mysql concat issue [on hold]

I have a MySQL database and and I'm using CONCAT to join a variable and a column in a select statement. However, it appears to be adding to the previous column. The output is similar to below. ...
0
votes
1answer
11 views

How DML queries execute in MySQL InnoDB

I want to know if I fire query like insert into emp ... What are the steps involved in query ? It will go through InnoDB buffer pool? I know how select query execute in MySQL when MySQL uses default ...
0
votes
1answer
35 views

Selecting rows from one table not in another table

I have two tables table_a_mod (2 million rows) table_a (2 million rows + new live data) table_a_mod is identical to table table_a except for two extra columns table_a is a "live" table with data ...
0
votes
0answers
15 views

error code 1054, Unknown column coffee_origin in field list [on hold]

What am i doing wrong , please? INSERT INTO products(name,price,coffee_origin) VALUES('espresso',2.50,'Brazil');
0
votes
0answers
15 views

Proper way to fully backup and restore a database including indexes in a low-resource shared web host with ssh access?

I'm rather new to database management and I would like some advice on better practices by using the following experience as an example. Every time I update php software I do a quick export from ...
0
votes
0answers
15 views

SSAS error - invalid attempt to read when reader is closed

I'm trying to run my SSAS project but I got an error. I'm using a MySQL database as my data source. I can connect and build the view, but when I create a dimension and I try to run it get this error:...
2
votes
0answers
29 views

How To Query Databases in Subject-Predicate Format?

I have csv data in this format: id fieldname fieldvalue -- --------- ---------- 1 fieldA A1 1 fieldB B1 2 fieldA A2 2 fieldA B2 If I upload this to MySQL, how can I ...
0
votes
1answer
18 views

Store Function Value into temporary table

Hi i'm currently trying to run a SQL query on a 15 000 + entry dataset. I'm using a custom Mysql function to determine the start of the current week and the end of it. Probleme is, I'm using those ...
0
votes
1answer
11 views

Getting aggregate sub-values in aggregate query

I have a query that need to show aggregated values in an already aggregated outer query. Consider a query that fetches total count of accounts being credited on a certain day. I would like to ...
0
votes
0answers
15 views

Combining 2 mysql instances into 1 without downtime

Can I use replication from one database server to another and then start using instance for production and then import other database server into new instance and then use replication to catch up and ...
0
votes
0answers
17 views

is there Better way to do this kind database schema design, mysql

P.S. Hopefully I am not posting in the wrong place. If I am, please let me know where to post. I am currently using laravel with query builder instead of eloquent for a small self project. I am ...
0
votes
1answer
35 views
+50

How can I use mysql to determine the Sundays for the last year?

I have a table where I'm currently aggregating ~200 rows per day of stats. These stats include things like # of users, how many people using X feature, etc. What I'd like to do is query this table ...
0
votes
2answers
29 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
0
votes
1answer
13 views

cannot create a procedure with error [1064] [42000]

I am using mysql 5.7 and I have some code which works. But when I try to create procedure with the code, it reports error [1064] [42000]. I try to update the content of an old procedure with my ...
0
votes
2answers
17 views

How can one calculate percentage from previous week in aggregate query?

I have a challenge I set out to do that seemed initially trivial. Not so for my developper brain. Consider the following simple view, used to validate a cron I wrote that queries a subset of 200 000 ...
-1
votes
0answers
10 views

Pharmacy database [on hold]

I am developing course project web application that is purposed for Pharmacy, it's hard for me to analyze and design pharmacy db. so i need your help.
0
votes
1answer
14 views

Select the value of max(date) and id with mysql

I want the last dated record. But my query doesn't work. Where am I doing wrong? Example data: |-------------------------------------------------------------------| | id | messagges | ...
0
votes
0answers
10 views

MariaDB 10.2 crash on innodb table corruption - trying to restore the database

I'm running MariaDB 10.2 on Windows and had some hard drive corruption on my server that messed up some innodb tables. Unfortunately this also affected backups, so I'm trying to dump the tables and ...
2
votes
1answer
5 views

Does mysql configuration options accepts - instead of _?

Mysql 5.7 Documentation mentions that innodb_buffer_pool_instances can be set in /etc/mysql/mysqld.conf.d/mysqld.cnf as follows: [mysqld] innodb_buffer_pool_instances = 16 However, an existing ...
0
votes
0answers
15 views

How to ignore errors in mysqldbcopy

Is there an option to ignore errors and continue copy in mysqldbcopy
1
vote
0answers
17 views

MySQL FullText Search returning Zero rows

I have a query that works really good on searches in my table. However, I came across this certain search that resulted in no rows, even though I just added these rows and I know they are there (about ...
0
votes
2answers
18 views

Upgrading 5.6 to 8.0 without losing any data

I am using windows server. As per need of project I need to upgrade MySQL from 5.6 to 8.0 but I already have the existing databases and tables. How should I upgrade without losing any data in windows?
0
votes
1answer
12 views

Time addition based on previous column partition by different object

I asked a question on Time addition based on the previous row. Now I have another one related to that question. Appreciate if someone could give me some help. I use the following code to create the ...
0
votes
0answers
18 views

Help with high load MySQL configuraiton

Server: Disk: 2X 500 SSD RAID 1, 3 TB HDD RAM: 256 GB DDR4 CPU: Intel Xeon 10 core Database: Percona for MySQL 5.7.23 Average number of active connections: 1700 I have 4 schemas, and ...
2
votes
0answers
40 views

Recursive query

I'm not sure if I need exactly a recursive query but this is what I'm trying to do. This is my Minimal Complete Verificalbe Code: I have a similar table like this (MySQL v5.7, InnoDB): CREATE TABLE ...
-1
votes
1answer
34 views

Can I INSERT the result from a query INTO a new table with a TRIGGER?

I have a table named Stadistics which contains the data shown below: date hour num_prodcuts -------------------------------- 2019-01-16 12:42 3.2 2019-01-17 13:40 ...
0
votes
1answer
20 views

operand should contain 1 column select query mysql

SELECT events.*, location.*, provider.name as provider_name,provider.name_ar as provider_name_ar, category.name as category_name, category.name_ar as category_name_ar, sub_category.name as ...
1
vote
1answer
35 views

Time addition based on the previous row

I appreciate if someone could give me some help. I would like to deal with time addition based on the previous column using mysql. Here is what I have. create temporary table test(id integer, ...
-1
votes
0answers
19 views

what is the overhead of Mysql database users [on hold]

i have one application where we wish to create new connection for all users so everything is new for all means , new database, new mysql users everything new but is it a good practice. we are doing ...
-3
votes
0answers
18 views

How to get rolling 12 months data in Athena

I have a database like sno product date sale 1 - 2019-01-16 10 2 - 2019-01-15 20 3 - 2019-01-01 23 4 - 2018-12-29 12 and ...
1
vote
1answer
49 views

MySQL Crashed, innodb trying to access page number which is outside the tablespace bounds

I am running MySQL (5.7) on a Windows 2012 server. One of the table (> 1 million records) seems to be corrupted. The mysql error log shows 2019-01-16T09:41:09.256990Z 0 [Note] InnoDB: Buffer pool(s) ...
0
votes
1answer
23 views

need help in the update query

table name:- **t_ci** s39 column_name 176 i want to have my table like this s39 column_name 176 176 But this query works UPDATE t_ci SET s39=s345 WHERE blueprint_id=1234; But I ...
0
votes
2answers
35 views

How full is my innodb shared table space?

How can I find out how much data is in the innodb shared table space. I have a massive shared idb file but I suspect that it contains only about 350 Gigs of data. I need to know how full it is so I ...
0
votes
0answers
29 views

Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design?

I have a functionality that has multiple (7+) potential properties to be added via a central 'hub' table. In most cases there will only be one property assigned, but it's possible the user will want ...
0
votes
1answer
40 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
-1
votes
1answer
60 views

Speed up LEFT JOIN with OR operator in ON clause

I have a query that uses a LEFT JOIN with an OR operator within the ON clause. When commenting out the OR operator the query runs in ~150ms. With the OR operator kept in the query takes over 80s to ...
0
votes
2answers
33 views

MySQL insert with value derived from primary id

I'd like to INSERT into a MySQL table, in such a way that one of the columns holds a value derived from the primary id. For example: insert into picture_table ('picture name','folder') values ('...
0
votes
1answer
23 views

Why are bulk multi-column key queries so slow in MySQL?

(For this question, I am using AWS/Aurora MySQL with a reasonably-spec'd RDS instance) Consider the following schema: Table T: col0: the usual autoincrement primary key col1: varchar ...
0
votes
4answers
53 views

Group Replication plugin crashes MySQL 8.0

Problem description: INSTALL PLUGIN group_replication SONAME 'group_replication.so'; results in ERROR 2013 (HY000): Lost connection to MySQL server during query Specs: MySQL version: 8.0.13 ...
0
votes
1answer
33 views

Is it safe to use pt-online-schema-change in a multimaster environment?

I have 2 MySQL servers with row-based replication between them. Both of them are masters and slaves for each other (active-active master-master setup). If I understand it correctly pt-osc creates ...
0
votes
1answer
59 views

MySQL Slave lagging while executing delete,insert query [on hold]

I want to know how much memory & CPU core mysql using for executing relay log. MySQL Prod & DR version 5.6.20 Thanks for your support. We are facing delete query executing on MySQL slave ...
0
votes
1answer
26 views

Problem with creating foreign key in database, errno 1239. Incorrect foreign key definition

I'm getting "errno 1239. Incorrect foreign key definition" when creating this table. Can someone explain why this isn't working? Here is my code: CREATE TABLE IF NOT EXISTS `game_review`.`game` ( ...
-1
votes
1answer
30 views

Load a database into a host or in place to which I can access from different applications [closed]

I've just started working with databases. I create them and modify them using Python as a means to create dinamically a good amount of data. Moreover all the applications that I develop are written ...