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

1
vote
2answers
3k views

Mariadb on Centos 7 not changing max connections based on my.cnf

The following configuration in my.cnf is not being followed. Max_connections go back to 151. please help as i have 16gb of dedicated ram that i would like to use properly. [mysqld] datadir=/var/...
0
votes
2answers
125 views

Which files in the “MySQL-5.0.27-win32” folder do i need to backup?

Ok, I am using a pretty old MYSQL "mysql-5.0.27-win32" and the file and the structure maybe different from the latest Mysql (mysql 7). Here is the current structure of of mysql-5.0.27-win32: In "...
0
votes
0answers
22 views

mysql select duplicate columns for same row

Ok I'm trying to write a customized report for a preexsiting database(IE, I didn't design it and can't change the convoluted structure) that has a user table and 2 other tables that store user ...
0
votes
1answer
1k views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
0
votes
0answers
23 views

Update Multiple Row's [migrated]

I would like to change one field of multiple row's at once by user input. Something like: I had some books at some branch, and i want to change some of them to other branch. The user select the ...
0
votes
0answers
15 views

MYSQL : Need Query How to update the NULL value for every next row of current row in MySQL table?

Example : Two table we can take, Table names are table1 and table2. Table1: It having 3 columns are id,date_s,value_s and the records are, 1 2005-01-01 25 2 2005-01-02 30 3 2005-01-03 10 4 ...
0
votes
1answer
10 views

Create trigger that Find, Parse and Insert to another table After inserting on table

I want to create a trigger that will insert to another table after inserting on my table. Scenario: I will insert on my inbox table. Then, on my inbox table there is a field named Msg. Next, I want ...
1
vote
1answer
10 views

Mysql auto insert record in primary table if not exist

Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key? For example, assume these tables: - user(id, name, age) - topic(id, name) - ...
0
votes
0answers
21 views

mysql pirate users logged in, what should I do

I'm using Mysql Server 5.1.73 and when I do a show processlists I can see some connections which shouldn't be there. In theory only the web app should be connected to this database. The connection ...
1
vote
1answer
73 views

Primary Index VS Unique Index Performance

I have the following table: CREATE TABLE `actions` ( `user_id_from` int(11) unsigned NOT NULL, `user_id_to` int(11) unsigned NOT NULL, `action_type` int(11) NOT NULL, `counter` smallint(11) ...
1
vote
1answer
23 views

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

Problem/Issue : Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared I have a Stored Procedure which contains a Prepared Statement and a view DROP PROCEDURE IF EXISTS `sampleProc`...
3
votes
1answer
172 views

drop table from outside?

A poorly designed script ran on my MySQL 5.6 instance for 24 hours before I stopped it by killing the MySQL command line client and rebooting the machine while the script was still running. The ...
2
votes
1answer
138 views

AWS RDS - Changing Instance Class

So I'm fairly new to AWS and I'm hoping you guys can reassure me as I couldn't find anything in the AWS documentation either way. I actually have two questions, the first: I have an RDS MySql ...
2
votes
2answers
335 views

MySQL Escape Characters

I'm getting an SQL error when trying to mix a match against in a SQL statement with a specific character which is not covered by MySQL escape functions in PHP. This is an example: The name to ...
1
vote
2answers
4k views

Cannot Add Foreign Key Constraint

Can anyone tell me why there are errors such as: Cannot Add Foreign Key Constraint And am I on the right track? My schema looks like this: DDL: DROP DATABASE technology_university_hospital;...
0
votes
3answers
83 views

Memory(buffer pool) not populating on mysql start on Percona 5.6.28

We are testing Percona 5.6.28 at the moment, during our testing we found out even though the innodb_buffer_pool_populate is set to ON, it doesn't populate the memory(buffer pool) during mysql start. ...
1
vote
1answer
303 views

mysql replication missed quite a lot SQL statement

I have set up a master/slave with mysql-5.1.73 The master's binlog format is "statement". The master and slave seemed running very well with slave status: Slave_IO_Running: Yes ...
5
votes
2answers
279 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------...
0
votes
1answer
52 views

How could duplicate entry on unique key happen if only one master is active?

Say, I we have master-master replication with only one master active at a time. We have users table: create table users ( id int auto_increment, email varchar(255), primary key id(id), unique ...
1
vote
1answer
49 views

Percona: Backup preparation doesn't work (“cannot open ./xtrabackup_logfile”)

I've created a MySQL backup with PerconaXtrabackup tool via (thanks to this guide): innobackupex --user=backupmysqluser --password=xyz --compress --stream=xbstream ./ > /var/backups/db/firstBackup....
1
vote
3answers
898 views

Percona XtraDB - Random deadlock

Randomly my database gets locked completely and queries hang and they pile up until it gets "Too many connections". Below is my mysql log file, I couldn't find anything related to the error system ...
2
votes
1answer
57 views

Calculating a percentage discount in SQL [closed]

Basically just started using sql and having a minor problem. Working out the percentage of a number. Example: What would I need to type in to work out: 2.75 - 5% Drink_price= 2.75 After 5%...
1
vote
1answer
2k views

Replicating data from Oracle to MySQL

I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has ...
0
votes
2answers
523 views

MariaDB Galera cluster nod won't start

I have configured a MariaDB Galera cluster with three nodes. The master node and one slave-master node is up and running, but the third node just won't run. The system specs and cluster config are the ...
1
vote
1answer
155 views

Finding out the exact size of the database after inserting records?

I have this query: SELECT table_schema "db.name" , Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; Does this ...
5
votes
1answer
6k views

mysqladmin not taking inline password

I am trying to set a cron job for taking backup from my slave machine. So I need to stop the slave I issued a command mysqladmin --user=root --password=test_pass stop-slave But it is throwing ...
0
votes
0answers
30 views

MySQL replication: Master to Multiple Slaves with table filtering

I have 2 separate web systems (A, B) that will end up using the same 5 tables. I'm using MySQL. System A has only one database (A1). System B has at least 3 databases (B1, B2, B3) on different ...
0
votes
1answer
138 views

How to compare all rows in a table and extract data using triggers

I have two table one table stores the gps data of various users another table stores the idle time of the users based upon the gps data of latitude and longitude values and timestamp difference. ...
0
votes
1answer
34 views

Database ExecuteSQL(MySQL client run out of memory)

I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that ...
1
vote
0answers
22 views

How unsafe is to use ctrl+c or ctrl+d in mysql prompt?

How unsafe is to use ctrl + c when in mysql prompt whether to interrupt a hanging statement or ctrl + d to exit? When I do ctr + d for exit mysqly can see a ¿special character? typed besides Bye ...
0
votes
1answer
14 views

MySQL: Do killed query results get cached?

If a query is executed from the MySQL command line, then KILLed it while it is running will the query and its partial results* be in the query cache and usable to subsequent runs of the identical ...
12
votes
3answers
9k views

Best way to design tournament database

I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase. I have created a mockup ...
1
vote
1answer
19 views

MySQL 5.6 InnoDB doesn't work

We have been having issues with one of our Windows environments. It uses MySQL and has been running on MyISAM only. We need to create a few InnoDB tables but the engine won't start. We tried changing ...
1
vote
5answers
429 views

Replication in same server within different databases. Mysql 5.6

What I have : a windows machine with one instance of MySQL 5.6 Server. two database named test and test2. Test database has a table called activity with columns id and class What I need : ...
0
votes
0answers
15 views

Error with grants when migrated to NDB cluster

we are trying to migrate the data from mysql 5.1 to mysql 5.6 NDB cluster 7.4.x. 1. we migrated the data using some scripts and restored the grants but the users are not able to login, We checked ...
0
votes
2answers
336 views

Dealing with data stored as arrays in a MySQL DB

So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to ...
2
votes
1answer
270 views

innodb , mysql Indexes difference when adding index

When I am altering one of my MySQL table to add an index I am getting below error in error log. Table a/b contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined ...
-1
votes
0answers
18 views

ways to decrease physical memory usage and commit charge (Task Manager) mysql execute query

my tables are Myisam i want to know ways to decrease memory usage and commit charge while execute query because my server ran out of memory any cache service can i stop to reduce physical memory ...
0
votes
1answer
23 views

Create a MySQL Database using a MS SQL database dump (.CSV)

I have a dump of a MS SQL database in the form of CSV files for every table in the database. There are 679 .csv files each for every table. The highest size of the table extract is 9 GB and the lowest ...
3
votes
5answers
33k views

Remote MySQL connection fails (10060) - “Can't connect to MySQL server on domainname.com(10060)”

I have developed an application in VB6, and am using an online MySQL database server for storing my application's data. The first time I try to connect to my database server is successful, but after ...
0
votes
0answers
21 views

MySQL “stalling” when database grows in size

Having a problem where lots of queries timeout. From innodb engine output it looks like lots of queries are blocked? --Thread 139681112069888 has waited at btr0cur.c line 523 for 0.00 seconds the ...
1
vote
0answers
24 views

How to design efficient queries and structure for data logging table (numerical stats)

I'd like to create a logging table that logs numeric values (.NET application). Being a programmer and not really a database person, I'd like to ensure myself with the advice of more experience ...
-1
votes
1answer
44 views

Most efficient way to log user login IPs

I am looking for ideas on how to log user's login IPs without compromising on performance in MySQL. There are few primary tasks which the log would be used for: Finding list of other users who have ...
1
vote
1answer
10 views

Process possessing lock crashes

I have two machines, M1 and M2, through which I am connecting to MySQL Database on machine M3 using Perl script (DBI.pm). So, here is the scenario that I am confused about: M1 places a table level ...
0
votes
1answer
8 views

Error 1064 during import

When I try to import my database on the production server, I get: SQL запрос: CREATE TABLE `articles` ( `id_art` int(11) NOT NULL AUTO_INCREMENT, `id_section` int(11) NOT NULL, `id_ct` ...
-1
votes
1answer
40 views

Recovering a lost database [closed]

Before I start, there might not be a way to do this but I thought I would ask anyway.. I built a php application with Laravel 5.2 and have suddenly noticed I have lost the MySQL database for the ...
2
votes
2answers
187 views

Find all users who rated all the movies rated by a specific user

So we have the following relational schema: users(user_id, ..) movies(movie_id, ..) ratings(user_id, movie_id, rate) So, we want to find all the users who have rated all the movies which are rated ...
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 ...