MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
23 views

How to make a mysql statement for sent log

I have a table called "make_sent_log" CREATE TABLE IF NOT EXISTS `make_sent_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `transaction_id` varchar(255) NOT NULL, `total_base` int(11) NOT ...
2
votes
1answer
73 views
+50

User Privacy Settings DB Schema Design

On websites, like Facebook and Google+, you find settings (a.k.a. Privacy settings) which allow you to decide who is allowed to see what you share. How are such settings structured, or what is the ...
-2
votes
2answers
76 views

Update all table's columns with another table's colums

I want to update a table's all columns values from another table, but it doesn't work. I get this syntax error: You have an error in your SQL syntax; check the manual that corresponds to your ...
1
vote
0answers
33 views

Replicate existing database live

New to db admin here and I have a question. My setup is Windows Server 2012 R2, MySQL, using MySQL Workbench. I have Database A on Server A, how can I make an exact clone of this database (Database ...
2
votes
1answer
24 views

No mysql database in mysql server - how do I give users permissions?

I have a mysql server (5.5.44) running on my local Ubuntu 14 machine. I can start the interactive shell via $ mysql When I ask to see all the databases, there is only one mysql> SHOW DATABASES; ...
1
vote
3answers
33 views

Speeding Up MySQL Full Text Search

I am doing a complex full text search on a table with 130k+ records. It takes .5 seconds to complete. I was hoping it would be a bit faster. Is there anything I can do to make it faster? Below is ...
2
votes
2answers
85 views

MySQL growth 500k records/day

Im not a DBA however am an infrastructure guy used to installing and configuring database servers such as MSSQL, MySQL, MongoDB etc... I have a MySQL database which is being used for archival of ...
2
votes
1answer
21 views

Overwrite event - data from SQL Server to MySQL

In MySQL is it possible to execute an update query with values selected from external SQL Server database? I'd like to execute every night a timed query, which overwrites some data in MySQL with data ...
4
votes
2answers
158 views

Distinct Combination of Two Columns

I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other. The data is all around backup policies being run on against ...
1
vote
0answers
36 views

insertion creates duplicate with same data

I am using MySQL with PHP. I fetch data from a third party using SOAP. I have an issue with duplicate insertion of data. This occurs once or twice a day. Say there are 10 records ready to enter into ...
1
vote
0answers
39 views

Which database technologies to use? [closed]

We are a small construction company that currently has a very old, outdated database system that has been written over the past 20 years in Visual FoxPro. I have now been designated with the task of ...
2
votes
0answers
48 views

How to increment field value count using MySQL [migrated]

I have a student attendance table. Fields/data sample id | studid | smonth | syear | total_p | total_a 1 | 20 | 08 | 2015 | 1 | 0 2 | 21 | 08 | 2015 | 0 | 1 3 | ...
2
votes
2answers
31 views

encryption at rest for mysql

A MySQL database needs to contain highly sensitive data that cannot be left unencrypted at rest. But yet the database needs to remain searchable by an app. What are the options for encryption at ...
5
votes
2answers
117 views

Nested aggregation and grouping on multiple columns in MySQL

Consider the following product table (which is highly trimmed down): `id` int AUTO_INCREMENT `category_id` int `subcategory_id` int `vendor_id` int `price` decimal(6,2) `inserted_at` timestamp For ...
2
votes
1answer
29 views

Any risks in updating the system time on a mysql 5.5 server?

We just discovered one of our mysql systems has not been running ntp, with the result that the time has drifted a bit. If I enable ntp, and synchronize the time while the system is running are ...
1
vote
1answer
25 views

enable log_bin_use_v1_row_events in mysql 5.6

I have a MYSQL master with two slaves also running MYSQL 5.6. I want to create another slave and that needs to be MYSQL 5.5. I was wondering if replication will work smoothly. I have ...
1
vote
1answer
37 views

How can I Rewrite Update Query to Update Using First Record From Inner Join?

I wrote up a question about trying to do an update with an inner join last week that got a lot of answers that I am currently trying out. Here is the link to the answer I used to get to the point I'm ...
-1
votes
1answer
34 views

Pivot data - dynamic column names [duplicate]

I am trying to pivot data. The column names have to be dynamic based on the row values for subject_id column. Table Sid marks subject_id 101 20 3 101 25 2 101 60 1 104 22 3 104 ...
1
vote
1answer
40 views

fetch data from two tables

I have two tables one for property detail and other for amenities. In amenities i have inserted data one by one means it has three column amenity_id,property_id,amenity. Now I want to get the property ...
2
votes
0answers
14 views

How do I quickly tell the structural differences between two databases? [duplicate]

I am upgrading a web application to a newer version and need to migrate the data manually. The newer version uses slightly different database structure. It has added a few tables, but all of the old ...
1
vote
2answers
76 views

FLUSH TABLE : can't execute the given command because you have active locked tables mysql

If i execute these queries one by one , all get fail except first : Query1 : flush table tablename1 with read lock; Works Query2 : flush table tablename2 with read lock; Error : can't ...
0
votes
0answers
38 views

Does MySQL InnoDB compression combined with Windows compression save more space?

We have a very large MySQL InnoDB database, compressed using the following syntax at the end of the table declaration: ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 We also have windows ...
2
votes
2answers
49 views

optimize table in stored produce using phpMyAdmin causes out of sync error

I get the following error when running the code in phpMyAdmin. NOTE: I can run any other sql code (update, insert, replace) via phpMyAdmin in a stored procedure; but an optimize causes issues. NOTE: ...
1
vote
2answers
42 views

Execute code ONLY if we have the ability to do full text search on database (mysql >= 5.6.4)

I found this answer for how to have conditional logic for execution of mysql statements (http://stackoverflow.com/a/7537348/627473). Seems to work great. But I need to find a way to execute code for ...
3
votes
1answer
22 views

How to drop user@% on the MySQL CLI?

How to drop 'user'@'%' on the MySQL CLI using the mysql -e command? mysql -uroot -p -e "'myuser'@'%'"; This does not work.
2
votes
1answer
27 views

Remote MySQL host - is connecting to remote port 3306 vulnerable?

Hello experienced colleagues, I have a web application built on a MySQL database. I need to run another copy in a remote location, and refresh the data weekly. The size of the refresh is about 500k ...
6
votes
2answers
264 views

Trouble with a NOT IN query

SELECT DISTINCT d.customer_id, d.date_added FROM `order` d WHERE d.customer_id NOT IN ( SELECT DISTINCT i.customer_id FROM `order` i WHERE i.date_added > '2015-02-15 14:00:00' ) ORDER ...
4
votes
1answer
27 views

Does MySQL/InnoDB use table caches when dealing with foreign tables?

TL;DR: Does a table referred to via schema.table get held in table cache? I have a large number of client databases that our application connects to based on an http request. These are used for every ...
2
votes
1answer
64 views

How can filesystem storage (for blobs, images, etc.) be integrated with (traditional) database storage (e.g. MySQL/Postgres)?

If I use my database for relational data and opt to store large binary blobs in the filesystem, how can I integrate the two? How does this process work? I know that the database will need to store a ...
0
votes
1answer
71 views

MySQL Circular Replication

I have this scenario where I have 4 MySQL servers (A,B,C,D), as this: Server (A) is always online, and serves as the main website. Servers (B,C and D) are online on random periods of the day, and ...
1
vote
0answers
58 views

MySQL Master-Master Crash-Safe Replication: Slow UPDATE times

Environment 2 MySQL version 5.6.26 Dedicated MySQL Servers connected on a LAN. Servers Have 16 Cores with 32 GB RAM each and are really fast. Master-Master Crash Safe Replication is setup on both ...
2
votes
0answers
8 views

Update a column based on other values in the row [migrated]

Consider the following trials table: CREATE TABLE trials ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name_A VARCHAR(6), name_B VARCHAR(6), score_A INT, score_B INT); Abstractly, this represents a ...
2
votes
1answer
46 views

How to insert values in junction table for many to many relationships (MySQL)?

I'm new to relational database design and I've hit a stumbling block. I have three tables... PROJECTS project_id, name, ... PROJECTS_UNDERGROUND projects_FK, underground_FK (I have both FKs setup ...
2
votes
1answer
33 views

Database server down: file /dev/vda1 used up all diskspace

My database server went down last night because there was no diskspace available. I checked what used up diskspace and it was 28GB by /dev/vda1 I have no clue what this is. The database all together ...
3
votes
3answers
89 views

What architecture is best to store a large 3 dimensional array in MySQL?

I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy: { x, y, ...
5
votes
1answer
48 views

Getting column information from the MySQL command line

I am trying to get all the columns from MySQL using bash scripting. I want them to be stored in an array. If I write it this way: mysql -uroot -pPassword1 "select column_name from ...
3
votes
0answers
76 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to ...
2
votes
2answers
64 views

MySQL: memory requirements for django app with 1 big table (460M rows)

I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows). CREATE TABLE `genescorrelation` ...
3
votes
3answers
47 views

Problems updating with inner join with multiple on statements

I have an orders table on a database that contains geographical information about the city and state that the order is from. This includes latitude and longitude for doing haversine calculations. I ...
3
votes
1answer
38 views

Which databases are backed up by “mysqldump --all-databases”?

Which databases are included in the dump generated by mysqldump --all-databases? By my experience, that should be all the user-created databases, plus the mysql database. The database ...
1
vote
1answer
21 views

mysql - select random rows with max 3 rows per parent id

I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it ...
6
votes
3answers
40 views

Can't get access to other database

I am working on a project for my university. They gave me a login username and password for MySQL database they've setup on the server. With that username and password, I'm able to login to the ...
1
vote
2answers
62 views

MySQL LOAD DATA INFILE Error Code 1366. Incorrect integer value: ' '

I've been trying to upload a CSV table into my remote server database but I find an error. I use MySQL in a RedHat Linux distrib. My mycsv.csv file looks like this: ...
3
votes
3answers
53 views

MySQL adds broken comment script after each line

Every time I try to create a table using MySQL Workbench 6.3 CE it adds "broken" comments at the end of the script. Example: I am trying to create a table called "user". This is what the table ...
0
votes
2answers
32 views

Consistent mysqldump of mixed InnoDB and MyISAM tables from RDS instance

I have MySQL db on dedicated server. I would like it to be an external master for an RDS instance. And I want to make backups of RDS. (Currently we have mix of both InnoDB and MyISAM but we are slowly ...
1
vote
0answers
25 views

MySQL Server Instance Configuration Wizard does not start for the first time

I tried to install MySQL on my machine (win 8 64 bit). I downloaded mysql-5.6.24-winx64.exe and after completing the MySQL 5.6 setup wizard, MySQL Server Instance Configuration Wizard does not start ...
1
vote
1answer
35 views

Unable to load mysql dump file

I recently had to deal with migrating some servers over (rather was forced to by the hosting company) and we took too long to migrate our data so they did it for us. Unfortunately they just copied ...
0
votes
0answers
8 views

schema file that works for INNODB full text support on or off

I have users that will have mysql versions that DO support full text and do NOT support full text search for INNODB. Is there a way to do the following: Have sql statements for adding full text ...
2
votes
0answers
16 views

selecting data from joomla back end mysql

I have developed a front-end that call a php server side service that adresses the mysql. On my local everything works perfectly, but on distant 1and1 machine, it only works for tables created by ...
2
votes
1answer
33 views

Mariadb galera cluster nodes won't stay joined

I have a fresh install on Mariadb 10.0.20 on three servers. I'm using the same my.cnf from another running cluster, just changed the cluster name and the IPs. I bootstrap the first node, and I get a ...