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.

learn more… | top users | synonyms

3
votes
3answers
3k views

mysqldump doesn't restore database. why not?

Why doesn't my restore operation work? I created a dumpfile for a database using: mysqldump -u root -p databasename > /home/databasename_bkup.sql I then opened the dumfile and confirmed ...
3
votes
1answer
660 views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
0
votes
2answers
30 views

Rank rows using weighted average with multiple sub queries

So what I want to do, is to rank students based on their weighted average which will be calculated by separating the grades based on the type (whether "exam" or "not exam). The average for both will ...
1
vote
1answer
362 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
0
votes
2answers
433 views

How to select different format of the date in mysql

0 down vote favorite. I have this table and I can't change its format to yyyy-mm-dd because I have so many scripts that are related to this format dd-mm-yyyy. It would be a mess if I change its ...
12
votes
4answers
25k views

Modify DEFINER on Many Views

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result. Got error: 1449: The user ...
2
votes
2answers
180 views

Query to Access a specfic data in the row using python

Am using wordpress and phpmyadmin for handling my database Below is the code I use to view entries on my form using python. In the form I ask users to check/uncheck specific questions. It works as ...
1
vote
1answer
729 views

Setting MySQLDump to keep running in the background after session times out

I'm tryng to set up a new Slave DB by copying another Slave DB. Normally I would simply use MySQLDump to dump the Slave and create a .sql file, then import that into the new database. However, as our ...
0
votes
0answers
16 views

Migrating SQL Blob to Server File System

I was given a MySQL database that was created by making a .sql dump file from an existing MS Access DB. During that migration I have an attachment column that was given a BLOB datatype and the ...
0
votes
1answer
424 views

MySQL Config for server with 8gb RAM and 4 cpus

I have a server with 8gb ram and 4 cpus. I don't have enough time for testing and bench marking so I would like to get the advise of experts like you guys, let me know if something is wrong with my ...
0
votes
1answer
18 views

MySQL replication on slave server Broken/Stopped without error

(Hi; sorry for my English) My question is: how can I restart MySQL replication on a slave server. At first sight everything seems to be normal but when I check with SHOW SLAVE STATUS\G besides ...
-4
votes
1answer
39 views

I need to change the format of 'date' from yyyy-dd-mm to mm-dd-yyyy [closed]

My dilema, I was requested to populate a table of sample data and I completed the command to add a date. I entered '07-07-2016' as sample data. When running a query the "date_ordered" field came ...
2
votes
1answer
167 views

MySQL running fine while data files (disk) are not present. Data folder nowhere to be found

I'm running MySQL server 5.6 on Windows 2012 R2, and since the installation didn't go as planned, I had to re-install it to change the data directory path. The installer for some reasons never gave ...
1
vote
0answers
51 views

MYSQL Extremely slow - Heavy CPU Load

I am having an issue with my Percona MySQL 5.7 node. Since past 3 days, MySQL is processing queries very slowly. When I run top, the % CPU usage of MySQL is always 600. # top | grep mysqld 8409 ...
1
vote
1answer
251 views

MySQL partitioning table by date and hour

I have a table with a timestamp field. This table will have millions of rows every day so the idea is to create partitions by date and hour. Every day I will delete partitions older than 10 days and ...
1
vote
1answer
64 views

Optimize mysql database performance

I have a database with some relatively small tables and one big table with about 10 million rows (11 columns) that I would like to optimize its performance. The table looks like this: Id: (int 11, ...
0
votes
1answer
141 views

MySQL user privileges lost on reboot

On occasion our MySQL 5.6 server loses some user privileges when restarted. The CREATE USER and GRANT commands are run through phpMyAdmin and function for as long as the server is running. After a ...
0
votes
1answer
53 views

Reset MySQL table data after each session is completed in php

I am creating a PHP application demo. The users who login can insert, modify and delete data but when they logout the data must be reset to the default data I set before. The data must reset ...
1
vote
1answer
7k views

MySQL slave stuck at 'System lock' most of the time, high CPU usage

Let me describe what we are facing now: We have a MySQL Master-Slave, Row Based Replication setup and at the moment, the slave is stuck with a perpetual System Lock. The Master is an active server ...
0
votes
1answer
16 views

shrink ibdata1 in Galera Cluster

I'm having a Galera cluster (3 nodes, but currently using only one), MariaDB 10.0 Galera. However, my ibdata1 is growing HUGE (although using inno_file_per_table=1). Now i want to "shrink" it by ...
0
votes
2answers
202 views

how to GRANT EXECUTE on MySQL

I updated the timezone on an AWS MySql instance, and tech support told me I also had to runt his command for each MySql user: GRANT EXECUTE ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'...
1
vote
1answer
518 views

Syncronize mysql databases between local and hosted servers automatically

We have many websites with Development, Staging and Production servers. We have many developers for many projects. We need a solution to synchronize the developer and staging databases. After that one ...
1
vote
0answers
16 views

Modeling Multiaccounts + content interactions [closed]

I'm having trouble to model a scalable database schema with performance in mind. The requirements are wide, as such that I need a schema that supports; user can swith between multiple types of ...
0
votes
2answers
24 views

How can I fetch parent table's value within a trigger?

I have a PARENT table and a CHILD table. PARENT ------ ID BIGINT PRIMARY KEY NAME VARCHAR(255) NOT NULL CHILD ----- ID BIGINT PRIMARY KEY NAME VARCHAR(255) NOT NULL PARENT_ID BIGINT NOT NULL I ...
0
votes
1answer
147 views

Slow mysql query full text search tag database

In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. ...
0
votes
1answer
823 views

MySQL data transfer to different schema

We have two servers: one for the developer (me) and one for the tester. While working on the app, I sometimes make little changes in tables's schemas. (Like a new field, or a field type) I'm trying ...
0
votes
1answer
518 views

MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

I had a table with ~0.5M rows and tried to exec simple query, something like UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999 but result was ERROR 1297 (HY000): Got ...
0
votes
0answers
27 views

Data Base Error

I have an online game. The game server every 2 hrs lost connection with data base & when i check the gameserver log i found this 2016-7-18 2:1:8 -- ERROR: Database ExecuteSQL(MySQL client run out ...
-1
votes
0answers
12 views

how do we know if mysql server is overloaded [closed]

I am developing an application using PHP. Sometimes Mysql is overloaded. Is there anyway I can tell the user the database is overloaded & to come back later?
0
votes
0answers
26 views

Getting values from tables to string, combined one-to-many and many-to-many

I am stuck with one task. I have a database with one-to-many and many-to-many relationship. The whole problem is that I need to take from one table all persons in to a separate strings and count how ...
3
votes
1answer
3k views

invalid default value (mysql 5.7) for timestamp field

EDIT: When updating an existing database from mysql 5.6 and executing: UPDATE phppos_register_log SET shift_end = '2015-01-01 00:00:00' WHERE shift_end = '0000-00-00 00:00:00'; This produces: #1292 ...
0
votes
1answer
214 views

MySQL taking significantly longer to query via PHP than CLI

TL;DR: I have a query that takes under a second to run via MySQL's CLI, but over 12 minutes via PHP and I'm at a total loss on what to check next. It's a fairly horrid query, full of joins, but the ...
0
votes
1answer
18 views

MySQL - Upgrading from 5.5 to 5.6

I have MySQL community version 5.5.50 running in linux having 4-5 databases on it. Requirement is to upgrade to Percona Server 5.6.31-77.0. What are the pre-requisites that I need to check before ...
0
votes
0answers
10 views

Trying to recover a database with just .ibd and .frm files, but no idbata1 file

Trying to restore a WAMP database with just .frm and .ibd files. The uninstall of WAMP left .ibd and .frm files, but no ibdata1 file. I have read several posts here to figure out the process for doing ...
2
votes
1answer
220 views

Why do innodb tables get corrupted so easily?

I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10. We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been ...
0
votes
1answer
169 views

What is the optimal number of worker tasks to specify in MySQL Workbench for a speedy migration?

What is the optimal number of worker tasks to specify in MySQL Workbench for a speedy migration? The default is 2. The MySQL server is running on the same Windows machine, installed with defaults. ...
0
votes
2answers
260 views

Please recommend changes for my.cnf

I am not having any performance problems per se but want to improve my sites' performance if possible. Would appreciate any advice for how I can improve my.cnf. I have just one server, not ...
0
votes
2answers
18 views

Open Source tools to visualise MySQL/MariaDB performance metrics (show global status with intervall)

I search for an Open Source tool to visualise MySQL/MariaDB performance metrics (output of show global status in nice graphs). In MongoDB world there is the Closed Source Ops Manager and the Open ...
0
votes
2answers
34 views

General Database Optimizations - correct data types - why to avoid NULL? [closed]

Oracle recommends: Choose the correct data types and size to: – Avoid NULLs – Improve performance – Protect data – Use data compression where appropriate I don't understand why I need to avoid ...
0
votes
1answer
31 views

Why MySQL does not provide stddev(distinct expression)? [closed]

I ask the question because some other numerical aggregates, like sum, avg or count support the distinct modifier. And other databases like Oracle or MSSQL support distinct modifier for stddev.
0
votes
1answer
117 views

Slow ranking values in MySQL

I have a table of around 30 million retail products, with various INT columns that describe sales counters and pricing at different retailers. I'm supposed to display well over 70 different rankings ...
0
votes
3answers
24 views

Create a mysql event with condition [closed]

I currently have a mysql database where I keep data about rented books. When somebody rents a book, I save on the database an expiration date. So, I want to create an mysql event to update the book ...
0
votes
2answers
62 views

How to select the same data for unique item?

I have this SQL table: +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+-...
0
votes
2answers
39 views

How to improve query count execution with mySql replicate?

For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table). And its really time consuming when it ...
0
votes
1answer
533 views

MySQL Aborted connection warning messages

I am using "5.6.22-log MySQL Community Server (GPL)" MySQL server. All the tables in my servers are using Innodb storage Engine. Below is the configuration of my server: [mysqld] #...
0
votes
1answer
270 views

MySQL Cluster work well but ndb_desc doesn't work

I have deployed and configured MySQL cluster. It works fine. ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration [ndbd(NDB)]2 node(s) id=2@192.168.56.2 (...
0
votes
1answer
21 views

Moving log files & data in MySQL

Is it possible to separate out data files and log files of MySQL from one drive to another? For eg: Data (actual database data and system tablespace ibdata) in one partition, say /data and log files ...
0
votes
0answers
16 views

MySQL - sys_exec to trigger python scripts - Alternatives

I am trying to log some heartbeat data to check successful completion of errors in some frequently running stored procedures to Datadog. At the moment these are triggered from a Java application ...
0
votes
1answer
5k views

How to convert a MySQL database from utf8mb4 to utf8?

I would like to convert a MySQL database, which has utf8mb4 character set and utf8mb4_unicode_ci collation, to utf8 and utf8_unicode_ci. How to do that please? Thank you in advance for your ...
1
vote
1answer
60 views

Error code 1054: Unknown column when inserting data into a table [closed]

I have been trying to join data from two separate tables with distinct columns into a third table, but Error code 1054: Unknown Column 'Company' in field list keeps popping up. My current ...