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

How can one generate a view, with tallied sums from a secondary view?

I have a view, built to generate a validation report on a mission critical cron that runs weekly. The view show weekly runs, Sum of total accounts credited, ( some credit goes to SO buddies for my ...
0
votes
1answer
13 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
8 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
8 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
13 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
21 views

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
1answer
14 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
5 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
15 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
8 views

Pharmacy database

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
12 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
6 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 ...
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
15 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
15 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
15 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
31 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
16 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
34 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
48 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
0answers
8 views

Setup mysql innodb group replication in AWS with instance from different region

The plan is to have 4 mysql instances, 3 at US and 1 at Asia as emergency backup. The problem is, the public ip address (elastic public ip) in EC2 is not part of the system's interface. When the ...
0
votes
1answer
22 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
34 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
58 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
52 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 ...
1
vote
1answer
40 views

MySQL Slave lagging while executing delete,insert query

I want to know how much memory & CPU core mysql using for executing relay log. MySQL Prod & DR version 5.6.20 Memory & swap output. [![enter image description here][1]][1] [hostname ~]$ ...
0
votes
1answer
25 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
29 views

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

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 ...
0
votes
2answers
28 views

How do I optimize this extremely simple MySQL query?

I am using MySQL 5.7 (can't change this), and I am kind of new to MySQL. I have a fair amount of experience with SQL sever so just trying to work through some of these quirks. The query I want to run ...
1
vote
1answer
18 views

SQL How to show information from others tables in select requet

There are my database user id | user_code | user_name 1 | 1x2d | Peter shops id | shop_code | shops_name 1 | 1234 | shop1 2 | 3456 | shop2 articles id | art_code | art_name 1 | ...
-1
votes
2answers
26 views

mysql trigger data from a many to many relation

I am new to triggers, so I want to know how I could select attributes from 3 different tables to insert them into a new table. I have 3 tables with a many to many relationship: table users wich has ...
4
votes
4answers
799 views

Length of varchar column - is there an advantage to using 255? [duplicate]

I often come across columns that are varchar(255), so presumably this is a convention of sorts. 255 is 2^8 - 1. So is there something 'magical' about lengths that are 2^n - 1? Or just specifically 255?...
3
votes
1answer
25 views

Lock database in time

I'm currently working with prototyping some stuff with a dump of a MySQL (5.7) database where much of the data is time dependent. Most of the queries I write use the NOW() function and the results ...
0
votes
0answers
20 views

Need an efficient mysql database schema to store points/ratings

I have four main tables- 1.user(user_id, name,...) 2. post(post_id, description, ...) 3.comment(comment_id,user_id,comment,...) 4.reply(reply_id,user_id, reply,...) and 5.user_post(user_id,...
0
votes
2answers
14 views

mysql - Right join not retriving correct data

I am storing the user activity in the table by start and end time , now i want to get all records from my subject table and matched records from log table. This is my subject table This is my log ...
0
votes
0answers
18 views

Is ProxySQL Suitable for Connection Pooling Apps?

I'm looking for some guidance on whether ProxySQL is suitable as a method of failing over Java connection pool based apps in a multi-master replication (with read/write split) set up? I've found ...
0
votes
1answer
24 views

How do you create a Geographic Point with MariaDB and MySQL?

PostGIS provides a simple cast to construct a geography type from floating point lat, and long SELECT ST_Point(1,2)::geography; -- standard version (MM) less precision SELECT ST_MakePoint(1,2)::...
0
votes
3answers
15 views

MYSQL: Display NULL value when foreing key is NULL

I have the following database structure in MYSQL: CREATE TABLE xs_bugtracking ( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `product` VARCHAR(64) NOT ...
0
votes
1answer
17 views

How to transfer data from mysql 5.5.24 to 5.1.36

I've had a server that is not booting up, it had mysql 5.5.24. I was able to copy the whole /data directory. I will be temporarily using an older backup server that has mysql 5.1.36 and cannot be ...
0
votes
1answer
22 views

MySql incremental backup of added rows only

How can I create incremental backups of a MySql database that only includes data that is added and ignores data that has been removed? Background here is that I have an instance of home-assistant ...
0
votes
2answers
43 views

MySQL my.cnf recommended settings MariaDB 10.2

I am a beginner managing a server and I have a problem after upgrading from MySQL 5.7 to MariaDB 10.2. Performance usage is very high: 191%. Can somebody help optimize values in my.cnf for a database ...