Tagged Questions
All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.
0
votes
0answers
2 views
Selected Child Categories based on parent categories from another table
I have two tables
rules
With three step hierarchy
|id | name | parent |
|---|-------|--------|
| 1 | A | 0 |
| 2 | B | 0 |
| 3 | A(1) | 1 |
| 4 | ...
0
votes
0answers
6 views
for large database migration what type of database server/service required?
currently i am using MySQL and it process 10k insertions per day and soon it will going to be insert 200,000 Two hundred thousand of records per day. now i am using a normal server which is okay with ...
0
votes
1answer
9 views
can i have password for my mysql table
is there a way i can set password on a table i am creating in mysql. i am working with rather sensitive data , and i have to use mysql on a server on which i am obviously not the admin. so people ...
0
votes
1answer
10 views
Mysql Order , Select highest value item and update to another table
i have list in data_1 table. i want to select highest value item in one group and update it to another table. can i do that with 1 query?
example : Select "Group B Leader Jane" and update it to other ...
0
votes
1answer
13 views
SQL Server to MySQL migration, replace '\xA0' with a regular space
I am having some trouble while migrating data from SQL Server to MySQL--it seems some data does not want to place nice. I am recieving the error: Incorrect string value: '\xA0\xA0' for column ...
1
vote
0answers
15 views
Special Characters in MySQL using UTF8 Unicode Collation and LOAD DATA INFILE
I have several csv files in am loading into MySQl using Java. In the Description field I have several Special Characters that are causing the load to fail. I am using LOAD DATA INFILE. This is nested ...
0
votes
1answer
20 views
MySql - Get all children in JOIN if one child matches a condition
I have a one-to-many relationship between two tables Parent and Child. I want to get all children if one child matches a condition. Something like below:
SELECT * FROM Parent p
INNER JOIN Child c ON ...
0
votes
0answers
17 views
Mysql Disk Scanning algorithm
How mysql scans data from disk? What scanning algorithm it uses to scan from disk? Since the rows are stored in data pages will it read the entire data page to get the resulting rows?
0
votes
0answers
17 views
Is there an equivalent of 'FORMATFILE' in MySQL?
I'm looking for a way to import data from a text file to a MySQL database,
the text file used to be imported in a SQL SERVER db via BCP and FORMATFILEs option for exemple :
BULK INSERT [MyTable] ...
1
vote
0answers
8 views
mysqldump show Table doesn´t exist when doing LOCK TABLES
Our server had power outage and after start some of mysql tables was inaccessible. I tried many things but nothing helped and currently i am at freeze point.
I used command :
mysqldump database ...
1
vote
0answers
11 views
Database Designs Alternative to Polymorphism in Establishing Relationships
I'm working on a database which handles replacement of parts in a machine.
Rules
A machine has many components
A machine has a set of assembly instructions
Many components have many part (screws,
...
0
votes
1answer
28 views
What happens when the buffer pool size is insufficient in mysql
Let us assume I run a query for a table whose size is very huge. My buffer pool size is as low as possible. What happens now? whether I will get an error regarding the insufficient space in buffer ...
0
votes
0answers
12 views
Tag system for multiple Item types and groups Schema
I have a groups table and users table, and a many to many relationship between them.
In my app, there are different modules, like question, product,suggestion , etc.
Users can create ...
0
votes
0answers
33 views
Freezing Centos server
My dedicated server (CENTOS 6.4) freeze every two day and I have some trouble to found te origin.
What I know :
This is not hardware issue as tests were made about (RAM, CPU, Discs, ...).
At the ...
1
vote
0answers
15 views
pub/sub database schema
I am having a hard time creating a pub/sub database schema with stateful metrics.
Saying it aloud it sounds easy enough:
"There are many organizations that will subscribe to checklists that
are ...
0
votes
1answer
22 views
Creating trigger to manipulate language
This is the table that I have. Effective date is a user input, then I need to use a trigger that produces Print depending on the language the user inputs (English or French only).
For English, I used ...
1
vote
0answers
21 views
Getting InnoDb internal Errors on every query run
I am having following errors in error.log at the run of each query due to which I am not able to use error.log properly.
2016-05-30 09:39:58 7f7c7e3cb700 InnoDB: Error: Column last_update in table ...
0
votes
2answers
35 views
Sorting by month by chronological occurrence when the year doesn't matter?
Given a table with a date column, and given a query that selects dates in a given range of the year (like "November 20th through February 20th"), and where the year portion of the date doesn't matter ...
0
votes
0answers
17 views
Mysql/pivot table for two tables
I was trying to get multiple rows into single row with dynamic columns created as per the data.i have two tables combined with foreign key.here is my data follows
table 1:
| id | name | Invoice value ...
1
vote
1answer
27 views
how to upload a file on a mysql server if access to temp folder is not present [duplicate]
I have a CSV file that i want to enter into a table in my database which is present on a remote server. But presently I don't have access to temp folder so I cannot copy my file on that folder. Is ...
1
vote
1answer
43 views
Delete the maximum ID row [duplicate]
How can I find duplicate rows and for each group of duplicate rows delete the max ID row, in MySQL?
Using this query, I can find duplicate rows:
SELECT * , COUNT( awb_no ) c
FROM tbl_cod_rpt_COPY
...
1
vote
1answer
51 views
Using indexes in ORDER BY MySQL
Here i have table with indexes and more than 700000 entries.
CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` char(255) COLLATE utf8_unicode_ci NOT NULL,
`cat_id` ...
1
vote
0answers
33 views
Null instead '0000-00-00 00:00:00' in mysql 5.7
Before: mysql 5.6. One of the columns in the table is created as follows:
`f_stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
When i view this field in mysql client or using DataGrip, this ...
0
votes
0answers
16 views
What is the difference between XtraDB and TokuDB? And why are there no comparisons? [on hold]
I'm just getting into DB and doing some search on different MySQL tables. My current company's tables are all old and in MyIsam form. At first, I was going to simply convert to InnoDB, then noticed ...
0
votes
0answers
39 views
MySQL - Database Design : Encrypting user id and creating a mask for further internal references
For a food order delivery system that I am working on, I want to make sure my user's private information is well protected; from system admins too.
So I thought of coming up with this concept of ...
0
votes
0answers
29 views
Speed up SUM() on a result with JOINS in MySQL
I have a query with a combination of 6 left joins and inner joins. The query gives a result of 25000 records in 112ms. When I do a SUM() the query takes 4s. A SUM() another table with 35000 records ...
0
votes
0answers
24 views
Mysql How do I insert multiple table with duplicate key checking?
I hope to this work.
When UserAccount table duplicated, Out Parameter put -1.
When UserInfo table duplicated, Out Parameter put -2.
When all work is succeed, Out Parameter put LAST_INSERT_ID.
...
1
vote
0answers
14 views
Add index to mysql table with ndbcluster engine
I'm having trouble while adding index on mysql table with ndbcluster storage engine below is the error . [mysqlcluster ver. 7.4 GA]
mysql> alter table `sks_staff_office` add index `pid_index` ...
1
vote
1answer
30 views
Optimize MySQL Query with multiple subqueries
I have the following Query:
SELECT p.id AS post_id, p.status, p.user_id AS post_user_id,
(SELECT pl.id FROM postlocks AS pl WHERE pl.post_id = p.id) AS postlock_id,
(SELECT COUNT(rn.id) FROM ...
-1
votes
2answers
30 views
How to perform multiple checks on same column of same table in Mysql? [on hold]
Table Structure :
+-------+--------+-------------+------------------+---------------+----------------+
| recid | cartid | attributeid | attributevalueid | attributename | attributevalue |
...
-1
votes
0answers
20 views
Backup whole database including procedures [on hold]
I'm using MariaDB 10.1. I backup my database using mysqldump. My stored procedure, view, function and event definitions are not backed up. How to backup full database including tables, functions, ...
0
votes
0answers
14 views
Connect to MySQL Webserver (not localhost)
I have a problem. And I hope someone can help me or point me in the right direction.
I am working on a project and need to sync a MySQL database (running on PHPmyAdmin) with a MS SQL Server.
I ...
0
votes
0answers
16 views
Copy data from one table into other on some condition
I have two table as follows.
Table : city
city_id city_name state_id
1 Cachar 1
2 Darrang 1
3 Nicobar 1
Table 2 : locality
pincode Address ...
0
votes
0answers
8 views
Recursive Conditional trigger for insert in table MYSQL
user[ user_id, parent_user_id]
user_id parent_user_id
1 null
2 1
3 1
4 1
5 1
user_assigned_role [user_assigned_role_id, role_id, ...
-5
votes
1answer
41 views
Could someone explain how PHP, MySQL, SQL work together? [on hold]
I feel so confused on what they are and how they work. From what I've read PHP is a server-side language. Therefore, it's a language basically designed to talk with the web server to get it to do ...
0
votes
0answers
29 views
Update column with different value for multiple rows [on hold]
I often find myself using Excel to create update queries.
When I need to update multiple rows with different values for a column, I simply use Excel to create the update queries (multiple).
Here's ...
0
votes
1answer
21 views
PostgreSQL vs MariaDb On Read/Write when table has about 200M Rows [on hold]
I want to build a new web application with my collected data, but I don't know witch DBMS is best choose for my Project, MariaDB or PostgreSQL
there is 2 table : categories and data
more than 200 ...
0
votes
0answers
19 views
Transaction on delete is committed, but for a brief time the entries can still be read?
I have a Java application that uses Spring JDBC and HikariCP for pooling MySQL connections. There is a high level of concurrency where a thread creates a unique entry for a given task in a single ...
1
vote
1answer
16 views
MySQL trigger to update a key if record exists
I'm trying to build a trigger in MariaDB to follow this logic:
1. If the primary key val exists, increment the counter column
2. Else, insert the primary key into the table
Here is my prosed ...
0
votes
0answers
11 views
MySQL: database setup to properly deliver non-ascii characters
I'm facing the following problem: I have a MySQL database which contains non-ascii characters in some records. Characters like "ñ", for example.
What kind of setup do I need to perform at the ...
1
vote
0answers
31 views
Converting a composite primary key to composite unique, and adding an auto-incremented PK column
Background
I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign ...
0
votes
0answers
7 views
Linked Server Mysql Workbench
Is it possible to use 2 MySql Workbench at a time by connecting it using Linked Server? Or is there any other alternative? By the way, I'm working on a web application with jsp and java.
1
vote
1answer
18 views
MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index
It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, ...
1
vote
1answer
19 views
How did you setup MySQL Replication with autofailover that is app transparent?
Recently told MySQL is shelving MySQL Fabric. Interested how others have implemented a MySQL replicated environment that is app transparent.
I am considering using HA Proxy to host virtual IP ...
1
vote
1answer
26 views
MySQL data types for common columns
I have been a frontend programmer for a while and moving to making some applications on my own and becoming a full stack engineer. I have worked with plenty of databases for applications and sites but ...
0
votes
1answer
23 views
Mysql scaling up vs scaling out
I have an application where each client gets their own database. This is for security, backup ease, and portability. It provides great flexibility and performance has been fine. I am up to 300+ ...
-1
votes
1answer
21 views
Select MySQL with 2 tables conditions, Is this right?
Tables:
agencia (nome_agencia, cidade_agencia, fundos)
cliente (nome_cliente, rua_cliente, cidade_cliente)
conta (nome_agencia, numero_conta, saldo)
emprestimo (nome_agencia, numero_emprestimo, ...
0
votes
0answers
24 views
MySQL 5.5 to 5.7 doesn't work, apparently this is a feature?
Has anyone found a way yet of circumventing MySQL 5.7s insistence that it's Master must have a server_UUID.
I currently have multiple instances of MySQL 5.5 over various sites, and want to look at ...
0
votes
1answer
39 views
MySql timeouts waiting for table lock, no apparent deadlocks
I started having the following error on two different applications that write to the same database and table:
Lock wait timeout exceeded; try restarting transaction
At the beginning I thought it ...
0
votes
0answers
29 views
Managing large database tables, large tables VS seperate databases
In my project I have a system where users sign up and perform daily tasks.
Note that I am not a database expert, none the less I am trying to solve a potential issue that may arise.
A problem has a ...
