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.

Filter by
Sorted by
Tagged with
0
votes
1answer
9 views

how to hide the columns and data in table based on the header and data values by using php and mysql

I have two queries for table header and table data so first query is table header I am fetching and second query is table data,my queries are like this, select a1,a2,a3,a4,a5 from test1 select b1,b2,...
0
votes
2answers
13 views

What happens at low level when I add a column to a table in MySQL/ PostgreSQL?

I have a table with number of rows in the order of billions. Let's say I want to add a column to that table. I would like to know what happens behind the scene to know the cost, expected time of this ...
0
votes
0answers
9 views

Is Mysql faster if on separate VPS (with cores dedicated to it) or on the same but faster VPS

Note: This is not a question about capacity planning The problem: Lately traffic on my wordpress site grew and so did my cpu usage. mainly by mysql (roughly 2/3 of the cpu is used by mysql and the ...
0
votes
1answer
9 views

Best practices relational design

I'm creating tables in a database currently and one of the tables has a status column. the status column has 4 different statuses that it could potentially have. I've made a separate table that has ...
0
votes
1answer
19 views

how to show combined results of two tables?

I want to show the result as combination of two. Table 1 SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID` FROM au.`invoice` WHERE au.`invoice`.`InvoiceID` > 11126 -- total results 757 ...
-1
votes
1answer
9 views

Compare 2 column- Column A and Column B , result if the column A has the string of Column B get the output

Sl no Column A Column B 1 Dell Laptop Laptop 2 Dell Mouse Laptop 3 Dell Acc Laptop 4 Dell Laptop 1 Laptop Result Output Sl no Column A ...
0
votes
1answer
9 views

Select rows from table having specific values depending on another values in the same table

I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore 7 and 8 example: Userpin TrDate Time1 Tr_type 4555 12-02-2020 ...
-1
votes
0answers
11 views

Sub ID mysql based on another field

I need to create a sub-ID for users in database. Actually I have something like that: ID USER others columns 1 1 ... 2 1 ... 3 1 ... 4 ...
0
votes
0answers
17 views

Is it practical to use entity-attribute-value (EAV) model to support custom user fields and avoid bloating of tables?

I am currently working on a medium-sized web app project that is already in production using Laravel framework and MySQL 5.6.40. My clients have been asking for the ability to add additional ...
0
votes
0answers
11 views

MySQL on windows: OS error code 28: No space left on device

I have MySQL version 8.0.16 installed on Windows Server 2016 Standard. I have a query that required lots of data to be returned from MySQL. The query runs for like 30 minutes and then I get the ...
0
votes
1answer
14 views

i have error in query

i am not getting what is the issue in the query probably i am not following the correct way to put the string and char sign , i am inserting the data in c# to local host with where clause please check ...
0
votes
1answer
23 views

Using subquery within IF condition

Hello i am having a weird problem currently i have the following query SELECT * FROM `txt_campaign_message` `cm` LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id LEFT JOIN `...
0
votes
0answers
29 views

Why simple mysql query takes too long?

I really give up. Mysql server suddenly got working very slowly. I have mysql server 5.7.29-0ubuntu0.18.04.1 - (Ubuntu). The machine is pretty powerful, 50 cores, 300GB RAM. I did not have problems, ...
0
votes
1answer
14 views

Mysql max connections and event scheduler

Is there a limit to how many "instance" of a scheduled event MySQL can run? I have set this up: CREATE TABLE IF NOT EXISTS t (c1 INT UNSIGNED NOT NULL) ENGINE=INNODB; SELECT * FROM t; DELIMITER $$ ...
0
votes
0answers
9 views

percona xtrabackup streaming logfile

I am currently streaming my backups to a tar file. But I tried logging the output but am getting nowhere. dest1="backup/"; dest2="streaming_current_backup" xtrabackup --backup --stream=tar --target-...
0
votes
0answers
9 views

mysqljs batch insert placeholder for mysql-function

using mysqljs/mysql how to correctly use placeholder when doing batch insert where one or more value is using mysql-functions? e.g: UUID(), UNHEX(), NOW(), DEFAULT, FROM_UNIXTIME(), etc example: ...
0
votes
0answers
10 views

MySQL 5.6 to 8.0 (Windows)

I need to upgrade MySQL 5.6 to 8.0 on a Windows community server (install version). Do I need to dump and import to 5.7 then to 8.0 ? or a simple dump of all databases from 5.6 is enough ? thanks ...
0
votes
0answers
8 views

Add data from rows to different columns if more than 1 row have a partial match

I tried to find a solution but do not find anything to add multiple columns from partially matching rows. Data:(Maximum of 3 programs per person) name surname program Jan Peters ...
0
votes
1answer
14 views

Mysql Right join with mutiple condtion not working

i have two table table1 and table2 and when right joining the table2 with table1 and i am not getting the row. here i have attached the fiddle link also CREATE TABLE `table1` ( `orderid` int(11) NOT ...
0
votes
1answer
21 views

Unknown column 'empresa' in 'where clause' [closed]

I have a problem with my Query and I have tried multiple ways to make it works and I can't wrap it around. Basically I have 2 script, the first script takes all the column in the companies on ...
0
votes
0answers
15 views

Convert rows into columns MySQL DataBase

I have two tables and I would like to generate a query relating the tables. Until now I have this query: SELECT Table1.*, table2.* FROM Table1 INNER JOIN table2 ON table2.FK_ID = Table1.ID but I ...
1
vote
1answer
39 views

Is this query too slow regarding the job it is doing?

It takes around 6 seconds for the following query to execute on a MacBook Air with 1,8 GHz Dual-Core Intel Core and 8 GB 1600 MHz DDR3, running mysql Ver 14.14 Distrib 5.7.27. I would like to know if ...
1
vote
2answers
32 views

MySQL Optimization on my server

I have an active server running around 80-100 active websites. I would like to optimize the server and possibly reduce the amount of RAM and optimize my MySQL settings. I have 30 GB of RAM assigned ...
0
votes
1answer
29 views

Why does this UPDATE statement no work? [closed]

UPDATE hwdb1.tblBearbeiter, (SELECT tbltmp.tmp_pnr, tbltmp.tmp_bez FROM tbltmp) AS relevant SET tblBearbeiter.b_pnr = relevant.tmp_pnr WHERE tblBearbeiter.b_pnr = 0 AND tblBearbeiter....
0
votes
0answers
9 views

lock-ddl-per-table option not working as expected in Xtrabackup

I've recently been getting the following errors on some of my Percona XtraBackup backups: InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have ...
0
votes
0answers
18 views

Delete from table one row with 2 variable with limit

I'm developing a PHP script who store session of users in a Database. When an user logout from server, remove only one row ( because same user can login more than one time ) When server reboot, ...
0
votes
1answer
25 views

Split database per pool

I need to split a database of users by X pool and each pool need to have nearly the same number of users based on their email provider... For explain to you better, if my database content: 100 users ...
-1
votes
0answers
17 views

MySQL - Which options in the configuration file have an impact on memory usage?

I've been wondering how to manage MySQL memory usage since by default it takes up to 350MB while idle on my machine, I don't have memory issues, I honestly just wondered how could it be done. I found ...
0
votes
0answers
18 views

How to prevent 'table is mark as and last (automatic) repair failed'?

This is the second time within the last 4 days that one of the tables in my database got corrupted. The is the error that I have seen in my apache log: PHP Fatal error: Uncaught exception '...
-1
votes
0answers
22 views

MySQL Get Root Password

I have created adatabase on https://db4free.net/ Now After I login. It tells me that my user has no privilages. Access denied for user 'myroot@localhost' (using password: YES) (SQL: select count(*)...
-1
votes
0answers
24 views

How does MySQL store the row data internally?

Does a RDBMS store the row data one after another in a data block? Once data block is full, is the new block assigned continuous? Per my understanding when table is created, DBMS vendor must be ...
0
votes
0answers
15 views

TiDB vs Vitess - How should I base my decision?

I need a scalable MySQL-compatable solution that can scale to multiple installs across a network, handling millions of records on several tables with tens of thousands of concurrent requests per-...
0
votes
0answers
18 views

How to punctuate an INSERT statement that uses FROM_UNIXTIME

In a Python environment, I've got the following variable: post_time_ms="1581546697000" which is a Unix-style time with milliseconds. In my table, "created_date_time" is defined as a datetime column....
0
votes
0answers
13 views

database structure for ecommerce (laravel) [closed]

I'm creating an E-Commerce website. and I'm stuck with the shopping cart and payments and orders ... I don't know how to build a reliable and safe structure. you can see my attempt in this picture: ...
0
votes
1answer
17 views

Exporting mysql query result to .txt file

I want to export query result into .txt file The path of .txt file and query is dynamic DELIMITER // create procedure test(filepath text , _query text) BEGIN SET @sql = CONCAT(_query, ' into ...
1
vote
3answers
35 views

Unique over 2 columns

I have 2 columns email1 and email2. Is it possible to define a constraint so as to be unable to add the same email twice in these columns for the same record, using only indexes?
0
votes
0answers
53 views

Slow query when matching columns from second table in JOIN with GROUP BY

Update: so, as mentioned in the comments thread, it seems the ORDER BY might be the most direct cause of slow execution. So, the question should be: how can this query use ORDER BY score and execute ...
0
votes
1answer
15 views

MariaDB Can't Create New User

Fresh install of MariDB 10.4 (fedora 31). I login into mysql as root: MariaDB [mysql]> select User, Host,Password from user; +-------+-----------+-------------------------------------------+ | ...
0
votes
1answer
15 views

How to correlate data between 2 tables?

I have 2 tables that I am dealing with, table 1 has identity information (II), columns ID and Name. Table 2 has data collection (DC) information, InfoId, DCId, data, timestamp. The DC table has time ...
0
votes
0answers
33 views

What kind of complications would I face downgrading from MySQL 8 to MySQL 5?

Let's assume I wanted to downgrade to from MySQL 8 to MySQL 5.7. I know this isn't supported, but what kind of complications am I likely to encounter. The docs say, The only supported alternative ...
0
votes
1answer
16 views

Percona Server - binlog not automatically purge with mysqladmin flush-logs

I have a little problem and I need your help please. Here is the story : I have Percona Server in version 8.0.13 with one slave and the old binlogs on master are not deleted automatically when flush-...
-1
votes
0answers
17 views

-bash: PURGE: command not found

I want to delete binary log files following are my binary log files binary2.000001 binary2.000002 i want to delete binary2.000002 mysql> PURGE BINARY LOGS TO 'binary2.000002'; It shows me:...
0
votes
1answer
16 views

MySQL ERROR 1064 (42000) insert via bash script (RHEL)

MySQL Server: 8.0.16 Commercial OS: RHEL 7.5 I have written a simple script, to insert certain information into a database table. Following is my script # cat ~mysqldba/bin/connections.sh #!/...
-2
votes
0answers
13 views

Setting remote access permission Error

I am looking to allow another developer remote access on his machine to the database I am running. I have logged onto MySQL database via the command prompt and have entered the following command ...
2
votes
0answers
54 views

Why does MySQL take so long to execute this query on a large dataset?

Been banging my head against the wall on this one for a while now and hoping someone can help me out. The SQL query: SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `...
0
votes
2answers
28 views

create a related row if no related row exists

I have two mysql tables with a one-to-many relationship. I want to find all rows in the 'one' table which do not have a row in the 'many' table and add a single row in the 'many' table for each one. ...
-1
votes
2answers
25 views

UPDATE with too much values at IN state takes too long

I have the next query: UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND branch_id IN (23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,...
0
votes
0answers
18 views

Most efficient way to store data for aggregation queries [closed]

Say I have a table with the columns category_id, sub_category_id, time. I want to be able to do various aggregate queries like SELECT COUNT(*) FROM table WHERE category_id IN (1, 2) AND ...
0
votes
2answers
30 views

Finding rows in a table with a foreign key pointing back at itself where the referenced row differs in a certain column

I can't formulate my question, so I will try to explain here and hopefully change the title later on. I have a table Organisations ( id, platform_id, parent_id ) Here is the tricky part: ...
-1
votes
0answers
13 views

Does a unique clustered index give array-like lookup performance?

Suppose I have a MySQL table with a unique integer index id that is auto-incremented for each row. id | PlanetName ---+----------- 0 | Mercury 1 | Venus 2 | Neptune Suppose that I am primarily ...

1 2 3 4 5 366