MySQL : Open-Source, Relational Database Management System
0
votes
0answers
5 views
How to restore a database from a backup?
So, I'm trying to restore a database from a previously backup, deleted by a big mistake with AppServer that we did. We have a folder with a lot of *.frm, *.myd, *.myi files that I do believe it's the ...
1
vote
0answers
3 views
MySQL: Migrating database with utf8 collation and charset but latin1 data to new full UTF-8 database
With my current situation I have mysql database with collation and character sets on database and tables set to utf8, but all data is still latin1. This is due to server being configured (my.cnf) ...
0
votes
2answers
14 views
How can I repair a TokuDB table?
I have a TokuDB table that works fine for most queries, but segfaults on others. I did a CHECK TABLE which indicated that the table had errors.
However, TokuDB doesn't support REPAIR TABLE. How can I ...
0
votes
1answer
23 views
Multiple “root” users in MySQL
Mysql is showing 5 root users:
| root | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | mysrverhostname |
What are these 5 for? Are there some I can ...
0
votes
1answer
12 views
CHROOT install after I already installed Mysql 5.6
I installed some time ago Mysql 5.6 on Ubuntu server 12.04 and now I'd like to enhance security by putting mysql into a CHROOT environment on that same Ubuntu server.
Is this possible without ...
0
votes
2answers
6 views
how make mysql table name case insensitive in ubuntu
I am using Ubuntu 13.10 and Mysql 5.6 and I know Database Name and Table Name are case sensitive in Ubuntu(some *nix) environment.
Now i want mysql should work for as case insensitive.
Is it possible. ...
0
votes
0answers
8 views
Probable database replication issue in LAMP setup with Master/Master MySQL replication
I have been facing issues in my high availability failover (on WAN links) LAMP setup hosting a Drupal CMS based site, approximately after every 2 months both servers suddenly stop responding to http ...
0
votes
1answer
19 views
MYSQL Master-Slave replication not working with PHP
My data gets replicated from the master database when I do it manually but when it gets inserted from PHP into the master database it doesn't get replicated to the slave database.
My Master database ...
0
votes
0answers
21 views
How to recover a crashed MyISAM table which myisamchk couldn't recover?
We take db backup by stopping the database and then issuing a tar -zcf on the mysql data directory. When we restore the data directory on another machine and try to start the slave, the mysql error ...
1
vote
1answer
15 views
Amazon RDS - and binary log rotation
New to RDS (used to having my own server in my own data center).
We are on MySQL 5.6 on RDS.
when a slave gets behind in replication lag, the master ceases to rotate out its binary logs. It is, ...
1
vote
2answers
84 views
Can counting rows be made any faster?
I need to calculate the total number of rows based on a set of criteria:
SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a';
The above query takes 0.10 sec to run on a MyISAM table with ...
1
vote
0answers
13 views
Upgrading from MySQL 5.5 to 5.6 - timestamp issue?
I'm looking at upgrading my Percona Server setup from 5.5 to 5.6. Reading the upgrade docs from MySQL sounds mostly straightforward except for anything involving time/date columns.
As far as I can ...
0
votes
2answers
46 views
Select * on inner join with common columns
I have 3 tables (mysql db) on which I want to do an inner join. The problem is that these tables have common columns. I know I could use aliases to differentiate them (select A.x as Ax, B.x as Bx, C.c ...
0
votes
1answer
24 views
Is searching an alternate index on a partitioned myISAM table concurrent or serial?
I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
0
votes
1answer
17 views
exceeding 'max_updates' resource when importing large database
I need to import a large database on my shared server.I'm using the following statement in a shell to import my database
mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME < ...
0
votes
1answer
18 views
Select command denied after importing a view [on hold]
I imported a view from our development server into my localhost, but when I try to view the table in Sequel Pro I'm getting the following error:
SELECT command denied to user ''@'%' for column ...
...
0
votes
1answer
16 views
Restoring backup from mysqldump does not remove additions
I am restoring a backup from a mysqldump and noticing that if I made additions (in the form of new tables) to the database they persist after the restore. I can go back in and remove those tables to ...
0
votes
1answer
22 views
How to Revoke and Re-grant Permissions for an in-use MySQL User
We have a user which (wrongly) has global privileges (i.e. *.* rather then database specific).
We would like to restrict this user to one database, so my approach would normally be to REVOKE ALL and ...
0
votes
1answer
36 views
Can't get rid of “temporary; Using filesort”
I can't get rid-off "temporary; Using filesort" when ordering columns from the second table
explain SELECT *
FROM post
JOIN post_plus
ON post_plus.news_id = post.id ...
0
votes
0answers
20 views
Inserting data into MySQL while looping over a result set [migrated]
How can I execute insert queries while retrieving data from database?
I mean that after running a select query where I am retrieving data using a while loop, I also want to insert some fields into ...
2
votes
3answers
44 views
MySQL: Keep the 10 most recent rows for each user
I have a table saving scores for a game that have 3 columns (more in practice, but it is easier for everyone this way):
userid : the id of the user that got the score
timestamp: the time the score ...
0
votes
1answer
19 views
Using Auto Increment and Not Null
Is there any point in following up a AUTO INCREMENT with a NOT NULL?
Seeing as the db takes care of this, I assumed a AUTO INCREMENT will always have a value?
0
votes
2answers
43 views
How to solve multidimensional data relationship
I'm having a problem, that I could solve programmatically, but for my personal (test) project I'm trying to see if I can fix it in the relational model.
Imagine a complex fashion product Pants with ...
1
vote
1answer
53 views
MySQL with slow queries
I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page.
I ...
0
votes
0answers
29 views
Which of these two queries is better - can be optimized more?
With regards to the usage of the IF statement, which is better to use?
Query #1:
SELECT
whatever
FROM
auctions AS a
LEFT JOIN
...............
WHERE
...............
ORDER BY ...
1
vote
1answer
37 views
Correct way to write this query?
I'm trying to write a query that basically is searching by price, though sometimes the items may have a discount on them and this price isn't stored in the DB, only the discount percent and dates the ...
0
votes
0answers
28 views
How can I backup/dump MYSQL database with foreign key constraints?
I wanted to take the dump of the MySQL database with all the constraints on that database. The dump (.sql file) I am having right now don't show any reference key(foreign key) relationship.
I am ...
-1
votes
0answers
20 views
How innodb stores data in its file?
As we know that innodb stores data in ibdata or *.ibd files, is innodb following any encryption method to store the data? because while viewing those files we can able to roughly identify whats are ...
0
votes
0answers
20 views
restore using mysqlbinlog failing
While doing restore from a MySQL binary log file, it is failing with the following error .
mysqlbinlog mysql-bin.000036 | mysql -u root -p asdb
ERROR 1032 (HY000) at line 48: Can't find record in ...
0
votes
0answers
14 views
On replicating all tables of a given database except for those starting with some pattern
I'd like a slave to replicate all tables of a given database except for those starting with some pattern, eg. those with names starting with 'nc' (non-critical, redundant denormalized tables). So I ...
0
votes
0answers
14 views
Upgrade 2 servers with master-master replication without data loss
We have two MySQL servers running with master-master replication in a Pacemaker cluster. If a database operation should be done, the primary server is asked first to do the job, if it fails, the ...
0
votes
0answers
15 views
Preparing a Database for a personal Collection [migrated]
I have a movie/comic/games collection in my PC and now i want it to be added in a database such that when i query for something like
SELECT genre FROM movie WHERE genre='horror'
it gives the list ...
0
votes
1answer
15 views
Method for identifying max_allowed_packet queries generating errors in MySQL
I'm wondering if anyone knows of a good way to track down queries causing max_allowed_packet errors.
Even if you turn on general query logging the query isn't logged as the
connections aborted ...
0
votes
0answers
32 views
How do I perform a batch update on the first n matching rows?
I need to perform a large number of updates at the same time. Each update needs to only affect up to a specific number of rows even though the WHERE clause might match to many more rows. So far, I'm ...
0
votes
2answers
31 views
Cannot stop MySQL 5.5 replication threads
We have a master-master replication setup using MySQL 5.5. On one of the servers I am trying to stop the slave thread, but without success. To ensure that the replication does not connect to a real ...
0
votes
0answers
25 views
USING to join multiple tables with same field
I have 4 tables, each with a common field QuestionnaireId.
I am wondering if there is any difference in output or performance difference between using USING on the field in each join or by specifying ...
0
votes
0answers
19 views
Connecting mysql on server A/ to webapp on server B/
I am connecting mysql on server A/ to a webapp located on server B/. Both servers are Ubuntu servers.
For security purposes, is there any way on server B to avoid showing the full path / IP address ...
0
votes
0answers
12 views
Output datetime from MySQL with offset
I'm currently writing to a datetime XML field from MySQL data. The MySQL field is populated with current_timestamp() but this only outputs - 2014-02-18 15:34:02 whereas I need something like ...
1
vote
1answer
33 views
Set value of character_set_client to utf8mb4
I'm trying to convert my DB to utf8mb4 by following this guide. I have set:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
init-connect='SET NAMES ...
0
votes
2answers
17 views
MySQL Master/Slave replication on same VM instance?
I'm fairly new to databases so I hope this isn't a silly question!
Is it possible to set up master/slave replication on the SAME server?
I understand there are not any advantages to this in terms of ...
0
votes
1answer
20 views
MySQL Workbench - Creating a one-to-one relationship using existing columns?
How do I create a one-to-one relationship using existing columns in MySQL Workbench?
I have an EER diagram tab open and I am selecting from the toolbar on the left hand side.
Unfortunately it seems ...
0
votes
0answers
55 views
What is wrong with this a MySQL 5.6 procedure definition? Use a variable for table name
Just can't understand why it's throwing this error?
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ...
0
votes
1answer
36 views
Forgot mysql owner/root username and password
Recently, I changed my mysql root user to someother username (say kevin). But, now both the accounts don't login with the old password. It says access denied.
I'm also not sure if the username is ...
0
votes
1answer
28 views
Finding missing records from two tables in MySQL
I am trying to compare total number of records for a particular table in two databases . Table structure is same on both the tables , but no of records varies on both the tables .
I want to find out ...
1
vote
1answer
25 views
mysqlbinlog failing with “ ERROR 1032 (HY000) at line 48: Can't find record in `` ” Error
I am facing some issue with PITR restore using mysqlbinlog . It is failing with the following error .
# mysqlbinlog mysql-bin.000035 | mysql -u root -p db
Enter password:
ERROR 1032 (HY000) at ...
1
vote
2answers
40 views
MySQL replication: slave not updated
This has been asked many times over but we really cannot get a simple Master-Slave Mysql (5.5.25) replication to work for our two dbs (FatTree and Torus). And no trace of error in the logs.
The ...
0
votes
2answers
42 views
How to store a database table in multiple disks with assigned ratio?
I am going to deploy database tables to multiple disks.
My database layout would be like following example:
There are 3 tables (Customer, Order, Product) and 4 disks (disk_A, B, C, D).
Customer: 5% ...
1
vote
1answer
34 views
Error while importing sql - Specified key was too long; max key length is 1000 bytes
I am trying to import an sql file which shows me the following error:
Error
SQL query:
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE ...
1
vote
0answers
36 views
mysql query doesn't work 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 ...
1
vote
1answer
35 views
How to relate 2 user profile tables to a single login table?
I have an Employee table , and another table named Student. Now I have a third table named the Login.
Employee has emp_id as PK , and other specific attributes for an employee.
Student has std_id as ...
