Re: Complex Query

[Johan De Meersman]

Heh. The parser is pointing out a simple syntax oversight, yes. The correct 
syntax for that is select ... from (subselect) aliasname;

- Original Message -
> From: "Mimi Cafe" 
> To: "Johan De Meersman" , "Guido Schlenke" 
> 
> Cc: mysql@lists.mysql.com
> Sent: Friday, 20 May, 2011 11:52:14 PM
> Subject: RE: Complex Query
> 
> Select count(*) from (select) did not work. It says: "ERROR
> 1248 (42000): Every derived table must have its own alias" and I am
> not sure it really mean aliases.
> 
> Although "select found_rows();" works, I am trying to avoid it as the
> documentation says it may produce expected result - especially after
> running a query with MySQL limit clause.
> 
> Mimi
> 
> => -Original Message-
> => From: Johan De Meersman [mailto:vegiv...@tuxera.be]
> => Sent: 20 May 2011 21:16
> => To: Guido Schlenke
> => Cc: mysql@lists.mysql.com
> => Subject: Re: Complex Query
> =>
> => Hmm. Simply replacing the field list with count(*) should work,
> too.
> => If you only need the count after having executed the select, I'm
> => pretty sure there's something in the API that gives you that
> without a
> => second query, although I'll be buggered if I can remember right
> now.
> =>
> => - Original Message -
> => > From: "Guido Schlenke" 
> => > To: mysql@lists.mysql.com
> => > Sent: Friday, 20 May, 2011 9:04:32 PM
> => > Subject: Re: Complex Query
> => >
> => > Hi Mimi,
> => >
> => > try this
> => >
> => > select count(*) from
> => > (SELECT module_nr, IFNULL(image,'no_image.jpg'),title,
> subtitle,
> => > group_concat(concat(fname,' ',initial,' ',lname)), pkisbn,
> => >
> => > publisher,publication_year, edition, cover_type, pages FROM
> book
> => > INNER
> => > JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN
> => > module_book on
> => >
> => > pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr =
> => module_nr
> => > INNER
> => > JOIN book_author on pkisbn = fkisbn INNER JOIN author on
> fkauthor_id
> => > =
> => > pkauthor_id
> => >
> => > WHERE module_nr = ? group by
> IFNULL(image,'no_image.jpg'),title,
> => > subtitle, pkisbn, publisher);
> => >
> => > Guido
> => >
> => > "Mimi Cafe"  schrieb im Newsbeitrag
> => > news:004701cc16f1$a2d550c0$e87ff240$@com...
> => >
> => > Hi
> => >
> => >
> => >
> => > I now need to determine the number of rows returned by this
> query
> => > below.
> => > Wrapping it within SELECT COUNT did not work as expected.
> => >
> => >
> => >
> => >
> => >
> => > SELECT module_nr, IFNULL(image,'no_image.jpg'),title,
> subtitle,
> => > group_concat(concat(fname,' ',initial,' ',lname)), pkisbn,
> => >
> => > publisher,publication_year, edition, cover_type, pages FROM
> book
> => > INNER
> => > JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN
> => > module_book on
> => >
> => > pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr =
> => module_nr
> => > INNER
> => > JOIN book_author on pkisbn = fkisbn INNER JOIN author on
> fkauthor_id
> => > =
> => > pkauthor_id
> => >
> => > WHERE module_nr = ? group by
> IFNULL(image,'no_image.jpg'),title,
> => > subtitle, pkisbn, publisher;
> => >
> => >
> => >
> => > Mimi
> => >
> => >
> => >
> => > From: Mimi Cafe [mailto:mimic...@googlemail.com]
> => > Sent: 20 May 2011 11:33
> => > To: 'Anupam Karmarkar'
> => > Cc: 'Guido Schlenke'; mysql@lists.mysql.com
> => > Subject: RE: Complex Query
> => >
> => >
> => >
> => > Hi guys
> => >
> => >
> => >
> => > That's cool! It looks like my query was good except that I miss
> the
> => > "group
> => > by".  Now I only had to remove the "as image" from the grouping
> => below
> => > and it
> => > works fine.
> => >
> => >
> => >
> => > Thanks guys
> => >
> => >
> => >
> => > Mimi
> =

Re: Complex Query

[Johan De Meersman]

Hmm. Simply replacing the field list with count(*) should work, too. If you 
only need the count after having executed the select, I'm pretty sure there's 
something in the API that gives you that without a second query, although I'll 
be buggered if I can remember right now.

- Original Message -
> From: "Guido Schlenke" 
> To: mysql@lists.mysql.com
> Sent: Friday, 20 May, 2011 9:04:32 PM
> Subject: Re: Complex Query
> 
> Hi Mimi,
> 
> try this
> 
> select count(*) from
> (SELECT module_nr, IFNULL(image,'no_image.jpg'),title, subtitle,
> group_concat(concat(fname,' ',initial,' ',lname)), pkisbn,
> 
> publisher,publication_year, edition, cover_type, pages FROM book
> INNER
> JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN
> module_book on
> 
> pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr
> INNER
> JOIN book_author on pkisbn = fkisbn INNER JOIN author on fkauthor_id
> =
> pkauthor_id
> 
> WHERE module_nr = ? group by IFNULL(image,'no_image.jpg'),title,
> subtitle, pkisbn, publisher);
> 
> Guido
> 
> "Mimi Cafe"  schrieb im Newsbeitrag
> news:004701cc16f1$a2d550c0$e87ff240$@com...
> 
> Hi
> 
> 
> 
> I now need to determine the number of rows returned by this query
> below.
> Wrapping it within SELECT COUNT did not work as expected.
> 
> 
> 
> 
> 
> SELECT module_nr, IFNULL(image,'no_image.jpg'),title, subtitle,
> group_concat(concat(fname,' ',initial,' ',lname)), pkisbn,
> 
> publisher,publication_year, edition, cover_type, pages FROM book
> INNER
> JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN
> module_book on
> 
> pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr
> INNER
> JOIN book_author on pkisbn = fkisbn INNER JOIN author on fkauthor_id
> =
> pkauthor_id
> 
> WHERE module_nr = ? group by IFNULL(image,'no_image.jpg'),title,
> subtitle, pkisbn, publisher;
> 
> 
> 
> Mimi
> 
> 
> 
> From: Mimi Cafe [mailto:mimic...@googlemail.com]
> Sent: 20 May 2011 11:33
> To: 'Anupam Karmarkar'
> Cc: 'Guido Schlenke'; mysql@lists.mysql.com
> Subject: RE: Complex Query
> 
> 
> 
> Hi guys
> 
> 
> 
> That's cool! It looks like my query was good except that I miss the
> "group
> by".  Now I only had to remove the "as image" from the grouping below
> and it
> works fine.
> 
> 
> 
> Thanks guys
> 
> 
> 
> Mimi
> 
> 
> 
> From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com]
> Sent: 20 May 2011 07:48
> To: 'Guido Schlenke'; mysql@lists.mysql.com; Mimi Cafe
> Subject: RE: Complex Query
> 
> 
> 
> 
> Hi Guido,
> 
> You need to add group by in your query get desire result
> 
> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle,
> group_concat(concat(fname,' ',initial,' ',lname)) as author, pkisbn,
> publisher FROM book INNER JOIN publisher on pkpublisher_id =
> fkpublisher_id
> INNER JOIN module_book on pkisbn = fkbook_isbn INNER JOIN module
> on
> fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn
> INNER JOIN
> author on fkauthor_id = pkauthor_id
> WHERE module_nr = 'MSING0010'
> group by IFNULL(image,'no_image.jpg') as image,title, subtitle,
> pkisbn,
> publisher
> 
> It should give desire result. As group concat will return group by 1
> if you
> dont specify group by, which will return only one row, to get result
> you
> need to specify group by as given in above query.
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Changing the default database location

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" 
> 
> so put the wgole mysqld and its data on a server in the network
> for this mysql was built and not for borking the dadadir somewhere
> else

Hmm. The way I interpret what he's saying, is that he wants multiple instances 
accessing the same datafiles. If that's the case, Firdosh, don't - MySQL is not 
made for concurrent access to the same datafiles. If multiple 
people/applications need access to the same data, let them connect to the same 
server. If that's not an option, you're going to have to look at replication.

I repeat, there is no setup possible where it is safe to have multiple 
instances of mysqld access the same datafiles.

If you do, the first thing you'll notice is that one instance doesn't see the 
other instance's update. The second thing you'll notice, is that your data 
files will be FUBAR. Fucked Up Beyond Any Recognition. Not kidding, here.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Changing the default database location

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" 
> 
> first: please post log-outputs instead of "don't work"
> 
> i guess: you changed only the path in my.cnf
> have you oved th existing datadir to the new location?
> if not the server will not start because it is missing
> the database "mysql" with permissions, users... and will
> say this in the error-log

That's pretty much the most likely explanation, yes.

On another note, be aware that putting the data files on a network drive is 
generally considered a bad idea, as because a) performance is going to suffer, 
and b) if the network borks the daemon is going to get a bit confused (read: 
you will lose data).


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Interesting bug/oversight

[Johan De Meersman]

- Original Message -
> From: "Dan Nelson" 
> 
> I doubt that mysql calls anything other than gethostbyname() or
> getaddrinfo(), so your behaviour is probably dependant on whatever OS
> you are running and how often its local resolver re-checks resolv.conf.
> Usually that's only once when a program starts.  If you're running bind,
> nscd, or some other intermediate DNS client on your machine, bouncing that
> should work.  If not, you'll need to bounce mysql.

Yep, that was my first though, too. The documentation also confirms that the 
daemon calls gethostbyaddr() and gethostbyname(). However, as I said, it failed 
to switch to the new nameserver upon changing the resolv.conf, and didn't until 
I kicked the daemon in the olives.

Production machine also pointed to the wrong DNS server, but since I can't just 
restart that (badly written Java apps go boom) it still hasn't switched. Adding 
the correct entry to /etc/hosts does work around the issue, further confirming 
that yes, it probably does use the standard resolver.

Random *nix people in the meantime confirm that this is not only a MySQL 
problem; although I can't help but wonder if it would be possible to work 
around it in the flush hosts procedure.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Interesting bug/oversight

[Johan De Meersman]

- Original Message -

> From: "Claudio Nanni" 

> Consider also the DNS TTL.
That should be irrelevant when changing DNS servers :-) 

> If you flush hosts in MySQL it'll ask again the OS to resolve a name
> , but if that is still in the DNS cache it could return that 'old'
> value instead of querying the newly updated NS.
I know, but it's another DNS server so not applicable. Also, I did verify on 
the commandline :-) 

> I'm not sure thou, may be test by restarting the name server cache
> deamon /etc/rc.d/init.d/nscd restart
Not running local caching. The host only runs MySQL which has it's own cache, 
so that would be a useless layer. 

Nice try :-) 

-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Re: Restore only one database or one table

[Johan De Meersman]

- Original Message -
> From: "Suresh Kuna" 
> 
> Try to take a tab separated dump, so you can restore what ever you
> want in terms of tables or databases.

Uhh. I'm a bit fuzzy today, but I really don't see how a tab-separated dump 
will help split off tables or databases :-)


To answer the original question, though; the technically accurate answer is 
"yes, you can". It's made "easy" because mysqldump conveniently dumps 
database-per database and table-per table. It's a bugger to do, however, 
because if you take a monolithic dump you need to open the whole dumpfile in a 
text editor and copy the data you want to another file or straight to the MySQL 
commandline. Good luck with your 250G backup :-)

You can use sed or awk to look for markers and split the file up that way. 
You'd be much better off in the future to dump database-per-database, and if 
you think you need it table-per-table. 's Not all that hard, just script to 
loop over the output of show databases and show tables. Probably plenty of 
scripts on the internet that do exactly that.

Compressing your dumps is a good idea, too - the output is a text file, so 
bzip2 will probably compress that a factor 10 or better. Simply use bzcat to 
pipe the file back into the MySQL client to restore.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Interesting bug/oversight

[Johan De Meersman]

Just encountered an interesting issue. 

I use DNS names instead of IPs in mysql grants. Yes, I'm aware of the 
performance impact, that's not an issue. 

I just found out through failing logins that a server was still connecting to 
an old DNS server, and properly updated the resolv.conf. Commandline host 
lookups then returned correct results. 

However, even after repeated flush hosts commands, the MySQL kept returning 
wrong results. Only after a full restart did it pick itself up and start doing 
proper lookups. I strongly suspect that this is due to it internally caching 
the nameserver, too, and not refreshing that along with the host cache on a 
flush hosts command. 

Can anyone confirm this is the case, and wether or not a bug has been logged 
about it? I can't seem to find one. 

-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Re: [setting value when INSERT for auto increment]

[Johan De Meersman]

If you're asking what I think you're asking, then yes, both NULL and 0 will 
trigger an autoincrement field to put in the next value.

- Original Message -
> From: "Grega Leskovšek" 
> To: mysql@lists.mysql.com
> Sent: Monday, 16 May, 2011 4:49:43 PM
> Subject: [setting value when INSERT for auto increment]
>
> Should it be null?
> INSERT INTO `friendlyCMS`.`log` (`idlog`, `imepriimek`, `clock`,
> `action`, `onfile`, `filesize`) VALUES (NULL, $_COOKIE['user'],
> CURRENT_TIMESTAMP, 'saved',$filename, filesize($filename));
> idlog is primaryk ey auto inrement not null...
> When insertin the value what should I pass it? NULL? Thanks in
> advance!!

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
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

[Johan De Meersman]

- Original Message -
> From: "Gavin Towey" 
> 
> The server will disconnect idle connections after a while.  The
> wait_timeout variable controls how many seconds it will wait.  You
> can set it for your connection when you connect by issuing a query
> like:
> 
> SET SESSION wait_timeout=NNN;
> 
> Just give it a large enough value.

That will, of course, work; but it has as side effect that improperly 
terminated connections (application crash, ...) will stay around for that time, 
too.

It is better to have your connection (-pooling) code be aware of connection 
timeouts, and have it transparantly reconnect. If I recall correctly, the 
client library has a ping() function or something similar.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 Backup solution for non-technical user

[Johan De Meersman]

Zmanda ZRM backup, although the fancy webinterface is only available in the 
commercial version. Backups are stored on the host that runs the server, and of 
course it serves multiple MySQL machines.

Webinterface is annoyingly slow, though :-)


- Original Message -
> From: "Michael Heaney" 
> To: mysql@lists.mysql.com
> Sent: Tuesday, 10 May, 2011 9:58:43 PM
> Subject: Re: MySQL Backup solution for non-technical user
> 
> Check out Zmanda:   http://zmanda.com/zrm-mysql-enterprise.html
> 
> 
> Michael Heaney
> JCVI
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 based upon LIKE

[Johan De Meersman]

- Original Message -
> From: "Jerry Schwartz" 
> 
> I'm not sure that I could easily build a dictionary of non-junk
> words, since

The traditional way is to build a database of junk words. The list tends to be 
shorter :-)

Think and/or/it/the/with/like/...

Percentages of mutual and non-mutual words between two titles should be a 
reasonable indicator of likeness. You could conceivably even assign value to 
individual words, so "polypropylbutanate" is more useful than "synergy" for 
comparison purposes.

All very theoretical, though, I haven't actually done much of it to this level. 
My experience in data mangling is limited to mostly should-be-fixed-format data 
like sports results.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 based upon LIKE

[Johan De Meersman]

http://www.gedpage.com/soundex.html offers a simple explanation of what it does.

One possibility would be building a referential table with only a recordID and 
soundex column, unique over both; and filling that with the soundex of 
individual nonjunk words.

So, from the titles

1 | Rain in Spain
2 | Spain's Rain

you'd get

1 | R500
1 | S150
2 | S150
2 | R500

>From thereon, you can see that all the same words have been used - ignoring a 
>lot of spelling errors like Spian. Obviously not a magic solution, but it's a 
>start.

- Original Message -
> From: "Jerry Schwartz" 
> To: "Johan De Meersman" 
> Cc: "Jim McNeely" , "mysql mailing list" 
> 
> Sent: Monday, 2 May, 2011 4:09:36 PM
> Subject: RE: Join based upon LIKE
> 
> [JS] I've thought about using soundex(), but I'm not quite sure how.
> 
> I didn't pursue it much because there are so many odd terms such as
> chemical
> names, but perhaps I should give it a try in my infinite free time.
> 
> 
> [JS] Thanks for your condolences.
> 
> Regards,
> 
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
> 
> 860.674.8796 / FAX: 860.674.8341
> E-mail: je...@gii.co.jp
> Web site: www.the-infoshop.com
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 based upon LIKE

[Johan De Meersman]

- Original Message -
> From: "Jerry Schwartz" 
> 
> I shove those modified titles into a table and do a JOIN ON
> `prod_title` LIKE
> `wild_title`.

Roughly what I meant with the shadow fields, yes - keep your own set of data 
around :-)

I have little more to offer, then, I'm afraid. The soundex() algorithm may or 
may not be of some use to you; it offers comparison based (roughly) on 
pronounciation instead of spelling.

Apart from that, you have my deepest sympathy. I hope you can wake up from the 
nightmare soon :-)

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 based upon LIKE

[Johan De Meersman]

- Original Message -
> From: "Jerry Schwartz" 
> 
> [JS] This isn't the only place I have to deal with fuzzy data. :-(
> Discretion prohibits further comment.

Heh. What you *really* need, is a LART. Preferably one of the spiked variety.

> A full-text index would work if I were only looking for one title at
> a time, but I don't know if that would be a good idea if I have a list of
> 1 titles. That would pretty much require either 1 separate queries
> or a very, very long WHERE clause.

Yes, unfortunately. You should see if you can introduce a form of data 
normalisation - say, shadow fields with corrected entries, or functionality in 
the application that suggests correct entries based on what the user typed.

Or, if the money's there, you could have a look at Amazon Mechanical Turk (yes, 
really) for cheap-ish data correction.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 based upon LIKE

[Johan De Meersman]

- Original Message -
> From: "Jerry Schwartz" 
> 
> No takers?

Not willingly, no :-p

This is a pretty complex problem, as SQL itself isn't particularly 
well-equipped to deal with fuzzy data. One approach that might work is using a 
fulltext indexing engine (MySQL's built-in ft indices, or an external one like 
Solr or something) and doing best-fit matches on the keywords of the title 
you're looking for.
 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: left join two tables

[Johan De Meersman]

Hey there, 

- Original Message -

> From: "Rocio Gomez Escribano" 

> Hi!! Is it possible to create a left join consult with 2 tables??

> I mean:

> SELECT * FROM table1 LEFT JOIN (table2, table3) on table1.ID =
> table2.subID and table1.ID= table3.subID
Pretty close already. Might I suggest sampling the fine manual ? 

Have a look at http://dev.mysql.com/doc/refman/5.0/en/join.html 

-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Re: Запрос

[Johan De Meersman]

At which point I used google translate to ask the switch to english :-p

- Original Message -
> From: "Andre Polykanine" 
> To: "Johan De Meersman" 
> Cc: "Виктор Ефимович" , mysql@lists.mysql.com
> Sent: Thursday, 28 April, 2011 12:58:18 PM
> Subject: Re: Запрос
>
> Hello Johan,
>
> Sorry guys, that's just because I've seen familiar letters :-).
> the topic starter asked how he can "quit from this application". and
> I
> asked, from which application he wants to quit. That's all :-).
>

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Запрос

[Johan De Meersman]

Я предлагаю более отчетливо английски применения :-p

- Original Message -
> From: "Andre Polykanine" 
> To: "Виктор Ефимович" 
> Cc: mysql@lists.mysql.com
> Sent: Thursday, 28 April, 2011 12:04:01 PM
> Subject: Re: Запрос
> 
> Hello Виктор,
> 
> Из какого приложения?)
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: PHP Generator for MySQL 11.4 released

[Johan De Meersman]

- Original Message -
> From: "walter harms" 
> 
> maybe but what is mysql 11.4 ?

A parsing error :-)

> the release of (PHP Generator for MySQL) 11.4

That should make more sense, I think.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 in accept: Too many open files

[Johan De Meersman]

300 is pretty low - MySQL counts every instance of a table in any query as an 
"open file". A query that uses the same table twice (with an alias, for 
example) thus counts for two open files.

This may also be outside of MySQL, the ulimit for the user running the daemon 
may have open files restrictions, too.


- Original Message -
> From: "Brent Clark" 
> To: "mysql mailing list" 
> Sent: Tuesday, 26 April, 2011 9:44:16 AM
> Subject: Error in accept: Too many open files
> 
> Hiya
> 
> I recently imported data on a new server (5.1). But before the
> importing
> I added the feature 'innodb_file_per_table'.
> 
> I now want to enable Master - Master replication, but the problem is,
> im
> seeing the following
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 deal with specail character problem

[Johan De Meersman]

- Original Message -
> From: "Steve Staples" 
> 
> Doesn't the '?-1-1'  mean that it's a joined key?  so the 3

That's what I tought, but I *can* see the characters he's typed, and the last 
of what you see as ? is definitely different.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 deal with specail character problem

[Johan De Meersman]

- Original Message -
> From: "赵琦" 
>
> it is strange, the primary key field is not the same, but i get this
> error.

I'm entirely unsure how MySQL handles non-roman, so I'll start off with a 
stupid question: are you sure there was no previous entry in the table with 
that value for a ?


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
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 avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination...

[Johan De Meersman]

The smoothest way to avoid deadlocks, is to ensure that all your sessions lock 
their tables in exactly the same order. From your explanation, that might not 
be as easy as one would expect, though.

If you can't create triggers, is it acceptable to have delayed updates on the 
totals? Your idea was good, but the classic way to go about that is 
materialized views - in this case also known as aggregate tables. You simply 
run a cron job every hour or whatever that drops and recreates a (temporary) 
table holding all the summaries you'll ever need.

Sure, the build job is heavy - but it only runs once every so often; and 
building an aggregate of ten subset is not as heavy as running the aggregate 
query for every subset separately, so there's a definite win. If you can live 
with 24h old data, just run the aggregate build somewhere during the slow hours.


- Original Message -
> From: "Andrés Tello" 
> To: "Mailing-List mysql" 
> Sent: Wednesday, 20 April, 2011 9:15:09 PM
> Subject: How to avoid deadlocks.. advice needed also insight, illumination 
> and a bit of hallucination...
>
> I'm running into some deadlocks issues.
>
> I have this structure
> accounting
>
> To know the balance of the account, I usualy do a
> sum(movements.amount)
> where accounting.id=someid
>
> The issue is that the sum is starting to run very slow due hardware
> constraints, and I can't trow more hardware :(, so I need to find a
> software
> solution
>
> My approach was to create a balance field inside accounting, but I'm
> running
> into deadlocks, because accounting has a tree structure based upon
> accounting.id and accounting.parentid.
>
> Btw, I can't use triggers or store procedure due  reason
> here>. Out of discussion.
>
> So, to have all the accounts in balance, I do a drill down to get all
> paretns, grandparent from an account...
> So I think, I have race conditions and multiversioning issues.
> I read about innodb locking and decided to use lock in share mode...
> But I'm runing with some deadlocks:
>
> TRANSACTION 0 264994, ACTIVE 1 sec, process no 5031, OS thread id
> 140061201196816 starting index read
> mysql tables in use 1, locked 1
> LOCK WAIT 151 lock struct(s), heap size 30704, 36974 row lock(s)
> MySQL thread id 62743, query id 35566790 localhost 127.0.0.1 vortex
> Updating
> UPDATE `account` SET `balance` = '-3961.30' WHERE `accountid` ='408'
> LIMIT 1
> *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of
> table
> `management`.`account` trx id 0 264994 lock_mode X locks rec but not
> gap
> waiting
> Record lock, heap no 19 PHYSICAL RECORD: n_fields 14; compact format;
> info
> bits 0
>  0: len 4; hex 0198; asc ;; 1: len 6; hex 0001b314; asc
>;;
> 2: len 7; hex 3b0b18; asc ;  ;; 3: len 4; hex 0194;
> asc
> ;; 4: len 1; hex 02; asc  ;; 5: len 20; hex
> 414e4149535f5a41495a41525f414c434152415a; asc LOANS;; 6: SQL NULL; 7:
> SQL
> NULL; 8: len 1; hex 00; asc  ;; 9: len 1; hex 01; asc  ;; 10: len 3;
> hex
> 8fb56d; asc   m;; 11: len 1; hex 00; asc  ;; 12: len 1; hex 80; asc
>  ;; 13:
> len 9; hex 7086e1; asc  ;;
>
> *** (2) TRANSACTION:
> TRANSACTION 0 264995, ACTIVE 1 sec, process no 5031, OS thread id
> 140061201999632 starting index read, thread declared inside InnoDB
> 500
> mysql tables in use 1, locked 1
> 4360 lock struct(s), heap size 456688, 579126 row lock(s)
> MySQL thread id 60636, query id 35566800 localhost 127.0.0.1 vortex
> Updating
> UPDATE `account` SET `balance` = '1.31' WHERE `accountid` ='11009'
> LIMIT 1
> *** (2) HOLDS THE LOCK(S):
> RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of
> table
> `management`.`account` trx id 0 264995 lock mode S locks rec but not
> gap
> Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format;
> info
> bits 0
>  0: len 4; hex 0001; asc ;; 1: len 6; hex 00040aba; asc
>;;
> 2: len 7; hex 3c1b0a; asc <  ;; 3: SQL NULL; 4: len 1;
> hex 01;
> asc  ;; 5: len 6; hex 41435449564f; asc ACTIVO;; 6: len 1; hex 31;
> asc 1;;
> 7: SQL NULL; 8: len 1; hex 00; asc  ;; 9: len 1; hex 01; asc  ;; 10:
> len 3;
> hex 8faa7d; asc   };; 11: len 1; hex 01; asc  ;; 12: len 1; hex 80;
> asc  ;;
> 13: len 9; hex 78f72f3efa; asc   /> ;;
>
>
> Any clues about how to avoid this?
>

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Out of sync tables

[Johan De Meersman]

- Original Message -
> From: "Gary" 
> 
> I'm not sure I undertand this, could you explain a little further for
> me.

This is what they're talking about: 
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Practical connection limits MySQL 5.1/5.5

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" 
> 
> even if you have enough memory why will you throw it away for a
> unusual connection count instead use the RAm for innodb-buffer-pool,
> query-cache, key-buffers?

Maybe the application doesn't have support for connection pooling and can't be 
easily replaced. Maybe there's just that much clients instead of a central 
service. Maybe there's not just a single application that uses that database.

As usual, Harald, you fail to realise that your experience does not encompass 
the whole of human civilisation. You seem to have a good technical background, 
but it might be useful to learn to consider problems from the point of view of 
the people who have them, at times. It tends to be a much appreciated skill in 
the real world.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Any table visualization tools with wires connecting the actual columns?

[Johan De Meersman]

- Original Message -
> From: "Daevid Vincent" 
> 
> It only seems to do the lines for InnoDB tables, not MyISAM... I
> mean, it not only won't auto-connect them, it won't even allow ME to connect
> them. :(

Probably because it wants to adhere to the engine capabilities, and MyISAM 
doesn't have referential integrity.

MySQL Workbench seems to do it right, though. It may or may not complain at 
apply time, but it does seem to save the relations you set, even for MyISAM 
tables. See attached file as example :-)

The tool is freely downloadable from the MySQL site.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel


test.mwb
Description: Zip archive

-- 
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 common request

[Johan De Meersman]

- Original Message -
> From: "mos" 
> 
> The IN() clause is very inefficient because MySQL will NOT use the
> index.
> It will have to traverse the entire table looking for these values.

Has that still not been remedied ?

> It will get the information from the index and not have to access the
> record data from disk. If the index is stored in memory, then it
> won't have to go to disk (unless you also have a sort). That is why the query
> cache is so important.

Indices don't go in the query cache, strictly speaking, they go in the MyISAM 
index cache or in the InnoDB pool.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 common request

[Johan De Meersman]

- Original Message -
> From: "Gregory Magarshak" 
> I am guessing that the MySQL indexes map indexed fields (fb_uid) to the
> primary key (id) so I wouldn't have to touch the disk. Am I right
> about that?

Correct for InnoDB, but MyISAM maps every index straight onto records. That's 
why secondary indices are slightly more performant on MyISAM tables - InnoDB 
needs to do the extra primary key lookup - which it of course is bloody good at 
:-)


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: getting procedure code via mysqldump

[Johan De Meersman]

Might it not be easier to use something like "show create procedure" instead? 
Given that the purpose is debugging, I would assume you want the exact text 
used to create the procedure, not the one with version-specifics removed.

You can still pump that into a file by using "mysql -e 'show create procedure 
procname\G' dbname > outputfile.sql". There will still be a bit of superfluous 
information as this is an information request, but that should be easily 
removed with some sed hacking.


- Original Message -
> From: "Claudio Nanni" 
> To: "Shawn Green (MySQL)" 
> Cc: "John G. Heim" , mysql@lists.mysql.com
> Sent: Wednesday, 30 March, 2011 9:01:06 AM
> Subject: Re: getting procedure code via mysqldump
> 
> In case you use a linux or unix system, to strip off the comments in
> linux
> bash is very easy, you can use this simple bash command:
> 
> grep -v "^/\*" yourdumpfile.sql > yourdumpfilewithoutcomments.sql
> 
> this will create a new dump without comments.
> 
> Cheers
> 
> Claudio
> 
> 
> 2011/3/30 Shawn Green (MySQL) 
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Question about Backup

[Johan De Meersman]

You are assuming that the database is one table of 5.000 gigabyte, and not 
5.000 tables of one gigabyte; and that the backup needs to be consistent :-p


- Original Message -
> From: "Reindl Harald" 
> To: mysql@lists.mysql.com
> Sent: Monday, 21 March, 2011 12:44:08 PM
> Subject: Re: Question about Backup
> 
> Forget mysqldump because TABLE LOCKS for so hughe databases
> I would setup a replication-slave because you can stop
> the salave and make a filesystem-backup of the whole db-folder
> while the production server is online, we do this with our
> dbmail-server since 2009
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 using aio/raw device on linux

[Johan De Meersman]

- Original Message -
> From: "Chao Zhu" 
> 
>One Q: Can mysql binlog use raw device on Linux?

Mmm, good question. Don't really know; but I'm not convinced you'll get huge 
benefits from it, either. Modern filesystems tend to perform pretty close to 
raw throughput.

>From a just-thinking-it-through point of view, I'd guess no - mysqld never 
>seems to open binlogs for append, it always opens a new one. This may have 
>something to do with the way replication works; not to mention the question of 
>what'll happen if the log is full - it's not a circular buffer.

> Can we use asynch IO for binlog writing? sequential non-qio fsync is slowing 
> our throughput...

Mmm... Theoretically, yes, you could use an async device (even nfs over UDP if 
you're so inclined) but async means that you're going to be losing some 
transactions if the server crashes.

You can also tweak 
http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#sysvar_sync_binlog
 - basically, this controls how often the binlog fsyncs. Same caveat applies, 
obviously: set this to ten, and you'll have ten times less fsyncs, but you risk 
losing ten transactions in a crash.

If your binlogs are async, then you also risk having slaves out of sync if your 
master crashes.


Personally, if your binlogs are slowing you down, I would recommend putting 
them on faster storage. Multiple small, fast disks in RAID10 are going to be 
very fast, or you could invest in solid state disks - not all that expensive 
anymore, really. Maybe even just a RAM disk - you'll lose data when the machine 
crashes (and need an initscript for save/load of the data on that disk), but 
not if just the mysqld crashes.


Weigh the benefits of each option very, very carefully against the risk of 
losing data before you go through with this.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Suggestions for InnoDB files

[Johan De Meersman]

- Original Message -

> From: "Adarsh Sharma" 

> Johan De Meersman wrote:
> A Heartiest Thanks from my heart for explaining all these things in a
> fantastic manner. I agreed with your suggestions but one thing which
> isn't explained from your side , as you go deeper in RAID point.

> Q:- What is your recommendations for number of ibdata files , would
> it be
> Make sure the disk /hdd2-1/innodb_data1 is big enough and it doesn't
> affect performance.
Roughly, yes - file-per-table is only useful if you need to be able to reclaim 
the space for non-InnoDB data; and I don't think InnoDB stripes across 
datafiles, so just use one large file on one RAID partition. Saves on file 
descriptors, saves on header space. 

-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Re: mysql binlog format

[Johan De Meersman]

- Original Message -
> From: "Brent Clark" 
> 
>   'Statement may not be safe to log'

Heh. Some of those statements weren't particularly safe in previous versions, 
either, but they didn't whine :-p

Roughly, what it comes down to is that statements that contain things that may 
be different on master and slave at the respective time of execution might 
replicate differently. For some things (like date() calls) this is solved by 
replacing them with a constant before they get logged (now() is replaced with 
the actual timestamp, for example), while for other things (unORDERed selects) 
it may not be a simple.

Row logging is another take on it: instead of logging the statement, it logs 
the actual changes, thereby neatly sidestepping the problem. This brings other 
issues, though.

> The question I would like to ask is. Is it safe to just change the binlog 
> format? Or is a preprocedure that needs to first be 
> exercised.
> And then lastly, if the change is made, and something goes wrong. Is there a 
> role back procedure, or can I just change the binlog
> format back to STATEMENT.

AFAIK, the log format is automatically detected by the slave, so there should 
be no problem in switching between the two (well, three) forms. The very 
existence of Mixed format logging is actually proof of that, in a sense :-)

If "something goes wrong", however, it's a reasonable assumption that your 
replication got messed up, so it's highly recommended to reinitialize the slave 
at that point, as wel as changing back to statement-based logs.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Suggestions for InnoDB files

[Johan De Meersman]

> From: "Adarsh Sharma" 
>
> Johan De Meersman wrote:
> > Interesting, but why like this instead of simply larger disks or raidsets ?
>
> It's the IT-Admin Issue , I can't question that and we have only disks of 
> 300GB ( SAS ).

Your admin is supposed to provide services that benefit the application you 
need to run on the server. You're stuck with the hardware, but not the setup.


> > Why would you use 8G datafiles instead of large, partition-filling ones?
>
> What is your recommendations for number of ibdata files , keeping in Mind 
> Raid10 is not used and the size of tables .
> Because in RAID10 :
>
> We can utilize 50 – 55 percent size of hard disk.(50-55 % of 4 hard disk 
> total space if hard disks are 500 GB X 4 then we can
> utilize only 1 TB space from 2 TB.

Correct. That's the price you pay for the performance and redundancy RAID10 
gives you. Nothing is free in life :-) Incidentally, it's going to be exactly 
50% - I'll be very interested to see where he pulls those extra 5% from.

You could ostensibly go for RAID5, which will allow you to use 1.5 TB off those 
same four disks, at a minor loss of disk redundancy (only one may fail) and 
some loss of performance - but still better than no RAID at all. If you want to 
lose no space at all, use RAID0 (striping) to increase performance, but that 
offers no disk redundancy at all - single disk fails, you lose all data.

As a small overview, RAID 10 gives you the benefits of striping (data for a 
single file is split over multiple disks) so reads and writes faster, AND of 
mirrorring (every block is available on multiple disks, which provides 
insurance data loss when a disk breaks and additionally increases the read 
speed even more. You won't actually quadruple the read speed, but I wouldn't be 
surprised to see it triple on a 4-disk RAID 10.

RAID 5 uses one of your disks for redundancy purposes, so any single disk may 
fail and you'll still have all your data. Data is striped, so disk performance 
also increases, although not as much as mirrorring. This is however the most 
CPU-intensive form, as checksumming over all disks happens at every write. This 
also makes that write speed won't see as much benefit.

RAID 0 has no redundancy whatsoever - if anything you could say it's worse than 
data over multiple disks, because if one disk fails the entire volume is lost. 
Because it offers striping, however, it gives performance a good boost.


> Software RAID is not reliable on production environment because software raid 
> is dependent on hardware and software both thing
> if one thing go down then it will not work, but in hardware raid there is no 
> role of software every thing is depend on hardware.
> But, We are not able to afford Hardware RAID.

Maybe you shouldn't have an OS then, either; because if that fails everything 
is down? My word, if that's his excuse, I seriously recommend you get a better 
admin.

Software RAID offers the same or better performance than hardware RAID, save 
for the real high-end RAID cards. Additionally it offers more flexibility in 
the setup - many combinations of RAID levels are possible, whereas the majority 
of controllers offer 1, 5 and 10 at most.

An additional benefit that is not to be laughed at, especially if you're on a 
budget, is that software RAID will work regardless of the hardware involved. 
Hardware RAID controllers tend to have their own specific set of metadata on 
the disks, and if your controller breaks, you had better manage to get the 
exact same one, or you risk not being able to read your disks. Sofware RAID, by 
virtue of being software, can simply be reinstalled on another system if need 
be. Tell MD to scan for and assemble RAID arrays and it'll just find the 
appropriate partitions and match the pieces together. No more accidentally 
putting a disk in the wrong bay and having it break the RAIDset. (I'll admit 
that has become rare with controllers getting smarter over the years, but I've 
seen multi-terabyte arrays go useless because some idiot operator switched two 
disks into the wrong bays)


So, yes, my recommendation remains the same: switch the system to software 
RAID; preferably 10, 5 or 0 if you really need all that space.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Suggestions for InnoDB files

[Johan De Meersman]

- Original Message -
> From: "Adarsh Sharma" 
> 
> Dear all,
> 
> I have doubt regarding the storage structure for Innodb files :
> 
> Our database server has the following paths :
> 
> /dev/sda5  69G   35G   32G52% /hdd1-1
> /dev/sdb1 274G  225G   36G  87% /hdd2-1
> /dev/sdc5 274G  225G   36G  87% /hdd3-1
> /dev/sdd5 274G  218G   43G  84% /hdd4-1
> /dev/sde1 266G  184G   69G  73% /hdd5-1

Interesting, but why like this instead of simply larger disks or raidsets ?

> Is it better to have innodb_file_per_table on.
>   or
> innodb_data_file_path =
> /hdd2-1/innodb_data1/ibdata1:8G;/hdd3-1/innodb_data1/ibdata2:8G;/hdd4-1/innodb_data1/ibdata3:8G;/hdd2-1/innodb_data1/ibdata4:8G;
[unmanageable mess cut]

Why would you use 8G datafiles instead of large, partition-filling ones?

> which is currently set because to increase performance to read from
> separate small files instead of reading from one large one because
> one table is expected to grow more than 300 GB & some tables are near
> about 60-80 GB & increasing day by day.

I should check up on InnoDB internals wether it strips across datafiles, but 
from a disk point of view, many smaller files aren't likely to be faster than 
one large one.

> Make sure the disk /hdd2-1/innodb_data1 is big enough
>  /hdd2-1/innodb_data1 is going to need be a large RAID10 set

A good RAID10 is recommended for databases anyway; I suggest you go with that.

> What is the best configuration for them so that we doesn't hit
> performance issues.

Performance issues are oftentimes more dependant on how you use the DB than how 
you set it up; but a good setup never hurt anyone, of course.

Consider throwing all those disk partitions into a single RAID10 set, either 
through underlying hardware or using MD on Linux. Even if you already have 
hardware RAID under those devices and can't modify that, consider concatenating 
the individual devices with LVM to benefit from striping.

InnoDB file-per-table should yield roughly the same performance as global 
datafiles, albeit with more file descriptors used. If you want to be able to 
reclaim space, go for file-per-table; if all space is for InnoDB anyway, 
monolithic storage might be slightly more convenient. I seem to recall InnoDB 
can use raw devices, too; I'm not sure wether there's a big performance gain, 
though. I that Oracle has stepped down from recommending that in recent years, 
stating only marginal gains.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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"

[Johan De Meersman]

- Original Message -
> From: "Sándor Halász" 
>
> Yes, but Access s "IIF", of the same use, evaluates all three, and
> the documentation explicitly says so. MySQL s, that I have seen,
> says neither. Assuming the worst is safer, and then one uses CASE
> ..., but if not, 

Well, they never have been very good at this whole "common sense" thing, have 
they. Sleep with the dog, get bitten by it's lice.

>

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Backup Policy

[Johan De Meersman]

- Original Message -
> From: "Krishna Chandra Prajapati" 
> 
> incremental backup using zamanda.

I'm running Zmanda on about two dozen hosts, and it comes well-recommended. It 
doesn't do anything that you can't do yourself, but it's easy to set up, 
reports well and backs up in what are basically tarfiles, so if all else fails 
you can still do manual restores.

If you have plenty of servers, the enterprise version adds a web interface that 
the free doesn't have - not a huge thing, but very nice to have. Support is 
reasonably responsive, albeit at times a bit... well, prone to asking the 
obvious :-)

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Script to mail output of select query

[Johan De Meersman]

- Original Message -
> From: "Adarsh Sharma" 
> 
> Please check the attachment for the script & output.

Thanks for your password :-)

> Now I just want to mail the output of my script to some persons
> e-mail-ID

Assuming you run this from crontab, just set MAILTO=per...@domain.ext right 
before the script execution line.

> Also, I want to do some calculations and provide the information of
> how much increase data after 1 day daily.

Uhh. Munin? Cacti? Output CSV-formatted data and append to a file, then process 
using a spreadsheet? Insert the data into a table in your DB and play with 
that? Plenty of ways to do that, all depends on what you want and need. The key 
is that you're going to have to keep historical data for that somewhere.

Strictly speaking, also not on topic here :-)


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Unexpected Select Output

[Johan De Meersman]

- Original Message -

> From: "Adarsh Sharma" 

> I am able to fetch the output individually, but I try that I access
> all information through one command :

> mysql> SELECT table_schema 'database',table_name 'Table', concat(
> round( sum( data_length + index_length ) / ( 1024*1024*1024) , 2 ) ,
> 'G' ) sizeGB,concat( round( sum( data_length + index_length ) / (
> 1024*1024) , 2 ) , 'M' ) sizeMB,concat( round( sum( data_length +
> index_length ) / ( 1024) , 2 ) , 'K' ) sizeKB FROM
> information_schema.TABLES WHERE (table_name ='hc_categories')
> -> OR (table_name='hc_master') OR (table_name='hc_source') OR
> (table_name='job_queue') OR (table_name='master_seed')
> -> OR (table_name='metadata') OR (table_name='page_content') OR
> (table_name='page_crawled') OR (table_name='url_statistics')
> -> OR (table_name='website_authentication') OR
> (table_name='website_internalurl') OR (table_name='website_master');

Hmmm. I hadn't noticed yet, but you're using sum() in there, and you're not 
using a group by - I'm pretty sure this isn't your full query; and the addition 
of a group by clause would explain why you only get one row. 

Sum() does vertical summing, not horizontal; that is, it sums all values of the 
same column per GROUP BY resultset, not the values you give it for each row. 
You don't want sum(data_length + index_length) , you just want (data_length + 
index_length) , I bet. 

Try this: 

SELECT table_schema 'database', table_name 'Table', 
concat(round((data_length + index_length) / (1024*1024*1024), 2), 'G' ) sizeGB, 
concat(round((data_length + index_length) / (1024*1024), 2), 'M') sizeMB, 
concat(round((data_length + index_length) / (1024) ,2), 'K') sizeKB 
FROM information_schema.TABLES 
WHERE table_schema = 'pdc_crawler' 
AND table_name IN ('hc_categories', 'hc_master', 'hc_source', 'job_queue', 
'master_seed', 'metadata', 'page_content', 
'page_crawled', 'url_statistics', 'website_authentication', 
'website_internalurl', 'website_master'); 

-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Re: Unexpected Select Output

[Johan De Meersman]

Probably not the cause, but you should know that and binds more tightly than 
or, so what you've written is actually 

WHERE (table_name = 'hc_categories') 
OR (table_name = 'hc_master') 
OR (table_name = 'hc_web' AND table_schema = 'pdc_crawler') 
What you probably mean is 

WHERE (table_name = 'hc_categories' OR table_name = 'hc_master' OR table_name = 
'hc_web') 
AND (table_schema = 'pdc_crawler') 

As to why the query doesn't provide the three tables... not really an idea - 
the contents of your information_schema.tables table might provide insight :-) 

- Original Message -

> From: "Adarsh Sharma" 
> To: mysql@lists.mysql.com
> Sent: Tuesday, 15 March, 2011 5:56:17 AM
> Subject: Unexpected Select Output

> Dear all,

> Today I shoot a query to know the size of tables in a particular
> database, but don't know why it prints only the output of only one
> table. Here is my query & output :

> mysql> SELECT table_schema 'database',table_name 'Table', concat(
> round(
> sum( data_length + index_length ) / ( 1024*1024*1024) , 2 ) , 'G' )
> sizeGB,concat( round( sum( data_length + index_length ) / (
> 1024*1024) ,
> 2 ) , 'M' ) sizeMB,concat( round( sum( data_length + index_length ) /
> (
> 1024) , 2 ) , 'K' ) sizeKB FROM information_schema.TABLES WHERE
> table_name ='hc_categories' or table_name='hc_master' or
> table_name='hc_web' and table_schema='pdc_crawler';
> -> ;
> +-+---++++

> +-+---++++

> +-+---++++

> Why it is not able to print all table that fits in OR condition.

> Thanks & best Regards,

> Adarsh Sharma

-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Re: "IF"

[Johan De Meersman]

- Original Message -
> From: "Sándor Halász" 
>
> Does the _function_ 'IF' always evaluate its arguments? or only the
> two that it is needful to evaluate?

I'm afraid I'm not authoritative on this, but it seems to me that it would be 
very very bad if the third, unused expression were to be evaluated - not only 
from a performance point of view, but who is to say that that expression is not 
a user-defined function that modifies the database ?



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Table Records Deleted by anonymous user!

[Johan De Meersman]

> From: "Vikram A" 

> Thank you for info. Now we enabled the logs. The DB administrator
> itself made a mistake that he restored the back up

This may be obvious, but keep your logs on separate disks if you can - full 
query logs take quite a bit of I/O away, so if you have them on the same disks 
as your data they may have a significant impact on performance. 

-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Re: Table Records Deleted by anonymous user!

[Johan De Meersman]

- Original Message -
> From: "Vikram A" 
>
> say that it is done intentionally but could not point anyone because
> we did not enable the logging feature in MySQL.

You already said it yourself: you don't have logging enabled, so that data is 
not available.

If you have binary logs, you could comb through those for the delete 
statements; that'll at least give you a timeframe.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
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 5.1 -> 5.0

[Johan De Meersman]

Just like that, not advisable. There's upgrade scripts in the packages that 
should handle 5.0 to 5.1; but your safest bet is still going to be a clean 
mysqldump and import.

- Original Message -
> From: "Brent Clark" 
> To: mysql@lists.mysql.com
> Sent: Thursday, 10 March, 2011 2:07:11 PM
> Subject: Mysql 5.1 -> 5.0
> 
> Hiya
> 
> We have client that is using Ubuntu, therefore MySQL is 5.1, but
> where I
> work, we still standardise on Debian Lenny (upgrading to Squeeze, is
> in
> the pipeline), therefore, MySQL is 5.0.
> 
> What I would like to know is, can I just make a copy of the mysql
> database files and copy them the Lenny box?
> 
> If anyone could help / answer, it would be appreciated.
> 
> Kind Regards
> Brent Clark
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 apache md5

[Johan De Meersman]

Umm... I'm no crypto guru, but I've never heard of MD5 having variants, let 
alone a salt. MD5 is MD5 is MD5. APR, incidentally, is the Apache Runtime, 
afaik - part of the build kit for apache modules.

I strongly suspect your problem is on another level.


- Original Message -
> From: "Edward avanti" 
> To: mysql@lists.mysql.com
> Sent: Monday, 7 March, 2011 5:54:02 AM
> Subject: Re: mysql apache md5
> 
> everything to do with mysql
> I try make it clearer, sorry for not so in first post
> 
> customer relationship manager add users into mysql
> we want not to use apache auth of encrypt, but use md5 for longer
> password
> apache use variant of md5, called md5 -apr, but mysql md5  only uses
> the -1
> type
> so, when CRM add userlike
> INSERT INTO users  (..other`appass`) values  (...other...
> 'MD5('$PASS')
>   
> the md5 -1 that mysql uses is not compatible so apache auth fail. the
> variant is apaprently add
> $apr1$$md5passwordhere,
> making allabove line the salted md5.
> I try to get mysql and apache to play nice, but thy do not because
> mysql and
> apache not use same method, hence my attempt to work around, even SHA
> same
> affect, i am try use anything but DES encrypt('$PASS')
> sadly that only thing that work happily witrh each other.
> openssl have ability to do this so not sure why mysql not have option
> 
> Sure someone had same problem and simple work around to have mysql
> use
> correct md5, but no google fu work
> 

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
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 practice: mysql_multi, VMs w/single instance per or doesn't matter?

[Johan De Meersman]

Other people have answered with pros and cons of virtualisation, but I would 
rather ask another question: why do you feel it necessary to split up the 
database?

If it's only used for QC, it's probably not in intensive use. Why would you go 
through the bother of splitting it up? You're staying on the same server, 
apparently, so you'll have to decide which instance gets what part of cpu, 
memory and other resources, you'll have to provide separate backup for all 
instances, et cetera; while leaving things as they are is zero effort.

What is the problem with the current setup that will be resolved by splitting?


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Two Identical Values on Primary Key Column

[Johan De Meersman]

Is it possible that someone did an alter table disable keys at some point, 
maybe for a bulk load, and forgot to re-enable them ? 

- Original Message -

> From: "Rodrigo Ferreira" 
> To: mysql@lists.mysql.com
> Sent: Wednesday, 2 March, 2011 3:04:31 PM
> Subject: Two Identical Values on Primary Key Column

> Hi all,

> I have just experienced a strange problem with mysql production
> database. The table faqsessions have a primary key on column `Code`
> and the above select return 2 rows!

> mysql>
> mysql>
> mysql> show create table faqsessions;
> +-+--+

> +-+--+

> `Code` int(11) unsigned NOT NULL auto_increment,
> `sid` int(11) NOT NULL,
> `ip` text NOT NULL,
> `time` int(11) NOT NULL,
> PRIMARY KEY (`Code`)
> ) ENGINE=InnoDB AUTO_INCREMENT=1567573 DEFAULT CHARSET=latin1 |
> +-+--+
> 1 row in set (0.00 sec)

> mysql>
> mysql>
> mysql> select * from faqsessions where time in (1268650281,
> 1268650260);
> +++--++

> +++--++

> +++--++
> 2 rows in set (1.49 sec)

> mysql>
> mysql>
> mysql> select * from faqsessions where Code = 611179;
> +++--++

> +++--++

> +++--++
> 1 row in set (0.00 sec)

> Any idea?

> Rodrigo Ferreira
> CMDBA, CMDEV

-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Re: quick question

[Johan De Meersman]

- Original Message -
> From: "Hervey Liu" 
> 
> CREATE TABLE logins (
>success 
> enum('Y','N[banned]','N[password]','N[panic]','N[activation]','N[authorization]')
>  DEFAULT 'Y' NOT NULL,
>when datetime DEFAULT '-00-00 00:00:00' NOT NULL,
 
This is going to be an issue when querying. Avoid using reserved words as 
column names whenever possible.

>browser varchar(255) NOT NULL,
>ipaddress varchar(20) NOT NULL,
>activity enum('user','quote','liquidate','manage','finale') DEFAULT 
> 'quote' NOT NULL,
>userid int(10) unsigned NOT NULL,
>symbolid int(10) unsigned,
>PRIMARY KEY (when, userid)


For the rest, that isn't data, so it won't "look" like anything quite so much 
as an empty table. Just execute the statement and have a look :-)


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: I can't have "group" as a column name in a table?

[Johan De Meersman]

On Thu, Feb 24, 2011 at 12:06 PM, Dave M G  wrote:

> Should I never use the word "group" for column names? Seems a little
> silly. Is there a way to protect column names to that there is no
> confusion?
>

As several people already pointed out, simply use backticks. Simple quotes
have started to work in more and more places in newer versions of MySQL.

However, it is considered bad form to name columns for reserved words - even
ones as obvious as "group" or "index". Bad form in the same way that you
wouldn't name any variables "define" or "if" while programming; or in a very
similar way that you wouldn't put a box of TNT next to a burning candle -
it's an accident waiting to happen.

The escapes are there in case an upgrade creates new reserved words that
you've already used in column names - "partition" comes to mind - but if you
are still in a phase where you can avoid using reserved words, please spare
yourself and others a lot of trouble and do so; even if only because while
you can fix your code, you can't fix someone else's - think management
tools, backup scripts, whatever may touch the db in the future.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Mysql clustering

[Johan De Meersman]

On Wed, Feb 23, 2011 at 7:53 AM, Machiel Richards wrote:

>I tried to find info on the net and on the mysql website, but thus
> far I haven't been able to find proper documentation on how to set
> everything up.
>

Uhh... the documentation on the mysql site is very complete, afaik.



>If someone perhaps have the ebook version of the mysql clustering
> certification study guide, that would help me as well as I might be
> looking at going for the certification as well.
>

Sorry, don't have that. The only course I ever followed was Advanced Tuning;
the rest is self-taught. I really do feel the official documentation is very
complete :-)


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Replication, log info

[Johan De Meersman]

On Wed, Feb 16, 2011 at 12:23 PM, Carl  wrote:

> 110216  5:15:20 [ERROR] Error reading packet from server: log event entry
> exceeded max_allowed_packet; Increase
> max_allowed_packet on master ( server_errno=1236)
>

This seems to be the major player, here. I would make sure to increase the
setting identically on both sides, though.

I'm not entirely sure how this would happen - maybe due to making a
borderline-sized query deterministic?


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Replicating to mysql5.1 from 5.0 master?

[Johan De Meersman]

On Wed, Feb 16, 2011 at 10:23 AM, Machiel Richards wrote:

> Due to differences within the 2 versions, we had to exclude the
> mysql database from the backup and restore.
>

Yep :-)


>When setting up the replication, should we still
> exclude the mysql database from the replication and manually do any
> changes if required or can we replicate all databases without any
> problems?
>

Ah :-)

If you use statement-based replication, I would hazard a guess that grant
statements will replicate perfectly fine and will work as expected on both
sides (you DON'T pilfer with the authentication tables manually, do you?).
If you use row-based replication, I would definitely exclude the mysql
database.

The question is, however: how often do you need to change privileges or
routines during this migration period? I would assume rarely to not at all;
so why not just leave the mysql database out of the replication, just in
case ? You presumably already set it up manually on the new server, so just
let good enough alone.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Limit of Mysql

[Johan De Meersman]

Mostly correct - save for pointer sizes and such, but it's pretty hard to
reach those.

SQL vs NoSQL is not a matter of data size - plenty of fud is being spread
about NoSQL, for some reason - but a matter of access patterns.

Without knowing what you need and how you design, that question can't be
answered.


On Wed, Feb 16, 2011 at 8:48 AM, Reindl Harald wrote:

> there are no hard limits as long your hardware ist fast enough
>
> * memory, memory and agin: memory
> * disk-speed
> * cpu
>
> Am 16.02.2011 06:04, schrieb Adarsh Sharma:
> > Dear all,
> >
> > I want to know the upper limit of mysql after which Mysql-5.*  fails to
> handle large amount of data ( 100's of GB
> > or 100's of TB's ) . After which we have to move to some NoSQL databases
> ( Hadoop, Hive , Hbase).
> >
> > Currently we have 100 of GB's  data in Mysql -5.1 RDBMS.
> >
> > Is anyone has some experience of moving from these open -source databses
> ( Mysql, Postgresql ) to some mechanism of
> > handling data in file systems.
> >
> > Please share the views.
> >
> >
> >
> > Thanks & best regards,
> >
> > Adarsh Sharma
> >
> >
> >
>
> --
>
> Mit besten Grüßen, Reindl Harald
> the lounge interactive design GmbH
> A-1060 Vienna, Hofmühlgasse 17
> CTO / software-development / cms-solutions
> p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
> icq: 154546673, http://www.thelounge.net/
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: overhead in memory tables

[Johan De Meersman]

What particular overhead is growing ? :-)

On Tue, Feb 15, 2011 at 10:11 AM, Geoff Galitz  wrote:

>
> Hello.
>
> We have a table using the memory engine and we notice in PMA that the
> overhead continues grow over time.  Normally we'd optimize with such an
> issue but that is not applicable to memory based tables.   So... does this
> pose a problem for long term use of the table?  If so, is there a
> recommended way to deal with it other than switch to a new table engine?
>
> We are not experiencing any problems at this time, we are just trying to be
> proactive about it.
>
> Mysql version is 5.5.9 + memcached
>
> Thanks.
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: CR: add support of interactive transactions for webclients

[Johan De Meersman]

I can't speak for the MySQL people, but in my view your "workaround" is the
correct way of implementing this. It is not the database's job to keep track
of which user wants to keep what session open, and HTTP is stateless by
design. Keeping transactions open for relatively long periods of time would
be a major load on the database, as it needs to keep track of all the
different changesets and consistent views. I'd rather have my database be
good at handling data :-)

I can see your need, but that's what middleware is for.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: function to limit value of integer

[Johan De Meersman]

How about the square root of the number of jobs, or some other root if you
want another coefficient? That doesn't have the limiting behaviour a
logarithmic function offers, though.







On Fri, Feb 11, 2011 at 2:08 PM, Richard Reina  wrote:

> Hi Travis,
>
> This is very helpful thank you.  However, is there a way to make it not be
> less than a 1.  As it's written below someone with one job gets a zero and
> someone with no jobs gets a NULL.  It would be great if someone with  1 job
> got a 1 and someone with zero jobs got a 0.
>
> Thanks again,
>
> Richard
>
> 2011/2/10 Travis Ard 
>
> > Maybe some sort of logarithmic expression?
> >
> > select no_of_jobs, 10 * log(10, no_of_jobs) as job_weight
> > from data;
> >
> > Of course, you'd have to tweak your coefficients to match the weighting
> > system you want to use.
> >
> > -Travis
> >
> > -Original Message-
> > From: Richard Reina [mailto:gatorre...@gmail.com]
> > Sent: Thursday, February 10, 2011 3:07 PM
> > To: mysql@lists.mysql.com
> > Subject: function to limit value of integer
> >
> > Is there a function that can limit the value of an integer in a MySQL
> > query?   I am trying to write a query that scores someones experience.
> > However, number of jobs can become overweighted in the the query below.
> If
> > someone has done 10 jobs vs. 1 that's a big difference in experience. But
> > someone who's done 100 vs. someone who's done 50 the difference in
> > experience is not so great as they are both near the top of the learning
> > curve.  In essence number of jobs becomes less and less of a contributor
> as
> > it increases. Is there a way to limit it's value as it increases?
> >
> > SELECT years_srvd + no_of_jobs AS EXPERIENCE
> >
> > Thanks,
> >
> > Richard
> >
> >
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: High disk usage

[Johan De Meersman]

On Thu, Feb 10, 2011 at 2:15 PM, Santiago Soares
wrote:

> With a show global status I see a strange behavior:
> | Open_files| 286   |
> | Opened_files  | 1050743   |
>
> At this time the database has just started (about 10 minutes).
>

That's quite a lot for ten minutes, yes. Increasing the open table cache,
query cache and/or index cache might help, depending on data size, table
types (I'd assume mostly InnoDB based off your pool size), et cetera.

Basically it's a matter of finding out why you open and close so many files,
as that's likely to be one of the causes of the I/O waits.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Backup onle one procedure

[Johan De Meersman]

Hmm, I haven't seen the mail from Singer, yet.

On Thu, Feb 10, 2011 at 9:33 AM, Ananda Kumar  wrote:

> On Thu, Feb 10, 2011 at 1:58 PM, Singer X.J. Wang wrote:
>
>> mysqldump -u[user] -p[pass] --where="db=`whatyouwant` and
>> name=`whatyouwant`" mysql proc
>>
>
Yes, I thought of that, too; but the manual explicitly states that manual
manipulation of the proc procedure isn't supported (which i presume to mean
there is no equivalent to *flush privileges*), so that's gonna take manual
reassembly to restore. The privileges to the procedure (creator/invoker and
so) are also in a separate table, procs_priv.

*Show create procedure* should yield a complete, executable statement that
recreates the procedure as-is.

Something to keep in mind, incidentally, is that security is *creator* by
default - the proc runs with the creator's privileges. If you migrate the
procedure to a host where that same user does not exist or has different
permissions, it might "mysteriously" not work. IMO, security should be set
to *invoker* by default, as that is also the more secure option - if you
don't have permission on the tables, I don't want you running stored procs
on them. That's for MySQL to change, though :-)


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Backup onle one procedure

[Johan De Meersman]

On Thu, Feb 10, 2011 at 7:43 AM, Adarsh Sharma wrote:

> I am researching all the ways to backup in mysql and donot able to find a
> command that take individual backup of only one procedure in mysql.
>

Have a look at the SHOW CREATE PROCEDURE syntax. It's not mysqldump, but it
will yield a statement that can recreate the given procedure from scratch.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: BLOB data gets encoded as utf8!

[Johan De Meersman]

I can't help but wonder, if you send a string, does that mean you're putting
text in a blob ? Blobs are binary, and thus don't get encoded in the sense
of UTF8 vs Unicode. For a string, you may want a TEXT type column.

On the other hand, if you're indeed trying to insert binary data, it is not
the best of ideas to insert it into an SQL string, where it will be subject
to UTF8 or whatever interpretation.

For large objects, and generally with repeating statements too, it's best to
use bind variables to send your data. That also removes the need to worry
about escaping, sql insertion and the like.


2011/2/10 Andreas Iwanowski 

> Thank you for your reply, Janusz.
>
> I appreciate your help.
>
> I have tried making that call before the INSERT statement, but to no avail.
> The table collation is set to "utf8 - default collation", and all columns
> are set to "Table default".
> I am thinking that this problem might be due to me sending the BLOB data in
> a unicode string to the server, i.e:
>
> INSERT INTO TableName (Rawfield) VALUES ('%s'); - whereas the %s string is
> a unicode string
>
> That string is encoded by escaping mysql characters according to
> http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html (0x00, 0x22,
> 0x27, 0x5C).
> Then each byte (and each escaped byte) is followed by a 0x00 before being
> sent to the server, just so that I can send it as a unicode string via ODBC.
>
> I.e. the binary data FA 5C 93 A4 would be expanded into FA 00 5C 00 5C 00
> 93 00 A4 00
>
> Otherwise I can't send the data in a unicode INSERT statement via ODBC from
> C++ / MFC.
>
> Do you think that could be the issue? If so, do you have a suggestion on
> how to do it better?
>
> Thank you again for your help!
>
> Sincerely,
>   Andreas Iwanowski
>
> -Original Message-
> From: Janusz Paśkiewicz [mailto:ad...@multipasko.pl]
> Sent: Wednesday, February 09, 2011 7:45 AM
> To: Andreas Iwanowski
> Subject: BLOB data gets encoded as utf8! (Anyone?)
>
> Before inserting BLOB data use:
>
>
>
> mysql_query("SET CHARACTER SET latin1");
>
>
>
>
> after that you can set it back to:
>
>
>
>
> mysql_query("SET CHARACTER SET utf8");
>
>
>
>
>
>
>
>
> This is due to:
>
> http://dev.mysql.com/doc/refman/5.1/en/charset-conversion.html
>
>
>
>
> "If the column has a binary data type (BINARY <
> http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html> , VARBINARY
>  , BLOB <
> http://dev.mysql.com/doc/refman/5.1/en/blob.html> ), all the values that
> it contains must be encoded using a single character set (the character set
> you're converting the column to). If you use a binary column to store
> information in multiple character sets, MySQL has no way to know which
> values use which character set and cannot convert the data properly."
>
>
>
>
>
>
>
> Kind Regards,
> Janusz Paskiewicz
>
> www.freelancer4u.net
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Replacing MS SQL with MySql

[Johan De Meersman]

On Wed, Feb 9, 2011 at 7:42 PM, Jerry Schwartz  wrote:

> *[JS] I don’t have any data at the moment. I know that I tried outsmarting
> Access with pass-through queries, with little luck.*
>

Hmm. I seem to remember those working, but that was in access itself, I
think. It's been many years since I allowed myself to be degraded to that
point :-)

I believe I also resorted to creating views in MySQL and importing those
instead of the tables, for some things.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Replacing MS SQL with MySql

[Johan De Meersman]

On Wed, Feb 9, 2011 at 7:02 PM, Jerry Schwartz  wrote:

> [JS] Actually, I've done a lot of tracing recently (to solve my own
> performance problems), and Access 2007 is very clever at pulling parts of a
> dataset and a number of other things. For example, when you are browsing a
> dataset Access will pre-fetch records 20 at a time.
>

Good to know that performance enhancements have been made, but what I'm
specifically thinking of is joins between remote tables - have you got data
on how it handles those ?

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Replacing MS SQL with MySql

[Johan De Meersman]

On Wed, Feb 9, 2011 at 7:55 AM, David Brian Chait wrote:

> To borrow your line of reasoning, translators can be rather slow and
> unreliable. Adding the extra overhead and complexity is certainly not worth
> the potential gains.
>

I daresay that's up to the user to decide, no? OP never specified why he
needs the application to connect to a MySQL DB.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Replacing MS SQL with MySql

[Johan De Meersman]

On Wed, Feb 9, 2011 at 7:18 AM, Reindl Harald wrote:

> Am 09.02.2011 06:36, schrieb Y z:
> >
> > I have a windows app that wants to talk to either a) an access database,
> b) a MS
> > Sql Express database, or c) a MS Sql 2008 database.
> >
> > Can anyone please point me in the direction of configuring My Sql to
> > imitate any of the above?
>
> no way if the application has no abstraction-layer
> you can not easy switvh from one rdbms to another
> because in a closed source app you will even not
> can connect
>
> after connect there are hughe differences in many details
>
> so if a application does not support a specific rdbms
> you can not use it
>

Remember, TIMTOWTDI:-)
If someone you want to talk to knows only one language, find an
interpreter. MS Access apparently speaks the same language as his app; and
using ODBC it can also talk MySQL. Link the tables in Access and let the app
use them as if they were regular tables.




-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Replacing MS SQL with MySql

[Johan De Meersman]

No way to do that directly; however, using the MySQL ODBC connector you can
get at least a) and c) to play passthrough. Performance will likely suffer,
though; especially Access' Jet Engine has a tendency to pull in full remote
datasets instead of passing through the query.


On Wed, Feb 9, 2011 at 6:36 AM, Y z  wrote:

>
> I have a windows app that wants to talk to either a) an access database, b)
> a MS Sql Express database, or c) a MS Sql 2008 database.
>
> Can anyone please point me in the direction of configuring My Sql to
> imitate any of the above?
>
> Thanks!
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Table/select problem...

[Johan De Meersman]

Do you delete data from the table ?

MyISAM will only grant a write lock when there are no locks on the table -
including implicit read locks. That may be your problem.

There is a single situation when concurrent reads and writes are possible on
MyISAM, however: when your table has no holes in the data. At that point,
selects happen on the existing data, while the insert queue is handled
(sequentially) at the same time.

If that is indeed your problem, you may "fix" the table using OPTIMIZE
TABLE.

Two other options might be:

   - set the variable concurrent_insert to 2 - this will allow concurrent
   inserts at the end of the table even when there are holes. Downside is that
   freed space (from deletes) is not reused.
   - use INSERT DELAYED. Code returns immediately, but you have no way of
   knowing wether or not any given insert succeeded.


If you delete data, but only relatively old data, you might also benefit
from partitioning the table: I'm not sure about this, but it seems
reasonable that concurrent updates would be possible on partitions with no
holes. Should try this sometime.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: writing to disk at a configurable time

[Johan De Meersman]

InnoDB definitely has some parameters you can play with, but I've never
actually done so myself.


On Fri, Feb 4, 2011 at 8:09 PM, Vinubalaji Gopal wrote:

> Hi all,
>  I wanted to know if Mysql allows me to configure it such that the
> writes to disk happen at a configurable time or after the buffers have
> enough data. Say there are 10 clients connecting to mysql and each one
> is inserting a row - I want to only write to disk when the buffer has
> enough data (say all the 10 rows are written to the main memory) or at
> some configurable interval. I know there could be  a data loss in this
> case (if a crash happens before the buffer is written to disk) but I
> am willing to take that risk in the application.
>
> --
> Vinu
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: MySQL Administrator

[Johan De Meersman]

On Fri, Feb 4, 2011 at 8:03 AM, Angela liu  wrote:

> Is MySQL Administrator still available for  MySQL 5.1 and 5.5?
>

I believe that line of applications has been superceded by the MySQL
Workbench.

If you must use MySQL administrator for some reason, they will undoubtedly
connect to 5.1 and 5.5, but newer functionality will not be available.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: How do increase memory allocated to MySQL?

[Johan De Meersman]

2011/2/4 Yannis Haralambous 

> SELECT * FROM wasfoundin WHERE yakoright LIKE '%geography%'
>

That won't use a regular index. Have a look at fulltext indexing.

For the phpmyadmin, I personally feel it's an abomination, not to mention a
disaster waiting to happen; but if you really want to keep using it, you'll
probably have to set the PHP script runtime timeout to something
appropriately high to accomodate the long query runtimes. This can be done
from within the script if neccesary, look at the PHP documentation for that.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: map polygon data for popular us cities

[Johan De Meersman]

On Wed, Feb 2, 2011 at 11:30 AM, viraj  wrote:

> dear list,
> where can i find a list of map polygons for united states cities? any
> open database? or tool to obtain correct coordinates?
>

A bit offtopic here, but I suspect that most such databases will be
proprietary and thus payable through the nose. Have a look at the
OpenStreetMap project, I suspect their database might be accessible under
some open license.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Replication Error on Slave

[Johan De Meersman]

On Thu, Jan 27, 2011 at 10:40 AM, Nagaraj S  wrote:

> **On Slave Server I replicate database *A alone* and my replication not
> working due to data fetching happen on B database.
>

Well, yes. Statement-based replication does what it says on the box: it
executes the exact same statement on the slave. If database B is not there,
then insert into A select from B will not work.

You may switch to row-based replication (which of course has it's own
caveats, see the online manuals); or you can simply choose to also replicate
database B.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: InnoDB and rsync

[Johan De Meersman]

On Wed, Jan 26, 2011 at 6:58 AM, Robinson, Eric wrote:

> > > You need to quiesce the InnoDb background threads. One technique is
> > > mentioned here:
> > > http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp
> > aces.html
> > >
> > > Look for the section talking about "clean" backups.
> >
> > Now we're talkin. I'll check it out.
> >
> >
>
> I read that section but it is not at all clear (1) how one quiesces the
> InnoDB background threads, or (2) if there is a way to keep them
> quiesced while the backup is in progress.
>

>From what I see there:

> You can make a clean backup .ibd file using the following method:
>
>1.
>
>Stop all activity from the 
> *mysqld*server and commit 
> all transactions.
>2.
>
>Wait until SHOW ENGINE INNODB 
> STATUSshows that 
> there are no active transactions in the database, and the main
>thread status of InnoDB is Waiting for server activity. Then you can
>make a copy of the .ibd file.
>
> I would assume that "flush tables with read lock" would work for 1. and
then you wait for 2. to happen. Probably shouldn't take very long,
especially in the quiet moments.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: InnoDB and rsync

[Johan De Meersman]

> jesus christ nobody cares if they are binary replica as long
> as the data is consistent and ident
>

Actually, I can see this being an issue if you're using LVM snapshot backups
or another similar technique - if the datafiles aren't all identical you
won't be able to restore to any machine from a single backup.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: InnoDB and rsync

[Johan De Meersman]

On Tue, Jan 25, 2011 at 3:00 PM, Robinson, Eric wrote:

> > your whole solution is crippled because why in the world are
> > you killing your salves and reinit them without any reason daily?
>
> There is a very good reason: it is the phenomenon of row drift. The
>

Interesting. I never heard of that, and can't, at first glance, seem to find
a lot of useful things on Google. Could you explain what you mean ?

The one thing I can think of, would be the fact that your rows are not
guaranteed to be in the same disk blocks, or even necessarily in the same
data block of your file. This in itself doesn't really pose a problem for
backups, though, afaik ?


I'm starting to worry that you may be right. I know FLUSH TABLES WITH
> READ LOCK does not work as expected with InnoDB, but is there really no
>

It doesn't, exactly, no; but afaik no actual data will be written. Some
metadata may not be fully sync, but I do not believe a lot could happen that
the recovery when you start your slave can't fix. Still, the issue is there.


> really the only way? (And even if I stop the service, is rsync totally
> safe with InnoDB?)
>

As a stopped MySQL can't update the files or keep data in memory, that
should be safe, yes.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: InnoDB and rsync

[Johan De Meersman]

I suspect the same trick might work with InnoDB (with pretty much the same
caveats), but you'd be best off setting innodb-file-per-table - I'm sure
you've already seen that the large datafiles are a hindrance to smooth
rsyncing :-)

Make sure to test extensively, though.


On Tue, Jan 25, 2011 at 5:37 AM, Robinson, Eric wrote:

> Is there a way to safely backup an InnoDB database using rsync?
>
> Right now we have a very efficient and reliable way to backup 240+
> separate instances of MySQL with MyISAM tables. The databases range in
> size from .5GB to 16GB. During this time, users can still access the
> system, so our customers can work 24x7. In the process, we also refresh
> 240+ slave instances with a perfect byte-for-byte replica of the master
> databases.
>
> The whole thing takes about 30 minutes.
>
> Here's how we do it.
>
> Late at night when the number of users on the system is low, we do the
> following for each of the 240+ instances of MySQL...
>
> 1. Shut down the slave and remove all replication-related log files.
>
> 2. Perform an rsync of the master's data directory to the slave. Users
> may be making changes to tables during this rsync.
>
> 3. Issue a FLUSH TABLES WITH READ LOCK on the master followed by a RESET
> MASTER.
>
> 4. Perform a second rsync of the data directory from the master to the
> slave to copy any user changes that happened during step 2. This usually
> completes in a few seconds, often less than 1. If any users were trying
> to insert records at this exact moment, their application may appear to
> pause very briefly.
>
> 5. Start the slave.
>
> When I'm all done, I have 240+ slave servers in perfect sync with their
> masters, each having a 100% identical binary copy of its master's
> database. Since these copies are truly identical, they can be used for a
> second layer of backup to other media.
>
> Like I said, the whole process takes about 30 minutes because the rsync
> algorithm only copies the byte-level changes.
>
> IS THERE ANY WAY TO SET UP SOMETHING THIS EASY AND EFFICIENT USING
> INNODB?
>
> I've been reading about InnoDB hot copy and other approaches, but none
> of them seem to work as well as the approach I have worked out with
> MyISAM. Unfortunately, my software wants to force us to switch to
> InnoDB, so I'm really stuck. If we have to switch to InnoDB and we
> cannot come up with a method for doing fast, rsync-style backups, it
> will probably mean huge, costly, and unnecessary changes to our
> infrastructure.
>
> Any help will be GREATLY appreciated.
>
> --
> Eric Robinson
>
>
> Disclaimer - January 24, 2011
> This email and any files transmitted with it are confidential and intended
> solely for mysql@lists.mysql.com. If you are not the named addressee you
> should not disseminate, distribute, copy or alter this email. Any views or
> opinions presented in this email are solely those of the author and might
> not represent those of Physicians' Managed Care or Physician Select
> Management. Warning: Although Physicians' Managed Care or Physician Select
> Management has taken reasonable precautions to ensure no viruses are present
> in this email, the company cannot accept responsibility for any loss or
> damage arising from the use of this email or attachments.
> This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Is is possible to update a column based on a REGEXP on another column?

[Johan De Meersman]

If the bracketed stuff really can be anything, you're better off doing it
externally, I guess. If you can be reasonably sure that there'll not be any
square brackets in there, you can fluff about with instr() and substr().


On Sat, Jan 22, 2011 at 6:18 PM, Eric Bergen  wrote:

> There isn't a built in way but you can try
> http://www.mysqludf.org/lib_mysqludf_preg/
>
> I would go with the php/perl script if this is a one time thing.
>
> -Eric
>
> On Jan 21, 2011, at 11:31 AM, Phil  wrote:
>
> > I have a table which contains a username column which may be constructed
> > something like
> >
> > somename[A] or [DDD]someothername
> >
> > The A or DDD can be anything at all.
> >
> > I've added a new column to the table to which I'd like to populate with
> the
> > value within the square brackets.
> >
> > I could write something in perl or php to run through each and update
> them
> > but was wondering if there is a way to do this within mysql itself?  The
> > regexp only returns a boolean so I can't see how to use that.
> >
> > Regards
> >
> > Phil
> >
> >
> > --
> > Distributed Computing stats
> > http://stats.free-dc.org
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: best way to have a unique key

[Johan De Meersman]

I have to say, something similar was my first thought, too - you never
mention uuid in your original post. As already stated, uuid() should be a
Universal Unique IDentifier. It's afaik a random 128-bit number; given the
space to choose from it should be rather unique. I have to admit that I'm
not entirely confident about that myself, either, though: as Pratchett put
it, one-in-a-million chances tend to pop up nine times out of ten.

The code should have bits for handling duplicate primaries regardless of the
method used to generate it, tough, so there's no reason to not do it. Having
two subsequent UUID() calls generate pre-existing numbers seems to me to be
likely in the same way as having Bush return his dirty oil dollars to Irak.

On Thu, Jan 20, 2011 at 8:10 PM, Anthony Pace wrote:

> Dude, come on.  I know that all primary keys have to be unique; however, I
> was obviously referring to the use of uuid over auto incrementation.
>
> On 1/20/2011 1:36 PM, Michael Dykman wrote:
>
>> It is axiomatic in the relational model that a primary must be unique.
>>  This is not a quirk put forth by your current employer.  Neither
>> MySQL nor any other RDBMS will allow you to establish a primary key
>> that is not unique.
>>
>>  - michael dykman
>>
>> On Thu, Jan 20, 2011 at 1:32 PM, Anthony Pace
>>  wrote:
>>
>>> Due to certain reasons, the company I am doing business with has decided
>>> that the primary key, for an orders table, be a unique key; however, I
>>> don't
>>> like the possibility of it conflicting if moved to another machine.
>>>
>>> What are some pitfalls of using a unique key, that is generated by a
>>> server
>>> side script, rather than by mysql?
>>> What are the best ways to do this?
>>>
>>> Please keep in mind this variable will also be displayed on the
>>> customer's
>>> Receipt, but again, since it's random, it doesn't have to mean anything.
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
>>>
>>>
>>>
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql/#sql_9e1_0.MYI' (Errcode: 13)

[Johan De Meersman]

On Tue, Jan 18, 2011 at 6:24 AM, sushant chawla  wrote:

> Make sure the following things:
>
>
>   - /tmp folder is having 1777 permissions
>   - mysql folder is having the ownership from which it is running. Refer
>   /etc/my.cnf
>   - Make sure you have space on your MySQL partition
>

Also, make sure you have room where MySQL puts it's temp files (possibly
/tmp or /var/tmp, check "show global variables like '%tmpdir%';"). This may
mean that the server couldn't write a temporary sorting file or something
similar to disk.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Incorrect key file for table

[Johan De Meersman]

On Sat, Jan 15, 2011 at 8:07 AM, Jørn Dahl-Stamnes
wrote:

> On Saturday 15 January 2011 00:28, Johnny Withers wrote:
> > The result of your query without the join
> > probably exceeded your tmp_table_size variable. When this
> > occurs, MySQL quit writing the temp table to disk thus producing an
> > incorrect table file. (I think).
>
> Yes, part of this was my fault (the missing join) but I still wonder why
> the
> server processed the query. Would it not be more appropriate with an error
> message saying that this query contain an error?
>

Most likely (too lazy to actually check :-p ) your SQL was syntacticlaly
correct, so it tried to answer the question you asked. Which, unfortunately,
was not the question you wanted to ask :-)


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Incorrect key file for table

[Johan De Meersman]

Check your free diskspace on your temp location.

On Fri, Jan 14, 2011 at 1:31 PM, Jørn Dahl-Stamnes
wrote:

> Hello,
>
> While doing a select query I  got the following error in the error-log
> file:
>
>Incorrect key feil for table '/tmp/#sql_5f8_0.MYI'; try to repair it
>
> It seem rather meaningless to try to repair a temporary table... So is this
> a
> bug in MySQL, or?
>
> The database I'm using has only InnoDb tables and I'm using version
> 5.1.42-log.
> The query is build using Propel (from a web-application made by Symfony
> framework).
>
> --
> Jørn Dahl-Stamnes
> homepage: http://www.dahl-stamnes.net/dahls/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Rewrite SQL to stop table scan

[Johan De Meersman]

The problem is that you're using a function on your indexed field, which
prevents the index from being used (I'm assuming you have an index on
stamp).

Store stamp directly as unixtime (use a time field) or if that's not an
option, add a column that does - if you want you can autofill it with a
trigger from stamp, or now() if that is appropriate.

On Fri, Jan 14, 2011 at 11:52 AM, Bruce Ferrell wrote:

> How would you rewrite the following SQL so that is doesn't do a full
> table scan.   It does in fact do a full scan in spite of the time
> clause.  It's been
> making me nuts for months.
>
> select count(*) as count
> from alerts where (unix_timestamp(stamp) > (unix_timestamp(now()) -
> '300' ) )
> and devid = '244';
>
> Thanks in advance,
>
> Bruce
>
> P.S.
>
> I've tried it this way:
>
> select count(*) as count  from alerts where ((unix_timestamp(stamp) >
> (unix_timestamp(now()) - '300' ) )) and devid = '244';
>
> and explain always says this:
>
> +--+---+--+-+--+-+-+
> | id | select_type | table  | type | possible_keys | key  | key_len |
> ref  | rows| Extra   |
>
> ++-++--+---+--+-+--+-+-+
> |  1 | SIMPLE  | alerts | ALL  | NULL  | NULL | NULL
> |NULL | 2041284 | Using where |
>
> ++-++--+---+--+-+--+-+-+
>
> And it's structured this way:
>
> | Field| Type| Null  |  Key | Default
> | Extra
> | id| varchar(60)| NO   | MUL |
>|
> | stamp  | timestamp | NO   | MUL |
> CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: How to Install Mysql

[Johan De Meersman]

On Fri, Dec 24, 2010 at 6:37 AM, Adarsh Sharma wrote:

> Or I am going to install through rpm which is the easiest way. But which is
> best for our Production Servers.
>

Unless you have very specific needs, it's always best to use official
packages for production. That also makes it easier to get support, since you
have a known build.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Error while running Mysql

[Johan De Meersman]

Probably one for the guys with the compilers, but have you tried running it
with dtrace and seeing where it explodes ?

On Thu, Dec 23, 2010 at 1:38 PM, Adarsh Sharma wrote:

> Dear all,
>
> I am able o successfully build Mysql 5.5.8 from its source code on CentOS
> but when I issued the following command , it doesn't shows the mysql>
> prompt, there is no error in log file.
>
>
> [r...@ws-test mysql-5.5.8]# bin/mysql -p
> Enter password:
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 2
> Server version: 5.5.8 Source distribution
>
> Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights
> reserved.
>
> Oracle is a registered trademark of Oracle Corporation and/or its
> affiliates. Other names may be trademarks of their respective
> owners.
>
> Segmentation fault
> [r...@ws-test mysql-5.5.8]#
>
> Don't know what to do.Please help.
>
>
> Thanks & Regards
>
> Adarsh Sharma
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Another replication question

[Johan De Meersman]

Glad to hear I'm not the only one annoyed :-) I've plonked him in the
meantime.

2010/12/23 Jorg W Young 
>

> This guy has been saying nothing meaningful on this list, but
> advertise his blog everywhere.
> Just be shame. He should be kicked out from the list.
>
> Jorg.
>
> 2010/12/23 杨涛涛 :
> > This way is very well,  but it has to do lots of human work.
> > David Yeung, In China, Beijing.
> > My First Blog:http://yueliangdao0608.cublog.cn
> > My Second Blog:http://yueliangdao0608.blog.51cto.com
> > My Msn: yueliangdao0...@gmail.com
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Query Stored Index instead of Group By

[Johan De Meersman]

On Sun, Dec 19, 2010 at 3:19 AM, Feris Thia <
milis.datab...@phi-integration.com> wrote:

> Hi Everyone,
>
> Is there a way to query values stored in our index instead of using "group
> by" selection which will produce same results ?
>

You can't query the index directly, but if you select only fields that are
in the index, no table lookups will be performed - this is called a covering
index.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Password Reset

[Johan De Meersman]

Change password statements should show up in the binary logs, too, in some
form or other.

On Thu, Dec 16, 2010 at 9:58 PM, Alejandro Bednarik wrote:

> SQL injection? Check Apache or whatever log files.
>
> On Thu, Dec 16, 2010 at 4:17 PM, Gary  wrote:
>
> > I recieved a call from a client saying the web site did not work, turns
> out
> > the database password was reset, and not by me.  In looking in the DB
> after
> > the PW was reset, I could find nothing out of place, although frankly I
> was
> > not sure what to look for.
> >
> > Is this indicitive of an attack?  Is this something to worry about?  I
> had
> > (or so I assumed) plenty of protections on the files, including one of
> the
> > more popular anit-spam/injection attack systems.
> >
> > Any guidance on this would be appriciated.
> >
> > --
> > Gary
> >
> >
> >
> > __ Information from ESET Smart Security, version of virus
> signature
> > database 5708 (20101216) __
> >
> > The message was checked by ESET Smart Security.
> >
> > http://www.eset.com
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?unsub=alejand...@olx.com
> >
> >
>
>
> --
>
>
> Infrastructure Team
>
> OLX Inc.
>
> Buenos Aires - Argentina
> Phone   : 54.11.4775.6696
> Mobile : 54.911.50436059
> Email: alejand...@olx.com
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Bulk Insertion Performance

[Johan De Meersman]

Hmm, interesting. What does this do, exactly ? Can something similar be
applied to non-jdbc connections, too ?

On Thu, Dec 16, 2010 at 8:34 AM, Feris Thia <
milis.datab...@phi-integration.com> wrote:

> Hi Mark,
>
> On Thu, Dec 16, 2010 at 4:50 AM, Mark Matthews 
>  wrote:
>
> > 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.
> >
>
> It works. By having rewriteBatchedStatements=true in the jdbc url it
> increases. Now it performs an average 4500 rows / second. Thanks Mark.
>
> Regards,
>
> Feris
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: restrict mysql replication ?

[Johan De Meersman]

On Wed, Dec 15, 2010 at 10:08 AM, Per Jessen  wrote:

> Per Jessen wrote:
>
> > Is there a way of limiting that?  Alternatively, is there a way of
> > doing "replication-on-demand", perhaps triggered by cron?
>
> Ignore this, problem solved.  I'll let the slaves query the master
> regularly and just reload the entire table.
>

That works. As for what you're seeing, you're probably limiting replication
on the slave instead of limiting binlogging on the master. Can't quite
recall the exact option, something like binlog-do-db I think.

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Design: how to prioritise 1-to-many fields

[Johan De Meersman]

On Thu, Dec 9, 2010 at 3:33 PM, gvim  wrote:

> I have a typical contact database which caters for multiple email addresses
> with a distinct Email table keyed to a foreign key inside the Contact table,
> ie. a 1-to-many relationship. However, I want to prioritise these Email
> entries for a given Contact entry so all I can think of is to add a numeric
> Priority field alongside the Address field inside the Email table. Is this
> the best/standard solution or is there another way?
>

That would be it, I guess, yeah :)



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Resource required

[Johan De Meersman]

On Thu, Dec 9, 2010 at 1:46 PM, Machiel Richards  wrote:

>Does anybody know if there is a mysql mailing list where we can
> post for a position we have open in terms of MySQL dba.
>

Here might work, I'm not aware of a specific list for MySQL jobs.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Moving of databases from one server to another

[Johan De Meersman]

On Wed, Dec 8, 2010 at 11:05 AM, Machiel Richards wrote:

>  Hi Johan
>
> Would the server require a restart after this or not?
>

You can restart to check that the credentials in file and database match, to
avoid surprises later, but the server operation itself is not impacted by
this.

That user is used mostly for system-based maintenance: table checks at
startup, clean shutdown and package upgrade operations, plus during the
install of some other packags to create and initialize their necessary
databases.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Moving of databases from one server to another

[Johan De Meersman]

That's a very Debian-specific issue. The credentials for the
debian-sys-maint user are randomly generated at install, and stored in
/etc/mysql/debian.cnf. Either copy the file from the old to the new machine,
or update the user's password on the new machine to the one in the file.



On Wed, Dec 8, 2010 at 9:51 AM, Machiel Richards  wrote:

> HI All
>
>I am hoping someone has had this before as this one is baffling me
> entirely.
>
>We did a MySQL database move from one machine to another one last
> night.
>
>The O/S versions are the same and so are the database version
> (5.1.22).
>
>The database was installed and configured on the new machine and
> was running perfectly. no issues.
>
>Seeing that both versions would be exactly the same, we decided
> to do a full backup including the mysql database in order to preserve
> all permissions etc... and then restore this to the new machine.
>
>The backup and restore went through without any issues
> whatsoever.
>
>However when I used the /etc/init.d/mysql script to restart the
> database, I started getting errors during shutdown and startup
> procedure.
>
>Despite this the database did start up and was accessible,
> however even when running /etc/init.d/mysql status command I get errors
> as below (these are the same received during startup):
>
>
>
>
> /etc/init.d/mysql status
>/usr/bin/mysqladmin: connect to
>server at 'localhost' failed
>error: 'Access denied for user
>'debian-sys-maint'@'localhost'
> (using password: YES)'
> *
>
>I have tried to look on the net for answers but nothing was
> found to resolve this. I have reset the passwords for this user (also
> getting the same error for r...@localhost).
>One site also suggested correcting the password in
> the /etc/mysql/debian.cnf which I did, but still getting these messages.
>
>
>This is a production system and I am a bit concerned to the
> fact that the startup script does not work properly.
>
>
>  Is there anyone who has had this issue before and have
> been able to resolve it?
>
>Thank you in advance for any help on this matter.
>
>
> Regards
> Machiel
>
>
>
>
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Loading date takes a long time after replication

[Johan De Meersman]

Are you saying that mass inserts go much slower now that you've set up
replication? In that case, I suspect you have your binlogs on the same disk
as your data.

Put the binary logs on separate disks, and you'll notice a dramatic increase
in performance.


On Mon, Dec 6, 2010 at 7:17 AM, Sairam Krishnamurthy wrote:

> All,
>
> I have a situation. We have a very large database(gigabytes). When we load
> the data in to the table, it will be huge again (20 million rows). So in
> order to get good backup we set up a replication(master-slave setup). But
> now, because of this setup, loading into the table takes hours which will be
> generally done in a few minutes otherwise.
>
> Is there a workaround for this? We really don't want to wait for hours for
> the table to be loaded. Should we abandon replication for this? Any pointers
> ?
>
> --
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Sending an output of a query to file in debian lenny

[Johan De Meersman]

On Thu, Dec 2, 2010 at 4:05 PM, bars0 bars0 wrote:

> Yes, something is certailny wrong, because even when I added in MySQL user
> 'krzysztof', wchich is similar to regular user of my linux machine, I can't
>

MySQL users have nothing to see with OS users. As root, do "su -c /bin/bash
- mysql" and check if you can write to /tmp.


> write output neither to /home nor to /tmp directory.
> Only as a root user I can send output into /tmp directory.
> Linux host has basic Debian Lenny installation (without X). I will check
> the
> apparmor but I am not sure if it is enabled by default.
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Sending an output of a query to file in debian lenny

[Johan De Meersman]

On Thu, Dec 2, 2010 at 10:06 AM, bars0 bars0 wrote:

> I try o send an output of a query in debian lenny, using: SELECTINTO
> OUTFILE '/my_path/my_file' FROM...but I get an error: ERROR 1 (HY000):
> Can't
> create/write to file... (Errcode 13).
> What's the problem?
>

The file is not written as your user, but as the user mysqld runs as. That
user (usually "mysql") need to have the necessary permissions on the
directory/file you write to.



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: INSERT DELAYED and logging

[Johan De Meersman]

Interesting, but I feel the difference is rather small - could you rerun
with, say, 50.000 queries ? Also, different concurrency levels (1, 100)
might be interesting to see.

Yes, I'm to lazy to do it myself, what did you think :-p

On Tue, Nov 30, 2010 at 4:01 PM, Wagner Bianchi
wrote:

> Friends, I did a benchmark regarding to this subject.
> Please, I am considering your comments.
> => http://wbianchi.wordpress.com/2010/11/30/insert-x-insert-delayed/
>
> Best regards.
> --
> WB
>
>
> 2010/11/30 Wagner Bianchi 
>
> Maybe, the table in use must be a table that is inside cache now - SHOW
>> OPEN TABLES, controlled by table_cache, I mean.
>>
>> Well, if the amount of data trasactioned is too small as a simple INSERT,
>> you don't have to be worried, I suggest. If you partition the table, we must
>> a benchmark to know the performance relation of a INSERT and compress data
>> into Archive Storage Engine or the insertion data into a partitioned table.
>>
>> Best regards.
>> --
>> WB
>>
>>
>> 2010/11/30 Johan De Meersman 
>>
>> I would assume that it's slower because it gets put on the delay thread
>>> anyway, and thus executes only whenever that thread gets some attention. I'm
>>> not sure wether there are other influencing factors.
>>>
>>> I should also think that "not in use" in this context means "not locked
>>> against inserts", so the MyISAM insert-while-selecting at the end of a
>>> continguous table may well apply.
>>>
>>> No guarantees, though - I'm not that hot on this depth.
>>>
>>>
>>>
>>> On Tue, Nov 30, 2010 at 8:46 AM, WLGades  wrote:
>>>
>>>> What I'm confused by though, is this line.
>>>>
>>>> "Note that INSERT DELAYED is slower than a normal INSERT if the table is
>>>> not
>>>> otherwise in use."  What's the definition of "in use"?  Does a logging
>>>> table
>>>> do that given that it's pretty much append-only/write-only?
>>>>
>>>> Waynn
>>>>
>>>> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman >>> >wrote:
>>>>
>>>> > No, I think it's a good idea to do INSERT DELAYED here - it's only
>>>> logging
>>>> > application, and it's generally more important to not slow down the
>>>> > application for that. It's only ever into a single table, so there's
>>>> only
>>>> > going to be a single delay thread for it anyway.
>>>> >
>>>> > Archive tables are a good idea, agreed, but I suspect that inserts
>>>> into
>>>> > that are going to be slower than into regular MyISAM because of the
>>>> > compression, so why not use that overhead to (slightly) speed up your
>>>> > end-user experience instead ?
>>>> >
>>>> > You can always partition the table based on the log date or whatever,
>>>> if
>>>> > your table risks getting too big.
>>>> >
>>>> >
>>>> >
>>>> > On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi <
>>>> wagnerbianch...@gmail.com
>>>> > > wrote:
>>>> >
>>>> >> Well,  analyze if you need to create an excessive overhead into the
>>>> MySQL
>>>> >> Server because a simple INSERT. What you must have a look is it:
>>>> >>
>>>> >>   - How much data this connection is delivering to MySQL's handlers?
>>>> >>   - A word DELAYED in this case is making MySQL surfer?
>>>> >>
>>>> >> Perhaps, you are sophisticating something that do not need it.
>>>> Besides it,
>>>> >> analyzing your "log table", I imagine this table can be an Archive
>>>> table
>>>> >> instead of MyISAM. Log tables or history tables can be controlled by
>>>> >> Archive
>>>> >> Storage Engine to have more compressed data. Although, Archive
>>>> Storage
>>>> >> Engine only supports SELECT and INSERT. Maybe, a good deal to you,
>>>> get rid
>>>> >> of you INSERT DELAYED:
>>>> >>
>>>> >>
>>>> >>   - ALTER TABLE  ENGINE = ARCHIVE;
>>>> >>
>>>> >>
>>>> >> Best regards.
>>>> >> --
>>&

Re: MySQL upgrade from 5.0.51a to 5.1.53

[Johan De Meersman]

I suspect you need to have the new version running instead of the old one,
for the mysql_upgrade script to work.

On Tue, Nov 30, 2010 at 11:23 AM, Machiel Richards wrote:

> HI Guys
>
>  I found some info regarding a method to upgrade mysql databases.
>
>Currently the version is at mysql-5.0.51a (as installed via the
> ubuntu-8.0.4 respository).
>
> I downloaded the only version available from the web (5.1.53)
> that will work on ubuntu [except for source]. This is the binary package
> that need to be untarred into a directory and then the database to be
> initialized.
>
>The option was was looking at was to do the following (according
> to some reccomendations)
>
>
>- untar new version into seperate directory.
>-  keep the current version running.
>- cd to /path/to/new/directory/bin
>- ./mysql_upgrade -u root -p
>
>
>Running this I get a couple of errors / warnings though and
> I am hoping that someone can provide me with some answers or otherwise a
> better way to do the upgrade.
>
>
>Errors received:
>
>
>Enter password:
> Looking for 'mysql' as: ./mysql
> Looking for 'mysqlcheck' as: ./mysqlcheck
> Running 'mysqlcheck' with connection arguments: '--port=3306'
> '--socket=/var/run/mysqld/mysqld.sock'
> Running 'mysqlcheck' with connection arguments: '--port=3306'
> '--socket=/var/run/mysqld/mysqld.sock'
> machiel.test1  OK
> machiel.test2  OK
> mysql.columns_priv OK
> mysql.db   OK
> mysql.eventOK
> mysql.func OK
> mysql.help_categoryOK
> mysql.help_keyword OK
> mysql.help_relationOK
> mysql.help_topic   OK
> mysql.host OK
> mysql.ndb_binlog_index OK
> mysql.plugin   OK
> mysql.proc OK
> mysql.procs_priv   OK
> mysql.servers  OK
> mysql.tables_priv  OK
> mysql.time_zoneOK
> mysql.time_zone_leap_secondOK
> mysql.time_zone_name   OK
> mysql.time_zone_transition OK
> mysql.time_zone_transition_typeOK
> mysql.user OK
> Running 'mysql_fix_privilege_tables'...
> ERROR 1193 (HY000) at line 69: Unknown system variable 'have_csv'
> ERROR 1064 (42000) at line 71: 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 'NULL' at line 1
> ERROR 1243 (HY000) at line 72: Unknown prepared statement handler (stmt)
> given to EXECUTE
> ERROR 1243 (HY000) at line 73: Unknown prepared statement handler (stmt)
> given to DEALLOCATE PREPARE
> ERROR 1193 (HY000) at line 77: Unknown system variable 'have_csv'
> ERROR 1064 (42000) at line 79: 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 'NULL' at line 1
> ERROR 1243 (HY000) at line 80: Unknown prepared statement handler (stmt)
> given to EXECUTE
> ERROR 1243 (HY000) at line 81: Unknown prepared statement handler (stmt)
> given to DEALLOCATE PREPARE
> ERROR 1193 (HY000) at line 309: Unknown system variable 'general_log'
> ERROR 1193 (HY000) at line 310: Unknown system variable 'general_log'
> ERROR 1146 (42S02) at line 311: Table 'mysql.general_log' doesn't exist
> ERROR 1193 (HY000) at line 318: Unknown system variable 'general_log'
> ERROR 1193 (HY000) at line 320: Unknown system variable 'slow_query_log'
> ERROR 1193 (HY000) at line 321: Unknown system variable 'slow_query_log'
> ERROR 1146 (42S02) at line 322: Table 'mysql.slow_log' doesn't exist
> ERROR 1193 (HY000) at line 334: Unknown system variable 'slow_query_log'
> ERROR 1305 (42000) at line 522: FUNCTION mysql.CONCAT does not exist
> ERROR 1305 (42000) at line 537: FUNCTION mysql.CONCAT does not exist
> ERROR 1305 (42000) at line 552: FUNCTION mysql.CONCAT does not exist
> FATAL ERROR: Upgrade failed
> r...@machielr-laptop:/usr/local/mysql/bin#
>
>
>
> Regards
> Machiel
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: INSERT DELAYED and logging

[Johan De Meersman]

I would assume that it's slower because it gets put on the delay thread
anyway, and thus executes only whenever that thread gets some attention. I'm
not sure wether there are other influencing factors.

I should also think that "not in use" in this context means "not locked
against inserts", so the MyISAM insert-while-selecting at the end of a
continguous table may well apply.

No guarantees, though - I'm not that hot on this depth.


On Tue, Nov 30, 2010 at 8:46 AM, WLGades  wrote:

> What I'm confused by though, is this line.
>
> "Note that INSERT DELAYED is slower than a normal INSERT if the table is
> not
> otherwise in use."  What's the definition of "in use"?  Does a logging
> table
> do that given that it's pretty much append-only/write-only?
>
> Waynn
>
> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman  >wrote:
>
> > No, I think it's a good idea to do INSERT DELAYED here - it's only
> logging
> > application, and it's generally more important to not slow down the
> > application for that. It's only ever into a single table, so there's only
> > going to be a single delay thread for it anyway.
> >
> > Archive tables are a good idea, agreed, but I suspect that inserts into
> > that are going to be slower than into regular MyISAM because of the
> > compression, so why not use that overhead to (slightly) speed up your
> > end-user experience instead ?
> >
> > You can always partition the table based on the log date or whatever, if
> > your table risks getting too big.
> >
> >
> >
> > On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi <
> wagnerbianch...@gmail.com
> > > wrote:
> >
> >> Well,  analyze if you need to create an excessive overhead into the
> MySQL
> >> Server because a simple INSERT. What you must have a look is it:
> >>
> >>   - How much data this connection is delivering to MySQL's handlers?
> >>   - A word DELAYED in this case is making MySQL surfer?
> >>
> >> Perhaps, you are sophisticating something that do not need it. Besides
> it,
> >> analyzing your "log table", I imagine this table can be an Archive table
> >> instead of MyISAM. Log tables or history tables can be controlled by
> >> Archive
> >> Storage Engine to have more compressed data. Although, Archive Storage
> >> Engine only supports SELECT and INSERT. Maybe, a good deal to you, get
> rid
> >> of you INSERT DELAYED:
> >>
> >>
> >>   - ALTER TABLE  ENGINE = ARCHIVE;
> >>
> >>
> >> Best regards.
> >> --
> >> WB
> >>
> >>
> >> 2010/11/29 WLGades 
> >>
> >> > I'm adding a table to our site that logs all page loads.  In the past,
> >> when
> >> > I built this, I used MyISAM and INSERT DELAYED.  I went back to look
> at
> >> the
> >> > documentation to see if I should still do this, and saw this (taken
> from
> >> > http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html):
> >> >
> >> > Note that INSERT DELAYED is slower than a normal INSERT if the table
> is
> >> not
> >> > otherwise in use. There is also the additional overhead for the server
> >> to
> >> > handle a separate thread for each table for which there are delayed
> >> rows.
> >> > This means that you should use INSERT DELAYED only when you are really
> >> sure
> >> > that you need it.
> >> >
> >> > Does that mean that I shouldn't use it if all I'm doing is INSERT
> >> > (essentially an append-only table), with only very occasional SELECTs?
> >>  In
> >> > addition, the last time I took this approach for logging, it worked
> well
> >> > until the table got to 65M+ rows, when it would crash every now and
> >> then.
> >> >  I
> >> > know I can archive off the table on a per month/quarter basis as well.
> >> >
> >> > Waynn
> >> >
> >>
> >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: can't stop mysql under ubuntu

[Johan De Meersman]

On Tue, Nov 30, 2010 at 7:26 AM, Jorg W Young

> wrote:

> 2010/11/30 Johan De Meersman :
> > Yep, it's definitely an apparmor issue. Are you using your distribution's
> > packages ? I would say this is more a thing for their support - I for one
> > keep as far away from apparmor as I can :-)
> >
>
> yep I 'm using the distribution packages all from apt-get.
>

Then I would definitely say to contact your distro support or mailing lists,
and check the forums. It may be a known issue.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel