MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
3answers
98 views

How could I connect to MySQL database running on WAMP server from other computers in same LAN?

I'm new in web development. I'm requesting help from all of you. My problem is: I installed WAMP server in my desktop computer. My desktop computer has a static LAN IP (192.168.0.101). I'm able to ...
0
votes
2answers
32 views

How to create identity field in MySQL without using auto_increment column?

I am trying to create a dynamic system when a user can create different objects and relate them to another objects. I need is to be able to generate an identity column similar to auto_increment but ...
0
votes
0answers
14 views

Can't execute a MySQL stored procedure from Java [duplicate]

I am using MySQL. I gave below permission to Java to execute stored procedures: CREATE USER rateervice@'%' IDENTIFIED BY 'rate$123'; GRANT EXECUTE ON Ratetest.* TO 'rateervice'@'%'; But here Java ...
0
votes
1answer
31 views

Complex query on big table

I have a big user table (3M), of structure: age (int) country (FK) city (text) gender (text) active (bool) My objective is to run a search finding 15 users of given age area, gender, city and ...
0
votes
2answers
28 views

How to remove multiple records from one table based on the non-existence of a record in another table?

I have two tables, 'Products' and 'Details'. -------------------------- Products [ID, Name] -------------------------- 1, Link's Simple Syrup 2, Team Shirt 3, Animal DVD ...
0
votes
1answer
44 views

pt-table-checksum - Verify MySQL replication integrity

export/import: $ mysqldump -h6dhdbm01 -p clee2 users > 6dhdbm01-clee2.users.sql Enter password: $ echo $? 0 $ mysql clee2 < 6dhdbm01-clee2.users.sql $ echo $? 0 $ pt-table-checksum: $ ...
1
vote
1answer
33 views

MySQL service stops after trying to grant privileges to a user

I am using MySQL 5.6, I was trying to create a super user for specific database but I got ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%' Then I thought maybe there is already ...
0
votes
1answer
25 views

Upgrading from MySQL 5.1.37 to MySQL 5.1.73

We have a fairly advanced website that runs on a cloud system. We have one master DB server and 2 Slave DBs that read from the Master. We're currently using 5.1.37 (I know...its old and from 2009), ...
0
votes
2answers
16 views

Trigger not working after upgrade to 5.6

I've upgraded to 5.6 and my triggers are no longer working. I loaded a working trigger example from SE to test them, and they do work which means that there is something wrong with my code. When I ...
0
votes
2answers
31 views

Sync 8 tables of differents databases on same server

I will use eight wordpress websites and i need to sync ther wp-user tables so each single user can connect through all websites. All of these will be on the same server, using the same MySQL user, but ...
0
votes
1answer
23 views

Join Operation using index defined?

I need to optimize some queries with several joins. I plan to create some indexes for these queries. I am currently using MariaDB with TokuDB storage engine. Should I expect the indexes for the join ...
0
votes
1answer
41 views

Calculating difference between consecutive timestamps. Error when i reach the hour mark

I am using MySQL I have a table that contains among other things an id a time in the format date:time (YYYYhhmmss) and a readingvalue. The difference between each reading value is 300 seconds but ...
0
votes
0answers
24 views

MYSQL : Copying to TMP table nightmare

I'm having a (AFAIK) random issue. My server is MySQL and I have a query that is sometimes 10000 times slower as you can see on this screensot : http://snag.gy/vJJ39.jpg The long time occurs during 2 ...
0
votes
0answers
29 views

MySQL Binlog Dump process stops after net_write_timeout

We have 2 MySQL 5.6.19-1 servers with master-master replication that were working fine (DB01 and DB02). One of them was rebooted unexpectedly (DB01) and seemed to be working fine, but we saw that the ...
0
votes
1answer
29 views

MySQL 5.6 replication causes 'waiting for table lock'

All of the sudden queries on slave server stopped with status "Waiting for table level lock" I restart mysql service and stop replications and locking does not show up anymore. Once I turn ...
1
vote
0answers
37 views

Summing a column based on uniqueness of another column without using temporary table

I have a table like this: CREATE TABLE `files` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `hash` char(40) DEFAULT NULL, `size` bigint(20) unsigned DEFAULT '0', ...
1
vote
4answers
24 views

How to alter/migrate the schema of a table with tons of records

I have a table which has about 1 million records, and recently i'm going to ship a new feature which requires 2 new fields. But I found it takes about minutes to get it finish, and during the ...
0
votes
1answer
25 views

How to configure my.cnf in 5.6.17?

On production server(linux machine mysql 5.6.17), I tried to find my.cnf to change the system variables, but my.cnf was not in /etc/my.cnf location. It was in usr/ directory. Also that file contains ...
0
votes
0answers
17 views

Table user, role, and function MYSQL database design

Hi im a new in designing database. At first I have made a relationship of tables users, roles, and functions these was my database design: NOTE: the users that im pointing out is the adminUsers. ...
0
votes
0answers
14 views

mysql replication error 1236 need to restart replication process

I'm using mysql replication and the slave/master crashed in the process and then I got: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in ...
0
votes
0answers
15 views

How to Merge 2 or more row in one query [duplicate]

I have table like this RID | Data 111 | banana 111 | mango 112 | cabbage 112 | carrot 113 | etc I want to select by RID Group like this RID | Data 111 | banana mango 112 ...
0
votes
0answers
14 views

Best Query/Method to Import A Database Via Workbench and Push Edits Back Out to Prod

I have a local instance of Workbench. It connects to a local dev database, and also a remote prod database via SSH. I want to be able to work on my dev database and push the changes out to the prod ...
0
votes
0answers
11 views

Conditionally updating the columns PHP/MySQL [migrated]

I have embedded the MySQL statements in PHP code. Following is my code: $sql1="UPDATE labdata1 SET Date='$d1' WHERE SL=1"; I want to set the column "Date" to null after 48 hours. What I tried is ...
0
votes
2answers
64 views

Very high load avg for MySQL database [closed]

I have a backend server which response to my clients app, it has more than 20k~25k hit per second and this make my server load average very high, I believe that my MySQL config is not appropriate. In ...
1
vote
0answers
29 views

ORA-00937 when calculating a sub-value in an aggregate select statement

The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question. I want to use an ...
1
vote
1answer
22 views

SQL : get rows from two tables relatively

I've two tables named categories and products. Simple structure is given below. categories cat_id cat_name 1 c1 2 c2 3 c3 products prod_id ...
0
votes
1answer
25 views

Amazon RDS freezes on mysqldump

My questions is quite similar to Mysqldump from the Amazon RDS. I have the same behavior. Everytime when I do a backup using mysqldump my entire RDS freezes and hence my application is not responding. ...
1
vote
1answer
46 views

First date in a week, not exceed from the month, start from specific day

I want to get first date of week from date, but not exceed from first date of month. This is the query i've tried : SET @date:='2014-10-03'; SELECT IF(DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) ...
0
votes
1answer
22 views

How to use mysql live update on a mysql table between localhost and remote server?

I have mysql table created/modifying in localhost, and I need to update those in my remote database. I have gone through lot of post regarding this scenario and I found out that I need to use mysql ...
0
votes
1answer
28 views

Select Information from All Tables [closed]

EER Diagram Question List the customer number, customer name (first and last), order number, order date, employee number, employee name (first and last), product number, product name, and order ...
0
votes
2answers
36 views

Determine last time a mysql table was accessed (i.e. SELECT'ed)

In my mysql DB schema there are old tables that are no longer used. I want to come up with a systematic way to show my developer a list of tables and last time they were read (SELECT'ed, not ...
0
votes
1answer
37 views

How can I determine the average number of transactions per second in MySQL?

I'd like to see how many transactions per second an instance of MySQL server is processing, on average (per hour and per day). Is there some query to determine this? I've looked through ...
0
votes
0answers
44 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
25 views

What is the meaning of “available RAM” for calculating MySQL max_connections

The question was asked previously, "how do I calculate MySQL max_connections" and this was the answer: max_connections = (Available RAM - Global Buffers) / Thread Buffers Since I don't have ...
0
votes
0answers
17 views

is there any limit of MySQL status variable value?

As we know MySQL status variable is a counter variable since the service start. MySQL service in my DB server restart every week (on sunday). Below is my observation since last few week: Suddenly ...
1
vote
1answer
15 views

MYSQL Database PIVOT and wants in specific format

I have below table and its data in mysql CREATE TABLE `pointhistory` ( `id` bigint(20) NOT NULL auto_increment, `channelid` varchar(64) NOT NULL, `deviceid` varchar(64) NOT NULL, `pointid` ...
0
votes
0answers
35 views

Create separate temporary table/view and variables for every query

How can I create something similar to VIEW or TEMPORARY TABLE for every query separately? I have 1 process, it creates and holds one connection (doesn't destroy connection after request, so doesn't ...
1
vote
0answers
25 views

MySQL permissions or privileges (User does not have access to metadata required)

I am using MySQL and I need to give one user to web service for access database. The web service need all permission related to stored procedure, like procedure execution. I executed the following ...
2
votes
1answer
46 views

MySQL Security - Storing lots of sensitive phone numbers securely?

Imagine a table in a database in MySQL that holds millions of telephone and cellphone numbers that relate to itemized billing for VoIP and Cellular carriers. All of these numbers are in VARCHAR ...
1
vote
1answer
27 views

I broke replication. Could this have been fixed?

I ran mysql_upgrade on both my master and slave after upgrading to a later version of mysql. Somewhere during this process replication broke for the mysql table. I was able to fix it manually, but ...
1
vote
1answer
24 views

Shrinking VARCHAR with MODIFY: data alignment and potential for data loss

I'm using MODIFY to change (shrink) a number of VARCHAR fields of various lengths in various tables. In each case, the field is called ip and is a textual representation of an IPv4 or IPv6 address. To ...
1
vote
1answer
19 views

How to conditionally raise an error in MySQL without stored procedure

I need to conditionally raise an error, but I can only use a simple statement and no stored procedure. I'd like to do something like this: select case when foo = "bar" then 1 else SIGNAL SQLSTATE ...
0
votes
1answer
23 views

Transfer relational data from one DB to another production DB currently populated with data - MySQL

Details So, unfortunately, at work the other day we put a site into production after some editing/changes that still had the development database configuration. As a result, we have several records ...
1
vote
2answers
56 views

Help in designing mysql table for large project

I am planning to create a website which allows different Companies to register in it. It will have Employees module. Each company will have a CompanyId. How do I maintain the Employees table, since in ...
1
vote
1answer
32 views

How to take physical backup of MYSQL database

I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone ...
0
votes
1answer
16 views

ibdata1 recovery mysql failed

I have hosted the Digital Ocean one mysql host with the configuration of 60GB of HD and 4GB of RAM. Some days the database stopped accepting new connections then started a investigation and found that ...
0
votes
0answers
22 views

Two types of rows with mutual fields and unique fields

I have a question about DB design. (MySQL) In my website, users can leave reviews for Stores and Products. Right now, there's a table for stores and a table for products. Each item ('item' = either ...
0
votes
1answer
22 views

MySQL: What do we mean by 0 value in in_use column of show open tables statement?

If I run show open tables command, system return 73 records. But, in_use column value for every record is 0. What does this mean? Are these tables available in cache but not used by any thread?
-1
votes
1answer
16 views

alternative foreign key syntax

in mysql you can assign primary key constraint to a column using two different syntax: after the column is defined, separated by coma, optionally in a new line id int auto_increment, primary key(id) ...
1
vote
1answer
25 views

MySQL replication duplicate key errors after error 1594

So I had this happen to me recently: mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event ...