MySQL : Open-Source, Relational Database Management System
0
votes
3answers
135 views
How to store count value in DB [closed]
I have a table as below, order field is like auto increment, but it is not defined as auto_increment because i do not want any gaps in that value
+--------------+
| id | order1|
...
0
votes
3answers
330 views
How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?
I want to design a db schema concerning a schedule.
I. I can use all fields in the same table, for example:
id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su
1 | 1 | ...
0
votes
1answer
1k views
Setting default charset/collation for MySQL database
noob sysadmin here...
I have a MySQL DB that used to be latin1, and I changed all the columns in all the tables to be utf8. However, I evidently didn't do all I had to do, because I just found that I ...
0
votes
1answer
224 views
How to avoid table crash?
Couple of days back I had a table with 7GB of data and it was showing 63MB of overhead in phpMyAdmin and I mistakenly started its optimization and after working of more than 40 mins it corrupted the ...
0
votes
1answer
3k views
Restore MySQL DB from its folder
Some time ago, I've built an webapp on top of WAMP server. At that time, I didn't do any export/dump of my MySQL DB but I've did a copy of the WAMP folder. Does anyone have any ideas about how I can ...
0
votes
1answer
184 views
How to design databases? [closed]
Describe the process in steps, please. What tools do you use for it?
0
votes
1answer
31 views
Make rows immutable allow Insert mysql5
I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them. How can I do this?
(ideally returning a suitable error message if any change ...
0
votes
2answers
29 views
MySQL Master/Slave. Actual use of Slave Machine
We have a MySQL server running in a mobile production trailer. The trailer is out on the road for weeks at a time. We have cell internet and wifi access is nearly all of our locations. Our office ...
0
votes
1answer
26 views
“relation does not exist” trying to import mysql dump into postgres
environment:
ubuntu 10.04
mysql server 5.1.69
postgres 9.2
Here's the sequence of steps:
created a new postgres database, myDatabase
executed this command: mysqldump -u root -p ...
0
votes
1answer
44 views
Query that ranks students order by marks and give the position of student even when there are students with equal marks
I need an sql that will rank students order by marks scored in a specific examtyp e.g CAT1 and give exact position even if the students have equal marks e.g in this sample data admNo 2525 and admNo ...
0
votes
2answers
50 views
mysqlslap chokes on strings that contain “;” delimiter?
I'm having a problem passing preexisting files full of SQL statements into mysqlslap.
For example, I have a file named create.sql that contains my table structure (as dumped by mysqldump), with ...
0
votes
2answers
47 views
Can I find out what version of MySQL from the data files?
I have a very odd situation going on here. I had a linux box running ubuntu 8.10 and MySQL server with a Wordpress installation on it hosting an internal blog for our company. The machine that this ...
0
votes
1answer
20 views
mysql error creating table
I was creating a DB with mySQL, and this error appeared:
Can't create table 'projectefinal.apuntats' (errno: 150)
I've got this:
CREATE DATABASE IF NOT EXISTS projectefinal
CREATE TABLE IF ...
0
votes
2answers
53 views
How does mysql transaction works?
I already know how to use transactions, what I want to know is how MySQL transaction handles data being processed...
For example, consider the following:
1. I have 2 databases (db1 and db2) ...
0
votes
1answer
37 views
Redo Logs Mysql Cluster
The mysql cluster has 4 directories namely D8 D9 D10 D11 for redo logfiles.
But while inserting into table only one redo directory is used.
I ran following command to get redo information "all dump ...
0
votes
2answers
63 views
How do I find an invalid utf8 character “somewhere” in my MySQL/trac database
I have an installation of trac, which uses MySQL. It has been upgraded so many times, moved servers etc. and chances are that the MySQL character sets were not always set correctly or consistently ...
0
votes
2answers
66 views
Unique indexed column in where clause and mysql execution after finding first row
Suppose in a table there are 2 columns: login and password
login is indexed as unique index, password not indexed.
query: SELECT * FROM mytable WHERE login = 'Jhon'
query: SELECT * FROM mytable ...
0
votes
1answer
109 views
Is using the alpha version of Percona 5.6 safe? [closed]
I'm really sorry if my question is kind of very basic, but is something that is really making me thinking a lot about it for a long time. As Percona has a lot of lines of optimizing codes, I run it ...
0
votes
1answer
41 views
Index use and column type
I was told today that having a table structure similar to this is bad for performance of index's because of the mismatch of column types
Table 1
id - int (7) PK
Others ....
Table 2
id int (11) ...
0
votes
2answers
110 views
MySQL: can not get rid of “Using filesort” in a simple query
Simple query but can not get rid of "using filesort":
CREATE TABLE IF NOT EXISTS `online` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`expiration` int(11) NOT NULL,
...
0
votes
1answer
43 views
Mysql Join with filters
I have two tables: MAINCOLOR and SUBCOLOR with the data as:
MAINCOLOR :
MAINID COLOR
1 RED
2 BLUE
3 GREEN
SUBCOLOR:
SUBID MAINID SUBCOLOR STATUS
1 1 PINK 0
2 1 ...
0
votes
2answers
96 views
How are DB locks tied to connections and sessions?
I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
0
votes
2answers
71 views
How to take a database dump for selective set of values in few tables?
create table foo (id, val1, user_id,...)
create table bar (id, foo_id, val2, ...)
create table baz (id, bar_id, val3, ...)
select * from foo where user_id = 1;
select * from bar where id in (select ...
0
votes
1answer
173 views
MySQL crashes with Signal 8 after “Table has no partition for value” error
We have a mysql database running well and suddenly it stops and below are the snippet of the log file. What is the best remedy in this scenario?
130202 1:08:59 [ERROR] Event Scheduler: ...
0
votes
1answer
297 views
What is the proper way to backup MySQL database with rsnapshot?
I want to backup my website that contains both user uploaded images and data stored in MySQL database such that they are always consistent with each other in any particular point of time. While ...
0
votes
2answers
169 views
How often does the slow_log table get updated in MySQL?
I've changed the long_query_time from 10 secs to 5 secs. I haven't seen any queries logged for a few hours now. Am I supposed to tweak my database for the slow_log table to gets updated more often?
...
0
votes
2answers
493 views
Master - master replication not working
I configured two WAMP servers running on different machines for master-master replication. I am using a linksys router for the network. I created a wireless network, but one of the server boxes ...
0
votes
2answers
145 views
How to mix pairs of rows without JOIN (in a temporary table)?
I have a TEMPORARY table containing pairs of key/value as
id col1 col2 type pair
1 key1 key2 key 1
2 value1 value2 value 1
3 key3 ...
0
votes
1answer
178 views
How to UPDATE a column to SET previous row value to NULL cells?
I have a table as
id value
1 music
2
3 movie
4
5
6 book
7
8
Some values are missing, and I want to assign the value of previous row to produce
id value
1 music
2 music
3 movie
4 ...
0
votes
2answers
61 views
MySQL query slow even with indexes
Structure of my table
mysql> show create table t_group_tag_relation\G
*************************** 1. row ***************************
Table: t_group_tag_relation
Create Table: CREATE TABLE ...
0
votes
1answer
85 views
How to run this MySQL statement?
See the MySQL example on http://dev.mysql.com/doc/refman/5.5/en/loop.html:
I copy it and paste on MySQL command line but it stop at every semicolon.
I wonder how to make these sentences work? I ...
0
votes
1answer
63 views
Getting a “type” column from each table in query
Let's assume I have 3 tables: videos, books, albums and I would like a query that gives me the 3 latest entries in each table, along with a column "media_type" valued Video, Book, or Album, ...
0
votes
2answers
126 views
Need advises for a new MySQL architecture
I need to deploy a MySQL-based application with the following requirements and would love to receive some guidance (any) as to how I could set things up:
Scale of deployment
5 servers with a ...
0
votes
1answer
530 views
Why does max_used_connections status not get refreshed automatically once it reaches max_connections, even though the Threads_connected falls down
I always wonder, why does the max_used_connections value not come down, once it reaches the peak value of max_connections?
I always flush the status and bring down this value to avoid DB connection ...
0
votes
1answer
36 views
How to summarize Bridge results?
I am trying to summarize Bridge results for each pair (north/south pair and east/west pair).
Match points for pair north/south is MPNS, for pair east/west is MPEW. Is there a way to summarize all ...
0
votes
1answer
439 views
Foreign Key Constraint fails
I have the following tables:
// Base Scans
CREATE TABLE `basescans` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NULL DEFAULT NULL,
`status_id` INT(10) UNSIGNED ...
0
votes
2answers
381 views
Mysql giving only 25 inserts per second
I'm using ubuntu 12.04 LTS
Processor : Pentium(R) Dual-Core CPU E5200 @ 2.50GHz × 2
OS Type : 64-bit
Memory : 3.9 GiB
Disk : 101.1 GB
I installed mysql and wanted to test insert performance. Here is ...
0
votes
5answers
51 views
Copy my data from a existing db to a new db
I have a database which has my data's that I have been working for months. Now, I want to delete that db. What concern's me is that the data's that I have might come in handy in the future ...
0
votes
1answer
373 views
Unable to select the records from specific partition in mysql?
I've created a table using the following query,
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(25) NOT NULL,
lname VARCHAR(25) NOT NULL,
store_id ...
0
votes
1answer
87 views
How to set unique columns combination
see this table:
CREATE TABLE IF NOT EXISTS `default_messages_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message_id` int(11) NOT NULL,
`owner_user_id` int(11) NOT NULL,
`to_user_id` ...
0
votes
1answer
258 views
MYSQL dump and problems character encoding
I have a 2 gb old database sql backup file. The problem is that text is encoded in UTF-8. Actually this isnt the real problem. This database contains greek and english characters.
The problem is ...
0
votes
2answers
308 views
Dates changed to 0000-00-00
Previously, on my table (~81000 rows), I have had date and time fields set as VARCHAR instead of date/time. All those dates were of the type MM/DD/YY. I changed the type from VARCHAR to DATE and TIME ...
0
votes
1answer
260 views
percona mysql crashing at innodb initialization
i have a slave percona db setup that also maintains few of it's own tables
it started crashing with this in error.log:
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: ...
0
votes
1answer
290 views
Why do aggregate functions alter the way my rows are fetched?
Running this query
SELECT
s0.id,
s0.symbol,
YEARWEEK(p0.datetime) AS label,
p0.id AS point_id,
p0.datetime,
KG_FIRST(p0.open, p0.datetime) AS open,
MAX(p0.high) AS high,
...
0
votes
1answer
367 views
MySql command line cyrillic Iinsert result is corrupted
Have putty connection.
Putty config : window-->translation-->remote character set-->utf-8
Launch command line MySQL client.
Perform insert with cyrillic like:
CREATE TABLE `category` (
`catName` ...
0
votes
1answer
282 views
which DBMS should i go for? [closed]
I am interested in learning a DBMS. As a beginner which DBMS should i go for ?
I know few DBMS by name like Oracle,Sql Server,My Sql . How are these DBMS different from each other ?
As a beginner ...
0
votes
1answer
611 views
MySQL Replication question
I have setup a master-slave replication between two MySQL servers using mysql Version 14.12 Distrib 5.0.51a, for debian-linux-gnu.
Recently the replication errored out with this in the logs:
...
0
votes
1answer
1k views
Change path for binary logs and scheduling
I want to change the default name of binary log and path. I saw the documentation where it discusses about base_name. However, I just need to confirm how to use it correctly. I want binary logs to be ...
0
votes
1answer
122 views
Derived value not calculating as expected using combination of FK and Triggers (MySQL)
I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints.
There are 3 tables involved in this arrangement
responses
------------------------
...
0
votes
4answers
27 views
MySQL Master/Slave without populating tables
So I have some Zabbix instances which are using MySQL for their backends. What I would like to do is, for DR purposes, backup the DBs for each instance using a Master/Slave configuration, with the ...
