how to use mysql client source command through DBI/DBD

Hello All, I am trying to create a perl DBI/DBD script for creating a database initial build. My input is a extract file, which is a mysqldump result file with --opt and -B option. I am using DBIx::DWIW. I am able to open a successful database handler. I am having code like my $dropsql="DR

Re: How to remove an index

alter table drop index For details http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html Aman Raheja leegold wrote: I want to remove the index I made below. I intend to then redo it because I forgot to add a third field. How do I remove this index? Alter...? Thanks. mysql> ALTER TABLE page -> A

Re: Script question

Thanks, Charles! Your suggestion about removing the quotes and apostrophes altogether did the trick! Rhino - Original Message - From: "Sweet, Charles E" <[EMAIL PROTECTED]> To: "mysql" <[EMAIL PROTECTED]> Sent: Monday, October 18, 2004 9:22 PM Subject: RE: Script question Brian

How to remove an index

I want to remove the index I made below. I intend to then redo it because I forgot to add a third field. How do I remove this index? Alter...? Thanks. mysql> ALTER TABLE page -> ADD FULLTEXT (title), -> ADD FULLTEXT (descrip), -> ADD FULLTEXT (title, descrip); Query OK, 1 row af

Re: Table Lock Delays and Connection Pooling

Every new connection is considered a client. It's a bad idea to try to do your own scheduling client side to try to defeat table locks because MySQL can proceed with other clients as soon as the locks are freed vs your application waiting for a complete result set to return before proceeding with a

first day of week/month

Hi, I know this might be a little silly, but can anyone give me a example on how to get the date of the first day of a week and month? -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Script question

No, the system command is part of the mysql interpreter, do a \h and see if it's listed, if it isn't then there is only one other way to do it. Split your sql script up into several pieces and have a shell script call it like this: mysql script1.sql echo here's some output mysql script2.sql echo h

RE: Script question

Brian wrote: > try: > system "echo hi there"; > without the single quotes :) > > > On Mon, 18 Oct 2004 21:04:19 -0400, Rhino <[EMAIL PROTECTED]> wrote: >> I thought you'd nailed it when I read your note but I found that >> your suggestion didn't work. >> >> When I tri

Re: Script question

I got basically the same error: sh: line 1: echo Hi there: command not found I know that echo works; I can do: echo "Hello" or echo 'Hello' on the command line and the result is Hello. However, when I do this on a command line: system I get: -bash: system: command not found The problem s

Re: Script question

try: system "echo hi there"; without the single quotes :) On Mon, 18 Oct 2004 21:04:19 -0400, Rhino <[EMAIL PROTECTED]> wrote: > I thought you'd nailed it when I read your note but I found that your > suggestion didn't work. > > When I tried executing the script with this added to it: > > syste

Re: Script question

I thought you'd nailed it when I read your note but I found that your suggestion didn't work. When I tried executing the script with this added to it: system "echo 'hi there'"; I got this on the command line: sh: line 1: echo 'hi there': command not found I tried doing a man system and got an a

Re: Script question

If you are on an *nix system you can try \! echo Hello > test.txt Rhino wrote: Sorry, you still haven't got it;-) I want the echo command to be in the *script*, not to be supplied at the command line. Is there some way to put a mix of MySQL commands and OS commands into a script and have both execu

RE: Script question

The only way to mix and match with an .sql script would be to use the system command. The example I showed below would echo the text required and that would be diverted to Load.out file eg. -- use SFL; system "echo 'my choice of text'"; system "cat /etc/passwd"; system "cat afilethath

Fatal Error on db

I am using php/mysql for a program and everything installed fine but when I try to open it up I get this error: Fatal Error : Couldn't find local config file. File Name : /program/admin/index.php Error Code : err01 Time :18 Oct 2004, 07:19:44 pm PHP Fatal error: Call to undefined function: m

Re: Script question

Sorry, you still haven't got it;-) I want the echo command to be in the *script*, not to be supplied at the command line. Is there some way to put a mix of MySQL commands and OS commands into a script and have both executed successfully via: mysql -u myid -pmypass < Load.sql > Load.out Rhino -

Re: many fields or many tables? (Understanding DB design)

On Mon, 18 Oct 2004 17:49:22 -0400, Timothy Luoma <[EMAIL PROTECTED]> wrote: > > Ok, this makes a lot of sense now. (As usual, what seems like more > work initially pays off in the end.) > > Here's a specific question. > > The parent project is called "TiM". We will, at times, want to pull > o

RE: Script question

After re-reading your email in not so quite a rush 8-) Further to that, you can use the system command to run an OS command eg. system "echo thing"; mysql> system echo "thing"; thing mysql> Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5

Table Lock Delays and Connection Pooling

Hi all , I have a quick question regarding table locking. This is a snippet referring to when table locking is disadvantageous: "Another client issues another SELECT statement on the same table. Because UPDATE has higher priority than SELECT, this SELECT will wait for the UPDATE to finish. It

RE: Script question

Hi Rhino, This works, however I can't remember how to suppress the column heading. If you can do that, you can put anything you like in. [EMAIL PROTECTED] loganda]$ echo "set @thing='here we go';select @thing"| mysql -u root -p >outfile Enter password: [EMAIL PROTECTED] loganda]$ cat outfile @t

Script question

  Can anyone tell me how to make a script containing MySQL commands also execute an OS command?   For instance, given this script, called Load.sql:   -- use SFL;   #Load the data from the export file that was exported from Q&A as a standard#ASCII file.load data infile '/home/rhino

Is it possible to export column headings with data?

I am using SELECT statements to write some files via INTO OUTFILE. This has been working fine, but now I have the need to export the column headings from the MySQL tables along with the column contents. Is this possible? If so, how? Ex. SELECT stock, vin, color, mileage, price, cylinders, p

Preserving backslashes in DML

What are the options available for inserting\updating a MySQL table VARCHAR with a string containing backslash characters so that the backslash characters are preserved as is? For example, the UNC string '\\MyServer\MyDir ' would be changed on the way in to the VARCHAR to become '\MyServerMyDir'.

Re: Which Filesystem to choose?

[EMAIL PROTECTED] wrote: Hi, I'm trying to set up a new Opteron-Based MySQL-Server. The only thing I'm unsure about is which filesystem to choose.. ext3? ReiserFS? XFS? What's your experience? Thanks chris I've found XFS to be painfully slow. I haven't tried ext3. I have used reiser3 on our se

Complex update query

Hi, I don't know if this is possible ( my experience with SQL is very short) but I need to update table A from table B but saving the "Old values" from certain columns into table C. In other words I have 3 tables A,B,C. Table A is going to be updated with values from table B, but I have to kee

Re: many fields or many tables? (Understanding DB design)

Ok, this makes a lot of sense now. (As usual, what seems like more work initially pays off in the end.) Here's a specific question. The parent project is called "TiM". We will, at times, want to pull out information for *everyone*. But more often we will want to pull out information just fro

Re: many fields or many tables? (Understanding DB design)

Timothy, Definately follow the advice that Shawn gave you. Doing it this way will make it easy to have any number of emails per person without have to know how many beforehand . Here's an example below: Table USERS: userid=15 fname='Timothy' lname='Luoma' Table EMAILS: userid=15 email='[EMAIL

Guest login permissions

I have been working to configure MS Access 2000 as a front-end to an application running MySQL 4.0.20-standard. Our thinking was to have a guest login with minimal privileges (Select only) setup as the default on each users PC. This would allow anyone on the team to access the DB using the front

Re: INSERT on duplicate UPDATE?

Hi! On Sep 14, Yves Goergen wrote: > Hi, > I can vaguely remember there was something like "INSERT... on duplicate > key UPDATE..." in MySQL, but the documentation search is almost as > useful as I'm used to - it cannot tell me anything about this. Can you > please? How does this work, what's t

Re: Table name aliases in FULLTEXT and table locking

Hi! On Oct 05, Ville Mattila wrote: > Hi there, > > I have noticed a few things that cause problems when using table aliases > (SELECT ... FROM table1 t1, table2 t2): > > 1) Fulltext index queries don't work. I tried to complete a following query: > > a) > SELECT p.*, c.name AS categoryname FR

Re: many fields or many tables? (Understanding DB design)

There are numerous advantages to going with the "multiple" table database you described. That is what we call a "normalized" data structure. Try searching again for terms like "normalized", "normalizing", and "normal form" for additional background. Add the terms "tutorial", or "overview" to fi

many fields or many tables? (Understanding DB design)

I have been tinkering with MySQL long enough to suit what modest needs I have had, but now I need to setup a new DB that is going to have more information in it, and I want to make sure that I am doing it the most efficient way for the long term. Surprisingly, I have not been able to find a goo

how to optimize multiple many-to-many relationship related query

I have, to make it clear, 2 many-to-many relationships for table person: PersonPerson_Club ClubPerson_Creditcard CreditCard ----- - - -

Blob question

I'm just getting into the whole MySql (was using access). Is it better to create a Blob type and insert a image into it, or to create a char file type and have a directory structure to the specific file? How big of files to the different Blob's hold? And if I'm using Coldfusion, will that sup

Unable to start chrooted 5.0 server.

Hi everyone! I'm trying to start a jailed mysql-max (snapshot 5.0) inside a chrooted environment, and is not working. I'm not using the --chroot parameter, because I want to get this implementation fully isolated to run another (stable) version in the same server, without getting in trouble. T

Re: Date as Primary ID

Okay, but you are measuring the projected number of units of *something*, right? The number of widgets made, the number of gadgets sold, the number of x-rays taken or whatever. I think you need that *something* as a column in the table. Hmm. On second thought, if you are only making or selling a s

Re: MySQL TMPDIR.

Yes. RV Tec wrote: Clark, MySQL will stop processing the current query until space becomes available. It will then continue. Stop processing the current query means locking it all, and everything else has to wait for this query to be completed?! Thanks for your reply! Best regards, RV Tec

Re: Adding DSN into Coldfusion Admin?

Just the fact that you GOT the gibberish meant that the server was running and that you were able to connect. What you saw was a "Hello" packet. It's one of the first stages to authenticating a MySQL client to a MySQL server. It's not meant for humans. Shawn Green Database Administrator Unimin

Re: Transfering data from postgresql to MySQL

Hello, You can use SQLyog's ODBC Import Tool to import data from PgSQL to MySQL using PgSQL's ODBC driver. More information about SQLyog can be found at http://www.webyog.com Karam --- Jochem van Dieten <[EMAIL PROTECTED]> wrote: > On Mon, 18 Oct 2004 18:08:24 +0800, 謝洪恩 > Patrick Hsieh wrote:

MySQL/InnoDB-4.1.6 has been released

Hi! InnoDB is a MySQL table type which supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 4.1.6 is mainly a bugfix release. We do not yet declare MySQL/InnoDB-4.1 stable, because q

Re: tree structure

Thank you very much, I made my search with too few words... :-) the modified pre-order tree transversal seems interesting, but if I want to modify one element I have to go through the whole table to re-index everythingand I would like to avoid to use recursive methods.. I'm currently using a

RE: Date as Primary ID

My objective is to set up the projected estimate for day by day basis in ahead of time, then fill in the actual value when time comes. If today is Jan 3rd, and we know yesterday's values, then, Month Day ProjActual Eff Jan 1 11,0

Re: Date as Primary ID

- Original Message - From: "Scott Hamm" <[EMAIL PROTECTED]> To: "'Mysql ' (E-mail)" <[EMAIL PROTECTED]> Sent: Monday, October 18, 2004 10:11 AM Subject: RE: Date as Primary ID > Ok, so that means what Jay Blanchard just now said recently wasn't possible? > I'm trying to figure out a way

Re: tree structure

On 18 Oct 2004 at 15:27, Melanie wrote: > Hi, > Does anybody has a simple idea to store a tree structure under mySQL 4.1.5? > I have one table with id, familyType,superFamily,family, for example: Hi, This tutorial (PHP + MySQL) shows a few different methods: http://www.sitepoint.com/article/hie

MySQL 4.1.6-gamma with Jetspeed 1.5 problem

Hi all, I have successfully used MySQL 4.1.1-alpha and MySQL 5.0.0a-alpha with jetspeed 1.5. When I try to use MySQL 4.1.6-gamma with jetspeed 1.5 I get following exception when I try to login (The first page (anon user) is showing up correctly): java.sql.SQLException: There were 7 rows updated

Re: Date as Primary ID

Hi Scoot, I do my table structure like that CREATE TABLE `test_table` ( `timestamp` date NOT NULL default '-00-00', `data` varchar(5) NOT NULL default '', PRIMARY KEY (`timestamp`), KEY `timestamp` (`timestamp`) ) TYPE=MyISAM; Than I send a query as below INSERT INTO `test_table` (

tree structure

Hi, Does anybody has a simple idea to store a tree structure under mySQL 4.1.5? I have one table with id, familyType,superFamily,family, for example: +-+---++ | id | familyId | accession | +-+---++ | 287 |22 | . | | 288 |

RE: Date as Primary ID

Ok, so that means what Jay Blanchard just now said recently wasn't possible? I'm trying to figure out a way to ensure that date is not duplicated i.e.: create performance ( `DateID` whatever, `Projected_Num` int(7), `Actual_Num` int(7), primary key [something to pre

Re: MySQL TMPDIR.

Clark, > MySQL will stop processing the current query until space becomes > available. It will then continue. Stop processing the current query means locking it all, and everything else has to wait for this query to be completed?! Thanks for your reply! Best regards, RV Tec -- MySQL General

Re: MySQL TMPDIR.

RV Tec wrote: Folks, A couple of months ago I asked this question, and there was no answer, since then I couldn't find a reasonable answer or a way to find it myself. What happens if MySQL runs out of space at TMPDIR? I guess I could use the performance improvement of redirecting that to a MFS DIR

Re: Slow query?

What indices do you have? What does explain say? Richard Reina wrote: Anyone know why this query takes so long? SELECT SUM(l.cost+l.fscc) FROM orders o, acctg.invoice i LEFT JOIN acctg.payable p ON (o.ORD_NO=p.ORD_NO) WHERE p.ORD_NO IS NULL AND i.ORD_NO=o.ORD_NO; Is there something I can do to sp

MySQL TMPDIR.

Folks, A couple of months ago I asked this question, and there was no answer, since then I couldn't find a reasonable answer or a way to find it myself. What happens if MySQL runs out of space at TMPDIR? I guess I could use the performance improvement of redirecting that to a MFS DIR. The only pr

Re: Date as Primary ID

Scott Hamm wrote: How do I create table that uses timestamp in -dd-mm format as primary id (no duplicates)? You can't. Timstamps are only unique down to 1 second. It is quite possible to insert hundreds, if not thousands of records in that length of time. -- MySQL General Mailing List For

Slow query?

Anyone know why this query takes so long? SELECT SUM(l.cost+l.fscc) FROM orders o, acctg.invoice i LEFT JOIN acctg.payable p ON (o.ORD_NO=p.ORD_NO) WHERE p.ORD_NO IS NULL AND i.ORD_NO=o.ORD_NO; Is there something I can do to speed it up? Thanks, Richard -- MySQL General Mailing List For list

RE: **[SPAM]** Date as Primary ID

[snip] How do I create table that uses timestamp in -dd-mm format as primary id (no duplicates)? [/snip] CREATE TABLE `tblFoo` ( `timeID` timestamp(14) NOT NULL, PRIMARY KEY (`timeID`), ) TYPE=MyISAM; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubs

Date as Primary ID

How do I create table that uses timestamp in -dd-mm format as primary id (no duplicates)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: commit or rollback?

At 11:42 +0100 10/18/04, Colm G. Connolly wrote: Hi all, I'm working with tables stored by the InnoDB engine and would like to be able to commit only if there are no errors generated by a group of statements like this. /* -*- sql -*- */ SET AUTOCOMMIT=0; use db1; begin work; sql statement 1; sql st

Re: commit or rollback?

Colm G. Connolly wrote: Hi all, I'm working with tables stored by the InnoDB engine and would like to be able to commit only if there are no errors generated by a group of statements like this. /* -*- sql -*- */ SET AUTOCOMMIT=0; use db1; begin work; If you specify Begin or Start Transaction, set a

just testing, sorry.

pls delete it regards, tibyke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: commit or rollback?

I'm relatively new to all of this but just about finished setting up a transaction myself. I'm doing something like this: this is in php:( i also have functions set up for "begin", "rollback" and "committ". You should also set autocommitt to 0 . Hope this helps! Stuart function run_query($sql

commit or rollback?

Hi all, I'm working with tables stored by the InnoDB engine and would like to be able to commit only if there are no errors generated by a group of statements like this. /* -*- sql -*- */ SET AUTOCOMMIT=0; use db1; begin work; sql statement 1; sql statement 2; . . . sql statement n; At this poin

Re: Transfering data from postgresql to MySQL

On Mon, 18 Oct 2004 18:08:24 +0800, èææ Patrick Hsieh wrote: > > I am planing to transfer data from postgresql to mysql. Is there any > useful tools, scripts or utilities to achieve this? pg_dump First dump the schema, edit that until you have something MySQL understands. Then dump the data usi

Transfering data from postgresql to MySQL

Hello list, I am planing to transfer data from postgresql to mysql. Is there any useful tools, scripts or utilities to achieve this? Any infomation is highly appreciated! --- Patrick Hsieh(èææ) <[EMAIL PROTECTED]> MSN: [EMAIL PROTECTED] | ICQ: 97133580 Skype: pahud_at_pahud.net | YIM: pahu

monthly average for last 12 months?

Greetings, I have a table with these columns: Item_Description (varchar) date_purchased (-mm-dd) amount (float) I have managed to get the total amount for each month, formatted in this way: 2001-011000 2001-02 540 2001-031288 and so on... I need to add a third colu

Re: COUNT Problem

> >A subselect may help: > >[...] > >Don't know ATM if it can be done more easily, but a query like this > >should probably work. > > > It can be done without a sub-query: > [...] > > That *should* work, barring any typos or ommisions I may have made. I > used LEFT JOIN because of personal prefere

ANN: Database Workbench 2.5.5 released

Features and fixes: http://www.upscene.com/news/20041018.htm Database Workbench supports: - Borland InterBase ( v4.x - v7.x ) - Firebird ( v1.x ) - MS SQL Server/MSDE ( v6.5, 7, 2000, MSDE 1 & 2 ) - MySQL 4, 4.1 If you experience any problems with this new version, don't hestitate and eit

Re: Unable to Start MySQL on FreeBSD4.10 box

Lynette Tillner wrote: I'm setting up a development box with FreeBSD 4.10 and installed MySQL 4.0.12 on it. Everything in the install appeared to work smoothly. However, when I go to start MySQL I get an error that says: database list could not be retrieved So, how do I fix this? I've been unab

Varchar and InnoDB

Hello, I have a big table in my InnoDB database (more than 10 million rows) and it contains a column of varchar(40) type. For some reason I need to extend this column to varchar(160) but I don't expect to use the whole capacity of this column, except for several rare cases. My question is, after I

Which Filesystem to choose?

Hi, I'm trying to set up a new Opteron-Based MySQL-Server. The only thing I'm unsure about is which filesystem to choose.. ext3? ReiserFS? XFS? What's your experience? Thanks chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.m