MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
1 view

Can't select 2 tables at the same time

I have a pplId column @ ppl 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 diagnostic. I got to select ...
0
votes
1answer
18 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
18 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
22 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
2answers
15 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
24 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
40 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
15 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
43 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
37 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
18 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
55 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
39 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
16 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
13 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
68 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
16 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
14 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
10 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
19 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
14 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
23 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
16 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
43 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
25 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
35 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
9 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
23 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
44 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 ...
0
votes
1answer
15 views

Function for generate a huge and possible random relationship between tables

I have this two tables and N:M relationship between them: Let's said I am managed to create a lot of media and emails records but I need to create the relationship between them. I can do this ...
0
votes
1answer
61 views

Slow “CREATE TABLE” and “TRUNCATE” on MySQL / MariaDB

I am fighting with performance issues on a local MariaDB instance. Fun thing: I previously had problems with all my INSERT and UPDATE and DELETE queries. This got signigicantly faster (~40ms -> less ...
0
votes
2answers
49 views

how to store mp3 files in mysql database by using phpmyadmin?

first of all i am new to database, but i want a database for my site to store mp3 files in it so later i play them on my site by using jplayer. little though i know about database they said there are ...
-2
votes
0answers
11 views

PMA-SQL-ERROR while importing databse in xampp [closed]

While importing database in xampp i am receiving following errors SQL query: <!-- PMA-SQL-ERROR --> <div class="error"><h1>Error</h1> <p><strong>SQL ...
1
vote
2answers
27 views

MySQL High Severity Error [closed]

I am completely new to MySQL. I am not a programmer or anything. I was installing the program months ago, and forgot my root password, tried to fix things and it failed. I did a system restore and ...
0
votes
0answers
19 views

insert/update query running slow to specifi tables

I am monitoring MySQL client connections and queries run time. Found some insert/update queries to specific tables are slow than expected. I know the tables which are slow are big in size > 600K, i ...
0
votes
0answers
15 views

Searching & deleting content that starts & ends the same, but has random text in between

So my Wordpress sites been hacked & spuriously created a load of content in the posts which starts with & ends with . The only trouble is that the bit in between is random hyperlinks. Any ...
0
votes
1answer
30 views

What is wrong with the following Python code — trying to insert into a database?

This works in MySQL Workbench: insert into nametest (name,surname) values('John''Krailling'); I am trying a get similar result with Pycharm with the following code: import MySQLdb db = ...
1
vote
1answer
31 views

MySQL 5.6 Slaves Fail to Reconnect to older MySQL server when it goes down for backups

I have an existing replication setup with Masters and Slaves running MySQL 5.0. A long existing script runs at night which restarts MySQL on the master with skip-networking, runs a backup, and then ...
0
votes
0answers
17 views

Confused over table_open_cache

I've been reading about the table_open_cache and I'm slightly confused about how big it should be, I've seen conflicting advice. My DB is running on MyISAM and has 199 tables. I ran into an issue ...
0
votes
0answers
27 views

Mysql - too many Deletion - Insertion

I'm creating crons for my OLAP sysetm, I have a cron that deletes the records from the summery table first and then insert the records in it again, Say Deleting 1 month records, and ...
0
votes
0answers
32 views

Get the rank of a row based on a score I am generating from other field values

I am trying to figure out how I can get the rank of a row from a score I am generating from other field values. The score query looks something like this SELECT ((table2.field1*0.4) + ...
0
votes
0answers
41 views

Deadlocks due to foreign keys (MySQL)

We are in the process of creating a web application using mysql as a data source. We are using spring with hibernate as an ORM framework. We have hundreds of entities with relations (manyToMany \ ...
0
votes
1answer
24 views

querying a table to find minimum of average population across all cities [closed]

Consider the table (City) structure given below I need to query on this table: Query : the minimum of average population across all cities | Field | Type | +-------------+------+ | ID ...