MySQL University session on July 3: Advanced LUA Scripting
Hi, this Thursday, Giuseppe Maxia will give a MySQL University session: http://forge.mysql.com/wiki/Advanced_LUA_Scripting (topic: Advanced LUA Scripting) Please register for this session by filling in your name on the session Wiki page. Registering is not required but appreciated. That Wiki page also contains a section to post questions. Please use it! MySQL University sessions normally start at 13:00 UTC (summer) or 14:00 UTC (winter); see: http://forge.mysql.com/wiki/MySQL_University for more time zone information. Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. See http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the complete list. Regards, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.demailto: [EMAIL PROTECTED] Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scripting Issues
Hi,
Why don't you run the CREATE TABLE query with the IF NOT EXISTS clause
then your script could look like this
CREATE TABLE IF NOT EXISTS table_name ...;
ALTER TABLE table_name;
If the table already exists only the ALTER statement will be executed,
otherwise the ALTER statement will not do anything.
Check the MySQL Reference for more details
http://dev.mysql.com/doc/refman/4.1/en/create-table.html
HTH
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Friday 11 November 2005 14:14, Luke wrote:
> Hey all,
>
> I've got a question for you. What would be the best way to set up a set of
> srcipts to create\Update the databse when my app is installed?
>
> The problem is that I obviously can't just drop the database cause if the
> database is already installed then the client would lose all his data. I
> need a way to update the collumns in each table if they change but still
> keep the clients data. Stored Procedures, Views and Triggers are easy cause
> these I can drop then create again but tables are a bit more complex.
>
> I thought of writing something to the effect of :
>
> USE Information_Schema;
>
> IF (SELECT Table_Name FROM Tables WHERE TableName = '' AND
> Table_Schema = '') IS NULL THEN BEGIN
> {Create Table}
> END;
> ELSE
> BEGIN
> {Alter Table}
> END;
> END IF;
>
> Is there not a better way of doing things? We want to try and create\update
> the database through scripts which can then be deleted instead of writing
> an app.
>
> Any help would be greatly appreciated
>
> Thanx,
> Luke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Scripting Issues
Hey all,
I've got a question for you. What would be the best way to set up a set of
srcipts to create\Update the databse when my app is installed?
The problem is that I obviously can't just drop the database cause if the
database is already installed then the client would lose all his data. I need a
way to update the collumns in each table if they change but still keep the
clients data. Stored Procedures, Views and Triggers are easy cause these I can
drop then create again but tables are a bit more complex.
I thought of writing something to the effect of :
USE Information_Schema;
IF (SELECT Table_Name FROM Tables WHERE TableName = '' AND
Table_Schema = '') IS NULL THEN
BEGIN
{Create Table}
END;
ELSE
BEGIN
{Alter Table}
END;
END IF;
Is there not a better way of doing things? We want to try and create\update the
database through scripts which can then be deleted instead of writing an app.
Any help would be greatly appreciated
Thanx,
Luke
Re: mySQL Batch Scripting
Hi.
Remove ';' from the end of the line.
[EMAIL PROTECTED] wrote:
> [-- text/plain, encoding 8bit, charset: US-ASCII, 22 lines --]
>
> I am trying to utilize MySQL's batch execution functionality by using build scripts
> to create tables in my database (ref:
> http://dev.mysql.com/doc/mysql/en/Batch_Commands.html )
>
> Is it possible to execute additional source files from within a source file from the
> MySQL command line?
>
> Ex:
>
> mysql> \. c:\file1.sql ## execute the first sql batch file
>
>
>
>
> create table employees{...};
> \. c:\file2.sql; ## execute the second sql batch file
>
>
>
>
> create table customers{...};
>
>
>
> I 've tried this and get an error: "failed to open file 'c:\file2.sql;', error 2
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL Batch Scripting
Hi.
System error: 2 = No such file or directory
Check if file exists. Do you have permissions to read this file?
[EMAIL PROTECTED] wrote:
> [-- text/plain, encoding 8bit, charset: US-ASCII, 22 lines --]
>
> I am trying to utilize MySQL's batch execution functionality by using build scripts
> to create tables in my database (ref:
> http://dev.mysql.com/doc/mysql/en/Batch_Commands.html )
>
> Is it possible to execute additional source files from within a source file from the
> MySQL command line?
>
> Ex:
>
> mysql> \. c:\file1.sql ## execute the first sql batch file
>
>
>
>
> create table employees{...};
> \. c:\file2.sql; ## execute the second sql batch file
>
>
>
>
> create table customers{...};
>
>
>
> I 've tried this and get an error: "failed to open file 'c:\file2.sql;', error 2
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL Batch Scripting
I am trying to utilize MySQL's batch execution functionality by using build scripts to
create tables in my database (ref:
http://dev.mysql.com/doc/mysql/en/Batch_Commands.html )
Is it possible to execute additional source files from within a source file from the
MySQL command line?
Ex:
mysql> \. c:\file1.sql ## execute the first sql batch file
create table employees{...};
\. c:\file2.sql; ## execute the second sql batch file
create table customers{...};
I 've tried this and get an error: "failed to open file 'c:\file2.sql;', error 2
Re: mysql scripting
Brad Tilley <[EMAIL PROTECTED]> wrote: > > Is there a way to do regex search are replace on all entries in a mysql field? > Or, a way to uppercase or lowercase all characters for all entries in a > filed? Some of the fields are varchars with numbers and letters. In my > particular case, I have a field of serial numbers some of which are all caps > some of which are not. Also the vendor name may be DELL for one entry and > dell on another. We're standardizing the input to avoid this mess in the > future, but I'd like to clean up what's current present. All tips are > appreciated. > Take a look at UPPER(), LOWER(), REPLACE() functions in MySQL. They may help you: http://dev.mysql.com/doc/mysql/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql scripting
Hello again, Is there a way to do regex search are replace on all entries in a mysql field? Or, a way to uppercase or lowercase all characters for all entries in a filed? Some of the fields are varchars with numbers and letters. In my particular case, I have a field of serial numbers some of which are all caps some of which are not. Also the vendor name may be DELL for one entry and dell on another. We're standardizing the input to avoid this mess in the future, but I'd like to clean up what's current present. All tips are appreciated. Thanks, Brad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scripting
I too am a Java guy. The nice part of Java is that it's cross platform
and cross database. Using JDBC and pure java JDBC drivers you can move
your applications to different databases on just about any relevant
platform. This will allow you to run your java applications on Windows,
Mac, Linux, Solaris and many others. If you wanted a simple scripting
language you could use the Rhino project from Mozilla and create a
basic java application that launches java script giving you a true
scripting language that can access all the goodness of java.
From what I understand there are many possibilities outside of java
such as perl, ruby, python and others.
I'm partial to Java but more than that I'm a big believer in cross
platform solutions. I don't believe you should ever tell a customer
what to use, they should tell you what they want to use. Which accounts
for my motivation to suggest cross platform scripting/language
solutions.
Joe
On Thursday, March 4, 2004, at 10:25 AM, Victor Medina wrote:
HI!
ODBC is a nice, cheap and easy way. JDBC is a nice option also (i am a
java guy, forgive me! =)
There are a few utilities around that may help, including some gui
managers for mysql, better yet, sitted behind your comfortable windows
desktop =)
A few utilities to try:
sqlyog: www.sqlyog.com
dbtools: www.dbtools.com.br
Best Regards!
On Thu, 2004-03-04 at 11:11, HACKATHORN, TODD (SWBT) wrote:
I think that will help, I'll give it a try, I have had a lot of
trouble
finding good tutorials for bash also. Having been a windows person
for so
long it seems very foreign to me. I see how you connect to the local
data
base, does anyone know how to import data from another non mySQL
database
server? To make two connections and pull data from one to the other?
Thanks,
Todd Hackathorn
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 04, 2004 8:30 AM
To: HACKATHORN, TODD (SWBT); [EMAIL PROTECTED]
Subject: Re: Scripting
Todd,
I'm also pretty new to MySQL and Linux but I have years of DB2
experience on
various platforms.
I've bash scripts very useful and cron them when I have something
that I
want to automate, such as a daily backup of my databases. Here are
some
examples of bash scripts that I use with MySQL.
This example, which is in file RI.sql, creates two related tables in
the
MySQL 'tmp' database, populates them, and displays the results. NOTE:
The
'Another Mistake' row will not be successfully inserted into the
'emp' table
because it has an invalid foreign key; there is no row for dept 'X99'
in the
'dept' table.
---
use tmp;
drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
) Type=InnoDB;
drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
) Type=InnoDB;
insert into dept values
('A00', 'Administration', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values ('10', 'Christine', 'I',
'Haas','A00',5.00);
insert into emp values ('20', 'George', 'W', 'Bush', 'D11',
3.00);
insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
15000.00);
insert into emp values ('40', 'John', ' ', 'Kerry', 'E21',
35000.00);
select * from dept;
select * from emp;
---
You can run the preceding script from the mysql prompt by preceding
its name
with '\.'. For example:
mysql> \. RI.sql
This script, called backup2.bash, is what I use to back up my
databases each
night. It includes a comment showing the crontab entry I use to run
it.
NOTE: We are using a Perl package called 'sendEmai'l instead of the
traditional 'sendmail' program.
---
#!/bin/bash
#This script makes a separate database-level backup of each of the
current
MySQL databases and
#deletes backups older than a certain number of days.
#This script is normally invoked via a cron job so that it runs once
per day
in the middle of the night.
#The crontab entry looks like this:
#0 3 * * * sh /home/rhino/MySQL/backup2.bash >
/home/rhino/MySQL/backup2.out
2>&1; cat /home/rhino/MySQL/backup2.out | sendEmail -f
[EMAIL PROTECTED] -t
[EMAIL PROTECTED] -u "Daily Backup Report"
USERID="foo"; #The userid to use for creating the backup
PASSWORD="foopass"; #The password to u
Re: Scripting
Sorry, I don't know anything about PHP except that is supposed to be
somewhat Java-like. My main language these days is Java but I don't know PHP
at all.
For what it's worth, I checked my V7.2 DB2 manuals just now to see if PHP is
mentioned in the Application Development Guide. I found chapters on COBOL, C
and C++, Perl, Fortran, Java, and REXX but nothing on PHP.
I also checked the V8.1 DB2 manuals for Windows/Linux platforms and found
very little *except* for this little nugget, which is probably exactly what
you want:
a.. PHP. PHP can be used as a method to access DB2 from web-based
applications. PHP is a server-side, HTML-embedded, cross-platform scripting
language. It supports DB2 access using the Unified-ODBC access method, in
which the user-level PHP communicates to DB2 using ODBC calls. Unlike
standard ODBC, with the Unified-ODBC method, communication is directly to
the DB2 CLI layer, not through the ODBC layer. For more information about
using PHP with DB2, search the DB2 support site:
www.ibm.com/software/data/db2/udb/winos2unix/support
I followed the suggested link and did a search with the search term:
PHP && DB2
There were many hits but the most useful looking one was this:
http://www-106.ibm.com/developerworks/db2/library/techarticle/scott/0614_scott.html
The other good place to ask DB2 questions is the Usenet newsgroup
comp.databases.ibm-db2. The Toronto Lab, which develops DB2 for the Wintel
platforms monitors this newsgroup and often answers questions there.
Good luck!
Rhino
- Original Message -
From: "James Marcinek" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, March 04, 2004 9:32 AM
Subject: Re: Scripting
> Rhino,
>
> I know this is an off topic; however I see that you have DB2 experience.
> Most of the information I have a question you might be able to answer. Are
> you using PHP with MySQL? If so, have you tried to use PHP with DB2? I'd
> like to know what functions you call to connect or anything that could
> help? I'm new to the MySQL arena as well.
>
> Thanks,
>
> James
>
> > Todd,
> >
> > I'm also pretty new to MySQL and Linux but I have years of DB2
experience
> > on
> > various platforms.
> >
> > I've bash scripts very useful and cron them when I have something that I
> > want to automate, such as a daily backup of my databases. Here are some
> > examples of bash scripts that I use with MySQL.
> >
> > This example, which is in file RI.sql, creates two related tables in the
> > MySQL 'tmp' database, populates them, and displays the results. NOTE:
The
> > 'Another Mistake' row will not be successfully inserted into the 'emp'
> > table
> > because it has an invalid foreign key; there is no row for dept 'X99' in
> > the
> > 'dept' table.
> >
> > ---
> >
> > use tmp;
> >
> > drop table if exists dept;
> > create table dept(
> > deptno char(3) not null,
> > deptname varchar(36) not null,
> > mgrno char(6),
> > primary key(deptno)
> > ) Type=InnoDB;
> >
> > drop table if exists emp;
> > create table emp(
> > empno char(6) not null,
> > firstnme char(12) not null,
> > midinit char(1),
> > lastname char(15) not null,
> > workdept char(3) not null,
> > salary dec(9,2) not null,
> > primary key(empno),
> > index(workdept),
> > foreign key(workdept) references dept(deptno) on delete restrict
> > ) Type=InnoDB;
> >
> > insert into dept values
> > ('A00', 'Administration', '10'),
> > ('D11', 'Manufacturing', '20'),
> > ('E21', 'Education', '30');
> >
> > insert into emp values ('10', 'Christine', 'I',
> > 'Haas','A00',5.00);
> > insert into emp values ('20', 'George', 'W', 'Bush', 'D11',
3.00);
> > insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
> > 15000.00);
> > insert into emp values ('40', 'John', ' ', 'Kerry', 'E21',
35000.00);
> >
> > select * from dept;
> >
> > select * from emp;
> >
> > ---
> >
> > You can run the preceding script from the mysql prompt by preceding its
> > name
> > with '\.'. For example:
> >
> > mysql> \. RI.sql
> >
> >
> > This script, called backup2.bash, is what I use to back up my databases
> > each
>
RE: Scripting
HI!
ODBC is a nice, cheap and easy way. JDBC is a nice option also (i am a
java guy, forgive me! =)
There are a few utilities around that may help, including some gui
managers for mysql, better yet, sitted behind your comfortable windows
desktop =)
A few utilities to try:
sqlyog: www.sqlyog.com
dbtools: www.dbtools.com.br
Best Regards!
On Thu, 2004-03-04 at 11:11, HACKATHORN, TODD (SWBT) wrote:
> I think that will help, I'll give it a try, I have had a lot of trouble
> finding good tutorials for bash also. Having been a windows person for so
> long it seems very foreign to me. I see how you connect to the local data
> base, does anyone know how to import data from another non mySQL database
> server? To make two connections and pull data from one to the other?
>
> Thanks,
>
> Todd Hackathorn
>
>
> -Original Message-
> From: Rhino [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 04, 2004 8:30 AM
> To: HACKATHORN, TODD (SWBT); [EMAIL PROTECTED]
> Subject: Re: Scripting
>
> Todd,
>
> I'm also pretty new to MySQL and Linux but I have years of DB2 experience on
> various platforms.
>
> I've bash scripts very useful and cron them when I have something that I
> want to automate, such as a daily backup of my databases. Here are some
> examples of bash scripts that I use with MySQL.
>
> This example, which is in file RI.sql, creates two related tables in the
> MySQL 'tmp' database, populates them, and displays the results. NOTE: The
> 'Another Mistake' row will not be successfully inserted into the 'emp' table
> because it has an invalid foreign key; there is no row for dept 'X99' in the
> 'dept' table.
>
> ---
>
> use tmp;
>
> drop table if exists dept;
> create table dept(
> deptno char(3) not null,
> deptname varchar(36) not null,
> mgrno char(6),
> primary key(deptno)
> ) Type=InnoDB;
>
> drop table if exists emp;
> create table emp(
> empno char(6) not null,
> firstnme char(12) not null,
> midinit char(1),
> lastname char(15) not null,
> workdept char(3) not null,
> salary dec(9,2) not null,
> primary key(empno),
> index(workdept),
> foreign key(workdept) references dept(deptno) on delete restrict
> ) Type=InnoDB;
>
> insert into dept values
> ('A00', 'Administration', '10'),
> ('D11', 'Manufacturing', '20'),
> ('E21', 'Education', '30');
>
> insert into emp values ('10', 'Christine', 'I', 'Haas','A00',5.00);
> insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00);
> insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
> 15000.00);
> insert into emp values ('40', 'John', ' ', 'Kerry', 'E21', 35000.00);
>
> select * from dept;
>
> select * from emp;
>
> ---
>
> You can run the preceding script from the mysql prompt by preceding its name
> with '\.'. For example:
>
> mysql> \. RI.sql
>
>
> This script, called backup2.bash, is what I use to back up my databases each
> night. It includes a comment showing the crontab entry I use to run it.
> NOTE: We are using a Perl package called 'sendEmai'l instead of the
> traditional 'sendmail' program.
>
> ---
> #!/bin/bash
>
> #This script makes a separate database-level backup of each of the current
> MySQL databases and
> #deletes backups older than a certain number of days.
> #This script is normally invoked via a cron job so that it runs once per day
> in the middle of the night.
> #The crontab entry looks like this:
> #0 3 * * * sh /home/rhino/MySQL/backup2.bash > /home/rhino/MySQL/backup2.out
> 2>&1; cat /home/rhino/MySQL/backup2.out | sendEmail -f [EMAIL PROTECTED] -t
> [EMAIL PROTECTED] -u "Daily Backup Report"
>
> USERID="foo"; #The userid to use for creating the backup
> PASSWORD="foopass"; #The password to use for creating the backup
> BACKUP_TIMESTAMP=`/bin/date +%Y%m%d"-"%H%M%S`; #The timestamp
> (MMDD-HHMMSS) of the backup
> BACKUP_PATH="/home/rhino/MySQL/backup"; #The directory into which the backup
> will be written
> NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups to
> keep
>
> echo "** REPORT BEGINS **";
> echo
> echo "Program Name:" $0
> report_date=`/bin/date`
> echo "Report Date:" $report_date;
> echo
>
>
RE: Scripting
Here are two suggestions:
1) One option is to use source database to dump data into a MySQL
readable format, then import/load the data into MySQL.
2) Another option is to use a language like C++/or Java and open
simultaneous connections to each database. One dumping data, format the
data then one connection to import the data.
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 3/4/04, 9:11:02 AM, "HACKATHORN," TODD "(SWBT)" <[EMAIL PROTECTED]> wrote
regarding RE: Scripting:
> I think that will help, I'll give it a try, I have had a lot of trouble
> finding good tutorials for bash also. Having been a windows person for
so
> long it seems very foreign to me. I see how you connect to the local
data
> base, does anyone know how to import data from another non mySQL database
> server? To make two connections and pull data from one to the other?
> Thanks,
> Todd Hackathorn
> -Original Message-
> From: Rhino [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 04, 2004 8:30 AM
> To: HACKATHORN, TODD (SWBT); [EMAIL PROTECTED]
> Subject: Re: Scripting
> Todd,
> I'm also pretty new to MySQL and Linux but I have years of DB2 experience
on
> various platforms.
> I've bash scripts very useful and cron them when I have something that I
> want to automate, such as a daily backup of my databases. Here are some
> examples of bash scripts that I use with MySQL.
> This example, which is in file RI.sql, creates two related tables in the
> MySQL 'tmp' database, populates them, and displays the results. NOTE: The
> 'Another Mistake' row will not be successfully inserted into the 'emp'
table
> because it has an invalid foreign key; there is no row for dept 'X99' in
the
> 'dept' table.
> ---
> use tmp;
> drop table if exists dept;
> create table dept(
> deptno char(3) not null,
> deptname varchar(36) not null,
> mgrno char(6),
> primary key(deptno)
> ) Type=InnoDB;
> drop table if exists emp;
> create table emp(
> empno char(6) not null,
> firstnme char(12) not null,
> midinit char(1),
> lastname char(15) not null,
> workdept char(3) not null,
> salary dec(9,2) not null,
> primary key(empno),
> index(workdept),
> foreign key(workdept) references dept(deptno) on delete restrict
> ) Type=InnoDB;
> insert into dept values
> ('A00', 'Administration', '10'),
> ('D11', 'Manufacturing', '20'),
> ('E21', 'Education', '30');
> insert into emp values ('10', 'Christine', 'I',
'Haas','A00',5.00);
> insert into emp values ('20', 'George', 'W', 'Bush', 'D11',
3.00);
> insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
> 15000.00);
> insert into emp values ('40', 'John', ' ', 'Kerry', 'E21', 35000.00);
> select * from dept;
> select * from emp;
> ---
> You can run the preceding script from the mysql prompt by preceding its
name
> with '\.'. For example:
> mysql> \. RI.sql
> This script, called backup2.bash, is what I use to back up my databases
each
> night. It includes a comment showing the crontab entry I use to run it.
> NOTE: We are using a Perl package called 'sendEmai'l instead of the
> traditional 'sendmail' program.
> ---
> #!/bin/bash
> #This script makes a separate database-level backup of each of the
current
> MySQL databases and
> #deletes backups older than a certain number of days.
> #This script is normally invoked via a cron job so that it runs once per
day
> in the middle of the night.
> #The crontab entry looks like this:
> #0 3 * * * sh /home/rhino/MySQL/backup2.bash >
/home/rhino/MySQL/backup2.out
> 2>&1; cat /home/rhino/MySQL/backup2.out | sendEmail -f [EMAIL PROTECTED]
-t
> [EMAIL PROTECTED] -u "Daily Backup Report"
> USERID="foo"; #The userid to use for creating the backup
> PASSWORD="foopass"; #The password to use for creating the backup
> BACKUP_TIMESTAMP=`/bin/date +%Y%m%d"-"%H%M%S`; #The timestamp
> (MMDD-HHMMSS) of the backup
> BACKUP_PATH="/home/rhino/MySQL/backup"; #The directory into which the
backup
> will be written
> NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups
to
> keep
> echo "** REPORT BEGINS **";
> ech
Re: Scripting
t; $report_date;
> > echo
> >
> > #Display the non-secret values used in this run.
> > echo "Backup Values:";
> > echo " Backup timestamp is" $BACKUP_TIMESTAMP;
> > echo " Backup path is" $BACKUP_PATH;
> > echo " Number of daily backups to keep ="
> > $NUMBER_OF_DAILY_BACKUPS_TO_KEEP;
> >
> > #For each database currently in MySQL, take a database-level backup, then
> > list any backups older than a certain number of day
> > for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
> > do
> >echo
> >echo "Backing up database" $ONE_DBNAME;
> >/usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD}
> > ${ONE_DBNAME} -r ${BACKUP_PATH}"/"${ONE_DBNAME}"."${BACKUP_TI
> >echo ">> Deleting these old backups for this database..."
> >/usr/bin/find ${BACKUP_PATH} -mtime
> > +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display
> > old
> > backups (i
> >/usr/bin/find ${BACKUP_PATH} -mtime
> > +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';';
> > #delete old
> > done
> >
> > echo
> > echo "** REPORT ENDS **";
> > ---
> >
> > If you want to take a backup manually with this script (and omit the email
> > being sent to you), you can do this from the mysql prompt via:
> > mysql> \. /home/rhino/MySQL/backup2.bash > /home/rhino/MySQL/backup2.out
> > 2>&1
> >
> > I think the biggest obstacle you're going to face in writing bash scripts
> > is
> > the lack of good tutorials on it. There are umpteen bash references that
> > show you generic syntax and tell you the terminology used by bash but
> > darned
> > few decent tutorials to show you how to write bash scripts. Some people
> > can
> > do anything they want with only a reference but I'm one of those people
> > who
> > need examples to really understand something so I've had real challenges
> > learning bash since the references are extremely skimpy on concrete
> > examples.
> >
> > Good luck!
> >
> > Rhino
> >
> > - Original Message -
> > From: "HACKATHORN, TODD (SWBT)" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Thursday, March 04, 2004 8:57 AM
> > Subject: Scripting
> >
> >
> >> Hello,
> >> I am new to Linux, and mySQL. I am currently a MsSQL Server 2000 and VB
> >> programmer, but trying to break away from that side. I have my database
> > set
> >> up and working great, but I am really struggling on how to perform
> > scheduled
> >> updates, and data imports. Can anyone please point me to a good
> >> resource
> > to
> >> learn how to build a script, that can import data from an ODBC
> >> connection,
> >> and then maybe run multiple queries in the data one at a time. I don't
> > know
> >> what language I want to use because I don't know enough about it. Once
> >> I
> >> find a way to do what I need, I will learn that language. I think there
> >> might be a way to use php as cron'd scripts, but I don't know. Also I
> >> looked at perl, or just bash in linux. I am used to being able to do
> >> anything I needed to with DTS packages and stored procedures. I had ask
> >> a
> >> similar question a while back, and still can't get going with this. So
> > far
> >> the graphical packages for updates are the only thing I miss from SQL
> >> Server.
> >>
> >> Thanks in advance for any help.
> >>
> >> Todd Hackathorn
> >>
> >>
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
>
--
--
Curtis Maurand
mailto:[EMAIL PROTECTED]
http://www.maurand.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Scripting
I think that will help, I'll give it a try, I have had a lot of trouble
finding good tutorials for bash also. Having been a windows person for so
long it seems very foreign to me. I see how you connect to the local data
base, does anyone know how to import data from another non mySQL database
server? To make two connections and pull data from one to the other?
Thanks,
Todd Hackathorn
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 04, 2004 8:30 AM
To: HACKATHORN, TODD (SWBT); [EMAIL PROTECTED]
Subject: Re: Scripting
Todd,
I'm also pretty new to MySQL and Linux but I have years of DB2 experience on
various platforms.
I've bash scripts very useful and cron them when I have something that I
want to automate, such as a daily backup of my databases. Here are some
examples of bash scripts that I use with MySQL.
This example, which is in file RI.sql, creates two related tables in the
MySQL 'tmp' database, populates them, and displays the results. NOTE: The
'Another Mistake' row will not be successfully inserted into the 'emp' table
because it has an invalid foreign key; there is no row for dept 'X99' in the
'dept' table.
---
use tmp;
drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
) Type=InnoDB;
drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
) Type=InnoDB;
insert into dept values
('A00', 'Administration', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values ('10', 'Christine', 'I', 'Haas','A00',5.00);
insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00);
insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
15000.00);
insert into emp values ('40', 'John', ' ', 'Kerry', 'E21', 35000.00);
select * from dept;
select * from emp;
---
You can run the preceding script from the mysql prompt by preceding its name
with '\.'. For example:
mysql> \. RI.sql
This script, called backup2.bash, is what I use to back up my databases each
night. It includes a comment showing the crontab entry I use to run it.
NOTE: We are using a Perl package called 'sendEmai'l instead of the
traditional 'sendmail' program.
---
#!/bin/bash
#This script makes a separate database-level backup of each of the current
MySQL databases and
#deletes backups older than a certain number of days.
#This script is normally invoked via a cron job so that it runs once per day
in the middle of the night.
#The crontab entry looks like this:
#0 3 * * * sh /home/rhino/MySQL/backup2.bash > /home/rhino/MySQL/backup2.out
2>&1; cat /home/rhino/MySQL/backup2.out | sendEmail -f [EMAIL PROTECTED] -t
[EMAIL PROTECTED] -u "Daily Backup Report"
USERID="foo"; #The userid to use for creating the backup
PASSWORD="foopass"; #The password to use for creating the backup
BACKUP_TIMESTAMP=`/bin/date +%Y%m%d"-"%H%M%S`; #The timestamp
(MMDD-HHMMSS) of the backup
BACKUP_PATH="/home/rhino/MySQL/backup"; #The directory into which the backup
will be written
NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups to
keep
echo "** REPORT BEGINS **";
echo
echo "Program Name:" $0
report_date=`/bin/date`
echo "Report Date:" $report_date;
echo
#Display the non-secret values used in this run.
echo "Backup Values:";
echo " Backup timestamp is" $BACKUP_TIMESTAMP;
echo " Backup path is" $BACKUP_PATH;
echo " Number of daily backups to keep =" $NUMBER_OF_DAILY_BACKUPS_TO_KEEP;
#For each database currently in MySQL, take a database-level backup, then
list any backups older than a certain number of day
for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
echo
echo "Backing up database" $ONE_DBNAME;
/usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD}
${ONE_DBNAME} -r ${BACKUP_PATH}"/"${ONE_DBNAME}"."${BACKUP_TI
echo ">> Deleting these old backups for this database..."
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old
backups (i
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBN
Re: Scripting
gt; ${ONE_DBNAME} -r ${BACKUP_PATH}"/"${ONE_DBNAME}"."${BACKUP_TI
>echo ">> Deleting these old backups for this database..."
>/usr/bin/find ${BACKUP_PATH} -mtime
> +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display
> old
> backups (i
>/usr/bin/find ${BACKUP_PATH} -mtime
> +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';';
> #delete old
> done
>
> echo
> echo "** REPORT ENDS **";
> ---
>
> If you want to take a backup manually with this script (and omit the email
> being sent to you), you can do this from the mysql prompt via:
> mysql> \. /home/rhino/MySQL/backup2.bash > /home/rhino/MySQL/backup2.out
> 2>&1
>
> I think the biggest obstacle you're going to face in writing bash scripts
> is
> the lack of good tutorials on it. There are umpteen bash references that
> show you generic syntax and tell you the terminology used by bash but
> darned
> few decent tutorials to show you how to write bash scripts. Some people
> can
> do anything they want with only a reference but I'm one of those people
> who
> need examples to really understand something so I've had real challenges
> learning bash since the references are extremely skimpy on concrete
> examples.
>
> Good luck!
>
> Rhino
>
> - Original Message -
> From: "HACKATHORN, TODD (SWBT)" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, March 04, 2004 8:57 AM
> Subject: Scripting
>
>
>> Hello,
>> I am new to Linux, and mySQL. I am currently a MsSQL Server 2000 and VB
>> programmer, but trying to break away from that side. I have my database
> set
>> up and working great, but I am really struggling on how to perform
> scheduled
>> updates, and data imports. Can anyone please point me to a good
>> resource
> to
>> learn how to build a script, that can import data from an ODBC
>> connection,
>> and then maybe run multiple queries in the data one at a time. I don't
> know
>> what language I want to use because I don't know enough about it. Once
>> I
>> find a way to do what I need, I will learn that language. I think there
>> might be a way to use php as cron'd scripts, but I don't know. Also I
>> looked at perl, or just bash in linux. I am used to being able to do
>> anything I needed to with DTS packages and stored procedures. I had ask
>> a
>> similar question a while back, and still can't get going with this. So
> far
>> the graphical packages for updates are the only thing I miss from SQL
>> Server.
>>
>> Thanks in advance for any help.
>>
>> Todd Hackathorn
>>
>>
>
>
> --
> 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: Scripting
Todd,
I'm also pretty new to MySQL and Linux but I have years of DB2 experience on
various platforms.
I've bash scripts very useful and cron them when I have something that I
want to automate, such as a daily backup of my databases. Here are some
examples of bash scripts that I use with MySQL.
This example, which is in file RI.sql, creates two related tables in the
MySQL 'tmp' database, populates them, and displays the results. NOTE: The
'Another Mistake' row will not be successfully inserted into the 'emp' table
because it has an invalid foreign key; there is no row for dept 'X99' in the
'dept' table.
---
use tmp;
drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
) Type=InnoDB;
drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
) Type=InnoDB;
insert into dept values
('A00', 'Administration', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values ('10', 'Christine', 'I', 'Haas','A00',5.00);
insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00);
insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
15000.00);
insert into emp values ('40', 'John', ' ', 'Kerry', 'E21', 35000.00);
select * from dept;
select * from emp;
---
You can run the preceding script from the mysql prompt by preceding its name
with '\.'. For example:
mysql> \. RI.sql
This script, called backup2.bash, is what I use to back up my databases each
night. It includes a comment showing the crontab entry I use to run it.
NOTE: We are using a Perl package called 'sendEmai'l instead of the
traditional 'sendmail' program.
---
#!/bin/bash
#This script makes a separate database-level backup of each of the current
MySQL databases and
#deletes backups older than a certain number of days.
#This script is normally invoked via a cron job so that it runs once per day
in the middle of the night.
#The crontab entry looks like this:
#0 3 * * * sh /home/rhino/MySQL/backup2.bash > /home/rhino/MySQL/backup2.out
2>&1; cat /home/rhino/MySQL/backup2.out | sendEmail -f [EMAIL PROTECTED] -t
[EMAIL PROTECTED] -u "Daily Backup Report"
USERID="foo"; #The userid to use for creating the backup
PASSWORD="foopass"; #The password to use for creating the backup
BACKUP_TIMESTAMP=`/bin/date +%Y%m%d"-"%H%M%S`; #The timestamp
(MMDD-HHMMSS) of the backup
BACKUP_PATH="/home/rhino/MySQL/backup"; #The directory into which the backup
will be written
NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups to
keep
echo "** REPORT BEGINS **";
echo
echo "Program Name:" $0
report_date=`/bin/date`
echo "Report Date:" $report_date;
echo
#Display the non-secret values used in this run.
echo "Backup Values:";
echo " Backup timestamp is" $BACKUP_TIMESTAMP;
echo " Backup path is" $BACKUP_PATH;
echo " Number of daily backups to keep =" $NUMBER_OF_DAILY_BACKUPS_TO_KEEP;
#For each database currently in MySQL, take a database-level backup, then
list any backups older than a certain number of day
for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
echo
echo "Backing up database" $ONE_DBNAME;
/usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD}
${ONE_DBNAME} -r ${BACKUP_PATH}"/"${ONE_DBNAME}"."${BACKUP_TI
echo ">> Deleting these old backups for this database..."
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old
backups (i
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';';
#delete old
done
echo
echo "** REPORT ENDS **";
---
If you want to take a backup manually with this script (and omit the email
being sent to you), you can do this from the mysql prompt via:
mysql> \. /home/rhino/MySQL/backup2.bash > /home/rhino/MySQL/backup2.out
2>&1
I think the biggest obstacle you're going to face in writing bash scripts is
the lack of good tutorials on it. There are umpteen bash references that
show you generic syntax and tell you the terminology used by bash but d
Scripting
Hello, I am new to Linux, and mySQL. I am currently a MsSQL Server 2000 and VB programmer, but trying to break away from that side. I have my database set up and working great, but I am really struggling on how to perform scheduled updates, and data imports. Can anyone please point me to a good resource to learn how to build a script, that can import data from an ODBC connection, and then maybe run multiple queries in the data one at a time. I don't know what language I want to use because I don't know enough about it. Once I find a way to do what I need, I will learn that language. I think there might be a way to use php as cron'd scripts, but I don't know. Also I looked at perl, or just bash in linux. I am used to being able to do anything I needed to with DTS packages and stored procedures. I had ask a similar question a while back, and still can't get going with this. So far the graphical packages for updates are the only thing I miss from SQL Server. Thanks in advance for any help. Todd Hackathorn
RE: DB design question - shell scripting...
That worked like a charm, thanks so much! I don't know why I didn't try that before! Julian At 02:46 PM 11/21/2003 -0600, Paul DuBois wrote: At 10:56 -0500 11/21/03, Julian Zottl wrote: Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date "+%m%d%Y"` export date mysql -u root -p < createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? You could use a here-document instead of createdb.sql #!/bin/sh date=`date "+%m%d%Y"` mysql -u root -p < I put a "t" before $date -- you don't want to try creating a table with a name that's all digits. That makes it indistinguishable from a number, so you'd have to quote it with backticks every time you refer to it. Julian -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question - shell scripting...
At 23:34 -0800 11/21/03, Chris wrote: Wouldn't this also work?: mysql -u root -p -e "CREATE TABLE t$date(...)" yourdatabase Sure. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, November 21, 2003 12:46 PM To: Julian Zottl; Andy Eastham; Mysql List Subject: RE: DB design question - shell scripting... At 10:56 -0500 11/21/03, Julian Zottl wrote: Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date "+%m%d%Y"` export date mysql -u root -p < createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? You could use a here-document instead of createdb.sql #!/bin/sh date=`date "+%m%d%Y"` mysql -u root -p < I put a "t" before $date -- you don't want to try creating a table with a name that's all digits. That makes it indistinguishable from a number, so you'd have to quote it with backticks every time you refer to it. >Julian -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question - shell scripting...
Wouldn't this also work?: mysql -u root -p -e "CREATE TABLE t$date(...)" yourdatabase -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Friday, November 21, 2003 12:46 PM To: Julian Zottl; Andy Eastham; Mysql List Subject: RE: DB design question - shell scripting... At 10:56 -0500 11/21/03, Julian Zottl wrote: >Andy, >Thanks for responding. I think that I am going to go with the idea >of creating a tale for each day. My thoughts were to write a shell >script to do this for me, but I am running into a problem: I wrote >the following: >#!/bin/sh >date=`date "+%m%d%Y"` >export date >mysql -u root -p < createdb.sql > >Then in createdb.sql >CONNECT Blah; >CREATE TABLE $date ( >. >) TYPE=MyISAM; > >But it's not passing the $date variable to SQL :/ I've been looking >on the web for a way to do this, but have yet to find it. any ideas? You could use a here-document instead of createdb.sql #!/bin/sh date=`date "+%m%d%Y"` mysql -u root -p < >Julian -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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: DB design question - shell scripting...
At 10:56 -0500 11/21/03, Julian Zottl wrote: Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date "+%m%d%Y"` export date mysql -u root -p < createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? You could use a here-document instead of createdb.sql #!/bin/sh date=`date "+%m%d%Y"` mysql -u root -p < I put a "t" before $date -- you don't want to try creating a table with a name that's all digits. That makes it indistinguishable from a number, so you'd have to quote it with backticks every time you refer to it. Julian -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question - shell scripting...
Andy, Thanks for responding. I think that I am going to go with the idea of creating a tale for each day. My thoughts were to write a shell script to do this for me, but I am running into a problem: I wrote the following: #!/bin/sh date=`date "+%m%d%Y"` export date mysql -u root -p < createdb.sql Then in createdb.sql CONNECT Blah; CREATE TABLE $date ( . ) TYPE=MyISAM; But it's not passing the $date variable to SQL :/ I've been looking on the web for a way to do this, but have yet to find it. any ideas? Julian At 12:37 PM 11/21/2003 +, Andy Eastham wrote: Julian, Your design is sound in my opinion. An area you probably need to consider is when you need to search across a day boundary. You will need to make the application aware that it needs to search across a day boundary, so that it searches two tables with a union where necessary. It will also need to know what the oldest table is, so that it doesn't try to do a union with a table that doesn't exist. Alternatively, you could always search across three tables - so that you always union with the one before and one after the required time window. Of course, you again need to check that you're not searching the earliest or latest available table, and if so, modify the union so that you don't try to search a non-existent table. Hope this helps, Andy > -Original Message- > From: Julian Zottl [mailto:[EMAIL PROTECTED] > Sent: 21 November 2003 12:03 > To: [EMAIL PROTECTED] > Subject: DB design question > > > Hello all, > I am designing a database right now that will have between > 300-400k inserts > per day. I need to keep this information for approximately 3 months and > will probably do 5-10 reads on the data set per day. I've been > storing it > in one table up to now (only col.), but the searches are becoming > more and > more of a problem. I'd like to break it up so that I have one table for > every day, and then I'll just delete the trailing days when I > create a new > day. So I would have 90 tables of roughly 350k records instead > of a single > table with 6+ million records. What do you al think of this design? I'm > making an assumption that it will make my searches a lot faster for a > single day (I doubt I would ever need to search on more than one > day). Thanks! > Julian Zottl > Unix Systems Administrator > NASA HQ - 202-358-1682 > > > -- > 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] Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scripting MySQL Commands
At 12:25 -0500 3/2/03, Stephen Tiano wrote: Paul, Oliver-- I really, really appreciate you guys taking time from your respective Sundays to try and enlighten me. But I'm still getting nowhere fast. I've gotten it to this: I open a new shell and type: /usr/local/bin/mysql --local-infile -u root -p [the full pathname up to]/Temp.sql at which point it informs me "incorrect database name". Of course. The first non-option argument, if there is one, is taken as the name of the default database. You need a < character before the filename if you want to redirect the input of the command to read from the file: /usr/local/bin/mysql --local-infile -u root -p < filename Well, yes, the script is to create a nonexistent database and then a table called "Temp" in that database. I'm now officially lost. 'use' would only apply to an existing database. By the way, I've also tried: /usr/local/bin/mysql -u root -p [the full pathname up to]/Temp.sql to no avail. S frustrating ... Steve Tiano - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
I'm fairly new to MySQL as well (although, I do have experience with Oracle). Even though you create a new database doesn't mean that MySQL will automatically start using that database. After all you might want to create 10 or 15 databases all at once (why? I don't know), then start creating tables for the 5th one you created. So your script needs to do something like: create database 'Temp'; use 'Temp'; (You need the quotes [or maybe it's the backquotes] if you actually want a mixed case name.) I'm sure that if I'm off, someone here will correct me. jeff At 12:27 -0500 3/2/03, Stephen Tiano wrote: Paul, Oliver-- I really, really appreciate you guys taking time from your respective Sundays to try and enlighten me. But I'm still getting nowhere fast. I've gotten it to this: I open a new shell and type: /usr/local/bin/mysql --local-infile -u root -p [the full pathname up to]/Temp.sql at which point it informs me "incorrect database name". Well, yes, the script is to create a nonexistent database and then a table called "Temp" in that database. I'm now officially lost. 'use' would only apply to an existing database. By the way, I've also tried: /usr/local/bin/mysql -u root -p [the full pathname up to]/Temp.sql to no avail. S frustrating ... Steve Tiano - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Jeff Shapiro, Colorado Springs, CO, USA At work I *have* to use a Windows machine, at home I *get* to use a Mac. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
Guys, Guys-- I got it! I stopped being a dimwit long enough to realize that the change to "USE Temp;" below was for INSIDE the script. When I made that change and again went to a new shell and typed (actually, I dragged the file from the window in which it sat in the Finder into the Terminal after typing): /usr/local/bin/mysql -u [name] -p I entered my password, was presented with a new prompt and typed: /usr/local/bin/mysql -u [name] -p Again I was asked for my password, which I typed in. At the next prompt I typed "USE Temp". The database changed and at the prompt that followed, I typed "SHOW TABLES". "Test_Table" was there. I did a "SELECT * FROM Test_Table" and the test row of data was indeed there. Thank you all-- Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Scripting MySQL Commands
Paul, Oliver-- I really, really appreciate you guys taking time from your respective Sundays to try and enlighten me. But I'm still getting nowhere fast. I've gotten it to this: I open a new shell and type: /usr/local/bin/mysql --local-infile -u root -p [the full pathname up to]/Temp.sql at which point it informs me "incorrect database name". Well, yes, the script is to create a nonexistent database and then a table called "Temp" in that database. I'm now officially lost. 'use' would only apply to an existing database. By the way, I've also tried: /usr/local/bin/mysql -u root -p [the full pathname up to]/Temp.sql to no avail. S frustrating ... Steve Tiano - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Scripting MySQL Commands
Hy Steve, > USE DATABASE Temp; try to change this into : USE Temp; and try it again, this should work. HTH Oliver for the filter : sql,query,queries,smallint - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
Paul, Thanks very much for responding. I tried what you suggested, but got: ERROR 1102: Incorrect database name '/Users/stephent/Sites/Temp.sql' I don't get this. What wrong database name? The script is supposed to create the database and a table in it. What an I too dense to see? Thanks again-- Steve Tiano Type the command at the shell prompt, not after you've already invoked mysql. The error message is from mysql itself; it indicates that you typed a shell command to mysql. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
At 8:53 -0500 3/2/03, [EMAIL PROTECTED] wrote: I'm doing all kinds of exercises and tutorials to get up to speed on MySQL on my PowerBook with OS X.2 running. I just tried an exercise that, for the first time, had me creating a table not right in the MySQL monitor, but, rather, via a script that I saved as a textfile--Temp.sql--and then call the script up from the command line in the MySQL monitor. Here's the script: CREATE DATABASE Temp; USE DATABASE Temp; CREATE TABLE Test_Table (Test_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Test_Name VARCHAR(30), Test_Date DATETIME, Test_Giver VARCHAR(30)); INSERT INTO Test_Table (Test_ID, Test_Name, Test_Date, Test_Giver) VALUES (NULL, 'Test','2000-03-02','Etienne'); The line I used to call the script up is: bin/mysql -p I didn't get the expected result, receiving the following message: ERROR 1064: You have an error in your SQL syntax near 'bin/mysql -p Can anyone see what I've done wrong? Type the command at the shell prompt, not after you've already invoked mysql. The error message is from mysql itself; it indicates that you typed a shell command to mysql. Thank you. Steve Tiano mail2web - Check your email from the web at http://mail2web.com/ . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Scripting MySQL Commands
Oliver, Thanks for responding. Foolish of me to be in such a rush that I wasn't watching what I was copying and pasting. I actually did use the command you suggest below. And that's what drew the error message. So I'm back to the drawing board. Take care, and thanks again-- Steve Hy Steve, bin/mysql -p try the following command : bin/mysql -p HTH Oliver
Re: Scripting MySQL Commands
Hy Steve, > bin/mysql -p http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Scripting MySQL Commands
I'm doing all kinds of exercises and tutorials to get up to speed on MySQL on my PowerBook with OS X.2 running. I just tried an exercise that, for the first time, had me creating a table not right in the MySQL monitor, but, rather, via a script that I saved as a textfile--Temp.sql--and then call the script up from the command line in the MySQL monitor. Here's the script: CREATE DATABASE Temp; USE DATABASE Temp; CREATE TABLE Test_Table (Test_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Test_Name VARCHAR(30), Test_Date DATETIME, Test_Giver VARCHAR(30)); INSERT INTO Test_Table (Test_ID, Test_Name, Test_Date, Test_Giver) VALUES (NULL, 'Test','2000-03-02','Etienne'); The line I used to call the script up is: bin/mysql -p http://mail2web.com/ . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
encryption, access, scripting in a database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I have, with the generous help of Michael Babcock, worked out what I think is a reasonable way to protect the credit card numbers in my database from improper access. Basically, only the server (better yet, some other server) can access the card numbers in the cards table. and requests from the main database and application are pgp-signed while returned results are pgp-encrypted. So can any of this be implemented in the database, or is it all at the application level with just GRANT privs in the ccard database? TIA & HAND & Happy Holidays mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+DQJVGb7uCXufRwARAqkaAKCwpkJKKlbbSfn8NUCfSKNybgL/XwCePXgg TEgHYbgozvBRqmTbt5+hfZQ= =IpwO -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[ANN] JudoScript -- A Genuine JDBC Scripting Language
Greetings!
Introducing JudoScript, a full-fledged scripting
language that supports genuine JDBC scripting. Free
form SQL statements can be specified and executed
either individually or in a group or batch.
Expressions can be embedded in the SQL statements.
SQL statements can be prepared and run with bind
parameters. Stored procedure calls can take in-,
out- and bidirectional bind parameters. Multiple
connections can be set up independently and SQL
statements are bound to each.
The article at
http://www.judoscript.com/articles/jdbc.html
explains everything with a lot of sample scripts that
are runnable against Oracle and Microsoft SQL Server.
JudoScript is a powerful cross-database,
cross-format data processing language. Multiple
database connections can be set up simultaneously,
data from all databases are in JDBC SQL data types,
so cross-database transfer is natural. In addition
to JDBC scripting, JudoScript also supports XML
scripting, file operations, HTML/HTTP downloading
and scraping, networking, EJB invocation, and last
but not least, it is a complete general purpose
programming language with robust data strctures
that support sophisticated algorithms.
JudoScript has a built-in scheduler that can run
any tasks, including database operations, running
native executables and sending mails. The scheduled
jobs have an embedded control panel accessed via
HTTP, allowing system administrators to monitor
and/or control the job. For more information, visit
http://www.judoscript.com
The following is a simple example to give you a
sense what the code looks like:
//
// connect to database as the default connection
//
connect to 'jdbc:oracle:thin:@localhost:1521:somedb',
'dbuser', 'dbpass';
//
// create a table and an index
//
executeSQL {
create table newborn (
idint primary key,
name varchar(50) not null,
birthday date
);
create index emp_name on newborn(name);
}
//
// insert a few records using static SQL.
//
$data = new orderedMap (
'Michelle' = date(2001,11,14, 9,12, 5),
'Daniel' = date(2001,11,14,14,45, 8),
'Kristine' = date(2001,11,14,23,58,48)
);
$id = 100;
for $x in $data.keys() {
executeSQL
insert into newborn (id,name,birthday)
value ( (* $id++ *), (* $x *), (* $data.($x) *) );
}
//
// make a query with prepared SQL
//
prepare $q:
select id, name from newborn where birthday < ?
;
executeQuery $q
with @1:timestamp = date(2001,11,14,12,0,0);
println 'Babies born in the afternoon:';
while $q.next() {
println $q[1] :>5, ' ', $q[2]; // first column right adjusted
// println $q.id :>5, ' ', $q.name; // another way.
}
Cheers!
James Huang, author of JudoScript
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie Scripting question...
Hello all, I'm working on a tcsh script (using MacOSX 10.0.4) that performs a mysql query and pipes the output to the mail command. When I execute the script from the command line as root, it executes perfectly. However, when I allow the script to execute via /etc/crontab, (which is what I really need) it sends the email with no body, seemingly having failed to accomplish the mysql client query. The perms on the script: -rwxrwxr-x 1 root admin 532 Sep 6 19:52 sendLists.script crontab line: "2 7 * * * root /Volumes/pachyderm/administrative/projects/exports/sendLists.script" sendLists.script: "SELECTFIELDS=" CONCAT(contactCode,'-',projectID,': ' , projectName), statusName, responsible, hrsReq, taskDescription" ORDERBYFIELDS="clientID, parentProjectID, itemOrder, projectID" CURRENTNAME="Jason" mysql --database=xlmcentral -t -e "select $SELECTFIELDS from projectsTemp where upper(statusName) not like 'DONE' and responsible like '%$CURRENTNAME%' or upper(statusName) not like 'DONE' and responsible like '%all%' order by $ORDERBYFIELDS" \ | mail -s "$CURRENTNAME's Test To Do List..." $[EMAIL PROTECTED]" Any ideas? Thanks in advance, Jason - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Cobalt .pkg scripting
I just saw something on freshmeat.net called EPM (ESP Package Manager) that purports to be able to assemble AT&T .pkg files for you (as well as other popular package formats, .bpkg, .rpm, etc). Haven't tried it, let me know if it works out for you. -Aaron On Tue, 23 Jan 2001, James Raff wrote: > Are there any facilities or gurus who could write a .pkg file to help us Linux >newbies upgrade MySQL on a RaQ4 box? The most recent version from Cobalt is 3.22.32 >and we would like to go to 3.23.30 but do not feel confident to write a .pkg for this >machine. We are prepared to pay for the file and then we will put it on the net . An >indication of costs would be helpful. > > James Raff > Director > www.masterbuildersonline.com > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Cobalt .pkg scripting
Are there any facilities or gurus who could write a .pkg file to help us Linux newbies upgrade MySQL on a RaQ4 box? The most recent version from Cobalt is 3.22.32 and we would like to go to 3.23.30 but do not feel confident to write a .pkg for this machine. We are prepared to pay for the file and then we will put it on the net . An indication of costs would be helpful. James Raff Director www.masterbuildersonline.com
