Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [mysql]

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
1answer
16 views

Multiple columns from single table

My tables: categories: +----+------+ | id | name | +----+------+ | 1 | blue | | 2 | red | +----+------+ data: +----------+----------+-------+ | date | category | price | +----------+-------...
0
votes
0answers
18 views

Is ALTER TABLE … MODIFY column faster than CHANGE column?

In versions of MariaDB or MySQL, is ALTER TABLE mytable MODIFY column faster than ALTER TABLE mytable CHANGE column column? Or is there any other advantage of using MODIFY other than the shorter ...
0
votes
1answer
31 views

Using a caching tool for a remote database

When the DB is on a remote server, is there any tool to cache frequent queries on the main server in order to reduce database connections? If there are any available, what features should I consider ...
0
votes
1answer
9 views

Character set warning in PXC logs

I am using PXC 5.6 (Percona-XtraDB-Cluster-56-5.6.41-28.28.1.el7.x86_64) and my log file is flooded with this warning: [Warning] Client failed to provide its character set. 'latin1' will be used ...
0
votes
0answers
40 views

MySQL Can't create new tempfile

If anyone is an X-Cart 4 user, or has experience, I am experiencing an issue with the table xcart_variants. It keeps getting "corrupted" or something. It's giving an error Table './elements_xcart/...
-1
votes
2answers
64 views

Is there a non-syntactical guide for naming database columns? [closed]

Is there a guide or best case practise to help people on choosing the best database column names. I am not looking at syntactical naming conventions such as snake case, camel case or even singular or ...
0
votes
1answer
30 views

SQL schema for movie rating and seenlist

I'm making funny app. User can mark movie as seen. User can rate movie (1-10). When user rate movie it is automatically marks as seen. User can't remove movie from seenlist if movie is rated by him. ...
2
votes
1answer
37 views

find 16,000 words and replace with ##

I need to do a find a replace but rather than find one word to replace with another I have about 16k words, so as an example; table 1 has two words apple,orange - table 2 has the contents of a book, i ...
0
votes
1answer
29 views

Auto increment gap in percona xtradb cluster master master setup

I was using mariadb 5.5 before but now I have upgraded it and using percona xtradb cluster 5.6 I have 3 nodes cluster. I am only using single node for read and write. I saw some increment gaps in ...
2
votes
1answer
36 views

Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 “Using index condition”

My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different). EXPLAIN SELECT * FROM `...
1
vote
1answer
19 views

Error while restoring the table dump

ERROR 1067 (42000) at line 25: Invalid default value for 'created_on' I'm getting this error while restoring a table after taking the dump(remotely). I have used mysqldump -h (hostname) -u -p (...
0
votes
1answer
10 views

get all posts with their associative tags without duplicates

Two tables. Post and Tag. Many to Many bi-directional relationship. Post Table Tag Table Post_Tag Pivot Table --------------- ------------ ----------------- ...
0
votes
1answer
63 views

Percona Xtrabackup fails to backup InnoDB table with fulltext index

I'm trying to make a backup of a large InnoDB table with a fulltext index using Percona Xtrabackup (2.4.12). I have innodb_file_per_table set. To create the backup, I'm running: innobackupex --...
0
votes
1answer
28 views

Mysql: Count, Group By, and limiting to first instance of an ID

I am trying to write a query to generate a report of sorts and I'm stuck on the logic of it. The summary of it is: I need to select the count of activities, where post_type is 'invoice' AND event = '...
0
votes
0answers
24 views

What steps can I take to recover MySQL InnoDB databases when forced recovery fails?

Coming here from StackOverflow because I saw a similar question being closed as off-topic on there. I have corrupted my local MySQL instance in my development environment. I was running a set of ...
0
votes
0answers
20 views

point in time recovery not working with blackhole engine tables involved

The basic problem summary: I am trying to do a point in time recovery on a specific table. To speed things up I made every other table use the blackhole engine. Unfortunately this runs into errors ...
0
votes
0answers
23 views

explain on view shows derived while explain on select shows simple select type

I have a view v which involves a couple of sub-selects in its SELECT clause plus a couple of joins by id or indexed column. The view query is like: SELECT col1, col2, (SELECT ... FROM subt) AS col3 ...
0
votes
1answer
27 views

What is the relation between MySQL pages and Operating system pages?

By MySQL pages i mean this : https://dev.mysql.com/doc/internals/en/innodb-page-structure.html and operating system pages : https://en.wikipedia.org/wiki/Page_(computer_memory) I understand both ...
3
votes
3answers
450 views

Do all Relational DBMSes store table tuples in a clustered index based on primary key by default?

So i was reading MySQL innoDB, which apparently stores table data by default on a clustered Index (b+tree) based on primary key, and the tuples are in the leafs of that b+tree https://blog.jcole.us/...
0
votes
0answers
17 views

Why doesn't my profile of my procedure seem to account for the duration?

I'm running 10.3.10-MariaDB-1:10.3.10+maria~bionic locally in Docker and I'm profiling a procedure call like so via adminer: SET SESSION profiling = 1; CALL `add_user_to_role_in_unit`; SHOW PROFILES; ...
0
votes
1answer
22 views

Cannot allocate memory for the buffer pool when using remote access MySQL

I tried to separate the webserver and mysql server. MySQL server = 16GB RAM and the config of Innodb_buffer_pool_size = 10GB. And my Web server = 4GB RAM My website connect to my database server ...
2
votes
1answer
44 views

Sum values by time

id_ | date_ | hour_ | in_|out_| |…. | …. | …. | …. | …. |19 | 2018-12-12| 18:15:00| 1 | 3 | |19 | 2018-12-12| 18:30:00| 1 | 0 | |19 | 2018-12-12| 18:45:00| 2 | 4 | |...
-1
votes
1answer
39 views

how to sum values from two tables and date range between [closed]

I want to sum values from two tables using date range. I tried this, SELECT sum(t1.rcv),sum(t2.amount) FROM tbl_stock AS t1 INNER JOIN tbl_amount AS t2 ON t1.date=t2.date WHERE t1.date BETWEEN t1.'...
-1
votes
0answers
19 views

MySQL: change from master/multi-slave setup to master/master/multi-slave

I have the following master / slave configuration using mysql: 1 master and 3 slaves (slaves used in time-consuming queries for reports). I need to know if it is possible to convert a slave to master ...
1
vote
1answer
30 views

Import Windows MySQL dump on a Debian Linux server

I created a test database on a Windows computer with MySQL 5.7 and filled it with many data records. Now I created a database dump with mysqldump and wanted to import it on my server (Debian Jessie, ...
0
votes
1answer
23 views

Update Table 1 taking value from table 2

I want to update Table 1 by taking the value from Table 2. Both the tables have Emp No which is a key. I want to update the Salary column in Table 1 with the value that is present in Table 2 UPDATE ...
0
votes
1answer
49 views

Select only those rows based on specific condition of user id

Below is an example of child's activity table. I only want to see all rows after 1st Tv activity for each user id. I tried grouping but it is not working for me Table ID Timestamp ...
-1
votes
0answers
19 views

Can not start mysql cluster localhost mix with remote address

I am trying to use mysql cluster installer ndb_setup.py to set up 2 nodes mysql cluster and getting following error 2018-12-13 00:25:50 [MgmtSrvr] WARNING -- at line 35: [DB] BackupMaxWriteSize ...
0
votes
2answers
55 views

How can I speed up this 2m5s query that has indices?

How can I speed up this 2m5s query that has indices? select urls.id as urlId, count(case when s1.hit_type = 0 then 1 end) as aCount, count(case when s1.hit_type = 1 then 1 end) as bCount, ...
0
votes
1answer
25 views

I have 2 queries with only one difference that I want to merge in just one query

I have two queries in MySQL that are basically the same, where the only difference between them is a min() or max(), I would like to know if it is feasible to join the two. I believe that the ...
-1
votes
0answers
21 views

If deleting rows does not affect the index pages of that table, wouldn't this cause problems after some time? (MySQL)

So based on this article : https://www.sciencedirect.com/science/article/pii/S1742287616300500 When a row is deleted, DBMS does not delete the corresponding index values e nor are such index ...
2
votes
1answer
34 views

Select the same field based on different foreign keys

I have two tables: Containing a list of products with ids and names A table linking several products with parent-child relationship based on ids using foreign keys. I want to create a view that will ...
0
votes
3answers
31 views

How to use column alias to calculate other column value

My query is SELECT (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings ,(`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS ...
1
vote
0answers
21 views

Unable to LOAD from XML: The used command is not allowed with this MySQL version

I'm having difficulty loading records from XML on OSX using the command load xml local infile 'records.xml' into table Badges rows identified by '<row>'; and I keep getting the error: Error ...
-1
votes
1answer
63 views

how to calculate balance from two tables

I have two tables like that below: tbl_deposit: id,date,client_id,deposit tbl_expense: id date,client_id,expense I want to see the result when where client_id=1 like this: date---------...
0
votes
1answer
23 views

Copying data up in single SQL statement

I need to fix an SQL statement in MySQL that should calculate a field using values from two of the columns and I prefer to do this using set-based programming, ie not procedural. What needs to happen ...
-1
votes
1answer
30 views

the difference between customer address table and address table

I was having a look at this data model to get more acknowledged for designing my own. Could someone kindly tell me what I need the Customer_Address table for? why don't we just settle with the ...
0
votes
1answer
58 views

Good practice for hiding primary keys from a client

Let's say I have a table like this: CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ); Later on in my client, I may fetch a RESTful endpoint like this: GET /v1/users/1 The ...
0
votes
1answer
27 views

Map auto increment ids on multi-row insert

I have the following table with an AUTO_INCREMENT column. CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); Usually I load multiple ...
0
votes
1answer
33 views

How To return null value if no records present in select query

I am querying in DB for last 72 records in Db in my Db it may happens all data will not be present for that I want to add dummy row in which I will add rank and adding null value to remaining data. ...
0
votes
1answer
31 views

Full name as a Primary Key (PK) is my preferred choice; is there another way?

I'm new to DB management and I'm trying to create an ER Schema Diagram to best understand how to build my database for a project. My project is to list every speaker (i.e. an individual person) that ...
-1
votes
1answer
49 views

How to maintain customer addresses for Orders?

The database I am designing is part of a project I am working on. I have come up with 3 simple "business rules": Customers must either be Individuals or Businesses (a business belongs to a certain ...
0
votes
1answer
39 views

Vehicle Pickup time database design

The objective of this is to give workshops the ability to give their customers a choice of vehicle pick-up times and likewise customers having the ability to choose their preferred pick-up time. I ...
0
votes
1answer
32 views

How to get Average value from one row if have zero or null values in mysql

I'm new in mysql. I want to ask something about get average value from one row in mysql when have a null or zeros value on them. the image below assuming my table And I want to get the average from ...
0
votes
2answers
30 views

Best way to include AND exclude?

I have a large table that contains product IDs and product category IDs. I want to select product IDs that are in a certain list of category IDs, AND exclude the product ID entirely if it's in ...
0
votes
2answers
31 views

Time taken to drop/add index is lesser than adding a column. Why is that?

I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though. I'm performing an alter by default it does ONLINE alter. When I add column it takes at least 50 mins for 50G table in our ...
0
votes
1answer
25 views

Payment method schema design

I have a few relevant tables: user, payment_method, invoice, transaction. An user can own the following: invoices (n amount) payment methods (n amount) An invoice own the following: transactions (...
0
votes
1answer
34 views

MySQL schema design - how to decide between an `enum` vs. a table join

In designing a MySQL schema, should I avoid using an enum type altogether, as I have been reading really strong, but favored opinions on this subject, such as: Stackoverflow post on efficiency of ...
-1
votes
1answer
63 views

How to create a view joining the following tables? I want to find cities that have the same name as students' first names [closed]

SELECT * FROM `assignment-one-db`.student; INSERT INTO city (id, firstname, lastname, email) VALUES (1, 'Maria', 'Popescu', 'maria.popescu@yahoo.com'); SELECT * FROM `assignment-one-db`.city; ...
2
votes
1answer
22 views

My Database Tables Revert to MyISAM Instead of InnoDB (MySQL)

I am trying to change my tables engine to InnoDB because I am experiencing problems with the current engine which is inconsistent and crashes often. Unfortunately, it reverts back to MyISAM. This is ...