MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
2answers
16 views

Modeling invoices and orders

I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless. Model ...
2
votes
1answer
1k views

How to do While Loops?

How can we use while loops in MySQL? My test script: BEGIN SELECT 0 INTO @n; WHILE @n < 10 DO SELECT @n; SET @n := @n +1; END WHILE; END; But it has syntax errors. I've also ...
0
votes
1answer
107 views

Slow query log of wp_options WHERE autoload = 'yes'

Slow query log: # User@Host: # Query_time: 181.648376 Lock_time: 2.114138 Rows_sent: 1425838 Rows_examined: 1663532 SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'; Table ...
1
vote
2answers
10 views

Mysql user accesable with out password from the host machine but not from the remote machine

I have setup mysql and created a user 'test' and have given a password for it. Strange thing I observed is, for test user I am able to access with out password from the machine I have installed mysql ...
1
vote
2answers
11 views

Any ideas on how to parallelize mysql_upgrade?

I am migrating from 5.5 to 5.6 and would like to speed up the mysql_upgrade process. I have thousands of databases per machine and 200 tables (innodb) per db. Any suggestion how to speed up the ...
1
vote
1answer
13 views

Database design, many rows or many columns?

I am aware that this has been discussed before, but I've read conflicting information about this particular topic, so I'm hoping for a bit more decisive opinions from you. I have multiple tables, ...
1
vote
1answer
112 views

drop table from outside?

A poorly designed script ran on my MySQL 5.6 instance for 24 hours before I stopped it by killing the MySQL command line client and rebooting the machine while the script was still running. The ...
0
votes
1answer
208 views

NDB Cluster seems to ignore foreign_key_checks=OFF

According to MySQL documentation on the local foreign_key_checks variable Beginning with MySQL Cluster NDB 7.3.2, setting this variable has the same effect on NDB tables as it does for InnoDB ...
0
votes
1answer
569 views

xenforo vulnerability, limit max open connections by ip address

Trying to get some emergency help because my website is being attacked. It isn't as much as an actual attack, as it is a small bot to exploit xenforo vulnerability. ...
2
votes
0answers
13 views

Modeling discounts - Product specific and Customer specific discounts

I have a reasonably simple database but have the following problem: I'm modeling a store + a lot of non-relevant stuff database. I have the following problem. I want to allow for specific products to ...
2
votes
1answer
349 views

MySQL query returns unexpected results inside a stored procedure

This is my stored procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `isUserValid`(IN `facebookId` VARCHAR(20), IN `userAccessToken` TEXT) NO SQL COMMENT 'check if user is valid. return ...
0
votes
1answer
188 views

Does “Connect” statement for every query indicate broken connection pooling?

there's only one client application running queries against a MySQL instance. After enabling MySQL's general query log (configured to log every query) I noticed that every query starts with a ...
1
vote
2answers
129 views

mysql multiple column index Cardinality order

How does Multiple-Column Indexes work in MySQL? How is it stored internally? consider a table with Multiple-Column Indexes on two columns (X,Y). If X has High-cardinality than Y i.e., X is less ...
2
votes
2answers
306 views

Is this amount of disk IO expected with MySQL?

I am running MySQL 5.5.34 on an Amazon EC2 micro instance with EBS (elastic block storage). The instance has been running for around 10 days and according to Amazon I've accumulated around 8.5 ...
2
votes
1answer
14 views

automatically change value on the table mysql

i have table invoice in my database that contain atributes 'due date', 'date' and 'status'. What i want is data on 'status' change automatically. Example, status (unpaid change to expired) when date ...
1
vote
0answers
12 views

Mysql high cpu usage - cpanel

After an update from whm, I see a high CPU usage from MySQL (5.6). my.cfg: [mysqld] slow_query_log=1 slow_query_log_file="/var/lib/mysql/slow-query.log" long_query_time=1 skip_name_resolve ...
0
votes
1answer
212 views

How to split a row based on a delimiter into multiple rows and retain the ID and ordering by using a single SQL statement for MySQL

Scenario: A MySQL table A Delimiter (e.g. comma) is used to separate entities inside a string store in a column of the MySQL table MySQL table contain 2 columns - ID and Countries_Involved ...
1
vote
1answer
33 views

Update records from one table to another in different databases where condition is accomplish

I have two tables stored in two differents DBs and I need to perform some sync between both tables based on lastmodifieddate and lastSyncAt. Let's call them as db1 and db2. This is the DDL schema for ...
0
votes
3answers
200 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
2
votes
1answer
24 views

Is there any difference between 'LIMIT 0, 1' and 'LIMIT 1'?

I recently stumbled upon example codes, which differed by these notations. SELECT * FROM table LIMIT 0, 1 SELECT * FROM table LIMIT 1 The first argument should be considered as the offset if I'm ...
1
vote
1answer
81 views

Does PostgreSQL support online schema modification (DDL)?

Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table. Does PostgreSQL support this to any level? That is, can I add/drop columns from a table ...
0
votes
0answers
13 views

nbsp character might not be passing as nbsp to the MySQL backend

I am following a tutorial where a web application written in PHP, blacklists spaces from the input(The 'id' parameter). The task is to add other characters, which essentially bypasses this blacklist, ...
-2
votes
0answers
15 views

performance of dml statements in mysql [on hold]

how can we increase the performance of insert and update statements in mysql procedure. and also if a procedure has only updates statements Blockquote ...
0
votes
1answer
771 views

Can't change root password: “The system cannot find the file specified.”

I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message: ...
0
votes
1answer
139 views

Index to fix this query MySQL

We have an extremely large forum running lots of third party software (Most of which doesn't run all that well) and we're starting to optimize things as time allows. This query keeps popping up in the ...
1
vote
0answers
65 views

Problem I need to Insert Data every 5 minutes, not working [migrated]

Sorry for being such a Nubb This script is to insert Meteorological data from a weather station. The data comes from this text file online http://www.ndbc.noaa.gov/data/realtime2/IMGP4.txt The ...
3
votes
1answer
60 views

MySQL trigger after insert with insert and delete instructions does not work properly

I have the following trigger created in phpMyAdmin: Table: tb_agenda Time: AFTER Event: INSERT BEGIN INSERT INTO tb_realizacao (dt_agenda, titulo, ...
1
vote
1answer
667 views

Proper procedure for migrating a MySQL database to another Debian machine?

I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL. I've created a backup of all databases on the first server like so: mysqldump -uuser ...
5
votes
3answers
3k views

Using MySQL triggers or transactions?

I want to ask your opinion in using MySQL triggers or transactions in a website. Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
2
votes
1answer
62 views

Configuration required for a big mysql database around 20GB

I need to host a single machine database (constraints), wherein the database size is roughly 20gb. Most of the tables are in MyISAM. Any way I can actually run it with decent speed ? Alternatives I ...
0
votes
3answers
179 views

MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it ...
0
votes
2answers
4k views

How to force higher values of open_files_limit or table_open_cache?

I'm having a frustrating problem where setting my.cnf to values higher than I currently have them does not 'take'. This is MySQL 5.6.14 on MacOS X 10.8.3 I'm trying to address dropped connections ...
0
votes
1answer
893 views

Could not start mysql or MariaDB. Permissions were ok, but mysql-bin.index permission error

Using MariaDB 5.5.36. Here's what I got while trying to start MariaDB: # syslog Dec 14 09:40:52 vps1497 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql Dec 14 09:40:52 vps1497 ...
1
vote
0answers
20 views

Migrating Oracle 9i database to MySql database on a different Windows Server

I need to migrate my Oracle 9i database with all its tables from windows server 2003 to MySql on another Windows Server 2012. The oracle database is connected to Visual Studio web project. I have ...
3
votes
4answers
9k views

What's better/faster? MySql or FileSystem?

Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography. I'll admit my SQL queries could be better but in general what would be faster and ...
1
vote
2answers
31 views

Need simple MySQL query for below description

I have a table with [1...n] columns. I want to retrieve first 3 column names of that table not the records. Should be something like this: Select column_name(3) from table_name="sample";
5
votes
2answers
7k views

SQL command to remove prefix from a set of tables

I am no DBA. I have a situation with a MySQL database (5.0.51a) where I want to remove a 'dr_' prefix to all tables having this prefix (about 110 tables). I could rename them one by one of course, but ...
0
votes
1answer
137 views

mysql autoincrement counter

I need a way to keep auto-increment counter between mysql service restart. As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
1
vote
2answers
1k views

Which one to use? InnoDB Replication or MySQL Cluster?

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual ...
0
votes
1answer
24 views

RDS Replication Error (Apply Error 1406 / Truncation)

I have a MySQL RDS instance as a master, created a Read Replica from it, and ran some schema change operations on it. To be specific, I changed the charset and collation of all the tables and columns ...
1
vote
1answer
61 views

two similar queries with different results (one very slow)

Two almost identical queries, but one is extremely slow. All tables are innodb. Explain statements attached, but I don't know how to resolve it. userHits (table structure) -id - int - primary ...
0
votes
1answer
342 views

innodb completely crashed after interruption

I am having a major crash on mysql affecting a lot of websites. We had a crash on our SAN storage. Since that time, mysql refuse to start. I tried to reinstall mysql, but still no luck. I think ...
1
vote
1answer
34 views

mysql replication and innodb_file_per_table

Is it possible for the master (5.1) to have innodb_file_per_table = 0 and for the slave (5.1 or 5.5) to have innodb_file_per_table = 1 ?
3
votes
1answer
1k 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 ...
1
vote
1answer
338 views

Mysql Incremental Backup and point in time recovery

I'm writing a shell script using the concept of binary logs for point in time recovery. Now, the scenario is such that a full-backup will happen every alternate day and Incremental backup should be ...
0
votes
1answer
113 views

Is there any relation between BSON storage for mongoDB and MySQL storage?

I am working on an application where I need to make analysis that what will be the storage size of BSON structure if same data is stored which is stored in MySQL. Basically I am comparing MongoDB and ...
2
votes
1answer
327 views

How to repair all indexes in a MySQL database?

My database got corrupted, and now the php app is throwing this error: Invalid query: Can't find FULLTEXT index matching the column list The error is not very useful, since it doesn't say what ...
2
votes
2answers
917 views

Designing database with Category and Tags for Posts - Seeking for guidance/advice

I'm not really a database novice but I'm not an expert either. Problem: I'm having this difficulty on designing my Post, Category and Tags relationship. I've read Normalization many times but could ...
26
votes
6answers
84k views

MySQL DB import/export command line in Windows

How to export / import a database in MySQL through the command line? MySQL system configuration, WampServer server installed. OS: Windows The advice for my local host, not an live hosting...
1
vote
0answers
46 views

Database design/layout

I have an objective to record and store test data. A secondary objective is to track the progress of the test programme (it is a large programme). So I want to use MySQL (not Access or DOORS) to ...