Re: Convert SQL Server Compact data to MySQL
Unfortunately they don't have MySQL Migration Toolkit for Ubuntu --- On Thu, 4/5/12, Pothanaboyina Trimurthy wrote: From: Pothanaboyina Trimurthy Subject: Re: Convert SQL Server Compact data to MySQL To: "sam" Date: Thursday, April 5, 2012, 4:56 AM hello sam you can convert that file into mysql using mysql migration toolkit. you can download that one from mysql web site, dev.mysql.com please try that one. On 4/5/12, sam wrote: > Hello, > > I have data file written in SQL Server Compact.I need to Convert it to > MySQL. > > Does anyone know how to do that? > > Thank you, > > Sam > > -- 3murthy
Convert SQL Server Compact data to MySQL
Hello, I have data file written in SQL Server Compact.I need to Convert it to MySQL. Does anyone know how to do that? Thank you, Sam
Re: from excel to the mySQL
visit this site : http://www.daniweb.com/forums/thread119197.html --- On Tue, 8/3/10, Marc Guay wrote: From: Marc Guay Subject: Re: from excel to the mySQL To: "HaidarPesebe" Cc: "MySQL Lists" Date: Tuesday, August 3, 2010, 7:34 AM > I'm needs a way to upload data from excel to the mySQL database. Dear all, I > need help is how to upload data from excel columns and load into mysql > database using php? http://www.sqldbu.com/eng/sections/tips/mysqlimport.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=samc...@yahoo.com
drop down menu to a table
How do I insert data from a drop down menu (select box) to a table
Re: Error: Duplicate entry '0' for key 'PRIMARY'
Thank you all I solved the problem --- On Mon, 3/2/09, Darryle Steplight wrote: From: Darryle Steplight Subject: Re: Error: Duplicate entry '0' for key 'PRIMARY' To: samc...@yahoo.com Cc: mysql@lists.mysql.com, g...@primeexalia.com Date: Monday, March 2, 2009, 2:32 PM Are you trying to do an Insert On Duplicate Key? Do ou want to insert a new row if it doesn't already exist or update one if it does? On Mon, Mar 2, 2009 at 4:09 PM, sam rumaizan wrote: > Are you talking about Length/Values1 > > > > --- On Mon, 3/2/09, Gary Smith wrote: > > From: Gary Smith > Subject: Re: Error: Duplicate entry '0' for key 'PRIMARY' > To: samc...@yahoo.com, mysql@lists.mysql.com > Date: Monday, March 2, 2009, 1:58 PM > > Easy. Ensure that all in the primary key have unique values. > > With that said, it would be more useful to have a ddl and the query causing the > problem. > > > --Original Message-- > From: sam rumaizan > To: mysql@lists.mysql.com > ReplyTo: samc...@yahoo.com > Sent: Mar 2, 2009 12:56 PM > Subject: Error: Duplicate entry '0' for key 'PRIMARY' > > Error: Duplicate entry '0' for key 'PRIMARY' > > how can i fix it ? > > > > > > Sent via BlackBerry by AT&T > > >
Re: Error: Duplicate entry '0' for key 'PRIMARY'
Are you talking about Length/Values1 --- On Mon, 3/2/09, Gary Smith wrote: From: Gary Smith Subject: Re: Error: Duplicate entry '0' for key 'PRIMARY' To: samc...@yahoo.com, mysql@lists.mysql.com Date: Monday, March 2, 2009, 1:58 PM Easy. Ensure that all in the primary key have unique values. With that said, it would be more useful to have a ddl and the query causing the problem. --Original Message-- From: sam rumaizan To: mysql@lists.mysql.com ReplyTo: samc...@yahoo.com Sent: Mar 2, 2009 12:56 PM Subject: Error: Duplicate entry '0' for key 'PRIMARY' Error: Duplicate entry '0' for key 'PRIMARY' how can i fix it ? Sent via BlackBerry by AT&T
Re: Error: Duplicate entry '0' for key 'PRIMARY'
How do I modify the column to add value? Can I do it with phpmyadmin? --- On Mon, 3/2/09, Gary Smith wrote: From: Gary Smith Subject: Re: Error: Duplicate entry '0' for key 'PRIMARY' To: samc...@yahoo.com, mysql@lists.mysql.com Date: Monday, March 2, 2009, 1:58 PM Easy. Ensure that all in the primary key have unique values. With that said, it would be more useful to have a ddl and the query causing the problem. --Original Message-- From: sam rumaizan To: mysql@lists.mysql.com ReplyTo: samc...@yahoo.com Sent: Mar 2, 2009 12:56 PM Subject: Error: Duplicate entry '0' for key 'PRIMARY' Error: Duplicate entry '0' for key 'PRIMARY' how can i fix it ? Sent via BlackBerry by AT&T
Error: Duplicate entry '0' for key 'PRIMARY'
Error: Duplicate entry '0' for key 'PRIMARY' how can i fix it ?
Mail not being accepted
When I try to email to this list my email is reject with the following message? Error: 552 Mail with no Date header not accepted here My Mail has a date header in it...?
LAST_INSERT_ID & LAST_UPDATE_ID
How can I select (retrieve) the last updated cell (field). Basically I need to
pull the new information only.
I'm using for updating my database:
UPDATE table SET column = CONCAT_WS ('column,'" . $column."') WHERE column=
value;
I need to select data:
SELECT * FROM table WHERE column=Whatever
I found LAST_INSERT_ID but i doesn't work
What I want to do is:
1-user has 10 columns (fields).
2- User updated one of these fields using CONCAT_WS(adding new data to previous
data).
3- When user views any of his information he sees only the last updated part of
the data.
So, timestamp/datetime field is not going to work.
-
Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
Mysql and Textarea
I'm just a php beginner.
So please be patient with my stupid questions.
What am I missing in this code that causing the function cant update the
textarea?
Update button erase the old information from mysql database and replace it
with nothing. Basically it cant read what is inside the Textarea box. Why??
Read the highlighted code.
View
Existing Data
Choose a Category:
$value";
}
?>
ID
Reference No
Job Descriptions
Category
Assign Engineer
Date Received
Date Required
Date Assigned
Projected Completion Date
Date Completed
Manhour Spent
Status
";
echo "";
echo "{$row['ID']}";
echo "{$row['Ref_No']}";
echo ' '.$row['Job_Title'] .'';
echo " ";
echo 'Updaet data
';
echo "{$row['Category']}";
echo "{$row['Assign_Engineer']}";
echo "{$row['Date_Received']}";
echo "{$row['Date_Required']}";
echo "{$row['Date_Assigned']}";
echo "{$row['ProjectedCompletionDate']}";
echo "{$row['Date_Completed']}";
echo "{$row['ManhourSpent']}";
echo "{$row['Status']}";
echo "";
echo"";
}
?>
-
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search.
index number of a specific cell
Is there a function in mysql to view the index number of a specific cell/field? For example if I want the index number for info4 or info7. Is it possible? Column 1 Column 2 Column 3 Column 4 Column 5 Info1 Info2 Info3 Info4 Info5 Info6 Info7 Info8Info9 Info10 Info11 Info12Info13 Info14 Info15 - Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
Mysql and Textarea
Can someone show me the mysql query statement to update the Text Area (the
Text Area is populated with information from mysql) basically I want to allow
the user to change or add information to the original data.
scroll down to read the code. The text area I need to update is red font.
http://www.w3.org/TR/html4/loose.dtd";>
View
Existing Data
Choose a Category:
$value";
}
?>
ID
Reference No
Job Descriptions
Category
Assign Engineer
Date Received
Date Required
Date Assigned
Projected Completion Date
Date Completed
Manhour Spent
Status
";
echo "";
echo "{$row['ID']}";
echo "{$row['Ref_No']}";
echo " \n{$row['Job_Title']}";
echo "{$row['Category']}";
echo "{$row['Assign_Engineer']}";
echo "{$row['Date_Received']}";
echo "{$row['Date_Required']}";
echo "{$row['Date_Assigned']}";
echo "{$row['ProjectedCompletionDate']}";
echo "{$row['Date_Completed']}";
echo "{$row['ManhourSpent']}";
echo "{$row['Status']}";
echo "";
echo"";
}
?>
-
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search.
Object-Oriented database
Is there such thing call Object-Oriented database for mysql? Basically can I store an item as an object in myql? - Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
Importing data from excel sheet
I have created table in mysql with 12 fields Field1Field2 Field3 Field4 Field12 I have an excel sheet with 12 columns and 150 rows. My question is how can I import all of the columns from the excel sheet to my table without losing any information. - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends.
append information
I have a question if you don't mind. The update statement will replace the existing information. What if I have Textarea instead and I want to add information to the field without erasing previous information. Basically append information - Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
insert data in to columns base on the selection of the list box.
Can you help me please?
1-I have created a while loop to populate the list box with the information
of column1.
2-I need to update (insert data) in to column2, column3, column4 base
on the selection of the list box.
echo'';
$query = "SELECT column1 FROM table";
$result = mysql_query($query);
echo"";
echo"";
echo"";
echo"";
echo"Choose a Category:
";
while ($line = mysql_fetch_array($result))
{
foreach ($line as $value)
{
echo"$value";
}
echo "";
echo "";
$sql="INSERT INTO table WHERE column1='".$_POST["R"]."'
(column2, column3, column4)VALUES('info2', 'info3', 'info4')";
$result=mysql_query($sql);
-
No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.
The time of last updated field
I have to two different fields Last_Updated and Story. I want whenever Story Field got updated (modified) the time and date of the modification get recorded (insert) in Last_Updated Field - No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started.
Announcing the MySQL Journal
s, please send me email or send me an instant message on AIM at samflywheel. You are welcome to forward this to others. Thanks! Sam Publisher, Tabula email: [EMAIL PROTECTED] aim: samflywheel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Announcing the MySQL Journal
ng. 9. Set deadlines for issue #1 and set a launch date. 10. Get the word out! The permanent staff of the magazine has considerable experience editing and producing technical articles. The magazine has already been designed and looks great! If you want to receive email when subscriptions are available, please send email to [EMAIL PROTECTED] I know this posting is long, but I wanted to try and answer the most pressing questions that authors and advertisers tend to have. If you have any questions, please send me email. You are welcome to forward this to others. I look forward to your ideas, suggestions, and contributions! Sam Flywheel, Principal The Flywheel, Gadget, and Sprocket Co. Publisher, Tabula email: [EMAIL PROTECTED] aim: samflywheel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to change root password
On 2/26/06, Arnel Pastrana <[EMAIL PROTECTED]> wrote: > Hi everyone, > > I was installing drupal on my webserver with mysql after i followed > instructions on how to configure and setup drupal i cant't access the > root password of my mysql. my password won't work all. > > Any idea how to change my password of root? or is is passible that my > mysql is currupted? > > Any help? http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Debian and "localhost.localdomain"
On 2/25/06, Yves Goergen <[EMAIL PROTECTED]> wrote: > I've been trying to get MySQL to work on Debian 3.1 but it won't let my > newly created users connect. I added users like "[EMAIL PROTECTED]" > and when one tries to connect to the server, it says > "[EMAIL PROTECTED]" is not allowed to connect. I suppose > it's a Debian 3.1 issue since it worked on Debian 3.0. Does anybody know > how I can get that "localdomain" out of my computer? Using MySQL 4.0. > What do you have in your /etc/hosts file? Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem installing mysql on Debian linux
On 2/25/06, Tselemegkos Grigorios <[EMAIL PROTECTED]> wrote: > Hi, > > I'm trying to install mysql 4.0.25 from source on Debian linux without root > privileges. > The problem comes when i execute the configure command. > I typed: > configure --prefix /home/grtsel/mysql > > and the output error was: > > checking for FIONREAD in sys/ioctl.h... yes > checking for TIOCSTAT in sys/ioctl.h... no > checking if struct dirent has a d_ino member... yes > checking whether signal handlers are of type void... yes > checking for tgetent in -lncurses... no > checking for tgetent in -lcurses... no > checking for tgetent in -ltermcap... no > checking for termcap functions library... configure: error: No curses/termcap > library found > > I installed ncurses package from source in order to bypass this problem but > nothing happend... > > Does anyone know what's going wrong..? > Please help me... > Gregory, You need to install the termcap libraries. What version of Debian are you using? Why don't you install MySQL 5.0.18? Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql-bench and results
Hi All, I installed MySQL 5.0.18 on a Debian Sarge box. I ran the benchmark suite in the subfolder sql-bench. Now I'd like to compare my results with other results. According to http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html some results are included in sql-bench/Results. But I cannot find this folder at all. I looked inside the binary installation package and the source installation package. Any idea where I can find these results? Thanks in advance. Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does query load faster after executing 2nd time? (Query Caching DISABLED, Key-Cache already fully loaded)
Hello therei have seen this question before, I cannot exactly remember when but it was a while ago. My advice is to go to the mysql.com website and do a search thru the mailing list using a search term something like "must execute query twice" or something to that effect. -sam > Filesystem buffering? > > pow wrote: >> Hi everyone, >> Im puzzling over why a query loads faster the second time I execute it. >> >> I am sure it is not query cached, because that is off. >> I also made sure that the key that is used was already cached b4 i even >> executed the query the first time. So it is not like as if the 2nd >> execution used the key cache and the first attempt did not. >> >> I am executing the queries directly in MYSQL command prompt. >> >> >> Is there some other type of cacheing that i am missing? >> >> Thanks! >> >> Pow >> > > > -- > 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: more queries vs a bigger one
Hello, approx. how long does it take your "big" query to run as it is now? Are these queries appending a table? or are they buiding a result (from a "chain" of queries)? Have you tried separating them out? Any difference? -sam > Hi, > > I have a big query that involves searching in more tables, and I think > this > might be slower than creating more smaller queries. What do you think, is > this true generally? > > The query searches in a big table but it also counts the number of records > from other 2 tables based on a criteria, and usually the result is a big > number of records, but the final result is limited using "limit 0,30". > > So I am wondering... > Could it work faster if I won't count(*) the number of records in those 2 > tables, but get the result (only 30 records), then for each separate > record > use a separate query that gets that number? > > I don't know, could 31 queries work faster than a single bigger and > complex > query? > > Thank you. > > Teddy > > > > -- > 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: datetime/timestamps/4.1.12
Mike, Have you tried creating a new table with a field for some sort of date? Try adding some data and see if the new date time format you specified in the my.cnf file. See if that works. -sam > Hello, > > Nevermind - duh -datetime is not timestamp (oneday I fullfill my promise > to myself and not work on Sundays when my 'duh' level is a bit higher). > > Sofrom what I gather, the backward compatibility comes in the form of > adding a +0 (string to int). > > This is most inconvenient and annoying. > > Any plans on rectifying this, or has anyone found a workaround, or are we > left to go SIOH (hint OH stands for "our hat") :-} > > Later... > Michael > > -Original Message- > From: DePhillips, Michael P > Sent: Sun 7/17/2005 11:05 AM > To: mysql@lists.mysql.com > Cc: > Subject: datetime/timestamps/4.1.12 > > > > Hello, > > I just upgraded to 4.1.12 from 4.0.22 and my timestamps changed formats > to > %Y-%m-%d %H:%i:%s > > So I added the following lime to my my.cnf file > > datetime_format=%Y%m%d%H%i%s > > Which is the format I prefer, I restart the server and my time stamps > still appear as > %Y-%m-%d %H:%i:%s > > The 'show variables' command now confirms that the format is what I > defined in the my.cnf but the output of the query does not change... > > > What am I missing? > > Thanks, > Michael > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Resetting lsn/trx for an .ibd file
It's pretty clear that although you cannot currently move .ibd files between MySQL installations, it's technically possible - it would simply be a matter of resetting the lsn/trx id's of a clean .ibd file. http://lists.mysql.com/mysql/159206 http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html We have massive InnoDB data structures where building indexes can take days; it would be very convenient if we were able to cleanly move .ibd files to our slaves, and I'm sure I'm not the only one interested in this. Has there been any work done to solve this issue? -- Sam Sgro [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing a big query...
mos wrote: At 07:01 PM 3/10/2005, Chris W. Parker wrote: Homam S.A. <mailto:[EMAIL PROTECTED]> on Wednesday, March 09, 2005 5:09 PM said: > If your tables are mostly read-only, you could > pre-generate page numbers on a periodic basis and > select only specific ranges WHERE row_number BETWEEN > page_start AND page_finish. How about just getting the entire list on the first page and then on all subsequent pages using LIMIT()? Chris. Chris, This is deja vu all over again. I already suggested this. He needs the record count and Limit doesn't provide it. And separate Sql Count would be too slow. Mike This require the change of Mysql engine. I wish Mysql has such configuration option for that in future release. Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Perl DBI does not print error.
Hi, The perl DBI does not prints error message: $sth = $dbh->prepare($insert_sql) || print ERRFILE_OUT "prepare: $insert_sql: $DBI::errstr"; The out of this error only does not print why the error was occurred. eg. Unable to execute query: DBI::db=HASH(0x8647df0)->errstr: Dan anyone please tell me how to prints how a description of the error from DBI when sql executed failed? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
Deniss Hennesy wrote:
Hi,
I have had to change mysql root passwd
My procedure is just below
shell> mysql -u root -p
mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
mysql> flush privileges;
YOu may be need to execute command mysqladmin -u root password
(yourpassword) for the creation of password. I m not sure, not expert
in DBA.
Sam
and I checked this password another consol I saw MySQL wasn't
accepted new passwd else, it didnt old password or without password.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help - MySQL4.1.10 don't run properly after data restored from 5.0.1
] mysql 2895 0.0 8.8 302680 45340 ? S14:16 0:00 [mysqld] The questions I would like to ask is how to make sure mysqld is start up properly? Because it seems that the start script that come with the binary files from mysql4.1.10 can';t be started by typing "mysql.server start", and I don't see a /etc/my.conf is installed in the system, so I copied the support-files/my-huge.cnf to /etc/my.cnf. The second question is how to make sure all indexes are in-placed after the data restore? I used the following script backup all data from MySQL5.0.1: DBNAMES="`mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST --batch --skip-column-names -e "show databases"| sed 's/ /%/g'`" Thank you in advance Sam. mel list_php wrote: Yeah, I have looked at it, but not sure whether I need to repopulate the mysql.host db file. If I do that, I may be also need to recreate all db passwords as well. The error is: InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050302 16:36:43 InnoDB: Flushing modified pages from the buffer pool... 050302 16:36:43 InnoDB: Started; log sequence number 0 43634 050302 16:36:43 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050302 16:36:43 mysqld ended I don't know how you made your backup, apparently you missed the mysql.host table. Usually when making a new install a mysql database containing all the privileges is created, maybe you erased this when copying your files? In an other mail you say you used the --all-databases option so I suppose the error is somewhere else. To identify the problem you may try to recreate the mysql db, see if it works, and if yes check your dump. About your indexes I usually use mysqldump and the indexes are exported as well, you can check with show index but I'm not sure that is what you are looking for. http://dev.mysql.com/doc/mysql/en/mysqldump.html Since all DB data in this server are restored from the FreeBSD system in MySQL 5.0. I don't know how to recreate all indexes for db. Is there simple way for recreating all indexes? or check whether indexes are in-placed? Thanks Sam Good luck! From: sam wun <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: Silly mistake Date: Wed, 02 Mar 2005 15:58:28 +0800 Hi, I admit I m silly to shutdown mysqld with the killall command in the Redhat server, I can't restart mysql service now. Most of the reason is because the script mysql.server come with the mysql 4.1.10 does not like mysql.server start or mysql.server stop, so I need to start it up use & and shut it down with killall. Anyway, after killall mysql, I got the following error when I tried to restart it. Here is the error: ./mysqld Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid 050302 15:52:05 mysqld ended How can I start mysqld now? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Silly mistake
mel list_php wrote: Yeah, I have looked at it, but not sure whether I need to repopulate the mysql.host db file. If I do that, I may be also need to recreate all db passwords as well. The error is: InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050302 16:36:43 InnoDB: Flushing modified pages from the buffer pool... 050302 16:36:43 InnoDB: Started; log sequence number 0 43634 050302 16:36:43 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050302 16:36:43 mysqld ended I don't know how you made your backup, apparently you missed the mysql.host table. Usually when making a new install a mysql database containing all the privileges is created, maybe you erased this when copying your files? In an other mail you say you used the --all-databases option so I suppose the error is somewhere else. To identify the problem you may try to recreate the mysql db, see if it works, and if yes check your dump. It worked nearly perfectly before, I can login to db, use perl dbi connect to it, etc. So I think it is the problem when I executed command killall mysql. I don't supposed it remove the mysql.host file, may be mysql.host db is corrupted when I m doing so, so it removes it for me. This becomes a very big problem, I m not sure if one day the system crashes, mysql will remove any DB without warning me first. Now, it is most likely I need to erase all dbs and repopulate (restore) all data. About your indexes I usually use mysqldump and the indexes are exported as well, you can check with show index but I'm not sure that is what you are looking for. http://dev.mysql.com/doc/mysql/en/mysqldump.html I have checked all indexes with the show index TABLE commands, and compared the result with the one in FreeBSD, and looks quite identical. The only difference is the one in FreeBSD is running MySQL 5.01 and the one in this Redhat is running MySQL 4.1.10. The indexing problem gave me a big headache because I m not sure whether it is the versioning problem or missing indexes or something else... With MySQL5.01 in FreeBSD 5.3, it takes 2 seconds finish the query, but with MySQL 4.1.10 in Redhat, takes forever... Thanks Sam Since all DB data in this server are restored from the FreeBSD system in MySQL 5.0. I don't know how to recreate all indexes for db. Is there simple way for recreating all indexes? or check whether indexes are in-placed? Thanks Sam Good luck! From: sam wun <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: Silly mistake Date: Wed, 02 Mar 2005 15:58:28 +0800 Hi, I admit I m silly to shutdown mysqld with the killall command in the Redhat server, I can't restart mysql service now. Most of the reason is because the script mysql.server come with the mysql 4.1.10 does not like mysql.server start or mysql.server stop, so I need to start it up use & and shut it down with killall. Anyway, after killall mysql, I got the following error when I tried to restart it. Here is the error: ./mysqld Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid 050302 15:52:05 mysqld ended How can I start mysqld now? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Silly mistake
mel list_php wrote: Hi Sam, To shutdown mysql I just use mysqladmin shutdown. http://dev.mysql.com/doc/mysql/en/server-shutdown.html Thanks for this help. I will surely use this command next time. About you restart problem, have a look in mysql/data/host.err file, it should give you a reason there. Last time I had that problem it's because I hadn't kill all the process. Yeah, I have looked at it, but not sure whether I need to repopulate the mysql.host db file. If I do that, I may be also need to recreate all db passwords as well. The error is: InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050302 16:36:43 InnoDB: Flushing modified pages from the buffer pool... 050302 16:36:43 InnoDB: Started; log sequence number 0 43634 050302 16:36:43 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050302 16:36:43 mysqld ended Since all DB data in this server are restored from the FreeBSD system in MySQL 5.0. I don't know how to recreate all indexes for db. Is there simple way for recreating all indexes? or check whether indexes are in-placed? Thanks Sam Good luck! From: sam wun <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: Silly mistake Date: Wed, 02 Mar 2005 15:58:28 +0800 Hi, I admit I m silly to shutdown mysqld with the killall command in the Redhat server, I can't restart mysql service now. Most of the reason is because the script mysql.server come with the mysql 4.1.10 does not like mysql.server start or mysql.server stop, so I need to start it up use & and shut it down with killall. Anyway, after killall mysql, I got the following error when I tried to restart it. Here is the error: ./mysqld Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid 050302 15:52:05 mysqld ended How can I start mysqld now? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for start stop script
Hi, Does anyone have a working start/stop script for mysql4.1.10? The mysql.server script come with the mysql4.1.10 package does not work with argument (start/stop). Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Silly mistake
Hi, I admit I m silly to shutdown mysqld with the killall command in the Redhat server, I can't restart mysql service now. Most of the reason is because the script mysql.server come with the mysql 4.1.10 does not like mysql.server start or mysql.server stop, so I need to start it up use & and shut it down with killall. Anyway, after killall mysql, I got the following error when I tried to restart it. Here is the error: ./mysqld Starting mysqld daemon with databases from /usr/local/mysql/data STOPPING server from pid file /usr/local/mysql/data/vivaserver.pid 050302 15:52:05 mysqld ended How can I start mysqld now? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL runing very slow after restore
Hi, I finally got the backup data ported from mysql 5.0 (from freebsd) to mysql 4.1.10 in Redhat. Webmin started up and I can browse the data in each table. After installed all prerequisit perl modules, and start the web gui and start to run some queries, I expeirence it runs very slow. A sql in mysql 5.0 finsihed in few seconds, but my mysql 4.1.10 takes forever to run. I created indexes in mysql 5.0. When doing backup, I use --all-databases and restore it use command mysql < mysql.sql and mysql < data.sql. I don't know whether all indexes are rebuilt correctly in the new database (mysql 4.1.10). How do I know all my previous indexes are in-placed with the restore? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
Hi, I have installed the server and started up successfully. mysql.sock file is written to /var/lib/mysql/ directory as well. Now I found that I also need to install mysql 4.10-1 client. However there is already mysql3 installed in the system. How can I remove the old version in Redhat? Here are all error I got: # rpm -i MySQL-client-4.1.10-0.i386.rpm warning: MySQL-client-4.1.10-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 file /usr/bin/mysql from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysql_find_rows from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlaccess from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlbinlog from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlcheck from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqldump from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlimport from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/bin/mysqlshow from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysql.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysqlaccess.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysqladmin.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysqldump.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 file /usr/share/man/man1/mysqlshow.1.gz from install of MySQL-client-4.1.10-0 conflicts with file from package mysql-3.23.54a-11 Thanks Sam sam wun wrote: sam wun wrote: Gleb Paharenko wrote: Hello. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. If you add --all-databases option it would also dump the mysql database. I've tried to import the mysql database from the 5.0.2 to 4.1.10. It works fine, You may try just to restore the dump file with mysql < dump_file.sql command. Don't forget to execute FLUSH PRIVILEGES. Hi, I got the following error when execute mysql < mydata.sql: # mysql < mysql_2005-03-01.Tuesday.sql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I have looked thru the mysqld script in /etc/init.d/ directory, but didn't get a cule about start up mysqld with unix sock. You got any idea? I also search for where mysql.sock is, but I couldn't find this file in the system. have I miss something in the mysqld start up script? Thanks Sam Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
sam wun wrote: Gleb Paharenko wrote: Hello. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. If you add --all-databases option it would also dump the mysql database. I've tried to import the mysql database from the 5.0.2 to 4.1.10. It works fine, You may try just to restore the dump file with mysql < dump_file.sql command. Don't forget to execute FLUSH PRIVILEGES. Hi, I got the following error when execute mysql < mydata.sql: # mysql < mysql_2005-03-01.Tuesday.sql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I have looked thru the mysqld script in /etc/init.d/ directory, but didn't get a cule about start up mysqld with unix sock. You got any idea? I also search for where mysql.sock is, but I couldn't find this file in the system. have I miss something in the mysqld start up script? Thanks Sam Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
Gleb Paharenko wrote: Hello. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. If you add --all-databases option it would also dump the mysql database. I've tried to import the mysql database from the 5.0.2 to 4.1.10. It works fine, You may try just to restore the dump file with mysql < dump_file.sql command. Don't forget to execute FLUSH PRIVILEGES. Hi, I got the following error when execute mysql < mydata.sql: # mysql < mysql_2005-03-01.Tuesday.sql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I have looked thru the mysqld script in /etc/init.d/ directory, but didn't get a cule about start up mysqld with unix sock. You got any idea? Thanks Sam sam wun <[EMAIL PROTECTED]> wrote: Gleb Paharenko wrote: Hello. I also want to copy the user accounts in MySQL5.0 db over to MySQL4.1 in Redhat as well. All user accounts and rights are stored in the tables of mysql database. And fields of grant tables in 4.1.x version just a subset of fields in 5.0.x. You should set the values of fields in 4.1.x version to values of corresponding fields in 5.0.x. For example you may store 5.0.x mysql database under different name in 4.1.x and then move accounts using SQL statements. Hi, thanks for your help. I just able to backup all the database (with their tables I believe) from mysql 5.0. And now I stuck at restoring the database and their tables as well as user accounts rights to mysql4.1. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
Gleb Paharenko wrote: Hello. I also want to copy the user accounts in MySQL5.0 db over to MySQL4.1 in Redhat as well. All user accounts and rights are stored in the tables of mysql database. And fields of grant tables in 4.1.x version just a subset of fields in 5.0.x. You should set the values of fields in 4.1.x version to values of corresponding fields in 5.0.x. For example you may store 5.0.x mysql database under different name in 4.1.x and then move accounts using SQL statements. Hi, thanks for your help. I just able to backup all the database (with their tables I believe) from mysql 5.0. And now I stuck at restoring the database and their tables as well as user accounts rights to mysql4.1. I used the following command backup database: mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST --quote-names --opt I don't think this command backup user account info. Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
sam wun wrote: Hi, I just fresh install mysql4.1 in Redhat9.0. I have another MySQL5.0 installed in FreeBSD5.3, and just tried to copy all data in /var/db/mysql/ from FreeBSD5.3 to /var/db/mysql/data/ in Redhat9.0. After finished copied the data over to Redhat, MySQL4.1 can't start. The DB data in MySQL5.0 in the FreeBSD box has database and tables with indexes. I don't want to rebuild the entire database from scratch in Redhat, how can I "transfer" all db files from FreeBSD to Redhat and in another version of MySQL? I also want to copy the user accounts in MySQL5.0 db over to MySQL4.1 in Redhat as well. I supposed this is sort of data backup, but more generic than that because it is copying data between different version of MySQL, and from higher version to a lower version of MySQL. Sam Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copy DB data from FreeBSD to Redhat9.0
Hi, I just fresh install mysql4.1 in Redhat9.0. I have another MySQL5.0 installed in FreeBSD5.3, and just tried to copy all data in /var/db/mysql/ from FreeBSD5.3 to /var/db/mysql/data/ in Redhat9.0. After finished copied the data over to Redhat, MySQL4.1 can't start. The DB data in MySQL5.0 in the FreeBSD box has database and tables with indexes. I don't want to rebuild the entire database from scratch in Redhat, how can I "transfer" all db files from FreeBSD to Redhat and in another version of MySQL? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
download mysql 4 in rpm for Redhat 9.0
Hi, I realised that install mysql 4 thru rpm is the best way for a smooth installation. Can anyone please point me to a site where I can download mysql 4 rpm for Redhat9? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
download Mysql4 for Redhat9.0
Hi, Can anybody tell me where can I download Mysql4 rpm for Redhat9.0? I cannot confirm the specific version in rpmfind.net is for Redhat9.0/ Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't drop index.
Hi, I created an index on a foreign in a table before. I need to drop this index. but I got the following error: mysql> alter table transaction drop index prodcode; ERROR 1025 (HY000): Error on rename of './datacube/#sql-30e8_3' to './datacube/transaction' (errno: 150) mysql> mysql> show index from transaction; +-++-+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++-+--+-+---+-+--++--++-+ | transaction | 0 | PRIMARY |1 | transcode | A | 161361 | NULL | NULL | | BTREE | | | transaction | 1 | custcode|1 | custcode| A | 810 | NULL | NULL | | BTREE | | | transaction | 1 | prodcode|1 | prodcode| A | 911 | NULL | NULL | | BTREE | | | transaction | 1 | date|1 | date| A |1014 | NULL | NULL | | BTREE | | | transaction | 1 | netsales|1 | netsales| A | 14669 | NULL | NULL | | BTREE | | | transaction | 1 | salesvolume |1 | salesvolume | A | 197 | NULL | NULL | | BTREE | | | transaction | 1 | custcode_2 |1 | custcode| A | 139 | NULL | NULL | | BTREE | | | transaction | 1 | custcode_2 |2 | date| A | 26893 | NULL | NULL | | BTREE | | +-++-+--+-+---+-+--++--++-+ 8 rows in set (0.02 sec) mysql> show index from inventory; +---+++--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+-+---+-+--++--++-+ | inventory | 0 | PRIMARY|1 | prodcode| A |2377 | NULL | NULL | | BTREE | | | inventory | 1 | basename |1 | basename| A | 30 | NULL | NULL | YES | BTREE | | | inventory | 1 | vendorname |1 | vendorname | A | 27 | NULL | NULL | YES | BTREE | | +---+++--+-+---+-+--++--++-+ 3 rows in set (0.01 sec) mysql> show innodb status; = 050124 10:50:54 INNODB MONITOR OUTPUT = Per second averages calculated from the last 11 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 1372, signal count 1344 Mutex spin waits 2173, rounds 23570, OS waits 375 RW-shared spins 889, OS waits 444; RW-excl spins 661, OS waits 553 LATEST FOREIGN KEY ERROR 050124 10:49:59 Error in foreign key constraint of table `datacube/transaction`: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match to the ones in the referenced table. Constraint: , CONSTRAINT `transaction_ibfk_2` FOREIGN KEY (`prodcode`) REFERENCES `inventory` (`prodcode`) ON DELETE CASCADE TRANSACTIONS Trx id counter 0 3143526 Purge done for trx's n:o < 0 3143526 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 3143514, not started, OS thread id 167162880 MySQL thread id 3, query id 69 localhost root SHOW INNODB STATUS FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 79139 OS file reads, 100705 OS file writes, 6249 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH IND
Re: INNER JOIN across multiple tables appear very slow.
Hi Michael, Thank you for being so kind to explain the problem to me. Michael Stassen wrote: sam wun wrote: sam wun wrote: Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = "SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales Quantity', ROUND(avg(t.netsales),2) 'PastSales', ROUND(avg(tt.netsales),2) 'Sales', Round(((avg(t.netsales) - avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff', Round(((avg(t.salesvolume) - avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff' FROM customer c INNER JOIN transaction t ON c.custcode = t.custcode AND date(t.date) >= '$past_date_period_startdate' AND date(t.date) <= '$past_date_period_enddate' AND c.salescode = '$salescode_param' INNER JOIN transaction tt ON c.custcode = tt.custcode AND date(tt.date) >= '$input_date_period_startdate' AND date(tt.date) <= '$input_date_period_enddate' AND c.salescode = '$salescode_param' AND t.prodcode = tt.prodcode INNER JOIN inventory i ON i.prodcode = t.prodcode AND i.prodcode = tt.prodcode AND i.basename = '$basename_param' WHERE i.prodname is not NULL AND i.basename is not NULL GROUP BY c.salescode, i.basename, t.prodcode ORDER BY SalesDiff desc; Thanks Sam I see several potential problems: 1) The biggest fixable problem is the use of the date() function. You should never use a function of a column value in a WHERE clause, if you can avoid it, because that prevents the use of an index on that column to help choose rows. In this case, the date() function isn't even needed, so even if there is no index on t.date, or the optimizer doesn't choose to use it, you are still forcing 4 unnecessary function calls per row. In other words, change AND date(t.date) >= '$past_date_period_startdate' AND date(t.date) <= '$past_date_period_enddate' to AND t.date >= '$past_date_period_startdate' AND t.date <= '$past_date_period_enddate' or the equivalent AND t.date BETWEEN '$past_date_period_startdate' AND '$past_date_period_enddate' Yes, I am totally agree with this change. I m still new to mysql/sql statement. I never realised the date field can be used for comparison without using date. 2) In general, you should'nt put things in the JOIN condition which are not part of the JOIN criteria. For example, the condition "c.salescode = '$salescode_param'" is a restriction on which rows in c to consider, not a condition of the JOIN to t or tt, so it belongs in the WHERE clause. 3) You have a complex GROUP BY based on a column from each table, and an ORDER BY based on a calculated value. That rules out using an index to do the grouping or ordering. Hence the dreaded "Using temporary; Using filesort" in your explain output. This appears unavoidable in your case. This clears my question about how to use JOIN. The "using temporary" may be caused by the clause "create temporary table" in other sql statement that created before execute the sql statement as written above. I was trying to break down the entire operation into sub-queires so that it produce sub-recordset faster and then JOIN with these recordset later. I m not sure whether using temporary tables will also speed thing up. I will drop the idea of using temporary table if that doesn't help much. I guess I can change the group by with only one column rather than 3 columns, and the result still the same since salescode and basename are input by the user, so this query only display the recordset of the product(code/name) base on value of salescode and the basename. 4) You have redundant conditions on your JOIN to i. Since t.prodcode = tt.prodcode for every row of your results (because you require this in the join of t to tt), the two conditions "i.prodcode = t.prodcode" and "i.prodcode = tt.prodcode" are equivalent. The optimizer is probably smart enough to notice that, but it will have less work to do if you pick one and delete the other. 5) Because you require "i.basename = '$basename_param'", i.basename cannot be NULL, so there is no need for the conditrion "i.basename is not NULL". 6) Are you sure you need "i.prodname is not NULL"? If you can be certain that all rows in inventory with non NULL prodcode and basename have non NULL prodnames, you could leave that out. I can't assume you don't need it based only on the rest of your query, so I'll leave it
create indexes for temporary table
Hi,
I want to create indexes for temporary created tables in perl dbi.
The following is perl code that I tried, but perl dbi seems not allow
the syntax:
Unable to execute our query PastSales:You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '"alter table tmp_pastsales add
index(salescode,basename,prodcode);"' at line 1 Unable to execute our
query Sales:You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '"alter table tmp_sales add index(salescode,basename,prodcode);"'
at line 1
This is the actual perl code I use:
$tmp_sql = qq{"alter table tmp_pastsales add
index(salescode,basename,prodcode);"};
$sth_tmp = $dbh->prepare($tmp_sql) or print "Unable to prep our
query:".$dbh->errstr."\n";
$sth_tmp->execute or print "Unable to execute our query
PastSales:".$dbh->errstr."\n";
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show temporary table
Hi, How can I see all temporary tables that created by Create Temporary Table command in mysql 5.01? thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error with Storage engine
Hi, I was trying to optimize a sql (for INNER JOIN) by spliting the sql into 2 temporary tables. But I got error with the following sql: mysql> CREATE TEMPORARY TABLE pastsales_tab_3141604663377652915968 SELECT c.salescode,c.type, c.custcode, i.basename, i.vendorname, t.salesvolume, t.netsales, t.prodcode, i.prodname from inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND date(t.date) >= '2003-01-23' AND date(t.date) <= '2004-01-23' INNER JOIN customer c ON c.custcode = t.custcode ; ERROR 1030 (HY000): Got error 28 from storage engine How can I correct this error? I musing mysql5.01 Thanks Sam. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNER JOIN across multiple tables appear very slow.
sam wun wrote: Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = " SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales Quantity', ROUND(avg(t.netsales),2) 'PastSales', ROUND(avg(tt.netsales),2) 'Sales', Round(((avg(t.netsales) - avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff', Round(((avg(t.salesvolume) - avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff' from customer c INNER JOIN transaction t ON c.custcode = t.custcode AND date(t.date) >= '$past_date_period_startdate' AND date(t.date) <= '$past_date_period_enddate' AND c.salescode = '$salescode_param' INNER JOIN transaction tt ON c.custcode = tt.custcode AND date(tt.date) >= '$input_date_period_startdate' AND date(tt.date) <= '$input_date_period_enddate' AND c.salescode = '$salescode_param' AND t.prodcode = tt.prodcode INNER JOIN inventory i ON i.prodcode = t.prodcode AND i.prodcode = tt.prodcode AND i.basename = '$basename_param' WHERE i.prodname is not NULL and i.basename is not NULL GROUP BY c.salescode, i.basename, t.prodcode ORDER BY SalesDiff desc; Thanks Sam Hi, here is the result from the explain command on the query: mysql> explain SELECT i.basename, c.salescode, -> avg(t.salesvolume) 'PastSales Quantity', -> avg(tt.salesvolume) 'Sales Quantity', -> avg(t.netsales) PastSales, -> avg(tt.netsales) Sales, -> Round(((avg(t.netsales) - avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff', -> Round(((avg(t.salesvolume) - avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff', c.salescode -> from customer c -> INNER JOIN transaction t -> ON c.custcode = t.custcode -> AND date(t.date) >= '2003-01-22' -> AND date(t.date) <= '2004-01-22' -> INNER JOIN transaction tt -> ON c.custcode = tt.custcode -> AND date(tt.date) >= '2004-01-22' -> AND date(tt.date) <= '2005-01-22' -> INNER JOIN inventory i -> ON i.prodcode = t.prodcode -> AND i.prodcode = tt.prodcode -> WHERE i.prodname is not NULL -> and i.basename is not NULL -> group by c.salescode,i.basename -> order by c.salescode, 'SalesDiff' desc ; ++-+---++---+--+-+-+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++---+--+-+-+--+--+ | 1 | SIMPLE | i | ALL| PRIMARY,basename | NULL | NULL| NULL| 2491 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t | ref| custcode,prodcode | prodcode | 32 | datacube.i.prodcode | 30 | Using where | | 1 | SIMPLE | c | eq_ref | PRIMARY,custcode | PRIMARY | 32 | datacube.t.custcode |1 | | | 1 | SIMPLE | tt| ref| custcode,prodcode | prodcode | 32 | datacube.i.prodcode | 30 | Using where | ++-+---++---+--+-+-+--+--+ 4 rows in set (0.00 sec) sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNER JOIN across multiple tables appear very slow.
Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = " SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales Quantity', ROUND(avg(t.netsales),2) 'PastSales', ROUND(avg(tt.netsales),2) 'Sales', Round(((avg(t.netsales) - avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff', Round(((avg(t.salesvolume) - avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff' from customer c INNER JOIN transaction t ON c.custcode = t.custcode AND date(t.date) >= '$past_date_period_startdate' AND date(t.date) <= '$past_date_period_enddate' AND c.salescode = '$salescode_param' INNER JOIN transaction tt ON c.custcode = tt.custcode AND date(tt.date) >= '$input_date_period_startdate' AND date(tt.date) <= '$input_date_period_enddate' AND c.salescode = '$salescode_param' AND t.prodcode = tt.prodcode INNER JOIN inventory i ON i.prodcode = t.prodcode AND i.prodcode = tt.prodcode AND i.basename = '$basename_param' WHERE i.prodname is not NULL and i.basename is not NULL GROUP BY c.salescode, i.basename, t.prodcode ORDER BY SalesDiff desc; Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub query is extermely slow
[EMAIL PROTECTED] wrote: SELECT DISTINCT i.basename FROM inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND t.date >= '2004-01-01' AND t.date <= '2004-01-31' INNER JOIN transaction tt ON i.prodcode = tt.prodcode AND tt.date >= '2005-01-01' AND tt.date <= '2005-01-31' INNER JOIN customer c ON c.custcode = t.custcode AND c.custcode = tt.custcode WHERE i.basename is not NULL and i.prodname is not NULL order by i.basename This should give you a list of inventory basenames for all current customers (their names are still in the customer table) that "had transactions" (ordered products?) during both JAN 2004 and JAN 2005. This list will show only the products that were ordered during BOTH time periods BY THE SAME CUSTOMER at least once (at least one repeat sale, year to year, in JAN). Is this what you were after or was there a different question you were trying to answer? Hi, while this is much faster than subquery, I found there is "cumsy" way faster than this series inner joins. I dicovered that if I create two different temporary tables with "create view as select ..." for 2 differnet period, the join between this temp tables is also much faster than this series inner joins approach. With 16 records in the transaction table, the series inner joins takes very long time (more than 15 minutes) give out a result. Any comment? Thanks Sam Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
indexing operation running for few hours
Hi, I don't know what is wrong with running indexing on this table. It have been last for nearly 3 hours. here is the command I run at the prompt of mysql> : alter table inventory add index(basename); Because it is running indexing, all operation seems hang up. As far as I can remember I only injected 2300+ records in this table, compared to the transaction table in the same database which has 16 records ,this table is a tiny one in size. I run index on the trasactoin table, and it only took 43 min finishsed indexting on the date field. Can anybody tell me what is going on? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize mysql 5.01
HI, Currently 16 records in a table slow down the search alot. Most of the queries are based on the date field. so I m adding index on it at the moment. I m wondering how much speed I can gain after finshed the index. Apart from indexing, what other alternativies I can use to speed up the search? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub query is extermely slow
[EMAIL PROTECTED] wrote: I would simplify it by converting everything to us explicit (not implicit) JOIN statements,skipping unnecessary type conversions, and logically merging your conditions. Here is your original query, slightly reformatted. SELECT DISTINCT i.basename FROM inventory i, transaction t, customer c WHERE i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and ( (date(t.date) >= "2004-01-01" and date(t.date) <= "2004-01-31" ) and i.basename IN ( select DISTINCT ii.basename from inventory ii, transaction tt, customer cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and ii.basename is not NULL and ii.prodname is not NULL and( date(tt.date) >= "2005-01-01" and date(tt.date) <= "2005-01-31") ) ) order by i.basename Here is my proposal: SELECT DISTINCT i.basename FROM inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND t.date >= '2004-01-01' AND t.date <= '2004-01-31' INNER JOIN transaction tt ON i.prodcode = tt.prodcode AND tt.date >= '2005-01-01' AND tt.date <= '2005-01-31' INNER JOIN customer c ON c.custcode = t.custcode AND c.custcode = tt.custcode WHERE i.basename is not NULL and i.prodname is not NULL order by i.basename This should give you a list of inventory basenames for all current customers (their names are still in the customer table) that "had transactions" (ordered products?) during both JAN 2004 and JAN 2005. This list will show only the products that were ordered during BOTH time periods BY THE SAME CUSTOMER at least once (at least one repeat sale, year to year, in JAN). Is this what you were after or was there a different question you were trying to answer? Hi Shawn, this works great. This will give all products that exist in two different period. What if I also want to list products that in period 1 but not in peirod 2; while other products in preriod 2 but not in period 1? Do you think simply take out the join for i.prodcode = t.prodcode and i.prodcode = tt.prodcode and use left/right join will yield the desired result? Thanks Sam Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub query is extermely slow
Clint Edwards wrote: Sam, Can you create an index on transaction.date, then run your query again? If that is not better send me the output of 'explain ' again. This index may not be a good idea, depending on how many transaction are in the table on a specified date. May I ask how to add index to a field after a table is created? Thanks Sam Clint From: sam wun <[EMAIL PROTECTED]> To: Clint Edwards <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information: When was the last time 'analyze table ' (inventory, transaction, customer) was executed? Hi, here is the result of the analyze command: mysql> analyze table inventory,transaction, customer; +--+-+--+--+ | Table| Op | Msg_type | Msg_text | +--+-+--+--+ | datacube.inventory | analyze | status | OK | | datacube.transaction | analyze | status | OK | | datacube.customer| analyze | status | OK | +--+-+--+--+ 3 rows in set (0.83 sec) OS: MySQL Version: Mysql 5.0 Available Ram: 254RAM Output from 'SHOW CREATE TABLE ' (inventory, transaction, and customer): Output from "SHOW VARIABLES LIKE '%buffer%';": mysql> SHOW CREATE TABLE inventory; +---+---+ | Table | Create Table | +---+---+ | inventory | CREATE TABLE `inventory` ( `prodcode` varchar(32) NOT NULL default '', `qty` decimal(9,2) NOT NULL default '0.00', `lastupdatedate` date NOT NULL default '-00-00', `prodname` varchar(32) default 'UNKNOWN', `basename` varchar(32) default 'UNKNOWN', `vendorname` varchar(50) default 'UNKNOWN', `cost` decimal(9,2) NOT NULL default '0.00', PRIMARY KEY (`prodcode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+---+ 1 row in set (0.39 sec) mysql> SHOW CREATE TABLE transaction; +-+
Re: sub query is extermely slow
Clint Edwards wrote: Sam, Can you send the output of the following: #>explain \G Thanks for the suggestion, here is the output of the explain query: mysql> explain select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and ((date(t.date) >= "2004-01-01" and date(t.date) <= "2004-01-31") and i.basename IN (select DISTINCT ii.basename from inventory ii, transaction tt, customer cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) >= "2005-01-01" and date(tt.date) <= "2005-01-31"))) order by i.basename; +++---++---+--+-+--+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra| +++---++---+--+-+--+--+--+ | 1 | PRIMARY| c | index | PRIMARY | PRIMARY | 32 | NULL | 317 | Using index; Using temporary; Using filesort | | 1 | PRIMARY| t | ref| custcode,prodcode | custcode | 32 | datacube.c.custcode | 36 | Using where | | 1 | PRIMARY| i | eq_ref | PRIMARY | PRIMARY | 32 | datacube.t.prodcode |1 | Using where | | 2 | DEPENDENT SUBQUERY | cc| index | PRIMARY | PRIMARY | 32 | NULL | 317 | Using index; Using temporary | | 2 | DEPENDENT SUBQUERY | tt| ref| custcode,prodcode | custcode | 32 | datacube.cc.custcode | 36 | Using where | | 2 | DEPENDENT SUBQUERY | ii| eq_ref | PRIMARY | PRIMARY | 32 | datacube.tt.prodcode |1 | Using where | +++---++---+--+-+--+--+------+ 6 rows in set (0.01 sec) Clint From: sam wun <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and ((date(t.date) >= "2004-01-01" and date(t.date) <= "2004-01-31") and i.basename IN (select DISTINCT ii.basename from inventory ii, transaction tt, customer cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) >= "2005-01-01" and date(tt.date) <= "2005-01-31"))) order by i.basename Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sub query is extermely slow
Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and ((date(t.date) >= "2004-01-01" and date(t.date) <= "2004-01-31") and i.basename IN (select DISTINCT ii.basename from inventory ii, transaction tt, customer cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) >= "2005-01-01" and date(tt.date) <= "2005-01-31"))) order by i.basename Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create view not working on mysql 4.1.8
sirisha gnvg wrote: we are working on mysql 4.1.8 and windows XP platform. This version supports views.We created a view like this mysql>use sample mysql>create view v1 as select * from sam2; sam2 is a table We got an error errror 1024(42000):you have an error in your sql syntax; we checked the manual but the syntax is as written above.we also saw error 1024 in error notepad but we could not get any further details. please explain the fault in above statement Yahoo! India Matrimony: Find your life partneronline. Why don't you upgrade to 5.0? I use create view in 5.0 without any problem. Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert if Update failed without Select
[EMAIL PROTECTED] wrote:
Please also note hat UPDATE returns the number of records updated. If your
UPDATE returns 0, you know that the record does not exist, and you might
want to INSERT instead.
There is one situation where the number of records updated will return
0, yet the row exists. If you update the record with the exact same
information, mySQL will return a count of 0 rows updated. Yet the row
exists.
Hi, how can I find out the return value from update if I execute update
in perl dbi?
Here is my sample code:
$sql ="update transaction set salesvolume=$salesvol, netsales=$netsales,
transtype=\"$transtype\",
returnreason=\"$returnreason\"
where prodcode=\"$prodcode\" and
custcode=\"$custcode\" and date=$transdate";
$udpate_sql = qq {$sql;};
$sth = $dbh->prepare($update_sql) || die "prepare: $update_sql:
$DBI::errstr";
$sth->execute || die "Unable to execute query: $dbh->errstr\n";
$sth->finish;
print $sql;
Currently I found somethingn strange with mysql(perhaps with perl dbi as
well), after setup 1 or 2 test data, I tried to make a change to one of
the fiield in a table, but after update is completed, I don't see the
corresponding field in a record is updated by the new value.
If I copy the update statement exactly from the output of the print
statement ( print $sql), and paste it to the mysql login prompt to
execute it, the update statement update the record instantly. I don't
know what is happening here, why perl dbi does not do what the update
statement supposed to do? Have I missed a commit statement? but I don't
have idea how to place a commit statement to perl dbi.
Thanks
Sam
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update does not doing updates
Hi,
I use the following statement to update a table, but I don't see the
testing record being updated:
$udpate_sql = qq {update transaction
set salesvolume=$salesvol,
netsales=$netsales, transtype="$transtype",
returnreason="$returnreason"
where prodcode="$prodcode" and
custcode="$custcode" and date="$transdate";};
$sth = $dbh->prepare($update_sql) || die "prepare: $update_sql:
$DBI::errstr";
$sth->execute || die "Unable to execute query: $dbh->errstr\n";
$sth->finish;
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert if Update failed without Select
Hi, Without using Select statement, how can I execute Insert SQL statement if Update action is failed? I may be asking for too much. If Select statemnet have to be used to determine the existence of a recordset, what is an efficient way to execute Insert if Update is failed? I m using MySQL 5.0 Any example would be very apprecated. Thanks Sam. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error in Insert on Duplicate Key Update
HI,
the following insert/update produced error. I m using MySQL 5.0.
$insert_sql = qq {insert into inventory
(prodcode,qty,lastupdatedate,prodname,basename,vendorname,cost)
values (?,?,?,?,?,?,?) ON DUPLICATE KEY
UPDATE
qty=$qty,lastupdatedate=$date,prodname=$prodname,basename=$basename,
vendorname=$vendorname,cost=$cost;};
$sth = $dbh->prepare($insert_sql) || die "prepare: $insert_sql:
$DBI::errstr";
$sth->execute($prodcode,$qty,$date,$prodname,$basename,$vendor,$value)
|| die "Unable to execute query: $dbh->errstr\n";
Error:
DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near 'abc4_93419_06888,basename=Revlon,
vendorname=,cost=' at line 3 at insert-inventory-mysql.pl line 55,
line 1.
Unable to execute query: DBI::db=HASH(0x864a988)->errstr
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: easy way to drop a tempoary table created by Create view?
sam wun wrote: Gleb Paharenko wrote: Hello. Use -e command line option for mysql client program. For example: mysql -u root -p test -e 'drop view v' Hi, does it take wildcard? something like: mysql -u root -p test -e 'drop view v*' Or even drop all view tables (only affect the view tables): mysql -u root -p test -e 'drop view *' Sam. Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: easy way to drop a tempoary table created by Create view?
Gleb Paharenko wrote: Hello. Use -e command line option for mysql client program. For example: mysql -u root -p test -e 'drop view v' Hi, does it take wildcard? something like: mysql -u root -p test -e 'drop view v*' Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure for advance reporting
Hi, Can anyone tell me where I can download examples for creating Stored Procedure in perl DBI? It seems that I can't create advance reporting with basic operations (select, create) in perl dbi, so I m looking for whether Stored Proc in MySQL can solve my problem. Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
easy way to drop a tempoary table created by Create view?
Hi, What is the easiest way to drop a table created by "create view"? I m using mysql 5.0. I would like to execute the drop from Unix commandline. Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: traverse the recordset more than once
Steve Buehler wrote: At 08:11 PM 1/5/2005, you wrote: Hi list, How can I traverse the recordset moret than once? For example, the recordset $sth is returned from executing the sql statement. The following statement is writen with the "for" loop for retrieving each record from the recordset. $aref = $sth->fetchrow_arrayref However when the "for" loop is finished, the pointer of the recordset is point to the end of the recordset. How can I revise the point to the beginning of the recordset ($sth)? Another thing you might try is to Set $arefgood = $sth->fetchrow_arrayref $arefusethisone = $arefgood Use the $arefusethisone in the for loop. When you want to restart the for loop, just set "$arefusethisone = $arefgood" again. Doing this, you should always have your original. This will probably work in any language you are programming in, but since I don't program in to many languages, I can't give you a definite on that one. Thanks I hvae thought about this method, but conerning that it will require extra memory to keep the orginal copy of the recordset, and also whenever assign the original recordset to the temprecordset, it will need to take extra time to do the copies, I assumed this is not shadow copies. Sam Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: traverse the recordset more than once
Steve Buehler wrote: At 08:11 PM 1/5/2005, you wrote: Hi list, How can I traverse the recordset moret than once? For example, the recordset $sth is returned from executing the sql statement. The following statement is writen with the "for" loop for retrieving each record from the recordset. $aref = $sth->fetchrow_arrayref However when the "for" loop is finished, the pointer of the recordset is point to the end of the recordset. How can I revise the point to the beginning of the recordset ($sth)? Kind of depends on what programing language you are writing the script in. If you are using PHP, you might look into the reset function. I "think" that might do what you want. At least it is a startmaybe http://us3.php.net/manual/en/function.reset.php I m using Perl DBI. Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
traverse the recordset more than once
Hi list, How can I traverse the recordset moret than once? For example, the recordset $sth is returned from executing the sql statement. The following statement is writen with the "for" loop for retrieving each record from the recordset. $aref = $sth->fetchrow_arrayref However when the "for" loop is finished, the pointer of the recordset is point to the end of the recordset. How can I revise the point to the beginning of the recordset ($sth)? Thanks Sam. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User can't login to his DB.
Hi list,
I use the following commands to create a user hubert access to his DB
only, but got the following error:
# perl view.pl
DBI connect('datacube','hubert',...) failed: Access denied for user
'hubert'@'localhost' (using password: YES) at view.pl line 8
Unable to connect: Access denied for user 'hubert'@'localhost' (using
password: YES)
SQL Grant commands:
GRANT SHOW DATABASES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password';
GRANT SHOW DATABASES ON *.* TO hubert@'10.%' IDENTIFIED BY 'password';
GRANT ALL ON hubertsdb.* TO hubert;
Perl DBI commands:
$database = "datacube";
$username = "hubert";
$pw = "password";
$dbh = DBI->connect("DBI:mysql:$database",$username,$pw);
die "Unable to connect: $DBI::errstr\n" unless (defined $dbh);
What is wrong with this error and how to fix it?
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to determine a field is part of the primary key in a table?
Hi list, I m writing a perl program and would like to use it (wiht mysql command) to determine whether a field name is (or is part of) a primary key of a table. If you know how to handle this, please drop me a line. Thanks and Happy New Year. Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
value of array to sql statement
Hi list,
There is problem when with executing the sql statement in mysql 5.0.
I m not sure whether version 4.x has the same problem.
The problem is if I use static (hardcoded) values in the SQL statement,
mysql returns result for each element in the array.
If I use dynamically allocated value that assigned to the array, the sql
statement only return result for the first element of the array.
To illustrate the problem please see the code below.
The following perl code does return result when execute the SQL statement:
@outlets = ("07-6-0057","07-3-0051","07-2-0036");
my $mycustcode = "$outlets[1]"; # also work for [0] or [2]
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};
However if I change the code to be a bit more dynamic like the following:
In html.pl: # the following value (in the OPTION tag) will be submitted
to the query1_result.cgi.
while ($aref = $sth->fetchrow_arrayref){
print "[0],>$aref->[1]: $aref->[2]\n";
}
In query1_result.cgi:
sub split_outlets_to_array
{
my ($s) = @_;
@outlets = split(',',$s);
return @outlets;
}
$outlet_str = $in{'outlets'}; # the outlets contains a list of custcodes
that seperated by comma.
@outlets_array = &split_outlets_to_array($outlet_str);
my $mycustcode = "$outlets_array[0]"; # but value of [1] and [2] can't
make the following SQL statement return a result.
$sql = qq {
select c.custcode, c.custname, c.type, sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and c.custcode = "$mycustcode"
group by c.custcode;};
Summary:
The problem with the second case is that outlets_array[0] does make the
SQL statement return result, but [1] and [2] does not.
With the first case (with hardcoded values), all elements of
outlets_array does make the SQL return result.
There may be problem in the code $in{'outlets'}. However from printing
each element of the array on the html page, I found nothing wrong with
the value, they are all printed in the following format on the html page:
outlets[0]: 07-6-0057
outlets[1]: 07-3-0051
outlets[2]: 07-2-0036
I don't know what caused this error when execute it with the SQL statement.
I tried to turn on warninig with -w, but not sure how to see them when
running in web browser.
Thanks
Jack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparison SQL always return false
Hi,
I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
I found that if I assign a hard coded string (with double quote) to the
string variable which in turns become part of the SQL statement, the SQL
does return result.
If I assign a value which is return from an array in perl (eg.
@array[1]) and use this variable in the SQL statement, the SQL does not
return any result.
For example:
The following code does not return any result
my $mycustcode = "@outlets[1]"; # this array contains string "07-2-0057"
However, if I use the following hard coded assignment, the SQL statement
returns result:
# my $mycustcode = "07-2-0057";
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type, sum(t.netsales)
as sales
from customer c, transaction t
where c.custcode = t.custcode
and date(t.date) >= "$start_date"
and date(t.date) <= "$end_date"
and (c.type = "EXPORT" or c.type = "LOCAL")
and (c.custcode = "$mycustcode")
group by c.custcode;};
I may be using incorrect way to split the tokens, here is the function
to extract a list of the token:
$outlet_str = $in{'outlets'}
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sum on Float type
Hi, I created a Transaction table with a field Cost which is a Float type with only 2 precision eg. 123.01. When I use the following sql statement to make a sum of this field, it returned a Float number with more than 2 precision numbers eg. 456.92384933 select t.custcode, sum(t.cost) as "Sub-Total" from customer c, transaction t where c.custcode = t.custcode group by t.custcode I don't know why the Sum function returns more than 2 precision number. If I should not use Float type for the Cost field, what type should I use? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tricky timetable based query
I recieve a delimited file whose fields are: day,slot,subject,room An example of this file is: 2,1,Mat,R1 3,1,Sci,R6 1,2,Sci,R6 3,2,Mat,R3 1,3,Eng,R2 2,3,Eng,R5 1,4,Mat,R7 3,4,Eng,R9 I need a mysql query that will generate a timetable which looks like: Day1Day2Day3 --- 1 Mat R1 Sci R6 2 Sci R6 Mat R3 3 Eng R2 Eng R5 4 Mat R7 Eng R9 Any help would be appreciated sam ** This message is intended for the addressee named and may contain privileged information or confidential information or both. If you are not the intended recipient please delete it and notify the sender. **
RE: MySQL does not automatically start at BOOT time??
Kirti: I think you already did that as you said in you first msg about you installation steps. > > >>Setup to start MySQL automatically > > >> % cp /usr/local/ No>/mysql.server > > >>/etc/init.d Did you get my prev msg where I suggested to check the previleges on your mysql. Usually I do it this way: 8) Change ownership of binaries to root and ownership of the data directory to the user that you will run mysqld as (in our case mysql): chown -R root . chown -R mysql var chgrp -R mysql . ( this is important too) 9) To start MySQL server automatically when you boot your machine, copy the mysql.server script to the `/etc/init.d' directory with the name mysql, and then make it executable cp support-files/mysql.server /etc/init.d/mysql chmod +x /etc/init.d/mysql 10) Activate to run the script at system startup by adding to the chkconfig list chkconfig --add mysql chkconfig --level 345 mysql on For more information read http://dev.mysql.com/doc/mysql/en/Automatic_start.html Good luck... Sam --- "Kirti S. Bajwa" <[EMAIL PROTECTED]> wrote: > Brian: > > No. From where I copy this file from? Is it stright > copy or something else? > > You might be my savior!! > > Thanks. > > Kirti > > -Original Message- > From: Brian Duke [mailto:[EMAIL PROTECTED] > Sent: Sunday, May 09, 2004 5:44 PM > To: 'Kirti S. Bajwa'; [EMAIL PROTECTED] > Subject: RE: MySQL does not automatically start at > BOOT time?? > > > I know that feeling. perhaps I can help. > do you have an /etc/rc.d/init.d/mysqld file? > > > -Original Message- > > From: Kirti S. Bajwa [mailto:[EMAIL PROTECTED] > > Sent: Sunday, May 09, 2004 3:29 PM > > To: '[EMAIL PROTECTED]' > > Subject: RE: MySQL does not automatically start at > BOOT time?? > > > > Hello List: > > > > I have spent the entire weekend solving this > problem without success. > > PLEASE > > HELP. > > > > After the last response from Paul, and MySQL not > starting at BOOT time, I > > thought that I may have done something wrong. So I > went ahead and did a > > fresh install of RH9 & MySQL Source Distro with > one difference. This time > > I > > installed "mysql-4.0.18". I followed the > installation script as noted > > below, > > still MySQL does not automatically re-start at > BOOT time. > > > > For those who did not follow this posting; > previously I have installed > > MySQL > > and followed the same installation script. Each > time MySQL started at BOOT > > time. The only major difference is that in > previous cases, I installed > > from > > the "binaries" & this time I installed from > "source". > > > > I can start the MySQL by "mysqld_safe". > > > > Paul had suggested looking into the MySQL error > logs. Since I am new, I > > can't find where are error logs are kept. I have > looked in "/var/log" & > > "/usr/local/mysql" and have not found any error > logs for MySQL. > > > > I sure need help!! > > > > Thanks. > > > > Kirti > > > > > > > > > > -Original Message- > > From: Paul DuBois [mailto:[EMAIL PROTECTED] > > Sent: Saturday, May 08, 2004 6:44 PM > > To: Kirti S. Bajwa > > Subject: RE: MySQL does not automatically start at > BOOT time?? > > > > > > >Paul: > > > > > >Did what you said. Here is the response: > > > > > >% chkconfig --list mysql.server > > >mysql.server 0:off 1:off 2:on 3:on 4:on 5:on > 6:off > > > > That looks correct. > > > > If you did chkconfig mysql.server on, *and then > restarted*, and the > > server still doesn't start automatically, check > the MySQL error log > > to see if it has a clue. If it doesn't, I dunno. > > > > > > > > > > >What next? > > > > > >Kirti > > > > > > > > >-Original Message- > > >From: Paul DuBois [mailto:[EMAIL PROTECTED] > > >Sent: Saturday, May 08, 2004 2:24 PM > > >To: Kirti S. Bajwa; [EMAIL PROTECTED] > > >Subject: Re: MySQL does not automatically start > at BOOT time?? > > > > > > > > >At 14:08 -0400 5/8/04, Kirti S. Bajwa wrote: > > >>Hello List: > > >> > > >>I have been learning MySQL. I have installed & > tested MySQL server 3-4 > > >times > > >
Mac OSX MySQL Client 4.1.1
My Mac mysql client does not appear to work correctly. The DB is fine and setup working great but I cannot use that version of the mysql client. Right now I'm using the 4.0.18 client on my mac. Are there known bugs with the 4.1.1 Mac OSX client? Sam Peterson
Oracle Listener Like Functionality
We are designing a new solution and I have not been able to find functionality anywhere in the MySQL documentation or literature. With an Oracle database you can bring up the database, recover what is needed, all while leaving the listener down. The listener in Oracle just allows clients to connect if it is up. With the listener down no external clients can connect to the DB. Is there similar functionality in MySQL? Right now my work around is to bring up servers by default on a different port than 3306 so that clients aren't connecting to a slave or a master in a 'Not Ready' state. Once the database is recovered then I'm reconfiguring the port. Has anyone else done something similar to this? I'm new to MySQL so any better suggestions would be appreciated. Sam Peterson
How to protect MySQL server from intruders ?
Hello Group I would like to know if there is some thing I can do during the configuration of MySQL server so that I could restrict only one user from accessing the database and all others are restricted from the access of the databas? I mean I do not want anybody else to access the database from the outside world, except one particular application (with one username and password) which I would like to give access to , that would be running in the same system as the database Bottomline restrict the outside world traffic for the database !!! = Sam _o /\ __o -< \_/ \ < \, __(_)/_(_)/\___(_) /_(_)__ __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free software and open source
On Sun, 11 Apr 2004, Saurabh Data wrote: > Can anyone in your own words clarify the difference between "open source" > and "free software". Don't take our words for it, get it from the Gnu's mouth: http://www.gnu.org/philosophy/free-software-for-freedom.html -sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to turn-off the replication?
That was quick! I am trying to turn it off from master. --Sam -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 23, 2004 12:34 PM To: Sam Yathira; [EMAIL PROTECTED] Subject: Re: How to turn-off the replication? Are you wanting to turn off replication from the master or the slave? On Tue, 23 Mar 2004, Sam Yathira wrote: > Content-Type: multipart/alternative; name="unnamed" Content-Transfer-Encoding: 7bit Content-Description: Content-Type: text/plain; name="InterScan_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Description: Content-Type: text/plain; name="unnamed" Content-Transfer-Encoding: 7bit Content-Description: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Burlington Capital Markets and each of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorised to state them to be the views of any such entity. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal written confirmation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to turn-off the replication?
Hi, Everybody talks about setting up replication, but any documentation on turning off replication? Regards, Sam This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Burlington Capital Markets and each of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorised to state them to be the views of any such entity. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal written confirmation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with installation
Hi guys. I bought a script .And guys ,who make an installation on my data-base have a problem with it.Installation is completed but My SQl can not read it.When I trying to open my database trough internet explorer I see error : My user name KOSTA turns to [EMAIL PROTECTED] I don't know why .And I see this on my Display: Invalid query 'select login from fvf_user where login='Test'' Invalid query 'select value from fvf_counter where (code1='daily') and (code2='common')' Access denied for user: '[EMAIL PROTECTED]' to database 'HotandsexyRussianBrideca' Could you help me Please.Tell me what should I do.I do not have a computer skills. So tell me know step by step process,Please. Konstantin. [EMAIL PROTECTED]
Re: Query was empty -- any ideas
Okay, I feel dumb. After seeing my message appear on the list, in a
different font, I can see that here: $result =
mysql_query($sq12,$connection) or die (mysql_error()); I typed "$sq12"
instead of "$sql2" -- sorry! Works now.
Sam
Sam Folk-Williams wrote:
Hi,
This script keeps returning the error "Query was empty" via php's
mysql_error() function. I think this is just a run-of-the-mill query,
of which I have hundreds on this site. I just can't figure this out...
any ideas? I'm probably missing something small. I have tested the
variables and they all hold data. The script is below. User uploads a
form and data about that form is inserted into the DB
//include DB connection
include "../../../include/db.inc";
//if the form has been submitted...
if (is_uploaded_file($form))
{
//First, save the file to the forms directory...
move_uploaded_file($form, "../forms/$form_name");
//Second, insert info about the form into form_list
(form path, form keywords and section_id)...
//***THIS IS THE QUERY RESULTING IN ERROR "QUERY WAS
EMPTY"
$sql2 = "INSERT INTO form_list
(form_path, form_key, section_id)
VALUES ('$form_name',
'$form_key', '$section_id')";
$result = mysql_query($sq12,$connection) or
die (mysql_error());
//Third, grab the id of the form you just inserted
$form_id = mysql_insert_id();
**
?>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query was empty -- any ideas
Hi,
This script keeps returning the error "Query was empty" via php's
mysql_error() function. I think this is just a run-of-the-mill query, of
which I have hundreds on this site. I just can't figure this out... any
ideas? I'm probably missing something small. I have tested the variables
and they all hold data. The script is below. User uploads a form and
data about that form is inserted into the DB
//include DB connection
include "../../../include/db.inc";
//if the form has been submitted...
if (is_uploaded_file($form))
{
//First, save the file to the forms directory...
move_uploaded_file($form, "../forms/$form_name");
//Second, insert info about the form into form_list (form path, form
keywords and section_id)...
//***THIS IS THE QUERY RESULTING IN ERROR "QUERY WAS EMPTY"
$sql2 = "INSERT INTO form_list
(form_path, form_key, section_id)
VALUES ('$form_name', '$form_key', '$section_id')";
$result = mysql_query($sq12,$connection) or die (mysql_error());
//Third, grab the id of the form you just inserted
$form_id = mysql_insert_id();
**
?>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL Database
Lynch 'im!!! :-) On Wed, 21 Jan 2004 13:33, Jochem van Dieten wrote; > Douglas Sims wrote: > > > > You should check out: http://onlamp.com/ L.A.M.P. > > (Linux/Apache/MySQL/Perl(or PHP) > > Or Linux/Apache/Middleware/PostgreSQL ;-) > > Jochem > > -- > I don't get it > immigrants don't work > and steal our jobs > - Loesje > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- Sam Vilain, [EMAIL PROTECTED] Only the ignorant man becomes angry. The wise man understands. --Indian wisdom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL GPL License Question
On Thu, 15 Jan 2004 08:44, Computer Mail wrote; > If I create a program that just queries data from a > MySQL table and processes it...am I required to > release that program under the GPL? No. The below; 0. This License applies to any program or other work which contains a notice placed by the copyright holder saying it may be distributed under the terms of this General Public License. The "Program", below, refers to any such program or work, and a "work based on the Program" means either the Program or any derivative work under copyright law: that is to say, a work containing the Program or a portion of it, either verbatim or with modifications and/or translated into another language. RMS has made it quite clear that a "work based on the program" means that you're linking the programs together, as with `ld' (or a *very* similar logical equivalent). Bundling GNU products with commercial software is openly encouraged. The output is unrestricted; Activities other than copying, distribution and modification are not covered by this License; they are outside its scope. The act of running the Program is not restricted, and the output from the ^^^ Program is covered only if its contents constitute a work based on ^^ the Program (independent of having been made by running the ^^^ Program). Whether that is true depends on what the Program does. This clause does not catch the output from a MySQL server. On the other hand, if you were to link the MySQL binary directly into your program - avoiding the SQL server - then you would need to purchase a commercial license from MySQL AB to avoid the requirement for the derived work to be covered by the GPL. -- Sam Vilain, [EMAIL PROTECTED] It is better to be violent, if there is violence in our hearts, than to put on the cloak of nonviolence to cover impotence. -- Mahatma Gandhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VARCHAR 255 versus TINYTEXT
On Tue, 13 Jan 2004 12:40, Justin French wrote; > Can someone please calrify when I would use VARCHAR 255, and when > I would use TINYTEXT (which also has a length of 255)? I think the difference between a VARCHAR and a TEXT column is that the TEXT is saved as a reference to its own row-like object, that requires another access to the tablespace to retrieve. Based on this possibly rash assumption, it would seem that it is a good idea to use a TINYTEXT if you have a column which is usually not selected, if you are scanning the entire table (via unindexed columns) a lot. -- Sam Vilain, [EMAIL PROTECTED] "Automatic" simply means that you cannot repair it yourself. - anon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deciding whether to convert to InnoDB
Set up a load test system which is as near a replica of your production environment as possible. Capture an hour's worth of transactions. Load an copy of your database. Play the queries in and see how long it takes. Any advice without taking something like the above steps is pure speculation, and any change a gamble. It depends on so many factors (locking contention, where the bottlenecks are, what the access pattern is, configuration of disks, etc) that there is no clear-cut answer to your question. Other than, "it might" :). Set up a micro-version of your application, see if you can convince yourself that there might be benefits. On Mon, 05 Jan 2004 21:02, Travis Reeder wrote; > Hi, > > I'm sure this has been asked before, but I cannot find solid evidence as > to whether switching would provide us with any benefits. > > We currently run MyIsam tables on 4.1.x and we are continuously > processing 24 hours/day and using about 20 tables heavily. The process > is generally doing Updates or Inserts depending on whether the row is > available for updates, otherwise new rose is inserted and then updates > until the next time bucket. It's always a different time bucket though, > not always the same row being used. We found that running 3 processing > threads seems to be around optimal (10 was too many, 1 was too little) > for being able to process the maximum amount. Mysql runs at 100% pretty > much constantly. > > Now would InnoDB help in this situation? Would it allow us to increase > the thread count to push more through in a shorter amount of time > (because the tables wouldn't be locking)? > > And if so, would it be enough to justify the extra space required for > innodb? -- Sam Vilain, [EMAIL PROTECTED] Please dont lie to me, unless youre absolutely sure Ill never find out the truth. ASHLEIGH BRILLIANT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binary Log replay utility?
No-one out there doing step-by-step replay of binary logs ? On Tue, 30 Dec 2003 17:52, Sam Vilain wrote; > Hi there, > > Is there a utility out there to do transaction-at-a-time view and/or > replay with MySQL's binary log ? Primarily for audit / debug > purposes. > > I'm only interested in using it with the proper database back-end, not > ISAM. -- Sam Vilain, [EMAIL PROTECTED] Bible Misinterpretations # 3: Motorcycles The roar of David's Triumph was heard through out the land... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binary Log replay utility?
Hi there, Is there a utility out there to do transaction-at-a-time view and/or replay with MySQL's binary log ? Primarily for audit / debug purposes. I'm only interested in using it with the proper database back-end, not ISAM. -- Sam Vilain, [EMAIL PROTECTED] In married life, three is company and two none. OSCAR WILDE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Package Install for Mac OS X 10.3 (Panther)
I used the MySQL Complete package from Server Logistics and it installed fine on my clean Panther install. Also, the MySQL Preference Pane and CocoaSQL work fine as well. Thanks, -Sam On Tuesday, October 28, 2003, at 11:08 PM, Sam Carpenter wrote: My bad, I should have been more precise. I am not upgrading - I have done a clean install. So, now I need to re-install MySQL. -Thanks On Tuesday, October 28, 2003, at 11:00 PM, Kim Kohen wrote: I am upgrading to Panther -- 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: MySQL Package Install for Mac OS X 10.3 (Panther)
My bad, I should have been more precise. I am not upgrading - I have done a clean install. So, now I need to re-install MySQL. -Thanks On Tuesday, October 28, 2003, at 11:00 PM, Kim Kohen wrote: I am upgrading to Panther -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Package Install for Mac OS X 10.3 (Panther)
I am upgrading to Panther and I need to install MySQL. Does anybody know if the Mac OS X Installer Package v10.2 (Standard) works with 10.3? If it doesn't then what would be the best installation method on a clean 10.3 installation? Thanks, -Sam Carpenter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using OR in select statement with distinct results
Thanks everyone, I suspected it was something quite simple. Sam Matt W wrote: - Original Message - From: "Paul DuBois" Sent: Thursday, September 18, 2003 8:17 PM Subject: Re: using OR in select statement with distinct results At 7:47 PM -0500 9/18/03, Sam Folk-Williams wrote: Hi, I keep having this fundamental problem. It seems like this should be easy enough, but whenever I have a query where I'm using OR in the WHERE clause and the query is performed on multiple tables, I get strange results. For example: SELECT t1.title, t2.auth_name FROM t1, t2, WHERE cat_id = 1 OR cat_id = 2 AND t1.auth_id = t2.auth_id You probably should use parentheses in your WHERE clause to explicitly group terms the way you want them evaluated. Yeah, like WHERE (cat_id=1 OR cat_id=2) AND ... However, I just replied to say that it would be easier and cleaner looking to use IN: WHERE cat_id IN (1, 2) AND ... :-) Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using OR in select statement with distinct results
Hi, I keep having this fundamental problem. It seems like this should be easy enough, but whenever I have a query where I'm using OR in the WHERE clause and the query is performed on multiple tables, I get strange results. For example: SELECT t1.title, t2.auth_name FROM t1, t2, WHERE cat_id = 1 OR cat_id = 2 AND t1.auth_id = t2.auth_id I want this query to give me a list of all article titles from a content table (t1) and the author's name from a people table (t2) where, the author ID from t1 matches that of t2, AND the article is either from Category 1 or Category 2. What I get instead, though, is every possible combination of article titles and author names from both tables. So, if there were 10 authors in t2, each title would be listed 10 times, once with each author name. Should I be using a different JOIN syntax? Am I totally mis-using the OR operator? Note that if I only want results from one category (i.e.: SELECT t1.title, t2.auth_name FROM t1, t2, WHERE cat_id = 1 AND t1.auth_id = t2.auth_id) everything works just fine. Thanks for any help, Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query challanged newbie
On Wed, 23 Jul 2003, Sam Tregar wrote:
> SELECT a2.name
> FROM alpha AS a1, bravo AS b, alpha AS a2
> WHERE a1.name = 'fred' AND
> a1.id = b.afrom AND
> b.ato = b.id;
Whoops, that last line should read:
b.ato = a2.id;
-sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query challanged newbie
On Tue, 22 Jul 2003, Larry Brown wrote: > Table alpha: > > id int(3) not null auto_increment, > name char(12) not null, > Table bravo: > > afrom int(3) not null, > ato int(3) not null, > in the php variable coming in I have fred and need to run a query to get > george. You just need to join from alpha to bravo and then from bravo back to alpha. To do that you need to alias alpha with two different names (a1 and a2, for example). Try this: SELECT a2.name FROM alpha AS a1, bravo AS b, alpha AS a2 WHERE a1.name = 'fred' AND a1.id = b.afrom AND b.ato = b.id; The same thing can be done with LEFT JOIN for a little added style, but this should work fine as-is. -sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
