MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
389 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 ...
2
votes
1answer
297 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 ...
1
vote
1answer
135 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
2answers
364 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` ...
1
vote
2answers
193 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 ...
0
votes
1answer
200 views

Group By days interval (aging type)

I would lIke to have Mysql group by days interval for example group by every 20 days from current day, like 1 - 20, 21 - 40, 41 - 60 and son on up to lets 120 days. The user can choose the days ...
2
votes
1answer
472 views

What causes InnoDB to write 100% more pages while slowing down answering queries?

I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, ...
0
votes
0answers
55 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 ...
0
votes
2answers
866 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 ...
1
vote
3answers
5k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
-1
votes
2answers
633 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
352 views

Mysql. Block/disconnect slow queries

I am experiencing issue. Someone 'attacked' my server: simply by searching the same phrase with multiple requests. As it is text search request and database indices are not used, the engine searches ...
2
votes
2answers
724 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.
5
votes
2answers
11k 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
1k 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 ...
0
votes
1answer
213 views

Maintenance in MYSQL when innodb_file_per_table disabled

I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace. But what if I ...
1
vote
2answers
1k views

how to import mysql system “mysql” database [closed]

This question might be already answered but it's almost impossible to google it. When I perform full backup of mysql server using mysqldump --all-databases then reinstall it from scratch I want to ...
4
votes
1answer
5k views

SUPER privilege not defined for master user in Amazon MySQL RDS

I have created one medium instance on amazon rds in asia pecific (singapore) region. i have created my master user with master password. and it is working/connecting fine with workbench installed on ...
2
votes
3answers
6k 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
1
vote
1answer
2k views

MySQL optimization

I'm trying to optimize a MySQL Server to be able to serve as many connections as it can. The server is in AmazonAWS RDS and has currently the following resources: --7.5 GB memory, 4 ECUs (2 ...
2
votes
2answers
624 views

Problem with order by in MySQL subquery

I'm running a query that runs in about 2 seconds when the subquery does not include the order by date_sold clause. However, when I add the order by clause, the query runs forever. I'm confused, since ...
1
vote
1answer
231 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
1answer
461 views

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Here is my scenario Master1 hosting DB1 Master2 hosting DB2 ... MasterN hosting DBN replicate to: Slave1 hosting DB1,DB2... DBN I've read similar questions and they recommend to start different ...
4
votes
2answers
217 views

How to re-simulate a long query stuck in mysql queue?

BACKGROUND Occasionally, in my live web app, I'll get a query that locks up the database for 1-2 minutes. Sometimes it is hard to predict which queries will do this until the load gets to a certain ...
4
votes
1answer
5k 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.
4
votes
1answer
3k views

MySQL 5.6 on OS X 10.8.2 disconnects on user creation?

Via Homebrew I just upgraded from MySQL 5.5.29 to 5.6.10 and all my queries and existing users are working fine, but whenever I try to modify permissions or create users now MySQL disconnects me. Are ...
2
votes
3answers
2k views

How to import table's data in MySql from Sql Server?

I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error. Here this error may be ...
0
votes
1answer
613 views

Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

Is it possible to pipe the result of a mysqldump straight to rsync as the source argument? Conceptually, I was thinking something like: mysqldump -u root -p database_to_backup > ...
1
vote
1answer
100 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 ...
0
votes
1answer
196 views

What is the error in this TRIGGER?

The MySQL version I'm using is 5.1.46. The query is CREATE DEFINER = CURRENT_USER TRIGGER `movimentacao_before_del_tr` BEFORE DELETE ON `movimentacao` FOR EACH ROW IF (OLD.stMov IN ("E", "C", ...
1
vote
2answers
4k views

Multiple parents and multiple children in product categories

I am making a ecommerce site. In this site I want to categorise the items into three different layers primary category sub category sub category >>electronic ...
1
vote
1answer
754 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
2answers
3k views

Mysql Can't create test file + 1067 Error

I have installed mysql-5.5.30-win32. All works fine until i create my own my.ini configuration file. When I am trying to start the service using CMD (I do run the cmd as administrator, and install the ...
1
vote
2answers
200 views

Should master and slave have same mysql version?

I have a master database running mysql 5.5.11, and I am planning to setup a new slave. I am planning to use the 5.5.30 as it is the latest version. For setting up new slave, I am copying files from ...
2
votes
1answer
744 views

How does mysql deal with queries that touches myisam and innodb tables?

In our mysql database, we use both myisam and innodb tables, though there are more myisam tables. QUESTIONS If a query involves both myisam and innodb tables, will the query use table lock or row ...
2
votes
2answers
263 views

How does mysqldump take place when there are more than one database?

I'm writing a shell script wherein I'm doing a mysqldump of all databases. My questions are: Are the tables of all databases in the server locked during the mysqldump? If I have to do an ...
2
votes
1answer
1k views

Update to session table slow

I have a number of websites with session tables on a web server, these are used by Joomla CMS and Symfony framework systems. With slow_query_log=0.6, the update queries for the session tables come up ...
3
votes
1answer
739 views

How do uninitialized variables differ from NULL in Mysql?

Related, please read first for context: MySQL stored procedure: loop through table, delete rows. Logic problem: won't exit loop because of LIMIT option in query I think this is really ...
1
vote
2answers
4k views

Mysql : Maximum number of connections on a per user basis - Possible?

Is it possible to set a maximum number of open connections that a user (NOT all users combined, just one specific user) may have at a given time in mysql? Eg: If I have a user "user1234", and want ...
1
vote
1answer
110 views

How can I select the OS User in MySQL?

I need to select the OS user during trigger execution to insert into a table. How can I select the OS User as it may be different from the MySQL user?
0
votes
2answers
1k views

Does MYSQL join before LIMIT?

Imagine that I want to populate the last 10 questions of stackoverflow. I have a table that keep the questions list, a table to keep the question info, a table to list question's asker. Now imagine ...
1
vote
1answer
75 views

Why is this query so slow?

I have a quiz site with approx 2000 questions. The function in question worked great when I wasn't tracking that many users, but now there are 300,000 question that users have seen. The function only ...
2
votes
1answer
96 views

How does MySQL resolve schema information?

I’ve used MySQL on windows7. Recently my machine was infected by a virus. After that SHOW TABLES and SHOW DATABASES statements don’t show any tables and databases but I can see data from tables ...
2
votes
1answer
129 views

How can I programmatically grant (and revoke) temporary database access in MySQL?

Suppose I have a datastore comprising many databases. For the purposes of this question, let's assume MySQL, but I suspect it might apply to many technologies. The stored records aren't particularly ...
0
votes
1answer
1k 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 ...
0
votes
0answers
247 views

How to do point in time recovery of binary log files into .sql file

I have written a shell script to do an incremental backup of MySQL database. For point in time recovery I'm using the binary logs generated in /var/lib/mysql directory. For point-in-time recovery, Im ...
2
votes
1answer
1k views

How to choose a good value for MySQL relay-log-space-limit?

I just read this blog post about relay-log-space-limit on MySQL. I am convinced that the 'unlimited' default is indeed not very good. But how do I choose a good value for it? We are using ...
0
votes
3answers
737 views

Mysql optimization help

I've tried optimizing mysql as good as I could, but apparently I'm not to good at it :-) So I come to you guys for help. Server specs are: AMD Opteron CPU 8 cores 16 GB RAM 2x2.000 GB HDD 7.200 RPM ...
4
votes
2answers
402 views

How to sort category by path and sort order?

first of all I am sory for my english. My category structure is shown below. My purpose is to get category structure with single query. I sort my table by path and I get category tree. sort_order : ...
0
votes
1answer
676 views

How to apply patches in MySQL in any version

How to apply patches in MySQL 5.1.67 or any version. because we need to fix so many bugs in our version without any upgrading. please suggest me .