MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
1k views

How can I get Performance Statistics to work in MySQL Workbench?

I have a complex query that is performing in subsecond time on my laptop, while the same query takes 70 seconds on my production server under no load against an identical schema. The EXPLAIN plan is ...
0
votes
1answer
20 views

MySQL ERROR 2013 Lost connection

I am running a real old version of mysql(3.23.56). Everything is good, expect when I want to delete all entries of a table. I have checked query, like drop, select,update, their behaviours are ...
0
votes
3answers
42 views

Use “NOT IN” in the same table, Mysql

I have a table like that: table1 +----+------+--------------+ | id | name | phone_number | +----+------+--------------+ | 1 | A | 111111111111 | +----+------+--------------+ ...
3
votes
1answer
784 views

SQL set profiling

I'm setting up an automatic database backup for a website. When I try to do a backup it comes up with the error" MySQL error: The 'SHOW PROFILE' feature is disabled; you need MySQL built with ...
0
votes
1answer
260 views

MySQL Entity Relationship Problem - 3-way join table?

This is a db relationship question. I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows: ...
1
vote
0answers
22 views

Migrating tables from SQL Server to MySQL

I am using MySQL Migration Wizard to copy some tables from SQL server to MySQL local instance. When I define the source and target connection and test them I get a Connection Successful message and ...
1
vote
0answers
21 views

MySQL/Eloquent belongsToMany relation - two tables same pivot table allowed? [migrated]

Is it allowed to have two tables, each with a belongsToMany() relation to each other, using the same pivot table? Here is my User table: And here is my Org table: And here is the pivot table: ...
0
votes
1answer
112 views

Need help achieving HAProxy active/passive configuration for mysql

I'm using HAProxy for mysql database backends. The database had to be master-slave. The goal is to point all mysql clients at the proxy and for all requests to go to mysql server #1 and only go to ...
0
votes
1answer
30 views

Is it possible to convert mssql to mysql?

I've mssql.sql file I need to convert to mysql schema. Any ideas how? I'm on mac, I guess my choice is very limited. Tried workbench but it asks me for a live server connection to mssql which I don't ...
0
votes
0answers
7 views

Would a nosql db handle sorting by latitude and longitude queries as well as a mysql db?

I have a table with a couple hundred advertisers including the latitude and longitude of the centroid of their zip code. I will also know the centroid of the zip code of website visitors. What I want ...
1
vote
1answer
43 views

Sudden MySQL freeze, with spike in dirty pages, but without obvious write queries

I'm systematically (1/2 times per day) experiencing a freeze in the DB server. Suddenly, all the COMMITs get stuck in 'init' state; they pile up (up to 20/30), an almost no other queries are executed ...
0
votes
1answer
250 views

MySQL 5.6 replication causes 'waiting for table lock'

All of the sudden queries on slave server stopped with status "Waiting for table level lock" I restart mysql service and stop replications and locking does not show up anymore. Once I turn ...
1
vote
1answer
26 views

Foreign key default value

I have a master table and another table with foreign key. I can easily add data to master table. But whenever I want to add data to the connected table, I get a message saying Foreign key doesn't have ...
0
votes
0answers
8 views

MySQL Cluster and Async replication

My requirement is to have percona cluster for high availability and have the MySQL's classical aysnc replication for disaster recovery purpose. Is this is feasible? What are the possible issues that I ...
0
votes
2answers
113 views

How to change ibdata1 path in mysql?

My ibdata1 file is keep on increasing in size and for a temporary solution, we want to move the file to a separate location with more hard disk space. But, when I change the my.cnf file parameters ...
1
vote
1answer
2k 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
2answers
260 views

MySQL crashing randomly

We have a sever running a website Joomla 2.5. We are facing frequent mysql crashes that occur when website has medium visitor load like a few thousands. It appears that mysql consumes more memory and ...
1
vote
1answer
4k views

Amazon RDS - Can't connect to MySQL database server?

So I've been working on this issue for a while now, and I'll explain what I'm trying to do. I'm trying to connect to a MySQL database server, hosted by Amazon Web Services in the Amazon RDS service. ...
3
votes
2answers
254 views

ib_logfile opened with O_SYNC when innodb_flush_method=O_DSYNC

mysql will open and flush data/log files as below when innodb_flush_method=O_DSYNC Open log Flush log Open datafile Flush data O_SYNC NONE NONE fsync () ...
0
votes
0answers
25 views

Mysql permissions help [closed]

I need a simple Sql statement that will grant all of the privileges and basicly, establishing a connection to any computer that tries to connect to my remote mysql server with the root account. For ...
1
vote
4answers
92 views

Mysql - Select next x records even if reaching rowset end

I have a table with an ID field, unique, autoincremental. I need to, given certain ID, retrieve next 3 records even when given ID is the last one. Table: ID 1 2 3 ...
0
votes
1answer
69 views

how to minimize the data scanned on my database using index?

Is there anyone how to minimize the data scanned on my database?? please show me.. using index? query SELECT IF (e.channel LIKE 'IAX2%', e.calldate, IF(d.channel LIKE ...
0
votes
0answers
35 views

MariaDB error on alter table - table removed - constraint name is blocked

Today, we experienced a strange behavior in 5.5.41-MariaDB (InnoDB): An ALTER TABLE (add constraint) statement failed with error (mysql server has gone away after that). Then the database table ...
1
vote
2answers
20 views

Delete trigger mysql on another table

I am new on mysql and I am try to create a before delete trigger on mysql. I have a table call "persons" and I need delete a record on another table called "ldap_entries" the table "persons" have a ...
0
votes
1answer
110 views

Mysql Percona 5.6 slave lags gradually

I have got into slave lag gradually say every minute it lags around 5secs on slave. So it started morning 10AM the Seconds_behind_master was 13. Now at 4:30 PM it has reached to 560. No variable ...
0
votes
2answers
1k views

SQL - 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 ? DROP DATABASE technology_university_hospital; CREATE DATABASE ...
0
votes
2answers
102 views

How can i use the same query for all codes?

I use this query in PHPmyAdmin, i have date that contain(date,hour),supp, price and code_product (it repeated many times) : I want to return the last price for the last date of product code ...
1
vote
0answers
24 views

How to find where an unauthenticated user connects from

We are seeing some "unauthenticated users" when I run the command show full processlist in mysql (v5.6). | Id | User | Host | db | Command | Time ...
0
votes
1answer
33 views

Set in mySQL a predefined order for SELECT queries without using the ORDER BY clause

I have a MySQL database and users access it through an old legacy software (OLS) the company bought years ago. OLS connects to MySQL through MySQL ODBC driver. There is no way to change OLS in recent ...
1
vote
2answers
827 views

Trying to recover/import/transfer database from MySQL data dir

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused. However at the time of doing the export I ...
1
vote
1answer
64 views

Items and their tags

This is my table design: CREATE TABLE IF NOT EXISTS `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `description` mediumtext NOT NULL, ...
86
votes
7answers
80k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
0
votes
1answer
20 views

How to use explode mysql function in where condition with cakephp

I am using cakephp 2.6. I have payment_method fields in mysql table which store the values like 54,52,59,57 . Now my question is that how can i check if new posted payment method id is available in ...
0
votes
3answers
495 views

Mysql trigger not working, syntax issues

I have this trigger CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; END; mysql 5.1.72-0ubuntu0.10.04.1 ...
8
votes
1answer
217 views

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
0
votes
3answers
3k views

MySQL Continue Handler Problems inside a Function

I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I ...
0
votes
1answer
16 views

How should I reinit a new db (windows 8 64b)?

In the data directory of Mysql some files are corrupt. Recovering is not working anymore. I have a complete sql backup and want to setup a new db. The complete stuff is installed with the use of ...
3
votes
0answers
20 views

Create an SSL connection with ODBC driver to MySQL(5.6) using openSSL

I want to create an asymmetric SSL connection from MySQL to an ODBC driver but don't fully know how to generate the keys and certificates (I'm using openSSL as recommended here: ...
0
votes
1answer
782 views

MySQL workbench: Cannot Connect to Database Server (ssh)

General pointers would be appreciated -- it's likely I'm misunderstanding the mechanisms involved in a remote connection through ssh. I can use ssh to connect remotely, and then, through the CLI, ...
1
vote
1answer
21 views

Mysql shutting down during a mysqldump of a specific database

I have a server which is running Centos 6.6 with Cpanel. I have been attempting to do mysqldump backups on individual databases. All of the databases are under 5MB except for one. The database that ...
2
votes
1answer
49 views

Selecting max(date) from union query

I am having a bit of trouble getting accurate results from a query that looks to me as if it should work. RDBMS is MySQL. I am trying to get the max last login date from a union select on three ...
0
votes
1answer
410 views

Change MAMP to use a lower Mysql 5.1 version

I need to have the same development environment for my PHP program. My clients test server is running on Mysql 5.1 while my local is using MAMP running Mysql 5.5. How do downgrade my mysql database ...
7
votes
3answers
6k views

Differences between “Unique Key” and “Primary Key”

What are the major differences between Unique Key and Primary Key in MySQL?
0
votes
1answer
118 views

mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?

I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway ...
0
votes
2answers
1k views

Lock wait timeout exceeded on insert despite low lock_time in the slow query log

Our Java/Spring application reports occasional lock wait timeouts on single record INSERTs to a MySQL 5.5 InnoDB table. Here are a few slow query log records for some of the failed INSERTs. # ...
3
votes
1answer
3k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in ...
1
vote
0answers
34 views

databse size of 1000mb file is not uploading completely

we have a e commerce magento site we have a large database of 1000mb for that site. when we try to import database through phpmyadmin. its not uploading full databse, lot of tables will miss after ...
0
votes
1answer
4k 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 ...
-2
votes
1answer
42 views

how to trace the data whether the destination stop using mysql?

I am newbie in programming.. I have table test and 3 columns(source, from and destination) below.. Table test source from destination 01 VALID/NUM-6283 ...
0
votes
1answer
62 views

What is the difference between `MAX(column + 1)` and `MAX(column) + 1`

I know I should be writing MAX(column) + 1 in my queries rather than MAX(column +1). However, by mistake I wrote the wrong one and it seems to prevent a deadlock issue that I was having. Couldn't ...