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
Make your voice heard. Take the 2019 Developer Survey now

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.

1
vote
0answers
20 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
20 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
13 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
20 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
45 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
35 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
23 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, ...
-2
votes
0answers
21 views

what is the overhead of Mysql database users [closed]

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
21 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
50 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
32 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
41 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
24 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
54 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
61 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
27 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 ...
0
votes
2answers
29 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
28 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
807 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
28 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
21 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
15 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
20 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
26 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
28 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
50 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 ...
0
votes
1answer
17 views

Do I need to `mysql_upgrade --verbose -u root -p` each time when `source backup.sql`?

I am upgrading mysql 5.1 to mysql 5.5 in centos 7 server. As time limit,I only mysqldump and source several small database to mysql 5.5,and then mysql_upgrade --verbose -u root -p,everything is ok. ...
1
vote
2answers
38 views

How to name a constraint which was unnamed during the table creation?

I had created the table by specifying the columns as primary key and not null without giving them any constraint names and I have populated the data. I want to give names to them now. How do I do ...
0
votes
1answer
16 views

Running Mariadb on LXD (using proxmox) vs baremetal

I have benchmarked lxd and the difference has not been more than 2-3% of the bare metal performance. I am using fusion sx300 1.6tb to store a 450gb database with 20b rows in a single table. We have ...
0
votes
1answer
10 views

Run a SQL statement in MySQL on each connection

I must be going nuts here, but I could swear I read about a configuration option in MySQL that will run the defined SQL statement on every successful connection to the database, with the cavet that it ...
1
vote
1answer
8 views

Get related columns depending on a ID

I have a table with a m:n (many to many) relationship. Basically, if I select an article_id, I want to return all the other articles that have the same tag_id as the selected article_id. | article_id ...
0
votes
2answers
27 views

I have connection issue and its changing ip [duplicate]

I use DB 'Mysql 14.14 Distrib 5.7.24' and the OS is 'Ubuntu 18.04' The DB server have local IP : 10.0.211.198 And For The application IP : 10.0.214.198 when I try connect form application server to ...
0
votes
1answer
18 views

How to re-sync the Mysql DB if some Masters have different database incase of MySQL Group replication?

Somehow one of my server got into split brain situation, how can we reset & sync back one of servers's data with another master. i won't mind lossing one's set of new data but i want now that ...
1
vote
1answer
23 views

Error 2013 in MySQL Workbench when I perform any operation on a certain table

when using mysql workbench I perform any operation on the table "my_table" I get an error: Error Code: 2013 Lost connection to MySQL server during query Previously I stopped the execution of a ...
1
vote
3answers
70 views

Is it possible to optimize this query? Currently taking days (no sign of stopping) on ~11 million records

Table in question: CREATE TABLE `observations` ( `time_of_observation` datetime NOT NULL, `station` varchar(5) NOT NULL, `number` varchar(20) NOT NULL, `destination` varchar(100) NOT NULL, `...
0
votes
1answer
13 views

SELECT , GROUP AND DISPLAY IN TABULAR FORM AND COUNTING INSTANCES

Answer to this query was received as - SELECT location, AVG(CASE WHEN qtr = 'Q1' THEN achievement END) q1-average, AVG(CASE WHEN qtr = 'Q2' THEN achievement END) q2-average FROM sourcetable ...
0
votes
1answer
43 views

Help in mysql query

I have a column in database with name paid_amount that have multiple values with a separator like: 1,2,4,10 I need the sum of these values: sum = 17 select sum(paid_amount) from invoices where ...
0
votes
0answers
25 views

What does 'innodb_buffer_pool_reads' and 'innodb_buffer_pool_read_requests' actually mean?

Mysql version: 5.7.18 (Innodb) I have a table with the following specification Structure: CREATE TABLE `dummybigint10` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT 'A ...
0
votes
1answer
19 views

Unable to create an user using mysql_embedded

I am creating a docker mariadb image and wanted to create a database and user name before bootstrapping mysqld daemon. So as per the documentation I tried below queries. mysql_embedded -e "CREATE ...
0
votes
1answer
23 views

Uncertainty about number format in MySQL

In Oracle Database, fields like this: example number(6,4) not null; Indicate that I'm reserving 6 digits for this column, 4 that are being used from the point to the right and 2 for the point ...