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
136 views

Update records that match like regex from another table

I have two tables - ProductCategories and Products ProductCategories --- productid categoryid 1 2 3 4 Products -------- productid productname 1 Apple 2 ...
1
vote
1answer
25 views

Sql get results only containing all keywords

Let's say I have mysql table called SONGS, with columns: Artist | Featuredartist | Songname | Year What I need is to get Artist and Songname, only containing my keywords (and no matter how they are ...
1
vote
0answers
13 views

Many-many relation with specific type

Sorry if this question has already been asked, but I didn't find it performing a search. I have 2 tables (table_a and table_b), linked by a many/many relation. The relation can be of two types, ...
3
votes
2answers
1k views

Trying to recover/import/transfer database from MySQL data dir

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused. However at the time of doing the export I ...
0
votes
2answers
26 views

Mysqldump failing while restoring database - MySQL 5.6

I have dumped one of my databases of size 5-6GB. I'm trying to restore to another instance but the same is getting failed after sometime with the following error: ERROR 2013 (HY000) at line 12006: ...
0
votes
0answers
10 views

Storage Engine in MYSQL

What are the advantages of TokuDB storage engine in MYSQL ? How do you optimise tables with TokuDB Storage Engine ? Is it the same as OPTIMISE TABLE Tablename or any other optimisation query specific ...
0
votes
2answers
146 views

Does MySQL Workbench lock database during migration process?

I am doing a migration of my database from one server (RDS) to another (localhost) via the MySQL workbench tool. I want to know if during this process my database will be locked for write and read ...
-4
votes
0answers
42 views

Retrieving the data based on id from table contains 2 million rows [closed]

I have one table/collection contains 2 million rows/document (id(20 alphanumerical digits), name, ... etc). From that table I want to retrieve data based on the id within 1 sec Which database ...
18
votes
4answers
80k views

Access denied for user 'root'@'%'

I used to access the root user in MySQL just fine. But recently, I am no longer able to. I am able to login fine : mysql -u root -p Here is the mysql status after login : mysql> status ------...
0
votes
1answer
14 views

MySQL “CREATE TABLE LIKE” include triggers

I'm using MySQL 5.6 (5.6.29-76.2-56-log Percona XtraDB Cluster) When I issue a CREATE TABLE LIKE the triggers on the tables are not copied - is it possible to get them on the new table as well?
0
votes
1answer
127 views

Laggy slave OK to have only one system user in processlist?

In all the examples of a processlist on a mysql slave I've seen, there are two system users. I have a slave that is very far behind the master. When I do show a processlist, there is only one system ...
0
votes
0answers
10 views

Unable to rename table due to metadata lock

I am trying to rename a table but it always just hangs with a Waiting for table metadata lock. Here is how I am trying to do it: start transaction; RENAME TABLE `main_territorypricing` TO `...
0
votes
1answer
608 views

MySQL 5.6 replication causes 'waiting for table lock'

All of the sudden queries on slave server stopped with status "Waiting for table level lock" I restart mysql service and stop replications and locking does not show up anymore. Once I turn ...
0
votes
1answer
15 views

adding a column with default value to slow in mysq 5.1

I am adding a char(1) column to a table with this: mysql> alter table TransaccionesConvenio add Test2 char(1) default 's'; Query OK, 6711062 rows affected (52 min 27.75 sec) Records: ...
0
votes
1answer
114 views

MySQL hangs with nothing in error log

I'm having an issue where my MySQL server (part of a LAMP stack running WordPress) hangs every few hours (giving WordPress a "database connection error"). The error log shows nothing other than ...
0
votes
0answers
7 views

regarding creating two colums using the CONCAT and CONCAT_WS function

Complete newbie here using SQL. Sorry if i don't make much sense here. Basically i'm trying to combine fields on a database that would keep customer's details. For example: fields: customer_title,...
0
votes
0answers
10 views

View-Specific MySQL Replication

I'm trying to replicate only a portion of a table on Computer A (running an instance of MySQL) to a MySQL instance on Computer B, something that could easily be represented by a view. I know it is not ...
1
vote
2answers
156 views

How to solve intermittent MySQL performance issues on simple queries?

We are currently experiencing an intermittent issue with UPDATE/DELETE/INSERT performance. Simple queries like the following take a long time: DELETE FROM ip_filter WHERE filter_id = '348'; ...
0
votes
1answer
40 views

how to take output from second table by joining in SQL [on hold]

table 1 table 2 id Name value id status 1 a 1000 1 open 2 b 2000 2 closed 3 c 3000 ...
0
votes
3answers
37 views

How to select duplicates into new columns?

I am trying to select distinct data in new columns! My Table productID | imageURL 1 | a1.jpg 1 | a2.jpg 2 | a3.jpg Target Table productID | imageURL1 | ...
0
votes
2answers
33 views

How to create a function in MySQL that will return a random bool

So far I've got this: CREATE FUNCTION IS_ROLEMEMBER (@Rolename VARCHAR(255), @Username VARCHAR(255)) RETURNS BINARY AS RETURN ( SELECT (FLOOR(RAND() * 10) % 2) AS BINARY ); But that gives me "...
0
votes
1answer
28 views

Review the Design of My RDBMS (MySQL 5.6)

I am developing a web application for conducting Phishing Awareness testing (Github Repo). I have been getting tons of feedback on CR.SE to this project. It can be referenced if you want to get a more ...
1
vote
2answers
27 views

Biggest possible double/float for a column in my MySQL database

I am developing a PHP application in which I am in need to store values bigger than 1 million, but with the flexibility of having floating points. I know the traditional float and double. I am looking ...
1
vote
1answer
113 views

Differences between Authentication Types in MySQL Users and Privileges

I noticed that when I create a new account, that there are three different Authentication Typesavailable to choose from in MySQL Users and Privilegeswindow. What I am wondering is: What is the ...
0
votes
0answers
9 views

which is better firebase or mysql in developing a android application using android studio? [on hold]

i am thinking of the database software application and which of these is better to use as a database application for android studio? and easier to connect to android studio? thanks for your answer :)
0
votes
0answers
15 views

Unable to connect to any of the specified MySQL hosts [on hold]

I have an app we use at my company, this app works well, but the problem is not in the code of my app because this works fine, so the problem is because yesterday i used a query: Update mytable set ...
0
votes
2answers
268 views

How to create function using another functions mysql

I want to create a function in MySQL using another function but I am getting an error "Explicit or implicit commit is not allowed in stored function or trigger". This is my code: DELIMITER $$ ...
1
vote
1answer
21 views

Pass a parameter from SSRS to MySQL [on hold]

Here is the query for the dataset: SELECT DISTINCT CompanyId, Company FROM User_Hierarchy_Mapping WHERE UserId = @FieldManager FieldManager is the parameter name. It's not ...
0
votes
0answers
9 views

TNS- 12533 TNS illegal ADDRESS parameters

i was trying to create DB link from oracle 11g XE to Mysql5.7 in my local following the instructions given in this link [http://www.mysqlab.net/knowledge/kb/detail/topic/oracle/id/5386] using ODBC DSN....
0
votes
1answer
18 views

TokuDB Hot Column Expansion

I need to expand a varchar field length from 255 to 4000. I am using tokudb_version: tokudb-7.5.8 running on Linux 3.16.0-60-generic #80~14.04.1-Ubuntu SMP Wed Jan 20 13:37:48 UTC 2016 x86_64 x86_64 ...
0
votes
0answers
30 views

Which is more efficient: A join or a group by?

I've got a table A with a relation to another table B, where each A row has two rows in B. I want to extract all the values from a specific column in B for each row in A. Ignoring the difference in ...
3
votes
1answer
109 views

Null instead '0000-00-00 00:00:00' in mysql 5.7

Before: mysql 5.6. One of the columns in the table is created as follows: `f_stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' When i view this field in mysql client or using DataGrip, this ...
0
votes
0answers
23 views

How to delete data which violates the DB design

I am searching for tool/utility or a way to clear database of parasite information. The database is of over 250 tables so manual fixing is not acceptable. The database was running over the years with ...
0
votes
1answer
9 views

Why do cross-database statements break replication when using filters?

For example, say you you have table A and table B on the master and on the slave you've set up replicate-do-db = A. Now if I do something like this: USE B; DROP TABLE A.sometable; Why should this ...
0
votes
1answer
342 views

Unresponsive database, sleeping processes

my mysql database suddenly became unresponsive: OperationalError: (2003, "Can't connect to MySQL server on '<IP_address>' (110)") After killing the processes: +-------+-------+---------------...
0
votes
1answer
139 views

Not all MySQL processes are killed when restarting

We have a MySQL 5.6 server running on Ubuntu 12.04 using the MySQL 5.6 PPA. The service comes up fine when rebooting the box, but whenever we restart the MySQL process via /etc/init.d/mysql restart, ...
0
votes
3answers
542 views

MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it ...
0
votes
1answer
143 views

MySQL does not generating PID file

I upgraded my OS from redhat 2.6.3 to 2.6.5 after that when I restarted my MySQL server but it not started, error message is "150320 11:53:17 [ERROR] Error message file '/usr/share/mysql/english/...
0
votes
2answers
172 views

MySQL - installed version 5.5 but mysql --version says 5.0.95

I'm running CentOS 5.2 and upgraded MySQL to 5.5. When I run mysql I get this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.5.45 MySQL ...
0
votes
1answer
17 views

MySQL. Relationships

Thanks in advance for your answer. I am trying to tune my db skills. I have 3 tables: Brand, Model and Item. All models belongs to a brand, but an item can have a brand but not a model. I have 2 ...
1
vote
3answers
3k views

MySQL Workbench not showing stored procedures that I have created

I have a simple stored procedure that I have just created. I have applied this script to my database This script shows up in the my list of stored procedure However, when I restart MySQL ...
1
vote
1answer
19 views

Should I use TINYINT for ID (PK) field in MySQL?

Should I perform this type of optimisation if I know that a table can't have more records than 10?
1
vote
1answer
18 views

MySQL. Default length for a field

Is there a list of already set standards for some common fields like: firstName, lastName, email, phone? I see most people just set varchar(300) to all fields, does it affect performance in any way?
4
votes
2answers
10k views

How to do While Loops?

How can we use while loops in MySQL? My test script: BEGIN SELECT 0 INTO @n; WHILE @n < 10 DO SELECT @n; SET @n := @n +1; END WHILE; END; But it has syntax errors. I'm ...
1
vote
1answer
114 views

Moved a MySQL Database to another server, but socket keeps pointing to localhost

I know I shouldn't bother with this, maybe I should disable the old mysql, but this is a Magento virtual appliance from Bitnami thats work as a whole (application and database on the same server). I ...
1
vote
1answer
2k views

Problems in opening mysql query browser

My mysql query browser doesn't run. I just started to work with xampp and php. So I just installed xampp in my local machine. Now I have a problem in opening mysql query browser... What I'm getting ...
1
vote
1answer
167 views

Write-lock a whole table during transaction

I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
4
votes
1answer
2k views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
1
vote
2answers
154 views

How to get MS Access to reach a Linked server on MSSQL

We have an MS Access DB that has been in place for 16 years in a Front End/BackEnd configuration. It is now running under the latest version of MS Access using ACCDB files. Since its inception, we ...
1
vote
2answers
186 views

cannot access my DB server on ubuntu

server OS: Ubuntu 14.04 MYSQL Server version: 5.5.43-0ubuntu The output of: select User, host from user is: db % db localhost In my /etc/mysql/my.cnf, I made sure that bind-address is ...