Re: DATATYPES
Suggest you read the online manual... - Original Message - *From:* Krishna Chandra Prajapati [EMAIL PROTECTED] *To:* mysql mysql@lists.mysql.com *Date:* Tue, 9 Sep 2008 17:54:46 +0530 Hi, I would like to know the difference between char, varchar and text. char limit 255 character fixed length varchar limit 65,000 character variable length text limit 65,000 character variable length. -- Krishna Chandra Prajapati --- avast! Antivirus: Inbound message clean. Virus Database (VPS): 080908-0, 08/09/2008 Tested on: 09/09/2008 13:40:08 avast! - copyright (c) 1988-2008 ALWIL Software. http://www.avast.com Terry http://www.confexdb.co.uk/ --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 080908-0, 08/09/2008 Tested on: 09/09/2008 13:44:05 avast! - copyright (c) 1988-2008 ALWIL Software. http://www.avast.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATATYPES
Have you checked the manual to be sure that the 65000 varchar is available to the version of MySQL you are using? Prior to 5.0.3, it was 255 only. - Original Message - *From:* Krishna Chandra Prajapati [EMAIL PROTECTED] *To:* mysql mysql@lists.mysql.com *Date:* Tue, 9 Sep 2008 17:54:46 +0530 Hi, I would like to know the difference between char, varchar and text. char limit 255 character fixed length varchar limit 65,000 character variable length text limit 65,000 character variable length. -- Krishna Chandra Prajapati --- avast! Antivirus: Inbound message clean. Virus Database (VPS): 080908-0, 08/09/2008 Tested on: 09/09/2008 13:40:08 avast! - copyright (c) 1988-2008 ALWIL Software. http://www.avast.com Terry http://www.confexdb.co.uk/ --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 080908-0, 08/09/2008 Tested on: 09/09/2008 14:58:55 avast! - copyright (c) 1988-2008 ALWIL Software. http://www.avast.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting just 'N' first rows
Look up the LIMIT clause of SELECT statement, also ASCENDING/DESCENDING - depending on how you want it. Remember LIMIT can take a number and an offset. Terry - Original Message - *From:* Renito 73 [EMAIL PROTECTED] *To:* mysql@lists.mysql.com *Date:* Sun, 9 Sep 2007 10:05:52 -0500 Hello How can I send a query that retrieves only the first 'N' rows that match a condition? As far as I know you must call mysql_fetch_row() until the last row has been processed or the resources allocated won't be free. Am creating a program in PHP that should retrieve only 'N' records each time a query is sent, so I I'm thinking on using mysql_free_result(), but, is it safe to free the results even if there are more records remaining that match the query conditions? I need to know how secure could be to read only the first records and free the resources, or if there is another way to do the same thing. Thanks, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG. Version: 7.5.485 / Virus Database: 269.13.10/995 - Release Date: 08/09/2007 13:24 Terry http://booksihaveread.awardspace.co.uk -- No virus found in this outgoing message. Checked by AVG. Version: 7.5.485 / Virus Database: 269.13.10/995 - Release Date: 08/09/2007 13:24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: seoparator help
Learn something every day. TFT - Original Message - *From:* Andrew Braithwaite [EMAIL PROTECTED] *To:* coolcoder [EMAIL PROTECTED], mysql@lists.mysql.com *Date:* Thu, 23 Aug 2007 14:19:25 +0100 mysql select format(300,0); +---+ | format(300,0) | +---+ | 3,000,000 | +---+ 1 row in set (0.00 sec) mysql select format(300,2); +---+ | format(300,2) | +---+ | 3,000,000.00 | +---+ 1 row in set (0.00 sec) Cheers, Andrew -Original Message- From: coolcoder [mailto:[EMAIL PROTECTED] Sent: Thu, 23 August 2007 11:55 To: mysql@lists.mysql.com Subject: seoparator help Was wondering if anyone could help me with this little problem I'm having. I'd like to have a comma separator after every 3 digits. E.g 3,000,000. How would i go about this? This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG. Version: 7.5.484 / Virus Database: 269.12.2/967 - Release Date: 22/08/2007 18:51 Terry http://booksihaveread.awardspace.co.uk -- No virus found in this outgoing message. Checked by AVG. Version: 7.5.484 / Virus Database: 269.12.4/969 - Release Date: 23/08/2007 16:04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: seoparator help
Not really a MySQL problem, this is a presentation problem. MySQL will store the number as digits only (unless you are storing in a character field - but why would you?). If using PHP, for instance, the output of the field would be number_format($fieldvalue) or if you want the answer to two decimal places number_format($fieldvalue, 2) Regards Terry http://booksihaveread.awardspace.co.uk - Original Message - *From:* coolcoder [EMAIL PROTECTED] *To:* mysql@lists.mysql.com *Date:* Thu, 23 Aug 2007 03:55:27 -0700 (PDT) Was wondering if anyone could help me with this little problem I'm having. I'd like to have a comma separator after every 3 digits. E.g 3,000,000. How would i go about this? www.coderewind.com Best Place to hunt for Code -- View this message in context: http://www.nabble.com/seoparator-help-tf4316769.html#a12291343 Sent from the MySQL - General mailing list archive at Nabble.com. -- No virus found in this outgoing message. Checked by AVG. Version: 7.5.484 / Virus Database: 269.12.2/967 - Release Date: 22/08/2007 18:51 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch - easy check
- Original Message -
*From:* Ryan Stille [EMAIL PROTECTED]
*To:* mysql@lists.mysql.com
*Date:* Sat, 24 Feb 2007 15:28:25 -0600
Ryan Stille wrote:
Paul DuBois wrote:
At 4:40 PM -0600 2/20/07, Ryan Stille wrote:
Is there an easy way to test to see if MySQL already has the proper
tables loaded?
-Ryan
Yes, reload them. :-) After that, they're current! ...
After digging around on the net for a while I found an easy way to tell
if your MySQL installation is ready for the new daylight savings time.
SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),
UNIX_TIMESTAMP('2007-03-11 03:00:00');
This should return the same value, even though you are feeding it
different times, because this is when the 1 hr change occurs. I get
the correct result on both of my machines. On one of them I've run the
suggested |mysql_tzinfo_to_sql command, on the other, the time zone
tables are completely empty!
Any wisdom on these time zone tables - are they ever used, should I
populate them or not?
-Ryan
This may depend on where you live? I tried your select above and got
two different answers. Trying:
SELECT UNIX_TIMESTAMP('2007-03-25 01:00:00'),
UNIX_TIMESTAMP('2007-03-25 02:00:00');
which is when BST sets in in the UK (where I am), gave me identical
answers.
My 2 cents-worth
Terry
www.confexdb.co.uk
|
--
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: working on Microsoft® Windows Server™ 2003
- Original Message - hi, can new mysql work on Microsoft® Windows Server 2003 regards prao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Yes Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row Count Discrepency
- Original Message - I have an InnoDB table in a MySQL 4.1.14 database. Can anyone suggest why MySQL Adminstrator says the table has 497 rows, while doing a query or a count on the same table shows that it only has 434? IIRC, InnoDB only gives an estimated row count in admin (or SQLyog or whatever), not an actual count, because of the way it does (or doesn't) store row information. MyISAM tables, on the other hand, show accurately because they store the rowcount as part of the table data. Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem
We are running 4.1.13 standard on Linux as a master, and 4.1.14 on NT4
as a slave.
Replication seems to work extremely well, except in the following
circumstance.
On the master server, running a query similar to:
insert into zmast.leagueinfo
(countieslist, defaultleaguecode, leaguename ...)
select
countieslist, 'DDLS2005', leaguename ..
from zmast.leagueinfo
where defaultleaguecode='ddls2004'.
using the same table as source and target, with the only changes being
the defaultleaguecode and the autoincrement ID field, without any problem.
However, this does not seem to replicate to the slave, and no error shows
until later when an attempt is made to insert a record into a table where
a relationship to this record is required ('Cannot update child
record'-type message).
The manual that I have does specify that currently you cannot insert into
a table and select from the same table in a subquery. Clearly this is not
actually the case, but for some reason, it will not replicate.
Anyone else had/got this problem?
Is there a sensible solution - the only one I can come up with is changing
the intitial 'insert ... select' into a 'create table temp select
from...'/'insert ... select from temp'/ 'drop table temp' set.
This insert ... select stuff is not done on a regular basis, and then only
by an administrator.
Ideas welcome
Cheers
Terry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
- Original Message -
However, this does not seem to replicate to the slave, and no error
shows
Have you compared the slave's relay logs and master's binary logs? Does
this query present in both logs?
The hint was what I needed, Gleb. Spasibo. It turns out that the
administrator doing this is using an elderly version of SQLyog to run his
scripts, and either by accident or design, SQLyog doesn't make entries in
the master bin-log - so no replication is ever going to happen. The query
(actually a set of queries) don't appear in logs on either server.
This GUI has been in use for over a year, but we only switched on the
replication system a couple of weeks ago, and this is the first
'occasional' update - that was frequently used on the old master-only
system without problems.
Thanks again for the hint!
Terry
See:
http://dev.mysql.com/doc/mysql/en/slave-logs.html
http://dev.mysql.com/doc/mysql/en/Binary_log.html
http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html
Terry Riley [EMAIL PROTECTED] wrote:
We are running 4.1.13 standard on Linux as a master, and 4.1.14 on
NT4 as a slave.
Replication seems to work extremely well, except in the following
circumstance.
On the master server, running a query similar to:
insert into zmast.leagueinfo
(countieslist, defaultleaguecode, leaguename ...)
select
countieslist, 'DDLS2005', leaguename ..
from zmast.leagueinfo
where defaultleaguecode='ddls2004'.
using the same table as source and target, with the only changes
being the defaultleaguecode and the autoincrement ID field, without
any problem.
However, this does not seem to replicate to the slave, and no error
shows until later when an attempt is made to insert a record into a
table where a relationship to this record is required ('Cannot update
child record'-type message).
The manual that I have does specify that currently you cannot insert
into a table and select from the same table in a subquery. Clearly
this is not actually the case, but for some reason, it will not
replicate.
Anyone else had/got this problem?
Is there a sensible solution - the only one I can come up with is
changing the intitial 'insert ... select' into a 'create table temp
select from...'/'insert ... select from temp'/ 'drop table temp' set.
This insert ... select stuff is not done on a regular basis, and then
only by an administrator.
Ideas welcome
Cheers
Terry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Flushing logs on replication setup
- Original Message - Terry Riley wrote: I'm running 4.1.13 on Linux as master, with 4.1.12 on XP as slave (in house test setup). This vaguely duplicates what is set up on the live servers, except that the slave is 4.1.3 on NT4. On the test setup, if the logs are flushed on the master, the bin log is rotated to the next number, after closing (and preserving) the old one. On the slave, the change to the master bin-log is duly recognised in the slave status output. If I issue 'flush logs' on the slave, however, the old [servername]-relay-bin.nn file is closed, the n+1 is opened, and the original is deleted completely. My only reason for wishing to rotate logs this way is to prevent files getting too large to handle effectively in the event of needing to reinstate (either the master or the slave). However, the way that log flush on the slave seems to work implies that a backup of it should be taken before flushing, or you won't ever see that logged data again. Is this the way it is meant to be? I don't want to institute any log rotation policy on the slave of the live setup if this happens. Regards Terry Riley see http://dev.mysql.com/doc/mysql/en/slave-logs.html Basically, the master's binary log and the slave's relay log, though the same format, serve different purposes. The slave's relay log is a short-term copy of as much of the master's binary log as the slave's IO thread has read; this relay log is what the slave's SQL thread processes. This file is not needed once it is read since it does not store anything that is not stored in the master's binary log, and this file is not used for replication from the slave to another server. If your slave is handling updates, it should be writing a binary log so you can replicate those updates back to the master. Best regards, Devananda vdv Thanks, Devananda! I really should have looked at the manual once again. I'm not sure what you meant by the last sentence - all the updates are done on the master, and they are naturally replicated to the slave. Did you mean 'handling DIRECT updates'? I turned on the binlog for the slave, then made some updates on the master, and nothing changed in the slave bin log, only the relay log. That is normal, yes? Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Flushing logs on replication setup
I'm running 4.1.13 on Linux as master, with 4.1.12 on XP as slave (in house test setup). This vaguely duplicates what is set up on the live servers, except that the slave is 4.1.3 on NT4. On the test setup, if the logs are flushed on the master, the bin log is rotated to the next number, after closing (and preserving) the old one. On the slave, the change to the master bin-log is duly recognised in the slave status output. If I issue 'flush logs' on the slave, however, the old [servername]-relay-bin.nn file is closed, the n+1 is opened, and the original is deleted completely. My only reason for wishing to rotate logs this way is to prevent files getting too large to handle effectively in the event of needing to reinstate (either the master or the slave). However, the way that log flush on the slave seems to work implies that a backup of it should be taken before flushing, or you won't ever see that logged data again. Is this the way it is meant to be? I don't want to institute any log rotation policy on the slave of the live setup if this happens. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slightly Off Topic - MySQL Administrator
As a newbie on Linux (FC3), I have (evidently) done something stupid and
lost part of the Administrator application.
When first installed, it was fine. I then treid to change the path on the
restore page, assuming that it was to point to where backups would be
stored. Whatever was entered there (I don't honestly remember what it was)
has resulted in the following: whenever the application is up and running,
if I click on 'Restore' to go to that page, the whole app just disappears.
I've used what I believe to be the normal method of uninstalling ('rpm
-e') and the 'rpm -V' thereafter reports the pacjkage as not installed. If
I then reinstall, I still get the same problem of a disappearing
MySQLAdministrator when I click for Restore.
Eveidently there's a config file somewhere with that (obviously incorrect)
path in it, but it's not being destroyed by the 'rpm -e'.
Does anyone out there happen to know what that is? I've looked at the
archives for the MySQL-GUI and find no reference to this...
Cheers
Terry Riley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lost connection DURING query?
Luke - Original Message - This error message seems a bit different than others I have gotten. it is from a ColdFusion server that uses an ODBC driver... ODBC Error Code = S1000 (General error) Which version of CF are you using? If it is MX6.1, surely you can use a native MySQL datasource connection, and dispense with ODBC? I know it doesn't help the immediate problem, but it would remove one less reliable link in the chain. Terry [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-log]Lost connection to MySQL server during query that during part threw me, but it may just be the way ODBC states the error? mysql error log shows no errors for the time(s) that this happened, other than a bunch of aborted connections - but that is because I set wait_timeout pretty low to avoid connection problems we were having with other clients. does anyone know if this is the error ODBC gets when trying to use a connection that has been killed by the MySQL server? if that is so, it may be that I just need to put the wait_timeout back up. any help would be great! thanks! -L Luke Crouch 918-461-5326 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Compressing after Deletion
David, According to the documentation, OPTIMIZE will also work on InnoDB tables. Will that produce the same result as your ALTER TABLE ? Cheers Terry - Original Message - Hi Chris, For MyISAM/BDB tables use OPTIMIZE TABLE your table name; For InnoDB tables try ALTER TABLE your table name TYPE=InnoDB; Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, 9 March 2005 9:19 AM To: mysql@lists.mysql.com Subject: Compressing after Deletion I have looked in the documentation and either I am not looking for the right thing or have simply overlooked it. But my question is this, I have a database with 35 Million records, and I need to delete about 25 million of those. After deletion I would think that I would need to compress, shrink, or otherwise optimize the database. How is that done? do I need to do it? What commands should I be looking up in the docs? Any help is greatly appreciated. Chris Hood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to MySQL on Linux
Thanks, Joshua - just the sort of info I needed. Off to find a more recent distro Cheers Terry - Original Message - On Friday 11 February 2005 09:15, Terry Riley said something like: Having inherited an elderly PIII/500MHz box with an 8Gb SCSI disk, that had an apparently unusable XP SP2 OS on it, I decided to wipe the disk and install my first Linux instead, using an ancient RedHat 7.3 distribution. First suggestion: get something recent: Suse 9.2, Mandrake 10.1, Fedora Core 3, the latest Debian. A distro that old will have major security (and probably usability issues). Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). I would doubt the current MySQL RPM's would support something as old as RH 7.3. If you install something recent, there will be recent versions of MySQL (Mandrake even has 5.0 in the contrib section, I would assume Fedora would too. You will have to intstall the server portion, and probably the client portion. You then can use the MySQL GUI tools to admin the box from a Windows machine. Using something like Mandrake or Fedora, their installer tools will resolve all the dependencies for you. Hope that gets you started a little. If you need more detail, feel free to ask. j- k- -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New to MySQL on Linux
Having inherited an elderly PIII/500MHz box with an 8Gb SCSI disk, that had an apparently unusable XP SP2 OS on it, I decided to wipe the disk and install my first Linux instead, using an ancient RedHat 7.3 distribution. Having done that successfully, and increased the memory from 256 to 768Mb, I think I'm now ready to install the latest MySQL on it. All my previous MySQL experience, unfortunatley, has been on WinNT, usually installed with the msi installer. Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). Suggestions, please? Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to MySQL on Linux
Thanks to all who replied - food for thought... Cheers Terry - Original Message - Having inherited an elderly PIII/500MHz box with an 8Gb SCSI disk, that had an apparently unusable XP SP2 OS on it, I decided to wipe the disk and install my first Linux instead, using an ancient RedHat 7.3 distribution. Having done that successfully, and increased the memory from 256 to 768Mb, I think I'm now ready to install the latest MySQL on it. All my previous MySQL experience, unfortunatley, has been on WinNT, usually installed with the msi installer. Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). Suggestions, please? Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excluding Rows
Something like:
select
fh1109.state,
fh1109.cd,
fh1109.party,
fh1109.representative,
ssa1202.total,
((total-children*percentunder18)/vapall)*100,
ssa1202.retired_workers,
ssa1202.disabled_workers,
ssa1202.widow,
ssa1202.wives_and_husbands,
ssa1202.children
from ssa1202, fh1109, vapall
where
fh1109.state = ssa1202.state and
fh1109.cd = ssa1202.cd and
fh1109.state = vapall.state and
fh1109.cd = vapall.cd and
ssa1202.state = vapall.state and
ssa1202.cd = vapall.cd
AND ssa1202.state NOT IN('TX','PA','ME')
Terry
- Original Message -
How do I exclude some rows in a table? I am merging columns from three
tables all of which show all congressional districts in all states. I
want to exclude those congressional districts in TX, PA and ME. My
coding that brings up data for all congressional districts is shown
below. Thanks.
Ken
**
select
fh1109.state,
fh1109.cd,
fh1109.party,
fh1109.representative,
ssa1202.total,
((total-children*percentunder18)/vapall)*100,
ssa1202.retired_workers,
ssa1202.disabled_workers,
ssa1202.widow,
ssa1202.wives_and_husbands,
ssa1202.children
from ssa1202, fh1109, vapall
where
fh1109.state = ssa1202.state and
fh1109.cd = ssa1202.cd and
fh1109.state = vapall.state and
fh1109.cd = vapall.cd and
ssa1202.state = vapall.state and
ssa1202.cd = vapall.cd
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Flush_time question and related item
Gleb - Original Message - Hello. Does that imply that if we set this flush_time value to zero (ie no periodic flush to disk), then some of the data will not be committed to disk, and if we had a subsequent power failure, then any data since the last flush would be lost? We have innodb_flush_log_at_trx_commit set to 1. MySQL will update the files on disk with system call after every SQL statement and before the client is notified about the result. (This is not true if you are running with --delay-key-write, in which case data files are written but not index files.) This means that data file contents are safe even if mysqld crashes, because the operating system will ensure that the unflushed data is written to disk. Thanks for that - we're not running delay-key-write, so I assume therefore that there is no harm in changing flush_time to zero. Any ideas on the second part of my question (which tables are counted)? Cheers Terry Terry Riley [EMAIL PROTECTED] wrote: We're running mostly with InnoDB tables, about 5% updates/inserts/deletes, the rest selects, on Windows NT. In setting table_cache to 256 from the default 64, we hoped to improve performance a little, by not having to continually close/open tables. Then we noticed that the opened table count dropped to zero and began to climb again every 30 minutes - a consequence, through later reading of the Fine Manual, of the flush_time setting of 30 minutes (1800 sec), which seems to be recommended for W9x and Me only. The docs state that this action 'closes tables to flush pending changes to disk' every flush_time seconds. The means (I think) that some (though I doubt all, given the size of some tables) tables could be completely in memory. Does that imply that if we set this flush_time value to zero (ie no periodic flush to disk), then some of the data will not be committed to disk, and if we had a subsequent power failure, then any data since the last flush would be lost? We have innodb_flush_log_at_trx_commit set to 1. Given the above, is it unwise to drop the periodic flush? The related item: The number of tables in all our databases, including mysql, is 130. What other tables are counted in the opened_tables calculation; does this include tables that may be opened twice under different aliases? Does this include temporary tables (created by MySQL)? Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Flush_time question and related item
We're running mostly with InnoDB tables, about 5% updates/inserts/deletes, the rest selects, on Windows NT. In setting table_cache to 256 from the default 64, we hoped to improve performance a little, by not having to continually close/open tables. Then we noticed that the opened table count dropped to zero and began to climb again every 30 minutes - a consequence, through later reading of the Fine Manual, of the flush_time setting of 30 minutes (1800 sec), which seems to be recommended for W9x and Me only. The docs state that this action 'closes tables to flush pending changes to disk' every flush_time seconds. The means (I think) that some (though I doubt all, given the size of some tables) tables could be completely in memory. Does that imply that if we set this flush_time value to zero (ie no periodic flush to disk), then some of the data will not be committed to disk, and if we had a subsequent power failure, then any data since the last flush would be lost? We have innodb_flush_log_at_trx_commit set to 1. Given the above, is it unwise to drop the periodic flush? The related item: The number of tables in all our databases, including mysql, is 130. What other tables are counted in the opened_tables calculation; does this include tables that may be opened twice under different aliases? Does this include temporary tables (created by MySQL)? Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking Issue?
Heikki, - Original Message - Terry, - Original Message - From: Terry Riley [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 06, 2004 8:15 PM Subject: Locking Issue? Can someone help, please? We set up a server to handle a coldfusion web application (CFMX 6.1) running against MySQL 4.1.3b-beta on WinNT. When it is a little stretched, we are finding many instances of queries listed as either 'Sending...' or 'Copying...' in the processlist, with the time going ever upwards (last check was at 1000 seconds and rising). All the tables in the database concerned are InnoDB, and none of the queries concerned are, as far as I know, involved in any transaction - they are straight selects (albeit complex ones, perhaps). When this happens, the other requests to the server are inevitably slow, and these seem never to be cleared unless I kill the threads - and I'm not 100% sure how much damage I'm doing in that action. Is this a possible locking issue? If so, how do I get around it. The settings for the server are at default, except where noted. This is the my.ini file: [mysqld] max_connections=1000 basedir=e:/mysql datadir=e:/mysql/data wait_timeout=60 # TR added next 6 lines on 27/07/04, after instal of v4.1.3b old-passwords local-infile query_cache_size=25M query_cache_type=1 set-variable=max_allowed_packet=16M set-variable=key_buffer=8M log-bin= log_slow_queries= [mysql] local-infile=1 Any pointers as to what I may be doing wrong? Please? Yes, I know we should upgrade to 4.1.7, and we will - soon. try tuning InnoDB. Your workload may be seriously disk-bound. I notice that are a lot of tmp_disk_tables created (about 25% of the total tmp_tables), and have been increasing the tmp_table_size (it now stands at 120Mb, with a total database of only 400Mb), with no difference shown in the number of disk_tables created. This is happening even on a light load. Also changed innodb_buffer_pool_size. From its default of 8M (which only shows as 512 in the MySQLAdmin status) to 16M (which showed as 1024) to 32Mb (which shows as 2048). The startup values show correctly as 8,16 or 32Mb respectively. Is this how it should be? The machine is a dual-CPU WinNT with ONLY 512Mb memory - yes, I know, and I've been telling the owners for months to increase that to at least 1GB to give us some operating leeway, as the ColdFusion server is on the same box, consuming at least 150Mb of the memory before MySQL gets to have its share! Should I perhaps reduce the innodb_thread_concurrency from its default of 8 down to 2 or 3 (on the basis of documentation - No of CPUs * No of disks)? All help appreciated, as always. Cheers Terry Riley Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking Issue?
Can someone help, please? We set up a server to handle a coldfusion web application (CFMX 6.1) running against MySQL 4.1.3b-beta on WinNT. When it is a little stretched, we are finding many instances of queries listed as either 'Sending...' or 'Copying...' in the processlist, with the time going ever upwards (last check was at 1000 seconds and rising). All the tables in the database concerned are InnoDB, and none of the queries concerned are, as far as I know, involved in any transaction - they are straight selects (albeit complex ones, perhaps). When this happens, the other requests to the server are inevitably slow, and these seem never to be cleared unless I kill the threads - and I'm not 100% sure how much damage I'm doing in that action. Is this a possible locking issue? If so, how do I get around it. The settings for the server are at default, except where noted. This is the my.ini file: [mysqld] max_connections=1000 basedir=e:/mysql datadir=e:/mysql/data wait_timeout=60 # TR added next 6 lines on 27/07/04, after instal of v4.1.3b old-passwords local-infile query_cache_size=25M query_cache_type=1 set-variable=max_allowed_packet=16M set-variable=key_buffer=8M log-bin= log_slow_queries= [mysql] local-infile=1 Any pointers as to what I may be doing wrong? Please? Yes, I know we should upgrade to 4.1.7, and we will - soon. Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Locking Issue?
- Original Message - Thanks for those hints, Dathan (see below): -Original Message- From: Terry Riley [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 10:12 AM To: [EMAIL PROTECTED] Subject: Locking Issue? Can someone help, please? We set up a server to handle a coldfusion web application (CFMX 6.1) running against MySQL 4.1.3b-beta on WinNT. When it is a little stretched, we are finding many instances of queries listed as either 'Sending...' or 'Copying...' in the processlist, Sending Data means stream the result set back, mysql found the rows and is still searching. Copying to tmp table means that it's using the tmp_table_size variable and if it busts past that will write to a temp table. Since you using innodb you need to increase your innodb buffer pool. Additionaly increase your tmp_table_size buffer, and verify your queries. You might need to tweak innodb_io_threads a feature specific for windows, and the awe memory setting. You might be system bound. I've already increased the tmp_table_size a little, but now that hits have trailed off (it's 8pm here), I'll have to wait till tomorrow to test this and other suggestions you've made. [mysqld] max_connections=1000 basedir=e:/mysql datadir=e:/mysql/data wait_timeout=60 # TR added next 6 lines on 27/07/04, after instal of v4.1.3b old-passwords local-infile query_cache_size=25M query_cache_type=1 set-variable=max_allowed_packet=16M set-variable=key_buffer=8M log-bin= log_slow_queries= [mysql] local-infile=1 Any pointers as to what I may be doing wrong? Please? Yes, I know we should upgrade to 4.1.7, and we will - soon. Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Partial Restore
Replying to my own message: - Original Message - We are shortly to go live with a new set of databases (InnoDB). The data in each database is identical in fields and types, the only difference being in the relevance of the data - they are soccer leagues, and each database represents information on the leagues/teams for a single season. There is a separate controlling database which directs web requests to the right year. Each league is identified within every table by a 3-4 character code. This all works pretty well, and was deemed a better scenario than having a database for each league, with a numeric field to identify the year. There are something like 160 leagues involved, but only 6 years (and rising). So we have 6 databases to take care of, not 160. Having explained the basic setup, we come to a problem: what if one of the administrators accidentally deletes a set of league information (maybe fixture information, for instance) six hours after the last backup? We could, of course, restore from the backup, but that would compromise every other league administrator's efforts since backup. Ideally, we would restore only the data relevant to that particular league (with the proper 3-4 letter code). Apart from opening up the latest mysqldump file and extracting the data from there (assuming the dork who deleted it knows more or less what has gone), and re-inserting table by table, having removed the data from other leagues, I can't think of another way to do it. We're currently using 4.1.3/Apache/WinNT, with hopes of upgrading to 4.1.7 before going live, though I doubt if the MySQL sub-version is relevant. I'd appreciate any help or guidance or advice on suitable tools. It would seem that one way to get what I need (and I have tried this) is to restore a 'usable' backup into a dummy database, and run through the tables of the one which needs the partial restore (after backing it up first, naturally) deleting records for that league (using the 3-4 letter code), then insert ... select from the dummy. There are fifteen tables involved, and in the trial I carried out using this method, the actual delete/insert...select sequence took about 20 seconds on my beat-up, memory-starved machine. Took me longer doing the backup/transfer to dummy. So I have found *a* method for doing what I need, while quite a few of you have been away enjoying (hopefully) your Thanksgiving holiday. It would be useful if anyone could point me at a simpler alternative, though. Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Partial Restore
We are shortly to go live with a new set of databases (InnoDB). The data in each database is identical in fields and types, the only difference being in the relevance of the data - they are soccer leagues, and each database represents information on the leagues/teams for a single season. There is a separate controlling database which directs web requests to the right year. Each league is identified within every table by a 3-4 character code. This all works pretty well, and was deemed a better scenario than having a database for each league, with a numeric field to identify the year. There are something like 160 leagues involved, but only 6 years (and rising). So we have 6 databases to take care of, not 160. Having explained the basic setup, we come to a problem: what if one of the administrators accidentally deletes a set of league information (maybe fixture information, for instance) six hours after the last backup? We could, of course, restore from the backup, but that would compromise every other league administrator's efforts since backup. Ideally, we would restore only the data relevant to that particular league (with the proper 3-4 letter code). Apart from opening up the latest mysqldump file and extracting the data from there (assuming the dork who deleted it knows more or less what has gone), and re-inserting table by table, having removed the data from other leagues, I can't think of another way to do it. We're currently using 4.1.3/Apache/WinNT, with hopes of upgrading to 4.1.7 before going live, though I doubt if the MySQL sub-version is relevant. I'd appreciate any help or guidance or advice on suitable tools. Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error
You can't - auto_increment cannot have a default value, AFAIAA. Terry - Original Message - mysql create table list_admin ( - admin_id int(11) default '0' not null auto_increment - ); ERROR 1067 (42000): Invalid default value for 'admin_id' How can I set default value to 0? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Assertion failure (MySQL Administrator)
Hopefully, this is ONLY about the administrator, not MySQL in general. Our log file (the one with all the connects and queries) has reached about 1.3Gb in size (33272 pages). Whilst trying to view the last-but-one - or indeed any except the last page of the log in Administrator - a sudden 'Assertion failure' notice appeared. Failure in ~ols\MySQL Administrator\MySQLAdministrator.exe File: .\source\myx_log_files.c Line 161 Expression: block_num !=0 This is Administrator v 1.0.8 running on WinNT, with Apache 2 and MySQL 4.1.3b-beta-nt-log. Yes, I know I should update both the Server and the Administrator, but am unable to until we have some downtime. Can I assume that, as the server is still running (apparently without problems) that the reported fault is only to do with Admin trying to read the log files, and nothing more serious? TIA Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.4 still reports itself as 4.1.3b-beta
Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and restarted afterwards. And have restarted several times since, because I couldn't believe it! The majority of the files now in the c:\mysql directory have creation dates on or about 27 August, the documentation is for 4.1.4gamma, so it does look as though the upgrade went through, as far as copying files is concerned. Just restarted again with no change (just in case). Any other clues? Terry - Original Message - Terry Riley [EMAIL PROTECTED] wrote: I have tried several instals (Windows XP), using binaries from several of the mirrors, over a 4.1.3b-beta-log version. No errors reported during any install, but it still reports itself as the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at the command line opening of mysql. Have I done something stupid, or are others seeing this, too? First thing to check: have you stopped mysql service and started it again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1.4 still reports itself as 4.1.3b-beta
Victor C:\MYSQL\BIN\mysqld-opt --defaults-file=C:\WINDOWS\my.ini MySql is what the path-to-executable states. If I remember rightly, the new version should be mysqld, period. Is that what's wrong? Will I have to tweak the registry to change that? Cheers Terry - Original Message - In the registry and/or the windows service utility see which executable is actually in the path. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 9/2/04 9:19 AM Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and restarted afterwards. And have restarted several times since, because I couldn't believe it! The majority of the files now in the c:\mysql directory have creation dates on or about 27 August, the documentation is for 4.1.4gamma, so it does look as though the upgrade went through, as far as copying files is concerned. Just restarted again with no change (just in case). Any other clues? Terry - Original Message - Terry Riley [EMAIL PROTECTED] wrote: I have tried several instals (Windows XP), using binaries from several of the mirrors, over a 4.1.3b-beta-log version. No errors reported during any install, but it still reports itself as the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at the command line opening of mysql. Have I done something stupid, or are others seeing this, too? First thing to check: have you stopped mysql service and started it again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1.4 still reports itself as 4.1.3b-beta
Further to last message, I've found that string in three different places in the registry: HKEY_LOCAL_MACHINE\system\controlset001\services\MySQL\ImagePath\ HKEY_LOCAL_MACHINE\system\controlset003\services\MySQL\ImagePath\ HKEY_LOCAL_MACHINE\system\currentcontrolset\services\MySQL\ImagePath\ Never having had to fiddle with the registry much in the past, can I presume that if I ignore the first two and change the third one to read 'mysqld' instead of 'mysqld-opt', save and reboot, I should get the right one started? As you can tell, I'm a little wary of this. Cheers Terry - Original Message - In the registry and/or the windows service utility see which executable is actually in the path. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 9/2/04 9:19 AM Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and restarted afterwards. And have restarted several times since, because I couldn't believe it! The majority of the files now in the c:\mysql directory have creation dates on or about 27 August, the documentation is for 4.1.4gamma, so it does look as though the upgrade went through, as far as copying files is concerned. Just restarted again with no change (just in case). Any other clues? Terry - Original Message - Terry Riley [EMAIL PROTECTED] wrote: I have tried several instals (Windows XP), using binaries from several of the mirrors, over a 4.1.3b-beta-log version. No errors reported during any install, but it still reports itself as the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at the command line opening of mysql. Have I done something stupid, or are others seeing this, too? First thing to check: have you stopped mysql service and started it again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.4 still reports itself as 4.1.3b-beta
Problem solved! Changing the CurrentControlSet MySQL ImagePath from 'mysqld-opt' to 'mysqld' now brings up the correct (or rather, the expected) version. On reflection, perhaps I didn't shut down MySQL before running the setup for the gamma version, and that is why it continued to use the older beta, though I thought I had. (FX: talks to self - 'must read the instructions *first* next time'). Thanks for your help, guys. Running the 'mysqld --version ' on the command line convinced me that I had actually installed it. Cheers Terry - Original Message - I have tried several instals (Windows XP), using binaries from several of the mirrors, over a 4.1.3b-beta-log version. No errors reported during any install, but it still reports itself as the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at the command line opening of mysql. Have I done something stupid, or are others seeing this, too? Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1.4 still reports itself as 4.1.3b-beta
All sorted now, Ian. - Original Message - Hope this helps If not try this (joking): http://foldoc.doc.ic.ac.uk/foldoc/foldoc.cgi?RTFM Ian -- That's what was missing. LOL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.4 still reports itself as 4.1.3b-beta
I have tried several instals (Windows XP), using binaries from several of the mirrors, over a 4.1.3b-beta-log version. No errors reported during any install, but it still reports itself as the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at the command line opening of mysql. Have I done something stupid, or are others seeing this, too? Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using CREATE PROCEDURE/FUNCTION
I think you'll find that 'create procedure' and 'create function' don't appear until version 5.0 of MySQL. Which is why you get a syntax (are you sure you have the right version?) error. Regards Terry - Original Message - Hi! Anybody knows how to use CREATE PROCEDURE and CREATE FUNCTION in 4.1.1-alpha-Max. I keep trying the CREATE PROCEDURE and CREATE FUNCTION examples found in the MySQL web documentation, but there's no way to make it work. It always prompts problems with syntax. Is delimiter a function working in all versions of MySQL? Is there a bug in this version? Any helping hand? Thanks! Marti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help: Retrieving time stamp
Stuart, why not change your select statement (assuming it is correct and
actually works, in and of itself) to give the date-formatted field an
alias:
SELECT
LFWJobBank.JobReferenceCode,DATE_FORMAT(LFWJobBank.Entered,'%m/%d/%Y') AS
date_entered,
LFWJobBank.DazeLeft, LFWJobBank.JobTitle
FROM LFWJobBank
WHERE VendorID = colname
Then your php code should change to grab 'date_entered' (probably without
having to format it once again). I don't use PHP that often, so I'm not
sure of the right syntax myself - let someone else comment on that!
But I do remember it is useful in testing to have php output the full
SQL query text to the browser, just so you can see it is correct before
proceeding.
Cheers
Terry
- Original Message -
Is there something wrong with this code ?
Still confused after reading the manual.
td?php echo
$rsJobShortDat-Fields('DATE_FORMAT('LFWJobBank'.'Entered','%m/%d/%Y'));
?/td
Entered is a timestamp column.
With the apostrophes off the table / column I get
errors about unexpected % .
Here is my SQL statement
SELECT
LFWJobBank.JobReferenceCode,DATE_FORMAT(LFWJobBank.Entered,'%m/%d/%Y'),
LFWJobBank.DazeLeft, LFWJobBank.JobTitle
FROM LFWJobBank
WHERE VendorID = colname
Thank you,
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange Text Field
Stuart
Could this be something as simple as the fact that you have a field with a
space in its name ('Contact Email'), which is sometimes called as 'Contact
Email' and sometimes as 'Contact_Email'?
I'm not even sure if a blank space is actually allowed in field names
Cheers
Terry Riley
- Original Message -
Okay, still not working in this one table so here is
all my info: I've also attached the table dump.
Thank you ,
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fairly lame question
Stuart, If the field is the only (or first) timestamp-type field in the table columns, then the record will automatically have the current date/time inserted when it is added. The only (or first) timestamp field will *also* be updated every time you update the record. View the timestamp field in the manual for how to create it. Cheers Terry - Original Message - I think this can be done, but tried a few times with no success. I want a column in a table that sets a timestamp. Instead of passing a value from the form though is there a way mySql would automagically stamp the records as they are inserted ? and as a side note - would table type make any difference. 4.0.20 - standard Thank you, Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recommended books for web app.
I'd go along with that recommendation. Terry - Original Message - Welling Thomson is terrific. - Original Message - From: Kerry Frater To: MySQL List Sent: Thursday, August 12, 2004 8:09 AM Subject: recommended books for web app. I am looking to port an app from an existing web environment to MySQL. The requirement is relatively easy. The Tables are read only and the data is to be only accessed via login password. The login will give a limited view of records based on a master/detail table relationship. I need to be aware of securing the database and have been told by others that I should look to use PHP. I know my local bookstore has the following publications (based on asking about MySQL PHP) Beginning PHP, Apache MySQL Web Development published by Wrox PHP MySQL written by Larry Ullmen PHP MySQL Web Development written by Luke Welling Laura Thomsan Has anyone seen these books and possibly recommend one of them? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joing two fields in a query
CONCAT() is what you need! SELECT CONCAT(Firstname,' ',Lastname) AS Name FROM Users; Terry - Original Message - Hi, Is it possible to join two fields in a query so that they are displayed as one column? For exmaple: SELECT Firstname + ' ' + Lastname AS 'Name' FROM Users; I hope you can see what I am trying to achieve from SQL here! Thanks for your help _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: type=heap problem...
I think the error is self-explanatory - you can't use auto_increment in a heap table (but you can have an index) Terry - Original Message - I couldn't get temporary table to load into memory using type=heap, here's the sample error as following: mysql create table tblheap ( - id int not null auto_increment, - primary key (id), - value_a tinyint ) - type=heap; ERROR 1164: The used table type doesn't support AUTO_INCREMENT columns -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Thanks to all who replied. Regards Terry Riley - Original Message - We have the query cache turned on, and it appears to be working well. However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? We are using ColdFusion MX, which has its own query cache (which is very useful for whats called Query-of-query selects), but there the residence time is configurable, and the timer is restarted if the cached query is called before the timeout. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache
We have the query cache turned on, and it appears to be working well. However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? We are using ColdFusion MX, which has its own query cache (which is very useful for whats called Query-of-query selects), but there the residence time is configurable, and the timer is restarted if the cached query is called before the timeout. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bin-log strangeness
Hi I've just taken a look at a recent bin-log, and found a stack of code which started with 'SET ONE_SHOT CHARACTER_SET_CLIENT=33', and went on to deleting blank users from the user table and inserting rows into the mysql.db table, such as 'test', 'test\_%', and some of the existing dbs. I do not remember, at the time this was done (evidenced by some updates either side of it on other tables, which are datetime fields), doing any updating or checking or optimization etc. Using 4.1.3b-beta on WinNT with Apache2. The databases 'affected' were all MyISAM, but not all the MyISAM dbs were involved (IYSWIM). Have I been hacked (which is possible, as we haven't yet sorted the user privileges, and still have anonymous in there)? Or is this some internal updating by MySQL itself? I've now updated the security (as recommended by the documentation), but I'm still miffed by this set of entries. Any hints would be gratefully accepted. Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL user passwords and ColdFusion MX6.1
Helpful Hint: I had a major problem not being able to register a DSN (Data Source Name) with the CF Administrator, using username and password from a MySQL 4.1.3 beta user table. After much hair-pulling, I discovered that CF cannot apparently handle the new 41-character format of the passwords. Killing these off, restarting with --old-passwords and redoing them as 16-char passwords allowed CF to register the DSNs. Hope this might help someone else who's going prematurely bald. Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Small Bug in 4.1.3 beta ?
No problem, Heikki. It's just a bit disconcerting to see '1' when '0' is expected. As for larger numbers, I realise that the rowcount is only an estimate for InnoDB. Cheers Terry - Original Message - Terry, thank you for reporting this. Since the cardinality reported by SHOW TABLE STATUS is just an estimate, this is not strictly a bug, but it is best to correct this anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Small Bug in 4.1.3 beta ?
I have been trying out 4.1.3 beta on XP, and note that in SQLyog, Maestro, and even MYSQL Administrator, the number of records shown is always one more than actual - in InnoDB tables only. Even an empty table still shows as 1. This is different than the count shown in these GUIs with 4.1.1 alpha. SELECT COUNT(*) retrieves the correct number, however. This is not the case for the MyISAM tables, which show the correct numbers at all times. Is this supposed to happen? Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE_FORMAT DISTINCT
Using 4.1.3 beta (InnoDB) on XP, via ColdFusion MX I have a table containing (among other things) a list of dates for soccer matches to be played. In order to list them correctly, the SQL has been: SELECT DISTINCT fixturedate, MONTH(fixturedate) AS CalMonth FROM Fixtures ORDER BY fixturedate which works fine, and I'm able to output the result of the SQL without problem. If that query is changed to: SELECT DISTINCT fixturedate, MONTH(fixturedate) AS CalMonth, DATE_FORMAT(fixturedate, '%d a%') AS testing FROM Fixtures ORDER BY fixturedate Coldfusion tells me that it can't convert a ByteArray into a string when it tries to output #testing# from the query (the other two fields are output with no error). However, outputting this query to a text file shows the #testing# field as a string. If the 'DISTINCT' is removed: SELECT fixturedate, MONTH(fixturedate) AS CalMonth, DATE_FORMAT(fixturedate, '%d a%') AS testing FROM Fixtures ORDER BY fixturedate Then the #testing# string outputs without a problem. Is there something in the documentation I've missed, or is this a bug (perhaps in CFMX)? Can't see why the DISTINCT clause should change a string to a ByteArray Any help would be appreciated. Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search problem
Pieter,
I think FTS minimum WORD size is 4 characters - you may to be searching
with 3 on 'May May'.
Not having ever used FTS; I believe you can adjust it to count 3-character
words by changing the configuration, but I'm not sure where - and it would
then need re-indexing, if I'm not mistaken.
Hope that helps
Terry
--Original Message-
Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called May May which doesnt show up in the
search results. A dog called Doggy Doggy does show up however. I
guess the problem is that MySql sees May May as being a date or
something and doesnt do a text compare.
Here is my query, from php.
$query_results = sprintf(SELECT * FROM dogslost WHERE MATCH
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
('%s' IN BOOLEAN MODE), $crit_results);
any ideas?
Regards
Pieter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT problem... ER_DUP_ENTRY? (No, it's not a one byte index :)
Just a suggestion, Kevin, but how about changing from INT to BIGINT? Terry --Original Message- We have a table with a primary index which is INT NOT NULL AUTO_INCREMENT. After inserting ~87,000,000 entries, we started seeing error 1062, ER_DUP_ENTRY. We can get going again after doing an ALTER TABLE to reset the auto_increment starting point, but this takes about an hour... I've seen a couple of places where how to get around this problem was discussed, but nobody seems to discuss *why* this occurs in the first place. Does anyone know why MySQL would start failing to increment an auto_increment index properly when it's nowhere near the upper limit? Does anyone know a way to get things functioning again without a couple of hours downtime? Hoping there's an answer out there somewhere... Kevin Brock [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mixing GROUP BY, AVG and COUNT
--Original Message- I have a table where the date a record was added is recorded in the date column. I can get count of how many records were entered on each day by doing this SELECT COUNT(*) FROM table GROUP BY date; I can get a total number of records by doing SELECT COUNT(*) FROM table but how do I find the average count per day? We can ignore the fact that it is possible that no records are added on a given day. I almost for got this is on 4.0.18 so nested selects are not an option. -- Chris W If I understood the question correctly, what you need is something like: SELECT @totaldates:=COUNT(DISTINCT date) FROM Table; SELECT COUNT(*)/@totaldates AS Average FROM Table; Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: field = order, data not inserting?
Probable cause: 'order' is a reserved word in MySQL (as used in 'order by'). Terry --Original Message- I have a field named order i think im missing something obvious, but i cant find it. When i insert something on the field order via PHP, no data on all of my fields are being inserted. But when i tried to change the field name to orders data are now being inserted. Its weird. -- - Louie Miranda http://www.axishift.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit in sub-query - when can we expect it?
Also interested in answer to this one. Terry Riley --Original Message- Hi List, When can we expect limits in sub-queries? I am currently on 4.1.0. 1235 - This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Query: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Processlist
Occasionally, when looking at the processlist using MySQLAdmin, I see entries 'unauthenticated user' 'reading from net' Would some kind person tell me what this means, and if I'm in danger of having data compromised? Thanks Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I determine the row number or key when table has no key
Take a look at LIMIT in the Manual Cheers Terry --Original Message- eg. say a table is created using: create table fred (f1 char(10), f2 int) Then it has neither keys nor an AUTO_INCREMENT field. Let's say 1000,000 records are then inserted into table fred. I then say 'select * from fred' and loop through results writing to a web page. I stop writing to the web page after say 20 records. The user hits 'next page'. I want to say 'select * from fred where ?field? ?value? Where ?field? and ?value? are what I want to know. Surely there is some kind of 'record number' or something available in mySQL for me to : 1. Retrieve and save 2. Query against I'm new to this mailing list. Apologies if I am asking this question of an inappropriate email address. Regards ... Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error in nested query?
Subselects are only available from 4.1 - that's why you have an error. Terry --Original Message- Hi all I have Mysql 3.23.45 on linux. while trying this command : SELECT outbox_id FROM outbox WHERE send_time=(SELECT MAX(send_time) FROM outbox WHERE subs_id=myid); I encounter this error: ERROR 1064: You have an error in your SQL syntax near 'SELECT MAX(send_time) FROM outbox WHERE subs_id=myid)' do you think this is because of low version of mysql or is there any mistake in my query? thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update field conditionally
Using v4.0.15 on WinNT under Apache. For my sins, the client has insisted on creating a page counter! The fields are to be CounterCode (Varchar 10), CounterValue (Int 10) and CounterStartDateTime (DateTime). Setting up the table is no problem. However, client wants to have the CounterStartDateTime field updated to Now() only on the first hit to that page, so that it can be reported as the start of the count (logically). Otherwise, it remains as a NULL value, and the CounterValue remains as 0. I have tried to find out if it is possible to do a single-pass update, changing the CounterValue from 0 to 1 and the CounterStartDateTime to the current time on condition that it is currently NULL, with something like: UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = (IF CounterStartDateTime IS NULL, Now()) without success. I've looked through the on-line manual, and cannot find any reference to such conditional updates. Perhaps I missed it. Any clues, please? Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update field conditionally
Thanks, Jeremy What I actually needed was: UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = IF(CounterStartDateTime IS NULL, Now(), CounterStartDateTime) This prevents it going back to NULL if the value is already not NULL. Thanks again. Terry --Original Message- UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = (IF CounterStartDateTime IS NULL, Now()) without success. It looks like you just have the syntax wrong. Try: UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime = IF(CounterStartDateTime IS NULL, Now(), NULL); See: http://www.mysql.com/doc/en/Control_flow_functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update field conditionally
See below:
--Original Message-
If it was me writing the code, I'd use two different update statements:
a) an UPDATE to initialize the DateTime to Now() and set the counter to
1
when the page is first hit
b) another UPDATE to increment the counter on all of the remaining hits
Something like this (assuming Java is your programming language):
// Logic to display the rest of the web page
...
// Obtain the current row for the counter.
getCurrentCounterRow();
// Store the current counter value in a variable
counter = ; //value obtained from current row
// Adjust the counter row depending on the value of the counter
if (counter == 0) {
update COUNTER_TABLE
set CounterValue = 1;
CounterStartDateTime = now();
}
else {
update COUNTER_TABLE
set CounterValue = CounterValue + 1;
}
// Display the counter value that applies after the IF statement was
executed.
...
etc.
Just my two cents worth
Rhino
I'm using CFMX.
Problem is that the display of the count (on the page) has also to show
the initial start date, so I'm fairly sure I have to go the Update then
Select route, rather than the other way around. And I really don't
think I want to have a Select, Update, Select routine
I can be fairly certain that the record exists (or can code around it if
it doesn't), so I'll probably stick with what I've got.
Thanks anyway!
Terry
- Original Message -
From: Terry Riley [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 12:11 PM
Subject: Update field conditionally
Using v4.0.15 on WinNT under Apache.
For my sins, the client has insisted on creating a page counter!
The fields are to be CounterCode (Varchar 10), CounterValue (Int 10)
and
CounterStartDateTime (DateTime).
Setting up the table is no problem. However, client wants to have the
CounterStartDateTime field updated to Now() only on the first hit to
that
page, so that it can be reported as the start of the count
(logically).
Otherwise, it remains as a NULL value, and the CounterValue remains
as 0.
I have tried to find out if it is possible to do a single-pass update,
changing the CounterValue from 0 to 1 and the CounterStartDateTime to
the
current time on condition that it is currently NULL, with something
like:
UPDATE Table SET CounterValue = CounterValue+1, CounterStartDateTime =
(IF CounterStartDateTime IS NULL, Now())
without success.
I've looked through the on-line manual, and cannot find any reference
to
such conditional updates. Perhaps I missed it.
Any clues, please?
Cheers
Terry Riley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update field conditionally
See below: --Original Message- Hello Terry, Tuesday, March 9, 2004, 5:11:00 PM, you wrote: I know you have some solutions to the original problem already, but I just wanted to make one small observation: TR The fields are to be CounterCode (Varchar 10), CounterValue (Int 10) and TR CounterStartDateTime (DateTime). Using a varchar(10) for the CounterCode will give you a Dynamically sized table. If you changed this to char(10) you will have the speed benefits of a Fixed size table which MySQL will be able to process significantly faster. This could be especially useful if this counter is to be hit a lot of times (i.e. it's a popular site). -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html Good point, Richard. I was perhaps in a little bit too much of a hurry putting that together, and didn't even consider that! Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[3]: Update field conditionally
Hello Terry, Tuesday, March 9, 2004, 6:25:00 PM, you wrote: TR Good point, Richard. I was perhaps in a little bit too much of a hurry TR putting that together, and didn't even consider that! No worries. One other thought that occurred to me that might help with the original problem is as follows: Instead of having the date when the counter started as a date-time field, you could construct your table as so: counter_code char(10) :) counter_value int(10) counter_last_modified timestamp counter_started timestamp By replacing the single started date with 2 time stamps you won't ever have to actually worry about the date again because on the very first INSERT both time stamps will be set and on any future UPDATE you can simply do counter_value = counter_value + 1 and the modified field will change automatically, leaving the original started field intact. This also presents the option of showing to the client/visitor the last time a page was visited (and you just know that might be the next request on the list :) -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html Now that one I had considered, Richard, and then promptly forgot about! Thanks for your help. Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: uppercase field constraints
Think that is for your script to work on, i.e.
INSERT INTO table
(field1, field2)
VALUES (UCASE('form.field1'), form.field2)
depending on the syntax of your programming language (and which MySQL
version you are using). The above works on 4.1.1.
Terry
--Original Message-
can i create a constraint to a field so that every data entered to that
field (string) be stored in uppercase, if is posible can anybody give
me an
example or point me to a link with documentation.
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: any ideas about it
Try ABS() Terry --Original Message- Hi all, I m looking for any function or a work around to fetch numerical data without its sign (-10 --10, 10 -- 10). Any ideas? Thanx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query
I think it should be: SELECT * FROM articles WHERE sectionID=1 ORDER BY Entrydate Desc LIMIT 1,10 Terry --Original Message- Any idea what is wrong with the following: SELECT * From articles ORDER BY EntryDate DESC LIMIT 1,10 WHERE SectionID=1 I want to return all articles with a particular SectionID, ordered by EntryDate and then I want to pick the start point and list the next 10 from that. Obviously in the final version the start point and the SectionID will be dynamic. I have tried removing the LIMIT part. I've tried changing the SectionID to a different field it always gives me an uninformative error? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem while installing MySQL, etc.
Thanks, Matt - I managed by unzipping it all to a new directory then renaming the directories so that the new one was c:\mysql and the old one became c:\mysql_4017, then run the mysql_fix_privilege_tables script. Cheers Terry --Original Message- Sharma and Terry, I believe MySQL only supplies setup.exe files with the versions that have production status. However, if you download the appropriate .zip file from the website, then that will contain all the files that you need. Simply extract it to the default location (C:\mysql in Windows or \usr\local, I believe, in Linux). If you are upgrading, then make sure you back up your old installation as to not override your data. HTH, Matt At 09:25 AM 1/7/2004, Sharma, Saurabh wrote: Hi I am trying to install MySQL for practice on my PC (Windows XP). I have all the administrative rights I downloaded the zip file mysql-4.1.1a-alpha.zip from the downloads. I extracted the zip file in a temporary folder. The manual says run setup.exe for installing on Windows but I couldn't find this file in the temporary folder. Can you help me on this regard Thanks and Regards Saurabh Sharma Fidelity Brokerage Technology -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] At 09:43 AM 1/7/2004, Terry Riley wrote: I have downloaded the correct windows zip file, but unlike previous Windows downloads, this does not include a setup.exe file, but hundreds of other (source?) files. Yes, I have RTFM, which merely tells me to extract to a temporary directory the run the setup.exe file, which is non-existent. How can I upgrade from 4.0.17, please? Or even start again from scratch with 4.1.1? Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation of 4.1.1 on XP
I have downloaded the correct windows zip file, but unlike previous Windows downloads, this does not include a setup.exe file, but hundreds of other (source?) files. Yes, I have RTFM, which merely tells me to extract to a temporary directory the run the setup.exe file, which is non-existent. How can I upgrade from 4.0.17, please? Or even start again from scratch with 4.1.1? Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running 4.0.17 and 5 on same machine
Currently running 4.0.17 InnoDB on XP/Apache 2.0.47; client wants to investigate using capability for stored procs in v5, but doesn't have another machine on which to experiment. Can these two be run on the same machine (though not at the same time)? Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running 4.0.17 and 5 on same machine
Thanks, Bruce, Roger Terry Riley --Original Message- Currently running 4.0.17 InnoDB on XP/Apache 2.0.47; client wants to investigate using capability for stored procs in v5, but doesn't have another machine on which to experiment. Can these two be run on the same machine (though not at the same time)? Regards Terry Riley -- 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: combining fields in select
Michael --Original Message- I am trying to select the firstname and surname fields from a table. select firstname + surname fullname from people; This does work in other databases but does not seem to work here. Am I doing something wrong or is there a different way to achieve this with mysql? select CONCAT(TRIM(firstname),' ',surname) AS fullname from people should work Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A final Windows MySQL PHP plea
Gary Does your php code use persistent connections? mysql_pconnect() rather than mysql_connect() ? If so, that would ramp up the CPU usage fairly quickly, AFAIAA. Just a thought Terry --Original Message- Hi all Is there anybody out there who has managed to successfully configure Win2000, IIS5, MySQL 4.0.14 and PHP 4.3.2 (ISAPI) to work with a couple of hundred users at any one time? I have chucked absolutely everything I can think of at this, but the MySQL (it seems) simply eats all the available CPU within a short space of time (regardless of users) and brings the site to a halt. My last throw of the dice today was to install all on a new Dual 1.8Ghz Pentium, with three hard disks in a RAID array, and 2GB memory, but it's achieved pretty much nothing. I am now desperate, and if anyone has any flash of inspiration for me, I'm all ears. The previous ASP version of the site runs like a dream, but there's something I'm either doing wrong, or this new combination of software simply doesn't like. Many thanks Stressed Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding Table and database size
Dan What you may be looking for (and I had to hunt around to find it myself!) is myisamchk -eis table_name Hope that helps - just because you don't get an answer doesn't mean we don't care - it probably means we don't know. Terry --Original Message- Ok, if this is the wrong mysql list could someone tell me which list would best be able to handle my question if no one on this list knows how to get the table and database sizes? TIA Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP multiple SQL statements
As far as I'm aware, in php you can only do one statement per call. Terry --Original Message- Hi there, When i try to do the following in PHP I get errors. Is this not permitted or is this due to a setting somewhere? Can i do only one SQL statement per call to mysql_query? $sql = CREATE TEMPORARY TABLE tmp SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'; INSERT INTO tmp SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1'; SELECT * from tmp; DROP TABLE tmp;; $res = mysql_query($sql); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unknow SQL Error!
--Original Message- cut CREATE TABLE nuke_contactbook( cut workphone varchar( 255 ) , homepage varchar( 255 ) , IM varchar( 255 ) , events text, reminders int( 11 ) , notes text, PRIMARY KEY ( contactid ) , KEY contactid( contactid ) , KEY uid( uid ) You seem to be trying to create contactid key twice? ) Mensagens do MySQL : You have an error in your SQL syntax near 'events text, reminders int( 11 ) , notes text, PRIMARY KEY ( contactid ) , K' at line 13 Thanks very much.Marcelo---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.491 / Virus Database: 290 - Release Date: 18/6/2003 Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not an Ad
Hi, I've been struggling for a couple of days trying to get phpMyAdmin to accept csv files for data uploads onto my hosted MySQL, and was having no success using v2.5, despite 'local file being switched On at both ends. My ISP just installed v2.5.1 and whooppe! it works. I wouldn't normally make this sort of statement, but I know there are a fair number of people out there having similar problems. If uploading from your machine *don't* use the DATA LOCAL, but the plain DATA option. If anyone wants to rap my knuckles over this, fair enough, but I struggled, and a solution has been found, so why not? Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN function
Yes --Original Message- Hi, I have written a program that creates statments for me, and sometimes I end with SELECT..WHERE number IN(1) instead of SELECT... WHERE number IN(1,2,3,4) would number IN(1) works, for the moment i have no mean to test it, it is why i'm asking. Regards, Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: How to run a script? Newbie alert..
Vielen Dank, Mark Cheers Terry --Original Message- Use source filename from the mysql command line Start mysql in the bin directory, switch to the db in use via use dbname and run the command source path_to_filefilename Or read the manual section 3.6 Using mysql in Batch Mode Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to run a script? Newbie alert..
I've not been at this MySQL stuff too long, so bear with me.. (4.0.13 running on Windows XP/Apache). Most of my work has been in Windows, and almost always with a GUI, so that when presented with the instruction to 'run mysql_fix_privilege_tables' script, I am totally clueless as to how to go about it! Tried going to a command box, getting to the c:\mysql\bin directory, type mysql, get mysql fired up, but I can't figure out (and yes, I have read the (mainly *nix oriented) manual without success) how to get a script in the \scripts sub-dir to run. Everything I try, in my ignorance, ends with a 'SQL query error' or similar. Help, please, I'm not used to this command-line stuff! Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server variables
This may be a PHP rather than MySQL issue I'm using 4.0.13 on Windows XP and Apache 1.3. My phpinfo() shows (among others) QUERY_STRING and REQUEST_URI in the Apache environment and _SERVER[QUERY_STRING] with _SERVER[REQUEST_URI] under the PHP variables. On my ISP, who is running W2K/IIS5, none of these appear. Is this normal? I had some neat things going on a navigation bar using $REQUEST_URI, but now can't use them. If I'm on the wrong list with this, please forgive me. Cheers Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: random start to query
Scott --Original Message- I have a database table with say 100 rows. What I would like is to preform a query starting at a random place within the table, that will wrap around if I want it to. That is if the query starts returning results at the 98th row, then I would want it to return 98, 99, 100, 1, 2, . I know that mysql has the RAND() function but how can I determine on the fly how many rows are in my table, and what would the SQL statement look like? And how can I get the query to wrap? I'm a relative newcomer to this stuff myself, but you may have to do two separate queries for this - one to determine the row count, then another two-parter, using your random number in the first SQL (LIMIT) statement, with a UNION SQL statement to a similar query for the rest. Does that help? Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: random start to query
Scott --Original Message- What would be the most efficient query to determine the number of rows? Probably something like SELECT COUNT(*) AS rectotal FROM table ? Cheers Terry PS - better to reply direct to list rather than individual, as you seem to have done on this occasion :-} -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 10:50 AM To: [EMAIL PROTECTED] Subject: Re: random start to query Scott --Original Message- I have a database table with say 100 rows. What I would like is to preform a query starting at a random place within the table, that will wrap around if I want it to. That is if the query starts returning results at the 98th row, then I would want it to return 98, 99, 100, 1, 2, . I know that mysql has the RAND() function but how can I determine on the fly how many rows are in my table, and what would the SQL statement look like? And how can I get the query to wrap? I'm a relative newcomer to this stuff myself, but you may have to do two separate queries for this - one to determine the row count, then another two-parter, using your random number in the first SQL (LIMIT) statement, with a UNION SQL statement to a similar query for the rest. Does that help? Terry -- 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: random start to query
Great!Didn't know you could do that. Cheers Terry --Original Message- I think I found another answer to my own question. FYI a good way to return results in a random order SELECT user FROM table ORDER BY RAND() -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
