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
0answers
6 views

mysqldump from MySQL5.1 to MariaDB 5.5 breaks Wordpress site

We have an unstable Windows installation of Wordpress that uses MySQL 5.1.73. I am trying to migrate/upgrade this to MariaDB 5.5.62. I am creating a mysqldump of the Wordpress DB, uninstalling MySQL 5....
-1
votes
0answers
11 views

SQL Log File to Table Insert/Update

I have a emp table which contains three columns. Lets say EmpNo, EmpName and Age. Empno is a primary key column. I am getting data from logs or text file. If there is no particular Empno then row ...
0
votes
1answer
21 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
0answers
14 views

Sync test MySQL database with production

Is there a convenient way of syncing changes between two MySQL databases, each running on its separate server? I have a development / testing db on Heroku's JawsDB and would like to update changes (...
0
votes
1answer
21 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
0answers
12 views

mysql - allow a user to grant privilliges to databases to other users

My goal is to allow the blah_service user to grant privileges to all databases that start with blah to other database users. /* This results in access denied error. GRANT GRANT OPTION ON `blah\_%` . ...
0
votes
2answers
24 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
27 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
0answers
12 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 (...
-1
votes
0answers
15 views

Help needed in PHP MySQL code

My Code is not working like this <?php require_once('DbCon.php'); // Connect to DB if(isTheseParametersAvailable(array('a', 'b', 'c', 'd','e'))) { $a = $_POST['a']; $b = $_POST['b']; $c = $...
1
vote
1answer
25 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
57 views

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

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; ...
1
vote
1answer
14 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 ...
0
votes
0answers
27 views

Concurrent update queries locking rows when `where` clause doesn't match

My application sends messages to Slack. On very rare occasions we may need to retract the message going out. These retractions are handled by queueing a job for each messageId that has been created ...
0
votes
2answers
45 views

SQL Grouping rows in a feed database table, output only grouped rows adding a group row

I'm trying to write an SQL query to work on a raw feed that's been imported to a MySQL database table. The table's rows loosely resemble this example. | sku | group_name | description | ...
2
votes
1answer
25 views

MySQL when using WHERE BETWEEN, query hits all partitions

This is probably just me misunderstanding how MySQL partitions work, but I have a table defined with: `ID` int(11) NOT NULL, `target_id` int(11) NOT NULL, `created_at` datetime NOT NULL, ...
0
votes
0answers
35 views

How to Create a Database from File Names and Details [on hold]

I've downloaded a bunch of files (45,000+) from a collection of Go/Baduk/Weiqi games and would like to create a SQL DB for them, using their file names, dates, description, and, if possible (I'll ...
0
votes
0answers
8 views

Innodb_buffer_pool_instances in mysql 5.5.49 is working?

I defined my.cnf parameters as follows: [mysqld-5.5] thread_stack = 524288 table_open_cache = 1000 key_buffer_size = 512MB innodb_buffer_pool_size = 5GB innodb_buffer_pool_instances = 5 ...
0
votes
0answers
21 views

Syntax error in create trigger [on hold]

CREATE TRIGGER T_INSERT_blog ON `blog` FOR INSERT AS BEGIN TRANSACTION UPDATE `blog` SET `createTime` = CURRENT_TIMESTAMP WHERE `id` IN (SELECT `id` from INSERTED) COMMIT TRANSACTION ...
0
votes
0answers
6 views

Multiple MySQL(masters), one Database on each replication over VPN to one MySQL install

I am trying to figure out the best way to replicate data from multiple MySQL installs from different hosts to one central MySQL host. I was looking for a program or a setup that would allow me to ...
0
votes
1answer
12 views

MySQL - Blank fields in tab-delimited .txt file causing errors in LOAD DATA INFILE

I am working with MySQL 8.0 through MySQL Workbench on a Windows machine. I am having an enormous amount of problems importing an excel file into a MySQL database. I think the problem is that my data ...
0
votes
1answer
8 views

How to specify “partition (partx)” in “mysqldump” command?

I have to export some data from a table which is partitioned, and with some extra condition So I use mysqldump command,and use --where"xxxxxx" options as below mysqldump -hxxx -Pxxx -uxxx xxx rtm --...
1
vote
1answer
24 views

In MySQL replication, why aren't queries run on the slave when there was no USE statement on master?

We have replication running from our master server to a slave. binlog_format is the default; mixed. We have noticed something weird and are not sure whether it's a bug or intended behaviour. Certain ...
0
votes
0answers
48 views

Subscription and addon payment DB design

Please find a high level overview of the table design for subscription as well as addon payments for an e-commerce website. I have omitted several columns and tables for brevity. Table descriptions ...
0
votes
1answer
56 views

Is it good practice to create a separate table for views count?

I have created a blog table where I'm having a field called views_count but I have heard that updating the views_count field on each page view is strain. So I have created now a seperate table for ...
0
votes
1answer
20 views

SQL query on SUM function plus JOINS

Sorry if this has been covered before but I couldnt find an example to my issue Situation: Say I have a table of sales and want to display them Step 1) SELECT payment_id, amount, payment_date FROM ...
0
votes
1answer
23 views

mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0

I'm struggeling the last two days with the following issue: Restoring a ~1GB database dump with only innodb tables on a mysql 5.7 server takes about 5 minutes. After upgrading the same server to ...
0
votes
1answer
15 views

Auto update “TIMESTAMP” column only when a manual phpmyadmin edit occurs?

I have a column in my MySQL database that uses a "TIMESTAMP" to capture the current time. I have an attribute set to "on update CURRENT_TIMESTAMP" so it auto updates the column whenever and update ...
0
votes
1answer
27 views

Get Table name from another table

SELECT column1, column2, column3 FROM ( SELECT State FROM (SELECT Country FROM t1 where ...) where ... ) where ... MySQL query Is not working in PHP. I ...
0
votes
2answers
22 views

Perform calculation from count of values for a distinct value

My Goal is to show the top hosts with successful access attempts (Total, %) 200 = successful attempt 404 = unsuccessful attempt logs table host | status 10.1.1.1 | 200 10.1.1.1 | 200 10.1....
0
votes
1answer
15 views

Limit hostnames for user

I am running MySQL optimisation wizard, mysqltuner, which gives the suggestion: User 'root@%' does not specify hostname restrictions. How is this achieved? I can't readily find an answer.
-1
votes
1answer
20 views

Creating blog fresh tables from old tables [on hold]

I am re-creating a database structure from old tables. New tables should be optimized and easy to understand. In old database the developer used video table name for storing video blogs. However I ...
0
votes
1answer
15 views

How to test MySQL master-slave replication latency and get the exact time of slave library latency (accurate to milliseconds)

How to test MySQL master-slave replication latency, I need to get the exact time of slave library latency (accurate to milliseconds)
-1
votes
0answers
14 views

Connect to db by session PHP [on hold]

i am trying to make multi language website in php and mysql and i have sessions for EN and FR and i want if session is EN to connect to db2 which i have all texts in english and for FR the same thing, ...
0
votes
0answers
19 views

Activity log PHP webapp MySQL [on hold]

so I have 3 tables on my DB users colaboradores and ativos (quick explanation: users are the permission levels like 'admin' and 'normal user', ativos are just items that are given to colaboradores ...
0
votes
1answer
17 views

MySQL syntax error 1064 near IN stored procedure

an aspiring DBA Here. I'm trying to create a stored procedure for a query I'm running. It is a memsql server which is MySQL based. The procedure is as such: delimiter // CREATE PROCEDURE ...
0
votes
1answer
20 views

MySQL MONTH Clause: Want to get Month Name instead of number

My query is SELECT MONTH(`hire_date`) AS month FROM `emp_tbl` I'm getting output in Java fx if(resultSet.next()){ int month = resultset.getInt('month'); // output is like 11 } Then I ...
0
votes
4answers
47 views

Subtract from two MYSQL Queries

How can I subtract query result B from query result A so that I can see the difference between the two? A: SELECT character_name, ROUND(minutes_played / 60, 0) FROM outfit_member WHERE ...
0
votes
0answers
13 views

is it possible to call a stored procedure within a json_object function in mysql?

I am trying to execute something like this: select json_object ('key', call new_procedure(in id integer(45),out json))) However it gives an error. Is there any workaround to solve this situation? ...
0
votes
0answers
5 views

Data Type guessing in Toad import wizzard: increase number of rows considered

When importing files with Import wizzard of Toad it guesses the data type. In most cases that works fine. Sometimes though it is not correct. A varchar for example is guessed to have a max length of ...
0
votes
0answers
15 views

Red indicator current activity issue in MySQL - AWS

I run a MySQL RDS in AWS and I saw that the current activity indicator is reaching the limit, while the RDS in total is not that much loaded (CPU15%, 40GB of SSD are free, user connections are low etc....
1
vote
1answer
28 views

Query Efficiency - Filter people when name and surname are different columns, and string may appear in any of them

I am working with a table representing real people data, with a schema like this (MySQL Server): CREATE TABLE Users ( id INTEGER PRIMARY KEY, name NVARCHAR(32) NOT NULL, surname1 NVARCHAR(32)...
0
votes
2answers
28 views

UPDATE with multiple WHERE and SELECT condition - MySql

So, I have two tables ativos and colaboradores and they are linked by id_colaborador (FK) on my update page, I'm able to change the id_colaborador but instead of changing the ID I want to write the ...
0
votes
1answer
18 views

Generate Dates between Date Ranges in mysql

Is there a way to generate dates between date ranges. After looking on SO I found out there is a way to use CTE, another option is to use Union All from 0 to 9. Is there an inbuilt function which I ...
0
votes
1answer
12 views

MySQL Subquery count

I need to find how many resources are available in depot when transit arrives. I need to have transit name and resource count on result. I'm using MySQL. I wrote this but it gives me error when adding ...
0
votes
0answers
21 views

Want to fetch weekday values and run recursive queries

SQL table has the date as a column, which will be the Sunday of every week. Other columns have different status messages. I want to compare the status of the current week with last week and know the ...
1
vote
1answer
59 views

Anyone understand why this query no longer works? [on hold]

On Localhost this query works correctly, running normal MySQL: SELECT COUNT(*) as rows, customers. * FROM customers WHERE user_id = 6 AND age = 10 ORDER BY timestamp DESC LIMIT 0 However ...
0
votes
1answer
8 views

mysqlsh rebootClusterFromCompleteOutage force prompt

From mysqlsh I can run this instruction: ... shell.connect(host, pass); dba.rebootClusterFromCompleteOutage(clusterName); But then it's asking me (y|N answer) for each instance it finds offline ...
-1
votes
1answer
21 views

Best MySQL deployment Production [on hold]

I work for a Trading company. There are around 15 databases hosted on a single MySQL server. The MySQL is hosted on a single CentOS VM. The board of directors is concerned about the MySQL machine ...
0
votes
1answer
13 views

MySQL Replace not working

I have back slashes stored in mysql. I want to remove backslash '\'. So, I run query like UPDATE table_name set col_name = replace(col_name, "Equipe d\'Olivier", "Equipe d'Olivier"); More ...