MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
529 views

Unable to restore dump file in mysql5.5 [duplicate]

I'm trying to restore a mysql5.5 dump file using mysql command prompt using the command given below: mysql> -u <username> -p <password> <dbname> < <dumpfile> But it is ...
0
votes
1answer
50 views

Two dichotomy views of a table, and means to express this as a stored procudure?

There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events. How to express ...
1
vote
1answer
106 views

MySQL GRANT control

How can I write a script to automate MySQL database grants in a very easy way? I have a config file that contains the grant changes I want to make. I want the script to read the config file, and ...
0
votes
1answer
1k views

MySQL Fulltext match with forward slashes

I'm trying to run a fulltext match against a product database, where the product names have forward slashes in them. These are staple sizes, so it's quite important that they get matched. However, ...
1
vote
3answers
554 views

Which RDBMS has the most advanced “table-valued” functions?

Every Database Management System focuses on specific features and applications. I am using mysql, probably as it is popular and satisfies common needs. However, I came across a problem, which needs ...
0
votes
1answer
144 views

Sleep Processes are Running Longer Than Allowed

From time to time my mysql server will run into a "too many connections" error. I believe part of the problem is large amount of sleep processes that are running, some over 1000 seconds. When ...
0
votes
1answer
3k views

How to copy MySQL Database from Linux to Windows?

I would like to copy a MySQL database form Linux to Windows, from a little research I found this url http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html where the author says we ...
2
votes
2answers
3k views

MySQL Replication without stopping master

I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then ...
2
votes
2answers
3k views

When to create multiple-column index?

When to create multiple-column index in Mysql? Should I measure the slow queries and create multiple-column index for them?
2
votes
1answer
372 views

How do I ensure consistent MySQL backups?

How can we ensure MySQL backups are consistent when they are taken as full backups as well as incremental backups using binlogs? According to me, I record the COUNT(*) from random tables after ...
6
votes
2answers
3k views

A database in MySQL is a schema in Oracle : I do not understand this statement

Please help me understand this statement: A database in MySQL is a schema in Oracle. I have just started to use Oracle and I find it different from other RDBMS softwares I have used like MSSQL, ...
4
votes
1answer
5k views

Enable MySQL to use more than 1 core (multicore) [duplicate]

I am running a very intensive MySQL query and noticed that only 1 core is being used to 100% while the other cores are idle. The system is running Ubuntu 12.04 and MySQL 5.5.28 Is there a way to ...
2
votes
2answers
1k views

mysqldump: flush-privileges option

I'd like to get some clarification about the --flush-privileges option for mysqldump. Here's the description of the option from the MySQL docs: --flush-privileges Send a FLUSH PRIVILEGES ...
3
votes
1answer
110 views

Process sessions foreach minute

I have a table that stores sessions. The columns are: ID, Start (Timestamp) and Stop (Timestamp, can be NULL). It's for online radio. A listener connects to the stream, and a row is created. Once ...
1
vote
2answers
74 views

MySQL design problem

I have nearly no experience in database design, so forgive me if I'm asking something super nooby and ridiculous :) I have an item I need to create. This item will be constructed from 3-4 types of ...
1
vote
2answers
58 views

Separated databases, separeted datas

We have a global project. We will have some servers all continents. We separate the datas because it is not necessary that they be in one place together. BUT! We need unique IDs (MySQL, InnoDB). I ...
13
votes
1answer
12k views

Possible INDEX on a VARCHAR field in MySql

I am working in a MySql database, with a table like this: +--------------+ | table_name | +--------------+ | myField | +--------------+ ...and I need to make a lot of queries like this (with ...
1
vote
1answer
415 views

How can I create a MySQL database based on a SQLite schema?

I have MySQL on my server and different SQLite databases on a client. I'm trying to find a way to create the same databases on the server. Since a SQLite schema is actually a CREATE query, one way ...
0
votes
2answers
828 views

How to take a database dump for selective set of values in few tables?

create table foo (id, val1, user_id,...) create table bar (id, foo_id, val2, ...) create table baz (id, bar_id, val3, ...) select * from foo where user_id = 1; select * from bar where id in (select ...
2
votes
1answer
2k views

Better query to select multiple columns into a single output column?

I have the following query that sort of works, but I find it to be kludgy and inefficient. Is there a better way to write this query? I know this is a bit of an odd query, else I would probably have ...
1
vote
1answer
1k views

Storing metadata of various data types in a MySQL database

I am currently designing a MySQL database that will store a variety of documents in different digital formats (mostly docs, spreadsheets, and pdfs). Each document will have several metadata values ...
2
votes
1answer
500 views

How to write a shell script to check the mysql database backup status.

I have mysql production databases running in main server(Linux) and from this server every night the backup is taken into local server. Now i want to find a shell script to check if all the databases ...
3
votes
1answer
879 views

Why is InnoDB auto_increment column skipping over large blocks of unused values?

We have an active and important InnoDB table with about 8M rows. We just noticed that for several months, the primary key value (a BIGINT) has been jumping by hundreds at a time. I turned on query ...
0
votes
1answer
451 views

Making a message system

I have created a messaging system with MySQL structure is as follows `message_id` int(11) NOT NULL AUTO_INCREMENT, `message_from` int(11) NOT NULL, `message_to` int(11) NOT NULL, ...
7
votes
3answers
3k views

Is MySQL Replication Affected by a High-Latency Interconnect?

We've got a vanilla master and slave MySQL setup that reside in different data centers, and another slave in the same datacenter as the master. The bandwidth between the datacenter is pretty high (in ...
1
vote
1answer
516 views

mysql query - how to delete duplicates based on highest int?

I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId. ...
2
votes
1answer
588 views

Is it okay to have different lower_case_table_names value on master and slave?

I have a OSX MySQL master with lower_case_table_names set to 2. I have just setup a Linux MySQL slave with lower_case_table_names=1. I haven't started replication on the slave, but everything looks ...
1
vote
1answer
1k views

mysql restart won't kill child processes on CentOS

I have a mysql database running on a CentOS server. I have noticed that, when left to run for several weeks without restarting the service, then when I need to do a restart (/etc/init.d/mysqld ...
1
vote
2answers
1k views

How to get non repeat results

I've two tables default_tb_persona and default_tb_envio, this are the DDL for those tables: CREATE TABLE `default_tb_envio` ( `codigo` varchar(7) NOT NULL, `fecha` date NOT NULL, `id_modalidad` ...
2
votes
0answers
81 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
1
vote
2answers
375 views

managing a growing database in the long run (10 years from now)

I'm close to finishing-up a WEB app (PHP + mysql website portal), which will start slowly, as in uploading few pictures and inserting few rows in main database table, but soon, and over few years, I ...
1
vote
1answer
141 views

Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?

I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the ...
1
vote
1answer
2k views

View dropped, now shows as table “in use”, can't drop

I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid. I tried dropping the view so I could recreate it, then MySQL said it was a table ...
0
votes
0answers
63 views

How can a SQL database be migrated to MySQL faster?

I have a SQL Server DB. I want to migrate that DB to MySQL. In my SQL Server DB, some tables has about 50,000 to 1,00,000 records. I am trying to migrate the DB by DB migration tool embedded in MySQL ...
7
votes
2answers
523 views

What is the purpose of the USING parameter in SQL?

Working with some code on a standard MySQL database, and I came across this statement: SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 What's throwing me is: what is USING for, ...
-1
votes
2answers
1k views

SELECT only values THAT are different

I have two instances user1 and user2. They have colors u_id | color<br> 1 | red<br> 1 | green<br> 2 | red<br> 2 | blue<br> I need a mysql request, that ...
1
vote
2answers
2k views

How do I update last row of a group in MySQL

I have a table like this: +----------------------------+ | user | date | current | +----------------------------+ | 1 | 20010101 | 1 | | 1 | 20020202 | 1 | | 1 | 20030303 ...
2
votes
2answers
1k views

What is the difference between using varchar(100) and varchar(255)?

I want to know difference between varchar(100) and varchar(255) in terms of memory usage and programming problems of using more memory.
2
votes
3answers
14k views

How to use 2 auto increment columns in MySQL phpmyadmin

Is it possible to use 2 auto increment values ? One starting from 0 Another starting from 4000400 Please help me
9
votes
2answers
24k views

What is the difference between optimize table and analyze table in mysql

What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.
2
votes
1answer
348 views

FLUSH TABLES stays in the processlist even after execution

Here is my code from Stored Procedures in Mysql FLUSH TABLES; TRUNCATE TABLE tbl_cdrload; TRUNCATE TABLE tbl_cdrdetails_temp; Then i execute a query to load a file in to database as below LOAD ...
1
vote
2answers
2k views

Master-Master Replication in MySQL

Good day everyone! I want to clarify something about the behavior of master-master replication in MySQL, with respect to the logs i.e. bin-log and relay-log, it use in performing replication. I ...
1
vote
1answer
2k views

Graph Database with “non-graph” data

--- Update --- Thanks for the comments and the help so far. And I apologize for not specifying the question further. I have updated the question below. --- Update --- Currently I am asked to develop ...
1
vote
1answer
338 views

Create a mysql slave database from master database

I have a mysql master database running. I want to have a slave database. So, I stopped the master mysql database and copied it to the slave machine. I had bin log enabled on master so even those files ...
0
votes
2answers
1k views

How do I get spatial index feature if I mainly use innodb?

My current strategy is to create another table in myisam that contains those spacial information. Then if I want to find 20 closest business, I'll just use join. Is this a good idea?
2
votes
1answer
213 views

Receive strange error during mysql auto-repair check optimize

I am running the command: /usr/bin/mysqlcheck -hlocalhost -uDATABASE_NAME -pPASSWORD --auto-repair --check --optimize DATABASE_NAME And up until this past week, this command has always run just ...
12
votes
1answer
10k views

Is there a graceful or safe restart for mysql like for apache httpd?

I would like to restart mysql gracefully just like httpd where threads are served before restart. I would dislike queries breaking.
2
votes
1answer
853 views

Disabling “SHOW TABLES;” on mysql

I have been looking all over the web now, and can not seem to find the option to disable this command. I think this is quite a risky security hole. There is an option to disable SHOW DATABASES; , but ...
4
votes
2answers
385 views

Foreign key constraints with MySQL/InnoDB

I am try to do some performance testing with varchar foreign keys vs int foreign keys and was wondering how foreign key constraints effect performance of MySQL. I assume that they effect ...
1
vote
1answer
182 views

Mysql setting a record as deleted or archive

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is ...