Stack Exchange Network

Stack Exchange network consists of 175 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.

1
vote
1answer
45 views

MySQL Error: Data Too Long For Column CHAR(5) - MariaDB 10.3.10

When inserting data from CSV to MariaDB 10.3, the following error is generated while trying to insert value '00010' into NCHAR(5) column ERROR 1406 (22001): Data too long for column '...
0
votes
0answers
12 views

MySQL date_format German date value not change to date type

I have a column that is varchar and the value of the the data is a date (i.e. 31.05.2019) I'm trying to convert the varchar field to a date field using the following function: date_format(...
2
votes
0answers
43 views

What is the name of month 0?

MySQL permits you to store a zero value of '0000-00-00' as a “dummy date.”: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html I'm currently inserting into this field using STR_TO_DATE(?...
1
vote
2answers
24 views

Get the Largest Count of Consecutive Value

I have this table that records win and lose. Let us say this is my table create table test( col1 int not null )Engine=InnoDB; it has 10 rows : 0,1,1,1,0,1,1,0,0,1 1=Win and 0=Lose. I want to ...
0
votes
0answers
22 views

SQL Server - Fail to create new MySQL linked server - Error 7303

I'm trying to create a MySQL linked server but I get a 7303 error. I tried both x86 and x64 versions of the odbc driver but it doesn't change anything. My connection on the system odbc to the database ...
0
votes
1answer
45 views

Mysql - generating trends of rank

I've a table of students score ID_STUDENT | SCORE ------------------ 1 | 90 2 | 80 3 | 99 4 | 80 5 | 70 ...
0
votes
2answers
28 views

MySQL 8.0 cannot insert NULL values to date field

I'm trying to insert a data from file to MYSQL 8.0 using the InnoDB engine. I'm using this query: LOAD DATA INFILE '/var/lib/mysql-files/item_new_1_4.dat' INTO TABLE Item FIELDS TERMINATED BY '|'; ...
0
votes
1answer
28 views

Keep track of the last read record in MySQL

I have 2 databases in MySQL: 1) An input Latitude-Longitude_dB ('latlong_db', henceforth): It has the latitude and longitude of each reading from a GPS tracking device. 2) A Weather_db: I read the ...
0
votes
1answer
46 views

Row for each date, start and end

I have a table that has two columns, startDate and endDate. Each day has its own capacity. Between these dates, user can get from 1 to n items. I am going to get number of items remaining for each day ...
2
votes
2answers
22 views

How to design a database for search history with multiple facets?

I want to show users a history of their searches, which can often be complicated: any of these terms: [a,b,c] all of these terms: [d,e,f] not these colors: [h,i,j] Since these searches are done ...
0
votes
0answers
12 views

Missing directory after MySQL 8.0 for Ubuntu installation

After installing MySQL Community Server 8.0.16-2 on Ubuntu 19.04 I have the directory /var/run/mysqld missing and the server refusing to start. What's wrong?
0
votes
1answer
24 views

What's the best way to maintain a writable DB slave which is always overwritten by the master?

I need to replicate our main server's databases (MariaDB) to a local server in our office, which will be used as an offline mirror when we have connectivity issues. The data on the master should ...
0
votes
1answer
32 views

Can I configure MySQL Replication as Master - Master - Slave?

I am considering how to configure our MySQL replication. Hope I can get any advices from community. In fact, I am using MySQL 5.7, and have 01-Master 02-Slave 03-Slave My Applications are using ...
0
votes
1answer
35 views

“Cannot drop index 'unique_token_data': needed in a foreign key constraint” in MySQL without a foreign key

When I try to remove a unique index, MySQL Ver 5.7.26-0ubuntu0.19.04.1 says that it is needed in a foreign key constraint: mysql> ALTER TABLE project_erc20token DROP INDEX unique_token_data; ERROR ...
0
votes
2answers
23 views

MySQL Compare Tables and generate delta

I have two exact tables with different data. I need to be able to generate a Mysql View with the following: All new rows that don't appear in previous_table but appears in current_table. All rows in ...
0
votes
0answers
26 views

mysql v8.0.16 unable to initialize the database following fresh installation

After a fresh installation of mysql 8.0.16 I get these errors: 2019-06-02T06:00:29.777041Z 0 [System] [MY-013169] [Server] C:\mysql\bin\mysqld.exe (mysqld 8.0.16) initializing of server in progress ...
1
vote
2answers
70 views

Same column present on two or more foreign key constraints

A simple schema leading to my question is the following: CREATE TABLE A ( AID INT AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE A_COPY ( AID INT, CID INT, PRIMARY KEY (AID, CID), ...
0
votes
1answer
13 views

Get position of mysqlbinlog error. I have the error's line number

If I use mysqlbinlog to load a binlog into my database like this: mysqlbinlog --defaults-file=/path/mysqld.cnf bin.000011| mysql -u root and get an error like this: ERROR 2006 at line 290: MySQL ...
0
votes
0answers
5 views

Contact Form for my website not working in php [migrated]

I am trying to make a contact form for my website in php, but it seems something wrong somewhere, which i am unable to locate. Can you please help me to locate the error in the following code will be ...
0
votes
1answer
32 views

How necessary are primary and foreign keys when I truncate all the tables in my database daily?

I'm fairly inexperienced at database design, so I am trying to plan my database beforehand as much as possible. This database is to hold information about a VMware environment. The database has 9 ...
0
votes
0answers
15 views

Proper way to store permissions (datatype)

I'm modeling a MySQL database and it has a users table with the columns: username passhash permissions The access crontrol will be made on programming level (NodeJS). Each user may have a different ...
1
vote
1answer
30 views

MySQL Case Sensitive Join on Varchar

I have two tables, both with identical data types, character sets, and collation explicitly specified. CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL For a case-sensitive join on the ...
0
votes
1answer
23 views

How to replicate particular table in MySQL master-slave configuration?

How do I replicate a particular table in a MySQL master-slave configuration? I have tried replicate-do-table and replicate-wild-do-table. I tried in my.ini file; is there any alternative to replicate ...
0
votes
2answers
32 views

Predicting the primary keys exhaustion in MySQL based on rate of insertion

We are running out of primary keys for our table in production. We do not want that to occur again. So we need a mechanism to alert us when we are running out of primary keys based on the rate of ...
-2
votes
1answer
20 views

Procedure to change a person's designation [closed]

People are occasionally moved between different departments. I want to create a procedure that accepts a name (name of person) and a department(department that person is assigned to) and changes the ...
0
votes
1answer
41 views

MySQL - how to get time from Unix milliseconds timestamp?

My MySQL version is 5.6; it has a FROM_UNIXTIME function. But it works for timestamps with 10 digits: 1447430881. mysql> SELECT FROM_UNIXTIME(1447430881); -> '2015-11-13 10:08:01' Now ...
1
vote
1answer
19 views

MySQL replication: how to safely propagate ignored table structure changes from master to slave?

We have a MySQL database with a replication set up (master to single slave). Some tables are skipped from replication (they are listed in Replicate_Wild_Ignore_Table when calling SHOW SLAVE STATUS). ...
0
votes
2answers
56 views

MySQL 5.6.26 high concurrency dead lock

Table structure: CREATE TABLE `extend_0` ( `id` bigint(20) NOT NULL, `code` varchar(30) NOT NULL, `data_key` varchar(50) NOT NULL, `data_value` varchar(200) NOT NULL, `create_time` timestamp NOT ...
0
votes
1answer
17 views

Is it possible to grant trigger permissions on only one schema MySQL

I am an analyst that was recently granted a schema on MySQL that I could use for some business needs. I was told by my DBA that the permissions associated with Triggers require global super user ...
1
vote
1answer
39 views

How to automate basic ddl scripts execution and management [closed]

Looking for suggestions (A Script, third party tool, software etc.) which can help automate some of the administration tasks around execution of DDL scripts across environments. Database is usually ...
0
votes
0answers
26 views

How to prevent inserting same value in two columns in one Table?

I'm trying to solve problem which will compare BEFORE INSERTING columns values partnerCode and partnerCodeOwner1 with username in the table below, If partnerCode and partnerCodeOwner1 values same with ...
0
votes
0answers
20 views

Transaction stuck in “ROLLING BACK” - process does not exist

I have this "ROLLING BACK" transaction, with trx_mysql_thread_id = 0 > mysql> SELECT * FROM information_schema.innodb_trx\G; > *************************** 1. row *************************** &...
0
votes
0answers
14 views

Spatial Query using MySQL 8.0

I have a database in MySQL 5.7 where I create a Lat/Long column using "POINT", then I create a Index on this column with main key as second column. So my database is like PID, SurveyID, GeoPoint ...
0
votes
0answers
15 views

Having problems in cleardb database from my local machine

I am trying to host my simple php mysql app onto heroku.For that i have used cleardb addon. I have got database url: mysql://b567e9a2a204f8:ea13defd@us-cdbr-iron-east-02.cleardb.net/...
-2
votes
0answers
23 views

Delete record from 1 table based on another table like table 2 without delete record from table 2

I have 2 tables like this: product id_product name price stock 1 prdct1 100 0 2 prdct2 150 50 3 prdct3 50 7 cart ...
-1
votes
1answer
10 views

PHP query to fetch data from two table into one php script and display as one list

I have table called request,from where I am getting the data but my problem is that I want to get another table data to fetch in same php script that table name is OEM where no one table columns ...
0
votes
0answers
12 views

Write mysqlbinlog errors to log file

How do I write any errors from mysqlbinlog to a log file? This is on Ubuntu. I have a binlog that creates an error: ERROR 1032 (HY000) at line 29: ...' I want this error to be written to a log. I ...
0
votes
2answers
38 views

SQL query to list all student users, add column to show who were logged in at least once

I have a query, which lists those users, which are students: SELECT * FROM mdl_user as u JOIN mdl_role_assignments as ra ON u.id = ra.userid WHERE ra.roleid = 5 /* is student */ I have a table ...
1
vote
1answer
43 views

Very strange tables in mysql own database

I was checking some tables into MySQL database, and I can see a lot of tables (1000+) with random names, and I have no idea of how they created and why they exists. I'm sure they don't belong to the ...
0
votes
0answers
18 views

Composite index to cover sum of columns

Is it possible to create an index in MySQL that will help with a query like this: SELECT a, b, c FROM `table` WHERE a + b + c > 0 I've tried adding a composite index on all a, b, and c to no ...
1
vote
0answers
31 views

What does it take to replicate the mysql, 'mysql' database correctly?

I've searched for a while and I am having trouble finding anything that even addresses my situation. I have need to replicate the 'user' table specifically, from my master cluster to my async slave....
0
votes
1answer
29 views

Very slow JOIN + ORDER BY query only in dev environment

I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query. This query is taking 75 ...
3
votes
0answers
26 views

Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7 We have an index over a varchar(255) column in a table of ~ 500M rows. select distinct varchar_column from table yields seven results, with the max length 21. A DBA consultant ...
0
votes
1answer
12 views

Move Mysql database from Windows Xampp to Linux MySql Server

I 'am looking for a way to move some database in my local xampp to remote MySql Server , running into a Linux VM (Centos 7) Any idea or any suggestions to do this migration?
0
votes
1answer
19 views

Errors when importing dump from MariaDB with InnoDB engine to MySQL Cluster with NDB engine

When i import DB dump from MariaDB 10.3 with InnoDB engine to MySQL Cluster 7.6 with NDB engine i receive errors: ERROR 1112 (42000) at line 159: Table 'NAME' uses an extension that doesn't exist in ...
0
votes
1answer
32 views

Run PostgreSQL without admin / superuser

I'm trying to setup a secure append-only database with PostgreSQL or MySQL. My idea for that is to generate a database where no admin/superuser accounts exists and only users with limited privileges ...
0
votes
1answer
26 views

slow log contains query, but query time is less than long_query_time

mysql> select @@long_query_time; +-------------------+ | @@long_query_time | +-------------------+ | 2.000000 | +-------------------+ 1 row in set (0.00 sec) mysql> select @@...
0
votes
1answer
14 views

count and group_concat

I have data in a table like so: +---------+----------------------------------------------------+ | videoid | ips | +---------+---------------------------...
0
votes
0answers
10 views

How to model different entities that can have user accounts?

I need to model a system where Applicants can apply to Job Offers offered by a Company, but all that is through their User Accounts. How can I model that relationship? (I'll use MySQL) My ideas are ...
1
vote
1answer
37 views

Amazon RDS MySQL Troubleshooting High Sessions

I just migrated my forums application to AWS. I'm in the middle of tweaking things for performance and I noticed that the number of sessions for my RDS instance (t3.large) is consistently exceeding 2....