MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
35 views

How mysql order data without order by clause? [duplicate]

Is there any way to order data without order by clause in MYSQL? select * from table table name order by col1 asc.
2
votes
1answer
70 views

Have foreign key constraint and index key for the same column(s)

I am creating a composite index key for 2 columns and then adding foreign keys to its appropriate table. Do I need to create a multiple index key reference and then create separate foreign keys? My ...
1
vote
1answer
18 views

MySQL Crashing every few days

Our website hasn't launched yet so we're gaining little to no traffic at the moment. For some strange reason MySQL seems to crash every 2-3 days unexpectedly. Here's the SQL log of the crash ...
0
votes
2answers
61 views

Query syntax is checked at java level or Database Level?

If i used normal JDBC Connection for accessing database of Mysql, The syntax of query is checked at java level or Mysql level. Example String Query="Select * fromEMP" above wrong query syntax throw ...
0
votes
0answers
30 views

Get last updated date and group by in mysql

trans_id | item_id | price | last_updated 01 |001 | 100 | 2015-01-01 02 |002 | 200 | 2015-01-02 03 |001 | 150 | 2015-01-03 03 |003 | 200 | ...
0
votes
2answers
23 views

MySQL security group view permission query

I have a table of records that are only viewable by users with the correct group permission. The records may be viewable by multiple groups. Users may also be in multiple groups. record ...
1
vote
1answer
20 views

Completely removing InnoDb tables

To cut a long story short, I deleted several .idb files and then tried to re-import them from a .sql file. I'd imagine there is some relic of the old tables somewhere. Does anyone know what to remove ...
1
vote
2answers
60 views

How to prevent a trigger from running on certain statements?

I'm looking for an answer that satisfies my need on both SQL Server and MySQL. I have a small, custom audit table that I will be writing to through triggers on various tables. There are situations ...
1
vote
3answers
105 views

Are TEXT fields stored separately from the row in MySQL-ISAM?

We have a "notes" table and some of the queries are getting very slow even though we're not searching on the note contents. We're wondering if moving the note content into a separate table would speed ...
0
votes
1answer
38 views

MySQL Database Keeps Crashing

Our mysql instance is keep crashing after every 5-10 minutes very frequently. We have around 600 databases on that instance. I think 600 is not a big number of databases given the size of databases ...
0
votes
1answer
25 views

Optimize slave .ibd tables, where slave has innodb_file_per_table and master does not

We are trying to reclaim file space on our replication server. The space we want to reduce are .ibd files on the slave. We have a simple Master -> Slave (read only) replication setup, for some reason, ...
2
votes
1answer
29 views

Insert into a DB that was selected via other DB?

I have a table that stores what database should I use for a given device( specified by its IMEI), like this: imeiDB( IMEI VARCHAR(15), db VARCHAR(100)); Let's say I have the database ...
0
votes
0answers
18 views

Mysql database name

My Mysql version is Server version: 5.6.19-0ubuntu0.14.04.1 (Ubuntu) I have two databases, with a test table in each database. I can run the following select statement in one db, but not the other. ...
-1
votes
0answers
11 views

Does version MySQl 5.6.24 not support the none object orientated code of previous versions? [migrated]

I have the following code that works fine in other versions of Mysql but not in 5.6.24. Its on a testing server and not live. It works fine on the on-line server, which uses a version lower. 5. $con ...
0
votes
1answer
28 views

Are there any risks on using 'single-transaction' and 'extended-insert' on mysqldump?

I'm new in db administration and I've looked for a faster import of a mysqldump. I had a db with about 1.1 million rows and mostly InnoDB tables and wanted this to import on a managed server. Before ...
1
vote
1answer
51 views

Fast query for value between 2 fields

I'm asking something very similar to this question. In the two options provided, one is SELECT * FROM your_table WHERE 1265631252 BETWEEN `IP FROM` AND `IP TO`; which another poster said is slower ...
1
vote
1answer
65 views

Is it ok to increase `innodb_stats_sample_pages` up to large number?

MySQL sometimes generates bad execution plan irregularly. 2 years age, I have increased innodb_stats_sample_pages(default:8) up to 64 and recently changed into 128 because Data Volumn has been ...
-1
votes
0answers
37 views

Get available first booking date in my system

I am creating an application which connects users with workers. Users are those who have some requests to be done and Workers are those who works for the users and get paid based on the working time. ...
0
votes
2answers
32 views

How to grant access permision for multiple database and multiple tables to a user in MySQL?

I have two databases WORLD,TEST each one contain 3 Tables.I also created a new User test_grant now i want to give SELECT,DELETE,UPDATE permission to test_grant user.But here i want to give two tables ...
0
votes
0answers
28 views

subquery in set clause retrieving different values

I want to update the column "kstplcode" using different values coming from column warehouse except the value "main" with their corresponding delivery numbers. I've tried this code but the set clause ...
0
votes
0answers
44 views

Column based replication in mysql 5.6

I want to do column based replication which means: In my server, I have table T1 in which columns are A, B, C, D, E I want to replicate this table on different server in mysql 5.6 only, but with ...
0
votes
1answer
50 views

How do I recreate a table that was deleted during an ALTER statement (errno: -1)?

For the third time in the past 2-3 weeks I've gotten the following message during an ALTER statement: Error 2013: Lost connection to MySQL server during query SQL Statement: ALTER TABLE ...
1
vote
2answers
51 views

Master/Master/Slave Replication

Just trying to get some information on the best route to setup replication for this DB I've got. I'm using MySQL 5.1 I found this and it seems like the idea we have in mind. We're looking to have ...
0
votes
1answer
24 views

Unix timestamp conversion

How can I output my Unix timestamps to human readable GMT? SELECT category ,fullname ,shortname ,idnumber ,startdate ,visible ,timecreated ...
1
vote
1answer
41 views

select members with the longest running absence streak

I have a database containing a table that stores attendance data, and its columns are member_id and datetime_attended. And another table storing members info, which is made up by the columns id and ...
3
votes
2answers
139 views

ALTER TABLE … ROW_FORMAT=Compressed times out

I'm trying to change a table with the command: ALTER TABLE ... ROW_FORMAT=Compressed This fails after 15 minutes, the log says: InnoDB: Error: semaphore wait has lasted > 600 seconds InnoDB: We ...
0
votes
1answer
21 views

MySQL replicate-rewrite-db ALTER TABLE not rewritten

I have a production database called "master" that I want to replicate on another server under another name (call it "slave"). All worked fine until an ALTER TABLE statement was performed on the ...
0
votes
1answer
50 views

MySQL sudden restart

I have a strange issue with my MySQL server. It suddenly restarts. It's a rare but very big problem for me. This server is running as Master in Master-Slave replication. I'm using MySQL ...
0
votes
0answers
22 views

How to reorder again' auto increment column' after deleting some rows [duplicate]

I have a MySQL db table, and first column is for id which has auto_increment. I want the first column be reordered after deleting some rows. iIs there any way to do that? id name 1 alex 2 ...
0
votes
1answer
48 views

MySQL Aborted connection warning messages

I am using "5.6.22-log MySQL Community Server (GPL)" MySQL server. All the tables in my servers are using Innodb storage Engine. Below is the configuration of my server: [mysqld] ...
1
vote
1answer
74 views

How to structure this data? (Many columns currently.)

Working in MariaDB, we're trying to figure out how to store this data. All of our other tables have been normalized and are working nicely, but this has me stumped. We have a number of objects and ...
-1
votes
1answer
29 views

Recreating a MySQL database if you have the .frm, .myd and .myi records

Centos 6.5 MySQL 5.5? WHM/CPanel WHM/Cpanel seems to be creating mysql directories for each account in the directory /var/lib/mysql For one of these account, let's call it test, I have the ...
1
vote
3answers
92 views

Index optimization and cardinality

I'm by no means a DBA expert. Rather, a Magento developer having to troubleshoot some slow queries. I'll jump right in -- here's my attempt to EXPLAIN the troublesome query: EXPLAIN SELECT ...
-1
votes
0answers
15 views

Where are my MySQL data files

OS: Linux - Centos 6.5 WHM/CPanel I looked in /etc/my.cnf, but I could not find a reference to where the data directory might be. Any suggestions on where else I may look for the MySQL data ...
0
votes
1answer
27 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...
2
votes
3answers
104 views

“No database selected” or “Table 'DB.table' doesn't exist” failures in pt-upgrade run where queries from multiple DBs were collected with tcpdump

I collected queries on our Master using the following tcpdump command: tcpdump -i any -s 65535 -x -n -nn -q -tttt 'port 3306' > tcpdump.tcp The queries are executed amongst several databases. I ...
0
votes
1answer
65 views

How to store iterations of similar datasets

I have a dataset that is stored across 20 or so tables, most of which have a few million rows. This data will be generated fresh each month, and compared to past datasets. I'm pretty new to ...
0
votes
0answers
16 views

MySQL(/PHP) - Selecting a list of all children [duplicate]

We have a store with a hierarchical set of categories. The table looks like this, where parent_id is the categories_id of the parent category (0 is toplevel): mysql> describe categories; ...
1
vote
2answers
38 views

MySQL connection timeout on connection spike

I have a little bit difficult problem, whenever a spike of connection occurs our MySQL server, connecting from all sources to the MySQL is practically impossible, the max_connections setting is not ...
1
vote
1answer
55 views

Why I cannot execute this auto generated script with mysql workbench

-- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, ...
0
votes
1answer
46 views

Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

CRUD replicates just fine. But we noticed that DB structure altering commands such as ALTER TABLE and/or adding/updating columns to a table and even creating new ones do not replicate to the slave at ...
2
votes
2answers
48 views

How to have one Auto Incremented id for two separate tables in mysql?

I have 2 different tables, each having its own id column. I need that if one table is using a specific id, the other table will not use it. This is needed because the object in both tables will later ...
0
votes
0answers
36 views

Index pages in innodb file when innodb_file_per_table = ON

I have MySQL 5.5, and innodb_file_per_table was always in config file. After some time of using MySQL DB was produced big ibdata1 file, but i don't know how explain why this file is so big, i try to ...
0
votes
0answers
32 views

Optimize data handling in Symfony2 that cause out of memory

I am working on Symfony (const version="2.5") using Xampp with PHP version of 5.5.19 . I want to optimize my data handling because it cause out of memory error. I think,i query a lot of rows in ...
1
vote
2answers
73 views

Optimize table by removing large field

I have a MySQL table of articles. It's growing rapidly, and over 50% of the size on disk is accounted for by the article body field, as you might expect. There are no indexes on the body, but 8/16 ...
0
votes
0answers
47 views

PostgreSQL -> MySQL Migration with MySQLWorkbench

I'm trying to migrate data from a PostgreSQL DB to a MySQL DB (it's for permissions in the VPC, so please nobody link me the 'choose-something-else'). I'm getting this error from MySQL Workbench when ...
1
vote
1answer
46 views

Seemingly simple query…excruciating response time

I am in the process of running what seems to be a simple query and have yet to receive a result set. The purpose of the query is to obtain a list of all records within a table that share the same ...
-1
votes
1answer
25 views

how to use trigger to set value based on query result

would appreciate any example on how to set value on the added row based on query results from different table. something like this delimiter | CREATE TRIGGER trigger BEFORE INSERT ON table1 FOR ...
1
vote
1answer
29 views

mysql fulltext - exact match or nothing

I have mysql table with column tags, and would like to search exact match. For this data: proj7 invoice delivery proj7 exchange and this query MATCH(tags) AGAINST ('+proj7 +(invoice report)') ...
0
votes
1answer
42 views

Mysql REPLACE INTO query for Multiple rows insertion

I'm trying to add multiple rows using 'replace into' , but its updating one row. REPLACE INTO user_balance (user_id,acc_type,currency,balance,enable_deposit, ...