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 ...
4
votes
1answer
293 views

MySQL won't use an index on an int, if I provide a string

Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log: # Time: 130904 12:19:59 # User@Host: cron[cron] @ [10.71.41.38] # Query_time: 58.488760 ...
0
votes
2answers
7k views

JSON array format in MySQL not working?

What is the proper way to do a JSON data array in MySQL? Following the JSON.org example http://json.org/example.html does not work: insert into zz_TEST_ObsJSON (jsonData) values ('{"Test1":{"Val1":"...
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
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
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
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
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 ...
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
237 views

MySQL - Grant user permission to create table as long is it has a prefix in the name

I'd like to grant a user permission to create tables in a database so long as the table has a particular prefix, let's say example_. I've tried the following: $ mysql -u root -p mysql> CREATE ...
1
vote
1answer
231 views

CONCAT() between different alphabet echoes out '&' ampersand

I want to concatenate different alphabet strings and get a weird latin encoding with ampersands after the sixth greek character (I want to concatenate greek and english alphabet strings). Lets say I ...
0
votes
1answer
14 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
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
244 views

Incremetal backup using mysqldump in MySQL

I wanted to take an incremental backup using mysqldump for a MySQL database. I have created a script and had taken backup using crontab. It took backup but not incremental; it only takes a full ...
0
votes
1answer
287 views

Performance of MySQL BLOB fields processing

Suppose, I have large (about 1 GB) strings in ASCII encoding. I have to choose whether to store them as separate blob fields or split each one of them into smaller pieces and then store them as ...
0
votes
1answer
242 views

TYPE_SYM syntax error when running the mysql command to create or replace type

Here is my command: CREATE or REPLACE TYPE DRIVINGSCHED_TYPE AS OBJECT ( vehicle VARCHAR2(20), datesched DATE, starttime DATE, endtime DATE ); ERROR: Line 1: syntax error, unexpected TYPE_SYM, ...
0
votes
1answer
35 views

How to restrict mySQL queries to not affect server and take it down?

There are times that some heavy queries increase server load and after sometime both CPU and Memory reaches 100%. This is my.cnf configuration file: [mysqld] expire_logs_days= 2 pid-file = /...
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 ...
-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
233 views

Which one for MySql Bind addresss 127.0.0.1 (in apache & ubuntu)

Should I set the bind address 127.0.0.1 in etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf I am using mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) mysqladmin Ver 8.42 Distrib 5.7....
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. ...
0
votes
1answer
230 views

How can I transfer Mysql data to another computer and add it to an exist database

I'm trying to transfer Mysql data to another computer and add it to an exist database Here is the condition: 1. Computer A, A has a database named 'Test'; 2. Computer B, B has a database named '...
0
votes
1answer
235 views

Performance penalty for using thousands of tables in MySQL

Background I'm using wordpress multisite in which each user will have separate blog. For each blog, additional 15 tables are created. The website will be used by thousands of users. That means that ...
0
votes
1answer
305 views

Fabric - Operation CREATE USER failed for 'fabric_server'@'%''

I am finding the use of fabric to be tottalyh unstable. Here is what I do and the below is the result: mysqlfabric group add druid 192.34.59.181:3306 mysqlfabric group add druid 192.34.56.66:3306 ...
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 | ...
1
vote
1answer
242 views

Can you rollback a query in state 'committing alter table to storage engine'

We've got an InnoDB table with 70 million rows, and we have been trying to run an alter table statement to modify and add a couple of columns. The query seems to have altered the table, and is now in ...
3
votes
2answers
52 views

How to use Limit in query on my index column without scan all rows?

Here is my table : In my table Clustering_key (Primary key and auto incremental) ID (Index Column) Data (Text datatype column) Position(Index column) maintain the order of Data My table have 90,...
-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 ...
1
vote
1answer
346 views

Error 1304 when creating a procedure after a drop if exists

I have some php code that runs queries stored in a database to update some thousand client databases. Now, for the first time, I needed to create a stored procedure because the update is complex. I'm ...
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
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 ...
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 ...
7
votes
6answers
3k views

High CPU system time usage on MySQL server

A little backstory, some time ago we started to experience high CPU system time on one of our MySQL databases. This database was also suffering from high disk utilization so we figured that those ...
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, ...
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
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 ...
3
votes
3answers
2k views

What can cause a rapid drop in RDS MySQL Database free storage space?

How could my MySQL database on Amazon RDS have recently gone from 10.5 GB free to "storage-full" status within about 1.5 hours? It's a 15GB MySQL 5.6.27 database running on a db.t2.micro instance. ...
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 ...
1
vote
1answer
270 views

Update mysql DB when data has a single quote with Dynamic values

I am writing data to mysql DB from my linux machine using sqlcmd. My script is updating the data Dynamically based on the employee number. I have a situation where the the CN attribute might have ...
0
votes
1answer
257 views

mysqli importer file - disable foreign keys

I am using this script to import a mysql file but I want to set foreign key checks to 0 when he will do the import. https://github.com/davcs86/php-mysqlimporter/blob/master/php-mysqlimporter.php ...
3
votes
1answer
255 views

mariaDB not caching queries cached by mysql

I'm migrating my database (MySQL 5.1.73) to a new server, and I am trying to replace it with mariadb (5.5.5-10). Globally mariadb seems much faster, but there is a couple of queries that it refuses to ...
0
votes
0answers
25 views

Error 'Unknown Collation: '2048' on query

I'm doing MYSQL Replication for the first time. Master mysql version: mysql Ver 14.14 Distrib 5.1.52, for pc-linux-gnu (i686) using readline 5.1 Slave Version: mysql Ver 14.14 Distrib 5.1.73, for ...
4
votes
1answer
300 views

Unable to get rid of orphan temporary tables

After migrating from MariaDB 5.5 to 10.0, I noticed an error message mentioning a table named piwik/#sql-1526_3a. From there I started googling and landed on the innodb troubleshooting docs Indeed, ...
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 ...
1
vote
1answer
303 views

Do too many subquries create multiple sleeping processes in MySQL?

I am using java & MySQL to make an app. When I check the process list. I see a lot of sleeping processes. It also reaches to a points when no further connection is allowed. I have read many ...
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 ...
4
votes
1answer
282 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
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 ('...