RE: Query problem

Hey there I have the following table structure: CREATE TABLE documents ( id int(11) NOT NULL auto_increment, user varchar(50) NOT NULL default '', olduser varchar(50) NOT NULL default '', username varchar(100) NOT NULL default '', uploaddate timestamp(14) NOT NULL, docdate varchar(100) NOT

Is my server not tuned properly ?

Context: MySQL 4.0.18 on redhat linux 2.4.20-28.8smp kernel. Usage scenario: Around 10-20 users daily (not necessarily concurrent) I have collected some System variables over 2x periods. Period #1: 7 days uptime | Select_full_join | 1967 | |

InnoDB Deletes / Swap Problem

Hi, We have just recently moved 1 of our InnoDB cache servers to a new much bigger machine, 4 CPU, 8GB Ram and masses amount of disk space available from a SAN. The OS is Redhat AS 3 with kernel 2.4. MySQL is the only application on this machine and its the latest stable release of 4.0.x.

FW: Query problem

Sorry guys, I forgot to post to the list as well.. -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 5:42 PM To: [EMAIL PROTECTED] Subject: RE: Query problem Hi Schalk, I recommend using parenthesis to group your AND/OR logic more firmly.

FW: InnoDB Deletes / Swap Problem

Sorry guys, I forgot to post to the list as well.. -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 6:56 PM To: Marvin Wright Subject: RE: InnoDB Deletes / Swap Problem Marvin, Does the job run locally on this machine with MySQL?.. If so..

FW: Question

Sorry guys, I forgot to post to the list as well.. -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 9:27 AM To: [EMAIL PROTECTED] Subject: RE: Question Hi there, Can you please provide a sample of a query that is not working? Also if you

FW: Table query and column overlap

Sorry guys, I forgot to post to the list as well.. -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 4:12 PM To: [EMAIL PROTECTED] Subject: RE: Table query and column overlap Hi again, To fix this wrapping you can use the \G command, this

FW: Is my server not tuned properly ?

Sorry guys, I forgot to post to the list as well.. -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 6:45 PM To: Xanana Gusmao Subject: RE: Is my server not tuned properly ? Hi Xanana, The variables you are looking at, while giving you some

very large HEAP-tables in 4.1.3

I was just wondering if anybody has been using very large HEAP-tables and if there are ways to have mysql use the memory more efficient: (I have no experience with all heap-tables but using them as temporary tables...) I just started testing with 2 heap-tables on a development-system (p4

Re: very large HEAP-tables in 4.1.3

Maybe you should try to normalize your table, 'symbol' could have its own table, that would reduce data and index. And then try to reduce the size of your rows, bidsize and asksize should be in integer I think. Maybe 'float' would be enough. What represents the 'quelle' column ? Is kurszeit

keep losing login rights with MySQL

I have installed 4.0.20a binary on my own PC to work with MySQL. I take my PC home with me. The only thing I change at home is my IP address as my home n/w is different from my work n/w IP range and I have a VPN link between the office home. Since using this version (.17 was there before)

Microsoft's ASP/SQL combo

Do anyone have an ASP website that acquires info from SQL Database? I would like to see an sample file to see how it acquires info from SQL Database for my future Database project that I'm currently researching on Thanks in advance, Scott -- MySQL General Mailing List For list archives:

results with utf8 characters

Hi, sorry, I must ask again, because there was no answer to this problem until now. I've installed mysql 4.1.3 as rpm package. If SELECT results columns with unicode characters (multibyte characters like german umlauts), the shown frame in my client is defective: mysql select * from uml;

Re: very large HEAP-tables in 4.1.3

Philippe Poelvoorde wrote: Maybe you should try to normalize your table, 'symbol' could have its own table, that would reduce data and index. And then try to reduce the size of your rows, bidsize and asksize should be in integer I think. Maybe 'float' would be enough. What represents the 'quelle'

Where are the Foreign Keys?

Hi, as far as i know, there is only 1 way to extract the foreign key relations from a table: show create table tablename. Are there any others? regards ralf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Where are the Foreign Keys?

Hi Ralf, as far as i know, there is only 1 way to extract the foreign key relations from a table: show create table tablename. Are there any others? The comments column in SHOW TABLE STATUS is supposed to list the FKs as well. A rather silly way of doing things, IMO, cause you can't use the

Re: Table query and column overlap

You need to change the buffer size of your, for lack of a better term, DOS prompt window. RIGHT-click on the title bar of your window and click on properties. On the properties page for my platform (win2k) I have a Layout tab. On that tab are two size settings, Screen Buffer Size and Window

Re: Query problem

Schalk Neethling wrote: I run the following type of query against it: SELECT * FROM documents WHERE MATCH (content) AGAINST ('demyer Padgham robinson') AND doctype = 'Motion' AND jstate = 'California: State Court' OR jdistrict = 'Circuit Court: Federal, California' SELECT * FROM documents WHERE

RE: Where are the Foreign Keys?

You could also try SHOW KEYS FROM dbname.tablename Should work. Naresh -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 14:06 To: [EMAIL PROTECTED] Subject: Re: Where are the Foreign Keys? Hi Ralf, as far as i know, there is only 1 way to

standard processing of metadata

as far as i know, there is only 1 way to extract the foreign key relations from a table: show create table tablename. Are there any others? MT The comments column in SHOW TABLE STATUS is supposed MT to list the FKs as well. since SQL89 standard lot of things got standartized but still there

Re: Count() within Join

I think what you need is a pivot table (aka: cross tab report): SELECT c.id , c.campaign_name , count(1) as total , SUM(if(a.status='optin',1,0)) as optin , SUM(if(a.status='optout',1,0)) as optout FROM addresses as a INNER JOIN addresses_incampaign as i

RE: Table query and column overlap

Shawn, Thanks for posting about the command window sizes. It's been something that I have frustratingly accepted in silence for a while now (especially having a 20 inch monitor at work).. Hooray!! I can finally get some horizontal real estate!! ..obviously the concept of two whole megabytes of

Re: very large HEAP-tables in 4.1.3

Hi, I changed a few columns, bidsize and asksize are integer now, and i changed ticknumber to smallint unsigned. At first I used the ticknumbers by the feedserver, now I count up to 65,000 and then reset the counter back to 0. I need that additional column to handle multiple ticks within one

Re: Microsoft's ASP/SQL combo

Scott, I think you posted to the wrong group ;-). This is the group for MySQL not MS SQL Server (two totally different products)! You can find MANY examples for making ASP connections from the MSDN site (that's where I got all of mine the first few times I needed to make them work).

RE: Microsoft's ASP/SQL combo

Sorry, Connecting M$ ASP to MySQL is what I meant. (just noticed the misunderstanding statement in subject line. My apologies) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 06, 2004 9:46 AM To: Scott Hamm Cc: 'Mysql ' (E-mail) Subject: Re:

Moving a database

Good morning, What's the best way to move a database and all of its tables from one server to another? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: standard processing of metadata

On Fri, 6 Aug 2004 16:26:53 +0300, DebugasRu [EMAIL PROTECTED] wrote: since SQL89 standard lot of things got standartized but still there are more things to unify. One of them is metadata (system tables that a given DBMS uses) processing. I want to ask if there is going to be any standard on

Re: Where are the Foreign Keys?

From: Naresh Sadhnani You could also try SHOW KEYS FROM dbname.tablename Should work. According to the documentation, this is an alternative for SHOW INDEX FROM tablename A quick try gave me no results for FKs, only PKs and indices. With regards, Martijn Tonies Database Workbench -

RE: Table query and column overlap

Thanks for posting about the command window sizes. It's been something that I have frustratingly accepted in silence for a while now (especially having a 20 inch monitor at work).. Hooray!! I can finally get some horizontal real estate!! +1 ! I think this should be documented somewhere ! I

RE: Microsoft's ASP/SQL combo

What I have here is a basic template of creating and using a DSN-less connection from an ASP page (using VBScript) through ADO through an ODBC driver into a MySQL database. I would refer you to the ADO documentation if you need more specifics about other options (like setting the cursor

Re: very large HEAP-tables in 4.1.3

Philippe Poelvoorde wrote: Hi, I changed a few columns, bidsize and asksize are integer now, and i changed ticknumber to smallint unsigned. At first I used the ticknumbers by the feedserver, now I count up to 65,000 and then reset the counter back to 0. I need that additional column to handle

error while working with MySQL MyODBC and MS Access 97

hi their, i have a problem working with access mysql and myodbc. i ported a access database to mysql. now i had to integrate the mysql db in the old access frontend. i realized this with linked tables. now i have a problem, because myodbc seems to return the wrong results. the relation between

RE: Moving a database

-Original Message- From: David Barron [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 14:56 To: [EMAIL PROTECTED] Subject: Moving a database Good morning, What's the best way to move a database and all of its tables from one server to another? Thanks You can use the

RE: Moving a database

How do I recreate the database on the new server? Do I have to recreate each table again or will the dump file contain that information? -Original Message- From: Mark Pittam [mailto:[EMAIL PROTECTED] Sent: Friday, August 06, 2004 12:06 PM To: David Barron; [EMAIL PROTECTED]

Re: Moving a database

Don't forget to copy the my.cnf file, and make any changes neccesary (due to different directory/disk structures). If you are using InnoDB, and can shut down the database, you should just be able to move the files in data/database name or var/database name like Mark said below (InnoDB stores

RE: Moving a database

-Original Message- From: David Barron [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 17:10 To: Mark Pittam; [EMAIL PROTECTED] Subject: RE: Moving a database How do I recreate the database on the new server? Do I have to recreate each table again or will the dump file contain

Select non-matching fields

This is probably a very easy question for many of you. I have two tables, 'Purchases' and 'Purchased_Items'. For every purchase there is one entry in 'Purchases' and 1 or more entries on 'Purchased_Items' (depending on how many items were purchased'. The two tables are linked by a

Re: very large HEAP-tables in 4.1.3

Hi, A few questions for you. On Friday, August 6, 2004, at 06:17 AM, Jan Kirchhoff wrote: I was just wondering if anybody has been using very large HEAP-tables and if there are ways to have mysql use the memory more efficient: (I have no experience with all heap-tables but using them as

Re: Select non-matching fields

You need a LEFT JOIN: SELECT ticket_number FROM purchased_items LEFT JOIN purchases ON purchased_items.ticket_number = purchases.ticket_number WHERE purchases.ticket_number IS NULL; Michael Rob Best wrote: This is probably a very easy question for many of you. I have two tables,

ERROR 1030 (HY000): Got error 127 from storage engine

Hi, I have done following on a 8 million row table (4GB): repair table optimize table drop one of the fulltext index optimize table again Now fulltext search stop working and I am getting: ERROR 1030 (HY000): Got error 127 from storage engine What does this mean? Index corrupted? How that

Bash Shell issue with the mysqldump's password.....

I am struggling to get the bash script to use the password. What I'm doing here is to assign it to a variable.. --snip- MySQLdump_FilePath=/usr/local/mysql/bin/mysqldump UserId=root DB_Production=DB_NAME #Emarket_Production_Filepath=`/home/website/ProductionDB.sql`

RE: Bash Shell issue with the mysqldump's password.....

This line should do it (no space after -p) Prefix_Production_CmdLine=$MySQLdump_FilePath -u ${UserId} -pmy_password $DB_Production _ Steve Poirier -Original Message- From: Scott Fletcher [mailto:[EMAIL PROTECTED] Sent: August 6, 2004 1:24 PM To: [EMAIL

re: results with utf8 characters

I believe there is already a bug report about this. See if this is the same as your problem: http://bugs.mysql.com/bug.php?id=3453

Re: Select non-matching fields

On Fri, 06 Aug 2004 13:17:42 -0400, Michael Stassen [EMAIL PROTECTED] wrote: You need a LEFT JOIN: SELECT ticket_number FROM purchased_items LEFT JOIN purchases ON purchased_items.ticket_number = purchases.ticket_number WHERE purchases.ticket_number IS NULL; No, actually he

Re: Export and destroy relation

From within MySQL 3? I doubt it. It's pretty easy to do in the language of your choice, though. Here's a version in perl: use DBI; my $dbh = DBI-connect('DBI:mysql:database=test', user, pass); for (@{ $dbh-selectall_arrayref('SELECT * FROM users') }) { my ($id, @user_data) = @{ $_ }; my

Re: Select non-matching fields

Matt, I humbly beg to differ. I believe that what you wrote will be executed as a correlated subquery. That would make your statement much slower than a JOIN (especially if the correct indexes exist). I believe that when the query engine executes your statement, for each row of purchased_items

Re: Select non-matching fields

You're making the assumption that he's using 4.1.x. He didn't state which version he's using so your solution may be of no use to him. -- T --- Matt Warden [EMAIL PROTECTED] wrote: On Fri, 06 Aug 2004 13:17:42 -0400, Michael Stassen [EMAIL PROTECTED] wrote: You need a LEFT JOIN:

RE: ERROR 1030 (HY000): Got error 127 from storage engine

Did you try to repair it again and use myisamchk -r table.MYI Did you check your .err file (log) _ Steve Poirier -Original Message- From: Haitao Jiang [mailto:[EMAIL PROTECTED] Sent: August 6, 2004 1:21 PM To: [EMAIL PROTECTED] Subject: ERROR 1030

Customizing character processing for fulltext indexing?

Is there a way to customize the way that MySQL treats various characters when it builds a fulltext index? I am using version 4.0.18. I would like to treat a . as part of a word, rather than as a separator character. Ideally, I would like to set this for just one index of one table. I have a

Mixing Innodb MyISAM tables

Im considering a design that mixes InnoDB and MyISAM tables. I want Innodb for speed, etc, but i have one table where i want a column to have a FULLTEXT index on. Thus the need for MyISAM. Im not worried about the performance in using the MyISAM tables, as we speak, the production version of

Re: Select non-matching fields

I believe that when the query engine executes your statement, for each row of purchased_items data it looks at, it will have to run the query SELECT ticket_number FROM purchases scan those results and determine if the current row matches. If it is in the list then it wil exclude that row

how to add time to NOW() function

i want to add time to NOW() function, that means something like this, $addTime = 60; NOW() + $addTime; where 60 are seconds. I am not sure whether to add seconds or is there any other format I can use to add time to NOW() function? please let me know. thanks in advance. deepak -- MySQL General

Re: how to add time to NOW() function

Your options depend heavily on what version of MySQL you are running. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Deepak Dhake [EMAIL PROTECTED] wrote on 08/06/2004 04:28:20 PM: i want to add time to

RE: how to add time to NOW() function

Use DATE_ADD(NOW(),INTERVAL 1 HOUR) Or DATE_ADD(NOW(),INTERVAL 60 MINUTE) Or DATE_ADD(NOW(),INTERVAL 3600 SECOND) -Original Message- From: Deepak Dhake [mailto:[EMAIL PROTECTED] Sent: Saturday, August 07, 2004 1:58 AM To: [EMAIL PROTECTED] Subject: how to add time to NOW() function

Re: Insert problems with InnoDB (big table)

Harrison wrote: Hi, sorry about the long delay in the reply. I will be away for the next 2 weeks, but I will follow this thread if anything new comes up. Hi, A few more ideas you can try: 1. SET UNIQUE_CHECKS=0; You have a unique key that is quite large (model, id name). If you know the

Re: using mysql in commercial software

Greetings All, LinuxWorld is now finished (meaning that some of the key MySQL people who need to be in on these discussions will be back to a more normal working setup) and the weekend is more or less here. I hope to pick up discussions on these issues with the other MySQLers on Monday (or to

Seqment fault using a 3.23.59 client with a 4.0.16 server.

I have a program that uses the C API linked statically with the mysqlclient library. Something in the MySQL client version 3.23.59 is causing a problem with memory. Subsequent calls to malloc fail with a seqment fault. My program works if: A) The server is 3.23.59. or B) My program doesn't

Server will only start manually when logged in as root with 4.1.3

I'm unable to get the 4.1.3 server to start automatically at startup or even manually unless I'm logged in as root. I'm on an iBook G4. I downloaded and installed the mysql-standard-4.1.3-beta-apple-darwin7.4.0-powerpc binary and the associated startup item. I've done this successfully with

Re: using mysql in commercial software

At 11:24 AM 8/2/2004, you wrote: On Mon, 2 Aug 2004 01:35:44 -0700, Jeremy Zawodny [EMAIL PROTECTED] wrote: On Mon, Jul 26, 2004 at 01:26:15PM -0500, gerald_clark wrote: Steve Richter wrote: exactly! Is Linux distributed under the same type of license as MySql? If I sell software

Uninstalling MySQL

Greetings... How do I uninstall MySQL under linux (i have Fedora Core 2)... i will be installing a new version of it... I have 3.23 currently and will replace it with 4. Please Help Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Re: Uninstalling MySQL

On Fri, 2004-08-06 at 20:49 -0400, [EMAIL PROTECTED] wrote: Greetings... How do I uninstall MySQL under linux (i have Fedora Core 2)... i will be installing a new version of it... I have 3.23 currently and will replace it with 4. Please Help Thanks You dont necessarily have to uninstall it,

What would you store in a BLOB field?

I know the blob field is binary but what would you store there? and if you could give me an example of real-life uses please.

Finding Database and Tables

I have mysql installed on a pc running Windows 98. I recently had to reformat a partitioned c drive. My tables were stored in a database called samp_db. When I give the command use samp_db I get Error 1044: Access denied for user: '@localhost' to database 'samp_db' When I give the

Re: What would you store in a BLOB field?

In the last episode (Aug 06), Levi Campbell said: I know the blob field is binary but what would you store there? and if you could give me an example of real-life uses please. Say you want to have multiple remote webservers all serving the same data. Create a table with filename, mtime, and

Re: What would you store in a BLOB field?

Another use for binary columns is for case-sensitive text such as passwords. If you store text in a CHAR, VARCHAR, or TEXT column, comparisons will not be case-sensitive unless you use the BINARY keyword. An example (from section 6.3.22 of the Language Reference: mysql SELECT a = A;

Re: What would you store in a BLOB field?

I'm not sure if anyone has mentioned this - I only see part of this thread - but music is something else you can store in a BLOB. As an experiment, I stored a photograph (in JPEG format) and a music file (in MIDI format), each in their own BLOB columns and then fetched them out again to display

Re: What would you store in a BLOB field?

Another use of BLOB fields would be to store things like fingerprints or retina scans, which could be very useful if you have a security-oriented table that needs to store biometric data. I should note that I have never actually stored biometric data or worked with it but I saw an overhead once

Re: using mysql in commercial software

Hi Mike, On Aug 06, 2004, at 18:08, mos wrote: This means that, according to the most common interpretation of the GPL, just linking with them automatically requires your code be under the GPL. License: The GPL license is contagious in the sense that when a program is linked to a GPL program, all