MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
36 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
103 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
828 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
218 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
17 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
787 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
411 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
119 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
35 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 ...
3
votes
2answers
4k views

Database design for an E-commerce website

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand ...
2
votes
2answers
189 views

Java Spring application Database triggers auditing - how to provide the right user that made the change

We are developing a new web application. one of the most basic requirement is to audit all entities changes into a separate table. We would like to use DB triggers for that purpose. We use MySQL as ...
0
votes
1answer
27 views

How do I output the “most” in MySql?

I have a list of employees for our application. One of the fields is job title. I use the 4000 job titles to roll people into 1 of 50 "job roles". We then give people access to our apps via job ...
0
votes
1answer
48 views

Performing “dummy” queries in SQL

I'm working through the book SQL Cookbook and there's an example where the author uses a query which is purely cosmetic (it's after the first union all statement): select ename as ...
2
votes
1answer
309 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. ...
0
votes
1answer
235 views

Quickly establishing if a remote MySQL connection is valid using PHP

I have a hosted website (Namecheap Pro shared hosting) that connects to a remote MySQL database using a read-only user privilege. This remote DB is updated by a separate system. The website works ...
0
votes
1answer
26 views

Some tables not found after moving MySQL database from Linux to Windows

I had a MySQL database which I was working with from Linux. Some of the schema/table names were in mixed case. I then needed to work with the same database on Windows, but because of the enormous ...
0
votes
1answer
212 views

MySQL: SELECT GET_LOCK slow/timeout

I am using "Zebra Session" as a replacement for PHP's built-in session management. Zebra Session stores sessions in a MySQL database, and uses "session locking" in order to ensure that data is ...
0
votes
1answer
574 views

Cross tab query with concat field

I want to create a cross tab query for my table. When I use CONCAT I am unable to get output successfully. Can anyone help me with my query? SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT ...
1
vote
1answer
23 views

Calling a View from Procedure

I have a Large SQL Statement with several Join which need to be executed with different Where Conditions based on Procedure Input Parameters. Ex: CASE pSearchType WHEN 1 THEN SELECT Col1, ...
0
votes
3answers
27 views

What does MySQL mean by “Major Version”, and is MySQL replication supported between 5.1 and 5.6?

I am migrating from some MySQL 5.1 servers to MySQL 5.6 servers. The MySQL docs are a bit unclear if replication is supported between a 5.1 master and a 5.6 slave, and I'm a little confused what MySQL ...
0
votes
1answer
25 views

Where are tempory tables or record sets located?

Using mysql and php, I would like to understand where temporary results are stored. For instance, when I do a query that returns a set of data for processing by php, is the connection to the database ...
4
votes
1answer
68 views

How to model “Courses prerequisites” for a university?

I am trying to model a courses structure and dependencies of a university. For example, in the Computer Engineering department you need to do Data Structures before doing Artificial Intelligence. But ...
0
votes
1answer
396 views

MySQL: summary query for date activity by last 7 days, week and all year totals

I have a customer table with records like ID Name Date Status 1 Joe 2014.02.01 New 2 Bob 2014.02.02 Requested 3 Rob 2014.02.02 New 3 Dale 2014.02.02 New 4 Jane 2014.02.03 New ... assume ...
0
votes
1answer
178 views

Cannot start MySQL service on linux RHEL when pointing datadir to mounted ecryptfs folder

I moved the data directory for MySQL to an ecryptfs encrypted folder. When I run service mysqld start as root, the service will not start. [Warning] Can't create test file ...
1
vote
2answers
54 views

Get the nth distinct column value ordered by another column

I want to get the row with the nth distinct value in a column, as ordered by another column. In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the ...
-1
votes
0answers
29 views

has phpmyadmin any bug?

phpmyadmin version is: 4.1.14 mysql server version: 5.6.17 web server version: Apache/2.4.9 (Win64) PHP/5.5.12 OS: Win 8.1 I have this query: CREATE DEFINER=`root`@`localhost` PROCEDURE ...
4
votes
2answers
455 views

How do I find the next saturday in MySQL

I have trigger and a stored procedure in it (so SP runs when trigger runs). I need a function which finds the next saturday to put in SP. So lets say, it is Wednesday (2015-7-22) today. If my ...
1
vote
1answer
30 views

Int comparison very slow in query

I have this for simplicity fictional table which explains my problem: CREATE TABLE IF NOT EXISTS `posts` ( `pid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `posts` int(11) NOT ...
0
votes
1answer
1k views

Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard?

MySQL 5.1.x | Windows Server 2003 Can someone please clarify why max_connections in my.ini is much larger than the manual value I specify in the Concurrent Connections Dialogue? For example, if I ...
0
votes
2answers
43 views

X rows affected, but NO rows actually changed

I'm having a problem with MySQL. When I run the code below, via phpmyadmin, it returns 9000 rows affected, and if I make a search, shows that it replaced all the needed things successfully. But if I ...
0
votes
0answers
35 views

Improving SELECT queries involving multiple joins

I have the following query: SELECT event.* FROM event LEFT OUTER JOIN invalid ON invalid.event_id LIKE event.id AND invalid.userid LIKE '<userID>' LEFT OUTER JOIN owner ON owner.id ...