Skip to main content

New answers tagged

0 votes

Failure to initialise MySQL Server 5.7 due to mysqlx_port=0.0

This issue is happening due to the installer, you can use MySQL 1.4 installer to install your required version of SQL server.
Dharmendra's user avatar
0 votes

in Relational database, will index accelerator query: select count(*) from table where score >= number

An index might improve this query, as long as the values of score are a suitably good discriminator ("way of picking out rows"). If you have 99.9M rows with a score of zero and 100K with a ...
Phill  W.'s user avatar
  • 8,860
1 vote

MySQL 5.1 Time format of the 6-digit string

String and Numeric Literals in Date and Time Context MySQL recognizes TIME values in these formats: As a string in 'D hh:mm:ss' format. You can also use one of the following “relaxed” syntaxes: 'hh:...
Akina's user avatar
  • 20.1k
0 votes

How do we optimize a transaction-log VIEW in MySQL that will use a LIMIT when executed?

I see that joining the view to a subset of key values appears to avoid the full table scan. This could be a workaround.. but shouldn't be necessary if the optimizer worked as I think it ought. e.g. --...
Rob R's user avatar
  • 1
0 votes

How do we optimize a transaction-log VIEW in MySQL that will use a LIMIT when executed?

You could use two views Fir to get from 1 to 100 create or replace view opt as select translog.translog_id, translog.transtype_code, translog.target_id as target_id, translog.date_posted, ...
nbk's user avatar
  • 8,434
0 votes

How can I disable mysql_native_password and mysql_old_password on MariaDB / MySQL?

There is currently no mechanisms to disable mysql_native_password. old_passwords as a system variable defaults to off so mysql_old_password isn't available. MDEV-12320, is feature request to ...
danblack's user avatar
  • 7,809
0 votes

Unable to enable Performance Insights on Amazon RDS db.t3.small instance

For Aurora instances, t2 and t3 series, performance insights is not supported. Performance Insights has the following engine class restrictions: db.t2 – Not supported db.t3 – Not supported db.t4g....
Daniel James Canil's user avatar
0 votes

Multiple parents for AspNetUsers table for Asp.Net Core 3.1 app

To have multiple parent ids you can use a Bridge table see the shortened first example. so you have multiple parent even more than 2. The second sample a user can only have 2 parents, and the must be ...
nbk's user avatar
  • 8,434
0 votes
Accepted

Delay Async Replication Time ClusterSet

I assume you are using InnoDB ClusterSet. Currently, it's not supported to configure delayed replication in such architecture. You can configure it manually, however, you must understand the ...
Miguel Araújo's user avatar
0 votes

It's possible switchover a innodb cluster instance as primary?

First, MySQL 5.7 has been EOL since October 2023, so I highly recommend upgrading to 8.x. About your specific question: .setPrimaryInstance() is not supported on MySQL 5.7. That has been introduced in ...
Miguel Araújo's user avatar
0 votes

Setting up a Docker compose for Mysql and Vitess

I'm really only interested in the connection pooling functionality of Vitess, is there a minimal example on how to set this up myself anywhere? Hello! Many things are going on in your post. Is ...
IcyFlame's user avatar
  • 101
0 votes

Failure to initialise MySQL Server 5.7 due to mysqlx_port=0.0

Mysql Server 5.7.10 worked for me in Windows 11 Here's the download archive: https://downloads.mysql.com/archives/installer/
José's user avatar
  • 101
0 votes

Conditional update

There is a disadvantage in this approach: what if you change parent table not by this stored procedure but by other means? Another stored procedure or even direct update statement? The better solution ...
White Owl's user avatar
  • 853
0 votes

What is the reason the SQL query is not working?

what is the correct version SELECT FIELD1, COUNT(FIELD2) AS FIELD2_COUNT, SUM(FIELD3) AS FIELD3_sum , /* add missed comma */ AVG(field4) AS field4_avg FROM table1 /* ...
Akina's user avatar
  • 20.1k
0 votes

mysqldump: Error in field count for table: aborting

So from the code the error is comparing the number of fields returned by: SELECT * FROM table with the previous num_fields, which is the number of rows from the information_schema query: SELECT `...
danblack's user avatar
  • 7,809
0 votes

Failure to initialise MySQL Server 5.7 due to mysqlx_port=0.0

I also encountered the same issue when attempting to install versions 5.7.43 and 5.7.44 using the installer. However, I was able to successfully install version 5.7.7 RC, so if you are not tied to a ...
Lexa Titanium's user avatar
0 votes

Mysql : dumping a database fails

This problem seems to be a combination of factors affecting your MySQL connection and potentially some table corruption. You can follow below approach to resolve it: Connection Timeout: While you ...
Gary Futch's user avatar
0 votes

Ignore empty rows while loading data into a table

Try below LOAD DATA INFILE '/var/lib/mysql-files/data_file.csv' INTO TABLE dbcs_info FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (col1, col2, col3, ...
Aashutosh Kumar's user avatar
0 votes

MySQL profile on query "Creating Sort Index" using 75% of the total time

I know this is an old question, but it ranks highly when searching for "MySQL create sort index", so I figure it's worth responding to! :) In my case, adding an index to the column that I ...
neoncube's user avatar
-1 votes
Accepted

MySQL solution for many to many associative table that scale over billion entries

Ok I'll mark my answer as accepted but if another more complete answer comes out in the future I can change it, I really don't care, important is the most correct/complete answer to my question. My ...
Federico Baù's user avatar
2 votes

Removing partitions from MySQL table

Plan A There is essentially no way to avoid a full copy of the table. Plan B On the other hand, pt-online-schema-change may be able to handle such (See percona.com). Or gh-ost. Plan C It seems ...
Rick James's user avatar
  • 78.6k
0 votes

Need help rewriting view to avoid using subqueries in MySQL 5.1 db

My Solution: MySQL 5.1 apparently allows nested subqueries in a View as long as they part of the SELECT. Rewriting the JOINS as nested SELECTS like this: SELECT submission_id, ( SELECT response ...
horsepotatoe's user avatar
0 votes

A minimum of a 3 Node Galera Cluster is Recommended, but is it better to have 5 nodes?

I had a 3 node cluster just upgraded to 5 node and the difference is huge. I'm using haproxy to proxy into it. The main thing I noticed is that adding new nodes doesn't cause the cluster to go into an ...
nbtafelberg's user avatar
3 votes

MySQL solution for many to many associative table that scale over billion entries

Preface I think you're unfortunately misunderstanding: How databases work at their core level, and What some of the people of this community have been trying to communicate to you in regards to that. ...
J.D.'s user avatar
  • 38.1k
0 votes

MySQL Backup (.cfk) file to Microsoft SQL Server

so what if i want to back it up with MySQL You can't, it's incompatible. If you want to transfer your database from MySQL to SQL Server, you have two main options: Script out the entire database as ...
J.D.'s user avatar
  • 38.1k
2 votes

Need help rewriting view to avoid using subqueries in MySQL 5.1 db

If each attribute is unique for each submission id then you can use conditional aggregation to pivot the table (and does not require any joins): SELECT submission_id, MAX(CASE WHEN attribute_id ...
MT0's user avatar
  • 202
2 votes

MySQL solution for many to many associative table that scale over billion entries

The best solution for your problem is what you already designed. That's because all RDBMS are designed to handle exactly that: a bunch of tables, with many-to-many relations, and potentially billions ...
Andrea B.'s user avatar
  • 1,613
8 votes

Need help rewriting view to avoid using subqueries in MySQL 5.1 db

I hope the next code is clear enough SELECT q.submission_id , q.attribute_id , q.response AS is_reporting , w.response AS email FROM frm_form_attribute_data AS q JOIN ...
Kondybas's user avatar
  • 4,548
1 vote

MySQL solution for many to many associative table that scale over billion entries

Since there are only 100 items... If there won't be a lot more items, change to SMALL UNSIGNED (max of 65K). This will save 30GB. (Shrinking a table --> less I/O and better caching --> maybe ...
Rick James's user avatar
  • 78.6k
1 vote

How can I access SQLCLR assembly functions in MySQL

No, it appears that MySQL does not support SQLCLR assemblies. MySQL does seem to allow for adding custom functions, called Loadable Functions, but the only supported language is (at least currently) C+...
Solomon Rutzky's user avatar
5 votes

How can I access SQLCLR assembly functions in MySQL

Nope. A C# CLR assembly is specific to SQL Server which is intentionally designed to be able to load such an assembly. (Pretty easily conducive since Microsoft owns both C# and SQL Server.) MySQL (...
J.D.'s user avatar
  • 38.1k
0 votes
Accepted

Automated Remote db syncing to localhost db?

I am looking for the best practice to sync my hosted db with my local db, to serve as backup and emergency db! Best Practice is not to do this! You must have database Backups. Find out how hostgator ...
Phill  W.'s user avatar
  • 8,860
0 votes

MySQL processes consuming too much VIRT and RES memory

innodb_log_buffer_size is limited to 4G. And that is RAM. Even so, 4G is not necessarily much better than, say, 1G. Lower it. For further analysis, provide the data indicated here: https://mysql....
Rick James's user avatar
  • 78.6k
0 votes

Single Query to return result from multiple tables in mysql

frontend application has single text box to search by name or occupation. i would like to create single select query to search in both tables. This query can return the results as expected: SELECT ...
prasad_'s user avatar
  • 470
0 votes

Single Query to return result from multiple tables in mysql

PROPOSED QUERY SELECT * FROM (SELECT A.name,GROUP_CONCAT(B.occupation) occupations FROM tbloccupation B INNER JOIN tblperson A ON A.id = B.personid GROUP BY A.name) AA WHERE occupations LIKE '%Social ...
RolandoMySQLDBA's user avatar
0 votes

Single Query to return result from multiple tables in mysql

A combination of join and group by and group_concat will go bed you your wanted result. Gropu_concat has also a order by so you can order as you want it In your where clause you enter the wanted ...
0 votes

Can we prevent table from update directly but allow update using trigger from another table?

Prevent update stocks.stock value directly. But allow update stocks.stock value by trigger from buy or sell table. Here is a possibility. An example, using sell table: CREATE TABLE stocks ( pid ...
prasad_'s user avatar
  • 470
3 votes

Why does MySQL generate a `key` statement in DDL for composite primary keys?

The reason is this : The foreign key reference fk_person_team_person already saw an index that can satisfy for foreign key relationship. In your case, it was the primary key since the lead column is ...
RolandoMySQLDBA's user avatar
7 votes
Accepted

Why does MySQL generate a `key` statement in DDL for composite primary keys?

The answer, as is sometimes the case, can be found in the documentation: MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan....
mustaccio's user avatar
  • 26.3k
1 vote

Storing numbers 0-9 in a column

I tried this with MySQL 8.0.34 and hope its useful. CREATE TABLE products ( pid VARCHAR(5) NOT NULL, store SET('0', '1', '2', '3', '4', '5', '6') -- can include upto 9 ); INSERT INTO products ...
prasad_'s user avatar
  • 470
2 votes
Accepted

Unable to enable Performance Insights on Amazon RDS db.t3.small instance

I found a page entitled "Amazon RDS DB engine, Region, and instance class support for Performance Insights". According to the first table on that page (under the column "Instance class ...
RolandoMySQLDBA's user avatar
2 votes

Storing numbers 0-9 in a column

I need to select rows from another table that has a single digit column that matches any of the ones represented in the first table. If i have 3,4 and 5 in my column i need to have a clause that is ...
J.D.'s user avatar
  • 38.1k
1 vote

Storing numbers 0-9 in a column

I thought about using one column per number but that seems excessive? A BIT column per number seems a quite reasonable possibility, based on what you describe. I'm not familiar with MySQL internals ...
Steve's user avatar
  • 509

Top 50 recent answers are included