Questions tagged [mysql]

All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

Filter by
Sorted by
Tagged with
4
votes
2answers
10k views

How can I define the datatype of a view in MySQL?

I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side. Here's a simplified query for the sake ...
16
votes
1answer
101k views

How does MySQL return a result set from a stored procedure?

I'm trying to get my head around stored procedures for use in a web application. It seems to me that whatever the last statement is in a MySQL stored procedure is seems to be what gets treated as that ...
8
votes
4answers
162 views

What are the trade offs for incrementing ids vs. fulltext keys for foreign key designs?

In a lot of relational database designs there are fields that are referenced in other tables. For example consider a user table with a unique username and a second table storing address data. One ...
8
votes
2answers
5k views

How do I search to see if a MySQL user exists on the system?

I'm not finding a user by show grants for 'username'@'localhost'' I'm thinking that perhaps the username is slightly different, so I want to do a wildcard search of all the database users. How can I ...
3
votes
2answers
2k views

Quick MySQL Backup

I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack. Or mysqldump will work ...
2
votes
1answer
6k views

How to upgrade MySQL to newer version on existing database

I am having data of 20 GB with MySQL version 5.0.24. Now I want to update MySQL to newer version 5.5.16. We can do it by taking complete database backup and uninstall old MySQL and install new MySQL ...
3
votes
3answers
197 views

Database-backed website configuration

I'm planning on adding storage for configuration options to the website I'm working on (php + MySQL). The problem is that I cannot decide between having a 2-column table (with key and value fields) ...
0
votes
3answers
2k views

How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?

I want to design a db schema concerning a schedule. I. I can use all fields in the same table, for example: id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su 1 | 1 | 08:00-...
5
votes
3answers
3k views

Log MySQL DB changing queries and users

For security reasons I need a server-side (running Debian 6.0 Squeeze) logging of all queries that may have changed the content of a MySQL DB (v. 5.1) and the user who issued it. I had to rule out ...
3
votes
1answer
260 views

How do you convert IBM DB2's recursive increment to MySQL version?

DB2 Queries: WITH TABLE1 (YEAR_END) as( VALUES (2011)) , TABLE2 (YEAR_END) AS ( VALUES(2011)) , TABLE3 (YEARS) AS ( SELECT 0+2008 FROM TABLE1 T1, TABLE2 T2 union ...
3
votes
1answer
878 views

InnoDB has a transaction running, but “SHOW PROCESSLIST;” shows nothing

I have a replicated database setup, mostly Innodb databases/tables. The query that I believe caused the issue ran in about 20 minutes on the master database. This is where it gets weird. Show ...
1
vote
2answers
882 views

MySQL UPDATE Performance Issue

I'm running out of ideas on a performance bottleneck I'm seeing on one of my projects. The table in question is pretty small, with only 5396 records. However, I'm seeing queries like this show up in ...
2
votes
1answer
460 views

MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP

In MySQL InnDB engine I created TableColumn with data type TIMESTAMP and put there as default value CURRENT_TIMESTAMP, but this value are always changed to CURRENT_TIMESTAMP when I update that by ...
4
votes
2answers
289 views

MySQL aggregate anomaly

I was checking selectivity of some columns for an index. Where is this "ignore what I give you" behaviour documented? This gives 4,851,908, 4,841,060, and 1,000,052 SELECT COUNT(*), COUNT(...
3
votes
2answers
2k views

MySQL vs SQLite on Amazon EC2

I have a Java program and PHP website I plan to run on my Amazon EC2 instance with an EBS volume. The program writes to and reads from a database. The website only reads from the same database. On ...
14
votes
2answers
13k views

Write a slow query to test slow query logging?

Is there a simple query that would take > 2 sec so that I can test the slow query logger? I am looking for something like a generic recursive or iterative statement.
6
votes
2answers
2k views

MySQL: What is the maximum number of cores used by a single MySQL server?

What is the maximum number of cores used by a single MySQL server in a production setting?
7
votes
2answers
1k views

High Disk IO, How to mitigate?

I'm a programmer, not a DBA. Be gentle :) Overview InnoDB, MySQL mod_perl script, persistent connections script called every 20 seconds by thousands of users Problem High Disk IO (presumably ...
3
votes
1answer
1k views

Configuring MySQL slave to use SSL

I am trying to set up a MySQL slave to use replication via SSL. The my.cnf file contains the following: server-id = 1012 master-host = host master-port = port master-user = user master-password = ...
2
votes
1answer
2k views

Does mysqld --ndb-cluster need to be run on MySQL Cluster data nodes?

I have my management node and sql node on the same server, and 2 data nodes (3 machines in total). Do I need to run ./mysqld --ndb-cluster (or /etc/init.d/mysql-server start) on 1) the data nodes, or ...
2
votes
1answer
2k views

MySQL Cluster — Storing table data on data nodes

I have setup MySQL Cluster with the MySQL server node and the Management node on a single server, and 2 data nodes (each on a different server for 3 servers in all). After installing, I run ndb_mgm ...
18
votes
4answers
2k views

How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours?

is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to ...
3
votes
3answers
31k views

Where does my.cnf get stored in Linux?

I am new to linux, and i just installed MySQL-server-5.1.59-1.glibc23.i386.rpm in my linux machine. I wanted to know where my.cnf file gets stored..? Will my.cnf gets created with the installation ...
1
vote
1answer
923 views

MySQL: How do I limit or prevent caching for specific tables?

I'm currently working on a database schema for tables to hold user information on a website with integrated forums. Right now my idea is to have all the data in two separate tables: one table with "...
7
votes
2answers
2k views

Single Drive vs Multiple Drives

Generally bottleneck of RDBMS (I am a MySQL user) performance is disk access. SSD provides great performance compared with conventional spindle drives. Question : Is it possible to improve ...
1
vote
0answers
138 views

MySQL Cluster — Storing table data on data nodes [duplicate]

Possible Duplicate: MySQL Cluster — Storing table data on data nodes I have setup MySQL Cluster with the MySQL server node and the Management node on a single server, and 2 data nodes (each ...
5
votes
2answers
3k views

Concurrently update with limit mysql

I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db. After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned. I ...
1
vote
1answer
1k views

storing a mirror of IMAP/pop3 folder/inbox in a local DB, what choices are there?

I have a email inbox that will soon hit 4 million messages, I have an app that runs different policies over the inbox to associate emails with each other much like the conversation plugin for ...
3
votes
2answers
359 views

MySQL: 2 separate queries run very fast, combined query with OR runs very slow

EXPLAIN SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest, firewall.name as fw, proto.proto, service, action.action, sum(count) as hits, max(date) as lastused, rule_uid.rule_uid FROM ...
3
votes
1answer
1k views

Solution for deploying multiple instances of Django Server with Database replication

have written a Django application that reads / writes data into a postgres database, at the moment I have been deploying one of these devices per client. I am faced with having to deploy multiple ...
2
votes
1answer
4k views

Host Name Resolution in mysql.db and mysql.user tables

We're seeing something peculiar about the way MySQL does name resolution -- or peculiar to us at least (-: We have two data centers, A and B. A is primary, B is used in case of failover. We have two ...
3
votes
1answer
65 views

MySQL Download Question

I want to download MySQL and use it on CentOS. But I found the downloading selection menu on the MySQL website is confusing. Which one should I choose? See the attached image. Thanks.
4
votes
3answers
7k views

indexes, foreign keys and optimization

Not a DBA so pardon my ignorance... I have a table with 20 fields of which 8 are foreign keys. Mysql has by default added an index for each foreign key. Table has about 100k rows. When retrieving ...
6
votes
1answer
3k views

FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional

fulltext match is ignoring its index when I add a number of words conditional for its boolean mode. The selects are as follows: explain select * from seeds WHERE MATCH(text) AGAINST ("mount ...
4
votes
1answer
1k views

How to best store the Google Web Ngram data?

This is a continuation of How to best store Google ngrams in a database?, which covers how to store the Google Ngram Book data. I'm looking to store the Google NGram Web data, which is slightly ...
7
votes
3answers
659 views

MyISAM Performance: Join Decomposition

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones: Converting SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON tag_post.post_id=...
10
votes
4answers
26k views

Replicating a remote MySQL database to MS SQL Server 2008

I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database. Is this possible? Can anyone outline the steps required in order to achieve this? Thanks.
3
votes
1answer
996 views

MySQL InnoDB Plugin default engine type not accepting config setting

I have a strange MySQL InnoDB issue that I've been struggling with for a few hours now. I'm asking this here, and also on the main StackOverflow site - I will cross-post the answer to the other site ...
2
votes
2answers
208 views

In MySQL, should I add an index even if the query that scans the table is only ran once a month?

Concretely, is it worth it to add an index to a query that scans the table of a mysql database, even if that index would only be used once a month? For example, having a users a table and then ...
2
votes
3answers
180 views

Ordering by date and other columns

I have a one table Q&A like below: CREATE TABLE `totaalconceptterreinonderhoud`.`acties` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `type` int(11) NOT NULL DEFAULT '3' ...
4
votes
3answers
12k views

Want to order on two columns but not the usual way

I have a table as below ID userID Date 1 2273 22/08/2011 2 2274 24/08/2011 3 2275 26/08/2011 4 2273 26/08/2011 5 2273 26/08/2011 6 2271 26/08/2011 And want result as ...
1
vote
1answer
336 views

Master port is disabled from Public IP, MySQL log is not getting refreshed

My MySQL Replication setup is over internet. Master is on Public IP. If I stop mysql service in master, immediately an error message is thrown in slave. And when I start the service, slave is ...
4
votes
1answer
892 views

Will {fn concat} work without issue in MySQL, SQL Server and Oracle?

According to this (quite old) article the principle behind ODBC escape sequences is that The ODBC driver reads the escape sequence and translates it into the DBMS-specific syntax before sending ...
1
vote
2answers
1k views

MySQL: How does AES_ENCRYPT turn the password into a key?

I am trying to figure out how to create the best secure password for AES_ENCRYPT. Specifically, how long should the password be, and how it translates into the 128 bit key length that AES typically ...
3
votes
3answers
771 views

Dynamically changeable database

We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear ...
2
votes
1answer
857 views

Will a row update check all child tables which have foreign key relationships in InnoDB?

I have a master table and around 60 to 70 child tables, all are InnoDB tables and have foreign key relationships with the master table. Foreign key relations are On-Delete Cascade and On-Update No-...
4
votes
1answer
1k views

What are the concepts that need to be gone through for MySQL DBA?

I am a MySQL Developer, besides I am handling some DBA activities like backup, restore, mysql installation, etc at a top level. Now I wanted to shift to DBA, So I wanted to know in depth, like which ...
11
votes
1answer
5k views

What is the difference between XtraBackup and rsync?

Yes, I'm going to run strace on them both... However, I would like to know ... What is XtraBackup is doing over and above rsync -ac src dest ? What's so special about XtraBbackup ? How is XtraBackup ...
25
votes
2answers
43k views

What is the safest way to switch the binlog format at runtime?

Because of the following warning in mysqld.log: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it ...
27
votes
2answers
9k views

How do I properly perform a MySQL bake-off?

I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...

1
364 365
366
367 368
370