Re: questions about timestamps and DST

[Mark Goodge]

On 31/03/2015 12:20, Larry Martell wrote:

On Tue, Mar 31, 2015 at 1:13 AM, Andrew Moore eroomy...@gmail.com wrote:

When you use a timezone with DST there is no such thing as 2.30am on the
date of changeover. That hour doesn't exist.


I am using UCT - I am not using a timezone.


In MySQL 5 and above, TIMESTAMP values are converted from the local 
server time to UTC at storage and then back again at select. So if the 
local server is using a DST timezone, then your TIMESTAMP value will 
always reflect local DST.



Look up the difference between timestamp and datetime data types.


I did do that before I posted, but it wasn't really clear to me, but I
think I need to use a DATETIME instead of a TIMESTAMP. Correct?


Yes.

As a highly-rated comment on StackOverflow puts it:

Timestamps in MySQL generally used to track changes to records, and are 
often updated every time the record is changed. If you want to store a 
specific value you should use a datetime field.


http://stackoverflow.com/questions/409286/datetime-vs-timestamp

As a more general rule of thumb, use DATETIME unless you have a specific 
application for which you know that TIMESTAMP is more appropriate. 
They're not interchangeable, and not intended to be.


Mark
--
http://www.markgoodge.uk

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

Re: forum vs email

[Mark Goodge]

On 10/12/2014 23:40, Reindl Harald wrote:


Am 10.12.2014 um 18:38 schrieb h...@tbbs.net:

2014/12/10 09:00 +0100, Johan De Meersman 

 One of the (for me, at least) defining features of a forum, is
that the subjects tend to be divided up into a tree structure, which
has it's own benefits 

Something more sophisticated than grouping messages by trimmed
subject-lines?
maybe involving such header lines as were used in the old netnews (if
e-mail is part of it)?


every sane MUA supports threading
see attached screenshot


Indeed. That, to me, is one of the key arguments in favour of a mailing 
list: people can choose how to view the list according to their own 
preference (some like it threaded, others prefer a flat view based 
simply on message date). Other arguments in favour of email include:


* Email is a push medium. I don't have to continually re-check a website 
to see if there's any new messages, they simply arrive in my list 
mailbox and I view them at my convenience.


* Individual emails can be forwarded and/or saved independently of the 
others.


* Email gives me a local archive of messages in addition to any central 
archive.


having said that, I think that web-based archives of mailing lists can 
be very useful, particularly for a public list where the archive is open 
to search engines. That makes them a valuable historical resource as 
well as merely a for-the-moment discussion forum. And, if you're going 
to have a web-based archive, it isn't a huge step from there to add the 
ability to post to the list via the web as well. That can be helpful for 
people on corporate email systems who don't easily have the ability to 
subscribe to a list (or filter mail from it into a separate folder), as 
well as people who only need to contribute very infrequently and don't 
want to have to subscribe in order to do so. But all this should, IMO, 
be in addition to the core features of an email mailing list, rather 
than a replacement for them.


Mark

--
http://www.markgoodge.uk

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

column aliases in query

[Mark Haney]

I know it's been a while since I wrote serious queries, but I'm sure I 
have done something like this before:


SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, 
machine.factory_id FROM events JOIN machine ON events.mach_id = 
machine.mach_id WHERE machine.factory_id = 1 AND vDate = 2012-10-11


Where I've aliased the SUBSTR of the date and then used the alias in the 
WHERE clause of the query.  I'm getting an error message now, but I'm 
almost certain I've used that syntax before.  Am I missing something?


--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux

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

Re: Need Help Converting Character Sets

[Mark Phillips]

Thanks to all of you for your very helpful suggestions! I was pulled off on
a different project for a few days, and I am now just getting back to this
one. Sorry for my absence.

First, some more information:

My table definition:
CREATE TABLE `Articles` (
`articleID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL DEFAULT '',
`author` varchar(200) NOT NULL DEFAULT '',
`body` text NOT NULL,
`intro` text NOT NULL,
`caption` text NOT NULL,
`credits` text NOT NULL,
`articleDate` date NOT NULL DEFAULT '-00-00',
`imageTitle` varchar(255) NOT NULL DEFAULT '',
`imageAltText` varchar(255) NOT NULL DEFAULT '',
`imageWidth` float NOT NULL DEFAULT '1',
`imageHeight` float NOT NULL DEFAULT '1',
`imageFile` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`articleID`),
FULLTEXT KEY `search1` (`title`,`author`,`body`,`caption`,`credits`)
) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=latin1

First problem - CHARSET should be utf8.

The data for this table comes from a web page (charet utf8). I copy/paste
word files into gedit (on linux) and then copy/paste from gedit to a text
boxes on the web page input form. I had thought I was stripping out all the
funky characters by using a simple ascii editor like gedit, but obviously
not.

After looking at the mysqldump for the table in a hex editor, I discovered
I have these characters scatter throughout the body and intro columns:
#8220;
#8221;
#8217;
#8212;
#8230;
#8617;

I tried converting the columns into utf8 with the command
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;
but all the special characters are still there.

I tried converting to blob and back to utf8, and that didn't change
anything. (I had to first drop the fulltext key to convert to blob).
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
The above special characters are still in the body column when I view the
dump file with a hex editor.

Is there a way to replace these special characters with the appropriate
utf8 characters (eg #8220; - 22 hex) within the text columns body and
intro columns?

Thanks,

Mark

On Fri, Sep 28, 2012 at 7:59 AM, Rick James rja...@yahoo-inc.com wrote:

 Thanks for that link!  That's another subtle issue I had not noted.

 There are so many combinations, that it is hard to say do this:
 * Incoming bytes are latin1 / utf8 / Microsquish control characters.
 * You do/don't have SET NAMES (or equivalent)
 * The database/table/column is declared latin1/utf8/other.
 * The problem is on ingestion / on retrieval.

 The thing mentioned involved 2 steps:
 ALTER TABLE ... MODIFY COLUMN  BINARY (or BLOB);  -- to forget any charset
 knowledge
 ALTER TABLE ... MODIFY COLUMN  CHARACTER SET ...;  -- coming from BINARY,
 this does not check the encoding.
 (sorry, don't have the link handy)

  -Original Message-
  From: h...@tbbs.net [mailto:h...@tbbs.net]
  Sent: Thursday, September 27, 2012 2:24 PM
  To: Mark Phillips
  Cc: Mysql List
  Subject: Re: Need Help Converting Character Sets
 
   2012/09/24 16:28 -0700, Mark Phillips 
  I have a table, Articles, of news articles (in English) with three text
  columns for the intro, body, and caption. The data came from a web
  page, and the content was cut and pasted from other sources. I am
  finding that there are some non utf-8 characters in these three text
  columns. I would like to (1) convert these text fields to be strict
  utf-8 and then (2) fix the input page to keep all new submissions utf-
  8.
 
  91) For the first step, fixing the current database, I tried:
 
  update Articles set body = CONVERT(body USING ASCII);
 
  However, when I checked one of the articles I found an apostrophe had
  been converted into a question mark. (FWIW, the apostrophe was one of
  those offending non utf-8 characters):
 
  Before conversion: I stepped into the observatory?s control room ...
 
  After conversion: I stepped into the observatory?s control room...
 
  Is there a better way to accomplish my first goal, without reading each
  article and manually making the changes?
  
  I do not remember where on the MySQL website this is, but there was an
  article about converting from character sets in version 4 to those in
  version 5, when UTF-8 first was supported. It sounds to me that maybe
  the tricks shown there would be useful to you, since, in effect,
  through MySQL MySQL was fooled into accepting for UTF-8 that which was
  not. Conversion to binary string was mentioned.
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql

Need Help Converting Character Sets

[Mark Phillips]

I have a table, Articles, of news articles (in English) with three text
columns for the intro, body, and caption. The data came from a web page,
and the content was cut and pasted from other sources. I am finding that
there are some non utf-8 characters in these three text columns. I would
like to (1) convert these text fields to be strict utf-8 and then (2) fix
the input page to keep all new submissions utf-8.

91) For the first step, fixing the current database, I tried:

update Articles set body = CONVERT(body USING ASCII);

However, when I checked one of the articles I found an apostrophe had been
converted into a question mark. (FWIW, the apostrophe was one of those
offending non utf-8 characters):

Before conversion: I stepped into the observatory’s control room ...

After conversion: I stepped into the observatory?s control room...

Is there a better way to accomplish my first goal, without reading each
article and manually making the changes?

(2) For the second goal, insuring that all future articles are utf-8, do I
need to change the table structure or the insert query to insure I get the
correct utf-8 characters into the database?

Thanks,

Mark

InnoDB vs. other storage engines

[Mark Haney]

I hope this doesn't end in some kind of flame war.  I'm looking to 
optimize my tables (and performance in general) of the DB my web app is 
using.  I'm tweaking things a little at a time, but I'm curious as to 
what the rest of the MySQL list thinks about changing my storage engine 
from InnoDB to something else so I can optimize the tables on a regular 
basis.


Is it worth the effort?  Any caveats?

I've never really encountered this situation before and I'm curious to 
see what others have to say on it.


Thanks in advance.

--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux

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

Re: Create a VIEW with nested SQL

[Mark Haney]

On 09/06/2012 10:23 AM, h...@tbbs.net wrote:

How about

SELECT lights.*, machine.mach_name
FROM lights JOIN machine USING (mach_id)
/* ORDER BY date DESC */
GROUP BY mach_id

? With USING the fields mach_id from lights and machine become one unambiguous 
field mach_id.

Does mach_id really occur more times in lights or machine? If only once in both 
tables, GROUP BY at most orders it. It is not clear that ORDER BY does anything with GROUP BY outside it, unless 
mach_id is unique in both tables--I have found that GROUP BY not always orders the output, when everything 
is unique.

And yes, MySQL balks at saving a view with a query for a table. One has to make 
them separate views.




Now that's a syntax I've never seen before.  Then again, I haven't done 
any serious SQL in about 5 years, so I shouldn't be surprised.  If I 
read your question correctly about mach_id, the mach_id is precisely 
what it's name implies, the identifier of a particular machine.  It's 
unique to that machine, but it occurs many times in the lights table, as 
it is the table my monitoring system inserts data. I have them ORDERed 
BY date DESC to make sure I get the most recent status of EACH machine 
at the top of the list like this:


mach1,2010-09-01 10:00:00,running
mach2,2010-09-01 09:59:51,running
mach3

etc.

That's also what the GROUP BY is for, to group all the entries by 
machine ID, order them first, then group them.  Honestly, the ORDER BY 
may not be needed other than for getting them listed in 'numerical' 
order.  That's certainly not necessarily a requirement at the moment.


(And getting me to think about the grouping and ordering part of the 
query makes me stop and rethink the logic behind the query.  So thanks 
for that.  My SQL brain is still fuzzy, and combined with Vicodin, I'm 
no House.  I can't function 100% on pain meds.


I'll take a look at this, and if I need any help, I'll holler, but this 
looks really good at the moment.


Thanks.


--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux

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

Re: Create a VIEW with nested SQL

[Mark Haney]

On 09/06/2012 10:23 AM, h...@tbbs.net wrote:


SELECT lights.*, machine.mach_name
FROM lights JOIN machine USING (mach_id)
/* ORDER BY date DESC */
GROUP BY mach_id

? With USING the fields mach_id from lights and machine become one unambiguous 
field mach_id.

Does mach_id really occur more times in lights or machine? If only once in both 
tables, GROUP BY at most orders it. It is not clear that ORDER BY does anything with GROUP BY outside it, unless 
mach_id is unique in both tables--I have found that GROUP BY not always orders the output, when everything 
is unique.

And yes, MySQL balks at saving a view with a query for a table. One has to make 
them separate views.




The problem I encountered is that I can't find a way to just pull the 
most recent records for each machine without the GROUP BY statement. 
That's all I need.


Okay, so here's what I tried.  I created a view vLights from:



SELECT MAX(lights.date),machine.mach_name FROM lights JOIN machine USING 
(mach_id) GROUP BY mach_name;


Now, as I use the query by itself I get this:


mysql SELECT MAX(lights.date),machine.mach_name FROM lights JOIN machine USING 
(mach_id) GROUP BY mach_name;
+-+---+
| MAX(lights.date)| mach_name |
+-+---+
| 2012-09-07 09:03:10 | #10   |
| 2012-09-07 09:03:07 | #12   |
+-+---+
2 rows in set (3.62 sec)


This is better than the 20s+ I was getting before, but still not 
acceptable for only 2 machines when I'll have 40+ at production time.


All I need is the most recent record for EACH machine ID, THEN to pull 
the machine name from the table that has the name in it.


Somehow I'm missing something incredibly obvious here.

--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux

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

Create a VIEW with nested SQL

[Mark Haney]

I have a bit of a performance/best practice question for those in the 
know.  I have a nested SQL statement that selects fields from a SELECT 
that has a JOIN in it. Here's the SQL:



SELECT vLight.* FROM
(SELECT lights.*, machine.mach_name from lights
JOIN machine ON lights.mach_id = machine.mach_id
ORDER BY date DESC) as vLight
GROUP BY mach_id


Now, it's been a while for me to craft a complex SQL statement, so if 
there is a better way, that's great. However, that's not really the issue.


I'm having a performance issue with this query because I'm using it to 
pull data from the DB (read only) every 5 seconds or so to display 
status lights from machines.


My thought was to make this a VIEW to see if that made a difference in 
speed, but when I went to create it mySQL choked with an error about the 
VIEW being built from a SELECT inside the SELECT.  I googled a couple of 
answers that moved the JOIN so it wouldn't be a nested SQL, and I 
thought maybe I could build the initial SELECT (the internal one) as a 
VIEW, than query that VIEW with the initial statement.


Then, of course, I realized that maybe none of this will give me the 
boost I need, so I decided, in my vicodin soaked brain (I have a torn 
rotator cuff and tendon in my shoulder) that I should hit the list 
before I go any farther.


So, what's the best way, or the most common way to deal with this issue. 
I'll be happy to clarify anything in here that doesn't make sense.


Thanks in advance.


--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux

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

Unique index - opinions sought

[Mark Goodge]

I have a MySQL table (call it, say, item_spine) which contains three 
fields which, together, form a unique key. These three fields are a 
guid, a start date and an end date. The guid is alphanumeric (a 
fixed-length six characters) and the dates are ISO format dates 
(-MM-DD).


I also have another table (item_detail) containing data which is keyed 
to the first in that the unique key here is the unique key from 
item_spine plus a line id (which is numeric).


At the moment, I simply have the three fields in the item_spine set as a 
unique key, and replicate those three columns in item_detail and have 
those plus line_id as the unique key, thus making a four-column key.


But, for performance reasons, I was wondering if it might make more 
sense to create a single column in item_spine containing data which is 
generated from the original three and use that as a unique key instead. 
I then only need a single column in item_detail to link to item_spine, 
and thus my unique key there can be only two columns.


Another option is to have an autoincrement column as a primary key for 
item_spine, and then use that as the link key for item_detail. But I'd 
prefer to avoid that, because the content of item_spine has to be 
updated on a regular basis from external data and using autoincrement 
means I can't do that using REPLACE INTO while still maintaining a key 
association with item_detail.


Any thoughts? How would you do it?

Mark
--
 Sent from my Turing-Flowers Colossus
 http://mark.goodge.co.uk


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

Re: Unique index - opinions sought

[Mark Goodge]

On 16/07/2012 17:39, Rick James wrote:

How many rows?  If 1K, it does not matter.   If 1 billion, we need to
discuss in more detail.  Let's assume 1M...


Around 1M in the item_spine table and 10M in item_detail.


Dates should be stored in DATE datatype, which is 3 bytes. Your GUID
is non-standard, but should probably be stored in CHAR(6) CHARACTER
SET ascii, unless it is expected to have non-ascii characters.  Is
case folding important? Given those, the PRIMARY KEY is 6+3+3=12
bytes long.  This is not bad for a million-row table.  If bigger,
then the AUTO_INCREMENT should be considered.


The guid is case-insensitive. ISO dates map directly to MySQL's internal 
DATE type, so that's already taken care of. All data is ascii, and all 
alpha data is not case-sensitive.


I should maybe have mentioned earlier that this is external data over 
which I have no control (but do have a specification which I expect to 
be honoured).  My task is to store it and make it searchable for display.



Is this InnoDB or MyISAM?  (Please provide SHOW CREATE TABLE, not
English, for describing tables.)


It's MyISAM. I don't actually have a CREATE yet, as this is still just 
hypothetical :-)



Let's see the SELECTs that will be hitting the tables.  Then we can
discuss in more detail.


A typical select would be something like this:

SELECT guid
FROM item_spine
WHERE start_date = NOW()
AND end_date = NOW()
AND location_code = '$query'

followed by

SELECT *
FROM item_detail
WHERE guid = '$guid'
AND start_date = NOW()
AND end_date = NOW()
ORDER BY sequence

where $query is the initial query from the user and $guid is the result 
of the first query. location_code is VARCHAR(10) and is an alphanumeric 
string.


(I'm avoiding joins because doing multiple selects in the code is 
usually much faster)


Mark

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

Re: Trying to compile mysql 5.5 on Ubuntu 12.04

[Mark Haney]

On 06/27/2012 09:21 PM, Travis Briggs wrote:



I've seen http://bugs.launchpad.net/codership-mysql/+bug/890982 with the
same error in the trace, with the comment from ayurchen:

This leads me to conclude that compile-pentium64 script and its

derivatives is genuinely broken in MySQL and compile-amd64-* should be used
instead.

Is that true? Am I doing something wrong?

Thanks,
-Travis




I wouldn't think using compile-amd64 would be a problem.  IIRC, 
compiling for Intel would presumably enable certain compile options for 
intel chips.  Same for AMD.  However, I've compiled using both and 
honestly haven't found any difference.  There may be some difference at 
really like transaction levels, but my DBs run pretty much the same 
compiled with either.


YMMV.

--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux 3.4.2-1.fc16.x86_64 GNU/Linux



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

Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format

[Mark Kelly]

Hi.

On Friday 18 May 2012 18:21:07 Daevid Vincent wrote:

 Actually, I may have figured it out. Is there a better way to do this?

I don't see why you need the dvds table when the dvd_id is in the scene table:

SELECT a.dvd_id 
FROM scenes_list a, moviefiles b 
WHERE a.scene_id = b.scene_id 
AND b.format_id = '13';

or am I misunderstanding something?

Cheers,

Mark


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

Re: Postal code searching

[Mark Goodge]

On 24/04/2012 17:24, Tompkins Neil wrote:

How about if I want to only return postal codes that are like W1U 8JE
not W13 0SU.

Because in this example I have W1 as the postal code and W13 is the other
postal code


No, you don't. In this example you have W1U as one outbound code and W13 
as the other.


W1U postcodes are not a subset of W1 postcodes, any more than IP27 
postcodes are a subset of IP2 postcodes. The fact that in W1U the 
district segment is in the form of NA rather than NN doesn't change the 
fact that it's an indivisible two-character code.


So I think the first question has to be, why do you want to get W1 as a 
particular substring from the postcode W1U 8JE?


British postcodes have a structure which is easy for humans to 
understand, although (unfortunately) rather hard to parse automatically. 
Essentially, every full postcode contains four elements:


Area code: one or two alpha characters, either A or AA
District code: one or two alphanumeric characters the first of which is 
always numeric, either N, NN or NA

Sector code: single numeric character, always N
Walk code: two alpha characters, always AA

It's customary, but not part of the formal specification, to insert 
whitespace between the District and Sector codes.


So, given the postcode WC1H 8EJ, we have:

Area: WC
District: 1H
Sector: 8
Walk: EJ

Taken together, the first two sections form the outbound part of the 
postcode, and the second two form the inbound. (That is, the first two 
identify the destination sorting depot that the originating depot will 
send the post to, and the second two are used by the destination depot 
to make the actual delivery).


The reason for mentioning this is that postcodes, having a wide range of 
possible formats, are not easy to handle with simple substring searches 
if you're trying to extract outbound codes from a full postcode. It can 
be done with regular expressions, but you have to be wary of assuming 
that the space between District and Sector will always be present as, 
particularly if you're getting data from user input, it might not be.


In my own experience (which is quite extensive, as I've done a lot of 
work with systems, such as online retail, which use postcodes as a key 
part of the data), I've always found it simpler to pre-process the 
postcodes prior to inserting them into the database in order to ensure 
they have a consistent format (eg, inserting a space if none exists). 
That then makes it easy to select an outbound code, as you can use the 
space as a boundary. But if you want to be able to go further up the 
tree and select area codes (eg, distinguishing between EC, WC and W) 
then it's harder, as you have to account for the fact that some are two 
characters and some are only one. You can do it with a regular 
expression, taking everything prior to the first digit, but it's a lot 
easier in this case to extract the area code prior to inserting the data 
into the database and store the area code in a separate column.


Mark
--
 Sent from my ZX Spectrum HD
 http://mark.goodge.co.uk

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

OT: SQL Question

[Mark Phillips]

My question is not specific to MySQL, even though I am using a MySQL db for
this project. I have a servlet/jsp/MySQL web site in production, and there
are about 2,000 records in the flights table. One of the foreign keys is
teacher_id. Up to this point, there is a one to many relationship between
teacher_id and the data in the flights table. I need to change the data
model to allow for a many to many relationship between teacher_id and the
data in the flight table. What is the best way to do this?

Thanks,

Mark

Re: One inst has 39 columns- the other 40

[Mark Goodge]

On 22/03/2012 04:41, Brown, Charles wrote:

Look man, there has to be someone out there that can tell me why one
user table has 5 extra columns.


As far as I can see, plenty of people already have told you.


Is it version related or my sysprog person missed out on a step.


Yes, it's related to your version.

You probably have different versions of MySQL on different nodes of the
cluster.

Your sysprog person has missed out the step of ensuring that all nodes
are upgraded at the same time in order to maintain consistency.


Its hard to believe that this problem is unique to my site.


No, the problem is not unique to your site. It's an easy mistake to make.

Please could you now give some indication of having read and understood
this reply.

Mark
--
 Sent from my Babbage Difference Engine 2
 http://mark.goodge.co.uk

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

errors running WHILE loop

[Mark Haney]

I've had a DEVIL of a time with this problem and I'm sure it's something 
simple, but I can't find it anywhere in the documentation or online what the 
problem is.  Let me explain.

I cannot get any WHILE loop to work from either a CLI or a script on MySQL 
5.1.41.  For example, this is a simple script that sets a variable, and loops 
through 10 interations and prints the SQL version each time.  It's a silly 
script, but this is what I'm getting:

set @v1 = 0; while @v1  10 do; use mysql; select VERSION(); set @v1 = @v1 + 1;
Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'while @v1  10 do' at line 1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+-+
| VERSION()   |
+-+
| 5.1.41-3ubuntu12.10 |
+-+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Here's where I'm stuck.  In some cases, I've seen where END WHILE is used, or a 
BEGIN/END block inside the WHILE, and even outside the WHILE.  I'm at a 
complete loss as to WTF is going on.  The documentation tells me little.  In 
fact the test example in the MySQL reference manual online for 5.1 bombs as 
well:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1  0 DO
USE mysql;
SELECT VERSION();
SET v1 = v1 - 1;
  END WHILE;
END;

mysql source ~/dowhile.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near '' at 
line 3
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'WHILE v1  0 DO
USE mysql' at line 1
+-+
| VERSION()   |
+-+
| 5.1.41-3ubuntu12.10 |
+-+
1 row in set (0.00 sec)

ERROR 1193 (HY000): Unknown system variable 'v1'
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'END 
WHILE' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'END' 
at line 1
mysql

So, what the heck is going on here?  I'm at the end of my wits.

Re: In case you all missed it.

[Mark Goodge]

On 11/11/2011 16:29, Curtis Maurand wrote:



mysql  select date_format(now(),'%m-%d%-%y
%h:%i:%s') AS time;
+---+
|
time
|
+---+
| 11-11-11 11:11:11 |
+---+
1 row in set (0.00 sec)


Actually, it should be select date_format(now(),'%y-%m%-%d
%h:%i:%s') :-)

Mark
--
 Sent from my Babbage Difference Engine
 http://mark.goodge.co.uk
 http://www.ratemysupermarket.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

One database per server architecture

[Mark, Ryan]

Architecture question I'm having trouble finding an answer to:

I run four WordPress websites. I have mysql setup in a write master/read 
replica slave configuration on Amazon. There is one master that all the 
WordPress instances write to. I'm trying to figure out how to setup the read 
replicas.

Should I:

A. Give each WordPress instance it's own read replica?

or

B. Have all the WordPress instances use all the read replicas?

I have done A before, and it works fine. But by going with B I can consolidate, 
use beefier hardware and save money.

Any thoughts?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: mysql server does not recognize user password

[Mark]

Did you issue a 'FLUSH PRIVILEGES;' before quitting the mysql session?

- Mark



-Original Message-
From: Tim Johnson [mailto:t...@akwebsoft.com] 
Sent: woensdag 19 oktober 2011 1:02
To: MySQL ML
Subject: mysql server does not recognize user password

using 5.1.57 on Mac Lion.
blush I've done this a dozen times, but I've missed something.
Am setting up a new mysql installation ...
I have granted a user as follows (between lines of asterisks)

linus:~ tim$ sudo mysql
Password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.1.57 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software, and
you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

mysql use mysql;
Reading table information for completion of table and column names You can
turn off this feature to get a quicker startup with -A

Database changed
mysql GRANT ALL PRIVILEGES ON *.* TO 'tim'@'localhost' IDENTIFIED BY 
mysql 'secret'  WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql quit;
Bye

Now when I try to log in with host as localhost, user as tim with 'secret'
password:
linus:~ tim$ mysql --host=localhost --user=tim --password=secret ERROR 1045
(28000): Access denied for user 'tim'@'localhost' (using password: NO) Huh!
If I login into the server as root again:
linus:~ tim$ sudo mysql
mysql show grants for tim@localhost;
+---
+
| Grants for tim@localhost
|
+---
+
| GRANT ALL PRIVILEGES ON *.* TO 'tim'@'localhost' IDENTIFIED BY 
| PASSWORD '*E8482E479FD05E800263C26A724513BBBFAA108B' WITH GRANT OPTION 
| |
+---
+
1 row in set (0.00 sec)
It appears that the user is there.
Is there a difference in the syntax with this version for providing the
password?
If I try
mysql -h localhost -u tim -p
I get
ERROR 1045 (28000): Access denied for user 'tim'@'localhost' (using
password: NO) just as in the example above.
Have I forgotten to do something in the setup?

TIA
--
Tim
tim at tee jay forty nine dot com or akwebsoft dot com
http://www.akwebsoft.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=ad...@asarian-host.net


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: One database per server architecture

[Mark, Ryan]

Thanks guys!

I figured there would be no clearcut answer. I was curious if there were any 
nuggets of wisdom or rules of thumb I was overlooking.

We initially launched sharing db servers, but had performance trouble. We've 
since tracked down problems in our app server config (WordPress does not like 
to share an app server) and added memcache. So I think we're ripe for another 
try.

Thanks again,
Ryan

On Oct 18, 2011, at 5:39 PM, Lydia Rowe wrote:

 You have answered your own question, good sir. Or so I have come to believe. 
 Is your primary concern $? Is your organization focused on the bottom line? 
 Option B allows you to move in the costsaving direction. I imagine some may 
 object to sharing resources between (potentially exploitable) WordPress 
 installations but hey, so goes the show, as they say around these parts here!
 
 --
 Howdy,
 
 Lydia
 
 On Oct 18, 2011 5:19 PM, Mark, Ryan rm...@tribune.com wrote:
 Architecture question I'm having trouble finding an answer to:
 
 I run four WordPress websites. I have mysql setup in a write master/read 
 replica slave configuration on Amazon. There is one master that all the 
 WordPress instances write to. I'm trying to figure out how to setup the read 
 replicas.
 
 Should I:
 
 A. Give each WordPress instance it's own read replica?
 
 or
 
 B. Have all the WordPress instances use all the read replicas?
 
 I have done A before, and it works fine. But by going with B I can 
 consolidate, use beefier hardware and save money.
 
 Any thoughts?
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=ly...@lydiarowe.com
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Databasename/Tablename is marked as crashed and should be repaired

[Mark Goodge]

On 14/10/2011 08:07, James wrote:

Hello,

I have the following error on my mysql server log and managed to repaired
the broken table. However, it keeps occurring by time to time. I am using
MyISAM storage engine to all database and having some locking table which I
know / aware about the disadvantage of MyISAM.

'./Databasename/Tablename' is marked as crashed and should be repaired

Are there any ways to solved permanently? Any advise would be appreciated.


If it's happening repeatedly, and the MySQL server itself is running 
without any problems (ie, it isn't crashing and restarting) then you may 
have problems with the hardware - with the disk itself.


Mark
--
 Sent from my Babbage Difference Engine
 http://mark.goodge.co.uk
 http://www.ratemysupermarket.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Doubt regarding Mysqlsump

[mark carson]

Hi

We use the --single-transaction switch thinking it does less locking or waiting
for a required table lock. You then get a snapshot without stopping.

Subject should have included the word 'hot'? Looking forward to other 
suggestions.

Mark

On 2011/06/07 08:00, Adarsh Sharma wrote:
 Dear all,
 
 Is it possible to take backups of a table or complete database without 
 stopping
 the application that continuously inserts and select data from the tables.
 
 For taking complete backup of a database I follow the below steps :-
 
 1. First stop the application that insert  modifies tables.
 2. Mysqldump command to backup the tables.
 3. After complete backup , start the application.
 
 I know Mysql-Replication helps a lot to solve this issue but I have not any
 extra server to configure it.
 
 So , Can I solve this issue without Replication so that I don't need to stop 
 my
 application  I must have consistent backups too.
 
 Please note that size of databases may be more than 100GB
 
 
 Thanks
 

-- 
Mark Carson
Managing
Integrated Product Intelligence CC (CK95/35630/23)
EMail : mcar...@ipi.co.za/ (secondary:mcar...@pixie.co.za)
Physical Address : 34 Spanish Galliard, Mooikloof, Pretoria, South Africa
snailmail : P.O. Box 36095 Menlo Park 0102, South Africa
Tel. +27 12 996 1193/1815 Fax : +27 86 672 7012 Cell : +27 83 260 8515


This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended
only for use of the addressee. If you are not the addressee, or the person
responsible for delivering it to the person addressed, you may not copy or
deliver this to anyone else. If you received this e-mail by mistake, please
do not make use of it, nor disclose it's contents to anyone. Thank you for
notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED
IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION
OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT
LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND
THE USE OF THIS DOCUMENT.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Allowing all users to access a specified database

[Mark Goodge]

Hi,

I have a database server with multiple users and multiple databases. I 
have a situation where I want to allow any user to connect to a 
specified database.


Unfortunately, the documentation has this to say:

MySQL does not support wildcards in user names.

Which means, that, although I can use this syntax to grant access for a 
user to all databases:


GRANT SELECT, INSERT, UPDATE, DELETE ON * TO 'someuser'@'%';

I can't do something like this to grant all users access to a database:

GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';

So, my question is this: How can I allow any user to use a specific 
database, without individually granting them all access?


The reason I want to be able to do this is that the server hosts a large 
number of individual ecommerce sites running on the same core software. 
Each site has its own database for products, customer data, etc, which 
is only accessible to that site. But each site also needs to be able to 
access a single, server-wide database which both stores configuration 
information for the software that the sites run on and store usage 
statistics for each site which can be accessed by the server admins 
without needing to have access to each site's own database.


Clues, anyone?

Mark
--
 Sent from my Babbage Difference Engine
 http://mark.goodge.co.uk
 http://www.ratemyairport.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Allowing all users to access a specified database

[Mark Goodge]

On 03/06/2011 11:24, John Daisley wrote:

The reason

*GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';*
*
*
does not work is because that command would be suicidal in terms of
security.

If you are hosting a large number of ecommerce sites and granting any user
access to those databases then you would want security to be far tighter.
Allowing that sort of access is about as secure as publishing the data on
facebook.


That's not a problem in this case - the data is *intended* to be shared 
between all users of the system. It's data required by the software that 
the sites run on - which is simple, non-confidential stuff like basic 
settings as well as data which each site deliberately exports for 
copying by the others. The end users are not different organisations, 
they are different trading divisions within the same organisation.



What version of MySQL are you using?


5.0.7

Mark
--
 Sent from my Babbage Difference Engine
 http://mark.goodge.co.uk
 http://www.ratemyairport.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: ` vs '

[Mark Goodge]

On 30/03/2011 09:05, Brent Clark wrote:

Hiya

Im wondering if someone could help me understand this. If you look at my
two queries below. By the ORDER BY one is using ` and the other ', as a
result, if you do an explain you will see that the top query does a
filesort, while the other does not.


Because column names either need to be unquoted or enclosed in 
backticks. If you put a string inside ordinary quotes (either single or 
double) then it's treated as a string variable. And you can't sort by a 
string variable.


As a demonstration, try these:

SELECT * FROM contacts LIMIT 10
SELECT * FROM `contacts` LIMIT 10
SELECT * FROM 'contacts' LIMIT 10

The first two will work. The third will fail, as you can't select from a 
variable.


Alternatively, try this:

SELECT id FROM contacts LIMIT 10
SELECT `id` FROM contacts LIMIT 10
SELECT 'id' FROM contacts LIMIT 10

and all will be even more clear :-)

Mark
--
 http://mark.goodge.co.uk
 http://www.ratemysupermarket.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: How to protect primary key value on a web page?

[Mark Kelly]

Hi.

On Thursday 10 Mar 2011 at 20:09 mos wrote:

[snip]

 Let's say I have a Document_Id column and the url is
 www.mydocuments.com/public?docid=4
 to retrieve document_id=4, I don't want someone to write a program to
 retrieve all of my public documents and download them. I want them to go
 through the user interface.

Leaving aside the silliness of making a document public then trying to stop 
people downloading it, there is at least one common solution available to you 
- Apache's mod_rewrite.

http://httpd.apache.org/docs/2.0/mod/mod_rewrite.html

Obviously this is dependant on you running Apache, but it is a simple and 
common approach that will give you what you want.

You could also consider rate-limiting your application so that users who 
request too many pages for your tastes (indicating a possible program) are 
deliberately slowed down. Beware that this solution will likely have a 
detrimental effect on search engine spiders, and therefore your site rankings.

However, neither of these solutions are appropriate for discussion on a MySQL 
mailing list, and I agree with many of the other responses you have had - your 
plan to do this by changing your database is pointless and misdirected.

Cheers,

Mark

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Auto-Increment Values in Mysql

[Mark Goodge]

On 09/02/2011 11:41, Adarsh Sharma wrote:

Dear all,


I have an auto-increment column in Mysql database table. Let's say the
column has below values :


1
2
3
4
5
6
7
8
9
10


Now if i deleted some rows where id= 3 ,5 and 8

The data look like as :

1
2
4
6
7
9
10

I want to have it id's as
1
2
3
4
5
6
7
and next data is inserted right at 8

Please help how to achieve it.


Firstly, if this matters to you then an autoincrementing value is 
probably not what you should be using in the first place. The main point 
of autoincrement is that it doesn't matter what the actual value is, 
it's just a way of achieving a unique key for the table. If you care 
what the values are, then just use an INT field and generate the numbers 
yourself.


That said, if you want to renumber an autoincrementing field then the 
simplest way is to drop it and re-add it:


ALTER TABLE `mytable` DROP `myfield`;

ALTER TABLE `mytable` ADD `myfield` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Mark
--
http://mark.goodge.co.uk
http://www.ratemysupermarket.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Mark Goodge]

On 24/01/2011 15:42, Jerry Schwartz wrote:

-Original Message-
From: Donovan Brooke [mailto:li...@euca.us]
Sent: Friday, January 21, 2011 7:28 PM
Cc: mysql@lists.mysql.com
Subject: Re: CURRENT insert ID

Just an idear..

Don't auto_increment the main table.. create a unique Id table,
auto_increment that, and grab that value first for use with both fields
in your main table.


[JS] I've thought of that, but it creates another problem.

Let's say I add a record to the ID table, thereby auto-incrementing its key.
Now I need to retrieve that key value. How do I do that while retaining some
semblance of data integrity? I'd have to do something like SELECT MAX(),
which fails to retrieve my value if someone else has inserted a record in
the meantime.


That's what LAST_INSERT_ID() is for:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

This is on a per-connection basis, so even if another connection inserts 
a line in the meantime your query will return the auto-increment value 
of the line you inserted.


Most programming languages with an interface to MySQL, either built-in 
or via a module, implement this natively. For example, in PHP:


mysql_query(insert into mytable set name = 'foo');
$id = mysql_insert_id();

the value of $id will be the auto-increment number from the line you 
just inserted.


Mark
--
http://mark.goodge.co.uk
http://www.ratemysupermarket.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Bulk Insertion Performance

[Mark Matthews]

On Dec 14, 2010, at 7:21 PM, Feris Thia wrote:

 Hi Mark,
 
 On Wed, Dec 15, 2010 at 8:10 AM, Mark Matthews mark.matth...@oracle.com 
 wrote:
 Feris,
 
 *How* are you writing, via batch statements with rewriting, or directly, or 
 via LOAD DATA INFILE? It seems you're off by about a factor of 10-20x from 
 what I've seen performance-wise for writes.
 
 I'm using ETL mean - for this case, it is a java application name Kettle 
 (Pentaho Data Integration). And it use JDBC connection. 
 
 Is it a JDBC driver configuration ?

Feris,

I don't know what Kettle is doing under the hood, but if it's doing addBatch(), 
executeBatch(), then adding rewriteBatchedStatements=true to your MySQL JDBC 
URL should probably help quite a bit.

-Mark
-- 
Mark Matthews
Principal Software Developer -  MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Bulk Insertion Performance

[Mark Matthews]

On Dec 14, 2010, at 6:51 PM, Feris Thia wrote:

 Hi All,
 
 I have a data warehouse infrastructure with following configuration :
 - MySQL 5.0 MyISAM + InndoDB enabled (XAMPP Distribution)
 - Windows 2003 64 bit data center edition
 - Java Runtime 6 - 32 bit version
 
 And have ETL running data warehouse process. Reading is impressive, 12,000
 rows per second. But writing with only 10 columns (integer and varchar
 combinations) takes 3,000 rows / second.
 
 Is there a way to configure writing to have a better performance ?

Feris,

*How* are you writing, via batch statements with rewriting, or directly, or via 
LOAD DATA INFILE? It seems you're off by about a factor of 10-20x from what 
I've seen performance-wise for writes.

-Mark
-- 
Mark Matthews
Principal Software Developer -  MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Lowest non-zero number

[Mark Goodge]

Given a table containing a range of INT values, is there any easy way to 
select from it the lowest non-zero number?


Obviously, MAX(column) will return the highest, but MIN(column) will 
return 0 if any row contains a 0, which isn't what I want.


Any clues?

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Lowest non-zero number

[Mark Goodge]

On 03/12/2010 16:56, Paul Halliday wrote:

On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodgem...@good-stuff.co.uk  wrote:

Given a table containing a range of INT values, is there any easy way to
select from it the lowest non-zero number?



SELECT number FROM table WHERE number  0 ORDER BY number ASC LIMIT 1;


Sorry, I should have said that I need to do this as part of a query 
which returns other data as well, including data from the rows which 
have a 0 in this column. So I can't exclude them with the WHERE clause.


What I'm actually doing is something like this:

SELECT
name,
AVG(score) as average,
count(score) as taken
FROM tests GROUP BY name

and I want to extend it to something like this:

SELECT
name,
AVG(score) as average,
COUNT(score) as attempts,
SUM(score = 0) as failed,
SUM(score  0) as passed,
MAX(score) as best_pass,
. as lowest_pass
FROM tests GROUP BY name

and I need an expression to use in there to get the lowest non-zero 
value as lowest_pass.


Does that make sense? And, if so, is there any easy way to do it?

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Possible causes of table crashing

[Mark Goodge]

Hi,

I have a very strange problem whereby one particular table in our 
database is repeatedly (on average, every couple of days) generating 
errors stating that the table is crashed and needs to be repaired. 
Running a repair fixes it.


What makes it strange (and something that I've never encountered before) 
is the following:


1. There is one particular table which it happens to a lot, and a few 
other tables where it happens occasionally. Everything else is fine.


2. None of the tables where it happens are among the most heavily used 
or commonly updated.


3. There is no shortage of disk space.

4. The disk system reports no errors.

5. We have multiple databases with an identical structure but different 
content (we are an online retailer and each database is a separate 
storefront), but the problem occurs in all the databases - but the same 
tables in each database. It doesn't always happen to each database at 
the same time - it seems to be random.


6. (This is the really strange one) We have two separate servers with 
identical copies of the databases, one for production use and one for 
development use, and it happens independently on both of them - but 
still the same tables (and, specifically, the same table that it happens 
to more often than any other).


7. And, of course, the obvious statement: we haven't changed the 
structure of this database recently (it hasn't changed for months, if 
not years).


We are running MySQL 5.0.77 on Centos. All the databases use MyISAM 
exclusively.


Given the above, can anyone suggest any possible causes?

Thanks

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: backfill results for the same month and year

[Mark Goodge]

On 11/11/2010 09:00, HaidarPesebe wrote:

Dear All,

I have the following data

ID CITY  QTY   MONTH YEAR
---
1  Chigago10 11   2010
1  NewYork   22 11   2010
1  London 54 11   2010
1  Chigago7  10   2010
1  NewYork   26 10   2010
1  London 33 10   2010

ID = ID PRODUCT.

How can I backfill results with the same ID by month and year, with results 
like this:

MONTH/YEARQTY TOTAL
---
11/201086
10/201066
and etc.


select ID, concat(MONTH,'/',YEAR) as MONTHYEAR, sum(QTY) as TOTAL
from MYTABLE
group by concat(ID,MONTH,YEAR)

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Order by in clause

[Mark Goodge]

Hi,

I have a query like this:

select id, title from product where id in (1,3,5,8,10)

What I want it to do is return the rows in the order specified in the 
in clause, so that this:


select * from product where id in (10,3,8,5,1)

will give me results in this order:

+--+-+
| id   | title   |
+--+-+
|  10  |foo  |
+--+-+
|   3  |baz  |
+--+-+
|   8  |bar  |
+--+-+
|   5  | wibble  |
+--+-+
|   1  | flirble |
+--+-+

Is this possible? If so, how?

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SELECT WHERE IN help

[Mark Goodge]

On 21/09/2010 16:44, Tompkins Neil wrote:

Thanks for the quick reply.  Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table.  However, because 3 exists twice
within  (3,4,5,6,7,3), I want it to return two records for record_id 3.  Is
it possible ?


No, that isn't possible.

Why do you want a duplicate record to be retrieved? There may be a 
better way of doing it.


Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Best method to keep totals

[Mark Goodge]

On 03/09/2010 16:32, Arthur Fuller wrote:

While I agree with the general take on this subject (Never store totals
without a good reason and where there is duplication there is the
opportunity for skew), I must say that there are exceptions. A couple of
years ago I worked on an inherited database in which the operant principle
was sum don't store; the problem was that many of the rows summed dated
back a year or two or more, and as an accountant friend of mine loved to
say, A paid transaction is history; an unpaid transaction is fiction.


The other exception is also where financial data is being stored. If you 
have, say, a database containing sales order records, then as well as 
storing the individual values of each item in each order, you also need 
to store the total value of the order, the total price charged to the 
customer and the total paid by the customer. These three should, of 
course, be not only identical to each other but also to the sum of the 
individual items, so there is not only duplication but the potential for 
skew. But that, of course, is precisely *why* you store them, as any 
discrepancy indicates an error which needs to be investigated.


Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Performing subtraction between fields

[Mark Goodge]

On 21/08/2010 07:25, b...@qxhp.com wrote:

Hello,

For simplicity's sake, let's say I have three fields, A, B and C, all
of which are integers. I'd like the value of C to be equal to A less B
(A-B). Is there a way I can perform this calculation? I'm guessing it
would happen when I INSERT a row and specify the values for A and B.
Feel free to direct me to the fine manual I should have read.


Given two variables, $a and $b:

INSERT INTO mytable SET A = $a, B = $b, C = $a - $b

or

INSERT INTO mytable (A, B, C) VALUES ($a, $b, $a - $b)

or, if you've previously inserted A and B:

UPDATE mytable SET C = A - B

http://dev.mysql.com/doc/refman/5.1/en/numeric-functions.html

Simples :-)

Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: How to use SSL? (SSL is enabled but not used)

[Mark Matthews]

On Aug 18, 2010, at 1:34 PM, Shawn Green (MySQL) wrote:

 On 8/18/2010 2:22 PM, Anders Kaseorg wrote:
 On Wed, 18 Aug 2010, Shawn Green (MySQL) wrote:
 If the server specifies REQUIRES SSL then that client cannot connect 
 without going through the full SSL validation process. This means that 
 Mallory would need to present the same security credentials that Alice has 
 in order to qualify as a secure user (the same certs, same password, login 
 from the correct host, etc).
 Mallory got the username and hashed password from Alice over the unencrypted 
 connection, and we assume that Mallory, like any good MITM, has the ability 
 to intercept and forge traffic for arbitrary hosts.  So this attack goes 
 through against anyone using passwords over SSL.  This already constitutes a 
 vulnerability.
 Setting up client certificates does help to prevent this form of attack 
 where Mallory tries to issue evil commands to Bob.  It does not, however, 
 prevent the attack where Mallory ignores Bob, and uses only the unencrypted 
 connection to steal data from Alice or poison her with false data.  This 
 also constitutes a vulnerability, which, as far as I can see, cannot be 
 prevented in any way with the current MySQL software.
 Your redirect has pointed out to me what I missed in Yves's first post. In 
 order for the client to require an SSL connection, you have to designate a 
 certificate for it to use for the connection.
 No, that doesn’t work either!  Against a server with SSL disabled:
 $ mysql --ssl --ssl-verify-server-cert \
--ssl-ca=/etc/ssl/certs/ca-certificates.crt \
--ssl-cert=Private/andersk.pem \
--ssl-key=Private/andersk.pem \
-h MY-SERVER
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 …
 mysql \s
 --
 mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using 
 readline 6.1
 …
 SSL: Not in use
 From the same page but a few lines above the line he quoted
 ##
 This option is not sufficient in itself to cause an SSL connection to be 
 used.
 You must also specify the --ssl-ca option, and possibly the --ssl-cert and
 --ssl-key options.
 ##
 This documentation appears to be wrong.
 Anders
 
 Excellent logic.
 
 I have updated bug #3138 with a private comment to explain your presentation 
 of the vulnerability.
 http://bugs.mysql.com/bug.php?id=3138

Shawn, Anders, Yves,

For what it's worth, the MySQL JDBC driver has had client-side SSL require 
(i.e. requireSSL=true) since 2003 and the ADO.Net driver has had SSL 
Mode=Required since 2009.

-Mark
-- 
Mark Matthews
Principal Software Developer -  MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: searching serialized data stored in mysql

[Mark Goodge]

On 09/08/2010 18:33, Norman Khine wrote:

hello, i have a table called checkout, this has a row called products
which has contains a python dictionary data, like

http://pastie.org/1082137

{products: [{productId: 123, productName: APPLE,
 productPrice: 2.34, productUrl: http://appple-fruits.net,
productDescription: nice juicy apples},
  {productId: 333, productName: ORANGE,
 productPrice: 4.21, productUrl: http://appple-fruits.net,
productDescription: nice juicy oranges},
 ...]}


what will be the correct way to make a search on this data, for
example if i want to search for a range of products with a price
between €2 - €4

is this the correct way to store this type of data?


No, it isn't, not if you're going to be searching for individual 
elements of the array. Your products table should really have separate 
columns for productId, productName, productPrice, productUrl and 
ProductDescription. If you want to be able to store arbitrary key=value 
pairs then a separate table with columns for productId, keyName and 
keyValue would be a useful way of doing it.


Storing serialized data in single MySQL column is really only useful if 
that data will never be directly manipulated by MySQL itself - that is, 
if its only ever being used as the input to a separate program that 
handles all the searching and manipulation.


Having said that, I've just looked at the URL you link to 
(http://pastie.org/1082137) and what that's demonstrating isn't an 
example of a products table, it's an example of a ecommerce checkout 
table where the cart contents are a single column of serialized data 
within the cart line. Personally, that's not the way I'd do it[1], but 
it is a perfectly valid method if you start from the assumption that 
you're never going to want to find individual orders by searching the 
contents of the order. If you're looking at this as an example of a 
product table that you would use to search for products, then you're 
misunderstanding the example being given.


[1] I'd have a cart table with one line per cart, and then a separate 
cart_contents table with one line per product and a cart_id column which 
links it to the cart table. That also allows a separate cart_address 
table which can have multiple addresses per cart (eg, billing address, 
delivery address).


Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Need Help Writing Simple Query

[Mark Phillips]

On Sun, Jul 25, 2010 at 8:49 PM, Nguyen Manh Cuong 
cuong.m...@vienthongso.com wrote:

 Hi Mark,
 Please test this query:
 select test1.*, (select name from test2 where test2.id=test1.`v_id` limit
 1) as name_1,
 (select name from test2 where test2.id=test1.`h_id` limit 1) as name_2
 from test1;

 - test1 table:
 col1v_idh_id
 America 1   2

 - test2 table:
 id  name
 2   SAM
 1   UNCLE

 - Original Message -
 From: Mark Phillips m...@phillipsmarketing.biz
 To: Mysql List mysql@lists.mysql.com
 Sent: Monday, July 26, 2010 8:29:00 AM
 Subject: Need Help Writing Simple Query

 I have been away from sql for awhile, and can't seem to figure out how to
 write a simple query for two tables.

 Table 1 has many columns, two of which are hID and vID. Table 2 has two
 columns, ID and name. The hID and vID in table 1 correspond to the IDs in
 table 2. I want to make a query so I get all the columns from table 1, but
 substitute the names from table 2 for the hID and vID values. For example,

 Table 1:
 col 1, col 2, hID, vID, col 3
 AB1 2 C

 Table 2:
 ID, name
 1fred
 2sam

 Query result:
 col1, col 2, hName, vName, col 3
 A   Bfred  sam   C

 Thanks!

 Mark

 --
 Best Regards,
 Cuongmc.

 --
 Nguyen Manh Cuong
 Phong Ky Thuat - Cong ty Vien Thong So - VTC
 Dien thoai: 0912051542
 Gmail : philipscu...@gmail.com
 YahooMail : philipscu...@yahoo.com


Thanks! That did the trick.

Mark

Need Help Writing Simple Query

[Mark Phillips]

I have been away from sql for awhile, and can't seem to figure out how to
write a simple query for two tables.

Table 1 has many columns, two of which are hID and vID. Table 2 has two
columns, ID and name. The hID and vID in table 1 correspond to the IDs in
table 2. I want to make a query so I get all the columns from table 1, but
substitute the names from table 2 for the hID and vID values. For example,

Table 1:
col 1, col 2, hID, vID, col 3
AB1 2 C

Table 2:
ID, name
1fred
2sam

Query result:
col1, col 2, hName, vName, col 3
A   Bfred  sam   C

Thanks!

Mark

Re: Help me

[Mark Goodge]

On 21/07/2010 16:33, Karthik Pr wrote:


I have created a table as follows but i was not able to use full text search on
a specific data.
create table racebike  (id int auto_increment not null primary key, name
varchar(10), user text,fulltext(name,user));

[snip]

The query is
mysql  select * from racebike where match (user) against ('speed');


It should be:

mysql select * from racebike where match (name,user) against ('speed');

When using a fulltext index, the query has to name all the fields 
included in the index, unless you're performing the search in Boolean mode.


http://dev.mysql.com/doc/refman/5.1/en/fulltext-restrictions.html

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Decimal points

[Mark Goodge]

On 19/07/2010 10:04, Ashley M. Kirchner wrote:


  Is there a way to tell MySql to only return '2' in the first select as
opposed to '2.0'?  The second select is correct and should remain as such.


Not easily, no.


  Basically I have two columns, one with an integer and another with a
decimal.  And I'm adding the two, but for those where the decimal has a .0,
I just want the result to not have the .0 and for those that do have
anything other than .0, to display it accordingly.


This is the sort of thing that is far better handled in the application 
layer, rather than the database layer. PHP, for example, even has a 
built-in function which will do this:


setype($value,float);


for example:

?
$val = 2.1;
setype($val,float);
echo $val;
?

= 2.1

?
$val = 2.0;
setype($val,float);
echo $val;
?

= 2

http://www.php.net/manual/en/function.settype.php

Even if other languages don't have built-in functions to do this, it's a 
trivial piece of code to recreate it.


Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: MySQL Replication

[Mark Goodge]

On 24/06/2010 09:18, Tompkins Neil wrote:

HI,

We have set-up MySQL Community Server 5.1.46 with Master to Slave
replication and everything appears to be working correctly, however I have a
couple of questions which I hope somebody can shed some light.

(1) When the network connection goes down between the master and slave
servers, it would appear that the updates are only sent from the master to
the slave, but not from the slave to the master when the connect is
re-established.  Is this correct ?


Yes. Replication is one-way by default. If you want two-way replication 
you have to set it up explicitly with both servers simultaneously acting 
as both master and slave.



(2) What is the situation regarding conflicts if the same master and slave
record is edited at the same time ?


You shouldn't normally edit records on the slave while it's acting as a 
slave. Replication has two main functions: to provide a hot backup of 
the master so that you can switch to the slave as the new master 
instantly should the master fail, and to allow load balancing by 
performing all reads on the slave (or multiple slaves) and updating only 
the master (eg, where you have a web cluster with each web server having 
its own MySQL instance acting as a slave from a central master updated 
from your CMS).


Two-way replication is possible, but there are rarely any significant 
benefits from it. If you do use two-way replication, you have to 
implement locking at the application level as MySQL doesn't provide it 
natively.


See the replication FAQ for more information:

http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: substring query

[Mark Goodge]

On 10/06/2010 16:55, Aaron Savage wrote:

I am looking for some guidance on creating a substring query.  I have
a column that stores a path to a file.  I would like to extract that
file extension and that is it and display it on my results.  However,
the paths are different lengths and some extensions are 3 letter and
some are 4, eq 'html'.  The only common they all have is the period
before the extension.  Anyone created a nested substring query that
can do what I am looking to do?


SUBSTRING_INDEX should do what you want.

SELECT SUBSTRING_INDEX('myfile.path','.',-1)
= 'path'

SELECT SUBSTRING_INDEX('myfile.pth','.',-1)
= 'pth'

or, in a version that's closer to real life usage:

SELECT SUBSTRING_INDEX(myfield,'.',-1) from mytable

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Master - master replication

[Mark Goodge]

On 24/05/2010 13:40, Walter Heck wrote:

Carl,

if you want to be secure, do not use the internet to transfer your
data. SSH, VPN and SSL can not give you the kind of security a private
line can give you. That is a tad expensive though :)


That's true, but again that's not really answering the question which 
was asked. Plenty of sites use PCI-compliant transmission of data across 
the Internet, there's no reason why that should be an issue provided 
it's done correctly. The question here is whether MySQL natively 
supports the tools necessary to do it correctly, and if so how to 
implement them.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Array data type

[Mark Goodge]

On 14/05/2010 09:54, Joerg Bruehe wrote:

Hi Samrat, all!


Samrat Kar wrote:

Hello,



How to store multiple values in a single field? Is there any array data type
concept in mysql?


Multiple values in a single field would be an explicit violation of
the relational model (on which the SQL language is based) and cause all
kinds of trouble in your queries.

Ever and again, developers use some kind of encoding to store a
combination of values (like flags in a bit field) in one database field,
but in many cases this makes queries very hard to write, and may prevent
optimization of the SQL statement.

It depends on your application, especially on whether this field will be
used in search conditions (... WHERE combined_field has flag_X ...),
to decide about a sensible approach.
In general, I would prefer separate fields for different flags, and a
separate table for a truly multi-valued field (like multiple postal or
mail addresses for a person).


If you're merely *storing* the data in the table, and will only ever 
retrieve it based on other factors - that is, you'll never use that 
field for any operands including joins and 'where' clauses - then it's 
often useful to store a flattened array (eg, one created by PHP's 
serialize() function, javascript JSON or even XML) as a string and then 
expand it to an array again after retrieving it. That can often be a 
useful way of storing meta-data about a data object (eg, EXIF data from 
a photograph), especially where you can't know in advance what the array 
structure will be when you create the database.


However, that's not really an array datatype in MySQL, it's simply a 
method of storing an array as a string. So it's of fairly limited 
application, there are cases where it's very useful but it's not a 
substitute for storing the array values separately using the appropriate 
table design where you do need to run queries against it.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Fulltext Match BOOLEAN MODE not searching integers

[Mark Goodge]

On 31/03/2010 16:52, Tompkins Neil wrote:

Hi

I have the following fulltext search which appears to work fine for string
phrases.  However if I search like just 51 which is part of the string
name like 51 Blue Widget in the table it doesn't return any results.
However if I search like bl it returns the 51 Blue Widget result.  My
query is as follows :

SELECT  Name MATCH (Name) AGAINST ('51*') as Relevance
FROM Products
WHERE MATCH (Name) AGAINST ('51*' IN BOOLEAN MODE)
ORDER BY Relevance DESC

Any ideas what the problem might be ?


51 is too short to be included in the index by default, so will never 
match. Blue, on the other hand, is indexed and therefore is returned 
by a search.


The default minimum word length is four characters. See 
http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html for 
more information on how to change that if necessary.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: mysql proxy in production?

[Mark Matthews]

On Mar 11, 2010, at 9:49 AM, Brent Clark wrote:

 On 11/03/2010 16:52, Krishna Chandra Prajapati wrote:
 Hi Brent
 
 You can visit the below link.
 
 http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/
 
 Well thats disappointing.
 
 sigh
 So what are we supposed to use for loadbalancing mysql.
 
 Thank you for your reply.
 
 Kind Regards
 Brent Clark

Brent,

Some clients (namely the JDBC driver, and the R-O-R adapter) have load 
balancing built in.

-Mark
-- 
Mark Matthews
Principal Software Developer - Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Possible to find this duplicate?

[Mark Goodge]

On 13/02/2010 16:12, Brian Dunning wrote:

Hey all -

I have a table listing references for chapters in a book. I'm trying to find 
all the cases where a single chapter lists more than one reference from the 
same author. In this example table, I want it to find IDs 1 and 2, because 
they're both from the same author, and both in chapter 1 of the book. It should 
not return ID 4, because that's in a different chapter.

Note that J. and John have to be considered the same. For my purposes, it's 
sufficient to look at the first word, Smith, and consider that a duplicate.

++--+-+
| ID | Author   | Chapter |
++--+-+
|  1 | Smith, John  |1|
|  2 | Smith, J.|1|
|  3 | Williams, B. |1|
|  4 | Smith, John  |2|
++--+-+

I haven't been able to even get a start on this. Any suggestions?


Try this:

  select
  count(id) as total,
  concat(substring_index(Author,,,1),Chapter) as my_reference
  from my_table
  group by my_reference
  having total  1

That may or may not work straight off, I haven't tested it. But the 
thing you're looking for is something involving a substring_index on the 
Author column.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Good source for sample data?

[Mark Goodge]

On 29/01/2010 03:18, John Meyer wrote:

If I may recommend:
http://www.generatedata.com/#download


That's brilliant. The only minor issue is that, at least for UK data, it 
won't validate for mapping purposes - the postcodes are syntactically 
correct, but non-existent. I don't know if it would have the same 
problem for US or Canadian data.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Good source for sample data?

[Mark Goodge]

On 29/01/2010 15:20, Jerry Schwartz wrote:


That's brilliant. The only minor issue is that, at least for UK data, it
won't validate for mapping purposes - the postcodes are syntactically
correct, but non-existent. I don't know if it would have the same
problem for US or Canadian data.


[JS] Sorry, my suggestion won't work either: I didn't notice that you are from
the UK.

Well, the name part will work; and perhaps the UK has databases similar to
what the USPS (US Postal Service) makes available. I'd think they would, since
they are accountable to the public.


You might think so; you'd be wrong! In the UK, postcode data - even a 
comprehensive list of allocated postcodes - is the copyright of Royal 
Mail and only available under license.


(Off-topic, but relevant to UK-based developers: please take a look at 
my blog at http://mark.goodge.co.uk/musings/422/locate-that-postcode/ 
for a topical twist on this)


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Problem starting connection pooling

[Mark Witczak]

 I'm very new to MySQL, Tomcat, connection pooling, JSP, etc. and I've 
been banging my head against a wall for two weeks trying to get a simple 
program to connect to a MySQL database.


*Vital Stats:*
Ubuntu 9.10, Java 1.6.0_0,  Java Servelet 2.5, Java Server Pages 2.1, 
JSTL 1.2, Apache2, Tomcat 6.0.20, MySQL 5.1.41  5.0.67

MySQL Connector/J 5.1.11 (also 5.1.10) - in $CATALINA_HOME/lib
dbcp 1.2.1 - in $CATALINA_HOME/lib
(all standard Ubuntu issue)

*testapp/WEB-INF/web.xml:*
?xml version=1.0 encoding=ISO-8859-1?
web-app xmlns=http://java.sun.com/xml/ns/javaee;
   xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
   xsi:schemaLocation=http://java.sun.com/xml/ns/javaee 
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd;

   version=2.5

description
  Servlet and JSP Examples.
/description
display-nameServlet and JSP Examples/display-name

resource-ref
descriptionDB Connection/description
res-ref-namejdbc/mydatabase/res-ref-name
res-typejavax.sql.DataSource/res-type
res-authContainer/res-auth
/resource-ref
/web-app

*testapp/META-INF/context.xml:*
?xml version=1.0 encoding=UTF-8?

Context
Resource name=jdbc/mydatabase auth=Container 
type=javax.sql.DataSource

   maxActive=100 maxIdle=30 maxWait=1
   username=foo password=bar 
driverClassName=com.mysql.jdbc.Driver

   url=jdbc:mysql://test.hostname.com:3306/database_test1/
/Context

*testapp/testapp.jsp:*
%@ page contentType=text/html %
%-- These libraries are required for the c and sql tags --%
%@ taglib prefix=c uri=http://java.sun.com/jsp/jstl/core; %
%@ taglib prefix=sql uri=http://java.sun.com/jsp/jstl/sql; %
meta http-equiv=Content-Type content=text/html; charset=UTF-8
html
head
titleJNDI DBCP Test Page/title
/head
body

h1JNDI DBCP Test Page/h1
br/Executing the query ...
br/

%-- Note: Enter a query that is valid for your database here --%
sql:query var=result dataSource=jdbc/mydatabase
SELECT company FROM manuals
/sql:query
/body
/html

I create the WAR (jar cvf testapp.war *), undeploy the old version and 
redeploy the new one through Tomcat Web Application Manager. Then 
restart Tomcat (sudo /etc/init.d/tomcat restart). The result is:


Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
INFO: ContextListener: contextInitialized()
Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
INFO: SessionListener: contextInitialized()
Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet jsp threw exception
javax.servlet.jsp.JspException: Unable to get connection, DataSource 
invalid: org.apache.commons.dbcp.SQLNestedException: Cannot create 
PoolableConnectionFactory (Communications link failure


The last packet sent successfully to the server was 0 milliseconds ago. 
The driver has not received any packets from the server.)
at 
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown 
Source)
at 
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown 
Source)
at 
org.apache.jsp.test_jsp._jspx_meth_sql_005fquery_005f0(test_jsp.java:188)

at org.apache.jsp.test_jsp._jspService(test_jsp.java:138)
at 
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
Blah, Blah, Blah

*More info: *The connection to MySQL tested successfully using the 
command line 'mysql'. There are no firewalls, that I can find, between 
the servers.


What is going on here? What am I missing? What is going on here? How do 
I fix it?


-Do I need to create a foo user in the tomcat-users.xml?
-Do I have to mess with the policy files? or security?

Thanks for your help.
Mark



No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.432 / Virus Database: 271.1.1/2636 - Release Date: 01/21/10 
07:34:00



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Problem starting connection pooling

[Mark Matthews]

On Jan 22, 2010, at 10:21 AM, Mark Witczak wrote:

  [snip]
 I create the WAR (jar cvf testapp.war *), undeploy the old version and 
 redeploy the new one through Tomcat Web Application Manager. Then restart 
 Tomcat (sudo /etc/init.d/tomcat restart). The result is:
 
 Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
 INFO: ContextListener: contextInitialized()
 Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
 INFO: SessionListener: contextInitialized()
 Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke
 SEVERE: Servlet.service() for servlet jsp threw exception
 javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: 
 org.apache.commons.dbcp.SQLNestedException: Cannot create 
 PoolableConnectionFactory (Communications link failure
 
 The last packet sent successfully to the server was 0 milliseconds ago. The 
 driver has not received any packets from the server.)
 at 
 org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown
  Source)
 at 
 org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown 
 Source)
 at 
 org.apache.jsp.test_jsp._jspx_meth_sql_005fquery_005f0(test_jsp.java:188)
 at org.apache.jsp.test_jsp._jspService(test_jsp.java:138)
 at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
 Blah, Blah, Blah
 
 More info: The connection to MySQL tested successfully using the command line 
 'mysql'. There are no firewalls, that I can find, between the servers.

Mark,

What message is where you posted blah blah blah. *Usually* there's 
information from the driver right there, which will have the details of why 
there was a communications link failure.

If I had to guess, your mysql server was started with --skip-networking (most 
debian-based distributions do this by default), and mysql is using unix 
domain sockets (which Java can't) to speak to mysqld. If that's the case, 
you'll have to reconfigure mysqld to listen at least on the loopback 
(127.0.0.1) by removing --skip-networking from my.cnf and adding 
--bind-address=127.0.0.1

-Mark
-- 
Mark Matthews, Architect - Enterprise Tools
MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Problem starting connection pooling

[Mark Witczak]

Alright, here is the entire log entry:

Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
INFO: ContextListener: contextInitialized()
Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
INFO: SessionListener: contextInitialized()
Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet jsp threw exception
javax.servlet.jsp.JspException: Unable to get connection, DataSource 
invalid: org.apache.commons.dbcp.SQLNe
stedException: Cannot create PoolableConnectionFactory (Communications 
link failure


The last packet sent successfully to the server was 0 milliseconds ago. 
The driver has not received any pack

ets from the server.)
at 
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown 
Source)
at 
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown 
Source)
at 
org.apache.jsp.test_jsp._jspx_meth_sql_005fquery_005f0(test_jsp.java:188)

at org.apache.jsp.test_jsp._jspService(test_jsp.java:138)
at 
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at 
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at 
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
at 
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:616)
at 
org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:269)

at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAsPrivileged(Subject.java:537)
at 
org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:301)
at 
org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:162)
at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:283)
at 
org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56)
at 
org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189)

at java.security.AccessController.doPrivileged(Native Method)
at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185)
at 
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at 
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at 
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at 
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at 
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at 
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at 
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at 
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at 
org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)

at java.lang.Thread.run(Thread.java:636)

Also, the MySQL instance I'm connecting to is hosted by dreamhost. I 
don't have any control over their networking configuration. Is there a 
command that will tell me if the --skip-networking flag was used for 
startup?


On 1/22/2010 11:38 AM, Mark Matthews wrote:

On Jan 22, 2010, at 10:21 AM, Mark Witczak wrote:

   

  [snip]
I create the WAR (jar cvf testapp.war *), undeploy the old version and redeploy 
the new one through Tomcat Web Application Manager. Then restart Tomcat (sudo 
/etc/init.d/tomcat restart). The result is:

Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
INFO: ContextListener: contextInitialized()
Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
INFO: SessionListener: contextInitialized()
Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet jsp threw exception
javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: 
org.apache.commons.dbcp.SQLNestedException: Cannot create 
PoolableConnectionFactory (Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver 
has not received any packets from the server.)
 at 
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown
 Source

Re: Problem starting connection pooling

[Mark Matthews]

On Jan 22, 2010, at 10:49 AM, Mark Witczak wrote:

 Alright, here is the entire log entry:
 
 Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
 INFO: ContextListener: contextInitialized()
 Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
 INFO: SessionListener: contextInitialized()
 Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke
 SEVERE: Servlet.service() for servlet jsp threw exception
 javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: 
 org.apache.commons.dbcp.SQLNe
 stedException: Cannot create PoolableConnectionFactory (Communications link 
 failure
 
 The last packet sent successfully to the server was 0 milliseconds ago. The 
 driver has not received any pack
 ets from the server.)
at 
 org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown
  Source)
at 
 org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown 
 Source)
at 
 org.apache.jsp.test_jsp._jspx_meth_sql_005fquery_005f0(test_jsp.java:188)
at org.apache.jsp.test_jsp._jspService(test_jsp.java:138)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at 
 org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at 
 org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at 
 sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at 
 sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at 
 org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:269)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAsPrivileged(Subject.java:537)
at 
 org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:301)
at 
 org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:162)
at 
 org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:283)
at 
 org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56)
at 
 org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189)
at java.security.AccessController.doPrivileged(Native Method)
at 
 org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185)
at 
 org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at 
 org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at 
 org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at 
 org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at 
 org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at 
 org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at 
 org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at 
 org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at 
 org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Thread.java:636)
 
 Also, the MySQL instance I'm connecting to is hosted by dreamhost. I don't 
 have any control over their networking configuration. Is there a command that 
 will tell me if the --skip-networking flag was used for startup?

Mark,

In mysql, issuing show variables like 'skip_networking' should tell you.

-Mark

-- 
Mark Matthews, Architect - Enterprise Tools
MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Problem starting connection pooling

[Mark Witczak]

skip-networking is OFF

On 1/22/2010 12:09 PM, Mark Matthews wrote:

On Jan 22, 2010, at 10:49 AM, Mark Witczak wrote:

   

Alright, here is the entire log entry:

Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
INFO: ContextListener: contextInitialized()
Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log
INFO: SessionListener: contextInitialized()
Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet jsp threw exception
javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: 
org.apache.commons.dbcp.SQLNe
stedException: Cannot create PoolableConnectionFactory (Communications link 
failure

The last packet sent successfully to the server was 0 milliseconds ago. The 
driver has not received any pack
ets from the server.)
at 
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown
 Source)
at 
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown 
Source)
at 
org.apache.jsp.test_jsp._jspx_meth_sql_005fquery_005f0(test_jsp.java:188)
at org.apache.jsp.test_jsp._jspService(test_jsp.java:138)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at 
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at 
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at 
org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:269)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAsPrivileged(Subject.java:537)
at 
org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:301)
at 
org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:162)
at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:283)
at 
org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56)
at 
org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189)
at java.security.AccessController.doPrivileged(Native Method)
at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185)
at 
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at 
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at 
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at 
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at 
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at 
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at 
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at 
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at 
org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Thread.java:636)

Also, the MySQL instance I'm connecting to is hosted by dreamhost. I don't have 
any control over their networking configuration. Is there a command that will 
tell me if the --skip-networking flag was used for startup?
 

Mark,

In mysql, issuing show variables like 'skip_networking' should tell you.

-Mark

   




No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.432 / Virus Database: 271.1.1/2638 - Release Date: 01/22/10 
07:34:00

   

Re: Record old passwords ?

[Mark Goodge]

On 21/01/2010 11:07, Lucio Chiappetti wrote:

On Tue, 19 Jan 2010, Tompkins Neil wrote:


I can enforce that the user can't use the same password as the
previous four
- when they change their password. However, the user can manipulate
this by
changing the password four times and then resetting back to there
original
password. How would I overcome this problem ? Any thoughts or
recommendations ?


Probably if your users do that, it means they (rightfully) consider A
DAMN NUISANCE the fact to be compelled to change password. Abandon the
idea.

I share their feeling about forcing this change of passwords, and cannot
see almost no real life application (unless perhaps one is a spy) which
really require this degree of security !


The real life application most commonly encountered where this is 
necessary is where your organisation wishes to process credit card or 
other financial data, and needs to be certified as PCI compliant by the 
banks and card companies in order to be able to process payments via 
their systems. One of the requirements of PCI compliance is that any 
login which has access to financial data must have the password changed 
regularly, with restrictions on reusing recent passwords.


Now, you may well argue that the PCI requirements are wrong in this 
respect, and if so then a lot of people may well agree with you :-) 
However, unless you are a huge multinational and able to negotiate your 
own terms with the banks, disagreeing with the requirements doesn't 
alter the need to comply with them - at least, not if you want to be 
able to use their payment APIs.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Record old passwords ?

[Mark Goodge]

On 19/01/2010 14:44, Tompkins Neil wrote:

Hi All,

Following on from my earlier email - I've the following question now :

I can enforce that the user can't use the same password as the previous four
- when they change their password.  However, the user can manipulate this by
changing the password four times and then resetting back to there original
password.  How would I overcome this problem ? Any thoughts or
recommendations ?


Store the date/time that the password was changed, and as well as not 
alllowing one within the past four passwords you can also disallow one 
that was last used within the past N days, for whatever value of N you 
prefer.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Record old passwords ?

[Mark Goodge]

On 19/01/2010 09:14, Tompkins Neil wrote:


I think I will go with the four additional column
approach as I proposed (in the current table) - since this need is a PCI
compliancy security requirement.


Do you have a reference for that? Storing past passwords as additional 
fields like that is inflexible and generally bad database design. I'd be 
somewhat surprised if PCI compliance really did require it.


Mark
--
http://mark.goodge.co.uk - blog
htp://www.good-stuff.co.uk - stuff

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: last_insert_id

[Mark Goodge]

Gary Smith wrote:

Steve Edberg wrote:
(2) autoincrement values are not reused after deletion, so if you 
deleted the record with ID=1000 inserted in (1), the next 
autoincrement would still be 1001, even if the existing records are 
IDs 1,2,3. This is usually the desired behavior, but again, may not be 
what *you* need.


Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch 
which changes this behaviour, or is my mind dribbling out of my ears?


As far as I'm aware there's no mode to change the default behaviour, but 
you can always reset the autoincrement value:


ALTER TABLE tbl AUTO_INCREMENT = n;

Do that, and the next inserted record will have id = n, provided that n 
is greater than the current maximum value. If, on the other hand, n is 
lower than or equal to the current maximum value, the next id will be 
the next value higher than the current maximum. So


ALTER TABLE tbl AUTO_INCREMENT = 1;

on a non-empty table is functionally equivalent to

ALTER TABLE tbl AUTO_INCREMENT = MAX(id) + l

(which isn't valid SQL, so don't try it!)

If you want to reuse autoincrement values above the current maximum, 
therefore, you can achieve that in practice by resetting the 
autoincrement value prior to any insertion.


What you can't do, though, is get autoincrement to insert values into 
the middle of a sequence. So if you have, say, ids 1,2,3,4,5,8,9 and you 
issue


ALTER TABLE tbl AUTO_INCREMENT = 1;

or

ALTER TABLE tbl AUTO_INCREMENT = 6;

then the next inserted id will still be 10, not 6.

Mark
--
http://mark.goodge.co.uk



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Join Statement

[Mark Goodge]

Victor Subervi wrote:

On Mon, Dec 14, 2009 a

mysql select SKU, Quantity, Name, Price, t.sizes,
t.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID http://t.prodid-p.id/;
Empty set (0.00 sec)

mysql select ID, SKU, Name, Price from products;
++--+---++
| ID | SKU  | Name  | Price  |
++--+---++
|  2 | prodSKU1 | name1 | 555.22 |
++--+---++
1 row in set (0.00 sec)

t.ProdID == 2
p.ID == 2
That's a match.
So why does my select join fail?


Because you're using a minus sign where you should be using an equals 
sign. This is what you're doing:


select
SKU, Quantity, Name, Price, t.sizes,t.colorsShadesNumbersShort
from tem126080739853 t
join products p on
t.ProdID-p.ID

This is what you should be doing:

select
SKU, Quantity, Name, Price, t.sizes,t.colorsShadesNumbersShort
from tem126080739853 t
join products p on
t.ProdID=p.ID

Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Update Doesn't Update!

[Mark Goodge]

Jørn Dahl-Stamnes wrote:

On Friday 11 December 2009 10:38, Victor Subervi wrote:

Hi;

mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

  
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.


You can, but not if it's an auto-increment field.

Mark



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: mysqldump vs phpmyadmin dump

[Mark Goodge]

Wang Zi Feng wrote:

Hi everyone,

Here is a rookie question.

The problem what I found is that mysqldump and phpmyadmin generate different
size of backup file against same database.

I try to dump same database with the 2 different methods, the original
database is 2.8mb, phpmyadmin export 1.5mb file, and mysqldump export only
941kb file.


mysqldump has a number of different settings, and the file size will 
vary according to which you use. For example, using extended insert 
syntax will significantly increase the size of the output, and that 
could easily account for the difference between your two files.


What's probably happening is that the settings you're using when running 
mysqldump from the command line are different to those used by 
phpMyAdmin, so you end up with differently formatted files.



I know there must be some difference between the two export method, but
after I import the 941kb file which mysqldump created into a new database,
it just works fine.

So I'm not sure if I can use mysqldump as the best option to do mysql
backup, can someone can help me to figure out why phpmyadmin would generate
twice big file? And I see some post that address it is not recommend to
import mysqldump file by using phpmyadmin, because it will cause problem.


Importing any large file via phpMyAdmin is likely to have problems, as 
you'll find yourself limited by the maximum upload file size of the web 
server where phpMyAdmin is running (that's typically 2Mb for PHP on 
Apache, although the administrators can change that). But the source of 
the file is irrelevant; so long as it's within the file upload limit 
then it doesn't matter whether it was exported by phpMyAdmin itself or 
created using mysqldump from the command line.


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Questions on Database Design

[Mark Phillips]

Thanks to Martin and John for their help!

Mark

On Sat, Oct 3, 2009 at 5:53 PM, Martin Gainty mgai...@hotmail.com wrote:

  enforcing by username/password to the DB is your safest method
 and if you want to really be safe put ssh access onto the MySQL Server

 here is how to install SSH and MySQL onto Ubuntu
 http://ubuntuforums.org/showthread.php?t=388073

 and to access SSHClient
 http://dev.mysql.com/doc/refman/5.1/en/windows-and-ssh.html

 HTH
 Martin Gainty
 __
 Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

 Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
 Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
 Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
 dient lediglich dem Austausch von Informationen und entfaltet keine
 rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
 E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

 Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
 destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
 l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci 
 est interdite. Ce message sert à l'information seulement et n'aura pas 
 n'importe quel effet légalement obligatoire. Étant donné que les email 
 peuvent facilement être sujets à la manipulation, nous ne pouvons accepter 
 aucune responsabilité pour le contenu fourni.






  Date: Sat, 3 Oct 2009 18:11:59 -0600
  From: john.l.me...@gmail.com
  To: m...@phillipsmarketing.biz
  CC: mysql@lists.mysql.com
  Subject: Re: Questions on Database Design

 
  Mark Phillips wrote:
   On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty mgai...@hotmail.com
 wrote:
  
  
   depends on the relationship of the Data Tables and the Users that use
 them
  
   for instance if I was to setup a table of outgoing calls from 2
 distinct
   individuals :
   Me calls to HarvardMedicalSchool, MassGeneral,
   SomervilleHospital and AMA
   VereinDesKrankRufscalls to Biff,Tony,EdSoprano and Destiny
  
   so as you can see the difference between my calls and Vereins calls
 should
   never be joined
   as Vereins customers are distinctly not mine and mine are not his
   Moreover my contact table would contain Degrees and titles where
 Vereins
   customers
   have no need for that
   So in this case it would make perfect sense for my Database to be
 separate
   and distinct from Vereins database..if for no other reason than the
 schemas
   are completely difference
  
   With an emphasis on security once Verein initiates populating his
 records
   on your DB by populating the same tables and using the same join
   relationships it will be impossible to force him to not use those
 tables
   or even to restrich his access to the slave server while you're
 updating
   the master
   You can restrict access by GRANT SELECT on the tables to Verein but
 that
   would last only a week or 2 until Verein requests update and insert
 access
   to the DB. Once the INSERT and UPDATE grants are made you wont be able
 to
   separate his records from yours
  
   Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is
 cheap
   so this should be a low cost solution for you
  
   Keep us apprised and any feel free to inquire on any operational
 details
   you may require.
  
   Thanks! To make sure I understand. Even if the schemas are the same,
 if the
  
   data is not related, nor is meant to be combined in some way (eg rolled
 up
   or summed in some way), then creating a separate database for each user
 is a
   better way to go; or at least a meaningful way to go. A side benefit is
   greater security from the stand point that user a cannot get to user
 b's
   data.
  
   Can't I achieve the same level of security if each row has a userID,
 and all
   queries use a where userID=xxx clause?
  
   Mark
  
 
 
 
  no, don't confuse that with database security. There are too many ways
  to get around that sort of trick through SQL injection attacks. Read
  http://dev.mysql.com/doc/refman/5.4/en/privilege-system.html for a
  starter on privileges and security.
  But as long as you're not needing to regularly combine and aggregate the
  data then creating separate databases is a reasonable option.
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
 

 --
 Hotmail: Trusted email with powerful SPAM protection. Sign up 
 now.http://clk.atdmt.com/GBL/go/177141665/direct/01/

Questions on Database Design

[Mark Phillips]

I am new at database design, and my question relates to the trade-offs
between putting all data in one database or several for mysql. For example,
say I have an application where a users login from their mobile phones and
read/write data to a database. Say there are roughly 10-15 tables in the
database and each user will add approximately 20,000 records per year. Each
user should not have access to data from another user. Users have to
register in some way to create their database in the first place. When does
it make sense to give each user their own database versus putting all the
data into one database (ie one set of tables) and with multiple userIDs? 10
users? 1,000 users? Never?

Thanks!

Mark

Re: Questions on Database Design

[Mark Phillips]

On Sat, Oct 3, 2009 at 2:47 PM, John Meyer john.l.me...@gmail.com wrote:

 Mark Phillips wrote:

 I am new at database design, and my question relates to the trade-offs
 between putting all data in one database or several for mysql. For
 example,
 say I have an application where a users login from their mobile phones and
 read/write data to a database. Say there are roughly 10-15 tables in the
 database and each user will add approximately 20,000 records per year.
 Each
 user should not have access to data from another user. Users have to
 register in some way to create their database in the first place. When
 does
 it make sense to give each user their own database versus putting all the
 data into one database (ie one set of tables) and with multiple userIDs?
 10
 users? 1,000 users? Never?





 It's not so much how many users you have (though that may be a question of
 data storage more than databases) as to what are they doing?  Are the
 actions related?  If they are, then have one database with each user having
 access to their records and their records only, which can easily be done
 with terms of database security..


John,
Thanks. The data is private to each user; there is no sharing of data. I am
not sure what you mean by are the actions related Each user is
reading/writing independently of each other. Would that argue for separate
databases?

Mark

Re: Questions on Database Design

[Mark Phillips]

On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty mgai...@hotmail.com wrote:

  depends on the relationship of the Data Tables and the Users that use them

 for instance if I was to setup a table of outgoing calls from 2 distinct
 individuals :
 Me  calls to HarvardMedicalSchool, MassGeneral,
 SomervilleHospital and AMA
 VereinDesKrankRufscalls to Biff,Tony,EdSoprano and Destiny

 so as you can see the difference between my calls and Vereins calls should
 never be joined
 as Vereins customers are distinctly not mine and mine are not his
 Moreover my contact table would contain Degrees and titles where Vereins
 customers
 have no need for that
 So in this case it would make perfect sense for my Database to be separate
 and distinct from Vereins database..if for no other reason than the schemas
 are completely difference

 With an emphasis on security once Verein initiates populating his records
 on your DB by populating the same tables and using the same join
 relationships it will be impossible to force him to not use those tables
 or even to restrich his access to the slave server while you're updating
 the master
 You can restrict access by GRANT SELECT on the tables to Verein but that
 would last only a week or 2 until Verein requests update and insert access
 to the DB. Once the INSERT and UPDATE grants are made you wont be able to
 separate his records from yours

 Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is cheap
 so this should be a low cost solution for you

 Keep us apprised and any feel free to inquire on any operational details
 you may require.

 Thanks! To make sure I understand. Even if the schemas are the same, if the
data is not related, nor is meant to be combined in some way (eg rolled up
or summed in some way), then creating a separate database for each user is a
better way to go; or at least a meaningful way to go. A side benefit is
greater security from the stand point that user a cannot get to user b's
data.

Can't I achieve the same level of security if each row has a userID, and all
queries use a where userID=xxx clause?

Mark



  Date: Sat, 3 Oct 2009 14:38:25 -0700
  Subject: Questions on Database Design
  From:
  To: mysql@lists.mysql.com

 
  I am new at database design, and my question relates to the trade-offs
  between putting all data in one database or several for mysql. For
 example,
  say I have an application where a users login from their mobile phones
 and
  read/write data to a database. Say there are roughly 10-15 tables in the
  database and each user will add approximately 20,000 records per year.
 Each
  user should not have access to data from another user. Users have to
  register in some way to create their database in the first place. When
 does
  it make sense to give each user their own database versus putting all the
  data into one database (ie one set of tables) and with multiple userIDs?
 10
  users? 1,000 users? Never?
 
  Thanks!
 
  Mark

 --
 Hotmail: Free, trusted and rich email service. Get it 
 now.http://clk.atdmt.com/GBL/go/171222984/direct/01/

Re: Questions on Database Design

[Mark Phillips]

On Sat, Oct 3, 2009 at 4:02 PM, John Meyer john.l.me...@gmail.com wrote:


  John,
 Thanks. The data is private to each user; there is no sharing of data. I
 am not sure what you mean by are the actions related Each user is
 reading/writing independently of each other. Would that argue for separate
 databases?
  Mark



 Are the actions of a similar nature (i.e. they're all writing the same type
 of data and the databases themselves would be similar if not the same)?


Each user will write the same type of data to the same  schema. So the
databases schemas would be identical.

Is there any sort of application that would traverse all of those databases
 at once?


Not really necessary from the user's perspective.

 Also keep in mind that multiple databases increases your complexity.  I
 think we'd have a better idea if we knew a little more of the specifics of
 this application.


Sure, no great military secrets here. The application is a mobile softball
(baseball, basketball, soccer, etc.) score book. The data for each pitch
(softball = pitch type, who made what play, what the batter did, errors,
etc.) is entered on the cell phone, and stored in MySQL tables in order to
create game and season stats for a team and each player. This can also apply
to other sports. Each user is a team manager or scorekeeper. There really
isn't any need for team A to see/access team B's stats. A league may want to
do a special type of roll-up, but this app is really just for each team. I
am sure an app could be written to do the roll-up, but that is not the main
focus.

I think by your discussion, it may make sense to have separate databases for
each user instead of add a userID column to many of the tables to separate
each user's data from the other users. Does that make sense?

Mark

Re: Another Join Problem

[Mark Goodge]

Victor Subervi wrote:

Hi;
I get the following error:

*SQL query:*

SELECT ID, Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

 *MySQL said:*
 #1052 - Column 'ID' in field list is ambiguous

Please note the error is about ambiguity. products has an ID field and so
does categories. If I run the statement taking out the ID from the
select, it runs. So, where is the ambiguity??


The ambiguity is that the select clause doesn't know which table you're 
referring to, since you're joining two of them that both have an 'ID' 
field. This will work:


SELECT products.ID, Item
FROM products
JOIN categories ON categories.ID = products.Category
LIMIT 0 , 30;

Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Wierd PHP/MySQL result

[Mark Goodge]

Mogens Melander wrote:

Well, no typos.

The relevant piece of code:

if (! $cust = mysql_fetch_assoc($res))
{
echo {success: false, errors: { reason: 'Fetch Customer failed:  .
htmlspecialchars(mysql_error(),ENT_QUOTES) .
br . htmlspecialchars($sql,ENT_QUOTES) . '}};
}
else
{
echo {success: true, total: 1, results:[ . json_encode($cust) . ]};
}



What happens if you simplify the PHP?


$cust = mysql_fetch_assoc($res);
print_r($cust);


That will tell you what PHP is seeing from MySQL. If the results have 
the correct values, then the problem is elsewhere in the code.


I suspect that the problem is in the json_encode() function. According 
to the PHP documentation this requires utf8-encoded data. If your values 
in oname are not utf8, then that may explain why the function is 
returning a 'null' where it should have a string.


Mark


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: How to compare 2 columns in different tables.

[Mark Goodge]

John Furlong wrote:


My question is, can the argument in AGAINST() refer to a column in
another table or does it have to be a specific string you are
searching for? If  the MATCH() function won't work, any suggestions
on how else to compare table1.name against table2.name? The columns
are defined as VARCHAR.


Does anyone have an answer to this? I, too, would like to know how to 
use fulltext to compare data between two columns (as opposed to 
comparing data between a column and a pre-defined string). Is there any 
way to do this?


Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Fulltext query expansion query

[Mark Goodge]

Hi,

I'm currently working on a project which uses fuulltext searching. The 
with query expansion feature is useful, but I was wondering if there's 
any way to obtain the list of terms that the expanded query uses (other 
than those originally input, of course). Is that possible, and, if so, how?


Thanks

Mark
--
Stuff: http://www.good-stuff.co.uk
Blog: http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Join

[Mark Farnsworth]

Join

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Problems After MySql 5.1.34

[Mark]

Gary wrote:

 Welcome to the hell that is php + apache + mysql. If you upgrade your
 MySql (especially major versions 5.0 = 5.1) you will also need to
 recompile php against the new MySql client libs. We've had very
 limited success trying to get it to work otherwise.

Well, you don't actually have to recompile PHP entirely, of course: just
its mysql.so extension.

@TS: Other than that, you basically need to recompile *everything* (or its
mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw;
so you'd need to build DBD:mysql as well (same for Python, etc).

Walter wrote:

 Any (major)upgrade of mysql client requires the dependent subsystem to
 upgrade also. Anything else would be careless since you do not know if the
 interface has changed.

Actually, you *do* know: that's what the changelog is for. :) When C
header changes are made, an upgrade is in order. If not, when upgrading
between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you
won't need to recompile all system-wide MySQL client extensions. I've done
this many times, without issue: you just need to be absolutely sure no
header changes were made (when in doubt, recompile).

I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's
working just fine, but I spent several hours recompiling MySL client
stuff; without doing so, your apps will likely behave erratically, or just
segfault altogether.

This isn't a MySL hell exclusively, btw. You'll get the same issue
upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the
beast.

- Mark


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Problems After MySql 5.1.34

[Mark]

Depends on your OS, I guess. On FreeBSD you can just go to the

'php5-extensions' port, run a 'make config', and deselect everything but

the MySQL extension. Then it will only build mysql.so for you. Very easy.

:)

 

- Mark

 

 

From: Martin Gainty [mailto:mgai...@hotmail.com] 
Sent: donderdag 7 mei 2009 15:15
To: ad...@asarian-host.net; mysql@lists.mysql.com
Subject: RE: Problems After MySql 5.1.34

 

Gary

this isnt mysql's fault that php has such abysmal build tools..try
requesting a web developer to simply download php and run the proprietary
binary tools
after a week of sleepless nights and endless searching for the right
dll/so/lib and endless searching for the right tools i guarantee they will
call no joy 
and implement the webapp in Java!

Martin 
__ 
Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung/Note de
déni et de confidentialité

This message is confidential. If you should not be the intended receiver,
then we ask politely to report. Each unauthorized forwarding or
manufacturing of a copy is inadmissible. This message serves only for the
exchange of information and has no legal binding effect. Due to the easy
manipulation of emails we cannot take responsibility over the the contents.


Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.


Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.






 From: ad...@asarian-host.net
 Subject: RE: Problems After MySql 5.1.34
 Date: Thu, 7 May 2009 12:57:42 +
 To: mysql@lists.mysql.com
 
 Gary wrote:
 
  Welcome to the hell that is php + apache + mysql. If you upgrade your
  MySql (especially major versions 5.0 = 5.1) you will also need to
  recompile php against the new MySql client libs. We've had very
  limited success trying to get it to work otherwise.
 
 Well, you don't actually have to recompile PHP entirely, of course: just
 its mysql..so extension.
 
 @TS: Other than that, you basically need to recompile *everything* (or its
 mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw;
 so you'd need to build DBD:mysql as well (same for Python, etc).
 
 Walter wrote:
 
  Any (major)upgrade of mysql client requires the dependent subsystem to
  upgrade also. Anything else would be careless since you do not know if
the
  interface has changed.
 
 Actually, you *do* know: that's what the changelog is for. :) When C
 header changes are made, an upgrade is in order. If not, when upgrading
 between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you
 won't need to recompile all system-wide MySQL client extensions. I've done
 this many times, without issue: you just need to be absolutely sure no
 header changes were made (when in doubt, recompile)..
 
 I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's
 working just fine, but I spent several hours recompiling MySL client
 stuff; without doing so, your apps will likely behave erratically, or just
 segfault altogether.
 
 This isn't a MySL hell exclusively, btw. You'll get the same issue
 upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the
 beast.
 
 - Mark
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
 

  _  

Windows Live™: Keep your life in sync. Check it out.
http://windowslive.com/explore?ocid=TXT_TAGLM_BR_life_in_synch_052009 

RE: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

[Mark]

Seems I was in error suggesting a file limit issue; which is why I wisely
left it to to, Jörg. :)

Ok, next step, then:

ERRORS
 pthread_create() will fail if:

[EAGAIN] The system lacked the necessary resources to create
 another thread, or the system-imposed limit on the
 total number of threads in a process
 [PTHREAD_THREADS_MAX] would be exceeded.

Which brought me to the likely solution:

http://www.krellis.org/unix-stuff/mysql-freebsd-threads.html

My system only needs 128 connections (as opposed to the 2000 of the OP),
so I guess that's why I never ran into this.

- Mark



-Original Message-
From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com] 
Sent: woensdag 29 april 2009 8:14
To: Mark; mysql@lists.mysql.com
Subject: Re: 1135: Can't create a new thread (errno 35); if you are not out
of available memory, you can consult the manual for a possible OS-dependent
bug

Hi all!


Mark wrote:
 On my FreeBSD:
 
 usr/include/errno.h:#define EAGAIN  35 /* Resource temporarily unavailable
 */
 /usr/include/errno.h-#ifndef _POSIX_SOURCE
 /usr/include/errno.h-#define EWOULDBLOCK EAGAIN /* Operation would block
*/

Ok, next step then:

The OP wrote the problem was when creating a new thread.
From some obscure sources (personal knowledge, reading, wild guess, ...)
we assume it might be a system call pthread_create() which is used for
this.

So we do a man pthread_create and get (quoted from my Linux machine):
|  NAME
| pthread_create - thread creation

Looks good! Continuing reading that page, we arrive at (again, from Linux):
| ERRORS
|The pthread_create() function shall fail if:
|
|EAGAIN The  system  lacked  the  necessary resources to create
another thread, or the system-imposed
|   limit on the total number of threads in a process
{PTHREAD_THREADS_MAX} would be exceeded.
|

I leave it to the OP (or other users of his system, FreeBSD 7.1) to
1) check the man page there for the exact description when
   pthread_create() might cause error EAGAIN,
2) find out which resources that might be, or which system limit, and
   how to check more, maybe even change the limits.

 
 I still wouldn't discount the files resources limit; though obviously
 I bow to Jörg's expertise on the matter. :)

Thanks :)

I never claimed it wouldn't be files, I just said that using the errno
value reported will help in the analysis (and tried to explain how to do
that).

But if somebody finds that the message
  *1135: Can't create a new thread (errno 35); if you are not out of
   available memory, you can consult the manual for a possible
   OS-dependent bug*
really is caused by a file limit, I propose to report a bug about a
misleading error message.


Regards,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,
   joerg.bru...@sun.com
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=ad...@asarian-host.net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Information Schema

[Mark]

Hmm, seems indeed every non-privileged user can access it:

Your MySQL connection id is 13949
Server version: 5.1.34 FreeBSD port: mysql-server-5.1.34

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

mysql 
mysql 
mysql show databases;
++
| Database   |
++
| information_schema | 
| hgallery   | 
++
2 rows in set (0.00 sec)

mysql

Ok, so how do I disable that?

- Mark


-Original Message-
From: John Daisley [mailto:john.dais...@mypostoffice.co.uk] 
Sent: woensdag 29 april 2009 5:58
To: Scott Haneda
Cc: mysql@lists.mysql.com
Subject: Re: Information Schema

Scott

Information_schema is a virtual database only. I think it was added in
MySQL 5.0.2 to comply with SQL:2003 specifications.

information_schema provides the same info as you can get from 'SHOW'
commands. Every user automatically has select privs for information_schema
and its not possible to perform any other action other than select on the
tables within information_schema.

If you have a look here

http://dev.mysql.com/tech-resources/articles/mysql-datadictionary.pdf

you will find a document giving more details on what information_schema
is, how it works and how to use it.

Regards
John


 I have been meaning to find out about this since I moved to mysql 5.
 In version 4, I never saw the table information schema.  With it
 being in version 5, I assume it was something only the root users, or
 a higher level user could see.

 I now know that it shows up under any account.  I will certainly go
 read more in the docs about what this table is for.  However, since it
 seems to be important, I would assume you do not want database users
 to be able to update, insert, or delete against it.

 Is the default set up in a way that database/tables are protected?
 --
 Scott * If you contact me off list replace talklists@ with scott@ *


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk


 __
 This email has been scanned by Netintelligence
 http://www.netintelligence.com/email





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=ad...@asarian-host.net


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Start MySQL with --intit-file?

[mark konetchy]

i dont think that the init.d script will accept the argument.

you need to run /usr/bin/mysqld_safe --init-file=clouds.sql 

(or whatever the path to mysqld_safe is)

2009/4/29 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com

 Hi Guys,

 i am still lost here:

  GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'XXX';
 GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'XXX';
 FLUSH PRIVILEGES;


 Here is the command that I am executing:

 [r...@server1 ~]# /etc/init.d/mysqld restart --init-file=~/cloudsql.sql
 Stopping MySQL:[  OK  ]
 Starting MySQL:[  OK  ]
 [r...@server1 ~]#

 In cloudsql.sql I have:

 GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'xxx';
 GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'xxx';
 GRANT ALL ON mysql.* TO 'root'@'67.23.34.37' IDENTIFIED BY 'xxx';
 FLUSH PRIVILEGES;
 commit;

 When I try to connect from my machine in my apartment I get:

 Unable to connect to host 67.23.34.37.
 Be sure that the address is correct and that you have the necessary
 privileges.
 MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL
 server

 Can I start over some how or how do I fix? I have never had this much
 trouble, but I guess historically for me, I have not done a setup from
 scratch.

 -Jason

RE: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

[Mark]

I happen to run the exact same MySQL version + OS version (although I'm
doing a huge upgrade to 5.1.34 today), and I have not experienced any such
issues. Granted, I only get ~60 queries/s, so your traffic is probably
much higer.

At any rate, the first place I'd look is for open files limit (and
'sysctl' it to higher if it's not sufficient). I'm always amazed how much
open files MySQL keeps. The amount of files MySQL reserves, way I recall,
is also directly related to max. connections; so you could lower that too,
temporarily, to see if it makes the error go away.

- Mark


-Original Message-
From: VeeJay [mailto:maan...@gmail.com] 
Sent: dinsdag 28 april 2009 11:27
To: Mark
Cc: mysql@lists.mysql.com
Subject: Re: 1135: Can't create a new thread (errno 35); if you are not out
of available memory, you can consult the manual for a possible OS-dependent
bug

Hi Mark

Yes, you are right. I should have provided complete information in order to
get help...

I am running

DB:  Server version: 5.0.77-log FreeBSD port: mysql-server-5.0.77_1
OS:  FreeBSD 7.1

On Tue, Apr 28, 2009 at 10:44 AM, Mark ad...@asarian-host.net wrote:

 It would probably help if you told folks what MySQL version you are
 running.
 :)

 - Mark


 -Original Message-
 From: VeeJay [mailto:maan...@gmail.com]
 Sent: dinsdag 28 april 2009 10:22
 To: mysql@lists.mysql.com; VeeJay
 Subject: 1135: Can't create a new thread (errno 35); if you are not out of
 available memory, you can consult the manual for a possible OS-dependent
 bug

 Hello guys and gurus

 I am keep getting this error after a while *1135: Can't create a new
 thread
 (errno 35); if you are not out of available memory, you can consult the
 manual for a possible OS-dependent bug*


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=maan...@gmail.com




-- 
Thanks!

BR / vj


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: 1135: Can't create a new thread (errno 35); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

[Mark]

On my FreeBSD:

usr/include/errno.h:#define EAGAIN  35 /* Resource temporarily unavailable
*/
/usr/include/errno.h-#ifndef _POSIX_SOURCE
/usr/include/errno.h-#define EWOULDBLOCK EAGAIN /* Operation would block */

I still wouldn't discount the files resources limit; though obviously
I bow to Jörg's expertise on the matter. :)

- Mark


-Original Message-
From: Martin Gainty [mailto:mgai...@hotmail.com] 
Sent: dinsdag 28 april 2009 14:44
To: Joerg Bruehe; maan...@gmail.com
Cc: ad...@asarian-host.net; mysql@lists.mysql.com
Subject: RE: 1135: Can't create a new thread (errno 35); if you are not out
of available memory, you can consult the manual for a possible OS-dependent
bug


Jörg

mgai...@martini ~
FGIN.sh 35 | fgrep errno

find: File system loop detected; `/usr/include/gnome-xml/libxml' is part of
the
same file system loop as `/usr/include/gnome-xml'.
/usr/include/apr-1/apr_errno.h:806:#define SOCEWOULDBLOCK
(SOCBASEERR+3
5)/* Operation would block */
/usr/include/mingw/errno.h:60:/* 35 - Unknown Error */

Vielen Danke!
Martin 
__ 
Verzicht und Vertraulichkeitanmerkung 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.







 Date: Tue, 28 Apr 2009 12:26:47 +0200
 From: joerg.bru...@sun.com
 Subject: Re: 1135: Can't create a new thread (errno 35); if you are not
out of  available memory, you can consult the manual for a possible
OS-dependent bug
 To: maan...@gmail.com
 CC: ad...@asarian-host.net; mysql@lists.mysql.com
 
 Hi VeeJay, Mark, all,
 
 
 VeeJay wrote:
  Hi Mark
  
  Yes, you are right. I should have provided complete information in order
to
  get help...
  
  I am running
  
  DB:  Server version: 5.0.77-log FreeBSD port: mysql-server-5.0.77_1
  OS:  FreeBSD 7.1
  
  On Tue, Apr 28, 2009 at 10:44 AM, Mark ad...@asarian-host.net wrote:
  
  It would probably help if you told folks what MySQL version you are
  running.
  :)
 
  - Mark
 
 
  -Original Message-
  From: VeeJay [mailto:maan...@gmail.com]
  Sent: dinsdag 28 april 2009 10:22
  To: mysql@lists.mysql.com; VeeJay
  Subject: 1135: Can't create a new thread (errno 35); if you are not out
of
  available memory, you can consult the manual for a possible
OS-dependent
  bug
 
  Hello guys and gurus
 
  I am keep getting this error after a while *1135: Can't create a new
  thread
  (errno 35); if you are not out of available memory, you can consult the
  manual for a possible OS-dependent bug*
 
 Why don't you believe the mesage?
 It clearly says possible OS-dependent bug, so the OS is where to look..
 
 Also, 35 is a pretty low number, so I expect it to be an errno as
 provided by the operating system.
 (Which agrees with the message text, that says errno 35.)
 
 
 I don't have FreeBSD available, on my Linux the code 35 means EDEADLK:
   /usr/include/asm-generic/errno.h:6:#define  EDEADLK 35
   /* Resource deadlock would occur */
 
 
 For such searches, I have my tiny script FGIN:
 
   #! /bin/sh
   #
   # FGIN  shell script to use fgrep on all
 /usr/include/h files
   #
   # $1, $2, ...   options and arguments given to fgrep
   #
   # Simple example:
   #FGIN seteuid   return the line(s) in system header files where
   #   'seteuid()' is defined or otherwise mentioned.
   #
   # 2004-09-13  Joerg Bruehe  Initial published version
 
   find /usr/include -follow -name '*.h' -print | xargs fgrep -n $*
 
 As 35 my be a common term, I do
   FGIN 35 | fgrep errno
 
 I propose you do something similar on your machine.
 
 Granted, this still doesn't tell you what exactly happened, but it
 should help to narrow the possible causes.
 
 
 HTH,
 Jörg
 
 -- 
 Joerg Bruehe,  MySQL Build Team,
joerg.bru...@sun.com
 Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
 Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
 

_
Rediscover Hotmail®: Get e-mail storage that grows with you. 
http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Sto
rage2_042009


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Start MySQL with --intit-file?

[mark konetchy]

hey jason,

you need to restart the *server* with the init-file option, have a look at:

http://dev.mysql.com/doc/refman/5.0/en/server-options.html



2009/4/28 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com

 I am trying to start MySQL with --init-file but i get that it is an invalid
 option. the 'man' page and --help dont help me decide what is.

 Here is what I am doing:

 r...@server1 ~]# mysql start --init-file = cloudsql.txt
 mysql: unknown option '--init-file'

 Thoughts?

 -Jason

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=markkonet...@gmail.com

RE: Error : Incorrect key file for table 'X'

[Mark]

I found out it's actually a documented bug, for folks upgrading from 5.0.x
to 5.1.x,

 

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

 

And occurs with tables originally created with 4.0.x. There's currently no
fix, it seems.

There's a patch, but dev-comments to that patch suggest it can cause
crashes.

 

-  Mark

 

 

From: zhu dingze [mailto:mysql.li...@gmail.com] 
Sent: zondag 26 april 2009 16:40
To: Mark
Cc: mysql@lists.mysql.com
Subject: Re: Error : Incorrect key file for table 'X'

 

Hey Dude,

 

So many errors imply that there are some issues with your meta_data other
than the tables and files itself.

Please note, MySQL has upgrade some information in Information_schema
Database.

May I ask which kind of data have you been backup? including
information_schema.

 

 

 

 

Bests,

Dingze

2009/4/25 Mark ad...@asarian-host.net

C'mon, guys, there's gotta be someone who knows something about this. :)
Anyone? Anyone? Bueller? Bueller? I even did a complete mysqldump (from
the 5.0.77 mysqld) and imported it that way. To no luck, alas.

Thanks,

- Mark



-Original Message-
From: Mark [mailto:ad...@asarian-host.net]
Sent: zaterdag 25 april 2009 2:34
To: mysql@lists.mysql.com
Subject: Error : Incorrect key file for table 'X'

Odd. I just painstakingly upgraded to MySQL 5.1.34, coming from 5.0.77.

So, I run: /usr/local/bin/mysql_upgrade, and get these all over the

place (see below; just a few lines: the error list is too long to post in

whole). So, accomodating MySQL, I run:



/usr/local/bin/myisamchk --force --extend-check --update-state -O

key_buffer=128M -O sort_buffer=128M -O read_buffer=2M -O write_buffer=2M

/var/db/mysql/*/*.MYI



Makes no difference, though. All the tables are unusable now (thank God

for Vmware!). But obviously, I can't take it to the producton server like

this (FreeBSD). Anyone any idea what's happening?



Thanks,



- Mark







Repairing tables

albatross.banned

Error: Incorrect key file for table 'banned'; try to repair it

error: Corrupt

albatross.bridge

Error: Incorrect key file for table 'bridge'; try to repair it

error: Corrupt

albatross.categories

Error: Incorrect key file for table 'categories'; try to repair it

error: Corrupt

albatross.comments

Error: Incorrect key file for table 'comments'; try to repair it

error: Corrupt

albatross.config

Error: Incorrect key file for table 'config'; try to repair it

error: Corrupt

albatross.dict

Error: Incorrect key file for table 'dict'; try to repair it

error: Corrupt

albatross.ecards

Error: Incorrect key file for table 'ecards'; try to repair it

error: Corrupt

albatross.exif

Error: Incorrect key file for table 'exif'; try to repair it

error: Corrupt

albatross.favpics

Error: Incorrect key file for table 'favpics'; try to repair it

error: Corrupt

albatross.filetypes

Error: Incorrect key file for table 'filetypes'; try to repair it

error: Corrupt

albatross.hit_stats

Error: Incorrect key file for table 'hit_stats'; try to repair it

error: Corrupt

albatross.plugins

Error: Incorrect key file for table 'plugins'; try to repair it

error: Corrupt



Etc, etc.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mysql.li...@gmail.com

 

SOLVED! (was: RE: Error : Incorrect key file for table 'X')

[Mark]

Okay, I finally got it fixed. I was on the right track going the mysqldump
route, but turns out I had to physically do a 'rm -rf' on the mysql dir
(sans the mysql database itself; and after mysqld was shut down, of
course), even though I had mysqldump add command to DROP existing
databases first. After that, the import succeeds, and all errors are gone.

Odd. It's been quite quiet on this list of late. I expected at least
someone to know the answer. If this is indicative of people's interest in
MySQL these days, then maybe it really IS a good idea to start using
PostgreSQL.

- Mark


-Original Message-
From: Mark [mailto:ad...@asarian-host.net] 
Sent: zondag 26 april 2009 17:04
To: mysql@lists.mysql.com
Subject: RE: Error : Incorrect key file for table 'X'

I found out it's actually a documented bug, for folks upgrading from 5.0.x
to 5.1.x,

 

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

 

And occurs with tables originally created with 4.0.x. There's currently no
fix, it seems.

There's a patch, but dev-comments to that patch suggest it can cause
crashes.

 

-  Mark

 

 

From: zhu dingze [mailto:mysql.li...@gmail.com] 
Sent: zondag 26 april 2009 16:40
To: Mark
Cc: mysql@lists.mysql.com
Subject: Re: Error : Incorrect key file for table 'X'

 

Hey Dude,

 

So many errors imply that there are some issues with your meta_data other
than the tables and files itself.

Please note, MySQL has upgrade some information in Information_schema
Database.

May I ask which kind of data have you been backup? including
information_schema.

 

 

 

 

Bests,

Dingze

2009/4/25 Mark ad...@asarian-host.net

C'mon, guys, there's gotta be someone who knows something about this. :)
Anyone? Anyone? Bueller? Bueller? I even did a complete mysqldump (from
the 5.0.77 mysqld) and imported it that way. To no luck, alas.

Thanks,

- Mark



-Original Message-
From: Mark [mailto:ad...@asarian-host.net]
Sent: zaterdag 25 april 2009 2:34
To: mysql@lists.mysql.com
Subject: Error : Incorrect key file for table 'X'

Odd. I just painstakingly upgraded to MySQL 5.1.34, coming from 5.0.77.

So, I run: /usr/local/bin/mysql_upgrade, and get these all over the

place (see below; just a few lines: the error list is too long to post in

whole). So, accomodating MySQL, I run:



/usr/local/bin/myisamchk --force --extend-check --update-state -O

key_buffer=128M -O sort_buffer=128M -O read_buffer=2M -O write_buffer=2M

/var/db/mysql/*/*.MYI



Makes no difference, though. All the tables are unusable now (thank God

for Vmware!). But obviously, I can't take it to the producton server like

this (FreeBSD). Anyone any idea what's happening?



Thanks,



- Mark







Repairing tables

albatross.banned

Error: Incorrect key file for table 'banned'; try to repair it

error: Corrupt

albatross.bridge

Error: Incorrect key file for table 'bridge'; try to repair it

error: Corrupt

albatross.categories

Error: Incorrect key file for table 'categories'; try to repair it

error: Corrupt

albatross.comments

Error: Incorrect key file for table 'comments'; try to repair it

error: Corrupt

albatross.config

Error: Incorrect key file for table 'config'; try to repair it

error: Corrupt

albatross.dict

Error: Incorrect key file for table 'dict'; try to repair it

error: Corrupt

albatross.ecards

Error: Incorrect key file for table 'ecards'; try to repair it

error: Corrupt

albatross.exif

Error: Incorrect key file for table 'exif'; try to repair it

error: Corrupt

albatross.favpics

Error: Incorrect key file for table 'favpics'; try to repair it

error: Corrupt

albatross.filetypes

Error: Incorrect key file for table 'filetypes'; try to repair it

error: Corrupt

albatross.hit_stats

Error: Incorrect key file for table 'hit_stats'; try to repair it

error: Corrupt

albatross.plugins

Error: Incorrect key file for table 'plugins'; try to repair it

error: Corrupt



Etc, etc.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mysql.li...@gmail.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Error : Incorrect key file for table 'X'

[Mark]

C'mon, guys, there's gotta be someone who knows something about this. :)
Anyone? Anyone? Bueller? Bueller? I even did a complete mysqldump (from
the 5.0.77 mysqld) and imported it that way. To no luck, alas.

Thanks,

- Mark


-Original Message-
From: Mark [mailto:ad...@asarian-host.net] 
Sent: zaterdag 25 april 2009 2:34
To: mysql@lists.mysql.com
Subject: Error : Incorrect key file for table 'X'

Odd. I just painstakingly upgraded to MySQL 5.1.34, coming from 5.0.77.

So, I run: /usr/local/bin/mysql_upgrade, and get these all over the

place (see below; just a few lines: the error list is too long to post in

whole). So, accomodating MySQL, I run:

 

/usr/local/bin/myisamchk --force --extend-check --update-state -O

key_buffer=128M -O sort_buffer=128M -O read_buffer=2M -O write_buffer=2M

/var/db/mysql/*/*.MYI

 

Makes no difference, though. All the tables are unusable now (thank God

for Vmware!). But obviously, I can't take it to the producton server like

this (FreeBSD). Anyone any idea what's happening?

 

Thanks,

 

- Mark

 

 

 

Repairing tables

albatross.banned

Error: Incorrect key file for table 'banned'; try to repair it

error: Corrupt

albatross.bridge

Error: Incorrect key file for table 'bridge'; try to repair it

error: Corrupt

albatross.categories

Error: Incorrect key file for table 'categories'; try to repair it

error: Corrupt

albatross.comments

Error: Incorrect key file for table 'comments'; try to repair it

error: Corrupt

albatross.config

Error: Incorrect key file for table 'config'; try to repair it

error: Corrupt

albatross.dict

Error: Incorrect key file for table 'dict'; try to repair it

error: Corrupt

albatross.ecards

Error: Incorrect key file for table 'ecards'; try to repair it

error: Corrupt

albatross.exif

Error: Incorrect key file for table 'exif'; try to repair it

error: Corrupt

albatross.favpics

Error: Incorrect key file for table 'favpics'; try to repair it

error: Corrupt

albatross.filetypes

Error: Incorrect key file for table 'filetypes'; try to repair it

error: Corrupt

albatross.hit_stats

Error: Incorrect key file for table 'hit_stats'; try to repair it

error: Corrupt

albatross.plugins

Error: Incorrect key file for table 'plugins'; try to repair it

error: Corrupt

 

Etc, etc.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Error : Incorrect key file for table 'X'

[Mark]

Odd. I just painstakingly upgraded to MySQL 5.1.34, coming from 5.0.77.

So, I run: /usr/local/bin/mysql_upgrade, and get these all over the

place (see below; just a few lines: the error list is too long to post in

whole). So, accomodating MySQL, I run:

 

/usr/local/bin/myisamchk --force --extend-check --update-state -O

key_buffer=128M -O sort_buffer=128M -O read_buffer=2M -O write_buffer=2M

/var/db/mysql/*/*.MYI

 

Makes no difference, though. All the tables are unusable now (thank God

for Vmware!). But obviously, I can't take it to the producton server like

this (FreeBSD). Anyone any idea what's happening?

 

Thanks,

 

- Mark

 

 

 

Repairing tables

albatross.banned

Error: Incorrect key file for table 'banned'; try to repair it

error: Corrupt

albatross.bridge

Error: Incorrect key file for table 'bridge'; try to repair it

error: Corrupt

albatross.categories

Error: Incorrect key file for table 'categories'; try to repair it

error: Corrupt

albatross.comments

Error: Incorrect key file for table 'comments'; try to repair it

error: Corrupt

albatross.config

Error: Incorrect key file for table 'config'; try to repair it

error: Corrupt

albatross.dict

Error: Incorrect key file for table 'dict'; try to repair it

error: Corrupt

albatross.ecards

Error: Incorrect key file for table 'ecards'; try to repair it

error: Corrupt

albatross.exif

Error: Incorrect key file for table 'exif'; try to repair it

error: Corrupt

albatross.favpics

Error: Incorrect key file for table 'favpics'; try to repair it

error: Corrupt

albatross.filetypes

Error: Incorrect key file for table 'filetypes'; try to repair it

error: Corrupt

albatross.hit_stats

Error: Incorrect key file for table 'hit_stats'; try to repair it

error: Corrupt

albatross.plugins

Error: Incorrect key file for table 'plugins'; try to repair it

error: Corrupt

 

Etc, etc.

RE: A good US Hosting Site?

[Mark]

Okay, does anyone know of a hosting site that supports 'Image::Magick'?
(the Perl package). The salesrep at AwardSpace said they didn't have it.

P.S. This is probably getting a mite off-topic. So, feel free to reply to
me off-list, if you happen to know the answer.

Thanks,

- Mark


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: A good US Hosting Site?

[Mark]

-Original Message-
From: Claudio Nanni [mailto:claudio.na...@gmail.com] 
Sent: maandag 20 april 2009 10:28
To: p...@pwilson.net
Cc: mysql@lists.mysql.com; Cameron Rogers; step...@kionic.com
Subject: Re: A good US Hosting Site?

 www.awardspace.com

 I have both free and paid hosting and it is really really good, it has a
lot
 of tools.
 
 Claudio

http://www.free-webhosts.com/reviews/AwardSpace.php

 Maximum file size limit 500 KB

Please, tell me that's a joke.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: A good US Hosting Site?

[Mark]

-Original Message-
From: Claudio Nanni [mailto:claudio.na...@gmail.com] 
Sent: maandag 20 april 2009 17:22
To: Mark
Cc: mysql@lists.mysql.com
Subject: Re: A good US Hosting Site?

 Well,
 
 not a joke for free hosting.
 
 But it is, for me, by far the best hosting site.
 
 Compleat, easy to use, five subdomains, and also mysql included.
 
 Paid hosting is outstanding.

Yeah, but a Maximum file size limit 500 KB, what can you do with that??
Your average background image is bigger! Not to mention my MySQL
databases. Sorry to say, but a 500KB file limit makes hosting there
useless, IMHO.

- Mark


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: A good US Hosting Site?

[Mark]

-Original Message-
From: Claudio Nanni [mailto:claudio.na...@gmail.com] 
Sent: maandag 20 april 2009 22:29
To: Mark
Cc: mysql@lists.mysql.com
Subject: Re: A good US Hosting Site?

 Mark wrote:

  Yeah, but a Maximum file size limit 500 KB, what can you do with
  that?? Your average background image is bigger! Not to mention my
  MySQL
   
 really? you use background images bigger than 500kb? lucky if you
 get a second visit! mysql database of course has nothing to do with
 that limit.

Yes, really. :) Besides, it's not just images, also all kinds of data
files, like .swf and such: 500KB is just ridiculously small. I have a
client here with an elegant photo gallery, and a quick 'find' shows that
13% of all its files are  500KB.

Fortunately, it seems that absurd limit just exists for the free hosting.
So, I may consider them after all, as there's no such thing as a free
lunch, and their paid plans seem very reasonable.

And, indeed, looks like their MySQL databases were never included in
said limit to begin with.

- Mark


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: MySQL runs on 16-cores server

[Mark]

-Original Message-
From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf
Of Baron Schwartz
Sent: dinsdag 14 april 2009 15:18
To: mos
Cc: Jerry Schwartz; Andy Smith; mysql@lists.mysql.com
Subject: Re: MySQL runs on 16-cores server

Mike,

 Now the SSD that I would like to have is the Hyperdrive 5 from
 http://www.hyperossystems.co.uk/. It is a DDR SSD and each drive has
 slots for 8 DIMM's which means it can hold up to 32GB (64GB if you can
 find 8GB DDR2's) per drive.

Too bad these aren't SCSI drives, so they could be used with Vmware ESX 3.5.

- Mark


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: if there're a tool which can replace mysql-proxy?

[Mark Matthews]

On Mar 24, 2009, at 8:06 AM, Claudio Nanni wrote:


Question:



Hello Claudio,

my company tried the mysql-proxy about one year ago(may be more) but  
could

not use it

for not being multithreaded. They say they spoke to the 'mysql-proxy'
developer.

Is this still true? Are there any limitation on using mysql proxy on  
a high

load production server?


It's still not multi-threaded, but work is progressing in this area,  
see:


https://lists.launchpad.net/mysql-proxy-discuss/msg00041.html

There are people using it on high-load production servers, for various  
flavors of high load. How much impact *any* proxy will have depends  
a lot on the type of workload you run through it, and what you do with  
the data while it's in the proxy itself, since what's going to hurt  
you performance-wise is directly related to latency, caused by the  
extra network hop, and anything else you do that delays the data  
being forwarded.




Will it be completely(almost) transparent?


Once again, that depends on what you do to the data flowing through  
it. The only major non-transparent part of the proxy is the  
permissions system, in that clients connecting through the proxy will  
always *appear* to be connecting *from* the proxy from mysqld's point  
of view, since there is no way to forward the client address to  
mysqld itself.


For proxy-related questions, you'll probably get more detailed,  
quicker responses if you join the launchpad project's mailing list at:


https://launchpad.net/~mysql-proxy-discuss

If you're considering using mysql-proxy, I highly recommend tracking  
the project via the mailing list and staying in touch with the  
developers and the community, to both get a better idea if it's going  
to work for your situation in it's current (and always changing)  
state, and also to provide input into the direction of the developers.


Best regards,

-Mark
--
Mark Matthews, Architect - Enterprise Tools
MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Mysqld fails to start

[Mark Goodge]

JD wrote:

Hello List,

mysqld fails to start, and emits the following
error messages in /var/log/mysqld.log

090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from 
/var/lib/mysql

/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run 
mysql_upgrade to create it.
090127 10:00:30  InnoDB: Operating system error number 13 in a file 
operation.

InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.


That's most likely to be the real error. Or, at least, the most 
important one.



InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
090127 10:00:30 mysqld_safe mysqld from pid file 
/var/run/mysqld/mysqld.pid ended


So, I tried to run mysql_upgrade:
# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck'...
mysqlcheck: Got error: 2002: Can't connect to local MySQL server through 
socket '/var/lib/mysql/mysql.sock' (2) when trying to connect

FATAL ERROR: Upgrade failed

So, it seems like a chicken and egg thing???
Cannot run mysql_upgrade bcause the server is not running.
Cannot run the server because I must first run mysql_upgrade.


I think you're being misled by the fact that there are two errors: A 
missing table and a file system that mysqld doesn't have access to. Fix 
the file permissions first, and then try starting mysqld. You'll then be 
able to run mysql_upgrade to fix the missing table problem (which is a 
warning, not a fatal error, hence why you need to have the server 
running to be able to fix it).


Mark
--
http://www.good-stuff.co.uk - Stuff, some of it good

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Optimizing nullable expiration dates

[Mark Goodge]

Norman Elton wrote:

I've got a table that tracks expiration dates. Currently, if the
record hasn't expired, the column is stored as NULL. In order to find
records that haven't expired, I search for WHERE expire_at  NOW() OR
expire_at IS NULL. This seems dirty, and I suspect it makes my
indexes very angry.

I suppose I could pick some arbitrary future date (Dec 31 2999) to use
in place of NULL.

Is there a better way? Surely I'm not the first to run into this!


If the field is only updated when the record expires, then a simple 
where expire_at is NULL will do. You only need to compare expiration 
dates with NOW() if they can be in the future - which, from your 
description, isn't possible.


If an expiry date can be either future, past or non-existent, then your 
existing selector is as good as any.


Mark
--
http://mark.goodge.co.uk - my pointless blog
http://www.good-stuff.co.uk - my less pointless stuff

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

Re: normalised designs: customer database

[Mark Goodge]

metastable wrote:

Hello all,


I have a question that's been bugging me for quite some time.
Let's say we have a small business that has both private and corporate
customers.
We want to store contact and address data about these customers, as well
as invoicing data. Off course, only companies have VAT numbers.


It's more complex than that, because there isn't a clear distinction 
between individuals and companies. A customer may be a company, a 
partnership, a charity, a sole trader or a non-trading individual. All 
but the non-trading individual may have (but are not necessarily 
required to have) a VAT number.



When normalising this design, you would reach something like the following:

- table for contact details (separate, because multiple contact details
may apply)
- table for address  details (separate, because multiple addresses may
apply)
- table for people (first name, last name, etc)
- table for companies (company name and vat number)
- tables that link the above data to each other (people-contact,
people-address, people-company, company-address, ...)
- table for customers, i.e. 'entities' that are invoiced


What do you guys think about this ? Which option is most viable ? Which
solution have you chosen ?


What I'd do is have a table for customer, which is essentially the 
invoice information (since that is, effectively, what defines a customer 
to you). I'd have separate tables for alternate addresses and contacts. 
The customer table would have a field defining the customer type, and 
another field containing an id which is the key to further information 
about that customer in the relevant table, where necessary.


I wouldn't try to arbitrarily normalise the database for SQL efficiency. 
In a real-life situation, it's more important that the database design 
reflects your actual workflow and business requirements. Having a field 
that's empty 50% or more of the time is far less of a problem than not 
being able to process a sale because your database structure is too 
inflexible :-)


Mark
--
http://mark.goodge.co.uk - my pointless blog
http://www.good-stuff.co.uk - my less pointless stuff

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

Re: Most efficient way of handling a large dataset

[Mark Goodge]

Joerg Bruehe wrote:

Hi Mark, all!


Mark Goodge wrote:

I'd appreciate some advice on how best to handle a biggish dataset
consisting of around 5 million lines. At the moment, I have a single
table consisting of four fields and one primary key:

partcode varchar(20)
region varchar(10)
location varchar(50)
qty int(11)
PRIMARY KEY (partcode, region, location)

The biggest variable is partcode, with around 80,000 distinct values.
For statistical purposes, I need to be able to select a sum(qty) based
on the other three fields (eg, select sum(qty) from mytable where
partcode ='x' and region = 'y' and location = 'z') as well as
generating a list of partcodes and total quantities in each region and
location (eg, select sum(qty), partcode from mytable where region = 'y'
and location = 'z' group by partcode).


Sorry, I don't get it - I think there is a contradiction in your mail:

Your table has four fields, three of which form the primary key.
This means that for any combination of values for those three fields
(partcode, region, location) there will be at most one row, and so only
one qty value. Why do you want to sum over one value?


Sorry, my mistake. I mean that I need to be able to sum any combination 
of two from three of the first three fields.



And in your second query there is also no need for sum(qty), a plain
qty will do because for each group there will be only one row (region
and location are set to fixed values in your statement).

IMO, the main question is whether all your statements use fixed values
for region and location (like your second statement), but only some do
for partcode (your first statement).


No; any of the three can be fixed or not, as the case may be.


If that holds true for your statements, then your primary key is defined
in the wrong order: it should have partcode as the last field (= the
least significant one). Then, all your statement could use the primary
key, and you need no separate index.


[[...]]

Does anyone have any suggestions? My initial thought is to replace the
region and location varchar fields with int fields keyed to a separate
list of region and location names. Would that help, or is there a better
way?


Well, if your data are integer values, then using integer as column type
should speed up your operations considerably:
Operations (including comparisons) on integers are faster than on
character strings, and reduced data size means shorter (= faster)
transfers and more elements in caches (assuming same cache size).


At the moment, only the qty is an integer value. The others are strings 
(a variable-length alphanumeric for partcode, and county/town names for 
region and location). For presentation reasons, I need to display the 
human-readable names of the region and location, but this could be done 
by means of having the names in a separate table with integer ids that's 
joined to the main table when querying.


Mark
--
http://mark.goodge.co.uk - my pointless blog
http://www.good-stuff.co.uk - my less pointless stuff

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

Most efficient way of handling a large dataset

[Mark Goodge]

I'd appreciate some advice on how best to handle a biggish dataset 
consisting of around 5 million lines. At the moment, I have a single 
table consisting of four fields and one primary key:


partcode varchar(20)
region varchar(10)
location varchar(50)
qty int(11)
PRIMARY KEY (partcode, region, location)

The biggest variable is partcode, with around 80,000 distinct values. 
For statistical purposes, I need to be able to select a sum(qty) based 
on the other three fields (eg, select sum(qty) from mytable where 
partcode ='x' and region = 'y' and location = 'z') as well as 
generating a list of partcodes and total quantities in each region and 
location (eg, select sum(qty), partcode from mytable where region = 'y' 
and location = 'z' group by partcode).


The selection is done via a web-based interface. Unfortunately, it's too 
slow. So I want to be able to optimise it for faster access. Speed of 
updating is less crucial, as it isn't updated in real-time - the table 
gets updated by a nightly batch job that runs outside normal working 
hours (and, apart from the rare occasion when a location is added or 
removed, the only thing that changes is the value in qty).


Does anyone have any suggestions? My initial thought is to replace the 
region and location varchar fields with int fields keyed to a separate 
list of region and location names. Would that help, or is there a better 
way?


Mark
--
http://mark.goodge.co.uk - my pointless blog
http://www.good-stuff.co.uk - my less pointless stuff

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

Re: quick question on innodb_log_file_size

[Mark Leith]

Jenny Chen wrote:

Hi,

According to the reference manual, it was said that the combined lnnodb log
file size is less than 4G on 32-bit system. But I'm running on my 64-bit
solaris, I still got the error complaining the innodb log file 4G for my
64-bit MySQL. So I'm wondering is this 4G limit apply on 64-bit system as
well? Why?
  


Yes.

[EMAIL PROTECTED]:~/mysql/mysql-5.1/storage] $ grep -irn 
innobase_log_file_size ./*
./innobase/handler/ha_innodb.cc:105:static long long 
innobase_buffer_pool_size, innobase_log_file_size;
./innobase/handler/ha_innodb.cc:1466:   if 
(innobase_log_file_size  UINT_MAX32) {
./innobase/handler/ha_innodb.cc:1468:   
innobase_log_file_size can't be over 4GB
./innobase/handler/ha_innodb.cc:1580:   srv_log_file_size = (ulint) 
innobase_log_file_size;
./innobase/handler/ha_innodb.cc:8106:static 
MYSQL_SYSVAR_LONGLONG(log_file_size, innobase_log_file_size,


The docs are wrong - I'll have that updated.

Best regards

Mark

--
Mark Leith
MySQL Regional Support Manager, Americas
Sun Microsystems, Inc., http://www.sun.com/mysql/ 



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