Tagged Questions

MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
22 views

Mysqldump throwing error 'Couldn't execute 'ROLLBACK TO SAVEPOINT sp':

Can any one please explain what is happening with our DB dumps? We are executing dump as below mysqldump -v --single-transaction --quick --lock-tables=false --skip-triggers -uXXX -pXXX ...
1
vote
2answers
51 views

return my.conf values to sane levels (total memory footprint = 220GB)

I recently took over administration responsibilities for one of our databases. The existing my.cnf has some crazy values. I should mention that one table has 1.8 billion rows of mostly unneeded ...
0
votes
0answers
36 views

Slow MySQL Query when Ordering

This query takes around 2 seconds when the order by is included, without the order it takes less than a tenth of a second. The field that is doing the sorting, qc_products.addedDate, is an index and ...
0
votes
1answer
10 views

Storing Page View Statistics

This is a rather open-ended question, but only because I'm unable to find much information on it... That being said, how would you efficiently track unique page views? From what I've read, you'd have ...
0
votes
1answer
18 views

MySQL #1005 - Can't create table 'startet' (errno: 150)

Can anyone figure out why phpMyAdmin won't create the last table? It works fine if I drop the last foreign key, but I need it in there to get my assignment approved... CREATE TABLE nasjon ( ...
0
votes
1answer
29 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 ...
1
vote
1answer
27 views

Localization: One table or multiple tables? (MySQL)

I need to be able to store string messages in various languages. I guess I need a column for each language string. For example my columns could be: ID | message_EN | message_DE | message_FR Now, I ...
0
votes
0answers
19 views

Statements in State “Copying to tmp table” disrupt performance

On a MySQL database (version 5.1.73) a Magento-Shop executes a statement (A) that uses distinct, see http://pastebin.com/A2sy45nF and therefore is "Using temporary" according to "explain". ...
0
votes
1answer
23 views

Multiple left join hangs server

i've got problem with left join query. It connect's 3 tables and calculate sales value for categories. Query: SELECT categories.name, categories.id, ...
0
votes
2answers
33 views

How can I remove only consecutive duplicate fields when the id is not consecutive in MySQL

In this table, I have a price change history. The prices are checked regularly and as you can see, sometimes the price does not change. How can I take a specific sku and delete all cases where a ...
0
votes
0answers
13 views

insert data into mysql from mssql with arabic unicode

I insert data into MSSQL with arabic unicode. I want to insert this field into MYSQL but it insert empty. In MYSQL , I choose utf8-bin , I use php for this action, I declare name , character unicode. ...
0
votes
1answer
34 views

Cannot drop databse

I am unable to either drop database, now create the database. Following is the output. How can I fix this? mysql> show databases; +--------------------+ | Database | ...
0
votes
1answer
55 views

Optimize this query

SELECT `permission_tbl`.`service_id` AS `service_id`, `permission_tbl`.`shop_id` AS `shop_id`, `permission_tbl`.`easy_id` AS `easy_id`, `permission_tbl`.`permission_type` AS ...
-1
votes
1answer
26 views

Using Select statement twice for same column of table for MySQL

I'm supposed to write a query for a database that is a little tricky for me. It has an EMPLOYEE table that has these attributes(columns): Emp_Id,Name,Age, Dept_ID, Salary It also has a DEPARTMENT ...
-2
votes
0answers
30 views

DBA MySQL experience [closed]

I am in a programming class (Python) as well as a database concepts class. I'm also taking an HTML/CSS class. I knew HTML years ago, but much has changed in 10 years. I have a good background in ...
0
votes
1answer
77 views

SQL execution 2 seconds with LIMIT parameter, without only 0.2s why?

This is my query and I need to return only 12 rows, but it takes 2 seconds with parameter LIMIT 12 while it takes only <0.2 to process all ~600 results (without LIMIT 12 or with LIMIT 1200 for ...
0
votes
1answer
30 views

How to design a query that gives count of different parameters based on different condition from a single table

I have a table books containing columns as bookid, issue_date,return_date. Once a book is issued from library an issue_date corresponding to it is made into the table while return_date can have valid ...
0
votes
1answer
22 views

SQL: return values as column table

I am running this query to get all returns for a list of tickers: select return1 from STOCK_DATA where TDATE between 20130101 and 20131231 and TICKER in (SELECT DISTINCT TICKER FROM STOCK_DATA) ...
0
votes
0answers
20 views

Please review and advise on the database schema design for a simple shopping cart [closed]

I'm designing a simple mp3 shopping cart, a customer needs to recharge their account before they can do any purchase. A song or album purchase amount is deducted on the customer's account credit. The ...
2
votes
1answer
20 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
0
votes
1answer
48 views

MySQL JOIN vs LIKE - faster selects?

Okay, so first of all let me tell a little about what I'm trying to do. Basically, during my studies I wrote a little webservice in PHP that calculates how similar movies are to each other based on ...
0
votes
1answer
18 views

Mysql pagination without offset

Let's assume I have got a table with these data: I want to order it by createdAt column DESC, and id column DESC select id, createdAt from post order by createdAt desc, id desc Now it looks like ...
0
votes
1answer
32 views

where in (sub query) vs join

just want to ask about how to optimize my query. because i feel efficiency using join instead of "where in" sub query can someone enplane the deference SELECT C.Customer_id, C.tran_id, ...
0
votes
0answers
28 views

MySQL: How to get all values in “TICKER” column`? [migrated]

I never used SQL before, I want to get all values in "TICKER" from this database: The first statement select TDATE, price, return1 from STOCK_DATA where TDATE < 20131231 and TDATE > ...
-1
votes
0answers
12 views

Template for MySQL database upgrade script

What is the equivalent of this Microsoft SQL Server upgrade script template for MySQL? I want to upgrade my database version in repeatable transactions in sequential scripts as I develop an ...
0
votes
0answers
21 views

Advice on table structure change

I am in the process of converting an Access application that stores and reports evaluations to being web-based using php and mysql. I have converted the structure and data over from Access and I have ...
-1
votes
0answers
14 views

Cpy data from one DB to another in different networks [closed]

How can I copy some rows from a MySQL DB to another MySQL DB located in different networks? I want to copy the rows of table1 WHERE col1a = 0 from DB number1 to DB number2, table2. This is what we ...
-2
votes
0answers
30 views

What is the best database system for my situation? [closed]

I've got a few coders, who program in C++/C# and PHP/Python but we right now need all the infrastructure (from you of course) and some advise on a Database System that could handle potentially ...
0
votes
0answers
27 views

How to notice data lost in innodb engine

I have a web-application installed in some desktop pc, and I experienced some troubles with mysql innodb. In case of hardware fault (disk failure) or just power fault I lost many data in my database ...
0
votes
0answers
9 views

this code is not deleting a row whose id comes from form? but running fine without any error? [migrated]

#!/usr/bin/perl use strict; my $query = new CGI; print $query->header( "Content-type: text/html; charset=utf-8" ); print <<END_HERE; <html> <head> ...
-1
votes
1answer
25 views

Update table using SELECT in WHERE clause

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON t2.t1id = t1.id LEFT JOIN table3 as t3 ON t3.t1id = t1.ID SET t1.status = 4, t2.status = 2, t3.status = 2, WHERE t2.status = 1 AND t3.StatusID = 1 AND ...
-1
votes
0answers
16 views

Does database compress data?

I have a file with 5000 rows and 10MB in size. Winrar is able to compress it to 1MB. If I store in a database, will it take 1MB or 10MB? If I retrieve it from a remote computer via c#, will it be 1MB ...
0
votes
1answer
10 views

Triggering a shell script from MySQL without any 3rd party libraries

Is it possible to call a bash script file in linux using a trigger in MySQL? I've been searching for the answer online and I ran into this articles: ...
1
vote
0answers
34 views

Handling SQL updates for production deploys

I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment. I run a pretty ...
0
votes
0answers
19 views

How alter on infobright (BRIGHTHOUSE storage engine) actually works

I have been using infobright-3.4.1 (5.1.40) for data warehousing. I know it is outdated version but there no plan to upgrade the version currently. Even though infobright is build on mysql, the way it ...
0
votes
0answers
3 views

Enable general_log in Mysql 5.0.96

I want to enable general_log in MySql 5.0.96. I have set log variable but it isn't working. [mysqld] log=/var/log/mysql.log and restart mysql with following command /etc/init.d/mysql restart But ...
0
votes
1answer
25 views

Federated table of two LEFT JOIN tables not working

For some reasons I am getting the following error when executing the below query: (What I am trying to achieve is to sync and merge two left-joined table to into one You have an error in your SQL ...
0
votes
0answers
16 views

mysql trigger help

I need to create a MySQL trigger. I don't know very well MySQL syntax but I've written something, maybe you would understand what I need, I need to insert the difference from 2 rows into a new row, ...
0
votes
1answer
30 views

(MySQL) Need an “in-place” REPLACE for adjusting historical data

I have a table with around 6,000,000 records. It is backed up every few hours. Recently a script ran that cascade updated about 2,000,000 of the older records to bad values. I have extracted those ...
1
vote
1answer
19 views

Find MySQL Patch Level

What command do you use to find the patch level of MySQL? I am currently on 5.1.73. I need to find out what patches have been applied to this database. Thanks...
0
votes
0answers
22 views

MySQL Master-Master-Slave failover

I have setup a Master-Master configuration in MySQL with one additional slave, like this: Master1 <-> Master2 <- Slave Master1 is the write master used by my application, Master2 is used ...
0
votes
1answer
53 views

Simple query is slow on 4M-rows table

I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to ...
-1
votes
1answer
32 views

List two columns - MYSQL [closed]

i have a table like this: cp1 | cp2 ----------- A | 5 A | 1 A | 1 B | 3 C | 4 and i want to create a query like this: cp1 | cp2 ----------- A | 5 A | 1 ...
0
votes
0answers
22 views

Checking for replication options

I have a master/master setup with one DB in each. DB name and tables in one server are different from the other, and few 'user' tables are being replicated with the option replicate-do-table. (It's ...
0
votes
1answer
52 views

MySQL Performances Issues

I'm in debate with my database admin. He claims that my queries I'm sending through PHP are the problem and he is stating several things I believe are completely false. To begin I have a site ...
0
votes
0answers
9 views

How can I see all the incoming queries in real time to my MariaDB 10 server on windows?

I just want to see the queries coming in to MariaDB (very similar to mysql) in real time to help debug my application. What windows frontends are there that do this sort of thing? Thanks
1
vote
1answer
32 views

Slow execution of a subquery with 0 matches

In MySQL, I have two basic tables - Posts and Followers: CREATE TABLE Posts ( id int(11) NOT NULL AUTO_INCREMENT, posted int(11) NOT NULL, body varchar(512) NOT NULL, authorId int(11) NOT ...
0
votes
1answer
26 views

Should I use data type SERIAL for table id columns?

I found out that there is a mysql datatype called SERIAL, it appears to be designed for use as an id column. However some of my peers don't think that the real data type BIGINT implied by SERIAL is ...
0
votes
1answer
43 views

MySql replication from 5.1 master to 5.6 slave keep crashing

I recently updated one of the replication slaves to mysql 5.6. After importing a recent dump from the master, I run mysql_upgrade with no errors. the database deamon started correctly with no ...
1
vote
1answer
26 views

MAX (id) function with MySQli

How can I use the following statement to return only the record with the maximum PersonID from the table? /* Create the prepared statement */ if ($stmt = $mysqli->prepare("SELECT Max(PersonID) AS ...