MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
37 views

MySQL Dynamic columns duplicate value workaround

As the title suggests i have the following code which transposes certain row values into field names, however the problem im having is that the values "closed" & "reopened" may appear in several ...
0
votes
0answers
28 views

Increasing limit by 1 results increases execution time from less than a second to infinite

Using a simple query like: select * from <table> limit 5; will return results in a fraction of a second, which is expected. I can increase the limit up to 49, and the results will still be ...
0
votes
1answer
24 views

How to get something written to MySQL error log without restart

I want to cause the MySQL 5.5 error log to be written to at will without a service stop or start. Note that I do not care what gets written to the error log, as long as something gets written. I ...
0
votes
1answer
25 views

How to report durations of 100+ queries in MySQL?

I have list of 142 SQL queries in an .sql file. At the beginning of this file I put SET profiling = 1;. At the end SHOW PROFILES;. I execute it with: mysql -u root live_copy < ...
0
votes
0answers
28 views

MySQL Query Causing Massive Slowdown Over Time

I've been experiencing massive slowdown on my server after launching a new website. Using MONyog I can see that after an apache/mysql restart, the queries/second starts out nice and low for around 10 ...
0
votes
1answer
20 views

What should be the structure for a person-to-person store database

I am going to create a person-to-person store with Laravel, a website whose users can share and sell their product or their mobile, TV, Computer, Shoes, Shirts, Books ,Cars, Bags, Animal etc. I am ...
6
votes
2answers
59 views

Prevent MySql from mangling queries by casting string to int

On one of my tables I have an auto incrementing ID field. It also has another field (richter_code) with a unique constraint but, over the seasons, this field can change - that's why I'm not using it ...
0
votes
0answers
12 views

Performance/Maintenance Implications of a Write Heavy Table In An Otherwise Normal “CRUD” Database

We have a small-ish (<100,000 rows) MySQL database (on Amazon RDS). Its supporting a fairly simple read heavy CRUD web application. Currently we're building out a web analytics system. It will ...
1
vote
1answer
30 views

Database query: join tags with exact matches or at least one

Given I have a query like: SELECT count(mm.id) AS c FROM merchant_mobile mm INNER JOIN mobile m ON mm.mobile_id = m.id LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id) INNER ...
0
votes
0answers
61 views

MySql: Get count of incremented items by multiple conditions

Here's the create and insert script, it's a calls record data table. This is a glimpse of it: | call_id | customer | company | call_start | ...
-1
votes
1answer
40 views

converting mysql datebase

I have a question about converting mysql databases, I doing now new site, and want to use my old database, which is in latin_swedish_1 colacolation I want to convert this old database into ...
0
votes
0answers
11 views

Database systems with users and sub users accessing the data

I am making a database for school student maintenance. Below is the basic description. There are schools. One school has many staff (i.e: Principles,Teachers, Section Head, Office Staff etc) These ...
0
votes
1answer
37 views

Help with MySQL my.cnf config

So I'm running MySQL on a high availability cluster with the MySQL data directory on a GlusterFS. I want to: - Disable the query cache (--query-cache-size=0) - Switch on external locking ...
3
votes
1answer
54 views

what does !! as an operator in mysql?

I tried to use the exclamation mark as a not operator in a query SELECT !0, !!0, !!!0, !1, !!1, !!!1, !2, !!2, !!!2; | !0 | !!0 | !!!0 | !1 | !!1 | !!!1 | !2 | !!2 | !!!2 | ...
0
votes
1answer
58 views

Transpose MySQL rows as column names - DYNAMIC

My goal is to be able to calculate the time difference between the ticket timestamp being created and closed and if reopened Add time from being reopened to closed again etc.. I have an existing mySQL ...
1
vote
1answer
18 views

Mariadb refusing to read values on the my.cnf file

today i was optimizing my mariadb since my website was running too slow My machine is a Centos 7 , 4 gbs ram 3 cpu i runned a script called mysql_tuner.pl and the results were: -- MYSQL ...
1
vote
2answers
64 views

MySQL to SQL Server - Unixtime being major problem

I'm trying to convert this query to SQL Server. It already has a lot of updates. Still, I don't know what to do from here ( i'm not the best when it comes to programming in MySQL ) SELECT ...
7
votes
2answers
445 views

ROLLBACK doesn't work after INSERT INTO newly created destination table

I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers). Before inserting contents of CSV-file into the mysql table I am first backing up the original customers ...
0
votes
1answer
29 views

How to reference categories table in products table?

I am using modified preorder traversal algorithm to store product categories in database (like image shown above). But i don't understand how to reference it in my products table. Here is my ...
1
vote
0answers
11 views

Custom cycling order in MySQL

Having following Foo table: id | name | group ===|=========|====== 1 | "John" | 1 2 | "Paul" | 1 3 | "Marry" | 1 4 | "Josh" | 1 5 | "Mike" | 2 6 | "Kevin" | 2 7 | "Ramon" | 2 8 | "Kim" ...
0
votes
1answer
28 views

How bigger page size effect at performance?

I know in case of Cluster index bigger page size improve peformance for range and multipoint query. Does bigger page size can effect to degrade performance when we have non cluster index .Because ...
0
votes
0answers
16 views

MySQL View: Transform rows in columns and update

i would like to show data from this mysql-table: ID | Channel_ID | Value | Time | 01 | 15 | 10,5 | 364 | 02 | 3 | 4,2 | 365 | 03 | 3 | 5,3 | 394 | in an ...
0
votes
0answers
14 views

How I do a complete backup restore of MySQL Schema with views and functions?

I am trying to backup a single schema on a Windows server and restore it to an Ubuntu 12.04 Linux server running MySQL 5.5.4. The schema includes views and functions. When I used the MySQL Workbench ...
0
votes
1answer
29 views

Modifying a result set in a Stored Procedure, then returning that modified set

I am currently in the process of designing my database for a mobile/web application. When I look at competing apps I see a massive flaw in how they handle user locations. Due to the nature of the ...
0
votes
0answers
25 views

Designing a correct table structure

We are trying to save the Family History of a particular foreign job applicant. Below are the details we have to save. Familiy Member: Father|Mother|1st Brother| 2nd Brother| 1st Sister| etc etc ...
-2
votes
0answers
23 views

mysql some partition has no rows some has

currently I have a table created by sql below CREATE TABLE `tid` ( `ukey` char(32) NOT NULL, `tid` varchar(40) NOT NULL, `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ...
1
vote
0answers
47 views

Is >58 hours to create an index normal? Should I cancel and try again?

I have an InnoDB table with ~43,964,557 rows containing 20.5 GiB of data (not counting existing indexes). mysql> show table status like 'backpack_items'; ...
1
vote
1answer
15 views

enable logging of password-like patterns in mysql client history

From MySQL 5.6.8 onwards, statements containing the pattern *PASSWORD* or *IDENTIFIED* are not logged in the mysql command line client history. I know I can add to this list with --histignore or ...
1
vote
0answers
19 views

MySQL InnoDB Configuration with 60Gb of Memory

I'm running a large MySQL Database, in the range of 30Gb in size. The MySQL 5.5 server is running on a dedicated server with 60GB of RAM, with no other processes installed apart from monitoring tools, ...
1
vote
0answers
60 views

Why are tiny Innodb row updates causing so much disk IO?

This is a followup question to my earlier How can I determine which Innodb table is being written? Using the accepted answer from the above-referenced question I was able to determine which (two) of ...
2
votes
2answers
77 views

How to auto assign ordinal ranking in mysql with as minimal queries for new rows?

+----+---------+---------+-----------------+ | id | view_id | user_id | ordinal_ranking | +----+---------+---------+-----------------+ | 1 | 10 | 1 | 1 | | 2 | 10 | ...
-4
votes
0answers
33 views

SELECT * FROM `Table` query is returning rows not ordered by primary key [duplicate]

First question here and I hope it is the right place. I'm using MySQL and I've always expected a SELECT * FROM 'Table' query to return rows in order of primary key, (BTW those should be back-ticks ...
0
votes
0answers
22 views

mysqlcheck table is marked as crashed and should be repaired

When restarting mysql service, I got the ff error message 150117 18:49:52 [ERROR] /usr/sbin/mysqld: Table 'xxxx' is marked as crashed and should be repaired 150117 18:49:52 [ERROR] /usr/sbin/mysqld: ...
0
votes
1answer
39 views

What's wrong with my SQL query?

The query I currently am using is this: SELECT locations.id AS locations_id, locations.community AS locations_community, locations.lat AS locations_lat, locations.`long` AS ...
0
votes
1answer
28 views

Lowering MySQL Memory Usage without Restart

We are using Percona Server 5.5.35-rel33.0-611.precise with InnoDB in our production server. Recently Mysql memory went high and it did't drop from there. We are afraid whether it would result in OOM ...
2
votes
2answers
103 views

find all numbers that appear at least three times consecutively

This is a question from leetcode online judge I tried this SELECT Num FROM Logs GROUP BY Num HAVING COUNT (Num) >1 but got runtime error!! Anyhelp would be appreciated
2
votes
1answer
44 views

How can I determine which Innodb table is being written?

I am running Mysql 5.5. I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The ...
5
votes
1answer
550 views

How this can return more than 1 result?

How this query can return more than 1 row? SELECT username FROM phpbb_users WHERE user_id = (SELECT (FLOOR(RAND() * (5050 - 50 + 1)) + 50)); Table meta: CREATE TABLE `phpbb_users` ( `user_id` ...
10
votes
2answers
115 views

Find total duration of each consecutive series of rows

MySQL Version The code will run in MySQL 5.5 Background I have a table like the following one CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT , patient_id INT NOT NULL , bed_id INT NOT NULL , ...
0
votes
1answer
27 views

Cannot shutdown a shutdown connection MySQL Enterprise Monitor

We are facing below errors on MySQL Enterprise Monitor.. While deleting a connection from MySQL Instances it is displaying following error messages. Also it is not deleted event it displays success ...
0
votes
0answers
19 views

Default engine innodb problem

My MYSQL Version is 5.6.12 so the default engine should be InnoDB and is the same. I created few functions and procedures using MYSQL Query Browser but it went and got saved in a database called ...
0
votes
1answer
23 views

How to determine column character set used on creation?

I have this SQL creation statement: CREATE TABLE `testdb`.`new_table` ( `id` INT NOT NULL AUTO_INCREMENT, `text_default` VARCHAR(45) NULL, `text_latin` VARCHAR(45) CHARACTER SET 'latin1' ...
0
votes
0answers
41 views

SELECT query on mySQL taking 20 seconds to execute

Many points to note: This SELECT statement is happening both on my site, terminal, and on phpmyadmin, so the problem isn't a coding problem. If you delete the 11,000,000 rows and keep 20,000 rows ...
-1
votes
0answers
24 views

Selecting data using mysql query and php

Assuming you a table table1 | ID | NAME | | 1 | da | | 2 | sa | | 3 | bf | | 1 | da | | 2 | sa | | 3 | ...
-1
votes
2answers
25 views

Mysql query not working

CREATE DATABASE `menu`; USE menu; DROP TABLE IF EXISTS `fish`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `fish` ( `ID` int(11) NOT NULL ...
1
vote
1answer
53 views

Is it possible to emulate the WITH command in MySQL 5.1.73? [closed]

I have the following query: SELECT COUNT(*) AS `count` FROM agreements WHERE start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y') AND start_date < ...
2
votes
1answer
46 views

ibdata1 increases when firing update queries [duplicate]

I have read a lot of posts which explain that the only solution to stop the growth of an ever increasing ibdata1 file is to: Take a dump of all databases Set innodb_file_per_table in the mysqld ...
0
votes
0answers
12 views

Does pt-table-checksum work in circular replication environments?

I have 3 masters in circular replication. I run pt-table-checksum in one of the masters only. A->B->C->A The queries do get replicated the "next" server B but I don't think they are being replicated ...
0
votes
0answers
17 views

Scheduling large numbers of events in Mysql

Looking at using the Mysql event scheduler to schedule potentially quite a large number of events which could be in the 100,000's of discrete events per day (possibly more). Does anyone have any ...
0
votes
1answer
56 views

unable to insert utf8mb4 characters in mysql 5.6

recently i was facing problem in inserting emoji and other 4 bytes character in my mysql database then i found that my mysql version was 5.1 and it does not support utf8mb4 so i upgraded to mysql 5.6 ...