MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
7 views

Connection to mysql database- alternating usernames

My server has a max_connections value of 400 and a max_user_connections of 250. Since I am always connecting with the same user name, my effective max_connections is 250 instead of 400. To solve this, ...
0
votes
0answers
7 views

MySQL group concatenation - creation of insert statement on temp table

I have a temporary table TEST_GROUP, from where I'm working its data to fill another one. From this table, giving the example in the picture, my expected result is to group these three rows, so I ...
0
votes
0answers
2 views

MySQL binlog limitations

I have a MySQL instance running on AWS, with around 5000 inserts per second. Any idea what the performance impact will be if I use binlog (row) and a binlog tailer? Grts, Tom
0
votes
2answers
15 views

Select MAX DATE from joined Table

I have two tables, PROJECT and BILLINGS, as follows: Project_ID Project_Client ---------- ------------------ P1 ABC Company P2 DEF Company P3 GHI Company Proj_ID ...
0
votes
0answers
10 views

Optimize Wordpress Mysql Query that take 5 seconds to execute

Here is my original post: http://wordpress.stackexchange.com/questions/189736/optimize-wordpress-query-that-take-5-seconds-to-execute I have a large wordpress mysql database (Server version: 5.6.22): ...
0
votes
0answers
9 views

Requirements for Setting up mysql cluster

I am very new to MySQL cluster, have been reading a lot online but could not find answers to my questions. I am setting up a MySQL cluster for an application that may scale in months to come. ...
0
votes
0answers
17 views

How many database users/admins should be created for a website?

I have created a CMS website that only had one database connection for both the admin area and the user area and it only had one user("root") with no password, and now I want to upload it, but I am ...
0
votes
2answers
19 views

Fixing MyISAM database without losing data

My old server was crashed but I was able to recover the databases from MySQL through testdrive. I'm trying to dump a database using mysqldump but it gives error for certain tables are crashed and ...
0
votes
2answers
20 views

Selecting distinct records having multiple properties

For following table data: ID | Property ================= 1 | Sweet 2 | Sweet 2 | Sour 3 | Hot How can I get the ID's having Property Sweet AND Sour (in this example, 2) ...
0
votes
2answers
29 views

Merge MySQL tables without data repetition

My situation is this: I have several DIFFERENT tables in my MySQL database with the same name columns. Such tables can contain (or not) the same data. What I need to do is a kind of "merge" between ...
0
votes
1answer
32 views

how to use a specific column for a day

CREATE TABLE `csptargets` ( `id` int(11) NOT NULL AUTO_INCREMENT, `start_date` datetime NOT NULL, `end_date` datetime NOT NULL, `cspcode` varchar(11) NOT NULL, `vol`decimal(20,3) NOT NULL ...
0
votes
0answers
12 views

What are the type of drivers for Communication with Java?

I am developing one application using core java and Mysql Database. As i read in Most of the site, they suggest that use type 4 driver for communication using JDBC. In JDBC Connection i have use ...
0
votes
0answers
9 views

change the name of the database according the changes in while loop [migrated]

<?php $sql="SELECT topics FROM subjects "; $result=mysqli_query($db,$sql); for($x=1;$x<=4;$x++) ...
1
vote
1answer
42 views

Optimizing a query that performs worse on a smaller dataset

I am trying to run this query. The * is just for simplification, I'm selecting ~10 specific fields for some calculations. SELECT * FROM `orders` LEFT JOIN `orders_processed` ON ...
2
votes
2answers
57 views

Can't select 2 tables at the same time

I have a pesId (peopleId) column at pessoas (people) where is stored data from medics and patient. Also I have a table that stores medical consult information like Date, patient id, medic id and ...
1
vote
2answers
33 views

SQL Error (2027): Malformed packet

I have a mysql database (Version 5.6.23-log running on windows) that is set up for use with Teamcity. When running a command against it using HeidiSQL I am getting an error: SQL Error (2027): ...
0
votes
1answer
24 views

How can I delete all users except 1 [on hold]

How can I delete all users except 1 in mysql database in phpmyadmin.(it's a joomla 1.5 website database)
1
vote
1answer
34 views

MySQL: Why is inserting data with transactions faster then without them

I am using stored procedure to generate sequence consisting of prefix and number in given range (etc sequence "AB100", "AB101", ..., "AB199", "AB200") The stored procedure includes REPEAT loop, in ...
0
votes
1answer
18 views

MySQL “SLEEP” command throws syntax error

The 'SLEEP' command in MySQL is throwing a syntax error: mysql> sleep(5); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version ...
0
votes
1answer
30 views

mysql Quiz leaderboard filter by points, time taken

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken. ...
1
vote
1answer
51 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
0
votes
0answers
19 views

Ideas for Database schema for Automotive web app

For the last couple of days I have been searching all over Google for database schema for my automotive web application[like a marketplace of sort](in Laravel 5 PHP). I have tried many models some I ...
0
votes
0answers
48 views

Outer left join with WHERE loses row

The line below MUST be in query or the null rows when joining are dropped. Why is this? //If I don't add this line; I lose rows where items.name IS NULL $this->db->or_where('items.name ...
0
votes
0answers
53 views

MySql Innodb Database Slow INSERT Query to Remote Server

I have searched thoroughly on how to improve general performance of my database which is hosted on Hostgator. I have mostly come across performance tuning strategies which are catered to only local or ...
1
vote
1answer
24 views

Can you enforce uniqueness in MySQL based on column in foreign table?

I am refactoring one of my database tables and I've come up with an issue. Specifically, I have a table with four columns. We'll call the first three my.a, my.b and my.c. I have a uniqueness ...
0
votes
0answers
19 views

MYSQL - number of rows returned equals number of connections?

Does my query get sent to the database once and I get a list of all the results in one shot which I then loop through, or do I have to request the next row from the DB each time? Essentially, does ...
0
votes
1answer
38 views

MySQL command “SHOW DATABASES” returns invalid databases

I have a MySQL server with a number of databases on it - 16 if I'm not mistaken. When I run the SHOW DATABASES command I get 30 rows. I would have expected 17 rows: The 16 databases and ...
1
vote
1answer
61 views

MySQL Index Creation Internals

Right now there are two ways to build an index of a table in MySQL: Create the table structure first, then import the data and add indexes later. Create the table structure with indexes and then ...
1
vote
0answers
41 views

MySQL bulk deletions

I am currently looking at how to most efficiently delete rows from a table in bulk. The database I'm using is MySQL with innodb. The table I want to delete from can be any size, but will usually be ...
0
votes
1answer
18 views

Cannot overwrite file: /var/lib/mysql/ibdata1 when using Percona xtrabackup

I am receiving an error when I am trying to restore from a percona backup to my local mysql server. Cannot overwrite file: /var/lib/mysql/ibdata1 at /usr/bin/innobackupex line 2128. sudo ...
0
votes
0answers
14 views

how to do a backup and restore for a single database with mysql enterprise backup

I am Chinese, the documentation of mysql enterprise backup is hard to understand for me, and now I need to finish my job quickly, I wonder is that possible to backup a single database and restore it ...
3
votes
1answer
70 views

How to check foreign keys related to a table

How to see foreign keys related to a table in MySql? Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this: Error Code: 1217. Cannot delete or ...
0
votes
0answers
17 views

hashed password retrieval in mysql

here I am again with the next question! I hash a password as follows: mysalt=os.urandom(12).encode('hex') passhash =mysalt + pw hash =str( pbkdf2_sha256.encrypt(passhash)) How can I verify this ...
0
votes
0answers
15 views

What could cause 300+ connections from a simple query in MySQL?

I am retrieving results from a table in MySQL 5.5. When I run even a basic query on this table hundreds of connections are shown in the Server Status screen in MySQL Workbench. Even for a simple query ...
0
votes
0answers
21 views

Post categories model for a user submission wedbsite

I'm building an user submission site where users can make posts to content_types: Blog, Movies, Photos, Forums. Since these content types only have slight variances I would like to keep them in one ...
0
votes
0answers
22 views

Filesort when using view

I am sorry for asking, but I can't figure what i am doing wrong and filesort appears in my explain. The id column that I use for sorting in the following examples is a primary key... If I create ...
0
votes
0answers
15 views

Working on a blog creation website; should I be creating a new table for each blog, or keep everything on the same table?

So I'm working on this blog creation website as a side project. I don't actually expect it to become anything, but I want to make it as efficient as possible, even if just for practice. The idea is ...
-1
votes
1answer
51 views

Grouping based on the status, then count its product and the total based on its category ID

I have a table with product ID, category ID and status of the product. +---------+-------------+-------------+ | id | cat_id | status | +---------+-------------+-------------+ | 1 ...
0
votes
2answers
24 views

understanding mysqldump auto-backup performance issues and inner functionality

Is there a performance issue, if I run a cronjob in background with mysqldump --single-transaction with a 60 seconds interval? Will my original database keep functioning without an issue while the ...
0
votes
0answers
17 views

Optimized ways for defragment mysql database table

We have a database in MySQL which has more than 500 GB of data, and currently in the process of purging data and count might go beyond 1 million. As we know that purging might lead to high ...
2
votes
1answer
40 views

Query to flatten schema

I have the following tables and columns: Users id | other-non-important-columns Containers id | Users_id | other-non-important-columns Content id | Container_id | other-non-important-columns ...
2
votes
2answers
44 views

Optimal engine for small lookup tables in MySQL

I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly ...
0
votes
0answers
26 views

Need some help building a query to get info from several tables

I have this semi-complex table structure: And I need to return this data: { "id": "10111001", "rep_id": "30000001", "targets_id": "60011001", "brands_id": "10010001", "brand_name": ...
1
vote
1answer
27 views

How to get row count from all tables of a schema ( without using information schema )

The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.
2
votes
1answer
39 views

How MySQL Queries and Questions status variables are calculated?

I have a mysql server running on 5.6.19 version and I got confused with the status variables Queries and Questions. show global status like 'Queries%'; | Queries | 28058738 | ...
0
votes
0answers
20 views

MySQL Pivot table

I have a MySQL table with following columns ZipCode PartNo Price DateCaptured Example data rows 50070 |P1234 |57.90 |2015-05-05 05:05:05 50070 |P1235 |58.90 |2015-05-05 05:05:05 ...
0
votes
0answers
27 views

Mysql trigger on insert on another table

I have three tables all three have [product_id] column, what all i want to do is when there is new entry of quantity of respective [product_id] in table A and table B i want show the sum of the ...
0
votes
0answers
11 views

Removing per column and table collation and charset

I recently completed a transition of a database from CHARSET=utf8 COLLATE=latin1 to CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. During the process tables were migrated one at a time by altering their ...
0
votes
1answer
24 views

How to insert UNIQUE IDs values from several tables into events table

I have a table called events defined as follow: rep_id, hcp_id, brand_id, email_id and content_id is the IDs in other tables. event_type should be kind of array containing this values: [ ...
0
votes
2answers
45 views

insert into select with union needs derived table

Why do I need to do another select when I am trying to insert distinct values into a table? Also: is there a better way to do this? WORKS: INSERT INTO phppos_categories (name) SELECT category FROM ...