MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
2k views

InnoDB tables inaccessible after reboot

After reboot each time, I can't see my InnoDB table. However, when use command mode I can see my tables, but cannot access to it. mysql> show tables; +----------------------+ | ...
1
vote
2answers
73 views

adding ORDER BY decreases speed by 15x

I have a simply query: SELECT DISTINCT images.* FROM `images` WHERE `images`.`user_id` IN (SELECT following_id FROM `follows` ...
1
vote
1answer
5k views

BEFORE INSERT trigger in MySQL

I am new to MySQL and I'm having problems creating a BEFORE INSERT trigger. I get an unexpected END error. I have a table named verlof_aanvragen with a column datum (and 6 more columns). What I ...
1
vote
1answer
281 views

How to rebuild entire MySQL data after server re install?

I have a MySQL server running with multiple databases in a 32 bit environment. I need to rebuild my system to a 64 bit environment. Is it possible to replicate the entire MySQL databases, users, ...
0
votes
1answer
193 views

What's the practice to generate long user id like those on Facebook?

Now I am about to launch a site and I have trouble coming up with a way to create long user id(e.g. 4000002). Do I set id to auto-increment(Starts at 0) and then add something(e.g. 4000000) to this ...
1
vote
2answers
311 views

mysql innodb_flush_log_at_trx_commit clarification

I have been reading up on innodb as a storage engine, mainly because I have recently moved to AWS and they do not recommend myISAM which my databases current run on (and have for about 10 years) So ...
0
votes
2answers
54 views

MySQL Logging Options

I want to know what logging options are available to me in MySQL; I find a lot more results about binary logging than I do about general/error logging and I'm not sure what options are available to me ...
0
votes
1answer
356 views

Can't connect to a MySQL from the command line (works from MySQL Workbench)

I am connected through a local socket/pipe and the username password is root/roots. I can login and view the 4 schemas I have created. However, when I use the mysql command at the command line and ...
0
votes
1answer
134 views

Optimizing a query with ORDER BY in a derived table

The query below takes too long to execute (58 seconds). If I run two more queries like this with different nid values, the second and third query take much more time to execute. How can I optimize it? ...
2
votes
0answers
19 views

MySQL is too slow [duplicate]

I don't know why mysql is too slow for querying about 39000 records which include inner join with other two tables. I have country, region and city tables as follows: tbl_country CREATE TABLE ...
3
votes
1answer
350 views

mysqldump freezing on a specific table

I dumped a database (sys_data) which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376). My dump command: mysqldump -uxx -pxx ...
0
votes
1answer
146 views

Implementing something similar to “pointer arrays” with MySQL

While building a site I came across the following issue: I want to be able to have multiple containers and within each container have ids pointing to multiple items that lie on another table. Scheme: ...
0
votes
1answer
634 views

User roles and permissions

Look at the attached graphic. I'm trying to keep this as simple as possible. Basically I want to have multiple admins(or superusers) who own regular users who own sites. Admins can give regular ...
1
vote
2answers
779 views

/usr/sbin/mysqld was deleted and replaced by another mysqld with different inode

This happened on three of our mysql server running on RHEL. While checking for open and deleted files, I found that mysqld in use is deleted (as seen in lsof) and was replaced by a similar mysqld (in ...
2
votes
2answers
1k views

Percona XtraDB Cluster and MySQL Replication

I've setup a 3-node multi master replication with percona xtradb cluster, it works perfectly. Now I've tried to add some read-only slaves setting up replication as usual but it seems the binlog ...
0
votes
1answer
40 views

FILE privilege still allows for file writing on server (localhost)

I created a user without the FILE privilege, and a web app connected to the DB with this user is still able to write text files on my localhost. To write these files, the program just uses fopen and ...
1
vote
1answer
354 views

creating a report on the mysql jasperReport server

I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf ...
9
votes
2answers
2k views

Does MySQL cache queries?

I'm interfacing a MySQL database with PHP Data Objects (PDO) and executing an extensive SQL query. Normally, it takes about 1500 ms; I still need to optimize it. When I run the PHP script twice ...
0
votes
2answers
80 views

Query keeps on executing

I'm executing below query, but it keeps on executing. In place of some time I have a time value. select distinct(col1) from table1 where col2 > 'some time' and col1 not in (select ...
0
votes
0answers
111 views

MySql Scalability, Master Slave replication a good idea? Read description please

I have a database on mysql which is scaling on daily basis and right now its on 5M records and in a month it may double itself. I have a table containing users data which is the largest one. I have ...
1
vote
1answer
781 views

MySQL - Export two columns from large table without causing a lock

I am a sysadmin who doesn't do a ton of dba stuff. For a project, I have access to a production server--which I am not the sysadmin for--with an enormous 40,000,000 row >10 GB MySQL InnoDB table. I ...
0
votes
1answer
28 views

New to mySQL Relationships

I'm new to mySQL relastionships and I'm wondering if you can help me out. This is what I want to do: Users Table user_id user_name pass_word permission_id Permissions Table p_id ...
0
votes
1answer
2k views

MySQL dynamically optimize innodb tables without “file per table” setting

We are getting a “too many connections” error once a week the same time a mysql procedure runs. The procedure runs “optimize table” on hundreds of tables and takes nearly ten hours to finish, taking ...
0
votes
0answers
40 views

Having an issue updating the cost field in my db according to the rules of a specific telecoms tariff

Our organization is running an OpenSuSe 11 enviroment with MySQL, I'm currently trying to take all the calls in our calls table and cost them using the duration multiplied by 0.00504, then from that ...
1
vote
1answer
509 views

Running pt-table-checksum on a table in different databases

I'm using filters for replication: replicate-wild-do-table = test_slave.% replicate-rewrite-db = test->test_slave Is there an option to specify different DB name on slave for "pt-table-checksum" ...
0
votes
1answer
104 views

Is there an advantage to moving to 'real' date partitions on MySQL 5.5?

We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column): ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
0
votes
2answers
302 views

what happens when InnoDB logspace is too small

I've just seen several mysql errors in my log: InnoDB: ERROR: the age of the last checkpoint is X InnoDB: which exceeds the log group capacity Y I understand, that I do have to increase the ...
0
votes
0answers
66 views

InnoDB data back in 3 months after OS shutdown

I've got an interesting issue last week that after shutting down an instance of mine, all data were back in 3 months (like a big rollback), even though there is no sign of crashing, recovery crash ...
1
vote
1answer
710 views

Sorting Result state taking very long time

I am using a MySQL 5.1.69-log DB for analysis purpose. I've designed my database in a star schema. All tables are using InnoDB. My innodb_buffer_pool_size=2Gb This is the query I am running : ...
0
votes
2answers
2k views

MySQL Continue Handler Problems inside a Function

I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I ...
1
vote
1answer
1k views

Table is marked as crashed and should be repaired

I have MySQL and Nagios installed Linux PC (Centos5.5) I have wrote some script to fetch the data from Nagios to Mysql DB (Exactly fetch data in Table) The data fetching working properly (confirmed ...
1
vote
1answer
1k views

Need to replace only first occurrence of string

I hope I worded the question right. Here are the specifics: This is regarding a MySQL database. I've inherited several hundred posts with custom fields and a unique excerpt. At this point I think I ...
1
vote
1answer
2k views

In creating view use SQL Security definer or invoker?

CREATE VIEW syntax is CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name ...
0
votes
1answer
2k views

Too Many database connections on Amazon RDS

We are having problems with users running queries/views in Drupal that occasionally cause our site to freeze. The freeze occurs because the query causes the number of database connections to go up to ...
1
vote
1answer
990 views

mysql replication goes out of sync for some tables

We are running mysql 5.1.61 on redhat systems and have the following setup One master and four slaves replicating from the master, we recently added a new slave for replication and over a few days we ...
1
vote
1answer
150 views

Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

Environment: Ubuntu 12.04 (and 13.04) MySQL 5.6.11 I have a table which has a full text index on it (real table has much more columns and rows): DROP TABLE IF EXISTS articles; CREATE TABLE ...
3
votes
3answers
860 views

MYSQL wont use index in “IN(SELECT ..)” sub clause

I have two tables in MYSQL with target fields set as index / primary index already. Then I ran the below sql query. EXPLAIN SELECT charge FROM ec_provider_account_option WHERE ...
0
votes
3answers
919 views

Is it worth to separate columns into multiple tables for one-to-one relational table

I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table. ...
1
vote
1answer
2k views

How to run a SELECT query within while loop in PHP?

Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as $result1 = $mysqli->query("SELECT * FROM table1"); while ( $row = $result->fetch_assoc() ) { if ( $row['X'] == ...
0
votes
1answer
428 views

Database replication using wamp?

I have created a POS system for our corporation, in the HQ we have a wamp server with the main database, we also have more than 25 branches across the country. I will setup a wamp server on each ...
4
votes
1answer
11k views

How can I reset a mysql table auto-increment to 1 in phpMyAdmin?

I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this: ALTER TABLE tablename AUTO_INCREMENT = 1 I am curious if there is a way to do this from within ...
-1
votes
1answer
4k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
0
votes
1answer
108 views

What structure should be used to store data with multiple category identifiers?

I have a table within a MySQL database with various data in it (for the sake of example, let's give it the following: ID | Place | Phone | Email | Category ) Now, I want to redesign ...
1
vote
1answer
90 views

Stored Procedure Not working as Expected

When I create the stored procedure without parameter it works as expected, but when I create it with parameter it doesn't return any row. DELIMITER // CREATE PROCEDURE search() BEGIN SELECT * FROM ...
0
votes
4answers
1k views

How do I backup a database from MySQL 32-bit and restore it to MySQL 64-bit?

I'm currently using MySQL version 5.0.16, on my local computer (which runs a 32-bit OS), for easier access while developing my program. Now that I'm ready to deploy my program, I'd like to test the ...
1
vote
2answers
997 views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
0
votes
1answer
44 views

Load infile query issue in mysql

We are using Load data infile query to load txt files in the database (mysql) . we have a continues flow of text files to process using load query and is dumped in the table but sometimes the load ...
0
votes
1answer
47 views

MYSQL - Using simple inventory number convention as Primary Key

I have an inventory with three numbered groups like so: Group 1: 0-999 Group 2: e0-e999 Group 3: v0-v999 Can I simply make a one-column table to store this information and make that column the ...
3
votes
1answer
488 views

Getting Random Sample from large database MYSQL (No Auto Inc Field)

You were so helpful with my last question, I thought id throw another one at you thats stumping me. Basically I got a table, requirements from company was it was all supposed to be 1 table so I got ...
1
vote
1answer
308 views

Is there a way to find the least recently used tables in a schema?

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...