Re: Can records be moved between tables without knowing columns

[Illyes Laszlo]

On Tue, 7 Oct 2003 16:48:55 -0400, Jim wrote
> Hi,
> 
> I need to be able to 'delete' records from table(s) but still 
> maintain their information for an audit trail.
> 
> I'd rather not have a field for a deleted flag and have to condition 
> all the queries on the table on the state of this flag.
> 
> Is it possible to move a record from one table to another easily?
> 
> Even better, can I do it opaquely (without knowledge of the columns)?
> Something
> like:
> "move from LiveUsers to DeletedUsers where ID=?;"
> where all the fields in the first table get moved to the 
> corresponding field of the 2nd.  (If this is possible, what happens 
> with autoincrement, timestamp fields?)

In the second table (DeletedUsers you don't define the ID autoincrement.
Timestamp you can freeze, and you can try.

>From ducumentation I cut for You:

Let MySQL set the column when the row is created. This will initialise it to 
the current date and time. 
When you perform subsequent updates to other columns in the row, set the 
TIMESTAMP column explicitly to its current value. 

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DATET
IME

> 
> Thanks for the help; I hope it isn't a trivial question.
> I'm new at this; I didn't find anything on moving records in the list
> archives.
> 
> Jim Cant
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]


Laszlo Illyes
Teaching-assistant
Sapientia University
(Csikszereda) Miercurea-Ciuc
Tel:+40266317310
Fax:+40266317310/+40266371121
Mobil:+40740055706
E-mail: [EMAIL PROTECTED]


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

What would be an efficient way to accomplish this (Statistics/Trends in a table?) Thanks!

[Paul F]

I would appreciate any advice on this. PHP+MySQL snippets even better :P

How can I do this:

For example I have a table with a field FIRST_NAME

I want to produce the statistics of how many identical FIRST_NAME entries
there are in the table.

Yes I could hard code queries to match a list of predetermined entries (ie.,
knowing that there are common names like Paul, Bob and John) and do a count
on each match but this will not suffice because in this example it will not
account for say occurrences of the first name Xanther, LOL.

Anyway... I had a nap and am back before I clicked send. During my nap I
dreamed up something like selecting all FIRST_NAME ordered by same, and them
some kind of script to do counts. 

Any other/further suggestions appreciated. Thanks!





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

Re: MySQL: Ordering Random Records

[Matt W]

Hi Stuart,

Good solution! I didn't even think of that. :-) Interestingly, and FYI,
it doesn't work correctly on 4.1.0 (ignores the LIMIT). Hopefully it's a
known bug that's been fixed in 4.1.1.

4.1.0 does work correctly though using derived tables as John Bonnett
suggested:

SELECT * FROM
(SELECT * FROM table ORDER BY RAND() LIMIT 20) tbl_alias
ORDER BY col1


Matt


- Original Message -
From: "Stuart M. Robinson"
Sent: Tuesday, October 07, 2003 6:00 PM
Subject: Re: MySQL: Ordering Random Records


> Folks,
>
> Urgh, you know that feeling when you've burnt lots of cycles trying to
> come up with a clever solution to a problem only to find the answer is
> easy?  We've been thrashing through ideas on how to order random
records
> that have included temporary tables and writing data in and out of
tables,
> when in fact all that's needed is some union syntax:
>
> rsTest.Source = "(SELECT record1, record2, record3 FROM
MyDatabase.MyTable
> ORDER BY RAND() LIMIT 20) ORDER BY record1 ASC;"
>
> And that's all there is to it!  No permission or thread problems
whatsoever.
>
> Many thanks to all who helped out.
>
>
> Stuart.


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

Japanese display problem with mysql on Linux

[kayamboo]

Hi folks
  I wrote an application which uses japanese characters. I can display
japanese characters, fetching from a mysql database on my WindowsXP
machine.
  I moved the same to a Linux machine and could see only junk characters.


Any suggestions?

MySQL version 4.0.13 on Win XP Japanese
MySQL version 4.0.14 on Linux English

But the charset is set to latin1 on both installation. I used the mysql
binary distribution. Do I need to change this. If so how?

Best Regards
K.Suresh





___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!

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

Re: relay log hosed

[Partap Davis]

Responding to my own message...

Looks like I can't simply delete the relay binlog, since while the slave 
continued to run it continued to update it's place in the master 
binlog.  So, short of copying all the data over again, what can I do?  
Can I just pick a random spot in the master binlog that was before the 
slave choked?  Are there any side effects of having some of the 
replicated commands fail?  Most of the updates to the db have been 
REPLACEs, with a few INSERTs and no DELETEs...If it fails on a few 
INSERTs is that likely to cause any problems?  If it does cause 
problems, is it possible that failed INSERT commands to the master are 
causing the slave's relay logs to become corrupt? (There happen to be a 
good deal of failed inserts to the master as a result of a sloppy script 
that is run regularly...)

I'm really loathe to copy all the data (~250GB) from scratch again, as 
that took me all day last sunday, and this is the second time my relay 
logs have been corrupted. 

My master server:
P3 700MHz, 768MB with 3ware IDE RAID0 array
My slave server:
Dual P3 700MHz, 512MB with Adaptec IDE RAID0 array
both running MySQL 4.0.15-Max and linux 2.4.18 kernels

Partap Davis wrote:

Hi,
So I set up replication and everything seemed to be going alright for 
a couple days until the slave server stopped responding.  Any attempts 
to connect to the slave server hang indefinitely, and if I ssh into 
the machine, any attempts to access the data disk also hang.

I had to hard-boot the server to get any response...the data disk 
(reiserfs) claimed it was alright, but MySQL says the relay log file 
is corrupted, verified by mysqlbinlog:
[EMAIL PROTECTED] data]# mysqlbinlog stout-relay-bin.001 >/dev/null
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 
70266926, event_type: 46
ERROR: Could not read entry at offset 756432957 : Error in log format 
or read error

fsck says the file system is consistent.  Also, no problems found with 
the master binlog...

Can I just delete the relay-bin files and reset the master info to 
continue at the point of failure?  (assuming the disk is not bad and 
the data is valid...which it seems to be so far)

Thanks,
Partap Davis



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

RE: [Fwd: MySQL w/dual-master replication?]

[Andrew Braithwaite]

>>Are you doing high-availability MySQL yourself?

Yes.  In Production we're doing extremely read-heavy, write-light MySQL
database stuff.

The first thing that springs to mind when you say "High Availability" is
"How many 9's do you need" : read Jeremy Z's blog entry @
http://jeremy.zawodny.com/blog/archives/000805.html

Speaking for myself in a scenario where we can't afford all those 9's is
that the method we employ is to use an expensive piece of hardware for the
master MySQL DB (redundant power supplies, redundant fans, redundant nics
etc.. And RAID 5 hot-swappable SCSI disk) and loads of cheap slave servers.

We do all the DB load balancing at the application layer and if a slave goes
BANG! The apps just move on to another slave. (build a nice wrapper in your
apps that send selects to the slaves in a load balanced fashion and
everything else to the master (you already have by the sounds of it)).

You can have a "pseudo-slave/standby-master" standing by (another quality
piece of hardware) replicating all the updates and if the production master
server goes down you can "promote" the standby server to be the new master,
fix the old master server and relegate the old master server to standby
status and point all the slaves to the new master server.

The above will mean that you will experience some loss of updates on the
master whilst you're swapping masters around (either manually or
automatically) but if you are a read heavy site (like you say) and your apps
are well configured for this system then it won't be a big issue.

MySQL say that they'll have automatic master promotion in the event of a
failure sometime in the future but it isn't available right now.

Another tactic you can use is to employ seriously heavy network/system
managing systems that will alert you to potential problems like high CPU
load, rapid loss of disk space, temperature, swap usage etc (I can
recommend MRTG/Nagios as a free one)

I have some other ideas on MySQL high availability so if the above doesn't
meet your needs then I'll be happy to talk about it.

Hope this helps,

Cheers,

Andrew



-Original Message-
From: Don MacAskill [mailto:[EMAIL PROTECTED] 
Sent: Tuesday 07 October 2003 17:59
To: Andrew Braithwaite
Cc: '[EMAIL PROTECTED]'
Subject: Re: [Fwd: MySQL w/dual-master replication?]



Hi Andrew,

Thanks for the reply.  Great question.

It's a very read-heavy system.

Are you doing high-availability MySQL yourself?  I'd love to hear how 
you're doing it, if so.

Thanks!

Don


Andrew Braithwaite wrote:

> Is the system read-heavy or write-heavy?
> 
> Cheers,
> 
> Andrew
> 
> -Original Message-
> From: Don MacAskill [mailto:[EMAIL PROTECTED]
> Sent: Monday 06 October 2003 20:47
> To: [EMAIL PROTECTED]
> Subject: [Fwd: MySQL w/dual-master replication?]
> 
> 
> 
> Hey all,
> 
> I sent this a few days ago, but it may have gotten lost in the weekend
> for many of you.  Don't worry, I won't keep re-posting it.  :)
> 
> I'm hoping someone out there is doing some sort of high-availability
> replication and can give me a few pointers.
> 
> Thanks in advance!
> 
> Don
> 
> 
>  Original Message 
> Subject: MySQL w/dual-master replication?
> Date: Sat, 04 Oct 2003 11:07:43 -0700
> From: Don MacAskill <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> 
> 
> Hi there,
> 
> I realize it's not supported, but nonetheless, I need to investigate 
> how possible it is.  The primary goal is high-availability, not 
> performance scaling.
> 
> It sounds like if I'm careful, it might work out ok.  Careful means:
> 
> - No auto_increment columns... handle unique key assignment in my app
> 
> - Update/insert/delete on primary master only except in case of 
> failure
> 
> - Prevent possibly 'flap' by automatically using secondary master 
> exclusively after primary master failure.  Bring up primary master 
> manually and manually tell the app when to start using it again after 
> it's allowed to catch back up.
> 
> Are there any other gotchas I need to worry about?  Is anyone actually 
> doing this in a production environment?  I'd love to hear from you.
> 
> 
> Another option is to use master/slave, and have a monitoring app 
> change the slave's status to master after the master has died.  
> There's info about this in the FAQ 
> (http://www.mysql.com/doc/en/Replication_FAQ.html), but I'm afraid the 
> documentation on the sequence of events is pretty vague.  Does anyone 
> have any insight as to exactly how it works?
> 
> In particular, one part of the doc says to use 'STOP SLAVE; RESET 
> MASTER; CHANGE MASTER TO' and another part of the doc says 'STOP SLAVE;
>   CHANGE MASTER TO' ...  which is appropriate?   Certainly, I understand
> why 'STOP SLAVE', but why 'RESET MASTER'?   Would the sequence of events
> differ if we're just dealing with 1 master/1 slave instead of 1 
> master/multiple slaves?
> 
> Once the old master joins back up, I can let it be a slave for awhile 
> to catch back up, then swap back, cor

Strange Error

[Dan Cumpian]

I’ve recently started getting an error:

 

file '.\newsman\messages.MYD' not found (Errcode: 22)

 

but when I check the database, the file is there and it is OK. I’ve
repaired and optimized it, but no change. What is the root cause of this
error and what should I do to fix it?

 

Thanks,

Dan Cumpian

 


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003
 

RE: FW: MySQL not null vs MSAccess required

[Fortuno, Adam]

Are you serious?

Its certainly a feature I can do without. Just one more idiosyncrasy to
remember for another RDBMS.

Regards,
Adam

-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 5:46 PM
To: [EMAIL PROTECTED]
Subject: Re: FW: MySQL not null vs MSAccess required



Cal Evans wrote:

> I humbly submit an apology. You are correct.  This is a bug (No it is 
> NOT a feature) 

While you may not like it, this definitely is a feature (or an 
intentional design decision, at least), not a bug.  See the docs at 
.  The first line 
is, "To be able to support easy handling of non-transactional tables, 
all fields in MySQL have default values."  So, if you don't set a 
default for a column, mysql chooses one for you.  With a few exceptions, 
NULLable columns default to NULL, NOT NULL columns default to 0 (zero) 
or '' (empty string).  You can change this behavior by building your own 
mysql from source with the -DDONT_USE_DEFAULT_FIELDS compile option.

> you should be able to define a field as NOT NULL without 
> a default or at the very least, define the default as NULL.

This does not make sense to me.  Allowing NULL as the default for a 
column declared NOT NULL would defeat the purpose of declaring it NOT 
NULL in the first place.

Michael


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

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

Re: FW: MySQL not null vs MSAccess required

[Michael Stassen]

Cal Evans wrote:

I humbly submit an apology. You are correct.  This is a bug (No it is 
NOT a feature) 
While you may not like it, this definitely is a feature (or an 
intentional design decision, at least), not a bug.  See the docs at 
.  The first line 
is, "To be able to support easy handling of non-transactional tables, 
all fields in MySQL have default values."  So, if you don't set a 
default for a column, mysql chooses one for you.  With a few exceptions, 
NULLable columns default to NULL, NOT NULL columns default to 0 (zero) 
or '' (empty string).  You can change this behavior by building your own 
mysql from source with the -DDONT_USE_DEFAULT_FIELDS compile option.

you should be able to define a field as NOT NULL without 
a default or at the very least, define the default as NULL.
This does not make sense to me.  Allowing NULL as the default for a 
column declared NOT NULL would defeat the purpose of declaring it NOT 
NULL in the first place.

Michael

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

RE: Help With a DATETIME Query

[Christensen, Dave]

I've worked on applications like this one in a court scheduling application.
Since trials can run several days, this was an issue I had to deal with,
too.  The solution I set up was to have a table like your "bookings" table,
then have another table containing available time slots for each eligible
date.  At the time a booking was created, my application then reserved
appropriate time slots in the adjacent table for that particular booking
row. The key is that the timeslots table had rows for each day involved and
made subsequent queries much easier and more accurate.

This one is my .02... :-)

-Original Message-
From: shaun thornburgh [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 07, 2003 3:51 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Help With a DATETIME Query


Thanks for your reply, but its not an option!


>From: "Dan Greene" <[EMAIL PROTECTED]>
>To: "shaun thornburgh" <[EMAIL PROTECTED]>,
><[EMAIL PROTECTED]>
>Subject: RE: Help With a DATETIME Query
>Date: Tue, 7 Oct 2003 16:41:04 -0400
>
>I know it's not the answer you're looking for... :(  but dealing with
>overnights has caused me so much aggravation in past apps I've written, 
>I've tended to make the client create two (or more) 'bookings' for the 
>covered time... don't know if it's an option for you, but it's my 
>$0.02.
>
>
>
> > -Original Message-
> > From: shaun thornburgh [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, October 07, 2003 4:33 PM
> > To: [EMAIL PROTECTED]
> > Subject: Help With a DATETIME Query
> >
> >
> > Hi,
> >
> > I have a table called Bookings which has two important columns; 
> > Booking_Start_Date and Booking_End_Date. These columns are both of 
> > type DATETIME. The following query calculates how many hours are 
> > available between the hours of 09.00 and 17.30 so a user can see at 
> > a glance how many
> > hours they have unbooked on a particular day (i.e. 8.5 hours
> > less the time
> > of any bookings on that day). However, when a booking spans
> > more than one
> > day the query doesn't work, for example if a user has a
> > booking that starts
> > on day one at 09.00 and ends at 14.30 on the next day, the
> > query returns 3.5
> > hours for both days. The query is run for each day i.e day 1,
> > day 2 day
> > 10.
> >
> > Any help here would be greatly appreciated.
> >
> > SELECT
> > 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
> >  DATE_FORMAT(B.Booking_End_Date, '%i')) -
> >((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) +
> >  DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS 
> > Available_Hours FROM WMS_Bookings B
> > WHERE B.User_ID = '16'
> > AND B.Booking_Status <> '1'
> > AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date,
> > "%Y-%m-%d") OR
> > '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )
> >
> > _
> > On the move? Get Hotmail on your mobile phone 
> > http://www.msn.co.uk/msnmobile
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: 
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:
>http://lists.mysql.com/[EMAIL PROTECTED]
>

_
Use MSN Messenger to send music and pics to your friends 
http://www.msn.co.uk/messenger


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

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

Re: printing reports

[Wang Feng]

Thanks Dave

That sounds good although I haven't installed them. I don't know if that
works to my OfficeXP or not. Seems better for me to get a RedHat 9.0
somewhere. :-)


cheers,

feng

- Original Message -
From: "Christensen, Dave" <[EMAIL PROTECTED]>
To: "Wang Feng" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, October 08, 2003 1:10 AM
Subject: RE: printing reports


> You can, via ODBC, connect to MySQL using OpenOffice or Star Office which
> would give you a no-cost, or low-cost, option for producing reports from
> MySQL.  There are a number of execellent web articles about interfacing
> either of these with MySQL.  Try searching via Google.
>
> -Original Message-
> From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 07, 2003 9:49 AM
> To: Wang Feng; [EMAIL PROTECTED]
> Subject: Re: printing reports
>
>
> To get data from/into MySQL, you will always need a client software. So,
the
> reporting thing should be implemented in your client software.
>
> Hope to be right!!!
>
>
> Thanks
> Emery
> - Original Message -
> From: "Wang Feng" <[EMAIL PROTECTED]>
> To: "Wang Feng" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Tuesday, October 07, 2003 12:22
> Subject: Re: printing reports
>
>
> > > In MySQL, how can I create and print reports without the help of
> > > neither
> a
> > > scripting language(e.g. php) nor a thrid party software tool?
> >
> > bad grammar :-(  but you guys understand what i'm saying, right?  :-)
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]


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

Re: Can records be moved between tables without knowing columns

[Dan Nelson]

In the last episode (Oct 07), Jim said:
> I need to be able to 'delete' records from table(s) but still
> maintain their information for an audit trail.
> 
> I'd rather not have a field for a deleted flag and have to condition
> all the queries on the table on the state of this flag.
> 
> Is it possible to move a record from one table to another easily?
> 
> Even better, can I do it opaquely (without knowledge of the columns)?
> Something
> like:
> "move from LiveUsers to DeletedUsers where ID=?;"
> where all the fields in the first table get moved to the corresponding field
> of the 2nd.  (If this is possible, what happens with autoincrement,
> timestamp fields?)

You would have to do it as two queries (if you're using BDB or InnoDB
you could do it as one transaction so it's an atomic operation):

insert into DeletedUsers select * from LiveUsers where ID = :id
delete from LiveUsers where ID = :id

The autoincrement and timestamp fields will carry over whatever values
you had in the original table, since you are implicitly selecting and
inserting them with the all-fields "*" wildcard.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: printing reports

[Wang Feng]

Hi, Rudy.

You're right. I'm looking for some commands to do the report. (of couse I
can solve the problem using PHP, but really just wanna play with MySQL alone
for this issue). But I couldn't find any command from MySQL manual. If you
can show me a link, then that would be great!!!


cheers,

feng


- Original Message -
From: "Rudy Metzger" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, October 08, 2003 1:00 AM
Subject: RE: printing reports


>
> You can use the mysql command client, it that is what you mean. You can
> also pass the query to the command client and then spool the output.
>
> mysql < the query > report file (or something like that, just check the
> manual)
>
> to do it really without any other tools, third party or not, you can
> always hack the DB :)
>
> cheers
> /rudy
>
>
> -Original Message-
> From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED]
> Sent: dinsdag 7 oktober 2003 16:49
> To: Wang Feng; [EMAIL PROTECTED]
> Subject: Re: printing reports
>
> To get data from/into MySQL, you will always need a client software.
> So, the reporting thing should be implemented in your client software.
>
> Hope to be right!!!
>
>
> Thanks
> Emery
> - Original Message -
> From: "Wang Feng" <[EMAIL PROTECTED]>
> To: "Wang Feng" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Tuesday, October 07, 2003 12:22
> Subject: Re: printing reports
>
>
> > > In MySQL, how can I create and print reports without the help of
> neither
> a
> > > scripting language(e.g. php) nor a thrid party software tool?
> >
> > bad grammar :-(  but you guys understand what i'm saying, right?  :-)
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


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

RE: Help With a DATETIME Query

[shaun thornburgh]

Thanks for your reply, but its not an option!


From: "Dan Greene" <[EMAIL PROTECTED]>
To: "shaun thornburgh" <[EMAIL PROTECTED]>, 
<[EMAIL PROTECTED]>
Subject: RE: Help With a DATETIME Query
Date: Tue, 7 Oct 2003 16:41:04 -0400

I know it's not the answer you're looking for... :(  but dealing with 
overnights has caused me so much aggravation in past apps I've written, 
I've tended to make the client create two (or more) 'bookings' for the 
covered time... don't know if it's an option for you, but it's my 
$0.02.



> -Original Message-
> From: shaun thornburgh [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 07, 2003 4:33 PM
> To: [EMAIL PROTECTED]
> Subject: Help With a DATETIME Query
>
>
> Hi,
>
> I have a table called Bookings which has two important columns;
> Booking_Start_Date and Booking_End_Date. These columns are
> both of type
> DATETIME. The following query calculates how many hours are available
> between the hours of 09.00 and 17.30 so a user can see at a
> glance how many
> hours they have unbooked on a particular day (i.e. 8.5 hours
> less the time
> of any bookings on that day). However, when a booking spans
> more than one
> day the query doesn't work, for example if a user has a
> booking that starts
> on day one at 09.00 and ends at 14.30 on the next day, the
> query returns 3.5
> hours for both days. The query is run for each day i.e day 1,
> day 2 day
> 10.
>
> Any help here would be greatly appreciated.
>
> SELECT
> 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
>  DATE_FORMAT(B.Booking_End_Date, '%i')) -
>((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) +
>  DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS
> Available_Hours
> FROM WMS_Bookings B
> WHERE B.User_ID = '16'
> AND B.Booking_Status <> '1'
> AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date,
> "%Y-%m-%d") OR
> '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )
>
> _
> On the move? Get Hotmail on your mobile phone
> http://www.msn.co.uk/msnmobile
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

_
Use MSN Messenger to send music and pics to your friends 
http://www.msn.co.uk/messenger

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

Can records be moved between tables without knowing columns

[Jim]

Hi,

I need to be able to 'delete' records from table(s) but still maintain their
information
for an audit trail.

I'd rather not have a field for a deleted flag and have to condition all the
queries on the
table on the state of this flag.

Is it possible to move a record from one table to another easily?

Even better, can I do it opaquely (without knowledge of the columns)?
Something
like:
"move from LiveUsers to DeletedUsers where ID=?;"
where all the fields in the first table get moved to the corresponding field
of the 2nd.  (If this is possible, what happens with autoincrement,
timestamp fields?)

Thanks for the help; I hope it isn't a trivial question.
I'm new at this; I didn't find anything on moving records in the list
archives.

Jim Cant

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

RE: Help With a DATETIME Query

[Dan Greene]

I know it's not the answer you're looking for... :(  but dealing with overnights has 
caused me so much aggravation in past apps I've written, I've tended to make the 
client create two (or more) 'bookings' for the covered time... don't know if it's an 
option for you, but it's my $0.02.



> -Original Message-
> From: shaun thornburgh [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 07, 2003 4:33 PM
> To: [EMAIL PROTECTED]
> Subject: Help With a DATETIME Query
> 
> 
> Hi,
> 
> I have a table called Bookings which has two important columns;
> Booking_Start_Date and Booking_End_Date. These columns are 
> both of type
> DATETIME. The following query calculates how many hours are available
> between the hours of 09.00 and 17.30 so a user can see at a 
> glance how many
> hours they have unbooked on a particular day (i.e. 8.5 hours 
> less the time
> of any bookings on that day). However, when a booking spans 
> more than one
> day the query doesn't work, for example if a user has a 
> booking that starts
> on day one at 09.00 and ends at 14.30 on the next day, the 
> query returns 3.5
> hours for both days. The query is run for each day i.e day 1, 
> day 2 day 
> 10.
> 
> Any help here would be greatly appreciated.
> 
> SELECT
> 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
>  DATE_FORMAT(B.Booking_End_Date, '%i')) -
>((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) +
>  DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS
> Available_Hours
> FROM WMS_Bookings B
> WHERE B.User_ID = '16'
> AND B.Booking_Status <> '1'
> AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, 
> "%Y-%m-%d") OR
> '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )
> 
> _
> On the move? Get Hotmail on your mobile phone 
> http://www.msn.co.uk/msnmobile
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

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

Help With a DATETIME Query

[shaun thornburgh]

Hi,

I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they have unbooked on a particular day (i.e. 8.5 hours less the time
of any bookings on that day). However, when a booking spans more than one
day the query doesn't work, for example if a user has a booking that starts
on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5
hours for both days. The query is run for each day i.e day 1, day 2 day 
10.

Any help here would be greatly appreciated.

SELECT
8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
DATE_FORMAT(B.Booking_End_Date, '%i')) -
  ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) +
DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS
Available_Hours
FROM WMS_Bookings B
WHERE B.User_ID = '16'
AND B.Booking_Status <> '1'
AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR
'2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )
_
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: myadmin for mac? or?

[Patrick Larkin]

On Tuesday, October 7, 2003, at 03:15 PM, Hassan Schroeder wrote:

katherine wrote:

I have php on mac and pc, but have mysql on the mac only. I heard 
there are GUI interfaces available for mysql but am not seeing what I 
am looking for.
google: Mac OS X MySQL GUI

finds quite a few things, including this open source offering as
hit #3 :-)
  

HTH!


Cocoa MySQL is quite good!

Patrick Larkin
Bethlehem Area School District
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

4.0.15 FULLTEXT INDEX

[Dathan Vance Pattishall]

I have to drop the FULLTEXT INDEX added to a set of dbs containing a
TEXT field with an average of 6K of text added to each row, at a growth
of 250K rows added a day to a set of 100 tables. The reason for the drop
is due to table corruption and non-related query slowdown.
 
DELETES and Frequent UPDATES of a FULLTEXT INDEX column, IMO are the
root cause of table corruption and query slow down time. 
 
The reason for this opinion is based on the situation where after adding
the FULL TEXT INDEX to the body of each user's message, table corruption
occurred everyday, and query time of non related queries to a full text
column multiplied by 10 or more. The application made frequents updates
to a table and a batch job or a user action would cause deletes.
 
Here are the corruption errors.
Can't open file: 'messages_06.MYI'. (errno: 145)


The fix
REPAIR TABLE messages_06;
 
I would see dead lock queries as well: Explanation below.
 
Prior to the FULL TEXT INDEX queries normally took no more then a
second. These same queries took nearly 5 min before I killed them. The
stuck state was either waiting for table (btw no alter/flush change was
going on) or Locked, but not locked on any particular query. The second
granularity that "show full processlist" displays did not indicate which
query was the problem. 
 
 
If mysql.com is interested in this issue, I can send a copy the table
format etc. In the meantime I have to drop the FULL TEXT INDEX.
 
 
 
 
- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688
 

Re: MyIsam or InnoDB? (WARNING -- LONG MSG)

[Dyego Souza do Carmo]

Randy:

Tuesday, October 7, 2003, 4:49:44 PM, você escreveu:

---[inicio]--

RC> Apologies for the length of this post. 

RC> We started off this little proof of concept project using MySQL
RC> InnoDB
RC> tables, in part because we figured we needed foreign key constraints,
RC> row locking, and all the other bells and whistles that one gets with
RC> DB2 (our production DB on a big - for us - project). 

RC> We are creating a reports-only application for billing and accounts
RC> receivable information now kept in a Lotus Notes database. We will
RC> export the billing information every night and do a load into MySQL.
RC> We will then use Crystal Reports (and perhaps later a Java GUI) to
RC> generate various reports like aged accounts, cash receipts, this by
RC> office, that by month, etc. etc. While good RDBMS design says we
RC> should have half a dozen tables all glued together with foreign key
RC> constraints, I'm not sure any of that really applies. It's not a
RC> transactional database, reads and writes will essentially never be
RC> concurrent so row locking is not an issue, and logging in this
RC> environment doesn't seem productive. On the other hand, we have a
RC> moderately strong dedicated server with 1  GB ram and 150  GB drive
RC> space, so resources to run one table type over another also don't
RC> seem
RC> relevent. On the gripping hand, the collective experience around here
RC> is with DB2, so on some level, some of us expect to find things like
RC> transaction logs, commit and rollback capability, and such like even
RC> if we never have occasion to use them.

RC> So, how's a person to decide? To MyIsam or not to MyIsam, that is the
RC> question.

RC> Thanks and apologies again for the long post.
RC> Randy

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


---[cortar]--

it always uses InnoDB.

My company used MyISAM for many data...
had problems with competing access.
With innodb we did not have plus no claim.

For great volume of data, InnoDB.

InnoDB,Query,MySQL

-
  ++  Dyego Souza do Carmo   ++   Dep. Desenvolvimento   
-
 E S C R I B A   I N F O R M A T I C A
-
The only stupid question is the unasked one (somewhere in Linux's HowTo)
Linux registred user : #230601
--ICQ   : 1647350
$ look into "my eyes" Phone : +55 041 2106-1212  
look: cannot open my eyes Fax   : +55 041 296 -6640
-
   Reply: [EMAIL PROTECTED]


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

MyIsam or InnoDB? (WARNING -- LONG MSG)

[Randy Chrismon]

Apologies for the length of this post. 

We started off this little proof of concept project using MySQL
InnoDB
tables, in part because we figured we needed foreign key constraints,
row locking, and all the other bells and whistles that one gets with
DB2 (our production DB on a big - for us - project). 

We are creating a reports-only application for billing and accounts
receivable information now kept in a Lotus Notes database. We will
export the billing information every night and do a load into MySQL.
We will then use Crystal Reports (and perhaps later a Java GUI) to
generate various reports like aged accounts, cash receipts, this by
office, that by month, etc. etc. While good RDBMS design says we
should have half a dozen tables all glued together with foreign key
constraints, I'm not sure any of that really applies. It's not a
transactional database, reads and writes will essentially never be
concurrent so row locking is not an issue, and logging in this
environment doesn't seem productive. On the other hand, we have a
moderately strong dedicated server with 1  GB ram and 150  GB drive
space, so resources to run one table type over another also don't
seem
relevent. On the gripping hand, the collective experience around here
is with DB2, so on some level, some of us expect to find things like
transaction logs, commit and rollback capability, and such like even
if we never have occasion to use them.

So, how's a person to decide? To MyIsam or not to MyIsam, that is the
question.

Thanks and apologies again for the long post.
Randy

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

Re: myadmin for mac? or?

[Hassan Schroeder]

katherine wrote:

I have php on mac and pc, but have mysql on the mac only. I heard there 
are GUI interfaces available for mysql but am not seeing what I am 
looking for.
google: Mac OS X MySQL GUI

finds quite a few things, including this open source offering as
hit #3 :-)
  

HTH!
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com
  dream.  code.



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

myadmin for mac? or?

[katherine]

I have php on mac and pc, but have mysql on the mac only. I heard there 
are GUI interfaces available for mysql but am not seeing what I am 
looking for.

I searched for myadmin and found notes on the mysql.com site but not 
clear to me how I get this program and or if its available for mac.

Is there a version out there? Saw one another mysql site (myadmin.org) 
but it was not a full version and was PC only. To get full version one 
has to pay. I am just learning and thought this was freeware.

Help please? Thanks!

Katherine

portfolio :: www.lucidbydesign.com :: Keep it simple.
Solutions for web and print.
Knowledge, and curiosity, can you have one without having had the 
other? Curiosity gave the cat nine lives.

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

Re: FW: MySQL not null vs MSAccess required

[Cal Evans]

I humbly submit an apology. You are correct.  This is a bug (No it is 
NOT a feature) you should be able to define a field as NOT NULL without 
a default or at the very least, define the default as NULL.

My bad,
=C=
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.
Arūnas Milašauskas wrote:
:(, oh how I wanted it to be true in MySQL 4.1, maybe it's a bug?.. I
don't know realy...
Here is Copy From Console:

mysql> create table `temp` (
-> col1 int(9) NOT NULL,
-> col2 date NOT NULL,
-> col3 Varchar(30) NOT NULL,
-> col4 Varchar(30));
Query OK, 0 rows affected (0.02 sec)
mysql> desc `temp`;
+---+-+---+--+-++---
+
| Field | Type| Collation | Null | Key | Default|
Extra |
+---+-+---+--+-++---
+
| col1  | int(9)  | binary|  | | 0  |
|
| col2  | date| latin1_swedish_ci |  | | -00-00 |
|
| col3  | varchar(30) | latin1_swedish_ci |  | ||
|
| col4  | varchar(30) | latin1_swedish_ci | YES  | | NULL   |
|
+---+-+---+--+-++---
+
4 rows in set (0.01 sec)
As you see, default NULL is only for col3 - Nullable column :(

P.S. there is no matter what type of table you create (MyISAM, InnoDB,
etc.).
So, Cal Evans, maybe you can try above create statement and email MySQL
output?






-Original Message-
From: Cal Evans [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 9:04 PM
To: Arūnas Milašauskas
Cc: Fortuno, Adam; [EMAIL PROTECTED]
Subject: Re: FW: MySQL not null vs MSAccess required
I hate to disagree with you but in the 30+ databases I've created in 
MySQL, not defining a default value leaves the default value as NULL. 
Setting that field to NOT NULL means that I have to enter something or 
the record won't commit.

=C=
p.s. I use the 3.xx series, maybe this behavior has changed in 4 but I 
hope not.
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.

Arūnas Milašauskas wrote:

No, no, no ... Dear Friends, defining column as NOT NULL and do not
specifying default value does not means that there is no default value
at all. MySQL defines default value for that column of his on decision
(for Date it's '-00-00', for text column's it is '', for number
column's it is '0' and so on...)
P.S. I use MySQL 4.1alpha, but I am true it is on other versions
too.
Regards,
Arunas
-Original Message-
From: Cal Evans [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:51 PM
To: Arūnas Milašauskas
Cc: Fortuno, Adam; [EMAIL PROTECTED]
Subject: Re: FW: MySQL not null vs MSAccess required
Of course the OT NULL is not firing, you put DEFAULT ''. This defeats 
the purpose of NOT NULL since every new record written has a default 
value that IS NOT NULL. (Whether the user entered the field or not.

remove the DEFAULT form the table definition and leave the NOT NULL. 
This will prevent ANY program (even Access) from writing to the table 
without specifying a value.

Now if you want more than that you'll have to write it into your FE. 
(i.e.not null and not '')

HTH,
=C=
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.
Arūnas Milašauskas wrote:


Yes it's true, when someone enter's NULL value, but when someone does
not even touch that field on the form? :(
In other DBMS it's like a rule to make all possible chechking on
DB for any unforeseen accident... So I'm hopefully waiting for MySQL
5xx


relise to realize this...
As for now I see that there is no other way as to make check
constraint's validation (even for Required columns :( ) on forms on
client side :(
-Original Message-
From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:33 PM
To: Arūnas Milašauskas
Subject: RE: MySQL not null vs MSAccess required
Arunas,

Then take change the table's definition to make the CustName field NOT
NULL
and don't include a default value. When someone attempts to change the
CustName field to NULL, you'll get a runtime error.
Regards,
Adam
-Original Message-
From: Arūnas Milašauskas 
Sent: Tuesday, October 07, 2003 8:33 PM
To: '[EMAIL PROTECTED]'
Subject: FW: MySQL not null vs MSAccess required



MSAccess as frontend :) sorry, you're right.

So, I defined column without default value, but nothing
changed... I still can enter empty values into field :(
Now, actualy I want MySQL to rise run-time error, because there
are many forms in access and DB structure is changing ... I mean it is
better (I think) to capture MySQL run-time errors (I have library
writen


for this) than edit every form trigger's after changing NOT

RE: FW: MySQL not null vs MSAccess required

[Arūnas Milašauskas]

:(, oh how I wanted it to be true in MySQL 4.1, maybe it's a bug?.. I
don't know realy...

Here is Copy From Console:

mysql> create table `temp` (
-> col1 int(9) NOT NULL,
-> col2 date NOT NULL,
-> col3 Varchar(30) NOT NULL,
-> col4 Varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql> desc `temp`;
+---+-+---+--+-++---
+
| Field | Type| Collation | Null | Key | Default|
Extra |
+---+-+---+--+-++---
+
| col1  | int(9)  | binary|  | | 0  |
|
| col2  | date| latin1_swedish_ci |  | | -00-00 |
|
| col3  | varchar(30) | latin1_swedish_ci |  | ||
|
| col4  | varchar(30) | latin1_swedish_ci | YES  | | NULL   |
|
+---+-+---+--+-++---
+
4 rows in set (0.01 sec)

As you see, default NULL is only for col3 - Nullable column :(

P.S. there is no matter what type of table you create (MyISAM, InnoDB,
etc.).

So, Cal Evans, maybe you can try above create statement and email MySQL
output?







-Original Message-
From: Cal Evans [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 9:04 PM
To: Arūnas Milašauskas
Cc: Fortuno, Adam; [EMAIL PROTECTED]
Subject: Re: FW: MySQL not null vs MSAccess required


I hate to disagree with you but in the 30+ databases I've created in 
MySQL, not defining a default value leaves the default value as NULL. 
Setting that field to NOT NULL means that I have to enter something or 
the record won't commit.

=C=
p.s. I use the 3.xx series, maybe this behavior has changed in 4 but I 
hope not.
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.


Arūnas Milašauskas wrote:
> No, no, no ... Dear Friends, defining column as NOT NULL and do not
> specifying default value does not means that there is no default value
> at all. MySQL defines default value for that column of his on decision
> (for Date it's '-00-00', for text column's it is '', for number
> column's it is '0' and so on...)
> 
>   P.S. I use MySQL 4.1alpha, but I am true it is on other versions
> too.
> 
> Regards,
>   Arunas
> 
> -Original Message-
> From: Cal Evans [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 07, 2003 8:51 PM
> To: Arūnas Milašauskas
> Cc: Fortuno, Adam; [EMAIL PROTECTED]
> Subject: Re: FW: MySQL not null vs MSAccess required
> 
> 
> Of course the OT NULL is not firing, you put DEFAULT ''. This defeats 
> the purpose of NOT NULL since every new record written has a default 
> value that IS NOT NULL. (Whether the user entered the field or not.
> 
> remove the DEFAULT form the table definition and leave the NOT NULL. 
> This will prevent ANY program (even Access) from writing to the table 
> without specifying a value.
> 
> Now if you want more than that you'll have to write it into your FE. 
> (i.e.not null and not '')
> 
> HTH,
> =C=
> * Cal Evans
> * http://www.eicc.com
> * We take care of your IT,
> * So you can take care of your business.
> *
> * I think inside the sphere.
> 
> 
> Arūnas Milašauskas wrote:
> 
>>Yes it's true, when someone enter's NULL value, but when someone does
>>not even touch that field on the form? :(
>>
>>  In other DBMS it's like a rule to make all possible chechking on
>>DB for any unforeseen accident... So I'm hopefully waiting for MySQL
> 
> 5xx
> 
>>relise to realize this...
>>  As for now I see that there is no other way as to make check
>>constraint's validation (even for Required columns :( ) on forms on
>>client side :(
>>
>>-Original Message-
>>From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
>>Sent: Tuesday, October 07, 2003 8:33 PM
>>To: Arūnas Milašauskas
>>Subject: RE: MySQL not null vs MSAccess required
>>
>>
>>Arunas,
>>
>>Then take change the table's definition to make the CustName field NOT
>>NULL
>>and don't include a default value. When someone attempts to change the
>>CustName field to NULL, you'll get a runtime error.
>>
>>Regards,
>>Adam
>>
>>-Original Message-
>>From: Arūnas Milašauskas 
>>Sent: Tuesday, October 07, 2003 8:33 PM
>>To: '[EMAIL PROTECTED]'
>>Subject: FW: MySQL not null vs MSAccess required
>>
>>
>>
>>MSAccess as frontend :) sorry, you're right.
>>
>>  So, I defined column without default value, but nothing
>>changed... I still can enter empty values into field :(
>>
>>  Now, actualy I want MySQL to rise run-time error, because there
>>are many forms in access and DB structure is changing ... I mean it is
>>better (I think) to capture MySQL run-time errors (I have library
> 
> writen
> 
>>for this) than edit every form trigger's after changing NOT NULL
> 
> columns
> 
>>in DB especially if there is more than one form allowing to enter data
>>into such tables...
>>
>>  Thanks, for thought about form self validation ;)
>>
>

help on Index

[Hsiu-Hui Tseng]

Hi,

After rebuild the following table, the Cardinality does not get updated.
This table have 18 million of rows. Why this happening and how do I fix it?
Should I run OPTIMIZE TABLE or CHECK TABLE?

mysql> show index from UserDetail;
++++--+-
---+---+-+--++-+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part | Packed | Comment |
++++--+-
---+---+-+--++-+
| UserDetail |  0 | PRIMARY|1 | UserID
| A |NULL | NULL | NULL   | |
| UserDetail |  0 | PRIMARY|2 |
PartnerAttributeID | A |18936654 | NULL | NULL   | |
| UserDetail |  1 | IDX_AttributeValue |1 |
PartnerAttributeID | A |NULL | NULL | NULL   | |
| UserDetail |  1 | IDX_AttributeValue |2 |
AttributeValue | A |NULL | NULL | NULL   | |
++++--+-
---+---+-+--++-+

thanks

Hsiu-Hui


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

Re: FW: MySQL not null vs MSAccess required

[Cal Evans]

I hate to disagree with you but in the 30+ databases I've created in 
MySQL, not defining a default value leaves the default value as NULL. 
Setting that field to NOT NULL means that I have to enter something or 
the record won't commit.

=C=
p.s. I use the 3.xx series, maybe this behavior has changed in 4 but I 
hope not.
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.

Arūnas Milašauskas wrote:
No, no, no ... Dear Friends, defining column as NOT NULL and do not
specifying default value does not means that there is no default value
at all. MySQL defines default value for that column of his on decision
(for Date it's '-00-00', for text column's it is '', for number
column's it is '0' and so on...)
P.S. I use MySQL 4.1alpha, but I am true it is on other versions
too.
Regards,
Arunas
-Original Message-
From: Cal Evans [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:51 PM
To: Arūnas Milašauskas
Cc: Fortuno, Adam; [EMAIL PROTECTED]
Subject: Re: FW: MySQL not null vs MSAccess required
Of course the OT NULL is not firing, you put DEFAULT ''. This defeats 
the purpose of NOT NULL since every new record written has a default 
value that IS NOT NULL. (Whether the user entered the field or not.

remove the DEFAULT form the table definition and leave the NOT NULL. 
This will prevent ANY program (even Access) from writing to the table 
without specifying a value.

Now if you want more than that you'll have to write it into your FE. 
(i.e.not null and not '')

HTH,
=C=
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.
Arūnas Milašauskas wrote:

Yes it's true, when someone enter's NULL value, but when someone does
not even touch that field on the form? :(
In other DBMS it's like a rule to make all possible chechking on
DB for any unforeseen accident... So I'm hopefully waiting for MySQL
5xx

relise to realize this...
As for now I see that there is no other way as to make check
constraint's validation (even for Required columns :( ) on forms on
client side :(
-Original Message-
From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:33 PM
To: Arūnas Milašauskas
Subject: RE: MySQL not null vs MSAccess required
Arunas,

Then take change the table's definition to make the CustName field NOT
NULL
and don't include a default value. When someone attempts to change the
CustName field to NULL, you'll get a runtime error.
Regards,
Adam
-Original Message-
From: Arūnas Milašauskas 
Sent: Tuesday, October 07, 2003 8:33 PM
To: '[EMAIL PROTECTED]'
Subject: FW: MySQL not null vs MSAccess required



MSAccess as frontend :) sorry, you're right.

So, I defined column without default value, but nothing
changed... I still can enter empty values into field :(
Now, actualy I want MySQL to rise run-time error, because there
are many forms in access and DB structure is changing ... I mean it is
better (I think) to capture MySQL run-time errors (I have library
writen

for this) than edit every form trigger's after changing NOT NULL
columns

in DB especially if there is more than one form allowing to enter data
into such tables...
	Thanks, for thought about form self validation ;)

Regards,
Arunas
	

-Original Message-
From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:09 PM
To: Arūnas Milašauskas
Cc: [EMAIL PROTECTED]
Subject: RE: MySQL not null vs MSAccess required
Arunas,

Actually, I think you mean MySQL is the backend for MS Access.

Don't assume columns that are NOT NULL must have a default value. You
are
welcome to define columns as NOT NULL without a default value. Doing
this
will force the client (the Access form's) to require a value before
inserting the record.
CREATE TABLE customers (
ID INT(9) NOT NULL,
CustName VARCHAR(30) NOT NULL,
...
) Type = MyISAM;
Since you're using an MS Access front-end to allow users to enter
data.

Put
logic in your form to require the users to populate a value for the
customer
name. Something like this:
** Assuming you have a form with customer name field named
"tbCustName".

Private Sub Form_BeforeUpdate()
 Dim strMsg as String
On Error Resume Next

 If (Not ValidateSelf()) Then
'** Notify the user that the form failed validation.
Let strMsg = "Please ensure all required fields are populated."
Call MsgBox(strMsg, vbOkOnly)

'** Cancel's the save event.
Call DoCmd.CancelEvent
 EndIf
End Sub
Private Function ValidateSelf()
 Dim blnIsValid As Boolean
 
 '** Determine if the field has a value.
 If (Not Nz(Me.tbCustName.Value, "") = "") Then Let blnIsValid = True

 '** Return the results of the validation process.
 Let ValidateSelf = blnIsValid
End Function
Its been a while since I've used Access for soemthing like this. My
VBA

might be a li

RE: Check (expr) Syntax and/or other fields checks

[Miguel Ernesto]

I wish this help! Tell me if it is more clear now.
 
# PRIMARY TABLE

-
CREATE TABLE `sku` (
# Constants Fields
 `id_ts` TIMESTAMP NOT NULL,
 `pri_key` INT(10) UNSIGNED NOT NULL auto_increment,
 `SKU` VARCHAR(10),
 `description` VARCHAR(50),
 `tax` TINYINT(1) NOT NULL DEFAULT 0,
 `CPSFree` TINYINT(1) NOT NULL DEFAULT 0,
 
# Special_Condition Field
 `udv` TINYINT(1),
 
# Values Fields: Depending on the special_value given on
udv
# If True (not 0) is given on udv
 `du` MEDIUMINT,
 `pvu` DOUBLE (0,2),
 `bvu` DOUBLE (0,2),
 `cibou` DOUBLE (0,2),
 `pmenoru` DOUBLE (0,2),
 
# If False (0) or True (not 0) is given on udv
 `dL` MEDIUMINT,
 `pvL` DOUBLE (0,2),
 `bvL` DOUBLE (0,2),
 `ciboL` DOUBLE (0,2),
 `pmenorL` DOUBLE (0,2),
#

---
 
 PRIMARY KEY `pri_key` (`pri_key`),
 UNIQUE KEY `uk_sku` (`sku`),
 INDEX `i_udv` (`sku`,`udv`),
) TYPE=InnoDB CHARSET=latin1;
#

---
 
# FOREIGN TABLE

-
CREATE TABLE `foreign_table` (
 `timestamp` TIMESTAMP NOT NULL,
 `pri_key` INT unsigned NOT NULL auto_increment,
 `c` INT NOT NULL,
 `sku` VARCHAR(10) NOT NULL,
 `udv` TINYINT(1) NOT NULL DEFAULT -1,
 PRIMARY KEY (`pri_key`),
 INDEX `i_sku` (`sku`),
 INDEX `i_udv` (`sku`,`udv`),
 
# - HERE what I Asking for
--
 # If True (not 0) is given on udv
 CONSTRAINT `fkc_sku` FOREIGN KEY (`sku`) REFERENCES `sku` (`sku`) ON
DELETE RESTRICT ON UPDATE CASCADE,
 # If False (0) or True (not 0) is given on udv
 CONSTRAINT `fkc_sku` FOREIGN KEY (`sku`,`udv`) REFERENCES `sku`
(`sku`,`udv`) ON DELETE RESTRICT ON UPDATE CASCADE
#

---
 
) TYPE=InnoDB CHARSET=latin1;
#

---
 
# Query Stament

-
SELECT 
c, ft.sku, ft.udv, 
 
# - HERE what I Looking for
-
if(ft.udv=0,`dL`,null) as u,
if(ft.udv=0,`pvL`,null)  as pv,
if(ft.udv=0,`bvL`,null)  as bv,
if(ft.udv=0,`pmenorL`,null)  as bv
#

---
 
FROM foreign_table ft JOIN sku USING (sku);
#

---


-Mensaje original-
De: Fortuno, Adam [mailto:[EMAIL PROTECTED] 
Enviado el: Martes, 07 de Octubre de 2003 13:15
Para: 'Miguel Ernesto'
CC: [EMAIL PROTECTED]
Asunto: RE: Check (expr) Syntax and/or other fields checks


Miguel:
 
First: Be more specific. Did you have an expression in mind?
 
Second: Yes, use InnoDb tables to enforce referential integrity. See the
URL (below) for information on InnoDb table usage.
 
Regards,
Adam
 
InnoDb in MySQL -> http://www.mysql.com/doc/en/InnoDB.html

-Original Message-
From: Miguel Ernesto [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 12:57 PM
To: [EMAIL PROTECTED]
Subject: Check (expr) Syntax and/or other fields checks


Hello everyones,
 
Did somebody know: 
First: 
What kind of expr it is allowed on the check condition on: create
table syntax?
 
Second:
Is it posible to force conditional FOREIGN KEY checks? 
for example:
We make One table who have one UNIQUE Key and one special condicion:
TRUE or FALSE. 
Then we create a second table who refers one field to the FOREIGN key
table
... but we got that there are 1 or 2 posible values for each row on the
FOREIGN table: 'the foreign key + true' and 'the foreing key + false'
for each row, but we want to allow just 2 posible values when the
foreign key condition its set to TRUE, and only 1 when its set to false.
And we want be completly sure that just the correspondings values are
entered on the second table.
 
Example:
First table: Primary key field: SKU+Special_Condition
SKUSpecial_ConditionAllow
A-1FALSEJUST one value with this SKU on the
foreign table
B-1TRUE 2 values with this SKU on the foreign
table: 'B-1: FALSE' and 'B-1: TRUE'
 

Second table: Foreign key field: SKU+Special_Condition
SKUSpecial_ConditionIntegrety_check
A-1TRUE Refuesed!: not valid value
A-1FALSEOk!
B-1TRUE Ok!
B-1TRUE Ok!
 
Why do I need this?
1. I have a series of rows who can take one or more values depending on
a special condition who much be specified first. 
2. I need to choose a specific field depending on the special condition
given,
3. I do not want

RE: FW: MySQL not null vs MSAccess required

[Arūnas Milašauskas]

No, no, no ... Dear Friends, defining column as NOT NULL and do not
specifying default value does not means that there is no default value
at all. MySQL defines default value for that column of his on decision
(for Date it's '-00-00', for text column's it is '', for number
column's it is '0' and so on...)

P.S. I use MySQL 4.1alpha, but I am true it is on other versions
too.

Regards,
Arunas

-Original Message-
From: Cal Evans [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:51 PM
To: Arūnas Milašauskas
Cc: Fortuno, Adam; [EMAIL PROTECTED]
Subject: Re: FW: MySQL not null vs MSAccess required


Of course the OT NULL is not firing, you put DEFAULT ''. This defeats 
the purpose of NOT NULL since every new record written has a default 
value that IS NOT NULL. (Whether the user entered the field or not.

remove the DEFAULT form the table definition and leave the NOT NULL. 
This will prevent ANY program (even Access) from writing to the table 
without specifying a value.

Now if you want more than that you'll have to write it into your FE. 
(i.e.not null and not '')

HTH,
=C=
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.


Arūnas Milašauskas wrote:
> Yes it's true, when someone enter's NULL value, but when someone does
> not even touch that field on the form? :(
> 
>   In other DBMS it's like a rule to make all possible chechking on
> DB for any unforeseen accident... So I'm hopefully waiting for MySQL
5xx
> relise to realize this...
>   As for now I see that there is no other way as to make check
> constraint's validation (even for Required columns :( ) on forms on
> client side :(
> 
> -Original Message-
> From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 07, 2003 8:33 PM
> To: Arūnas Milašauskas
> Subject: RE: MySQL not null vs MSAccess required
> 
> 
> Arunas,
> 
> Then take change the table's definition to make the CustName field NOT
> NULL
> and don't include a default value. When someone attempts to change the
> CustName field to NULL, you'll get a runtime error.
> 
> Regards,
> Adam
> 
> -Original Message-
> From: Arūnas Milašauskas 
> Sent: Tuesday, October 07, 2003 8:33 PM
> To: '[EMAIL PROTECTED]'
> Subject: FW: MySQL not null vs MSAccess required
> 
> 
> 
> MSAccess as frontend :) sorry, you're right.
> 
>   So, I defined column without default value, but nothing
> changed... I still can enter empty values into field :(
> 
>   Now, actualy I want MySQL to rise run-time error, because there
> are many forms in access and DB structure is changing ... I mean it is
> better (I think) to capture MySQL run-time errors (I have library
writen
> for this) than edit every form trigger's after changing NOT NULL
columns
> in DB especially if there is more than one form allowing to enter data
> into such tables...
> 
>   Thanks, for thought about form self validation ;)
> 
> Regards,
>   Arunas
> 
> 
>   
> 
> -Original Message-
> From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 07, 2003 8:09 PM
> To: Arūnas Milašauskas
> Cc: [EMAIL PROTECTED]
> Subject: RE: MySQL not null vs MSAccess required
> 
> 
> Arunas,
> 
> Actually, I think you mean MySQL is the backend for MS Access.
> 
> Don't assume columns that are NOT NULL must have a default value. You
> are
> welcome to define columns as NOT NULL without a default value. Doing
> this
> will force the client (the Access form's) to require a value before
> inserting the record.
> 
> CREATE TABLE customers (
>  ID INT(9) NOT NULL,
>  CustName VARCHAR(30) NOT NULL,
>  ...
> ) Type = MyISAM;
> 
> Since you're using an MS Access front-end to allow users to enter
data.
> Put
> logic in your form to require the users to populate a value for the
> customer
> name. Something like this:
> 
> ** Assuming you have a form with customer name field named
"tbCustName".
> 
> Private Sub Form_BeforeUpdate()
>   Dim strMsg as String
> 
> On Error Resume Next
> 
>   If (Not ValidateSelf()) Then
>   '** Notify the user that the form failed validation.
>   Let strMsg = "Please ensure all required fields are populated."
>   Call MsgBox(strMsg, vbOkOnly)
>   
>   '** Cancel's the save event.
>   Call DoCmd.CancelEvent
>   EndIf
> End Sub
> 
> Private Function ValidateSelf()
>   Dim blnIsValid As Boolean
>   
>   '** Determine if the field has a value.
>   If (Not Nz(Me.tbCustName.Value, "") = "") Then Let blnIsValid = True
> 
>   '** Return the results of the validation process.
>   Let ValidateSelf = blnIsValid
> End Function
> 
> Its been a while since I've used Access for soemthing like this. My
VBA
> might be a little rusty. The point is you want to enforce user
> population of
> the customer name field in the client. If you do it from MySQL, the
odbc
> driver will generate a runtime error, which Access will then need to
> capture
> - you should

Re: FW: MySQL not null vs MSAccess required

[Cal Evans]

Of course the OT NULL is not firing, you put DEFAULT ''. This defeats 
the purpose of NOT NULL since every new record written has a default 
value that IS NOT NULL. (Whether the user entered the field or not.

remove the DEFAULT form the table definition and leave the NOT NULL. 
This will prevent ANY program (even Access) from writing to the table 
without specifying a value.

Now if you want more than that you'll have to write it into your FE. 
(i.e.not null and not '')

HTH,
=C=
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.
Arūnas Milašauskas wrote:
Yes it's true, when someone enter's NULL value, but when someone does
not even touch that field on the form? :(
In other DBMS it's like a rule to make all possible chechking on
DB for any unforeseen accident... So I'm hopefully waiting for MySQL 5xx
relise to realize this...
As for now I see that there is no other way as to make check
constraint's validation (even for Required columns :( ) on forms on
client side :(
-Original Message-
From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:33 PM
To: Arūnas Milašauskas
Subject: RE: MySQL not null vs MSAccess required
Arunas,

Then take change the table's definition to make the CustName field NOT
NULL
and don't include a default value. When someone attempts to change the
CustName field to NULL, you'll get a runtime error.
Regards,
Adam
-Original Message-
From: Arūnas Milašauskas 
Sent: Tuesday, October 07, 2003 8:33 PM
To: '[EMAIL PROTECTED]'
Subject: FW: MySQL not null vs MSAccess required



MSAccess as frontend :) sorry, you're right.

So, I defined column without default value, but nothing
changed... I still can enter empty values into field :(
Now, actualy I want MySQL to rise run-time error, because there
are many forms in access and DB structure is changing ... I mean it is
better (I think) to capture MySQL run-time errors (I have library writen
for this) than edit every form trigger's after changing NOT NULL columns
in DB especially if there is more than one form allowing to enter data
into such tables...
	Thanks, for thought about form self validation ;)

Regards,
Arunas
	

-Original Message-
From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:09 PM
To: Arūnas Milašauskas
Cc: [EMAIL PROTECTED]
Subject: RE: MySQL not null vs MSAccess required
Arunas,

Actually, I think you mean MySQL is the backend for MS Access.

Don't assume columns that are NOT NULL must have a default value. You
are
welcome to define columns as NOT NULL without a default value. Doing
this
will force the client (the Access form's) to require a value before
inserting the record.
CREATE TABLE customers (
 ID INT(9) NOT NULL,
 CustName VARCHAR(30) NOT NULL,
 ...
) Type = MyISAM;
Since you're using an MS Access front-end to allow users to enter data.
Put
logic in your form to require the users to populate a value for the
customer
name. Something like this:
** Assuming you have a form with customer name field named "tbCustName".

Private Sub Form_BeforeUpdate()
  Dim strMsg as String
On Error Resume Next

  If (Not ValidateSelf()) Then
'** Notify the user that the form failed validation.
Let strMsg = "Please ensure all required fields are populated."
Call MsgBox(strMsg, vbOkOnly)

'** Cancel's the save event.
Call DoCmd.CancelEvent
  EndIf
End Sub
Private Function ValidateSelf()
  Dim blnIsValid As Boolean
  
  '** Determine if the field has a value.
  If (Not Nz(Me.tbCustName.Value, "") = "") Then Let blnIsValid = True

  '** Return the results of the validation process.
  Let ValidateSelf = blnIsValid
End Function
Its been a while since I've used Access for soemthing like this. My VBA
might be a little rusty. The point is you want to enforce user
population of
the customer name field in the client. If you do it from MySQL, the odbc
driver will generate a runtime error, which Access will then need to
capture
- you should capture errors anyway.
Regards,
Adam
-Original Message-
From: Arunas Mila?auskas [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 12:23 PM
To: [EMAIL PROTECTED]
Subject: MySQL not null vs MSAccess required
Hello,
I'm Using MSAccess as backend for my MySQL database. Tables are
linked with MyODBC 3.51, so all data user enters through MSAccess forms.
I need that some fields (CustName) in MySQL table (CUSTOMERS)
would be required, it means, that user must fill a value into that field
(CustName) displayed on MSAccess form (Customers).
MySQL table create statement is:
create table CUSTOMERS (ID `ID` int(9) NOT NULL,
`CustName` Varchar(30) NOT NULL DEFAULT '');
Default value in table declaration is requred as you know for
NOT NULL columns.
The problem is:
When user enters a new record in MSAcce

4.1 final

[Nick Marsh]

Any idea when 4.1 will be released as a final?

nick marsh
[EMAIL PROTECTED]

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

FW: MySQL not null vs MSAccess required

[Arūnas Milašauskas]

Yes it's true, when someone enter's NULL value, but when someone does
not even touch that field on the form? :(

In other DBMS it's like a rule to make all possible chechking on
DB for any unforeseen accident... So I'm hopefully waiting for MySQL 5xx
relise to realize this...
As for now I see that there is no other way as to make check
constraint's validation (even for Required columns :( ) on forms on
client side :(

-Original Message-
From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:33 PM
To: Arūnas Milašauskas
Subject: RE: MySQL not null vs MSAccess required


Arunas,

Then take change the table's definition to make the CustName field NOT
NULL
and don't include a default value. When someone attempts to change the
CustName field to NULL, you'll get a runtime error.

Regards,
Adam

-Original Message-
From: Arūnas Milašauskas 
Sent: Tuesday, October 07, 2003 8:33 PM
To: '[EMAIL PROTECTED]'
Subject: FW: MySQL not null vs MSAccess required



MSAccess as frontend :) sorry, you're right.

So, I defined column without default value, but nothing
changed... I still can enter empty values into field :(

Now, actualy I want MySQL to rise run-time error, because there
are many forms in access and DB structure is changing ... I mean it is
better (I think) to capture MySQL run-time errors (I have library writen
for this) than edit every form trigger's after changing NOT NULL columns
in DB especially if there is more than one form allowing to enter data
into such tables...

Thanks, for thought about form self validation ;)

Regards,
Arunas




-Original Message-
From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:09 PM
To: Arūnas Milašauskas
Cc: [EMAIL PROTECTED]
Subject: RE: MySQL not null vs MSAccess required


Arunas,

Actually, I think you mean MySQL is the backend for MS Access.

Don't assume columns that are NOT NULL must have a default value. You
are
welcome to define columns as NOT NULL without a default value. Doing
this
will force the client (the Access form's) to require a value before
inserting the record.

CREATE TABLE customers (
 ID INT(9) NOT NULL,
 CustName VARCHAR(30) NOT NULL,
 ...
) Type = MyISAM;

Since you're using an MS Access front-end to allow users to enter data.
Put
logic in your form to require the users to populate a value for the
customer
name. Something like this:

** Assuming you have a form with customer name field named "tbCustName".

Private Sub Form_BeforeUpdate()
  Dim strMsg as String

On Error Resume Next

  If (Not ValidateSelf()) Then
'** Notify the user that the form failed validation.
Let strMsg = "Please ensure all required fields are populated."
Call MsgBox(strMsg, vbOkOnly)

'** Cancel's the save event.
Call DoCmd.CancelEvent
  EndIf
End Sub

Private Function ValidateSelf()
  Dim blnIsValid As Boolean
  
  '** Determine if the field has a value.
  If (Not Nz(Me.tbCustName.Value, "") = "") Then Let blnIsValid = True

  '** Return the results of the validation process.
  Let ValidateSelf = blnIsValid
End Function

Its been a while since I've used Access for soemthing like this. My VBA
might be a little rusty. The point is you want to enforce user
population of
the customer name field in the client. If you do it from MySQL, the odbc
driver will generate a runtime error, which Access will then need to
capture
- you should capture errors anyway.

Regards,
Adam

-Original Message-
From: Arunas Mila?auskas [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 12:23 PM
To: [EMAIL PROTECTED]
Subject: MySQL not null vs MSAccess required


Hello,
I'm Using MSAccess as backend for my MySQL database. Tables are
linked with MyODBC 3.51, so all data user enters through MSAccess forms.
I need that some fields (CustName) in MySQL table (CUSTOMERS)
would be required, it means, that user must fill a value into that field
(CustName) displayed on MSAccess form (Customers).

MySQL table create statement is:
create table CUSTOMERS (ID `ID` int(9) NOT NULL,
`CustName` Varchar(30) NOT NULL DEFAULT '');
Default value in table declaration is requred as you know for
NOT NULL columns.

The problem is:
When user enters a new record in MSAccess form and does
not even touch field "CustName", new record is created with empty string
as default value
If user enters for example "1" and then deletes the
value in MSAccess form, Access reports, that value must not be NULL. But
what can I do if user does not even go into that field and leave empty
field? User Must Enter A Value How to do this without writing many
code in every form with required (NOT NULL) fields?


Thanks for any help, It is needed very very much because for now
any user can not fill required fields!
It would be very very appreciated

Rega

relay log hosed

[Partap Davis]

Hi,
So I set up replication and everything seemed to be going alright for a 
couple days until the slave server stopped responding.  Any attempts to 
connect to the slave server hang indefinitely, and if I ssh into the 
machine, any attempts to access the data disk also hang.

I had to hard-boot the server to get any response...the data disk 
(reiserfs) claimed it was alright, but MySQL says the relay log file is 
corrupted, verified by mysqlbinlog:
[EMAIL PROTECTED] data]# mysqlbinlog stout-relay-bin.001 >/dev/null
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 
70266926, event_type: 46
ERROR: Could not read entry at offset 756432957 : Error in log format or 
read error

fsck says the file system is consistent.  Also, no problems found with 
the master binlog...

Can I just delete the relay-bin files and reset the master info to 
continue at the point of failure?  (assuming the disk is not bad and the 
data is valid...which it seems to be so far)

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

FW: MySQL not null vs MSAccess required

[Arūnas Milašauskas]

MSAccess as frontend :) sorry, you're right.

So, I defined column without default value, but nothing
changed... I still can enter empty values into field :(

Now, actualy I want MySQL to rise run-time error, because there
are many forms in access and DB structure is changing ... I mean it is
better (I think) to capture MySQL run-time errors (I have library writen
for this) than edit every form trigger's after changing NOT NULL columns
in DB especially if there is more than one form allowing to enter data
into such tables...

Thanks, for thought about form self validation ;)

Regards,
Arunas




-Original Message-
From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 8:09 PM
To: Arūnas Milašauskas
Cc: [EMAIL PROTECTED]
Subject: RE: MySQL not null vs MSAccess required


Arunas,

Actually, I think you mean MySQL is the backend for MS Access.

Don't assume columns that are NOT NULL must have a default value. You
are
welcome to define columns as NOT NULL without a default value. Doing
this
will force the client (the Access form's) to require a value before
inserting the record.

CREATE TABLE customers (
 ID INT(9) NOT NULL,
 CustName VARCHAR(30) NOT NULL,
 ...
) Type = MyISAM;

Since you're using an MS Access front-end to allow users to enter data.
Put
logic in your form to require the users to populate a value for the
customer
name. Something like this:

** Assuming you have a form with customer name field named "tbCustName".

Private Sub Form_BeforeUpdate()
  Dim strMsg as String

On Error Resume Next

  If (Not ValidateSelf()) Then
'** Notify the user that the form failed validation.
Let strMsg = "Please ensure all required fields are populated."
Call MsgBox(strMsg, vbOkOnly)

'** Cancel's the save event.
Call DoCmd.CancelEvent
  EndIf
End Sub

Private Function ValidateSelf()
  Dim blnIsValid As Boolean
  
  '** Determine if the field has a value.
  If (Not Nz(Me.tbCustName.Value, "") = "") Then Let blnIsValid = True

  '** Return the results of the validation process.
  Let ValidateSelf = blnIsValid
End Function

Its been a while since I've used Access for soemthing like this. My VBA
might be a little rusty. The point is you want to enforce user
population of
the customer name field in the client. If you do it from MySQL, the odbc
driver will generate a runtime error, which Access will then need to
capture
- you should capture errors anyway.

Regards,
Adam

-Original Message-
From: Arunas Mila?auskas [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 12:23 PM
To: [EMAIL PROTECTED]
Subject: MySQL not null vs MSAccess required


Hello,
I'm Using MSAccess as backend for my MySQL database. Tables are
linked with MyODBC 3.51, so all data user enters through MSAccess forms.
I need that some fields (CustName) in MySQL table (CUSTOMERS)
would be required, it means, that user must fill a value into that field
(CustName) displayed on MSAccess form (Customers).

MySQL table create statement is:
create table CUSTOMERS (ID `ID` int(9) NOT NULL,
`CustName` Varchar(30) NOT NULL DEFAULT '');
Default value in table declaration is requred as you know for
NOT NULL columns.

The problem is:
When user enters a new record in MSAccess form and does
not even touch field "CustName", new record is created with empty string
as default value
If user enters for example "1" and then deletes the
value in MSAccess form, Access reports, that value must not be NULL. But
what can I do if user does not even go into that field and leave empty
field? User Must Enter A Value How to do this without writing many
code in every form with required (NOT NULL) fields?


Thanks for any help, It is needed very very much because for now
any user can not fill required fields!
It would be very very appreciated

Regards, Arunas

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

RE: Adding indexes on large tables

[Brad Teale]

Brendan,
  We have used ext2, ext3, and reiser for testing purposes, and we have
found ext3 to be terribly slow on file read/write operations.  If you need 
a journaling file system, I would go with reiser, otherwise ext2 will be 
blazingly fast.

The other thing I would do is move your DB to another drive like Dan, said.

Brad

-Original Message-
From: Brendan J Sherar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 6:27 AM
To: [EMAIL PROTECTED]
Subject: Adding indexes on large tables


Greetings to all, and thanks for the excellent resource!

I have a question regarding indexing large tables (150M+ rows, 2.6G).

The tables in question have a format like this:

word_id mediumint unsigned
doc_id mediumint unsigned

Our indexes are as follows:

PRIMARY KEY (word_id, doc_id)
INDEX (doc_id)

The heart of the question is this:

When calling ALTER IGNORE TABLE doc_word ADD PRIMARY KEY(doc_id, word_id),
ADD INDEX(doc_id), MySQL proceeds to create a working copy of the table.
This
process takes over an hour to perform. During this time, disk I/O for the
rest of the database (live) reaches a bottleneck, and slows to an
unacceptable crawl. Once the copy has been created, MySQL is able to do
the actual index build very quickly and efficiently. This process must
occur three times daily.

A) MySQL creates these temporary tables in the same directory as the
original datafile. Is there a way to cause it to use an alternate
directory (i.e., on a separate mounted disk)?

B) Is there a way to "nice" this process in such a way that the amount of
I/O it consumes in performing the copy is restricted to a manageable level
so that other requests to the disks can be served in a timely fashion?

C) Would abandoning ext3 in favor of ext2 create a substantial difference?

D) We're reluctant to upgrade to 4.0 at this point, but were we do so, are
there any significant gains in this situation?

E) The ALTER TABLE query is performed using perl DBI. Is there a lower
level call available which would improve performance?

F) Any other ideas or suggestions?

The system in question has the following setup:

Dual Xeon 2.8, 4G RAM, 2 x 146GB U160 SCSI (10,000 RPM) on RAID 1
(hardware). Redhat 8.0, 2.4.18 kernel, using ext3 fs. MySQL 3.23.56, with
myisam tables.

Relevant variables:

myisam_sort_buffer_size=512M
tmp_table_size=128M
This is a master, so bin_log is on

Thanks in advance for your help, and please keep up the excellent work!

Best,
Brendan



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

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

RE: Check (expr) Syntax and/or other fields checks

[Fortuno, Adam]

Miguel:
 
First: Be more specific. Did you have an expression in mind?
 
Second: Yes, use InnoDb tables to enforce referential integrity. See the URL
(below) for information on InnoDb table usage.
 
Regards,
Adam
 
InnoDb in MySQL -> http://www.mysql.com/doc/en/InnoDB.html
 

-Original Message-
From: Miguel Ernesto [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 12:57 PM
To: [EMAIL PROTECTED]
Subject: Check (expr) Syntax and/or other fields checks


Hello everyones,
 
Did somebody know: 
First: 
What kind of expr it is allowed on the check condition on: create table
syntax?
 
Second:
Is it posible to force conditional FOREIGN KEY checks? 
for example:
We make One table who have one UNIQUE Key and one special condicion: TRUE or
FALSE. 
Then we create a second table who refers one field to the FOREIGN key table
... but we got that there are 1 or 2 posible values for each row on the
FOREIGN table: 'the foreign key + true' and 'the foreing key + false' for
each row, but we want to allow just 2 posible values when the foreign key
condition its set to TRUE, and only 1 when its set to false.
And we want be completly sure that just the correspondings values are
entered on the second table.
 
Example:
First table: Primary key field: SKU+Special_Condition
SKUSpecial_ConditionAllow
A-1FALSEJUST one value with this SKU on the foreign
table
B-1TRUE 2 values with this SKU on the foreign table:
'B-1: FALSE' and 'B-1: TRUE'
 

Second table: Foreign key field: SKU+Special_Condition
SKUSpecial_ConditionIntegrety_check
A-1TRUE Refuesed!: not valid value
A-1FALSEOk!
B-1TRUE Ok!
B-1TRUE Ok!
 
Why do I need this?
1. I have a series of rows who can take one or more values depending on a
special condition who much be specified first. 
2. I need to choose a specific field depending on the special condition
given,
3. I do not want to allow to enter special conditions on the foreign table
who are not allowed the primary table, and
4. Later I'm going to retrieve that specific value, based on the special
condition given
 
Example:

SKUSpecial_ConditionValue1   Value2

A-1FALSE10 null
B-1TRUE 20   30
C-1 TRUE  10
20
 
As you can see:
- I would like to retrieve from A-1 just Value1 because the special
condition just allow the first value

- I would like to retrieve from B-1 or C-1 Value1 or Value2 because the
special condition allow both values
 
Any Idea?
 


A BETTER LIFE, starts with the best and more exclusive products
for HEALTH, BEAUTY and HOME, the Sweet 16 by  
Quixtar. 

Miguel Ernesto Pérez Cabrera 
Independent Businnes Owner
IBO# 1066703 
___ 
For more professional information, please go to: 
  Quixtar and  
Pronet
and contact me: at   [EMAIL PROTECTED],
or call me now 
at (809) 530-3433. 
If you have not visited yet, go to   Pronet
choose the region or country closest to you, register like guest, and
post a comments about what was that who get more interesting to you.
 

RE: Adding indexes on large tables

[Dan Greene]

an option for A-

no idea if this will work, but what if you moved your actual data file to new drive, 
and soft linked it from the other drive?

> -Original Message-
> From: Brendan J Sherar [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 07, 2003 7:27 AM
> To: [EMAIL PROTECTED]
> Subject: Adding indexes on large tables
> 
> 
> Greetings to all, and thanks for the excellent resource!
> 
> I have a question regarding indexing large tables (150M+ rows, 2.6G).
> 
> The tables in question have a format like this:
> 
> word_id mediumint unsigned
> doc_id mediumint unsigned
> 
> Our indexes are as follows:
> 
> PRIMARY KEY (word_id, doc_id)
> INDEX (doc_id)
> 
> The heart of the question is this:
> 
> When calling ALTER IGNORE TABLE doc_word ADD PRIMARY 
> KEY(doc_id, word_id),
> ADD INDEX(doc_id), MySQL proceeds to create a working copy of 
> the table. This
> process takes over an hour to perform. During this time, disk 
> I/O for the
> rest of the database (live) reaches a bottleneck, and slows to an
> unacceptable crawl. Once the copy has been created, MySQL is 
> able to do
> the actual index build very quickly and efficiently. This process must
> occur three times daily.
> 
> A) MySQL creates these temporary tables in the same directory as the
> original datafile. Is there a way to cause it to use an alternate
> directory (i.e., on a separate mounted disk)?
> 
> B) Is there a way to "nice" this process in such a way that 
> the amount of
> I/O it consumes in performing the copy is restricted to a 
> manageable level
> so that other requests to the disks can be served in a timely fashion?
> 
> C) Would abandoning ext3 in favor of ext2 create a 
> substantial difference?
> 
> D) We're reluctant to upgrade to 4.0 at this point, but were 
> we do so, are
> there any significant gains in this situation?
> 
> E) The ALTER TABLE query is performed using perl DBI. Is there a lower
> level call available which would improve performance?
> 
> F) Any other ideas or suggestions?
> 
> The system in question has the following setup:
> 
> Dual Xeon 2.8, 4G RAM, 2 x 146GB U160 SCSI (10,000 RPM) on RAID 1
> (hardware). Redhat 8.0, 2.4.18 kernel, using ext3 fs. MySQL 
> 3.23.56, with
> myisam tables.
> 
> Relevant variables:
> 
> myisam_sort_buffer_size=512M
> tmp_table_size=128M
> This is a master, so bin_log is on
> 
> Thanks in advance for your help, and please keep up the 
> excellent work!
> 
> Best,
> Brendan
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

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

RE: MySQL not null vs MSAccess required

[Fortuno, Adam]

Arunas,

Actually, I think you mean MySQL is the backend for MS Access.

Don't assume columns that are NOT NULL must have a default value. You are
welcome to define columns as NOT NULL without a default value. Doing this
will force the client (the Access form's) to require a value before
inserting the record.

CREATE TABLE customers (
 ID INT(9) NOT NULL,
 CustName VARCHAR(30) NOT NULL,
 ...
) Type = MyISAM;

Since you're using an MS Access front-end to allow users to enter data. Put
logic in your form to require the users to populate a value for the customer
name. Something like this:

** Assuming you have a form with customer name field named "tbCustName".

Private Sub Form_BeforeUpdate()
  Dim strMsg as String

On Error Resume Next

  If (Not ValidateSelf()) Then
'** Notify the user that the form failed validation.
Let strMsg = "Please ensure all required fields are populated."
Call MsgBox(strMsg, vbOkOnly)

'** Cancel's the save event.
Call DoCmd.CancelEvent
  EndIf
End Sub

Private Function ValidateSelf()
  Dim blnIsValid As Boolean
  
  '** Determine if the field has a value.
  If (Not Nz(Me.tbCustName.Value, "") = "") Then Let blnIsValid = True

  '** Return the results of the validation process.
  Let ValidateSelf = blnIsValid
End Function

Its been a while since I've used Access for soemthing like this. My VBA
might be a little rusty. The point is you want to enforce user population of
the customer name field in the client. If you do it from MySQL, the odbc
driver will generate a runtime error, which Access will then need to capture
- you should capture errors anyway.

Regards,
Adam

-Original Message-
From: Arunas Mila?auskas [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 12:23 PM
To: [EMAIL PROTECTED]
Subject: MySQL not null vs MSAccess required


Hello,
I'm Using MSAccess as backend for my MySQL database. Tables are
linked with MyODBC 3.51, so all data user enters through MSAccess forms.
I need that some fields (CustName) in MySQL table (CUSTOMERS)
would be required, it means, that user must fill a value into that field
(CustName) displayed on MSAccess form (Customers).

MySQL table create statement is:
create table CUSTOMERS (ID `ID` int(9) NOT NULL,
`CustName` Varchar(30) NOT NULL DEFAULT '');
Default value in table declaration is requred as you know for
NOT NULL columns.

The problem is:
When user enters a new record in MSAccess form and does
not even touch field "CustName", new record is created with empty string
as default value
If user enters for example "1" and then deletes the
value in MSAccess form, Access reports, that value must not be NULL. But
what can I do if user does not even go into that field and leave empty
field? User Must Enter A Value How to do this without writing many
code in every form with required (NOT NULL) fields?


Thanks for any help, It is needed very very much because for now
any user can not fill required fields!
It would be very very appreciated

Regards, Arunas

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

Re: [Fwd: MySQL w/dual-master replication?]

[Don MacAskill]

Hi Andrew,

Thanks for the reply.  Great question.

It's a very read-heavy system.

Are you doing high-availability MySQL yourself?  I'd love to hear how 
you're doing it, if so.

Thanks!

Don

Andrew Braithwaite wrote:

Is the system read-heavy or write-heavy?

Cheers,

Andrew

-Original Message-
From: Don MacAskill [mailto:[EMAIL PROTECTED] 
Sent: Monday 06 October 2003 20:47
To: [EMAIL PROTECTED]
Subject: [Fwd: MySQL w/dual-master replication?]



Hey all,

I sent this a few days ago, but it may have gotten lost in the weekend 
for many of you.  Don't worry, I won't keep re-posting it.  :)

I'm hoping someone out there is doing some sort of high-availability 
replication and can give me a few pointers.

Thanks in advance!

Don

 Original Message 
Subject: MySQL w/dual-master replication?
Date: Sat, 04 Oct 2003 11:07:43 -0700
From: Don MacAskill <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Hi there,

I realize it's not supported, but nonetheless, I need to investigate how
possible it is.  The primary goal is high-availability, not performance
scaling.
It sounds like if I'm careful, it might work out ok.  Careful means:

- No auto_increment columns... handle unique key assignment in my app

- Update/insert/delete on primary master only except in case of failure

- Prevent possibly 'flap' by automatically using secondary master
exclusively after primary master failure.  Bring up primary master manually
and manually tell the app when to start using it again after it's allowed to
catch back up.
Are there any other gotchas I need to worry about?  Is anyone actually doing
this in a production environment?  I'd love to hear from you.
Another option is to use master/slave, and have a monitoring app change the
slave's status to master after the master has died.  There's info about this
in the FAQ (http://www.mysql.com/doc/en/Replication_FAQ.html), but I'm
afraid the documentation on the sequence of events is pretty vague.  Does
anyone have any insight as to exactly how it works?
In particular, one part of the doc says to use 'STOP SLAVE; RESET MASTER;
CHANGE MASTER TO' and another part of the doc says 'STOP SLAVE;
  CHANGE MASTER TO' ...  which is appropriate?   Certainly, I understand
why 'STOP SLAVE', but why 'RESET MASTER'?   Would the sequence of events
differ if we're just dealing with 1 master/1 slave instead of 1
master/multiple slaves?
Once the old master joins back up, I can let it be a slave for awhile to
catch back up, then swap back, correct?
Thanks in advance!

Don MacAskill
http://onethumb.smugmug.com/






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

Check (expr) Syntax and/or other fields checks

[Miguel Ernesto]

Title: Mensaje



Hello 
everyones,
 
Did somebody know: 

First: 

    What kind of expr it is 
allowed on the check condition on: create table 
syntax?
 
Second:
    Is it posible to force 
conditional FOREIGN KEY checks? 
    
for example:
We make One table who have one UNIQUE Key and 
one special condicion: TRUE or FALSE. 
Then we create a second 
table who refers one field to the FOREIGN key table
... 
but we 
got that there are 1 or 2 posible values for each row on the FOREIGN table: 'the 
foreign key + true' and 'the foreing key + false' for each row, but we want 
to allow just 2 posible values when the foreign key condition its set to TRUE, 
and only 1 when its set to false.
And we want be 
completly sure that just the correspondings values are entered on the second 
table.
 
Example:
First table: Primary key field: 
SKU+Special_Condition
SKU    
Special_Condition    Allow
A-1FALSEJUST 
one value with this SKU on the foreign table
B-1    
TRUE 
2 values with this SKU on the foreign table: 'B-1: FALSE' and 'B-1: 
TRUE'
 

Second table: Foreign key field: 
SKU+Special_Condition
SKU    
Special_Condition    Integrety_check
A-1    
TRUE 
Refuesed!: not valid value
A-1    
FALSEOk!
B-1    
TRUE 
Ok!
B-1    
TRUE 
Ok!
 
Why do I need 
this?
1. I have a series 
of rows who can take one or more values depending on a special condition who 
much be specified first. 
2. I need to 
choose a specific field depending on the special condition 
given,
3. I do not want to 
allow to enter special conditions on the foreign table who are not 
allowed the primary table, and
4. Later I'm going 
to retrieve that specific value, based on the special condition 
given
 
Example:

SKU    
Special_Condition    
Value1   Value2

A-1    
FALSE    
10 null
B-1    
TRUE 20   30
C-1 
TRUE  
10  
20
 
As you can 
see:
- I would like 
to retrieve from A-1 just Value1 because the special condition just allow 
the first value

- I would like 
to retrieve from B-1 or C-1 Value1 or Value2 because the special 
condition allow both 
values
 
Any 
Idea?
 

A BETTER LIFE, starts with the best and more exclusive 
productsfor HEALTH, BEAUTY and HOME, the Sweet 16 by Quixtar. Miguel 
Ernesto Pérez Cabrera Independent Businnes OwnerIBO# 
1066703 ___ 
For more professional information, please go to: Quixtar and 
Pronetand contact me: at [EMAIL PROTECTED], or 
call me now at (809) 530-3433. If you have not visited yet, go to 
Pronetchoose the region or country closest to you, 
register like guest, andpost a comments about what was that who get more 
interesting to you.
 
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Replication

[Christopher Ferry]

I don't recommend using differing versions.
That's a no no.
Upgrade XP to 4.1 before you start replication.

On Tue, 2003-10-07 at 12:45, Fernando Gomes Bernardino wrote:
> Can I do a replication using:
> - Master with Linux and MySQL 4.1
> - Slave with Windows XP and MySQL 4.0.15
> 
> Thanks!
> 
> Fernando Bernardino
-- 


[image]
 

Christopher Ferry
Senior Systems Administrator
WhenU.com
494 8th Avenue, Floor 21
New York, NY 10001

Email: [EMAIL PROTECTED]
Tel: 212.239.
Fax: 212.239.4442

 

[image]


 



signature.asc
Description: This is a digitally signed message part

Replication

[Fernando Gomes Bernardino]

Can I do a replication using:
- Master with Linux and MySQL 4.1
- Slave with Windows XP and MySQL 4.0.15

Thanks!

Fernando Bernardino

MySQL not null vs MSAccess required

[Arūnas Milašauskas]

Hello,
I'm Using MSAccess as backend for my MySQL database. Tables are
linked with MyODBC 3.51, so all data user enters through MSAccess forms.
I need that some fields (CustName) in MySQL table (CUSTOMERS)
would be required, it means, that user must fill a value into that field
(CustName) displayed on MSAccess form (Customers).

MySQL table create statement is:
create table CUSTOMERS (ID `ID` int(9) NOT NULL,
`CustName` Varchar(30) NOT NULL DEFAULT '');
Default value in table declaration is requred as you know for
NOT NULL columns.

The problem is:
When user enters a new record in MSAccess form and does
not even touch field "CustName", new record is created with empty string
as default value
If user enters for example "1" and then deletes the
value in MSAccess form, Access reports, that value must not be NULL. But
what can I do if user does not even go into that field and leave empty
field? User Must Enter A Value How to do this without writing many
code in every form with required (NOT NULL) fields?


Thanks for any help, It is needed very very much because for now
any user can not fill required fields!
It would be very very appreciated

Regards, Arunas


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

RE: how to export data from multiple tables

[Andy Eastham]

Nitin,

Create a temporary table, then select each table into it in turn, then
output that to the file?

Andy

> -Original Message-
> From: Nitin [mailto:[EMAIL PROTECTED]
> Sent: 07 October 2003 11:40
> To: [EMAIL PROTECTED]
> Subject: how to export data from multiple tables
>
>
> Hi all,
>
> I've got a small problem (hope it's very small). Could any of you
> suggest me how to select my data into a simple text file from
> multiple tables (selected with union)!
>
> I've tried:
>
> select field-list into outfile filename from table1
> union
> select field-list into outfile filename from table2
> union
> select field-list into outfile filename from table3
>
> It says check your version manualblah blah
>
> Any idea, how to do it?
>
> Thanx in advance
> Nitin



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

Re: how to export data from multiple tables

[Nitin]

can u suggest any workaround?

anyway thanx for help
Nitin

- Original Message - 
From: "Victoria Reznichenko" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 8:32 PM
Subject: Re: how to export data from multiple tables


> "Nitin" <[EMAIL PROTECTED]> wrote:
> > union works fine but with union 'into outfile' doesn't work
>
> UNION with SELECT .. INTO OUTFILE works from version 4.1
>
> mysql> SELECT * FROM t1 UNION SELECT * FROM t2 INTO OUTFILE
'/tmp/tmp.txt';
> Query OK, 4 rows affected (0.00 sec)
>
>
> >
> > anyway mysql ver is 4.0.14
> >
> > thanx for ur time anyway
> > Nitin
> >
> > - Original Message ---
>
> -- 
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
><___/   www.mysql.com
>
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



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

RE: Query help

[Fortuno, Adam]

Darryl,

At first blush, I would try something like:

 DELETE faqcat
 FROM faqcat LEFT JOIN article 
 ON faqcat.cat = article.cat 
 WHERE article.cat Is Null;

I vaguley remember MySQL implementing syntax permitting users to remove data
from one or more tables. I think this might be your ticket.

Regards,
Adam

-Original Message-
From: Darryl Hoar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 11:11 AM
To: [EMAIL PROTECTED]
Subject: Query help


I have a two tables.
One has catagories, the other has articles.
I need a query that deletes catagories only
if not articles have that catagory.

Table faqcat(cat int not null auto_increment, name varchar(20))

Table article(aid in not null auto_increment, cat int, .)

Want to :
delete from faqcat where cat=1 (if no records in table article
have field with cat set to 1.

how do I accomplish this with a single query ?

thanks,
-D

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

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

Re: Query help

[Mojtaba Faridzad]

you can create a temporary table (as del_temp_table) by this select command:

SELECT faqcat.cat FROM faqcat LEFT JOIN article ON faqcat.cat = article.cat
WHERE article.cat IS NULL;

then delete them:

DELETE FROM faqcat USING faqcat, del_temp_table WHERE faqcat.cat =
del_temp_table.cat ;

I hope it works

- Original Message - 
From: "Darryl Hoar" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 11:10 AM
Subject: Query help


> I have a two tables.
> One has catagories, the other has articles.
> I need a query that deletes catagories only
> if not articles have that catagory.
>
> Table faqcat(cat int not null auto_increment, name varchar(20))
>
> Table article(aid in not null auto_increment, cat int, .)
>
> Want to :
> delete from faqcat where cat=1 (if no records in table article
> have field with cat set to 1.
>
> how do I accomplish this with a single query ?
>
> thanks,
> -D
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


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

mysql_error() returning nothing

[Ben Edwards]

I have been having this problem for a long time.  A while ago I wrote
the below functions to handle database errors.  They used to display the
message and email it to me.  Now mysql_error() returns nothing!

Any help would be greatly appreciated, as you can imagine this is
causing a lot of problems.

Are they any other ways of getting the error?

Ben

  function query_db( $sql, $db ) {
$result = mysql_query( $sql, $db ) or
  error_db( $sql, $db );
return $result;
  }

  function error_db( $sql, $db ) {

  global $SERVER_NAME, $SCRIPT_NAME;

  table_top( "Database Error" );

  table_middle();

  $sqlerr = mysql_error( $db );

  echo "SQL::$sqlError:$sqlerr";

  table_bottom();

  // Clost of table/html from calling script
  table_bottom();
  html_footer();

  // Send error via email

  $msg  =
"Application error has accured on CriticalDistribution
instalation ".
"on '$SERVER_NAME'. The error message is :-\n\n".
"SQL:$sql\n\nError:$sqlerr\n\n".
"This message was ".
"generated by '$SERVER_NAME/$SCRIPT_NAME'";

  $subj = "CritDist App error from $SERVER_NAME";

  // Hard coded to minimize chance of this module erroring
  $to   = "CriticalDistribution <[EMAIL PROTECTED]>";
  $from = "From: ".$to;

  mail($to, $subj, $msg, $from);

  die();
  }


-- 

* Ben Edwards   Tel +44 (0)1179 553 551  ICQ 42000477  *
* Homepage - nothing of interest here   http://gurtlush.org.uk *
* Webhosting for the masses http://www.serverone.co.uk *
* Critical Site Builderhttp://www.criticaldistribution.com *
* online collaborative web authoring content management system *
* Get alt news/views films online   http://www.cultureshop.org *
* i-Contact Progressive Video  http://www.videonetwork.org *
* Fun corporate graphics http://www.subvertise.org *
* Bristol Indymedia   http://bristol.indymedia.org *
* Bristol's radical news http://www.bristle.org.uk *



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

RE: printing reports

[Fortuno, Adam]

If you're going the ODBC route, StarOffice sounds like a cheap alternative
(and a good suggestion); however, there are a ton of other tools at your
disposal too - MS Access if you've got access to a Win32 workstation.

Regards,
Adam

-Original Message-
From: Christensen, Dave [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 11:11 AM
To: Wang Feng; [EMAIL PROTECTED]
Subject: RE: printing reports


You can, via ODBC, connect to MySQL using OpenOffice or Star Office which
would give you a no-cost, or low-cost, option for producing reports from
MySQL.  There are a number of execellent web articles about interfacing
either of these with MySQL.  Try searching via Google.

-Original Message-
From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 07, 2003 9:49 AM
To: Wang Feng; [EMAIL PROTECTED]
Subject: Re: printing reports


To get data from/into MySQL, you will always need a client software. So, the
reporting thing should be implemented in your client software.

Hope to be right!!!


Thanks
Emery
- Original Message -
From: "Wang Feng" <[EMAIL PROTECTED]>
To: "Wang Feng" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 12:22
Subject: Re: printing reports


> > In MySQL, how can I create and print reports without the help of 
> > neither
a
> > scripting language(e.g. php) nor a thrid party software tool?
>
> bad grammar :-(  but you guys understand what i'm saying, right?  :-)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>



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

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

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

RE: printing reports

[Christensen, Dave]

You can, via ODBC, connect to MySQL using OpenOffice or Star Office which
would give you a no-cost, or low-cost, option for producing reports from
MySQL.  There are a number of execellent web articles about interfacing
either of these with MySQL.  Try searching via Google.

-Original Message-
From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 07, 2003 9:49 AM
To: Wang Feng; [EMAIL PROTECTED]
Subject: Re: printing reports


To get data from/into MySQL, you will always need a client software. So, the
reporting thing should be implemented in your client software.

Hope to be right!!!


Thanks
Emery
- Original Message -
From: "Wang Feng" <[EMAIL PROTECTED]>
To: "Wang Feng" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 12:22
Subject: Re: printing reports


> > In MySQL, how can I create and print reports without the help of 
> > neither
a
> > scripting language(e.g. php) nor a thrid party software tool?
>
> bad grammar :-(  but you guys understand what i'm saying, right?  :-)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>



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

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

Query help

[Darryl Hoar]

I have a two tables.
One has catagories, the other has articles.
I need a query that deletes catagories only
if not articles have that catagory.

Table faqcat(cat int not null auto_increment, name varchar(20))

Table article(aid in not null auto_increment, cat int, .)

Want to :
delete from faqcat where cat=1 (if no records in table article
have field with cat set to 1.

how do I accomplish this with a single query ?

thanks,
-D

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

Re: how to export data from multiple tables

[Victoria Reznichenko]

"Nitin" <[EMAIL PROTECTED]> wrote:
> union works fine but with union 'into outfile' doesn't work

UNION with SELECT .. INTO OUTFILE works from version 4.1

mysql> SELECT * FROM t1 UNION SELECT * FROM t2 INTO OUTFILE '/tmp/tmp.txt';
Query OK, 4 rows affected (0.00 sec)


> 
> anyway mysql ver is 4.0.14
> 
> thanx for ur time anyway
> Nitin
> 
> - Original Message ---

-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





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

RE: printing reports

[Rudy Metzger]

You can use the mysql command client, it that is what you mean. You can
also pass the query to the command client and then spool the output.

mysql < the query > report file (or something like that, just check the
manual)

to do it really without any other tools, third party or not, you can
always hack the DB :)

cheers
/rudy


-Original Message-
From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] 
Sent: dinsdag 7 oktober 2003 16:49
To: Wang Feng; [EMAIL PROTECTED]
Subject: Re: printing reports

To get data from/into MySQL, you will always need a client software.
So, the reporting thing should be implemented in your client software.

Hope to be right!!!


Thanks
Emery
- Original Message -
From: "Wang Feng" <[EMAIL PROTECTED]>
To: "Wang Feng" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 12:22
Subject: Re: printing reports


> > In MySQL, how can I create and print reports without the help of
neither
a
> > scripting language(e.g. php) nor a thrid party software tool?
>
> bad grammar :-(  but you guys understand what i'm saying, right?  :-)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>



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


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

Re: printing reports

[Director General: NEFACOMP]

To get data from/into MySQL, you will always need a client software.
So, the reporting thing should be implemented in your client software.

Hope to be right!!!


Thanks
Emery
- Original Message -
From: "Wang Feng" <[EMAIL PROTECTED]>
To: "Wang Feng" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 12:22
Subject: Re: printing reports


> > In MySQL, how can I create and print reports without the help of neither
a
> > scripting language(e.g. php) nor a thrid party software tool?
>
> bad grammar :-(  but you guys understand what i'm saying, right?  :-)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>



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

Re: error messages in different languages

[Egor Egorov]

"Director General: NEFACOMP" <[EMAIL PROTECTED]> wrote:
>>
>> Yup, there is a comp-err.exe in the bin directory.
> 
> I am sorry but I asked this after not finding it. Will you please give me an
> URL on the MySQL website where I can download it from ?

What version of MySQL do you use? I don't exactly remember in which version 
comp-err.exe was added into windows distribution, seems it was 3.23.50. 




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




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

RE: Multiple Join Issue

[Rudy Metzger]

If you want to restrict the tuples you get from 'broadcasts' use AND
instead of WHERE.

Cheers
/rudy

-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED] 
Sent: dinsdag 7 oktober 2003 15:42
To: Wayne Helman
Cc: [EMAIL PROTECTED]
Subject: Re: Multiple Join Issue

Leave off the 'WHERE e.Game_ID is NULL'

Wayne Helman wrote:

>How would one join multiple table and selected all
>records from a joined table whether they exist OR not?
>I can get the records if they exist in a second table
>and I can get the records if they don't exist, but is
>there a way to combine the two?
>
>I have, for example, a statement like this (selects
>where doesn't exist):
>SELECT 
>  c.Game_Date AS Date,
>  c.Game_Time AS Time, 
>  a.Team_Name AS Home_Team, 
>  b.Team_Name AS Away_Team, 
>  e.Game_ID
>  FROM schedule c
>  JOIN Teams a ON c.Home_Team = a.ID
>  JOIN Teams b ON c.Away_Team = b.ID 
>  LEFT JOIN broadcasts e ON c.ID = e.Game_ID WHERE
>e.Game_ID IS NULL
>
>Any ideas?
>
>__ 
>Post your free ad now! http://personals.yahoo.ca
>
>  
>



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


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

Re: error messages in different languages

[Director General: NEFACOMP]

>
> Yup, there is a comp-err.exe in the bin directory.

I am sorry but I asked this after not finding it. Will you please give me an
URL on the MySQL website where I can download it from ?


Thanks
Emery
- Original Message -
From: "Egor Egorov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 11:21
Subject: Re: error messages in different languages


> "Director General: NEFACOMP" <[EMAIL PROTECTED]> wrote:
> >
> > I have two questions about error messages in MySQL.
> >
> > 1. Section 4.6.2 talks about Non-English error messages. It says that
for recompiling the errmsg.sys file, one will issue a comp_err command at
the SHELL prompt.
> > Does such a utility exist for Windows? I mean can someone tell me when
to find the 'comp_err.exe' in the MySQL Windows distribution?
>
> Yup, there is a comp-err.exe in the bin directory.
>
> > 2. One can change the error message file used by MySQL to instruct it to
to send error messages in a given language. My question is that my
application will be used by both English and French speakers. So, you
understand that I don't need to use this or that error file; I need to use
both at the same time. Is there anyway to instruct MySQL to use both error
files and send messages based on client request? So that whenever the
connected client will get personalized error messages as MySQL accepts
different character SETs to be used based on Client request?
>
> You can't.
>
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
><___/   www.mysql.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>



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

convert databases

[Gustavo Polillo]

Hi,

 I am using Oracle, Mysql Postgresql and ...MS-SQLserver, so i´d like to 
know what the best tool to convert databases... The DBTolls is a good 
option? does anyone use it?

thanks.
  

  ---
   Gustavo Polillo Correa - Analista de Sistemas
   Instituto de Biociencias - USP
   Sao Paulo - Brasil
   Tel/Fax : 55-11-3091-7436
  ---


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

Re: Multiple Join Issue

[gerald_clark]

Leave off the 'WHERE e.Game_ID is NULL'

Wayne Helman wrote:

How would one join multiple table and selected all
records from a joined table whether they exist OR not?
I can get the records if they exist in a second table
and I can get the records if they don't exist, but is
there a way to combine the two?
I have, for example, a statement like this (selects
where doesn't exist):
SELECT 
 c.Game_Date AS Date,
 c.Game_Time AS Time, 
 a.Team_Name AS Home_Team, 
 b.Team_Name AS Away_Team, 
 e.Game_ID
 FROM schedule c
 JOIN Teams a ON c.Home_Team = a.ID
 JOIN Teams b ON c.Away_Team = b.ID 
 LEFT JOIN broadcasts e ON c.ID = e.Game_ID WHERE
e.Game_ID IS NULL

Any ideas?

__ 
Post your free ad now! http://personals.yahoo.ca

 



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

Re: mysqldump from cron > empty file

[Patrick Larkin]

On Monday, October 6, 2003, at 05:11 PM, Paul DuBois wrote:

I don't understand.  Here you say that mysqldump is in the path, but 
above
you say that you don't know if mysqldump is in the path for cron jobs.

I suggest you change the crontab entry to invoke mysqldump by its full
pathname and then see what happens.


Paul - Sorry.  I was confused about the PATH and cron.  You were 
correct and it works fine now.  Thanks again.

Patrick 

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

Re: Problem setting root password.

[gerald_clark]

Rob Yale wrote:

Hi folks,

The following is copied exactly from my machine, except for the munged
password.  Setting the root password can't be completed, because the host
apparently can't connect the mysql server.  What am I doing wrong?:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root  password 'new-password'
/usr/bin/mysqladmin -u root -h melody.yalemusic.ca  password 'new-password'
See the manual for more instructions.
NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!
Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
Starting mysqld daemon with databases from /var/lib/mysql
[EMAIL PROTECTED] local]# /usr/bin/mysqladmin -u root password 'xxx'
You just successfully set the password.

[EMAIL PROTECTED] local]# /usr/bin/mysqladmin -u root -h melody.yalemusic.ca
password 'xxx'
/usr/bin/mysqladmin: connect to server at 'melody.yalemusic.ca' failed
error: 'Host 'melody.yalemusic' is not allowed to connect to this MySQL
server'
Two problems here.
1. You have one password per user. You set it above, but don't use it here.
2. Root can only connect via localhost until you GRANT privileges from 
elsewhere.
   You are trying to connect from melody.yale.ca.  This is not 
localhost, even though
   it is the same machine.

[EMAIL PROTECTED] local]#

Thanks,

Rob Yale

 



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

Adding indexes on large tables

[Brendan J Sherar]

Greetings to all, and thanks for the excellent resource!

I have a question regarding indexing large tables (150M+ rows, 2.6G).

The tables in question have a format like this:

word_id mediumint unsigned
doc_id mediumint unsigned

Our indexes are as follows:

PRIMARY KEY (word_id, doc_id)
INDEX (doc_id)

The heart of the question is this:

When calling ALTER IGNORE TABLE doc_word ADD PRIMARY KEY(doc_id, word_id),
ADD INDEX(doc_id), MySQL proceeds to create a working copy of the table. This
process takes over an hour to perform. During this time, disk I/O for the
rest of the database (live) reaches a bottleneck, and slows to an
unacceptable crawl. Once the copy has been created, MySQL is able to do
the actual index build very quickly and efficiently. This process must
occur three times daily.

A) MySQL creates these temporary tables in the same directory as the
original datafile. Is there a way to cause it to use an alternate
directory (i.e., on a separate mounted disk)?

B) Is there a way to "nice" this process in such a way that the amount of
I/O it consumes in performing the copy is restricted to a manageable level
so that other requests to the disks can be served in a timely fashion?

C) Would abandoning ext3 in favor of ext2 create a substantial difference?

D) We're reluctant to upgrade to 4.0 at this point, but were we do so, are
there any significant gains in this situation?

E) The ALTER TABLE query is performed using perl DBI. Is there a lower
level call available which would improve performance?

F) Any other ideas or suggestions?

The system in question has the following setup:

Dual Xeon 2.8, 4G RAM, 2 x 146GB U160 SCSI (10,000 RPM) on RAID 1
(hardware). Redhat 8.0, 2.4.18 kernel, using ext3 fs. MySQL 3.23.56, with
myisam tables.

Relevant variables:

myisam_sort_buffer_size=512M
tmp_table_size=128M
This is a master, so bin_log is on

Thanks in advance for your help, and please keep up the excellent work!

Best,
Brendan



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

Re: Table crashes

[Thierno Cissé]

Hello,

> MyODBC 3.51 is the only way that I access the database. (MyODBC is used in
a multithreaded server and in >InternetInformationServer (asp))
>The server is coded in c++ and got a class that acts like a odbc pool
(keeps connections open during the lifetime of the server >and handles them
to threads that requests a db connection)

It may help to know more about the work of features (multithreaded server,
'odbc pool') you are using with MySQL.
I have only one vague idea it above. I know that MySQL is multi-threaded (it
may have many queries on the same table simultaneously).

Remarks that keeps connections open during the lifetime of the server and
handles them to threads that requests a db connection SHOULDN'T be the
culprit of table crash. In contrary this is a good thing;  PHP, that i used,
do the same job with the feature of PERSISTENT CONNECTIONS.
Also the version of MyODBC (MyODBC 3.51 ) you are using is declared STABLE
by MySQL , though MySQL says :
'some issues brought up appear to be application-related and independent of
the ODBC driver or underlying database server.

> How are tables getting unclean?
   Has many ways like :
   - the mysql daemon server stops in a abnormal way , you can see it in the
mysql error log file
   - a query fail to continue processing
   - a system problem

> When are tables closed?
A table is opened for each concurrent acces,
A table is closed when the connection using them is closed / lost

Regards
Thierno6C - MySQL 4.0.15

- Original Message - 
From: "Datatal AB - Gauffin, Jonas" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 10:05 AM
Subject: SV: Table crashes


Hello

> crashes occurs for some reasons like :
> - unclean tables ( tables are closed improprely),
> - hardware problems
> - something goes wrong in the system
> - know bugs
> Check that your tables are clean, before start using them.

MyODBC 3.51 is the only way that I access the database. (MyODBC is used in a
multithreaded server and in InternetInformationServer (asp))
The server is coded in c++ and got a class that acts like a odbc pool (keeps
connections open during the lifetime of the server and handles them to
threads that requests a db connection)

How are tables getting unclean? When are tables closed?
Should I disconnect/connect when a thread is handling the dbconnection back
to the pool?


> It seems that you said the REPAIR doesn't occurs after you
> configuring related variables in the my.cnf file ? Because
> the daemon will not start when the my.cnf contains any errors
> (variable names),  the format is to be correct.

No. I asked if my configuration were correct. I've just added it to my.cnf.



> Can you indicates what MySQL version you run and your OS version.

Win2000
Mysql 4.0.12 (mysqld.exe)
MyODBC was an old version (2.x), I've just installed 3.51.06


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


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

Help and advice needed with mysqldump

[Ian Blackford]

Hi

I need to get some data out of a table hosted for me on a webserver.

I'm thinking of using the command mysqldump to grab the data, though I'm
confused as to how to structure the statement.

Also I'd like to automate the process on a Windows PC, how would I do that?

(My perfect solution would be to call only a few records out the table on
the hosted db and insert them into a table on my local machine - I haven't
got a clue how to do that also.)

TIA

Best Regards

ICB

-- 
. Ian Blackford

. Tel: 01746 718000
. Mobile:  07970 250168
. web: http://www.designconscious.com/


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

Re: ODBC Error

[Egor Egorov]

"Fortuno, Adam" <[EMAIL PROTECTED]> wrote:
> Yo gang! Having a problem with the MySQL ODBC 3.51 driver.
> 
> I've got a MySQL volume which has a user table (tbl_usr). I'm linking this
> table to an Access database on my workstation (via ODBC link). The account
> being used for the connection has select, update, and insert rights to all
> tables in the database.
> 
> I'm populating the linked (MySQL) table with data I've got on my workstation
> in an MS Access database. To do this, I'm simply executing an "insert into
> ... select ... from" statement.
> 
> INSERT INTO tbl_usr (fst_nm, lst_nm, tle_nm, email_addr)
>   SELECT tbl_stage.First, tbl_stage.Last, tbl_stage.Title,
> tbl_stage.Email
>  FROM tbl_stage;
> 
> However, each time I execute the statement I receive an error (see attached
> file - mysql_odbc_err.pdf). The error message provided isn't very helpful.
> Can anyone elaborate?

Could you show error message as a plain text?



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




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

Re: how to export data from multiple tables

[Nitin]

union works fine but with union 'into outfile' doesn't work

anyway mysql ver is 4.0.14

thanx for ur time anyway
Nitin

- Original Message - 
From: "Victoria Reznichenko" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 4:12 PM
Subject: Re: how to export data from multiple tables


> "Nitin" <[EMAIL PROTECTED]> wrote:
> >
> > I've got a small problem (hope it's very small). Could any of you
suggest me how to select my data into a simple text file from multiple
tables (selected with union)!
> >
> > I've tried:
> >
> > select field-list into outfile filename from table1
> > union
> > select field-list into outfile filename from table2
> > union
> > select field-list into outfile filename from table3
> >
> > It says check your version manualblah blah
> >
>
> What is the version of MySQL server? UNION is supported from 4.0.
>
>
> -- 
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
><___/   www.mysql.com
>
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



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

Re: how to export data from multiple tables

[Victoria Reznichenko]

"Nitin" <[EMAIL PROTECTED]> wrote:
> 
> I've got a small problem (hope it's very small). Could any of you suggest me how to 
> select my data into a simple text file from multiple tables (selected with union)!
> 
> I've tried:
> 
> select field-list into outfile filename from table1
> union
> select field-list into outfile filename from table2
> union
> select field-list into outfile filename from table3
> 
> It says check your version manualblah blah
> 

What is the version of MySQL server? UNION is supported from 4.0.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





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

how to export data from multiple tables

[Nitin]

Hi all,

I've got a small problem (hope it's very small). Could any of you suggest me how to 
select my data into a simple text file from multiple tables (selected with union)!

I've tried:

select field-list into outfile filename from table1
union
select field-list into outfile filename from table2
union
select field-list into outfile filename from table3

It says check your version manualblah blah

Any idea, how to do it?

Thanx in advance
Nitin

Re: printing reports

[Wang Feng]

> In MySQL, how can I create and print reports without the help of neither a
> scripting language(e.g. php) nor a thrid party software tool?

bad grammar :-(  but you guys understand what i'm saying, right?  :-)


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

Re: Compression: Security or Zipping?

[Danny Haworth]

Hi,

I think this is more of a "Security by Obscurity" approach. E.g.
compressed credit card details flying down the wire are less obvious
than their plaintext equivalent.

I guess there may also be a case of increased difficulty when trying to
decompress a single part of captured traffic, like you would get when
trying to decompress a 10k part of a large zip file.

hth

danny

On Tue, 2003-10-07 at 10:49, Director General: NEFACOMP wrote:
> Hi group,
> 
> I have read in the MySQL manual that the client/Server Compression protocol adds 
> some security to the application.
> 
> Does anyone have more information on this?
> 
> 
> 
> Thanks,
> __
> NZEYIMANA Emery Fabrice


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

printing reports

[Wang Feng]

Greetings,

In MySQL, how can I create and print reports without the help of neither a
scripting language(e.g. php) nor a thrid party software tool?

I just couldn't find any commands for the printing function in MySQL.

Please advise. And please let me know how you print reports.



cheers,

feng




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

SV: Table crashes

[Datatal AB - Gauffin, Jonas]

Hello

> crashes occurs for some reasons like :
> - unclean tables ( tables are closed improprely),
> - hardware problems
> - something goes wrong in the system
> - know bugs
> Check that your tables are clean, before start using them.

MyODBC 3.51 is the only way that I access the database. (MyODBC is used in a 
multithreaded server and in InternetInformationServer (asp))
The server is coded in c++ and got a class that acts like a odbc pool (keeps 
connections open during the lifetime of the server and handles them to threads that 
requests a db connection)

How are tables getting unclean? When are tables closed?
Should I disconnect/connect when a thread is handling the dbconnection back to the 
pool?


> It seems that you said the REPAIR doesn't occurs after you 
> configuring related variables in the my.cnf file ? Because 
> the daemon will not start when the my.cnf contains any errors 
> (variable names),  the format is to be correct.

No. I asked if my configuration were correct. I've just added it to my.cnf.



> Can you indicates what MySQL version you run and your OS version.

Win2000
Mysql 4.0.12 (mysqld.exe)
MyODBC was an old version (2.x), I've just installed 3.51.06


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

RE: Compression: Security or Zipping?

[Greg_Cope]

> Hi group,
> 
> I have read in the MySQL manual that the client/Server 
> Compression protocol adds some security to the application.
> 
> Does anyone have more information on this?
> 

It adds security by compressing the network trafic, which is more security
by obscurity, as this might stop a casual observer sniffing network trafic,
but a dedicated person would just sniff and then uncompress.

Think of the similarity between a plain file, and a compressed one - it
offers a similar level of protection.  If you know how to recreate all the
fragments and uncompress it, it offers little protection.

If client server coms security is an issue, either use a VPN, ssh tunnel, or
look at the mysql SSL client-server features.

The above could be completely wrong..

Greg

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

Compression: Security or Zipping?

[Director General: NEFACOMP]

Hi group,

I have read in the MySQL manual that the client/Server Compression protocol adds some 
security to the application.

Does anyone have more information on this?



Thanks,
__
NZEYIMANA Emery Fabrice
NEFA Computing Services, Inc.
P.O. Box 5078 Kigali
Office Phone: +250-51 11 06
Office Fax: +250-50 15 19
Mobile: +250-08517768
Email: [EMAIL PROTECTED]
http://www.nefacomp.net/

DATE / DATETIME in IF(): BUG???

[Rudy Metzger]

Dear all,
 
anyone knows why the following is going wrong (I expect 1 in the columns Total
and Open)? I suppose it is because MySQL is internally casting the IF
expression (which is DATE vs DATE or DATE vs DATETIME) internally into a
STRING before comparison, but not sure about that 
 
(String) '2003-10-06' >= (String) '2003-10-06'  ==> true
(String) '2003-10-06' >= (String) '2003-10-06 00:00:00'  ===> false
 
If the IF statement really casts the two dates (DATE, DATETIME) into a STRING,
i think this is a bug. And a pretty nasty one.
 
NOTE: You have to run the SQL statement on the next day of the entered date in
the table. So like the date is now you have to run the script on 2003/10/07
(octobre 7, 2003).
 
Again to point out the problem: Apparently it is a difference to substract 24
HOURS or 1 DAY from a DATE (not DATETIME) and then using this result in an IF
clause (CASE too??, ifnull()???, any other expression?).
 
Thanx for any feedback!
 
Cheers
/rudy
 
-
 
HERE my Table definition:
 
CREATE TABLE `tpr` (
  `tprnr` int(10) unsigned NOT NULL auto_increment,
  `projectid` int(10) unsigned NOT NULL default '0',
  `severityid` char(3) NOT NULL default 'L',
  `statusid` char(3) NOT NULL default 'ENT',
  `entered` date NOT NULL default '-00-00',
  `entered_by` varchar(20) NOT NULL default '',
  `assigned` date default NULL,
  `assigned_to` varchar(20) default NULL,
  `fixed` date default NULL,
  `released` date default NULL,
  `released_by` varchar(20) default NULL,
  `tested` date default NULL,
  `tested_by` varchar(20) default NULL,
  `finished` date default NULL,
  `closed` date default NULL,
  `quote` int(10) unsigned default NULL,
  `quoted` date default NULL,
  `quoted_by` varchar(20) default NULL,
  `screenid` int(10) unsigned default NULL,
  `screenname` varchar(80) default NULL,
  `subject` varchar(80) default NULL,
  `description` text,
  `solution` text,
  `testreport` text,
  `is_parked` char(1) NOT NULL default 'N',
  `creusr` varchar(20) default NULL,
  `credat` date default NULL,
  `modusr` varchar(20) default NULL,
  `moddat` date default NULL,
  PRIMARY KEY  (`tprnr`),
  KEY `idx1` (`projectid`)
) TYPE=MyISAM
 
 
mysql> select * from tpr where tprnr = 368\G
*** 1. row ***
      tprnr: 368
  projectid: 1
 severityid: H
   statusid: ENT
    entered: 2003-10-06
 entered_by: rmetzger
   assigned: NULL
assigned_to: NULL
      fixed: NULL
   released: NULL
released_by: NULL
     tested: NULL
  tested_by: NULL
   finished: NULL
     closed: NULL
  quote: NULL
     quoted: NULL
  quoted_by: NULL
   screenid: NULL
 screenname: NULL
    subject: finish invoicing module
description: - create summary reports (total invoice amount per month,
 
   solution: NULL
 testreport: NULL
  is_parked: N
 creusr: rmetzger
 credat: 2003-10-06
 modusr: rmetzger
 moddat: 2003-10-06
1 row in set (0.00 sec)
 
-
The statement which is going WRONG:
 
SELECT s.statusid, s.status, count(tpr.tprnr),entered,
  sum( if ( ( CASE WHEN s.statusid = 'ENT' THEN tpr.entered
   WHEN s.statusid = 'ASS' THEN tpr.assigned
   WHEN s.statusid = 'FIX' THEN tpr.fixed
   WHEN s.statusid = 'REL' THEN tpr.released
   WHEN s.statusid = 'TOK' THEN tpr.tested
   WHEN s.statusid = 'TNO' THEN tpr.tested
   WHEN s.statusid = 'FIN' THEN tpr.finished
   WHEN s.statusid = 'CLS' THEN tpr.closed
   ELSE '1990-01-01' /*IW,IT*/
  END ) >= date_sub( curdate(), INTERVAL 24 HOUR ),
 1, 0 ) )
    FROM status s
    LEFT JOIN tpr ON tpr.statusid = s.statusid
 AND tpr.projectid = 1
 AND tpr.is_parked = 'N'
 AND tpr.tprnr = 368
   GROUP BY s.sort_idx, s.statusid, s.status
   ORDER BY s.sort_idx
 
-
The statement which is WORKING:
 
SELECT s.statusid, s.status, count(tpr.tprnr),entered,
  sum( if ( ( CASE WHEN s.statusid = 'ENT' THEN tpr.entered
   WHEN s.statusid = 'ASS' THEN tpr.assigned
   WHEN s.statusid = 'FIX' THEN tpr.fixed
   WHEN s.statusid = 'REL' THEN tpr.released
   WHEN s.statusid = 'TOK' THEN tpr.tested
   WHEN s.statusid = 'TNO' THEN tpr.tested
   WHEN s.statusid = 'FIN' THEN tpr.finished
   WHEN s.statusid = 'CLS' THEN tpr.closed
   ELSE '19

Re: error messages in different languages

[Egor Egorov]

"Director General: NEFACOMP" <[EMAIL PROTECTED]> wrote:
> 
> I have two questions about error messages in MySQL.
> 
> 1. Section 4.6.2 talks about Non-English error messages. It says that for 
> recompiling the errmsg.sys file, one will issue a comp_err command at the SHELL 
> prompt.
> Does such a utility exist for Windows? I mean can someone tell me when to find the 
> 'comp_err.exe' in the MySQL Windows distribution?

Yup, there is a comp-err.exe in the bin directory.

> 2. One can change the error message file used by MySQL to instruct it to to send 
> error messages in a given language. My question is that my application will be used 
> by both English and French speakers. So, you understand that I don't need to use 
> this or that error file; I need to use both at the same time. Is there anyway to 
> instruct MySQL to use both error files and send messages based on client request? So 
> that whenever the connected client will get personalized error messages as MySQL 
> accepts different character SETs to be used based on Client request?

You can't.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




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

FW: [Fwd: MySQL w/dual-master replication?]

[Andrew Braithwaite]

Is the system read-heavy or write-heavy?

Cheers,

Andrew

-Original Message-
From: Don MacAskill [mailto:[EMAIL PROTECTED] 
Sent: Monday 06 October 2003 20:47
To: [EMAIL PROTECTED]
Subject: [Fwd: MySQL w/dual-master replication?]



Hey all,

I sent this a few days ago, but it may have gotten lost in the weekend 
for many of you.  Don't worry, I won't keep re-posting it.  :)

I'm hoping someone out there is doing some sort of high-availability 
replication and can give me a few pointers.

Thanks in advance!

Don


 Original Message 
Subject: MySQL w/dual-master replication?
Date: Sat, 04 Oct 2003 11:07:43 -0700
From: Don MacAskill <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]


Hi there,

I realize it's not supported, but nonetheless, I need to investigate how
possible it is.  The primary goal is high-availability, not performance
scaling.

It sounds like if I'm careful, it might work out ok.  Careful means:

- No auto_increment columns... handle unique key assignment in my app

- Update/insert/delete on primary master only except in case of failure

- Prevent possibly 'flap' by automatically using secondary master
exclusively after primary master failure.  Bring up primary master manually
and manually tell the app when to start using it again after it's allowed to
catch back up.

Are there any other gotchas I need to worry about?  Is anyone actually doing
this in a production environment?  I'd love to hear from you.


Another option is to use master/slave, and have a monitoring app change the
slave's status to master after the master has died.  There's info about this
in the FAQ (http://www.mysql.com/doc/en/Replication_FAQ.html), but I'm
afraid the documentation on the sequence of events is pretty vague.  Does
anyone have any insight as to exactly how it works?

In particular, one part of the doc says to use 'STOP SLAVE; RESET MASTER;
CHANGE MASTER TO' and another part of the doc says 'STOP SLAVE;
  CHANGE MASTER TO' ...  which is appropriate?   Certainly, I understand
why 'STOP SLAVE', but why 'RESET MASTER'?   Would the sequence of events
differ if we're just dealing with 1 master/1 slave instead of 1
master/multiple slaves?

Once the old master joins back up, I can let it be a slave for awhile to
catch back up, then swap back, correct?

Thanks in advance!

Don MacAskill
http://onethumb.smugmug.com/





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

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

RE: Help With a DATETIME Query

[Andrew Braithwaite]

Hi,

I would do this in the application layer something like this:

$list_of_days_you_want_to_look_at = ('mon','tue','wed'..); Foreach ($day
in $list_of_days_you_want_to_look_at) {
use a similar query to below but geared to only look at $day instead; }

You should get an output like this:

Mon: 0 hrs
Tue: 3.5 hrs
Wed: .

Hope this helps,

Cheers,

Andrew


-Original Message-
From: shaun thornburgh [mailto:[EMAIL PROTECTED] 
Sent: Monday 06 October 2003 20:29
To: [EMAIL PROTECTED]
Subject: Help With a DATETIME Query


Hi,

I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they have unbooked on a particular day (i.e. 8.5 hours less the time
of any bookings on that day). However, when a booking spans more than one
day the query doesn't work, for example if a user has a booking that starts
on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5
hours for both days. Any help here would be greatly appreciated.

SELECT
8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
 DATE_FORMAT(B.Booking_End_Date, '%i')) -
   ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) +
 DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS 
Available_Hours
FROM WMS_Bookings B
WHERE B.User_ID = '16'
AND B.Booking_Status <> '1'
AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR
'2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )

Thanks for your help

_
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile


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

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

RE: RAID, miiror OR replication?

[Andrew Braithwaite]

Hi,

Having implemented all the solutions you suggest, I would need more
information to answer this problem.

1. What is the acceptable uptime of the system?  95%, 99%, 99.9%, 99.99% ?

2. In the event of a failure, what is the acceptable recovery time?  None,
20 mins, 1 hr, 5 hrs, 1 day ?

3. What hardware is running the DB now?

4. How many queries per second is the system running?  Is it read heavy or
write heavy?  (and what about the future)

5. What is the hardware budget?  Just the existing hardware, $1000, $5000
etc..

6. How much time can you afford to spend on it?

With this info, I could help to suggest a solution... But without it, You
may receive ideas for solutions that are overkill or underkill for your
needs.

Hope this helps,

Andrew

-Original Message-
From: Richard Reina [mailto:[EMAIL PROTECTED] 
Sent: Monday 06 October 2003 20:36
To: [EMAIL PROTECTED]
Subject: RAID, miiror OR replication?


I am wanting to protect myself against future potential hard drive 
failures on my database server running version 3.23.49a.  Should I try 
and set up a RAID, a mirror or would the best solution be to set up 
MySQL replication.  Any suggestions would be greatly appreciated.

Richard


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

RE: Help With a DATETIME Query

[Andrew Braithwaite]

Hi,

I would do this in the application layer something like this:

$list_of_days_you_want_to_look_at = ('mon','tue','wed'..);
Foreach ($day in $list_of_days_you_want_to_look_at) {
use a similar query to below but geared to only look at $day instead;
}

You should get an output like this:

Mon: 0 hrs
Tue: 3.5 hrs
Wed: .

Hope this helps,

Cheers,

Andrew


-Original Message-
From: shaun thornburgh [mailto:[EMAIL PROTECTED] 
Sent: Monday 06 October 2003 20:29
To: [EMAIL PROTECTED]
Subject: Help With a DATETIME Query


Hi,

I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they have unbooked on a particular day (i.e. 8.5 hours less the time
of any bookings on that day). However, when a booking spans more than one
day the query doesn't work, for example if a user has a booking that starts
on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5
hours for both days. Any help here would be greatly appreciated.

SELECT
8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) +
 DATE_FORMAT(B.Booking_End_Date, '%i')) -
   ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) +
 DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS 
Available_Hours
FROM WMS_Bookings B
WHERE B.User_ID = '16'
AND B.Booking_Status <> '1'
AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR
'2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )

Thanks for your help

_
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile


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

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

RE: [Fwd: MySQL w/dual-master replication?]

[Andrew Braithwaite]

Is the system read-heavy or write-heavy?

Cheers,

Andrew

-Original Message-
From: Don MacAskill [mailto:[EMAIL PROTECTED] 
Sent: Monday 06 October 2003 20:47
To: [EMAIL PROTECTED]
Subject: [Fwd: MySQL w/dual-master replication?]



Hey all,

I sent this a few days ago, but it may have gotten lost in the weekend 
for many of you.  Don't worry, I won't keep re-posting it.  :)

I'm hoping someone out there is doing some sort of high-availability 
replication and can give me a few pointers.

Thanks in advance!

Don


 Original Message 
Subject: MySQL w/dual-master replication?
Date: Sat, 04 Oct 2003 11:07:43 -0700
From: Don MacAskill <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]


Hi there,

I realize it's not supported, but nonetheless, I need to investigate how
possible it is.  The primary goal is high-availability, not performance
scaling.

It sounds like if I'm careful, it might work out ok.  Careful means:

- No auto_increment columns... handle unique key assignment in my app

- Update/insert/delete on primary master only except in case of failure

- Prevent possibly 'flap' by automatically using secondary master
exclusively after primary master failure.  Bring up primary master manually
and manually tell the app when to start using it again after it's allowed to
catch back up.

Are there any other gotchas I need to worry about?  Is anyone actually doing
this in a production environment?  I'd love to hear from you.


Another option is to use master/slave, and have a monitoring app change the
slave's status to master after the master has died.  There's info about this
in the FAQ (http://www.mysql.com/doc/en/Replication_FAQ.html), but I'm
afraid the documentation on the sequence of events is pretty vague.  Does
anyone have any insight as to exactly how it works?

In particular, one part of the doc says to use 'STOP SLAVE; RESET MASTER;
CHANGE MASTER TO' and another part of the doc says 'STOP SLAVE;
  CHANGE MASTER TO' ...  which is appropriate?   Certainly, I understand
why 'STOP SLAVE', but why 'RESET MASTER'?   Would the sequence of events
differ if we're just dealing with 1 master/1 slave instead of 1
master/multiple slaves?

Once the old master joins back up, I can let it be a slave for awhile to
catch back up, then swap back, correct?

Thanks in advance!

Don MacAskill
http://onethumb.smugmug.com/





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

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

RE: RAID, miiror OR replication?

[Andrew Braithwaite]

Hi,

Having implemented all the solutions you suggest, I would need more
information to answer this problem.

1. What is the acceptable uptime of the system?  95%, 99%, 99.9%, 99.99% ?

2. In the event of a failure, what is the acceptable recovery time?  None,
20 mins, 1 hr, 5 hrs, 1 day ?

3. What hardware is running the DB now?

4. How many queries per second is the system running?  Is it read heavy or
write heavy?  (and what about the future)

5. What is the hardware budget?  Just the existing hardware, $1000, $5000
etc..

6. How much time can you afford to spend on it?

With this info, I could help to suggest a solution... But without it, You
may receive ideas for solutions that are overkill or underkill for your
needs.

Hope this helps,

Andrew

-Original Message-
From: Richard Reina [mailto:[EMAIL PROTECTED] 
Sent: Monday 06 October 2003 20:36
To: [EMAIL PROTECTED]
Subject: RAID, miiror OR replication?


I am wanting to protect myself against future potential hard drive 
failures on my database server running version 3.23.49a.  Should I try 
and set up a RAID, a mirror or would the best solution be to set up 
MySQL replication.  Any suggestions would be greatly appreciated.

Richard


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

What is 4.0.15-max-debug

[Arthur Maloney]

Dear All

I have recently reformatted my hard disk and done 
a clean install of 4.0.15 on Win 2k Pro SP4.

I used C:\> C:\mysql\bin\ mysqld --install

This created a mySQL service and it works ok.
I happened look at a log in the data folder
and noticed

031007  9:37:53  InnoDB: Started
MySql: ready for connections.
Version: '4.0.15-max-debug'  socket: ''  port: 3306


Since I asked for the standard server mysqld
What is max-debug and where did it come from?

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

Re: Table crashes

[Thierno Cissé]

Hi, Jonas

crashes occurs for some reasons like :
- unclean tables ( tables are closed improprely),
- hardware problems
- something goes wrong in the system
- know bugs
Check that your tables are clean, before start using them.

It seems that you said the REPAIR doesn't occurs after you configuring
related variables in the my.cnf file ?
Because the daemon will not start when the my.cnf contains any errors
(variable names),  the format is to be correct.

Can you indicates what MySQL version you run and your OS version.

Regards
Thierno6C - MySQL 4.0.15


- Original Message - 
From: "Datatal AB - Gauffin, Jonas" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 07, 2003 7:05 AM
Subject: Table crashes


Hello

Some one my tables have crashed (again). I had to repair them ASAP since
the db is in my production enviroment.
1. How can I find the cause of the crashes?

2. I've created a my.cnf and added the following lines. Is the format
correct?
[mysqld]
set-variable = myisam-recover=BACKUP,FORCE
set-variable = myisam_sort_buffer_size=4M


I need to get this going without getting crashes every week, please
help.

/Jonas

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


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

Table crashes

[Datatal AB - Gauffin, Jonas]

Hello

Some one my tables have crashed (again). I had to repair them ASAP since
the db is in my production enviroment.
1. How can I find the cause of the crashes?

2. I've created a my.cnf and added the following lines. Is the format
correct?
[mysqld]
set-variable = myisam-recover=BACKUP,FORCE
set-variable = myisam_sort_buffer_size=4M


I need to get this going without getting crashes every week, please
help.

/Jonas

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