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

0
votes
2answers
7 views

getting error while starting mysql by justing typing mqsql in terminal

getting error while starting mysql by justing typing mqsql in terminal amtz@amtz-VPCEA23EN:~$ mysql ERROR 1045 (28000): Access denied for user 'amtz'@'localhost' (using password: NO)
1
vote
1answer
13 views

Does innodb_buffer_pool_size accept “*GB” values?

I thinks innodb_buffer_pool_size is specified in bytes, would typing in values with unit (i.e 1024MB or 1GB) work? Has anyone already tried this? Specs: MySQL 5.6
2
votes
1answer
247 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 ...
4
votes
1answer
413 views

Storing history of full/partial tables

I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables. I wonder if it's efficient to store a lot of rows ...
0
votes
1answer
17 views

Taking load off the Master server by using a slave

My gameserver utilizes a Master and a slave on one server. The reason I am wanting to use a slave is because I want to beable to run select queries (read only) to get data without affecting the Master ...
2
votes
1answer
29 views

Passing multiple value for a parameter in my sql

I have written the query below: SET @City = 'London,Lyon,Kln'; SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@City, ',',@r:=@r+1),',',-1) City FROM (SELECT @r:=0) x, (SELECT 'x' xx UNION SELECT ...
1
vote
1answer
301 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
0answers
8 views

Every “INSERT” query check a combination of column's records

Unfortunately, my mind has stacked right now and i would like to design a really simple DB schema. What i want? In a simple table, for instance Table1, there are two different columns, Series and ...
0
votes
0answers
10 views

Problem connecting to MySQL using Ruby on rails

I'm using the MySQL command line tool, and I can successfully log into my root user. However, when I have created a new user, I can't log on with the command tool, but it works fine in the MySQL ...
0
votes
0answers
14 views

Sql multiple table checks for polling

I am implementing long polling using mysql for a near realtime data update for clients. Currently, this is what I have (and it works): A messages table, that holds messages between users,there is a ...
1
vote
2answers
39 views

Creating calculated column

I want to create a rating weight and I need do calculate AVG, then find COUNT and multiply both to get weight. I have something like that: SELECT post_id, COUNT(CASE WHEN vote > 0 THEN vote ELSE ...
0
votes
1answer
128 views

GET_LOCK([name], 0) behaviour and multiple GET_LOCK() order

I read from the documentation that the behavior of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined. I was curious: say ...
0
votes
0answers
18 views

MySql master stopped writing to binlogs when tmp partition full

Percona MySql version is : mysql Ver 14.14 Distrib 5.5.46-37.5, for debian-linux-gnu (x86_64) using readline 5.1 OS Version is : Debian 7.9 MySql master has a separate tmp partition, of ...
0
votes
1answer
415 views

my.ini configuration for a huge server

I have developed a web based app in PHP MySQL and Flash Builder. It was running fine until a few days ago when the client began to connect a lot of users. Then the app started to freeze and sent ...
1
vote
1answer
182 views

Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

CRUD replicates just fine. But we noticed that DB structure altering commands such as ALTER TABLE and/or adding/updating columns to a table and even creating new ones do not replicate to the slave at ...
0
votes
0answers
8 views

In Database administration is there any proven advantages in using/choosing a MySQL IDE over another? [on hold]

Would it really matter if I would choose a MySQL IDE between Workbench, HeidiSQL or SQLYog? Does there any difference between how these IDE's has been implemented?
0
votes
2answers
112 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
2answers
982 views

Restore InnoDB to different machine

It was pointed to me from serverfault that the following question would be a better fit here so this is basically my original question from there. I currently have backups that were taken with ...
0
votes
0answers
27 views

Replication Error_code: 1032

I an unable to fix this as I don't know what cause this error. Replication was stoped for maintenance activity and after completing the maintenance we started it and then after some point the ...
1
vote
1answer
6k views

Mysql replication is enabled but stuck at “waiting for binlog to be updated”

I have set up replication between an existing database (server-id=23), and a new one (server-id=51). I have followed the steps in this stackoverflow question, but replication is not happening. On ...
8
votes
2answers
5k views

Deleting MySQL table with pending transactions

Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)? What happened: I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
0
votes
0answers
5 views

how to change relay log fie name on “Adding Slaves to a Replication Environment”?

MySQL document advised that "new slave relay log file name " should followed the "old slave relay log file name" to avoid this problem, use the same value for --relay-log on the new slave that was ...
0
votes
1answer
518 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around ...
0
votes
0answers
24 views

How to add a trigger on table create in Mysql

I want to apply a trigger on all tables if my database. I already created a script in PHP which will add triggers in all existing tables. But if any new tables are added in database, I have to run ...
2
votes
1answer
121 views

Write-lock a whole table during transaction

I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
3
votes
1answer
416 views

How can I connect to a network MySQL server using ODBC?

Somewhere on my company's network there's a (Oracle) MySQL server named FOO. I see FOO reference in SAS code written by my team's analysts, who use SAS's PROC SQL command to connect to it using ODBC. ...
4
votes
1answer
15 views

Checking for fields not on SET clause on before update trigger on MySQL

I want to set a before update trigger that sets a column to a given value if an update query is done and that column has no new value on the set clause. The table I'm using. (Sort of) DROP TABLE IF ...
0
votes
1answer
21 views

Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT?

Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT using MySQL Workbench's SQL Query window or MySQL Connection Navigator instead of in a c# ...
1
vote
1answer
46 views

Between optimizing a 1-billion rows table and dump-restoring it, which would be faster?

Have multiple tables which had more than 1 billion rows. Archived and deleted many of them, now want to recover the disk space. Have option to either optimize the table, or dump the table and restore ...
1
vote
1answer
37 views

Mixed based replication between two databases on the same server

I have two apps with a current configuration as follows: App1 DB on server A/MySQL Instance A App2 DB on server B/MySQL Instance B Students can apply for internship using App1 and those ...
0
votes
1answer
40 views

Index on integer and string fields

A table has about 300M records. Each has an unique ID that starts with 13 to 14 digits (with the first digit on the left being either 1 or 2), and is optionally followed by a string of up to 7 ASCII ...
0
votes
0answers
26 views

MySQL Errror “Cannot Connect Invalid Settings”

I was working with mysql using phpmyadmin on a local server and when I clicked on a table it took a very long time to do anything so I let it do its thing, I walked away and when I came back I had the ...
5
votes
1answer
374 views

Deadlock free MERGE-alike in MySQL?

I am trying to implement some parts of MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions. So, ...
0
votes
3answers
146 views

9 node percona xtradb cluster

We are looking to launch a 9 node percona xtradb cluster on Amazon where each ec2 node uses 20k iops disks. I was afraid if a 9 node cluster will work and mostly what I see is 3 node clusters. We ...
1
vote
1answer
138 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 ...
1
vote
0answers
45 views

Is there a way to throttle MySQL requests to mitigate DoS traffic?

I've a server with ~200 wordpress websites hosted. When Google Favicon bot pays us a visit it searches all the sites indexed on our IP, that means it's requesting ~200 favicons. Most of the sites ...
1
vote
1answer
143 views

Can having a LIMIT on DELETE degrade performance dramatically?

I have a database project in php and mysql. For, hopefully redundant, data security I added a LIMIT 1 to the delete($uniqueID) php function we use. Nobody cared because records really aren't ...
2
votes
2answers
111 views

Is there any reason not to use foreign keys in MySQL?

I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign ...
2
votes
1answer
9k views

MySQL got signal 11 error and slave is down

My slave server is down and mysqld unable to start. What should I do? Below follows the error logs: 2014-09-26 12:51:53 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2014-09-26 12:51:53 ...
8
votes
2answers
11k views

Taking MySQL dump from another machine

I have a MySQL db on Machine A and I want to access the mysqldump from machine B. Both machines are running on Linux and I also have the credentials of both, but I can't access the mysqldump. What ...
4
votes
2answers
3k views

Trigger in MySQL to prevent insertion

I want to create the trigger which prevents insertion in the reservation table of flights (numflights) which does not exist. Tables ---------------------------------------------- flights(numflight, ...
8
votes
1answer
8k views

MySQL INNODB corruption after server crash during concurrent truncate command

My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
8
votes
2answers
9k views

MySQL equivalent of WITH in oracle

Is there a MySQL equivalent of the WITH clause in Oracle?
1
vote
1answer
211 views

“Remote” connection to a local MYSQL database on LAN

I have a mySQL (5.5.38) server running on my desktop (windows 7), I can connect just fine into any of the servers Users, and skip-networking is off when checked via show variables. I am trying to give ...
1
vote
2answers
23 views

Interested in counting number of threshold crossings

I would like to determine the number of times that a value has crossed a threshold in either direction in a specified time frame. As an example, lets say that I am interested in the number of times ...
1
vote
1answer
209 views

Can't see temp table from subquery

I have a query that selects data from tables and one temp table. This works fine, but there is a GROUP BY/ORDER BY combination that doesn't work well for me. Indeed, I need to order the results and ...
0
votes
2answers
24 views

ulimit 'open files' and MySQL max_connection

When running mysql on SSD, does max_connections, if set more than the kernel's ulimit open files, be limited to the kernel's limit? My understanding is that if the kernel's ulimit open files is 1024 ...
2
votes
1answer
40 views

Can I use an index to optimize select clause, versus where clause?

I'm not sure if this question has been answered, because I'm not sure of the proper wording to describe what I need. So here it is: I am trying to optimize a database that is running very slowly, ...
0
votes
1answer
508 views

MySQL: Transaction and Deadlock

I have table "tbl_data", where have a lot of rows (3 - 5 million). I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table. If I start *.jar file (for example, "run.jar") and after ...
0
votes
0answers
8 views

mysqlworkbench nasty result grid with text columns

On MySQL Workbench 6.3, when you query a table with text columns that have carrier return \n in it, the Result Grid expands record's height accordingly. In the image, I've reduced the height of the ...