MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
5k views

MySQL: Row size too large (> 8126)

Here's the issue. I have a table which has 300 columns in it, each up to 255 bytes (there IS a business justification for this). When I try it create it using VARCHAR(255), I go past the limit for ...
2
votes
2answers
1k views

Two primary keys to one foreign key

racing_couple ID (PRIMARY) breeding_couple ID (PRIMARY) egg IDpar(FOREIGN KEY) I have 2 IDs from 2 different tables (racing_couple and breeding_couple) and they are primary keys. In 3. table (egg) ...
3
votes
1answer
134 views

What if I want to keep using MyISAM

In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate. The script uses MYISAM tables and ...
-1
votes
1answer
688 views

mysql tried to restart itself 3 or more times, but it failed finally

it happended the second time, could be the same reason cause it. mysql tried to restart itself 3 or more times, but it failed finally. here is mysql log 130706 09:43:27 mysqld_safe Number of ...
1
vote
1answer
258 views

Cannot change root access in MySQL to %

This is a new MySQL Server install. I created my root password as part of the setup (on Centos 6.4). When I connect to the server through a terminal, I can connect to MySQL and issue commands, using ...
0
votes
2answers
203 views

MySQL database datafiles in windows SystemData directory

When I create a database and some tables in MySQL Server, it makes some data files in Windows SystemData directory in C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\. Each table has three files with ...
2
votes
1answer
367 views

List of variable defaults for InnoDB engine

I would like to list the defaults for the global variables that are innodb specific. The problem is that the following commands doesn't list any innodb variables: /usr/sbin/mysqld --no-defaults ...
1
vote
1answer
963 views

pt-table-checksum - Diffs cannot be detected because no slaves were found (1)

I'm new to Percona tools. I am trying to use pt-table-checksum as follows: pt-table-checksum h=localhost -u root -p xyz--replicate=percona.checksums --create-replicate-table --databases=mysql But ...
1
vote
1answer
196 views

MYSQL COLLATE Performance

I have a table called users with a column firstname and with collation utf8_bin I want to know what happens under the hood when I execute an query like SELECT * FROM `users` `a` WHERE ...
-2
votes
1answer
390 views

MySQL Incremental Backup (cron job): “sh: mysqlbackup: command not found” [closed]

I want to run cron job Mysql Incremental backup, Can any one pls help me. I tried: $command = "mysqldump-h $dbhost -u $dbuser -p$dbpass ". $dbname." > $backup_file 2>&1"; ...
2
votes
1answer
232 views

InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them? Posts and Comments table design below CREATE TABLE `my_posts` ( `forum_id` ...
-5
votes
0answers
61 views

Website is working very slowly [closed]

We have replication from Oracle to MySQL via Oracle Golden Gate. Yesterday because of some issues we have rebuilt the replication from Oracle to MySQL (this is called opal replication). Now our ...
1
vote
1answer
92 views

InnoDB query duration inconsistent

I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 ...
-6
votes
1answer
3k views

Using inner join to get data from multiple tables [closed]

I have 5 tables: A(a_id, a1, a2); B(b_id, b1, b2, a_id); C(c_id, c1, c2, b_id); D(d_id, d1, b_id); E(e_id, e1, e2, d_id); table b have a_id match with table a(a_id). table c have b_id match ...
2
votes
2answers
750 views

any documentation on tpcc-mysql benchmark tool

Where can I find tpcc-mysql documentation? I google it but I can't find any documentation about it.
3
votes
1answer
286 views

Splitting a table based on enum value

I am re-designing our company database, and am wondering whether or not to split this table up into two (or even three) different tables based on the type field. Prospects can become clients, and in ...
2
votes
1answer
163 views

MySQL Performance Gains By Splitting Tables?

I am designing a database schema to be used with MySQL. The database needs to keep track of a users basket items and purchased items. I've thought about two ways I could do this but not sure which ...
1
vote
1answer
528 views

Unknown storage engine 'XtraDB'

I installed Percona Server 5.6.12-rc60.4, when I want create table such as : CREATE TABLE IF NOT EXISTS `mydb`.`table` ( `id` INT NOT NULL AUTO_INCREMENT , `title` VARCHAR( 64 ) NOT NULL , PRIMARY ...
3
votes
1answer
860 views

What is log sequence number ? How it is used in MySQL?

Can anybody give an example or scenario of log sequence number(LSN) used in MySQL
3
votes
2answers
874 views

Allow SELECT on specific column only

I have a User table with 3 column; name, email, password, etc. I would like to create another user that can only SELECT the name column. SELECT name FROM User; -- Ok! SELECT email FROM ...
0
votes
1answer
1k views

Speeding up MySQL NOT IN (Subquery) [duplicate]

I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed ...
1
vote
2answers
191 views

MySQL Replicate Databases without Tables or Content

I would like to replicate all the databases from a MySQL master to a slave used for archiving data older than 30 days. It isn't common for this data to be accessed so I would like it removed from the ...
0
votes
1answer
273 views

Information about Disk Storage MySQL

I am trying to create an application for monitoring MySQL,in my application there's a part of monitor DISK STORAGE, so after more research I have not found how to calculate : used and free size of ...
0
votes
2answers
1k views

How can I better optimize my tables and a LEFT JOIN query to find items that don't yet exist in the right table?

I have two tables that represent a list of urls and their related word indexes. Here are the table definitions for reference. desc urllist; ...
1
vote
1answer
108 views

MySQL on Unix, should administrator accounts join the operating system “mysql” group?

When I install MySQL via Yum, it automatically creates the mysql operating system user and group. Then mysqld executes as mysql (instead of as root), and all the files in the data directory are owned ...
5
votes
2answers
7k views

What max_allowed_packet is big enough, and why do I need to change it?

I have MySQL (5.5) in master-slave setup and created another slave server. I stopped the original slave, dumped the data, copied and reimported and it worked fine. I noted the master_log pos of the ...
1
vote
0answers
240 views

Ignore foreign keys in mysql workbench model synchronization

I've made an ERD in MySQL Workbench, where I added foreign keys to visualize the relationships. In the actual database I want to work without the foreign keys (I'll organize that in my application). ...
1
vote
1answer
612 views

What does a round relationship icon mean in MySQL Workbench Visual Data Model

I have created a model in the Visual Data Model of MySQL Workbench, and on one single relationship, this icon shows up: What does it mean? The Relation is 1:1, and this icon shows only on this ...
1
vote
3answers
202 views

Is MySQL replication okay after writing to slave then making identical write to master?

I'm running MySQL 5.5.31 and I have a master/slave setup. I accidentally ran a mysql UPDATE on the slave, a simple one-row update like: UPDATE thetable SET somefield = 'asdf' WHERE id = 1234 I ...
2
votes
2answers
125 views

MySQL Security permissions

Is it possible in MySQL to block users from executing these statements? DESC "table" SHOW TABLES SHOW CREATE TABLE "table" Or I need to write a MySQL transport port wrapper ? GRANT ON TABLE does ...
2
votes
1answer
569 views

Is it possible to reduce a group to a row based on some criteria?

I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows ...
0
votes
2answers
40 views

This sql select must be optimized…how?

Here is my query SELECT DISTINCT SM.sales_date, (SELECT SUM(gross_sale) FROM sales_main AS INTERNAL WHERE INTERNAL.sales_date = ...
1
vote
2answers
805 views

boolean purpose column char(0) or tinyint(1)

For Boolean purpose column which one is better: nullable char(0) or tinyint(1). I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said: "If ...
1
vote
2answers
1k views

MySQL Workbench Alter Procedure

i have a problem with my workbench program. I use Mac Lion OS X and mysql workbench program. When i want to do any procedure to alter that doesn't work. Which is show to the procedure code i right ...
0
votes
4answers
3k views

Auto increment value needs to be changed inside trigger

I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table. Original structure: id varchar(15), seqno int(11) auto increment Joint PK ...
1
vote
0answers
129 views

Any solution to fix circular replication fail-over?

I've developed a point-of-sale system for our corporation, which has 25+ branches across the country. Each branch has its own MySQL POS database on a WAMP server. I want every event that occurs in ...
1
vote
0answers
322 views

Duplicate GROUP_CONCAT values

Writing a plugin for Wordpress using a custom post type and an additional alias table to hold repetition of the post events. The problem comes with duplicate post meta values. wp_post_meta table: ...
4
votes
2answers
479 views

Why monitor bytes sent and received on mysql

I want to know why we should monitor bytes sent and received on mysql ? I hope I have not asked a vague question. Thanks.
0
votes
2answers
70 views

Grouping results based on link table

I have three tables: users +----+-------+ | id | name | +----+-------+ | 1 | dave | | 2 | cher | | 3 | meg | | 4 | sarah | +----+-------+ groups +----+-------+ | id | name | ...
1
vote
2answers
10k views

How to optimize table_cache?

I searched google but couldnt find an exact answer, some have this value at 10000 or even 20000. Others say that even 1000 is too much for 1gb of RAM. Confusion. My number of opened_tables is ...
1
vote
2answers
2k views

Create multiple columns from a single column filtering criteria?

thanks in advance for your time, what I need to achieve is generate multiple columns result from a single query, the thing is this: I got a table were is storing the next: id | user_id | revision 1 ...
0
votes
1answer
1k views

What are the default settings for accessing MySQL remotely?

What is default settings in MYSQL for Remote access? In CentOS the file /etc/my.cnf does not have skip-networking or bind-address. MySQL Version is 5.5, does it mean the server is enabled for remote ...
4
votes
2answers
6k views

MySQL replication: 'Duplicated entry for PRIMARY key'

Could you please help me to understand why I'm receiving 'Duplicated entry for PRIMARY key' on a slave server after a full re-sync. Basically 'mysqldump' was running almost whole night and then the ...
1
vote
1answer
212 views

I have accidently deleted ibdata1 on an ubuntu server

I have accidently deleted the 'ibdata1' file on the server having innodb, Is there any way I can recover it? the mysql service has restarted since the deletion.
2
votes
1answer
5k views

How do I select items from a table where a single column must contain two (or more) values?

I have a MySQL database table that references different words and their locations in documents. I want to return the IDs of documents that contain all of the words. Here is an example table. docid ...
2
votes
2answers
304 views

MySQL Master-Slave setup: master is not indexed and slave is indexed

Is it possible to have a MySQL master-slave setup where the master has no indexes (for faster insertion) and the slave will be indexed? Thanks in advance!
1
vote
0answers
169 views

innobackupex: What causes Slave_open_temp_tables did not become zero after waiting 15 minutes?

As you might know, in order to assure a consistent replication state, the slave should be backup only when there are no open temporary tables: ...
1
vote
1answer
343 views

monitoring mongodb and mysql replications using nagios

I have set replication for mongoDB and mySQL. Now I have to manage them using nagios. I have to write some php-script that will read server status. I am good with nagios setup. Now I want to know ...
1
vote
1answer
87 views

Recover Wrong encoded string values in MySQL database

I have a database in which I store the alt and title values of the images. Unfortunately the program which inserts these values hasn't been connecting to the MySQL database with a UTF-8 link and the ...
1
vote
3answers
3k views

What is the best way to recover from a mysql replication fail?

Today, the replication between our master mysql db server and the two replication servers dropped. I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to ...