Re: UPDATE statement causes signal 11 on 5.0.16

[Ian Sales (DBA)]

Gleb Paharenko wrote:


Hello.

Resolve a stack trace and provide it to the list. See:
 http://dev.mysql.com/doc/refman/5.0/en/crashing.html
 http://dev.mysql.com/doc/refman/5.0/en/using-stack-trace.html
 


- resolving the stack dump gives me this:-

0x8150650  + 135595600
0xe420  + -7136
0x9aec5f0  + 162448880
0x834e4e8  + 137684200
0x81edff2  + 136241138
0x81b632e  + 136012590
0x819c51d  + 135906589

- which doesn't help...


Now about the replication problem. As I've understood you have
triggers only on the slave. In my opinion, to solve this problem,
you should just recreate all triggers after an upgrade. If you use
mysqldump during the upgrade, it should recreate triggers automatically.

 

- I have tried manually creating the triggers after the upgrade. That's 
not the problem. The problem is that replicated queries fire off the 
triggers, but replicated queries run as "system user". There is no 
"system user" in the privilege tables, so I can't give the account the 
privileges necessary to run triggers. As a result, the triggers always 
fail and break replication.


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: UPDATE statement causes signal 11 on 5.0.16

[Ian Sales (DBA)]

Gleb Paharenko wrote:


Hello.

Please, could you explain what does it mean 'Signal 11 without fail'.
Usually after receiving such a signal MySQL crashes.  Of course, it
should do this in normal circumstances. What is in the error log? Check
if the problem still exists on official binaries of 5.0.18.

 

- sorry, idiomatic English :-) "signal 11 without fail" means it 
*always* causes a signal 11. I have tried upgrading to 5.0.18, but 
encountered the same problem. I downgraded back to 5.0.16 because the 
DEFINER functionality added to triggers in 5.0.17 does not allow 
triggers to be fired off my replicated queries.


- the error log gives a stack dump, and says this "could be because you 
hit a bug".  I'm looking for a little more information than that...


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

UPDATE statement causes signal 11 on 5.0.16

[Ian Sales (DBA)]

Hi,

I'm running a 5.0.16 instance on a Debian box (2.6.13 kernel). The 
following statement causes a signal 11 without fail, and each time when 
mysqld_safe restarts the daemon, no socket file is created:


UPDATE X_Products.product_details AS pd , 
X_Products.tblMaxProductStockDisplay AS sd SET pd.allocated = IF( 
pd.stock_level>sd.intMaxStockDisplay , 
(pd.stock_level-sd.intMaxStockDisplay) , 0 ) WHERE pd.product_uid = 
sd.intProductID;


I can find no reference to unsupported syntax or a bug. Has anyone else 
had the same happen to them?


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: High Performance MySQL on Amazon

[Ian Sales (DBA)]

Ian wrote:


Hi,

On Amazon uk there are two versions of the book "High Performance MySQL ":

1st:
High Performance MySQL 
~Jeremy D. Zawodny, Derek Balling
O'Reilly 
Paperback - April 23, 2004 
£19.95


2nd:
High Performance MySQL 
~Derek J. Balling
O'Reilly Vlg. GmbH & Co. 
Paperback - October 31, 2004

£29.26


 


- the second one is German language.

- ian


--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: triggers on 5.0.17 -- definer not fully qualified

[Ian Sales (DBA)]

Gleb Paharenko wrote:


Hello.


This should be interesting for you:

 http://bugs.mysql.com/bug.php?id=16266

 


Hi,
The bug report implies that if you recreate the triggers on the slave 
(deleting them manually from the database directory), then it resolves 
the issue. Unfortunately, the problem I have is that the triggers 
themselves are not being replicated; they exist only on the 5.0.17 slave.


- ian


--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: triggers on 5.0.17 -- definer not fully qualified

[Ian Sales (DBA)]

Gleb Paharenko wrote:


Hello.



 


I've subsequently upgraded the instance to 5.0.18,
   





Have you updated master to 5.0.18 as well?

 

- unfortunately, the set up demands that the master stays at 4.0. I 
can't change that. The triggers were working in 5.0.16. It's the new 
DEFINER that was added with 5.0.17 that seems to be causing the problem.


- ian


--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

triggers on 5.0.17 -- definer not fully qualified

[Ian Sales (DBA)]

After upgrading to 5.0.17, the triggers on one of my instances now break 
replication with a "definer is not fully qualified" error. I set the 
DEFINER in the CREATE TRIGGER statement to CURRENT_USER (i.e., 
[EMAIL PROTECTED]), but the error still occurs. If I try setting the 
DEFINER to any other user, then I get "user does not have access" 
errors, irrespective of whether that user does or does not have the 
necessary privileges.


I've subsequently upgraded the instance to 5.0.18, but the error still 
occurs. I've also tried experimenting with setting the DEFINER as a 
specific user and then assigning INSERT, UPDATE, DELETE, SELECT and 
SUPER privileges to that user. To no avail.


Anyone have any ideas how to resolve this?

Thanks

- ian


--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: PHP4 or PHP5?

[Ian Sales (DBA)]

Jan Pieter Kunst wrote:


I don't see why 40 tables is such a big deal? Normalizing data usually
results in more (but smaller) tables and crosstables. If you are
working with complicated normalized data, 40 tables does not sound so
excessive to me.
 

- I wish I only had 40 tables to worry about... Our core database 
platform contains some 2,600 tables in 50 databases. And that's only one 
of 8 platforms...


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Not finding customers without invoices

[Ian Sales (DBA)]

Brian Dunning wrote:

I'm trying to find a list of customers including a count of all their  
invoices, but it's not including customers who have no invoices - and  
it should. What's broken?


SELECT customers.company, count(invoices.id) as invcount
FROM customers, invoices
WHERE customers.id= invoices.customer_id
GROUP BY customers.id
ORDER BY customers.creation desc

Thanks!


- you need a LEFT JOIN. See http://dev.mysql.com/doc/refman/5.0/en/join.html

- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Triggers

[Ian Sales (DBA)]

Steffan A. Cline wrote:


Upon insert or update I get the following error:

ERROR 1442 (HY000): Can't update table 'locations' in stored
function/trigger because it is already used by statement which invoked this
stored function/trigger.

What exactly is the meaning of this? Is there no way around this? I only
want to update the one that was just inserted/updated.
 

- you cannot use a table in a trigger which is triggered by an action on 
that self-same table, as this is recursive. You can, however, use NEW as 
a synonym for the data being changed which fires off the trigger. E.g.,


CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW
UPDATE zipcodes
SET zipcodes.lat=NEW.lat, zipcodes.lon=NEW.lon
WHERE  zipcodes.zip=NEW.zip;


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: innodb locking

[Ian Sales (DBA)]

Tony Leake wrote:

Hi, 


I have a query:

UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid =
10798

intOrderUid is the primary key

There are 25 columns in the table and a further 8 of these have indexes
on them. The table is innodb

I have just tried to run the above query 3 times and i got the follwing
error

Invalid Query Lock wait timeout exceeded; try restarting transaction

 


- try using SHOW INNODB STATUS. That will give you more info on table locks.

- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: good database design

[Ian Sales (DBA)]

Tim Hayes wrote:


I disagree completely.

I prefer to have regard to the statement of requirement, which in this case
is a concern over performance. If following conventional design rules
creates performance issues, then performance related issues come first when
considering design.

 

- personally, I would consider integrity, and then reliability, above 
performance. But then 80% of any performance hit is in the application 
code. Design a database that gives you confidence in the data it stores 
first and foremost.


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: database structure question...

[Ian Sales (DBA)]

bruce wrote:


hi...

i'm considering an app where i'm going to parse a lot of colleges (~1000)
faculty information. would it be better to have all the faculty information
in one large table or would it be better/faster to essentially place each
college in it's own separate table, and reference each table by a
college_ID, that's unique and assigned to each college, and maintained in a
master_collegeTBL...

thoughts/comments/etc

i'm leaning towards the side that keeps each college information separate,
although this means that i essentially have to deal with 1000s of
tables/files...

 

- don't split data into separate tables by location. Seperate it by the 
type of information.


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Suggestions on db server configuration - Replication & load balancing or Clustering??

[Ian Sales (DBA)]

Ed Pauley II wrote:



I need to come up with a high availability, high performance MySQL 
server setup. I have two database servers half way across the country 
from one another being replicated through a VPN. These db servers 
serve two very busy web sites with multiple applications accessing the 
db. During busy times we are seeing 1200 to 2000 QPS. For good reason 
our database servers have high load averages during peek times. I have 
been looking at MySQL clustering, but due to the fact that our 
database is rather large the in memory only restriction will make it 
unfeasible. The other option is load balancing and replication. My 
problem with this setup is that there will be too many points of 
failure since there can only be one master for each slave. Not to 
mention the lag that may be introduced since there would be multiple 
servers at each location. It is crucial to the operation of the sites 
that all of the servers stay in sync at all times.


Does anyone have any suggestions?




- check out http://www.ultramonkey.org/3/ It's not the perfect solution 
to your problem(s), but it might help.


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How do you become a MySQL DBA

[Ian Sales (DBA)]

Mark Ahlstrom wrote:


Here's an odd question for you, how do you become a MySQL DBA?

I've got enough Solaris/Linux Experience under my belt and I was a Jr.
Oracle DBA for a year, which got me really interested in RDBMS. I try
to work with MySQL as much as possible, but I work with one of those
large telco's that does not like anything where they can't pay large
amounts of money. This means I have to work it into my spare time.

I've been trying to tie MySQL into basic services: ftp, DSPAM, pop3,
AND offer help for what we do have: running backups and repairing the
odd table when needed.

But the question is, what else could I do to help develope DBA skills?
Right now I have very little data that goes beyond 2 tables, so my
query skills are withering.


 

- DBA skills are more important than the MySQL skills. Two very useful 
books on the subject are AN INTRODUCTION TO DATABASE SYSTEMS and 
DATABASE IN DEPTH, both by CJ Date. For MySQL skills: there are plenty 
of books on the subject, and everyone no doubt has their favourite(s). 
Other than that: experience. You could try building yourself a MySQL 
database at home, for something like your book or DVD collection.


- ian


--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB to MyISAM

[Ian Sales (DBA)]

Peter Normann wrote:


Martijn Tonies  wrote:

 


Nevertheless, foreign key constraints belong in the database, not in
your application... If you have foreign keys (your wording), you need
foreign key constraints. Period. Plain and simple. No discussion :-)
   



Foreign keys are foreign keys. Constraints are constraints. Foreign key
constraints are... well, you do the math.

So, in your opinion, MySql was never really a relational database until
whatever version enforcing refential constraints was released?


 

- if you want the full half-hour argument on whether RDBMS are *really* 
relational, check out http://www.dbdebunk.com/index.html :-)


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB to MyISAM

[Ian Sales (DBA)]

Scott Purcell wrote:


Hello,
I posted last night but did not receive an answer. I am trying to create a 
fulltext index, but my table was created as an InnoDB type.

There is quite a bit of data there (1000+ records) and I need to change to a 
MyISAM table for the indexing for fulltext search.

How can I convert the InnoDB to a MyISAM, and also, when using MyISAM, can I 
have foreign key relationships?



 


- ALTER TABLE table_name TYPE=MyISAM;

- and no, you can't have foreign keys with MyISAM tables--or rather, 
there's nothing preventing you using foreign keys but you will have to 
enforce referential integrity programmatically. MySQL won't do it for you.


- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: substituting a certain word in all the tinytext rows in a table using SQL command

[Ian Sales (DBA)]

symbulos wrote:
Is it possible to substitute, using SQL command, a certain word with another 
word in all the field in all the rows (tinytext) within a certain table?

 

- see REPLACE here:
http://dev.mysql.com/doc/mysql/en/string-functions.html
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Update Email Column

[Ian Sales (DBA)]

shaun thornburgh wrote:
Is it possible to update all the domain names for an email column? I 
want to change everyones email address to my domain for a test site, 
so can I change the email address column such that everything after @ 
is changed to mydomain.com?

- see REPLACE here:
http://dev.mysql.com/doc/mysql/en/string-functions.html
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Performance Tuning - Table Joins

[Ian Sales (DBA)]

j llarens wrote:
2) varchar(255) on ALL fields? That's unlikely, in-cre-di-ble. Right type for the right
data, int for numbers, float for money, char for fixed string, and certainly NOT 255 for
lenght! If its necesary such amount of characters, TEXT or BLOB must be used, but only if
it is necesary.
 

- I'd recommend not using float for money. You get rounding errors.
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Sum() unequal number of rows

[Ian Sales (DBA)]

"Dave Kennedy" <[EMAIL PROTECTED]> wrote on 03/06/2005 05:09:36 PM:
 

SELECT sum(col1) AS total1
FROM t1
GROUP by col1
SELECT col1, sum(col2) AS total2
FROM t2
GROUP by col2
Table t1 contains 4 rows to sum for each value in col1 

Table t2 contains 8 rows to sum for each value in col2
If one select statement is used, the result has 8 rows and total2 is
correct but the tota11 is summed over 8 rows instead of 4 

How can they be joined to produce one row total1, total2 
   

- UNION? (http://dev.mysql.com/doc/mysql/en/union.html)
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with an SQL query

[Ian Sales (DBA)]

Michael Satterwhite wrote:
-
As it is, all I can suggest is to JOIN on all 90 tables - and hope MySQL
can handle the query - and that you can type all of them without error.
Note that if you use a UNION query as you suggest above, you will get
the last login FOR EACH DAY - not the overall last loging.
- MySQL will only allow you to join 32 tables in a single SQL statement.
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: how to make question that check the last hour

[Ian Sales (DBA)]

Jesper Andersson wrote:
Hello,
I relly new with databases and writing sql-questions. 

But in my db want I to check what have new rows have come the last hour.
the db have I as follow:
ID  email created   updated 
001 [EMAIL PROTECTED]  20050215131034   20050215133401
063 [EMAIL PROTECTED]  20050215141034   20050215141201
76  [EMAIL PROTECTED]  20050215134500   20050215134556

Now I would like to make a sql-question that show which new users have come the 
last hour, without that I need to edit the question each time I want to ask.
Please help!
 

- try using date_format and now(). See
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| ebuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL as Email database [sort of OT]

[Ian Sales (DBA)]

Brent Baisley wrote:
I am looking to store all incoming emails into a MySQL database. I've 
looked into dbmail, but can't get it to compile under Mac OSX (I 
posted a message on that list). I was wondering if anyone could point 
me in another direction to use MySQL as an email message store. I 
don't need a webmail interface, just a way of getting messages from a 
mail server to a MySQL database. Preferably as a direct transfer, but 
it could be a script that runs periodically.
Currently it seems the best path is using Perl, but I would think this 
has been done before, just can't find it on google.
- have a look at http://atmail.com/index.ehtml It's a web-based email 
system that uses a MySQL database as a back-end. It's written mostly in 
Perl, and there might be something you can use. The db schema, however, 
is appalling...

- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Difference between 'LIKE' and '='

[Ian Sales (DBA)]

Joshua Beall wrote:
Hi All,
From what I understand the LIKE keyword allows you to do things like include 
wildcards and match against regular expressions.
But I have a question: is there any difference between the following?
SELECT lname, fname FROM contacts WHERE lname = 'smith';
SELECT lname, fname FROM contacts WHERE lname LIKE 'smith';
I'm having difficulty figuring out the difference between these two queries, 
if there is any.  Can someone clarify?

 

- er, there is no difference as you've written it. But if you use 
wildcards, you get a very different set of results:

SELECT lname, fname FROM contacts WHERE lname = 'smith';
... will bring back all rows where lname is exactly 'smith'
SELECT lname, fname FROM contacts WHERE lname LIKE '%smith%';
... will bring back all rows where lname contains the letters 's m i t 
h', in that order, such as Smithfield, Aerosmith, Nasmith, Smithsonian...

- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Syntax for Compound "IF" Statements?

[Ian Sales (DBA)]

Harald Fuchs wrote:
That's correct, but it can be written shorter and clearer:
CASE Lccation
WHEN 1 THEN 'Downstairs Cat Room'
WHEN 2 THEN 'Kitten Room'
WHEN 3 THEN 'Quarantine'
ELSE 'Unknown' END AS Location
 

- surely it would be better to have the location ids and location names 
in a lookup table, and simply write the query as a join between the 
animals table and the locations table? Hard-coding the meaning of ids 
into the code itself is never a good idea.

- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Load Balancing w/ Alteons...Half Open Connections

[Ian Sales (DBA)]

Kevin A. Burton wrote:
Jason J. W. Williams wrote:
Has anyone ever had a problem with Alteon load balancers leaving the
MySQL connections half open? After about a minute of heavy use the
Alteon has completely DoS'd our MySQL servers. I know we must be doing
something wrong...just not sure what. Any help is greatly appreciated!
 

Define DoS?

- Denial of Service...
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: A problem of structure

[Ian Sales (DBA)]

Pupeno wrote:
What I don't like about that, is that half the person_id fields would 
be empty

and half the group_id fields would be empty. I was thinking about:
SONG-TO-PERSON-OR-GROUP LINK TABLE
* song_id
* musician_id
* type ('person', 'group')
* role (performer, lyricist, etc.)
But this is the solution that has so many redundant data. It's not very clear, 
but it's there.
 

- and conversely, I don't like the idea of column that can join to 
either of two tables depending on the value of a switch (the type 
column) :-)

- perhaps using separate columns in the link for each role might work... 
performer_group_id, performer_person_id, lyricist_person_id, etc. There 
will be one row per recording of a song, although some columns on that 
row may be empty.

- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: A problem of structure

[Ian Sales (DBA)]

Pupeno wrote:
Now that I'm thinking about a fourth solution: Have two totally separate 
tables for groups and persons (this is what I really like) and then, one 
table to relate songs to persons, and another table to relate songs to 
groups. The problem with that is that, sometimes, I need to get all the 
musicians that are related to a song, including both, persons and groups, to 
just list them, BUT, with some identification if it's a person or a group, so 
in the listing I can do some exceptions.

 

- how about...
- 6 tables:
PERSON
* person_id
* name
GROUP
* group_id
* name
PERSON-TO-GROUP LINK TABLE
* person_id
* group_id
ALBUM
* album_id
* title
SONG
* song_id
* title
* album_id
SONG-TO-PERSON-OR-GROUP LINK TABLE
* song_id
* person_id
* group_id
* role (performer, lyricist, etc.)
- left joins across song-to-person-or-group and person and group will 
return whichever is the case.

- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mydb-bin.xxx files

[Ian Sales (DBA)]

Philippe de Rochambeau wrote:
my /var/lib/mysql directory contains many -bin.xxx files, some of 
which date back to October 2004. What exactly are these files? Can I 
safely remove the older ones?

- these are the bin logs, used in replication. MySQL writes all INSERT, 
UPDATE, etc., and DDL commands to the bin log, and the slave reads them 
from there. If you're not using replication, then you can happily remove 
all the files except the last one. If you are, you need to check which 
bin log the slave is currently reading (use SHOW SLAVE STATUS), and only 
remove the files before that one.

- to remove the bin logs:
PURGE MASTER LOGS TO 'filename';   (where filename is the 
name of the last bin log to keep)

- if you no longer required bin-logging enabled, you must comment out 
the bin-log line in your my.cnf

- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slackware10 & mysql

[Ian Sales (DBA)]

Mohsen Pahlevanzadeh wrote:
Dears,I have slackware10.I install it completely.
But when i use /etc/rc.d/rc.mysqld start  ,I recieve following mesage :
mysql ended
Please guide me that i can configure mysql in slack.
Yours,Mohsen
 

- look in the .err file (probably located in your data directory) to see 
what caused mysqld to die.

- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Complicated Query

[Ian Sales (DBA)]

Ron Watson wrote:
This works, but only if a title and a role exists for the company member.
Basically, I want to display the season name from tbl Season, then the show
title from tbl Shows, then the roles from cast and titles from
production_team for the company member. 

 

- use LEFT JOINs.
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't drop index.

[Ian Sales (DBA)]

sam wun wrote:
Hi,
I created an index on a foreign in a table before. I need to drop this 
index. but I got the following error:
mysql> alter table transaction drop index prodcode;
ERROR 1025 (HY000): Error on rename of './datacube/#sql-30e8_3' to 
'./datacube/transaction' (errno: 150)
- you need to drop the foreign key constraint before you can drop the 
index on the column. Foreign keys must be explicitly indexed in MySQL... 
which means the reverse also holds true. Use SHOW CREATE TABLE to 
discover the name of the foreign key constraint. Alternatively, you can 
use SET FOREIGN_KEY_CHECKS = 0 to turn off referentiality checking.

- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  "If your DBA is busy all the time... |
|   ...he's not doing his job properly" |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]