MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
294 views

`SELECT FOR UPDATE` in master-master mysql setup

I am benchmarking a database schema with a dozen workers processes and was wondering what a SELECT FOR UPDATE does in a master-master configuration. For the moment it seems to work, but I am either ...
1
vote
1answer
222 views

MySql cluster data node replication

What I'd like to do is to replicate data nodes within a cluster not only for backup/failover purposes but also for scale-out reasons. Is this possible? If not: why? If yes: how do I do it? Actually, ...
3
votes
1answer
1k views

MYSQL Slow SELECT with multiple joins and ORDER BY

I have this query SELECT r0_.id AS id0, r0_.deleted_at AS deleted_at1, r0_.updated_at AS updated_at2, r0_.created_at AS created_at3, r0_.is_vehicle_offer AS is_vehicle_offer4, ...
1
vote
1answer
257 views

What storage engine should I use for this MySQL table?

I have a table where I store all the urls from my website, and I update them from time to time when a visitor enters. The primary key is the url itself varchar(255). I have set it to use InnoDB but ...
0
votes
2answers
417 views

MySQL upgrade on Ubuntu - any heads ups?

I am needing to upgrade MySQL on Ubuntu, it is a production server and naturally cautious. My many googles look to be essentially saying that I need to : Backup my current mysql database and ...
3
votes
1answer
121 views

Complex MySQL Query returning almost all the correct rows

I've created a SQLFiddle with my schema and some test data. The calculated columns aren't working for some reason in SQLFiddle, but they come out fine in my MYSQL Workbench. Assume they work ...
2
votes
4answers
830 views

best mysql field type for temperatures

I'm realising that this is the first time I've stored temperatures in a MySQL DB. What do think the best mysql field type for temperatures (c) is? Decimial (3,3) ?
1
vote
1answer
1k views

PDO count(*) does not work with LIMIT and OFFSET

here's my code it works normally, but this time i want to also count the records it gives. at the point of inserting COUNT(*) it will give NULL value to counted rows $query_list_records = ...
1
vote
1answer
385 views

Need help optimizing mysql on a busy server

I am managing a server for a very busy news website with lots of select queries (read-heavy), concurrent about 4000 users online at peak time. Around 1.4Mil pages views / day The server has 42G RAM, ...
2
votes
1answer
131 views

MySQL REPLACE appears to corrupt data

For reference, this issue was posted on StackOverflow.com before coming here. In MySQL I am running multiple REPLACE queries to update hardcoded URLs in a database. Queries look like this: UPDATE ...
1
vote
1answer
753 views

Delete by primary key takes too long

What would make a query that deletes a row using the primary key of the table .. take too long ? This is a table with about 1.4M rows that gets .. 70%/30% read/write . I don't know where to look. ...
1
vote
0answers
107 views

Why do we need myisam spatial extension anyway?

If just to determine whether a point is within a rectangle, What is wrong with this query? EXPLAIN SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude FROM TableBusiness AS TB WHERE ...
-1
votes
1answer
78 views

Index Proposals

As a theoretical database question, if you are asked 'which indexes would you propose and why?' are the answers from primary, clustering and secondary index? How would we know if we should introduce ...
2
votes
4answers
1k views

One table with many rows or many tables with a few rows

I'm having a question rounding my mind. It's better to create one table and store a lot of rows there or create various tables and have a few rows on them? What's the performance and the I/O saved at ...
0
votes
1answer
284 views

MySQL Partition based on month-year

Is there a way that I can create a month-year partition on the below table? Without having to define all the permuations of month/year combos ? CREATE TABLE visitor_visits ( id int(10) UNSIGNED ...
1
vote
1answer
194 views

What is the SQL command to get 20 closest points from some center location

There are 1.6 million rows. Must not compute distance for all rows. Spatial index is available This is the table structure. The name of the table is businessauxiliary. So yes I used MyIsam, added ...
1
vote
3answers
1k views

MySQL function to return breadcrumb-like string from hierarchical structure

Show create table: CREATE TABLE `a` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`), ...
11
votes
1answer
5k views

MySQL transaction size - how big is too big?

I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction. There are many aspects, and the imports may yield anywhere between ...
0
votes
1answer
1k views

Centos 6.2 mysql configuration file help

I have a new centos 6.2 setup. Below is the configuration file. The machine is going to be fully innodb and it had 8Gb ram. What else must I include in the .cnf? [mysqld] datadir=/var/lib/mysql ...
3
votes
3answers
575 views

This query is choking the server. How can I improve it?

Following query is hogging a lot of resources, as much as that the queries are getting locked down & CPU usage goes through the roof! The MySQL Processes list shows that as the number of requests ...
1
vote
2answers
3k views

MySQL - automatically update all references to specific table to use on delete cascade?

I have a specific table for one of my software packages on a MySQL 5.1 box which I need to drop a few hundred entries. These are user names with names in uppercase, the application is case specific ...
1
vote
0answers
25 views

JBoss/Alfresco cannot write on slave after failover [duplicate]

Possible Duplicate: JBoss don't fail over MySQL slave using datasource I'm trying to set up an high available MySQL "cluster", using a Master / Slave Replication configuration. I want ...
1
vote
1answer
104 views

How can I reset the index of the rows?

I would like to select and insert data from an old table to the new table: CREATE TABLE test2 LIKE test1 INSERT INTO test2 SELECT timestamp, datenum FROM test1 WHERE datenum>=23 and ...
2
votes
1answer
3k views

JBoss don't fail over MySQL slave using datasource

I'm trying to set up an high available MySQL cluster, using a Master / Slave configuration. I want to the slave become master (e.g. not read-only but writeable) if the first node come down (like in ...
1
vote
2answers
263 views

How to recover previous values in Mysql Table?

I have MySQL Innodb table of 14M rows. There was a coloumn called is_disabled which had different values. By mistake I ran the query without the where clause: update url_queue set is_disabled=1 ...
5
votes
1answer
7k views

WAMP server performance tuning for MySQL

We have a Drupal site with nearly 2000 pages running on WAMP server. The pages also contain images, and flash videos embedded into them. So, they are taking some time to load under normal settings. ...
2
votes
1answer
1k views

Why does MySQL only sometimes use my index for a range query?

I have a table of IP address ranges and their country, here is the structure: CREATE TABLE `geoIP` ( `startBlock` int(11) unsigned NOT NULL DEFAULT '0', `endBlock` int(11) unsigned NOT NULL ...
2
votes
2answers
936 views

Innodb: Operating system error number 1117 in a file operation

i installed apache and mysql 5.5 on windows 7 64 bit. it worked but today mysql can not start. when i see at error log i find this 120616 16:35:47 [Note] Plugin 'FEDERATED' is disabled. 120616 ...
1
vote
1answer
66 views

How to order by 5, 501, 50101, 6, 601, 60101, etc?

I've got a list of ledger account numbers. These numbers go like this: 5 (main account, #5) 501 (sub account of main account 5, #01) 50101 (sub account of this account, #01) etc. So if I have a ...
1
vote
2answers
562 views

Using an older MySQL binlog filename (with position zero)? Risks? Downsides?

Let's say I'm setting up a new slave. I start with a mysqldump after running mysql -e "STOP SLAVE", transfer that and restore it to the new server. If I didn't capture the BINLOG filename and ...
0
votes
1answer
3k views

Slave start to generate relay log files but not master server

I am settings up two new servers. One is the master and the other is the slave. Below is the config file for both. Master DB [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ...
2
votes
2answers
987 views

Model a family tree database in Access 2007

I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have ...
1
vote
2answers
91 views

Trying to optimize a MySQL query that has sub selects

I have the following sql SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate, (SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as ...
3
votes
2answers
1k views

Cannot update certain rows in innodb tables

I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are ...
1
vote
1answer
423 views

What is the proper way of Adding a Field in a MySQL table with 1.6 million row

I have a table with 1.6 million rows. Then I add a field on that table. I am using hostgator as hosting. You know, they have like 16 core CPU. Result: 1. phpmyadmin doesn't work for 10 minutes 2. ...
2
votes
1answer
2k views

log_warnings doesn't seem to work for aborted connections in MySQL 5.1

The docs for 5.1 say that I can set log_warnings = 2 to capture Aborted_connects in the error log. This bug report suggests that this was only merged into 5.5 and 6.0 (and maybe 5.2) but not into ...
3
votes
1answer
1k views

Configured MySQL replication but its not working

I have configured MySQL master and slave replication setup on 5.5.x. But for some reason data is not getting replicated properly. What might be the reason? At Master: /etc/my.cnf [mysqld] ...
3
votes
1answer
212 views

How does the MySQL Query Optimizer react to a SELECT COUNT sentence?

Let's say we have a table called "customers" with these columns ("id", "name", "country", "created_at). There's an index by ("country", "created_at"). If SELECT COUNT(*) FROM customers WHERE ID ...
8
votes
2answers
8k views

What to set innodb_buffer_pool and why..?

I have 170GB of InnoDB index and Data. I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB. So what should be the optimal ...
2
votes
1answer
832 views

Correct SQL Server linked server connection settings to MySQL that will allow inserts

I'm in linked server hell! I've created a linked server to a remote MySQL database. I'm able to query and do updates. However when I attempt an insert I get an error: "[MySQL][ODBC 5.1 ...
1
vote
2answers
2k views

Can MySQL Handle Geo Location

Say I want 20 closest businesses to a location. Innodb doesn't support that. So what does? Is it possible to have some tables in some engine and other tables on different engines?
3
votes
2answers
218 views

Which database system can break me free?

I am currently running a MySQL Database for logging and analyzing those logs. My current table schema looks like this: CREATE TABLE `mylogs` ( `transfer_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ...
2
votes
1answer
639 views

Change in datatype?

I have a table on which rows are inserted on hourly basis. The row contains id, installation, product_id and hit_allowed. all of them were mediumint in the beginning. but few days back id reached it ...
1
vote
1answer
1k views

Error in login in with root user in new mysql server

I've installed a MySQL server in CentOS 6.2 machine +-------------------------+---------------------+ | Variable_name | Value | ...
1
vote
1answer
121 views

How to Archive Only MySQL Master not the Slave.?

Currently I have a Master and Slave. Both of them have same set of data(Can Say are in Sync).The Replication is running fine. What i need to accomplish is that... Need to Archive the Master(Say I ...
2
votes
2answers
7k views

MySQL Error Reading Communication Packets

In MySQL error logs, I see these quite few a warnings like these: 120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading ...
4
votes
1answer
1k views

How does max_allowed_packet affect the backup and restore of a database?

I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps ...
1
vote
1answer
77 views

Configuration Persistance Design

We have a well designed and normalized DB, but, there is a requisit that could cloud this design. This image shows the design of part of the DB. The main functionality of our application is to create ...
1
vote
2answers
518 views

MYSQL only Show columns which allow null

I need to be able to pull the column names which allow a NULL value, I know that show columns from TABLE Will give show me the table properties and whether or not the column allows null values, ...
3
votes
3answers
1k views

Comparison Tool for MySQL data base schemas [closed]

We'd like to update an old database schema for a new software release that requires changes in database schema (additional colums, column renames, table renames, datatypes, ...). Is there any ...