MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
0answers
31 views

MySQL method for inserting data from JSON using Python Connector

I have been using Python to insert parsed data from a JSON server response into a MySQL database. The JSON data consists of around 10,000 responses, each holding around 40 nested pieces of ...
0
votes
2answers
18 views

How to deny connections to localhost and force to use FQDN instead

I have a server running Apache and MySQL. Connections to MySQL work fine when using localhost in the connection settings. Now I'd like to force our developers to use a FQDN instead of localhost. This ...
1
vote
1answer
24 views

Mysql's internal mysql database in replication

I searched around but the word game seems to delude any valid result and could not find anything relevant. Mysql (the RDBMS) has an internal database named mysql, this database has information about ...
0
votes
0answers
13 views

Insert in PXC Cluster are not equally distributed

I have another query with regards to pxc cluster i have 4 nodes in pxc cluster all nodes have the same cnf file and one server as haproxy. Config File Detail [mysqld] datadir=/var/lib/mysql ...
1
vote
0answers
17 views

How to make an indexed search over a list (set)?

A follow-up question for How to most efficiently store possible combinations of categories? An answer pointed to SET datatype. As far as I know, the default search option for it, FIND_IN_SET, doesn't ...
0
votes
1answer
10 views

Wiki instance mirrored to another box

I've never really done anything like this before, but I have a wiki instance setup on my internal network. Since it is a support wiki, in the case that our internal network is inaccessable through our ...
1
vote
1answer
29 views

Ticket-like table and growing database

I manage a MySQL database through phpMyAdmin. I have a table for cinema cupouns tickets. I upload a bunch of tickets, and then I assign tickets to users. Each ticket contains, id -> ...
0
votes
1answer
11 views

Checking for duplicate entries for Column A - Table A based on user_id from Table B

I have two tables which have a 1 - 1 relationship. Table A - Skill_id Tabel A - Entry_id Table B - Entry_id Table B - Author_id The two entry_id columns are a primary key - foreign key. I need to ...
0
votes
1answer
31 views

auto_increment incremented on non-affecting queries

When performing the following query on 'sessions': INSERT IGNORE INTO sessions (user_id, archived) VALUES (3, 0) while: CREATE TABLE `sessions` ( `id` bigint(20) unsigned NOT NULL ...
0
votes
1answer
34 views

Storing small array of many possibilities

Let's say I need to store the text of a series of books, line per line in a database, and present them in a (CMS-based) website. I thought the correct setup would be: a 'library' table: containing ...
0
votes
1answer
26 views

How to run a mysqldump from a .txt or .sql file inside a command line window?

If I run the follow line directly in command line window it will works good. C:\xampp\mysql\bin>mysqldump -u root d3bi>C:\Users\me\Desktop\test\testDump.sql How can I run a .txt or .sql file ...
0
votes
1answer
13 views

manual transfer fails with table does not exist

I performed a manual transfer of data manually (zip up the dir and rsync'ed it over. I moved the members.ibd (the only .ibd file) to a save place, extracted and then replaced members.ibd with the ...
0
votes
0answers
17 views

Error when creating multiple tables at once

I'm using mysql through the CLI in OS X. The problem I'm having, is that when I create multiple (more than 3 in this case) tables, I receive errors, but if I enter the code, 3 tables at a time, I get ...
0
votes
1answer
17 views

Create a default value for session valid_until MySQL

Server version: 5.6.22 Homebrew The table: CREATE TABLE `sessions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `date_created` datetime ...
0
votes
2answers
51 views

Schema review for system to advertise cars

Ok, so I am carrying out a rather simple task as part of a job requiretement. Here is the task in a nutshell: Design a form for creating ad for selling car Form has 2 have: 2 drop downs: Car make ...
0
votes
0answers
30 views

I have a table from which I can retrieve information but I cannot update or delete information

I can update and delete items via PhpMyAdmin. I changed the owner to mysql. My program is a clone of insert, update, and delete that is functioning for other tables within the database. I receive a ...
2
votes
2answers
74 views

Retrieve total available days in date range compared against multiple records

Given the following MySQL database structure for a booking system, how can I retrieve all available days between two user supplied dates? | ID | code | date_arrival | date_departure | 4 | APT01 | ...
6
votes
2answers
762 views

Why are mysql password hashes internally saved with a star (asterisk)?

I was reading upon some mysql internals, when going through the mysql.user table in the my mysql shell, I get mysql> select * from mysql.user limit 1 \G *************************** 1. row ...
1
vote
1answer
28 views

how to cache a subset for cascading select queries in mysql

Here is another database problem I stumbled upon. I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements ...
0
votes
2answers
39 views

How to most efficiently store possible combinations of categories?

I have a table of categories (currently with 14 rows, not likely to change). A need to cache combinations of categories emerged, so I'm creating a table of allowed combinations (only few hundreds of ...
0
votes
1answer
77 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in mysql although I feel the schema should be optimized for it since it's based on the wordpress database model. I've got 4 tables... ...
-1
votes
1answer
31 views

What MySQL activity eats CPU? [closed]

I have noticed weird CPU consumption peaks for MySQL process. When I monitor running queries, it's empty or some reasonable queries being executed. How do I better address the problem? Is there a ...
1
vote
1answer
20 views

Is it safe to disable InnoDB doublewrite buffering when setting Innodb_page_size = filesystem page size?

As I understand, doublewrite buffering is needed to avoid partial write, because Innodb_page_size default is 16K, while filesystem page size normallly is 4K; I wonder if I set "Innodb_page_size = ...
1
vote
0answers
11 views

MySQL Cluster 7.4.6 - Analyze table failure on 15m reference table

I'm running MySQL Cluster 7.4.6 on Azure VM's with the following infrastructure: 2 x Mgmt Nodes: Ubuntu 14.10LTS, 2 cores, 3.5GB ram 2 x MySQL API Nodes: Ubuntu 14.10LTS, 2 cores, 3.5GB ram 2 x Data ...
0
votes
0answers
24 views

manual data migration/rescue MySQL version: 5.6.23

MySQL died and could not be started. So I transferred my database files via rsync to another server and restore from back up. Now I am trying to access my data with my updated records. mysql> use ...
2
votes
2answers
46 views

MySQL taking too much CPU

Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that ...
0
votes
1answer
52 views

Does my ERD look well designed?

I am making a database at no cost for a local dance school. I'm still very new this would like to be sure that I designed it appropriately. From the front end, the parent will: fill out their ...
0
votes
2answers
30 views

How to solve “Can't connect to local MySQL server through socket” error?

I kept getting this following error every time I tried to restart MySql. It was working fine before I changed the default data directory. ERROR 2002 (HY000): Can't connect to local MySQL server ...
0
votes
1answer
23 views

Does it makes sense to partition on a foreign key?

I have a MySQL table which looks a bit like this: ID (BIGINT, PK), OtherID (INT, FK, indexed), NegativeTimestamp (BIGINT, indexed) Selects should go very fast, updates almost never happens, inserts ...
0
votes
0answers
15 views

SQL Insert with HTTP1.1 [migrated]

Sorry for an amateur question but I have no idea why this does not work. I have a "add.php" to connect to the SQL server <?php include("connect.php"); $link=Connection(); ...
0
votes
2answers
31 views

Why does “DELETE WHERE (VARCHAR)=0” execute successfully?

DELETE FROM stats WHERE name=0 "name" is type VARCHAR(64). The above resulted in all records in the table being deleted, instead of just the 10~ that should have been affected. Only that many were ...
0
votes
0answers
25 views

Designing multi-table notes

I have several tables (Providers, Items, Users) representing items that my users can comment on. In the OO mindset, if the above tables were classes, they would each be a subclass of Commentable, ...
0
votes
2answers
42 views

How to retrieve all data from table if column value providing null value?

How to write a query: If country = ' ' I need to display all data in table. If country='algeria', it will display the particular data. I need both queries in a single query. I am getting result, ...
0
votes
1answer
48 views

Scalability question

There is a DB {MySql} with 5 tables A, BIG, C, D, E. Their grow factor is about 1/100/1/1/1. The table BIG has Insert/Read/Update request ratio about 1/10/2. The Inserts and Updates 'cannot fail'. ...
1
vote
2answers
27 views

Need to make MySQL's LOAD DATA LOCAL INFILE load large data faster

I need to load 4 million rows of data into a MySQL InnoDB table using LOAD DATA INFILE and would like to know if there are server configuration options I can tweak to get faster load. It took me 15 ...
0
votes
1answer
27 views

How can I create a mongo db based on mysql squema?

How can I create a mongo db based on mysql squema? this is my schema productCode VARCHAR(15) productName VARCHAR(70) productLine VARCHAR(50) productScale VARCHAR(10) productVendor VARCHAR(50) ...
0
votes
0answers
26 views

Cant Import mysql system DB from another server

Both Servers are the same in hardware, OS, and MySQL versions (5.6.11). One server has MySQL on it and on the other one I just setup a fresh instance. I want to import all of the data to the second ...
2
votes
1answer
33 views

Strange Availability Problem with MySQL

I am trying to troubleshoot an e-commerce site problem that seems to be database related. In my investigation so far, I noticed that the server seems to have an unusually high numbers relating to ...
1
vote
2answers
33 views

Best way to implement concurrent table based queue

I have a table in MySQL that represents a queue of links to be processed. The links are processed by an external app, one by one, and deleted in the end. This is a high volume queue and I have ...
1
vote
0answers
17 views

Explain unexpected result in MATCH () AGAINST ()

I have a database with a fulltext column name. I made a query SELECT name, MATCH (name) AGAINST ('breaking bad') AS score FROM files WHERE MATCH (name) AGAINST ('breaking bad'); So that I can see ...
1
vote
2answers
66 views

How to update a multiple rows all at once with a procedure.?

Im trying to do a procedure to update an (for now) empty column recently added. In this new column, i will put the values of the result obtained by a join query. To explain it better, i have this ...
2
votes
1answer
38 views

How to move a 80GB MySQL Database with least effort and least time offline?

Im a software developer with 4 years experience but brand new in my company, my first job is to move a 80GB mySQL database from a very very small and old database server to a brand new. The problem: ...
2
votes
1answer
22 views

Using the output from mysqldiff.exe to transform database2 to match database1

This question is related to another I had asked earlier here: Passing passwords more securely to mysql utilities on the command line. I have recently been trying to use mysqldiff.exe, part of MySQL ...
-2
votes
0answers
11 views

how to design Multilevel and multiuser application database schema [closed]

I am developing an event management application with the user roles like super admin, admin and user. I want to design database in the way that only superadmin can login and create admins of ...
1
vote
1answer
17 views

if set global transaction, how can I commit?

I found that MySQL can set a global transaction, but can I commit in another session? refer: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html
0
votes
2answers
33 views

Whats SQL Server NVARCHAR(max) equivalent in MySQL?

Using SQL Server you just have to give the "MAX" parameter to the length of a text data type, but in MySQL there's no such a thing. According to Ispirer: "n" is the maximum number of characters, ...
0
votes
1answer
17 views

Calculate total attendance for each workshop

I want to get overall attendance in each workshop. NumberOFWeeks column holds the number of weeks the workshop runs. for example Workshop 101 runs for 6 weeks meaning 6 days. So far I have done ...
0
votes
1answer
44 views

Using a subquery in a LIKE statement

I need to write a subquery inside a LIKE statement. This subquery would return me a word that I need to find in the middle of a sentence (that belongs to another field). Is it possible? This is how ...
0
votes
1answer
40 views

Passing passwords more securely to mysql utilities on the command line

This question is related to another question I subsequently asked here: Using the output from mysqldiff.exe to transform database2 to match database1. I have been trying to use mysqldiff.exe, part of ...
1
vote
2answers
40 views

Database design for multiple user attributes

I am building an app with Laravel, and I have users which also have a profile. I am unsure about the best way to set up the database for the user attributes. For example: user_ud | name | ...