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

[Waynn Lue]

Percona's got a great reputation in the community, and I would have no
qualms using their builds.

On 11/18/10, Ewen Fortune  wrote:
> Daevid,
>
> On Thu, Nov 18, 2010 at 11:41 PM, Daevid Vincent  wrote:
>> 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?
>
> I work for Percona :o)
> So I think its best someone else chips in.
>
> Ewen
>
>>
>>> -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,
>>>
>>> 
>>> > 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=waynn...@gmail.com
>
>

-- 
Sent from my mobile device

__

Waynn Lue
626.429.6412 | waynn...@gmail.com
Facebook: www.facebook.com/waynn
LinkedIn: www.linkedin.com/in/waynn
__

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

my.cnf settings

[Waynn Lue]

I currently have a dedicated database server with 8 GBs of RAM and 8 1.60
GHz processors.  The tables on my databases are almost exclusively InnoDB,
except for 2-3 tables that are MyISAM and used for logging purposes (lots of
INSERT DELAYED statements).  I have the following settings in my my.cnf, and
I'm having trouble adjusting the innodb_buffer_pool_size to something
logical.  I first tried setting it to 6000M, but the server went OOM and
eventually crashed.  I've subsequently kept bringing it down, and now it's
at 4000M but it looks like swap is still being hit.

$ free -m
 total   used   free sharedbuffers cached
Mem:  7982   7943 38  0  8175
-/+ buffers/cache:   7759222
Swap: 1992702   1289

I spent some time looking at various Google links to figure out memory
usage, and what I'm confused by is how mysqld is still talking up 8388m of
virtual memory (according to top) and has 6.7g of physical memory used.

http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/
http://www.mysqlperformanceblog.com/2009/02/12/how-much-memory-can-mysql-use-in-the-worst-case/

What I'm trying to figure out is

1. Are there settings I should turn down for myisam or myisamchk, and is
that why I'm hitting 6.7GBs of actual memory?
2. Is 4000M the correct setting for innodb_buffer_pool_size?
3. Even if it is 6.7 GBs of memory, isn't 1.3 GBs of RAM (give or take) more
than enough to run the rest of the machine?  I don't see anything else
coming close to the memory footprint of mysql, and I'm not sure why swap is
still getting hit.

[mysqld]
#datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
max_connections = 320
safe-show-database
skip-locking
key_buffer = 192M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 32M
thread_concurrency = 8
wait_timeout = 15
innodb_buffer_pool_size=4000M
innodb_log_buffer_size=4M
#innodb_log_file_size=128M
#innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-error=/var/log/mysql/mysqlerror.log
#innodb_file_per_table
sql-mode="NO_AUTO_VALUE_ON_ZERO"

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
#no-auto-rehash
max_allowed_packet = 1M

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

Re: question regarding mysql database location

[Waynn Lue]

On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save <
manasi.s...@artificialmachines.com> wrote:

> Thanks Waynn,
>
> I could not get your point of using symlinks. Because as per my knowledge
> symlink will store same data which is there in original directory.
> and What do you mean by "The limit for files is significantly higher than
> > directories."
>
> Can you elaborate it more.
>
> Thanks in advance.
>
>
So assuming /var/lib/mysql/data/ is your mysql data directory, you could
create a new directory called /var/lib/mysql/data/data1, then move all the
directories from /var/lib/mysql/data/* into data1.  Then you could create a
symlink in /var/lib/mysql/data/ pointing to /var/lib/mysql/data/data1/.  When mysql tries to load the data directory, it follows the symlink
to the underlying directory (in /var/lib/mysql/data/data1).

Re: question regarding mysql database location

[Waynn Lue]

I fixed this by using symlinks for the directories for the underlying
databases. The limit for files is significantly higher than
directories.

Waynn

On 11/24/09, Manasi Save  wrote:
> Hi All,
>
> I have asked this question before But, I think I am not able to describe
> it better.
>
> Sorry for asking it again.
> I have multiple databases but there is a limit on the folders getting
> created in one folder.
>
> I have mysql default directory set as /var/lib/mysql/data.
> Now, After 32000 folder creation I am not able to create more folders than
> that. Well Its not like I want to create 32000 database's in it (Which I
> wanted to earlier :-P).
>
> for example - I want to create 10 databases but 5 in
> /var/lib/mysql/data/d1 to d5
> and othe 5 in /var/lib/mysql/data/d6 to d10.
>
> but I want to access all the databases that is d1-d10.
>
> as I ca change the database location after 5 databases but not able to
> access old five which I have created in old location.
>
>
> Please let me know if anymore information is needed on this. I am really
> looking for the solution. Please Help me.
> --
> Thanks and Regards,
> Manasi Save
> Artificial Machines Pvt Ltd.
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=waynn...@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: storage difference in VARCHAR(size)?

[Waynn Lue]

Oh, I wasn't seeing a difference, I just wanted to make sure I understood
how the two choices affected our system.

Thanks so much for all the answers!

On Wed, Nov 11, 2009 at 11:12 AM, Johan De Meersman wrote:

> Your mail suggests that you *are* seeing a difference, though. What
> are you seeing?
>
> On 11/11/09, Martijn Tonies  wrote:
> >>You have stumbled across the secret.  No, there is no difference at
> >>all as the calculations suggested here confirm.
> >>
> >>http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
> >
> > Note: as you can see in the above, CHAR data DOES take up room for it's
> > full size, stupidly enough.
> >
> > On Tue, Nov 10, 2009 at 6:37 PM, Waynn Lue  wrote:
> >> Hey all,
> >>
> >> I was building a table for storing email addresses today and ran into an
> >> issue that I couldn't find an answer for using Google. If I declare the
> >> column as a VARCHAR (this is an InnoDB table), does it matter what size
> I
> >> declare it as if it's between 1 and 255? I know there's an extra byte of
> >> storage once it goes above 255 because of the length, but is there a
> >> storage
> >> difference between VARCHAR(100) and VARCHAR(255)? Obviously there's a
> >> functional difference in what gets cut off when I try to store into that
> >> table, but is that the only difference?
> >
> > With regards,
> >
> > Martijn Tonies
> > Upscene Productions
> > http://www.upscene.com
> >
> > Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
> > Anywhere, MySQL, InterBase, NexusDB and Firebird!
> >
> > Database questions? Check the forum:
> > http://www.databasedevelopmentforum.com
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=waynn...@gmail.com
>
>

storage difference in VARCHAR(size)?

[Waynn Lue]

Hey all,

I was building a table for storing email addresses today and ran into an
issue that I couldn't find an answer for using Google.  If I declare the
column as a VARCHAR (this is an InnoDB table), does it matter what size I
declare it as if it's between 1 and 255?  I know there's an extra byte of
storage once it goes above 255 because of the length, but is there a storage
difference between VARCHAR(100) and VARCHAR(255)?  Obviously there's a
functional difference in what gets cut off when I try to store into that
table, but is that the only difference?

Thanks for any insight,
Waynn

Best RAID for a DB + LVM?

[Waynn Lue]

I currently have a RAID 5 setup for our database server.  Our space is
running out, so I'm looking to increase the disk space.  Since I'm doing
that anyway, I decided to re-evaluate our current disk array.  I was told
that RAID 5 isn't a good choice for databases since it's slower to write.
In addition, I've also been considering setting up LVM to take quick db
snapshots, after reading various links on the web (and posts to this list).

So on to the questions!  First, if that's what I eventually want to do (get
a new RAID server with LVM), do I need to do anything special to set up LVM
on the new system?  Second, what is a good RAID setup for databases?  RAID
10?  0+1?  Third, I have the choice of using SATA or SCSI in conjuction with
the RAID drives I choose.  How much of a difference is there in using SATA
instead of SCSI, especially in light of whatever RAID I end up going with?

Thanks for any insights,
Waynn

Re: --safe-updates and DELETEs

[Waynn Lue]

>
>  If I have a table like this:
>> CREATE TABLE `Test` (
>>  `TestId` bigint(20) default NULL
>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>>
>> Does using --safe-updates mean that it's impossible for me to ever delete
>> from it, since it doesn't have a key?  For example:
>>
>
>
> http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html#option_mysql_safe-updates
>

Yeah, that's the documentation I was looking at for my question.  It says

"You are not allowed to execute an
UPDATEor
DELETE  statement unless
you specify a key constraint in the WHERE clause or provide a LIMIT clause
(or both)."

Since there's no key on this table, does that mean it's impossible unless I
turn it off?

Thanks for any help!

Waynn

--safe-updates and DELETEs

[Waynn Lue]

If I have a table like this:
 CREATE TABLE `Test` (
  `TestId` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Does using --safe-updates mean that it's impossible for me to ever delete
from it, since it doesn't have a key?  For example:

INSERT INTO Test VALUES (1), (2);
mysql> DELETE FROM Test WHERE TestId = 1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a
table without a WHERE that uses a KEY column

Waynn

Re: Row before and after?

[Waynn Lue]

Yeah, so a UNION would work, would this solution be faster than using
a subquery (my instinct says yes) but thought I would ask. They both
execute fast on my system so it's hard to say under load.

Thanks,
Waynn

On 11/12/08, Dan Nelson <[EMAIL PROTECTED]> wrote:
> In the last episode (Nov 12), Waynn Lue said:
>> I'm trying to find the first row before and the first row after a specific
>> row.  Essentially I want to do these two queries, and get each row.
>>
>> SELECT * FROM Users WHERE UserId =  ORDER BY UserId DESC LIMIT 1;
>> SELECT * FROM Users WHERE UserId =  ORDER BY UserId LIMIT 1;
>>
>> Is there any way to combine this into one query?  OFFSET doesn't allow a
>> negative number, which is essentially what I want.
>
> How about joining them with a UNION?  Two queries for the round-trip
> cost of one!
>
> --
>   Dan Nelson
>   [EMAIL PROTECTED]
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

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

Re: Row before and after?

[Waynn Lue]

Whoops, just realized I made a mistake in the examples.  What I'm really
looking for is these two queries:

SELECT * FROM Users WHERE UserId > *userid*;
SELECT * FROM Users WHERE UserId < *userid*;

Waynn

On Wed, Nov 12, 2008 at 12:14 AM, Waynn Lue <[EMAIL PROTECTED]> wrote:

> I'm trying to find the first row before and the first row after a specific
> row.  Essentially I want to do these two queries, and get each row.
>
> SELECT * FROM Users WHERE UserId =  ORDER BY UserId DESC LIMIT 1;
> SELECT * FROM Users WHERE UserId =  ORDER BY UserId LIMIT 1;
>
> Is there any way to combine this into one query?  OFFSET doesn't allow a
> negative number, which is essentially what I want.
>
> Thanks,
> Waynn
>

Row before and after?

[Waynn Lue]

I'm trying to find the first row before and the first row after a specific
row.  Essentially I want to do these two queries, and get each row.

SELECT * FROM Users WHERE UserId =  ORDER BY UserId DESC LIMIT 1;
SELECT * FROM Users WHERE UserId =  ORDER BY UserId LIMIT 1;

Is there any way to combine this into one query?  OFFSET doesn't allow a
negative number, which is essentially what I want.

Thanks,
Waynn

"lost connection to mysql server during query" errors

[Waynn Lue]

We've started seeing mysql errors in the logs, and when i look at the output
of mysql_error() (in php), i get "lost connection to mysql server during
query". Here's an example stack trace:

'Can't connect to  database [Lost connection to MySQL server during
query]'

Similarly, we're seeing stack traces here as well:

'Can't connect to  database []'

I usually only see this mesasge when I don't use a connection for awhile and
it timeouts, but in this case, the connection is only opened for the
duration of a script, which can't be running for more than a second. The
mysql error logs don't show anything, and wait_timeout is set to 28800.

At first, I thought it was because I was calling mysql_select_db too much,
so I ended up using two mysql connections per page load, but that didn't
seem to change anything. How can we prevent this error from happening, what
else can I do to diagnose this further?  Google brings up some more
discussions about it, but nothing seems related to this, like packetsize.
This is happening when we select two ids from a database.  And SHOW
PROCESSLIST shows that the number of connections aren't even coming close to
max connections.

Thanks for any advice,
Waynn

Re: Picking the better query (join vs subselect)

[Waynn Lue]

Out of curiosity, is it generally faster to do a sub query or do it in
code for something like this.

Schema of Settings table, where the PK is (ApplicationId, SettingId):
ApplicationId, SettingId, SettingValue

Select SettingValue from Settings where SettingId = 10 and
ApplicationId IN (select ApplicationId from Settings where SettingId =
22 and SettingValue = "1");

The other solution is to do the two queries separately then do the
filtering in code.

What's generally faster?

Waynn



On 8/6/08, Rob Wultsch <[EMAIL PROTECTED]> wrote:
> On Wed, Aug 6, 2008 at 5:18 AM, Morten Primdahl <[EMAIL PROTECTED]> wrote:
>> I've tried to find out if joins are preferred over subselects, but am not
>> able to come to a definite conclusion. I read that correlated subselects
>> are
>> bad, and I should go for the join, but I know the id of the record in the
>> outer query and can hard code that into the subselect (if that makes a
>> difference).
>>
>
> Sub queries should be avoided if possible in MySQL.
>
>
> --
> Rob Wultsch
> [EMAIL PROTECTED]
> wultsch (aim)
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

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

Re: UPDATE rows based on multiple WHERE values?

[Waynn Lue]

Thanks, I'll take a look at that.
Appreciate the help,
Waynn
On Sat, Jul 12, 2008 at 9:02 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote:

>  On Sat, Jul 12, 2008 at 8:01 PM, Waynn Lue <[EMAIL PROTECTED]> wrote:
> > Is there any way to have an UPDATE statement change a column value based
> on
> > the WHERE statement?
> >
> > Essentially, I want to do something like this
> >
> > UPDATE Actions SET ActionsSent =  WHERE ActionsReceived = 
> >
> > where foo and bar change for multiple sets of values.  Is there a way to
> do
> > this in one SQL statement, or do I have to execute an UPDATE statement
> per
> > pair of values?
> >
> > Thanks,
> > Waynn
> >
>
> Take a look at CASE
> http://dev.mysql.com/doc/refman/5.0/en/case-statement.html
>
> mysql> CREATE TABLE `t2` (
>->   `col1` varchar(255) default NULL,
>->   `col2` varchar(255) default NULL
>-> );
> Query OK, 0 rows affected (0.13 sec)
>
> mysql> INSERT INTO t2 (col1,col2) VALUES(null, 'stuff'),(null,
> 'foo'),(null, 'bar');
> Query OK, 3 rows affected (0.05 sec)
> Records: 3  Duplicates: 0  Warnings: 0
>
> mysql> UPDATE t2 SET col1 = CASE  `col2`
>-> WHEN  'stuff' THEN 'blah'
>-> WHEN  'foo' THEN 'darn foo'
>-> ELSE 'not blah'
>-> END;
> Query OK, 3 rows affected (0.06 sec)
> Rows matched: 3  Changed: 3  Warnings: 0
>
> mysql> SELECT * FROM t2;
> +--+---+
> | col1 | col2  |
> +--+---+
> | blah | stuff |
> | darn foo | foo   |
> | not blah | bar   |
> +--+---+
> 3 rows in set (0.00 sec)
>
> --
> Rob Wultsch
> [EMAIL PROTECTED]
>

UPDATE rows based on multiple WHERE values?

[Waynn Lue]

Is there any way to have an UPDATE statement change a column value based on
the WHERE statement?

Essentially, I want to do something like this

UPDATE Actions SET ActionsSent =  WHERE ActionsReceived = 

where foo and bar change for multiple sets of values.  Is there a way to do
this in one SQL statement, or do I have to execute an UPDATE statement per
pair of values?

Thanks,
Waynn

Re: Schema Design

[Waynn Lue]

On Mon, Jul 7, 2008 at 4:20 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote:

> Yes, i definitly agree on the third table if "APPLICATION FUNCTIONALITY"
> increase and need to add more columns into the current tables.
>
> On 7/7/08, metastable <[EMAIL PROTECTED]> wrote:
>>
>> Ananda Kumar wrote:
>>
>>> I feel creating the third table would just be duplicating the data and
>>> occupying space. Creating index on existing table and quering would be
>>> better. But you got a good point of FOREIGN KEY CONSTRAINT. Can we use
>>> this
>>> constraint on current "SETTINGS" table, rather than creating the third
>>> table.
>>>
>> [snip]
>>
>>
>>
>> On the contrary, using the third table would eliminate all data
>> duplication, thus safeguarding data integrity and making maintenance easier
>> (think 'I want to change this setting description or make it a boolean in
>> stead of a text setting').  It will also use less space because of the
>> previous.
>>
>> Ofcourse, I don't see any settings description or setting types
>> limitations in your schema as it is now.
>> If that isn't an objection, you could create the foreign key constraint on
>> the existing table as such:
>>
>> ALTER TABLE Settings ADD UNIQUE KEY (applicationID)
>> ALTER TABLE Settings ADD FOREIGN KEY (applicationID) REFERENCES
>> Applications(applicationID) ON UDPATE CASCADE ON DELETE SET NULL
>>
>> (from the top of my head, something like this)
>> This implies that you are using InnoDB tables !
>>
>> I would however still create the third table. It will make your life much
>> easier when at some time in the future you decide to expand the
>> functionality and do indeed include limitations on the settings ('must be
>> boolean', 'must be any of green,red,blue', ...), to allow for validation and
>> such.
>>
>>
>> HTH
>>
>> Stijn
>>
>>
> Well, the other thing I'm somewhat concerned about is that the column type
is TEXT.  For some of the things I'm indexing, it's an INT, but I had to
create SettingValue as TEXT so that we could store text in it as well.  It
seems like a waste to index on text when the underlying value is really a
number.

Waynn

Re: Schema Design

[Waynn Lue]

On Mon, Jul 7, 2008 at 2:49 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote:

> >>Problem is that there are combinations that I'll never care about
>
> What do u mean by the above statement.
>

What I meant is that I will never do a query for (e.g.) (30, ).
So I have an index on values that I'll potentially never care about, and
that space of values could be rather large.  Out of 95 values (let's say
SettingId 1-95), I may only care to query about only 7 of them (let's say 1,
4, 10, 15, 16, 23, 29).  At some point, I feel like there's a tradeoff.

Thanks,
Waynn

Schema Design

[Waynn Lue]

I have two tables, Applications and Settings.  Here are the two schemas:

mysql> desc Applications;
+-+-+--+-+-++
| Field   | Type| Null | Key | Default |
Extra  |
+-+-+--+-+-++
| ApplicationId   | bigint(20) unsigned |  | PRI | NULL|
auto_increment |
| ApplicationName | varchar(200)| YES  | | NULL
||
+-+-+--+-+-+

mysql> desc Settings;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| ApplicationId | bigint(20) unsigned |  | PRI | 0   |   |
| SettingId | int(10) unsigned|  | PRI | 0   |   |
| SettingValue  | text| YES  | | NULL|   |
+---+-+--+-+-+---+

Settings is just a multi map, where I'll have things like (1, 1, "true"),
(1, 2, "short text"), (1, 3, "145"), etc.  Essentially each Application has
multiple rows in Settings.

There are values in Settings that I want to index, for example I want to
find all ApplicationIds where SettingId = 34 and SettingValue = "149951".
Here are the two methods I'm considering.

1.  Add an index on (SettingId, SettingValue).  It allows me to look for any
arbitrary combination of SettingId, SettingValue, and it also gives me
flexibility in that any thing I add to the table, it'll automatically be
indexed.  Problem is that there are combinations that I'll never care about,
so I'm wasting space and INSERT time.
2.  Pull the values that I'm interested in out into Applications, and add it
as a column that that table with a secondary index.  I'll have to update
both Applications and Settings in a transaction, and make sure they're kept
consistent.  This is more efficient, but every time I want to index
something new, I'll have to either take down Applications and add a new
column, or I'll have to create another table and INNER JOIN it to
Applications.

Does anyone have experience building out a system that's similar?  Am I
getting all the pros and cons?  Also, is a VARCHAR better than a text field
in terms of indexing, or even in terms of performance?

Thanks for any input,
Waynn

Re: Queues on MySQL?

[Waynn Lue]

>>Thanks for the suggestion--is that essentially using
>>http://www.php.net/posix_mkfifo within PHP?
>>
> [JS] Yes, that's what I had in mind. Be warned, though: I've never used this
> from PHP; and, although I found no references to this in the PHP
> documentation, I couldn't find the POSIX functions in a Windows
> installation.

Hm, I've spent some more time investigating this, and I'm not sure
it'll work quite as expected.  It looks like it's a blocking write to
the pipe, which means it'll block until a reader actually acts on the
pipe.

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

Re: Queues on MySQL?

[Waynn Lue]

Thanks for the suggestion--is that essentially using
http://www.php.net/posix_mkfifo within PHP?

On Mon, Jun 30, 2008 at 7:11 AM, Jerry Schwartz
<[EMAIL PROTECTED]> wrote:
>>-Original Message-----
>>From: Waynn Lue [mailto:[EMAIL PROTECTED]
>>Sent: Monday, June 30, 2008 6:04 AM
>>To: MySQL List
>>Subject: Queues on MySQL?
>>
>>Hey,
>>
>>I'm looking to write a queue backed by MySQL to enable batch updates
>>of a system I'm writing.  Essentially what I wanted to do was every
>>time I want to enqueue, I just INSERT a new row into a table.  Then I
> [JS] Just a bit of a warning: in my (very old) experience, this is not the
> most effective way of implementing a queue. Does your environment support
> fifo files, or can you emulate them? They have a lot less overhead.
>
>>have a cron job that runs every X minutes, selects all the existing
>>rows and does the actions, then deletes the rows that have been
>>selected.  It's a fairly simple algorithm, and not hard to implement,
>>but I'm wondering if there are existing tools out there that do queues
>>that will have additional functionality in case I ever want to change
>>it.
>>
>>A quick Google search turned up links like http://q4m.31tools.com/,
>>but I'm not sure what's a good one.
>>
>>Thanks,
>>Waynn
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>infoshop.com
>
>
>
>
>

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

Queues on MySQL?

[Waynn Lue]

Hey,

I'm looking to write a queue backed by MySQL to enable batch updates
of a system I'm writing.  Essentially what I wanted to do was every
time I want to enqueue, I just INSERT a new row into a table.  Then I
have a cron job that runs every X minutes, selects all the existing
rows and does the actions, then deletes the rows that have been
selected.  It's a fairly simple algorithm, and not hard to implement,
but I'm wondering if there are existing tools out there that do queues
that will have additional functionality in case I ever want to change
it.

A quick Google search turned up links like http://q4m.31tools.com/,
but I'm not sure what's a good one.

Thanks,
Waynn

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

Re: latin1 vs UTF-8

[Waynn Lue]

> > I assume I should check if my mysql has support for UTF-8,
> >
>
>  I believe it just has to be 4.1 or newer.  And, that's only necessary so
> you can get UTF-8 aware sorting and such.  You don't need any special
> support to just _store_ UTF-8 data.

Ah, that's actually the critical part.  I'm actually generating the
data myself through PHP, but I'm getting a weird ^A character when I
try to print it out in a textarea field.  I'm trying to figure out if
there's some weird interaction between htmlentities that's causing it
to be displayed strangely.  Can I trust that mysql is displaying the
text correctly on the command line tool if I have 4.1, even if the
charset is set to latin1?

Are there any caveats to using htmlentities that I'm missing?
Essentially I'm creating a form with a text area that allows people to
enter in values, then they can reload the form with the text area
pre-filled in for the id they stored it for.

Waynn

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

latin1 vs UTF-8

[Waynn Lue]

I'm trying to store the symbol (R) (that's the registered trademark
symbol) in my database, but I get a weird Ctrl-A (^A) character
whenever I try.  At first, I thought it was because I was calling
htmlentities without passing in "UTF-8" as the last argument, but that
only solved one of my problems.  Then I spent some time looking at
encodings, and I'm trying to figure out if the fact that the charset
is set to latin1 is the reason why.

Assuming it is, is there anything I can do to avoid having to dump the
database and recreate it with the other encoding?  I've spent some
time tonight looking on the web and at MySQL's documentation on
charsets and these are the options I've come up with.
alter table TABLE_NAME convert to character set utf8;
I'm assuming this is just a regular alter table, which means I'm going
to have to take down my server for the duration of the change, which
can take long periods of time.

http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html
This essentially is dump and recreate the db.

iconv
This was mentioned somewhere, but no one had a concrete implementation.

Also, are there any gotchas in doing this?  I assume I should check if
my mysql has support for UTF-8, that I need to issue SET NAMES 'utf8';
or put it into my.cnf, and that my php code needs to output the
headers with UTF-8 as well.

Thanks,
Waynn

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

Re: UPDATEs against one db affecting others

[Waynn Lue]

I'll do that, thanks.  (Sorry for the long response time, I was in
Dublin this past week...).

On Mon, Apr 21, 2008 at 3:16 PM, Wm Mussatto <[EMAIL PROTECTED]> wrote:
>
> On Mon, April 21, 2008 07:28, Waynn Lue wrote:
>  > Is there any way to prevent UPDATEs on one database on one mysql
>  > instance from affecting SELECTs on other databases in the same
>  > instance?  I'm noticing a whole bunch of backed up connections on
>  > other databases when I run massive UPDATEs on one.
>  >
>  > Thanks!
>  >
>  Are there any I/O or CPU load issues?
>  If you don't have any stored procedures that work between them, I'd check
>  server level limits.  MySQL is supposed to be multi-threaded to avoid just
>  this sort of problem, but if your disk and/or CPU are maxed out (or for
>  that matter you are out of memory) the whole system may be tied up.
>
>  Just my thoughts .
>
>  --
>  William R. Mussatto
>  Systems Engineer
>  http://www.csz.com
>  909-920-9154
>
>
>  --
>
> MySQL General Mailing List
>  For list archives: http://lists.mysql.com/mysql
>  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

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

UPDATEs against one db affecting others

[Waynn Lue]

Is there any way to prevent UPDATEs on one database on one mysql
instance from affecting SELECTs on other databases in the same
instance?  I'm noticing a whole bunch of backed up connections on
other databases when I run massive UPDATEs on one.

Thanks!

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

Re: InnoDB Hot Backup

[Waynn Lue]

Oh, and a followup question that I forgot to ask--what if the two
systems have different db schemas?  Is it possible to do some sort of
mapping between the two?

On Fri, Mar 21, 2008 at 1:48 PM, Waynn Lue <[EMAIL PROTECTED]> wrote:
> I'm thinking of buying a license for this tool to do a migration from
>  one server to another, but it's been hard for me to find good
>  documentation on it after searching for awhile last night.  Is it
>  possible for me to migrate from a 32-bit to a 64-bit system across a
>  network?  How long would perceived downtime be?
>
>  Thanks for any help,
>  Waynn
>

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

InnoDB Hot Backup

[Waynn Lue]

I'm thinking of buying a license for this tool to do a migration from
one server to another, but it's been hard for me to find good
documentation on it after searching for awhile last night.  Is it
possible for me to migrate from a 32-bit to a 64-bit system across a
network?  How long would perceived downtime be?

Thanks for any help,
Waynn

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

PRIMARY KEY and CreationTime columns

[Waynn Lue]

Say I have this schema

CREATE TABLE temp (
  EntityId BIGINT AUTO_INCREMENT PRIMARY KEY,
  CreationTime DEFAULT NOW()
);

Now let's say I want to find all rows created within the last 24 hours.  If I do

select * from temp where CreationTime > DATE_SUB(NOW(), INTERVAL 24 HOUR)

that's going to do a full table scan to find out, even though there's
already an implicit ordering in EntityId (this is of course assuming I
don't manually set CreationTime to something else).  Is there any way
to take advantage of the fact that there's a primary key index on
entityId, or do I have to put a secondary index on CreationTime?

Thanks,
Waynn

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

Re: mysqldump on specific columns only?

[Waynn Lue]

Thanks for the responses, Inline:

On Sun, Mar 9, 2008 at 7:49 PM, Tim McDaniel <[EMAIL PROTECTED]> wrote:
> > On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue <[EMAIL PROTECTED]> wrote:
>  >> Subject: Re: mysqldump on specific columns only?
>
> >> I'm trying to dump all the columns of a table but one in a
>  >> restructuring of my schema.
>
>  By "dump", I assume you mean "output", not "delete".

Indeed, I meant "output."

>  I suspect I'm too inexperienced in MySQL or I don't understand the
>  question, because I don't see why you haven't mentioned
>  ALTER TABLE DROP COLUMN.  Or, if you don't want to touch the original
>  database but rather a copy database: mysqldump, load it into another
>  database, ALTER TABLE DROP COLUMN.  It's true that you wrote
>
>
>  >> since the files are going to be rather big.
>
>  The second notion has the disadvantage that the data will be copied
>  three times (mysqldump, load, ALTER TABLE).  On the other hand, you
>  have the full reliability of mysqldump, with its care in copying
>  CREATE TABLEs and data correctly.
>
>  Am I perhaps misunderstanding?

ALTER TABLE unfortunately takes longer than dumping and recreating the
tables.  I started a previous thread on this, and the consensus was
recreating everything was faster, and I'm taking advantage of this
downtime to restructure our schema, and add a few other columns that
will be needed.  The initial impetus was expanding an INT to a BIGINT,
but in the presence of a foreign key.  So that would require dropping
the FK, expanding the INT to BIGINT in two tables, then recreating the
FK.  I would also want to add three more columns while I'm at it.  And
in this case, the cost of all those operations is significantly higher
than just dumping all the tables, truncating/dropping the tables,
recreating the tables, then importing.

>  On Sun, 9 Mar 2008, Rob Wultsch <[EMAIL PROTECTED]> wrote:
>  > I would probably use [SELECT] INTO OUTFILE. Any particular reason it
>  > does not work?
>
>  It's not well-suited for "restructuring of my schema".  mysqldump
>  emits SQL that can be used immediately to create the table with
>  exactly the same data types and column names and keys and other
>  properties (like NOT NULL), and to insert data into columns with
>  quoting and escaping as needed, and nice little flourishes like DROP
>  TABLE IF EXISTS, LOCK TABLES, UNLOCK TABLES.

I'm actually not going to be using the exact same schema anyway, so
SELECT INTO does work, but ... let me continue this thought below.

>  > mysql> select c1,c2 from t INTO OUTFILE 'outfile';
>  > Query OK, 4 rows affected (0.00 sec)
>  >
>  >
>  > outfile contents:
>  > 1 2
>  > 4 5
>  > 7 8
>  > 1011
>
>  That provides only the data to insert.  Further, it would require
>  considerable massaging to get it into the form of INSERT statements,
>  though for all I know MySQL provides other mechanisms to load that
>  sort of data.  But those are tab-separated columns: what if one of the
>  columns were a text field with a literal tab?

MySQL does provide LOAD DATA INFILE
(http://dev.mysql.com/doc/refman/5.0/en/load-data.html), but I was
hoping to do it through mysqldump instead of SELECT INTO because I
assumed the performance was faster.  If that's not the case, though,
I'll go ahead and use SELECT INTO OUTFILE and LOAD DATA INFILE.  I
guess most any character is fine as a delimiter, since my fields
should all just be IDs...

Thanks,
Waynn

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

mysqldump on specific columns only?

[Waynn Lue]

I'm trying to dump all the columns of a table but one in a
restructuring of my schema.  I found this post:
http://www.jsw4.net/info/listserv_archives/mysql/05-wk49/msg00131.html
which seems to indicate that this isn't possible, but I was wondering
if anyone had any suggestions.  My current solution is to do a simple
search and replace, but that won't work for everything.  I could
always do regex search/replace, as well, but I'm hoping for something
native to mysqldump, since the files are going to be rather big.

Thanks,
Waynn

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

Re: Can't drop database that shows up in show databases

[Waynn Lue]

This problem actually started after I moved the datadir to another
folder, so that's quite possible. We stopped everything, rsynced the
folders over, then restarted mysql. But I do notice a permissions
problem, since the owner currently is root. I'll chown it to mysql
then see what happens.

I'll post back with results.

Thanks for all the advice,
Waynn



On 3/8/08, Baron Schwartz <[EMAIL PROTECTED]> wrote:
> Hi,
>
> On Sat, Mar 8, 2008 at 6:58 AM, Waynn Lue <[EMAIL PROTECTED]> wrote:
> > SHOW DATABASES; shows that I have a database called "test", but when I
> >  call "DROP DATABASE test", I get
> >
> >  ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist
> >
> >  When I run any queries on that database, I get errors like:
> >
> >  mysql> select * from Users;
> >  ERROR 1017 (HY000): Can't find file: './test/Users.frm' (errno: 13)
>
> Whenever you see "errno: X", run perror and see what the error is:
>
> [EMAIL PROTECTED]:~$ perror 13
> OS error code  13:  Permission denied
>
> That is why other posters are suggesting it might be a permissions
> problem.  It sounds to me like there might be an InnoDB problem mixed
> in with this: did you delete any .frm files (or databases) from the
> filesystem without first dropping the InnoDB files that use them?
> InnoDB will complain about this if you did.
>
> >
> >  My previous solution of deleting the ./test folder worked only
> >  temporarily, but restarting mysqld seemed to recreate it.  Anyone have
> >  ideas on how to drop this database?
> >
> >  --
> >  MySQL General Mailing List
> >  For list archives: http://lists.mysql.com/mysql
> >  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>

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

Can't drop database that shows up in show databases

[Waynn Lue]

SHOW DATABASES; shows that I have a database called "test", but when I
call "DROP DATABASE test", I get

ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist

When I run any queries on that database, I get errors like:

mysql> select * from Users;
ERROR 1017 (HY000): Can't find file: './test/Users.frm' (errno: 13)

My previous solution of deleting the ./test folder worked only
temporarily, but restarting mysqld seemed to recreate it.  Anyone have
ideas on how to drop this database?

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

Re: SELECT causing connections to back up?

[Waynn Lue]

In response to an email offlist, I forgot to specify that these are
InnoDB tables.

On Mon, Mar 3, 2008 at 2:53 PM, Waynn Lue <[EMAIL PROTECTED]> wrote:
> I've been noticing strange load spikes on our mysql machine, throwing
>  back the dreaded "max connections error", even though the value is set
>  to 500.  I'm wondering if this is related to an hourly script I run
>  that does a few somewhat-db intensive queries.  The script runs a
>  query that groups by Users to find out who's been the most active
>  across our system.  Would that cause other connections to the database
>  to back up?  I just saw it happen, so I killed the query and
>  everything returned to normal pretty quickly, but I'm somewhat
>  confused, because it looked like connections to other databases were
>  being backed up as well.  One other weird thing, if I'm doing a
>  complex SELECT from a table, and something else is INSERTing into that
>  same table, the INSERT is taking awhile, even though there shouldn't
>  be any contention since I'm not doing SELECT FOR UPDATE.
>
>  Any ideas?
>

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

SELECT causing connections to back up?

[Waynn Lue]

I've been noticing strange load spikes on our mysql machine, throwing
back the dreaded "max connections error", even though the value is set
to 500.  I'm wondering if this is related to an hourly script I run
that does a few somewhat-db intensive queries.  The script runs a
query that groups by Users to find out who's been the most active
across our system.  Would that cause other connections to the database
to back up?  I just saw it happen, so I killed the query and
everything returned to normal pretty quickly, but I'm somewhat
confused, because it looked like connections to other databases were
being backed up as well.  One other weird thing, if I'm doing a
complex SELECT from a table, and something else is INSERTing into that
same table, the INSERT is taking awhile, even though there shouldn't
be any contention since I'm not doing SELECT FOR UPDATE.

Any ideas?

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

Re: User Preferences?

[Waynn Lue]

Whoops, finished my thought too early.  I was going to append, should
we also have UserId, PreferenceId be the PRIMARY KEY?  Then possibly a
secondary index on UserId in case I ever want to get all preferences
for a user?

On Sun, Mar 2, 2008 at 6:18 PM, Waynn Lue <[EMAIL PROTECTED]> wrote:
> But if you're using a varchar for the purposes of getting a list, you
>  might as well store it in a more compact format like int or SET.  The
>  only benefit (which shouldn't be ignored) is that you have a
>  human-readable string to parse, right?
>
>  It seems like most people ended up with a normalized format with
>  UserPreferences, then?  Have people generally found any performance
>  problems with that?  I'd imagine it would be something like
>
>  UserId, PreferenceId, PreferenceSetting
>
>  Where PreferenceSetting might just be another int, that could take all
>  possible settings of that preference.
>
>
>
>  On Fri, Feb 29, 2008 at 8:41 AM, Curtis Maurand <[EMAIL PROTECTED]> wrote:
>  >
>  > I think that I'd set up a varchar column and store a tab separated list in
>  > it.
>  >
>  > Then parse it  upon retrieval.
>  >
>  > Curtis
>  >
>  >
>  >
>  >
>  > Dan Buettner wrote:
>  > > Waynn, I've used both schemes 1 and 2 as you describe, and in my
>  > > experience
>  > > 2 is the best way to go. It's easy to scale up as you add users and
>  > > settings, and it's easy to make changes if the meaning of settings should
>  > > change (i.e. you need to do a backend change to people's settings).
>  > >
>  > > #1 is harder to make those kind of back end updates on, and harder for
>  > > someone troubleshooting to make sense of the data.
>  > >
>  > > #3 may not scale well - you would end up having to track too many tables,
>  > > I
>  > > think.
>  > >
>  > > What I'm doing in my current project is using a data model that has a
>  > > method
>  > > for each preference setting, and returns a sensible value by default if
>  > > the
>  > > user has no pref set for a given lookup key; otherwise, I return what the
>  > > user has set. This means adding a method every time I add a preference
>  > > setting, which on the one hand means adding code - on the other hand,
>  > > chances are very high that if I am adding the ability for a user to set a
>  > > preference, I'm already adding code somewhere to ensure that preference
>  > > has
>  > > an effect.
>  > >
>  > > HTH,
>  > > Dan
>  > >
>  > >
>  > >
>  > > On Thu, Feb 28, 2008 at 9:50 AM, Waynn Lue <[EMAIL PROTECTED]> wrote:
>  > >
>  > >> I'm looking for a good way to store user preferences. The most
>  > >> straightforward way is just to add a column to the Users table for
>  > >> each preference we're looking to store. Downside is that it requires
>  > >> an ALTER TABLE which gets prohibitively expensive as it gets larger,
>  > >> as it's fairly inflexible. I've come up with a few alternatives, and
>  > >> I'm wondering if people have ideas or suggestions, as this has to be a
>  > >> common problem. A quick Google search didn't turn up anything.
>  > >>
>  > >> 1. Store the preferences as a binary blob on the Users table. This
>  > >> blob could be either a blob, or an integer that I use application
>  > >> logic to read/write from, or I could use the SET datatype.
>  > >> 2. Store the preferences in normalized form, with a new table called
>  > >> UserPreferences that has UserId, Setting, Preference and we add a row
>  > >> for each setting of that user.
>  > >> 3. Create a separate table each time we want to add a new setting,
>  > >> UserId, WhateverTheNameOfThePreferenceIs.
>  > >>
>  > >> Anyone have any experience with this, or better suggestions?
>  > >>
>  > >> Thanks,
>  > >> Waynn
>  > >>
>  > >> --
>  > >> MySQL General Mailing List
>  > >> For list archives: http://lists.mysql.com/mysql
>  > >> To unsubscribe:
>  > >> http://lists.mysql.com/[EMAIL PROTECTED]
>  > >>
>  > >>
>  > >
>  > >
>  >
>  >
>  > --
>  > Curtis Maurand
>  > Head Honcho
>  > Xyonet Hosting Services
>  > Biddeford, ME 04005
>  > mailto:[EMAIL PROTECTED]
>  > mailto:[EMAIL PROTECTED]
>

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

Re: User Preferences?

[Waynn Lue]

But if you're using a varchar for the purposes of getting a list, you
might as well store it in a more compact format like int or SET.  The
only benefit (which shouldn't be ignored) is that you have a
human-readable string to parse, right?

It seems like most people ended up with a normalized format with
UserPreferences, then?  Have people generally found any performance
problems with that?  I'd imagine it would be something like

UserId, PreferenceId, PreferenceSetting

Where PreferenceSetting might just be another int, that could take all
possible settings of that preference.

On Fri, Feb 29, 2008 at 8:41 AM, Curtis Maurand <[EMAIL PROTECTED]> wrote:
>
> I think that I'd set up a varchar column and store a tab separated list in
> it.
>
> Then parse it  upon retrieval.
>
> Curtis
>
>
>
>
> Dan Buettner wrote:
> > Waynn, I've used both schemes 1 and 2 as you describe, and in my
> > experience
> > 2 is the best way to go. It's easy to scale up as you add users and
> > settings, and it's easy to make changes if the meaning of settings should
> > change (i.e. you need to do a backend change to people's settings).
> >
> > #1 is harder to make those kind of back end updates on, and harder for
> > someone troubleshooting to make sense of the data.
> >
> > #3 may not scale well - you would end up having to track too many tables,
> > I
> > think.
> >
> > What I'm doing in my current project is using a data model that has a
> > method
> > for each preference setting, and returns a sensible value by default if
> > the
> > user has no pref set for a given lookup key; otherwise, I return what the
> > user has set. This means adding a method every time I add a preference
> > setting, which on the one hand means adding code - on the other hand,
> > chances are very high that if I am adding the ability for a user to set a
> > preference, I'm already adding code somewhere to ensure that preference
> > has
> > an effect.
> >
> > HTH,
> > Dan
> >
> >
> >
> > On Thu, Feb 28, 2008 at 9:50 AM, Waynn Lue <[EMAIL PROTECTED]> wrote:
> >
> >> I'm looking for a good way to store user preferences. The most
> >> straightforward way is just to add a column to the Users table for
> >> each preference we're looking to store. Downside is that it requires
> >> an ALTER TABLE which gets prohibitively expensive as it gets larger,
> >> as it's fairly inflexible. I've come up with a few alternatives, and
> >> I'm wondering if people have ideas or suggestions, as this has to be a
> >> common problem. A quick Google search didn't turn up anything.
> >>
> >> 1. Store the preferences as a binary blob on the Users table. This
> >> blob could be either a blob, or an integer that I use application
> >> logic to read/write from, or I could use the SET datatype.
> >> 2. Store the preferences in normalized form, with a new table called
> >> UserPreferences that has UserId, Setting, Preference and we add a row
> >> for each setting of that user.
> >> 3. Create a separate table each time we want to add a new setting,
> >> UserId, WhateverTheNameOfThePreferenceIs.
> >>
> >> Anyone have any experience with this, or better suggestions?
> >>
> >> Thanks,
> >> Waynn
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> >> http://lists.mysql.com/[EMAIL PROTECTED]
> >>
> >>
> >
> >
>
>
> --
> Curtis Maurand
> Head Honcho
> Xyonet Hosting Services
> Biddeford, ME 04005
> mailto:[EMAIL PROTECTED]
> mailto:[EMAIL PROTECTED]

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

User Preferences?

[Waynn Lue]

I'm looking for a good way to store user preferences.  The most
straightforward way is just to add a column to the Users table for
each preference we're looking to store.  Downside is that it requires
an ALTER TABLE which gets prohibitively expensive as it gets larger,
as it's fairly inflexible.  I've come up with a few alternatives, and
I'm wondering if people have ideas or suggestions, as this has to be a
common problem.  A quick Google search didn't turn up anything.

1.  Store the preferences as a binary blob on the Users table.  This
blob could be either a blob, or an integer that I use application
logic to read/write from, or I could use the SET datatype.
2.  Store the preferences in normalized form, with a new table called
UserPreferences that has UserId, Setting, Preference and we add a row
for each setting of that user.
3.  Create a separate table each time we want to add a new setting,
UserId, WhateverTheNameOfThePreferenceIs.

Anyone have any experience with this, or better suggestions?

Thanks,
Waynn

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

Re: Re-creating tables

[Waynn Lue]

The problem here though is that there is no MyTable.  There are two
separate tables, Users and Actions, and I can't alter both of them in
the same statement, as far as I know.  As a result, when I alter just
Users, that fails because there's an FK between Users and Actions and
the type of the two columns is now different.

On Mon, Feb 25, 2008 at 7:50 AM, mos <[EMAIL PROTECTED]> wrote:
> At 05:16 PM 2/24/2008, Waynn Lue wrote:
>  >That's actually why I'm dropping/recreating, because I thought the
>  >changes I have to make require multiple statements.  Let me know if
>  >that's a wrong assumption, here's what I have to do.
>  >
>  >1.  drop two foreign keys from Users to Actions (in the previous
>  >example I gave).
>  >2.  expand INT to BIGINT on Users
>  >3.  expand INT to BIGINT on Actions
>  >4.  recreate two foreign keys from Users to Actions.
>  >
>  >That's four alter statements, which each require making temporary
>  >table copies, so I assumed dropping/recreating was faster.
>
>  Each of your Alter statements will mean a temp table is created, the data
>  is moved over, the changes are made, and the indexes are rebuilt.
>  It will be 4x faster if you do it all in one Alter statement. Since the
>  alter statement will rebuild the keys at the end, is there really a need to
>  to drop the foreign keys or is this an InnoDb quirk?
>
>  Try something like:
>
>  alter table MyTable change column Users Users BigInt, change column Actions
>  Actions BigInt;
>
>  You normally would drop indexes to speed things up when loading a lot of
>  data into the table, then rebuild the indexes after the data has been
>  loaded. But since Alter table does this anyways, you're not accomplishing
>  anything by doing it manually.
>
>  Mike
>
>
>
>
>  >On Sat, Feb 23, 2008 at 2:42 PM, mos <[EMAIL PROTECTED]> wrote:
>  > >
>  > > At 05:55 AM 2/23/2008, Waynn Lue wrote:
>  > >  >I have three or four different ALTER TABLE commands I need to run on a
>  > >  >9 million row table (related to the previous email I sent).  I've
>  > >  >tried running it before and it just takes way too long, so I was
>  > >  >thinking the fastest way to get this done is to create new tables with
>  > >  >the final schema, then drop the old tables and rename the new ones.
>  > >  >
>  > >  >There are a few ways to go about this.
>  > >  >
>  > >  >1. Stop the reads/writes to the db.  Use mysqldump, truncate the
>  > >  >tables, drop the tables, recreate with the correct schema, then import
>  > >  >it again.
>  > >  >2. Create a new temporary table, keep the reads and writes going,
>  > >  >SELECT into that new table, when it catches up, turn off the
>  > >  >reads/writes for a short period of time while I truncate/drop then
>  > >  >rename the temporary table.
>  > >  >3. Use replication somehow to go from the old table to the new table
>  > >  >(can I do that?).
>  > >  >4. Create a new temporary table, stop reads/writes to it, then do an
>  > >  >INSERT INTO SELECT from the old to new table.
>  > >  >
>  > >  >One slight problem with choice 2 is that I don't know how to make sure
>  > >  >that I know when the reads/writes are done.  Not all the tables have
>  > >  >an auto-increment id, so I can't just keep inserting in random ids.
>  > >  >As an aside, if I do INSERT INTO SELECT, does it block any operations
>  > >  >on the table that I'm SELECTing from?
>  > >  >
>  > >  >Thanks for any insights,
>  > >  >Waynn
>  > >
>  > >  Waynn,
>  > >  Why are you using 3 or 4 alter table commands on the same table? 
> Each
>  > >  command means it will create a copy of the table, makes the changes to
>  > >  that, then it renames it to the correct table name and deletes the old
>  > >  table name. You should be able to add all 4 alter table commands in 1
>  > Alter
>  > >  Table statement, just by putting a "," between the alter specifications.
>  > >  See the syntax in the manual:
>  > >  http://dev.mysql.com/doc/refman/5.0/en/alter-table.html This means the
>  > >  table gets rebuilt only once and not 4 times!
>  > >
>  > >  Mike
>  > >
>  > >  --
>  > >  MySQL General Mailing List
>  > >  For list archives: http://lists.mysql.com/mysql
>  > >  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>  > >
>  > >
>
>  --
>
>
> MySQL General Mailing List
>  For list archives: http://lists.mysql.com/mysql
>  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

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

Re: SELECT ascending incremental values

[Waynn Lue]

That worked perfectly, thanks!

On Fri, Feb 22, 2008 at 4:49 AM, C.R.Vegelin <[EMAIL PROTECTED]> wrote:
> Hi Waynn,
>
>  Try:
>  SET @row := 0;
>  SELECT @row := @row + 1 AS Rank,
>
> UserId, count(*) as NumActions from Actions group
> by UserId order by NumActions desc limit 10;
>
>  HTH, Cor
>
>
>
>  - Original Message -
>  From: "Waynn Lue" <[EMAIL PROTECTED]>
>  To: 
>  Sent: Friday, February 22, 2008 10:38 AM
>  Subject: SELECT ascending incremental values
>
>
>  > I'm doing a top 10 list from a database based on a group by and order
>  > by, something like
>  >
>  > select UserId, count(*) as NumActions from Actions group
>  > by UserId order by NumActions desc limit 10;
>  >
>  > Is there any way to put a separate column so I get results like
>  > Rank, UserId, NumActions
>  > 1, 123, 43
>  > 2, 9844, 40
>  > 3, 88, 34
>  > 4, 144240, 20
>  > etc.?
>  >
>  > Waynn
>  >
>  > --
>  > MySQL General Mailing List
>  > For list archives: http://lists.mysql.com/mysql
>  > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>  >
>  >
>
>
>

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

Re: Re-creating tables

[Waynn Lue]

That's actually why I'm dropping/recreating, because I thought the
changes I have to make require multiple statements.  Let me know if
that's a wrong assumption, here's what I have to do.

1.  drop two foreign keys from Users to Actions (in the previous
example I gave).
2.  expand INT to BIGINT on Users
3.  expand INT to BIGINT on Actions
4.  recreate two foreign keys from Users to Actions.

That's four alter statements, which each require making temporary
table copies, so I assumed dropping/recreating was faster.

On Sat, Feb 23, 2008 at 2:42 PM, mos <[EMAIL PROTECTED]> wrote:
>
> At 05:55 AM 2/23/2008, Waynn Lue wrote:
>  >I have three or four different ALTER TABLE commands I need to run on a
>  >9 million row table (related to the previous email I sent).  I've
>  >tried running it before and it just takes way too long, so I was
>  >thinking the fastest way to get this done is to create new tables with
>  >the final schema, then drop the old tables and rename the new ones.
>  >
>  >There are a few ways to go about this.
>  >
>  >1. Stop the reads/writes to the db.  Use mysqldump, truncate the
>  >tables, drop the tables, recreate with the correct schema, then import
>  >it again.
>  >2. Create a new temporary table, keep the reads and writes going,
>  >SELECT into that new table, when it catches up, turn off the
>  >reads/writes for a short period of time while I truncate/drop then
>  >rename the temporary table.
>  >3. Use replication somehow to go from the old table to the new table
>  >(can I do that?).
>  >4. Create a new temporary table, stop reads/writes to it, then do an
>  >INSERT INTO SELECT from the old to new table.
>  >
>  >One slight problem with choice 2 is that I don't know how to make sure
>  >that I know when the reads/writes are done.  Not all the tables have
>  >an auto-increment id, so I can't just keep inserting in random ids.
>  >As an aside, if I do INSERT INTO SELECT, does it block any operations
>  >on the table that I'm SELECTing from?
>  >
>  >Thanks for any insights,
>  >Waynn
>
>  Waynn,
>  Why are you using 3 or 4 alter table commands on the same table? Each
>  command means it will create a copy of the table, makes the changes to
>  that, then it renames it to the correct table name and deletes the old
>  table name. You should be able to add all 4 alter table commands in 1 Alter
>  Table statement, just by putting a "," between the alter specifications.
>  See the syntax in the manual:
>  http://dev.mysql.com/doc/refman/5.0/en/alter-table.html This means the
>  table gets rebuilt only once and not 4 times!
>
>  Mike
>
>  --
>  MySQL General Mailing List
>  For list archives: http://lists.mysql.com/mysql
>  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

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

Re-creating tables

[Waynn Lue]

I have three or four different ALTER TABLE commands I need to run on a
9 million row table (related to the previous email I sent).  I've
tried running it before and it just takes way too long, so I was
thinking the fastest way to get this done is to create new tables with
the final schema, then drop the old tables and rename the new ones.

There are a few ways to go about this.

1. Stop the reads/writes to the db.  Use mysqldump, truncate the
tables, drop the tables, recreate with the correct schema, then import
it again.
2. Create a new temporary table, keep the reads and writes going,
SELECT into that new table, when it catches up, turn off the
reads/writes for a short period of time while I truncate/drop then
rename the temporary table.
3. Use replication somehow to go from the old table to the new table
(can I do that?).
4. Create a new temporary table, stop reads/writes to it, then do an
INSERT INTO SELECT from the old to new table.

One slight problem with choice 2 is that I don't know how to make sure
that I know when the reads/writes are done.  Not all the tables have
an auto-increment id, so I can't just keep inserting in random ids.
As an aside, if I do INSERT INTO SELECT, does it block any operations
on the table that I'm SELECTing from?

Thanks for any insights,
Waynn

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

SELECT ascending incremental values

[Waynn Lue]

I'm doing a top 10 list from a database based on a group by and order
by, something like

select UserId, count(*) as NumActions from Actions group
by UserId order by NumActions desc limit 10;

Is there any way to put a separate column so I get results like
Rank, UserId, NumActions
1, 123, 43
2, 9844, 40
3, 88, 34
4, 144240, 20
etc.?

Waynn

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

Re: Expanding a field leading to FK violations

[Waynn Lue]

Hm, ok.  Then one more followup question, if dropping the FKs is
necessary, is there any way to figure out how long such an operation
will take?  SHOW PROCESSLIST usually just has "copying to tmp table"
or some such, does SHOW INNODB STATUS or any other commands give
insight into how long it will take?

Thanks again,
Waynn

On Feb 19, 2008 5:11 PM, Baron Schwartz <[EMAIL PROTECTED]> wrote:
> Hi Wayne,
>
>
> On Feb 19, 2008 4:52 PM, Waynn Lue <[EMAIL PROTECTED]> wrote:
> > I'm trying to change the definition of a field and getting 1025 errors
> > when I try.  Here's a simplified reproducible test case, on MySQL 5.
> >
> > CREATE TABLE `Users` (
> >  `UserId` int(11) NOT NULL,
> >  PRIMARY KEY  (`UserId`)
> > ) ENGINE=InnoDB;
> >
> > CREATE TABLE `Actions` (
> >  `ActionId` int(11) NOT NULL auto_increment,
> >  `FromUserId` int(11) NOT NULL,
> >  `ToUserId` int(11) NOT NULL,
> >  PRIMARY KEY  (`ActionId`),
> >  KEY `FromUserId` (`FromUserId`),
> >  KEY `ToUserId` (`ToUserId`),
> >  CONSTRAINT `Actions_ibfk_1` FOREIGN KEY (`FromUserId`) REFERENCES
> > `Users` (`UserId`),
> >  CONSTRAINT `Actions_ibfk_2` FOREIGN KEY (`ToUserId`) REFERENCES
> > `Users` (`UserId`)
> > ) ENGINE=InnoDB;
> >
> > mysql> ALTER TABLE Users MODIFY UserId BIGINT NOT NULL;
> > ERROR 1025 (HY000): Error on rename of './test/#sql-1d11_9c0fb' to
> > './test/Users' (errno: 150)
> >
> > This is happening regardless of whether I first do SET
> > FOREIGN_KEY_CHECKS=0; or not, and whether I define it as a PK in the
> > ALTER TABLE statement.  One way around it would be to drop the FKs
> > first, but if there's a lot of data, I'm assuming that will be
> > prohibitively slow.  Especially since I'm just going to put it back up
> > again.
> >
> > Anyone have ideas on how to get the ALTER TABLE statement working?
>
> Unfortunately you really can't do what you're trying to do without
> dropping the FK and then putting it back :-(
>
> Baron
>

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

Expanding a field leading to FK violations

[Waynn Lue]

I'm trying to change the definition of a field and getting 1025 errors
when I try.  Here's a simplified reproducible test case, on MySQL 5.

CREATE TABLE `Users` (
 `UserId` int(11) NOT NULL,
 PRIMARY KEY  (`UserId`)
) ENGINE=InnoDB;

CREATE TABLE `Actions` (
 `ActionId` int(11) NOT NULL auto_increment,
 `FromUserId` int(11) NOT NULL,
 `ToUserId` int(11) NOT NULL,
 PRIMARY KEY  (`ActionId`),
 KEY `FromUserId` (`FromUserId`),
 KEY `ToUserId` (`ToUserId`),
 CONSTRAINT `Actions_ibfk_1` FOREIGN KEY (`FromUserId`) REFERENCES
`Users` (`UserId`),
 CONSTRAINT `Actions_ibfk_2` FOREIGN KEY (`ToUserId`) REFERENCES
`Users` (`UserId`)
) ENGINE=InnoDB;

mysql> ALTER TABLE Users MODIFY UserId BIGINT NOT NULL;
ERROR 1025 (HY000): Error on rename of './test/#sql-1d11_9c0fb' to
'./test/Users' (errno: 150)

This is happening regardless of whether I first do SET
FOREIGN_KEY_CHECKS=0; or not, and whether I define it as a PK in the
ALTER TABLE statement.  One way around it would be to drop the FKs
first, but if there's a lot of data, I'm assuming that will be
prohibitively slow.  Especially since I'm just going to put it back up
again.

Anyone have ideas on how to get the ALTER TABLE statement working?

Thanks,
Waynn

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