MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
2answers
27 views

How to grant super privilege to the user?

I have created a user and given privileges to the user1. `grant all privileges on db1.* to user1@'%' with grant option; Am using mysql workbench to import dumps to my database. While importing ...
0
votes
1answer
16 views

How to find the rows from a table which has exactly two 'n' s in the employee name?

I want to find all the employees whose names have exactly two n's in their name. How can this be done?
1
vote
2answers
29 views

Does having many databases affect MySQL performance?

I have huge unused databases in MySQL. Do they affect my MySQL engine performance even if they are unused? Do they consume memory on their own? Thanks
0
votes
0answers
9 views

How can I store least used table files to a slower drive?

Is it possible to separate the least used and most frequently used data files on to seperate disks when using innodb?
0
votes
0answers
6 views

PHPMy Admin Exporting Importing with Foreign Key Constraints

I have a major issue here, How do I export a MySQL Database from PHPMyAdmin whilst retaining all the table relationships(foreign key constraints) - I have noticed performing a quick export does not ...
1
vote
1answer
31 views

Mysql join two tables and limit by first input?

i am only starting to play with MySQL and i seem to have been stumped by one problem. I have one table containing a number of fields including a primary id field. I have a second table containing ...
2
votes
1answer
164 views

SQL table architecture

I'm looking for a SQL architecture solution for the following problem. The idea is that I have a validation form that contains the client's data. Each client can log in to his account and edit his ...
0
votes
0answers
29 views
+50

Securing Your MySQL Server within your team?

Recently I have been having an issue with our internal team members changing the password of the root user. I noticed the following things: OLD_PASSWORD = 1 (So all passwords on our development ...
0
votes
1answer
585 views

Mysql settings for query_cache_min_res_unit

What is the best setting for query_cache_min_res_unit for these results: +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | ...
1
vote
2answers
791 views

How to add 'root' MySQL user back on MAMP?

On PhpMyAdmin, I removed 'root' user by mistake. I was also logged in as 'root'. How can I add the user 'root' back, on MAMP?
0
votes
0answers
11 views

How do you hide tool tips in MySQL Workbench?

Is there a way to hide the tool tips that popup when you hover over objects? Especially when you hover over tables, I do not want to see the breakdown of what objects are in that table.
-2
votes
1answer
17 views

Can this architecture supports 350 concurrent access?

This question is more about server capacity issue. I have a linux server with Apache 2.24 installed (12G Ram, 32 cores CPU, Red Hat 64Bit). Install 11 instances of Wordpress, all 3.8.1, with WP ...
1
vote
1answer
45 views

Any idea why this seemingly-simple query is really slow?

Here's the query SELECT count(*) FROM registration r LEFT JOIN registration r2 ON r.baggage_group_id = r2.baggage_group_id AND r2.team_id IS NULL WHERE r.event_id = "102577"; It seems pretty ...
0
votes
1answer
38 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
0
votes
0answers
27 views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
0
votes
1answer
36 views

make MariaDB Auto-Increment-Increment dynamic

In a certain replication setup I have created multi-master replication in STAR topology using MariaDB. I have an unknown number of 'client-masters', so I have set the auto_increment_increment to 30 ...
1
vote
1answer
17 views

Need help tuning my.cnf file

I need help tuning mysql. Currently it is using a lot of CPU. I dont find any query that last longer than 3 seconds in SHOW PROCESSLIST and there are only around 20-25 running most of the time. Still ...
2
votes
1answer
50 views

Relational modeled design to store versioned files

The scenario I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the ...
0
votes
1answer
19 views

Update table from calculated temporary table

I have two tables, one containing a set of user ID's, vote direction (-1 or 1) and ID of the object they voted on (casted votes table). In the other table, the object id, upvotes, downvotes and ...
1
vote
1answer
42 views

How to disable starting MySQL service on Windows7 startup?

When I turn on my desktop (Windows 7 SP1), I often find 2 or 3 GB of memory is already occupied by MySQL processes. I don't care about how much memory a database requires, if it actually runs some ...
1
vote
1answer
10 views

How to decipher the transaction message in innodb status log.

I have several transactions that seem have stalled. Looking at the innodb status log, I found several messages similar to the following: ---TRANSACTION 69DBBB, ACTIVE 947 sec starting index read, ...
1
vote
2answers
22 views

Query to Access a specfic data in the row using python

Am using wordpress and phpmyadmin for handling my database Below is the code I use to view entries on my form using python. In the form I ask users to check/uncheck specific questions. It works as ...
1
vote
2answers
516 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
2
votes
1answer
43 views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
2
votes
1answer
564 views

MySQL - run SELECT statement on another server without defining the table structure

In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally. In MS SQL Server, however, I can run any SQL statement against a ...
0
votes
0answers
15 views

MySQL - Out of memory: Kill process 3046 (mysqld)

We run a MySQL (v5.6.17) dump backup every 4 hours, and today noticed: Apr 19 20:09:30 mysql1 kernel: Out of memory: Kill process 3046 (mysqld) score 970 or sacrifice child Apr 19 20:09:30 mysql1 ...
0
votes
1answer
61 views

Efficient way to write query for multiple table which depends on column value of a table in mysql?

In the given schema, there is a table A having column A1,A2,A3,A4 There are three more tables i.e. B,C,D If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup ...
0
votes
1answer
34 views

How to automatically Reset Query Cache for improve table cache hit in MySQL?

I have a VPS running CentOS 6.4 and 2.5GB RAM, Intel(R) Xeon(R) CPU. Its MySQL version is Server version: 5.5.35-cell. I have two active wordpress sites and two inactive sites (inactive sites use for ...
0
votes
3answers
86 views
+100

MySQL schema for recurring events - Query by specific date

I've just asked this exact same question on stackoverflow so apologies for cross posting but I only just noticed dba so thought I'd ask across here as well. I'm just about to start putting together a ...
0
votes
2answers
160 views

Massive MySQL delete from CSV file

To insert massive CSV file into MySQL, there is LOAD DATA INFILE, but is there any similar command to delete massive entries with keys stored in a massive CSV file? If not, what would you see as the ...
0
votes
1answer
92 views

Creating a database redundancy based on Mysql

I have this requirement where my web application running on a tomcat is behind a loadbalancer and I am planning to deploy this application on multiple servers. This webapp though needs a database ( ...
0
votes
1answer
513 views

consequences of using “innodb_flush_method = O_DIRECT” without having a battery backed write cache? or on a KVM guest?

Mysql 5.5.29 Innodb- 128GB Ram - 32 cores - Raid 10 SSD. Our server which is a dedicated KVM guest on a 'baremetal' is hosting our heavy read-write DB server. Everything is file-per-table. ...
0
votes
2answers
145 views

How to migrate data from SQL database to MySQL including flat files?

one of my friend has asked some asp developer to make online application where user can upload files also. jpg, doc, pdf and all these files are saved in SQL database as flat file. Now my friend asked ...
4
votes
1answer
104 views

Searching 500 million records of binary data

I will inserting into a database the signatures of 500,000,000 pictures. The signatures will be generated using libpuzzle. Each signature is 338 bytes. (so 160 GB) plus a table for searching (read ...
0
votes
1answer
46 views

Select historical prices for an exact datetime - MySQL

I have a price_list table where I store historical prices for each item. columns are - id, name, price, start_date, item_id When a price is changed, a one new row is inserted with a current ...
1
vote
2answers
63 views

Storing a Shipping Address Best Practice

I'm building a checkout system which needs to store the customer's shipping address in the database and am unsure the best way to go about it. Currently I have an addresses table which stores each ...
1
vote
2answers
31 views

Altering table Foreign Keys produces an error (errno:150) (MySQL)

This is the first database I set up so I apologize if my problem is something obvious. I'm playing around with my table design and tried to create some link tables with FKs to avoid redundant data. ...
0
votes
0answers
18 views

Can't Add Databases through PHPMyAdmin

EDIT (should've mentioned this sooner): I'm using a Mac, running OS X 10.9.2 Being fairly new to PHPMyAdmin, I thought I was removing settings/privileges for one single user through PHPMyAdmin, but ...
0
votes
1answer
713 views

How can I set a default session sql_mode for a given user?

I want to have a default session sql_mode that is different from the global sql_mode. I can set it manually, but I'd like to do it automatically. I thought I could add it to .my.cnf, but that ...
0
votes
1answer
298 views

Errors while creating multiple mysql-5.5 instances

I have installed 3rd mysql instance on my testing server. 2 instances already running without any issues. When I installed 3rd instance by mysql-5.5.30 zip source, it installed successfully but when ...
0
votes
1answer
27 views

MySQL - When Command column in Processlist shows “Prepare” or “execute”

Can someone please highlight when a "Command" shows "Prepare" or "Execute" for "Show Full Processlist" Any example which shows this would be helpful.
3
votes
1answer
254 views

Deadlock free MERGE-alike in MySQL?

I am trying to implement some parts of MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions. So, ...
1
vote
1answer
39 views

Installing MySQL in Solaris

I am trying to install MySQL 5.5 on Solaris 10. I executed the code useradd -r -g mysql mysql where a group called mysql is already created using groupadd command. I get the following error : UX: ...
1
vote
1answer
855 views

Replicating data from Oracle to MySQL

I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has ...
1
vote
2answers
67 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
0
votes
1answer
21 views

Can I estimate the row count of a large mysql table using the disk space?

Sysadmin here (not a developer by trade), so go easy please ;) I have a mysql table, on a linux server that uses innodb_file_per_table. When I try to do select count(foo) from table; It doesn't ...
1
vote
1answer
17 views

How to store temp tables in memory during table “optimize”?

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to ...
-2
votes
1answer
21 views

MySQL ran out of space, now won't start

So the partition MySQL was mounted on ran out of space. As a quick fix, I decided to move mysql's data directory to /home/minecraft/mysql/. I then changed the datadir in the config to the correcy ...
0
votes
2answers
111 views

How to duplicate related rows

Tables: Payments with auto-increment ID and one-to-many relationship to Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects. Now next month, ...
0
votes
1answer
85 views

MySQL Table rename and Grants

We are in the process of spring-cleaning our production MySQL 5.0 database, and this includes the renaming a number of tables. We are using the following syntax for the rename ALTER TABLE ...