MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
6answers
15k views

How to search whole MySQL database for a particular string

is it possible to search a whole database tables ( row and column) to find out a particular string. I am having a Database named A with about 35 tables,i need to search for the string named "hello" ...
0
votes
1answer
135 views

Duplicate Relationships in Rails/MySQL Healthcare Application

Overview of the Application I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and ...
0
votes
1answer
12 views

Grant permissions on views, deny select on tables

I have a MySQL user and I want it to view ONLY the views I want and not any other table in the database. I've granted this user permissions only on certain views as following: GRANT SHOW VIEW ON ...
0
votes
1answer
560 views

MySQL failover - Master to Master Replication

My company is trying to implement a MySQL failover mechanism, to achieve higher availability in our webservices tier - we commercialize a SaaS solution. To that end we have some low-end VMs scattered ...
0
votes
1answer
14 views

Best way to create materialized view in mysql

I am using mysql 5.6, I am not being able to create materialized view here like oracle. I got one or two solution like flexview and all... Can anybody tell me best way to create materialized view in ...
0
votes
1answer
9 views

Converting Heap to MyISAM issue

At work we have a problem that has now occurred more then once. Problem is that query is in state of converting HEAP to MyISAM and it paralyzes our server( query in this state is not able to kill ...
0
votes
1answer
121 views

InnoDB Failure of some kind

I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked. InnoDB is listed under SHOW ENGINES; Any idea ...
-3
votes
0answers
39 views

SELECT returning NULL always

I have small MySQL database with table called user_information. The columns in that table are: uid, firstname, lastname,age,gender and mail. I want to get all information about user with ID.So the ...
0
votes
1answer
81 views

mysql Replication and Point in time Recovery

I have a master and a slave mysql database (version 5.1). Is it possible to dump the database only on the slave side? If I do a dump with mysqldump I have a fulldump. Which files can I use for a point ...
0
votes
1answer
390 views

MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Can someone tell me how to prevent the following commands DROP TABLE <tbl-name>; TRUNCATE TABLE <tbl-name>; from being replicated to slaves?
0
votes
1answer
26 views

How to update a huge MySQL table from a text file

I have a huge MySQL table with 1.3 TB data with InnoDB engine format. Now I have a text file containing updated values of 2.2 GB of records in the 1.3 TB of data. How do I proceed ? I am a newbie ...
0
votes
1answer
6 views

Mysql Master-Master Replication Error

Configured Master-Master Replication But both servers slave shows the below error !! Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ...
0
votes
0answers
11 views

Mysql error code 1452

I'm trying to create a database that will keep information about users. Here is the strucure; I have 3 tables 1. is the users which keeps uid and login info(username and password) 2. is the ...
-1
votes
1answer
18 views

Problem in adding foreign key

I'm having an error while adding a foreign key constrain to my database. In my database, I'm having a table named user, student, staff, complaint and review. The table user contains fields such as ...
2
votes
2answers
3k views

How do I add a MySQL linked server to SQL Server?

I am trying to add a linked server on SQL Server to MySQL Server. Note the SQL Server is installed on a separate machine with a different domain name. The SQL Server is 10.0.1.1 on old_domain.com. ...
2
votes
1answer
30 views

Sorting by two columns conditionally

Column a has category ids, column b has set orders: +-------------+-----------+ | category_id | set_order | +-------------+-----------+ | 80 | null | | 81 | null | | ...
2
votes
1answer
9k views

MySQL: InnoDB keeps crashing - how to recover?

# free -m total used free shared buffers cached Mem: 48289 35288 13000 0 347 30399 -/+ buffers/cache: 4541 43747 ...
1
vote
1answer
2k views

MySQL Subquery returns more than one row

I have a table namely tbl_plays. It has the following columns and data id(PK)| gameid | drawid | etc ---------------------------------------------- 1 | 1509 | 73 | ...
0
votes
1answer
21 views

optimizing a select query of 1.5 million entry

I have the following query: SELECT s0_.id, s0_.lowresimageurl FROM app_instagram_shop_picture s0_ FORCE INDEX ( idx_deleted_shopId ) LEFT JOIN app_instagram_shop s1_ ON s0_.shop_id = s1_.id AND ( ...
0
votes
1answer
243 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
7
votes
2answers
571 views

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

In SQL queries, we do use Group by clause to apply aggregate functions. But what is the purpose behind using numeric value instead of column name with Group by clause? For example: Group by 1.
0
votes
0answers
10 views

JDBC4/SQL or Hibernate/ORM?

I have an upcoming thesis project that may be used by the school itself if the system proves to be good. It is a web application (Struts2) for an Online Teacher's Performance Evaluation System. The ...
0
votes
1answer
39 views

MySQL dump - how to order tables?

It seems that mysqldump dumps tables in sequential order - using the same order tables are shown when running: show tables; I would like to reverse that order - is it possible to do without getting ...
0
votes
0answers
9 views

Inserting a lot of big numbers into a mysql database

The tutorials online about inserting images and other binary data into a database have you put it in a character buffer and use mysql_real_escape_string() on it. The thing is, characters are 1 byte. ...
1
vote
1answer
262 views

MySQL slave taking too long on “Queueing master event to the relay log”

I have two mysql mysql slave servers. The DB version is: mysql> show variables like '%version%'; +-------------------------+-----------------------------------------------------+ | Variable_name ...
0
votes
0answers
7 views

How can I update the amount on parentid

I'm using MySQL database,This is the first time that i handle this kind of project the Multi-Level Marketing.I am having problem in updating the amount of parentid if he gets paired means he has left ...
0
votes
1answer
621 views

Can't change root password: “The system cannot find the file specified.”

I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message: ...
2
votes
2answers
3k views

How to Log All mysql queries into log file?

I am using Mysql 5.6.12 under Wamp server environment. Now I want to log All queries into ".log" file, the queries which are running by PHP or from PHPMyAdmin, I want to log them...
2
votes
2answers
127 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 () ...
1
vote
2answers
1k views

My MySQL server time is not the same as my Server

So a month ago I had purchased a server from a hosting company. The info are; Linux Centos (latest version). I installed ZPanel which installs phpMyAdmin, MySQL and all the other good stuff. ...
0
votes
0answers
8 views

Best mysql database structure for this data

I need to create a MySQL database that can store the following data for many users: General settings (for each user) 10 url's. 10 words of 20 letters for. ...and more fields like these. Sentences ...
0
votes
2answers
11 views

SQL Query Help to get Quiz statistics

I'm coding a quiz web application and i need help. I've got three tables Nicknames : |USER_ID|NICKNAME| that stores USER_ID(auto increment) and NICKNAME registered to my system. Questions : ...
2
votes
3answers
836 views

Mysql datatype for username of maximum 15 characters

I currently have a database with the "user" table having username as one of the columns. I allow a maximum of 15 characters for the username in my system... Is it fine to have the username column as ...
0
votes
1answer
514 views

Are there any disadvantages to partitioning on financial year?

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year. The majority of ...
1
vote
2answers
381 views

Import CSV file into Mysql with Multiple Delimiters/Field Separators

I'm trying to import a large csv file into Mysql. Unfortunately, the data within the file is separated both by spaces and tabs. As a result, whenever I load the data into my table, I end up with ...
1
vote
1answer
29 views

database design for simple comment

I have a following given scenario. A user can have zero or more item. A user can comment on the item. (Just simple comment listing with name of people who comment, no need hierarchical structure for ...
0
votes
1answer
652 views

MySQL connection timeout error retrieving big results

All of the sudden our MySQL 5.6 server (on Windows server) started to give us this error: "Lost connection to server during query". We never had this problem before and we haven't touch MySQL config ...
0
votes
1answer
378 views

MySQL Master-Master Replication not syncing

SETUP: I have a WAMP MySQL Master-Master replication setup on 2 machines with sync_binlog=1 for safety. Machine config: Windows Azure Standard 2: 2 core, 4GB RAM, Windows Server 2008, MySQL 5.6.12, ...
0
votes
1answer
104 views

Simple SELECT query doesn't return FALSE if no results

This is probably a simple fix, but I can't figure it out. This is the MySQL query I have: SELECT (`clockcards`.`date` IS NOT NULL) AS `clockedin` FROM `users` JOIN `clockcards` ON ...
1
vote
2answers
101 views

MySQL login after install

I was install mysql5.5 version.When i was install mysql at that time i was completed secure installation precedure by using "sudo mysql_secure_installation" this command. Now when i trying to login ...
1
vote
0answers
22 views

MySQL column type to boost performance

I'll have a table where: userid (link to user's id in users table) foldername (a varchar, max. 260 char) entry (varchar, max. 1024) data (varchar, max. 20kb) Single user will have several ...
0
votes
2answers
4k views

How to sync two databases (alternative to mysql replication)?

Hello i have 2 mysql databases where most of the structure is constant except few tables (additions) in 2 dbs. I need to synchronize these 2 databases mainly common tables from one db to another. This ...
0
votes
0answers
13 views

Insert data from a table and with parameters

I have 2 tables, one feeds data for 2 others but I must add external value too Is this query does the job ? MySqlCommand cmd1 = new MySqlCommand("INSERT INTO tableA (dt1, dt2) SELECT (dt1, dt2) FROM ...
1
vote
1answer
437 views

MySQL relay log rotates too soon and too often

I have a simple master/slave setup where the slave is just a backup. Sometimes I notice that the relay log on the slave rotates too often and too soon, when it is still very small: 4:00 PM ...
3
votes
1answer
833 views

MySQL error 2006 MySQL server has gone away after upgrade to 5.6.10 using homebrew on Max OS X 10.8.3

I upgraded my installation of MySQL on my Mac OS X 10.8.3 using homebrew brew install mysql Everything seemed to go smoothly, but...not really, as it turns out. I cannot add a new user (neither ...
0
votes
0answers
12 views

Optimal Table Index for hourly data

I have a table that contains ~40,000,000 records and consisting of the following fields: idName - foreign key relationship to another table Date Hour Type (two possible types) Value Currently I ...
0
votes
1answer
17 views

Why storing product fees in another table is preferred?

MySql: What is the benefit of storing several types of fees in another table like Product Table -> (productid...), ProductFee Table -> (productid, feetypeid, feeamount), ProductFeeType -> ...
0
votes
1answer
16 views

using foreign key as primary key

MySQL: If I have a user table and multiple other tables like seller, buyer etc, is it okay to use user_id foreign key as primary key inside tables like seller, buyer? That'll leave gaps in primary ...
0
votes
1answer
93 views

Index to fix this query MySQL

We have an extremely large forum running lots of third party software (Most of which doesn't run all that well) and we're starting to optimize things as time allows. This query keeps popping up in the ...
1
vote
2answers
1k views

How to decide whether to enable MySQL query cache using InnoDB

How should the decision to enable / disable the MySQL query cache be made, on a server that uses just InnoDB tables. Say for example, if the cache is enabled, then how should the output of: SHOW ...