from:"Daevid Vincent"

RE: a

2015-10-18 Thread Daevid Vincent

d

> -Original Message-
> From: ryan.esca...@gmail.com [mailto:ryan.esca...@gmail.com] On Behalf
> Of Ryan Escarez
> Sent: Friday, October 16, 2015 2:47 AM
> To: Ryan Coleman
> Cc: Dennis Ruiz; mysql-le...@lists.mysql.com
> Subject: Re: a
> 
> c
> 
> On Fri, Oct 16, 2015 at 3:01 PM, Ryan Coleman 
> wrote:
> 
> > b
> >
> > > On Oct 15, 2015, at 10:07 PM, Dennis Ruiz  wrote:
> > >
> > > a
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:http://lists.mysql.com/mysql
> > >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >
> 
> 
> --
> Ryan Escarez
> CEBB F1E4 1E39 EC48 F05D  6B72 9C11 DD88 5E39 E471 $ gpg --keyserver
> pgp.mit.edu --recv-keys 0x5E39E471 main(k){float i,j,r,x,y=-
> 16;while(puts(""),y++<15)
> for(x=0;x++<84;putchar(" .:-;!/>)|%*#"[k&15])) for(i=k=r=0;j=r*r-i*i-
> 2+x/25,i=2*r*i+y/10,j*j+i*i<11&++<111;r=j);}


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

RE: How do I mysqldump different database tables to the same .sql file?

2013-11-21 Thread Daevid Vincent

Except that it outputs the USE statement if you have more than one
database, so your theory doesn't hold a lot of water IMHO. Not to mention
it's near the very top of the output so it's pretty easy to trim it off if
you REALLY needed to move the DB (which I presume is not as frequently as
simply wanting a backup/dump of a database to restore).

Thanks for the shell script suggestion, that is what I've done already to
work around this silliness.

 -Original Message-
 From: Michael Dykman [mailto:mdyk...@gmail.com]
 Sent: Thursday, November 21, 2013 1:59 PM
 To: MySql
 Subject: Re: How do I mysqldump different database tables to the same .sql
 file?
 
 There is a good reason that the USE database is not output in those
dumps..
  it would make the tool very difficult to use for moving data around.
 
 If I might suggest, a simple workaround is to create a shell script along
 these lines..  you might to do something a little more sophisticated.
 
 #
 #!/bin/sh
 
 echo  USE `database1`;  outflfile.sql
 mysqldump -(firstsetofoptions)  outfile.sql
 echo  USE `database2`;  outflfile.sql
 mysqldump -(secondsetofoptions)  outfile.sql
 
 
 
 
 On Thu, Nov 21, 2013 at 4:44 PM, Daevid Vincent dae...@daevid.com wrote:
 
  I'm working on some code where I am trying to merge two customer
accounts
  (we get people signing up under different usernames, emails, or just
 create
  a new account sometimes). I want to test it, and so I need a way to
 restore
  the data in the particular tables. Taking a dump of all the DBs and
tables
  is not feasible as it's massive, and importing (with indexes) takes
HOURS.
  I
  just want only the tables that are relevant. I can find all the tables
 that
  have `customer_id` in them with this magic incantation:
 
  SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS`
  WHERE
  `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME`
 
  Then I crafted this, but it pukes on the db name portion. :-(
 
  mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
  --complete-insert --insert-ignore --create-options --quick --force
  --set-charset --disable-keys --quote-names --comments --verbose --tables
  member_sessions.users_last_login support.tickets mydb1.clear_passwords
  mydb1.crak_subscriptions mydb1.customers mydb1.customers_free
  mydb1.customers_free_tracking mydb1.customers_log
  mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players
  mydb1content.actors_comments mydb1content.actor_collections
  mydb1content.actor_likes_users mydb1content.collections
  mydb1content.dvd_likes_users mydb1content.free_videos
  mydb1content.genre_collections mydb1content.playlists
  mydb1content.poll_votes mydb1content.scenes_comments
  mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections
  mydb1content.scene_likes_users mydb1content.videos_downloaded
  mydb1content.videos_viewed  merge_backup.sql
 
  -- Connecting to localhost...
  mysqldump: Got error: 1049: Unknown database
  'member_sessions.users_last_login' when selecting the database
  -- Disconnecting from localhost...
 
  I searched a bit and found that it seems I have to split this into
 multiple
  statements and append like I'm back in 1980. *sigh*
 
  mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
  --complete-insert --insert-ignore --create-options --quick --force
  --set-charset --disable-keys --quote-names --comments --verbose
--database
  member_sessions --tables users_last_login  merge_backup.sql
  mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
  --complete-insert --insert-ignore --create-options --quick --force
  --set-charset --disable-keys --quote-names --comments --verbose
--database
  support --tables tickets  merge_backup.sql
  mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
  --complete-insert --insert-ignore --create-options --quick --force
  --set-charset --disable-keys --quote-names --comments --verbose
--database
  mydb1 --tables clear_passwords customers customers_free
  customers_free_tracking customers_log customers_subscriptions
  customers_transactions players  merge_backup.sql
  mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
  --complete-insert --insert-ignore --create-options --quick --force
  --set-charset --disable-keys --quote-names --comments --verbose
--database
  content --tables actors_comments actor_collections actor_likes_users
  collections dvd_likes_users free_videos genre_collections playlists
  poll_votes scenes_comments scenes_ratings_users_new2 scene_collections
  scene_likes_users videos_downloaded videos_viewed  merge_backup.sql
 
  The critical flaw here is that the mysqldump program does NOT put the
  necessary USE DATABASE statement in each of these dumps since there is
  only one DB after the -database apparently. UGH. Nor do I see a command
  line
  option to force it to output this seemingly

How do I mysqldump different database tables to the same .sql file?

2013-11-21 Thread Daevid Vincent

I'm working on some code where I am trying to merge two customer accounts
(we get people signing up under different usernames, emails, or just create
a new account sometimes). I want to test it, and so I need a way to restore
the data in the particular tables. Taking a dump of all the DBs and tables
is not feasible as it's massive, and importing (with indexes) takes HOURS. I
just want only the tables that are relevant. I can find all the tables that
have `customer_id` in them with this magic incantation:
 
SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE
`COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME`
 
Then I crafted this, but it pukes on the db name portion. :-(
 
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --tables
member_sessions.users_last_login support.tickets mydb1.clear_passwords
mydb1.crak_subscriptions mydb1.customers mydb1.customers_free
mydb1.customers_free_tracking mydb1.customers_log
mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players
mydb1content.actors_comments mydb1content.actor_collections
mydb1content.actor_likes_users mydb1content.collections
mydb1content.dvd_likes_users mydb1content.free_videos
mydb1content.genre_collections mydb1content.playlists
mydb1content.poll_votes mydb1content.scenes_comments
mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections
mydb1content.scene_likes_users mydb1content.videos_downloaded
mydb1content.videos_viewed  merge_backup.sql
 
-- Connecting to localhost...
mysqldump: Got error: 1049: Unknown database
'member_sessions.users_last_login' when selecting the database
-- Disconnecting from localhost...
 
I searched a bit and found that it seems I have to split this into multiple
statements and append like I'm back in 1980. *sigh*
 
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --database
member_sessions --tables users_last_login  merge_backup.sql
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --database
support --tables tickets  merge_backup.sql
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --database
mydb1 --tables clear_passwords customers customers_free
customers_free_tracking customers_log customers_subscriptions
customers_transactions players  merge_backup.sql
mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert
--complete-insert --insert-ignore --create-options --quick --force
--set-charset --disable-keys --quote-names --comments --verbose --database
content --tables actors_comments actor_collections actor_likes_users
collections dvd_likes_users free_videos genre_collections playlists
poll_votes scenes_comments scenes_ratings_users_new2 scene_collections
scene_likes_users videos_downloaded videos_viewed  merge_backup.sql
 
The critical flaw here is that the mysqldump program does NOT put the
necessary USE DATABASE statement in each of these dumps since there is
only one DB after the -database apparently. UGH. Nor do I see a command line
option to force it to output this seemingly obvious statement.
 
It's a pretty significant shortcoming of mysqldump if you ask me that I
can't do it the way I had it in the first example since that's pretty much
standard SQL convetion of db.table.column format. And even more baffling is
why it wouldn't dump out the USE statement always even if there is only
one DB. It's a few characters and would save a lot of headaches in case
someone tried to dump their .sql file into the wrong DB on accident.
 
Plus it's not easy to edit a 2.6GB file to manually insert these USE
lines.
 
Is there a way to do this with some command line option I'm not seeing in
the man page?

Why is mySQL not respecting foreign characters as different

2013-09-26 Thread Daevid Vincent

How come MySQL is not differentiating between these characters?

SELECT text_id, us, de, es, fr  
  FROM texts
  WHERE us = fr;

Results in matching here. Notice the difference in the scene vs scène

text_id us  es  de fr  
--  --  --  -  
all_page_scene  scene   escena  Filmszene  scène  


I wold expect this NOT to match.

Do I have to add something to my query to tell MySQL to respect other
character sets as different?

CREATE TABLE `texts` (
  `text_id` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci NOT
NULL DEFAULT '',
  `us` text,
  `es` text,
  `de` text,
  `fr` text,
  PRIMARY KEY (`text_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

FieldType Collation  NullKey Default  Extra
Privileges   Comment  
---  ---  -  --  --  ---  --
---  -
text_id  varchar(50)  latin1_general_ci  NO  PRI
select,insert,update,references   
us   text utf8_general_ciYES (NULL)
select,insert,update,references   
es   text utf8_general_ciYES (NULL)
select,insert,update,references   
de   text utf8_general_ciYES (NULL)
select,insert,update,references   
fr   text utf8_general_ciYES (NULL)
select,insert,update,references   


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

Google swaps out MySQL, moves to MariaDB

2013-09-16 Thread Daevid Vincent

http://www.theregister.co.uk/2013/09/12/google_mariadb_mysql_migration/

#1341 [Com]: InnoDB ibdata1 never shrinks after data is removed

2013-07-23 Thread Daevid Vincent

For 10 YEARS we have been asking for a way to reclaim the ibdata files (or 
even .MYD files) and finally someone from mysql/oracle replied. It's not great 
news, but at least they acknowledge and give some explanations.

-Original Message-
From: Bug Database [mailto:do-not-re...@mysql.com] 
Sent: Tuesday, July 23, 2013 2:30 PM
Subject: #1341 [Com]: InnoDB ibdata1 never shrinks after data is removed

View this bug at: http://bugs.mysql.com/1341

 Updated by:   James Day
 Reported by:  Scott Ellsworth
 Category: Server: InnoDB
 Severity: S4 (Feature request)
 Status:   Verified
 Version:  All versions
 OS:   Any
 Defect Class: D5 (Feature request)

[23 Jul 21:29] James Day

Ten years is because we know it is desired but it is a hard problem. To
free the space takes moving lots of internal pointers and data. That is
almost as much work as alter table, but in theory it could be done
gradually in the background. But would be very complicated with high
chance for bugs and performance problems due to the locking needed.

With 5.6 the best combination that is implemented so far is:

1. Innodb_file_per_table, on by default in 5.6. Prevents data from
going to shared tablespace.

2. Undo logs in their own tablespace, see
http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html Separate
Tablespaces for InnoDB Undo Logs . This will prevent undo logs from
causing the shared tablespace to grow, but there is still no way to
shrink the undo logs.

3. Shared tablespace then contains data dictionary, change buffer and
statistics, so is much less likely to grow to big sizes.

4. For some workloads the compressed tables feature will help to reduce
data sizes and hence the times. Still some way to go on performance of
this for OLTP but it's way better in 5.6 than 5.5, in part due to many
improvements suggested by Facebook. Best used in general for tables
that don't have the highest change rates but do have big column sizes
that compress well.

This definitely does not solve all problems:

5. The ibd files can have free space and the only way to release that
is slow alter table or truncate. Slow for big tables, not practical on
a live server. But is practical on a system that has failover
capability. Can take a window of slow failover time and do it during
this window with a known risk that there might be slower failover than
usual. If you do this, drop all the non-unique secondary indexes then
add them back later, the fast index recreate will save you time and
rebuild the indexes with less free space inside the pages.

6. The portable tablespaces feature in 5.6 could then be used to load
the tablespaces into a new copy of Mysql with small shared tablespace
and redo log. This still requires downtime, so still requires a
failover solution, but it's far faster for big tables than mysqldump
and reload.

7. Dropping tables should be faster in 5.6 and 5.5.20 or later but it's
still going to be slower using innodb_file_per_table than having the
tables in the shared tablespace. More practical for big tables that
aren't dropped often than for small tables that are very regularly
dropped. For the big/infrequent combination, most people already use
innodb_file_per_table. The work on this in bug
http://bugs.mysql.com/bug.php?id=51325 helps a lot but there's still
scope for more.

So we know it's desirable, some major improvements that help the
workarounds, but still no way to make it practical online. For now,
failover based workarounds are still the way to go. Not ideal, but at
least doable.

James Day, MySQL Senior Principal Support Engineer, Oracle

Earlier comments can be viewed at http://bugs.mysql.com/1341

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

Need query to determine different column definitions across tables

2013-07-08 Thread Daevid Vincent

I'm noticing that across our several databases and hundreds of tables that
column definitions are not consistent. I'm wondering if there is a tool or
query (using INFORMATION_SCHEMA perhaps) that will show me all databases,
tables and columns where they don't match (by column name).
 
For example in one table `foo_id` might be UNSIGNED and in other's it is
not. Or maybe it's INT(11) in some and INT(10) or worse MEDIUMINT in others.
Or extending further Charset/Collation might mismatch and be that stupid
latin1_swedish_ci and fixed to be utf8 in others. 
 
Stuff like that. I want to see everything where there is some difference.

RE: Need query to determine different column definitions across tables

2013-07-08 Thread Daevid Vincent

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Monday, July 08, 2013 2:11 PM
 To: mysql@lists.mysql.com
 Subject: Need query to determine different column definitions across
tables

 I'm noticing that across our several databases and hundreds of tables that
 column definitions are not consistent. I'm wondering if there is a tool or
 query (using INFORMATION_SCHEMA perhaps) that will show me all databases,
 tables and columns where they don't match (by column name).

 For example in one table `foo_id` might be UNSIGNED and in other's it is
 not. Or maybe it's INT(11) in some and INT(10) or worse MEDIUMINT in
others.
 Or extending further Charset/Collation might mismatch and be that stupid
 latin1_swedish_ci and fixed to be utf8 in others.

 Stuff like that. I want to see everything where there is some difference.

Well, here's the query I'm using currently. Will post updates as I tweak it.

USE `information_schema`;

SELECT 
t1.`COLUMN_NAME`,
t1.`TABLE_NAME`,
t1.`COLUMN_TYPE`,
-- CONCAT(t1.`TABLE_NAME`,'.',t1.`COLUMN_TYPE`) as t1_type,
t2.`TABLE_NAME`,
t2.`COLUMN_TYPE`
-- CONCAT(t2.`TABLE_NAME`,'.',t2.`COLUMN_TYPE`) AS t2_type
FROM
`COLUMNS` AS t1 
LEFT JOIN `COLUMNS` AS t2 
   ON t1.`COLUMN_NAME` = t2.`COLUMN_NAME` 
AND t1.`COLUMN_TYPE`  t2.`COLUMN_TYPE` 
WHERE   t1.`TABLE_SCHEMA` = 'mydatabase' 
AND t2.`TABLE_NAME` IS NOT NULL
-- HAVING t2_type IS NOT NULL
ORDER BY `COLUMN_NAME` ASC;

Having separate columns there is easier to read/compare than CONCAT() I
think.

Another bulk version that comes in handy:

SELECT `COLUMN_NAME`, `COLUMN_TYPE`, `TABLE_SCHEMA`, 
`TABLE_NAME`, `CHARACTER_SET_NAME`, `COLLATION_NAME` 
FROM   `COLUMNS` WHERE `TABLE_SCHEMA` = ' mydatabase ' 
ORDER BY `COLUMN_NAME`;

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

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

2013-06-12 Thread Daevid Vincent

This idea is so fancy pants and clever I *wish* it could have worked for me.
I checked and we actually have 65 genres currently (with more to come I'm
sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me some
bitmasks and this solution is so elegant. It's unfortunate there isn't a way
to use more than 64-bits natively.

You're RICK JAMES Bitch! :-p   (please tell me you know the Dave Chappelles
skit I'm referring to)

 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: Wednesday, June 12, 2013 9:39 AM
 To: Daevid Vincent; mysql@lists.mysql.com
 Cc: 'shawn green'
 Subject: RE: How do I select all rows of table that have some rows in
 another table (AND, not OR)
 
 Thinking out of the box... (And posting my reply at the 'wrong' end of the
 email.)...
 
 Are there fewer than 64 genres?  Use a SET or BIGINT UNSIGNED.
 
 AND sg.`genre_id` IN (10,38)
 AND sg.`genre_id` NOT IN (22,61)
 
 --
 AND  genre  ((110) | (138)) != 0
 AND  genre  ((122) | (161))  = 0
 
 This would avoid having that extra table, and save a lot of space.
 
 If you have more than 64 genres, then
 Plan A: clump them into some kind of application grouping and use multiple
 INTs/SETs.
 Plan B: do mod  div arithmetic to compute which genre field to tackle.
 
 For B, something like:
 AND  (genre1  (10)) + (genre3  (18)) != 0
 AND  (genre2  (12)) + (genre6  (11))  = 0
 (That's assuming 10 bits per genre# field.  I would use 32 and INT
 UNSIGNED.)
 
 
 
  -Original Message-
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Tuesday, June 11, 2013 4:17 PM
  To: mysql@lists.mysql.com
  Cc: 'shawn green'
  Subject: RE: How do I select all rows of table that have some rows in
  another table (AND, not OR)
 
 
 
   -Original Message-
   From: shawn green [mailto:shawn.l.gr...@oracle.com]
   Sent: Tuesday, June 11, 2013 2:16 PM
   To: mysql@lists.mysql.com
   Subject: Re: How do I select all rows of table that have some rows in
   another table (AND, not OR)
  
   Hello Daevid,
  
   On 6/11/2013 3:59 PM, Daevid Vincent wrote:
I am trying to implement a filter so that a user could select
various
   genres
they want in or out. Perhaps they like 'action' and 'car chases'
but don't like 'foreign' and 'drifting' (or whatever...)
   
So I want something sort of like this, however IN() is using an OR
comparison when I need it to be an AND
   
SELECT DISTINCT
 s.`scene_id` AS `id`,
 GROUP_CONCAT(sg.`genre_id`) FROM
 `dvds` AS d
 JOIN `scenes_list` AS s
 ON s.`dvd_id` = d.`dvd_id`
 JOIN `scenes_genres` AS sg
 ON sg.`scene_id` = s.`scene_id`
 AND sg.`genre_id` IN (10,38)
 AND sg.`genre_id` NOT IN (22,61) GROUP BY
s.`scene_id`;
   
This is giving me way way too many rows returned.
   
For example, I would expect this scene_id to be in the result set:
   
SELECT * FROM scenes_genres WHERE scene_id = 17;
   
scene_id  genre_id
  --
   17   1
   17   3
   17  10 --
   17  19
   17  38 --
   17  53
   17  58
   17  59
   
And this scene ID to NOT be in the result set:
   
SELECT * FROM scenes_genres WHERE scene_id = 11;
   
scene_id  genre_id
  --
   11   1
   11  10 --
   11  19
   11  31
   11  32
   -- but does not have 38
   11  59
   
I've tried various subselect ideas, but of course this fails b/c
  genre_id
can't be multiple things at one time (AND)
   
JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
 AND sg.`genre_id` IN (
   SELECT `genre_id` FROM `scenes_genres`
WHERE `genre_id` = 10
   AND `genre_id` = 38
   AND `genre_id`  22
   AND `genre_id`  61
)
   
And straight up like this failure too...
   
   JOIN `scenes_genres` AS sg
 ON sg.`scene_id` = s.`scene_id`
AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)
   
So I'm sort of out of ideas at this point and hoping someone has a
way
  to
   do
this.
   
Also, just for SG this is how we are currently implementing it, but
we
   feel
the REGEXP is killing our queries and while clever is a bit hacky
and nullifies any indexes we have on the genres column as it
requires a file_sort table scan to compare substrings basically...
   
SELECT * FROM scene_all_genres WHERE scene_id = 17;
   
scene_id  genres

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

2013-06-12 Thread Daevid Vincent

Oh! I must have misread. I didn't see how you had a solution for  64 bits.
I may have to experiment with that!

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Wednesday, June 12, 2013 11:26 AM
 To: mysql@lists.mysql.com
 Subject: RE: How do I select all rows of table that have some rows in
 another table (AND, not OR)

 This idea is so fancy pants and clever I *wish* it could have worked for
 me. I checked and we actually have 65 genres currently (with more to come
 I'm sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me
 some bitmasks and this solution is so elegant. It's unfortunate there
 isn't a way to use more than 64-bits natively.

 You're RICK JAMES Bitch! :-p   (please tell me you know the Dave
 Chappelles skit I'm referring to)

  -Original Message-
  From: Rick James [mailto:rja...@yahoo-inc.com]
  Sent: Wednesday, June 12, 2013 9:39 AM
  To: Daevid Vincent; mysql@lists.mysql.com
  Cc: 'shawn green'
  Subject: RE: How do I select all rows of table that have some rows in
  another table (AND, not OR)

  Thinking out of the box... (And posting my reply at the 'wrong' end of
 the
  email.)...

  Are there fewer than 64 genres?  Use a SET or BIGINT UNSIGNED.

  AND sg.`genre_id` IN (10,38)
  AND sg.`genre_id` NOT IN (22,61)

  --
  AND  genre  ((110) | (138)) != 0
  AND  genre  ((122) | (161))  = 0

  This would avoid having that extra table, and save a lot of space.

  If you have more than 64 genres, then
  Plan A: clump them into some kind of application grouping and use
 multiple
  INTs/SETs.
  Plan B: do mod  div arithmetic to compute which genre field to tackle.

  For B, something like:
  AND  (genre1  (10)) + (genre3  (18)) != 0
  AND  (genre2  (12)) + (genre6  (11))  = 0
  (That's assuming 10 bits per genre# field.  I would use 32 and INT
  UNSIGNED.)

   -Original Message-
   From: Daevid Vincent [mailto:dae...@daevid.com]
   Sent: Tuesday, June 11, 2013 4:17 PM
   To: mysql@lists.mysql.com
   Cc: 'shawn green'
   Subject: RE: How do I select all rows of table that have some rows in
   another table (AND, not OR)

-Original Message-
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows
 in
another table (AND, not OR)

Hello Daevid,

On 6/11/2013 3:59 PM, Daevid Vincent wrote:
 I am trying to implement a filter so that a user could select
 various
genres
 they want in or out. Perhaps they like 'action' and 'car
 chases'
 but don't like 'foreign' and 'drifting' (or whatever...)

 So I want something sort of like this, however IN() is using an
 OR
 comparison when I need it to be an AND

 SELECT DISTINCT
  s.`scene_id` AS `id`,
  GROUP_CONCAT(sg.`genre_id`) FROM
  `dvds` AS d
  JOIN `scenes_list` AS s
  ON s.`dvd_id` = d.`dvd_id`
  JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
  AND sg.`genre_id` IN (10,38)
  AND sg.`genre_id` NOT IN (22,61) GROUP BY
 s.`scene_id`;

 This is giving me way way too many rows returned.

 For example, I would expect this scene_id to be in the result set:

 SELECT * FROM scenes_genres WHERE scene_id = 17;

 scene_id  genre_id
   --
17   1
17   3
17  10 --
17  19
17  38 --
17  53
17  58
17  59

 And this scene ID to NOT be in the result set:

 SELECT * FROM scenes_genres WHERE scene_id = 11;

 scene_id  genre_id
   --
11   1
11  10 --
11  19
11  31
11  32
  -- but does not have 38
11  59

 I've tried various subselect ideas, but of course this fails b/c
   genre_id
 can't be multiple things at one time (AND)

 JOIN `scenes_genres` AS sg
   ON sg.`scene_id` = s.`scene_id`
  AND sg.`genre_id` IN (
SELECT `genre_id` FROM `scenes_genres`
 WHERE `genre_id` = 10
AND `genre_id` = 38
AND `genre_id`  22
AND `genre_id`  61
 )

 And straight up like this failure too...

JOIN `scenes_genres` AS sg
  ON sg.`scene_id` = s.`scene_id`
 AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
 AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)

 So I'm sort of out

How do I select all rows of table that have some rows in another table (AND, not OR)

2013-06-11 Thread Daevid Vincent

I am trying to implement a filter so that a user could select various genres
they want in or out. Perhaps they like 'action' and 'car chases' but
don't like 'foreign' and 'drifting' (or whatever...)

So I want something sort of like this, however IN() is using an OR
comparison when I need it to be an AND

SELECT DISTINCT 
s.`scene_id` AS `id`,
GROUP_CONCAT(sg.`genre_id`)
FROM
`dvds` AS d 
JOIN `scenes_list` AS s 
ON s.`dvd_id` = d.`dvd_id` 
JOIN `scenes_genres` AS sg 
ON sg.`scene_id` = s.`scene_id` 
AND sg.`genre_id` IN (10,38) 
AND sg.`genre_id` NOT IN (22,61)
GROUP BY s.`scene_id`;

This is giving me way way too many rows returned. 

For example, I would expect this scene_id to be in the result set:

SELECT * FROM scenes_genres WHERE scene_id = 17;

scene_id  genre_id  
  --
  17   1
  17   3
  17  10 --
  17  19
  17  38 --
  17  53
  17  58
  17  59

And this scene ID to NOT be in the result set:

SELECT * FROM scenes_genres WHERE scene_id = 11;

scene_id  genre_id  
  --
  11   1
  11  10 --  
  11  19
  11  31
  11  32
   -- but does not have 38
  11  59

I've tried various subselect ideas, but of course this fails b/c genre_id
can't be multiple things at one time (AND)

   JOIN `scenes_genres` AS sg 
 ON sg.`scene_id` = s.`scene_id` 
AND sg.`genre_id` IN (
  SELECT `genre_id` FROM `scenes_genres` 
   WHERE `genre_id` = 10 
  AND `genre_id` = 38
  AND `genre_id`  22 
  AND `genre_id`  61
   )

And straight up like this failure too...

  JOIN `scenes_genres` AS sg 
ON sg.`scene_id` = s.`scene_id` 
   AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38) 
   AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)

So I'm sort of out of ideas at this point and hoping someone has a way to do
this.

Also, just for SG this is how we are currently implementing it, but we feel
the REGEXP is killing our queries and while clever is a bit hacky and
nullifies any indexes we have on the genres column as it requires a
file_sort table scan to compare substrings basically...

SELECT * FROM scene_all_genres WHERE scene_id = 17;

scene_id  genres  
  
  17  1|3|10|19|38|53|58|59|  

SELECT * FROM scene_all_genres WHERE scene_id = 11;

scene_id  genres 
  ---
  11  1|10|19|31|32|59|  

SELECT DISTINCT 
s.`scene_id` AS `id`,
sg.`genres` 
FROM
`scene_all_genres` AS sg,
`dvds` AS d,
`scenes_list` AS s 
WHEREdvd_id` = d.`dvd_id` 
AND sg.`scene_id` = s.`scene_id` 
AND sg.`genres` REGEXP '[[::]]10[[::]].*[[::]]38[[::]]' 
AND sg.`genres` NOT REGEXP '(([[::]]22[[::]])|([[::]]61[[::]]))'

;

http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp

[[::]], [[::]]

These markers stand for word boundaries. They match the beginning and end of
words, respectively. A word is a sequence of word characters that is not
preceded by or followed by word characters. A word character is an
alphanumeric character in the alnum class or an underscore (_).



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

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

2013-06-11 Thread Daevid Vincent

 -Original Message-
 From: shawn green [mailto:shawn.l.gr...@oracle.com]
 Sent: Tuesday, June 11, 2013 2:16 PM
 To: mysql@lists.mysql.com
 Subject: Re: How do I select all rows of table that have some rows in
 another table (AND, not OR)

 Hello Daevid,

 On 6/11/2013 3:59 PM, Daevid Vincent wrote:
  I am trying to implement a filter so that a user could select various
 genres
  they want in or out. Perhaps they like 'action' and 'car chases' but
  don't like 'foreign' and 'drifting' (or whatever...)

  So I want something sort of like this, however IN() is using an OR
  comparison when I need it to be an AND

  SELECT DISTINCT
   s.`scene_id` AS `id`,
   GROUP_CONCAT(sg.`genre_id`)
  FROM
   `dvds` AS d
   JOIN `scenes_list` AS s
   ON s.`dvd_id` = d.`dvd_id`
   JOIN `scenes_genres` AS sg
   ON sg.`scene_id` = s.`scene_id`
   AND sg.`genre_id` IN (10,38)
   AND sg.`genre_id` NOT IN (22,61)
  GROUP BY s.`scene_id`;

  This is giving me way way too many rows returned.

  For example, I would expect this scene_id to be in the result set:

  SELECT * FROM scenes_genres WHERE scene_id = 17;

  scene_id  genre_id
    --
 17   1
 17   3
 17  10 --
 17  19
 17  38 --
 17  53
 17  58
 17  59

  And this scene ID to NOT be in the result set:

  SELECT * FROM scenes_genres WHERE scene_id = 11;

  scene_id  genre_id
    --
 11   1
 11  10 --
 11  19
 11  31
 11  32
 -- but does not have 38
 11  59

  I've tried various subselect ideas, but of course this fails b/c
genre_id
  can't be multiple things at one time (AND)

  JOIN `scenes_genres` AS sg
ON sg.`scene_id` = s.`scene_id`
   AND sg.`genre_id` IN (
 SELECT `genre_id` FROM `scenes_genres`
  WHERE `genre_id` = 10
 AND `genre_id` = 38
 AND `genre_id`  22
 AND `genre_id`  61
  )

  And straight up like this failure too...

 JOIN `scenes_genres` AS sg
   ON sg.`scene_id` = s.`scene_id`
  AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38)
  AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)

  So I'm sort of out of ideas at this point and hoping someone has a way
to
 do
  this.

  Also, just for SG this is how we are currently implementing it, but we
 feel
  the REGEXP is killing our queries and while clever is a bit hacky and
  nullifies any indexes we have on the genres column as it requires a
  file_sort table scan to compare substrings basically...

  SELECT * FROM scene_all_genres WHERE scene_id = 17;

  scene_id  genres

 17  1|3|10|19|38|53|58|59|

  SELECT * FROM scene_all_genres WHERE scene_id = 11;

  scene_id  genres
    ---
 11  1|10|19|31|32|59|

  SELECT DISTINCT
   s.`scene_id` AS `id`,
   sg.`genres`
  FROM
   `scene_all_genres` AS sg,
   `dvds` AS d,
   `scenes_list` AS s
  WHEREdvd_id` = d.`dvd_id`
   AND sg.`scene_id` = s.`scene_id`
   AND sg.`genres` REGEXP '[[::]]10[[::]].*[[::]]38[[::]]'
   AND sg.`genres` NOT REGEXP
 '(([[::]]22[[::]])|([[::]]61[[::]]))'

  ;

  http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp

  [[::]], [[::]]

  These markers stand for word boundaries. They match the beginning and
end
 of
  words, respectively. A word is a sequence of word characters that is not
  preceded by or followed by word characters. A word character is an
  alphanumeric character in the alnum class or an underscore (_).

 To me it looks like you want to know how to match N for N when looking
 for subset of properties. It's a multi-step process using plain SQL but
 it's fast:

 1) Build a list of scenes containing the genres you want to see

 CREATE TEMPORARY TABLE tmpList(key(scene_id, genre_id))
 SELECT scene_id, genre_id
 FROM scenes_genres
 WHERE genre_id` IN (10,38)  -- in this case you have 2 terms to mach

 2) From that list, determine which of those scenes also contain unwanted
 genres and remove them.

 DELETE tmpList
 FROM tmpList t
 INNER JOIN scenes_genres sg
on sg.scene_id = t.scene_id
and sg.genre_id IN (22,61)

 # at this point, tmpList contains all scenes that have any of the
 desired genres but none of the unwanted ones.

 3) Check to see if any scene has all N matches.

 SELECT scene_id, count(genre_id) as matches
 FROM tmpList
 GROUP BY scene_id
 HAVING matches = 2
 /* ^--- this is the number of terms you are trying to match. */

 Of course you can modify the last query to eliminate the HAVING clause
 and pick

Major MySQL Revision Takes on NoSQL

2013-02-07 Thread Daevid Vincent

http://adtmag.com/articles/2013/02/06/mysql-update.aspx

Help with purging old logs for each customer ID

2012-10-25 Thread Daevid Vincent

I have a customer log table that is starting to rapidly fill up (we have
hundreds of thousands of users, but many are transient, and use the service
for a few months, or use the free trial and quit, etc.)

CREATE TABLE `customers_log` (
  `customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned default '0',
  `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
  `type`
enum('View','Action','Admin','Search','Login','Logout','Access','General','A
PI'),
  `source` enum('web','mobile','system'),
  `body` text,
  PRIMARY KEY  (`customer_log_id`),
  KEY `created_on` (`created_on`),
  KEY `customers_id` (`customer_id`)
) ENGINE=InnoDB

What I'd like to do now is make a 'rolling log' in that I want to DELETE any
entries older than 90 days for EACH `customer_id`.

I'm not sure how to do that in a query? I'd rather not iterate over each
customer_id if I can help it.




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

RE: Help with purging old logs for each customer ID

2012-10-25 Thread Daevid Vincent

Well, the customer_id is relevant in that I want the last 90 days relative
to each customer.

customer_id = 123 might have logs from jan - mar
customer_id = 444 might have logs from feb - may

So it's a rolling log of THEIR last 90 days from their last log (most
recent) back 90 days from there.

Does that make more sense?

I guess I was trying to avoid looping over every customer ID and computing
if I could help it. I thought by using a GROUP BY or something it could
group all the logs for a given customer and then trim them that way.

But maybe brute force is the way to go?

 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: Thursday, October 25, 2012 1:09 PM
 To: Daevid Vincent; mysql@lists.mysql.com
 Subject: RE: Help with purging old logs for each customer ID
 
 Off hand, I would iterate over the PRIMARY KEY, looking at a thousand rows
 at a time, DELETEing any that need to be purged.  I would use a Perl or
PHP
 loop, or write a stored procedure.  More discussion of huge deletes
(which
 this _could_ be):
 http://mysql.rjweb.org/doc.php/deletebig
 (PARTITIONing does not apply in your case, as I understand it.)
 
 I like the loop:
 
 SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1;
 DELETE FROM customers_log
 WHERE customer_log_id = @left_off AND customer_log_id  @z
   AND created_on  NOW() - INTERVAL 90 DAY;
 sleep a few seconds (to be a nice guy)
 
 Plus code to take care of iterating and terminating.
 
 That loop could be done continually.
 
 It seems that customer_id is irrelevant??
 
  -Original Message-
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Thursday, October 25, 2012 11:46 AM
  To: mysql@lists.mysql.com
  Subject: Help with purging old logs for each customer ID
 
  I have a customer log table that is starting to rapidly fill up (we
  have hundreds of thousands of users, but many are transient, and use
  the service for a few months, or use the free trial and quit, etc.)
 
  CREATE TABLE `customers_log` (
`customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
`customer_id` int(10) unsigned default '0',
`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
  CURRENT_TIMESTAMP,
`type`
  enum('View','Action','Admin','Search','Login','Logout','Access','Genera
  l','A
  PI'),
`source` enum('web','mobile','system'),
`body` text,
PRIMARY KEY  (`customer_log_id`),
KEY `created_on` (`created_on`),
KEY `customers_id` (`customer_id`)
  ) ENGINE=InnoDB
 
  What I'd like to do now is make a 'rolling log' in that I want to
  DELETE any entries older than 90 days for EACH `customer_id`.
 
  I'm not sure how to do that in a query? I'd rather not iterate over
  each customer_id if I can help it.
 
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


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

RE: Help with purging old logs for each customer ID

2012-10-25 Thread Daevid Vincent

Thank you Rick! You're a super freak! ;-p

This gets me in the vicinity of where I'm trying to go and I learned a few
new tricks with the StartLoop: stuff too! Neat!

d.

 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: Thursday, October 25, 2012 1:54 PM
 To: Daevid Vincent; mysql@lists.mysql.com
 Subject: RE: Help with purging old logs for each customer ID
 
 If the 90 days is back from MAX(created_on) for a given customer...
INDEX(customer_id, created_on)
 will probably be needed.  And that should replace KEY `customers_id`
 (`customer_id`).
 
 Maybe...
 
 DELETE FROM customers_log AS a
 WHERE a.customer_log_id = @left_off AND a.customer_log_id  @z
   AND a.created_on 
  ( SELECT MAX(created_on) FROM customers_log
  WHERE customer_id = a.customer_id
  )
   - INTERVAL 90 DAY );
 (Since this has the subquery, I would do only 100 at a time, not 1000)
 
 Or...
 CREATE TEMPORARY TABLE tmp
 SELECT customer_id, MAX(created_on) - INTERVAL 90 DAY  AS cutoff
 FROM customers_log
 GROUP BY customer_id;
 DELETE FROM customers_log AS a
JOIN tmp  ON a.customer_id = tmp.customer_id
 WHERE a.customer_log_id = @left_off AND a.customer_log_id  @z
   AND a.created_on  tmp.cutoff;
 
 If you have millions of rows, a delete without some kind of loop is asking
 for trouble.
 
 Or...
 Turning things around to base it on customers...
 Loop through customer_ids (yeah, you did not want to do this)
 
 SELECT @id := 0;
 StartLoop:
 SELECT @id := customer_id WHERE customer_id  @id ORDER BY customer_id
 LIMIT 1;
 if @id is NULL, exit
 DELETE FROM customers_log AS a
 WHERE a.customer_id = @id
   AND a.created_on 
  ( SELECT MAX(created_on) FROM customers_log
  WHERE customer_id = @id
  )
   - INTERVAL 90 DAY );
 EndLoop.
 
 Since there is no rush for the purging, there is little need to optimize
it
 other than to keep it from interfering with other queries.  To that end,
the
 compound index I propose is important.
 
  -Original Message-
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Thursday, October 25, 2012 1:33 PM
  To: Rick James; mysql@lists.mysql.com
  Subject: RE: Help with purging old logs for each customer ID
 
  Well, the customer_id is relevant in that I want the last 90 days
  relative to each customer.
 
  customer_id = 123 might have logs from jan - mar customer_id = 444
  might have logs from feb - may
 
  So it's a rolling log of THEIR last 90 days from their last log (most
  recent) back 90 days from there.
 
  Does that make more sense?
 
  I guess I was trying to avoid looping over every customer ID and
  computing if I could help it. I thought by using a GROUP BY or
  something it could group all the logs for a given customer and then
  trim them that way.
 
  But maybe brute force is the way to go?
 
   -Original Message-
   From: Rick James [mailto:rja...@yahoo-inc.com]
   Sent: Thursday, October 25, 2012 1:09 PM
   To: Daevid Vincent; mysql@lists.mysql.com
   Subject: RE: Help with purging old logs for each customer ID
  
   Off hand, I would iterate over the PRIMARY KEY, looking at a thousand
   rows at a time, DELETEing any that need to be purged.  I would use a
   Perl or
  PHP
   loop, or write a stored procedure.  More discussion of huge deletes
  (which
   this _could_ be):
   http://mysql.rjweb.org/doc.php/deletebig
   (PARTITIONing does not apply in your case, as I understand it.)
  
   I like the loop:
  
   SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1;
   DELETE FROM customers_log
   WHERE customer_log_id = @left_off AND customer_log_id  @z
 AND created_on  NOW() - INTERVAL 90 DAY;
   sleep a few seconds (to be a nice guy)
  
   Plus code to take care of iterating and terminating.
  
   That loop could be done continually.
  
   It seems that customer_id is irrelevant??
  
-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Thursday, October 25, 2012 11:46 AM
To: mysql@lists.mysql.com
Subject: Help with purging old logs for each customer ID
   
I have a customer log table that is starting to rapidly fill up (we
have hundreds of thousands of users, but many are transient, and
  use
the service for a few months, or use the free trial and quit, etc.)
   
CREATE TABLE `customers_log` (
  `customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned default '0',
  `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
  `type`
   
  enum('View','Action','Admin','Search','Login','Logout','Access','Gen
era
l','A
PI'),
  `source` enum('web','mobile','system'),
  `body` text,
  PRIMARY KEY  (`customer_log_id`),
  KEY `created_on` (`created_on

need list of country ISO code to demonyms

2012-10-03 Thread Daevid Vincent

Anyone have a SQL dump or other programmatically useable map of country ISO
codes to demonyms?
http://www.geography-site.co.uk/pages/countries/demonyms.html
 
I can parse the strings I suppose there, but that's not quite as accurate,
and ripping that out of the HTML page to parse seems painful too.

RE: need list of country ISO code to demonyms

2012-10-03 Thread Daevid Vincent

HA! No $hit! Well isn't that clever. I didn't know that you can highlight a
table like that, and paste it into Excel. That darn Microsoft -- they think
of everything! :) Thanks for the tip.

 -Original Message-
 From: Nuno Tavares [mailto:nuno.tava...@dri.pt]
 Sent: Wednesday, October 03, 2012 3:10 PM
 To: mysql@lists.mysql.com
 Subject: Re: need list of country ISO code to demonyms

 I don't have it, sorry.

 But it took me 10 seconds to copy  paste it to an Excel, so I could
 save it as CSV and import it directly to MySQL. Further on, it'd be as
 simples as JOIN by name with a table with ISO-to-country-names, which
 you can fetch in hundreds of places, such as Wikipedia.

 -NT

 Em 03-10-2012 22:22, Daevid Vincent escreveu:
  Anyone have a SQL dump or other programmatically useable map of country
 ISO
  codes to demonyms?
  http://www.geography-site.co.uk/pages/countries/demonyms.html

  I can parse the strings I suppose there, but that's not quite as
accurate,
  and ripping that out of the HTML page to parse seems painful too.

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

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

RE: New Fast MySQL Compatible Server Released under the GPL License

2012-08-22 Thread Daevid Vincent

Well, considering that MySQL/Sun/Oracle can't even figure out how to reduce
an ibdata1 file from ever-expanding after a decade
(http://bugs.mysql.com/1341), it doesn't surprise me that parallel computing
is a brain-stumper for them. :-\

Besides, I would suspect that Oracle would see this as one more threat
against their cash cow product if they were to make MySQL even
faster/better.

I've not tried this offering below, nor do I know anything about it, but
when a big company is involved, usually it comes down to money -- making it
or spending it.

d

 -Original Message-
 From: Gael Martinez [mailto:gael.marti...@gmail.com]
 Sent: Wednesday, August 22, 2012 10:13 AM
 To: Hiromichi Watari
 Cc: mysql@lists.mysql.com
 Subject: Re: New Fast MySQL Compatible Server Released under the GPL
License
 
 On Wed, Aug 22, 2012 at 11:43 AM, Hiromichi Watari 
 hiromichiwat...@yahoo.com wrote:
 
  [..]
 
  Available at www.paralleluniverse-inc.com
 
 
 Hello
 Pardon my question on that one, but why wasn't the concept detailed in
that
 white paper implemented into the core version of mysql or the percona
 version (and MariaDB?) ? Too many hardware dependencies ?
 Regards
 
 --
 Gaël Martinez


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

RE: console input - finding duplicate entries

2012-06-15 Thread Daevid Vincent

 -Original Message-
 From: Gary Aitken [mailto:my...@dreamchaser.org]
 Sent: Thursday, June 14, 2012 2:58 PM
 
 I can get the table loaded by specifying REPLACE INTO TABLE, but that
still
 leaves me with not knowing where the duplicate records are.

To find duplicate entries

select dupe_column, count(*) as n from mytable group by dupe_column having n
 1;

or

select n, m, count(*) as c from foo group by n, m having c  1;

Here's one solution that will find the oldest duplicate(s)

SELECT prod.prod_num,prod.prod_title,prod.updated FROM prod
JOIN (
 SELECT prod_title,max(updated) maxdate
 FROM prod GROUP BY prod_title 
 ) 
 AS proda
 ON prod.prod_title = proda.prod_title 
AND 
prod.updated != proda.maxdate

A simple and fast way is via an exclusion join

delete t1
from tbl t1
left join tbl t2 on t1.value=t2.value and t1.idt2.id
where t2.id is not null;

Deleting duplicate rows via temporary table

DROP TABLE IF EXISTS `dupes`;
CREATE TEMPORARY TABLE dupes 
  SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*)  1 ORDER BY
BID;
ALTER TABLE `dupes` ADD INDEX `LogID` ( `LogID` ); #//This index is
critical.
DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes);
DROP TABLE IF EXISTS `dupes`;


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

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

2012-05-19 Thread Daevid Vincent

There are a bunch of other columns in all these tables. A quick reason is
need the dvd.title too therefore the dvd table is needed. Another reason is
that the query is generated programmatically based upon parameters passed to
a method. But yes, I do she your point and maybe I can refactor some things
in this special case.

I haven't tried your query as I'm home and not at work right ATM, but I
think you need a DISTINCT dvd_id right? Otherwise I'll get a bunch of rows
all with the same dvd_id since multiple scene_ids will match.

d

-Original Message-
From: Mark Kelly [mailto:my...@wastedtimes.net] 
Sent: Saturday, May 19, 2012 3:34 PM
To: mysql@lists.mysql.com
Subject: Re: SQL query help. Retrieve all DVDs that have at least one scene
of a certain encoding format

Hi.

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

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

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

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

or am I misunderstanding something?

Cheers,

Mark


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


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

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

2012-05-18 Thread Daevid Vincent

I have a table of DVDs, another of scenes and a last one of encoding
formats/files...

I want to find in one query all the dvd_id that have  0 scene_id that's
encoded in format_id = 13.
In other words all DVDs that are format_id = 13 despite not having a direct
link.

CREATE TABLE `dvds` (
  `dvd_id` smallint(6) unsigned NOT NULL auto_increment,
  `dvd_title` varchar(64) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`dvd_id`),
)

CREATE TABLE `scenes_list` (
  `scene_id` int(11) NOT NULL auto_increment,
  `dvd_id` int(11) NOT NULL default '0',
  `description` text NOT NULL,
  PRIMARY KEY  (`scene_id`),
)

CREATE TABLE `moviefiles` (
  `scene_id` int(11) NOT NULL default '0',
  `format_id` int(3) NOT NULL default '0',
  `filename` varchar(255),
  `volume` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`scene_id`,`format_id`),
)



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

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

2012-05-18 Thread Daevid Vincent

 -Original Message-
 Sent: Friday, May 18, 2012 5:34 PM
 
 I have a table of DVDs, another of scenes and a last one of encoding
 formats/files...
 
 I want to find in one query all the dvd_id that have  0 scene_id that's
 encoded in format_id = 13.
 In other words all DVDs that are format_id = 13 despite not having a
direct
 link.
 
 CREATE TABLE `dvds` (
   `dvd_id` smallint(6) unsigned NOT NULL auto_increment,
   `dvd_title` varchar(64) NOT NULL default '',
   `description` text NOT NULL,
   PRIMARY KEY  (`dvd_id`),
 )
 
 CREATE TABLE `scenes_list` (
   `scene_id` int(11) NOT NULL auto_increment,
   `dvd_id` int(11) NOT NULL default '0',
   `description` text NOT NULL,
   PRIMARY KEY  (`scene_id`),
 )
 
 CREATE TABLE `moviefiles` (
   `scene_id` int(11) NOT NULL default '0',
   `format_id` int(3) NOT NULL default '0',
   `filename` varchar(255),
   `volume` smallint(6) NOT NULL default '0',
   PRIMARY KEY  (`scene_id`,`format_id`),
 )

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

SELECT DISTINCT 
d.`dvd_id` AS `id`,
(SELECT 
COUNT(s_sl.scene_id) AS s_tally 
FROM
scenes_list AS s_sl 
JOIN moviefiles AS s_mf USING (scene_id) 
WHERE s_sl.dvd_id = d.`dvd_id` 
AND s_mf.format_id = 13) AS s_tally 
FROM
`dvds` AS d 
WHEREd.`date_release` = '2012-05-18' 
HAVING s_tally  0 
ORDER BY d.`date_release` DESC;


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

RE: Database Workbench 4.2.3, the multi-DBMS IDE now available!

2012-03-23 Thread Daevid Vincent

Any plans to add sqlite to your list of supported DBs there?

I develop Android and use LAMP as the server backend. Currently I use SQLYog
as I have for like 10 years. But I would really like one GUI to work on both
the android sqlite and the mysql backend since they usually tie together.

 -Original Message-
 From: Martijn Tonies [mailto:m.ton...@upscene.com]
 Sent: Friday, March 23, 2012 2:10 AM
 To: mysql@lists.mysql.com; firebird-to...@yahoogroups.com
 Subject: ANN: Database Workbench 4.2.3, the multi-DBMS IDE now available!
 
 ANN: Database Workbench 4.2.3, the multi-DBMS IDE now available!
 
 Ladies, gentlemen,
 
 Upscene Productions is proud to announce the next version
 of the popular Windows-based multi-DBMS development tool:
 
  Database Workbench 4.2.3 Pro 
 
 
 For more information, see http://www.upscene.com/go/?go=newsid=20120323
 
 Today's release includes the FREE Lite Editions.
 
 For a full list of fixes in this release, see:
 http://www.upscene.com/go/?go=trackerv=4.2.3id=3
 http://www.upscene.com/go/?go=trackerv=4.2.2id=3
 http://www.upscene.com/go/?go=trackerv=4.2.1id=3
 http://www.upscene.com/go/?go=trackerv=4.2.0id=3
 
 
 
 Database Workbench supports:
 - Borland InterBase ( 6.x - XE )
 - Firebird ( 1.x, 2.x )
 - MS SQL Server/MSDE ( 7, 2000, 2005, 2008 )
 - MySQL 4.x, 5.x
 - Oracle Database ( 8i, 9i, 10g, 11g )
 - Sybase SQL Anywhere ( 9, 10, 11 and 12 )
 - NexusDB ( 3.0 and up )
 
 
 
 
 
 
 Thank you for your support,
 
 Martijn Tonies
 Database Workbench - the database developer tool for professionals
 Upscene Productions
 http://www.upscene.com
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


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

USING WHERE; USING TEMPORARY; USING filesort

2012-02-23 Thread Daevid Vincent

Anyone have any thoughts on how I might optimize this query? It takes about
2 seconds. I know that seems quick, but we get nearly 30,000 hits per second
and so if we can shave this down, it helps. Also we do use memcache, but
even with that, we still see this in the slow-log sometimes. I have indexes
on everything used in this query and even a compound one as you see in the
EXPLAIN.

I'm not going to lose sleep over it, but I thought if there was something
simple or a way to refactor I'd give it a shot. I thought changing the query
to use JOIN ON syntax would have helped, but it didn't do anything really?!
Also, this is all being used with PHP, so I'm fine with pulling things out
into two or three queries if you suggest it will make a faster difference.

-- old query:

SELECT sg.`scene_id`, COUNT(*) AS num FROM `scenes_list` AS s, `dvds` AS d,
`scenes_genres` AS sg, `scenes_downloads_new` AS sd WHERE sg.`genre_id` IN
('1','8','10','19','38','58','65') AND d.`dvd_id`=s.`dvd_id` AND
sg.`scene_id`=s.`scene_id` AND d.`status`='ok' AND
d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP BY
sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30;

-- refactored: is this correct order?? Smallest table and most filters first
right to narrow the dataset as quick as possible?

-- EXPLAIN
SELECT 
s.`scene_id`,
COUNT(*) AS num 
FROM`dvds` AS d
JOIN `scenes_list` AS s ON  d.`dvd_id` = s.`dvd_id`
AND d.`date_release` != '-00-00' 
AND d.`status` = 'ok' 
JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id`
AND sg.`genre_id` IN ('1', '8', '10', '19', '38', '58',
'65') 
JOIN `scenes_downloads_new` AS sd ON sd.`scene_id` = s.`scene_id`
GROUP BY sg.`scene_id` 
ORDER BY num DESC, sd.`count_dl_monthly` DESC 
LIMIT 30
;

SELECT COUNT(*) FROM dvds; -- 12181
SELECT COUNT(*) FROM scenes_downloads_new; -- 66054
SELECT COUNT(*) FROM scenes_list; -- 67197
SELECT COUNT(*) FROM scenes_genres; -- 344272

-- why do I still hit the filesort and temp table? how can I get rid of
that?

id  select_type  TABLE   TYPEpossible_keys
KEY key_len  ref  ROWS  Extra

--  ---  --  --
--  --
---  -  --
--
 1  SIMPLE   d   ref
PRIMARY,date_release,STATUS,status_release,dvd_release  status_release  1
const2436  USING WHERE; USING TEMPORARY; USING
filesort  
 1  SIMPLE   s   ref PRIMARY,dvd_id_2,dvd_id
dvd_id  4.d.dvd_id 6  USING WHERE

 1  SIMPLE   sd  eq_ref  PRIMARY
PRIMARY 3.s.scene_id   1  USING WHERE

 1  SIMPLE   sg  ref PRIMARY,scene_id,genre_id
scene_id4.s.scene_id   5  USING WHERE



If I take off the ORDER BY (just to see what happens), basically it's
exactly the same EXPLAIN output (wrong results of course), but it does add
Using index to the scene_id row (weird).

If I take off the GROUP BY (just to see what happens), basically it's
exactly the same EXPLAIN output (wrong results of course), but it does
remove Using Filesort.

Taking them both off is optimal I suspect, and again, barring the fact the
results are wrong, it takes 1 second for the query. Should I read that to
mean, it is what it is and that's the best I can expect from that
multi-join query? Is there any benefit to splitting this up and if so, how
should I split it?

d


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

RE: How often should we upgrade MySQL version

2011-11-18 Thread Daevid Vincent

Ever heard the old saying, If it ain't broke, don't fix it. ;-)

I'd say that as a general rule:

1. if you aren't experiencing problems then don't upgrade.
2. if you aren't subject to any vulnerabilities that may be found, then
don't upgrade
3. if you don't need a new feature introduced, then don't upgrade
4. if you need to be up 100%, then don't upgrade

Having said that, it is possible to upgrade with minimal downtime if you're
smart about it.

Get another server (or two or three) that is a clone of the existing ones.
Upgrade those. Test those. Swap over.

Then for the next release, do the same thing with the servers you now have
as the spares.

If you're in such a mission critical situation, you should have spare
servers and live hot-swapable backups anyways right.


-Original Message-
From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] 
Sent: Friday, November 18, 2011 11:54 AM
To: MySQL ML
Subject: How often should we upgrade MySQL version

We are running MySQL 5.1.46 with master to master replication with 3 other
servers for 3 different websites in 3 different parts of the world.

My question is how often should we be looking to upgrade our MySQL version
considering we can't really afford any downtime. 

Thanks
Neil
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com


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

Within-group aggregate query help please - customers and latest subscription row

2011-10-24 Thread Daevid Vincent

I know this is a common problem, and I've been struggling with it for a full
day now but I can't get it. 

I also tried a few sites for examples:
http://www.artfulsoftware.com/infotree/queries.php#101
http://forums.devarticles.com/general-sql-development-47/select-max-datetime
-problem-10210.html

Anyways, pretty standard situation:

CREATE TABLE `customers` (
  `customer_id` int(10) unsigned NOT NULL auto_increment,
  `email` varchar(64) NOT NULL default '',
  `name` varchar(128) NOT NULL default '',
  `username` varchar(32) NOT NULL,
...
);

CREATE TABLE `customers_subscriptions` (
  `subscription_id` bigint(12) unsigned NOT NULL default '0',
  `customer_id` int(10) unsigned NOT NULL default '0',
  `date` date NOT NULL default '-00-00',
  ...
);

I want to show a table where I list out the ID, email, username, and LAST
SUBSCRIPTION.

I need this data in TWO ways:

The FIRST way, is with a query JOINing the two tables so that I can easily
display that HTML table mentioned. That is ALL customers and the latest
subscription they have.

The SECOND way is when I drill into the customer, I already know the
customer_id and so don't need to JOIN with that table, I just want to get
the proper row from the customers_subscriptions table itself.

SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY
`date` DESC;

subscription_id  processor  customer_id  date 
---  -  ---  --  
  134126370  chargem  7  2005-08-04  
 1035167192  billme   7  2004-02-08  

SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7
GROUP BY customer_id;

gives me 2005-08-04 obviously, but as you all know, mySQL completely takes a
crap on your face when you try what would seem to be the right query:

SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE
customer_id = 7 GROUP BY customer_id;

subscription_id  MAX(`date`)
---  ---
 1035167192  2005-08-04 
 
Notice how I have the correct DATE, but the wrong subscription_id.

In the example web sites above, they seem to deal more with finding the
MAX(subscription_id), which in my case will not work.

I need the max DATE and the corresponding row (with matching
subscription_id).

Thanks,

d


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

RE: Within-group aggregate query help please - customers and latest subscription row

2011-10-24 Thread Daevid Vincent

A kind (and shy) soul replied to me off list and suggested this solution,
however,
this takes 28 seconds (that's for a single customer_id, so this is not going
to scale). 
Got any other suggestions? :-)

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
GROUP BY customer_id) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate 
WHERE c.customer_id = 7;

There are 781,270 customers (nearly 1 million) and  1,018,092
customer_subscriptions.

Our tables have many indexes on pretty much every column and for sure the
ones we use here.

EXPLAIN says:

id  select_type  table   typepossible_keys key
key_len  refrows  Extra  
--  ---  --  --    ---
---  --  ---  ---
 1  PRIMARY  c   const   PRIMARY   PRIMARY  4
const 1 
 1  PRIMARY  s   ref date,customer_id  customer_id  4
const 2 
 1  PRIMARY  derived2  ALL (NULL)(NULL)
(NULL)   (NULL)   781265  Using where
 2  DERIVED  cs  ALL (NULL)(NULL)
(NULL)   (NULL)  1018092  Using temporary; Using filesort

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Monday, October 24, 2011 1:46 PM
 To: mysql@lists.mysql.com
 Subject: Within-group aggregate query help please - customers and latest
 subscription row
 
 I know this is a common problem, and I've been struggling with it for a
full
 day now but I can't get it.
 
 I also tried a few sites for examples:
 http://www.artfulsoftware.com/infotree/queries.php#101

http://forums.devarticles.com/general-sql-development-47/select-max-datetime
 -problem-10210.html
 
 Anyways, pretty standard situation:
 
 CREATE TABLE `customers` (
   `customer_id` int(10) unsigned NOT NULL auto_increment,
   `email` varchar(64) NOT NULL default '',
   `name` varchar(128) NOT NULL default '',
   `username` varchar(32) NOT NULL,
   ...
 );
 
 CREATE TABLE `customers_subscriptions` (
   `subscription_id` bigint(12) unsigned NOT NULL default '0',
   `customer_id` int(10) unsigned NOT NULL default '0',
   `date` date NOT NULL default '-00-00',
   ...
 );
 
 I want to show a table where I list out the ID, email, username, and LAST
 SUBSCRIPTION.
 
 I need this data in TWO ways:
 
 The FIRST way, is with a query JOINing the two tables so that I can easily
 display that HTML table mentioned. That is ALL customers and the latest
 subscription they have.
 
 The SECOND way is when I drill into the customer, I already know the
 customer_id and so don't need to JOIN with that table, I just want to get
 the proper row from the customers_subscriptions table itself.
 
 SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY
 `date` DESC;
 
 subscription_id  processor  customer_id  date
 ---  -  ---  --
   134126370  chargem  7  2005-08-04
  1035167192  billme   7  2004-02-08
 
 SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7
 GROUP BY customer_id;
 
 gives me 2005-08-04 obviously, but as you all know, mySQL completely takes
a
 crap on your face when you try what would seem to be the right query:
 
 SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE
 customer_id = 7 GROUP BY customer_id;
 
 subscription_id  MAX(`date`)
 ---  ---
  1035167192  2005-08-04
 
 Notice how I have the correct DATE, but the wrong subscription_id.
 
 In the example web sites above, they seem to deal more with finding the
 MAX(subscription_id), which in my case will not work.
 
 I need the max DATE and the corresponding row (with matching
 subscription_id).
 
 Thanks,
 
 d


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

RE: Within-group aggregate query help please - customers and latest subscription row

2011-10-24 Thread Daevid Vincent

Okay, it seems I am learning... slowly...

So there needs to be a second WHERE in the sub-select...

To get ONE customer's last subscription (0.038s):

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
  WHERE cs.customer_id = 7
GROUP BY customer_id
) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate
WHERE c.customer_id = 7;

To get ALL customers and their last subscription row (1m:28s)

SELECT 
c.customer_id,
c.email,
c.name,
c.username,
s.subscription_id,
s.`date`
FROM
customers AS c 
INNER JOIN customers_subscriptions AS s 
ON c.customer_id = s.customer_id 
INNER JOIN 
(SELECT 
MAX(`date`) AS LastDate,
customer_id 
FROM
customers_subscriptions AS cs 
  GROUP BY customer_id
) AS `x`
ON s.customer_id = x.customer_id 
AND s.date = x.LastDate
ORDER BY customer_id LIMIT 10;

Thanks to you know who you are for pointing me in the right direction. 

Hopefully this helps someone else.

d.

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Monday, October 24, 2011 4:06 PM
 To: mysql@lists.mysql.com
 Subject: RE: Within-group aggregate query help please - customers and
latest
 subscription row
 
 A kind (and shy) soul replied to me off list and suggested this solution,
 however,
 this takes 28 seconds (that's for a single customer_id, so this is not
going
 to scale).
 Got any other suggestions? :-)
 
 SELECT
 c.customer_id,
 c.email,
 c.name,
 c.username,
 s.subscription_id,
 s.`date`
 FROM
 customers AS c
 INNER JOIN customers_subscriptions AS s
 ON c.customer_id = s.customer_id
 INNER JOIN
 (SELECT
 MAX(`date`) AS LastDate,
 customer_id
 FROM
 customers_subscriptions AS cs
 GROUP BY customer_id) AS `x`
 ON s.customer_id = x.customer_id
 AND s.date = x.LastDate
 WHERE c.customer_id = 7;
 
 There are 781,270 customers (nearly 1 million) and  1,018,092
 customer_subscriptions.
 
 Our tables have many indexes on pretty much every column and for sure the
 ones we use here.
 
 EXPLAIN says:
 
 id  select_type  table   typepossible_keys key
 key_len  refrows  Extra
 --  ---  --  --    ---
 ---  --  ---  ---
  1  PRIMARY  c   const   PRIMARY   PRIMARY  4
 const 1
  1  PRIMARY  s   ref date,customer_id  customer_id  4
 const 2
  1  PRIMARY  derived2  ALL (NULL)(NULL)
 (NULL)   (NULL)   781265  Using where
  2  DERIVED  cs  ALL (NULL)(NULL)
 (NULL)   (NULL)  1018092  Using temporary; Using filesort
 
  -Original Message-
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Monday, October 24, 2011 1:46 PM
  To: mysql@lists.mysql.com
  Subject: Within-group aggregate query help please - customers and latest
  subscription row
 
  I know this is a common problem, and I've been struggling with it for a
 full
  day now but I can't get it.
 
  I also tried a few sites for examples:
  http://www.artfulsoftware.com/infotree/queries.php#101
 

http://forums.devarticles.com/general-sql-development-47/select-max-datetime
  -problem-10210.html
 
  Anyways, pretty standard situation:
 
  CREATE TABLE `customers` (
`customer_id` int(10) unsigned NOT NULL auto_increment,
`email` varchar(64) NOT NULL default '',
`name` varchar(128) NOT NULL default '',
`username` varchar(32) NOT NULL,
  ...
  );
 
  CREATE TABLE `customers_subscriptions` (
`subscription_id` bigint(12) unsigned NOT NULL default '0',
`customer_id` int(10) unsigned NOT NULL default '0',
`date` date NOT NULL default '-00-00',
...
  );
 
  I want to show a table where I list out the ID, email, username, and
LAST
  SUBSCRIPTION.
 
  I need this data in TWO ways:
 
  The FIRST way, is with a query JOINing the two tables so that I can
easily
  display that HTML table mentioned. That is ALL customers and the latest
  subscription they have.
 
  The SECOND way

RE: Vista crashes

2011-10-12 Thread Daevid Vincent

 -Original Message-
 From: Halász Sándor [mailto:h...@tbbs.net]
 Sent: Tuesday, October 11, 2011 9:28 PM
 To: mysql@lists.mysql.com
 Subject: Vista crashes

 I find that, when under Vista the MySQL daemon has been shut down 

There's your first two problems:

[a] why are you running Vista. Period.
[b] why are you running MySQL under windows and not a Unix flavor as the
rest of the world does?

;-)

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

Episode 1 - Mongo DB Is Web Scale

2011-09-16 Thread Daevid Vincent

http://www.youtube.com/watch?v=b2F-DItXtZs
 
LOLercopter.

RE: Database Workbench 4.1.3, the multi-DBMS IDE now available!

2011-08-16 Thread Daevid Vincent

No SQLite support?
http://www.sqlite.org/

Seems curious you wouldn't have this yet, especially given its popularity on
both embedded systems and Android to say the least.

 -Original Message-
 From: Martijn Tonies [mailto:m.ton...@upscene.com]
 Sent: Tuesday, August 16, 2011 6:56 AM
 To: mysql@lists.mysql.com
 Cc: firebird-to...@yahoogroups.com
 Subject: ANN: Database Workbench 4.1.3, the multi-DBMS IDE now available!
 
 ANN: Database Workbench 4.1.3, the multi-DBMS IDE now available!
 
 Ladies, gentlemen,
 
 Upscene Productions is proud to announce the next
 version of the popular multi-DBMS development tool:
 
  Database Workbench 4.1.3 Pro 
 
 This release includes the FREE Lite versions for InterBase, Firebird
 and MySQL.
 
 
 For more information, see http://www.upscene.com/go/?go=newsid=20110816
 
 For a full list of fixes in this release, see:
 http://www.upscene.com/go/?go=trackerv=4.1.3id=3
 
 
 
 Database Workbench supports:
 - Borland InterBase ( 4.x - XE )
 - Firebird ( 1.x, 2.x )
 - MS SQL Server/MSDE ( 7, 2000, 2005, 2008, MSDE 1  2, SQL Express )
 - MySQL 4.x, 5.x
 - Oracle Database ( 8i, 9i, 10g, 11g )
 - Sybase SQL Anywhere ( 9, 10, 11 and 12 )
 - NexusDB ( 3.0 and up )



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

NoSQL to MySQL with Memcached

2011-07-27 Thread Daevid Vincent

http://dev.mysql.com/tech-resources/articles/nosql-to-mysql-with-memcached.h
tml

RE: Next Unique Number - Generation

2011-07-21 Thread Daevid Vincent

I can think of several ways to accomplish this (or close to it).

 * Assign each server a number and prefix/append that number to the unique
ID.
 * initialize each table on each server at a different huge number so they
don't ever collide:
ALTER TABLE `students` AUTO_INCREMENT=1000;
Other server:
ALTER TABLE `students` AUTO_INCREMENT=2000;
 * don't re-invent the wheel and just use UUID()
 * make the primary key a combination key of a regular auto-increment and
NOW() or RAND() or some other unique column of the table itself or something
like IP number of the server, etc.
 * setup a trigger to alter the primary key accordingly prior to insert.
Maybe one server is even numbers and the other is odd numbers, or use MOD()
or other math to have a pool of servers.
http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with
-auto-increment


 -Original Message-
 From: Vikram A [mailto:vikkiatb...@yahoo.in]
 Sent: Wednesday, July 20, 2011 11:18 PM
 To: MY SQL Mailing list
 Subject: Next Unique Number - Generation
 
 Hi there,
 
 I need a technical help fro you,
 I have developed a software for college  school. Here we have concept
 called register number/admission number. These are two unique umber for
each
 student. My application resides Client/server model.
 These numbers will be generated (some defined format) to each student when
 they are admitted at first time. This admission process is taken place at
 different nodes at a time.
 In this scenario, I am facing difficulty that, the same number is
generated
 at time in two machines. (Logic is newest number will be displayed in the
 node before admission).
 
 How can I stop this logical issue?
 Can i have solution for this?
 
 Thank you in advance.


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

Facebook Trapped In MySQL a 'Fate Worse Than Death'

2011-07-11 Thread Daevid Vincent

http://developers.slashdot.org/story/11/07/09/1256241/Facebook-Trapped-In-My
SQL-a-Fate-Worse-Than-Death
 
According to database pioneer Michael Stonebraker, Facebook is operating a
huge, complex MySQL implementation equivalent to 'a fate worse than death,'
and the only way out is 'bite the bullet and rewrite everything
http://gigaom.com/cloud/facebook-trapped-in-mysql-fate-worse-than-death/
.' Not that it's necessarily Facebook's fault, though. Stonebraker says the
social network's predicament is all too common among web startups that start
small and grow to epic proportions.

Using where; Using temporary; Using filesort

2011-05-31 Thread Daevid Vincent

I'm trying to optimize a query that doesn't seem all that complicated,
however I can't seem to get it to not use a temp table and filesort.

developer@vm_vz_daevid:~$ mysql --version
mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using  5.2


EXPLAIN EXTENDED
SELECT 
-- d.date_release,
-- d.dvd_title,
-- s.type,
-- s.id_place,
s.scene_id AS index_id,
s.dvd_id 
FROM
dvds AS d JOIN scenes_list AS s 
ON s.dvd_id = d.dvd_id 
AND d.status = 'ok' 
AND d.date_release != '-00-00' 
ORDER BY d.date_release DESC,
d.dvd_title ASC,
s.type ASC,
s.id_place ASC;


*** 1. row ***
   id: 1
  select_type: SIMPLE
table: d
 type: ref
possible_keys: PRIMARY,date_release,status,status_release
  key: status_release
  key_len: 1
  ref: const
 rows: 1976
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
   id: 1
  select_type: SIMPLE
table: s
 type: ref
possible_keys: dvd_id_2,dvd_id
  key: dvd_id
  key_len: 4
  ref: videoszcontent.d.dvd_id
 rows: 6
Extra: Using where
2 rows in set, 1 warning (0.00 sec)

There are proper indexes on most every column in both tables (as you can see
there).

[a] the EXTENDED keyword doesn't seem to do anything different? I get the
same columns and results??!

[b] The commented out columns above I thought might help with the ORDER BY
for some reason from my reading here:
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
they did not.

[c] lopping off the ORDER BY all together stops the Using temporary; Using
filesort of course. Yeah! But now I'm left with a table of data in random
order. Re-sorting it in PHP seems like an even bigger waste of cycles, when
no doubt MySQL can sort hella-faster.

[d] just doing  ORDER BY d.date_release DESC, d.dvd_title ASC; , prevents
the using temporary but still does filesort and again I'm in the boat of
[c]

I guess my question is this: Is it ALWAYS possible to fabricate a
query/schema in such a way that MySQL ALWAYS uses the ideal 'Using where'
extra -- you just have to keep at it? Or is it the case that sometimes
you're just S.O.L. and no matter what, MySQL is going to give you a
Cleveland Steamer? In other words, am I wasting my time trying to tweak my
query and indexes here with the idea there's some magic incantation that
will get this right or do I just have to accept it is what it is and it's
not going to do any better.

d.


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

Using where; Using temporary; Using filesort

2011-05-31 Thread Daevid Vincent

I sent this Friday, but it never made it to the list?!

-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com] 
Sent: Friday, May 27, 2011 12:27 PM
To: mysql@lists.mysql.com
Subject: Using where; Using temporary; Using filesort

I'm trying to optimize a query that doesn't seem all that complicated,
however I can't seem to get it to not use a temp table and filesort.

developer@vm_vz_daevid:~$ mysql --version
mysql  Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using  5.2

EXPLAIN EXTENDED
SELECT 
-- d.date_release,
-- d.dvd_title,
-- s.type,
-- s.id_place,
s.scene_id AS index_id,
s.dvd_id 
FROM
dvds AS d JOIN scenes_list AS s 
ON s.dvd_id = d.dvd_id 
AND d.status = 'ok' 
AND d.date_release != '-00-00' 
ORDER BY d.date_release DESC,
d.dvd_title ASC,
s.type ASC,
s.id_place ASC;

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: d
 type: ref
possible_keys: PRIMARY,date_release,status,status_release
  key: status_release
  key_len: 1
  ref: const
 rows: 1976
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
   id: 1
  select_type: SIMPLE
table: s
 type: ref
possible_keys: dvd_id_2,dvd_id
  key: dvd_id
  key_len: 4
  ref: videoszcontent.d.dvd_id
 rows: 6
Extra: Using where
2 rows in set, 1 warning (0.00 sec)

There are proper indexes on most every column in both tables (as you can see
there).

[a] the EXTENDED keyword doesn't seem to do anything different? I get the
same columns and results??!

[b] The commented out columns above I thought might help with the ORDER BY
for some reason from my reading here:
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
they did not.

[c] lopping off the ORDER BY all together stops the Using temporary; Using
filesort of course. Yeah! But now I'm left with a table of data in random
order. Re-sorting it in PHP seems like an even bigger waste of cycles, when
no doubt MySQL can sort hella-faster.

[d] just doing  ORDER BY d.date_release DESC, d.dvd_title ASC; , prevents
the using temporary but still does filesort and again I'm in the boat of
[c]

I guess my question is this: Is it ALWAYS possible to fabricate a
query/schema in such a way that MySQL ALWAYS uses the ideal 'Using where'
extra -- you just have to keep at it? Or is it the case that sometimes
you're just S.O.L. and no matter what, MySQL is going to give you a
Cleveland Steamer? In other words, am I wasting my time trying to tweak my
query and indexes here with the idea there's some magic incantation that
will get this right or do I just have to accept it is what it is and it's
not going to do any better.

d.

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

RE: Renaming a database?

2011-05-24 Thread Daevid Vincent

 -Original Message-
 On Tue, May 24, 2011 at 4:19 PM, Jerry Schwartz je...@gii.co.jp wrote:
 
  It looks like there’s no way to rename a database. Is that true?

I know. Retarded right? Version 5.1.x and still what would seem the most basic 
of tasks is still impossible. 

Maybe Oracle can fix what MontyAB and Sun couldn't... that feature alone would 
be well worth the $5.6 Billion dollars spent.

I just had a need to do this last week and was astonished to find it's still 
not implemented after all these... well ever! I unfortunately had to dump the 
database to a .sql file, edit said file, re-dump in, then dump in the new 
database, then run a SQL diff tool to find out what schemata and data was 
different. The whole process delayed me a few hours that I didn't budget for. 
Talk about a hindrance. 

-

And before anyone gets all upset with my use of the word Retarded...
Read this: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html

And then the definition:

re•tard•ed

–verb (used with object)
1. to make slow; delay the development or progress of (an action, process, 
etc.); hinder or impede.

–verb (used without object)
2. to be delayed.


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

FW: MySQL shell...with parameters

2011-05-23 Thread Daevid Vincent

Here’s an email from a good friend of mine that may be of interest. It is done 
in Ruby.

From: Brad Robel-Forrest [mailto:b...@gigglewax.com] 
Sent: Sunday, May 15, 2011 7:01 PM
To: Daevid Vincent
Subject: MySQL shell...with parameters

Remember a long while back I was bitching about the need for parameterized 
MySQL when working at the prompt? Well, it took me down a road and I've 
fulfilled that need and many more. If you're interested, I've opened a project 
on a shell that allows you to write all manner of SQL queries with variable 
interpolation and even generated query for programmatic searching through your 
database. Plus, it has the ability to do all of this remotely over an SSH 
connection without needing to have a mysql binary installed either locally or 
remotely.

Main project site: https://github.com/bradrf/rsql
Installing on Windows: https://github.com/bradrf/rsql/wiki/Install-on-Windows
Tutorial: https://github.com/bradrf/rsql/raw/master/example.rsqlrc
I've been using it a ton for analyzing our database at work for the past 
several months. It's a rare day that goes by where I don't fire this up.

-brad

How do I get more pages in mySQL Workbench?? WAS: Any table visualization tools with wires connecting the actual columns?

2011-05-03 Thread Daevid Vincent

I just discovered that MySQL Workbench actually WILL connect columns. Use
the #6 wire type shortcut (bottom one).
 
However, I must be retarded or blind because I can't figure out how to make
the diagram have more than the default of 2 pages. I have a database with 50
tables and they just don't fit. In fact I only noticed this after printing
out the page that many were missing. Turns out you can't scroll down to see
them, you can only zoom way out and see that tables are off the white page.
:-\
 
Oddly though, I imported another database with 88 tables and it
automatically made 4 pages for me.
 
Anyone know how to get more pages added?
 
From: Daevid Vincent [mailto:dae...@daevid.com] 
Sent: Friday, April 01, 2011 4:27 PM
To: mysql@lists.mysql.com
Subject: Any table visualization tools with wires connecting the actual
columns?
 
I am evaluating various tools for diagram generating of existing databases
on some smaller databases (9 tables or so) first.

The two I've tried so far are these:

http://dev.mysql.com/downloads/workbench/
http://www.sqlmaestro.com/download/#mysql

Both _seem_ robust and cosmetically polished, but feel to me lacking the
most obvious and key component of the whole purpose to make an EER diagram.

I don't understand in workbench, why it creates new keys for me on existing
tables. Maestro doesn't do this nonsense. It isn't the tools business where
I have keys, it only needs to be concerned with what links to what -- that I
tell it to. It's further exacerbated by the fact that the documentation
indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add
confusion guys?

1. Neither one seem to be smart enough to automatically know that columns of
the same name should be linked, and furthermore they should be linked from
all tables to the one where that column name is the PK. my tables don't have
true InnoDB FKs setup. And some tables are MYISAM (as they're significantly
faster). But I do use keys and I do have sane naming conventions, so I don't
understand why they can't use the names, and if there are multiple tables
(for some unlikely reason) then just prompt me which table to use.

Which leads me to the second and third problems...

So I manually have started to draw the connections, but:

2. How can I make the wires stick to a column on the left or right edge, so
that I can have a direct visual link between the columns. Right now, it
seems they float around the edge of the table box. That's sort of useless
isn't it? it's like saying, well, something in this table points to
something in that table.?! I would think that two programs with such high
version numbers would have this feature. Maybe I'm missing a configuration
or some way I'm supposed to do it?

3. Some of my databases point to tables in other databases on the same
server. It would be useful if I could make a wire that indicates this.

Are there other (better) options out there for this? I really don't want to
do this in Visio or make a printout of the table boxes and tape string to my
walls to visualize all the databases, tables and columns.

-Daevid.

RE: WHERE does not work on calculated view field

2011-04-22 Thread Daevid Vincent



 -Original Message-
 From: Daniel Kraft [mailto:d...@domob.eu]
 Sent: Friday, April 22, 2011 12:37 PM
 To: mysql@lists.mysql.com
 Subject: WHERE does not work on calculated view field
 
 Hi all,
 
 I'm by no means a (My)SQL expert and just getting started working with
 VIEWs and stored procedures, and now I'm puzzled by this behaviour:
 
 DROP DATABASE `test`;
 CREATE DATABASE `test`;
 USE `test`;
 
 CREATE TABLE `mytable`
(`ID` SERIAL,
 `Type` INTEGER UNSIGNED NULL,
 PRIMARY KEY (`ID`));
 INSERT INTO `mytable` (`Type`) VALUES (NULL);
 
 CREATE TABLE `types`
(`ID` SERIAL,
 `Name` TEXT NOT NULL,
 PRIMARY KEY (`ID`));
 INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
 
 DELIMITER |
 CREATE FUNCTION `EMPTY_STRING` (value TEXT)
 RETURNS TEXT
 DETERMINISTIC
 BEGIN
RETURN IF(value IS NULL, '', value);
 END|
 DELIMITER ;
 
 CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
  FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
 
 SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
 SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
 SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
 
 (I tried to simplify my problem as far as possible.)  When I run this
 against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
 at the end -- shouldn't at least one of them match the single row?
 (Preferably first and third ones.)
 
 What am I doing wrong here?  I have no clue what's going on...  Thanks a
 lot!

Try this maybe:

 SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL;
 SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL;
 SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%';


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

2011-04-08 Thread 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. :(

Thanks though.

 -Original Message-
 From: mos [mailto:mo...@fastmail.fm]
 Sent: Thursday, April 07, 2011 8:50 PM
 To: mysql@lists.mysql.com
 Subject: RE: Any table visualization tools with wires connecting the
actual
 columns?

 At 05:36 PM 4/7/2011, Daevid Vincent wrote:
 I am a paid subscriber to SQLYog -- I love that tool, but AFAIK it
 doesn't do diagrams (with wires between tables and all that glory). Am
 I wrong? Is that feature there and I just never noticed it?

 David,
   SqlYog Ultimate has a schema designer. Take a look at the screen
shots.
 http://www.webyog.com/en/screenshots.php

 Mike

   -Original Message-
   From: mos [mailto:mo...@fastmail.fm]
   Sent: Thursday, April 07, 2011 1:36 PM
   To: mysql@lists.mysql.com
   Subject: Re: Any table visualization tools with wires connecting the
 actual
   columns?

   At 02:17 PM 4/7/2011, Daevid Vincent wrote:
   Does anyone have any suggestions on this? I've written to SQL
   Maestro twice and they've not replied either.

   Take a look at SqlYog from www.webyog.com. I use their community
   version but their paid version has a schema designer. They are
   responsive to
 emails
   and forum posts.

   Mike

   From: Daevid Vincent [mailto:dae...@daevid.com]
   Sent: Friday, April 01, 2011 4:27 PM
   To: mysql@lists.mysql.com
   Subject: Any table visualization tools with wires connecting the
   actual columns?

   I am evaluating various tools for diagram generating of existing
   databases on some smaller databases (9 tables or so) first.

   The two I've tried so far are these:

   http://dev.mysql.com/downloads/workbench/
   http://www.sqlmaestro.com/download/#mysql

   Both _seem_ robust and cosmetically polished, but feel to me
   lacking the most obvious and key component of the whole purpose to
   make an EER
   diagram.

   I don't understand in workbench, why it creates new keys for me on
   existing tables. Maestro doesn't do this nonsense. It isn't the
   tools business where I have keys, it only needs to be concerned
   with what links to what -- that I tell it to. It's further
   exacerbated by the fact that the documentation indicates these
   aren't even REAL keys, they are cosmetic only! WTF? Why add confusion
 guys?

   1. Neither one seem to be smart enough to automatically know that
   columns of the same name should be linked, and furthermore they
   should be linked from all tables to the one where that column name is
 the PK.
   my tables don't have true InnoDB FKs setup. And some tables are
   MYISAM (as they're significantly faster). But I do use keys and I
   do have sane naming conventions, so I don't understand why they
   can't use the names, and if there are multiple tables (for some
   unlikely reason) then just
 prompt
   me which table to use.

   Which leads me to the second and third problems...

   So I manually have started to draw the connections, but:

   2. How can I make the wires stick to a column on the left or right
   edge, so that I can have a direct visual link between the columns.
   Right now, it seems they float around the edge of the table box.
   That's sort of useless isn't it? it's like saying, well, something
   in this table points to something in that table.?! I would think
   that two programs with such high version numbers would have this
   feature. Maybe I'm missing a configuration or some way I'm supposed
 to do it?

   3. Some of my databases point to tables in other databases on the
   same server. It would be useful if I could make a wire that indicates
this.

   Are there other (better) options out there for this? I really don't
   want to do this in Visio or make a printout of the table boxes and
   tape string to my walls to visualize all the databases, tables and
columns.

   -Daevid.

  _

   No virus found in this message.
   Checked by AVG - www.avg.com
   Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date:
   04/01/11

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

  _

   No virus found in this message.
   Checked by AVG - www.avg.com
   Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date:
   04/01/11

  _

   No virus found in this message.
   Checked by AVG - www.avg.com
   Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date:
   04/07/11

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

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

Any table visualization tools with wires connecting the actual columns?

2011-04-07 Thread Daevid Vincent

Does anyone have any suggestions on this? I've written to SQL Maestro twice
and they've not replied either.

From: Daevid Vincent [mailto:dae...@daevid.com] 
Sent: Friday, April 01, 2011 4:27 PM
To: mysql@lists.mysql.com
Subject: Any table visualization tools with wires connecting the actual
columns?

I am evaluating various tools for diagram generating of existing databases
on some smaller databases (9 tables or so) first.

The two I've tried so far are these:

http://dev.mysql.com/downloads/workbench/
http://www.sqlmaestro.com/download/#mysql

Both _seem_ robust and cosmetically polished, but feel to me lacking the
most obvious and key component of the whole purpose to make an EER diagram.

I don't understand in workbench, why it creates new keys for me on existing
tables. Maestro doesn't do this nonsense. It isn't the tools business where
I have keys, it only needs to be concerned with what links to what -- that I
tell it to. It's further exacerbated by the fact that the documentation
indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add
confusion guys?

1. Neither one seem to be smart enough to automatically know that columns of
the same name should be linked, and furthermore they should be linked from
all tables to the one where that column name is the PK. my tables don't have
true InnoDB FKs setup. And some tables are MYISAM (as they're significantly
faster). But I do use keys and I do have sane naming conventions, so I don't
understand why they can't use the names, and if there are multiple tables
(for some unlikely reason) then just prompt me which table to use.

Which leads me to the second and third problems...

So I manually have started to draw the connections, but:

2. How can I make the wires stick to a column on the left or right edge, so
that I can have a direct visual link between the columns. Right now, it
seems they float around the edge of the table box. That's sort of useless
isn't it? it's like saying, well, something in this table points to
something in that table.?! I would think that two programs with such high
version numbers would have this feature. Maybe I'm missing a configuration
or some way I'm supposed to do it?

3. Some of my databases point to tables in other databases on the same
server. It would be useful if I could make a wire that indicates this.

Are there other (better) options out there for this? I really don't want to
do this in Visio or make a printout of the table boxes and tape string to my
walls to visualize all the databases, tables and columns.

-Daevid.

  _ 

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11

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

  _  

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11

  _  

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11

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

2011-04-07 Thread Daevid Vincent

I am a paid subscriber to SQLYog -- I love that tool, but AFAIK it doesn't
do diagrams (with wires between tables and all that glory). Am I wrong? Is
that feature there and I just never noticed it?

 -Original Message-
 From: mos [mailto:mo...@fastmail.fm]
 Sent: Thursday, April 07, 2011 1:36 PM
 To: mysql@lists.mysql.com
 Subject: Re: Any table visualization tools with wires connecting the
actual
 columns?

 At 02:17 PM 4/7/2011, Daevid Vincent wrote:
 Does anyone have any suggestions on this? I've written to SQL Maestro
 twice and they've not replied either.

 Take a look at SqlYog from www.webyog.com. I use their community version
 but their paid version has a schema designer. They are responsive to
emails
 and forum posts.

 Mike

 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Friday, April 01, 2011 4:27 PM
 To: mysql@lists.mysql.com
 Subject: Any table visualization tools with wires connecting the actual
 columns?

 I am evaluating various tools for diagram generating of existing
 databases on some smaller databases (9 tables or so) first.

 The two I've tried so far are these:

 http://dev.mysql.com/downloads/workbench/
 http://www.sqlmaestro.com/download/#mysql

 Both _seem_ robust and cosmetically polished, but feel to me lacking
 the most obvious and key component of the whole purpose to make an EER
 diagram.

 I don't understand in workbench, why it creates new keys for me on
 existing tables. Maestro doesn't do this nonsense. It isn't the tools
 business where I have keys, it only needs to be concerned with what
 links to what -- that I tell it to. It's further exacerbated by the
 fact that the documentation indicates these aren't even REAL keys, they
 are cosmetic only! WTF? Why add confusion guys?

 1. Neither one seem to be smart enough to automatically know that
 columns of the same name should be linked, and furthermore they should
 be linked from all tables to the one where that column name is the PK.
 my tables don't have true InnoDB FKs setup. And some tables are MYISAM
 (as they're significantly faster). But I do use keys and I do have sane
 naming conventions, so I don't understand why they can't use the names,
 and if there are multiple tables (for some unlikely reason) then just
prompt
 me which table to use.

 Which leads me to the second and third problems...

 So I manually have started to draw the connections, but:

 2. How can I make the wires stick to a column on the left or right
 edge, so that I can have a direct visual link between the columns.
 Right now, it seems they float around the edge of the table box. That's
 sort of useless isn't it? it's like saying, well, something in this
 table points to something in that table.?! I would think that two
 programs with such high version numbers would have this feature. Maybe
 I'm missing a configuration or some way I'm supposed to do it?

 3. Some of my databases point to tables in other databases on the same
 server. It would be useful if I could make a wire that indicates this.

 Are there other (better) options out there for this? I really don't
 want to do this in Visio or make a printout of the table boxes and tape
 string to my walls to visualize all the databases, tables and columns.

 -Daevid.

_

 No virus found in this message.
 Checked by AVG - www.avg.com
 Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11

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

_

 No virus found in this message.
 Checked by AVG - www.avg.com
 Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11

_

 No virus found in this message.
 Checked by AVG - www.avg.com
 Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11

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

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

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

2011-04-07 Thread Daevid Vincent

Whoa! I never realized Toad did that. Man that is one robust program. I'm
half minded to switch away from 'the Yog'... especially for FREE! Yeah, and
it does do the sticky wires!! It only guessed some of them, but at least
it's something. It seems to be missing an auto arrange kind of feature so
once I make the wires, it can optimize the layout, but compared to other
other crappy options, this is way better... and did I mention it's FREE!
zOMGz.

d

 -Original Message-
 From: Jason Trebilcock [mailto:jason.trebilc...@gmail.com]
 Sent: Thursday, April 07, 2011 12:38 PM
 To: mysql@lists.mysql.com
 Subject: Re: Any table visualization tools with wires connecting the
actual
 columns?
 
 Toad for MySQL can do the diagramming piece...but, it looks and feels like
 you might have some of the same frustrations with it as well.  But,
another
 tool worth exploring nonetheless.
 
 On Thu, Apr 7, 2011 at 2:17 PM, Daevid Vincent dae...@daevid.com
 wrote:
 
  Does anyone have any suggestions on this? I've written to SQL Maestro
  twice and they've not replied either.
 
 
 
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Friday, April 01, 2011 4:27 PM
  To: mysql@lists.mysql.com
  Subject: Any table visualization tools with wires connecting the
  actual columns?
 
 
 
  I am evaluating various tools for diagram generating of existing
  databases on some smaller databases (9 tables or so) first.
 
  The two I've tried so far are these:
 
  http://dev.mysql.com/downloads/workbench/
  http://www.sqlmaestro.com/download/#mysql
 
  Both _seem_ robust and cosmetically polished, but feel to me lacking
  the most obvious and key component of the whole purpose to make an
 EER diagram.
 
  I don't understand in workbench, why it creates new keys for me on
  existing tables. Maestro doesn't do this nonsense. It isn't the tools
  business where I have keys, it only needs to be concerned with what
  links to what -- that I tell it to. It's further exacerbated by the
  fact that the documentation indicates these aren't even REAL keys,
  they are cosmetic only! WTF? Why add confusion guys?
 
  1. Neither one seem to be smart enough to automatically know that
  columns of the same name should be linked, and furthermore they should
  be linked from all tables to the one where that column name is the PK.
  my tables don't have true InnoDB FKs setup. And some tables are MYISAM
  (as they're significantly faster). But I do use keys and I do have
  sane naming conventions, so I don't understand why they can't use the
  names, and if there are multiple tables (for some unlikely reason)
  then just prompt me which table to use.
 
  Which leads me to the second and third problems...
 
  So I manually have started to draw the connections, but:
 
  2. How can I make the wires stick to a column on the left or right
  edge, so that I can have a direct visual link between the columns.
  Right now, it seems they float around the edge of the table box.
  That's sort of useless isn't it? it's like saying, well, something in
  this table points to something in that table.?! I would think that
  two programs with such high version numbers would have this feature.
  Maybe I'm missing a configuration or some way I'm supposed to do it?
 
  3. Some of my databases point to tables in other databases on the same
  server. It would be useful if I could make a wire that indicates this.
 
  Are there other (better) options out there for this? I really don't
  want to do this in Visio or make a printout of the table boxes and
  tape string to my walls to visualize all the databases, tables and
  columns.
 
  -Daevid.



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

Any table visualization tools with wires connecting the actual columns?

2011-04-01 Thread Daevid Vincent

I am evaluating various tools for diagram generating of existing databases
on some smaller databases (9 tables or so) first.

The two I've tried so far are these:

http://dev.mysql.com/downloads/workbench/
http://www.sqlmaestro.com/download/#mysql

Both _seem_ robust and cosmetically polished, but feel to me lacking the
most obvious and key component of the whole purpose to make an EER diagram.

I don't understand in workbench, why it creates new keys for me on existing
tables. Maestro doesn't do this nonsense. It isn't the tools business where
I have keys, it only needs to be concerned with what links to what -- that I
tell it to. It's further exacerbated by the fact that the documentation
indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add
confusion guys?

1. Neither one seem to be smart enough to automatically know that columns of
the same name should be linked, and furthermore they should be linked from
all tables to the one where that column name is the PK. my tables don't have
true InnoDB FKs setup. And some tables are MYISAM (as they're significantly
faster). But I do use keys and I do have sane naming conventions, so I don't
understand why they can't use the names, and if there are multiple tables
(for some unlikely reason) then just prompt me which table to use.

Which leads me to the second and third problems...

So I manually have started to draw the connections, but:

2. How can I make the wires stick to a column on the left or right edge, so
that I can have a direct visual link between the columns. Right now, it
seems they float around the edge of the table box. That's sort of useless
isn't it? it's like saying, well, something in this table points to
something in that table.?! I would think that two programs with such high
version numbers would have this feature. Maybe I'm missing a configuration
or some way I'm supposed to do it?

3. Some of my databases point to tables in other databases on the same
server. It would be useful if I could make a wire that indicates this.

Are there other (better) options out there for this? I really don't want to
do this in Visio or make a printout of the table boxes and tape string to my
walls to visualize all the databases, tables and columns.

-Daevid.


  _  

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11




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

2011-03-03 Thread Daevid Vincent

There is almost no VM overhead these days. mySQL is disk I/O bound, not CPU
bound.

With VMWare you can setup your partitions to be raw disks (not virtual disk
files) so you get native I/O. If you were to get some SSD's, I bet you
would even see some significant performance increase too even over a true
native system. Also consider sharding your tables to put some on
raw/ssd/vmdk depending on how they're used.

VMWare has options that are nearly bare-metal. There are other free options
like KVM that are built right into the kernel. I personally use VirtualBox
here at work for development, but I use VMWare Workstation at home. At
previous jobs, we used VMWare Server (free) for all the UAT from the test
servers themselves to the test guest OS (XP, Win7, OSX, Linux, browser
variants, etc.)

Virtual Machines are the ONLY way to go these days IMHO. It's silly to try
and setup mySQL on different ports and go through all that hassle and
configuration. With VM's you can just clone one to setup a new instance,
you have fail-over, backups, they're easy to move to new hardware, they
have console GUIs, intelligent shuffling of resources, maximizing hardware,
minimizing costs (electric, carbon, space, etc). There are so many benefits
and almost no detriments to a VM these days with computers as powerful as
they are. Even updating the VMs (patching) is fairly straight forward with
the major Linux distros (many even have web GUI front ends to push patches
to all VMs, not to mention automated unattended updates if you desire)

Just do it. DO IT! You won't ever look back, and like Reindl said, you'll
wonder how you got this far without VMs. :-)

-Daevid.

There are only 11 types of people in this world. Those that think binary
jokes are funny, those that don't, and those that don't know binary.


 -Original Message-
 From: Claudio Nanni [mailto:claudio.na...@gmail.com] 
 Sent: Thursday, March 03, 2011 2:14 PM
 To: Reindl Harald
 Cc: mysql@lists.mysql.com
 Subject: Re: best practice: mysql_multi, VMs w/single 
 instance per or doesn't matter?
 
 Just know that there is not-a-problem in running multiple 
 instances on the same host,
 then all you have to do is to evaluate the performance factor.
 In your case I would not introduce the overhead of the VMs,
 but take advantage of this to learn how to manage multiple 
 instances on the
 same host that is always useful.
 
 You can have a look at Giuseppe Maxia's MySQL 
 Sandboxhttp://mysqlsandbox.net/
 
 Or if you wish I can share my technique I use since 3.23.
 
 Cheers
 
 Claudio
 
 
 
 2011/3/3 Reindl Harald h.rei...@thelounge.net
 
  i would use virtual machines because port/socket/configuration
 
  after running our whole infrastructure on vmware i can not 
 understand
  how i could live without machine-snapshots and auto-failover :-)
 
  on hardware with virtualization support performance is also
  not a problem and ESXi is free without support on hardware
  matching the HCL
 
  Am 03.03.2011 22:52, schrieb Sid Lane:
   I've always had a single physical server that is the qc 
 mysql database
  for
   all our applications but it's now up to 85 schemas so I 
 want to break it
  up
   along the same lines as production (where there's 
 redundant pools of
  mysql
   servers by application class).
  
   my basic question is whether it's better to run multiple 
 instances on the
   same host or run single instances on multiple VMs on the 
 same physical
   server.  I can see slight advantages/disadvantages to 
 each but no obvious
   upside nor downside to either.
  
   remember, this is dev/qc, not prod, so I'm leaning toward 
 VMs so I don't
   have to manage port #s in configs or expect developers to 
 remember that
   (also, I don't have to modify scripts for multiple 
 instances, paths,
  etc).
   not big reasons for sure but all else equal I'll go the 
 less work route
  and
   the only upside to multi I see is not having to reload 
 the box as VM
  host.
  
   any compelling argument for either approach?
 
 
 
 
 -- 
 Claudio
 


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

RE: Multi slave-single master

2011-02-15 Thread Daevid Vincent

Absolutely true.

We have a master/slave pair and a secondary slave that is our 'live backup'
and we take offline every night to rsync the tarballs to tape backup too.
When it comes online, it syncs up with master. Rinse repeat. Works awesome
and seemless.

 -Original Message-
 From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] 
 Sent: Tuesday, February 15, 2011 4:50 AM
 To: mysql@lists.mysql.com
 Subject: Re: Multi slave-single master
 
 As far as I know your correcct. You can set as much slave 
 servers as you 
 need.
 
 -- 
 João Cândido de Souza Neto
 
 Machiel Richards machi...@rdc.co.za escreveu na mensagem 
 news:1297774004.1798.25.camel@machielr-laptop...
  Good day all
 
 I just have a quick question in order to confirm something..
 
 If I remember correctly, one master are allowed to have more
  than one slave server (i.e. serverA can be master to both 
 serverB and
  serverC)
 
 Am I correct in this matter?
 
 We are busy with a migration to new hardware this 
 weekend and my
  plan is to setup the new master server as a slave to 
 current production
  master in order to get all data in sync and during 
 switchover to simply
  shutdown current production and change over to the new servers using
  relevant configuration.
 
  Regards
  Machiel
 
 
 
  
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=dae...@daevid.com
 


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

RE: best way to have a unique key

2011-01-20 Thread Daevid Vincent

http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/ 

 -Original Message-
 From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] 
 Sent: Thursday, January 20, 2011 10:45 AM
 To: Anthony Pace
 Cc: mysql.
 Subject: Re: best way to have a unique key

 uuid()

 Krishna

 On Fri, Jan 21, 2011 at 12:02 AM, Anthony Pace 
 anthony.p...@utoronto.cawrote:

  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=prajapat...@gmail.com

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

RE: This just seems to slow

2011-01-02 Thread Daevid Vincent

Another option would be to mangle your insert statement with some other
language like PHP, Python, Ruby, etc. so that the inserts are MULTI inserts
instead. Something like:

INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299519),
VALUES (13071, 299520),
VALUES (13071, 299521),
...

That will radically speed up the inserts.

Also delete your INDEX / KEYs and add them at the very end instead. 

-Original Message-
From: mos [mailto:mo...@fastmail.fm] 
Sent: Sunday, January 02, 2011 8:42 PM
To: mysql@lists.mysql.com
Subject: Re: This just seems to slow

Jerry,
   Use Load Data Infile when loading a lot of data. Whoever is giving 
you the data should be able to dump it to a CSV file. Your imports will be 
much faster.

Mike

At 07:51 PM 1/2/2011, you wrote:
I'm trying to load data into a simple table, and it is taking many hours
(and
still not done). I know hardware, etc., can have a big effect, but NOTHING
should have this big an effect.

=
us-gii show create table t_dmu_history\G
*** 1. row ***
Table: t_dmu_history
Create Table: CREATE TABLE `t_dmu_history` (
   `t_dmu_history_id` int(11) NOT NULL AUTO_INCREMENT,
   `DM_History_DM_ID` int(11) DEFAULT NULL,
   `DM_History_Customer_ID` int(11) DEFAULT NULL,
   PRIMARY KEY (`t_dmu_history_id`),
   KEY `DM_History_DM_ID` (`DM_History_DM_ID`),
   KEY `DM_History_Customer_ID` (`DM_History_Customer_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1446317 DEFAULT CHARSET=utf8
=

Here's a snip of what the input file looks like:
=
SET autocommit=1;

#
# Dumping data for table 'T_DMU_History'
#

INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299519);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299520);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299521);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299522);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299524);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299526);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299527);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299528);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299529);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299531);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299532);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299533);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299534);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13071, 299535);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298880);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298881);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298882);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298883);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298884);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298885);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298886);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298887);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298889);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298890);
INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`)
VALUES (13073, 298891);
=

There are about 87 records.

I realize that using one INSERT per row is going to hurt, but I don't
control
the format of the incoming data.

Besides, I'd have thought this would be pretty quick regardless of how
clumsy
the method was.

Is that autocommit a problem? This is a bulk load into an empty table, so
I'm not worried about ACID.

Any suggestions?





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





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


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

RE: /etc/init.d/mysql start WITHOUT integrity check?

2010-12-30 Thread Daevid Vincent

 -Original Message-
 From: andrew.2.mo...@nokia.com [mailto:andrew.2.mo...@nokia.com] 
 Sent: Thursday, December 30, 2010 2:45 AM
 To: dae...@daevid.com; mysql@lists.mysql.com
 Subject: RE: /etc/init.d/mysql start WITHOUT integrity check?

 Daevid,

 I'm not quite sure I understand why you want to restart your 
 master. Adding a slave shouldn't require any restarts/reloads. 

 What have you changed in the my.cnf to solicit a restart?

It requires a restart of the slave mysql server. It also requires shutting
down the master so that no data is written while we copy the snapshot to
the slave.

http://dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html

 Andy 

 From: ext Daevid Vincent [dae...@daevid.com]
 Sent: 29 December 2010 20:25
 To: 'mysql'
 Subject: /etc/init.d/mysql start WITHOUT integrity check?

 Is there a way to /etc/init.d/mysql start WITHOUT doing an integrity
 check?

 Can I pass in a command line parameter or set something in 
 the my.cnf file?

 Our DB is a Billion rows (with a B) and that check can take HOURS.

 All we want to do is restart the server to put another slave 
 online because
 sadly /etc/init.d/mysql reload does NOT re-load the config 
 file (as one
 might hope), it is only to reload PRIVS (how useless is that 
 since GRANT
 already does that).

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com

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

RE: /etc/init.d/mysql start WITHOUT integrity check?

2010-12-30 Thread Daevid Vincent

Comment WHAT lines?
 
I looked through /etc/init.d/mysql and don't see anything related to
check or chk. I eyeballed each line in the file and nothing stands out
as the culprit causing an integrity check of the databases.
 
develo...@mypse:/etc/init.d$ ps aux | grep mysql
46:root 10239  0.0  0.1   1680   520 ?SDec25   0:00 /bin/sh
/usr/bin/mysqld_safe
49:mysql11165  0.0  5.4 129924 27864 ?Sl   Dec25   0:58
/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql
--pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306
--socket=/var/run/mysqld/mysqld.sock
50:root 11167  0.0  0.1   2920   692 ?SDec25   0:00 logger
-p daemon.err -t mysqld_safe -i -t mysqld
81:1000 19149  0.0  0.1   3004   788 pts/0R+   21:54   0:00 grep -n
-i --color=auto mysql

 mailto:develo...@mypse:/etc/init.d$ develo...@mypse:/etc/init.d$ cat
/proc/11165/cmdline
/usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mysql--user=mysql--pid-fil
e=/var/run/mysqld/mysqld.pid--skip-external-locking--port=3306--socket=/var
/run/mysqld/mysqld.sockd
 
Does anyone know if a kill -SIGHUP 11165 will cause a reload of the
configuration?


  _  

From: Wagner Bianchi [mailto:wagnerbianch...@gmail.com] 
Sent: Thursday, December 30, 2010 5:01 AM
To: andrew.2.mo...@nokia.com
Cc: dae...@daevid.com; mysql@lists.mysql.com
Subject: Re: /etc/init.d/mysql start WITHOUT integrity check?


Let me know with you whether I understood what do you want to do. Normally,
after mysqld restart on OSs as Ubuntu/Debian, we can observe a script
execution, which will check integrity of all databases tables and present a
message of Corrupt  What I did when I wanted to get rid this check of
was comment the lines inside the file script with # character.

Please, let us know if it is the operation do you want to avoid when
restart mysqld.

Best regards.
--
Wagner Bianchi


2010/12/30 andrew.2.mo...@nokia.com


Daevid,

I'm not quite sure I understand why you want to restart your master. Adding
a slave shouldn't require any restarts/reloads.

What have you changed in the my.cnf to solicit a restart?

Andy



From: ext Daevid Vincent [dae...@daevid.com]
Sent: 29 December 2010 20:25
To: 'mysql'
Subject: /etc/init.d/mysql start WITHOUT integrity check?


Is there a way to /etc/init.d/mysql start WITHOUT doing an integrity
check?

Can I pass in a command line parameter or set something in the my.cnf file?

Our DB is a Billion rows (with a B) and that check can take HOURS.

All we want to do is restart the server to put another slave online because
sadly /etc/init.d/mysql reload does NOT re-load the config file (as one
might hope), it is only to reload PRIVS (how useless is that since GRANT
already does that).


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

To unsubscribe:
http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com



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

/etc/init.d/mysql start WITHOUT integrity check?

2010-12-29 Thread Daevid Vincent

Is there a way to /etc/init.d/mysql start WITHOUT doing an integrity
check? 

Can I pass in a command line parameter or set something in the my.cnf file?

Our DB is a Billion rows (with a B) and that check can take HOURS. 

All we want to do is restart the server to put another slave online because
sadly /etc/init.d/mysql reload does NOT re-load the config file (as one
might hope), it is only to reload PRIVS (how useless is that since GRANT
already does that).


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

RE: Does mysql cache strip out /* comments */ first?

2010-11-18 Thread Daevid Vincent

Ewen thank you! You've opened my eyes to something I didn't even know about
and made my special purpose tingle.

Have you used Percona personally? What are your opinions/thoughts? If you
haven't used it, I'd be curious why not or what turned you away from it? 

 -Original Message-
 From: Ewen Fortune [mailto:ewen.fort...@gmail.com] 
 Sent: Thursday, November 18, 2010 4:56 AM
 To: Daevid Vincent
 Cc: mysql
 Subject: Re: Does mysql cache strip out /* comments */ first?

 Daevid,

 snip
  My concern is, my gut tells me that the built in mysql 
 cache system is
  dumb. And by that I mean, I suspect that mySQL isn't 
 smart enough to
  strip out comments from the SQL statement string BEFORE 
 storing it as the
  cache hash key (yet I have no facts either way to back it 
 up and hence the
  reason for this email).

  http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
  http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html

  Can anyone please tell me I'm wrong and that it is smarter 
 than I give it
  credit for, as I think this would be a very useful 
 feature (or bug-fix as
  the case may be).

 Your suspicions are correct, the query cache does not strip comments
 before storing the statement.

 This can however be done in the Percona build.

 http://www.percona.com/docs/wiki/percona-server:features:query
 _cache_enhance#query_cache_strip_comments
 http://www.percona.com/docs/wiki/percona-server:features:imple
mentation_details:details_query_cache_with_comments

 Cheers,

 Ewen

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

Does mysql cache strip out /* comments */ first?

2010-11-17 Thread Daevid Vincent


Like most developers, I have a wrapper that all of my SQL queries go
through in PHP.

We have a dedicated NOC screen that shows the mytop status of each
DEV/TEST/PROD master/slave pair.
http://daevid.com/content/examples/snippets.php (Automatic Monitoring of
remote servers)

We sometimes see stuck queries and are always hesitant to kill them off
because we never know WHO is executing that SQL. Is it a customer? Is it a
developer? Is it the boss? Is it rogue from some script gone awry? Mytop
doesn't give the full query due to screen real-estate amongst other
reasons. The downside is they bog down the server until they eventually
time-out or complete.

Anyways, today I implemented a simple, transparent and effective step
towards this puzzle.

I prefix ALL SQL (since it goes through my sql_query() function) with 

/* ${SCRIPTNAME} */ 

Now all sql in the mytop shows up as:

/* foo.php */  SELECT * FROM foo WHERE id = 1;
/* bar.php */  UPDATE bar SET a = b WHERE id = 2;
Etc...

What I'd REALLY like to do is add more information in there. Perhaps add
the FUNCTION/METHOD and the logged-in web USER that is actually executing
that SQL, etc.

My concern is, my gut tells me that the built in mysql cache system is
dumb. And by that I mean, I suspect that mySQL isn't smart enough to
strip out comments from the SQL statement string BEFORE storing it as the
cache hash key (yet I have no facts either way to back it up and hence the
reason for this email).

http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html 

Can anyone please tell me I'm wrong and that it is smarter than I give it
credit for, as I think this would be a very useful feature (or bug-fix as
the case may be).

 


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

RE: FW: [USN-1017-1] MySQL vulnerabilities

2010-11-14 Thread Daevid Vincent

I don't think you understand how many exploits work. Through some social
engineering or plain brute force or rainbow tables I can get the user/pass
for many typical users. I could also give you some code and tell you to run
it and thereby my code is executed as an authenticated user without you
even knowing it. And here's another statistic you might not be aware of --
most hacking attempts are done BY people INSIDE a company, not external to
it. It's extremely foolish and short-sighted to think that your system is
safe unless it's in a glass jar and YOU are the ONLY user on it. Even
then, YOUR account could be compromised too.

-Original Message-
From: Jan Steinman [mailto:j...@bytesmiths.com] 
Sent: Saturday, November 13, 2010 1:33 PM
To: mysql@lists.mysql.com
Subject: RE: FW: [USN-1017-1] MySQL vulnerabilities

 From: Daevid Vincent dae...@daevid.com
 
 my point exactly. there is NONE. and if you don't patch your mysql as
 needed, then you will need a lot more help when you're hacked. ;-p

I note that the impact of every single one of these vulnerabilities was An
authenticated user could exploit this to make MySQL crash, causing a denial
of service.

That's a pretty low threat level. No mention was made of gaining or
increasing access, nor of corrupting data.

First, you need an authenticated user who is trying to exploit a
vulnerability to cause denial of service.

If you're allowing a publicly accessible pseudo-user to exploit such
vulnerabilities through script injection, that's YOUR problem!

If an authenticated user causes a MySQL crash on my system, they get
de-authenticated pretty quickly. :-)


No rational person can see how using up the topsoil or the fossil fuels as
quickly as possible can provide greater security for the future, but if
enough wealth and power can conjure up the audacity to say that it can, then
sheer fantasy is given the force of truth; the future becomes reckonable as
even the past has never been. -- Wendell Berry
 Jan Steinman, EcoReality Co-op 


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


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

FW: [USN-1017-1] MySQL vulnerabilities

2010-11-12 Thread Daevid Vincent

How come these kinds of notices are not sent to the mysql list? I realize
this particular one is from Ubuntu, but the vulnerability is not ubuntu
specific, it's mysql. Why aren't the mysql, er um, Oracle people more
pro-active about letting us know these things? 

-Original Message-
From: ubuntu-security-announce-boun...@lists.ubuntu.com
[mailto:ubuntu-security-announce-boun...@lists.ubuntu.com] On Behalf Of
Marc Deslauriers
Sent: Thursday, November 11, 2010 7:49 AM
To: ubuntu-security-annou...@lists.ubuntu.com
Cc: full-disclos...@lists.grok.org.uk; bugt...@securityfocus.com
Subject: [USN-1017-1] MySQL vulnerabilities

===
Ubuntu Security Notice USN-1017-1  November 11, 2010
mysql-5.1, mysql-dfsg-5.0, mysql-dfsg-5.1 vulnerabilities
CVE-2010-2008, CVE-2010-3677, CVE-2010-3678, CVE-2010-3679,
CVE-2010-3680, CVE-2010-3681, CVE-2010-3682, CVE-2010-3683,
CVE-2010-3833, CVE-2010-3834, CVE-2010-3835, CVE-2010-3836,
CVE-2010-3837, CVE-2010-3838, CVE-2010-3839, CVE-2010-3840
===

A security issue affects the following Ubuntu releases:

Ubuntu 6.06 LTS
Ubuntu 8.04 LTS
Ubuntu 9.10
Ubuntu 10.04 LTS
Ubuntu 10.10

This advisory also applies to the corresponding versions of
Kubuntu, Edubuntu, and Xubuntu.

The problem can be corrected by upgrading your system to the
following package versions:

Ubuntu 6.06 LTS:
  mysql-server-5.05.0.22-0ubuntu6.06.15

Ubuntu 8.04 LTS:
  mysql-server-5.05.0.51a-3ubuntu5.8

Ubuntu 9.10:
  mysql-server-5.15.1.37-1ubuntu5.5

Ubuntu 10.04 LTS:
  mysql-server-5.15.1.41-3ubuntu12.7

Ubuntu 10.10:
  mysql-server-5.15.1.49-1ubuntu8.1

In general, a standard system update will make all the necessary changes.

Details follow:

It was discovered that MySQL incorrectly handled certain requests with the
UPGRADE DATA DIRECTORY NAME command. An authenticated user could exploit
this to make MySQL crash, causing a denial of service. This issue only
affected Ubuntu 9.10 and 10.04 LTS. (CVE-2010-2008)

It was discovered that MySQL incorrectly handled joins involving a table
with a unique SET column. An authenticated user could exploit this to make
MySQL crash, causing a denial of service. This issue only affected Ubuntu
6.06 LTS, 8.04 LTS, 9.10 and 10.04 LTS. (CVE-2010-3677)

It was discovered that MySQL incorrectly handled NULL arguments to IN() or
CASE operations. An authenticated user could exploit this to make MySQL
crash, causing a denial of service. This issue only affected Ubuntu 9.10
and 10.04 LTS. (CVE-2010-3678)

It was discovered that MySQL incorrectly handled malformed arguments to the
BINLOG statement. An authenticated user could exploit this to make MySQL
crash, causing a denial of service. This issue only affected Ubuntu 9.10
and 10.04 LTS. (CVE-2010-3679)

It was discovered that MySQL incorrectly handled the use of TEMPORARY
InnoDB tables with nullable columns. An authenticated user could exploit
this to make MySQL crash, causing a denial of service. This issue only
affected Ubuntu 6.06 LTS, 8.04 LTS, 9.10 and 10.04 LTS. (CVE-2010-3680)

It was discovered that MySQL incorrectly handled alternate reads from two
indexes on a table using the HANDLER interface. An authenticated user could
exploit this to make MySQL crash, causing a denial of service. This issue
only affected Ubuntu 6.06 LTS, 8.04 LTS, 9.10 and 10.04 LTS.
(CVE-2010-3681)

It was discovered that MySQL incorrectly handled use of EXPLAIN with
certain queries. An authenticated user could exploit this to make MySQL
crash, causing a denial of service. This issue only affected Ubuntu
6.06 LTS, 8.04 LTS, 9.10 and 10.04 LTS. (CVE-2010-3682)

It was discovered that MySQL incorrectly handled error reporting when using
LOAD DATA INFILE and would incorrectly raise an assert in certain
circumstances. An authenticated user could exploit this to make MySQL
crash, causing a denial of service. This issue only affected Ubuntu 9.10
and 10.04 LTS. (CVE-2010-3683)

It was discovered that MySQL incorrectly handled propagation during
evaluation of arguments to extreme-value functions. An authenticated user
could exploit this to make MySQL crash, causing a denial of service. This
issue only affected Ubuntu 8.04 LTS, 9.10, 10.04 LTS and 10.10.
(CVE-2010-3833)

It was discovered that MySQL incorrectly handled materializing a derived
table that required a temporary table for grouping. An authenticated user
could exploit this to make MySQL crash, causing a denial of service.
(CVE-2010-3834)

It was discovered that MySQL incorrectly handled certain user-variable
assignment expressions that are evaluated in a logical expression context.
An authenticated user could exploit this to make MySQL crash, causing a
denial of service. This issue only affected Ubuntu 8.04 LTS, 9.10,
10.04 LTS and 10.10. (CVE-2010-3835)

It was discovered that MySQL incorrectly

RE: FW: [USN-1017-1] MySQL vulnerabilities

2010-11-12 Thread Daevid Vincent

my point exactly. there is NONE. and if you don't patch your mysql as
needed, then you will need a lot more help when you're hacked. ;-p
 
http://lists.mysql.com/
 


  _  

From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De
Meersman
Sent: Friday, November 12, 2010 12:18 PM
To: Daevid Vincent
Cc: mysql
Subject: Re: FW: [USN-1017-1] MySQL vulnerabilities


I suspect that that is because this is not a security list, but a general
help list. If you want those things, you'll get them from either your
vendor, bugtraq, or the mysql security-specific mailing list that
undoubtedly exists somewhere. Don't ask me where, though - I'm not on it
either :-)



On Fri, Nov 12, 2010 at 8:02 PM, Daevid Vincent dae...@daevid.com wrote:


How come these kinds of notices are not sent to the mysql list? I realize
this particular one is from Ubuntu, but the vulnerability is not ubuntu
specific, it's mysql. Why aren't the mysql, er um, Oracle people more
pro-active about letting us know these 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: Order by in clause

2010-11-09 Thread Daevid Vincent

 

 -Original Message-
 From: Joeri De Backer [mailto:fons...@gmail.com] 
 Sent: Tuesday, November 09, 2010 1:16 AM
 To: mysql
 Subject: Re: Order by in clause
 
 On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge 
 m...@good-stuff.co.uk wrote:
  Hi,
 
  I have a query like this:
 
  select id, title from product where id in (1,3,5,8,10)
 
  What I want it to do is return the rows in the order 
 specified in the in
  clause, so that this:
 
  select * from product where id in (10,3,8,5,1)
 
  will give me results in this order:
 
  +--+-+
  | id   | title   |
  +--+-+
  |  10  |    foo  |
  +--+-+
  |   3  |    baz  |
  +--+-+
  |   8  |    bar  |
  +--+-+
  |   5  | wibble  |
  +--+-+
  |   1  | flirble |
  +--+-+
 
  Is this possible? If so, how?
 
 
 select * from product where id in (10,3,8,5,1) order by 
 field(id,10,3,8,5,1)
 
 should do the trick...
 
 Regards,
 
 Joeri

...ya learn something new every day... ;-)

Here's more on this topic:
http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html 
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field


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

RE: Death of MySQL popularity?

2010-11-08 Thread Daevid Vincent

 -Original Message-
 From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On 
 Behalf Of Johan De Meersman
 Sent: Thursday, November 04, 2010 2:26 AM
 To: jcbo...@yahoo.com
 Cc: MySQL
 Subject: Re: Death of MySQL popularity?

 You may want to read that again, but with your glasses on :-)

 Subscription means roughly commercial support. The (1) 
 subscript means
 Features only available in Commercial Editions, and is 
 noted *only* for
 Workbench SE, Enterprise Monitor, Enterprise Backup and 
 Cluster Manager.

 I will join you in wondering whether that means Workbench is gonna go
 payware, though.

 On Thu, Nov 4, 2010 at 10:13 AM, Christoph Boget
 christoph.bo...@gmail.comwrote:

  http://www.mysql.com/products/

  So the free version is going to include only MyISAM?  And 
 you won't be
  able to connect using MySQL Workbench (and presumably apps 
 like MySQL
  Query Browser)?  Otherwise you have to shell out $2k?  Wow.  I think
  it might be time to start seriously looking at Postgres...

So there definitely is some confusion out there. Can someone from the
@mysql / @oracle camp please confirm or deny the allegations?

http://blog.herlein.com/2010/11/oracle-is-the-borg-enterprise-software-deve
lopment-will-be-assimilated/

http://digitizor.com/2010/11/05/innodb-dropped-from-oracle-mysql-classic-ed
ition/

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

RE: Is SSD suitable for mysql server?

2010-10-25 Thread Daevid Vincent

 I guess it depends on how important your data is too.  Quite 
 a few of the SSDs on the market have been proven to not 
 honour flush requests, so if the power goes out you've got 
 corrupted data.

Uh. If you're not using a UPS battery backup then you deserve to loose your
data. And if you don't have it configured to auto-power off when it's low
on battery (extended outtage) then you also deserve to feel the resulting
pain. (http://www.apcupsd.com/)



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

mysql_tzinfo_to_sql tz_file rebuilds tables for each tzdata!

2010-10-19 Thread Daevid Vincent

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html#c11545

Note that if you are trying to keep your own database of cities and their
timezones, you can NOT use the mysql.time_zone_name.Time_zone_id as your FK
because the key will change the next time you get a new tzdata update
(like from an apt-get update) and re-run mysql_tzinfo_to_sql tz_file.
You'll have to store the Name column instead otherwise you loose data
integrity.

That script wipes the tables and re-creates them! rather than doing an
UPDATE to existing records as you would reasonably expect.

*sigh*


-- 
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 do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

2010-10-15 Thread Daevid Vincent

 -Original Message-
 From: Jesper Wisborg Krogh [mailto:jes...@noggin.com.au] 
 Sent: Friday, October 15, 2010 2:33 PM
 To: MY SQL Mailing list
 Subject: Re: How do I GRANT SELECT to mysql.time_zone_name 
 for ANYONE?!

 Hi

 On 16/10/2010, at 1:47 AM, Suresh Kuna wrote:

  Hey Daevid,

  As this time zone table won't change once it is set up. Do a copy  
  of the
  table data into another database and give grants to it.

 Copy the data is not a good solution. First of all, time zone data  
 does change. Secondly if you need to use functions such as CONVERT_TZ 
 () I believe you need access to the time zone tables in the mysql  
 database.

  On Fri, Oct 15, 2010 at 7:57 PM, Johnny Withers  
  joh...@pixelated.netwrote:

  I think this is one of those times you would update the 
 mysql.user  
  table
  directly, then flush privileges.

 You can grant access to the time zone tables just as you would do to  
 any other table.

  GRANT SELECT ON `mysql`.`time_zone_name` TO '%';
  GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%';
  GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%';
  GRANT SELECT ON `mysql`.`time_zone_name` TO '';
  GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails)

 As mentioned above, granting access to the time zone tables works  
 exactly as it does for all other tables, so. e.g. granting SELECT to  
 '%' will not allow everybody to do a SELECT on the table, but rather  
 allow users logging in as the '%'@'%' user to select from the  
 mysql.time_zone_name table. If the users used in the above GRANT  
 statements don't exist, they will also end up being created. This  
 means that you suddenly might have opened access to the database for  
 a user called '%' from everywhere (although they only can 
 select from  
 the time_zone_name table). Note that the new user can login without  
 using a password.

 (none) SELECT User, Host FROM mysql.user;
 +--+---+
 | User | Host  |
 +--+---+
 | root | localhost |
 | testuser | localhost |
 +--+---+
 2 rows in set (0.37 sec)

 (none) GRANT SELECT ON `mysql`.`time_zone_name` TO '%';
 Query OK, 0 rows affected (0.18 sec)

 (none) SELECT User, Host FROM mysql.user;
 +--+---+
 | User | Host  |
 +--+---+
 | %| % |
 | root | localhost |
 | testuser | localhost |
 +--+---+
 3 rows in set (0.00 sec)

 $ mysql -u % -p
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 ...

 Jesper

Thanks for the reply Jesper, but either there isn't a solution in your
response, or I'm missing it?

Any user can get into mysql, it's what they can do after that's the
interesting part.

I used your GRANT example above and get this...

develo...@mypse:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2275
Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
(develo...@localhost) [(none)] SELECT * FROM mysql.time_zone_name;
++--+
| Name   | Time_zone_id |
++--+
| Africa/Abidjan |1 |
| Africa/Accra   |2 |
| Africa/Addis_Ababa |3 |
| Africa/Algiers |4 |
...

But then when I try an existing user that I use for all my PHP/DB
connections:

develo...@mypse:~$ mysql -uOMT_Master -pSOMEPASS -hmypse -P3306 agis_core
(omt_mas...@mypse) [agis_core] SELECT * FROM mysql.time_zone_name;
ERROR 1142 (42000): SELECT command denied to user
'OMT_Master'@'mydomain.com' for table 'time_zone_name'

Here's the current user's I have in my VM so far:

SELECT `User`, `Host` FROM mysql.user;

User  Host  
  --
  % 
% % 
OMT_Master% 
OMT_Web   % 
View_ReadOnly % 
developer % 
diagnostics   % 
diagnostics   10.10.10.%
root  127.0.0.1 
  localhost 
debian-sys-maint  localhost 
root  localhost 

At this point of frustration, unless someone has a way to do this -- which
seems like it should be a pretty straight forward thing to do -- I'll just
add this particular OMT_Master user to have this particular table's SELECT
GRANT. Or am I missing something?

-- 
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 do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

2010-10-15 Thread Daevid Vincent

 

 -Original Message-
 From: Jesper Wisborg Krogh [mailto:jes...@noggin.com.au] 
 Sent: Friday, October 15, 2010 5:54 PM
 To: MY SQL Mailing list
 Subject: Re: How do I GRANT SELECT to mysql.time_zone_name 
 for ANYONE?!

  Any user can get into mysql, it's what they can do after that's the
  interesting part.
 
 Sorry, I'm not sure what you mean. Unless a username and host  
 combination matches a record in the mysql.user table, then the user  
 cannot log into the server.
 
 $ mysql -u random_user -p
 Enter password:
 ERROR 1045 (28000): Access denied for user  
 'random_user'@'localhost' (using password: NO)
 
 $ mysql -u random_user -p
 Enter password:
 ERROR 1045 (28000): Access denied for user  
 'random_user'@'localhost' (using password: YES)

Don't use the -p password prompt and you get into mysql...

develo...@mypse:~$ mysql -u random_user
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2393
Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)
(random_u...@localhost) [(none)] show databases;
++
| Database   |
++
| information_schema |
| mysql  |
++
2 rows in set (0.00 sec)



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

How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!

2010-10-14 Thread Daevid Vincent

I'm pulling my hair out. How do I GRANT the SELECT ability to ANY USER for
the very specific mysql.time_zone_name table?? I don't want to GRANT it to
every individual user manually, I want one single GRANT that encompasses
every user simultaneously.

I've tried all of these, and they all are valid in mySQL but none of them
actually have the desired result.

GRANT SELECT ON `mysql`.`time_zone_name` TO '%';
GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%';
GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%';
GRANT SELECT ON `mysql`.`time_zone_name` TO '';
GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails)

Here are the results:

SELECT * FROM mysql.time_zone_name LIMIT 0, 5000

Error Code : 1142
SELECT command denied to user 'daevid'@'mycompany.com' for table
'time_zone_name'


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

Are Ubuntu 64-bit /var/lib/mysql/ibdata (etc) compatible with 32-bit ??

2010-10-13 Thread Daevid Vincent

We have a PSE05 Master and PSE06 Slave (PRODUCTION servers) both are
Ubuntu 32-bit. 
We have a third slave PSE07 which is Ubuntu 64-bit. This is our 'live
backup' so to speak. We take mysqld down daily on there and tarball the
/var/lib/mysql and /var/log/mysql as snapshots (since mysqldump would take
a week literally to re-import).
Our data is about 100GB and nearly 1 Billion records and growing by several
hundred thousand per day.

We had some replication hose-up where someone accidentally wrote to the
PSE06 slave. This wasn't caught right away and so it cascaded and queued up
about 130 rows to be written. Obviously going through this whole business:

mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
slave status\G

Would take WAY too long and painful. So we thought we'd shut down PSE06,
scp the .tgz file from PSE07, dump it in, set the pointers on the slave and
let replication catch up.

Not so much.

So is this even possible. Are the ibdata files binary compatible between
bit versions (or even different OS's for that matter)

We are getting errors:

101013 23:56:22 [Warning] Neither --relay-log nor --relay-log-index were
used; so replication may break when this MySQL server acts as a slave and
has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to
avoid this problem.^M

r...@pse06:~# find /home/mysql/ | grep relay
/home/mysql/mysqld-relay-bin.01
/home/mysql/relay-log.info
/home/mysql/mysqld-relay-bin.05
/home/mysql/binlog/mysqld-relay-bin.004055
/home/mysql/binlog/mysqld-relay-bin.004287
/home/mysql/binlog/mysqld-relay-bin.005693
/home/mysql/binlog/mysqld-relay-bin.004041
/home/mysql/binlog/mysqld-relay-bin.004676
/home/mysql/binlog/mysqld-relay-bin.004664
/home/mysql/binlog/mysqld-relay-bin.004044
/home/mysql/binlog/mysqld-relay-bin.004468
/home/mysql/binlog/mysqld-relay-bin.004682
/home/mysql/binlog/mysqld-relay-bin.004094
/home/mysql/binlog/mysqld-relay-bin.004153
/home/mysql/binlog/mysqld-relay-bin.004051
/home/mysql/binlog/mysqld-relay-bin.004645
/home/mysql/binlog/mysqld-relay-bin.004516
/home/mysql/binlog/mysqld-relay-bin.003889
/home/mysql/binlog/mysqld-relay-bin.004715
/home/mysql/binlog/mysqld-relay-bin.004299
/home/mysql/binlog/mysqld-relay-bin.004483
/home/mysql/binlog/mysqld-relay-bin.004628
/home/mysql/binlog/mysqld-relay-bin.004088
/home/mysql/binlog/mysqld-relay-bin.004059
/home/mysql/binlog/mysqld-relay-bin.index
/home/mysql/binlog/mysqld-relay-bin.004271
/home/mysql/binlog/mysqld-relay-bin.004077
/home/mysql/binlog/mysqld-relay-bin.004671
/home/mysql/binlog/mysqld-relay-bin.004513
/home/mysql/binlog/mysqld-relay-bin.004732
/home/mysql/binlog/mysqld-relay-bin.004096
/home/mysql/mysqld-relay-bin.06
/home/mysql/mysqld-relay-bin.08
/home/mysql/mysqld-relay-bin.02
/home/mysql/mysqld-relay-bin.03
/home/mysql/mysqld-relay-bin.04
/home/mysql/mysqld-relay-bin.07
/home/mysql/mysqld-relay-bin.10
/home/mysql/mysqld-relay-bin.index
/home/mysql/mysqld-relay-bin.12
/home/mysql/mysqld-relay-bin.13
/home/mysql/mysqld-relay-bin.09
/home/mysql/mysqld-relay-bin.11


Oct 13 23:55:27 pse06 mysqld[9185]: 101013 23:55:27 [Warning] Neither
--relay-log nor --relay-log-index were used; so replication may break when
this MySQL server acts as a slave and has his hostname changed!! Please use
'--relay-log=mysqld-relay-bin' to avoid this problem.
Oct 13 23:55:27 pse06 mysqld[9185]: 101013 23:55:27 [ERROR] Failed to open
the relay log '/home/mysql/binlog/mysqld-relay-bin.005693' (relay_log_pos
53058571)
Oct 13 23:55:27 pse06 mysqld[9185]: 101013 23:55:27 [ERROR] Could not find
target log during relay log initialization


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

How do I use and JOIN the mysql.time_zone% tables?

2010-10-13 Thread Daevid Vincent

I'm trying to figure out how to join the mysql.time_zone% tables and make
sense of this.

YES, I know how to use them with SET time_zone = timezone; and all that. 
http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
That is NOT what I need them for (yet).

I have a list of airports and those airports have cities and countries.

I need to correlate those cities (which may or may not have an exact match
in the time_zone_name table, so for each airport/city, I will need to hunt
down the right offset via some page like this:
http://www.timeanddate.com/worldclock/search.html

THEN store the Time_zone_id in my city table (or whatever the unique
combination is that I'd need and at this point I'm very confused as to what
that is).

The problem is I can find no real documentation what all these 5 tables are
each for (some are obvious, but so cryptic it's hard to digest them)

Moreover, I don't understand the results I'm getting...

SELECT * FROM time_zone_name WHERE `Name` LIKE '%brussels%'; 

Name   Time_zone_id
-  
Europe/Brussels 412
posix/Europe/Brussels   993
right/Europe/Brussels  1574

First, WTF are there THREE Brussels?? 

SELECT * FROM time_zone_transition_type WHERE Time_zone_id IN (412, 993,
1574);

Time_zone_id  Transition_type_id  Offset  Is_DST  Abbreviation
  --  --  --  
 412   0   0   0  WET 
 412   13600   0  CET 
 412   27200   1  CEST
 412   33600   0  CET 
 412   47200   1  CEST
 412   53600   1  WEST
 412   6   0   0  WET 
 412   7   0   0  WET 
 412   87200   1  CEST
 412   93600   0  CET 
 993   0   0   0  WET 
 993   13600   0  CET 
 993   27200   1  CEST
 993   33600   0  CET 
 993   47200   1  CEST
 993   53600   1  WEST
 993   6   0   0  WET 
 993   7   0   0  WET 
 993   87200   1  CEST
 993   93600   0  CET 
1574   0   0   0  WET 
1574   13600   0  CET 
1574   27200   1  CEST
1574   33600   0  CET 
1574   47200   1  CEST
1574   53600   1  WEST
1574   6   0   0  WET 
1574   7   0   0  WET 
1574   87200   1  CEST
1574   93600   0  CET   

Now WTF are there TEN rows PER?

This page: http://www.timeanddate.com/worldclock/city.html?n=48 
says Brussels, Belgium is CEST so why are there 10
time_zone_transition_types

SELECT * 
FROM time_zone_name 
 JOIN time_zone_transition_type 
ON time_zone_name.Time_zone_id = 
 time_zone_transition_type.Time_zone_id 
--   AND time_zone_transition.Transition_type_id =
-- time_zone_transition_type.Transition_type_id
WHERE `Name` LIKE '%brussels%'; 

Name   Time_zone_id  Transition_type_id  Offset  Is_DST
Abbreviation
-    --  --  --

Europe/Brussels 412   0   0   0
WET 
Europe/Brussels 412   13600   0
CET 
Europe/Brussels 412   27200   1
CEST
Europe/Brussels 412   33600   0
CET 
Europe/Brussels 412   47200   1
CEST
Europe/Brussels 412   53600   1
WEST
Europe/Brussels 412   6   0   0
WET 
Europe/Brussels 412   7   0   0
WET 
Europe/Brussels 412   87200   1
CEST
Europe/Brussels 412   93600   0
CET 
posix/Europe/Brussels   993   0   0   0
WET 
posix/Europe/Brussels   993   13600   0
CET

RE: Can this be done with a single query?

2010-10-12 Thread Daevid Vincent

Absolutely was just going to suggest this Travis.

Another option and this is untested, but is to use HAVING and an alias.
Something to this effect...

SELECT INET_NTOA(e.src_ip) AS source_ip
WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
HAVING source_ip BETWEEN '10.0.0.0' and '10.255.255.255'

But yeah, Travis suggestion is cleaner and more efficient.

 -Original Message-
 From: Travis Ard [mailto:travis_...@hotmail.com] 
 Sent: Tuesday, October 12, 2010 9:51 AM
 To: 'Paul Halliday'
 Cc: mysql@lists.mysql.com
 Subject: RE: Can this be done with a single query?
 
 You may get better performance from your query, and be able 
 to make better
 use of indexes if you use integer comparisons for your IP address
 expressions instead of converting to strings with pattern 
 matching.  You
 might consider something like the following:
 
 
 SELECT DISTINCT(e.src_ip) 
 FROM event e
 left outer join mappings m on mappings.src_ip = e.src_ip
 WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND 
 '2010-10-12 00:00:00' 
 AND e.src_ip NOT BETWEEN 167772160 AND 184549375
 AND e.src_ip NOT BETWEEN 2886729728 AND 2886795263
 AND e.src_ip NOT BETWEEN 3232235520 AND 3232301055
 AND m.src_ip IS NULL;
 
 -Travis
 
 
 -Original Message-
 From: Paul Halliday [mailto:paul.halli...@gmail.com] 
 Sent: Tuesday, October 12, 2010 10:08 AM
 To: Johnny Withers
 Cc: mysql@lists.mysql.com
 Subject: Re: Can this be done with a single query?
 
 On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers
 joh...@pixelated.netwrote:
 
  I would try:
 
  SELECT DISTINCT(e.src_ip)
  FROM event AS e
  LEFT JOIN mappings AS m ON e.src_ip=m.src_ip
  WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND 
 '2010-10-12 00:00:00'
   AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%'
  AND INET_NTOA(e.src_ip) NOT LIKE '172.16.%.%'
  AND INET_NTOA(e.src_ip) NOT LIKE '192.168.%.%'
  AND m.src_ip IS NULL
  ;
 
  I would also modify the where clause to use:
 
  AND src_ip NOT BETWEEN INET_ATON('10.0.0.0') AND 
 INET_ATON(10.255.255.255)
  AND src_ip NOT BETWEEN INET_ATON('172.16.0.0') AND
  INET_ATON(172.16.255.255)
  AND src_ip NOT BETWEEN INET_ATON('192.168.0.0') AND
  INET_ATON(192.168.255.255)
 
  instead of
 
  AND INET_NTOA(src_ip) NOT LIKE '10.%.%.%'
  AND INET_NTOA(src_ip) NOT LIKE '172.16.%.%'
  AND INET_NTOA(src_ip) NOT LIKE '192.168.%.%
 
  You should also ensure there is an index on src_ip in 
 events and mappings
  tables.
 
  Using the INET_NTOA() function on the src_ip column will 
 prevent index
  usage during the query.
 
 
 This and the suggestion by Nathan both work.
 
 Thanks for the help!
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=dae...@daevid.com
 


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

mySQL vs. NoSQL

2010-10-07 Thread Daevid Vincent

You guys hear talk about NoSQL and here's a good article on the topic 
especially as to how it pertains to mySQL...
 
http://www.linuxjournal.com/article/10770


-- 
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 way to change timezone WITHOUT mysqld restart?

2010-10-04 Thread Daevid Vincent

Trust me, I read it. 
 
We had an I18N product at my last company and all our time was stored in
UTC in mySQL and we'd alter it on the fly for each user. This isn't rocket
science. It's done every day in probably many of the sites you visit and
don't even know it.
 
To clarify for you (again):
 

*   
Per-connection time zones. Each client that connects has its own time zone
setting, given by the session
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar
_time_zone time_zone variable. Initially, the session variable takes its
value from the global
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar
_time_zone time_zone variable, but the client can change its own time zone
with this statement: 

mysql SET time_zone = timezone;



The current session time zone setting affects display and storage of time
values that are zone-sensitive. This includes the values displayed by
functions such as
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#functi
on_now NOW() or
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#functi
on_curtime CURTIME(), and values stored in and retrieved from
http://dev.mysql.com/doc/refman/5.1/en/datetime.html TIMESTAMP columns.
Values for  http://dev.mysql.com/doc/refman/5.1/en/datetime.html
TIMESTAMP columns are converted from the current time zone to UTC for
storage, and from UTC to the current time zone for retrieval. 

Don't forget to do this stuff too:
http://dev.mysql.com/doc/refman/5.1/en/mysql-tzinfo-to-sql.html

So if it's not clear by now, you store all your dates/times in UTC (convert
them via some script if you didn't start out that way). Then per web page
connection, you read the user's profile TZ (presumably from the user
session object or some other persistent means), execute that SQL statement
above as one of the first things on the page, and FM ensues. All your
properly saved mysql rows will display in the LOCAL timezone instead of
UTC. You ALSO have to set the TZ in PHP too don't forget or you'll get
whacky discrepencies.

http://php.net/manual/en/function.date-default-timezone-set.php

There's plenty of info on this out there for using PHP  MySQL if that's
what you're using too...

http://www.ferdychristant.com/blog//archive/DOMM-84NEJN

 

  _  

From: Bryan Cantwell [mailto:bcantw...@firescope.com] 
Sent: Saturday, October 02, 2010 5:18 AM
To: Daevid Vincent
Cc: mysql@lists.mysql.com
Subject: RE: Any way to change timezone WITHOUT mysqld restart?


As a matter of fact I did, the real question is : Did you even read my
email? I said WITHOUT a restart...
The manual states that a restart of the mysqld is required. The reason for
the post to such a list is because on many occasions, user have suggestions
on some workaround for things that do work in spite of what the manual
says. 

On Fri, 2010-10-01 at 15:42 -0700, Daevid Vincent wrote: 

Did you even look at the manual?



http://lmgtfy.com/?q=mysql+set+timezone



First link.

 



 -Original Message-

 From: Bryan Cantwell [mailto:bcantw...@firescope.com] 

 Sent: Friday, October 01, 2010 10:25 AM

 To: mysql@lists.mysql.com

 Subject: Any way to change timezone WITHOUT mysqld restart?

 

 Any way to change timezone WITHOUT mysqld restart?

 It would be a lifesaver if there were some way for me not to have to

 restart because if mysql restarts then I have to go through a lot of

 other issues with my other apps.

RE: Any way to change tinezone WITHOUT mysqld restart?

2010-10-01 Thread Daevid Vincent

Did you even look at the manual?

http://lmgtfy.com/?q=mysql+set+timezone

First link.
 

 -Original Message-
 From: Bryan Cantwell [mailto:bcantw...@firescope.com] 
 Sent: Friday, October 01, 2010 10:25 AM
 To: mysql@lists.mysql.com
 Subject: Any way to change tinezone WITHOUT mysqld restart?
 
 Any way to change timezone WITHOUT mysqld restart?
 It would be a lifesaver if there were some way for me not to have to
 restart because if mysql restarts then I have to go through a lot of
 other issues with my other apps.
 
 
 


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

How to get hanging 1:M table rows as single column in main query?

2010-09-29 Thread Daevid Vincent

Given three basic tables. An fmr table which has Field Maintenance
Reports, a Seat table and a hanging or glue table to map Seats to FMRs.
[See below]

How do I get all the Seats to be in a single row with the FMR data?

If I make this kind of query, they come in as separate rows:

SELECT
`id_fmr`,
`fmr_number`,
`fmr_system`,
`fmr_station`,
`created_ts`,
 `seat`
FROM `fmr` 
  JOIN `fmr_has_seat` USING (id_fmr)
  JOIN `dim_seat` USING (id_dim_seat)
WHERE id_fmr = 3;

id_fmr  fmr_number  fmr_system  fmr_station   created_ts  seat  
--  --  --  ---  ---  
 3  320237274 2333  JFK  2010-09-24 04:35:31  35C   
 3  320237274 2333  JFK  2010-09-24 04:35:31  35D   
 3  320237274 2333  JFK  2010-09-24 04:35:31  35E   

I want something more like:

id_fmr  fmr_number  fmr_system  fmr_station   created_ts  seat  
--  --  --  ---  ---
---
 3  320237274 2333  JFK  2010-09-24 04:35:31
35C,35D,35E   


Now, I'm going to be showing a few thousand FMR rows (and ideally their
seats).

What I do now is use PHP to pull the FMR records that match a certain
criteria/filter. 
Then I pull in the entire dim_seats as an array and store it in a session
since it's not going to change ever. Then I loop over all the id_fmr that I
have pulled and look up in the fmr_has_seat table by id_fmr and implode()
the seats from the session array. It saves me a few joins and gets the job
done, but I keep feeling like there's a better way to do it.

I'm thinking there's some magic with a subselect and concat or something in
SQL, but then I wonder if that's any more efficient as mySQL still has to
do two SELECTs per FMR row. This feels to me like a common problem and
there must be an optimal mySQL way of doing it. Hanging tables of 1:M
relationships are used everywhere.

===
==

CREATE TABLE `fmr` (
  `id_fmr` int(11) NOT NULL auto_increment,
  `fmr_number` varchar(32) NOT NULL default '',
  `fmr_system` smallint(6) default NULL,
  `fmr_station` varchar(4) NOT NULL default '',
  `created_ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id_fmr`)
) ENGINE=InnoDB

id_fmr  fmr_number  fmr_system  fmr_station created_ts  
--  --  --  ---  ---
 1  319235F2A 2333  JFK  2010-09-24 04:35:31
 2  319235F29 2333  JFK  2010-09-24 04:35:31
 3  320237274 2333  JFK  2010-09-24 04:35:31  ---
 4  32023726D 2333  JFK  2010-09-24 04:35:31
 5  32023725A 2333  JFK  2010-09-24 04:35:31
 6  32023724F 2333  JFK  2010-09-24 04:35:31
 7  320237241 2333  LAX  2010-09-24 04:35:31
 8  32023723A 2333  LAX  2010-09-24 04:35:31
 9  320237232 2333  JFK  2010-09-24 04:35:31
10  320237230 2333  JFK  2010-09-24 04:35:31
 ....   ..   ..   ..

CREATE TABLE `fmr_has_seat` (
  `id_fmr` int(11) NOT NULL auto_increment,
  `id_dim_seat` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id_fmr`,`id_dim_seat`),
  KEY `id_dim_seat` (`id_dim_seat`),
  CONSTRAINT `fmr_has_seat_ibfk_1` FOREIGN KEY (`id_fmr`) REFERENCES `fmr`
(`id_fmr`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fmr_has_seat_ibfk_2` FOREIGN KEY (`id_dim_seat`) REFERENCES
`dim_seat` (`id_dim_seat`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

id_fmr  id_dim_seat
--  ---
 3  888 ---
 3  889 ---
 3  890 ---
 4  422
 4  423
 4  551
 4  552
 4  553
 5  420
 5  550
 5  628
 5  629
 5  706
 5  707
 5  811
  ...   ...

CREATE TABLE `dim_seat` (
  `id_dim_seat` int(10) unsigned NOT NULL auto_increment,
  `seat` varchar(4) default NULL,
  PRIMARY KEY  (`id_dim_seat`),
  KEY `seat` (`seat`)
) ENGINE=InnoDB

id_dim_seat  seat  
---  --
  ...  ...
888  35C  --- 
889  35D  --- 
890  35E  --- 
891  35F   
892  35G
...  ...


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

INSERT DELAYED and created_on timestamps

2010-09-29 Thread Daevid Vincent

I'm doing some reading on INSERT DELAYED
http://dev.mysql.com/doc/refman/5.0/en/insert.html

I have a user_log table:

CREATE TABLE `user_log` (
  `id_user_log` bigint(20) unsigned NOT NULL auto_increment,
  `id_user` int(10) unsigned default '0',
  `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
  `type`
enum('View','Action','Admin','Search','Login','Logout','Access','General','
API') NULL,
  `source` enum('web','mobile') character set latin1 collate
latin1_general_ci default 'web',
  `body` text character set latin1 collate latin1_general_ci,
) ENGINE=InnoDB

We are noticing a lot of these in the logs however:

Sep 29 03:05:02 pse05 mysqld[14640]: TRANSACTION 0 62715480, ACTIVE 0 sec,
process no 14639, OS thread id 2904791952 inserting
Sep 29 03:05:02 pse05 mysqld[14640]: mysql tables in use 1, locked 1
Sep 29 03:05:02 pse05 mysqld[14640]: LOCK WAIT 4 lock struct(s), heap size
320, undo log entries 1
Sep 29 03:05:02 pse05 mysqld[14640]: MySQL thread id 8330, query id 799424
10.10.10.46 OMT_Master update
Sep 29 03:05:02 pse05 mysqld[14640]: INSERT INTO user_log (`id_user`,
`type`, `source`, `body`) VALUES ...)
Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR THIS LOCK TO BE
GRANTED:

So I'm thinking we could use the DELAYED or LOW_PRIORITY.

My concern is the created_on time.

Is there any difference in the actual timestamp recorded in the database if
I use:

INSERT INTO user_log (id_user) VALUES (3);

INSERT DELAYED INTO user_log (id_user) VALUES (3);

INSERT LOW_PRIORITY INTO user_log (id_user) VALUES (3);

INSERT INTO user_log (id_user, created_on) VALUES (3, NOW());

INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3, NOW());

(or set the date via PHP):

'INSERT INTO user_log (id_user, created_on) VALUES (3, '.gmdate().')';

'INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3,
'.gmdate().')';

My point is, is mySQL smart enough to know what the time WAS when the
INSERT was supposed to be written by default, or if I DELAY it will it
process the NOW() at INSERT time or DELAYED time or what time is NOW() and
lastly if I set it with gmdate() in PHP, then that seems like it's the
exact server time at the right moment??


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

RE: INSERT DELAYED and created_on timestamps

2010-09-29 Thread Daevid Vincent

 

 -Original Message-
 From: Dan Nelson [mailto:dnel...@allantgroup.com] 
 Sent: Wednesday, September 29, 2010 2:26 PM
 To: Daevid Vincent
 Cc: 'MySQL'
 Subject: Re: INSERT DELAYED and created_on timestamps
 
 In the last episode (Sep 29), Daevid Vincent said:
  I'm doing some reading on INSERT DELAYED
  http://dev.mysql.com/doc/refman/5.0/en/insert.html
  
  I have a user_log table:
  
  CREATE TABLE `user_log` (
`id_user_log` bigint(20) unsigned NOT NULL auto_increment,
`id_user` int(10) unsigned default '0',
`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP 
 on update CURRENT_TIMESTAMP,
`type` 
 enum('View','Action','Admin','Search','Login','Logout','Access
 ','General',' API') NULL,
`source` enum('web','mobile') character set latin1 
 collate latin1_general_ci default 'web',
`body` text character set latin1 collate latin1_general_ci,
  ) ENGINE=InnoDB
 
  We are noticing a lot of these in the logs however:
  
  Sep 29 03:05:02 pse05 mysqld[14640]: TRANSACTION 0 
 62715480, ACTIVE 0 sec, process no 14639, OS thread id 
 2904791952 inserting
  Sep 29 03:05:02 pse05 mysqld[14640]: mysql tables in use 1, locked 1
  Sep 29 03:05:02 pse05 mysqld[14640]: LOCK WAIT 4 lock 
 struct(s), heap size 320, undo log entries 1
  Sep 29 03:05:02 pse05 mysqld[14640]: MySQL thread id 8330, 
 query id 799424 10.10.10.46 OMT_Master update
  Sep 29 03:05:02 pse05 mysqld[14640]: INSERT INTO user_log 
 (`id_user`, `type`, `source`, `body`) VALUES ...)
  Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR 
 THIS LOCK TO BE GRANTED:
  
  So I'm thinking we could use the DELAYED or LOW_PRIORITY.
 
 INSERT DELAYED only works on MyISAM, MEMORY, and ARCHIVE 
 tables.  You'll get
 a 1616 error if you try it on InnoDB.  MySQL 5.5 is supposed 
 to have a lot
 of concurrency improvements in; can you test your application 
 on that and
 see if it's any faster than 5.0?

Yeah, I just discovered that. However LOW_PRIORITY works on InnoDB tables
it seems (at least, no error). But my original question still applies (even
if for curiosity sake). Does mySQL account for the DELAY or
LOW_PRIORITY time it took to write to the DB and adjust the timestamp
accordingly or does it do the timestamp at the time of actual write vs. the
time it was originally called?


-- 
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 get hanging 1:M table rows as single column in main query?

2010-09-29 Thread Daevid Vincent

BRILLIANT

SELECT
`id_fmr`,
`fmr_number`,
`fmr_system`,
`fmr_station`,
`created_ts`,
 GROUP_CONCAT(`seat`)
FROM `fmr`
  JOIN `fmr_has_seat` USING (id_fmr)
  JOIN `dim_seat` USING (id_dim_seat)
WHERE id_fmr = 3
GROUP BY id_fmr;

id_fmr  fmr_number  fmr_system  fmr_station   created_ts
group_concat(`seat`)
--  --  --  ---  ---

 3  320237274 2333  JFK  2010-09-24 04:35:31
35C,35D,35E  

 -Original Message-
 From: Johnny Withers [mailto:joh...@pixelated.net] 
 Sent: Wednesday, September 29, 2010 1:35 PM
 To: Daevid Vincent
 Cc: MySQL
 Subject: Re: How to get hanging 1:M table rows as single 
 column in main query?
 
 GROUP_CONCAT() ?
 
 And group by id_fmr ?
 
 JW
 
 
 On Wed, Sep 29, 2010 at 2:38 PM, Daevid Vincent 
 dae...@daevid.com wrote:
 
  Given three basic tables. An fmr table which has Field Maintenance
  Reports, a Seat table and a hanging or glue table to 
 map Seats to FMRs.
  [See below]
 
  How do I get all the Seats to be in a single row with the 
 FMR data?
 
  If I make this kind of query, they come in as separate rows:
 
  SELECT
 `id_fmr`,
 `fmr_number`,
 `fmr_system`,
 `fmr_station`,
 `created_ts`,
  `seat`
  FROM `fmr`
   JOIN `fmr_has_seat` USING (id_fmr)
   JOIN `dim_seat` USING (id_dim_seat)
  WHERE id_fmr = 3;
 
  id_fmr  fmr_number  fmr_system  fmr_station   
 created_ts  seat
  --  --  --  ---  
 ---  
  3  320237274 2333  JFK  2010-09-24 04:35:31  35C
  3  320237274 2333  JFK  2010-09-24 04:35:31  35D
  3  320237274 2333  JFK  2010-09-24 04:35:31  35E
 
  I want something more like:
 
  id_fmr  fmr_number  fmr_system  fmr_station   
 created_ts  seat
  --  --  --  ---  ---
  ---
  3  320237274 2333  JFK  2010-09-24 04:35:31
  35C,35D,35E
 
 
  Now, I'm going to be showing a few thousand FMR rows (and 
 ideally their
  seats).
 
  What I do now is use PHP to pull the FMR records that match 
 a certain
  criteria/filter.
  Then I pull in the entire dim_seats as an array and store 
 it in a session
  since it's not going to change ever. Then I loop over all 
 the id_fmr that I
  have pulled and look up in the fmr_has_seat table by id_fmr 
 and implode()
  the seats from the session array. It saves me a few joins 
 and gets the job
  done, but I keep feeling like there's a better way to do it.
 
  I'm thinking there's some magic with a subselect and concat 
 or something in
  SQL, but then I wonder if that's any more efficient as 
 mySQL still has to
  do two SELECTs per FMR row. This feels to me like a common 
 problem and
  there must be an optimal mySQL way of doing it. Hanging 
 tables of 1:M
  relationships are used everywhere.
 
  
 ==
 =
  ==
 
  CREATE TABLE `fmr` (
   `id_fmr` int(11) NOT NULL auto_increment,
   `fmr_number` varchar(32) NOT NULL default '',
   `fmr_system` smallint(6) default NULL,
   `fmr_station` varchar(4) NOT NULL default '',
   `created_ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
   PRIMARY KEY  (`id_fmr`)
  ) ENGINE=InnoDB
 
  id_fmr  fmr_number  fmr_system  fmr_station created_ts
  --  --  --  ---  ---
  1  319235F2A 2333  JFK  2010-09-24 04:35:31
  2  319235F29 2333  JFK  2010-09-24 04:35:31
  3  320237274 2333  JFK  2010-09-24 
 04:35:31  ---
  4  32023726D 2333  JFK  2010-09-24 04:35:31
  5  32023725A 2333  JFK  2010-09-24 04:35:31
  6  32023724F 2333  JFK  2010-09-24 04:35:31
  7  320237241 2333  LAX  2010-09-24 04:35:31
  8  32023723A 2333  LAX  2010-09-24 04:35:31
  9  320237232 2333  JFK  2010-09-24 04:35:31
 10  320237230 2333  JFK  2010-09-24 04:35:31
  ....   ..   ..   ..
 
  CREATE TABLE `fmr_has_seat` (
   `id_fmr` int(11) NOT NULL auto_increment,
   `id_dim_seat` int(10) unsigned NOT NULL,
   PRIMARY KEY  (`id_fmr`,`id_dim_seat`),
   KEY `id_dim_seat` (`id_dim_seat`),
   CONSTRAINT `fmr_has_seat_ibfk_1` FOREIGN KEY (`id_fmr`) 
 REFERENCES `fmr`
  (`id_fmr`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `fmr_has_seat_ibfk_2` FOREIGN KEY 
 (`id_dim_seat`) REFERENCES
  `dim_seat` (`id_dim_seat`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB
 
  id_fmr  id_dim_seat
  --  ---
  3  888 ---
  3  889 ---
  3  890 ---
  4  422
  4  423
  4  551

ENGINE=ARCHIVE doesn't support INDEX!!??

2010-09-29 Thread Daevid Vincent

I gotta ask...

http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html#c11511



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

RE: ORDER BY with field alias issue

2010-09-29 Thread Daevid Vincent

Easy.

SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time_Format`
FROM `reservation`
ORDER BY `Time`

 -Original Message-
 From: BMBasal [mailto:bmb37...@gmail.com] 
 Sent: Wednesday, September 29, 2010 3:50 PM
 To: 'Chris W'; 'MYSQL General List'
 Subject: RE: ORDER BY with field alias issue

 It is inherent in your naming.
 As long as your alias time is the same as the column name 
 time, MySQL
 will have no way to distinguish which one you refers to 
 exactly in your
 order-by clause, and chooses the alias in the select-clause 
 as the one you
 intended. You confused MySQL.

 First, why you have to hang on time as alias. 
 Second, if you don't mind adding another column in your 
 select-clause as a
 throw-away, say, 
 select DATE_FORMAT(`Time`, '%h:%i%p') as `Time`, `time` as 
 `timex`   
 Then, you could use `timex` in your order clause. This works, 
 but with extra
 output, not elegant.

 -Original Message-
 From: Chris W [mailto:4rfv...@cox.net] 
 Sent: Tuesday, September 28, 2010 8:10 PM
 To: MYSQL General List
 Subject: ORDER BY with field alias issue

   I have the following query that is giving me problems.

 SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time`
 FROM `reservation`
 ORDER BY `Time`

 Problem is it sorts wrong because of the date format function output 
 with am and pm.  I guess I should have named things differently but I 
 would rather not do that.  Is there a standard way to get around this 
 and have it sort by the non-formatted time value?

 Chris W

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

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

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

RE: Capitalize Input via Auto Complete?

2010-09-14 Thread Daevid Vincent

Get this tool: http://sqlyog.com/ it rocks. 

There is also http://www.quest.com/toad-for-mysql/ which is pretty great. 

 -Original Message-
 From: Carlos Mennens [mailto:carlosw...@gmail.com] 
 Sent: Tuesday, September 14, 2010 9:03 AM
 To: MySQL
 Subject: Capitalize Input via Auto Complete?

 I am new to MySQL coming from PostgreSQL  noticed a really annoying
 issue. When I select a database, and try to auto complete in MySQL, it
 doesn't capitalize the MySQL statements.

 It's irritating to me to only be able to auto complete statements like
 'SELECT', 'ALTER',  'INSERT' only if I hold down the shift key or
 caps lock key while typing. Is there a way to force MySQL to auto
 complete commonly used statements while typing them in lowercase which
 is normal behavior in PostgreSQL?

 Thanks for any info!

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

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

RE: Capitalize Input via Auto Complete?

2010-09-14 Thread Daevid Vincent

 -Original Message-
 From: Carlos Mennens [mailto:carlosw...@gmail.com] 
 Sent: Tuesday, September 14, 2010 12:54 PM
 To: MySQL
 Subject: Re: Capitalize Input via Auto Complete?

 On Tue, Sep 14, 2010 at 2:24 PM, Daevid Vincent 
 dae...@daevid.com wrote:
  Get this tool: http://sqlyog.com/ it rocks.

  There is also http://www.quest.com/toad-for-mysql/ which is 
 pretty great.

 I can't use any graphical or 3rd party add-on's. I was hoping MySQL
 had this native / built in. I guess not...

Is there some reason you can't use a graphical tool?

You do know you can use ssh tunnels and such to connect to your server from
your desktop right? I do it all day long. It's pretty easy to do and built
in to these programs.

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

RE: Unique ID's across multiple databases

2010-09-13 Thread Daevid Vincent

 -Original Message-
 From: Kiss Dániel [mailto:n...@dinagon.com] 
 Sent: Monday, September 13, 2010 5:59 AM

 Well, thanks, but I'm afraid using UUID's (even with hex 
 compression) is
 kind of a suicide, when it comes to performance.
 This is a good summary about the issues:
 http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/

Is this UUID issue unique to mySQL or are there other RDBMS's that handle
it better (Postgress, Oracle, SQL Server, etc?)

I too have a need for a unique identifier that will mesh with other
databases periodically. So that a user in one local DB/server will get
migrated to a master DB which in turn will sync up with remote sites so
that all sites will have all users in it each night (for example).

Having a mapping of UUID to local ID seems one way, but I feel there is a
lot of room for collisions and integrity issues that way no?

There are some solutions at the bottom of that blog post. Are those not
good then? They seem interesting to me.


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

Does putting a LIMIT on a DELETE clause make any difference?

2010-09-08 Thread Daevid Vincent

I am curious about something.

I have a glue or hanging table like so:

CREATE TABLE `fault_impact_has_fault_system_impact` (
  `id_fault_impact` int(10) unsigned NOT NULL,
  `id_fault_system_impact` smallint(5) unsigned NOT NULL,
  KEY `fault_impact_key` (`id_fault_impact`,`id_fault_system_impact`),
  KEY `id_fault_system_impact` (`id_fault_system_impact`),
  CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_1` FOREIGN KEY
(`id_fault_impact`) REFERENCES `fault_impact` (`id_fault_impact`) ON DELETE
CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_2` FOREIGN KEY
(`id_fault_system_impact`) REFERENCES `fault_system_impact`
(`id_fault_system_impact`) ON DELETE CASCADE ON UPDATE CASCADE
) 

And a lookup table like this:

CREATE TABLE `fault_system_impact` (
  `id_fault_system_impact` smallint(5) unsigned NOT NULL auto_increment,
  `fault_sytem_impact_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id_fault_system_impact`)
) 

I have a bunch of checkboxes in a FORM and so in order to update
properly, I wipe out all the PK IDs and then start inserting. It looks like
this:

UPDATE `fault_impact` SET `bite_code` = '12', `bite_code_type` = '32',
`bite_subcode` = '21', `description_text` = 'Some random fault description
here.', `fault_id` = '11-1', `fault_impact_other_explain` = '',
`id_fault_area_impact` = '3', `symptom_lru_id` = '232', `symptom_lru_subid`
= '34', `sys_perf_affected` = '', `update_date` = '2010-09-09 00:04:29'
WHERE id_fault_impact = '2495' LIMIT 1;

DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact =
2495;

INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 1);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 3);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 2);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 7);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 10);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 14);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 9);
INSERT INTO
fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp
act`) VALUES(2495, 4);

Given that I know there can only be a maximum of id_fault_system_impact IDs
-- currently there are 17 rows in the fault_system_impact table -- and
they're unique to any given id_fault_impact, would it benefit me to change
my DELETE statement to something like this:

DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact =
2495 LIMIT 17;

Since the fault_impact_has_fault_system_impact table could have thousands
of rows and it seems that mySQL would do a table scan? Unfortunately, you
can't EXPLAIN on a DELETE to see what it might be doing. :( OR is mySQL
smart enough to know that the id_fault_impact is an index and therefore it
will just be right quick and stop after deleting those 8 rows above?





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

RE: Symlinks not working when pointing to another table.

2010-09-02 Thread Daevid Vincent

 -Original Message-
 From: Julien Lory [mailto:julien.l...@gmail.com] 
 Sent: Thursday, September 02, 2010 12:31 PM
 To: mysql@lists.mysql.com
 Subject: Symlinks not working when pointing to another table.

   Hello,

 I've done lot of researches and tests but can't find any 
 answer. I need 
 to share one table between two db, those two db are in the 
 same path ( 
 /var/lib/mysql/db1  db2 ). I created symbolic links for db2 
 pointing to 
 the table in db1.
 When I query the table from db2 I get this error :
 'ERROR 1030 (HY000): Got error 140 from storage engine'
 I'm sure it was working in previous version of MySQL, but 
 from the last 
 version it's no longer working.

 This is how it looks :

 test-lan:/var/lib/mysql/test3# ls -alh
 drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 .
 drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 ..
 lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm -  
 /var/lib/mysql/test/blbl.frm
 lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD -  
 /var/lib/mysql/test/blbl.MYD
 lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI -  
 /var/lib/mysql/test/blbl.MYI
 -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt

 I really need those symlinks, is there a way to make them 
 working like 
 before ? ( old MySQL-server is fine )

Just out of curiosity. If you're using apparmor and this new directory is
on another partion or mount point or anything, you might have to add a rule
in apparmor's config for them...

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

RE: Does innodb have a temp table space?

2010-09-01 Thread Daevid Vincent

InnoDB is one of MANY engines in the RDBMS mySQL.

There IS in fact a few ways to store in temporary tables (both RAM and DISK
based)

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Look at:

TABLESPACE 
PARTITIONS
ENGINE 

 -Original Message-
 From: neutron [mailto:neutronsh...@gmail.com] 
 Sent: Wednesday, September 01, 2010 6:05 PM
 To: mysql@lists.mysql.com
 Subject: Does innodb have a temp table space?
 
 Hi all,
 
 As far as I know, some DB has a separate table space to store temp
 data (such as for external sort).
 
 My questions are:
 
 1. Does innodb also has a separate temp-tablespace?
 2.  If I don't use innodb_file_per_table,   where is innodb's
 temporary tablespace? Is it in the shared tablespace?
 
 
 Thanks all!
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=dae...@daevid.com
 


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

RE: Even or Odds numbers

2010-08-31 Thread Daevid Vincent

Not sure what you're trying to do, but if all you want to do is toggle
between two things (as in row color zebra stripes or something) then I
would suggest you not use some MOD() routine and instead just flip a
boolean.

For example in PHP just do:

tr class=?= ($r == !r) ? 'light' : 'dark' ?  

just sayin'.

 -Original Message-
 From: Mike Blezien [mailto:mick...@frontiernet.net] 
 Sent: Tuesday, August 31, 2010 11:13 AM
 To: jcbo...@yahoo.com
 Cc: MySQL List
 Subject: Re: Even or Odds numbers
 
 - Original Message - 
 From: Christoph Boget christoph.bo...@gmail.com
 To: Mike Blezien mick...@frontiernet.net
 Cc: MySQL List mysql@lists.mysql.com
 Sent: Tuesday, August 31, 2010 1:06 PM
 Subject: Re: Even or Odds numbers
 
 
  is there a function, using MySQL 5.0v, that can detect if 
 a numerical value
  is either an Even or Odd number
 
  MOD()
 
  
 http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.
 html#function_mod
 
  SELECT MOD( X, 2 )
 
  where X is your number (or column name).  If 0, it's even 
 if 1 it's odd.
 
  thnx,
  Christoph
 
 
 Thanks that should do the trick. Appreciate the other 
 response too. Big help :)
 
 Mike(mickalo)Blezien
 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 Thunder Rain Internet Publishing
 Custom Programming  Web Hosting Services
 http://www.thunder-rain.com/
 Office: 1.712.395.0670
 Skype Contact: cgimickalo
 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=dae...@daevid.com
 


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

RE: Creating a dedicated reporting server for management?

2010-08-27 Thread Daevid Vincent

You misunderstand.

The slave is LIVE. Every write to the master writes to BOTH slaves. You can
add as many slaves to a master as you want.

My point was IN ADDITION to that benefit, it also affords you the ability
to take down one of the slaves for an offsite archive. So if you need
master/slave for prod, and slave for reports that all have to be up 100%
then setup a 3rd slave then that is a live backup and you can then take it
down at whatever interval you like to make a tarball for offsite backups.
When you start the slave up again, it will re-synch itself to the master
again automatically. Rinse. Repeat.

I don't care how many slaves you have, they don't count as a real backup.

If I say DROP DATABASE foo on the master, it will happily replicate to
the slaves and you're whole company is now fsck'd in a way you can't even
begin to comprehend. :) Ala
http://www.geek.com/articles/news/disgruntled-employee-kills-journalspace-w
ith-data-wipe-2009015/


 -Original Message-
 From: nunziodav...@yahoo.com [mailto:nunziodav...@yahoo.com] 
 Sent: Friday, August 27, 2010 5:01 PM
 To: Daevid Vincent
 Subject: Re: Creating a dedicated reporting server for management?
 
 Thanks for the idea.  The sales guys want real time access so 
 tarballing daily or even several times a day won't work.
 
 Nunzio
 Sent via BlackBerry from T-Mobile
 
 -Original Message-
 From: Daevid Vincent dae...@daevid.com
 Date: Mon, 23 Aug 2010 16:11:47 
 To: 'Travis Ard'travis_...@hotmail.com; 'Nunzio 
 Daveri'nunziodav...@yahoo.com; mysql@lists.mysql.com
 Subject: RE: Creating a dedicated reporting server for management?
 
 While not elegant, you could setup multiple instances of 
 mysql on different
 ports on the same box so each instance is a slave to a 
 different master.
 
 From your web GUI, this is transparent to the end user since your
 configuration files and DB wrappers would handle the 
 connections to 4.
 
 We do something similar now where we have DEV 01/02 TEST 
 03/04 PROD 05/06
 and have an 07 slave that runs several instances so each 
 01,03,05 have a
 dedicated slave and the 07 secondary slave. Then we have a script that
 takes 07 mysql down, tarballs (7zip) the /var/lib/mysql and 
 /var/log/mysql
 dirs (it's WAY faster than a mysql_dump for both import and 
 export), rsyncs
 them to a master offsite backup server/tape.
 
  -Original Message-
  From: Travis Ard [mailto:travis_...@hotmail.com] 
  Sent: Monday, August 23, 2010 3:52 PM
  To: 'Nunzio Daveri'; mysql@lists.mysql.com
  Subject: RE: Creating a dedicated reporting server for management?
  
  You could try doing a multi-master configuration by setting 
  up mysql proxy
  to receive changes from 1, 2, and 3 and replicate to 4.
  
  -Travis
  
  -Original Message-
  From: Nunzio Daveri [mailto:nunziodav...@yahoo.com] 
  Sent: Monday, August 23, 2010 3:28 PM
  To: mysql@lists.mysql.com
  Subject: Creating a dedicated reporting server for management?
  
  Hello Gurus, I have a customer who wants to create a 
  reporting server for
  his 
  management team.  He wants to take server 1,2,3 and move the 
  3 databases
  from 
  all 3 servers to one server server 4 and then have the 
  management team run
  all 
  the reports from server 4 since there are tons and tons of 
  joins.  How can I
  
  accomplish this?  I can't do replication since server 4 is 
  going to be a
  slave 
  AND a slave can only have one master so I am sitting here 
  trying to figure
  out 
  how to get this done?  This is an ongoing transfer of data as 
  the reports
  have 
  to be in sync with the 3 master servers so replication would 
  be perfect.
  
  Any help, docs and directions is most appreciated.
  
  Thanks In Advance :-)
  
  Nunzio
  
  
  

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


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

RE: master-slave replication sync problems.

2010-08-26 Thread Daevid Vincent

ssh to the slave
mysql -uroot -pPASSWORD -P3306 -hlocalhost

show slave status\G

If the Slave IO is NOT Running, but SQL is, then simply try to restart the
slave...

*** 1. row ***
 Slave_IO_State:
Master_Host: 10.10.10.45
Master_User: slave
Master_Port: 3306
  ...
   Slave_IO_Running: No
  Slave_SQL_Running: Yes

start slave; show slave status\G

Otherwise if it's a replication issue such as this, then you must skip over
the bad SQL queries one at a time till the slave syncs.

*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.41
Master_User: slave
Master_Port: 3306
  ...
   Slave_IO_Running: Yes
  Slave_SQL_Running: No
Replicate_Do_DB: agis_core_2008
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 1061
 Last_Error: Error 'Duplicate key name 'id_operator'' on
query. 

The SQL statement will give you an idea of where the master and slave went
askew. If these are recent commands you did you can guess as to how much to
increment the SKIP_COUNTER below, otherwise, you just have to do it one at
a time until they sync again.

mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
slave status\G

Repeat the above statements over and over until you see two YES rows.

*** 1. row ***

   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

 -Original Message-
 From: Norman Khine [mailto:nor...@khine.net] 
 Sent: Thursday, August 26, 2010 6:05 AM
 To: mysql@lists.mysql.com
 Subject: master-slave replication sync problems.
 
 hello,
 i have a working master-slave replication, the problem i find is that
 if i restart the MASTER there is a difference in the MASTER_LOG_FILE
 and MASTER_LOG_POS on the SLAVE.
 
 what is the correct way to keep the two slaves in sync even after i
 restart the server. the way i do it now is to:
 
 [MASTER]
 mysql show master status;
 
 +--+--+--+
 --+
 | mysql-bin.10 | 13405429 | upgrade,tracker,bugs |
 mysql,information_schema |
 +--+--+--+
 --+
 1 row in set (0.00 sec)
 
 [SLAVE]
 mysql stop slave;
 mysql change master to MASTER_HOST='master.domain.com',
 MASTER_USER='replicator', MASTER_PASSWORD='xxx',
 MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215;
 mysql start slave;
 
 is this correct or is there a better way to do this?
 
 thanks


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

RE: Creating a dedicated reporting server for management?

2010-08-23 Thread Daevid Vincent

While not elegant, you could setup multiple instances of mysql on different
ports on the same box so each instance is a slave to a different master.

From your web GUI, this is transparent to the end user since your
configuration files and DB wrappers would handle the connections to 4.

We do something similar now where we have DEV 01/02 TEST 03/04 PROD 05/06
and have an 07 slave that runs several instances so each 01,03,05 have a
dedicated slave and the 07 secondary slave. Then we have a script that
takes 07 mysql down, tarballs (7zip) the /var/lib/mysql and /var/log/mysql
dirs (it's WAY faster than a mysql_dump for both import and export), rsyncs
them to a master offsite backup server/tape.

 -Original Message-
 From: Travis Ard [mailto:travis_...@hotmail.com] 
 Sent: Monday, August 23, 2010 3:52 PM
 To: 'Nunzio Daveri'; mysql@lists.mysql.com
 Subject: RE: Creating a dedicated reporting server for management?
 
 You could try doing a multi-master configuration by setting 
 up mysql proxy
 to receive changes from 1, 2, and 3 and replicate to 4.
 
 -Travis
 
 -Original Message-
 From: Nunzio Daveri [mailto:nunziodav...@yahoo.com] 
 Sent: Monday, August 23, 2010 3:28 PM
 To: mysql@lists.mysql.com
 Subject: Creating a dedicated reporting server for management?
 
 Hello Gurus, I have a customer who wants to create a 
 reporting server for
 his 
 management team.  He wants to take server 1,2,3 and move the 
 3 databases
 from 
 all 3 servers to one server server 4 and then have the 
 management team run
 all 
 the reports from server 4 since there are tons and tons of 
 joins.  How can I
 
 accomplish this?  I can't do replication since server 4 is 
 going to be a
 slave 
 AND a slave can only have one master so I am sitting here 
 trying to figure
 out 
 how to get this done?  This is an ongoing transfer of data as 
 the reports
 have 
 to be in sync with the 3 master servers so replication would 
 be perfect.
 
 Any help, docs and directions is most appreciated.
 
 Thanks In Advance :-)
 
 Nunzio
 
 
 
   
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=dae...@daevid.com
 


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

RE: Possible tricks to ALTER on huge tables?

2010-08-06 Thread Daevid Vincent

 -Original Message-
 From: Rob Wultsch [mailto:wult...@gmail.com] 
 Sent: Thursday, August 05, 2010 6:05 PM
 To: Daevid Vincent
 Cc: MySQL List
 Subject: Re: Possible tricks to ALTER on huge tables?

 Having significant amount of overhead for unused columns will without
 doubt harm performance significantly for certain operations.

I don't see this as significant overhead. A few null columns that are
dormant are not going to impact performance in any measurable way I don't
think.

 Altering .frm files should is always be tried on a non-prod box before
 even considering using it on prod. 

Thanks Capt'n Obvious. ;-p

Isn't that the case for ANY and ALL DB operations?

 There are some well known use cases
 (adding enums values, enlarging varchar columns) where altering a .frm
 is useful, but it should always be considered very dangerous.

Of course.

 You could of course consider using PostgreSQL which would only need a
 very brief exclusive lock for adding a default null column...

...an interesting thought.

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

Possible tricks to ALTER on huge tables?

2010-08-05 Thread Daevid Vincent

Driving to work today, I had an epiphany thought, but wanted to see if
anyone could prove my theory or not.

We currently have some tables that are approaching 1 BILLION rows (real
Billion, with nine zeros, not that silly six zero version). Trying to do an
ALTER on them to add a column can sometimes take hours.

I'm wondering if we had the foresight to create the tables, and then tack
on extra dormant columns of various common types, such as:

future_uint int(10) unsigned null,
future_int int(10) signed null,
future_var varchar(255) null,
Etc.

So basically they'd be unused, then when we wanted a new column of that
type, we would just rename the dormant one. 

I'm not sure if mySQL is smart enough to realize that if the schema
definition for a column is identical, then it's just a simple rename, or if
it treats any change the same and will still take hours to complete (if so,
perhaps there's an optimization for you mysql developer team)

Another option I considered, was is it possible to just go in with a hex
editor and rename the field in the .frm file? Is there some kind of .frm
editor available anywhere?

r...@mypse:/var/lib/mysql/agis_core# hexdump -C country.frm
1000  01 01 00 00 0a 00 00 00  02 00 01 00 00 00 01 80
||
1010  02 00 00 12 00 02 00 ff  50 52 49 4d 41 52 59 ff
|PRIMARY.|
1020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00
||
*
13f0  00 00 00 00 00 00 00 00  00 00 00 00 06 00 49 6e
|..In|
1400  6e 6f 44 42 00 00 00 00  00 00 00 00 00 00 00 00
|noDB|
1410  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00
||
*
2150  04 00 0b 69 64 5f 63 6f  75 6e 74 72 79 00 05 00
|...id_country...|
2160  05 69 73 6f 32 00 06 00  05 69 73 6f 33 00 07 00
|.iso2iso3...|
2170  0d 63 6f 75 6e 74 72 79  5f 6e 61 6d 65 00 04 0b
|.country_name...|
2180  05 05 00 02 00 00 12 00  0f 00 00 02 c0 00 00 05
||
2190  05 06 06 00 04 00 00 00  80 00 00 00 fe c0 00 00
||
21a0  06 05 09 09 00 0a 00 00  00 80 00 00 00 fe c0 00
||
21b0  00 07 0d 42 fd 02 13 00  00 00 00 00 00 00 0f c0
|...B|
21c0  00 00 ff 69 64 5f 63 6f  75 6e 74 72 79 ff 69 73
|...future_var...|
21d0  6f 32 ff 69 73 6f 33 ff  63 6f 75 6e 74 72 79 5f
|...future_int...|


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

There is something wrong with bugs.mysql.com email server I think.

2010-08-02 Thread Daevid Vincent

I don't know if anyone from mysql is monitoring this list, but I think
there is something goofy going on with your email servers. I just now got
this email below. Uhm. I wrote that bug over a year and a half ago, and the
reply by Susanne was the day after. WTF?

...better late than never I suppose, but maybe you should look into your
mail server Q and see if there are more stuck emails?! 

-Original Message-
From: Bug Database [mailto:do-not-re...@mysql.com] 
Sent: Tuesday, July 27, 2010 12:43 PM
To: dae...@daevid.com
Subject: #42774 [Com]: mysqldump command line switch to ignore temporary
tables

ATTENTION! Do NOT reply to this email!
To reply, use the web interface found at
http://bugs.mysql.com/?id=42774edit=2

 Comment by:   Bugs System
 Reported by:  Daevid Vincent
 Category: Client
 Severity: S4 (Feature request)
 Status:   Verified
 Version:  5.0.51a
 OS:   Linux
 OS Details:   Ubuntu
 Tags: IGNORE, temporary, mysqldump
 Defect Class: D5 (Feature request)

[12 Feb 2009 12:53] Susanne Ebrecht

Many thanks for writing a bug report.

Changed category because mysqldump is CLI.

Verified as described.

The problem is related to general indexing.



[11 Feb 2009 21:05] Daevid Vincent

Description:
We have a live database with tables that are over 500M rows. We cannot
take the server down to do a backup, or in this case to delete
temporary tables. However we use temporary tables constantly to take
offloads (in SQL format, create a temp database/tables, then normalize
and re-insert that offload data into our main database). This happens
every minute or so and is increasing as we expand. 

We would like a way to ignore temp tables from mysqldump's command
line.

/*!4 ALTER TABLE `offload_temp_1d071827a37c402d8294d4ee65d86e4d`
DISABLE KEYS */;
/*!4 ALTER TABLE `offload_temp_1d071827a37c402d8294d4ee65d86e4d`
ENABLE KEYS */;
/*!4 ALTER TABLE `offload_temp_55cd7b89c3def439a9727821717428ec`
DISABLE KEYS */;
/*!4 ALTER TABLE `offload_temp_55cd7b89c3def439a9727821717428ec`
ENABLE KEYS */;

So as you can see, we end up with HUGE amounts of temporary tables
being backed up and then on occasion re-imported. Then we don't know
which temp tables are stale vs. active (the active ones do remove
themselves, but our naming convention doesn't have dates)


How to repeat:
mysqldump --opts to dump out our entire database.

Suggested fix:
mysqldump --ignore-temp-tables 




-- 
http://bugs.mysql.com/42774


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

RE: There is something wrong with bugs.mysql.com email server I think (another one)

2010-08-02 Thread Daevid Vincent

And now I just got yet another email for a different bug from 2008!! What's
going on here? 

BTW, this bug is super annoying! How sad that in FOUR YEARS AND FOURTEEN
DAYS it is yet to be fixed. Seriously? This can't be that hard to fix, in
fact, it seems that Innodb would have to go out of it's way to be different
than the standard SQL logic.

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com] 
 Sent: Monday, August 02, 2010 12:35 PM
 To: 'MySQL List'
 Subject: There is something wrong with bugs.mysql.com email 
 server I think.
 
 I don't know if anyone from mysql is monitoring this list, but I think
 there is something goofy going on with your email servers. I 
 just now got
 this email below. Uhm. I wrote that bug over a year and a 
 half ago, and the
 reply by Susanne was the day after. WTF?
 
 ...better late than never I suppose, but maybe you should 
 look into your
 mail server Q and see if there are more stuck emails?! 

 

-Original Message-
From: Bug Database [mailto:do-not-re...@mysql.com] 
Sent: Sunday, August 01, 2010 4:04 AM
To: dae...@daevid.com
Subject: #21641 [Ver]: nonpreserving of InnoDB auto_increment values can
lead to unintended inheritance

Unsubscribe from updates to this bug at: http://bugs.mysql.com/21641

 Updated by:   Shane Bester
 Reported by:  Stephen Dewey
 Category: Server: InnoDB
 Severity: S4 (Feature request)
 Status:   Verified
-Version:  4.1.20
+Version:  4.1, 5.1, 5.5
-OS:   Linux
+OS:   Any
-OS Details:   Linux (Red Hat)
+OS Details:   
 Tags: auto_increment, innodb
 Defect Class: D5 (Feature request)

[20 Oct 2008 23:40] Mattias Jonsson

Closed bug#34077 as a duplicate of this.



[13 Feb 2008 23:29] Dave Latham

The current functionality is definitely not intuitive.  A persistent
auto-increment would be much appreciated!



[19 Dec 2007 22:59] Mike Schumacher

I agree that this is a critical problem that needs to be addressed. We
are having all kinds of issues related to it. What is the status of
this bug?



[4 Jun 2007 22:06] Dimitriy Alekseyev

What's the status of this feature request?



[8 May 2007 3:20] Daevid Vincent

This bug is critical and is causing us all kinds of grief with database
integrity. Please escalate it.

mysqladmin create INNODB_IS_BROKEN

locutus ~ # mysql INNODB_IS_BROKEN
mysql CREATE TABLE `foo` (
-   `id` int(10) unsigned NOT NULL auto_increment, 
-   `name` varchar(20),  
-   PRIMARY KEY  (`id`)
- ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql INSERT INTO foo values (null, 'a');
mysql INSERT INTO foo values (null, 'b');
mysql INSERT INTO foo values (null, 'c');

mysql SELECT * FROM foo;
++--+
| id | name |
++--+
|  1 | a| 
|  2 | b| 
|  3 | c| 
++--+
3 rows in set (0.00 sec)

mysql DELETE FROM foo;
Query OK, 3 rows affected (0.00 sec)

mysql QUIT;

locutus ~ # /etc/init.d/mysql restart
 * Stopping mysql ...
 * Starting mysql (/etc/mysql/my.cnf) 
 
locutus ~ # mysql INNODB_IS_BROKEN
mysql INSERT INTO foo values (null, 'd');
Query OK, 1 row affected (0.01 sec)

mysql SELECT * FROM foo;
++--+
| id | name |
++--+
|  1 | d| 
++--+
1 row in set (0.00 sec)



[28 Aug 2006 13:08] Valeriy Kravchuk

Thank you for a reasonable feature request.



Earlier comments can be viewed at http://bugs.mysql.com/21641


-- 
http://bugs.mysql.com/21641


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

RE: Why is MySQL always linked to PHP?

2010-07-16 Thread Daevid Vincent

 -Original Message-
 From: Jay Blanchard [mailto:jblanch...@pocket.com] 
 Sent: Friday, July 16, 2010 5:25 AM
 To: Michael Dykman; mysql
 Subject: RE: Why is MySQL always linked to Php?

 [snip]
 PHP applications are, for the most part, not that ambitious
 and mysql is simply the most accessible database with the best
 developed API.
 [/snip]

 I know that you said for the most part and you are 
 absolutely correct.
 I just want to point out that there are many corporations 
 relying on PHP
 and MySQL to deliver robust, scalable and enterprise capable
 applications each and every day.

 I suppose that is part of the appeal - low barriers to entry with
 infinite possibilities.

Yeah, I took a bit of offense to that original statement too.

I work for Panasonic Avionics, and we have no less than 12 servers here
running LAMP boxes in my department alone. They are in master/slave pairs.
We have almost 100GB of data with about 1 BILLION rows. Everytime an
aircraft lands, we get an offload about the (IFE) In Flight Entertainment
system. What movies are watched, for how long, what games were played, what
level reached, did the system reboot, was it commanded to reboot, all sorts
of things. We get about an offload per minute or so 24/7. These servers are
reliable and the SaaS we sell along with the IFE costs MILLIONS of dollars
per airline. The GUI is all PHP with some Python as the backend to parse
the offloads. We run Ubuntu 8.04LTS.

When I founded WildTangent, everything there was LAMP boxes. All the games
checked into a LAMP server. All the backend tools we created were LAMP.
They switched to IIS I believe a few years ago because I can only assume
that Alex St. John (the creator of DirectX) is a Microsoft guy, and our
game technology is based upon Microsoft tech, so it probably didn't look so
good to be running Linux for them. ;-)

At Lockdown Networks, we sold $50k rack mount units that were all LAMP
based with Ruby backends. These units secured many of the worlds networks
from banks to nuclear reactors to colleges to government/military agencies.

In all cases, we could have spent thousands to millions on Oracle servers
or used the free alternatives like Postgress. We researched and CHOSE to
use mySQL -- even paying the licensing fees at Lockdown over porting to
Postgress (which we seriously looked into)

So, I think someone needs to recognize that mySQL and PHP are extremely
powerful and robust tools and are used in VERY ambitious projects. ;-)

d

http://daevid.com

There are only 11 types of people in this world. Those that think binary
jokes are funny, those that don't, and those that don't know binary.

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

RE: [ANN] PBXT 1.5.02 Beta Released!

2010-07-16 Thread Daevid Vincent

 -Original Message-
 From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On 
 Behalf Of Johan De Meersman
 Sent: Friday, July 16, 2010 7:41 AM
 To: MySQL List
 Subject: Re: [ANN] PBXT 1.5.02 Beta Released!

 Can I somehow unsubscribe (that is opt-out , as opposed to the more
 civilised opt-in) from the myriad of commercial announcements 
 on this list,
 or do I just have to spambrand all senders ?

 Some weeks it feels as if there's more productspamming than 
 posting going on.

Interesting. I actually LIKE the announcements of new products related to
mySQL. 

Each new product (commercial or FOSS) makes mySQL that much more robust and
gives more credibility to our favorite RDBMS. 

Keep 'm comin guys!

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

RE: Why is MySQL always linked to Php?

2010-07-15 Thread Daevid Vincent

 -Original Message-
 From: alba.albetti [mailto:alba.albe...@libero.it] 
 Sent: Thursday, July 15, 2010 7:00 AM
 To: mysql
 Subject: Why is MySQL always linked to Php?

 Browsing the Web I've seen that usually companies look for 
 developers working on MySQL and Php. Why are the two things 
 linked? I mean I've not found any requests for just a MySQL 
 developer or DBA (as for example it happens for Oracle), but 
 it's always requested a MySQL/Php expert.
 I ask for it 'cause I've always been a developer/DBA on RDBMS 
 (Oracle and DB2) and as I've been learning MySQL for few 
 weeks, I'd like to know whether and why it's so important to 
 learn Php as well. It would be so difficult to find a job as 
 MySQL developer/DBA without knowing Php as well.
 Thanks!

I would say it's a combination of things.

MySQL is pretty much fire and forget for the most part for most sites.
Unless you're dealing with HUGE amounts of data or massive amounts of hits,
you rarely have to configure or DBA it. 

Oracle on the other hand is this huge mega-monstrosity that costs millions
of dollars to implement and therefore you want someone presumably trained
so they don't brick your box.

With mySQL, if you brick the box, you pretty much just setup a new LAMP
install and put your DB backups on.

I'm of course oversimplifying.

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

RE: finding exact query being run

2010-07-13 Thread Daevid Vincent

Well, you should be using a wrapper around your DB library class already
right? Like you don't use PEAR:DB directly, you have a wrapper class to
handle making a connection, doing the query, handling the error, etc.
Otherwise you're wasting a LOT of code/time. Not to mention it makes it
trivial to swap out the calls to another database library without chaning
all of YOUR code.

So all I did is wedge a logger/profiler into my sql_query() routine. This
has various levels of logging and saving the start/end times, etc. 

 -Original Message-
 From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On 
 Behalf Of Johan De Meersman
 Sent: Tuesday, July 13, 2010 1:17 AM
 To: Daevid Vincent
 Cc: Machiel Richards; mysql@lists.mysql.com
 Subject: Re: finding exact query being run
 
 Which will still not be particularly helpful for subsecond queries :-)
 
 Either turn on the full query log (beware of I/O 
 implications, use separate
 spindles if possible) or use mk-query-analyzer with tcpdump 
 to get a running
 analysis of what passes the line.
 
 The addition of tcpdump support is my all-time favourite 
 feature of maatkit
 :-D
 
 
 On Tue, Jul 13, 2010 at 12:05 AM, Daevid Vincent 
 dae...@daevid.com wrote:
 
  get mytop
 
 
   _
 
  From: Machiel Richards [mailto:machi...@rdc.co.za]
  Sent: Monday, July 12, 2010 8:40 AM
  To: mysql@lists.mysql.com
  Subject: finding exact query being run
 
 
 
  Hi All
 
 
 
 I am trying to find out how to see the exact 
 query being
  run.
 
 
 
 When running show processlit, I get a lot of 
 processes that
  have been running for a VERY long time.
 
 
 
 I a trying to find out exactly what query it 
 is that is
  being run , any ideas?
 
 
 
 I tried the slow query log but it is not 
 showing up in
  there.
 
 
 
 All the queries are showing sleep
 
 
 
 Not sure if this means anything
 
 
 
  Machiel Richards
 
  MySQL DBA
 
  Relational Database Consulting
 
 
 
  RDC_Logo
 
 
 
 
 
 
 -- 
 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: finding exact query being run

2010-07-12 Thread Daevid Vincent

get mytop

  _  

From: Machiel Richards [mailto:machi...@rdc.co.za] 
Sent: Monday, July 12, 2010 8:40 AM
To: mysql@lists.mysql.com
Subject: finding exact query being run

Hi All

I am trying to find out how to see the exact query being
run.

When running show processlit, I get a lot of processes that
have been running for a VERY long time.

I a trying to find out exactly what query it is that is
being run , any ideas?

I tried the slow query log but it is not showing up in
there.

All the queries are showing sleep

Not sure if this means anything

Machiel Richards

MySQL DBA

Relational Database Consulting 

RDC_Logo

RE: opening a server to generalized queries but not too far

2010-06-16 Thread Daevid Vincent

 

 -Original Message-
 From: Don Cohen [mailto:don-mysq...@isis.cs3-inc.com] 

 The http request I have in mind will be something like
  https://server.foo.com?user=johnpassword=wxyz;...
 and the resulting query something like
  select ... from table where user=john and ...
 (I will first have verified the password.)

For the love of God and all that is holy, 
do NOT put the user/pass on the URL like that!!

Do something like this instead:
http://us.php.net/manual/en/features.http-auth.php

Or use mod_auth_mysql to maintain your 'authorized' users to your page.

And as Adam beat me to, use a VIEW to expose ONLY the columns and joins you
want. This is also a good time to normalize the data and column names so
that you don't expose what their internal names are.

http://dev.mysql.com/doc/refman/5.0/en/create-view.html
http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views
http://www.devshed.com/c/a/MySQL/Views-and-More-in-MySQL-50/

But also has he said, I don't see what you're trying to accomplish. If
someone is technically literate to format SQL statements, then just give
them a read-only account to the mysql (or view) directly. Let them use
their own GUI tool like SQLYog or whatever -- it will be far more robust
than anything you can write yourself.

If you're trying to do some reports, then just code up the reports and
use select boxes for the options you want someone to choose. Use jQuery and
table_sorter plugin and you're done.


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

RE: opening a server to generalized queries but not too far

2010-06-16 Thread Daevid Vincent

 -Original Message-
 From: Don Cohen [mailto:don-mysq...@isis.cs3-inc.com] 
 Sent: Wednesday, June 16, 2010 2:48 PM
 To: Daevid Vincent
 Cc: mysql@lists.mysql.com
 Subject: RE: opening a server to generalized queries but not too far

 Daevid Vincent writes:

   For the love of God and all that is holy, 
   do NOT put the user/pass on the URL like that!!
 What's so unholy (or even unwise) about it?

Oh my goodness, where to begin...

Well barring the fact that it'll be in the user's cache and browser
history, you are sending this information in plain text across the
internet, open to ANYONE on the way who wishes to packet sniff.

   Or use mod_auth_mysql to maintain your 'authorized' 
 users to your page.
 Why is this so much better?
 In my case it's worse cause I don't want this to be interactive.
 I want to install something on a user's machine that will access his
 data without him having to do anything.  The url is to be used by
 a program, not a person on a browser.

One has nothing to do with the other. 

Mod_auth_mysql will serve as the user/password prompt and authentication
between the user and the page you're creating. It is just an easy way for
YOU to manage the users. If you have their user/password in a database --
presumably you already do, then leverage that. That's all this apache
module will do.

It's no different than you having to use $_GET['user'] and
$_GET['password'] from your URL, querying the DB and granting some data
back, except you've removed the horrific thought of forcing the user to
expose their vitals in plain text in the URL/GET/POST/whatever.

   And as Adam beat me to, use a VIEW to expose ONLY the 
 columns and joins you
   want. This is also a good time to normalize the data and 
 column names so
   that you don't expose what their internal names are.
 So far I don't like this solution.

Interesting. Perhaps you don't understand it? A view seems to suit your
needs very well from what you've explained. It allows a READ ONLY access to
your data. It prevents exposing your column/table names. It normalizes your
data so your customers get it without having to jump through hoops and
knowing intimate details about your schema. Pretty much this is why VIEWs
were invented.

   But also has he said, I don't see what you're trying to 
 accomplish. If
 I'm trying to let a large number of users each access his own data
 and not everyone else's in a very flexible way, in particular,
 allowing selection using functions, aggregations, filters.

Right. And for YOU to code all that up in some fancy web GUI is going to be
a nightmare and will never be all things to all people that you desire. So
you either give people a GUI front end with select boxes and parameters, OR
you give them some direct SQL access to a VIEW, OR you give them some
XML/JSON/Excel/CVS/whatever output that they can then pull into their tool
of choice to manipulate. Why not provide daily SQL dumps of their
normalized data to your users and let them run their reports -- if they're
trying to run SQL queries themselves?

   someone is technically literate to format SQL statements, 
 then just give
   them a read-only account to the mysql (or view) directly. 
 Let them use
   their own GUI tool like SQLYog or whatever -- it will be 
 far more robust
   than anything you can write yourself.
 In this case there may be a lot of users but the queries are likely to
 be written by a small number.

   If you're trying to do some reports, then just code up 
 the reports and
   use select boxes for the options you want someone to 
 choose. Use jQuery and
   table_sorter plugin and you're done.
 I can't predict what options will be needed.

Well you don't have to. You just make select boxes and such for each field
that someone would want to select on and various filters. I bet you can
cover 80% of your users with that if not all of them. 

 And this seems much easier.

Oh grasshopper... So Naïve. :)

Here's what I think will happen -- and you can tell me nah nah nah nah in
2 years when you finish writing this uber-tool that does all this fancy SQL
magic manipulation. It will either still fall short of what you wanted it
to do b/c it's too complicated to code, OR it will be too complicated to
use and nobody will understand it enough to use it.

But hey, more power to ya Don if you want to tackle it, go ahead. I would
suggest maybe looking at http://www.phpmyadmin.net 

Daevid.
http://daevid.com

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

RE: int(10) va int(11)

2010-06-14 Thread Daevid Vincent

AFAIK, the number in parenthesis is ONLY for display purposes in formatting
the size of the column in mySQL command line output, NOT the size of the
data that can be held.

I think they use (11) because unsigned will need one extra character for
the minus sign.

INT SIGNED = -2147483648 to 2147483647

Now this changes for things like a CHAR(2) where that *IS the column is
only going to handle 2 characters. Or VARCHAR(10) where that column will
handle from 0 to 10 characters. Same goes for FLOAT(7,4) which means 7
total digits and 4 of them are decimal places.

But for *INT columns, I don't think the same is true. It's cosmetic only.
Someone PLEASE correct me if I'm wrong. It's kind of silly if you ask me.
This one special case just adds confusion.

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Another extension is supported by MySQL for optionally specifying the
display width of integer data types in parentheses following the base
keyword for the type (for example, INT(4)). This optional display width may
be used by applications to display integer values having a width less than
the width specified for the column by left-padding them with spaces. (That
is, this width is present in the metadata returned with result sets.
Whether it is used or not is up to the application.)

The display width does not constrain the range of values that can be stored
in the column, nor the number of digits that are displayed for values
having a width exceeding that specified for the column. For example, a
column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to
32767, and values outside the range allowed by three characters are
displayed using more than three characters. 

 -Original Message-
 From: Ryan Chan [mailto:ryanchan...@gmail.com] 
 Sent: Monday, June 14, 2010 9:14 AM
 To: MySql
 Subject: int(10) va int(11)
 
 Assume MySQL int range (unsigned) is from 0 to 4294967295
 
 There are total 10 digits.
 
 Why a lot of tutorial in the web tell you to declare,
 
 e.g.
 
  CREATE TABLE t1  (f INT(11) UNSIGNED);
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=dae...@daevid.com
 


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

RE: list rows with no recent updates

2010-06-14 Thread Daevid Vincent

The only way I could think of is to have a column that's an auto updated
timestamp and then just query using that time.

`updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP

So for your mass update, I'd SET @updated_time = NOW(); and then you could
use that in your future query where @updated_time +/- some fuzzy amount of
seconds.

 -Original Message-
 From: MadTh [mailto:madan.feedb...@gmail.com] 
 Sent: Monday, June 14, 2010 2:02 PM
 To: mysql@lists.mysql.com
 Subject: list rows with no recent updates
 
 Hi,
 
 
 I ran a update command on around 2700 rows inside a mysql 
 database table
 which has around 3000 table rows to change the ( say)  price 
 of each item (
 with unique ID. unique product code).
 
 like:
 
 mysql UPDATE tbl_xyz  set listprice='9.45' where prod_id='3069' and
 prod_code='a0071';
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
 
 How can I list rows with no recent updates ( or the once 
 where the above
 updates were not done)  or say with no updates in last 2 hours?
 
 
 
 
 
 Thank you.
 


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

1 2 3 4 >

1 - 100 of 386 matches

Mail list logo