Re: Can't Connect Localhost
Hi John, Starting over…. What is the error message? Terry ___ Terry J Fundak Systems Engineer Network Design and Security Solutions for SMBs Tech Support - Client and Server Systems TJSoftworks 1834 Chadwick Court Santa Rosa, CA 95401 (707) 849-1000 Cell e-Mail: te...@tjsoftworks.com On Aug 31, 2013, at 3:26 PM, John Smith cantinaderecuer...@yahoo.com wrote: Hi; How do I change my connection from localhost to 127.0.0.1 on a Win8 machine? TIA, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql smime.p7s Description: S/MIME cryptographic signature
RE: Multiple joins from same table?
Shawn, Thanks for the info, it does help indeed. I had also replied back to Gary to thank him as well, but I don't think that it made it to the list... so to Gary, thanks as well. Regards, Terry Terry Van de Velde Email: bya...@rogers.com Phone: (519) 685-0295 Cell: (519) 619-0987 -Original Message- From: shawn.gr...@sun.com [mailto:shawn.gr...@sun.com] Sent: December 12, 2009 4:39 PM To: Terry Van de Velde Cc: mysql@lists.mysql.com Subject: Re: Multiple joins from same table? Terry Van de Velde wrote: Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no Any help is appreciated. The trick to using the same table two or more times in the same query is through something called aliases when you alias a column or table you give it a different name and will make it easier to manage. Something like this is what you are after SELECT s.date_time , th.team_name home_team , ta.team_name away_team FROM schedule s INNER JOIN teams th ON th.team_no = s.home INNER JOIN teams ta ON ta.team_no = s.visitor Here you can see that I aliased the `teams` table twice. Once to handle the home team information (th) and once for the away team info (ta). I also aliased the team_name columns to make them less confusing labeling one as home_team and other as away_team. I think that once you get a grip on how to use aliases, all of this multiple-table stuff will start to become much easier. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bya...@rogers.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.716 / Virus Database: 270.14.105/2561 - Release Date: 12/12/09 14:39:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Multiple joins from same table?
Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. - Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint - teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) - SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no - Any help is appreciated. Best Regards, Terry
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]
Migrate HUGE Database
Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Thanks, Terry Terry Babbey Infrastructure Specialist Information Technology, Lambton College of Applied Arts Technology [EMAIL PROTECTED], 519.542.7751 x3293
RE: Migrate HUGE Database
Thanks to all for the quick replies.
Yes, the mysqldump worked perfectly. Boy do I feel like a newbie now!
If I use the method below, will that transfer the mysql admin database
too with the user information?
Thanks,
Terry
-Original Message-
From: Rolando Edwards [mailto:[EMAIL PROTECTED]
Sent: March 10, 2008 2:52 PM
To: Daniel Brown; Terry Babbey
Cc: mysql@lists.mysql.com
Subject: RE: Migrate HUGE Database
You can take this a step further
nohup mysqldump -hhost of Linux Machine -uusername -ppassword
--all-databases --routines --triggers | mysql -hhost of Windows
Machine -A
This will pipe all the data directly to Windows machine without an
intermittent file. Even if you logout of Linux, it should keep going
-Original Message-
From: Daniel Brown [mailto:[EMAIL PROTECTED]
Sent: Monday, March 10, 2008 2:40 PM
To: Terry Babbey
Cc: mysql@lists.mysql.com
Subject: Re: Migrate HUGE Database
On Mon, Mar 10, 2008 at 2:29 PM, Terry Babbey [EMAIL PROTECTED]
wrote:
Hello,
I have a huge database that I would like to migrate from a server
running 4.0.16 to a server running the Windows version 5.0.45. The
database is approximately 3,500,000 records. I get timeout errors
using
PHPMyAdmin to export the data.
If you have shell access, do the following:
mysqldump -u username -p database_name database_name.sql
To explain:
-u username Replace 'username' with the database
username.
-p This signifies that you'll use
a password (at a prompt)
database_nameThe full name of the database to dump
Redirects all output to a
file, deleting previous data, if any
database_name.sql The SQL output file, written to the
current directory.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
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: 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]
user permissions to all DB
Hello, I want to grant a user all permissions to all DBs on the system as well as any new DBs that show up. I want to avoid having to modify permissions everytime a new DB is added. Is there a way to do this? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user permissions to all DB
Just to verify, will that include all new databases? On 8/20/07, Jay Pipes [EMAIL PROTECTED] wrote: Terry wrote: Hello, I want to grant a user all permissions to all DBs on the system as well as any new DBs that show up. I want to avoid having to modify permissions everytime a new DB is added. Is there a way to do this? GRANT SELECT, INSERT, CREATE, ... ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password'; Cheers, Jay -- 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: UPDATE question
On 5/3/06, Barry [EMAIL PROTECTED] wrote: Cummings, Shawn (GNAPs) schrieb: If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) FIELD4 = FIELD1 FIELD 2? Field 4 should be field 1 AND field 2? Can't decide which one field4 should get? :P Well, every child learns that 1 and 1 is 2 ;-P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the last entry per item
On 4/27/06, Brian J. Matt [EMAIL PROTECTED] wrote: As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. If you are looking to obtain a result set the represents the current location of all items in the system you can use a sub-select as follows: SELECT item_id AS lid,location,status,timestamp FROM xyz WHERE timestamp=( SELECT MAX(timestamp) FROM xyz WHERE item_id=lid ) For the sake of efficiency make sure you have a key on timestamp. Hope this helps, Tez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL Backups
On 4/12/06, Tim Lucia [EMAIL PROTECTED] wrote: Would you not lock tables on the slave? The idea of catching it up implies this is way it is done. Catching up means once replication can proceed once the tables are unlocked (on the slave). At least that is the way I read it... On the slave I perform a slave stop, (optional) lock tables, mysqldump, slave start. Since the slave is a dedicated backup instance, and is never connected to directly by applications, the backup is entirely transparent to database users. My suggestion is for the purpose of simplifying such a setup such that it can be performed from a single MySQL instance. Tez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL Backups
On 4/11/06, Dana Diederich [EMAIL PROTECTED] wrote: We use a dedicated replicated instance for backups. Every night, we lock all of the tables, and dump all of them to compressed files, and unlock them afterwards. It takes a while to catch up, but that doesn't hurt anything. I too use this solution, but it occurs to me that it would be useful if it were possible to replicate one database into another (differently named) database on the same mysql instance for the very purpose of taking snapshots without locking tables in the live system. I realise that it is possible to do this by running two local mysql instances, but a local replicator could be an interesting interim backup solution. Might something like this be on the agenda, or would the planned holistic backup solution eclipse such a feature. Thanks, Tez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE TABLE x AS with ENGINE = ARCHIVE
Hi, I'm using MySQL 4.11. Is it possible to use the CREATE TABLE x AS syntax alongside the ENGINE = x pragma, since this would make archiving of tables very simple. I require something along the lines of this: CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE If this cannot be done then I can always get equivalent functionality by performing CREATE TABLE x (...) ENGINE=x followed by INSERT INTO x SELECT * FROM x, however this is not so neat since it require knowledge of the source table structure which makes it less ideal for automation. Many thanks, Tez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE x AS with ENGINE = ARCHIVE
On 3/28/06, Pure Web Solution [EMAIL PROTECTED] wrote: I dont think that it is possible to specify the engine when creating a table this way, you could however create the table using the: CREATE TABLE old AS SELECT * FROM request_log and then issue an alter table command setting the engine to whatever you like. Thanks Paul, That solution seems a little wasteful in terms of resources though. If there is no nice way to achieve this operation then do people think that it would be a good idea to push for the inclusion of this functionality. If more folks than myself can see the benefit in this then please speak up, in which can I'll cross post this to the development list to see what the devs make of it. Warm regards, Tez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE x AS with ENGINE = ARCHIVE
On 3/28/06, Peter Brawley [EMAIL PROTECTED] wrote: I require something along the lines of this: CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE CREATE TABLE tblname ENGINE=archive SELECT * FROM request_log; PB Excellent. Exactly what I need. Thanks :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
-help
Terry Spencer Haigh Consultancy Services Tel: +44 (0)116 262 3966 Fax: +44 (0)116 262 3946 (Leciester Office) Fax: +44 (0)870 052 4572 (Terry) Mob: +44 (0)7796108244 www.haigh-cs.co.uk http://www.haigh-cs.co.uk
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]
join question
Hi All, I have a question for clearer brains than mine. I would like to join two tables,. There may be many possible joins in table B to table A, but I only want to join one row from B to table A - the row with the closest, but lesser date. TABLE A Row Id date 1 46 3 Jan 7 20 10 Jan TABLE B Row Id date 4 46 1 Jan 5 46 2 Jan 6 46 4 Jan 8 20 8 Jan 10 20 7 Jan 11 20 9 jan Result Row 1` in A is joined to row 5 in B Row 7` in A is joined to row 11 in B SELECT a.row, b.row FROM A a LEFT JOIN B b ON (a.id = b.id AND a.date b.date AND the row with the max dates from the possible join in b?) Any suggestions would be appreciated. Terry -- 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]
Connect issues
I can connect on the command line, but have problems connecting using DBI/D on the same server. # ./bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.0-alpha-standard Mysql is running # ps -ef | grep mysql root 10626 9589 0 11:57 pts/341 00:00:00 /bin/sh ./bin/mysqld_safe mysql10642 10626 0 11:57 pts/341 00:00:00 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --bag ... I can connect at the command line # ./bin/mysql -username=hcspt Welcome to the MySQL monitor. Commands end with ; or \g. When I connect using perl on the same server the following error occurs ...failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'... Ive noted that mysql.sock is not in the location in the same directory as the above error message # find / -name mysql.sock /tmp/mysql.soc Setting the location of mysql.sock in my.conf [client] socket = /tmp/mysql.sock Causes error Any suggestions? Thanks Terry
RE: Hour counts
There are a few options, for more information see
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
Returns the integer difference between the date or datetime expressions
datetime_expr1 and datetime_expr2. The unit for the result is given by the
interval argument. The legal values for interval are the same as those
listed in the description of the TIMESTAMPADD() function.
mysql SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
- 3
mysql SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
- -1
TIMESTAMPDIFF() is available as of MySQL 5.0.0.
It appears you require the answer in fraction hours. Set the interval to
seconds and divide the result by 3600 (60*60 = seconds in an hour)
---
UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since
'1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is
called with a date argument, it returns the value of the argument as seconds
since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME
string, a TIMESTAMP, or a number in the format YYMMDD or MMDD in local
time.
mysql SELECT UNIX_TIMESTAMP();
- 882226357
mysql SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
- 875996580
Convert both dates to seconds using UNIX_TIMESTAMP() and subtract one form
the other. Divide the result by 3600 (60*60 = seconds in an hour) to obtain
the fractional hours.
Terry
-Original Message-
From: Gyurasits Zoltán [mailto:[EMAIL PROTECTED]
Sent: 27 July 2005 17:12
To: mysql@lists.mysql.com
Subject: Hour counts
Hello All!
I would like to calculate the hour counts from 2 'datetime'.
Example: 2005-07-27 18:00 and 2005-07-27 19:30 = 1,5 hour
I try this but not good!
R1 : munkaido_end-munkaido_start /simple substract/
R2 : ROUND(ROUND((end-start)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 R3
07-14 15:00 07-14 17:30 23000 2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a question about MySQL installation
Dear, I have installed MySQL4.1. But why it can not startup when I open my computer? Also, how can I test MySQL can run or not? Thanks for your advice. Best Regards, Terry
compiling mysql on macosx 10.4
does anybelly know why the following happens? i am using macosx 10.4 and ./configure --prefix=/usr/local/mysql --with-unix-socket-path=/usr/local/mysql/run/mysql_socket --with-mysqld-user=mysql --with-comment --with-debug sql_list.h: At global scope: sql_list.h:401: warning: inline function `base_ilist::~base_ilist()' used but never defined if g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local\ -DDATADIR=\/usr/local/var\ -DSHAREDIR=\/usr/local/share/mysql\ -DHAVE_CONFIG_H -I. -I. -I.. -I../innobase/include -I../include -I../regex -I. -O3 -DDBUG_OFF-fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_DARWIN_THREADS -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT -MT mysqld.o -MD -MP -MF .deps/mysqld.Tpo -c -o mysqld.o mysqld.cc; \ then mv -f .deps/mysqld.Tpo .deps/mysqld.Po; else rm -f .deps/mysqld.Tpo; exit 1; fi opt_range.h: In member function `bool SQL_SELECT::check_quick(THD*, bool, ha_rows)': opt_range.h:146: warning: passing negative value '-0x1' for converting 1 of 'Bitmap64u::Bitmap(uint)' mysqld.cc: In function `int bootstrap(FILE*)': mysqld.cc:3350: warning: converting of negative value '-0x1' to 'ulong' mysqld.cc: In function `void* handle_connections_sockets(void*)': mysqld.cc:3589: error: invalid conversion from 'size_socket*' to 'socklen_t*' mysqld.cc:3589: error: initializing argument 3 of 'int accept(int, sockaddr*, socklen_t*)' mysqld.cc:3662: error: invalid conversion from 'size_socket*' to 'socklen_t*' mysqld.cc:3662: error: initializing argument 3 of 'int getsockname(int, sockaddr*, socklen_t*)' sql_list.h: At global scope: sql_list.h:401: warning: inline function `base_ilist::~base_ilist()' used but never defined make[4]: *** [mysqld.o] Error 1 make[3]: *** [all-recursive] Error 1 make[2]: *** [all] Error 2 make[1]: *** [all-recursive] Error 1 :-)2 -- 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]
Explain and indexes
Im trying to speed up a query. select project_id from timesheet ts where ts.del is null and signoff = 'A' The output of explain is detailed below. ++-+---+--+-+--+ | id | select_type | table | type | possible_keys | key | key_len ++-+---+--+-+--+ | 1 | SIMPLE | ts| ALL | signoff,del,del_signoff | NULL |NULL ++-+---+--+-+--+ | ref | rows | Extra | ++-+---+--+-+--+ | NULL | 3907 | Using where | ++-+---+--+-+--+ An index exists on all three columns referred to, in addition to a combination of del and signoff. The indexes are listed as possible keys, but none used by the query; key = null. Can anyone suggest why? How can I optimise this? Thanks Terry -- 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]
mysql and leftt outer join
Lets say I have a Database defined as create table Photos ( PhotoIdxVarchar(20), PhotograherIdx varchar(20), PhotoTypeIdxvarchar(20), CameraIdx varchar(20) PhotoDate DateTime ); Create table Photographer ( PhotographerIdx varchar(20), Photographervarchar(50)); Create table PhotoType ( PhotoTypeIdxvarchar(20), PhotoType varchar(50)); Create table Camera ( CameraIdx varchar(20), Camera varchar(50)); Therefore, a select to print the basic info would be: select Photos.PhotoIdx, Photographer.Photograper, PhotoType.PhotoType, Camera.Camera, Photos.PhotoDate from Photos, Photorapher, PhotoType, Camera where ((Photos.PhotograherIdx = Photographer.PhotograperIdx) and (Photos.PhotoTypeIdx = PhotoType.PhotoType) and (Photos.CameraIdx = Camera.CameraIdx)); The problem with doing this is that if any of the lookup indexes are null (such as Photos.PhotographerIdx, Photos.PhotoTypeIdx or Photos.CameraIdx) or point to a nonexistent record in the lookup table, the record will not be selected. So one has to use a left outer join. The problem is I can get a left outer join to work that links two table, but not more. So how woulds you construct the above select to use only left outer joins??? Thx,Terry -- 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]
Spatial extensions and GIS
Hi. This may be a daft question, so forgive me: I am just starting out with the Spatial extensions and I am wondering how I can use it to determine the distances between any 2 landmarks that have been entered as POINTs in the database. The POINTs are entered with longitude and lattitude coordinates. I have 2 problems: 1) The Distance nor the Buffer functions have not been implemented. 2) How can I use MySQL to help me convert from lat / long to distances in miles (or KM)? Thanks. Terry. -- 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]
calc field ?
How do you set up calculated fields in mysql? Thx,Terry -- 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]
New to MySQL
I have just started having a look at MySQL. I have the server installed and running with the default settings and I now need to create a database and tables. I will be testing all locally on WinXPpro. I would like to use phpMyAdmin but am having some trouble displaying the index.php page. IE shows code, or does not load at all, MacromediaMX is complaining about a server not existing. Or is there another way? I guess I could be missing a few nuts-n-bolts, what services do I need to run on WinXP and their configs? Is there anywhere where I can find some info for absolute beginners to try and shorten the learning curve? Once up and running I expect to be linking using Access 2000 with which I have some experience and hope to port over some tables. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to MySQL
Thanks for the reply. I will have a look at MySQLCC. Regards -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 17 March 2004 13:30 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: New to MySQL Why don't you try MySQLCC from the MySQL site? -Original Message- From: Terry Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 17, 2004 4:25 AM To: [EMAIL PROTECTED] Subject: New to MySQL I have just started having a look at MySQL. I have the server installed and running with the default settings and I now need to create a database and tables. I will be testing all locally on WinXPpro. I would like to use phpMyAdmin but am having some trouble displaying the index.php page. IE shows code, or does not load at all, MacromediaMX is complaining about a server not existing. Or is there another way? I guess I could be missing a few nuts-n-bolts, what services do I need to run on WinXP and their configs? Is there anywhere where I can find some info for absolute beginners to try and shorten the learning curve? Once up and running I expect to be linking using Access 2000 with which I have some experience and hope to port over some tables. Regards -- 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: New to MySQL
Thanks Peter, Will try out XAMPP. Regards -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: 17 March 2004 10:50 To: Terry Smith; [EMAIL PROTECTED] Subject: RE: New to MySQL Hi To use phpMyAdmin you need a webserver and php installed - the code suggests you either are not running through a webserver or that it is not configured for php. If you are not developing for php you might take a look at windows front ends for mysql - search the list for recommendations HTH Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 --- -Original Message- From: Terry Smith [mailto:[EMAIL PROTECTED] Sent: 17 March 2004 10:25 To: [EMAIL PROTECTED] Subject: New to MySQL I have just started having a look at MySQL. I have the server installed and running with the default settings and I now need to create a database and tables. I will be testing all locally on WinXPpro. I would like to use phpMyAdmin but am having some trouble displaying the index.php page. IE shows code, or does not load at all, MacromediaMX is complaining about a server not existing. Or is there another way? I guess I could be missing a few nuts-n-bolts, what services do I need to run on WinXP and their configs? Is there anywhere where I can find some info for absolute beginners to try and shorten the learning curve? Once up and running I expect to be linking using Access 2000 with which I have some experience and hope to port over some tables. Regards -- 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 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]
mysqldump ?
I have a mysql 3.23 running under win98. I also have a database with a largeblob, which has a jpeg image in it. Now I am trying to upload this to a free server using the following command to export the table: bin/mysqldump -u user --password=mypass mydatabase mytable dumpdir\mytable.sql The jpegs are becoming corrupted, I know it is happening in the mysqldump program. Because, I have a rh 8.0 box running mysql 3.23 and if I issue the following command: mysql -u user --password=mypass mydatabase mytable.sql All of the data is ok except for the blobs which are corrupted. I also tryed exporting it as: bin/mysqldump -u user --password=mypass --quick mydatabase mytable dumpdir\mytable.sql But the data was still corrupted. Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump ?
For the free web server, the transfer was web based using MyPHPAdmin. To test it on my RedHat 8.0 box(my home network server,) I copied the file to my Samba directory with Windows Explorer Then I switched over and signed on and did the import. On Wed, 15 Oct 2003 11:37:32 -0500, Paul DuBois wrote: You have listed the dump and reload phases of this operation, but there is another phase implicit in what you're doing: the transfer of the file from one machine to another. How are you transferring the file, and are you sure that process is not changing the file contents -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump ?
Yes, that seems to be part of the problem. I still seem to be loosing some images though. And how can I upload to PHPMyAdmin without altering the comments that mysqldump creates. Terry On Wed, 15 Oct 2003 12:05:19 -0500, Christensen, Dave wrote: Are you attempting any sort of editing of the exported file? We routinely dump our production database, which includes several tables that hold TIF images, with no problems at all. In fact, we'll also transfer data between servers with a command like: Mysqldump --add-drop-table databasename -uroot -ppassword | mysql --host=otherIPaddress -uroot -ppassword IN addition, we'll also dump to text files with no problem at all. This is a part of our daily backup and has worked for data restore/loading on several servers, both LINUX and Windows. The only time we've had problems is if I would try to edit the file, the editor would truncate the text lines containing the blob data which would then create corrupted images. -Original Message- From: Terry Cheryl Haimann [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 11:11 AM To: mysql Subject: mysqldump ? I have a mysql 3.23 running under win98. I also have a database with a largeblob, which has a jpeg image in it. Now I am trying to upload this to a free server using the following command to export the table: bin/mysqldump -u user --password=mypass mydatabase mytable dumpdir\mytable.sql The jpegs are becoming corrupted, I know it is happening in the mysqldump program. Because, I have a rh 8.0 box running mysql 3.23 and if I issue the following command: mysql -u user --password=mypass mydatabase mytable.sql All of the data is ok except for the blobs which are corrupted. I also tryed exporting it as: bin/mysqldump -u user --password=mypass --quick mydatabase mytable dumpdir\mytable.sql But the data was still corrupted. Any suggestions? -- 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]
PHP/MySQL/MyODBC drivers
I need to know the syntax to link two tables together that are in two seperate DB's using the ODBC drivers in PHP. Can anyone help with an example? Thanks Terry L. -- 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]
