from:"Neil Tompkins"

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

2016-01-22 Thread Neil Tompkins

One point to add, that might be of interest is that the primary key and
foreign key for two of related tables is a VARCHAR(255), and my MySQL
version is '5.6.12-log'

On Fri, Jan 22, 2016 at 8:47 PM, Lisa Smith  wrote:

> Hi Neil,
>
> When you say you delete the current database, do you mean the database
> files only? Are you doing a complete restore?
>
> On Fri, Jan 22, 2016 at 1:23 PM, Neil Tompkins <
> neil.tompk...@googlemail.com> 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_m

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

2016-01-22 Thread Neil Tompkins

Sorry to clarify;  I have my database schema, and when the database crashes
at the moment, I delete that crashed database, create the new database
using the schema, and then restore the data using a import routine I have
written in C Sharp, to import data from a CSV file to MySQL tables.

The database works fine, but seems to crash when I shut the PC down.


On Fri, Jan 22, 2016 at 8:47 PM, Lisa Smith  wrote:

> Hi Neil,
>
> When you say you delete the current database, do you mean the database
> files only? Are you doing a complete restore?
>
> On Fri, Jan 22, 2016 at 1:23 PM, Neil Tompkins <
> neil.tompk...@googlemail.com> 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

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

2016-01-22 Thread Neil Tompkins

My disk drive has about 3 gb free space.  The sequence of events is

1. Create database
2. Restore the data
3. Use the database, SELECT data etc
4. Shut down PC
5. When I restart PC I get this error

mysql\innodb_table_stats.ibd. Cannot open tablespace

2016-01-22 Thread Neil Tompkins

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] InnoDB: Attempted to open a previously
opened tablespace. Previous tablespace mysql/innodb_table_stats uses space
ID: 1 at filepath: .\mysql\innodb_table_stats.ibd. Cannot open tablespace
sportstrader/event which uses space ID: 1 at filepath:
.\sportstrader\event.ibd
InnoDB: Error: could not open single-table tablespace file
.\sportstrader\event.ibd
InnoDB: We do not continue the crash recovery, because the table may

Re: MySQL dying?

2014-11-24 Thread Neil Tompkins

Personally I think people, myself included are using other resources like 
stackoverflow to get answers to my MySQL questions. 

> On 24 Nov 2014, at 17:27, Michael Dykman  wrote:
> 
> Please gentlemen,
> 
> It is a valid question if a somewhat hackneyed one.
> 
> MySQL continues to live on in many forms but obviously, would have to lose
> some ground in the face of the NoSQL solutions which are in vogue. The
> concepts of relational data are too powerful to stop being relevant but it
> is not longer the universal data store it once was seen as (The aplies to
> RDBMS in general).
> 
> Let's not ugly up this list (which I have been on for an absurdly long
> time) with flame wars.  It certainly does not have the bandwidth it once
> did, but flames are a terrible way to boost it.
> 
>> On Mon, Nov 24, 2014 at 12:04 PM, Ruben Safir  wrote:
>> 
>>> On 11/24/2014 10:00 AM, Johan De Meersman wrote:
>>> 
>>> - Original Message -
 From: "Ruben Safir" 
 Subject: Re: MySQL dying?
 
 Well, this mailing list is dead.  This is a mailing list that used to
 handle 70+ questions a day, or more.
>>> 
>>> 
>>> Is that why you feel the need to troll on posts from two years ago?
>>> 
>>> If you think it's dead, unsubscribe and go install MSSQL. If not, either
>> ask a question or stop wasting bandwidth.
>>> 
>>> 
>>> Bye now.
>> 
>> 
>> No, but I thought it was interesting to see what has happened within the
>> last 2 years.  Its not an issue of trolling.  But perhaps Oracle could
>> have learned something from the MYSQL community, which it seems to have
>> failed to.  If you find that this post was troll, then you've more than
>> missed the point, you missed the entire boat.
>> 
>> Bandwidth?  This list no longer produces bandwidth...  It has been
>> abandoned.  What would you have done in those days when we handled so
>> much mail in this list that there was no time to answer trolls... the
>> real trolls?
>> 
>> You have a bad attitude man, and it sucks.
>> 
>> Now, back to business..
>> 
>> Ruben
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql
> 
> 
> -- 
> - michael dykman
> - mdyk...@gmail.com
> 
> May the Source be with you.

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

MySQL UUID_SHORT() gives error Out of range value for column

2014-06-20 Thread Neil Tompkins

Hi,

I'm using MySQL 5.6.17 on Amazon Web Services RDS and when calling SELECT
UUID_SHORT() I'm getting a number bigger than 9223372036854775807. For
example the number I get is

12057145185130250250

The problem is in my table I have a column as BIGINT(20) unsigned, but when
storing a number 12057145185130250250 I get the error MySQL 22003

'MySQL 22003 Out of range value for column '' at row 1'

If I run SELECT UUID_SHORT() on our test server which is MySQL 5.6.11
(running on Windows 2008 64x) the result is as follows;

23526798209843216

I changed the column I'm trying to save my number to, as BIGINT(20)
unsigned, but still get this error.

Any ideas why ?

Cheers
Neil

Re: Update Column in table only if variable is Not NULL

2013-10-30 Thread Neil Tompkins

Shawn


What I need is that if I pass say 10 parameters/variables to a query, I
only want to update the column/field if the value passed is NOT NULL.


On Wed, Oct 30, 2013 at 3:41 AM, Shawn Green wrote:

> Hi,
>
> On 10/29/2013 9:52 PM, h...@tbbs.net wrote:
>
>> 2013/10/29 11:35 -0400, Shawn Green 
>>
> My favorite technique is the COALESCE function for this on a
>> column-by-column basis
>>
>> SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2)
>> 
>> but if MyVariable is NULL, FieldName1 reflects the attempt to change, not
>> change.
>>
>>
>>
> The way I understood the second explanation was like this.
>
> He wants to update a row of data. The FieldName1 field is always updated
> to the current date and time.  If any of the new values (passed in via
> variables) are not NULL for a specific column, replace the value on the row
> with the new value otherwise maintain the current value.
>
> He may yet mean something completely different than how I read it the
> second time.
>
> --
> Shawn Green
> MySQL Senior Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Update Column in table only if variable is Not NULL

2013-10-28 Thread Neil Tompkins

Hi Shawn

Thanks for your reply.  Maybe my example wasn't detailed enough.  Basically
the snippet of the UPDATE statement I provided shows updating only 1 field.
 However in my live working example, I have about 20 possible fields that
"might" need to be updated if the variable passed for each field is NOT
NULL.

Therefore, I felt this needs to be done at database level in the stored
procedure.  How can I accomplish this.

Thanks
Neil


On Mon, Oct 28, 2013 at 6:17 PM, Shawn Green wrote:

> Hello Neil,
>
> On 10/28/2013 2:06 PM, Neil Tompkins wrote:
>
>> Hi
>>
>> If I have a update statement like
>>
>> UPDATE MY_TABLE
>> SET FieldName1 = Now(), FieldName2 = :MyVariable
>> WHERE FieldName3 = 'Y'
>>
>> How can I only update the FieldName2 field if the value of MyVariable is
>> NOT NULL ?
>>
>> Thanks
>> Neil
>>
>>
> This needs to be a decision you make at the application level to not
> execute the UPDATE command in the first place. Not every decision needs to
> be made by the database. Plus, it will save you the time of a full network
> round trip just to get a result from the server that you affected 0 rows
> (parsing, optimizing, executing).
>
> Now, if this was just a typo and your :MyVariable was meant to be
> @MyVariable (a MySQL user variable) then you can put that test in the WHERE
> clause of the command
>
> UPDATE MY_TABLE
> SET FieldName1 = Now(), FieldName2 = @MyVariable
> WHERE FieldName3 = 'Y'  AND @MyVariable IS NOT NULL
>
> --
> Shawn Green
> MySQL Senior Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Update Column in table only if variable is Not NULL

2013-10-28 Thread Neil Tompkins

Hi

If I have a update statement like

UPDATE MY_TABLE
SET FieldName1 = Now(), FieldName2 = :MyVariable
WHERE FieldName3 = 'Y'

How can I only update the FieldName2 field if the value of MyVariable is
NOT NULL ?

Thanks
Neil

MySQL 5.6.13 on our Windows 2003 local access only

2013-08-30 Thread Neil Tompkins

I have installed MySQL 5.6.13 on our Windows 2003 server and need to
configure the service so that no external access is possible from a remote
IP addresses.

On the server it's self, the MySQL service will need to be accessed by IIS
hosting a ASP.NET application and web services.

I know that this can be set at user level using GRANT to USER @localhost
etc, but because in general I don't want external access I'd prefer it be
set at server level and just prevent network access.

Currently I've configured the My.ini file as follows;

[mysqld]
# The next three options are mutually exclusive to SERVER_PORT below.
skip-networking
enable-named-pipe
# The Pipe the MySQL Server will use# socket=mysql

bind-address = localhost
# The TCP/IP Port the MySQL Server will listen on# port=3306

I've tried to connect to the MySQL server through MySQL Workbench, using
Local Socket/Pipe but it doesn't work. I set the Local Socket/Pipe to MySQL.

The error I get is "Invalid for this platform protocol requested
(MYSQL_PROTOCOL_SOCKET)" . Can anyone help ?

How to update MySQL table based on 3 other tables

2013-08-24 Thread Neil Tompkins

I have the following four MySQL tables

Region
RegionId

City
CityId
RegionId

Hotel
HotelId
CityId

HotelRegion
HotelId
RegionId

I'm struggling to write a UPDATE statement to update the City table's
RegionId field from data in the HotelRegion table.

Basically how can I update the City table with the correct RegionId where
the HotelId in the HotelRegion table matches the City table's CityId.

This is my UPDATE statement at the moment

UPDATE City cSET c.RegionId = (SELECT DISTINCT(HotelRegion.RegionId)
FROM HotelRegion INNER JOIN Hotel ON Hotel.HotelID =
HotelRegion.HotelIDINNER JOIN City ON City.CityId = Hotel.CityIdWHERE
City.CityId = 1233)WHERE c.CityId = 1233

Edit MySQL Trigger in Workbench problem

2013-07-09 Thread Neil Tompkins

Hi,

I've created a Trigger and want to edit it.  Using MySQL Workbench, I can
Alter the table, and click Triggers and select the trigger action I want to
edit (on my local database, MySQL running on same PC)

However, if I try the exact same procedure on a Trigger on a remote
database, I don't see the Triggers under each action.

The only way these can be edited is to drop and create it again.

Thanks
Neil

Get Affected Rows after Stored Procedure COMMIT

2013-07-02 Thread Neil Tompkins

Hi,

I have a number of INSERT and UPDATE statements in a MySQL Stored
Procedure, that works in the form of START TRANSACTION followed by COMMIT.
 Also I am handling any EXCEPTION.

However, after calling COMMIT, how can I get the number of Rows that were
affected either INSERTED or UPDATTED ?

Thanks,
Neil

Re: Audit Table storage for Primary Key(s)

2013-05-31 Thread Neil Tompkins

The kind of look ups will be trying to diagnose when and by who applied a
update.  So the primary key of the audit is important.  My question is for
performance, should the primary key be stored as a indexed field like I
mentioned before, or should I have a actual individual field per primary key


On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen wrote:

> Again: Unless you can give some idea as to the kind of lookups you will be
> performing (which fields? Temporal values? etc.), it is impossible to give
> advice on the table structure. I wouldn't blame anyone for not being able
> to do so; saving data for debugging will always be a moving target and
> almost by definition you don't know today what you'll be looking for
> tomorrow.
>
> That's why I think that using CSV tables _the contents of which can
> subsequently be analyzed using any of a number of text file processing
> tools_ may indeed be your best initial option.
>
> On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how UUIDs
> are generated. If it's the same server that generates all the UUIDs, you
> won't get a lot of uniqueness for the amount of space you'll be using for
> your data and index; (2) Please do the math of just how many inserts you
> can do per second over the next 1.000 years if you use a longint
> auto-increment field for your PK.
>
> / Carsten
>
> On 31-05-2013 11:14, Neil Tompkins wrote:
>
>> Thanks for your response.  We expect to use the Audit log when looking
>> into
>> exceptions and/or any need to debug table updates.  I don't think a CSV
>> table would be sufficient as we are wanting to use a interface to query
>> this data at least on a daily basis if not weekly.
>>
>> I use UUID because we have currently 54 tables, of which probably 30 will
>> be audited.  So a INT PK wouldn't work because of the number of updates we
>> are applying.
>>
>>
>> On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen > >wrote:
>>
>>  On 30-05-2013 09:27, Neil Tompkins wrote:
>>>
>>>  Hi,
>>>>
>>>> I've created a Audit table which tracks any changed fields for multiple
>>>> tables.  In my Audit table I'm using a UUID for the primary key.
>>>>  However
>>>> I
>>>> need to have a reference back to the primary key(s) of the table
>>>> audited.
>>>>
>>>> At the moment I've a VARCHAR field which stores primary keys like
>>>>
>>>> 1
>>>> 1|2013-05-29
>>>> 2|2013-05-29
>>>> 2
>>>> 3
>>>> 1|2|2
>>>> etc
>>>>
>>>> Is this the best approach, or should I have a individual field in the
>>>> audit
>>>> table for all primary keys.  At the moment I think the max number of
>>>> primary keys on any given table is 3
>>>>
>>>> Thanks
>>>> Neil
>>>>
>>>>
>>>>  First you need to ask yourself how you expect to use the table in the
>>> future. Will you be looking up the data on a regular basis? Or will
>>> lookups
>>> only be something you will do in exceptional situtions?
>>>
>>> What is the intended goal of having a UUID for the primary key rather
>>> than, say, an integer - or having no PK at all?
>>>
>>> My immediate thought when reading this was "why even store that data in a
>>> table?" - if it's a simple log, use a log file. Especially if you don't
>>> know how you intend to search for data later on. There are many tools
>>> that
>>> are far superior to SQL when it comes to searching for text strings. You
>>> could even consider having a CSV table, which will give you an SQL
>>> interface to said text file.
>>>
>>> / Carsten
>>>
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:http://lists.mysql.com/mysql
>>>
>>>
>>>
>>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Audit Table storage for Primary Key(s)

2013-05-31 Thread Neil Tompkins

Thanks for your response.  We expect to use the Audit log when looking into
exceptions and/or any need to debug table updates.  I don't think a CSV
table would be sufficient as we are wanting to use a interface to query
this data at least on a daily basis if not weekly.

I use UUID because we have currently 54 tables, of which probably 30 will
be audited.  So a INT PK wouldn't work because of the number of updates we
are applying.


On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen wrote:

> On 30-05-2013 09:27, Neil Tompkins wrote:
>
>> Hi,
>>
>> I've created a Audit table which tracks any changed fields for multiple
>> tables.  In my Audit table I'm using a UUID for the primary key.  However
>> I
>> need to have a reference back to the primary key(s) of the table audited.
>>
>> At the moment I've a VARCHAR field which stores primary keys like
>>
>> 1
>> 1|2013-05-29
>> 2|2013-05-29
>> 2
>> 3
>> 1|2|2
>> etc
>>
>> Is this the best approach, or should I have a individual field in the
>> audit
>> table for all primary keys.  At the moment I think the max number of
>> primary keys on any given table is 3
>>
>> Thanks
>> Neil
>>
>>
> First you need to ask yourself how you expect to use the table in the
> future. Will you be looking up the data on a regular basis? Or will lookups
> only be something you will do in exceptional situtions?
>
> What is the intended goal of having a UUID for the primary key rather
> than, say, an integer - or having no PK at all?
>
> My immediate thought when reading this was "why even store that data in a
> table?" - if it's a simple log, use a log file. Especially if you don't
> know how you intend to search for data later on. There are many tools that
> are far superior to SQL when it comes to searching for text strings. You
> could even consider having a CSV table, which will give you an SQL
> interface to said text file.
>
> / Carsten
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Fwd: Audit Table storage for Primary Key(s)

2013-05-31 Thread Neil Tompkins

Any advice anyone ?

-- Forwarded message --
From: Neil Tompkins 
Date: Thu, May 30, 2013 at 8:27 AM
Subject: Audit Table storage for Primary Key(s)
To: "[MySQL]" 

Hi,

I've created a Audit table which tracks any changed fields for multiple
tables.  In my Audit table I'm using a UUID for the primary key.  However I
need to have a reference back to the primary key(s) of the table audited.

At the moment I've a VARCHAR field which stores primary keys like

1
1|2013-05-29
2|2013-05-29
2
3
1|2|2
etc

Is this the best approach, or should I have a individual field in the audit
table for all primary keys.  At the moment I think the max number of
primary keys on any given table is 3

Thanks
Neil

Audit Table storage for Primary Key(s)

2013-05-30 Thread Neil Tompkins

Hi,

I've created a Audit table which tracks any changed fields for multiple
tables.  In my Audit table I'm using a UUID for the primary key.  However I
need to have a reference back to the primary key(s) of the table audited.

At the moment I've a VARCHAR field which stores primary keys like

1
1|2013-05-29
2|2013-05-29
2
3
1|2|2
etc

Is this the best approach, or should I have a individual field in the audit
table for all primary keys.  At the moment I think the max number of
primary keys on any given table is 3

Thanks
Neil

Re: Temporary Tables with Triggers Problem

2013-05-29 Thread Neil Tompkins

OK, the data is going into the temp table.  But when I run the
command INSERT INTO AuditTrail SELECT tempHotelRateAvailability.* FROM
tempHotelRateAvailability;   from the TRIGGER it does not copy the data.

However if I run this query INSERT INTO AuditTrail SELECT
tempHotelRateAvailability.* FROM tempHotelRateAvailability;   from the
MySQL query editor (not the trigger), it copies the data find.

Any ideas ?


On Wed, May 29, 2013 at 3:02 PM, Ananda Kumar  wrote:

> But, does it work inside the trigger. If not, then based on the logic,
> there will not be any data, and data goes not get inserted from temp table
> to innodb table
>
>
> On Wed, May 29, 2013 at 7:29 PM, Neil Tompkins <
> neil.tompk...@googlemail.com> wrote:
>
>> I took the following lines of code slightly modified and it returned some
>> data using a normal Query Editor
>>
>> CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
>> varchar(36),UserId bigint(20),ActionType varchar(36),TableName
>> varchar(36),RowKey varchar(255),FieldName varchar(36),OldValue
>> varchar(255),NewValue varchar(255), LoggedOn TIMESTAMP);
>> INSERT INTO tempHotelRateAvailability VALUES
>> (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2, NOW());
>> INSERT INTO tempHotelRateAvailability VALUES
>> (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',4,4, NOW());
>> SELECT * FROM tempHotelRateAvailability;
>>
>>
>> On Wed, May 29, 2013 at 2:57 PM, Ananda Kumar  wrote:
>>
>>> did u check if data is getting inserted into tempHotelRateAvailability
>>>
>>>
>>> On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins <
>>> neil.tompk...@googlemail.com> wrote:
>>>
>>>> This is my Trigger which doesn't seem to work; but doesn't cause a error
>>>>
>>>>  DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;
>>>>
>>>> CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
>>>> varchar(36),UserId bigint(20),ActionType
>>>> enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
>>>> varchar(255),FieldName varchar(36),OldValue text,NewValue text);
>>>> IF NEW.RoomsToSell <> OLD.RoomsToSell THEN
>>>> INSERT INTO tempHotelRateAvailability VALUES
>>>> (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
>>>> CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
>>>> END IF;
>>>>
>>>> IF SELECT COUNT(*) FROM tempHotelRateAvailability > 0 THEN
>>>> INSERT INTO AuditTrail
>>>> SELECT tempHotelRateAvailability.* FROM
>>>> tempHotelRateAvailability;
>>>> END IF;
>>>>
>>>> DROP TEMPORARY TABLE tempHotelRateAvailability;
>>>>
>>>> However if I use this call in the Trigger and change a value in the
>>>> table it works fine;
>>>>
>>>> INSERT INTO AuditTrail
>>>> (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
>>>> LoggedOn)
>>>>  VALUES (UUID(),1,'UPDATE','HotelRateAvailability',
>>>> 1,'RoomsToSell',1,2, NOW());
>>>>
>>>>
>>>> On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar wrote:
>>>>
>>>>> can you please share the code of the trigger. Any kind of error your
>>>>> getting
>>>>>
>>>>>
>>>>> On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins <
>>>>> neil.tompk...@googlemail.com> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I've a trigger that writes some data to a temporary table; and at the
>>>>>> end
>>>>>> of the trigger writes all the temporary table data in one insert to
>>>>>> our
>>>>>> normal Innodb table.
>>>>>>
>>>>>> However, for some reason the trigger isn't copying the table from the
>>>>>> temporary table to the Innodb table.  If I write in the trigger the
>>>>>> inserts
>>>>>> to the Innodb table, it works fine.
>>>>>>
>>>>>> Any ideas why.  I'm running MySQL 5.6.
>>>>>>
>>>>>> Thanks
>>>>>> Neil
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>

Re: Temporary Tables with Triggers Problem

2013-05-29 Thread Neil Tompkins

I took the following lines of code slightly modified and it returned some
data using a normal Query Editor

CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType varchar(36),TableName
varchar(36),RowKey varchar(255),FieldName varchar(36),OldValue
varchar(255),NewValue varchar(255), LoggedOn TIMESTAMP);
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2, NOW());
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',4,4, NOW());
SELECT * FROM tempHotelRateAvailability;


On Wed, May 29, 2013 at 2:57 PM, Ananda Kumar  wrote:

> did u check if data is getting inserted into tempHotelRateAvailability
>
>
> On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins <
> neil.tompk...@googlemail.com> wrote:
>
>> This is my Trigger which doesn't seem to work; but doesn't cause a error
>>
>>  DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;
>>
>> CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
>> varchar(36),UserId bigint(20),ActionType
>> enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
>> varchar(255),FieldName varchar(36),OldValue text,NewValue text);
>> IF NEW.RoomsToSell <> OLD.RoomsToSell THEN
>> INSERT INTO tempHotelRateAvailability VALUES
>> (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
>> CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
>> END IF;
>>
>> IF SELECT COUNT(*) FROM tempHotelRateAvailability > 0 THEN
>> INSERT INTO AuditTrail
>> SELECT tempHotelRateAvailability.* FROM
>> tempHotelRateAvailability;
>> END IF;
>>
>> DROP TEMPORARY TABLE tempHotelRateAvailability;
>>
>> However if I use this call in the Trigger and change a value in the table
>> it works fine;
>>
>> INSERT INTO AuditTrail
>> (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
>> LoggedOn)
>>  VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
>> NOW());
>>
>>
>> On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar  wrote:
>>
>>> can you please share the code of the trigger. Any kind of error your
>>> getting
>>>
>>>
>>> On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins <
>>> neil.tompk...@googlemail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> I've a trigger that writes some data to a temporary table; and at the
>>>> end
>>>> of the trigger writes all the temporary table data in one insert to our
>>>> normal Innodb table.
>>>>
>>>> However, for some reason the trigger isn't copying the table from the
>>>> temporary table to the Innodb table.  If I write in the trigger the
>>>> inserts
>>>> to the Innodb table, it works fine.
>>>>
>>>> Any ideas why.  I'm running MySQL 5.6.
>>>>
>>>> Thanks
>>>> Neil
>>>>
>>>
>>>
>>
>

Re: Temporary Tables with Triggers Problem

2013-05-29 Thread Neil Tompkins

This is my Trigger which doesn't seem to work; but doesn't cause a error

DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType
enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
varchar(255),FieldName varchar(36),OldValue text,NewValue text);
IF NEW.RoomsToSell <> OLD.RoomsToSell THEN
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
END IF;

IF SELECT COUNT(*) FROM tempHotelRateAvailability > 0 THEN
INSERT INTO AuditTrail
SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;

END IF;

DROP TEMPORARY TABLE tempHotelRateAvailability;

However if I use this call in the Trigger and change a value in the table
it works fine;

INSERT INTO AuditTrail
(AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
LoggedOn)
 VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
NOW());


On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar  wrote:

> can you please share the code of the trigger. Any kind of error your
> getting
>
>
> On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins <
> neil.tompk...@googlemail.com> wrote:
>
>> Hi,
>>
>> I've a trigger that writes some data to a temporary table; and at the end
>> of the trigger writes all the temporary table data in one insert to our
>> normal Innodb table.
>>
>> However, for some reason the trigger isn't copying the table from the
>> temporary table to the Innodb table.  If I write in the trigger the
>> inserts
>> to the Innodb table, it works fine.
>>
>> Any ideas why.  I'm running MySQL 5.6.
>>
>> Thanks
>> Neil
>>
>
>

Temporary Tables with Triggers Problem

2013-05-29 Thread Neil Tompkins

Hi,

I've a trigger that writes some data to a temporary table; and at the end
of the trigger writes all the temporary table data in one insert to our
normal Innodb table.

However, for some reason the trigger isn't copying the table from the
temporary table to the Innodb table.  If I write in the trigger the inserts
to the Innodb table, it works fine.

Any ideas why.  I'm running MySQL 5.6.

Thanks
Neil

Editing existing Trigger MySQL 5.6

2013-05-29 Thread Neil Tompkins

Hi,

Using Workbench with MySQL 5.6 how do I edit a existing Trigger.  Do I need
to DROP the Trigger and create a new one ?  If that is the case how can you
run start command in a live environment ?

Thanks
Neil

Re: Version 5.6.2-m5 Boolean Datatype

2013-05-22 Thread Neil Tompkins

Hi Shawn

I plan in installing the latest MySQL version tomorrow.   Does MySQL not 
support Bool eg true and false

Neil

On 22 May 2013, at 19:05, shawn green  wrote:

> Hello Neil,
> 
> On 5/22/2013 1:05 PM, Neil Tompkins wrote:
>> Hi, Like the link states
>> 
>> "For clarity: a TINYINT(1) datatype does NOT ENFORCE a boolean value data
>> entry. For instance, it's still possible to insert a value of "2" (any
>> integer up to the TINYINT max value). I personally don't see the added
>> value of a 'BOOLEAN' synonym type which infact behaves unlike a boolean
>> should."
>> 
>> Has BOOL, BOOLEAN been taken out of MySQL 5.6 ?
>> 
>> 
>> On Wed, May 22, 2013 at 6:01 PM, Ian Simpson wrote:
>> 
>>> BOOLEAN is a synonym for TINYINT(1) in MySQL:
>>> 
>>> http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html
>>> 
>>> 
>>> On 22 May 2013 17:55, Neil Tompkins  wrote:
>>> 
>>>> Hi,
>>>> 
>>>> I've just created some tables that I designed using the MySQL Workbench
>>>> Model.  However, the database type BOOLEAN which was in my models has been
>>>> converted to TINYINT(1);  I'm currently running MySQL Version 5.6.2-m5 on
>>>> Windows 2008 server.
>>>> 
>>>> Any ideas why this has been removed ?
>>>> 
> 
> This is exactly the same behavior that MySQL has had for over a decade. 
> Nothing has been added or removed since release 4.1.0 (2003-04-03)
> http://dev.mysql.com/doc/refman/4.1/en/numeric-type-overview.html
> http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html
> 
> Also, why are you using a pre-release (milestone) version of 5.6 when the 
> full release (GA) versions of 5.6 are available?
> http://dev.mysql.com/doc/relnotes/mysql/5.6/en/
> 
> Regards,
> -- 
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 

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

Re: Version 5.6.2-m5 Boolean Datatype

2013-05-22 Thread Neil Tompkins

Hi, Like the link states

"For clarity: a TINYINT(1) datatype does NOT ENFORCE a boolean value data
entry. For instance, it's still possible to insert a value of "2" (any
integer up to the TINYINT max value). I personally don't see the added
value of a 'BOOLEAN' synonym type which infact behaves unlike a boolean
should."

Has BOOL, BOOLEAN been taken out of MySQL 5.6 ?

On Wed, May 22, 2013 at 6:01 PM, Ian Simpson wrote:

> BOOLEAN is a synonym for TINYINT(1) in MySQL:
>
> http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html
>
>
> On 22 May 2013 17:55, Neil Tompkins  wrote:
>
>> Hi,
>>
>> I've just created some tables that I designed using the MySQL Workbench
>> Model.  However, the database type BOOLEAN which was in my models has been
>> converted to TINYINT(1);  I'm currently running MySQL Version 5.6.2-m5 on
>> Windows 2008 server.
>>
>> Any ideas why this has been removed ?
>>
>> Neil
>>
>
>

Version 5.6.2-m5 Boolean Datatype

2013-05-22 Thread Neil Tompkins

Hi,

I've just created some tables that I designed using the MySQL Workbench
Model.  However, the database type BOOLEAN which was in my models has been
converted to TINYINT(1);  I'm currently running MySQL Version 5.6.2-m5 on
Windows 2008 server.

Any ideas why this has been removed ?

Neil

Re: Adding language support to tables

2013-05-01 Thread Neil Tompkins

"You want to say either "Germany" or "Deutschland", depending on a
"language_code" somewhere?"

*Yes, this is the exact issue I'm on on about. *

I like your idea.  Does anyone else have any thoughts before I try this way
?


On Wed, May 1, 2013 at 4:19 PM, Rick James  wrote:

> You want to say either "Germany" or "Deutschland", depending on a
> "language_code" somewhere?
>
> Remove the strings you have in those tables now; add about 4 new tables,
> each one paralleling the existing tables, but more rows and these columns:
> * id (the PK of the existing table)
> * language code (ENUM or TINYINT UNSIGNED indicating the language)
> * spelling (utf8 version for the language in question)
>
> There may be better ways to do your task, but see how this feels.  Sketch
> out the CREATE TABLEs, INSERTs and SELECTs.
>
> (Adding N columns for N languages is a maintenance and coding nightmare.
>  Tomorrow, you will need N+1 languages.)
>
> I would jettison the id in:
>   `country_id` INT NOT NULL ,
>   `country_code` CHAR(2) NOT NULL ,
> and use country_code as the PRIMARY KEY, and make it ASCII, not UTF8.
>  That would turn the 4-byte id into a 2-byte string.
>
> I gather you are using an new-enough NDB so that FOREIGN KEYs are
> implemented?
>
> > -Original Message-
> > From: Neil Tompkins [mailto:neil.tompk...@googlemail.com]
> > Sent: Wednesday, May 01, 2013 5:53 AM
> > To: [MySQL]
> > Subject: Adding language support to tables
> >
> > Hi,
> >
> > I've the following database structure of 4 tables for geographical
> > information
> >
> >
> >
> > CREATE  TABLE IF NOT EXISTS `mydb`.`country` (
> >
> >   `country_id` INT NOT NULL ,
> >
> >   `country_code` CHAR(2) NOT NULL ,
> >
> >   `name` VARCHAR(255) NOT NULL ,
> >
> >   PRIMARY KEY (`country_id`) ,
> >
> >   UNIQUE INDEX `country_code_UNIQUE` (`country_code` ASC) )
> >
> > ENGINE = ndbcluster
> >
> > DEFAULT CHARACTER SET = utf8
> >
> > COLLATE = utf8_unicode_ci;
> >
> >
> >
> >
> >
> > CREATE  TABLE IF NOT EXISTS `mydb`.`region` (
> >
> >   `region_id` INT NOT NULL ,
> >
> >   `name` VARCHAR(255) NOT NULL ,
> >
> >   `country_code` CHAR(2) NOT NULL ,
> >
> >   PRIMARY KEY (`region_id`) ,
> >
> >   INDEX `FK_country_code` (`country_code` ASC) ,
> >
> >   CONSTRAINT `FK_country_code`
> >
> > FOREIGN KEY (`country_code` )
> >
> > REFERENCES `mydb`.`country` (`country_code` )
> >
> > ON DELETE NO ACTION
> >
> > ON UPDATE NO ACTION)
> >
> > ENGINE = ndbcluster
> >
> > DEFAULT CHARACTER SET = utf8
> >
> > COLLATE = utf8_unicode_ci;
> >
> >
> >
> >
> >
> > CREATE  TABLE IF NOT EXISTS `mydb`.`city` (
> >
> >   `city_id` INT NOT NULL ,
> >
> >   `region_id` INT NOT NULL ,
> >
> >   `name` VARCHAR(255) NOT NULL ,
> >
> >   `latitude` DOUBLE NOT NULL ,
> >
> >   `longitude` DOUBLE NOT NULL ,
> >
> >   PRIMARY KEY (`city_id`) ,
> >
> >   INDEX `FK_region_id` (`region_id` ASC) ,
> >
> >   CONSTRAINT `FK_region_id`
> >
> > FOREIGN KEY (`region_id` )
> >
> > REFERENCES `mydb`.`region` (`region_id` )
> >
> > ON DELETE NO ACTION
> >
> > ON UPDATE NO ACTION)
> >
> > ENGINE = ndbcluster
> >
> > DEFAULT CHARACTER SET = utf8
> >
> > COLLATE = utf8_unicode_ci;
> >
> >
> >
> >
> >
> > CREATE  TABLE IF NOT EXISTS `mydb`.`district` (
> >
> >   `district_id` INT NOT NULL ,
> >
> >   `city_id` INT NOT NULL ,
> >
> >   `name` VARCHAR(255) NOT NULL ,
> >
> >   `latitude` DOUBLE NOT NULL ,
> >
> >   `longitude` DOUBLE NOT NULL ,
> >
> >   PRIMARY KEY (`district_id`) ,
> >
> >   INDEX `FK_city_id` (`city_id` ASC) ,
> >
> >   CONSTRAINT `FK_city_id`
> >
> > FOREIGN KEY (`city_id` )
> >
> > REFERENCES `mydb`.`city` (`city_id` )
> >
> > ON DELETE NO ACTION
> >
> > ON UPDATE NO ACTION)
> >
> > ENGINE = ndbcluster
> >
> > DEFAULT CHARACTER SET = utf8
> >
> > COLLATE = utf8_unicode_ci;
> >
> >
> >
> >
> > Basically I'm wanting to add language support for each table to
> > translate the name field in each instance.  All other information will
> > remain the same.
> >
> > Therefore is my best approach to add some sort of look-up table with
> > the translation...?
> >
> > Thanks
> > Neil
>

Adding language support to tables

2013-05-01 Thread Neil Tompkins

Hi,

I've the following database structure of 4 tables for geographical
information



CREATE  TABLE IF NOT EXISTS `mydb`.`country` (

  `country_id` INT NOT NULL ,

  `country_code` CHAR(2) NOT NULL ,

  `name` VARCHAR(255) NOT NULL ,

  PRIMARY KEY (`country_id`) ,

  UNIQUE INDEX `country_code_UNIQUE` (`country_code` ASC) )

ENGINE = ndbcluster

DEFAULT CHARACTER SET = utf8

COLLATE = utf8_unicode_ci;





CREATE  TABLE IF NOT EXISTS `mydb`.`region` (

  `region_id` INT NOT NULL ,

  `name` VARCHAR(255) NOT NULL ,

  `country_code` CHAR(2) NOT NULL ,

  PRIMARY KEY (`region_id`) ,

  INDEX `FK_country_code` (`country_code` ASC) ,

  CONSTRAINT `FK_country_code`

FOREIGN KEY (`country_code` )

REFERENCES `mydb`.`country` (`country_code` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = ndbcluster

DEFAULT CHARACTER SET = utf8

COLLATE = utf8_unicode_ci;





CREATE  TABLE IF NOT EXISTS `mydb`.`city` (

  `city_id` INT NOT NULL ,

  `region_id` INT NOT NULL ,

  `name` VARCHAR(255) NOT NULL ,

  `latitude` DOUBLE NOT NULL ,

  `longitude` DOUBLE NOT NULL ,

  PRIMARY KEY (`city_id`) ,

  INDEX `FK_region_id` (`region_id` ASC) ,

  CONSTRAINT `FK_region_id`

FOREIGN KEY (`region_id` )

REFERENCES `mydb`.`region` (`region_id` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = ndbcluster

DEFAULT CHARACTER SET = utf8

COLLATE = utf8_unicode_ci;





CREATE  TABLE IF NOT EXISTS `mydb`.`district` (

  `district_id` INT NOT NULL ,

  `city_id` INT NOT NULL ,

  `name` VARCHAR(255) NOT NULL ,

  `latitude` DOUBLE NOT NULL ,

  `longitude` DOUBLE NOT NULL ,

  PRIMARY KEY (`district_id`) ,

  INDEX `FK_city_id` (`city_id` ASC) ,

  CONSTRAINT `FK_city_id`

FOREIGN KEY (`city_id` )

REFERENCES `mydb`.`city` (`city_id` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = ndbcluster

DEFAULT CHARACTER SET = utf8

COLLATE = utf8_unicode_ci;




Basically I'm wanting to add language support for each table to translate
the name field in each instance.  All other information will remain the
same.

Therefore is my best approach to add some sort of look-up table with the
translation...?

Thanks
Neil

Re: MySQL Cluster or MySQL Cloud

2013-04-29 Thread Neil Tompkins

Hi Andrew,

Thanks for your response and the useful white paper.  I've read the
document in great detail.  I'm looking for the best up time possible for my
application and am still struggling to see the major differences with MySQL
cluster compared to MySQL in the Cloud on multiple servers; apart from
MySQL Cluster being much better solution for automatic failover including
IP failover.

Regards, Neil

On Mon, Apr 29, 2013 at 8:47 AM, Andrew Morgan wrote:

> Hi Neil,
>
>  I hate just sending people off to white papers but you might get some
> good insights by taking a look at the "MySQL Guide to High Availability
> Solutions" paper -
> http://www.mysql.com/why-mysql/white-papers/mysql-guide-to-high-availability-solutions/
>
> Regards, Andrew.
>
> Andrew Morgan - MySQL High Availability Product Management
> andrew.mor...@oracle.com
> @andrewmorgan
> www.clusterdb.com
>
> > -Original Message-
> > From: Neil Tompkins [mailto:neil.tompk...@googlemail.com]
> > Sent: 27 April 2013 23:28
> > To: [MySQL]
> > Subject: Fwd: MySQL Cluster or MySQL Cloud
> >
> > > If deploying MySQL in the Cloud with two MySQL servers with master to
> > master replication i have a good failover solution.
> > >
> > > Whats the different in terms of availability if we opted for MySQL
> Cluster
> > instead ?
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
>

Re: Design help

2013-04-21 Thread Neil Tompkins

Many thanks for your response.  Can yo u offer any advice with regards
usage of country_codes eg gb and regions, cities etc ?  I've been reading
up on http://en.wikipedia.org/wiki/ISO_3166 etc.  Should I be looking to
use a Surrogate key for countries ?  Or the country code like fr for France
?

Same with regions/states and cities and districts ?


On Sun, Apr 21, 2013 at 9:28 AM, Denis Jedig  wrote:

> Neil,
>
> Am 21.04.2013 08:47, schrieb Neil Tompkins:
>
>  Using joins I can obtain which country each city belongs too.  However,
>> should I consider putting a foreign key in the CITIES table referencing
>> the
>> countries_id ?  Or is it sufficient to access using a join ?
>>
>
> It depends. Adding a reference to countries into the cities table would
> break normalization and would require you to maintain the correct reference
> (e.g. through the use of ON UPDATE triggers).
>
> It might be beneficial to do so if you have a high number of queries for
> cities filtering for countries - having a direct reference obviously would
> spare you a JOIN execution and at least two index lookups.
>
> In your current example however, the data set will typically be small
> enough (in the order of 1,000 - 10,000 cities) so the query performance
> certainly would not be that much of an issue to justify the
> denormalization[1].
>
> [1] 
> http://en.wikipedia.org/wiki/**Denormalization<http://en.wikipedia.org/wiki/Denormalization>
> --
> Denis Jedig
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Design help

2013-04-20 Thread Neil Tompkins

Hi

I'm creating the following basic tables

COUNTRIES
countries_id
name

REGIONS
region_id
countries_id
name

CITIES
cities_id
region_id


Using joins I can obtain which country each city belongs too.  However,
should I consider putting a foreign key in the CITIES table referencing the
countries_id ?  Or is it sufficient to access using a join ?

Thanks
Neil

MySQL Cluster Solution

2013-03-07 Thread Neil Tompkins

Hi,

I've used in the past MySQL Community Server 5.x.  Everything is fine,
however I'm now wanting to implement a new High Availability solution and
am considering MySQL Cluster.  However, I heard that MySQL Cluster doesn't
support store procedures ?  Are there any other restrictions I need to be
aware of.

Thanks
Neil

Get lowest value

2012-12-14 Thread Neil Tompkins

Hi,

I've the following data

total, supplier_id, product_name, supplier_code
125,2,iPod,xyz123
100,1,iPod,abc123
145,3,iPod,1213113
245,4,iPod,12345

What query do I need to get the lowest total in this case 100 for
supplier_id 1 ?

Thanks
Neil

Re: Basic SELECT help

2012-11-22 Thread Neil Tompkins

Claudio

This is the solution i decided to go for as provided in a previous response.

Thanks
Neil

On 23 Nov 2012, at 00:41, Claudio Nanni  wrote:

> On 11/22/2012 04:10 PM, Ben Mildren wrote:
>> SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id;
> Ben you were almost there ;)
> 
> SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id HAVING COUNT(id)= of params>
> 
> The only bad is the hardcoded parameter in the HAVING, may be it might be 
> improved.
> 
> Anyway if the query is handwritten then you just hand-modify that too,
> if it is built from code I can't imagine counting the parameters in the code 
> being so hard.
> 
> Cheers
> 
> Claudio
> 
> 
>> 
>> On 22 November 2012 15:01, Neil Tompkins  
>> wrote:
>>> Michael,
>>> 
>>> Thanks this kind of works if I'm checking two types.  But what about if I
>>> have 5 types ?
>>> 
>>> On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman  wrote:
>>> 
>>>> response did not go to the list..
>>>> 
>>>> 
>>>> I assume that you mean the id must be associated with both type=5 AND
>>>> type=2 as opposed to type=5 OR type=2;
>>>> 
>>>> in some dialect of SQL (not mysql) you can do this:
>>>> select distinct id from 'table' where type=5
>>>> intersect
>>>> select distinct id from 'table' where type=2
>>>> 
>>>> 
>>>> As INTERSECT is not avilable under mysql, we will have to go the JOIN route
>>>> 
>>>> select distinct a.id from mytable a
>>>> inner join mytable b on (a.id=b.id)
>>>> where a.type= 2 and b.type = 5;
>>>> 
>>>>  - michael dykman
>>>> 
>>>> On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
>>>>  wrote:
>>>>> Hi,
>>>>> 
>>>>> I'm struggling with what I think is a basic select but can't think how to
>>>>> do it : My data is
>>>>> 
>>>>> id,type
>>>>> 
>>>>> 1000,5
>>>>> 1001,5
>>>>> 1002,2
>>>>> 1001,2
>>>>> 1003,2
>>>>> 1005,2
>>>>> 1006,1
>>>>> 
>>>>> From this I what to get a distinct list of id where the type equals 2
>>>> and 5
>>>>> Any ideas ?
>>>>> 
>>>>> Neil
>>>> 
>>>> 
>>>> --
>>>>  - michael dykman
>>>>  - mdyk...@gmail.com
>>>> 
>>>>  May the Source be with you.
>>>> 
>>>> 
>>>> --
>>>>  - michael dykman
>>>>  - mdyk...@gmail.com
>>>> 
>>>>  May the Source be with you.
>>>> 
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:http://lists.mysql.com/mysql
>>>> 
>>>> 
> 
> 
> -- 
> Claudio
> 

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

Re: Basic SELECT help

2012-11-22 Thread Neil Tompkins

Doing a EXPLAIN on the SELECT statement it is using "Using where; Using
temporary; Using filesort" with 14000 rows of data.  How best to improve
this; when I already have indexed on id and type

On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman  wrote:

> Assuming that (id,type) is unique in the source data, that is a pretty
> elegant method:
>
> > select id from
> > (select distinct id, count(*)
> > from my_table
> > where type in (2,5)
> > group by id
> > having count(*) = 2)a;
> >
>
> --
>  - michael dykman
>  - mdyk...@gmail.com
>
>  May the Source be with you.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Basic SELECT help

2012-11-22 Thread Neil Tompkins

Ignore that it does work fine. Sorry

On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman  wrote:

> Assuming that (id,type) is unique in the source data, that is a pretty
> elegant method:
>
> > select id from
> > (select distinct id, count(*)
> > from my_table
> > where type in (2,5)
> > group by id
> > having count(*) = 2)a;
> >
>
> --
>  - michael dykman
>  - mdyk...@gmail.com
>
>  May the Source be with you.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Basic SELECT help

2012-11-22 Thread Neil Tompkins

When trying this query I get

FUNCTION id does not exist

On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman  wrote:

> select id from
> > (select distinct id, count(*)
> > from my_table
> > where type in (2,5)
> > group by id
> > having count(*) = 2)a;
>

Re: Basic SELECT help

2012-11-22 Thread Neil Tompkins

By unique you mean that no id and type would be duplicated like

1,1
1,1

Yes it isn't possible for duplicate id and type in more than 1 row


On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman  wrote:

> Assuming that (id,type) is unique in the source data, that is a pretty
> elegant method:
>
> > select id from
> > (select distinct id, count(*)
> > from my_table
> > where type in (2,5)
> > group by id
> > having count(*) = 2)a;
> >
>
> --
>  - michael dykman
>  - mdyk...@gmail.com
>
>  May the Source be with you.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Basic SELECT help

2012-11-22 Thread Neil Tompkins

Do you know if I had multiple joins there would be a performance issue ?


On Thu, Nov 22, 2012 at 3:06 PM, Michael Dykman  wrote:

> Keep joining I think. In the absence of intersect (which incurs the cost
> of a query per type anyhow ), this join pattern is the only option I can
> think of.
>
> On 2012-11-22 10:01 AM, "Neil Tompkins" 
> wrote:
>
> Michael,
>
> Thanks this kind of works if I'm checking two types.  But what about if I
> have 5 types ?
>
> On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman  wrote:
>
>> >
>> > response did not go to the list..
>> >
>> >
>> > I assume that you mean the id must be associated with bo...
>>
>> >
>> >
>> > --
>> >  - michael dykman
>> >  - mdyk...@gmail.com
>> >
>> >  May the Source be with you.
>> >
>>
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe...
>>
>
>

Re: Basic SELECT help

2012-11-22 Thread Neil Tompkins

Michael,

Thanks this kind of works if I'm checking two types.  But what about if I
have 5 types ?

On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman  wrote:

> response did not go to the list..
>
>
> I assume that you mean the id must be associated with both type=5 AND
> type=2 as opposed to type=5 OR type=2;
>
> in some dialect of SQL (not mysql) you can do this:
> select distinct id from 'table' where type=5
> intersect
> select distinct id from 'table' where type=2
>
>
> As INTERSECT is not avilable under mysql, we will have to go the JOIN route
>
> select distinct a.id from mytable a
> inner join mytable b on (a.id=b.id)
> where a.type= 2 and b.type = 5;
>
>  - michael dykman
>
> On Thu, Nov 22, 2012 at 9:30 AM, Neil Tompkins
>  wrote:
> > Hi,
> >
> > I'm struggling with what I think is a basic select but can't think how to
> > do it : My data is
> >
> > id,type
> >
> > 1000,5
> > 1001,5
> > 1002,2
> > 1001,2
> > 1003,2
> > 1005,2
> > 1006,1
> >
> > From this I what to get a distinct list of id where the type equals 2
> and 5
> >
> > Any ideas ?
> >
> > Neil
>
>
>
> --
>  - michael dykman
>  - mdyk...@gmail.com
>
>  May the Source be with you.
>
>
> --
>  - michael dykman
>  - mdyk...@gmail.com
>
>  May the Source be with you.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Basic SELECT help

2012-11-22 Thread Neil Tompkins

How about if I have the following

SELECT DISTINCT id
FROM my_table
WHERE (type = 3 OR type  = 28 OR type  = 1)

In this instance, for the id 280149 it only has types 3 and 28 but *not *1.
 But using the OR statement returns id 280149




On Thu, Nov 22, 2012 at 2:53 PM, Benaya Paul  wrote:

> U can remove the type field it will work
> On Nov 22, 2012 8:21 PM, "Neil Tompkins" 
> wrote:
>
>> Basically I only what to return the IDs that have both types.
>>
>>
>> On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski > >wrote:
>>
>> > SELECT DISTINCT id FROM table WHERE type IN ('2','5')
>> >
>> > should work
>> >
>> >
>> > On 22 November 2012 14:30, Neil Tompkins > >wrote:
>> >
>> >> Hi,
>> >>
>> >> I'm struggling with what I think is a basic select but can't think how
>> to
>> >> do it : My data is
>> >>
>> >> id,type
>> >>
>> >> 1000,5
>> >> 1001,5
>> >> 1002,2
>> >> 1001,2
>> >> 1003,2
>> >> 1005,2
>> >> 1006,1
>> >>
>> >> From this I what to get a distinct list of id where the type equals 2
>> and
>> >> 5
>> >>
>> >> Any ideas ?
>> >>
>> >> Neil
>> >>
>> >
>> >
>>
>

Re: Basic SELECT help

2012-11-22 Thread Neil Tompkins

Basically I only what to return the IDs that have both types.


On Thu, Nov 22, 2012 at 2:39 PM, marek gutowski wrote:

> SELECT DISTINCT id FROM table WHERE type IN ('2','5')
>
> should work
>
>
> On 22 November 2012 14:30, Neil Tompkins wrote:
>
>> Hi,
>>
>> I'm struggling with what I think is a basic select but can't think how to
>> do it : My data is
>>
>> id,type
>>
>> 1000,5
>> 1001,5
>> 1002,2
>> 1001,2
>> 1003,2
>> 1005,2
>> 1006,1
>>
>> From this I what to get a distinct list of id where the type equals 2 and
>> 5
>>
>> Any ideas ?
>>
>> Neil
>>
>
>

Basic SELECT help

2012-11-22 Thread Neil Tompkins

Hi,

I'm struggling with what I think is a basic select but can't think how to
do it : My data is

id,type

1000,5
1001,5
1002,2
1001,2
1003,2
1005,2
1006,1

>From this I what to get a distinct list of id where the type equals 2 and 5

Any ideas ?

Neil

Extract text from string

2012-10-12 Thread Neil Tompkins

Hi,

Is there such a way in a MySQL query to extract the text "this is a test"
from the following strings as a example

http://www.domain.com/"; class="link">this is a
test
http://www.domain.com/"; title="this is a test"
class="link">link

Thanks
Neil

Re: Postal code searching

2012-04-24 Thread Neil Tompkins

At the moment im concentrating on london postal codes but future would be us 
zip codes too

On 24 Apr 2012, at 18:09, Rick James  wrote:

> Please be more precise about the rules.  In the US, "12345-6789" would become 
> "12345".  This would follow a different rule.
> 
> Is your rule "stop after the first digit"?  That gets quite messy in SQL, and 
> would be better done in an application code.
> 
> See also
> http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
> 
> RLIKE can distinguish digits from letters, but won't help you isolate them.
> 
>> -Original Message-
>> From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
>> Sent: Tuesday, April 24, 2012 9:11 AM
>> To: [MySQL]
>> Subject: Postal code searching
>> 
>> Hi
>> 
>> I've a number of different postal codes in a system for example
>> 
>> WC1B 5JA
>> WC1H 8EJ
>> W1J 7BX
>> W1H 7DL
>> NW1 1NY
>> 
>> I can use like statements for example
>> 
>> SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me
>> 
>> W1J 7BX
>> W1H 7DL
>> 
>> In addition I have a number of abbreviated postal codes like
>> 
>> W1
>> WC1
>> WC2
>> NW1
>> 
>> Now, if I know the postal code W1J 7BX what is the best way using a
>> MySQL query to get the abbreviated postal codes W1.  Same if I have the
>> postal code WC1H 8EJ, how do I get the abbreviated postal codes WC1
>> 
>> Can I use any matching patterns ?
>> 
>> Thanks,
>> Neil

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

Re: Design advice for hotel availability program

2012-02-20 Thread Neil Tompkins

Hi

I am trying to get some background information with regards the design.  I have 
a fair idea but would like advice from anyone that has previously worked on 
similar projects. 



On 20 Feb 2012, at 17:22, Jan Steinman  wrote:

> Where are your domain experts? You *are* consulting with them, no?
> 
> If you don't know the answers, and don't have access to domain experts to 
> help you, I would design for the most general case, and factor out exceptions 
> as they prove to be so. "Pre-optimization" for exceptions almost always turns 
> out to be a bad choice.
> 
>> From: Tompkins Neil 
>> 
>>   Am
>> I best using the following pattern
>> 
>> (1) Default rates/rooms stored in a generic table
>> (2) Any exceptions/changes/closed days to the daily rates are store in
>> a separate table.
>> (3) Any special offer exceptions are stored as a rule
>> 
>> All, should I consider that for any hotel, for any room, for any day I have
>> a record in a huge single table ???
> 
> 
> Everything we think we know about the world is a model... None of these is or 
> ever will be the real world. -- Donella H. Meadows
>  Jan Steinman, EcoReality Co-op 
> 
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 

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

Re: Chinese characters not displaying in Workbench latest version

2011-11-22 Thread Neil Tompkins

The problem was the character set not loaded on the OS

On 21 Nov 2011, at 22:08, Michael Cole  wrote:

> I think what he was asking is, Are you running the Mysql workbench on the 
> same 
> machine? You may not have the correct Fonts.
> 
> Being replicated the character sets of the two dbs should be the same have 
> you 
> checked that they actually are set to the same?
> 
> 
> 
> 
> On Monday, November 21, 2011 9:20:10 PM Neil Tompkins wrote:
>> MySQL workbench
>> 
>> On 21 Nov 2011, at 13:36, Chris Tate-Davies 
>  wrote:
>>> What are you using to view the data?
>>> 
>>> On Mon, 2011-11-21 at 08:22 -0500, h...@tbbs.net wrote:
>>>> ;>>> 2011/11/20 20:27 +, Tompkins Neil >>>>
>>>> Does anyone know why Chinese characters are not displaying correctly
>>>> in a replicated database on a slave machine ?  I'm just getting
>>>> square boxes. <<<<<<<<
>>>> What displays them? it sounds to me as if the display lacks something,
>>>> not so much MySQL.
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
> 

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

Re: Chinese characters not displaying in Workbench latest version

2011-11-21 Thread Neil Tompkins

MySQL workbench

On 21 Nov 2011, at 13:36, Chris Tate-Davies 
 wrote:

> What are you using to view the data?
> 
> 
> 
> On Mon, 2011-11-21 at 08:22 -0500, h...@tbbs.net wrote:
>> ;>>> 2011/11/20 20:27 +, Tompkins Neil 
>> Does anyone know why Chinese characters are not displaying correctly in a
>> replicated database on a slave machine ?  I'm just getting square boxes. 
>> 
>> What displays them? it sounds to me as if the display lacks something, not 
>> so much MySQL.
>> 
>> 
> 
> -- 
> Chris Tate-Davies 
> 
> 
> 
> -
> 
> 
> Registered Office: 15 Stukeley Street, London WC2B 5LT, England.
> Registered in England number 1421223
> 
> This message is for the designated recipient only and may contain privileged, 
> proprietary, or otherwise private information. If you have received it in 
> error, please notify the sender immediately and delete the original. Any 
> other use of the email by you is prohibited. Please note that the information 
> provided in this e-mail is in any case not legally binding; all committing 
> statements require legally binding signatures.
> 
> 
> http://www.inflightproductions.com
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
> 

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

How often should we upgrade MySQL version

2011-11-18 Thread Neil Tompkins

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

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

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

Re: Additional Software to Download and Install

2011-10-14 Thread Neil Tompkins

Try the MySQL workbench software 

On 14 Oct 2011, at 19:12, AndrewMcHorney  wrote:

> Hello
> 
> I just downloaded the MySql server software. I am now looking for software 
> that is gui based and will allow me to easily define a database, create 
> tables and to do updates of records within the tables. It would be fantastic 
> if the software had report generating capabilities and also would allow me to 
> create and execute sql commands and to write stored procedures to process the 
> data. The tables are going to be fairly simple.
> 
> Thanks
> Andrew
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
> 

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

Re: Index question

2011-10-11 Thread Neil Tompkins

In this instance would you create four indexes key(a) key(b) key(a,b) key (b,a) 
? Or is the decision based on the query response time ?

On 11 Oct 2011, at 13:40, Rik Wasmus  wrote:

>> Next question. If you have the two separate indexes and then do two
>> queries, one for a and one for b. If you then get a list of unique id's
>> of both, would it be faster to create an intersection yourself rather
>> than have the server do the legwork?
> 
> If you only have 2 unrelated indexes on a & b, it depends on the data, the 
> distribution of values, etc. No single answer here, test with your data and 
> you'll have the results.
> 
> If you need it often, I'd go for the combined index & let MySQL do the work, 
> which is probably fastest. 
> -- 
> Rik Wasmus
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
> 

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

Re: Index question

2011-10-11 Thread Neil Tompkins

Just to clarify having key indexes of (a,b) or (b,a) have no difference ?

On 11 Oct 2011, at 09:36, Johan De Meersman  wrote:

> - Original Message -
>> From: "Alex Schaft" 
>> 
>> If you have a table with columns A & B, and might do a where on A or
>> B, or an order by A, B, would single column indexes on A and B suffice
>> or would performance on the order by query be improved by an index on
>> A,B?
> 
> Depends on usage :-)
> 
> key (a, b) is good for "where a=.." or "where a=.. and b=.."
> key (b, a) is good for "where b=.." or "where b=.. and a=.."
>  (note that the sequence of a and b in the where clause is not important)
> key (a), key (b) is good for "where a=.." or "where b=.." but will only use 
> one index for "where a=.. and b=..".
> 
> I think work is ongoing on having the parser use multiple indices, but I'm 
> not sure where that's at.
> 
> 
> -- 
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
> 

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

Fwd: Large insert query gives MySQL server gone away

2011-10-10 Thread Neil Tompkins


> As per the subject we've a large insert query that gives up the error MySQL 
> server has gone away when we try to execute it.  Any ideas why ?

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

2011-10-07 Thread Neil Tompkins

Do you have any good documentation with regards creating indexes. Also 
information for explain statement and what would be the desired result of the 
explain statement?

On 7 Oct 2011, at 17:10, Michael Dykman  wrote:

> How heavily a given table is queried does not directly affect the index size, 
> only the number and depth of the indexes.
> 
> No, it is not that unusual to have the index file bigger.  Just make sure 
> that every index you have is justified by the queries you are making against 
> the table.
> 
>  - md
> 
> 
> On Fri, Oct 7, 2011 at 4:26 AM, Tompkins Neil  
> wrote:
> Is it normal practice for a heavily queried MYSQL tables to have a index file 
> bigger than the data file ?
> 
> 
> On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman  wrote:
> Only one index at a time can be used per query, so neither strategy is 
> optimal.  You need at look at the queries you intend to run against the 
> system and construct indexes which support them.
> 
>  - md
> 
> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins  
> wrote:
> Maybe that was a bad example.  If the query was name = 'Red' what index 
> should I create ?
> 
> Should I create a index of all columns used in each query or have a index on 
> individual column ?
> 
> 
> On 6 Oct 2011, at 17:28, Michael Dykman  wrote:
> 
>> For the first query, the obvious index on score will give you optimal 
>> results.
>> 
>> The second query is founded on this phrase: "Like '%Red%' " and no index 
>> will help you there.  This is an anti-pattern, I am afraid.  The only way 
>> your database can satisfy that expression is to test each and every record 
>> in the that database (the test itself being expensive as infix finding is 
>> iterative).  Perhaps you should consider this approach instead:
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
>> 
>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil 
>>  wrote:
>> Hi,
>> 
>> Can anyone help and offer some advice with regards MySQL indexes.  Basically
>> we have a number of different tables all of which have the obviously primary
>> keys.  We then have some queries using JOIN statements that run slowly than
>> we wanted.  How many indexes are recommended per table ?  For example should
>> I have a index on all fields that will be used in a WHERE statement ?
>>  Should the indexes be created with multiple fields ?  A example  of two
>> basic queries
>> 
>> SELECT auto_id, name, score
>> FROM test_table
>> WHERE score > 10
>> ORDER BY score DESC
>> 
>> 
>> SELECT auto_id, name, score
>> FROM test_table
>> WHERE score > 10
>> AND name Like '%Red%'
>> ORDER BY score DESC
>> 
>> How many indexes should be created for these two queries ?
>> 
>> Thanks,
>> Neil
>> 
>> 
>> 
>> -- 
>>  - michael dykman
>>  - mdyk...@gmail.com
>> 
>>  May the Source be with you.
> 
> 
> 
> -- 
>  - michael dykman
>  - mdyk...@gmail.com
> 
>  May the Source be with you.
> 
> 
> 
> 
> -- 
>  - michael dykman
>  - mdyk...@gmail.com
> 
>  May the Source be with you.

Re: MySQL Indexes

2011-10-07 Thread Neil Tompkins

Can you give more information as to why the second index would be of no use ?  

On 7 Oct 2011, at 18:24, Michael Dykman  wrote:

> No, I don't think it can be called.  It is a direct consequence of the
> relational paradigm.  Any implementation of an RDBMS has the same
> characteristic.
> 
> - md
> 
> On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald wrote:
> 
>> but could this not be called a bug?
>> 
>> Am 07.10.2011 18:08, schrieb Michael Dykman:
>>> When a query selects on field_a and field_b, that index can be used.  If
>>> querying on field_a alone, the index again is useful.  Query on field_b
>>> alone however, that first index is of no use to you.
>>> 
>>> On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps  wrote:
>>> 
>>>> This thread has sparked my interest. What is the difference between an
>>>> index on (field_a, field_b) and an index on (field_b, field_a)?
>>>> 
>>>> 
>>>> On 10/06/2011 07:43 PM, Nuno Tavares wrote:
>>>> 
>>>>> Neil, whenever you see multiple fields you'd like to index, you should
>>>>> consider, at least:
>>>>> 
>>>>> * The frequency of each query;
>>>>> * The occurrences of the same field in multiple queries;
>>>>> * The cardinality of each field;
>>>>> 
>>>>> There is a tool "Index Analyzer" that may give you some hints, and I
>>>>> think it's maatkit that has a tool to run a "query log" to find good
>>>>> candidates - I've seen it somewhere, I believe
>>>>> 
>>>>> Just remember that idx_a(field_a,field_b) is not the same, and is not
>>>>> considered for use, the same way as idx_b(field_b,field_a).
>>>>> 
>>>>> -NT
>>>>> 
>>>>> 
>>>>> Em 07-10-2011 00:22, Michael Dykman escreveu:
>>>>> 
>>>>>> Only one index at a time can be used per query, so neither strategy is
>>>>>> optimal.  You need at look at the queries you intend to run against
>> the
>>>>>> system and construct indexes which support them.
>>>>>> 
>>>>>> - md
>>>>>> 
>>>>>> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins
>>>>>> **wrote:
>>>>>> 
>>>>>> Maybe that was a bad example.  If the query was name = 'Red' what
>> index
>>>>>>> should I create ?
>>>>>>> 
>>>>>>> Should I create a index of all columns used in each query or have a
>>>>>>> index
>>>>>>> on individual column ?
>>>>>>> 
>>>>>>> 
>>>>>>> On 6 Oct 2011, at 17:28, Michael Dykman  wrote:
>>>>>>> 
>>>>>>> For the first query, the obvious index on score will give you optimal
>>>>>>> results.
>>>>>>> 
>>>>>>> The second query is founded on this phrase: "Like '%Red%' " and no
>> index
>>>>>>> will help you there.  This is an anti-pattern, I am afraid.  The only
>>>>>>> way
>>>>>>> your database can satisfy that expression is to test each and every
>>>>>>> record
>>>>>>> in the that database (the test itself being expensive as infix
>> finding
>>>>>>> is
>>>>>>> iterative).  Perhaps you should consider this approach instead:
>>>>>>> <http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
>>>>>>> natural-language.html<
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html>
>>>>>>>> 
>>>>>>> http://dev.mysql.com/doc/**refman/5.5/en/fulltext-**
>>>>>>> natural-language.html<
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html>
>>>>>>> 
>>>>>>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil<>>>>>> googlemail.com >
>>>>>>> neil.tompk...@googlemail.com>  wrote:
>>>>>>> 
>>>>>>> Hi,
>>>>>>>> 
>>>>>>>> Can anyone help and offer some advice with regards MySQL indexes.
>>>>>>>> Basically
>>>>>>>> we have a number of different tables all of which have

Re: MySQL Indexes

2011-10-06 Thread Neil Tompkins

Maybe that was a bad example.  If the query was name = 'Red' what index should 
I create ?

Should I create a index of all columns used in each query or have a index on 
individual column ?

On 6 Oct 2011, at 17:28, Michael Dykman  wrote:

> For the first query, the obvious index on score will give you optimal results.
> 
> The second query is founded on this phrase: "Like '%Red%' " and no index will 
> help you there.  This is an anti-pattern, I am afraid.  The only way your 
> database can satisfy that expression is to test each and every record in the 
> that database (the test itself being expensive as infix finding is 
> iterative).  Perhaps you should consider this approach instead:
> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
> 
> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil  
> wrote:
> Hi,
> 
> Can anyone help and offer some advice with regards MySQL indexes.  Basically
> we have a number of different tables all of which have the obviously primary
> keys.  We then have some queries using JOIN statements that run slowly than
> we wanted.  How many indexes are recommended per table ?  For example should
> I have a index on all fields that will be used in a WHERE statement ?
>  Should the indexes be created with multiple fields ?  A example  of two
> basic queries
> 
> SELECT auto_id, name, score
> FROM test_table
> WHERE score > 10
> ORDER BY score DESC
> 
> 
> SELECT auto_id, name, score
> FROM test_table
> WHERE score > 10
> AND name Like '%Red%'
> ORDER BY score DESC
> 
> How many indexes should be created for these two queries ?
> 
> Thanks,
> Neil
> 
> 
> 
> -- 
>  - michael dykman
>  - mdyk...@gmail.com
> 
>  May the Source be with you.

Fwd: Slow query - please help

2011-10-04 Thread Neil Tompkins

Can anyone help me ?


Begin forwarded message:

> From: Tompkins Neil 
> Date: 30 September 2011 20:23:47 GMT+01:00
> To: mark carson 
> Cc: "[MySQL]" 
> Subject: Re: Slow query - please help
> 

> I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions are 
> below, let me know if you need any more information.
> 
> CREATE TABLE `districts` (
>   `district_id` int(11) NOT NULL,
>   `language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
>   `city_id` int(11) DEFAULT NULL,
>   `country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
>   `latitude` double DEFAULT NULL,
>   `longitude` double DEFAULT NULL,
>   `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
>   `last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
>   PRIMARY KEY (`district_id`,`language_code`),
>   UNIQUE KEY `UNQ_folder_url` (`folder_url`),
>   KEY `IDX_country_code` (`country_code`),
>   KEY `IDX_enabled` (`enabled`),
>   KEY `IDX_folder_url` (`folder_url`),
>   KEY `IDX_language_code` (`language_code`),
>   KEY `IDX_latitude` (`latitude`),
>   KEY `IDX_longitude` (`longitude`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
> 
> CREATE TABLE `cities` (
>   `city_id` int(11) NOT NULL,
>   `language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
>   `country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
>   `nr_hotels` int(11) DEFAULT NULL,
>   `latitude` double DEFAULT NULL,
>   `longitude` double DEFAULT NULL,
>   `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
>   `last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
>   PRIMARY KEY (`city_id`,`language_code`),
>   UNIQUE KEY `UNQ_folder_url` (`folder_url`),
>   KEY `IDX_country_code` (`country_code`),
>   KEY `IDX_enabled` (`enabled`),
>   KEY `IDX_folder_url` (`folder_url`),
>   KEY `IDX_language_code` (`language_code`),
>   KEY `IDX_latitude` (`latitude`),
>   KEY `IDX_longitude` (`longitude`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
> 
> 
> CREATE TABLE `hotels` (
>   `hotel_id` int(11) NOT NULL,
>   `language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
>   `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `city_id` int(11) DEFAULT NULL,
>   `class_is_estimated` tinyint(4) DEFAULT NULL,
>   `class` tinyint(4) DEFAULT NULL,
>   `country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `hoteltype_id` int(11) DEFAULT NULL,
>   `is_closed` tinyint(4) DEFAULT NULL,
>   `latitude` double DEFAULT NULL,
>   `longitude` double DEFAULT NULL,
>   `maxrate` double DEFAULT NULL,
>   `minrate` double DEFAULT NULL,
>   `original_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `nr_rooms` int(11) DEFAULT NULL,
>   `preferred` int(11) DEFAULT NULL,
>   `ranking` int(11) DEFAULT NULL,
>   `review_nr` int(11) DEFAULT NULL,
>   `review_score` double DEFAULT NULL,
>   `zip` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `checkin_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `checkin_to` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `checkout_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `checkout_to` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `folder_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `short_profile` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
>   `enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
>   `last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
>   PRIMARY KEY (`hotel_id`,`language_code`),
>   UNIQUE KEY `UNQ_folder_url` (`folder_url`),
>   KEY `IDX_country_code` (`country_code`),
>

Re: replication between two tables in same database

2011-09-29 Thread Neil Tompkins

Am thinking now that it might be best to use MySQL 5.6 as this is a new project 
still in development and we will have the new FullText Search with Innodb

On 29 Sep 2011, at 18:43, Andrew Moore  wrote:

> Hey Neil, I read your question too quickly and jumped to the conclusion you 
> weren't sure MyISAM replicates in a mixed storage engine environment. 
> However, you could still potentially replicate the data using standard 
> replication and even on the same server (different instances). 
> 
> InstanceA ---> InstanceB
> MyISAM   InnoDB
> 
> You could use Triggers but be careful if you decide to do that because 
> triggers and replication don't play well together in some circumstances.
> 
> As a side note and something that was already mentioned to you, MySQL are 
> working on 5.6 and as part of that release InnoDB will have it's own 
> implementation of FT indexing.
> 
> HTH 
> 
> Andy
> 
> On Thu, Sep 29, 2011 at 6:00 PM, Tompkins Neil  
> wrote:
> Can you give me any pointers ?
> 
> 
> On Thu, Sep 29, 2011 at 5:59 PM, Andrew Moore  wrote:
> Hey Neil, it sure is possible through standard replication configuration.
> 
> Hth Andy
> 
> On Sep 29, 2011 5:57 PM, "Tompkins Neil"  wrote:
> > Hi
> > 
> > I've a Innodb and MyISAM table in the SAME database that I wish to replicate
> > the data between the two because I need to use FULLTEXT searching on
> > the MyISAM table. Is this possible ? If so how do I do it.
> > 
> > Thanks
> > Neil
> 
>

Re: replication between two tables in same database

2011-09-29 Thread Neil Tompkins

I'm not sure it would work in my environment of IIS and MySQL ?

On 29 Sep 2011, at 20:10, mos  wrote:

> Derek is right. The Sphynx search engine is much faster than MyISAM's full 
> text search engine. It will work on InnoDb tables so you don't have to export 
> the data to MyISAM.
> 
> Mike
> 
> At 01:43 PM 9/29/2011, you wrote:
>> But I could create an additional myisam table to overcome my problem 
>> providing I can get the data to synchronise between the two tables
>> 
>> On 29 Sep 2011, at 18:16, Reindl Harald  wrote:
>> 
>> > so mysql is currently the wrong database for your project
>> > sad but true, you can not have fulltext-search and innodb this time
>> >
>> > Am 29.09.2011 19:15, schrieb Tompkins Neil:
>> >> We've succesfully used FULLTEXT searching on another application that 
>> >> does not need Innodb tables.  In addition for
>> >> the FULLTEXT searching we use things like "IN BOOLEAN MODE" as well - so 
>> >> for our project Like %% is not enough.
>> >>
>> >> On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald > >> > wrote:
>> >>
>> >>you do not need any replication or myisam for
>> >>select * from table where field like '%input%';
>> >>
>> >>for most workloads this is enough and you have not the problem
>> >>with stop-words, minimum input length and so on
>> >>
>> >>Am 29.09.2011 19:07, schrieb Tompkins Neil:
>> >>> Yes, unless I can set-up some sort of replication between the two tables.
>> >>>
>> >>> On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald > >>> >wrote:
>> >>>
>>  please do NOT post off-list!
>> 
>>  so your only workaround is like '%whatever%' currently
>> 
>>  Am 29.09.2011 19:04, schrieb Tompkins Neil:
>>  The reason I'm using Innodb is because the usage of foreign keys in 
>>  short
>>  however, we need a couple of the tables to support FULLTEXT searching 
>>  which Innodb
>>  does not support.
>> 
>> 
>>  On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald >  >wrote:
>> 
>> >
>> >
>> > Am 29.09.2011 18:56, schrieb Tompkins Neil:
>> >> Hi
>> >>
>> >> I've a Innodb and MyISAM table in the SAME database that I wish to
>> > replicate
>> >> the data between the two because I need to use FULLTEXT searching on
>> >> the MyISAM table.  Is this possible ? If so how do I do it.
>> >
>> > in short: no
>> >
>> > in longer:
>> > why in the world are you using the wrong engine if you need
>> > fulltext-search?
>> > mysql 5.6 semmes to start support this in innodb, but currently not
>> >
>> > replication is based on binlogs and contains database/table so there is
>> > no dirty trick do this on one server except let run a script and copy
>> > the table-contents per cronjob
>> >
>> >
>> 
>>  --
>> 
>>  Mit besten GrÃ¼Ãen, Reindl Harald
>>  the lounge interactive design GmbH
>>  A-1060 Vienna, HofmÃ¼hlgasse 17
>>  CTO / software-development / cms-solutions
>>  p: +43 (1) 595 3999 33 , m: +43 
>>  (676) 40 221 40
>> >>
>>  icq: 154546673, http://www.thelounge.net/
>>  http://www.thelounge.net/signature.asc.what.htm
>> 
>> 
>> >>>
>> >>
>> >>--
>> >>
>> >>Mit besten GrÃ¼Ãen, Reindl Harald
>> >>the lounge interactive design GmbH
>> >>A-1060 Vienna, HofmÃ¼hlgasse 17
>> >>CTO / software-development / cms-solutions
>> >>p: +43 (1) 595 3999 33 , m: +43 
>> >> (676) 40 221 40
>> >>
>> >>icq: 154546673, http://www.thelounge.net/
>> >>
>> >>http://www.thelounge.net/signature.asc.what.htm
>> >>
>> >>
>> >
>> > --
>> >
>> > Mit besten GrÃ¼Ãen, Reindl Harald
>> > the lounge interactive design GmbH
>> > A-1060 Vienna, HofmÃ¼hlgasse 17
>> > CTO / software-development / cms-solutions
>> > p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
>> > icq: 154546673, http://www.thelounge.net/
>> >
>> > http://www.thelounge.net/signature.asc.what.htm
>> >
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
> 

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

Re: replication between two tables in same database

2011-09-29 Thread Neil Tompkins

But I could create an additional myisam table to overcome my problem providing 
I can get the data to synchronise between the two tables

On 29 Sep 2011, at 18:16, Reindl Harald  wrote:

> so mysql is currently the wrong database for your project
> sad but true, you can not have fulltext-search and innodb this time
> 
> Am 29.09.2011 19:15, schrieb Tompkins Neil:
>> We've succesfully used FULLTEXT searching on another application that does 
>> not need Innodb tables.  In addition for
>> the FULLTEXT searching we use things like "IN BOOLEAN MODE" as well - so for 
>> our project Like %% is not enough.
>> 
>> On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald > > wrote:
>> 
>>you do not need any replication or myisam for
>>select * from table where field like '%input%';
>> 
>>for most workloads this is enough and you have not the problem
>>with stop-words, minimum input length and so on
>> 
>>Am 29.09.2011 19:07, schrieb Tompkins Neil:
>>> Yes, unless I can set-up some sort of replication between the two tables.
>>> 
>>> On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald >> >wrote:
>>> 
 please do NOT post off-list!
 
 so your only workaround is like '%whatever%' currently
 
 Am 29.09.2011 19:04, schrieb Tompkins Neil:
 The reason I'm using Innodb is because the usage of foreign keys in short
 however, we need a couple of the tables to support FULLTEXT searching 
 which Innodb
 does not support.
 
 
 On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald >>> >wrote:
 
> 
> 
> Am 29.09.2011 18:56, schrieb Tompkins Neil:
>> Hi
>> 
>> I've a Innodb and MyISAM table in the SAME database that I wish to
> replicate
>> the data between the two because I need to use FULLTEXT searching on
>> the MyISAM table.  Is this possible ? If so how do I do it.
> 
> in short: no
> 
> in longer:
> why in the world are you using the wrong engine if you need
> fulltext-search?
> mysql 5.6 semmes to start support this in innodb, but currently not
> 
> replication is based on binlogs and contains database/table so there is
> no dirty trick do this on one server except let run a script and copy
> the table-contents per cronjob
> 
> 
 
 --
 
 Mit besten Grüßen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33 , m: +43 
 (676) 40 221 40
>>
 icq: 154546673, http://www.thelounge.net/
 http://www.thelounge.net/signature.asc.what.htm
 
 
>>> 
>> 
>>--
>> 
>>Mit besten Grüßen, Reindl Harald
>>the lounge interactive design GmbH
>>A-1060 Vienna, Hofmühlgasse 17
>>CTO / software-development / cms-solutions
>>p: +43 (1) 595 3999 33 , m: +43 
>> (676) 40 221 40
>>
>>icq: 154546673, http://www.thelounge.net/
>> 
>>http://www.thelounge.net/signature.asc.what.htm
>> 
>> 
> 
> -- 
> 
> Mit besten Grüßen, Reindl Harald
> the lounge interactive design GmbH
> A-1060 Vienna, Hofmühlgasse 17
> CTO / software-development / cms-solutions
> p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
> icq: 154546673, http://www.thelounge.net/
> 
> http://www.thelounge.net/signature.asc.what.htm
> 

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

Re: Design advice

2010-10-08 Thread Neil Tompkins


Hi Shawn

Thanks for your response.  In your experience do you think I should  
still retain the data used to generate the computed totals ? Or just  
compute the totals and disregard the data used ?


Regards
Neil

On 8 Oct 2010, at 19:46, "Shawn Green (MySQL)"  
 wrote:



Hi Neil,

On 10/5/2010 5:07 AM, Tompkins Neil wrote:

Hi

I have a number of tables of which I use to compute totals.  For  
example I

have

table : players_master
rec_id
players_name
teams_id
rating

I can easily compute totals for the field rating.  However, at the  
end of a

set period within my application, the values in the rating field are
changed. As a result my computed totals would then be incorrect.

Is the best way to overcome this problem to either compute the  
total and
store as a total value (which wouldn't change in the future), or to  
store
the rating values in a different table altogether and compute when  
required.
 If you need table information please let me know and I can send  
this.




Many databases designed for rapid, time-based reporting do exactly  
as you propose: build a table just to hold the aggregate of a time- 
interval of values.


Here's a rough example.

Let's say that you run a web site and you want to track your traffic  
levels.  Every second you may have thousands of hits, every hour  
hundreds of thousands of hits, and by the end of the week you may  
have hundreds of millions of individual data points to report on. To  
compute monthly stats, you are looking at a huge volume (billions)  
of data points unless you start aggregating.


Lets say you build tables like: stats_hour, stats_day, stats_week,  
and stats_month.


Every hour, you would take the last hour's worth of traffic and  
condense those values into the stats_hour table. At the end of the  
day, you take the previous 24 entries from stats_hour and compute a  
stats_day entry. Each level up aggregates the data from the level  
below.


Does that give you an idea about how other people may have solved a  
similar problem?


--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN


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

Re: Indexing question

2010-10-04 Thread Neil Tompkins

I've got a fair few number of queries to be checked over. Will send  
them tommorrow


On 4 Oct 2010, at 18:27, Gavin Towey  wrote:

Include the query, EXPLAIN output, and the relavant SHOW CREATE  
TABLE table \G output.  Someone should be able to offer suggestions.


-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Monday, October 04, 2010 8:54 AM
To: Joerg Bruehe
Cc: [MySQL]
Subject: Re: Indexing question

Jörg

Thanks for the useful reply.  Maybe I can EXPLAIN my select queries  
for you

to advise if any changes need to be made ?

Regards
Neil

On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe  
wrote:



Hi!


Neil Tompkins wrote:
Thanks for your reply. So should we create individual indexes on  
each

field or a multiple column index ??


This question cannot be answered without checking and measuring your
installation. The decision whether to create an index is always an  
act

of balancing:

- If there is an index, the database server can use it to find data
records by looking up the index, not scanning the base data.
This results in load reduction (both CPU and disk IO) and speeds up
query execution.

- If there is an index, the database server must maintain it whenever
data are altered (insert/update/delete), in addition to the base  
data.

This is increased load (both CPU and disk IO) and slows down data
changes.

So obviously you want to create only those indexes that are helpful  
for

query execution: you will never (voluntarily) create an index on a
column which isn't used in search conditions, or whose use is already
provided by other indexes.
Of the remaining candidate indexes, you will never (voluntarily)  
create
one that provides less gain in searches than it costs in data  
changes.


With MySQL, AFAIK there is the limitation that on one table only one
index can be used. As a result, the choice of indexes to create  
depends
on the searches executed by your commands, their relative  
frequency, and

the frequency of data changes.


To answer your other question: If you run aggregate functions (like
SUM(), MIN(), or MAX()) on all records of a table, their results  
could

be computed by accessing a matching index only. I don't know whether
MySQL does this, I propose you check that yourself using EXPLAIN.

If you run them on subsets of a table only, an index on that column  
will

not help in general.

In database implementations, there is the concept of a "covering  
index":

If you have an index on columns A and B of some table, its contents
(without the base data) would suffice to answer
 SELECT SUM(B) WHERE A = x
Again, I don't know whether MySQL does this, and I refer you to  
EXPLAIN.



HTH,
Jörg

--
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099  
Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d.  
Ven

Amtsgericht Muenchen: HRA 95603




This message contains confidential information and is intended only  
for the individual named.  If you are not the named addressee, you  
are notified that reviewing, disseminating, disclosing, copying or  
distributing this e-mail is strictly prohibited.  Please notify the  
sender immediately by e-mail if you have received this e-mail by  
mistake and delete this e-mail from your system. E-mail transmission  
cannot be guaranteed to be secure or error-free as information could  
be intercepted, corrupted, lost, destroyed, arrive late or  
incomplete, or contain viruses. The sender therefore does not accept  
liability for any loss or damage caused by viruses or errors or  
omissions in the contents of this message, which arise as a result  
of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt  
court, Sunnyvale, CA 94089, USA, FriendFinder.com


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

Re: Indexing question

2010-10-03 Thread Neil Tompkins

Following on from my previous email I have columns containing numbers  
which are then used in SUM and MIN/ MAX functions should these be  
indexed too ?


On 3 Oct 2010, at 16:44, Joerg Bruehe  wrote:


Hi Neil, all!


Tompkins Neil wrote:
So if you have individual indexes for example field_1, field_2 and  
field_3

etc and then perform a search like

WHERE field_1 = 10
AND field_3 = 'abc'

This wouldn't improve the search ?  You have to create a index for  
all

possible combined field searches ?


No - you didn't read Gavin's mail exact enough:


On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey  wrote:


[[...]]

Additionally indexes are always read left to right.  So an index on
('user_id', 'product_id') will help when doing WHERE user_id=N AND
product_id IN (1,2,3), but wouldn't help for just the condtion on
product_id.


What Gavin calls "left to right" is what I call "most significant
first", the result is the same:

In a multi-column index, the columns are listed in the order of their
significance. Any DBMS (this is not limited to MySQL) can use such an
index only if a condition for the first (= most significant) field 
(s) is

(are) specified.

Example: Assume the index is on fields A, B, and C in that order.

A statement "... where A = x and B = y and C = z" can use the index.
A statement "... where A = x and B = y" can use the index, limited to
the first two fields.
A statement "... where A = x" can use the index. the first field only.
A statement "... where A = x and C = z" can also use the index for A,
but will have to evaluate the condition on C by scanning all records
matching A.

A statement "... where B = y and C = z" cannot use the index, because
there is no condition on A.

If there are many searches based on A and C only (not B), and there  
are

many records matching A with different values of C, then an additional
index on these two columns may be helpful.

Compare the index with a phone book, which (typically) lists the  
entries

sorted by last name (most significant), then first name, then ... :
If you don't know the last name, you cannot profit from the sorting  
and

have to scan the wole book.



See the manual for full details on how mysql uses indexes:
http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html


HTH,
Jörg

--
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099  
Berlin

Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603



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

Re: Indexing question

2010-10-03 Thread Neil Tompkins

Thanks for your reply. So should we create individual indexes on each  
field or a multiple column index ??




On 3 Oct 2010, at 16:44, Joerg Bruehe  wrote:


Hi Neil, all!


Tompkins Neil wrote:
So if you have individual indexes for example field_1, field_2 and  
field_3

etc and then perform a search like

WHERE field_1 = 10
AND field_3 = 'abc'

This wouldn't improve the search ?  You have to create a index for  
all

possible combined field searches ?


No - you didn't read Gavin's mail exact enough:


On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey  wrote:


[[...]]

Additionally indexes are always read left to right.  So an index on
('user_id', 'product_id') will help when doing WHERE user_id=N AND
product_id IN (1,2,3), but wouldn't help for just the condtion on
product_id.


What Gavin calls "left to right" is what I call "most significant
first", the result is the same:

In a multi-column index, the columns are listed in the order of their
significance. Any DBMS (this is not limited to MySQL) can use such an
index only if a condition for the first (= most significant) field 
(s) is

(are) specified.

Example: Assume the index is on fields A, B, and C in that order.

A statement "... where A = x and B = y and C = z" can use the index.
A statement "... where A = x and B = y" can use the index, limited to
the first two fields.
A statement "... where A = x" can use the index. the first field only.
A statement "... where A = x and C = z" can also use the index for A,
but will have to evaluate the condition on C by scanning all records
matching A.

A statement "... where B = y and C = z" cannot use the index, because
there is no condition on A.

If there are many searches based on A and C only (not B), and there  
are

many records matching A with different values of C, then an additional
index on these two columns may be helpful.

Compare the index with a phone book, which (typically) lists the  
entries

sorted by last name (most significant), then first name, then ... :
If you don't know the last name, you cannot profit from the sorting  
and

have to scan the wole book.



See the manual for full details on how mysql uses indexes:
http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html


HTH,
Jörg

--
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099  
Berlin

Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603



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

Re: Best method to keep totals

2010-09-04 Thread Neil Tompkins

Thanks for all the useful information. I'm going to ensure the  
relevant fields are indexed and our db is optimised.




On 4 Sep 2010, at 16:10, Arthur Fuller  wrote:


100% agreed.

Arthur

The other exception is also where financial data is being stored. If  
you

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

error which needs to be investigated.

Mark



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

Database design help

2010-08-31 Thread Neil Tompkins


Hi

I've a soccer application consisting of managers, teams players and  
fixtures/results. Basically each manager will get points for each game  
which will depend on the result.


What would be the best table design bearing in mind that a manager can  
move to a different club.


My thought was to have a field in the fixtures/results table for the  
manager points but i think that I will also need a users field so that  
I can remember which points belong to which manager.


Is this the correct approach??

Cheers Neil

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

Re: Fixture List generation using MySQL

2010-08-20 Thread Neil Tompkins


Carl you don't wish go offer so sample code ?


On 19 Aug 2010, at 19:18, "Carl"  wrote:

I have written this in both C and Java.  It is very complex as, in  
real
life, you want to balance home and away, sequence the games so that  
the home
or away games are spread throughout the schedule, accomodate partial  
rounds
(10 team league where each team is to play 13 games), accomodate odd  
numbers
of teams (7,9,etc.) and create games for teams with short schedules  
and a

lot more.  In addition, this is only the beginning as, once you have a
playing schedule, you need to assign the games to space which is  
much more

complicated than creating the schedule.  Reporting the games is rather
trivial except for situations where games have been moved, teams have
dropped out or been forfeited out, etc.

Thanks,

Carl

Gavin - Sorry, didn't mean to send it to you privately... itchy  
trigger finger.


- Original Message - From: "Gavin Towey" 
To: "Tompkins Neil" ; "[MySQL]" >

Sent: Thursday, August 19, 2010 1:50 PM
Subject: RE: Fixture List generation using MySQL


That's almost a cartesean product; except you just want to eliminate  
results where a team would be paired up with itself.



create table teams ( id serial );

Query OK, 0 rows affected (0.02 sec)


insert into teams values (), (), (), ();

Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

[ff] test> select * from teams;
++
| id |
++
|  1 |
|  2 |
|  3 |
|  4 |
++
4 rows in set (0.00 sec)


select * from locations;

+--+
| name |
+--+
| home |
| away |
+--+
2 rows in set (0.00 sec)



select * from teams t1 JOIN teams t2;

+++
| id | id |
+++
|  1 |  1 |
|  2 |  1 |
|  3 |  1 |
|  4 |  1 |
|  1 |  2 |
|  2 |  2 |
|  3 |  2 |
|  4 |  2 |
|  1 |  3 |
|  2 |  3 |
|  3 |  3 |
|  4 |  3 |
|  1 |  4 |
|  2 |  4 |
|  3 |  4 |
|  4 |  4 |
+++
16 rows in set (0.00 sec)


With no join condition, we every possible combination of t1 paired  
with t2; however, this leads to the undesireable result that we have  
combinations like team 4 vs team 4.  So you just need to add a  
condition to prevent those rows from showing up:



select * from teams t1 JOIN teams t2 ON t1.id!=t2.id;

+++
| id | id |
+++
|  2 |  1 |
|  3 |  1 |
|  4 |  1 |
|  1 |  2 |
|  3 |  2 |
|  4 |  2 |
|  1 |  3 |
|  2 |  3 |
|  4 |  3 |
|  1 |  4 |
|  2 |  4 |
|  3 |  4 |
+++
12 rows in set (0.10 sec)


Notice you get both combinations of 2 vs 1 and 1 vs 2, so you could  
just call whichever team is in the first column as the "home team."



Regards,
Gavin Towey

-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Thursday, August 19, 2010 10:07 AM
To: [MySQL]
Subject: Re: Fixture List generation using MySQL

I'm looking at a routine / script to create the fixtures like

team 1 vs team 2
team 3 vs team 4
team 5 vs team 6 etc





On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley <
peter.braw...@earthlink.net> wrote:




I'm tasked with generating a list of fixtures from a table of teams,

whereby
each team plays each other home and away.  Does anyone have any
experience
generating such information using MySQL ?



Basically ...

select a.id,b.id from tbl a join tbl b on a.idb.id;

PB

-


On 8/19/2010 9:12 AM, Tompkins Neil wrote:


Hi,

I'm tasked with generating a list of fixtures from a table of  
teams,

whereby
each team plays each other home and away.  Does anyone have any
experience
generating such information using MySQL ?

Thanks for any input.

Regards
Neil






This message contains confidential information and is intended only  
for the individual named.  If you are not the named addressee, you  
are notified that reviewing, disseminating, disclosing, copying or  
distributing this e-mail is strictly prohibited.  Please notify the  
sender immediately by e-mail if you have received this e-mail by  
mistake and delete this e-mail from your system. E-mail transmission  
cannot be guaranteed to be secure or error-free as information could  
be intercepted, corrupted, lost, destroyed, arrive late or  
incomplete, or contain viruses. The sender therefore does not accept  
liability for any loss or damage caused by viruses or errors or  
omissions in the contents of this message, which arise as a result  
of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt  
court, Sunnyvale, CA 94089, USA, FriendFinder.com


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



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



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

Re: combined or single indexes?

2010-07-22 Thread Neil Tompkins

Thanks for the useful information. This is the answer I was. Looking  
for.

Neil

On 22 Jul 2010, at 22:25, "Jerry Schwartz"  wrote:

From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Thursday, July 22, 2010 4:50 PM
To: Jerry Schwartz
Cc: Shawn Green (MySQL); mysql@lists.mysql.com
Subject: Re: combined or single indexes?

Thanks for the information Jerry.  Just to confirm, you mentioned  
"if you only need one key then you only need one key".  My question  
was that this particular query was using SELECT against a primary  
key and other fields which are NOT indexed.  The EXPLAIN result was

table,type,possible_keys,key,key_len,ref,rows,Extra,
Products,const,PRIMARY,PRIMARY,8,const,1,,

So from this do I assume that if I'm always searching the PRIMARY  
KEY, that I don't need to index the other fields ?

[JS] I think I must have missed the start of this thread, because I  
don’t remember seeing the original query. The answer lies in your WH 
ERE clause, and in the number of records that would potentially qual 
ify. MySQL will ignore keys and do a full table scan if it decides t 
hat none of the keys would eliminate a big portion of the records. ( 
This is why I warned about small sample datasets.) If your query loo 
ks like

… WHERE `account_num` = 17 …

and account numbers are unique, then an index on `account_num`  
should be enough. If you are always and ONLY searching on the  
primary key, then the primary key is all you need. That’s usually no 
t the case, though. You’re probably going to want to search on other 
 things, sooner or later.

I’m not an expert on optimizing queries in MySQL, and there are prob 
ably differences between the storage engines, but I hope this helps.

Regards,

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

E-mail: je...@gii.co.jp

Web site: www.the-infoshop.com

Cheers

Neil

On Thu, Jul 22, 2010 at 9:27 PM, Jerry Schwartz   
wrote:

>-Original Message-
>From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
>Sent: Thursday, July 22, 2010 3:39 PM
>To: Shawn Green (MySQL)
>Cc: mysql@lists.mysql.com
>Subject: Re: combined or single indexes?
>
>Thanks for your reply, and sorry for not verifying in the manual.   
Another

>couple of questions I have :
>
>If I run a EXPLAIN query and SELECT against a primary key and  
SELECT fields
>which are not indexed, I assume that returned EXPLAIN statement as  
below,
>means I don't need to index additional fields providing the PRIMARY  
KEY is

>included in the SELECT statement ?
>
>table,type,possible_keys,key,key_len,ref,rows,Extra,
>Products,const,PRIMARY,PRIMARY,8,const,1,,
>

[JS] Your posts will be more legible if you use "\G" instead of ";"  
at the end

of an EXPLAIN.

As for the indexing, if you only need one key then you only need one  
key. Just

remember that when you test things with sample data, MySQL might make
surprising decisions based upon the amount of data. You'll only  
really know

what will happen if you have a substantial data set.

>Also, if I want to add a index to an existing table containing 9000  
records,

>how long should I expect this to take ?  Is it instant ?
>

[JS] Faster than you can type, I should think.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com

>Cheers
>Neil
>
>
>On Thu, Jul 22, 2010 at 5:20 PM, Shawn Green (MySQL) <
>shawn.l.gr...@oracle.com> wrote:
>
>> On 7/21/2010 1:02 PM, Tompkins Neil wrote:
>>
>>> Hi
>>>
>>> So Just running a basic query I get returned the following :
>>>
>>> table,type,possible_keys,key,key_len,ref,rows,Extra,
>>> Products,ALL,9884,where used,
>>>
>>> Therefore, I assume "*ALL*" is the worst possible type and  
should look at

>>> adding a an index to this particular field ?
>>>
>>>
>> Why assume when the manual is right there to remove all doubt?
>>
>> http://dev.mysql.com/doc/refman/5.1/en/explain-output.html
>> 
>> ALL
>>
>> A full table scan is done for each combination of rows from the  
previous
>> tables. This is normally not good if the table is the first table  
not

>> marked
>> const, and usually very bad in all other cases. Normally, you can  
avoid ALL

>> by adding indexes that enable row retrieval from the table based on
>> constant
>> values or column values from earlier tables.
>> 
>>
>>
>>
>>
>> --
>> Shawn Green
>> MySQL Principle Technical Support Engineer
>> Oracle USA, Inc.
>> Office: Blountville, TN
>>

Re: combined or single indexes?

2010-07-08 Thread Neil Tompkins



Should we be looking to create a index for all fields that we might be  
perform a select condition in a where clause for ?


On 9 Jul 2010, at 05:59, Johan De Meersman  wrote:


As many as you need, but no more :-)

The right indexes give you a boost in select performance, but every  
index also needs to be updated when your data changes.




On Thu, Jul 8, 2010 at 11:25 PM, Neil Tompkins > wrote:

How many indexes are recommended per table ??




On 7 Jul 2010, at 06:06, "Octavian Rasnita" > wrote:


Hi,

MySQL can use a single index in a query as you've seen in the result  
of explain.
Of course it is better to have an index made of 2 or more columns  
because it will match better the query.


But if I remember well, the in() function  can't use an index.
And I think it also can't use an index if you use OR operators like:

select foo from table where a=1 or a=2;

So for your query the single-column index for the second column is  
enough.


I've seen some tricks for using a faster method by using union and 2- 
column index, something like:


select foo from table where a=1 and b<1234
union
select foo from table where a=2 and b<1234
union
select foo from table where a=3 and b<1234

This might be faster in some cases because the query would be able  
to use the 2-column index, and especially if the content of those  
columns is made only of numbers, because in that case the query will  
use only the index, without getting data from the table.


--
Octavian

- Original Message - From: "Bryan Cantwell" >

To: 
Sent: Tuesday, July 06, 2010 6:41 PM
Subject: combined or single indexes?


Is there a benefit to a combined index on a table? Or is multiple  
single

column indexes better?

If I have table 'foo' with columns a, b, and c. I will have a query
like:
select c from foo where a in (1,2,3) and b < 12345;

Is index on a,b better in any way than an a index and a b index?
An explain with one index sees it but doesn't use it (only the where)
and having 2 indexes sees both and uses the one on b.

Am I right to think that 2 indexes are better than one combined one?

thx,
Bryancan



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/mysql?unsub=octavian.rasn...@ssifbroker.ro


__ Information from ESET NOD32 Antivirus, version of virus  
signature database 5257 (20100707) __


The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




__ Information from ESET NOD32 Antivirus, version of virus  
signature database 5257 (20100707) __


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=neil.tompk...@googlemail.com


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




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

Re: combined or single indexes?

2010-07-08 Thread Neil Tompkins


How many indexes are recommended per table ??



On 7 Jul 2010, at 06:06, "Octavian Rasnita" > wrote:



Hi,

MySQL can use a single index in a query as you've seen in the result  
of explain.
Of course it is better to have an index made of 2 or more columns  
because it will match better the query.


But if I remember well, the in() function  can't use an index.
And I think it also can't use an index if you use OR operators like:

select foo from table where a=1 or a=2;

So for your query the single-column index for the second column is  
enough.


I've seen some tricks for using a faster method by using union and 2- 
column index, something like:


select foo from table where a=1 and b<1234
union
select foo from table where a=2 and b<1234
union
select foo from table where a=3 and b<1234

This might be faster in some cases because the query would be able  
to use the 2-column index, and especially if the content of those  
columns is made only of numbers, because in that case the query will  
use only the index, without getting data from the table.


--
Octavian

- Original Message - From: "Bryan Cantwell" >

To: 
Sent: Tuesday, July 06, 2010 6:41 PM
Subject: combined or single indexes?


Is there a benefit to a combined index on a table? Or is multiple  
single

column indexes better?

If I have table 'foo' with columns a, b, and c. I will have a query
like:
select c from foo where a in (1,2,3) and b < 12345;

Is index on a,b better in any way than an a index and a b index?
An explain with one index sees it but doesn't use it (only the where)
and having 2 indexes sees both and uses the one on b.

Am I right to think that 2 indexes are better than one combined one?

thx,
Bryancan



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/mysql?unsub=octavian.rasn...@ssifbroker.ro


__ Information from ESET NOD32 Antivirus, version of virus  
signature database 5257 (20100707) __


The message was checked by ESET NOD32 Antivirus.

http://www.eset.com





__ Information from ESET NOD32 Antivirus, version of virus  
signature database 5257 (20100707) __


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=neil.tompk...@googlemail.com



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

Re: ssl questions

2009-10-12 Thread Neil Tompkins


We are looking to install on windows.


Sent from my iPod

On 11 Oct 2009, at 18:02, muhammad subair  wrote:


Hi,

Maybe, first you can check MySQL documentation [0], [1]

[0] http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html
[1] http://dev.mysql.com/doc/refman/5.0/en/secure-basics.html

--  
Muhammad Subair


On Thu, Oct 8, 2009 at 6:03 PM, Tompkins Neil > wrote:

Hi

I wondered how you are getting on with installing SSL for mysql 5.1  
server.
We are looking to install it too.  Do you have any pointers/ 
recommendations

?

Regards
Neil

On Tue, Sep 8, 2009 at 11:36 PM, qt4x11  wrote:

> I'm trying to set up my mysql 5.1 server to work over ssl, I'm  
following

> the
> directions at
> http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html.
>  I've confirmed that my server supports ssl
>
> mysql> SHOW VARIABLES LIKE 'have_ssl';
> +---+---+
> | Variable_name | Value |
> +---+---+
> | have_ssl  | YES   |
> +---+---+
>

Table Design

2008-07-15 Thread Neil Tompkins

Hi, I've the following table design (attached txt file, for some reason the 
content was being blocked) and I'd like any advice if this is the correct 
method/design. 
 
Thanks for any comments. Neil
_
Play and win great prizes with Live Search and Kung Fu Panda
http://clk.atdmt.com/UKM/go/101719966/direct/01/
 
TableName:ProductMaster
ProductMasterID
FriendlyProductName

TableName:ProductContent
ProductContentID
ProductName
Site
Language

TableName:ProductLookup
ProductLookupID
ProductContentID

TableName:Products
ProductsID
ProductSupplier
Cost
 
Below is my query extracting the data :
 
SELECT ProductName, ProductSupplier 
FROM Products
INNER JOIN ProductLookup ON ProductMaster.ProductMasterID = 
ProductLookup.ProductLookup
INNER JOIN Products ON ProductLookup.ProductID = Products.ProductsID
INNER JOIN ProductContent ON Products.ProductsID = 
ProductContent.ProductContentID
WHERE ProductMaster.ProductMasterID = 1
AND ProductContent.Site = "mysite.com"
AND ProductContent.Language = "eng"
 


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

RE: replace chr(10) in field

2008-05-15 Thread Neil Tompkins

I've overcome the problem by using a RichText memo box.  Thanks for your help 
in any case.
 
Neil



> From: [EMAIL PROTECTED]> To: [EMAIL PROTECTED]> CC: mysql@lists.mysql.com> 
> Subject: RE: replace chr(10) in field> Date: Wed, 14 May 2008 19:18:07 +> 
> > I thought this, but when I display the information in a Memo box in my C++ 
> builder application I get little square boxes (binary type chars). And all 
> the information is displayed on the same line.> > Any ideas why ? > > > > > 
> Date: Wed, 14 May 2008 13:08:04 -0500> From: [EMAIL PROTECTED]> To: [EMAIL 
> PROTECTED]> CC: mysql@lists.mysql.com> Subject: Re: replace chr(10) in field> 
> > Neil Tompkins wrote:> > Hi,> > > > I've got some data in our fields which 
> contain a carriage return 'chr(10)', as saved using a ASP page. I'm now 
> trying to extract the information from a different system, however the saved 
> chr(10) are showing as binary values.> > > > What would be the best way for 
> my to replace chr(10) to a \n ? I tried using the mysql REPLACE() function, 
> but it did not appear to work.> > > > Thanks,> > Neil> > 
> _> > > > All 
> new Live Search at Live.com> > > > 
> http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/> > Chr(10) is a 
> \n.> Chr(13) is a \r.> -- > Gerald L. Clark> Sr. V.P. Development> Supplier 
> Systems Corporation> Unix since 1982> Linux since 1992> 
> _> > All new 
> Live Search at Live.com> > 
> http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
_

All new Live Search at Live.com

http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/

RE: replace chr(10) in field

2008-05-14 Thread Neil Tompkins

I thought this, but when I display the information in a Memo box in my C++ 
builder application I get little square boxes (binary type chars).  And all the 
information is displayed on the same line.
 
Any ideas why ? 



> Date: Wed, 14 May 2008 13:08:04 -0500> From: [EMAIL PROTECTED]> To: [EMAIL 
> PROTECTED]> CC: mysql@lists.mysql.com> Subject: Re: replace chr(10) in field> 
> > Neil Tompkins wrote:> > Hi,> > > > I've got some data in our fields which 
> contain a carriage return 'chr(10)', as saved using a ASP page. I'm now 
> trying to extract the information from a different system, however the saved 
> chr(10) are showing as binary values.> > > > What would be the best way for 
> my to replace chr(10) to a \n ? I tried using the mysql REPLACE() function, 
> but it did not appear to work.> > > > Thanks,> > Neil> > 
> _> > > > All 
> new Live Search at Live.com> > > > 
> http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/> > Chr(10) is a 
> \n.> Chr(13) is a \r.> -- > Gerald L. Clark> Sr. V.P. Development> Supplier 
> Systems Corporation> Unix since 1982> Linux since 1992
_

All new Live Search at Live.com

http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/

replace chr(10) in field

2008-05-14 Thread Neil Tompkins

Hi,
 
I've got some data in our fields which contain a carriage return 'chr(10)', as 
saved using a ASP page.  I'm now trying to extract the information from a 
different system, however the saved chr(10) are showing as binary values.
 
What would be the best way for my to replace chr(10) to a \n ? I tried using 
the mysql REPLACE() function, but it did not appear to work.
 
Thanks,
Neil
_

All new Live Search at Live.com

http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/

RE: Query execution time - MySQL

2008-05-14 Thread Neil Tompkins

Thanks for your help.  In the end I've decided to use GetTickCount()
 
Neil



> Date: Wed, 14 May 2008 13:44:22 +0100> From: [EMAIL PROTECTED]> To: [EMAIL 
> PROTECTED]> CC: mysql@lists.mysql.com> Subject: Re: Query execution time - 
> MySQL> > Hi Neil,> > If your using Linux then you have to install the glib 
> RPM's in the usual > way. I don't know about other platforms, but I am sure 
> there will be a > version of glib out there...> > Also ensure the correct 
> include and link directives are in your > Makefile, which you can get (on 
> Linux) using the commands:> > # glib-config --cflags> # glib-config --libs> > 
> Ben> > Neil Tompkins wrote:> > Thanks Ben, but I don't appear to have the 
> header file  in my > > libraries.> > > > Neil> > > > > > 
> > > > 
> > > Date: Wed, 14 May 2008 12:39:09 +0100> > > From: [EMAIL PROTECTED]> > > 
> To: [EMAIL PROTECTED]> > > CC: [EMAIL PROTECTED]; mysql@lists.mysql.com> > > 
> Subject: Re: Query execution time - MySQL> > >> > > If you using C++ then you 
> can use this:> > >> > > 
> http://developer.gimp.org/api/2.0/glib/glib-Timers.html> > >> > > I use this 
> in my code, does an excelent job.> > >> > > Also you may want to look at the 
> 'slow log' in mysql which will show, to> > > the nearest second, the length 
> of queries> > >> > > Ben> > >> > > Neil Tompkins wrote:> > > > Hi Craig,> 
> > > >> > > > Thanks for your detailed reply. Basically what I'm trying to > > 
> extract is the time taken from when I execute the mysql query in my C++ > > 
> Builder program until the time the query has finished.> > > >> > > > So my 
> question is can I build in to my SQL query SELECT Name FROM > > Customers the 
> time the query actually took or do I need to do this > > outside of my 
> query.> > > >> > > > Regards> > > > Neil> > > >> > > >> > > > Date: Wed, 14 
> May 2008 07:21:04 -0400From: > > [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: 
> Query > > execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not > > much 
> detail there (but I'll go off what you provided...). Some people > > limit 
> the actual MySQL system for times it TAKES MySQL to execute > > queries. For 
> THIS to be accomplished, MySQL has built-in functionality > > to measure the 
> time is takes queries to take place so it can ... limit > > them. So, in 
> essence, I guess we can extract that data and get it back > > to you for 
> whatever usage statistic you are looking to measure. ( See: > > 
> http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html
>  > > ) or for usage in JDBC by calling the setQueryTimeout() function of a > 
> > Statement object...and so forth.HOWEVER - Just so you know, if you > > 
> execute the query MANUALLY via the command-line of MySQL it will tell > > you 
> how long the query took. Just use normal SQL syntax, execute> > > the query 
> on the table and VOILA! Your answer:mysql queryormysqlrun > > the query (use 
> the below quoted/threaded example as a starting place to > > write your own 
> query...?)Take a look at this thread (it basically > > explains the answer 
> with a bit more detail on what the output will > > 
> be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404> > > > 
> SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) > > SELECT 
> BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) > > I 
> believe the results are the following: The first number is the time it > > 
> took MySQL server to send the result set to the client. The second > > number 
> (in parens) is the time it took MySQL server to execute the query > > 
> itself.> > > > TOTAL TIME will EQUAL A + B (for total time it took on your > 
> > server/P.C. or wherever you are running the query...). Many things come > > 
> into factoring why it takes longer or shorter. So this is why I asked if > > 
> you are attempting to optimize or what not, but that is whole new story. > > 
> (( > What Operating System are you running? This would be helpful to > > 
> give you the step-by-step, so to speak. Or perhaps provide u

RE: Query execution time - MySQL

2008-05-14 Thread Neil Tompkins

Hi Ben
 
I running on Windows.  I think I need a solution where i can get the search 
time within my sql query.  Is this possible.  At the moment I;m running mysql 
server version 3.28
 
Neil



> Date: Wed, 14 May 2008 13:44:22 +0100> From: [EMAIL PROTECTED]> To: [EMAIL 
> PROTECTED]> CC: mysql@lists.mysql.com> Subject: Re: Query execution time - 
> MySQL> > Hi Neil,> > If your using Linux then you have to install the glib 
> RPM's in the usual > way. I don't know about other platforms, but I am sure 
> there will be a > version of glib out there...> > Also ensure the correct 
> include and link directives are in your > Makefile, which you can get (on 
> Linux) using the commands:> > # glib-config --cflags> # glib-config --libs> > 
> Ben> > Neil Tompkins wrote:> > Thanks Ben, but I don't appear to have the 
> header file  in my > > libraries.> > > > Neil> > > > > > 
> > > > 
> > > Date: Wed, 14 May 2008 12:39:09 +0100> > > From: [EMAIL PROTECTED]> > > 
> To: [EMAIL PROTECTED]> > > CC: [EMAIL PROTECTED]; mysql@lists.mysql.com> > > 
> Subject: Re: Query execution time - MySQL> > >> > > If you using C++ then you 
> can use this:> > >> > > 
> http://developer.gimp.org/api/2.0/glib/glib-Timers.html> > >> > > I use this 
> in my code, does an excelent job.> > >> > > Also you may want to look at the 
> 'slow log' in mysql which will show, to> > > the nearest second, the length 
> of queries> > >> > > Ben> > >> > > Neil Tompkins wrote:> > > > Hi Craig,> 
> > > >> > > > Thanks for your detailed reply. Basically what I'm trying to > > 
> extract is the time taken from when I execute the mysql query in my C++ > > 
> Builder program until the time the query has finished.> > > >> > > > So my 
> question is can I build in to my SQL query SELECT Name FROM > > Customers the 
> time the query actually took or do I need to do this > > outside of my 
> query.> > > >> > > > Regards> > > > Neil> > > >> > > >> > > > Date: Wed, 14 
> May 2008 07:21:04 -0400From: > > [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: 
> Query > > execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not > > much 
> detail there (but I'll go off what you provided...). Some people > > limit 
> the actual MySQL system for times it TAKES MySQL to execute > > queries. For 
> THIS to be accomplished, MySQL has built-in functionality > > to measure the 
> time is takes queries to take place so it can ... limit > > them. So, in 
> essence, I guess we can extract that data and get it back > > to you for 
> whatever usage statistic you are looking to measure. ( See: > > 
> http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html
>  > > ) or for usage in JDBC by calling the setQueryTimeout() function of a > 
> > Statement object...and so forth.HOWEVER - Just so you know, if you > > 
> execute the query MANUALLY via the command-line of MySQL it will tell > > you 
> how long the query took. Just use normal SQL syntax, execute> > > the query 
> on the table and VOILA! Your answer:mysql queryormysqlrun > > the query (use 
> the below quoted/threaded example as a starting place to > > write your own 
> query...?)Take a look at this thread (it basically > > explains the answer 
> with a bit more detail on what the output will > > 
> be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404> > > > 
> SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) > > SELECT 
> BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) > > I 
> believe the results are the following: The first number is the time it > > 
> took MySQL server to send the result set to the client. The second > > number 
> (in parens) is the time it took MySQL server to execute the query > > 
> itself.> > > > TOTAL TIME will EQUAL A + B (for total time it took on your > 
> > server/P.C. or wherever you are running the query...). Many things come > > 
> into factoring why it takes longer or shorter. So this is why I asked if > > 
> you are attempting to optimize or what not, but that is whole new story. > > 
> (( > Wha

RE: Query execution time - MySQL

2008-05-14 Thread Neil Tompkins

Hi Craig,
 
Thanks for your detailed reply.  Basically what I'm trying to extract is the 
time taken from when I execute the mysql query in my C++ Builder program until 
the time the query has finished.
 
So my question is can I build in to my SQL query SELECT Name FROM Customers the 
time the query actually took or do I need to do this outside of my query.
 
Regards
Neil


Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL 
PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much 
detail there (but I'll go off what you provided...). Some people limit the 
actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be 
accomplished, MySQL has built-in functionality to measure the time is takes 
queries to take place so it can ... limit them. So, in essence, I guess we can 
extract that data and get it back to you for whatever usage statistic you are 
looking to measure. ( See: 
http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html
 ) or for usage in JDBC by calling the setQueryTimeout() function of a 
Statement object...and so forth.HOWEVER - Just so you know, if you execute the 
query MANUALLY via the command-line of MySQL it will tell you how long the 
query took. Just use normal SQL syntax, execute the query on the table and 
VOILA! Your answer:mysql queryormysqlrun the query (use the below 
quoted/threaded example as a starting place to write your own query...?)Take a 
look at this thread (it basically explains the answer with a bit more detail on 
what the output will 
be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404
SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT 
BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the 
results are the following: The first number is the time it took MySQL server to 
send the result set to the client. The second number (in parens) is the time it 
took MySQL server to execute the query itself.
TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or 
wherever you are running the query...). Many things come into factoring why it 
takes longer or shorter. So this is why I asked if you are attempting to 
optimize or what not, but that is whole new story. (( > What Operating 
System are you running? This would be helpful to give you the step-by-step, so 
to speak. Or perhaps provide us with a bit more information***Also, if you are 
looking to perhaps make it so queries take shorter times (optimization effort) 
to execute a little bit more about your MySQL database setup and machine(s) 
would be beneficial to us as well. ))Let me know if you have any 
questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq 
on FreeNode #mysql | #apache
On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins <[EMAIL PROTECTED]> wrote:
Hi,When performing a SQL query like SELECT Name FROM Customers.  How do I 
obtain the time in which the query took to execute like 1.5 seconds 
etcThanks,Neil_All
 new Live Search at 
Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
_
Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay 
today!
http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/

Query execution time - MySQL

2008-05-14 Thread Neil Tompkins

Hi,
 
When performing a SQL query like SELECT Name FROM Customers.  How do I obtain 
the time in which the query took to execute like 1.5 seconds etc
 
Thanks,
Neil
_

All new Live Search at Live.com

http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/

RE: Order Problem

2008-05-08 Thread Neil Tompkins

Perfect.  It worked just how I wanted.
 
Thanks for your help.
 
Neil



> Date: Wed, 7 May 2008 19:54:39 +0200> To: [EMAIL PROTECTED]> Subject: Re: 
> Order Problem> From: [EMAIL PROTECTED]> > Hi,> > You should look at the 
> `FIND_IN_SET` function here: > 
> http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set>
>  > Your query could look like:> SELECT ProductID FROM Products WHERE Enabled= 
> ' Yes' AND ProductID > IN(varProductID) ORDER BY FIND_IN_SET(ProductID, 
> varProductID);> > Haven't tested it, though...> > > Take care,> Aleksandar
_

Discover and Win with Live Search

http://clk.atdmt.com/UKM/go/msnnkmgl001007ukm/direct/01/

Order Problem

2008-05-07 Thread Neil Tompkins

Hi All,
 
I've the following query :SELECT ProductID FROM Products WHERE Enabled= ' Yes'  
AND ProductID IN(varProductID)
 
This query works fine.  However the query result is in a different order to 
what I passed in varProductID.  
 
How can I order the results based on my list like
 
varProductID = "1000,2500,1500"
 
At the moment the result is 
 
1000
1500
2500
 
But I want
 
1000
2500
1500
 
Thanks,
Neil
 
 
_

Discover and Win with Live Search

http://clk.atdmt.com/UKM/go/msnnkmgl001007ukm/direct/01/

RE: ORDER BY calculated field

2008-03-20 Thread Neil Tompkins

Thanks for the help.  However I found another solution which is to use the 
following SELECT COUNT(ProductsPurchases.ProductID)+Products.ProductReview as 
varProductCount, Products.Name, Products.ProductReviewFROM 
ProductsPurchasesINNER JOIN Products ON Products.ProductID = 
ProductsPurchases.ProductIDGROUP BY Products.ProductID ORDER BY varProductCount 
DESC 

> Date: Thu, 20 Mar 2008 13:08:51 +0100> From: [EMAIL PROTECTED]> CC: 
> mysql@lists.mysql.com> Subject: Re: ORDER BY calculated field> > Neil 
> Tompkins schrieb:> > Thanks Sebastian, but I now get the error message> > > > 
> [MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Invalid use of group function> > i 
> am not familiar with ODBC or MySQL 3.x> > but possible just GROUP BY is 
> missing> check the manual for your mysql version for the exact syntax> > if 
> this is not working at all you have to use HAVING> > -- > Sebastian> > -- > 
> MySQL General Mailing List> For list archives: http://lists.mysql.com/mysql> 
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]> 
_
Share what Santa brought you
https://www.mycooluncool.com

RE: ORDER BY calculated field

2008-03-20 Thread Neil Tompkins

Thanks Sebastian, but I now get the error message
 
[MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Invalid use of group function 



> Date: Thu, 20 Mar 2008 12:59:22 +0100> From: [EMAIL PROTECTED]> To: 
> mysql@lists.mysql.com> Subject: Re: ORDER BY calculated field> > Sebastian 
> Mendel schrieb:> > Neil Tompkins schrieb:> >> Hi> >> > >> I want to order by 
> the totalled fields varProductCount and > >> Products.ProductReviewDESC> > > 
> > just put them together, separated with comma, like it is written in the > > 
> manual> > > > ORDER BY varProductCount + Products.ProductReviewDESC,> > 
> COUNT(ProductsPurchases.ProductID)> > > > sorry:> > ORDER BY 
> COUNT(ProductsPurchases.ProductID) + Products.ProductReviewDESC> > -- > 
> Sebastian> > -- > MySQL General Mailing List> For list archives: 
> http://lists.mysql.com/mysql> To unsubscribe: http://lists.mysql.com/[EMAIL 
> PROTECTED]> 
_
Share what Santa brought you
https://www.mycooluncool.com

RE: ORDER BY calculated field

2008-03-20 Thread Neil Tompkins

Hi
 
I want to order by the totalled fields varProductCount and 
Products.ProductReviewDESC
 
Neil



> Date: Thu, 20 Mar 2008 11:36:30 +0100> From: [EMAIL PROTECTED]> To: 
> mysql@lists.mysql.com> Subject: Re: ORDER BY calculated field> > Neil 
> Tompkins schrieb:> > Hi,> > > > How do I achieve a SQL statement to order my 
> results based on two calculated fields for example :> > what two calculated 
> fields?> > > > SELECT COUNT(ProductsPurchases.ProductID) as varProductCount, 
> Products.Name, Products.ProductReview> > FROM ProductsPurchasesINNER JOIN 
> Products ON Products.ProductID = ProductsPurchases.ProductIDGROUP BY 
> Products.ProductID ORDER BY varProductCount+Products.ProductReviewDESC> > 
> ORDER BY COUNT(ProductsPurchases.ProductID)> > -- > Sebastian Mendel> > -- > 
> MySQL General Mailing List> For list archives: http://lists.mysql.com/mysql> 
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]> 
_
Telly addicts unite!
http://www.searchgamesbox.com/tvtown.shtml

ORDER BY calculated field

2008-03-20 Thread Neil Tompkins

Hi,
 
How do I achieve a SQL statement to order my results based on two calculated 
fields for example :
 
SELECT COUNT(ProductsPurchases.ProductID) as varProductCount, Products.Name, 
Products.ProductReview
FROM ProductsPurchasesINNER JOIN Products ON Products.ProductID = 
ProductsPurchases.ProductIDGROUP BY Products.ProductID ORDER BY 
varProductCount+Products.ProductReviewDESC
 
Thanks
Neil
_
Telly addicts unite!
http://www.searchgamesbox.com/tvtown.shtml

Record Counting

2007-10-27 Thread Neil Tompkins

Hi,
 
I have a table of records all of which have a timestamp against them like 
2007-10-25 10:10:19, and category like red, blue etc and a unique key.
 
Using a SELECT statement is it possible to retrieve the count and number of 
records for each day e.g 2007-10-25 for all red, and all blue etc
 
Thanks.
Neil
_
Feel like a local wherever you go.
http://www.backofmyhand.com

Speeding Up Process

2007-10-04 Thread Neil Tompkins

Hi
 
I've the following process/queries which I want to speed up, for a product 
inventory/warehouse which we have a number of items available to sell each day
 
For Each Company
   For Each Product
  For Next 7 Days
CheckQuantityUpdateQuantity
  Next Day
   Next Product
Next Company
 
Is it possible to re-write this routine so that it executes quicker ?
 
Thanks,Neil
_
Get free emoticon packs and customisation from Windows Live. 
http://www.pimpmylive.co.uk

Hotel availability system

2007-10-04 Thread Neil Tompkins

Hi
I'm looking to design a hotel reservation/availability system to be used 
online.  Has anyone had any experience with regards table / database design ?
 
Thanks
Neil
_
100’s of Music vouchers to be won with MSN Music
https://www.musicmashup.co.uk

Re: ENCODE() and DECODE()

2007-03-13 Thread Neil Tompkins


I'm using ASP.  Do you know any resources that I could use ?




From: "Wm Mussatto" <[EMAIL PROTECTED]>
To: "Neil Tompkins" <[EMAIL PROTECTED]>
Subject: Re: ENCODE() and DECODE()
Date: Mon, 12 Mar 2007 15:27:26 -0700 (PDT)

On Mon, March 12, 2007 15:05, Neil Tompkins said:
> Do you think these functions would be ideal for my requirements in terms
> of
> encrypting credit card numbers when stored in a database ?
>
Actually, no.  If you are working a language like perl look at encoding
the information and then storing it. I think encode and decode are too
weak.

in perl I use use Crypt::CBC; and then picked a strong cypher. If you use
perl, please go to cpan.org for the details.  BTW the reason for using
blob type it to avoid truncation. After its encoded removing trailing
spaces is a BAD THING.

Bill
>
>
>>From: "Wm Mussatto" <[EMAIL PROTECTED]>
>>To: "Neil Tompkins" <[EMAIL PROTECTED]>
>>CC: mysql@lists.mysql.com
>>Subject: Re: ENCODE() and DECODE()
>>Date: Mon, 12 Mar 2007 13:54:41 -0700 (PDT)
>>
>>On Mon, March 12, 2007 13:04, Neil Tompkins said:
>> > Sorry Bill, I'm unsure what you mean by bin
>>My error, too early in the morning here 'blob'
>> >
>> >>From: "William R. Mussatto" <[EMAIL PROTECTED]>
>> >>To: mysql@lists.mysql.com
>> >>Subject: Re: ENCODE() and DECODE()
>> >>Date: Mon, 12 Mar 2007 10:31:53 -0700 (PDT)
>> >>
>> >>On Mon, March 12, 2007 8:04, Neil Tompkins said:
>> >> > I've been researching the best method to store credit card numbers
>> in
>> >> a
>> >> > database which are saved encrypted.  My version of mysql is 3.23
>> >>therefore
>> >> > I
>> >> > think the only function I could use is
>> >> >
>> >> > ENCODE() and DECODE().
>> >> >
>> >> > I've tried these functions and they appear to work as I want.  
I've

>> a
>> >> > couple
>> >> > of questions though, can I use varchar when saving the data and 
are

>> >>these
>> >> > functions suitable for my requirements ?
>> >> >
>> >> > Thanks,
>> >> > Neil
>> >>use 'Bin' versions of fields since the encoded data may be binary.
>> >>
>> >>Bill
>> >>
>> >>
>> >>--
>> >>MySQL General Mailing List
>> >>For list archives: http://lists.mysql.com/mysql
>> >>To unsubscribe:
>> >>http://lists.mysql.com/[EMAIL PROTECTED]
>> >>
>> >
>> > _
>> > Txt a lot? Get Messenger FREE on your mobile.
>> > https://livemessenger.mobile.uk.msn.com/
>> >
>> >
>>
>>
>
> _
> Solve the Conspiracy and win fantastic prizes.
> http://www.theconspiracygame.co.uk/
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>




_
Get Hotmail, News, Sport and Entertainment from MSN on your mobile.  
http://www.msn.txt4content.com/



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

Re: ENCODE() and DECODE()

2007-03-12 Thread Neil Tompkins

Do you think these functions would be ideal for my requirements in terms of 
encrypting credit card numbers when stored in a database ?






From: "Wm Mussatto" <[EMAIL PROTECTED]>
To: "Neil Tompkins" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: ENCODE() and DECODE()
Date: Mon, 12 Mar 2007 13:54:41 -0700 (PDT)

On Mon, March 12, 2007 13:04, Neil Tompkins said:
> Sorry Bill, I'm unsure what you mean by bin
My error, too early in the morning here 'blob'
>
>>From: "William R. Mussatto" <[EMAIL PROTECTED]>
>>To: mysql@lists.mysql.com
>>Subject: Re: ENCODE() and DECODE()
>>Date: Mon, 12 Mar 2007 10:31:53 -0700 (PDT)
>>
>>On Mon, March 12, 2007 8:04, Neil Tompkins said:
>> > I've been researching the best method to store credit card numbers in
>> a
>> > database which are saved encrypted.  My version of mysql is 3.23
>>therefore
>> > I
>> > think the only function I could use is
>> >
>> > ENCODE() and DECODE().
>> >
>> > I've tried these functions and they appear to work as I want.  I've a
>> > couple
>> > of questions though, can I use varchar when saving the data and are
>>these
>> > functions suitable for my requirements ?
>> >
>> > Thanks,
>> > Neil
>>use 'Bin' versions of fields since the encoded data may be binary.
>>
>>Bill
>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:
>>http://lists.mysql.com/[EMAIL PROTECTED]
>>
>
> _
> Txt a lot? Get Messenger FREE on your mobile.
> https://livemessenger.mobile.uk.msn.com/
>
>




_
Solve the Conspiracy and win fantastic prizes.  
http://www.theconspiracygame.co.uk/



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

ENCODE() and DECODE()

2007-03-12 Thread Neil Tompkins

I've been researching the best method to store credit card numbers in a 
database which are saved encrypted.  My version of mysql is 3.23 therefore I 
think the only function I could use is


ENCODE() and DECODE().

I've tried these functions and they appear to work as I want.  I've a couple 
of questions though, can I use varchar when saving the data and are these 
functions suitable for my requirements ?


Thanks,
Neil

_
MSN Hotmail is evolving - check out the new Windows Live Mail.  
http://ideas.live.co.uk/



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

RE: FW: Re: MD5()

2007-03-12 Thread Neil Tompkins



I just tried and it appears the functions DO NOT work in version 3.23




From: "Neil Tompkins" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: FW: Re: MD5()
Date: Mon, 12 Mar 2007 09:28:27 +

I thought the DES_ENCRYPT/DES_DECRYPT  functions were released in mysql 
version 4 however they work with my version of mySQL 3.23 ?


Neil





From: "Neil Tompkins" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: FW: Re: MD5()
Date: Sat, 10 Mar 2007 18:24:19 +

Strange the DES_ENCRYPT/DES_DECRYPT  functions work with my version of 
mySQL 3.23





From: "Neil Tompkins" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: MD5()
Date: Sat, 10 Mar 2007 18:20:43 +

DES_ENCRYPT/DES_DECRYPT appears to be what we require.

Thanks
Neil






From: "Mogens Melander" <[EMAIL PROTECTED]>
To: "Neil Tompkins" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: MD5()
Date: Sat, 10 Mar 2007 12:19:53 +0100 (CET)

Or you might want to take a look at DES_ENCRYPT/DES_DECRYPT
funktions in the mysql manual.

On Sat, March 10, 2007 11:18, Ian P. Christian wrote:
> Neil Tompkins wrote:
>> I'm looking to use MD5() to encrypt credit card numbers.  How do I
>> unencrypt this when reading the value ?
>>
>
> you can't.
> Google for MD5, or better still look at wikipedia, I'm sure they will
> have something
>
> --
> Ian P. Christian ~ http://pookey.co.uk
>
> --
> 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]




_
MSN Hotmail is evolving - check out the new Windows Live Mail.  
http://ideas.live.co.uk/



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




_
MSN Hotmail is evolving - check out the new Windows Live Mail 
http://ideas.live.co.uk



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




_
MSN Hotmail is evolving - check out the new Windows Live Mail.  
http://ideas.live.co.uk/



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




_
MSN Hotmail is evolving - check out the new Windows Live Mail 
http://ideas.live.co.uk



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

FW: Re: MD5()

2007-03-12 Thread Neil Tompkins

I thought the DES_ENCRYPT/DES_DECRYPT  functions were released in mysql 
version 4 however they work with my version of mySQL 3.23 ?


Neil





From: "Neil Tompkins" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: FW: Re: MD5()
Date: Sat, 10 Mar 2007 18:24:19 +

Strange the DES_ENCRYPT/DES_DECRYPT  functions work with my version of 
mySQL 3.23





From: "Neil Tompkins" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: MD5()
Date: Sat, 10 Mar 2007 18:20:43 +

DES_ENCRYPT/DES_DECRYPT appears to be what we require.

Thanks
Neil






From: "Mogens Melander" <[EMAIL PROTECTED]>
To: "Neil Tompkins" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: MD5()
Date: Sat, 10 Mar 2007 12:19:53 +0100 (CET)

Or you might want to take a look at DES_ENCRYPT/DES_DECRYPT
funktions in the mysql manual.

On Sat, March 10, 2007 11:18, Ian P. Christian wrote:
> Neil Tompkins wrote:
>> I'm looking to use MD5() to encrypt credit card numbers.  How do I
>> unencrypt this when reading the value ?
>>
>
> you can't.
> Google for MD5, or better still look at wikipedia, I'm sure they will
> have something
>
> --
> Ian P. Christian ~ http://pookey.co.uk
>
> --
> 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]




_
MSN Hotmail is evolving - check out the new Windows Live Mail.  
http://ideas.live.co.uk/



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




_
MSN Hotmail is evolving - check out the new Windows Live Mail 
http://ideas.live.co.uk



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




_
MSN Hotmail is evolving - check out the new Windows Live Mail.  
http://ideas.live.co.uk/



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

FW: Re: MD5()

2007-03-10 Thread Neil Tompkins

Strange the DES_ENCRYPT/DES_DECRYPT  functions work with my version of mySQL 
3.23





From: "Neil Tompkins" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: MD5()
Date: Sat, 10 Mar 2007 18:20:43 +

DES_ENCRYPT/DES_DECRYPT appears to be what we require.

Thanks
Neil






From: "Mogens Melander" <[EMAIL PROTECTED]>
To: "Neil Tompkins" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: MD5()
Date: Sat, 10 Mar 2007 12:19:53 +0100 (CET)

Or you might want to take a look at DES_ENCRYPT/DES_DECRYPT
funktions in the mysql manual.

On Sat, March 10, 2007 11:18, Ian P. Christian wrote:
> Neil Tompkins wrote:
>> I'm looking to use MD5() to encrypt credit card numbers.  How do I
>> unencrypt this when reading the value ?
>>
>
> you can't.
> Google for MD5, or better still look at wikipedia, I'm sure they will
> have something
>
> --
> Ian P. Christian ~ http://pookey.co.uk
>
> --
> 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]




_
MSN Hotmail is evolving - check out the new Windows Live Mail.  
http://ideas.live.co.uk/



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




_
MSN Hotmail is evolving - check out the new Windows Live Mail 
http://ideas.live.co.uk



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

Re: MD5()

2007-03-10 Thread Neil Tompkins


DES_ENCRYPT/DES_DECRYPT appears to be what we require.

Thanks
Neil






From: "Mogens Melander" <[EMAIL PROTECTED]>
To: "Neil Tompkins" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: MD5()
Date: Sat, 10 Mar 2007 12:19:53 +0100 (CET)

Or you might want to take a look at DES_ENCRYPT/DES_DECRYPT
funktions in the mysql manual.

On Sat, March 10, 2007 11:18, Ian P. Christian wrote:
> Neil Tompkins wrote:
>> I'm looking to use MD5() to encrypt credit card numbers.  How do I
>> unencrypt this when reading the value ?
>>
>
> you can't.
> Google for MD5, or better still look at wikipedia, I'm sure they will
> have something
>
> --
> Ian P. Christian ~ http://pookey.co.uk
>
> --
> 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]




_
MSN Hotmail is evolving - check out the new Windows Live Mail.  
http://ideas.live.co.uk/



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

Re: MD5()

2007-03-10 Thread Neil Tompkins

The problem I have is our mysql database version is 3.23 and we are not in a 
position to upgrade.

From: "Ian P. Christian" <[EMAIL PROTECTED]>
To: Neil Tompkins <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: MD5()
Date: Sat, 10 Mar 2007 10:24:45 +

Neil Tompkins wrote:

What do you recommend I use ?

http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html

That should help you.

However... keep in mind that if your application is likely to be accessing 
this data all the time anyway, if someone compromises your database, 
chances are they will compromise your code, leaving to them being able to 
get your key, and then decrypt all your data anyway.

I'm not saying encrypting it in the database is pointless, it's just far 
from enough to say your data is secure.

A quick google on the subject returned this:

http://forums.mysql.com/read.php?30,14020,14020

which honestly, I've not read - but you might want to :)

--
Ian P. Christian ~ http://pookey.co.uk

_
Txt a lot? Get Messenger FREE on your mobile. 
https://livemessenger.mobile.uk.msn.com/

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

Re: MD5()

2007-03-10 Thread Neil Tompkins

What do you recommend I use ?

From: "Ian P. Christian" <[EMAIL PROTECTED]>
To: Neil Tompkins <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: MD5()
Date: Sat, 10 Mar 2007 10:18:41 +

Neil Tompkins wrote:
I'm looking to use MD5() to encrypt credit card numbers.  How do I 
unencrypt this when reading the value ?

you can't.
Google for MD5, or better still look at wikipedia, I'm sure they will have 
something

--
Ian P. Christian ~ http://pookey.co.uk

_
MSN Hotmail is evolving - check out the new Windows Live Mail.  
http://ideas.live.co.uk/

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

MD5()

2007-03-10 Thread Neil Tompkins

I'm looking to use MD5() to encrypt credit card numbers.  How do I unencrypt 
this when reading the value ?


Thanks,
Neil

_
Get Hotmail, News, Sport and Entertainment from MSN on your mobile.  
http://www.msn.txt4content.com/



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

FullText Scoring With Two Databases

2007-02-22 Thread Neil Tompkins

I've been using the MATCH() with FullText Scoring for quite a while now on 
one table.  I now need to combine the data from another database.  I have :


Database1.Table1

with

Database2.Table1

If I use the the FullText scoring using just one database/table it is OK, 
but when I query the database1 and then database2 the scoring is totally 
different because the data is different in each database.


What would be the best way to overcome this problem.  We are using a early 
version of mysql 3.28 and can't upgrade at this time.


Thanks for any help.

Neil

_
Exclusive Ed Byrne daily comedy clips on MSN Video 
http://specials.uk.msn.com/edbyrne/



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

Re: Query Two Databases

2007-02-13 Thread Neil Tompkins


Following on from the email below, if I run the query

SELECT * FROM database1.table, database2.table

I get the data back, but all the data is in the same row.

How can I seperate the records ?

Regards
Neil


~~


At the moment we are using mysql 3.23.58.  It would appear I can't use UNION 
in this version ?


Do I have any other options, without upgrading the database server version ?

Regards,
Neil





From: Nils Jünemann <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: Re: Query Two Databases
Date: Thu, 25 Jan 2007 12:01:48 +0100

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Neil Tompkins schrieb:
> Not sure if this is possible or not.  But I've two identical tables in
> two different databases.  Is it possible to retrieve data from the
> different tables in one query ?

(SELECT * FROM db1.table) UNION (SELECT * FROM db2.table)

If db2 on a other mysql server, it is possible to use the
federated storage engine of MySQL.

- --
Nils Jünemann
Database and System Administration

studiVZ | Saarbrueckerstr. 38 | 10405 Berlin | phone +49-(0)30-405042715
www.studiVZ.net  |  fax +49-(0)30-28093887   |  cell  +49-(0)175-9331740
www.estudiLN.es  |  www.studentIX.pl  | www.studiQG.fr |  www.studiLN.it
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFuI4cYT5ehyp2X5ERAh/AAKCEDPXhlegrCu08jG0K3Eh/ZxIfgwCeIbZz
hJRxd5b07AxIlFP8/RBKQx0=
=G/4h
-END PGP SIGNATURE-

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




_
MSN Hotmail is evolving  check out the new Windows Live Mail 
http://ideas.live.com



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

Re: Query Two Databases

2007-02-13 Thread Neil Tompkins

At the moment we are using mysql 3.23.58.  It would appear I can't use UNION 
in this version ?


Do I have any other options, without upgrading the database server version ?

Regards,
Neil





From: Nils Jünemann <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: Re: Query Two Databases
Date: Thu, 25 Jan 2007 12:01:48 +0100

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Neil Tompkins schrieb:
> Not sure if this is possible or not.  But I've two identical tables in
> two different databases.  Is it possible to retrieve data from the
> different tables in one query ?

(SELECT * FROM db1.table) UNION (SELECT * FROM db2.table)

If db2 on a other mysql server, it is possible to use the
federated storage engine of MySQL.

- --
Nils Jünemann
Database and System Administration

studiVZ | Saarbrueckerstr. 38 | 10405 Berlin | phone +49-(0)30-405042715
www.studiVZ.net  |  fax +49-(0)30-28093887   |  cell  +49-(0)175-9331740
www.estudiLN.es  |  www.studentIX.pl  | www.studiQG.fr |  www.studiLN.it
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFuI4cYT5ehyp2X5ERAh/AAKCEDPXhlegrCu08jG0K3Eh/ZxIfgwCeIbZz
hJRxd5b07AxIlFP8/RBKQx0=
=G/4h
-END PGP SIGNATURE-

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




_
MSN Hotmail is evolving  check out the new Windows Live Mail 
http://ideas.live.com



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

1 2 >

1 - 100 of 151 matches

Mail list logo