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.

learn more… | top users | synonyms

0
votes
1answer
23 views

Get Position of row from query

I have a database that looks like the following: +-------------------+------------+-----------+-------+------------+------+ | SteamID | Name | Map | Time | Date | id | ...
0
votes
0answers
15 views

DBA training/conference suggestions [on hold]

I recently transitioned from a developer role to a DBA role at my job. My boss wants me to attend some kind of training or conference this year. I maintain several Drupal websites that run on Ubuntu ...
1
vote
0answers
16 views

Exactly what are MySQL connections?

There are tons of questions on how to deal with errors around reaching the maximum number of connections in MySQL. I often program in PHP so answers generally range from changing the max_connections ...
1
vote
0answers
2 views

MariaDB database access using roles

I run MariaDB 10.0.23, and I use roles to grant privileges to users. I have a user john: +-------------------------------------------------------------------+ | Grants for john@% ...
1
vote
1answer
16 views

Get table sizes without SELECT permission

I want a scheduler to run a simple SQL script to collect the sizes of tables in our MySQL database: SELECT table_name, data_length, index_length FROM information_schema.TABLES The user the ...
0
votes
1answer
24 views

MySQL - design help for scale

I will be using Amazon Web Services RDS service to host a MySQL 5.7 server (Currently AWS do not support 5.7, but they have announced they are working on implementing this version and it will be out ...
0
votes
1answer
37 views

SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once [duplicate]

Table A (users) id | name | ip 52433 Yashas 1.1.1.1 512 Admin 5.6.7.8 Table B (admin_record) id | admin | time | date | reason | action 52433 512 2312352 ...
0
votes
0answers
28 views

MySQL 5.6 Server start fails: InnoDB Operating system error number 23

I use a local MySQL 5.6 Server (InnoDB) on a Windows 7 machine to store several schemas of a research project. Until yesterday the system ran without problems. During a query, MySQL Workbench ...
1
vote
0answers
24 views

Cardinality of an index in a MySQL table is equal to table's row size

I have just included an index to a MyISAM table and the cardinality appears to be 1 million something which is exactly the number of rows in the table. In fact it needed to be 315 because distinct ...
0
votes
0answers
4 views

How can I cache queries in sqlalchemy just like I do it in mysql by using SQL_CACHE keyword?

I want to cache sqlalchemy,i have discovered that using keyword SQL_CACHE in mysql can be helpful in caching queries on demand. But how do i do it in sqlalchemy ? Is it possible ?
0
votes
0answers
27 views

Issue with fetching data from mysql according to recent timestamp ASC ordering

I am having trouble with mysql query to fetch the data according to the recent timestamp in ASC order. it means the recent updated one record will be first shown. The Query is grouping with the ...
2
votes
2answers
62 views

How to optimize query on MYSQL with multiple group by clause

I have a web application where users can view data of performance. There will be around 200 to 250 users. Each user will have like 100,000 rows of data per day. It is only table and here is ...
0
votes
0answers
33 views

MariaDB: inner join searching for 1 in TINYINT column not working

Since yesterday I've been trying to make a query work. I have two tables: image where I store information about images and imagehit where I store information about how many times those images are ...
0
votes
0answers
27 views

Better configuration for a In-Memory MySQL database?

I have a local MySQL database for the only purpose of execute some PHP tests. To improve the speed of the execution, I created the database in memory using the ENGINE=MEMORY statement in every table ...
1
vote
4answers
53 views

Selecting data from another table using a foreign key

I have two tables A & B. Table A id | name | num Table B id | date | roll id in table B is a foreign key which relates to the id in table A. I want to get the contents of table B where id ...
0
votes
1answer
10 views

Incremental column depening on column value

I would like to have a column which has an incremental integer depending on the column value after sorting the table. E.g. after sorting by state: Nr | State | Counter ...
0
votes
0answers
8 views

Changing global variables in MySQL RDS instance

I have a MySQL RDS instance that I created. I need to set the net_write_timeout variable in the instance so it is available globally. Can this be done ?
0
votes
0answers
27 views

simple math with SELECT - no resulting row

Simple math with SQL don't give a result. There is written 1 row in set, but I don't see the result/row. MariaDB [(none)]> select 5242880 / 1024 / 1024 as current; 1 row in set (0.01 sec) ...
0
votes
1answer
26 views

Unique identifiers for the same type of object in the same request

This question is about your opinion and common approach for relational databases. So please, do not vote me down. I really need to solve this problem.. I remove this question once I meet the answer. ...
0
votes
1answer
22 views

Is it safe to sync MySQL binlogs?

I am working on a strategy to replicate my MySQL binlogs to Amazon s3. I have looked into mysqlbinlog --remote-server but it seems like adding a file watcher on /var/log/mysql which triggers an aws ...
0
votes
1answer
25 views

Import large DB 300 GB from ubuntu command line [on hold]

My DB Size is 300 GB , I have 2 SQL Dump file , one is 100 GB and Other one 200 GB , When I import the database it is disconnected due to some problem. almost 90 GB data’s are imported from both the ...
0
votes
0answers
16 views

Identify MySQL Connection timeout

I am just trying to identify when MySQL drops a connection . For this I initiated a connection to MySQL Port using nc with a timeout value of 7200. $ time nc -w 2 car-005 3306 N ...
1
vote
1answer
25 views

MySQL: SELECT and JOIN with not exists values?

How can i SELECT data from two tables using 'JOIN' operator, if some values with cat_idx and tar_id from categories (tar_id = 87) does not exists in time_discounts table. (I need to print '0' in this ...
2
votes
1answer
36 views

Increase date by 1

I have a query that fetch whole year record and sums up the count.The query is as follows select count(*) as t, m.machine1 from sap_prod_data s left join sap_machine_speed m on s.mch_desc = ...
0
votes
2answers
12 views

How to set query_cache_type=2 on mac?

I want to enable query_cache_type and change its value to DEMAND. When i am doing it via terminal its giving this error:- ERROR 1651 (HY000): Query cache is disabled; restart the server with ...
0
votes
2answers
29 views

mysql mutual friends and friends you may know

This is my Friends Relationship Database Design: INSERT INTO `relationship` (`user_one_id`, `user_two_id`, `status`, `action_user_id`) VALUES (1, 2, 0, 1) Please how do I develop mutual ...
2
votes
1answer
33 views

MySQL best performance settings [on hold]

I am going to do some performance tests on MySQL server as I want to evaluate how MySQL behaves in some situations. MySQL is the only (additional) software installed on my server (Windows Server ...
-1
votes
0answers
23 views

How to import multiple .csv files as a batch?

How can I load multiple .csv files to a remote MySQL database? For example I want to load all these CSV files C:\csvFiles\file1.csv C:\csvFiles\file2.csv C:\csvFiles\file3.csv C:\csvFiles\file4.csv ...
0
votes
0answers
28 views

One bitmask column vs multiple bit columns [duplicate]

I have many tables with a column storing a set of days of week. I used to use MySQL and data type "set" worked well. For example: [worker] id weekday_mask (data type: ...
4
votes
1answer
50 views

Mysql LOAD DATA INFILE 100,000 csv records perfect BUT 300,000 records - 0 rows inserted

The following query imports 100,000 perfectly, but larger files nothing happens. No errors, no rows loaded. Any clues you can offer are appreciated. I spent hours looking into max file and memory ...
0
votes
0answers
21 views

MySQL Replication - 1 Master - 2 Slaves. Inserting works for One, not the other

I am a Python developer, working with 3 databases, configured in Master -> slave,slave style. Each slave has their own application associated with it. I am not too familiar with database ...
3
votes
1answer
36 views

“TRUNCATE doesn't run ON DELETE trigger” true or false

I was searching on the topic of why TRUNCATE is placed in DDL instead of DML for which I found following answer here Why is truncate DDL? I this answer I found a sentence as given below "The fact ...
-2
votes
0answers
26 views

Correct the code? [on hold]

SELECT students.first_name, students.middle_name, students.last_name, subjects.subject_name, level_of_entry FROM entries INNER JOIN subjects ON entries.subject_id = subjects.subject_id FROM entries ...
0
votes
1answer
27 views

Full text indexes on big tables

I have a news website where I store my articles in a table that looks like that: Id title data Where data contains the content of the article and is the biggest column in the database by far. ...
4
votes
4answers
169 views

Question about 'mysql error 1064'

ALTER TABLE sample62 CHANGE newcol c; This code makes me mad. I just followed the book I had. Program said You have an error in your SQL syntax; check the manual that corresponds to your ...
0
votes
1answer
67 views

How to optimize database for specific queries?

Hello I have built this queries thanks answers to my questions: How to get data from three different tables and How to show rows with 0 without any records in database But now I have performance ...
0
votes
0answers
21 views

Is there any reason to use Innodb over Aria for wordpress?

Is there any reason to use Innodb over Aria for Wordpress? I currently have an nginx php-fpm wordpress Mariadb stack. Aria seems like a natural fit, given my workload is 98% select statements, and ...
-1
votes
0answers
21 views

Writing SQL query for scenario [on hold]

I am trying to develope a shopping application. I need to write a query for the following table structures : enter image description here GRPMT : This contains the master group of Items(like ...
0
votes
2answers
25 views

How do I do a join and exclude certain records?

Table STUDENT has columns ID, LASTNAME, FIRSTNAME Table TEACHER has columns ID, TEACHER_ID, STUDENT_ID Students can have many teachers. Teachers can add students to their class. I want to be able ...
0
votes
0answers
28 views

how to desing a database for loan management in mysql [on hold]

I am not very well experienced in database design . I designed database tables for user and account information `but in case of designing table for loan management and fixed deposits.` I want to ...
3
votes
1answer
60 views

Search between dates where all dates appear

I want to search house_id where all days appear within a range of 2 dates from startDate to finalDate. Tables columns are: id house_id date status house_id + date are unique the closest I could ...
1
vote
1answer
41 views

How do Locks Work in MySQL or MariaDB

There is a case which makes me a bit confused. As far as I understand deadlocks should appear if there are two query statements (including subqueries) in request (each transaction). So the ordering of ...
0
votes
1answer
45 views

Transaction Identifier - possible with MySQL?

I have quite complex code, which at the beginning starts transaction. After that several user queries are performed (more or less without my control) and at the end the transaction is committed if ...
3
votes
0answers
18 views

insufficient history for index

In MySql log, there is one error `InnoDB: insufficient history for index 0' I don't know why its occur. I have done google and found following ...
0
votes
0answers
31 views

Error: “mod_fcgid: can't apply process slot for” makes my server unresponsive for a period of time

Following error keeps appearing in my PHP error_log mod_fcgid: can't apply process slot for So far I pinpointed this issue to following settings in httpd.conf FcgidMaxProcessesPerClass 8 ...
0
votes
0answers
21 views

MySQL count multiple values in one column by PHP [migrated]

This my data in Column interested (Education; Job) I use this code: $sql3="SELECT interested, COUNT(*) as Education FROM register GROUP BY interested"; if ($result3=mysqli_query($con,$sql3)) { ...
-2
votes
0answers
20 views

My login page is returning Unknown column 'sitlanic' in 'where clause'

My login page is returning: Unknown column 'sitlanic' in 'where clause' $uname = $_POST['name']; $pass=$_POST['b']; $sql="select*from names where username=$uname and password=$pass"; ...
0
votes
0answers
25 views

mysql processes still alive from processlist

I have already posted this on stackoverflow, but I got no answer: mysql processes still alive from processlist I have this in my my.cnf: wait_timeout=1800 interactive_timeout=1800 ...
3
votes
1answer
97 views
+100

Insert data from multiple rows into multiple columns

I cannot find, after much searching, how this would be done. My web searches get Pivots and Concats and Cases and Subqueries etc, none of it is quite solving the issue for me. The ...
-1
votes
0answers
51 views

How do we convert SQL Server query to MYSQL? [on hold]

How do we implement this SQL Server syntax: IF(SELECT MAX(DateAdded) FROM RestoreDetectors " + " WHERE CONVERT(VARCHAR,DateAdded,101) = CONVERT(VARCHAR,GETDATE(), 101) " + " AND DATEDIFF(SECOND, ...