MySQL : Open-Source, Relational Database Management System
1
vote
1answer
46 views
mysql varchar index - will hashing value make it faster?
I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
0
votes
1answer
21 views
Why decrease the wait_timeout configuration parameter in MySQL?
Question
I'm working on an application that uses a connection pool in Apache Tomcat to communicate with MySQL. I was wondering why would you want to use a smaller wait_timeout than the default of ...
0
votes
2answers
179 views
How to tune mysql to be inmemory-like
I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is).
Is it possible to force ...
2
votes
1answer
54 views
MySQL-problem in Fetching Result set in Desired Format
I have 2 tables having the following columns
Person
------
person_id,
first_name,
middle_name,
last_name
Items
-----
person_item_id,
person_id,
item_type,
status,
ordered_date
The query is
...
0
votes
0answers
36 views
phpMyAdmin Error After Upgrade to 3.5.8.1
Im Nubie in mysql, I use that only for PHP.
to the point, I try to upgrade myphpadmin 3.5.8.1, after I that, I cant accses my database and have error like This:
#1064 - You have an error in your ...
0
votes
1answer
19 views
How do I selectively purge MySQL table rows using a list of coloum values?
I am new to databases and working with MySQL. I have a large MySQL table with the following columns: Id, Link, Timestamp.
And I have a list of links. I want to keep only the rows with link column ...
0
votes
1answer
40 views
Watch output of MySQL query over time
I'd like to run a query every few seconds and show the output in a console window. From bash, using watch:
watch "mysql -A -pXXXXX -h host -D dbname -u username --vertical -e \"SELECT rowID FROM IDs ...
0
votes
2answers
47 views
Can I find out what version of MySQL from the data files?
I have a very odd situation going on here. I had a linux box running ubuntu 8.10 and MySQL server with a Wordpress installation on it hosting an internal blog for our company. The machine that this ...
1
vote
2answers
56 views
How to avoid lagging when enabling log-slave-updates?
Master:
48GB RAM
16-core
my.cnf: https://clbin.com/hlkUo
Slave:
64GB RAM
24-core
my.cnf: https://clbin.com/viLm0
Both are running 5.5.28.
For the incremental backup purpose, I need to enable ...
2
votes
2answers
74 views
How to create a mysql stored procedure through linux terminal
In mysql console we will use the delimiter command to change the delimiter and it is useful to define the procedures.
I understood the command delimiter for only the mysql client(client side ...
1
vote
2answers
60 views
Challenge. Split database 500 records in two smaller datasets, randomly
I have a database with 500 records. I want to split these records to 75% and 25% *randomly*in order to use the different datasets for training and testing to machine learning algorithms. Does anyone ...
1
vote
1answer
30 views
Trigger not firing when deleting a record from stored procedure MySql
Bare with me, as I'm fairly new to stored procedures / triggers.
I have the following Stored Procedure:
DELIMITER $$
CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask ...
0
votes
3answers
42 views
Not getting the expected result with 'IN' and subquery
I am trying to access the ring_to_number from tbl_destinations that holds all the numbers. And field destinations has the comma separated ID like 1,3. but according to this query i just get the the ...
1
vote
1answer
22 views
Join two group_concat result
this is the query:
$sql = "select pn.id, pn.purchased_number, pn.plan, pn.ring_to_group,
count(rtg.id) as totalGroups,
concat_ws(',', group_concat(rtg.group_name), ...
2
votes
1answer
33 views
UPDATE table based on the same table
I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same ...
1
vote
1answer
28 views
Identical subquery optimisation in an update
update activitybooking set `submitted`='1' where id='958'
and (select SUM(pool1_count) from (select pool1_count from `activitybooking` where `abt`='12' and
(id='958' or ...
1
vote
2answers
40 views
What is the size of currently-existing tmp tables in innodb?
This is yet another "how to add a column to a gigantic table" question. The table on disk takes 2.5GB, and I have around 10GB of ram I can use. I have a feeling I can crank up the tmptablesize option ...
0
votes
0answers
81 views
Performance difference between MySQL and PostgreSQL for the same schema/queries
I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS.
I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests ...
-1
votes
0answers
30 views
information_schema.table_constraints doesn't exist error in MySQL
I'm new to this and Googling didn't help me with this one. I'm trying to check whether a foreign key constraint already exists like this:
$sql = 'SELECT `constraint_name`, `table_name` FROM ...
0
votes
0answers
22 views
MySQL not using resources?
Hi I have a decent setup: 16Gb RAM and a 4 cored CPU. I'm on a 64-bit Windows OS with MySQL Ver 14.14 Distrib 5.5.24 Win64 (x86). I am also using InnoDB.
The query I am using to test the setup is a ...
1
vote
1answer
50 views
Issues converting MyISAM table to InnoDB (auto column issue)
I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6.
The following is the table dump:
--
-- Table structure for table `companies`
--
DROP TABLE IF EXISTS `companies`;
...
1
vote
1answer
119 views
Master updates Slave but not Master itself
I have a setup master/slave in which applications are pointed only to master. Yesterday Master had got crashed due to "multi bit error on dimm detected" in Front indication panel in orange color.
...
0
votes
0answers
64 views
InnoDB tuning with 1G of ram limit
I am trying to calculate variable moving averages crossover with variable dates.
That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit ...
0
votes
1answer
56 views
MySQL Read Speed and Partitioning on Separate Drives
Introduction
Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
0
votes
0answers
40 views
Mysql float(13,3) not taking 10 digits [duplicate]
I have a total amount field of type float(13,3). When I tried to insert 123456789 to that field, the data changed to 123456792.000. I am not getting any truncate error. But data is changing while ...
0
votes
0answers
25 views
Defacto way of fixing constraint issues without downtime?
I come across two constraint issues all the time (UNIQUE, and referential integrity constraints) within InnoDB. My question is when these issues arise reproducibly what is typically the solution? I'm ...
0
votes
0answers
30 views
Mysql query browser can't connect, Toad, Perl, workbench and command line can?
Mysql query browser used to work fine then suddenly stopped working:
cannot connect to database server. Your connection attempt failed for user 'brianp' from your host to server at viper:3306: Could ...
3
votes
2answers
56 views
Why would I use the MySQL “system” command?
What can I do with the MySQL client's system command? Why does it exist?
The most productive use I've seen is to look around the file system (e.g., to remember the file name you want to SOURCE or ...
0
votes
1answer
38 views
I can't install db-mysql in node.js
When I execute this command: npm install db-mysql, I get the following result. What's the solution to this problem?
C:\Users\Mouad>npm install db-mysql
npm http GET https: ...
3
votes
1answer
63 views
Get data from two tables with recursive relationships
I have a MySQL 5.1 DB, being used behind a PHP application to track permissions and other items that need to be issued to staff that hold various positions within an org. To track what privileges a ...
1
vote
1answer
21 views
xtrabackup backup grants?
I'm using MySQL 5.5 + Opensuse 12.3 .
After face an issue with mysqldump (appear be a bug with OpenSuse Mysql distribution) I have looking for others backup solution.
I found the xtrabackup from ...
0
votes
2answers
45 views
Unable to drop the table since its showing waiting for meta data lock
We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on ...
0
votes
2answers
58 views
Database search with multi joins
I have a MySQL database and I want to perform a little bigger search.
I have about 10k records in one of the tables and It's expected to grow, but slowly.
The biggest problem is that to perform the ...
1
vote
1answer
79 views
Mysql Forcing close of thread 946 user
My mysql box keeping shutting down and up. Below is snippet of the log file. There is quite a number of places I notice this Forcing close of thread 946 user:
130426 12:36:28 [Note] Event Scheduler: ...
2
votes
2answers
117 views
MySQL replication: most important config parameters for performance on slave server?
I'm setting up a mysql master-slave configuration, where slave is located on a much weaker sever.
Since, if I understand correctly, slave only works on updates/inserts, what are the most critical ...
0
votes
1answer
26 views
Database for opening times of locations
I'm designing a database for opening times and created this solution.
The specifications of the database which will be a MySQL are, that a location have standard opening times in a week and can ...
-1
votes
2answers
51 views
How do I connect to a database with a blank password using a shell script?
## connect to mysql and source backup file ##
USER="root"
PASS=""
mysql -u$USER -p$PASS database_name < backup.sql
Above is my shell script which I used to source database but still its asking ...
0
votes
1answer
30 views
Problems in opening mysql query browser
My mysql query browser doesn't run. I just started to work with xampp and php.
So I just installed xampp in my local machine. Now I have a problem in opening mysql query browser... What I'm getting ...
1
vote
2answers
57 views
Is it safe to delete mysql-bin files?
I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those ...
0
votes
1answer
49 views
Creating a partial unique constraint for MySQL
I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL.
My ...
1
vote
1answer
81 views
Separating tables vs having one table
At the moment I have a table setup that looks somewhat like this:
create table tbl_locationcollections
(
id int(11) PRIMARY KEY IDENTITY, --(Primary Key),
name varchar(100) not null,
...
0
votes
1answer
29 views
mysql replica ignoring server-id [closed]
I've setup a mysql replica slave a couple months ago. After a couple hickups lately, I've decide to restore it from a clean master export.
I'm trying to start my mysql instance with this command :
...
1
vote
1answer
54 views
Is it possible for a trigger to update a row in a table using a value that was deleted?
I have a trigger which is updating a column in a different table after the row is inserted:
mysql>delimiter $$
mysql> create trigger status
after insert on table2
FOR EACH ROW
BEGIN ...
0
votes
0answers
17 views
Can i use XAMMP mysql server with Percona Xtradb cluster?
I just wanted to know if I can use a normal MySQL server (XAMPP) as part of the cluster on my EC2 Server running Percona XtraDB Cluster.
Can I just change the my.cnf config file on XAMPP with the ...
2
votes
0answers
35 views
Changing Charset to utf8 for all the fields and tables in mysql
How should I use the mysql and awk to change the charset for all the fields and tables for a specific database? I did not include username or password, so for sure it will not work.
Should I run it ...
-2
votes
0answers
48 views
how to fix error #1062 duplicate entry [closed]
whats causing this error
SQL query:
DELETE FROM `adminpanel`.`aggrement` WHERE `aggrement`.`id` =3
MySQL said:
#1062 - Duplicate entry '3' for key 'PRIMARY'
this is happening when i try ...
0
votes
1answer
52 views
best relationship for this design?
There is a table users with autoincrement primary key id.
There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid is not ...
0
votes
0answers
18 views
MySQL (v5.6.11) InnoDB 5.6.11 restoring indexes during table copy
When i see the profile for my optimize table query here is what i see for the InnoDB storage engine 5.6.11:
+----------------------+------------+
| Status | Duration |
...
0
votes
1answer
52 views
WITH ROLLUP WHERE NULL = x
I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just ...
0
votes
0answers
43 views
XML Data convert to SQL database,
I have an XML feed that i need to parse daily, i want to store all of the data from the XML feed in a SQL table so then that data can be queried and manipulated by my clients.
I can currently parse ...
