Re: characters oddity

[Steven Siebert]

If you insert new records (or update existing) containing correct UTF-8
data to the production database using a mysql client, does the data save
correctly?

Could it be your data source for the production database has/is incorrectly
handling the charset prior to the data being stored in the production
database?
Is there any chance that your production tables weren't originally set to
the correct charset for the data contained within, then it was changed?
This can cause problems unless done in a manner expected by MySQL
.

On Wed, Mar 2, 2016 at 11:29 AM, McGranahan, Jamen <
jamen.mcgrana...@vanderbilt.edu> wrote:

> Have two virtual machines, both running RedHat 7. Both are also running
> MySQL 5.6.29 and both have the same data. We have two databases, however,
> that keep throwing odd characters on one system but it's OK on the other
> and we've not been able to figure out why.
>
> What it should look like (from the test machine, pointing to the database
> on the test machine):
> Décimas a la censura de Carmen Aristegui
> Guillermo Velázquez Benavidez
>
> What it looks like on our Production database (from the test machine,
> pointing to the production database):
> Décimas a la censura de Carmen Aristegui
> Guillermo Velázquez Benavidez
>
> We have verified the my.cnf is the same on both machines, using utf8 as
> the default character set. We have also verified the character sets for the
> databases and tables are identical. We know it has to be something with the
> MySQL database on our Production server because we can point Production to
> the Test database and it the characters are translated correctly. But we
> just haven't been able to figure out what it is - and it's been 48 hours
> worth of work and investigation. Any advice, guidance, or suggestions would
> be greatly appreciated!  Thank you!
>
> Jamen McGranahan
> Systems Services Librarian
> Vanderbilt University LIbrary
> Central Library
> Room 811
> 419 21st Avenue South
> Nashville, TN 37214
>
>

Re: dump, drop database then merge/aggregate

[Steven Siebert]

Ah, ok, if I understand correctly within this context every record in the
one table _should_ have a unique identifier.  Please verify this is the
case, though, if for example the primary key is an auto increment what I'm
going to suggest is not good and Really Bad Things will, not may, happen.

If you want to do this all in MySQL, and IFF the records are ensured to be
*globally unique*, then what I suggested previously would work but isn't
necessary (and is actually dangerous if global record uniqueness is not
definite).  Uou _could_ do a standard mysqldump (use flags to do data only,
no schema) and on the importing server it will insert the records and if
there are duplicates records they will fail. If there is a chance the
records aren't unique, or if you want to be extra super safe (good idea
anyway), you can add triggers on the ingest server to ensure
uniqueness/capture failures and record them in another table for analysis
or perhaps even to immediate data remediation (update key) and do insert.

Now, for me, using triggers or other business-logic-in-database features is
a code smell.  I loath putting business logic in databases as they tend to
be non-portable and are hard to troubleshoot for people behind me that is
expecting to have logic in code.  Since you're having to script this
behavior out anyway, if it were me I would dump the data in the table to
CSV or similar using INSERT INTO OUTFILE rather than mysqldump, ship the
file, and have a small php script on cron or whatever ingest it, allowing
for your business logic for data validate/etc to be done in code (IMO where
it belongs).

S



On Mon, Feb 29, 2016 at 12:12 PM, lejeczek <pelj...@yahoo.co.uk> wrote:

> On 29/02/16 16:32, Steven Siebert wrote:
>
>> What level of control do you have on the remote end that is
>> collecting/dumping the data?  Can you specify the command/arguments on how
>> to dump?  Is it possible to turn on binary logging and manually ship the
>> logs rather than shipping the dump, effectively manually doing
>> replication?
>>
> in an overview it's a simple php app, a form of a questionnaire that
> collects user manual input, db backend is similarly simple, just one table.
> Yes I can operate mysqldump command but nothing else, I do not have
> control over mysql config nor processes.
>
> It's one of those cases when for now it's too late and you are only
> thinking - ough... that remote box, if compromised would be good to have
> only a minimal set of data on it.
>
> So I can mysqldump any way it'd be best and I'd have to insert ideally not
> replacing anything, instead aggregating, adding data.
> I think developers took care of uniqueness of the rows, and constructed it
> in conformity with good design practices.
>
> What I'm only guessing is when I lock, dump and remove then insert,
> aggregate could there be problems with keys? And no data loss during
> dump+removal?
>
> thanks for sharing your thoughts.
>
>
>> I agree with others, in general this approach smells like a bad idea.
>> However, updating data from a remote system in batch is quite common,
>> except often it's done at the application level polling things like web
>> services and perhaps some business logic to ensure integrity is
>> maintained.  Attempting to do it within the constructs of the database
>> itself is understandable, but there are risks when not adding that "layer"
>> of logic to ensure state is exactly as you expect it during a merge.
>>
>> At risk of giving you too much rope to hang yourself: if you use mysqldump
>> to dump the database, if you use the --replace flag you'll convert all
>> INSERT statements to REPLACE, which when you merge will update or insert
>> the record, effectively "merging" the data.  This may be one approach you
>> want to look at, but may not be appropriate depending on your specific
>> situation.
>>
>> S
>>
>>
>>
>> On Mon, Feb 29, 2016 at 11:12 AM, lejeczek <pelj...@yahoo.co.uk> wrote:
>>
>> On 29/02/16 15:42, Gary Smith wrote:
>>>
>>> On 29/02/2016 15:30, lejeczek wrote:
>>>>
>>>> On 28/02/16 20:50, lejeczek wrote:
>>>>>
>>>>> fellow users, hopefully you experts too, could help...
>>>>>>
>>>>>> ...me to understand how, and what should be the best practice to dump
>>>>>> database, then drop it and merge the dumps..
>>>>>> What I'd like to do is something probably many have done and I wonder
>>>>>> how it's done best.
>>>>>> A box will be dumping a database (maybe? tables if it's better) then
>>>>>> dropping (purging the data) it and on a different sys

Re: dump, drop database then merge/aggregate

[Steven Siebert]

Totally with you, I had to get up and wash my hands after writing such
filth =)

On Mon, Feb 29, 2016 at 12:14 PM, Gary Smith <li...@l33t-d00d.co.uk> wrote:

> On 29/02/2016 16:32, Steven Siebert wrote:
>
>>
>> At risk of giving you too much rope to hang yourself: if you use
>> mysqldump to dump the database, if you use the --replace flag you'll
>> convert all INSERT statements to REPLACE, which when you merge will update
>> or insert the record, effectively "merging" the data.  This may be one
>> approach you want to look at, but may not be appropriate depending on your
>> specific situation.
>>
>> I'd considered mentioning this myself, but this was the root of my
> comment about integrity - if the original database or tables are dropped,
> then the replace command will cause the data to poo all over the original
> dataset. As you mentioned in your (snipped) reply, this can go badly wrong
> in a short space of time without the correct controls in place. Even if
> they are in place, I'd have trouble sleeping at night if this were my
> circus.
>
> Gary
>

Re: dump, drop database then merge/aggregate

[Steven Siebert]

What level of control do you have on the remote end that is
collecting/dumping the data?  Can you specify the command/arguments on how
to dump?  Is it possible to turn on binary logging and manually ship the
logs rather than shipping the dump, effectively manually doing replication?

I agree with others, in general this approach smells like a bad idea.
However, updating data from a remote system in batch is quite common,
except often it's done at the application level polling things like web
services and perhaps some business logic to ensure integrity is
maintained.  Attempting to do it within the constructs of the database
itself is understandable, but there are risks when not adding that "layer"
of logic to ensure state is exactly as you expect it during a merge.

At risk of giving you too much rope to hang yourself: if you use mysqldump
to dump the database, if you use the --replace flag you'll convert all
INSERT statements to REPLACE, which when you merge will update or insert
the record, effectively "merging" the data.  This may be one approach you
want to look at, but may not be appropriate depending on your specific
situation.

S



On Mon, Feb 29, 2016 at 11:12 AM, lejeczek  wrote:

> On 29/02/16 15:42, Gary Smith wrote:
>
>> On 29/02/2016 15:30, lejeczek wrote:
>>
>>> On 28/02/16 20:50, lejeczek wrote:
>>>
 fellow users, hopefully you experts too, could help...

 ...me to understand how, and what should be the best practice to dump
 database, then drop it and merge the dumps..
 What I'd like to do is something probably many have done and I wonder
 how it's done best.
 A box will be dumping a database (maybe? tables if it's better) then
 dropping (purging the data) it and on a different system that dump swill be
 inserted/aggregated into the same database.
 It reminds me a kind of incremental backup except for the fact that
 source data will be dropped/purged on regular basis, but before a drop, a
 dump which later will be used to sort of reconstruct that same database.

 How do you recommend to do it? I'm guessing trickiest bit might this
 reconstruction part, how to merge dumps safely, naturally while maintaining
 consistency & integrity?
 Actual syntax, as usually any code examples are, would be best.

 many thanks.


 I guess dropping a tables is not really what I should even consider -
>>> should I just be deleting everything from tables in order to remove data?
>>> And if I was to use dumps of such a database (where data was first
>>> cleansed then some data was collected) to merge data again would it work
>>> and merge that newly collected data with what's already in the database
>>>
>> This sounds like a remarkably reliable way to ensure no data integrity.
>> What exactly are you trying to achieve? Would replication be the magic word
>> you're after?
>>
>> I realize this all might look rather like a bird fiddling with a worm
> instead of lion going for quick kill. I replicate wherever I need and can,
> here a have very little control over one end.
> On that end with little control there is one simple database, which data
> I'll need to be removed on regular basis, before removing I'll be dumping
> and I need to use those dumps to add, merge, aggregate data to a database
> on the other end, like:
> today both databases are mirrored/identical
> tonight awkward end will dump then remove all the data, then collect some
> and again, dump then remove
> and these dumps should reconstruct the database on the other box.
>
> Pointers on what to pay the attention to, how to test for consistency &
> integrity, would be of great help.
>
>
> Gary
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: can I just encrypt tables? what about the app?

[Steven Siebert]

Simple answer is no. What are you trying to accomplish?

S

On Mon, Feb 29, 2016 at 8:31 AM, lejeczek  wrote:

> hi everybody
>
> a novice type of question - having a php + mysql, can one just encrypt
> (internally in mysql) tables and php will be fine?
> If not, would it be easy to re-code php to work with this new, internal
> encryption?
>
> thanks.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

[Steven Siebert]

The error is stating that your innodb log sequence is higher that that of
the actual data files...any chance your data partition is full after your
restore?

On Fri, Jan 22, 2016 at 3:23 PM, Neil Tompkins  wrote:

> Hi,
>
> Hoping someone can help me identify why I keep having to restore my
> database.  You can see below, that my machine shut down normally, yet when
> I restarted the machine back up, I'm getting the error
> 'mysql\innodb_table_stats.ibd. Cannot open tablespace' and am having to
> delete the current database and restore my backup.
>
> Here is my Error log file.
>
> 2016-01-22 18:06:28 2540 [Note] MySQL: Normal shutdown
>
> 2016-01-22 18:06:28 2540 [Note] Giving 0 client threads a chance to die
> gracefully
> 2016-01-22 18:06:28 2540 [Note] Event Scheduler: Purging the queue. 0
> events
> 2016-01-22 18:06:28 2540 [Note] Shutting down slave threads
> 2016-01-22 18:06:28 2540 [Note] Forcefully disconnecting 0 remaining
> clients
> 2016-01-22 18:06:28 2540 [Note] Binlog end
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'partition'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_SYS_TABLESPACES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_SYS_FOREIGN_COLS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_SYS_TABLESTATS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_INDEX_TABLE'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_INDEX_CACHE'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_BEING_DELETED'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_DELETED'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_DEFAULT_STOPWORD'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_METRICS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_BUFFER_POOL_STATS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_BUFFER_PAGE_LRU'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_CMP_PER_INDEX_RESET'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP_RESET'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCKS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_TRX'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'InnoDB'
> 2016-01-22 18:06:31 2540 [Note] InnoDB: FTS optimize thread exiting.
> 2016-01-22 18:06:31 2540 [Note] InnoDB: Starting shutdown...
> 2016-01-22 19:43:48 2556 [Note] Plugin 'FEDERATED' is disabled.
> 2016-01-22 19:43:48 2556 [Warning] option 'innodb-autoextend-increment':
> unsigned value 67108864 adjusted to 1000
> 2016-01-22 19:43:48 a0c InnoDB: Warning: Using
> innodb_additional_mem_pool_size is DEPRECATED. This option may be removed
> in future releases, together with the option innodb_use_sys_malloc and with
> the InnoDB's internal memory allocator.
> 2016-01-22 19:43:48 2556 [Note] InnoDB: The InnoDB memory heap is disabled
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Mutexes and rw_locks use Windows
> interlocked functions
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Compressed tables use zlib 1.2.3
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Not using CPU crc32 instructions
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Initializing buffer pool, size =
> 1.0G
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Completed initialization of buffer
> pool
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Highest supported file format is
> Barracuda.
> 2016-01-22 19:43:48 2556 [Note] InnoDB: The log sequence numbers 1600607
> and 1600607 in ibdata files do not match the log sequence number 154136116
> in the ib_logfiles!
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Database was not shutdown normally!
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Starting crash recovery.
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Reading tablespace information from
> the .ibd files...
> 2016-01-22 19:43:48 2556 [ERROR] 

Re: XML to RDB

[Steven Siebert]

Larry,


If that's the only type of queries you're gonna make, sounds like MySQL can
be a good solution (from an indexing perspective, those queries will be
fine).  Basically, you would be using MySQL as a read-only copy of the XML
content...so your goal here is to understand how you'll be needing to
query, and build the best model (in MySQL) you can to achieve these
queries.  Going back to your initial question, I don't believe there is an
organic MySQL tool (or even a 3rd party tool that I know about) that will
do this for you - you'll probably have to write something, very small, to
do this.  Not sure how much programming experience you have...but something
like a simple SAX parser producing XML element and attribute rows that
you (probably batch) insert into the schema you make will work quite
easily...with very little code.

Be careful with the schema, especially the IDs.  The schema suggested by
Johan will work in simple cases, but might become a problem with unbounded
elements and if you wanted to break out XML arrays, for example (ie more
than one instance of a node's ID).  If you need to preserve sequence, that
might be something you need to consider too.  If you don't foresee the need
to query the parent hierarchy, I wouldn't add it (no parent_id)...not only
would it not be necessary, you have to consider how you want to query
hierarchy...if you need to, checked out the Nested Set model as opposed to
the adjacent list (see h
ttp://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/).
Again, this all depends on what you need...but based on the queries you
indicated, you wouldn't need it.

Good luck!

S




On Mon, Nov 25, 2013 at 4:29 AM, Johan De Meersman vegiv...@tuxera.bewrote:

  ___
 | Nodes |
 |---|
 | id| ---
 | [more fields] |   |
 | parent_id | --
 |___|


 And then you join the table with itself as needed. Do note that you'll
 need a self-join for every level you query, and that every self-join
 incrementally slows down the query.

 It may be beneficial (actually, I'm pretty sure it will be :-) ) to
 implement the recursion in software as necessary, simply follow the branch
 you want and check wether there are children at each turn; or pick up the
 child and go back up until parent_id is NULL.

 Depending on what you want/need, you could also add a depth field, so you
 don't have to loop to figure out where you are in the tree, etc.

 Storing node x/y/z as an identifier, as you suggest, is also an option
 with it's own benefits and downsides; nothing prevents you from taking any
 or all of those approaches at the same time.


 - Original Message -
  From: Larry Martell larry.mart...@gmail.com
  To: Steven Siebert smsi...@gmail.com
  Cc: mysql mailing list mysql@lists.mysql.com
  Sent: Friday, 22 November, 2013 3:17:44 PM
  Subject: Re: XML to RDB
 
  Yes, I will need to query the data, and yes, it's app specific to the
 data.
  The parent-node structure will meet my needs if I can figure out how to
  preserver the unbound multi level nodes. There will be a higher level
  table, which will have the file name, date received, and other
 identifying
  info that will be start of the query. The nodes will have a foreign key
  back to the file table. The queries will be something like get the abcd
  parameter from node x/y/z from the file foo.xml from 10/10/13. I guess I
  can just store the x/y/z in the node table (as opposed to trying to
  represent z is child of y which is a child of x in 3 different tables or
  rows.
 

 --
 Unhappiness is discouraged and will be corrected with kitten pictures.

Re: XML to RDB

[Steven Siebert]

Hi Larry,

I'm trying to figure out what your wanting to do with the data once its in
mysql?  At first it seemed you didn't want to put it in as a lob because
you might want to query on the data in different ways (kind of an
assumption on my part, but a common reason to do this).  Then, you thought
about making a very generic schema (parent-node) which really doesn't lend
itself well to this goal.

Could you explain what you plan to do with the data in mysql and maybe a
little about the goal of the application?  Is this app specific to this
data, or are you looking to build an app that can take any schema defined
XML file, ingest it, and allow you to do some work with it?

Why is a lob not appropriate for your needs?

S
On Nov 22, 2013 7:24 AM, Larry Martell larry.mart...@gmail.com wrote:

 I have a need to store data that comes in an XML file in MySQL. I don't
 want to store the entire file as a CBLOB, and it doesn't lend itself to
 loading with Load XML. I am looking for tools that will help me create the
 schema and parse and load the data. I have googled for this and I've found
 many scholarly papers written about it, but the examples are always with
 very simple XML files, and I haven't found any tools. I do have the xsd
 schema for the XML file, but what's confusing me about how to design the
 RDB schema is the unbounded recursion of the data. The data is basically
 nodes that have parameters which have items with values. My first thought
 was to simply have a node table and a parameter table with a foreign key
 back to the node it belongs to. But when digging into the data I found that
 nodes can have sub nodes - in one file I have this goes down for 7 levels,
 but in theory there is no bound on that. I'm now sure how to best represent
 that.

 Any experiences, advice, or pointers are very welcome.

 Thanks-
 larry

Re: Session ID Generation

[Steven Siebert]

Hartmut/Denis - Great information, thank you!  I was unaware that mysql
bound the session id to the socket in such a way that it would not permit
that session id to be provided on other socket.  This was the missing piece.

Hartmut - if the session Id is not a meaningful part of the client/server
protocol, is the session managed my the transport layer rather than the app
layer?  If the TCP connection is lost...is the effectively session over and
can not be re-established on another socket?  In a mysql client sense, I
would need to re-establish a connection and set my session variables again
rather than just reconnect using the session ID from the dropped
connection?

I apologize about these basic mysql-mechanics questions - I need to satisfy
our auditors, so I need to understand =)

Thanks,

S

On Fri, Jun 21, 2013 at 7:13 AM, Hartmut Holzgraefe hart...@skysql.comwrote:

 On 21.06.2013 12:48, Steven Siebert wrote:

  You stated these IDs are sequential...do you know if there is any way to
  modify this to utilize a random generation?  Sequential session IDs are
  an avenue to session hijacking.

 as a MySQL client session is bound to a specific TCP connection ... how
 would being able to predict a session ID help with hijacking that TCP
 session? Even more so as the session ID is not really part of the
 communication protocol between client and server at all and more like
 an identifier for SHOW PROCESSLIST (that would most likely be visible
 to an internal attacker anyway) and KILL (which requires SUPER
 privileges on the database anyway, and at that point you've already
 lost to an attacker ...)

 --
 Hartmut Holzgraefe hart...@skysql.com
 Principal Support Engineer (EMEA)
 SkySQL AB - http://www.skysql.com/

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

Re: Session ID Generation

[Steven Siebert]

Great, thanks to all.

I don't mean to defend our auditors, because they are a PITA, but they do
appear to be decently knowledgeable in general - but they aren't, not can
they be expected to, be specific application-level experts - otherwise, the
number of auditors we would be required to hire would be cost
prohibitive...there is a necessary balance =)  Just because MySQL
implements this way (and, obviously is concious of these security
concerns), doesn't mean the latest NoSQL solution deployed to github,
written in python during a cocaine fuelled weekend, does...they aren't here
to say no to whatever software I desire to use, they just need to
verify.  So, really, the wand of ignorance should be pointed in my
direction =)

This leads me to my final question: is this documented anywhere beyond the
source code and this thread?  I was specifically searching for session id
generation, but clearly this search was too narrow. I'll look more
generally for how MySQL establishes connections and maintains sessions -
but if you happen to know where it might be document off the top of your
head, I would appreciate it.

Thanks again for everyone's insightful and quite helpful responses.

S



On Fri, Jun 21, 2013 at 7:58 AM, Denis Jedig d...@syneticon.net wrote:

 Steven,

 Am 21.06.2013 13:35, schrieb Steven Siebert:


  If the TCP connection is lost...is the effectively session over and
 can not be re-established on another socket?


 Yes.


  In a mysql client sense, I
 would need to re-establish a connection and set my session variables again
 rather than just reconnect using the session ID from the dropped
 connection?


 Yes. There is no way for a client to specify a desired session ID. The
 session ID is only used once - the server notifies the client of the ID
 used in the initial handshake upon connection establishment, even before
 authentication is attempted. Take a look at the docs for protocol details:

 http://dev.mysql.com/doc/**internals/en/connection-phase.**
 html#plain-handshakehttp://dev.mysql.com/doc/internals/en/connection-phase.html#plain-handshake
 


  I apologize about these basic mysql-mechanics questions - I need to
 satisfy
 our auditors, so I need to understand =)


 The auditors should know their trade and not simply try pressing
 requirements they've read about in an IT manager magazine.

 Denis


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

Re: Session ID Generation

[Steven Siebert]

Tanks for responding Johan.

I am indeed looking for MySQL session ID's, not an HTTP session ID.  I'm
doing a defense in depth audit and reviewing potential threats to each
remote connection - in this case session fixation.  I know I can set
various session timeout properties that help mitigate fixation and
hijacking, but a randomly generated server-only generated session id goes a
log way to mitigate the risk.  Just a note, we are following industry best
practices utilizing a DMZ...but out biggest threat is an insider, so we
need to realize any potential risk.

You stated these IDs are sequential...do you know if there is any way to
modify this to utilize a random generation?  Sequential session IDs are
an avenue to session hijacking.

Thanks,

S

On Fri, Jun 21, 2013 at 2:40 AM, Johan De Meersman vegiv...@tuxera.bewrote:

 Mysql assigns its session IDs sequentially as they come in. I suspect,
 however, that you're looking for session IDs as used by websites
 -generation of those is entirely not a mysql issue, it is only a potential
 store for them.


 Steven Siebert smsi...@gmail.com wrote:

 Hello all,

 I've looked though, what I believe to be, the relevant areas in the MySQL
 docs  as well as standard search engine searches without luck.  I was
 hoping to find some documentation that would tell me:

  - how MySQL session Ids are generated (specifically, are they considered
 random)
  - does MySQL require session ids sent from the client to be server
 generated (ie the client can't make one up and that is used for the session)

  - is there any other relevant security protections or concerns for mysql
 session management that would be of interest?

 Thanks,

 Steve


 --
 Sent from Kaiten Mail. Please excuse my brevity.

Session ID Generation

[Steven Siebert]

Hello all,

I've looked though, what I believe to be, the relevant areas in the MySQL
docs  as well as standard search engine searches without luck.  I was
hoping to find some documentation that would tell me:
 - how MySQL session Ids are generated (specifically, are they considered
random)
 - does MySQL require session ids sent from the client to be server
generated (ie the client can't make one up and that is used for the session)
 - is there any other relevant security protections or concerns for mysql
session management that would be of interest?

Thanks,

Steve

RE: [Possible Spam]Php programmer

[Steven Staples]

 -Original Message-
 From: ratlhaga...@yahoo.com [mailto:ratlhaga...@yahoo.com]
 Sent: September 18, 2012 7:31 AM
 To: mysql@lists.mysql.com
 Subject: [Possible Spam]Php programmer
 
  Hi,
 
 I need help, I'm working on a project that need time query in Mysql. I
want
 to display a time in php page, that took mysql to execute 100 000 records
 Sent from my BlackBerryR wireless device
 -
 S naMlgi
 oltrishp/s.s.mylTuucb  t/isylomq
 

Taken from the PHP website:

?php
function microtime_float()
{
list($usec, $sec) = explode( , microtime());
return ((float)$usec + (float)$sec);
}

$time_start = microtime_float();

# do your query here

$time_end = microtime_float();
$time = $time_end - $time_start;

echo It took $time seconds to do whatever you just did\n;
?

Steve.


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

RE: function INTERVAL in view

[Steven Staples]

 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: September 17, 2012 3:04 PM
 To: peter.braw...@earthlink.net; mysql@lists.mysql.com
 Subject: RE: function INTERVAL in view
 
 INTERVAL is a keyword.  This is probably the root of the hiccup.
 Is that your Stored Function?  Or UDF?
 
 on 2012-09-17 12:58 PM, h...@tbbs.net wrote:
  My MySQL is of version 5.5.8-log. I find I cannot save a query with
  INTERVAL in a view: redundant round brackets are added. If the query is
 
  SELECT INTERVAL(1, 2, 3, 4)
 
  within the frm file there is the expression
 
  interval((1, 2, 3, 4))
 
  which is wrong.
 
  What is known about this?


It is also a function:
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_in
terval

as for why it does that?  I have no idea.


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

RE: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Steven Staples]

 -Original Message-
 From: Andrés Tello [mailto:mr.crip...@gmail.com]
 Sent: May 12, 2012 10:08 AM
 To: mysql
 Subject: Mysql is toying me... why sometimes an insert or update can be
 slow!? I getting bald cuz this
 
 While doning a batch process...
 
 show full processlist show:
 
 | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |6 |
 end  | update `account` set `balance`= 0.00 +
 '-4000' where accountid='2583092'
 
 No other process, lo locking no nothing...
 
 so you take this same query... run it isolated, and the mufu... is
 just...f fast!
 
 
 update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
 Query OK, 0 rows affected (0.00 sec)
 Rows matched: 1  Changed: 0  Warnings: 0
 
 
 ARRRG!  I have seen this type of query take as long as 100+ seconds.. and
I
 don't have a F*** clue...
 

2 things come to mind here...

1)  indexes could not be set, so it may cause huge slowdowns on bigger
tables

2)  it could have been fast because of query caching, so it may have cached
the index you were updating for, which may make it work fast.  Also, if
you run it manually, the first time may be slow, and subsequent attempts are
faster (again, caching)

I may be wrong here, but something to check out.

Try this:

Explain SELECT * FROM `account` WHERE accountid='2583092';

That will tell you what indexes it is using, if any


Steve


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

RE: How to quickly detect if there are any crashed tables

[Steven Staples]

 AFAIK the tables will be locked one by one until checked/repaired.
 
 On May 10, 2012, at 3:07 PM, Adrian Fita wrote:
 
  On 10/05/12 21:51, Mihail Manolov wrote:
  You can enable check/recovery automatically by using
  myisam_recover. Look it up in the documentation.
 
  There is no way to repair them faster, though.
 
  Thanks for the quick response. This definetly looks like a useable
  solution. Do you know if during the auto-repair at startup, the server
  with all the tables will be available and answering to queries? Or
  will it make the tables available as it progresses with the repair?
 
  --
  Fita Adrian

I think you can scan the syslog for the mysql daemon, and it will show you
any crashed, or problematic tables?

If this is in fact the case, you could try that, and then run though the
tables to check them later?

Just pushing out a thought...

Steve.


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

Group_Concat help...

[Steven Staples]

Hello!

I am trying to do something, but I can't seem to figure out how...

My query is as follows:
SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips',
COUNT(`id`) AS 'connections'
FROM `mysql_test`
WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19
23:59:59'
GROUP BY `user_id`
HAVING COUNT(`id`)  2
ORDER BY COUNT(`id`) DESC 
LIMIT 0, 15;


This query works, it gives me results like:
user_id  login_ipsconnections  
---  ---  -
  1  192.168.0.200,192.168.0.201  5


But what I am looking to add, is the number of connections per IP, so it
would look kinda something like this:
user_id  login_ipsconnections  
---  ---  -
  1  192.168.0.200 (1),192.168.0.201 (3)  5

Is this possible to do with just 1 query?  If so, how would I go about doing
it??


And the table schema:
CREATE TABLE `mysql_test` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `user_id` int(11) unsigned NOT NULL,
  `login_datetime` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `login_ip` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

here is the data in my table:
id  user_id  login_datetime   login_ip   
--  ---  ---  ---
 11  2012-03-19 11:57:38  192.168.0.200  
 21  2012-03-19 11:57:40  192.168.0.201  
 31  2012-03-19 11:57:42  192.168.0.200  
 41  2012-03-19 11:57:43  192.168.0.200  
 51  2012-03-19 11:57:45  192.168.0.201  


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

RE: Group_Concat help...

[Steven Staples]

 -Original Message-
 From: Mihail Manolov [mailto:mihail.mano...@liquidation.com]
 Sent: March 19, 2012 12:44 PM
 To: Steven Staples
 Cc: mysql@lists.mysql.com
 Subject: Re: Group_Concat help...
 
 Try this
 
 SELECT `user_id`, `login_ip`,
 COUNT(`id`) AS 'connections'
 FROM `mysql_test`
 WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19
 23:59:59'
 GROUP BY `user_id`, `login_ip`
 HAVING COUNT(`id`)  2
 ORDER BY COUNT(`id`) DESC
 LIMIT 0, 15;
 
 On Mar 19, 2012, at 12:06 PM, Steven Staples wrote:
 
  SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips',
  COUNT(`id`) AS 'connections'
  FROM `mysql_test`
  WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19
  23:59:59'
  GROUP BY `user_id`
  HAVING COUNT(`id`)  2
  ORDER BY COUNT(`id`) DESC
  LIMIT 0, 15;
 

Thanks for the reply Mihail.
I was initially doing it that way, but if you look at the example of what I
wanted as a reply, it doesn't work.
I was hoping for a result that I could just plop into the PHP code, and I
wouldn't have to manipulate it at all, but so far, it looks like I am going
to have to do that... unless anyone else here has another idea... 


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

MySQL Session Variables with PHP

[Steven Staples]

Good [insert time of day here] all!

I am trying to reorder my auto-inc field in my database, and I have
successfully done it with my front end that I use (SQLYog) with the
following code:

SET @var_name = 0; 
UPDATE `my_database`.`my_table` SET `id` = (@var_name := @var_name +1);


Now, when I try this within PHP... I can't get it to work at all.  I assume
that the SET is the issue, but I am not 100% sure.

I use the PEAR MDB2 class, and I have tried it in 1 statement, but it
failed, and I tried it with 2 statements, it didn't puke on it, but it
didn't work either.

?php
# db connection is already set up #
echo $db-exec('SET @var_name = 0;')
echo 'br /';
echo $db-exec('UPDATE `my_database`.`my_table` SET `id` =
(@var_name:= @ var_name +1);');
exit;
?

Does anyone know how I can do this within PHP?  Worst case scenario, is that
I just write a php shell() command instead, but I would rather avoid that if
at all possible.

Thanks in advance!

Steven Staples



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

RE: MySQL Session Variables with PHP

[Steven Staples]

 -Original Message-
 From: Peter Brawley [mailto:peter.braw...@earthlink.net]
 Sent: February 22, 2012 11:07 AM
 To: Steven Staples; mysql@lists.mysql.com
 Subject: Re: MySQL Session Variables with PHP
 
 On 2/22/2012 9:47 AM, Steven Staples wrote:
  Good [insert time of day here] all!
 
  I am trying to reorder my auto-inc field in my database, and I have
  successfully done it with my front end that I use (SQLYog) with the
  following code:
 
  SET @var_name = 0;
  UPDATE `my_database`.`my_table` SET `id` = (@var_name := @var_name +1);
 
 
  Now, when I try this within PHP... I can't get it to work at all.  I
 assume
  that the SET is the issue, but I am not 100% sure.
 
  I use the PEAR MDB2 class, and I have tried it in 1 statement, but it
  failed, and I tried it with 2 statements, it didn't puke on it, but it
  didn't work either.
 
  ?php
  # db connection is already set up #
  echo $db-exec('SET @var_name = 0;')
  echo 'br /';
  echo $db-exec('UPDATE `my_database`.`my_table` SET `id` =
  (@var_name:= @ var_name +1);');
  exit;
  ?
 
  Does anyone know how I can do this within PHP?  Worst case scenario, is
 that
  I just write a php shell() command instead, but I would rather avoid
that
 if
  at all possible.
 
 The manual warns us not to rely on repeat user var assignments, but your
 approach works for me:
 
 $conn=mysql_connect( ... );
 mysql_select_db(test);
 mysql_query( drop table if exists t ) or exit(mysql_error());
 mysql_query( create table t (id int) ) or exit(mysql_error());
 for( $i=0; $i10; $i++ ) mysql_query( insert into t values(0) ) or
 exit(mysql_error());
 mysql_query( set @var=0 ) or exit(mysql_error());
 mysql_query( update t set id=(@var:=@var+1) ) or exit(mysql_error());
 $res = mysql_query( select id from t ) or exit(mysql_error());
 while( $row = mysql_fetch_row( $res )) echo $row[0],  ;
 
 Output: 1 2 3 4 5 6 7 8 9 10
 
 PB
 

I feel like a TOTAL newb here...

If you look at my php example, I forgot the ; at the end of the first db
query... so it failed out silently (my php errors are off, changed that too)

Now it works... lol


As for why I want to do this?  I have a table that I constantly add, and
remove items from, and I do it by looking up, and then deleting them (it is
a long story/process).   Anyways, the script stops every 8 hours, and
restarts.  So what I want to do, is reset all the auto-inc counters back to
starting at 1, so that they don't get too large and out of control.   I
don't need to reference the ID's anywhere else, just when I go through each
iteration.   I know that this *could* be bad for most databases/tables, but
in my case, it is not going to be an issue.

Thanks, and sorry for my ID10T error, since it works just fine!

Steve.


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

Relication Issues

[Steven Staples]

Good morning list,

I am having issues with my replication setup. it seems that every few
weeks/months something happens, and I have to restart it, skip a row, or
delete, rsync and restart replication from scratch.

The databases that are being replicated, are rather large... there are about
12 new tables every year, each consisting of about 2-4g in size each
(today's total database size is 83g).

Again, things work fine for a while, and then there are issues.  The
backup/slave is on the same network/datacenter, so network speed isn't an
issue.  I just don't understand why inconsistencies keep arising.   Is there
a better way to do live backups, or have a hot space in the event of a
catastrophe?   Is there 3rd party software that would better achieve data
integrity or something?

Any help here would be appreciated.

Thanks!

Steve.


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

RE: Relication Issues

[Steven Staples]

My Apologies,

The tables are all MyISAM, most of the inserts/updates/deletes are done
through stored procedures on the master.  There are about 2 stored procedure
calls per second, consisting of a whole bunch of queries, updates, and
inserts within them.

The latest issue(s) we're having, are:

Could not execute Write_rows event on table xxx.x; Duplicate entry
'20-1016792' for key 'PRIMARY', Error_code: 1062; handler error
HA_ERR_FOUND_DUPP_KEY; the event's mas
 
Could not execute Write_rows event on table xxx.x; Duplicate entry
'44870420' for key 'PRIMARY', Er 

Update_rows event on table xxx.x; Can't find record in 'x',
Error_code: 1032; handler error HA_ERR_KEY

(Where xxx is the databse, and x is the table)

The replication broke yesterday morning for some reason, and the primary key
index was at around 900,000.  When we found out it was down, and ended up
getting to work on it, we tried to restart it and the next ID it was
inserting for was 1,016,792.   So we're missing about 100,000 enteries, and
they are not in the bin log (as far as I can see).


Steve.

 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: November 10, 2011 8:42 AM
 To: mysql@lists.mysql.com
 Subject: Re: Relication Issues
 
 replication is buggy sometimes
 
 but without any outputs desribing your problem nobody can really help you
-
 memory tables as example are making much more troubles as myisam
 
 Am 10.11.2011 14:26, schrieb Steven Staples:
  Good morning list,
 
  I am having issues with my replication setup. it seems that every few
  weeks/months something happens, and I have to restart it, skip a row,
  or delete, rsync and restart replication from scratch.
 
  The databases that are being replicated, are rather large... there are
  about
  12 new tables every year, each consisting of about 2-4g in size each
  (today's total database size is 83g).
 
  Again, things work fine for a while, and then there are issues.  The
  backup/slave is on the same network/datacenter, so network speed isn't
an
  issue.  I just don't understand why inconsistencies keep arising.   Is
 there
  a better way to do live backups, or have a hot space in the event of a
  catastrophe?   Is there 3rd party software that would better achieve
data
  integrity or something?
 
  Any help here would be appreciated.



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

RE: dynamic alias?

[Steven Staples]

DATE_ADD(NOW(), INTERVAL 1 DAY) will give you tomorrow...

Not sure if that is what you're looking for or not...


Steven Staples


 -Original Message-
 From: Eric Bloomquist [mailto:eric_bloomqu...@cooley-dickinson.org]
 Sent: August 27, 2010 12:17 PM
 To: mysql@lists.mysql.com
 Subject: dynamic alias?
 
 Hi all,
 
 I'm wondering if it's possible to have a dynamic alias in MySQL.  The
 result I'm looking for is essentially:
 
 +-+---++
 | Provider Name   | Facility Name | Appts on 8/28/2010 | ==
 +-+---++
 | Mildred Ratched | Oregon State Hospital | 12 |
 | Henry Jekyll| London Internal Medicine  |  3 |
 | ... |   ||
 
 Where Appts on 8/28/2010 instead includes whatever tomorrow's date is
 when the query is run.
 
 Is it possible to do something like this in a MySQL query?
 
 Thanks,
 Eric
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.851 / Virus Database: 271.1.1/3089 - Release Date: 08/27/10
 02:34:00


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

RE: Reduce dataset but still show anomalies

[Steven Staples]

I am not too good with charting (even though I would like to be), but what 
about getting the max, min and avg, if the max/min is greater than x% of the 
avg, show that... ?

Just throwing out ideas... prolly not useful... but may cause a better idea ;)


Steven Staples


 -Original Message-
 From: Bryan Cantwell [mailto:bcantw...@firescope.com]
 Sent: August 20, 2010 11:24 AM
 To: mysql
 Subject: Re: Reduce dataset but still show anomalies
 
 Yes, but I DON'T want eh spikes smoothed out
 
 On Fri, 2010-08-20 at 17:16 +0200, Jangita wrote:
 
  On 20/08/2010 5:12 p, Bryan Cantwell wrote:
   I am trying to produce charts for large amounts of data. I already
 limit
   the user to a smaller time frame in order to reduce the possible data
   points, but still can end up with far more data points than are clearly
   plottable on a chart.  Does anyone have an idea of how I can drop
   insignificant points, or average the data or do something to end up
 with
   no more than about 3k points and still show spikes and dips in the
   charts so my users can still clearly identify anomalies in their
 charts?
   I don't want to smooth out the spikes and dips if at all possible.
   I considered running through the dataset and doing a compare of point 2
   to point 1 and if it is close in value throw it away, otherwise keep
 it.
   That probably would not work on a 'noisy' chart however...
  
  
   THanks,
   Bryancan
  
  Have you tried instead of showing per minute, show the average per hour,
  or per day; this will generally smoothen the points out a little; In my
  case if i show registrations per day i get dips every Saturday and
  Sunday so it looks all jagged, But per week doesn't show the
  Saturday/Sunday dips...
 
  --
  Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
  Skype: jangita | GTalk: jangita.nyag...@gmail.com
 
 
 
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/20/10
 02:35:00


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

RE: Reduce dataset but still show anomalies

[Steven Staples]

On another thought,  what about if you group it by whatever, if the MIN()/MAX() 
is greater than X times STDDEV(), show MIN() or MAX() ?

I just recalled a conversation with my boss the other week about the STDDEV()


Steven Staples



 -Original Message-
 From: Steven Staples [mailto:sstap...@mnsi.net]
 Sent: August 20, 2010 11:32 AM
 To: bcantw...@firescope.com; 'mysql'
 Subject: RE: Reduce dataset but still show anomalies
 
 I am not too good with charting (even though I would like to be), but what
 about getting the max, min and avg, if the max/min is greater than x% of
 the avg, show that... ?
 
 Just throwing out ideas... prolly not useful... but may cause a better idea
 ;)
 
 
 Steven Staples
 
 
  -Original Message-
  From: Bryan Cantwell [mailto:bcantw...@firescope.com]
  Sent: August 20, 2010 11:24 AM
  To: mysql
  Subject: Re: Reduce dataset but still show anomalies
 
  Yes, but I DON'T want eh spikes smoothed out
 
  On Fri, 2010-08-20 at 17:16 +0200, Jangita wrote:
 
   On 20/08/2010 5:12 p, Bryan Cantwell wrote:
I am trying to produce charts for large amounts of data. I already
  limit
the user to a smaller time frame in order to reduce the possible data
points, but still can end up with far more data points than are
 clearly
plottable on a chart.  Does anyone have an idea of how I can drop
insignificant points, or average the data or do something to end up
  with
no more than about 3k points and still show spikes and dips in the
charts so my users can still clearly identify anomalies in their
  charts?
I don't want to smooth out the spikes and dips if at all possible.
I considered running through the dataset and doing a compare of point
 2
to point 1 and if it is close in value throw it away, otherwise keep
  it.
That probably would not work on a 'noisy' chart however...
   
   
THanks,
Bryancan
   
   Have you tried instead of showing per minute, show the average per
 hour,
   or per day; this will generally smoothen the points out a little; In my
   case if i show registrations per day i get dips every Saturday and
   Sunday so it looks all jagged, But per week doesn't show the
   Saturday/Sunday dips...
  
   --
   Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
   Skype: jangita | GTalk: jangita.nyag...@gmail.com
  
 
 
 
  No virus found in this incoming message.
  Checked by AVG - www.avg.com
  Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/20/10
  02:35:00
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/20/10
 02:35:00


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

query help

[Steven Buehler]

I am hoping that I can do this with one query, I have a table, Domains
with 3 columns
accountID, domainID, mailname

 

what I am trying to do is find all accountID's for domainID of 12345 and
see if a second row with domainID of 54321 exists for that
accountID,mailname.  If it doesn't exist, I want it to insert another row
with the same accountID and mailname, but with the second (54321) domainid.

 

Any help would be appreciated.

 

Thanks

Steve

RE: Moving from one MySQL server to three MySQL servers?

[Steven Staples]

Have you double checked the hardware?   Are you using 5400rpm drives, or 15k
rpm drives?   I/O bottlenecks are common, if you can't read the data fast
enough, then it will definitely be slower, and appear to have more issues
that it really does.   If the client can't/won't change/alter the code, then
maybe looking at changing the hardware would be better.  Having a smaller
drive size raid array with faster harddrives may solve the I/O bottleneck if
that is the case.

And maybe it is just poorly written queries with crappy indexing? Maybe look
at the slow query log, and ensure that the RIGHT indexes are there
(140gb/21gb index doesn't mean that the indexes are the correct ones)

Going to a replication setup may not be the solution to your problems, and
could just be a bandaid (and prolly cause you many sleepless nights
maintaining data integrity).   Find out the cause of the problem, before
adding to it.

Steven Staples


 -Original Message-
 From: Nunzio Daveri [mailto:nunziodav...@yahoo.com]
 Sent: August 4, 2010 2:40 PM
 To: mysql@lists.mysql.com
 Subject: Moving from one MySQL server to three MySQL servers?
 
 Hello Gurus :-)  I was running a simple load generator against our 16GB
 Dual
 Quad core server and it pretty much came down to it's knees within two
 hours of
 running tests.  The customer DOES NOT WANT to change any code, they just
 want to
 throw hardware at it since it took them a year to create all of the code.
 It is
 a 140GB database with 21GB of indexs all using InnoDB - currently doing
70%
 reads and 30% writes.
 
 My question is what is the best way of distributing the load without
 changing
 any of the php / perl code that their web server uses?  This is what I am
 thinking but need someone to tell me it is a good idea or bad please?
 
 1. Setup a single master and 2 slaves.  The question is how to tell the
web
 servers to get all the read data from the slaves and to only write to the
 master?
 
 2. Install a MySQL proxy box and let mysql proxy handle the load, problem
 is now
 it is the SPOF!
 
 3. Use DNS round robin, BUT how to tell round robin to ONLY go to master
 for
 writes and ONLY use one of the 2 slaves for reads?
 
 Any links, ideas or suggestions is most appreciated.
 
 TIA...
 
 Nunzio
 
 
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/04/10
 00:45:00


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

RE: [MySQL] Re: Decimal points

[Steven Staples]

Just out of curiosity, why not do it in the application layer?

Or maybe, you can try:
SELECT BINARY 1+1.2;
=  2.2
SELECT BINARY 1+1.0;
= 2
SELECT CAST(1+1.2 AS UNSIGNED);
= 2
SELECT CAST(1+1.6 AS UNSIGNED);
= 3 (so I guess rounding happens here)

And then I guess ultimately, you could also use FLOOR() CEIL() or ROUND()
SELECT ROUND(1+1.6, 0);
= 3
SELECT FLOOR(1+1.6);
= 2
SELECT CEIL(1+1.6);
= 3



Steven Staples


 -Original Message-
 From: Ashley M. Kirchner [mailto:ash...@pcraft.com]
 Sent: July 20, 2010 1:52 PM
 To: mysql@lists.mysql.com
 Subject: Re: [MySQL] Re: Decimal points
 
 On 7/20/2010 10:07 AM, Chris W wrote:
  I try to avoid asking why but in this case I have to.  I can't imagine
  wanting to have a list of numbers displayed and not have them all
  aligned right with the sane number of digits after the decimal
  point.   So why would you even want to do this is?
 
  Wish I can give you an answer, but it's a requirement from the
 client.  Their application needs it displayed that way.  '101' and
 '101.0' appear to have completely different meanings.  Especially since
 more often than not, it's followed by a letter.  I don't know, I'm just
 the programmer here.
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.839 / Virus Database: 271.1.1/3009 - Release Date: 07/20/10
 02:36:00


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

RE: Why is MySQL always linked to Php?

[Steven Staples]

MySQL and PHP go together very well in the web development world.  Almost
all of PHP websites use MySQL as their database storage engine, as almost
all hosting companies have installed PHP and MySQL on their servers.

Maybe the jobs you're looking at, are for programmers/developers, who can
take care of the mysql as well?



Steven Staples



 -Original Message-
 From: alba.albetti [mailto:alba.albe...@libero.it]
 Sent: July 15, 2010 10: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!
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.830 / Virus Database: 271.1.1/2991 - Release Date: 07/15/10
 07:09:00


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

RE: phpMyAdmin and other management tools

[Steven Staples]

I personally use SQLYog, and as for it not having the query creation... it
has a fairly decent query builder, but it is in the paid version, not the
community one.

I like all the features it has, and I use most of them.  Granted, it is
always a good idea to know how to use the command line, just incase you're
locked out of it somehow, or if you need to repair, backup or create users.
But if you're a windoze user, 99% of them don't know what a command line is
:)


Steven Staples



 -Original Message-
 From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com]
 Sent: July 1, 2010 7:58 AM
 To: David Stoltz
 Cc: mysql@lists.mysql.com
 Subject: Re: phpMyAdmin and other management tools
 
 Hi Dave,
 
 I recommend you to use Toad for MySQL.
 
 It has wizard for query creation phpMyAdimn, mysql workbench, sqlyog lack
 this feature.
 
 Krishna
 
 On Thu, Jul 1, 2010 at 5:08 PM, David Stoltz dsto...@shh.org wrote:
 
  Hi Folks,
 
 
 
  I'm currently using phpMyAdmin to manage the mySQL databases. I'm
  wondering what most people like to use? I know there is mySQL
  Workbench, which I haven't really fooled with yet
 
 
 
  Can anyone comment on what they use and why?
 
 
 
  Thanks!
 
  Dave
 
 
 
 
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.830 / Virus Database: 271.1.1/2972 - Release Date: 06/30/10
 02:36:00


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

RE: Two Primary Keys

[Steven Staples]

@Dušan Pavlica;

I must say thank you.  
This is really interesting, and in the 7 years I've been using mysql and sql, 
I've never know this (or had, but didn't realize what I had done).
This little bit of information could make for some interesting changes to a 
couple of my projects I am working on, where I've done this, but done it in 
code, rather than in mysql.


Steven Staples


 -Original Message-
 From: Dušan Pavlica [mailto:pavl...@unidataz.cz]
 Sent: June 29, 2010 11:26 AM
 To: Victor Subervi
 Cc: mysql@lists.mysql.com
 Subject: Re: Two Primary Keys
 
 Hi,
 
 try this and you will see exactly how autoincrement behaves in MyISAM
 tables when it is part of primary key.
 
 1) declare table like this:
 CREATE TABLE  `test_tbl` (
   `field1` int(10) unsigned NOT NULL default '0',
   `field2` int(10) unsigned NOT NULL auto_increment,
   `field3` char(10) NOT NULL default '',
   PRIMARY KEY  (`field1`,`field2`)
 ) ENGINE=MyISAM;
 
 2) then insert some values
 INSERT INTO test_tbl (field1, field3)
 VALUES(1,'test1'),(2,'test2'),(1,'test3'),(2,'test4');
 
 3) see what's in the table
 SELECT * FROM test_tbl ORDER BY field1;
 
 result is:
 1, 1, 'test1'
 1, 2, 'test3'
 2, 1, 'test2'
 2, 2, 'test4'
 
 field2 is unique only in context of  field1.
 
 Hth,
 Dusan
 
 
 
 Victor Subervi napsal(a):
  2010/6/29 João Cândido de Souza Neto j...@consultorweb.cnt.br
 
 
  As far as I know, if you have an auto_increment primary key, you cant
 have
  any other field in its primary key.
 
 
 
  Makes sense. Actually, I was just copying what someone else gave me and
  adding the auto_increment, then I got to wondering, what is the purpose
 of
  having two primary keys?
  TIA,
  V
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.830 / Virus Database: 271.1.1/2917 - Release Date: 06/29/10
 02:35:00


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

MySQL Upgrading

[Steven Staples]

Hi,

I am looking at upgrading my servers Debian version from Etch to Lenny, and
in doing that, I think it will upgrade MySQL from 5.0.32 to the lenny
version, which is  5.0.53 (I think).

I have also been thinking about using the 'dotdeb' packages, which will
upgrade it even further to 5.1.47.  I have done this on a test server, and
it all my stored procedures and stuff work fine, so now to my question.

Will this break any replication if I don't upgrade my replication server
that is still running 5.0.32 (until I upgrade that server as well, which
could be a few weeks due to timing)?

The other issue, is that the replication server is running multiple
instances of the same MySQL on different ports, so that I can replicate
multiple sources to a single server (that has attached tape drives for
backup purposes), is it possible to run both versions of mysql (the 5.0.32
and the 5.1.47)?granted, this is not the debian mailing list, just
thought I would ask that last part ;)


Steven Staples




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

Table Consistency/analize/check/repair

[Steven Staples]

Hello,

I had an issue yesterday, where one of my tables ended up being closed
improperly, and needed to be repaired.   This isn't really an issue in
itself, but I didn't know about it until 2 hours after it happened, and a
script was trying to write to that table, and it failed, which caused a
backup on that script for inserting into another table.

ANYWAY, what I am wondering is, is what would be the best way to check all
my tables for errors/issues, and then run a repair on the table if it finds
an issue?

I have at least 30 tables, each having about 3-6 million rows in each, and
each table is about 2-3gigabytes in size.   So, I would like to be able to
check them only if they have anything that has changed (there is a table
created for each month of the year, for the last 2 years), it would be nice
if I didn't have to stop or put a lock on each table if I didn't have to,
and it would also be great if it was quick (running at like 2am, and
completing before 6am would be my time frame, and then Sunday morning from
2am to 8-10am is ok)

Any ideas for scripts, or premade scripts would be great.  Also, if this
would traverse to the slave mysql server too, that would be ideal.

Thanks in advance,

Steven Staples



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

analyze table issue

[Steven Staples]

Ok, I know I am an idiot sometimes, and I think this falls into that
category.

I use SQLYog as my MySQL front end editor.   I was looking to do some
things, and was going to run a check table command, and clicked the
analyze button, and didn't realize that all the tables in the database
were selected.

Long story short, that was at 9am this morning, and it is 230pm now, and it
is still running.  What are the ramifications of killing that process?

The database is only a total of 8 tables, and consuming about 20gb, but it
is approaching home time, and I don't want this to run all weekend long...

Can I just kill it, or will it corrupt the table it is currently on?


Steven Staples




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

RE: analyze table issue

[Steven Staples]

Nevermind, it just finished... holy uggh!


Steven Staples



 -Original Message-
 From: Steven Staples [mailto:sstap...@mnsi.net]
 Sent: June 18, 2010 2:31 PM
 To: mysql@lists.mysql.com
 Subject: analyze table issue
 
 Ok, I know I am an idiot sometimes, and I think this falls into that
 category.
 
 I use SQLYog as my MySQL front end editor.   I was looking to do some
 things, and was going to run a check table command, and clicked the
 analyze button, and didn't realize that all the tables in the database
 were selected.
 
 Long story short, that was at 9am this morning, and it is 230pm now, and
it
 is still running.  What are the ramifications of killing that process?
 
 The database is only a total of 8 tables, and consuming about 20gb, but it
 is approaching home time, and I don't want this to run all weekend long...
 
 Can I just kill it, or will it corrupt the table it is currently on?
 
 
 Steven Staples
 
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.829 / Virus Database: 271.1.1/2917 - Release Date: 06/18/10
 02:35:00


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

RE: Strange GREATEST() result in 5.0.32

[Steven Staples]

Baron,

Out of curiosity, do you (or anyone else) know what could be an issue with
upgrading to even 5.0.93?  or even the 5.1 branch?

There are a lot of stored procedures/functions, as well as the fact that it
is being replicated (the backup server is running multiple instances, and
is replicating 3 other servers).   So, all the sql databases will have to
be updated/upgraded, but is there anything I/we should be made aware of
before we go ahead? (there is a lot of release notes to sift through)


Steven Staples


 -Original Message-
 From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf
 Of Baron Schwartz
 Sent: June 15, 2010 4:56 PM
 To: mysql@lists.mysql.com
 Subject: Re: Strange GREATEST() result in 5.0.32
 
 Steven,
 
 On Tue, Jun 1, 2010 at 11:15 AM, Steven Staples sstap...@mnsi.net wrote:
  Hello all
 
  I have a stored procedure that probably does a lot more than it should,
 but
  it works fine on my test server (which is running 5.0.67).   When I
moved
 it
  over to the production server, (which is running 5.0.32 and I never
 thought
  to check that the versions were the same before) it works almost
 perfectly.
 
 It sounds like you're running into a bug, simply put.  5.0.32 is very
 old and an amazing amount of bugs have been fixed since then.  I would
 not even consider running it in production.  I know it'll be tough to
 upgrade, but if you don't, my experience is that another of the
 unfixed bugs is going to cause you serious pain anyway, such as
 crashing your server.
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.829 / Virus Database: 271.1.1/2917 - Release Date: 06/15/10
 02:35:00


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

RE: WHERE clause from AS result

[Steven Staples]

Putting the 'HAVING' in there, works perfectly :)

THANKS!


Steven Staples



 -Original Message-
 From: SHAWN L.GREEN [mailto:shawn.l.gr...@oracle.com]
 Sent: June 10, 2010 8:03 PM
 To: Steven Staples
 Cc: 'MySql'
 Subject: Re: WHERE clause from AS result
 
 On 6/10/2010 4:38 PM, Steven Staples wrote:
  Ok, I have done it before, where I have used the AS result in an ORDER
 BY,
  but now, I can't figure out why I can't use it in a WHERE clause?
 
  SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM
 `pnums`
  WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` WHERE pnum LIKE
  '555-12%';
 
  It gives me this error:
  Error Code : 1054
  Unknown column 'pnum' in 'where clause'
 
 
 It has to do with the order in which things happen in the query. The
 results of the subquery are computed in the FROM...WHERE... part of the
 query. There is no way that the results could be named so that the WHERE
 clause could handle them. This is why aliases are available for use in
 the clauses processed after the WHERE clause - the GROUP BY and HAVING
 clauses.
 
 Try this as an alternative:
 
 SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM
 `pnums`
 WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` HAVING pnum LIKE
 '555-12%';
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.829 / Virus Database: 271.1.1/2917 - Release Date: 06/10/10
 02:35:00


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

WHERE clause from AS result

[Steven Staples]

Ok, I have done it before, where I have used the AS result in an ORDER BY,
but now, I can't figure out why I can't use it in a WHERE clause?

SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums`
WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` WHERE pnum LIKE
'555-12%';

It gives me this error:
Error Code : 1054
Unknown column 'pnum' in 'where clause'


Any ideas?


Steven Staples



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

RE: Help needed on query on multiple tables

[Steven Staples]

How about this?

SELECT
`first_table`.`names`
, `first_table`.`version`
, (SELECT
   COUNT(`other_table`.`names`)
   FROM `other_table`
   WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count'
FROM `first_table`
WHERE `first_table`.`progress`  0;


Granted, you have not provided structure or names of the tables so this is
just my interpretation, but maybe something like this could give you a
starting point?

Steven Staples


 -Original Message-
 From: Michael Stroh [mailto:st...@astroh.org]
 Sent: June 3, 2010 11:24 AM
 To: MySql
 Subject: Help needed on query on multiple tables
 
 Hi everyone. I'm trying to create a certain MySQL query but I'm not sure
 how to do it. Here is a stripped down version of the result I'm aiming
for.
 I'm pretty new to queries that act on multiple tables, so apologize if
this
 is a very stupid question.
 
 I have one table (data) that has two columns (names and progress). I have
a
 second table (items) that has two columns (names and version). I'd like to
 do a query that produces the name of every record in data that has
progress
 set to 0 and the number of records in the items table that have the same
 value in each table.names field.
 
 I can perform this by using two sets of queries, one that queries the data
 table and then loop through the names to do a count(names) query, but I'm
 not sure if I can somehow do it in one query.
 
 Thanks in advance!
 Michael
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10
 02:25:00


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

RE: Help needed on query on multiple tables

[Steven Staples]

I am glad that I was able to help someone finally :)

There may be other ways to do this, but that was what first came to mind.
I would maybe run an explain on that query to ensure that it is using
indexes.

Steven Staples


 -Original Message-
 From: Michael Stroh [mailto:st...@astroh.org]
 Sent: June 3, 2010 11:55 AM
 To: Steven Staples
 Cc: 'MySql'
 Subject: Re: Help needed on query on multiple tables
 
 Thanks! That did it perfectly!
 
 Michael
 
 
 On Jun 3, 2010, at 11:45 AM, Steven Staples wrote:
 
  How about this?
 
  SELECT
 `first_table`.`names`
 , `first_table`.`version`
 , (SELECT
COUNT(`other_table`.`names`)
FROM `other_table`
WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count'
  FROM `first_table`
  WHERE `first_table`.`progress`  0;
 
 
  Granted, you have not provided structure or names of the tables so this
 is
  just my interpretation, but maybe something like this could give you a
  starting point?
 
  Steven Staples
 
 
  -Original Message-
  From: Michael Stroh [mailto:st...@astroh.org]
  Sent: June 3, 2010 11:24 AM
  To: MySql
  Subject: Help needed on query on multiple tables
 
  Hi everyone. I'm trying to create a certain MySQL query but I'm not
sure
  how to do it. Here is a stripped down version of the result I'm aiming
  for.
  I'm pretty new to queries that act on multiple tables, so apologize if
  this
  is a very stupid question.
 
  I have one table (data) that has two columns (names and progress). I
 have
  a
  second table (items) that has two columns (names and version). I'd like
 to
  do a query that produces the name of every record in data that has
  progress
  set to 0 and the number of records in the items table that have the
same
  value in each table.names field.
 
  I can perform this by using two sets of queries, one that queries the
 data
  table and then loop through the names to do a count(names) query, but
 I'm
  not sure if I can somehow do it in one query.
 
  Thanks in advance!
  Michael
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
  No virus found in this incoming message.
  Checked by AVG - www.avg.com
  Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date:
06/03/10
  02:25:00
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org
 
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10
 02:25:00


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

Strange GREATEST() result in 5.0.32

[Steven Staples]

Hello all

I have a stored procedure that probably does a lot more than it should, but
it works fine on my test server (which is running 5.0.67).   When I moved it
over to the production server, (which is running 5.0.32 and I never thought
to check that the versions were the same before) it works almost perfectly.

The issue is when this runs:
GREATEST(d_UPDATE_Time, d_START_Time)

The value is '2010-05-21 20:26:18' and not '2010-05-26 21:49:51' even though
it is greater.


Both of these variables are declared in the top of the procedure:
DECLARE d_UPDATE_Time DATETIME;
DECLARE d_START_Time DATETIME;

When I put this in, to see the values of the variables, I get the right
data:
SELECT d_UPDATE_Time, d_START_Time;

d_UPDATE_Timed_START_Time  
---  ---
2010-05-26 21:49:51  2010-05-21 20:26:18


So if I ran this from the command line:
SELECT GREATEST('2010-05-26 21:49:51', '2010-05-21 20:26:18');

I get:
RESULT
---
2010-05-26 21:49:51


So it works perfectly fine when it runs outside of the stored procedure, but
inside the procedure, it returns the wrong date :(

Is there something I am doing wrong?   I can't seem to see anything wrong.
Also, upgrading from 5.0.32 would require upgrading both the master and
slave, and right now, that is not a good idea (we are discussing it though)


Steven Staples




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

RE: Using RAND to get a unique ID that has not been used yet

[Steven Staples]

If you wanted to use/go that route, then why not select a random limit 1
from that table, and then delete that row?

SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1;


On a side note, I would use the auto-inc field still, and store this number
in another field.

Steven Staples



 -Original Message-
 From: Jim Lyons [mailto:jlyons4...@gmail.com]
 Sent: May 28, 2010 11:49 AM
 To: Andre Matos
 Cc: mysql@lists.mysql.com
 Subject: Re: Using RAND to get a unique ID that has not been used yet
 
 If your specs are that specific (IDs must be between 1 and 99)
 then you could create a 99-row table with one integer column and
 prefill it with the numbers 1 to 99 in random order.
 
 Then you could write a function that would select and return the first
 number in the table, then delete that record so you would not reuse
 it.
 
 Once you've done the work of sorting 99 numbers in random order
 (which can be done anywhich way) it's easy and you don't have to loop
 an indeterminant number of times.  You would be looping an increasing
 number of times as you begin to fill up the table.
 
 Jim
 
 On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org
 wrote:
  Hi All,
 
  I have a table that uses auto_increment to generate the Id automatically
 working fine. However, I need to create a new table where the Id must be a
 number generated randomly, so I cannot use the auto_increment.
 
  MySQL has a function RAND. So I could use something like this:
 
  SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable
 
  But, let's suppose that the RandId is a number that was already used in
 the table. Then I need to run the SELECT again and again until I find a
 number that hasn't been used.
 
  Is there a way to have this SELECT to loop until it finds a number that
 hasn't been used?
 
  The RandId must be only numbers and length of 6 (from 1 to 99). No
 other character is allowed.
 
  Thanks for any help!
 
  Andre
 
  --
  Andre Matos
  andrema...@mineirinho.org
 
 
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com
 
 
 
 
 
 --
 Jim Lyons
 Web developer / Database administrator
 http://www.weblyons.com
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10
 02:25:00


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

RE: Obtain week number between years

[Steven Staples]

You could also try it this way, and then in the application, you can find
out what the dates are, that they are between.


SELECT YEARWEEK(`datefield`) AS 'week', 
COUNT(`visits`) AS 'visits'
FROM `mytable` WHERE YEAR(`datefield`) = '2009'
GROUP BY YEARWEEK(`datefield`);

(this was just taken off the top of my head, but it should give something
that you're looking for... hopefully ;) )

Steven Staples


 -Original Message-
 From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On
 Behalf Of Baron Schwartz
 Sent: April 29, 2010 8:34 AM
 To: MySql
 Subject: Re: Obtain week number between years
 
 Neil,
 
 I would start with something like this, assuming the date column is
 called d:
 
 SELECT count, or sum, or whatever aggregate function
 FROM table
 GROUP BY d - INTERVAL DAYOFWEEK(d) DAY;
 
 - Baron
 
 On Thu, Apr 29, 2010 at 8:12 AM, Tompkins Neil
 neil.tompk...@googlemail.com wrote:
  Hi
 
  We need to produce a query to return the total number of user visits
 between
  two date ranges that span over two year e.g from 2009-04-29 to 2010-
 04-29.
  My question is how can I compute the totals for each week within a
 query ?
  for example
 
  2009-04-29 to 2009-05-06   100 visits
  2009-05-07 to 2009-05-14   250 visits etc
 
  Cheers
  Neil
 
 
 
 
 --
 Baron Schwartz
 Percona Inc http://www.percona.com/
 Consulting, Training, Support  Services for MySQL
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.814 / Virus Database: 271.1.1/2783 - Release Date:
 04/29/10 02:27:00


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

RE: Join syntax problem

[Steven Staples]

As Tom Worster said, print($query); would show you what the query was trying
to run.


Without testing it, you also have some other whitespace issues between the
hw.wildlife and FROM, and also, i m unsure of the asterix infront of the
*images.

On another note, when I do my JOINs, I tend to write ON
(table1.field=jointable.field) rather than just ON (field).

And on a final thought, the where cause, seems to be the join clause as
well, so isn't that redundant? (or is would that only be in the way that i
said i do my joins?)


++
| Steven Staples |
++
| I may be wrong, but at least I tried...|
++



 -Original Message-
 From: Gary [mailto:g...@paulgdesigns.com]
 Sent: April 26, 2010 10:29 PM
 To: mysql@lists.mysql.com
 Subject: Re: Join syntax problem
 
 Thanks for the replies.  It was my understanding that whitespace is
 ignored,
 and I did not think that not having space, in particular with . would
 result in an error message.
 
 Gary
 Gary gp...@paulgdesigns.com wrote in message
 news:20100426233621.10789.qm...@lists.mysql.com...
 I cant seem to get this working.
 
  $query=SELECT im.image_id, im.caption, im.where_taken,
 im.description,
  im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife,
 kw.american,
  kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter .
  FROM *images AS im.JOIN keywords AS kw USING (image_id) .
  WHERE ky.image_id = im.image_id;
 
  Gets me this error message.
 
  You have an error in your SQL syntax; check the manual that
 corresponds to
  your MySQL server version for the right syntax to use near 'keywords
 AS kw
  USING (image_id)WHERE ky.image_id = im.image_id' at line 1
 
  Anyone see where I am going wrong?
 
  Thank you.
 
  Gary
 
 
  __ Information from ESET Smart Security, version of virus
  signature database 5063 (20100426) __
 
  The message was checked by ESET Smart Security.
 
  http://www.eset.com
 
 
 
 
 
 
 __ Information from ESET NOD32 Antivirus, version of virus
 signature database 5063 (20100426) __
 
 The message was checked by ESET NOD32 Antivirus.
 
 http://www.eset.com
 
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.814 / Virus Database: 271.1.1/2783 - Release Date:
 04/26/10 02:31:00


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

RE: subquery multiple rows

[Steven Staples]

If i may add (and I am no expert), but just be careful of how much you're
group_concat does, as there is a group_concat_max_len value (you can
override it though).  I have run into this once, and couldn't figure out why
i wasn't getting all my data.

-- taken from the mysql site:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_grou
p-concat 

SET [GLOBAL | SESSION] group_concat_max_len = val;


Steven Staples




 -Original Message-
 From: kalin m [mailto:ka...@el.net]
 Sent: April 7, 2010 12:59 PM
 To: Nathan Sullivan
 Cc: mysql@lists.mysql.com
 Subject: Re: subquery multiple rows
 
 
 
 
 yea..  almost. but it helped a lot. now i know about those functions
 too. thank you...
 
 
 Nathan Sullivan wrote:
  I think you want to do something like this:
 
  select prod, group_concat(category separator ', ')
  from products
  group by prod;
 
 
  Hope this helps.
 
  On Wed, Apr 07, 2010 at 08:37:04AM -0700, kalin m wrote:
 
  hi all...
 
  i have a bit of a problem with this:
 
  table products:
 
  --
  prod  |  category |
  -|
  boots |  winter|
  boots | summer  |
  boots | spring |
  shoes | spring |
  shoes | winter|
  shoes | fall |
  shoes | summer  |
  --
 
  when i do this:
select distinct prod as m, (select category from products where
 email
  = m) as n from products;
 
  i get:
 
  ERROR 1242 (21000): Subquery returns more than 1 row
 
  i know that the subquery returns more than one rows. i hope so...
 
  what i'd like to see as result is:
 
  -
  m | n |
  -
  boots   |  winter, summer, spring  |
  shoes   |  spring, winter, fall , summer  |
  -
 
 
  or at least:
 
  ---
  m | n  |
  ---
  boots   |  3  |
  shoes   |  4  |
  
 
 
 
 
  thanks
 
 
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
 
 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.791 / Virus Database: 271.1.1/2783 - Release Date:
 04/07/10 02:32:00


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

Table Length Question...

[Steven Staples]

Hi there,

I currently store some information about a users daily habits in a table.
The table has 4 fields per day, and another 4 fields as the keys.  This
table, depending on the month, can be from (4 keys + (28 days * 4 fields per
day)) fields, to (4 keys + (31 days * 4 fields per day)) fields long... 

The table layout is like such:
+-+---++-+--+--+--+--+--+--+
--
|name |id |id2 |type |d01f1 |d01f2 |d01f3 |d01f4 |d02f1 |d02f2 |.and so
on
+-+---++-+--+--+--+--+--+--+
--

Performance wise, would it be better to have it laid out in a manner such as
+-+---++-++---+---+---+---+
|name |id |id2 |type |day |f1 |f2 |f3 |f4 |
+-+---++-++---+---+---+---+
So that each row, contains a single days details, rather than have a single
row, contain the entire months details?

Also, when i would do a select, if i wanted say d02f1, would it load the
entire row first, and then just give me that field?
-Select `d02f01` from `mytable` where [where clause]
Or would it jsut load that field... 

Does these questions make sense?  (they do in my head)

Steven Staples



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

MySQL Slave is almost 1 day behind

[Steven Staples]

Good day :)

We've had our master/slave server running for a while now, and just
yesterday, we started getting behind.
Not entirely sure what happened, but it is getting further and furhter
behind.

(master server)
mysql show master status\G
*** 1. row ***
File: mysql-bin.000280
Position: 58090245
Binlog_Do_DB: admin_server,baf,freeradius,radius
Binlog_Ignore_DB:
1 row in set (0.00 sec)


(slave server)
mysql show slave status\G
*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.7.101
Master_User: slave_user
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: mysql-bin.000280
Read_Master_Log_Pos: 55208258
 Relay_Log_File: backup-relay-bin.000530
  Relay_Log_Pos: 96663109
  Relay_Master_Log_File: mysql-bin.000259
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
Replicate_Do_DB: admin_server,baf,freeradius,radius
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
   Skip_Counter: 0
Exec_Master_Log_Pos: 96662972
Relay_Log_Space: 2211376614
Until_Condition: None
 Until_Log_File:
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
Master_SSL_Cert:
  Master_SSL_Cipher:
 Master_SSL_Key:
  Seconds_Behind_Master: 77473
1 row in set (0.00 sec)

Now, we are logging the freeradius packets into mysql, and like I said, it
has been running fine, up until yesterday.   Any idea how the slave would
get this far behind, and not be generating any errors?

It is my understanding, that the slave only does update/insert/delete
queries, so even if there was a lot of select queries on the master, the
slave wouldn't see them.  We are not running any queries on the slave (it
was set up for backup purposes, so we could stop the slave and backup
completely), and we haven't done a backup on the slave in a couple of days
(yeah, i know... bad bad) so there is really no reason for this.

Can anyone help/assist/point me in the right direction to figure out how to
catch the slave back up to the master?  The master is not being overloaded,
it is keeping up no problem, and the backup server is 8x the server than the
application server, so it shoulnd't even be an i/o or cpu issue.

Please help! :)


Thanks in advance
Steven Staples


-- 
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 - trucate master table

[Steven Staples]

I don't have the error anymore, but the slave will start, but when you show
slave status, it shows that there is a failure, and the failure was, a
duplicate primary key.

What i did to fix/bandaid it, was i truncated the table on the slave, and
restarted the slave again... then i did a delete on the master, where ID 
.




Steven Staples


-Original Message-
From: Johnny Withers [mailto:joh...@pixelated.net] 
Sent: March 5, 2010 1:11 PM
To: Steven Staples
Cc: mysql@lists.mysql.com
Subject: Re: Master/Slave - trucate master table

Does START SLAVE fail?

If so, what is the output of SHOW SLAVE STATUS?

JW

On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples sstap...@mnsi.net wrote:

 Good day everyone...

 I am working with a master/slave set up, and of course, we started it once
 the database tables were a combined size of 60gb+

 We've got it running and in sync now, (stopped the master, deleted the bin
 files, rsync'd the database tables and restarted the master) and all has
 been fine for the last week... until today.

 We stopped the mysql process on the slave, and did a backup (due to size,
 we
 just rsync and backup the rsync) of the table files.  Took about 40
minutes
 to do (still not sure why it was that long, but anyway), and then we
 restarted the slave mysql.  We're getting an error now.  Apparently one of
 the tables is out of sync now?   What appears to have happened, is that
 when
 the slave was down, i truncated a table on the master.

 I've read that this sometimes causes errors, is this a bug? Or a is there
 something I should do differently?


 Steven Staples



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




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10
02:34:00


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

RE: Master/Slave - trucate master table

[Steven Staples]

Technically, shouldn't the slave be able to shutdown and then catch back
up?
What if the slave lost power and shut down... and when the power came back,
shouldn't the slave restart, get the bin logs, and catchup?

From now on, I wont truncate any tables, i will just delete from the table
where the ID   and then reset the ID to 0 or 1... and then delete
everything from  and beyond

I was just wondering if this was a bug, or if there was something wrong with
what I did?


Steven Staples


-Original Message-
From: Ananda Kumar [mailto:anan...@gmail.com] 
Sent: March 8, 2010 11:45 AM
To: Steven Staples
Cc: Johnny Withers; mysql@lists.mysql.com
Subject: Re: Master/Slave - trucate master table

did u stop the slave process before stopping mysql on slave.
Did u do the below before stopping the mysql on slave?
slave stop;
show slave status\G;

regards
anandkl

On Mon, Mar 8, 2010 at 6:46 PM, Steven Staples sstap...@mnsi.net wrote:

 I don't have the error anymore, but the slave will start, but when you
show
 slave status, it shows that there is a failure, and the failure was, a
 duplicate primary key.

 What i did to fix/bandaid it, was i truncated the table on the slave, and
 restarted the slave again... then i did a delete on the master, where ID 
 .




 Steven Staples


 -Original Message-
 From: Johnny Withers [mailto:joh...@pixelated.net]
 Sent: March 5, 2010 1:11 PM
 To: Steven Staples
 Cc: mysql@lists.mysql.com
 Subject: Re: Master/Slave - trucate master table

 Does START SLAVE fail?

 If so, what is the output of SHOW SLAVE STATUS?

 JW

 On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples sstap...@mnsi.net wrote:

  Good day everyone...
 
  I am working with a master/slave set up, and of course, we started it
 once
  the database tables were a combined size of 60gb+
 
  We've got it running and in sync now, (stopped the master, deleted the
 bin
  files, rsync'd the database tables and restarted the master) and all has
  been fine for the last week... until today.
 
  We stopped the mysql process on the slave, and did a backup (due to
size,
  we
  just rsync and backup the rsync) of the table files.  Took about 40
 minutes
  to do (still not sure why it was that long, but anyway), and then we
  restarted the slave mysql.  We're getting an error now.  Apparently one
 of
  the tables is out of sync now?   What appears to have happened, is that
  when
  the slave was down, i truncated a table on the master.
 
  I've read that this sometimes causes errors, is this a bug? Or a is
there
  something I should do differently?
 
 
  Steven Staples
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
 
 


 --
 -
 Johnny Withers
 601.209.4985
 joh...@pixelated.net

 No virus found in this incoming message.
 Checked by AVG - www.avg.com
 Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10
 02:34:00


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



No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/08/10
02:34:00


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

Master/Slave - trucate master table

[Steven Staples]

Good day everyone...

I am working with a master/slave set up, and of course, we started it once
the database tables were a combined size of 60gb+

We've got it running and in sync now, (stopped the master, deleted the bin
files, rsync'd the database tables and restarted the master) and all has
been fine for the last week... until today.

We stopped the mysql process on the slave, and did a backup (due to size, we
just rsync and backup the rsync) of the table files.  Took about 40 minutes
to do (still not sure why it was that long, but anyway), and then we
restarted the slave mysql.  We're getting an error now.  Apparently one of
the tables is out of sync now?   What appears to have happened, is that when
the slave was down, i truncated a table on the master.

I've read that this sometimes causes errors, is this a bug? Or a is there
something I should do differently?


Steven Staples



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

declare multiple 'Definer'

[Steven Staples]

Good morning!

I have been struggleing with creating a store procedure, that will allow 2
users


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

RE: declare multiple 'Definer'

[Steven Staples]

Well... let me finish... LOL  (hit send some how...)

I want to be able to have 2 different users access to a stored procedure...

I've tried multiple ways to write it, and none of them seem to work.  Does
anyone here know how to do this?

CREATE (definer=`use...@`%`, definer=`sstapl...@`localhost`) PROCEDURE
`sstest`()
.
CREATE definer=`use...@`%` OR definer=`sstapl...@`localhost` PROCEDURE
`sstest`()
.
CREATE definer=`use...@`%` definer=`sstapl...@`localhost` PROCEDURE
`sstest`()


I can't think how it would be possible?   Or is it?

Steve


-Original Message-
From: Steven Staples [mailto:sstap...@mnsi.net] 
Sent: February 22, 2010 8:42 AM
To: mysql@lists.mysql.com
Subject: declare multiple 'Definer'

Good morning!

I have been struggleing with creating a store procedure, that will allow 2
users


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

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.733 / Virus Database: 271.1.1/2683 - Release Date: 02/21/10
14:34:00


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

sql file system + optimization

[Steven Staples]

Ok... in the file system, my MySQL files are located in /var/lib/mysql/
I have a database (lets say 'test') so it gets its own directory
/var/lib/mysql/test/
Now, all the tables go in this folder. (I know, we *should* all know
this...)

if the database is on another disk, could it increase performance if this
was a large database? If so, could I just 
symlink the test/ directory to another raid array to increase performance?
Or would the increase be negligible?


Steven Staples




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

RE: Join Statement

[Steven Staples]

Victor,

As far as I can see... change the '-' to '='.

-- fixed query --
SELECT 
SKU
, Quantity
, Name
, Price
, p.sizes
, p.colorsShadesNumbersShort
FROM 
tem126080739853 t JOIN products p ON
t.ProdID = p.ID
;
-- end --

I didn't run this, or try to replicate it, it was just my observation on the
query.


Steven Staples


-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com] 
Sent: December 14, 2009 11:26 AM
To: mysql@lists.mysql.com
Subject: Join Statement

Hi;
I have the following:

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

mysql select * from tem126080739853;
+++--+-+--+
| ID | ProdID | Quantity | sizes   | colorsShadesNumbersShort |
+++--+-+--+
|  1 |  2 |2 | Extra-small | navy-blue:CC7722 |
+++--+-+--+
1 row in set (0.00 sec)
mysql select SKU, Quantity, Name, Price, p.sizes,
p.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID;
Empty set (0.03 sec)

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

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

So I'm at a loss as to why the above select join statement fails. Please
advise.
TIA,
Victor

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.716 / Virus Database: 270.14.101/2555 - Release Date: 12/14/09
02:37:00


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

RE: Stored Proc's

[Steven Staples]

They are actual IP's, not domain names.

So it would be like `us...@`192.168.0.100` and `us...@`192.168.0.101`... so
how would I go about putting that in the definer?   Currently, i have this:

DELIMITER $$
USE `tablename`$$
DROP PROCEDURE IF EXISTS `tablename`$$
CREATE definer=`us...@`192.168.0.100` PROCEDURE `tablename`(... etc etc
etc...


Sorry if this is sounding dumb, I've only just started using stored procs
(and i love them), and i have only seen examples with 1 user, or wildcarding
the domain.  I would also like to know how to do it for any/all users (but
that is not what I am needing now)

Steven Staples


-Original Message-
From: Michael Dykman [mailto:mdyk...@gmail.com] 
Sent: December 11, 2009 9:50 AM
To: mysql@lists.mysql.com
Subject: Re: Stored Proc's

of course.  you can have entries

u...@domain1.me.com
u...@xxx.foo.com

the only caveat is to make sure the reverse lookup of your client
hosts works as expected as those are the names that mysql will apply
at authentication time.


 - mkichael dykman



On Fri, Dec 11, 2009 at 9:36 AM, Steve Staples sstap...@mnsi.net wrote:
 Silly question here...

 But can I have multiple definers for a stored proc, rather than allowing
 `us...@`%`?   basically, I want to only allow 1 user name, but from only 2
 or 3 IP's.

 My Googleing didn't turn up anything of use, so now, I am here asking :)

 Thanks in advance!

 Steve Staples.




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





-- 
 - michael dykman
 - mdyk...@gmail.com

May you live every day of your life.
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

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

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.709 / Virus Database: 270.14.101/2555 - Release Date: 12/11/09
05:06:00


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

Select from remote server from stored procedure

[Steven Staples]

Ok, I feel silly for asking this, but I am going to do it anyway.

I have a huge stored procedure that does quite a bit of logic, and
gathering/splitting of data.   I currently have our customer database on one
server, and our logging on another.  What i need to do, is to pull the
customer id from the other server, so that the logs are tied back to the
customer.

Is this possible to do?  To make a connection, inside the stored procedure
to a completely different machine and access the mysql there?

Does my question make sense?   Currently what I am doing, is every new
customer that gets created, my php app adds the username/customerid to that
server, then makes a connection to the logging server and creates the same
record, same with deleting and updating... but there just has to be a
simpler way :)

Thanks in advance.


Steven Staples




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

RE: Select from remote server from stored procedure

[Steven Staples]

So what I am reading, I guess it would be safer to just do it how I am
currently doing it, as it really isn't that slow... it's just duplicating
the data elsewhere (I suppose maybe making this a slave table to the other
server... nah... lots of work there :P)

Thanks, and I did search it before, but I guess my searching keywords were
insufficient ;)


Steven Staples


-Original Message-
From: harrison.f...@sun.com [mailto:harrison.f...@sun.com] 
Sent: December 9, 2009 2:07 PM
To: Johan De Meersman
Cc: Neil Aggarwal; Steven Staples; mysql@lists.mysql.com
Subject: Re: Select from remote server from stored procedure

Hello Johan,

On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote:

 Posted this before, but beware: federated tables do NOT use indices.  
 Every
 select is a full table scan, and if you're talking about a logging  
 table
 that could become very expensive very fast.

This is not entirely true.  If you define an index on the local  
federated table, and it makes sense to use it, then a remote WHERE  
clause will be passed through and hence use the remote index.  Not all  
types of index accesses can be passed through such as this, however  
for a single row lookup on a primary key, it should be fine.

It is still not as fast as local access, but it's not as bad as always  
doing a full table scan remotely.

 On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal  
 n...@jammconsulting.comwrote:

 Is this possible to do?  To make a connection, inside the
 stored procedure
 to a completely different machine and access the mysql there?

 The only way I know to access tables from different servers
 from a single connection is federated tables:
 http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

 Once you do that, you are accessing it like a local table.

 I hope this helps.

   Neil

Regards,

Harrison
-- 
Harrison C. Fisk, MySQL Staff Support Engineer
MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/





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

No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.709 / Virus Database: 270.14.97/2550 - Release Date: 12/09/09
02:32:00


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

RE: MySQL View

[Steven Buehler]

 -Original Message-
 From: Jochem van Dieten [mailto:joch...@gmail.com]
 Sent: Tuesday, February 10, 2009 5:10 AM
 To: mysql@lists.mysql.com
 Subject: Re: MySQL View
 
 On Mon, Feb 9, 2009 at 3:41 PM, Steven Buehler wrote:
  Ok, I just saw a post about using view's in mysql.  I tried to look
 it up
  and found how to use it, but my question is: what is a view and why
 would
  you use it?
 
 The problem with any definition of an object in a database is that
 there are multiple definitions. Usually on the one hand you have the
 definition from abstract relational theory, and on the other hand you
 have the definition from actual working databases. So I am not going
 to bother with a definition, I will try to explain how a view works
 internally inside database code.
 
 The easiest way to understand a view is to consider a view as a macro
 that gets expanded during the execution of every query that references
 that view in its FROM. Lets take for example the view that your DBA
 has defined for you using:
 CREATE VIEW x AS SELECT * FROM y INNER JOIN z ON y.id = z.id;
 
 Then you query that view with the query:
 SELECT a FROM x;
 
 What the database will do for you behind the scenes is expand your
 usage of the view. In effect, the database will replace x with its
 definition. So your query SELECT a FROM x; gets expanded to:
 SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id);
 
 Notice that I have done nothing but replace x with its definition
 between parenthesis. And this results in a valid query that can be
 executed. And that is exactly what the database will do. It will do
 this substitution and then it will run the result of that substitution
 as if it were the query that you submitted.
 
 
 Obviously a bit more will go on behind the scenes to handle things
 like permissions and optimizations (especially if you get to databases
 that have more functionality then MySQL), but this is really all there
 is to it. A view is a simple macro that assigns an alias to a select
 statement, and when you reference that alias the select statement will
 get substituted back in.

Jochem,

Not sure about the other poster, but this helps explain it to me.  If I 
understand you correctly, if I have multiple tables with many columns in them, 
but have several queries that need to pull only a few columns from each and put 
them together, it is probably best to create a view to do this so that I don't 
have to keep running joins in my queries?  Even if I reboot the computer, the 
view will still be there when it comes back up too?

Thanks
Steve



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

MySQL View

[Steven Buehler]

Ok, I just saw a post about using view's in mysql.  I tried to look it up
and found how to use it, but my question is: what is a view and why would
you use it?  Is it like a temporary table?  Does it write a new database to
the disk or use memory?

 

Thanks

Steve

RE: MySQL View

[Steven Buehler]

 -Original Message-
 From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On
 Behalf Of Baron Schwartz
 Sent: Monday, February 09, 2009 9:19 AM
 To: Steven Buehler
 Cc: mysql@lists.mysql.com
 Subject: Re: MySQL View
 
 On Mon, Feb 9, 2009 at 9:41 AM, Steven Buehler st...@ibushost.com
 wrote:
  Ok, I just saw a post about using view's in mysql.  I tried to look
 it up
  and found how to use it, but my question is: what is a view and why
 would
  you use it?  Is it like a temporary table?  Does it write a new
 database to
  the disk or use memory?
 
 A view is a piece of SQL whose result can be queried like a table.  It
 stores no data; the results are always generated as the query
 executes.  In some cases it does use a temporary table to hold the
 result and then query against it; in other cases it merges the
 original query's SQL and the view's SQL together and then executes the
 resulting query.
 
 Why use it?  To abstract a complex bit of code away for simplicity.
 To grant permissions in a certain way (you can grant access to the
 view and deny access to the underlying table).
 
 There's a lot of complexity to it though, in terms of how to use views
 correctly and get good performance.  I think the manual goes over it
 in good detail, and our book High Performance MySQL 2nd Edition has
 probably the best exploration of it otherwise.
 
 Baron

Baron, Thank You
Questions:
1.  The view is temporary then?  So it kind of uses it in place of a
temporary table?
2.  Does it go away after the query or after the mysql_close?

I am going to have to go to the book store and get your book too.

Thanks
Steve


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

RE: MySQL View

[Steven Buehler]

I just found something else out and did a test.  So a view is a table in a
database that can be shared.  The example I found was if you have multiple
people that need a database for something, but it still has to keep the
information separate, instead of having multiple databases, you can create
one with a VIEW statement.  With the privileges setup correctly, each user
can only access the information in that database that they put in and not
other people's data.  And it does actually create this on disk not in memory
so it doesn't get deleted between sessions, reboots, etc.  Does that sound
correct?

 

Thanks

Steve

 

From: Martin Gainty [mailto:mgai...@hotmail.com] 
Sent: Monday, February 09, 2009 11:26 AM
To: Steven Buehler; ba...@xaprb.com
Cc: mysql@lists.mysql.com
Subject: RE: MySQL View

 

My current understanding of the delta between Views and Temporary Tables
Views are read only results from 1 or more tables ..in Oracle they are
stored in TEMP tablespace
http://www.psoug.org/reference/views.html

Temporary Tables are tables which are created/updated/inserted and exist
only for the duration of your client session
Oracle calls these Global Temporary Tables
http://www.psoug.org/reference/gtt.html

HTH
Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official
business of Sender. This transmission is of a confidential nature and Sender
does not endorse distribution to any party other than intended recipient.
Sender does not necessarily endorse content contained within this
transmission. 




 From: st...@ibushost.com
 To: ba...@xaprb.com
 CC: mysql@lists.mysql.com
 Subject: RE: MySQL View
 Date: Mon, 9 Feb 2009 10:10:45 -0600
 
 
 
  -Original Message-
  From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On
  Behalf Of Baron Schwartz
  Sent: Monday, February 09, 2009 9:19 AM
  To: Steven Buehler
  Cc: mysql@lists.mysql.com
  Subject: Re: MySQL View
  
  On Mon, Feb 9, 2009 at 9:41 AM, Steven Buehler st...@ibushost.com
  wrote:
   Ok, I just saw a post about using view's in mysql. I tried to look
  it up
   and found how to use it, but my question is: what is a view and why
  would
   you use it? Is it like a temporary table? Does it write a new
  database to
   the disk or use memory?
  
  A view is a piece of SQL whose result can be queried like a table. It
  stores no data; the results are always generated as the query
  executes. In some cases it does use a temporary table to hold the
  result and then query against it; in other cases it merges the
  original query's SQL and the view's SQL together and then executes the
  resulting query.
  
  Why use it? To abstract a complex bit of code away for simplicity.
  To grant permissions in a certain way (you can grant access to the
  view and deny access to the underlying table).
  
  There's a lot of complexity to it though, in terms of how to use views
  correctly and get good performance. I think the manual goes over it
  in good detail, and our book High Performance MySQL 2nd Edition has
  probably the best exploration of it otherwise.
  
  Baron
 
 Baron, Thank You
 Questions:
 1. The view is temporary then? So it kind of uses it in place of a
 temporary table?
 2. Does it go away after the query or after the mysql_close?
 
 I am going to have to go to the book store and get your book too.
 
 Thanks
 Steve
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
 

  _  

Windows LiveT: Keep your life in sync. See how it works.
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022
009 

RE: MySQL View

[Steven Buehler]

Claudio,

Thank you.  I do agree with you.  I need to read up on it.  I only work with
MySQL databases and queries and have never heard of it until today.  Maybe
after I learn more about them, I will find them useful.  Guess they must be
useful or it wouldn't be part of SQL.

 

Thank You

Steve

 

From: Claudio Nanni [mailto:claudio.na...@gmail.com] 
Sent: Monday, February 09, 2009 3:59 PM
To: Steven Buehler
Cc: mysql@lists.mysql.com
Subject: Re: MySQL View

 

Steven,
I would suggest you the reading of a basic book about SQL and Databases, I
explain you why.
Views are a very fundamental concept in database theory that you would love
to study in deep.
This mailing list, though the most professional on MySQL, is not suitable to
explain such basic and important concepts.
Views and temporary tables are like completely different concepts, temporary
tables are not even a concept, but a 'tool' for
SQL developer, while views are (views!) 'snapshots' of data structured in a
way that phisically does not exist, are reorganization of
the database relations(read tables) to pull out only relevant information
for a part of an application and by which you can create infinite views
(sorry) of the database. If you like Excel, very roughly speaking, views are
excel functions, while the table data is the actual excel column containing
data.

Steven, this is my humble opinion.

Please let me know what you think

Claudio




2009/2/9 Steven Buehler st...@ibushost.com

I just found something else out and did a test.  So a view is a table in a
database that can be shared.  The example I found was if you have multiple
people that need a database for something, but it still has to keep the
information separate, instead of having multiple databases, you can create
one with a VIEW statement.  With the privileges setup correctly, each user
can only access the information in that database that they put in and not
other people's data.  And it does actually create this on disk not in memory
so it doesn't get deleted between sessions, reboots, etc.  Does that sound
correct?



Thanks

Steve



From: Martin Gainty [mailto:mgai...@hotmail.com]
Sent: Monday, February 09, 2009 11:26 AM
To: Steven Buehler; ba...@xaprb.com

Cc: mysql@lists.mysql.com

Subject: RE: MySQL View




My current understanding of the delta between Views and Temporary Tables
Views are read only results from 1 or more tables ..in Oracle they are
stored in TEMP tablespace
http://www.psoug.org/reference/views.html

Temporary Tables are tables which are created/updated/inserted and exist
only for the duration of your client session
Oracle calls these Global Temporary Tables
http://www.psoug.org/reference/gtt.html

HTH
Martin
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official
business of Sender. This transmission is of a confidential nature and Sender
does not endorse distribution to any party other than intended recipient.
Sender does not necessarily endorse content contained within this
transmission.




 From: st...@ibushost.com
 To: ba...@xaprb.com
 CC: mysql@lists.mysql.com
 Subject: RE: MySQL View
 Date: Mon, 9 Feb 2009 10:10:45 -0600



  -Original Message-
  From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On
  Behalf Of Baron Schwartz
  Sent: Monday, February 09, 2009 9:19 AM
  To: Steven Buehler
  Cc: mysql@lists.mysql.com
  Subject: Re: MySQL View
 
  On Mon, Feb 9, 2009 at 9:41 AM, Steven Buehler st...@ibushost.com
  wrote:
   Ok, I just saw a post about using view's in mysql. I tried to look
  it up
   and found how to use it, but my question is: what is a view and why
  would
   you use it? Is it like a temporary table? Does it write a new
  database to
   the disk or use memory?
 
  A view is a piece of SQL whose result can be queried like a table. It
  stores no data; the results are always generated as the query
  executes. In some cases it does use a temporary table to hold the
  result and then query against it; in other cases it merges the
  original query's SQL and the view's SQL together and then executes the
  resulting query.
 
  Why use it? To abstract a complex bit of code away for simplicity.
  To grant permissions in a certain way (you can grant access to the
  view and deny access to the underlying table).
 
  There's a lot of complexity to it though, in terms of how to use views
  correctly and get good performance. I think the manual goes over it
  in good detail, and our book High Performance MySQL 2nd Edition has
  probably the best exploration of it otherwise.
 
  Baron

 Baron, Thank You
 Questions:
 1. The view is temporary then? So it kind of uses it in place of a
 temporary table?
 2. Does it go away after the query or after the mysql_close?

 I am going to have to go to the book store and get your book too.

 Thanks
 Steve


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

Re: mysqld, mysqld-nt, mysqld-debug

[Steven]

 Steve
(n)
Martin!


 if you want apache and mysql (and possibly PHP for scripting) AND you want
 all 3 to run at once
 then d/l and implement with XAMP
 there are alot of XAMP specific bells and whistles that are installed that
 get in the way of tuning your MySQL and tuning your Apache installations so
 my advice is if you just want to use Mysql standalone dont use XAMP but
 start mysql with mysqld as suggested

Well, that's your opinion. But I think for beginners is XAMPP the best
basis. Everything else you can later still wish to change. Once is a
fact, I wanted no discussion on the pros and reignite XAMPP. I just
wanted to help. ;-)

 Viel Gluck!
 Martin

Good Luck? What for?

Greetings,
Steven

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

Re: mysqld, mysqld-nt, mysqld-debug

[Steven]

Good Morning!

2008/10/5 Varuna Seneviratna [EMAIL PROTECTED]:
 Hello Everybody

 2 What is the difference between installing MySQL as a server and a service,
 Is it only that when installed as a service MySQL server starts when Windows
 starts and when Windows stops it stops?

That is right! ;-) MySQL as a service should be installed, it starts
either automatically or (depending on your attitude) about the
services configuration.

 3 In Windows is it not possible to start other to two servers mysqld and
 mysqld-debug

You have only to start mysqld. For starting with MySQL, you don't need
MySQL-Debug



 Varuna


Greetings from Germany,
Steven

P.S.: Why you don't use XAMPP? Especially for beginners is the clearly
better choice, since much is already preset.

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

Multiple Query/Insert help

[Steven Buehler]

I have a query that I just can't seem to get working.

 

insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) (select
'36',game_date,begin_time,loc_id,home_team_id,away_team_id,(select div_id
from team_season where team_id=s1.div_id) from scheduler s1);

 

Of course, I am getting the dreaded Unknown column 's1.div_id' in 'where
clause' error message.

I think the statement above shows what I am trying to do better than me
explaining it.  My only real problem is that the div_id needs to be gotten
from the team_season table.

 

Any help would be appreciated

Thanks

Steve

RE: Multiple Query/Insert help

[Steven Buehler]

Thank you, but I still get an error and I can't figure it out:  Unknown
column 's1.div_id' in 'on clause'
Any other thoughts?

Steve

-Original Message-
From: Andy Shellam [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 16, 2008 9:40 AM
To: Steven Buehler
Cc: mysql@lists.mysql.com
Subject: Re: Multiple Query/Insert help

Hi Steve,

You're seeing this error because this query:

select div_id
from team_season where team_id=s1.div_id

is being run independently of the rest, so it doesn't know of s1 in 
this context.  You would probably be better with an INNER JOIN here, 
something like the following (may need tweaking):

e.g.

insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) 
(
select
'36', game_date, begin_time, loc_id, home_team_id,
away_team_id, ts.div_id
from
scheduler s1 INNER JOIN team_season ts ON ts.team_id =
s1.div_id
)


Regards,
Andy

Steven Buehler wrote:
 I have a query that I just can't seem to get working.

  

 insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) (select
 '36',game_date,begin_time,loc_id,home_team_id,away_team_id,(select div_id
 from team_season where team_id=s1.div_id) from scheduler s1);

  

 Of course, I am getting the dreaded Unknown column 's1.div_id' in 'where
 clause' error message.

 I think the statement above shows what I am trying to do better than me
 explaining it.  My only real problem is that the div_id needs to be gotten
 from the team_season table.

  

 Any help would be appreciated

 Thanks

 Steve


   

-- 
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: Multiple Query/Insert help

[Steven Buehler]

Thank you, but I still get an error and I can't figure it out:  Unknown
column 's1.div_id' in 'on clause'
Any other thoughts?

Steve

-Original Message-
From: Martin Gainty [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 16, 2008 9:50 AM
To: Andy Shellam; Steven Buehler
Cc: mysql@lists.mysql.com
Subject: RE: Multiple Query/Insert help


the only possible suggestion i have would be to disambiguate the selected
columns with 'as'

insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) 
(
 select
 '36' as sea_id, game_date as date, begin_time as time, loc_id as loc_id,
home_team_id as hteam, away_team_id as vteam, ts.div_id as div_id
From scheduler s1 INNER JOIN team_season ts ON ts.team_id =
s1.div_id
 )

Thanks Andy
Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official
business of Sender. This transmission is of a confidential nature and Sender
does not endorse distribution to any party other than intended recipient.
Sender does not necessarily endorse content contained within this
transmission. 


 Date: Sat, 16 Aug 2008 15:40:08 +0100
 From: [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 CC: mysql@lists.mysql.com
 Subject: Re: Multiple Query/Insert help
 
 Hi Steve,
 
 You're seeing this error because this query:
 
 select div_id
 from team_season where team_id=s1.div_id
 
 is being run independently of the rest, so it doesn't know of s1 in 
 this context.  You would probably be better with an INNER JOIN here, 
 something like the following (may need tweaking):
 
 e.g.
 
 insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) 
 (
   select
   '36', game_date, begin_time, loc_id, home_team_id,
away_team_id, ts.div_id
   from
   scheduler s1 INNER JOIN team_season ts ON ts.team_id =
s1.div_id
 )
 
 
 Regards,
 Andy
 
 Steven Buehler wrote:
  I have a query that I just can't seem to get working.
 
   
 
  insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) (select
  '36',game_date,begin_time,loc_id,home_team_id,away_team_id,(select
div_id
  from team_season where team_id=s1.div_id) from scheduler s1);
 
   
 
  Of course, I am getting the dreaded Unknown column 's1.div_id' in
'where
  clause' error message.
 
  I think the statement above shows what I am trying to do better than me
  explaining it.  My only real problem is that the div_id needs to be
gotten
  from the team_season table.
 
   
 
  Any help would be appreciated
 
  Thanks
 
  Steve
 
 

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

_
See what people are saying about Windows Live.  Check out featured posts.
http://www.windowslive.com/connect?ocid=TXT_TAGLM_WL_connect2_082008


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

RE: Multiple Query/Insert help

[Steven Buehler]

Thank you Martin and Andy for your help in this matter.  I redid the queries
taking hints from yours and came up with one that worked.  I am not that
great with INNER JOIN's and couldn't get yours to work, even though you did
still say that I needed to tweek it.  It would be nice to know the query
that would work with an INNER JOIN though.  Just so that I can study it and
learn from it.

INSERT INTO games2( sea_id, date, time, loc_id, hteam, vteam, div_id ) 
(
SELECT '36' AS sea_id, s1.game_date AS date, s1.begin_time AS time,
s1.loc_id AS loc_id, s1.home_team_id AS hteam, s1.away_team_id AS vteam,
(
SELECT t1.div_id AS div_id
FROM team_season t1
WHERE t1.team_id = s1.home_team_id
AND t1.deleted != '1'
)
AS div_id
FROM scheduler s1
)

Thanks To All
Steve

-Original Message-
From: Andy Shellam [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 16, 2008 10:03 AM
To: Martin Gainty
Cc: Steven Buehler; mysql@lists.mysql.com
Subject: Re: Multiple Query/Insert help

Hi Martin,

Good point, I normally do but was just illustrating the join.  I would 
also normally fully-qualify each column when using table aliases and 
multiple tables to avoid disambiguity.

insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id)
(
select
'36' as sea_id, s1.game_date as date, s1.begin_time as time, 
s1.loc_id as loc_id, s1.home_team_id as hteam, s1.away_team_id as vteam, 
ts.div_id as div_ud
from
scheduler s1 INNER JOIN team_season ts ON ts.team_id = s1.div_id
)

Thanks,
Andy

Martin Gainty wrote:
 the only possible suggestion i have would be to disambiguate the 
 selected columns with 'as'

 insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id)
 (
  select
  '36' as sea_id, game_date as date, begin_time as time, loc_id as 
 loc_id, home_team_id as hteam, away_team_id as vteam, ts.div_id as div_ud
  from
  scheduler s1 INNER JOIN team_season ts ON ts.team_id = s1.div_id
  )

 Thanks Andy
 Martin
 __
 Disclaimer and confidentiality note
 Everything in this e-mail and any attachments relates to the official 
 business of Sender. This transmission is of a confidential nature and 
 Sender does not endorse distribution to any party other than intended 
 recipient. Sender does not necessarily endorse content contained 
 within this transmission.


  Date: Sat, 16 Aug 2008 15:40:08 +0100
  From: [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  CC: mysql@lists.mysql.com
  Subject: Re: Multiple Query/Insert help
 
  Hi Steve,
 
  You're seeing this error because this query:
 
  select div_id
  from team_season where team_id=s1.div_id
 
  is being run independently of the rest, so it doesn't know of s1 in
  this context. You would probably be better with an INNER JOIN here,
  something like the following (may need tweaking):
 
  e.g.
 
  insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id)
  (
  select
  '36', game_date, begin_time, loc_id, home_team_id, away_team_id, 
 ts.div_id
  from
  scheduler s1 INNER JOIN team_season ts ON ts.team_id = s1.div_id
  )
 
 
  Regards,
  Andy
 
  Steven Buehler wrote:
   I have a query that I just can't seem to get working.
  
  
  
   insert into games2 (sea_id,date,time,loc_id,hteam,vteam,div_id) 
 (select
   '36',game_date,begin_time,loc_id,home_team_id,away_team_id,(select 
 div_id
   from team_season where team_id=s1.div_id) from scheduler s1);
  
  
  
   Of course, I am getting the dreaded Unknown column 's1.div_id' in 
 'where
   clause' error message.
  
   I think the statement above shows what I am trying to do better 
 than me
   explaining it. My only real problem is that the div_id needs to be 
 gotten
   from the team_season table.
  
  
  
   Any help would be appreciated
  
   Thanks
  
   Steve
  
  
  
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
 

 
 See what people are saying about Windows Live. Check out featured 
 posts. Check It Out! 
 http://www.windowslive.com/connect?ocid=TXT_TAGLM_WL_connect2_082008


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

Encryption with collation

[Steven Buehler]

I have a column in a table that was turned into an cp1251_general_ci
for a type of encryption.  Question is, how do I unencrypt it?

Thanks
Steve


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

RE: How to auto repair db on hosting with quota

[Steven Buehler]

 -Original Message-
 From: Artem Kuchin [mailto:[EMAIL PROTECTED]
 Sent: Friday, December 07, 2007 4:35 AM
 To: mysql@lists.mysql.com
 Subject: How to auto repair db on hosting with quota
 
 I am asking this question as a hosting PROVIDER, not
 as a hosting client.
 
 The sitation is like this (this is a simple example with small
 numbers):
 
 1) a client has 100mb quota for site and db files (set on unix group)
 2) 80mb is already taken by 75MB database in ONE TABLE and 5mb of files
 3) databases gets broken
 4) mysql tried to auto repair the table
 5) to repair data it must create a temporary file and this files take
 almost as much as 75MB
 6) user goes over quota becauase mysql needs another 75MB to reapir
 table,
 and this summs up like 75+75+5 a lot  than 100mb in quota
 7) mysql gets stuck on this db
 8) people continue to acccess site and connections build up and each
 connection waits for repair to be finished
 9) eventually mysql runs out of connections and this stop other clients
 too.
 10) day is ruined.
 
 SO the questions are:
 
 1) Is the way to specify USER/GROUP and PLACE for temporary files
 for table repair?
 2) Maybe there is some OTHER way to set quota for databases?
 3) Is there way to specify max number of connections for a user?
 
 Current solution for this as i see it is to make a daemon which
 must connect to database first with administrative privs and
 monitor number of connections and repairs using processlist. If some
 user
 does more than allowed number of connection then just kill them off. If
 repair accures - then turn off that host for a while and turn if back
 on again
 when repair finished.
 
 I wonder how it is solved on mass hosting servers.

Have you tried doing the repair as the root user for MySQL instead of the
client user?

Steve


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

RE: secure port 3306

[Steven Buehler]

Thank You so much.  I will have to try this later today.  I have
never done a port forward in iptables before.  I knew that I could, but just
never had a need or tried so it slipped my mind about this.
The thing is...I need to securely do this.  Here would be the setup
Desktop - Secure connection to Server 1 - Secure connection to Server 2.
So I am assuming that what I need to do is to have the Desktop SSH into
Server 1 which will have the iptables setup to tunnel to Server 2 and then
use a tunnel from Secure CRT (or putty) to tunnel all the way to Server 2
through Server 1?  Server one can only be accessed with SSH from Server 1.

Again, Thank You.
Steve


On linux, one could do a port forward:

EXTIF=eth0 # Or whatever the interface that faces internet is called.

iptables -A FORWARD -i $EXTIF -p tcp -s client-ip --dport 3306 -j ACCEPT
iptables -A PREROUTING -t nat -p tcp -s client-ip \
   -d linux-fw-ip --dport 3306 -j DNAT --to internal-ip:3306

On Wed, May 2, 2007 17:03, Steven Buehler wrote:
 I have a client that needs to be able to remotely connect to port 3306 
 securely.  I have tried to suggest an SSH Tunnel, but they do not want 
 their clients to have SSH access.  Another problem is that even if we 
 do tunnel, it needs to go thru one server that is connected to the 
 Internet and into the MySQL server which is NOT accessible from the 
 Internet.

 Any suggestions?

 Thanks
 Steve


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


 --
 This message has been scanned for viruses and dangerous content by 
 OpenProtect(http://www.openprotect.com), and is believed to be clean.



-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.


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

RE: secure port 3306

[Steven Buehler]

But I also need to make sure that nobody is sniffing between Server-1 and 
Server-2.
Steve


-Original Message-
From: Mogens Melander [mailto:[EMAIL PROTECTED] 
Sent: Monday, May 07, 2007 1:35 PM
To: Steven Buehler
Cc: mysql@lists.mysql.com
Subject: RE: secure port 3306


On Mon, May 7, 2007 17:40, Steven Buehler wrote:
 The thing is...I need to securely do this.  Here would be the setup
 Desktop - Secure connection to Server 1 - Secure connection to Server 2.
 So I am assuming that what I need to do is to have the Desktop SSH into
 Server 1 which will have the iptables setup to tunnel to Server 2 and then
 use a tunnel from Secure CRT (or putty) to tunnel all the way to Server 2
 through Server 1?  Server one can only be accessed with SSH from Server 1.

The only reason for the need for ssh-tunnel would be to eliminate the
risk of somebody sniffing between desktop - server-1.

This iptables rule allow only access from one ip-address (desktop).

 
 On linux, one could do a port forward:

 EXTIF=eth0 # Or whatever the interface that faces internet is called.

 iptables -A FORWARD -i $EXTIF -p tcp -s client-ip --dport 3306 -j ACCEPT
 iptables -A PREROUTING -t nat -p tcp -s client-ip \
-d linux-fw-ip --dport 3306 -j DNAT --to internal-ip:3306

 On Wed, May 2, 2007 17:03, Steven Buehler wrote:
 I have a client that needs to be able to remotely connect to port 3306
 securely.  I have tried to suggest an SSH Tunnel, but they do not want
 their clients to have SSH access.  Another problem is that even if we
 do tunnel, it needs to go thru one server that is connected to the
 Internet and into the MySQL server which is NOT accessible from the
 Internet.

 Any suggestions?

 Thanks
 Steve


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


 --
 This message has been scanned for viruses and dangerous content by
 OpenProtect(http://www.openprotect.com), and is believed to be clean.



 --
 Later

 Mogens Melander
 +45 40 85 71 38
 +66 870 133 224



 --
 This message has been scanned for viruses and
 dangerous content by OpenProtect(http://www.openprotect.com), and is
 believed to be clean.


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


 --
 This message has been scanned for viruses and
 dangerous content by OpenProtect(http://www.openprotect.com), and is
 believed to be clean.



-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.


-- 
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: secure port 3306

[Steven Buehler]

Thank You so much.  I will have to try this later today.  I have never done
a port forward in iptables before.  I knew that I could, but just never had
a need or tried so it slipped my mind about this.
Again, Thank You.
Steve


On linux, one could do a port forward:

EXTIF=eth0 # Or whatever the interface that faces internet is called.

iptables -A FORWARD -i $EXTIF -p tcp -s client-ip --dport 3306 -j ACCEPT
iptables -A PREROUTING -t nat -p tcp -s client-ip \
   -d linux-fw-ip --dport 3306 -j DNAT --to internal-ip:3306

On Wed, May 2, 2007 17:03, Steven Buehler wrote:
 I have a client that needs to be able to remotely connect to port 3306
 securely.  I have tried to suggest an SSH Tunnel, but they do not want
 their
 clients to have SSH access.  Another problem is that even if we do tunnel,
 it needs to go thru one server that is connected to the Internet and into
 the MySQL server which is NOT accessible from the Internet.

 Any suggestions?

 Thanks
 Steve


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


 --
 This message has been scanned for viruses and
 dangerous content by OpenProtect(http://www.openprotect.com), and is
 believed to be clean.



-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.


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

secure port 3306

[Steven Buehler]

I have a client that needs to be able to remotely connect to port 3306
securely.  I have tried to suggest an SSH Tunnel, but they do not want their
clients to have SSH access.  Another problem is that even if we do tunnel,
it needs to go thru one server that is connected to the Internet and into
the MySQL server which is NOT accessible from the Internet.

Any suggestions?

Thanks
Steve


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

Support for JDBC 4.0?

[Steven Buroff]

Does anyone know if there are plans to add support for JDBC 4.0 to the java
connector? I'm particularly interested in support for the
Connection.createQueryObject(Class cls) method.
Thanks.
 
Steve Buroff

Urgent: Please Confirm Interest in China Business Opportunity

[Steven Forsberg]

This is a text part of the message.
It is shown for the users of old-style e-mail clients

MySQL 4.1.15 on Red Hat 7.3?

[Steven Quezadas]

Has anyone here successfully managed to install mysql
4.1.15 on a RedHat 7.3 box? RedHat 7.3 seems to be
using 

the glibc 2.2.5 libraries and mysql seems to insist on
using glibc 2.3. Am I out of luck? 





__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

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

Using MySQL libraries for a client app

[Steven Altsman]

checking for mysql_init in -lmysqlclient... yes
checking whether mysql clients can run... no
configure: error: Your MySQL client libraries aren't properly installed


I'm a bit confused.  It is able to do mysql_init() and gets a response, but
when it checks the libmysqlclient.so it doesn't work.  I've gone to the
custom directory that I have it installed in (/usr/local/mysql/lib) and
tried running libmysqlclient.so and I get a segmentation fault.  Would it be
a matter of permissions for the ./configure, the $LDFLAGS variable, or
something else that I am missing?

I've looked at the PureFTP archives and they aren't particularly helpful, so
I tried the MySQL archives to see if there is a problem with the shared
libraries.  I did a search for pureftp, pure-ftpd, pure-ftp, Your MySQL
client libraries aren't properly installed, checking whether mysql clients
can run... no, and no useful results.

I've also google'd those errors and got between 5 and 10 responses, of which
most are in Chinese, Japanese, Swedish, German, and Russian.  My english is
already bad enough and I'm a Texan.

All of the packages I'm installing are compiled from source, so RPMs are not
as much of an issue.  I've done RPMs before, but they don't always set them
up the way I like.  Also, this is for my developer box, because I have a lot
of projects that either require or will require database hooks.

The config that I used for MySQL 5.0 is:

./configure --prefix=/usr/local/mysql \
--with-unix-socket-path=/var/lib/mysql/mysql.sock \
--with-tcp-port=3306 \
--with-mysqld-user=mysql \
--with-openssl=/usr/local/openssl \
--with-openssl-includes=/usr/local/openssl/includes \
--with-openssl-libs=/usr/local/openssl/libs \
--with-big-tables \
--with-berkeley-db=/usr/local/bdb \
--with-berkeley-db-includes=/usr/local/bdb/includes \
--with-berkeley-db-libs=/usr/local/bdb/libs \
--with-example-storage-engine \
--with-archive-storage-engine \
--with-csv-storage-engine \
--with-blackhole-storage-engine \
--with-ndbcluster \
--with-ndb-test \
--with-ndb-docs \
--with-ndb-port \
--with-ndb-port-base \
--with-federated-storage-engine

I've also done a side-by-side thinking it might be the client is
incompatible so did MySQL 4.0

./configure --prefix=/usr/local/mysqlclient \
--without-server

I've specified for the environment variables:

$LDFLAGS='-L/usr/local/mysql/include/ -L/usr/local/mysql/lib/mysql/lib
-L/usr/local/mysql/libexec/ -L/usr/local/mysql/'

then

$LDFLAGS='-L/usr/local/mysqlclient/include/
-L/usr/local/mysqlclient/lib/mysql/lib -L/usr/local/mysqlclient/libexec/
-L/usr/local/mysqlclient/'

Am I wasting my (and your) time posting this here, or is it really something
with the MySQL client libraries?



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

RE: Using MySQL libraries for a client app

[Steven Altsman]

Argh.. I'm stupid.

http://64.233.161.104/search?q=cache:hzZrZNEtu6wJ:jim.3tchina.com/tech/ftp/0
40607_pureftpd_mysql.php+%22Your+MySQL+client+libraries+aren%27t+properly+in
stalled%22hl=en%20target=nw

cp /usr/local/mysql/lib/mysql/* /usr/lib

Please disregard.

3 For China :D

-Original Message-
From: Steven Altsman [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 19, 2005 11:37 AM
To: 'mysql@lists.mysql.com'
Subject: Using MySQL libraries for a client app


checking for mysql_init in -lmysqlclient... yes checking whether mysql
clients can run... no
configure: error: Your MySQL client libraries aren't properly installed


I'm a bit confused.  It is able to do mysql_init() and gets a response, but
when it checks the libmysqlclient.so it doesn't work.  I've gone to the
custom directory that I have it installed in (/usr/local/mysql/lib) and
tried running libmysqlclient.so and I get a segmentation fault.  Would it be
a matter of permissions for the ./configure, the $LDFLAGS variable, or
something else that I am missing?

I've looked at the PureFTP archives and they aren't particularly helpful, so
I tried the MySQL archives to see if there is a problem with the shared
libraries.  I did a search for pureftp, pure-ftpd, pure-ftp, Your MySQL
client libraries aren't properly installed, checking whether mysql clients
can run... no, and no useful results.

I've also google'd those errors and got between 5 and 10 responses, of which
most are in Chinese, Japanese, Swedish, German, and Russian.  My english is
already bad enough and I'm a Texan.

All of the packages I'm installing are compiled from source, so RPMs are not
as much of an issue.  I've done RPMs before, but they don't always set them
up the way I like.  Also, this is for my developer box, because I have a lot
of projects that either require or will require database hooks.

The config that I used for MySQL 5.0 is:

./configure --prefix=/usr/local/mysql \
--with-unix-socket-path=/var/lib/mysql/mysql.sock \
--with-tcp-port=3306 \
--with-mysqld-user=mysql \
--with-openssl=/usr/local/openssl \
--with-openssl-includes=/usr/local/openssl/includes \
--with-openssl-libs=/usr/local/openssl/libs \ --with-big-tables \
--with-berkeley-db=/usr/local/bdb \
--with-berkeley-db-includes=/usr/local/bdb/includes \
--with-berkeley-db-libs=/usr/local/bdb/libs \ --with-example-storage-engine
\ --with-archive-storage-engine \ --with-csv-storage-engine \
--with-blackhole-storage-engine \ --with-ndbcluster \ --with-ndb-test \
--with-ndb-docs \ --with-ndb-port \ --with-ndb-port-base \
--with-federated-storage-engine

I've also done a side-by-side thinking it might be the client is
incompatible so did MySQL 4.0

./configure --prefix=/usr/local/mysqlclient \ --without-server

I've specified for the environment variables:

$LDFLAGS='-L/usr/local/mysql/include/ -L/usr/local/mysql/lib/mysql/lib
-L/usr/local/mysql/libexec/ -L/usr/local/mysql/'

then

$LDFLAGS='-L/usr/local/mysqlclient/include/
-L/usr/local/mysqlclient/lib/mysql/lib -L/usr/local/mysqlclient/libexec/
-L/usr/local/mysqlclient/'

Am I wasting my (and your) time posting this here, or is it really something
with the MySQL client libraries?



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

RE: Need older version of mysql (current version seeminly corrupts FTS tables)

[Steven Roussey]

 See:
  http://downloads.mysql.com/archives.php

Thank you. Nice link to have around.

 Key 3 is the FTS key. The others are a UNIQUE KEY (#1) and a KEY(#2).

Do you have the same values for full-text parameters (ft_mit_word_len
for example)?

Not at first. I had noticed that not long after I sent my message, and I
rebuilt all the tables with mysamchk -rf *MYI. That eliminated the
Duplicate Key errors, but not the Incorrect key file... Try to repair it
error. 

Did it again with 4.0.18 and had the same error. :(

Next I did mysamchk -o *MYI to really be sure, and I even used the 4.0.18
binary. Then I tried running 4.0.23 with debug turned on (somewhat) and
still get the error. I'll try again with 4.0.18 (or 17) with debug and see
if it has the same error at the same place.

Here is what I had in the trace file (note that the query is a replace cmd
that often is used to update an entry): Do I need more fine grain debug info
to find the error?


mysql_change_db: info: Use database: search
do_command: info: Command on TCP/IP (9) = 3 (Query)
dispatch_command: query: replace into forums_posts_1239959
(forumid,messageid,parent,rootmessageid,deleted,deleted_marked,approved,auto
respond,loginid,ip,user_id,author,email,title,message,search_forumid )
values
(2255626,1108700026,0,1108700026,'no','no','yes','no',1524436,1100232325,'1c
c48d0a485629a91e2b5634c122a339',
'[EMAIL PROTECTED]','[EMAIL PROTECTED]','Message text deleted for
privacy, but I can sent if needed','fid2255626')
thr_lock: info: write_wait.data: 0x0
mi_get_status: info: key_file: 28418048  data_file: 39236852
mi_write: error: Got error: 121 on write
_mi_writeinfo: info: operation: 1  tot_locks: 1
_mi_ck_delete: info: root_page: 19973120
_mi_prefix_search: info: key: '004'
_mi_prefix_search: info: key: '004'
_mi_prefix_search: info: key: '004'
_mi_prefix_search: info: key: '004'
d_search: error: Didn't find key
mi_update: error: key: 2  errno: 126
_mi_writeinfo: info: operation: 1  tot_locks: 1
my_message_sql: error: Message: 'Incorrect key file for table:
'forums_posts_1239959'. Try to repair it'
thr_unlock: info: updating status:  key_file: 28418048  data_file: 39236852
mi_lock_database: info: changed: 1  w_locks: 0


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

Need older version of mysql (current version seeminly corrupts FTS tables)

[Steven Roussey]

I have replaced one server with another, and the new one has everything new
(RHEL 3, newest updates) and MySQL 4.0.23 (old one was RH9 and MySQL
4.0.18).

We now get table corruptions constantly (it only takes a minute before
several tables get marked as crashed). I'd like to revert to the 4.0.18
version (which I stopped updating after having some other issue, which I can
no longer remember). Where can I download it

Anyhow, with the errors, I get these in my application's log (mysql does not
log any errors):

Duplicate entry 'Some text here ---f' for key 3
Incorrect key file for table: 'table_messages_1'. Try to repair it

Key 3 is the FTS key. The others are a UNIQUE KEY (#1) and a KEY(#2).

I'd like to make sure it is not a mysql version issue. I have seen similar
behavior in the 4.1 series, and don't want to try it now (and do all the
table conversions -- possibly each way if it fails).

Thanks!

-steve--



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

Re: MySQL as Email database [sort of OT]

[Steven Weintraut]

Hi! I just finished a project just like this.
We just put in a brand new mail server running PostFix on Mac OS X 
Panther. We added all the extra bells and whistles for spam and virus 
filtering, as well as squirrel mail

There is an single-line option in Postfix's config file to have all 
email going into and out of the server cc'd to a single mailbox

We setup a catch-all account that all email gets cc'd to, and then 
after playing around with a php scripts for a while, I actually wound 
up writing a program to pull the emails out of that account and pour 
them into mysql tables using a programming environment called 
RealBasic, which has a real nice built-in set of POP email and 
attachment handling classes, plus a mysql link as well

Now that the emails are in sql, we've also integrated searching and 
linking through some of our php web interfaces. All in all, so far, 
it's working well.

You could do the same in php, but I was able to write a real nice 
interface for the sql-link in realbasic in a few hours.

On Feb 10, 2005, at 12:35 PM, [EMAIL PROTECTED] wrote:
Brent Baisley [EMAIL PROTECTED] wrote on 02/10/2005 12:21:38 PM:
I am looking to store all incoming emails into a MySQL database. I've
looked into dbmail, but can't get it to compile under Mac OSX (I 
posted
a message on that list). I was wondering if anyone could point me in
another direction to use MySQL as an email message store. I don't need
a webmail interface, just a way of getting messages from a mail server
to a MySQL database. Preferably as a direct transfer, but it could be 
a
script that runs periodically.
Currently it seems the best path is using Perl, but I would think this
has been done before, just can't find it on google.

Thanks
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
How to do what you ask depends on how I read your question.  When you 
say
store incoming emails does that mean that you are writing an SMTP 
server
that will act as a mail destination or are you retrieving mail from an
SMTP server via POP3 or IMAP or some other mail retrieval protocol?

In either case your code ends up writing the contents of the message 
to a
MySQL database rather than to a local file. What you need to decide is 
how
many pieces you want to break the message into (how much metadata you 
want
to extract from each message). Your database structure will depend on 
your
information needs and how you intend to use the emails once you store 
them
however I can almost guarantee that the body of each message will need 
to
be stored in a TEXT field.

You will probably run out of room faster by storing whole messages in 
your
database than if you were storing them as files on the hard drive.
Capacity control is definitely something you want to consider in your
project design. You may want to store the messages (or parts of them) 
as
files and keep just the metadata in the database, but again that goes 
to
your intended purpose for this project.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

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

Best Query Question

[Steven Weintraut]

This has to be so simple, but my solution runs much slower than I would 
expect it to. I'm wondering if there is a more efficient way to do this 
type of query.

I have a table of email messages, I have another table containing all 
of the email addresses linked to each email message

I want to search for email messages in the email mesasge table that 
have both [EMAIL PROTECTED] and [EMAIL PROTECTED] as recipients in the 
address table

select * from mailarchive, addresses as address1, addresses as address2
where
(
mailarchive.MailArchiveRecordID=address1.emaillink
and
mailarchive.MailArchiveRecordID=address2.emaillink
)
and
address1.emailaddress=\[EMAIL PROTECTED]
and
address2.emailaddress=\[EMAIL PROTECTED] 
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Does it matter?

[Steven Grosch]

Please remove me from your email distribution.

Best Regards,
 
Steven Grosch
[EMAIL PROTECTED]
 
TEK LLC
610 260 9899

-Original Message-
From: mysql@lists.mysql.com [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 07, 2005 12:05 AM
To: [EMAIL PROTECTED]
Subject: Does it matter?

You have written a very good text, excellent, good work!



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

RE: Does it matter?

[Steven Grosch]

NO.

Best Regards,
 
Steven Grosch

-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 07, 2005 8:41 AM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: RE: Does it matter?

{snip]
Please remove me from your email distribution.
[/snip]

Do you not see an unsubscribe link at the bottom of this e-amil?




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

Help upgrading from 4.1.1 to 4.1.8

[Blumenkrantz, Steven]

I haven't found anything in the documentation that will help me upgrade
mysql from 4.1.1 to 4.1.8.  We have several databases running under
4.1.1.  How do they get migrated to run under 4.1.8?  How do we
migrate our user data over?  Can someone point me toward a FAQ that
might help me or the documentation that I haven't found yet, or tell me
how to do this?

Thanks!

Re: Foreign Key Error 1005:150

[steven . p . long]

Michael,

Thank you for your reply.  Here is a bit more info.  I changed the default 
table type to innodn in the my.ini file before creating the database, so all 
tables are innodb.  I tried the create statements with and without explicit 
index clauses with all permutations - same result each time.  I agree that 
something is wrong.  Did you try running the ddl you suggested below?  If so, 
did it work for you?

I downloaded the latest release from thr ANL mirror which says v 4.1.2 in the 
file name.  When I run MySQL, the system says it is 4.0.22.  Is the engine 
version different than the release version?  This is a secondaary issue however.

Steve


-- Original message -- 

 Something is wrong, but it's hard to say what. It seems unlikely you entered 
 exactly those commands and got an error only on the last ALTER TABLE. 
 First, you need InnoDB tables to support foreign keys, but you don't specify 
 the table engine in your CREATE statements. The default is MyISAM, unless 
 you've changed it. But that's not it. If they were MyISAM tables, neither 
 ALTER would work, but if they're all InnoDB, then all should work. Is it 
 possible that just table address is MyISAM? 
 
 In order to create a foreign key, you must have an index on the columns on 
 each side of the relationship. That is, you need person_id and address_id 
 to be indexed in both tables. Prior to 4.1.2, you had to do that by hand, 
 but in 4.1.2 and later it's automatic. Again, all or nothing, so not likely 
 relevant here. 
 
 
 Some other things to note (which are unrelated to the error): 
 
 There is no need to put an index on a column which has already been indexed 
 as the primary key. It's a waste of space that adds overhead to inserts. 
 
 You are relying on MySQL to create indexes for you in table person_address, 
 but I don't think it will make the best choices in this case. You need an 
 index on each column, but you most likely also need the combination of 
 person_id and address_id to be unique. In other words, if you let mysql 
 create indexes for you to satisfy the foreign key needs, you get separate 
 single-column indexes, but you need a combined column unique constraint 
 which renders one of the single column indexes redundant. 
 
 How about: 
 
 CREATE TABLE person 
 ( 
 person_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
 constraint person_pk PRIMARY KEY (person_id) 
 ) ENGINE=InnoDB; 
 
 CREATE TABLE address 
 ( 
 address_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
 constraint address_pk PRIMARY KEY (address_id) 
 ) ENGINE=InnoDB; 
 
 CREATE TABLE person_address 
 ( 
 person_id INT UNSIGNED NOT NULL, 
 address_id INT UNSIGNED NOT NULL, 
 CONSTRAINT person_address_pk PRIMARY KEY (person_id, address_id), 
 INDEX (address_id), 
 CONSTRAINT person_person_address_FK1 
 FOREIGN KEY (person_id) REFERENCES person (person_id); 
 CONSTRAINT address_person_address_FK1 
 FOREIGN KEY (address_id) REFERENCES address (address_id); 
 ) ENGINE=InnoDB; 
 
 Michael 
 
 [EMAIL PROTECTED] wrote: 
 
  I am unable to define a foreign key with the following three tables. I 
  am unable to find the error having searched the documentation and tried 
  several variations. 
  
  Note that I created the first two tables with and without the index 
  clause in the table ddl with no difference in outcome. 
  
  The three tables and the first foreign key, person_person_address_FK1, 
  create properly. The second foreign key, address_person_address_FK1, 
  causes the error. 
  
  Please help. 
  
  create table person ( 
  person_id int unsigned not null auto_increment, 
  constraint person_pk primary key (person_id), 
  index(person_id)); 
  
  create table address ( 
  address_id int unsigned not null auto_increment, 
  constraint address_pk primary key (address_id), 
  index(address_id)); 
  
  create table person_address ( 
  person_id int unsigned not null, 
  address_id int unsigned not null); 
  
  -- This statement works. 
  alter table person_address 
  add constraint person_person_address_FK1 
  foreign key (person_id) references person (person_id); 
  
  -- This statement fails. 
  alter table person_address 
  add constraint address_person_address_FK1 
  foreign key (address_id) references address (address_id); 
  
  Replies may be sent to [EMAIL PROTECTED] 
  
  Thank you! 
  
  Steve 
 

Foreign Key Error 1005:150

[steven . p . long]

I am unable to define a foreign key with the following three tables.  I am 
unable to find the error having searched the documentation and tried several 
variations.  

Note that I created the first two tables with and without the index clause in 
the table ddl with no difference in outcome.

The three tables and the first foreign key, person_person_address_FK1, create 
properly.  The second foreign key, address_person_address_FK1, causes the error.

Please help.

create table person (
   person_id int unsigned not null auto_increment,
   constraint person_pk primary key (person_id),
   index(person_id));

create table address (
   address_id int unsigned not null auto_increment,
   constraint address_pk primary key (address_id),
   index(address_id));

create table person_address (
   person_id int unsigned not null,
   address_id int unsigned not null);

-- This statement works.
alter table person_address
 add constraint person_person_address_FK1 
foreign key (person_id) references person (person_id); 

-- This statement fails.
alter table person_address
 add constraint address_person_address_FK1 
foreign key (address_id) references address (address_id); 

Replies may be sent to [EMAIL PROTECTED]

Thank you!

Steve

RE: Help with query performance anomaly

[Steven Roussey]

For production systems, I would never let the mysql optimizer guess a query
plan when there are joins of big tables and you know exactly how it should
behave. Once you think a query is finished, you should optimize it yourself.
Use STRAIGHT_JOIN and USE INDEX as found here in the manual:

http://dev.mysql.com/doc/mysql/en/JOIN.html

STRAIGHT_JOIN is identical to JOIN, except that the left table is always
read before the right table. This can be used for those (few) cases for
which the join optimizer puts the tables in the wrong order.

http://dev.mysql.com/doc/mysql/en/SELECT.html

The use of USE INDEX, IGNORE INDEX, FORCE INDEX to give the optimizer hints
about how to choose indexes is described in section 14.1.7.1 JOIN Syntax. 

-steve--



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

RE: Upgrading MySQL erased all data

[Steven Roussey]

Thanks. I passed this on and he found what was lost. I guess since the data
directory was owned by mysql, he could not find the databases when doing a
MacOS file search. I impressed upon him to use a separate datadir as we do
with our servers, thus bypassing this whole thing.

Thanks again!

-steve--




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

Upgrading MySQL erased all data

[Steven Roussey]

Installing MySQL 4.1.7 (upgrading from 4.1.3) on MacOS X erased the contents
of /usr/local/mysql/data -- the privs and data of the previous installation.

FYI

Luckily (and unfortunately) we have a backup of that database from last
week. (The guy that did it here in the office is still in a bit of a frenzy
though).

-s



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

Alternate directory for database

[Steven Buroff]

Is it possible to tell mysql to create the directory for a database in a
specified directory rather than in mysql's data directory?

VIEW

[Steven Lobbezoo]

Hi,
I've been looking at the post in the website, but cannot find a clear answehr 
about views.
Once is stated that it will be  available in 5.0, and once that it's allready 
in 4.1.
In the 5.0 features list etc. nothing is mentioned.
I like to know: when and how (docs) can I use views with mysql ?
Thanks,
Steven

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

mysqldump causes slave to stop

[Steven Boger]

Hello,

I am dumping a single innodb DB using mysqldump.  Everytime I do it, it
crashes the slave thread.

Below are the commands I'm using to dump [I also have tried
--delete-master-logs command in the actual data dump too (second line)
with the same problem.]  I am willing to hear advice on the best place to
put this.

Note, I am cycling the bin-logs every fine minutes to provide a type -of
point-in-time backup using 'mysqladmin flush-logs'.  Thats why the binlog
numbers are so high.  I have the flush command NOT run if mysqldump is
running.

Important side-note: I have removed --quick from these as it was causing
another innodb db on the same server to *appear* to LOCKUP during the
backup of THIS db... ideas?

Also. during this backup, all threads attached to the db go into Waiting
for release of readlock - Is this normal?  Master is 4.0.14, slave is
4.0.20... Slave was 4.0.14 and I still saw the problem.



MASTER DUMP COMMANDS

# safety first!!! make a daily backup of the XX schema!
  mysqldump --extended-insert --single-transaction --no-data --add-locks
--compress --skip-lock-tables --delete-master-logs XX | bzip2 -c 
/mnt/data/backups/`date +%Y%m%d`/XX.SCHEMA.sql.bz2

# at one minute after midnight, fire off the backup
  mysqldump --single-transaction --add-locks --compress --skip-lock-tables
--extended-insert --master-data XX | bzip2 -c 
/mnt/data/backups/`date +%Y%m%d`/XX.dump.sql.bz2

 /MASTER DUMP COMMANDS



SLAVE ERROR LOG

040605  0:09:20  Error reading packet from server: Could not open log file
(server_errno=1236)
040605  0:09:20  Got fatal error 1236: 'Could not open log file' from
master when reading data from binary log
040605  0:09:20  Slave I/O thread exiting, read up to log
'atlblg01-bin.262', position 714675

/SLAVE ERROR LOG




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

mysqldump causes slave to stop

[Steven Boger]

Hello,
I am dumping a single innodb DB using mysqldump.  Everytime I do it, it
crashes the slave thread.
Below are the commands I'm using to dump [I also have tried
--delete-master-logs command in the actual data dump too (second line)
with the same problem.]  I am willing to hear advice on the best place to
put this.
Note, I am cycling the bin-logs every fine minutes to provide a type -of
point-in-time backup using 'mysqladmin flush-logs'.  Thats why the binlog
numbers are so high.  I have the flush command NOT run if mysqldump is
running.
Important side-note: I have removed --quick from these as it was causing
another innodb db on the same server to *appear* to LOCKUP during the
backup of THIS db... ideas?
Also. during this backup, all threads attached to the db go into Waiting
for release of readlock - Is this normal?  Master is 4.0.14, slave is
4.0.20... Slave was 4.0.14 and I still saw the problem.

MASTER DUMP COMMANDS
# safety first!!! make a daily backup of the XX schema!
 mysqldump --extended-insert --single-transaction --no-data --add-locks
--compress --skip-lock-tables --delete-master-logs XX | bzip2 -c 
/mnt/data/backups/`date +%Y%m%d`/XX.SCHEMA.sql.bz2
# at one minute after midnight, fire off the backup
 mysqldump --single-transaction --add-locks --compress --skip-lock-tables
--extended-insert --master-data XX | bzip2 -c 
/mnt/data/backups/`date +%Y%m%d`/XX.dump.sql.bz2
/MASTER DUMP COMMANDS

SLAVE ERROR LOG
040605  0:09:20  Error reading packet from server: Could not open log file
(server_errno=1236)
040605  0:09:20  Got fatal error 1236: 'Could not open log file' from
master when reading data from binary log
040605  0:09:20  Slave I/O thread exiting, read up to log
'atlblg01-bin.262', position 714675
/SLAVE ERROR LOG

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

RE: Problems with MySQL 4.0.20

[Steven Roussey]

 Thank you very much for your bug report!
 And sorry if I doubted your report at the beginning; I hadn't thought
 of the rpm script.

No problem. I sometimes get bug reports that I know are impossible! Yet they
weren't. This one I would have barely noticed if it had not knocked the
slaves all offline.

Mysql query just in case.

-steve-



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

RE: Problems with MySQL 4.0.20

[Steven Roussey]

We start mysql with 'service mysql start' (we install from the RPM for
linux).

I've never seen mysql create binlog files under the name root before, and
after reverting to an old version, it doesn't again. It created a big mess
with all the slaves stuck at the end of an older binlog and not advancing to
the next one and complaining about corruption. Unfortunately, I don't have
the contents of the log (I think the size of the file was 79 bytes) since a
script here checks that all the slaves are at a certain point and then
deletes the logs on the master.

Log:

040519 17:53:41  mysqld started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-standard-log'  socket: '/tmp/mysql.sock'  port: 3306
040520 16:58:54  /usr/sbin/mysqld: Normal shutdown

040520 16:58:56  /usr/sbin/mysqld: Shutdown Complete

040520 16:58:56  mysqld ended

040520 16:59:10  mysqld started
040520 16:59:10  Warning: Asked for 196608 thread stack, but got 126976
/usr/sbin/mysqld: ready for connections.
Version: '4.0.20-standard-log'  socket: '/tmp/mysql.sock'  port: 3306
040520 16:59:14  Failed to open log (file '/binlogs/binlog.032', errno 13)
040520 16:59:34  Aborted connection 134 to db: 'db' user: 'aaa' host:
`something.i' (Got an error writing communication packets)
040520 16:59:36  Aborted connection 544 to db: 'db' user: 'aaa' host:
`something.i' (Got an error writing communication packets)
040520 16:59:36  Aborted connection 541 to db: 'db' user: 'aaa' host:
`something.i' (Got an error writing communication packets)

 Binary logs are created by the mysqld daemon (after mysqld possibly
 changes to uid of 'mysql' if --user=mysql was used). So in any case,
 if mysqld is running as user mysql (no matter if it was 'mysql' which
 started mysqld or if it was 'root' which did 'mysqld --user=mysql'),
 the binary logs are created by 'mysql'.
 If you have some binary logs created by 'root', it means 'mysqld' was
 run as 'root'; this is what you should really check (if you can
 provide us with the way you started mysqld ('service mysql start',
 whatever) and a listing of 'ps -elf | grep mysqld', we may be able to
 check if it is a MySQL bug but this is quite unlikely, from the above
 reasoning).
 
 Thank you!
 --
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Mr. Guilhem Bichot [EMAIL PROTECTED]
  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Software Developer
 /_/  /_/\_, /___/\___\_\___/   Bordeaux, France
___/   www.mysql.com



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

RE: Problems with MySQL 4.0.20

[Steven Roussey]

 Hmm, I don't see any changes in ft-related files since 4.0.18 that could
 cause it (there were bugfixes, but they affect only *searching* - that
 is MATCH - and not *updating*).
 
 Can you create a test case ?

Well, I put up a file in the secret folder a few days ago as referenced in a
bug report: http://bugs.mysql.com/?id=3870

There is a select statement that crashes the server found in the log file. I
put the files up and posted the bug from a remote computer and couldn't
write much about it at the time. 

The table is fine according to 'check table the_table_name'. The select
crashes it. The select also crashes it in older versions of myslq!! Doing a
repair in the old version and then doing the select in the old version is
OK. That is why I came to the conclusion that the file is corrupt. CHECK
TABLE does not find the corruption, however.

Another note on this: The tables I had the most problems with had FTS
indicies. I can't say that it is more than coincidental just yet. I am not
conclusive that it is a cause and effect relationship at this time.

Even returning to the older versions of mysql is not getting rid of all our
problems (we are seeing extremely high loads on the same stream of queries
as usual). Selectively repairing tables has helped. It may be that it is not
FTS related and we should repair all tables. We are going to try that
tonight.






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

Problems with MySQL 4.0.20

[Steven Roussey]

We had some servers that were upgraded from 4.0.17/18 to 4.0.20 and had
several problems thereafter:

1. Tables with FTS indices became corrupted, with queries on them causing
segfaults on the servers.

2. BinLog files were getting created with ownership of root, not mysql. Then
Mysql complains that it can not read the file and so goes and creates
another (which is fine and owned by mysql). All slaves to the master then
die with corruption warnings about the master.

3. All servers suddenly have a lot of connection errors:
   Aborted connection 109 to db: 'xyz' user: 'aaa' host: `something.i' (Got
timeout reading communication packets)

4. Thread stack warnings:
   Warning: Asked for 196608 thread stack, but got 126976

Reverting back to 4.0.17/18 fixed everything except one server still reports
#4 (better than all servers reporting it). All FTS tables needed to be
repaired (using the older version -- didn't test or trust the newer one).

-steve--




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

thread stack issues

[Steven Roussey]

Since going from 4.0.18 to 4.0.20 (or 4.0.19) I now receive these warnings
on startup:

040520 14:55:21  mysqld started
040520 14:55:21  Warning: Asked for 196608 thread stack, but got 126976
/usr/sbin/mysqld: ready for connections.
Version: '4.0.20-standard'  socket: '/tmp/mysql.sock'  port: 3306

I noticed on another server that it had the same problem with v4.0.18. So
some servers have a problem with this version and others do not. All have
the warning with 4.0.20. They are configured differently. What configuration
options would be effecting this?

-steve--



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