MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
667 views

Optimising MySQL query with lots of self-joins

I have an entity_relationship table that describes relationships between entities. Each relationship will have 2+ entities involved. In some cases it can be said that one entity is a constituent of a ...
1
vote
1answer
1k views

MySQL INSERTs waiting for UPDATEs (MyISAM)?

In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables. We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. ...
2
votes
2answers
803 views

MySQL Workbench Loses Saved Connections

I've been using MySQL Workbench as my go-to management application for quite some time. However, recently (namely, in my most recent install), it will every so often lose the saved connections I've ...
2
votes
1answer
1k views

Insert Value based on One Column based on Value on another Column

I just tried to set _timeStamp column to store value on the basis of HOUR of _date column and tried to create table as like below but encounter 1064 error. I'm new to mysql so help me in figuring out ...
2
votes
2answers
479 views

How to design future proof tables?

I am new to database design and have started developing my first schema. Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular ...
0
votes
0answers
53 views

Phpmy admin not showing my table but mysqldump

I have a db running and suddenly when I go into my phpmyadmin under the db it shows no tables and when I tried to do dump it shows me some of my tables are crash. What is best survival in this case?
0
votes
1answer
306 views

Create a VIEW that spans 3 tables

Sorry if the title is not clear - feel free to improve. Here are 3 tables (not all data is relevant to the question): CREATE TABLE `content` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
2
votes
1answer
203 views

Query results in a strange explain output

So I have a medium sized query that hits 15 tables. The query takes 36 sec on MySQL to complete. I ran EXPLAIN on the query and one of the tables returned this. '1', 'SIMPLE', 'mount_adapters', ...
3
votes
1answer
1k views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
0
votes
1answer
65 views

Check for records that break a foreign constraint (created with foreign_key_checks = 0)?

Is there an easy way to check for any records in a table that breaks any constraint? The constraint was created on a populated table using "foreign_key_checks=0".
1
vote
1answer
508 views

Does replication with SSL encryption work on MySQL 5.5?

I am trying to set up replication with slave on Amazon EC2 and master in our local environment. The setup went fine without SSL encryption. But when I set up the replication to be SSL encrypted, it is ...
1
vote
1answer
1k views

while restoring mysql database, views changed into tables

I have a full database dump and i restored it. But in my new restored server all the views are appeared as MyISAM tables. In early times dumps were restored perfectly. can anyone help me to trigger ...
0
votes
1answer
302 views

Why number of matches in fulltext search get less when we put less strings?

SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('resta*' IN NATURAL LANGUAGE MODE) show 5 results SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('restaurant*' ...
0
votes
1answer
6k views

Adding a user to MySQL with 'name'@'%' fails with ERROR 1396

I just tried to add a new user to MySQL using CREATE USER 'name'@'%' IDENTIFIED BY '...' However, it fails with the following error: ERROR 1396 (HY000): Operation CREATE USER failed for ...
2
votes
1answer
132 views

index doesn't work properly

I have this query SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count FROM single GROUP BY user_id, stage table engine is innodb, plus I haven't use any indexes except one key ...
3
votes
1answer
103 views

What is a suitable index for this query?

Below query is taking long time - around 540 seconds. The table did not have any indexes. I created indexes on columns in the WHERE clause and other fields but the query is not using these indexes. ...
0
votes
2answers
2k views

MySQL database backup every day using java code

We are developing a Java web application using Spring, Hibernate and MySQL. I want our application to automatically backup the MySQL database daily; is there any way to accomplish that with Java ...
0
votes
1answer
192 views

memcached service stopped unexpectedly

I am working with MySQL Cluster 7.2.4 memcached 1.6 I started memcached service with ndb-caching option: memcached -E /usr/local/mysql/lib/ndb_engine.so -e ...
1
vote
2answers
2k views

MySql - partition by range and unique key

i'm trying to create the following table: CREATE TABLE `s_relations_with_partition` ( `id` int(11) NOT NULL AUTO_INCREMENT, `source_persona_id` int(11) NOT NULL, `relation_type` int(11) NOT ...
1
vote
1answer
46 views

simple query is not using indexes

My query is not using indexes. it was taking around 5 seconds. output is 500 rows only. it is a simple query Please find the explain plan and table structure here. Explain Plan mysql> explain ...
1
vote
1answer
247 views

MySQL not returning successful results on statement - query looks like it's hanging but is successful

So we have a script(s) that runs in succession. Randomly this task would halt/freeze and error out simply because it did not see a returned response from the MySQL server saying the statement ran ...
19
votes
6answers
7k views

What problems will I get creating a database per customer?

I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases. We are just ...
0
votes
1answer
205 views

MySQL: select a row if that row is equal for every user

In my database I have 5 fix users, lets say user1, ..., user5. Furthermore I have a table month(name, finished, month, year) where name is VARCHAR (the name of one of the five users) and the ...
0
votes
1answer
95 views

Setting up a use voting table

I had a table to store user votes for other users. So user1 can vote for user2 (vote up or vote down). One user should be able to vote for many users, but not repeatedly to the same user; user1 can ...
1
vote
4answers
162 views

Transfer selected information from a SQL Server business server to a MySQL web server?

I have a SQL Server dedicated server used for business information on service users etc, and a VPN with MySQL for the main website operation. I wish to transfer some selected tables from SQL Server to ...
0
votes
1answer
3k views

Problem with (not) Locking Tables during Restore

First time here in DBA. I'm working to create a simple and strong database backup plan. My solution that is already working to create a gzipped, encrypted backup is: $string = "mysqldump -u$db_user ...
0
votes
2answers
1k views

MySQL Reusing calculation in SELECT clause in a VIEW

I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all? ...
3
votes
1answer
166 views

Frequent MySQL table corruptions and instability

The database I'm working on is having frequent issues. Almost every day there is a problem. Things like tables crashing or becoming corrupt. So far, they've been repairable in one way or ...
1
vote
1answer
6k views

Some MySQL slave errors?

Master version: mysql-server-5.5.14-1.el5.remi Slave version: Percona-XtraDB-Cluster-server-5.5.24-23.6.340 Binlog format: ROW based I'm using PRM to setup HA for MySQL. Sometime, MySQL slave ...
1
vote
1answer
1k views

Screwed up MySQL init_connect

I executed an incorrect SQL command: SET GLOBAL init_connect='SET CHARACTER SET=utf-8'; Now, when I try and run a script that connects to this database, I get the following error: ...
2
votes
2answers
441 views

Query design for tracking order status

I have the following tables in MySQL `orders` ( `orderID` int(10) unsigned NOT NULL auto_increment, `agentID` int(10) unsigned NOT NULL, `order_date` datetime NOT NULL, `senderID` int(10) ...
1
vote
4answers
186 views

Normalization of DB

I have designed a database using MySQL likewise My question is what else i can do to normalize the DB, for now i am using InnoDB engine for the table to maintain the relation among different tables. ...
2
votes
2answers
472 views

Negative numbers in InnoDB Status

There are some negative numbers displayed in the InnoDB Engine Status output. Anybody has encountered this or know why this happens ? ---------------------- BUFFER POOL AND MEMORY ...
0
votes
1answer
53 views

Using multiple server databases

I was just seeking some higher advice on whether or not having your main data on one database, then having separate server database to use for frequent updating and reading will prove to be an ...
0
votes
0answers
131 views

Performance Optimising MySQL query

I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5) I was wondering if there is any way to enhance this query so ...
4
votes
2answers
3k views

Large files in wamp\bin\mysql\mysql5.5.24\data directory

I have discovered these files located in the wamp\bin\mysql\mysql5.5.24\data folder on my Windows 2008 server. They are consuming about 22 GB on a 40 GB drive. I am using a LMS called moodle, that ...
1
vote
1answer
194 views

MySQL query to search string in Master-Child tables and return all rows with Master-Child relation

Need MySQL query Help. Please find schema below: Table1 PK_id Table1_Title Table1_Description 1 Test1 This is Test1 2 Test2 This is Test2 Table2 FK_id ...
0
votes
0answers
118 views

What would this non-'unique index with a unique search condition` query lock?

I am not sure about this part of the transaction manual For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
1
vote
1answer
62 views

Column order for better index

let say I have two tables like tbl_parent and tbl_children (just as an example), in the first table I have parent_id, and in the second one I (will) have something like child_id plus the parent_id, ...
1
vote
1answer
2k views

Why does MATCH() give Score 0, if there are less rows in the database?

I followed this example from the Mysql Documentation: CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT ...
9
votes
3answers
6k views

Installing default MySQL Tables in Windows (mysql_install_db)

In Linux, the default MySQL grant tables can be created with the mysql_install_db script, but that does not work on Windows. How can the default grant tables be installed on Windows? (No, I’m not ...
1
vote
2answers
265 views

MySQL: Adding Routines to a Slave

While setting up the MySQL replication, I took the mysqldump (without--opt --routinesoptions) unaware of the fact that in mysqldump, --routines it is NOT enabled by default. Questions Is there any ...
1
vote
1answer
179 views

Mysql query - find out 1000th row from a table for each user

Lets say I have a table where we log activities of user, I need to find out created_at of the 1000th activity of each user.. Following query works fine for one user.. select `created_at` from ...
0
votes
0answers
5k views

How to use SUBSTRING using REGEXP in MySQL

I have the following situation. I have to substring regular expression from description using MySQL. Description: Lorem D9801 ipsum dolor sit amet Where D9801 is REGEXP. Every strong text ...
3
votes
2answers
200 views

Choosing between MyISAM & InnoDB - two servers with related tables

I don't want to make a mistake. I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB. I read here and there that InnoDB should be ...
1
vote
0answers
1k views

mysql remote connect

Having troubles with this and I'm not sure why.. I'm trying to access a mysql database from another server. I changed the my.cnf file (uncommented the bind-address to access from all): port=3306 ...
1
vote
0answers
906 views

Migrating from Oracle to MySQL using WorkBench Migration Tool

I am currently migrating from Oracle to MySQL and I am going to use the migration tool in workbench to help me with the procedure. I have entered my source connection details as follows: Database ...
0
votes
1answer
309 views

PHP and mySQL - Binding a parameter in an if statement

Setup: I have 4 tables: guestList eventList inviteList rsvpList The descriptions of the tables: guestList +-----------+-------------+------+-----+---------+----------------+ | Field | Type ...
2
votes
2answers
128 views

How to see what was executed in the database via binlogs for specific date?

I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had ...
3
votes
1answer
2k views

Very Slow MySQL Queries, even with indexes

I have a relatively large 4-deep relational data setup like this: client_applications: (potentially 1,000's of records)    - ...    - account_id    - ...