Re: Changing data types in mysql!

2008-01-23 Thread Sebastian Mendel
Lenin Lakshminarayanan schrieb: Hello, I was pulling data from one datasource [ oracle ] earlier which had a couple of fields as integer. Now i am moving to a newer data source and the same fields are now varchar's in the newer oracle database. I am planning to change the data types of those

Re: adding then removing index produces different query results

2008-01-23 Thread Sebastian Mendel
mysql mysql schrieb: Can anyone explain the following? I encountered the following very strange behaviour while attempting to optimize a query (more details are provided later on for those interested): 1) execute query takes 2 minutes 2) add index 3) execute same query takes 11 seconds 4)

RE: Backing up via slave

2008-01-23 Thread Martijn van den Burg
Ananda, My apologies for the late reply. To answer your question: I use 'show slave hosts'. It returns: +---+---+--+---+---+ | Server_id | Host | Port | Rpl_recovery_rank | Master_id |

Killing resource hogs - automatically

2008-01-23 Thread Martijn van den Burg
Hi, My DEV server is used by many people creating apps throughout the company. Consequently, I have no insight in the efficiency or quality of their SQL. This is an accepted state of affairs. However, 'developers' have repeatedly managed to crash mysqld because their queries are huge, don't use

Pass Reference to source table in Stored Procedure - How??

2008-01-23 Thread roger.maynard
Hi Is there any way I can pass the reference to a source table to be used in a SELECT command within a Stored Procedure Something like this CREATE PROCEDURE `myDB`.`sp_test` (myTable varchar(20)) BEGIN SELECT * FROM myTable; END $$ This gives error cannot find Table

Fwd: Killing resource hogs - automatically

2008-01-23 Thread Jan Pieter Kunst
2008/1/23, Martijn van den Burg [EMAIL PROTECTED]: Hi, My DEV server is used by many people creating apps throughout the company. Consequently, I have no insight in the efficiency or quality of their SQL. This is an accepted state of affairs. However, 'developers' have repeatedly managed to

Re: Killing resource hogs - automatically

2008-01-23 Thread Jan Pieter Kunst
No, I mean: tuning the mysql server variables in the /etc/my.cnf configuration. See e.g.: http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html JP 2008/1/23, Martijn van den Burg [EMAIL PROTECTED]: JP, Interesting option. You mean like re-nice-ing mysqld on the host (Linux) level?

Upgrading MySQL 4.0 to 5.0

2008-01-23 Thread John Pacylowski
Has anyone upgraded MySQL 4.0 to 5.0 on a Mac running Mac OS X Panther, 10.3.9 Sever with Lasso 8.5.4? I'm debating whether to just upgrade to MySQL to 5.0 or jump to Apples Leopard Server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Re: MySQL configured limits

2008-01-23 Thread Mark Kozikowski
Thanks for the responses. I am sorry I cannot give more detailed information now. The system is being run through tests for other issues. But I can say that we are using a my.cnf that contains only this one line: set-variable=max_connections=300 There are a few processes making queries to the

RE: Changing data types in mysql!

2008-01-23 Thread Jerry Schwartz
-Original Message- From: Lenin Lakshminarayanan [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 6:54 PM To: mysql@lists.mysql.com Subject: Changing data types in mysql! Hello, I was pulling data from one datasource [ oracle ] earlier which had a couple of fields as

RE: Did NOT condition on VARCHAR change with 5.0.45?

2008-01-23 Thread Jerry Schwartz
-Original Message- From: Mont McAdam [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 7:18 PM To: mysql@lists.mysql.com Subject: Re: Did NOT condition on VARCHAR change with 5.0.45? `method` is the name of the column in the table. In my opinion it should return every

Using Variables in Insert

2008-01-23 Thread Kc9cdt
Dan, Thanks very much for responding to my problem post. I was hoping I was just missing something in the syntax. Looks like I made a very big mistake recommending MySql to my Client. I have hundreds of SQL statements in the code I am converting for them. It is C code in IBM DB/2 running on OS/2

Newbie question, how do I respond to post?

2008-01-23 Thread Kc9cdt
Hello, I feel stupid here... How do I respond to a specific post? I posted last night, I got a response... now I want to post a response to the response. Don't see a way to do that... Thanks, Lee ** Start the year off right. Easy ways to stay in shape.

RE: Newbie question, how do I respond to post?

2008-01-23 Thread David Ruggles
Either use reply to all, or manually enter the list email address in the to field. There are two general feelings about list operation: 1) all replies should go to the list 2) all replies should default to the sender This list is configured as #2 (I prefer #1 myself) Thanks, David Ruggles CCNA

RE: Pass Reference to source table in Stored Procedure - How??

2008-01-23 Thread Rolando Edwards
What you need is Dynamic SQL via the PREPARE statement Like This : CREATE PROCEDURE `myDB`.`sp_test` (myTable varchar(20)) BEGIN DECLARE SQLCommand VARCHAR(1024); SET SQLCommand = CONCAT('SELECT * FROM ',myTable); SET @SQLStmt = SQLCommand; PREPARE s1 FROM @SQLStmt; EXECUTE s1;

Re: Did NOT condition on VARCHAR change with 5.0.45?

2008-01-23 Thread Perrin Harkins
On Jan 22, 2008 7:18 PM, Mont McAdam [EMAIL PROTECTED] wrote: In my opinion it should return every record from the table where `method` is equal to NULL, ZERO or EMPTY STRING, as this table doesn't allow null, and the field type is varchar, this would be EMPTY STRING's only. Although it

Introduction encoding woes latin1 vs. utf8

2008-01-23 Thread Niki Kovacs
Hi, I'm an Austrian sysadmin living in Montpezat (South France). I'm a 100% GNU/Linux user since 2001, and for the last two years, I've been working as a sysadmin (that's a big word) for our Communauté de Communes, a group of 16 small towns and villages, where my job is mainly to install a

RE: Pass Reference to source table in Stored Procedure - How??

2008-01-23 Thread roger.maynard
Perfect!! Thanks a million. -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: 23 January 2008 15:45 To: roger.maynard; mysql@lists.mysql.com Subject: RE: Pass Reference to source table in Stored Procedure - How?? What you need is Dynamic SQL via the PREPARE

MySQL and MS SQL Server 2005

2008-01-23 Thread J Trahair
Hi Everyone I am now familiar with connecting with MySQL using Visual Basic 6 and ADO. I am about to start a new project which must connect to a customer's MS SQL Server 2005 installation. How safe/unsafe is my assumption that as far as SQL strings and ADO connections are concerned 'if it

Re: Using Variables in Insert

2008-01-23 Thread Dan Nelson
In the last episode (Jan 23), [EMAIL PROTECTED] said: In a message dated 1/23/2008 2:02:46 AM Eastern Standard Time, [EMAIL PROTECTED] writes: http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-execute.html Dan, Thanks very much for responding to my problem post. I was hoping I was just

Re: MySQL and MS SQL Server 2005

2008-01-23 Thread Olexandr Melnyk
Most of the basic statements should work fine on both, but here are several points you should keep in mind: - SQL Server doesn't support LIMIT clause; - Don't use quotes for numerical values as it works on MySQL only; - In GROUP BY clause, list all not aggregated columns you are selecting; - Use

Help with MySQL Query (2 Outer joins)

2008-01-23 Thread Raghuveer Rawat
Hi, I need some urgent for sql query.. It will be great if someone could help me.. I have ARTICLE, FAVORITE_ARTICLES, RATING Tables apart from other table USER, CHANNEL, CATEGORY etc ARTICLE table stores a user's article, FAVORITE_ARTICLES will store a user's favorite articles, and rating

executing query from the command line -- need help

2008-01-23 Thread Brown, Charles
Hello All. I'm new to mysql. I would like to issue a query from the command line and pass the result to an update done on the command line within the same script. See below. My question is how can I run a select from the command line and pass the values to an update SELECT SYS_ID, SYS_LOCATION,

RE: executing query from the command line -- need help

2008-01-23 Thread Jay Blanchard
[snip] I'm new to mysql. I would like to issue a query from the command line and pass the result to an update done on the command line within the same script. See below. My question is how can I run a select from the command line and pass the values to an update SELECT SYS_ID, SYS_LOCATION,

RE: executing query from the command line -- need help

2008-01-23 Thread Jay Blanchard
[snip] SELECT SYS_ID, SYS_LOCATION, SYS_IP FROM PROD_SERVER; UPDATE TEST_SERVER SET SYS_ID = value passed from above SYS_LOCATION = value passed from above SYS_IPADDRESS = value passed from above; [/snip] And here http://dev.mysql.com/doc/refman/5.1/en/user-variables.html --

Re: MySQL and MS SQL Server 2005

2008-01-23 Thread J Trahair
What about DROP TABLE IF EXISTS TableName and CREATE TABLE NewTableName... and ALTER TABLE TableName ADD COLUMN DeliveryNoteNumber INT NOT NULL DEFAULT 0 Thanks Jonathan Trahair Most of the basic statements should work fine on both, but here are several points you should keep in mind: -

Re: MySQL and MS SQL Server 2005

2008-01-23 Thread Olexandr Melnyk
SQL Server doesn't support the IF EXISTS clause. There are also differences in data types. On 1/23/08, J Trahair [EMAIL PROTECTED] wrote: What about DROP TABLE IF EXISTS TableName and CREATE TABLE NewTableName... and ALTER TABLE TableName ADD COLUMN DeliveryNoteNumber INT NOT NULL DEFAULT

mysql replication

2008-01-23 Thread Naufal Sheikh
Hello, Just a small question. I had mysql replication configured on my master and slave server. Due to some issues ( when I was on vacations ), logging on master server was switched off and server restarted. Now my question is that in order to resysnc my master and slave correctly, do I need to

Re: mysql replication

2008-01-23 Thread B. Keith Murphy
Naufal, You probably need to start with a clean slate. Do a complete resync and start the slave back up from a known stopping point. Keith Naufal Sheikh wrote: Hello, Just a small question. I had mysql replication configured on my master and slave server. Due to some issues ( when I was

INSERT WHERE NOT EXISTS syntax

2008-01-23 Thread roger.maynard
Can anyone tell me why this isn't working... v5.0 INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS (SELECT comment_no FROM master_comments); I thought I had it working once but now it isn't?

Re: Newbie: A single number

2008-01-23 Thread Joerg Bruehe
Hi Mário, all ! Mário Gamito wrote (re-ordered): Sebastian Mendel wrote: Mário Gamito schrieb: Hi, I'm trying to get a single number out of a SELECT statement: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by

RE: INSERT WHERE NOT EXISTS syntax

2008-01-23 Thread roger.maynard
I think I sorted it out ... INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS ( SELECT comment_no FROM master_comments WHERE mComments.comment_no = master_comments.comment_no ); Hope this helps someone

how to re-index a table?

2008-01-23 Thread Lamp Lists
hi, I have table, something like: create table example ( ex_id integer(8) not null auto_increment primary key, ex_col1 int(8) null, ex_col2 int(4) not null, index(ex_col1), index(ex_col2) )engine=myisam; ex_col1 and ex_col2 are indexed

Re: MySQL configured limits

2008-01-23 Thread Mark Kozikowski
OK, got another piece of information. I was running with the same 'default' mysql configuration, but with a different data set. when the mysql database table reached 7,285,902 records mysql dropped the connection. When I tried to reconnect, the mysql library core dumped. Interesting note is

RE: executing query from the command line -- need help

2008-01-23 Thread Jay Blanchard
[snip] Hello Jay. Thanks for your reply but where is your solution to my problem. I'm lost here. Help me -- please Thx -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 11:29 AM To: Brown, Charles; mysql@lists.mysql.com Subject: RE:

failed queries

2008-01-23 Thread John Roddy
I know that successful, data changing queries get logged in the binary log. But is there any way to see the actual queries that fail (i.e. timeouts, deadlocks)? I'm hoping there's a way other than using the general query log, which takes up too much space just to catch that occasional failed

Re: executing query from the command line -- need help

2008-01-23 Thread obed
On Jan 23, 2008 2:36 PM, Jay Blanchard [EMAIL PROTECTED] wrote: [snip] Hello Jay. Thanks for your reply but where is your solution to my problem. I'm lost here. Help me -- please Thx -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23,

RE: executing query from the command line -- need help

2008-01-23 Thread Brown, Charles
thanks -Original Message- From: obed [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 10:49 PM To: Jay Blanchard Cc: Brown, Charles; mysql@lists.mysql.com Subject: Re: executing query from the command line -- need help On Jan 23, 2008 2:36 PM, Jay Blanchard [EMAIL PROTECTED]

Re: adding then removing index produces different query results

2008-01-23 Thread mysql mysql
Thanks for the response Chris, although I can't seem to reproduce the problem now, but I'm sure you're right. There's something else strange that I've encountered while trying to optimize this query. I've got two machines, dev and production. After adding the index to the title attribute on the