MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
2answers
42 views

Inserting emoji - smartphones in MySQL database with PHP?

Well with the the asks from is quite high explanatory will only strengthen the question. APP have a chat system which be late the HTML , send it to PHP with ajax , but the usurer try to insert some ...
0
votes
3answers
283 views

How to autoincrement Prod_id based on Product_Name using trigger in mysql

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented) i need to increment Prod_Id based on the product name field and prefix field (Input will be ...
1
vote
2answers
87 views

Select Between Date Range Mysql

I want to select records between 2 dates: select * from orders where orders.created <= '2015-07-31'; select * from orders where orders.created >= '2015-07-01' and orders.created <= ...
1
vote
1answer
229 views

how to connect mysql database in my vps from remote machine?

I have installed mysql database on my vps ip, and input the following command when to login mysql. mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%'IDENTIFIED BY 'passwd' WITH GRANT OPTION; mysql> ...
0
votes
1answer
20 views

Mysql 5.5 to Mysql 5.6 upgrade Insert query is breaking

Assume column with the following definition product_id INT NULL NO_DEFAULT product_id_nn INT NOT NULL NO_DEFAULT and query insert into table (product_id,product_id_nn) values ...
1
vote
1answer
17 views

MySQL SELECT blocking an INSERT?

Is it possible for a SELECT query to block INSERT / ALTER queries? I have the following (taken from ENGINE INNODB STATUS) on my slave database: ---TRANSACTION 26DA7060, not started mysql tables in ...
2
votes
1answer
3k views

Extract MySQL information from a compressed tar.gz file

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...
0
votes
1answer
245 views

Connection to Amazon RDS MySQL instance fails with `init_connect command failed`

I'm attempting to open a connection from my Amazon RDS instance to my local dev machine. I added my IP to the security policy in Amazon's panel (read: whitelisted myself in the firewall) and added my ...
0
votes
1answer
22 views

How to verify the password generated by ENCRYPT?

I use the ENCRYPT function to generate password for my email users. SELECT ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))) This function will generate a long encrypted string which ...
0
votes
2answers
32 views

Total number of read/writes in a day in MySQL

Aside from using standard count queries with a date, is there a global way to get the number of writes (or reads for that matter) in a 24hr period on a table, or an entire schema?
1
vote
2answers
25 views

MYSQL find if a default value has been set for a table (DateTime Specifically)

I am trying to internationalize a database, but there are places where the timestamp that gets set is using NOW() instead of UTC_TIMESTAMP(), and it's using Amazon's RDS with multiple MySQL servers. ...
0
votes
0answers
15 views

Why does this simple insert fail sometimes? [on hold]

I have the following insert which I suspected might be failing frequently. I just tested it manually by making a small series of http requests manually from my browser to execute it, making sure ...
0
votes
1answer
4k views

Moving mysql datadir cause info: checking for tables which need an upgrade

So after moving the data directory to another partition, I get this error: [info] checking for tables which need an upgrade, are corrupt or were not closed cleanly. This is on Debian 7.5. The ...
0
votes
1answer
152 views

How to convert database mysql4 to mysql5

I have mysql dump file named booster.dump. It was export from 4.0.27-standard mysql server. Now I want to convert this database to mysql 5.1.73-cll. How can I do it? I search about this topic in ...
1
vote
0answers
23 views

Tuning MySQL MyISAM for large joined updates

I have a database with two tables that I would consider small. I need to update values on the larger of the two tables (1,100,000 rows, 75M MYD, 120M MYI) from the join of the two tables. The smaller ...
2
votes
0answers
12 views

Mysql transaction READ REPEATABLE and READ COMMITTED read view

I've come across something strage using Amazon RDS (MySQL 5.6.23). I had issues when concurrent transactions were locking and then calculating a value from multiple tables. The following is a quick ...
0
votes
1answer
25 views

what is the fastest way to restore MySQL database without using dump files [on hold]

what is the best procedure to quickly restore the MySQL database without using dump files. can any body help me in this regard. Thanks in advance.
0
votes
0answers
29 views

mysql deadlock issues

we are having starnge deadlock issue. We have a deadlock in mysql and it appears conitously when we run engine innodb command. the webserver it is comming from do not exist as we have manually killed ...
0
votes
0answers
21 views

Versioning the DB scripts (MySQL)

We are a development team of 4 programmers. In order to manage the DB scripts we created some sort of update script that looks like this: DROP PROCEDURE IF EXISTS our_db.Temp; DELIMITER $$ CREATE ...
0
votes
2answers
110 views

Migrating MySQL database structure with foreign keys fails

I am trying to set up a new testing server with a somewhat complex database structure by dumping the old database and importing it. But even though I use SET FOREIGN_KEY_CHECKS=0;, the import fails ...
0
votes
1answer
99 views

What to monitor (key indicators) for MySQL server

I have installed Munin for monitoring my MySQL database server. It enabled a large amount of MySQL plugins out of the box. Since running these plugins can be CPU expensive I want to limit them to the ...
0
votes
0answers
13 views

MySQL - Seconds shown for query run time is incorrect

I came into work this morning, and first thing I noticed is that one of my Slave databases was running behind. I took a look at the processlist and could see that I had the following running. SELECT ...
0
votes
1answer
23 views

Deleting near duplicates based on one varchar field

I'm getting around to cleaning a database but I need some help with deleting near duplicates in MySQL database but is only off by one character in a column. I've searched and found ways to remove ...
-1
votes
0answers
30 views

How to check if all days of the month are in the database

This is my table wys_attendence: id studid adate amonth ayear acls_id attendence 1 28 02 07 2015 10 1 2 31 02 07 2015 10 0 4 ...
-1
votes
0answers
8 views

mysql incremental backup using percona [on hold]

Can anyone suggest me what are the pro's and cons of using percona xtrabackup to make incremental backups of mysql database..
2
votes
2answers
956 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 ...
2
votes
0answers
33 views

How to store data where various subcategories have specific columns?

I'm new to database designing. I'll use MySQL and PHP. I'm designing a database in which I have a lot of categories and subcategories like given below: Product Table p_id p_title category_id ...
0
votes
2answers
6k views

MySQL shutdown unexpectedly

2013-09-09 10:21:44 5776 [Note] Plugin 'FEDERATED' is disabled. 2013-09-09 10:21:44 1624 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future ...
0
votes
1answer
15 views

Extract four digits from a column by regexp

I am sitting on a database where one column contains two data types ;) Example "1";"When the Wind Blows 0275";"James Patterson " "4";"The Reluctant Fundamentalist 1026";"Mohsin Hamid"; The ...
1
vote
1answer
20 views

Moved a MySQL Database to another server, but socket keeps pointing to localhost

I know I shouldn't bother with this, maybe I should disable the old mysql, but this is a Magento virtual appliance from Bitnami thats work as a whole (application and database on the same server). I ...
2
votes
0answers
7 views

Caching impression counts to avoid mysql inserts

We currently get a lot of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on ...
0
votes
2answers
21 views

What is happening when Seconds_Behind_Master oscillates between two very different sets ov values?

I have a Percona 5.6 slave, newly set up, that is replicating from a server that is itself a slave to a third. The middle server (dbS2, the master for dbS3) is currently ~260000 seconds behind the ...
0
votes
1answer
1k 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 ...
2
votes
2answers
2k views

ERROR 1166 (42000) at line 65203: Incorrect column name ''

I have a big snapshot database size is about 44GB in mydata.sql. When I run it overnight, it show this error message: $ mysql -u root < mydata.sql ERROR 1166 (42000) at line 65203: Incorrect ...
0
votes
1answer
491 views

Insert into a view derived from a table with a discriminator field

I am in a situation in which I have a base table with some fields and a discriminator field which could have one out of a specific set of values. From that base table, I have derived many views, one ...
0
votes
4answers
18k views

1286 - Unknown storage engine 'InnoDB'

I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
0
votes
2answers
2k views

mySQL Optimization Suggestions

I'm trying to optimize our mySQL configuration for our large Magento website. The reason I believe that mySQL needs to be configured further is because New Relic has shown that our SELECT queries are ...
1
vote
0answers
18 views

Caching impression counts to avoid mysql inserts

We currently get a LOT of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on ...
-1
votes
1answer
23 views

Mysql: How does a rollback operation be performed inside InnoDB

I would like to hear in simple words Please answer me in simple words to the question on the below scenario. ============== Engine: Innodb Trx1 : started and a DML is executed at 10:00 AM and rolled ...
0
votes
0answers
25 views

Tables not updating and deleting the entries

I am facing the below problem on updating the tables ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 3 read views open inside InnoDB Main thread process no. 2376, id ...
0
votes
2answers
5k views

How to solve “Can't connect to local MySQL server through socket” error?

I kept getting this following error every time I tried to restart MySql. It was working fine before I changed the default data directory. ERROR 2002 (HY000): Can't connect to local MySQL server ...
2
votes
1answer
70 views

select n values per group

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 ...
5
votes
1answer
7k views

SELECT LIMIT 1 per column value?

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 | ...
1
vote
1answer
151 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 ...
0
votes
1answer
1k views

SQL Server to MySQL sync, using Workbench, FreeTDS, and iODBC on 10.9

I've been struggling to get the MySQL Workbench to behave properly to complete the following: With Mavericks (OS X 10.9), connect to a SQL Server and create a migration job to a MySQL server. The ...
0
votes
0answers
30 views

Need to make sure database is fully normalised and all is accessible using MySQL and Java EE

So as per the picture: I need to create the roster table with all the data from the connecting ones - Each Roster entry will have the country,state and city, tech and subtech and client. Then ...
3
votes
2answers
4k views

Query: All employee with sum of salary > 500

In my MYSQL Database COMPANY, I have a Table: Worker : mysql> DESC `Worker`; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
113 views

ON DELETE CASCADE not working

Trying to implement a simple user follower system in MySQL, using a junction table: CREATE TABLE users ( id int NOT NULL AUTO_INCREMENT, email varchar(255) NOT NULL, username varchar(25) ...
1
vote
1answer
49 views

Delete rows where matched value is twice as much

I'm trying to match two tables with two columns, table1 and table2. Each has id and price columns. I want the query to match each id and compare the price, then delete the row if the price in the ...
1
vote
0answers
37 views

Get average of multiple rows with criteria and return those rows

I'm attempting to get any items that are the same type, but are ranked one higher that have an average equal or greater than the item ranked below. I have a table and query like ...