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
2answers
68 views

What happens when I reach the last id with MySQL InnoDB?

So perhaps I don't know the technical name of the 'id' column which has autoincrement so if a similar question exists, I cannot find it. I was wondering about the limit of the number of rows in an ...
1
vote
1answer
30 views

Incorrect key file for table error. How can I change location of MySQL's temp folder to another part of drive with more disk space?

I am using MySQL with Sequel Pro on a Mac Pro OSX Mavericks and am trying to run the code for a stored procedure in order to add two new columns to a table. The procedure does math operations on ...
0
votes
1answer
35 views

Windows MySQL 5.5 Cannot connect to localhost: ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

I can ping successfully from my Windows command line to localhost and to 127.0.0.1, but attempts to connect with mysql.exe fail... I found similar errors but their suggested solutions don't work/...
1
vote
1answer
33 views

DELAYED User at MySQL

mysql> show processlist; | Id | User | Host | db | Command | Time | State | Info || 27098 | DELAYED | localhost | ...
5
votes
2answers
56 views

enable query log for single database

I am having many database schema in mysql 5.6 server, now the problem over here is that I want to catch the queries for one schema only. I can not enable query log for entire server as one of my ...
1
vote
3answers
43 views

COUNTING results per row in self referenced table

I have a table called Clients. And at this table, I have an idFather to know who added this row to the table. Sample: id | user | idFather | createdAt ---+---------+----------+----------- 01 | ...
0
votes
0answers
21 views

Row replication and statement replication in the same topology?

Is it possible to set one master server replicate to one or more slaves using row-based replication and then one of the previous slaves act as master and replicate to another slave using statement ...
1
vote
1answer
45 views

How can I count some rows before inserting?

Here is my table structure: // QandA +----+----------------------------------------+------+---------+-----------+------------+ | Id | body | type | related | ...
1
vote
4answers
43 views

20 mysqld processes using only 1 port?

These are from Aliyun Cloud DB Servers,20 mysqld processes using one 3306 port Has anyone seen this before ? Can anybody explain how to do that ? Are there any advantages to this ?
1
vote
0answers
12 views

I cannot fetch the value of a mysql alias [migrated]

I have the following code: $query = " SELECT distance, outcodepostcodes.lat, outcodepostcodes.lng FROM ( SELECT $table.*, outcodepostcodes.lat, outcodepostcodes.lng , outcodepostcodes.lat*3 AS ...
0
votes
0answers
38 views

how to get the data from table

Before this query is run an activity name (e.g. ‘Art’) should be assigned to a variable. The query should then output a list of the children registered for the specified activity. The output should be ...
0
votes
0answers
40 views

How do I restore my databases after upgrading to mariadb?

I installed mariadb on my Ubuntu 16.04 server without any preparation. While installing mariadb-server, it moved all of my existing mysql databases to /var/lib/mysql-5.7 and created a new /var/lib/...
0
votes
1answer
50 views

how to judge sql performance through these two explain?

If I give you 2 explain results from MySQL,how do you know which query is better? the first sql explain *************************** 1. row *************************** id: 1 select_type: ...
0
votes
1answer
46 views

How to Delete rows based on a combination of columns having a certain count

I'm having trouble writing a query for the table below. CREATE TABLE `c_example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c_id` int(11) DEFAULT NULL, `guid` varchar(32) DEFAULT NULL, `...
1
vote
2answers
38 views

Why is table opened every time a query is executed against it?

I execute this SQL command twice and profile select * from sbtest.sbtest2 limit 100; The first time profile result is mysql> show profile for query 1; +----------------------+----------+ | ...
0
votes
2answers
41 views

What is effective way to delete duplicate records based on multiple columns in MySQL?

I have a table named users having approx 70 Million records, out of which I guess 10 Million is duplicate. How to remove those 10 Million records keeping one copy of that. Two records are said ...
2
votes
1answer
40 views

Trying to restore SQL file from mysqldump, results in syntax error

I ran the following sqldump call in a MySQL 5.7 instance (Windows OS): mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 --routines -A > ~/dump.sql I ...
1
vote
1answer
25 views

MYSQL TABLESPACE = innodb_file_per_table GIVING ERROR 1030 (HY000): Got error 168 from storage engine

I have tried this statement to place a tables in different disks. Operating system is Linux Oracle 7, I've changed permissions in the directory with chmod -vR 777, to have total access to the disk ...
1
vote
1answer
64 views

All queries are so slow on mysql NDB cluster

Here is my situation: I got over 10G of web logs (around 30 million rows) and I mainly search it with various conditions. e.g.: select * from tbl where ip = '123.123.12.3' and agent = '...
1
vote
1answer
24 views

Changing number of rows when importing table [duplicate]

I'm currently importing a table with about 17 million rows in MySQL. What catches my attention is, the number of row seems to be fluctuate. I used the command show table status while my importing ...
0
votes
1answer
35 views

Determining whether a MySQL column is ever used

How can I determine whether a particular column in a MySQL table is ever SELECTed or explicitly UPDATEd or INSERTed or referenced in a WHERE clause? It'd be nice if I could determine the db user who ...
1
vote
1answer
31 views

Mysql will no longer start after DB import and password change

I'm running Mysql5.6 on RHEL 7. I had managed to get the mysql service up and running, and imported a database into it with no issues. However, after attempting to reset a user's password, the ...
0
votes
1answer
60 views

How do I remove first line from text and only the first?

I have a tables "Table1" in this table I have a Text NOT null field - "value". the value field is populated like so: value: 1.2.3.4 [cookie] : butter - 1 cup. 1.2.3.4 [cookie] : flour - 2 ...
0
votes
1answer
45 views

MySQL .ibd file is too big

I got a memory issue while in linux. When i checked memory allocation by using df -h i found a path /dev/sda1 and it is used 100%. When i dig more i found that /var/lib/mysql file ate almost all ...
0
votes
0answers
37 views

Error creating foreign key on (check data types)

Very weird problem. I need help, I can't figure out and I don't know how to trace the specific errors in phpmyadmin. I have 2 tables and I want to create FK between them. The first table is keywords:...
0
votes
0answers
31 views

Replication after Master down in Master-Master schema in MySql

I have set up a Master to Master replication scheme. I have 2 Master server: server A and server B. Server A is configured as follow. my.ini server-id = 1 binlog_do_db = example log-bin=mysql-...
0
votes
1answer
42 views

Adding where clause in LEFT OUTER JOIN giving unexpected result

SELECT abc_discussions.id as id, abc_discussions.subject as subject, abc_todo_lists.title as todo_list_title, abc_todos.title as todo_title, abc_files.title as file_title, ...
0
votes
2answers
31 views

Can i Make Index for all columns(No write operations)?

Few days I just learned indexes.I my New app(running on node js) There will no write operation.And my server has 500mb of ram.Can I set Index to all of My column.I almost have 500K rows.My query is ...
0
votes
0answers
12 views

How do display the vertical format like MYSQL in the console, but for H2 Database?

I am administering a H2 database and I'm coming from the mysql world. I usually use the console to do quick queries in the mysql console. When there is alot of data returned, I use the \G option for ...
0
votes
1answer
43 views

Really slow join, limit and order_by query

I've got these two tables: books (about 2 million records in total) Containing a unique ID (isbn) aswell as an integer value representing popularity (index, but not unique). category_pointers (also ...
0
votes
1answer
55 views

I Need Help With My Database Design for Product Management

I'm designing a MySQL database and the front end for my work. The database will store all of the products that we sell on our marketplaces and their relevant data. We will be able to lookup products ...
1
vote
0answers
25 views

Question about MySQL triggers performance [duplicate]

I currently have a database that stores some transactions for several users, for example purchasing or selling products is stored as 1 entry in the transactions table. It can also store profits made ...
0
votes
0answers
21 views

With regards to an ms access to sql migration

I am aware there is lot of information on the web about how to migrate from an access database to a SQL one. However most of the tutorials are about moving from MS Access to MS SQL Server, what If I ...
1
vote
0answers
17 views

Mysql to MS SQL Migration and synchronization

Database migration from MySQL to MS SQL using SSMA tool is very nifty, however, I have one question around this. I have 2 MySQL Db's want to migrate to MS SQL, one sizes around 850 GB and another one ...
1
vote
1answer
32 views

Temporary file write failure on MySQL

When running an ALTER TABLE ... ADD COLUMN ... I get: Error Code : 1878 Message : Temporary file write failure. And on log_error (/DATA/mysql/.err) 2016-06-29 17:27:01 7f11f08a7700 InnoDB: ...
3
votes
1answer
39 views

FULLTEXT Search (InnoDB) - Am I doing it right?

Goal: I wish to do autocomplete using ~130K global city names. Setup: I have created a 'cities' table as follows: CREATE TABLE IF NOT EXISTS cities ( id INT(11) NOT NULL AUTO_INCREMENT, ...
0
votes
1answer
23 views

Is there a UI tool for moving data from two different tables?

The schemas in the two databases are different. Eg. Database 1 / users - id - name - phone Database 2 / users - id - first_name - mobile Is there a tool that lets me visually create the statements ...
1
vote
0answers
34 views

Do not want to allow Queries to take more than x minutes?

Do not want to allow Queries to take more than x minutes ! What System variable can I set to automatically terminate it.
1
vote
3answers
50 views

How do I prevent multiple row insert?

I set up a playground MySQL database for users and I'd like to prevent multiple row (large) inserts. That is, if there will be over N rows after INSERT operation, then raise an error and do not ...
1
vote
0answers
27 views

Retrieve data from OLTP MySQL to SQL Server DWH using SSIS

I'm running a BI solution currently reading data from an OLTP SQL Server 2008 to a SQL Server 2012 data warehouse. The ETL phase is implemented with SSIS and runs twice a day. This phase relies on a ...
0
votes
0answers
18 views

mysql needs a start after turning system off

Hii I am facing severe installation issues after installing Ubuntu 16.04. Mysql needs a start everytime system is turned ON. I have removed every available package of mysql 5.7 but after installation ...
0
votes
0answers
17 views

Tool like `pt-table-sync` for Amazon RDS

I am looking for a tool / script that does the same functionality as pt-table-sync in the Percona Toolkit, (i.e. sync two tables that are explicitly NOT in a master-slave setup) but is compatible with ...
1
vote
1answer
34 views

Sync two MySQL servers data, while changing the Engine in the slave

I'm running a MySQL server 5.5.44, and I'd need to sync its data to a new server running MySQL 5.7.13 using master-slave. The idea is to bring both DBs synced until we migrate to the second, newer ...
0
votes
1answer
22 views

Overwrote database with old mysql dump. How can I restore it with a more recent backup

Have a server with multiple databases. Imported wrong .sql from over the top of existing database. I Have a rsync copy of the up to date files for that database. What do I need to do to just ...
1
vote
0answers
29 views

MySQL max connections [closed]

MySQL keeps crashing, but after futher research, I found this error SQLSTATE[08004] [1040] Too many connections but I have this line #max_connections = 100 commented in my.cnf.
2
votes
0answers
28 views

Select rows based on latest date

This are the tables: Material (Code, ID) Itemtrans (IteID, FtrID, Tradecode) Fintrade (Ftrdate, ID) Material.ID=Itemtrans.IteID Itemtrans.FtrID=Fintrade.ID I want to list the last record on ...
-2
votes
2answers
82 views

SQL Text data type deprecated?

I have read that Microsoft SQL Server has deprecated the text data type in favour of varchar(max). PostGreSQL already allows varchar (without a length) as an alternative for text. The question is, ...
3
votes
1answer
55 views

Easiest way to duplicate rows

The closest I can find to what I want to do is here, but my inexperience gets me lost. Basically, I want to copy a number of records, change one column and insert them back into the same table (so its ...
-4
votes
1answer
74 views

How does indexes work in a database? [closed]

If I have a list of name that are all different and I have to find a specific name,how could indexes help.thi research?How does index work in a database?I have read other discussion but I haven't ...
3
votes
1answer
31 views

Issue with running multiple mysql commands in a bash for loop

I try to restore some .sql backups using a shell script with a for loop. I run by bash command.sh 'password' using the command.sh below. However, when I typed it out, it didn't run and instead, mysql ...