MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
37 views

Conceptual data model for a billing system

I am working on existing MySQL database, my task is to create a new data model for a billing system on that existing application, I have created a conceptual data model, as given below, My ...
0
votes
3answers
57 views

How MongoDB indexes differs from MySQL indexes?

I am a newbie to MongoDB. I know how Indexes works and what it's importance in MySQL. Are MongoDB indexes are the same as MySQL ?
0
votes
3answers
83 views

Database update query for split and sum in another field using mysql

I have one table test ID text sum ----------------------- 1 1_2_3 0 2 2_3_4_5 0 i want to update this table as ID text sum ...
0
votes
0answers
29 views

Which RDBMS do return better error messages?

When you have some syntactical error in a query you will be a error message like: SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server ...
4
votes
1answer
104 views

Searching 500 million records of binary data

I will inserting into a database the signatures of 500,000,000 pictures. The signatures will be generated using libpuzzle. Each signature is 338 bytes. (so 160 GB) plus a table for searching (read ...
0
votes
1answer
41 views

NVARCHAR issue when migrating from SQL Server to MySQL with MySQL migration wizard

My issue is similar to this one but and I have tried what has been suggested but I still get the error. I have set the target column from VARCHAR(5120) to TEXT (tried LONGTEXT too) but the issue still ...
0
votes
0answers
13 views

Best way to store combobox options for an EAV table

I don't know if this question belongs to StackOverflow, but here it is: I have an EAV table: mysql> describe keywords; +-------------+--------------+------+-----+---------+----------------+ | ...
2
votes
2answers
47 views

How to merge column in mysql depending on match

I have one table which looks like this in mysql column1 column2 name column3 data 1 1 foo 1 1.5 1 1 foo 2 2.5 1 1 foo 3 ...
0
votes
1answer
13 views

Temporarily compressing MySQL binlogs

I am currently importing a pretty big MySQL database dump. This takes time and during this time I need some extra disk space. Will I break something in MySQL if I temporarily gzip a couple of it's ...
1
vote
0answers
48 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
1
vote
2answers
63 views

Storing a Shipping Address Best Practice

I'm building a checkout system which needs to store the customer's shipping address in the database and am unsure the best way to go about it. Currently I have an addresses table which stores each ...
0
votes
1answer
27 views

MySQL - When Command column in Processlist shows “Prepare” or “execute”

Can someone please highlight when a "Command" shows "Prepare" or "Execute" for "Show Full Processlist" Any example which shows this would be helpful.
0
votes
1answer
20 views

Database structure for product attributes on orders

I'm making a POS and currently kind of stuck on the order proccess due to the product attributes. As of now the DB looks something like this: tbl_products tbl_attributes (attr id, name, extra ...
0
votes
0answers
18 views

MariaDB : Larger innodb_buffer_pool_size causing SQL updates to be slower

Has anyone here encountered such scenario before where and update statement took longer time to run when innodb_buffer_pool_size is bigger. I've run a few tests using the same statements on around ...
0
votes
2answers
31 views

How to use passwords to protect database data

Hello another newbie question for all the database/web experts out there.... I have read a lot about protecting passwords stored in a database using hashes, salts, encryption algorithms etc. To avoid ...
1
vote
1answer
42 views

Vacuum in postgresql and Purge in mysql

Do the vacuum operation in postgresql and purging operation in mysql perform the same tasks?
0
votes
0answers
19 views

Is US/Pacific timezone in MySQL automatically adjusting to DST?

I'd like to define a view in MySQL which will convert some system data always written in UTC to our headquarter time, which is San Francisco. If I use CONVERT_TZ(..., 'UTC', 'US/Pacific'), will it ...
0
votes
0answers
29 views

How can the performance of a query that has a condition on two fields of each row be improved?

I have an images table with approximately 5 million rows in it. For the purposes of this question lets say the table looks like this: CREATE TABLE `images` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
33 views

What's the complexity of renaming a column in MySql?

We want to rename a column in our MySql (version 5.5) database with something like this: ALTER TABLE t1 CHANGE a b INTEGER; Is this an O(N) (N=number of rows in the table) or an O(1) operation? I ...
0
votes
1answer
20 views

Immigrate to file-per-table mode in MySQL

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini ...
1
vote
2answers
31 views

Altering table Foreign Keys produces an error (errno:150) (MySQL)

This is the first database I set up so I apologize if my problem is something obvious. I'm playing around with my table design and tried to create some link tables with FKs to avoid redundant data. ...
1
vote
1answer
23 views

creating table result in errno 150

I'm trying to create the following schema, but I'm getting errors when I'm trying to create project and staffphone tables. Not sure what is it complaining about. mysql> CREATE TABLE staff( ...
2
votes
2answers
27 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...
0
votes
3answers
278 views

Difference between DROP and TRUNCATE

What does the Truncate do differently to drop? I believe it deletes all the data in the table but keeps the table name in the database, where as DROP deletes all data and the table. Is this correct ...
0
votes
0answers
17 views

How to recover data in a MySQL DB?

Log file says: Recently disk space on my VPS got filled up. Now my DB looks corrupted, MySQL error log says: 140319 01:28:57 mysqld started 140319 1:28:57 InnoDB: Started; log sequence number 0 ...
0
votes
0answers
9 views

Using mysql without --skip-grant-tables

I have some problem with my mysql server, after an upgrade to mysql5.6 faild, Once the reinstallation to mysql5.1 succeeded I was only able to see one table "information_schema" and the rest hidden ...
0
votes
2answers
59 views

How do I group the data in the following format?

Using the following table OrderDate OrderID [dates] [id] I want to display in the following format : quater name nooforders q1 10 q2 10 q3 20 I ...
0
votes
1answer
29 views

Single MySQL DB contains both push and pull data. Recommended path to replication?

We have a single deployed (MySQL) database that contains both user tables and product tables (currently in the same DB). The product tables are "pushed to production" data -- they are generated in ...
1
vote
1answer
48 views

Generate INSERT statement from SELECT query

I have two databases, in first database I have a table called zoom_city and in the second database I have a table called courrier_city. The SQL for each is below: CREATE TABLE IF NOT EXISTS ...
0
votes
0answers
26 views

MySQL: Large data slow reads

I have very large table with 17,044,833 Rows and 6.4 GB in size. I am running the simple query below and it takes like 5 seconds. Any ideas what optimizations can I do to improve the speed of this ...
2
votes
1answer
54 views

Mysql Master-Master with additional slaves

We have two datacenters with a copy of our web app (online training platform) at each. The primary datacenter receives all of the traffic at any given time and the second datacenter serves as a ...
2
votes
1answer
52 views

Find the second highest value from a subquery or derived table

I have a subquery which returns the following results. 'c003', '120000.00' 'c002', '90000.00' 'c001', '20000.00' 'c005', '8000.00' 'c004', '5000.00' I want to find the second highest value from ...
0
votes
1answer
55 views

MySQL max_connection value

Questions I got a 12 GB machine with apache & myqsl, i have may apache max_client set to 150 will this cause some bottlenecks with the 50 max_connection in mysql? What value should I set mysql ...
0
votes
0answers
19 views

SQL Error while restoring big database in Mysql

I have successfully created a MYSQL dump on RHEL 6 machine. The dump file is having 40 GB. When I try to restore it in an UBUNTU machine I got this error.
1
vote
1answer
45 views

Migrate from innodb_file_per_table to off in MySQL

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large ...
0
votes
2answers
26 views

MySQL Index for searching for NULL values in text fields

I need to do the simple query SELECT id FROM table WHERE blob_field IS NULL. blob_field is a longblob. If I create an index on blob_field with prefix length 1, will this be sufficient to use for ...
0
votes
2answers
48 views

How to autoincrement Prod_id based on Product_Name using trigger in mysql

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented) i need to increment Prod_Id based on the product name field and prefix field (Input will be ...
0
votes
1answer
36 views

make MariaDB Auto-Increment-Increment dynamic

In a certain replication setup I have created multi-master replication in STAR topology using MariaDB. I have an unknown number of 'client-masters', so I have set the auto_increment_increment to 30 ...
0
votes
0answers
53 views

Opimizing Mysql

Below is a Snapshot of my Server info. And the results of mysqltuner.pl Some help with commands would be appreciated. I'm new to this and have gotten pretty far, but I'm having issue with Mysql ...
0
votes
1answer
70 views

How to find the same salaries and department names?

Write a query to display last name ,department number,and salary of any employee whose department number and salary both match the department number and salary of any employee using the following ...
0
votes
1answer
32 views

Need to find the employees who have switched jobs atleast twice

I am trying the following query on the tables which are described below: SELECT * FROM job_history INNER JOIN employees ON job_history.employee_id = employees.employee_id WHERE employee_ID IN ...
0
votes
2answers
32 views

How to join two result sets from same table with different conditions on same column?

select * from unit_of_measure; +--------+-----------+--------+------------+--------------+-----------------+-------------+---------------+------------------+ | uom_id | uom_name | symbol | created_by ...
0
votes
1answer
39 views

How do I solve this problem of a correlated subquery

We have an employee table with the following attributes : employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id Display the manager ...
0
votes
1answer
38 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
2
votes
2answers
32 views

Migrating data from a boolean column to a varchar column

My table currently has a boolean column, let's call it the "important" column, reflecting whether a record is urgent or not. I want to transition to using a varchar column that reflects a record's ...
0
votes
2answers
69 views

Inserting 60 000 rows taking too long

I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting. And after a while it says that maximum timeout is over. Shouldn't it be ...
1
vote
0answers
29 views

Script to query MongoDB with PHP [closed]

With Code Igniter and MySQL I do: $this->database->query('select * from table'); But I don't know how to use MongoDB. Can I still use this the same way to query MongoDB? ...
0
votes
2answers
25 views

Return row in given date

I have a MySQL table named tasks that contains four columns: id (Integer) name (Varchar) task_start (DateTime) task_finish (DateTime) How can I write an SQL statement that returns tasks that are ...
1
vote
1answer
36 views

mariadb 5.5.36 with innodb plugin

I installed mariadb 5.5.36 on a win32 system. Everything runs fine except innodb requests are 2 x slower than an identical MySQL installation. So I want to switch back to original innodb plugin to ...
-2
votes
1answer
64 views

how to autoincrement varchar datatype depending upon the product details name using trigger

I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id ...