MySQL : Open-Source, Relational Database Management System
12
votes
1answer
751 views
MySQL : Why are there “test” entries in mysql.db?
Recently, I posted an answer to a question about mysql.db.
Then, I got to thinking I should ask everyone this question:
I have noticed for years that upon installation of MySQL 5.0+, mysql.db is ...
0
votes
2answers
101 views
How do I use subquery on the same table in MySQL?
I have a query like this which takes a really long time to run. The table is around 4 million rows.
DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;
I'm hoping I could ...
0
votes
2answers
50 views
InnoDB Tablespace critical error in great need of a fix
Link to screenshot :
http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG
(The value reported is outside the allowed range - Byte free, free, gauge, warn, critic)
Question: ...
1
vote
3answers
36 views
How do I remove partitioning?
There is a table my_table which has not been created any partition in MySQL 5.6. And I'm trying to create partitions by procedure in my_table, when I run this code:
alter table my_table remove ...
0
votes
1answer
41 views
Syncronize mysql databases between local and hosted servers automatically
We have many website with Development , Staging and Production Server. we have many developers for many projects, we need a solution to synchronize the database with developer database with staging ...
0
votes
1answer
25 views
Creating database/tables in SequelPro (or MySQL) from External FTP Server
I just started getting a backup file (*.7z) from the folks that run our database once a week. They're sending it to me through an FTP server (I use FileZilla) -- but the file is pretty huge: 5.8 ...
0
votes
1answer
20 views
Filtering records not present with specific conditions in another table
I have a table (let's call it extra_info) in MySQL that has various extra infomation/labels on user accounts:
account_id / data_key
11 / 'test'
11 / 'blah'
12 / 'coupon a'
12 / 'coupon b'
15 / ...
1
vote
1answer
20 views
MySQL import LOCK doesn't prevent PRIMARY key issues
I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks).
Some info;
my tables are almost all ...
0
votes
1answer
89 views
speeding up a query on MySql [closed]
I have a table with more than 10 million rows and 10 fields(columns). There is an index on field_1 and I am running the following query.
create table t2
select field_1,
sum(ifnull(field_2,0)) ...
1
vote
1answer
150 views
MySQL backup InnoDB
I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full ...
0
votes
1answer
52 views
Strange characters in mysqlbinlog output
Has anyone experienced this? Data replicates fine but when output in mysqlbinlog there are hidden characters that break the input?
mysqlbinlog Ver 3.3 for Linux at x86_64
mysql 5.5.28 server
...
0
votes
1answer
31 views
I can't get MySQL56 service to start after i stopped it
I am having an issue on my server. I am running MySql on Windows Server 2008 R2. I have done some changes to my.ini file so I had to restart the MySQL for the changes to take effect. and when I went ...
0
votes
1answer
24 views
How to create the multiple sum query with joins
Please consider following database:
Table `invoices`:
-- invoice_id
Table `amounts`:
-- amount_id invoice_id type_id amount
I am trying to get sum for each type from table like this:
SELECT
...
-1
votes
0answers
10 views
i have to retrive all the rows from multiple table which does not have common field without duplication [closed]
TABLE: players
COLUMNS: id, first_name, last_name
TABLE: passing_stats
COLUMNS: pid, status, passing_yards (several other passing columns)
TABLE: rushing_stats
COLUMNS: p_pid, year, ...
0
votes
0answers
30 views
Faster way to GROUP or ORDER dynamic fields?
We have this query in our app, that searches a table for a song title using MATCH AGAINST and picks the most relevant results.
The table has around a million results and is growing.
The app has been ...
0
votes
0answers
22 views
mysql deadlock while updating: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction update
I am using the mysql version 5.5.29 in my production server. while executing set of update statement inside the procedure it is throughing the following error:
ERROR 1213 (40001): Deadlock found when ...
0
votes
1answer
10 views
Percona Xtrabackup on Suse EL 10 — Failing
I have evaluated Percona Xtrabackup successfully and it works like a charm on my several CentOS / RHEL servers! Hats off to the team!
But my problem starts when I tried to install this on one of my ...
7
votes
2answers
145 views
Does MySQL cache queries?
I'm interfacing a MySQL database with PDO and executing an extensive SQL query. Normally, it takes about 1500ms; I still need to optimize it. When I run the PHP script twice with a short interval in ...
0
votes
2answers
390 views
How to increase fast backup and restore of 500GB database using mysqldump?
There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables.
Backup and restore ...
0
votes
4answers
22 views
MySQL replication - slave update
I have a master slave setup of MySQL and my question is that if I make any changes in the slave database:
1. Will it mess up the sync in any way
2. Will the changes get overwritten from the master ...
0
votes
1answer
47 views
Having too many connection problem
We have an magento store and having 9K products and 2 store views the trouble is we are getting too many conection error while the site is in normal loads.
1) We have 100 connectoin open in mysql.
2) ...
1
vote
2answers
24 views
MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram
Here is my database design. I've been doing a lot of reading and still don't really know what I'm doing. Hoping you guys had tips!
My DB will have millions of entries.
Many "People" rows will have ...
0
votes
1answer
31 views
MySQL replication as both master and slave (replication loop?)
I've got two MySQL servers, one as a master in the replication process and the other as the slave. I was wondering if anything bad would happen if I made the current master a slave to the current ...
0
votes
2answers
16 views
Error Code : 1248 Every derived table must have its own alias in MySQL
i have problem here,
when im trying to select with query below, i got error message
Error Code : 1248 Every derived table must have its own alias
SELECT B.BRANCH_CODE, B.BRANCH_NAME, ...
0
votes
1answer
64 views
Am I overengineering MySQL?
On my project, I have to make difference between registered users, and simple visitors. Both of them can set their own properites, specified in accounts and visitors table.
I identify them by cookie, ...
-1
votes
2answers
61 views
Infinite Loop While Running Query
When I run this query from the command line, it continuously loops. I'm not sure why this is occurring. Could anyone explain this to me, and please offer a fix.
I will be using a query similar to ...
0
votes
1answer
15 views
MySQL - What is the diffrence between ClearText authentication and PAM authentication plugins?
PAM Authentication:
PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory.
Cleartext Client-Side ...
0
votes
1answer
103 views
How to check growth of database in mysql?
I want to know is there any method to check the growth of database on file
EXAMPLES
Database A contains all tables in INNODB storage engine
Database B contains all tables in MyISAM storage engine
...
0
votes
3answers
71 views
MySQL is running but not working
In an attempt to tune MySQL to make it work with a recent installation of Drupal I had to modify the MySQL settings on my server. After modifying the configuration file for MySQL (/etc/my.cnf) MySQL ...
0
votes
1answer
32 views
InnoDB Failure of some kind
I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked.
InnoDB is listed under SHOW ENGINES;
Any idea ...
1
vote
2answers
90 views
Would Mongo's ObjectID work well in InnoDB's clustered index?
Mongo's ObjectID is defined like this:
ObjectId is a 12-byte BSON type, constructed using:
a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
...
0
votes
0answers
6 views
MySQL - What is the diffrence between ClearText Authentication and PAM Authentication MySQL?
PAM Authentication:
PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory.
Cleartext Client-Side ...
2
votes
1answer
20 views
MySQL: Securing Access using Stored Procedures
I'm new to using MySQL. I'd like to use stored procedures to help secure my database when accessing it from a web service (i.e., grant only proc execute to the web service account).
What permissions ...
1
vote
1answer
46 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 ...
2
votes
1answer
28 views
Column type unknown at creation time
I have a table in a MySQL database that stores survey results.
CREATE TABLE survey_responses (
response_id INTEGER UNSIGNED AUTO_INCREMENT,
survey_id INTEGER UNSIGNED NOT NULL,
question_id ...
2
votes
3answers
214 views
Empty LONGTEXT or NULL?
In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use ...
1
vote
1answer
118 views
Query is not using indexes on third table in left join
My query is not using indexes on third table(pci promotion_coupon_images) .
i was tried with using index and force index in query but there is no change in result.
any body please suggest me how ...
0
votes
1answer
462 views
MySQL auto increment problem with deleting rows / archive table
A hosted server is running "maintenance" each weekend. I am not privy to the details.
In a database on this server there is a MyISAM table. This table never holds more than 1000 rows and usually much ...
-2
votes
0answers
18 views
MySQL-CLuster PHP5 [closed]
I have installed correctly MySQL Cluster on my Vm's the mgm nod with two SQL/Data node and everything looks ok, but I cant run my application because MySQL Cluster dont see PHP5 anyone know how to ...
1
vote
2answers
63 views
Multiple table rows in one row
I have a table from where want to get such classes where 3rd position candidate got more marks than difference of 1st and 2nd.
class position name total_marks
10 1 a 100
10 2 ...
0
votes
3answers
35 views
Convert the table PROCESSLIST from InnoDB to MYISAM
I use MySQL. My database's name is "db", and I have a table called "Test".
I want recover the last update, so I used this query:
SELECT update_time
FROM information_schema.tables
WHERE ...
4
votes
3answers
4k views
How to recover MySQL table structure from FRM files
What I have learned from Google searching:
With MyISAM tables you can simply copy the FRM, MYD, and MYI files. In the folder for the DB I'm trying to recover, there are only FRM files and a "db.opt" ...
1
vote
1answer
33 views
Repeated values in group_concat
I have two tables, first the table food and Second is Activity:
INSERT INTO food
(`id`, `foodName`)
VALUES
(1, 'food1'),
(2, 'food2'),
(3, 'food3'),
(4, 'food4'),
(5, ...
2
votes
4answers
675 views
INFORMATION_SCHEMA Selection Error
I'm trying to select data from information_schema but I'm getting the following error. How can I fix this?
mysql> SELECT * FROM information_schema.tables ;
ERROR 1018 (HY000): Can't read dir of ...
0
votes
1answer
150 views
MySQL PDO Cannot assign requested address
Can someone help me with this error?
[08-Apr-2013 17:44:08 Europe/Berlin] PHP Warning: PDO::__construct(): [2002]
Cannot assign requested address (trying to connect via tcp://****:3306) in
...
0
votes
1answer
53 views
How can extract the table schema from just the .frm file?
I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine.
I have read a fair number of tutorials today ...
-1
votes
3answers
39 views
App can't find or connect to database
I'm trying to install a web app on my Ubuntu 12.04 server.
The app is based on PHP and MySQL and I've got LAMP already working OK on my server - infact I've already got other apps working on PHP & ...
0
votes
1answer
26 views
Speeding up mysqldump / reload
Converting a large schema to file-per-table and I will be performing a mysqldump/reload with --all-databases. I have edited the my.cnf and changed "innod_flush_log_at_trx_commit=2" to speed up the ...
2
votes
1answer
187 views
How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB
I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told ...
-2
votes
1answer
56 views
create database table with a owner in my SQL
Can someone tell me what is the SQL command to create a table with specific owner?
