Re: ERROR 1005 (HY000): Can't create table '.\testDataBase\#sql-ec4_c.frm' (errno: 139)
Sunil, in InnoDB, the maximum indexed column length is 767 bytes. Osku is improving the error message: http://bugs.mysql.com/bug.php?id=13315 Regards, Heikki Oracle/Innobase - Original Message - From: Sunil Vishwas [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, October 25, 2005 5:33 AM Subject: ERROR 1005 (HY000): Can't create table '.\testDataBase\#sql-ec4_c.frm' (errno: 139) --_=_NextPart_001_01C5D90C.62DB5CF5 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable (I am using mysql-5.0.15-win32) Why I am getting this error and is there any way I can fix it, or is it a bug? Between I don't get this error if I change the field size to 767 or below: =20 drop table Address; CREATE TABLE `Address`=20 ( `RecId` CHAR(32) NOT NULL,=20 `WebAddress` VARCHAR(1000),=20 CONSTRAINT PKAddress PRIMARY KEY(RecId) ); =20 CREATE INDEX AddressWebAddressWebAddress ON Address (WebAddress); =20 --_=_NextPart_001_01C5D90C.62DB5CF5-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date storage format
Hello, Can anyone tell me what advantages there are in keeping dates and times in a MySQL DateTime field, as opposed to storing its string equivalent in a Varchar field ? Regards, Danny
Re: Date storage format
Can anyone tell me what advantages there are in keeping dates and times in a MySQL DateTime field, as opposed to storing its string equivalent in a Varchar field ? Decent sorting, validity checking, being able to use the DATE and TIME functions etc etc... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Microfinance database
Hi guys. I don't want my job done by anybody else, but I have found no solution
for several days to this problem.
I am developing a micro-finance system to farmer communities. The problem I
have got is in the database description.
I have three principal tables: (see SQL at the end).
1. Loan. # Where I register the loan.
2. Resources/goods given. # Where I register the goods give in the credit (and
I sum all the resources to know how much money the credit has).
3. Payments. # Where I register the payments.
I've got all the formulas to calculate the interest and amortization tables.
The problem is with the penalty/default interest.
Example:
Paul has got a loan: he's got a cow and money (total 500).
He has to pay 80 dollars each month. 75 correspond to capital and 5 to
interest.
If he fails in paying one of the installment, I have to calculate the penalty
(which in this cases is 10% per year) in daily basis. Two days later he has
paid part of the installment (30) so I have to calculate the penalization
interest for the two days, then discount the interest, and the discount the
rest from the Principal (capital). Then he has got 39 as unpaid capital.
Three days later he has paid 20 dollars. Again, I have to calculate the
penalization interest, and substract it from the capital in debt. And like this
until it gets the payment finished.
I know all the formulas and stuff, but my problem is in the database
definition, and specifically, where to store the payments, the failed
installment and the penalization interest.
The questions are:
1. Should I store the failed installment in a separate table, or in the same
payments table?.
2. The calculations (interest, failed capital, penalization interest) must be
stored in a table or retrieved using a query?.
3. The most important question: Does anybody could help me with a link or
information about how might be the structure of a financial database?.
Thank you very much guys, and again, as you can see I am very confused and I
don't my job done by anybody else. I just need your wise advice.
Best regards,
Alvaro.
TABLES:
/*Table for the credits. */
CREATE TABLE `tbl04_Credit` (
`PK_Credit` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`FK_ProjectHolderId` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`date_credit` date NOT NULL default '-00-00',
`interest` double NOT NULL default '0',
`tiempo_credito_days` double NOT NULL default '0',
`periodicity_credit` int(11) NOT NULL default '0',
`nom_responsable` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`comment_credit` varchar(255) collate latin1_spanish_ci default NULL,
`last_change` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
PRIMARY KEY (`PK_Credit`),
KEY `FK_ProjectHolderId` (`FK_ProjectHolderId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
INSERT INTO `tbl04_Credit` VALUES ('1', 'Community One', '2005-10-25', 12, 360,
30, 'Paul Simon', 'No comments', '2005-10-25 01:35:24');
INSERT INTO `tbl04_Credit` VALUES ('2', 'Community Two', '2005-10-27', 12, 360,
30, 'Art Garfunkel', 'No comments', '2005-10-25 01:35:05');
/* Resource table */
CREATE TABLE `tbl04_CreditResource` (
`PK_Cred_Recurso` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`FK_Credito` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`resource` varchar(60) collate latin1_spanish_ci default NULL,
`UnitValue` float NOT NULL default '0',
`Quantity` float default NULL,
`penalty_interest` float NOT NULL default '0',
`credit_time` int(11) NOT NULL default '0',
PRIMARY KEY (`PK_Cred_Recurso`),
KEY `FK_Credito` (`FK_Credito`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci ;
INSERT INTO `tbl04_CreditResource` VALUES ('Resource1', '1', 'Duck', 20, 8, 12,
360);
INSERT INTO `tbl04_CreditResource` VALUES ('Resource2', '1', 'Chicken', 5, 8,
12, 360);
INSERT INTO `tbl04_CreditResource` VALUES ('Resource3', '2', 'Cow', 250, 1, 12,
360);
INSERT INTO `tbl04_CreditResource` VALUES ('Resource4', '2', 'Chicken', 5, 10,
12, 360);
/*Payments*/
CREATE TABLE `tbl05_Payment` (
`PK_Pago` int(11) NOT NULL auto_increment,
`FK_Cred_Recurso` varchar(255) collate latin1_spanish_ci default NULL,
`installment_number` int(11) NOT NULL default '0',
`end_installment_date` date NOT NULL default '-00-00',
`payment_date` date NOT NULL default '-00-00',
`payment_capital` float default '0',
`payment_interest` float default '0',
`unpaid_capital` float default '0',
`interest_unpaid_capital` float default NULL,
`comentario_pago` varchar(255) collate latin1_spanish_ci default NULL,
`ultimo_cambio` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`Estado` enum('1','0') collate latin1_spanish_ci NOT NULL default '0',
PRIMARY KEY (`PK_Pago`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;
-
If you have an apple
Re: Help needed for SQL statement
Is this what you are looking for: SELECT clone_ids, COUNT(DISTINCT(gene_ids)) as count_genes FROM table_name GROUP BY clone_ids or SELECT clone_ids, COUNT(gene_ids) as count_genes FROM table_name GROUP BY clone_ids Hope this helps, Alvaro - Original Message - From: Xiaobo Chen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, October 24, 2005 11:43 PM Subject: Help needed for SQL statement Hi, I have such a situation: There is a table with gene_ids and clone_ids. Each gene only resides on a single clone and each clone may contain multiple genes. How do would I find how many genes are on each and every clone? Thanks in advance. X.Chen -- 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]
Huge table
Hi. I have upgraded RT Request Tracker. I'm using Mysql 4.0.18, Apache 1.3.27 (with static modperl module). The database we are using is quite large. The main problem we are having is that certain processes can take quite a long time. This is due to mysql churning through data. I'd like to find out how to speed up this process. How do I find out what tables mysql is checking through when RT (the application) is loading a particular page? I'm pretty sure that the lag is caused by an oversized table. Are there any suggestions on how to find out more precisely which processes are taking place during the lag? Thanks. Kind regards. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to increase query speed ?
Hi List, I have a performance problem I can't get solved. I have 2 tables, called Updates (1 mln rows) and Data (5 mln rows). Table Updates has 2 (non-unique) keys, defined as: Hash bigint(20) unsigned default NULL Year tinyint(4) NOT NULL default '0' Table Data has the same 2 (non-unique) keys, defined as: Hash bigint(20) unsigned NOT NULL default '0' Year tinyint(4) NOT NULL default '0' The final purpose is to insert and update Data with new / changed Updates. Before doing so, i have a simple query, like: Select Count(*) From Data Inner Join Updates ON Data.Hash = Updates.Hash AND Data.Year = Updates.Year; but this one takes more than 30 minutes. Before running this query, I did: Analyze Table Updates, Data; to speed up acc. the manual 7.2.3. Does someone know how to increase the performance for this query ? Below the results of the explain for this query. TIA, regards Cor myQuery id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Updates ALL Hash,Year 1003823 1 SIMPLE Data ref Hash,Year Hash 8 Updates.Hash 2 Using where
PHP and mysql
Greetings everyone, I am having a problem with PHP and mysql I have copied a
script exactly off a website to open the database and insert a new user I
try to access the mysql database using the root user, and enter information
into the user table, this is the PHP I am using
?
include 'library/config.php';
include 'library/opendb.php';
$query = INSERT INTO user (host, user, password, select_priv, insert_priv,
update_ priv) .
VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y',
'Y');
mysql_query($query) or die('Error, insert query failed');
$query = FLUSH PRIVILEGES;
mysql_query($query) or die('Error, insert query failed');
include 'library/closedb.php';
?
When I try this I get the access for user [EMAIL PROTECTED] host denied, it is
driving me nuts no matter what I try I get the same thing other scripts I
have tried do the same thing, have tried messing around with the config.php
and opendb.php included scripts but nothing works if I run php -f opendb.php
I get no errors which I think means it is working, then again I don't know.
Anyone have any ideas ??
Thanks in advance
Visit our websites:
http://www.dailysnack.com “bite size news and gossip”
http://www.express.co.uk The Worlds Greatest Newspaper
http://www.dailystar.co.uk Simply The Best 7 Days A Week
http://www.happymagazine.co.uk The One Stop Shopping Magazine
http://www.ok.co.uk First For Celebrity News
http://www.northernandshell.co.uk The Mark Of Excellence
http://www.expresspictures.com Express Newspapers and OK Magazine online
picture archive
Also visit:
The NMA: Opening Up Newspapers http://www.nmauk.co.uk
###2004###
Any views or opinions are solely those of the author
and do not necessarily represent those of Express Newspapers
The information transmitted is intended only for the person
or entity to which it is addressed and may contain confidential
and/or privileged material.If you are not the intended recipient
of this message please do not read ,copy, use or disclose this
communication and notify the sender immediately. It should be
noted that any review, retransmission, dissemination or other
use of, or taking action in reliance upon, this information by
persons or entities other than the intended recipient is prohibited.
E-mail communications may be monitored.
##EXN2000##
Re: Can conditions be mixed with DISTINCT()
Hi, I think you could use something like SELECT DISTINCT (CASE p.ship_status WHEN '1' THEN shipping_now WHEN '2' THEN shipping_soon ELSE 'unknow' END) as status FROM products AS p INNER JOIN cart AS i ON i.product_id = p.id WHERE i.session_id = prepSQL(tConn, tSessionID); If there are any products for the selected session_id this should return one row for every status like this | status | +-+ | unknown | | shipping_now | | shipping_soon | HTH -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ On Tuesday 25 October 2005 01:00, Scott Haneda wrote: tSql = SELECT DISTINCT(p.ship_status) FROM products AS p INNER JOIN cart AS i ON i.product_id = p.id WHERE i.session_id = prepSQL(tConn, tSessionID); p.ship_status is either a 1 or a 0, which is just how the database was set up ages, ago. I am moving these to enum() types as I go, but to change this one, would break too much stuff. I would like to toss in a condition to the select so it returns shipping_now for 1 and shipping_soon for 2. When I do this, I get zero results returned. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: demon quits immediately...
Hi. Looks like the 'hosts' grant table is missing, corrupt, or does not have permissions set correctly. My examples come from version 4.1 I hope they apply, or at least point you in the right direction. My datadir is /var/lib/mysql - your's may be different within the datadir there is another directory 'mysql' - (/var/lib/mysql/mysql/) these are where the grant tables live. within that directory, you should have these: -rw-rw 1 mysql mysql 9064 2005-10-24 15:21 host.frm -rw-rw 1 mysql mysql0 2005-10-24 15:21 host.MYD -rw-rw 1 mysql mysql 1024 2005-10-24 15:21 host.MYI make sure they 1) exsist 2) owned by the mysql user (in my case, 'mysql') 3) readable, writeable by 'mysql' (of course) You can also attempt to rerun 'mysql_install_db' to recreate the grant tables. Please read http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html for more information on the mysql_install_db script. Also, if you are upgrading from 4.1 to 5.0, you may need to run 'mysql_fix_privelage_tables' to add aditional columns to your existing tables. This is probably not the case, tho. Hope that helps you! -Mike --- tom wible [EMAIL PROTECTED] wrote: Description: [EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686]# Starting mysqld daemon with databases from /usr/local/mysql-standard-5.0.15-linux-i686/data STOPPING server from pid file /usr/local/mysql-standard-5.0.15-linux-i686/data/tomsEmachine.pid 051024 19:52:09 mysqld ended [EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686]# cat /var/lib/mysql/tomsEmachine.err 051024 18:38:27 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 051024 18:38:28 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 051024 18:38:28 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 051024 18:38:29 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 051024 18:38:29 InnoDB: Started; log sequence number 0 0 051024 18:38:29 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 051024 18:38:30 mysqld ended 051024 19:54:03 mysqld started 051024 19:54:03 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051024 19:54:03 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 36808. InnoDB: Doing recovery: scanned up to log sequence number 0 43655 051024 19:54:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 051024 19:54:04 InnoDB: Started; log sequence number 0 43655 051024 19:54:04 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 051024 19:54:04 mysqld ended Submitter-Id:airdrummer Originator:tom wible Organization: MySQL support: none Synopsis:demon quits immediately Severity: critical Priority:[ low | medium | high ] (one line) Category:mysql Class:sw-bug Release:mysql-5.0.15-standard (MySQL Community Edition - Standard (GPL)) C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) C++ compiler: gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) Environment: machine, os, target, libraries (multiple lines) System: Linux tomsEmachine 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i386-redhat-linux Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-libgcj-multifile --enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gtk --with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre --host=i386-redhat-linux Thread model: posix gcc version 4.0.0
Re: demon quits immediately...
change the group chown -R mysql.daemon /dir/name try that On Tuesday 25 October 2005 16:05, Michael McFadden wrote: Hi. Looks like the 'hosts' grant table is missing, corrupt, or does not have permissions set correctly. My examples come from version 4.1 I hope they apply, or at least point you in the right direction. My datadir is /var/lib/mysql - your's may be different within the datadir there is another directory 'mysql' - (/var/lib/mysql/mysql/) these are where the grant tables live. within that directory, you should have these: -rw-rw 1 mysql mysql 9064 2005-10-24 15:21 host.frm -rw-rw 1 mysql mysql0 2005-10-24 15:21 host.MYD -rw-rw 1 mysql mysql 1024 2005-10-24 15:21 host.MYI make sure they 1) exsist 2) owned by the mysql user (in my case, 'mysql') 3) readable, writeable by 'mysql' (of course) You can also attempt to rerun 'mysql_install_db' to recreate the grant tables. Please read http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html for more information on the mysql_install_db script. Also, if you are upgrading from 4.1 to 5.0, you may need to run 'mysql_fix_privelage_tables' to add aditional columns to your existing tables. This is probably not the case, tho. Hope that helps you! -Mike --- tom wible [EMAIL PROTECTED] wrote: Description: [EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686]# Starting mysqld daemon with databases from /usr/local/mysql-standard-5.0.15-linux-i686/data STOPPING server from pid file /usr/local/mysql-standard-5.0.15-linux-i686/data/tomsEmachine.pid 051024 19:52:09 mysqld ended [EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686]# cat /var/lib/mysql/tomsEmachine.err 051024 18:38:27 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 051024 18:38:28 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 051024 18:38:28 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 051024 18:38:29 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 051024 18:38:29 InnoDB: Started; log sequence number 0 0 051024 18:38:29 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 051024 18:38:30 mysqld ended 051024 19:54:03 mysqld started 051024 19:54:03 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051024 19:54:03 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 36808. InnoDB: Doing recovery: scanned up to log sequence number 0 43655 051024 19:54:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 051024 19:54:04 InnoDB: Started; log sequence number 0 43655 051024 19:54:04 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 051024 19:54:04 mysqld ended Submitter-Id:airdrummer Originator:tom wible Organization: MySQL support: none Synopsis:demon quits immediately Severity: critical Priority:[ low | medium | high ] (one line) Category:mysql Class:sw-bug Release:mysql-5.0.15-standard (MySQL Community Edition - Standard (GPL)) C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) C++ compiler: gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) Environment: machine, os, target, libraries (multiple lines) System: Linux tomsEmachine 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i386-redhat-linux Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib
Error installing perl module DBS::mysql
** Low Priority ** Hello, I was able to install the following perl modules fine: DBI Data::Dumper Data::ShowTable DBI.pm Then,when I am try to install perl module called 'DBD::mysql' on AIX 5.2(64-bit) and 5.3. In both cases I am getting following errors: dogwood:mysql:/home/mysql/DBD-mysql-3.0002 # perl make Can't open perl script make: A file or directory in the path name does not exi st. dogwood:mysql:/home/mysql/DBD-mysql-3.0002 # make cc_r -c -I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI/ -I/usr/local/mysql/include -q64 -ma -qstrict -qoptimize=2 -qmaxmem=8192 -DDBD_M YSQL_INSERT_ID_IS_GOOD -g -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmax mem=16384 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FI LES -qlonglong -O-DVERSION=\3.0002\ -DXS_VERSION=\3.0002\ -I/usr/opt/ perl5/lib/5.8.2/aix-thread-multi/CORE dbdimp.c cc_r -c -I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI/ -I/usr/local/mysql/include -q64 -ma -qstrict -qoptimize=2 -qmaxmem=8192 -DDBD_M YSQL_INSERT_ID_IS_GOOD -g -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmax mem=16384 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FI LES -qlonglong -O-DVERSION=\3.0002\ -DXS_VERSION=\3.0002\ -I/usr/opt/ perl5/lib/5.8.2/aix-thread-multi/CORE mysql.c Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.so LD_RUN_PATH= /usr/bin/perl myld ld -bhalt:4 -bM:SRE -bI:/usr/opt/perl 5/lib/5.8.2/aix-thread-multi/CORE/perl.exp -bE:mysql.exp -bnoentry -lpthreads -l c_r dbdimp.o mysql.o -o blib/arch/auto/DBD/mysql/mysql.so -L/usr/local/mysql /lib -lmysqlclient -lz -lcrypt -lnsl_r -lm ld: 0711-317 ERROR: Undefined symbol: .mysql_field_seek ld: 0711-317 ERROR: Undefined symbol: .mysql_fetch_field ld: 0711-317 ERROR: Undefined symbol: .mysql_fetch_fields ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_bind_result ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_errno ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_error ld: 0711-317 ERROR: Undefined symbol: .mysql_real_escape_string ld: 0711-317 ERROR: Undefined symbol: .mysql_errno ld: 0711-317 ERROR: Undefined symbol: .mysql_error ld: 0711-317 ERROR: Undefined symbol: .mysql_init ld: 0711-317 ERROR: Undefined symbol: .mysql_options ld: 0711-317 ERROR: Undefined symbol: .mysql_real_connect ld: 0711-317 ERROR: Undefined symbol: .mysql_free_result ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_bind_param ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_execute ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_result_metadata ld: 0711-317 ERROR: Undefined symbol: .mysql_num_rows ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_store_result ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_num_rows ld: 0711-317 ERROR: Undefined symbol: .mysql_list_fields ld: 0711-317 ERROR: Undefined symbol: .mysql_real_query ld: 0711-317 ERROR: Undefined symbol: .mysql_use_result ld: 0711-317 ERROR: Undefined symbol: .mysql_store_result ld: 0711-317 ERROR: Undefined symbol: .mysql_affected_rows ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_close ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_fetch ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_affected_rows ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_fetch_column ld: 0711-317 ERROR: Undefined symbol: .mysql_fetch_row ld: 0711-317 ERROR: Undefined symbol: .mysql_fetch_lengths ld: 0711-317 ERROR: Undefined symbol: .mysql_insert_id ld: 0711-317 ERROR: Undefined symbol: .mysql_num_fields ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_init ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_prepare ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_param_count ld: 0711-317 ERROR: Undefined symbol: .mysql_get_host_info ld: 0711-317 ERROR: Undefined symbol: .mysql_info ld: 0711-317 ERROR: Undefined symbol: .mysql_get_proto_info ld: 0711-317 ERROR: Undefined symbol: .mysql_get_server_info ld: 0711-317 ERROR: Undefined symbol: .mysql_stat ld: 0711-317 ERROR: Undefined symbol: .mysql_thread_id ld: 0711-317 ERROR: Undefined symbol: .mysql_autocommit ld: 0711-317 ERROR: Undefined symbol: .mysql_close ld: 0711-317 ERROR: Undefined symbol: .mysql_rollback ld: 0711-317 ERROR: Undefined symbol: .mysql_commit ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_free_result ld: 0711-317 ERROR: Undefined symbol: .is_prefix ld: 0711-317 ERROR: Undefined symbol: .mysql_get_parameters ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_data_seek ld: 0711-317 ERROR: Undefined symbol: .mysql_data_seek ld: 0711-317 ERROR: Undefined symbol: .mysql_ping ld: 0711-317 ERROR: Undefined symbol: .mysql_list_dbs ld: 0711-317 ERROR: Undefined symbol: .mysql_shutdown ld: 0711-317 ERROR: Undefined symbol: .mysql_refresh ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information. make: 1254-004 The error code from the last command is 1. Stop. Any ideas why ?
Error installing perl module DBD::mysql
** Low Priority ** Hello, I was able to install the following perl modules fine: DBI Data::Dumper Data::ShowTable DBI.pm Then,when I am try to install perl module called 'DBD::mysql' on AIX 5.2(64-bit) and 5.3. In both cases I am getting following errors: dogwood:mysql:/home/mysql/DBD-mysql-3.0002 # perl make Can't open perl script make: A file or directory in the path name does not exi st. dogwood:mysql:/home/mysql/DBD-mysql-3.0002 # make cc_r -c -I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI/ -I/usr/local/mysql/include -q64 -ma -qstrict -qoptimize=2 -qmaxmem=8192 -DDBD_M YSQL_INSERT_ID_IS_GOOD -g -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmax mem=16384 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FI LES -qlonglong -O-DVERSION=\3.0002\ -DXS_VERSION=\3.0002\ -I/usr/opt/ perl5/lib/5.8.2/aix-thread-multi/CORE dbdimp.c cc_r -c -I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI/ -I/usr/local/mysql/include -q64 -ma -qstrict -qoptimize=2 -qmaxmem=8192 -DDBD_M YSQL_INSERT_ID_IS_GOOD -g -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmax mem=16384 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FI LES -qlonglong -O-DVERSION=\3.0002\ -DXS_VERSION=\3.0002\ -I/usr/opt/ perl5/lib/5.8.2/aix-thread-multi/CORE mysql.c Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.so LD_RUN_PATH= /usr/bin/perl myld ld -bhalt:4 -bM:SRE -bI:/usr/opt/perl 5/lib/5.8.2/aix-thread-multi/CORE/perl.exp -bE:mysql.exp -bnoentry -lpthreads -l c_r dbdimp.o mysql.o -o blib/arch/auto/DBD/mysql/mysql.so -L/usr/local/mysql /lib -lmysqlclient -lz -lcrypt -lnsl_r -lm ld: 0711-317 ERROR: Undefined symbol: .mysql_field_seek ld: 0711-317 ERROR: Undefined symbol: .mysql_fetch_field ld: 0711-317 ERROR: Undefined symbol: .mysql_fetch_fields ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_bind_result ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_errno ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_error ld: 0711-317 ERROR: Undefined symbol: .mysql_real_escape_string ld: 0711-317 ERROR: Undefined symbol: .mysql_errno ld: 0711-317 ERROR: Undefined symbol: .mysql_error ld: 0711-317 ERROR: Undefined symbol: .mysql_init ld: 0711-317 ERROR: Undefined symbol: .mysql_options ld: 0711-317 ERROR: Undefined symbol: .mysql_real_connect ld: 0711-317 ERROR: Undefined symbol: .mysql_free_result ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_bind_param ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_execute ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_result_metadata ld: 0711-317 ERROR: Undefined symbol: .mysql_num_rows ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_store_result ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_num_rows ld: 0711-317 ERROR: Undefined symbol: .mysql_list_fields ld: 0711-317 ERROR: Undefined symbol: .mysql_real_query ld: 0711-317 ERROR: Undefined symbol: .mysql_use_result ld: 0711-317 ERROR: Undefined symbol: .mysql_store_result ld: 0711-317 ERROR: Undefined symbol: .mysql_affected_rows ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_close ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_fetch ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_affected_rows ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_fetch_column ld: 0711-317 ERROR: Undefined symbol: .mysql_fetch_row ld: 0711-317 ERROR: Undefined symbol: .mysql_fetch_lengths ld: 0711-317 ERROR: Undefined symbol: .mysql_insert_id ld: 0711-317 ERROR: Undefined symbol: .mysql_num_fields ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_init ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_prepare ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_param_count ld: 0711-317 ERROR: Undefined symbol: .mysql_get_host_info ld: 0711-317 ERROR: Undefined symbol: .mysql_info ld: 0711-317 ERROR: Undefined symbol: .mysql_get_proto_info ld: 0711-317 ERROR: Undefined symbol: .mysql_get_server_info ld: 0711-317 ERROR: Undefined symbol: .mysql_stat ld: 0711-317 ERROR: Undefined symbol: .mysql_thread_id ld: 0711-317 ERROR: Undefined symbol: .mysql_autocommit ld: 0711-317 ERROR: Undefined symbol: .mysql_close ld: 0711-317 ERROR: Undefined symbol: .mysql_rollback ld: 0711-317 ERROR: Undefined symbol: .mysql_commit ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_free_result ld: 0711-317 ERROR: Undefined symbol: .is_prefix ld: 0711-317 ERROR: Undefined symbol: .mysql_get_parameters ld: 0711-317 ERROR: Undefined symbol: .mysql_stmt_data_seek ld: 0711-317 ERROR: Undefined symbol: .mysql_data_seek ld: 0711-317 ERROR: Undefined symbol: .mysql_ping ld: 0711-317 ERROR: Undefined symbol: .mysql_list_dbs ld: 0711-317 ERROR: Undefined symbol: .mysql_shutdown ld: 0711-317 ERROR: Undefined symbol: .mysql_refresh ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information. make: 1254-004 The error code from the last command is 1. Stop. Any ideas why ?
Re: Map of MySQL Users
Claire McLister [EMAIL PROTECTED] wrote on 10/24/2005 05:19:18 PM: Hi, We've developed an automatic email mapping capability from Google Maps API. To try it out, we mapped origins of emails to this group from October 3 through October 14th. The result of this map is at: http://www.zeesource.net/maps/map.do?group=460 Would like to hear what you think of it. Best wishes Claire -- Claire McLister[EMAIL PROTECTED] 1684 Nightingale Avenue Suite 201 Sunnyvale, CA 94087408-733-2737(fax) http://www.zeemaps.com I think your map is cool but you my information doesn't show up correctly. I know it's not your fault, It's my network's fault. Several people have tried to reverse my IP address into a real location and have thought I was anywhere from Nashville (where you found me), to Atlanta, to Raleigh. My actual location is in Spruce Pine, NC (USA) 28777 It's not important to me to get my flag in the correct place but this information may help you to refine your data mining techniques. Thank you and good job! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help on nested categories query
pedro mpa [EMAIL PROTECTED] wrote on 10/24/2005 06:32:48 PM: Hi! I need help on implementing a query that performs a search for items under nested categories. An example is on ebay search where you can restrict your search by selecting a subcategory and you only get items under that category/subcategories. Do I have to first get a list of all subcategories under the selected category and then loop OR's or is there a better way? Some example tables: | id | pid | category | 1 0cat1 2 0cat2 3 1cat3 4 1cat4 5 2cat5 | id | id_category | item | 13 item1 24 item2 35 item3 (Running Mysql 4.1.9 and PHP 5.0.4). Thanks in advance. Apologies for my bad English. Pedro. I think there is a better way to store your information which will ease your searching problems. Please read these articles and see if you get any ideas: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://www.sitepoint.com/article/hierarchical-data-database/2 Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: how to increase query speed ?
How about posting the explain for your query. Just put explain before it, MySQL with then tell you how it will go about executing the query, like which indexes it's using. I assume you have both columns indexed? On Oct 25, 2005, at 4:46 AM, C.R. Vegelin wrote: Hi List, I have a performance problem I can't get solved. I have 2 tables, called Updates (1 mln rows) and Data (5 mln rows). Table Updates has 2 (non-unique) keys, defined as: Hash bigint(20) unsigned default NULL Year tinyint(4) NOT NULL default '0' Table Data has the same 2 (non-unique) keys, defined as: Hash bigint(20) unsigned NOT NULL default '0' Year tinyint(4) NOT NULL default '0' The final purpose is to insert and update Data with new / changed Updates. Before doing so, i have a simple query, like: Select Count(*) From Data Inner Join Updates ON Data.Hash = Updates.Hash AND Data.Year = Updates.Year; but this one takes more than 30 minutes. Before running this query, I did: Analyze Table Updates, Data; to speed up acc. the manual 7.2.3. Does someone know how to increase the performance for this query ? Below the results of the explain for this query. TIA, regards Cor myQuery id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Updates ALL Hash,Year 1003823 1 SIMPLE Data ref Hash,Year Hash 8 Updates.Hash 2 Using where -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Huge table
I would check the slow query log. Queries that are taking a long time (meaning slow) should be logged there. You can check your MySQL configuration file to find out how long a query will run before it's considered slow and where it is logging the info. On Oct 25, 2005, at 3:16 AM, Luke Vanderfluit wrote: Hi. I have upgraded RT Request Tracker. I'm using Mysql 4.0.18, Apache 1.3.27 (with static modperl module). The database we are using is quite large. The main problem we are having is that certain processes can take quite a long time. This is due to mysql churning through data. I'd like to find out how to speed up this process. How do I find out what tables mysql is checking through when RT (the application) is loading a particular page? I'm pretty sure that the lag is caused by an oversized table. Are there any suggestions on how to find out more precisely which processes are taking place during the lag? Thanks. Kind regards. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to increase query speed ?
Hi Brent, Well I did post an EXPLAIN before my query, but it got squeezed at the end of my former email. The EXPLAIN for my query says: == id:1 select-type: simple table:Updates type: ALL possible_keys: Hash, Year key: NULL key_len:NULL ref:NULL rows:1003823 Extra: === id:1 select-type: simple table:Data type: ref possible_keys: Hash, Year key: Hash key_len:8 ref:Updates.Hash rows:2 Extra:using where = And yes, both columns Hash and Year and indexed in both tables. Regards, Cor - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 25, 2005 4:15 PM Subject: Re: how to increase query speed ? How about posting the explain for your query. Just put explain before it, MySQL with then tell you how it will go about executing the query, like which indexes it's using. I assume you have both columns indexed? On Oct 25, 2005, at 4:46 AM, C.R. Vegelin wrote: Hi List, I have a performance problem I can't get solved. I have 2 tables, called Updates (1 mln rows) and Data (5 mln rows). Table Updates has 2 (non-unique) keys, defined as: Hash bigint(20) unsigned default NULL Year tinyint(4) NOT NULL default '0' Table Data has the same 2 (non-unique) keys, defined as: Hash bigint(20) unsigned NOT NULL default '0' Year tinyint(4) NOT NULL default '0' The final purpose is to insert and update Data with new / changed Updates. Before doing so, i have a simple query, like: Select Count(*) From Data Inner Join Updates ON Data.Hash = Updates.Hash AND Data.Year = Updates.Year; but this one takes more than 30 minutes. Before running this query, I did: Analyze Table Updates, Data; to speed up acc. the manual 7.2.3. Does someone know how to increase the performance for this query ? Below the results of the explain for this query. TIA, regards Cor myQuery id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Updates ALL Hash,Year 1003823 1 SIMPLE Data ref Hash,Year Hash 8 Updates.Hash 2 Using where -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to increase query speed ?
Adding compound (hash, years) index (or even better unique index if it fits in your business logic) in both tables should speed up things. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setup for MythTV
Where can I get tips on how to manually set up MySQL for MythTV? MythTV's site was not very helpful in this regards. Jim C. signature.asc Description: OpenPGP digital signature
Re: Setup for MythTV
Jim C. [EMAIL PROTECTED] wrote on 10/25/2005 11:10:50 AM: Where can I get tips on how to manually set up MySQL for MythTV? MythTV's site was not very helpful in this regards. Jim C. [attachment signature.asc deleted by Shawn Green/Unimin] I think they (MythTV) probably know more about MySQL than we (the list members) do about them. What is MythTV? Is is a RTOS, a regular OS, some kind of application platform, a game system, ...? The more you can tell us about the platform you are trying to use, the more help you can get as I am sure there are others out there that can help but don't have the slightest clue what you are talking about. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Making mysql tables
Hi all, I am doing a tutorial to make a shopping cart. I need to make a mysql db or convert an sql db to mysql. Can anyone give me any pointers as to how to make the following tables? The Microsoft SQL Server 2000 creation script is below the tables. Thanks a lot for any pointers to get me started off- I will use php admin to make the table Categories: int_CategoryID int IDENTITY txt_Category nvarchar(100) bit_Active bit Clients: int_ClientID int IDENTITY txt_ClientName nvarchar(100) txt_ClientEmail ntext(16) txt_Username nvarchar(30) txt_Password nvarchar(30) bit_Active bit int_CFID int txt_CFTOKEN nvarchar(100) Products: int_ProductID int IDENTITY txt_ProductTitle nvarchar(100) txt_ProductDescription ntext(16) flt_ProductPrice float bit_Active bit int_CategoryID int PurchasedProducts: int_saleCart int IDENTITY int_ClientID int int_ProductID int int_Quantity int WebCart: int_CartID int IDENTITY int_ProductID int int_Quantity int int_ClientID int Microsoft SQL Server 2000 creation scripts: !- SQL Create Script Begins -- if exists (select * from sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Categories] GO if exists (select * from sysobjects where id = object_id(N'[dbo].[Clients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Clients] GO if exists (select * from sysobjects where id = object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Products] GO if exists (select * from sysobjects where id = object_id(N'[dbo].[PurchasedProducts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PurchasedProducts] GO if exists (select * from sysobjects where id = object_id(N'[dbo].[WebCart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[WebCart] GO CREATE TABLE [dbo].[Categories] ( [int_CategoryID] [int] IDENTITY (1, 1) NOT NULL , [txt_Category] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [bit_Active] [bit] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Clients] ( [int_ClientID] [int] IDENTITY (1, 1) NOT NULL , [txt_ClientName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [txt_ClientEmail] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [txt_Username] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [txt_Password] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [bit_Active] [bit] NULL , [int_CFID] [int] NULL , [txt_CFTOKEN] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[Products] ( [int_ProductID] [int] IDENTITY (1, 1) NOT NULL , [txt_ProductTitle] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [txt_ProductDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [flt_ProductPrice] [float] NULL , [bit_Active] [bit] NULL , [int_CategoryID] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[PurchasedProducts] ( [int_saleCart] [int] IDENTITY (1, 1) NOT NULL , [int_ClientID] [int] NULL , [int_ProductID] [int] NULL , [int_Quantity] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[WebCart] ( [int_CartID] [int] IDENTITY (1, 1) NOT NULL , [int_ProductID] [int] NULL , [int_Quantity] [int] NULL , [int_ClientID] [int] NULL ) ON [PRIMARY] GO !--- SQL Create Script Ends -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to increase query speed ?
Sorry I missed the explain part. You are doing a full table scan on the Updates table. There really is no way around speeding up reading a million rows. That's going to be dependent mainly on the speed of your disks and then how much memory you have. MySQL is optimizing your query to select from the Updates table instead, so you may want to change your query to match what MySQL is doing. Not sure if it would be faster, but it's something to try. Select Count(*) From Update Inner Join Data ON Data.Hash = Updates.Hash AND Data.Year = Updates.Year As Alexey mentioned, you should absolutely add a compound index on Hash+Year. The bottom line is that you are searching Data for over a million matching Updates. Without fast disks and/or lots of RAM for caching, you're not going to get great performance. You just have a lot of matches to sift through. On Oct 25, 2005, at 10:32 AM, C.R. Vegelin wrote: Hi Brent, Well I did post an EXPLAIN before my query, but it got squeezed at the end of my former email. The EXPLAIN for my query says: == id:1 select-type: simple table:Updates type: ALL possible_keys: Hash, Year key: NULL key_len:NULL ref:NULL rows:1003823 Extra: === id:1 select-type: simple table:Data type: ref possible_keys: Hash, Year key: Hash key_len:8 ref:Updates.Hash rows:2 Extra:using where = And yes, both columns Hash and Year and indexed in both tables. Regards, Cor - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 25, 2005 4:15 PM Subject: Re: how to increase query speed ? How about posting the explain for your query. Just put explain before it, MySQL with then tell you how it will go about executing the query, like which indexes it's using. I assume you have both columns indexed? On Oct 25, 2005, at 4:46 AM, C.R. Vegelin wrote: Hi List, I have a performance problem I can't get solved. I have 2 tables, called Updates (1 mln rows) and Data (5 mln rows). Table Updates has 2 (non-unique) keys, defined as: Hash bigint(20) unsigned default NULL Year tinyint(4) NOT NULL default '0' Table Data has the same 2 (non-unique) keys, defined as: Hash bigint(20) unsigned NOT NULL default '0' Year tinyint(4) NOT NULL default '0' The final purpose is to insert and update Data with new / changed Updates. Before doing so, i have a simple query, like: Select Count(*) From Data Inner Join Updates ON Data.Hash = Updates.Hash AND Data.Year = Updates.Year; but this one takes more than 30 minutes. Before running this query, I did: Analyze Table Updates, Data; to speed up acc. the manual 7.2.3. Does someone know how to increase the performance for this query ? Below the results of the explain for this query. TIA, regards Cor myQuery id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Updates ALL Hash,Year 1003823 1 SIMPLE Data ref Hash,Year Hash 8 Updates.Hash 2 Using where -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setup for MythTV
[EMAIL PROTECTED] wrote: Jim C. [EMAIL PROTECTED] wrote on 10/25/2005 11:10:50 AM: Where can I get tips on how to manually set up MySQL for MythTV? MythTV's site was not very helpful in this regards. Jim C. [attachment signature.asc deleted by Shawn Green/Unimin] I think they (MythTV) probably know more about MySQL than we (the list members) do about them. What is MythTV? Is is a RTOS, a regular OS, some kind of application platform, a game system, ...? The more you can tell us about the platform you are trying to use, the more help you can get as I am sure there are others out there that can help but don't have the slightest clue what you are talking about. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine MythTV is a kick-ass PVR application that runs under Linux. Like an open-source TiVO. -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setup for MythTV
Erich Beyrent [EMAIL PROTECTED] wrote on 10/25/2005 12:06:28 PM: [EMAIL PROTECTED] wrote: Jim C. [EMAIL PROTECTED] wrote on 10/25/2005 11:10:50 AM: Where can I get tips on how to manually set up MySQL for MythTV? MythTV's site was not very helpful in this regards. Jim C. [attachment signature.asc deleted by Shawn Green/Unimin] I think they (MythTV) probably know more about MySQL than we (the list members) do about them. What is MythTV? Is is a RTOS, a regular OS, some kind of application platform, a game system, ...? The more you can tell us about the platform you are trying to use, the more help you can get as I am sure there are others out there that can help but don't have the slightest clue what you are talking about. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine MythTV is a kick-ass PVR application that runs under Linux. Like an open-source TiVO. -- Erich Beyrent -- http://www.beyrent.net So follow the installation directions appropriate for the flavor of Linux you are using. That's where MySQL lives, in the OS, not the application layer. Full, step-by-step details and troubleshooting information can be found here: http://dev.mysql.com/doc/refman/5.0/en/installing.html How you connect a MythTV application (or subcomponent or whatever it's composed of) to MySQL is entirely dependent on how it's been written. You will have to refer to the documentation specific to your application to know how to use MySQL as a data backend. If you are building something from scratch, you have many choices. Many details can be found here: http://dev.mysql.com/doc/refman/5.0/en/apis.html You can also research the web, the lists archives, the forum archives, and any other documentation you can find in your quest for knowledge. If there are any specific issues we can help you figure out we (the list members) will be happy to help. Please be sure to include all relevant information with any new post. Best wishes! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Error 150 when inputing a dump
Hello, I dumped a database from our 4.0 server to a .sql file. When I try to input it back into the new 5.0 database, it gives me this error which I beleive is a foreign key constraint error. Normally I just put FOREIGN_KEY_CHECKS=0; and that solves it but it doesnt accept that line in 5.0 Any help would be appreciated, James
Re: Error 150 when inputing a dump
SOLVED, sorry for the message. It was a different error, will post on it shortly James - Original Message - From: James Sherwood [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 25, 2005 1:39 PM Subject: Error 150 when inputing a dump Hello, I dumped a database from our 4.0 server to a .sql file. When I try to input it back into the new 5.0 database, it gives me this error which I beleive is a foreign key constraint error. Normally I just put FOREIGN_KEY_CHECKS=0; and that solves it but it doesnt accept that line in 5.0 Any help would be appreciated, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Huge table
Hello. MySQL allows you to find out the slow queries. Have a look here: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html Luke Vanderfluit wrote: Hi. I have upgraded RT Request Tracker. I'm using Mysql 4.0.18, Apache 1.3.27 (with static modperl module). The database we are using is quite large. The main problem we are having is that certain processes can take quite a long time. This is due to mysql churning through data. I'd like to find out how to speed up this process. How do I find out what tables mysql is checking through when RT (the application) is loading a particular page? I'm pretty sure that the lag is caused by an oversized table. Are there any suggestions on how to find out more precisely which processes are taking place during the lag? Thanks. Kind regards. -- 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 command line error
Hello.
Is it possible that you leave some variable blank? Your script doesn't
produce any error for me:
[EMAIL PROTECTED] mysql-debug-4.1.14-pc-linux-gnu-i686]$ mysql
--defaults-file=my.cnf -u${DB_NAME} -p${DB_PASSWORD} -eGRANT select,
update, insert, delete ON $DBNAME.* TO [EMAIL PROTECTED] IDENTIFIED BY
\$DBPASS \;SET PASSWORD FOR [EMAIL PROTECTED] = OLD_PASSWORD
('$DBPASS');flush privileges;
Enter password:
[EMAIL PROTECTED] mysql-debug-4.1.14-pc-linux-gnu-i686]$
Dustin Krysak wrote:
Hi there - I am trying to issue the following command in a terminal
window... I know my syntax is slightly off, however I can not see
what... it seems to be due to the OLD_PASSWORD function... any pointers?
mysql -u${DB_NAME} -p${DB_PASSWORD} -eGRANT select, update, insert,
delete ON $DBNAME.* TO [EMAIL PROTECTED] IDENTIFIED BY \$DBPASS \;SET
PASSWORD FOR [EMAIL PROTECTED] = OLD_PASSWORD ('$DBPASS');flush
privileges;
Dustin
--
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: demon quits immediately...
Hello. 051024 18:38:29 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist Have you launched mysql_install_db? See: http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html tom wible wrote: Description: [EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686]# Starting mysqld daemon with databases from /usr/local/mysql-standard-5.0.15-linux-i686/data STOPPING server from pid file /usr/local/mysql-standard-5.0.15-linux-i686/data/tomsEmachine.pid 051024 19:52:09 mysqld ended [EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686]# cat /var/lib/mysql/tomsEmachine.err 051024 18:38:27 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 051024 18:38:28 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 051024 18:38:28 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 051024 18:38:29 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 051024 18:38:29 InnoDB: Started; log sequence number 0 0 051024 18:38:29 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 051024 18:38:30 mysqld ended 051024 19:54:03 mysqld started 051024 19:54:03 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051024 19:54:03 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 36808. InnoDB: Doing recovery: scanned up to log sequence number 0 43655 051024 19:54:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 051024 19:54:04 InnoDB: Started; log sequence number 0 43655 051024 19:54:04 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 051024 19:54:04 mysqld ended Submitter-Id:airdrummer Originator:tom wible Organization: MySQL support: none Synopsis:demon quits immediately Severity: critical Priority:[ low | medium | high ] (one line) Category:mysql Class:sw-bug Release:mysql-5.0.15-standard (MySQL Community Edition - Standard (GPL)) C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) C++ compiler: gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) Environment: machine, os, target, libraries (multiple lines) System: Linux tomsEmachine 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i386-redhat-linux Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-libgcj-multifile --enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gtk --with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre --host=i386-redhat-linux Thread model: posix gcc version 4.0.0 20050519 (Red Hat 4.0.0-8) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 19 21:47 /lib/libc.so.6 - libc-2.3.5.so -rwxr-xr-x 1 root root 1489572 May 30 08:05 /lib/libc-2.3.5.so -rw-r--r-- 1 root root 2523698 May 30 08:44 /usr/lib/libc.a -rw-r--r-- 1 root root 238 May 30 07:10 /usr/lib/libc.so lrwxrwxrwx 1 root root 10 Jun 19 23:31 /usr/lib/libc-client.a - c-client.a lrwxrwxrwx 1 root root 16 Jun 19 23:31 /usr/lib/libc-client.so - libc-client.so.0 -rwxr-xr-x 1 root root 773464 Mar 2 2005 /usr/lib/libc-client.so.0 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-extra-charsets=complex'
Re: Mysql Processes
Hello. Run ps -axm on the second server, and normally you should see a lot of mysqld threads. Edward David wrote: I just joined this list so I am hoping that this question is relevant to this group. I am running Linux AS4 Enterprise Server. I am running Mysql Ver 14.7 Distrib 4.1.12, for pc-linux-gnu (i686) using readline 4.3 With Client version 3.23.49. When I go and do a ps -ax I see 18 mysqld processes running. When I look at the pid file I only see one Process ID Number. When I look at another server that is running mysql that we do not have access to the cnf file I only see two processes running. Can anyone explain why this is happening and if it is normal. --- Edward David Sr. Systems Analyst University of Calgary Information Resources Information Technology Service --- -- 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: Running mysql on XP
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html T Uma Shankari wrote: Hello! I am trying to run mysql on XP. I have started the net start mysql. This service started without any error. My.ini file was there in c:\windows only. But i couldn't able to connect mysql. Can anyone please tell me where is the problem? regards uma -- 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: PHP and mysql
Hello.
May be this could be a bit helpful:
http://dev.mysql.com/doc/refman/5.0/en/access-denied.html
Alastair Roy wrote:
Greetings everyone, I am having a problem with PHP and mysql I have copied a
script exactly off a website to open the database and insert a new user I
try to access the mysql database using the root user, and enter information
into the user table, this is the PHP I am using
?
include 'library/config.php';
include 'library/opendb.php';
+ACQ-query = INSERT INTO user (host, user, password, select+AF8-priv,
insert+AF8-priv,
update+AF8- priv) .
VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y',
'Y');
mysql+AF8-query(+ACQ-query) or die('Error, insert query failed');
+ACQ-query = FLUSH PRIVILEGES;
mysql+AF8-query(+ACQ-query) or die('Error, insert query failed');
include 'library/closedb.php';
?
When I try this I get the access for user root+AEA-local host denied, it is
driving me nuts no matter what I try I get the same thing other scripts I
have tried do the same thing, have tried messing around with the config.php
and opendb.php included scripts but nothing works if I run php -f opendb.php
I get no errors which I think means it is working, then again I don't know.
Anyone have any ideas ??
Thanks in advance
Visit our websites:
http://www.dailysnack.com +IBw-bite size news and gossip+IB0-
http://www.express.co.uk The Worlds Greatest Newspaper
http://www.dailystar.co.uk Simply The Best 7 Days A Week
http://www.happymagazine.co.uk The One Stop Shopping Magazine
http://www.ok.co.uk First For Celebrity News
http://www.northernandshell.co.uk The Mark Of Excellence
http://www.expresspictures.com Express Newspapers and OK Magazine online
picture archive
Also visit:
The NMA: Opening Up Newspapers http://www.nmauk.co.uk
+ACMAIwAj-2004+ACMAIwAj-
+AF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8-
Any views or opinions are solely those of the author
and do not necessarily represent those of Express Newspapers
+AF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8AXwBfAF8-
The information transmitted is intended only for the person
or entity to which it is addressed and may contain confidential
and/or privileged material.If you are not the intended recipient
of this message please do not read ,copy, use or disclose this
communication and notify the sender immediately. It should be
noted that any review, retransmission, dissemination or other
use of, or taking action in reliance upon, this information by
persons or entities other than the intended recipient is prohibited.
E-mail communications may be monitored.
+ACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACoAKgAqACo-
+ACMAIw-EXN2000+ACMAIw-
--
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: Problem starting MySQL With Moved Data Directory
Hello. and restarted MySQL with /etc/init.d/mysql start, I get the following: Starting MySQL database server: mysqld. Checking for crashed MySQL tables in the background. /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' Is there something in MySQL error log? See: http://dev.mysql.com/doc/refman/5.0/en/error-access-denied.html http://dev.mysql.com/doc/mysql/en/Error_log.html Hal Vaughan wrote: I'm using MySQL 4.0.24-10 on Debian Sarge (Linux, and the stable branch of Debian, just to clarify). I had a setup of a program I've been working on for several years that is on a RAID. I changed MySQL's data directory so it will be on the same RAID and backed up, along with all my other data and programs. Originally this was set up under a Debian-based distro that was based on the testing and unstable branches of Debian. Before putting the server into production, I wanted to change it to Debian Stable (Sarge) for safety. (I'll sleep easier at night if it's on Sarge with regular security updates than depending on unstable packages!) So I backed up all the data on the RAID, wiped the original boot/system drive, installed Debian Sarge, did an fsck on the RAID, found errors, wiped it out, restored the original data, including the MySQL db files. When I installed MySQL on the re-done system, it worked. When altered the line in /etc/mysql/my.cnf from: datadir = /var/lib/mysql to: datadir = /thresh/tNet/db and restarted MySQL with /etc/init.d/mysql start, I get the following: Starting MySQL database server: mysqld. Checking for crashed MySQL tables in the background. /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' However, after I got this, I entered mysql and got a connection. The databases and tables seemed intact. I checked, and it turned out that somewhere in the backup/restore process the ownership of the files was changed. I went through and changed the ownership of /thresh/tNet/db to mysql:mysql and changed the ownership of /thresh/tNet/db/mysql to mysql:root. Basically, I changed the ownership of the files in the new data directory to match what I found in /var/lib/mysql, then I stopped and restarted MySQL. I still got the same error messages. While MySQL is working, I don't want to do anything with it because I'm not clear why debian-sys-maint needs access, as opposed to just the user mysql. To test, I made sure all the files from the db directory on down were set for all to read and write and all directories set for all to read, write, and execute. That doesn't help. So what is wrong, what do I need to do, and why didn't I get this error when using the same database directory before? Thanks for any help! Hal -- 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: Date storage format
Hello. For example, the size of the field. DATETIME uses only 8 bytes. See: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html Sinang, Danny wrote: Hello, Can anyone tell me what advantages there are in keeping dates and times in a MySQL DateTime field, as opposed to storing its string equivalent in a Varchar field ? Regards, Danny -- 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: Making mysql tables
I won't translate all of it but I can show you the patterns. See comments interspersed Ed [EMAIL PROTECTED] wrote on 10/25/2005 11:57:21 AM: Hi all, I am doing a tutorial to make a shopping cart. I need to make a mysql db or convert an sql db to mysql. Can anyone give me any pointers as to how to make the following tables? The Microsoft SQL Server 2000 creation script is below the tables. Thanks a lot for any pointers to get me started off- I will use php admin to make the table Categories: int_CategoryID int IDENTITY txt_Category nvarchar(100) bit_Active bit snip !- SQL Create Script Begins -- if exists (select * from sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Categories] GO MySQL equivalent: DROP TABLE IF EXISTS Categories; snip CREATE TABLE [dbo].[Categories] ( [int_CategoryID] [int] IDENTITY (1, 1) NOT NULL , [txt_Category] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [bit_Active] [bit] NULL ) ON [PRIMARY] GO Rule 1: change all [] around column names to `` Rule 2: convert column types to a MySQL equivalent. See: http://dev.mysql.com/doc/refman/5.0/en/column-types.html Rule 3: collation rules can sometimes be hard to set up correctly. Unless you need language-specific collations, stick with the defaults. Rule 4: if you need transactions, use the InnoDB engine. If not, use whatever fits. MySQL example (compare to the text description you posted first): CREATE TABLE Categories ( `int_CategoryID` INT AUTO_INCREMENT NOT NULL, `txt_Category` VARCHAR(100) default NULL, `bit_Active` BIT default NULL ) ENGINE=MyISAM; snip HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Error 1406
Hello, I dumped a database from our 4.0 server to a .sql file. When I try to input it back into the new 5.0 database, it gives me this error (1406 data too long for column) This works fine on 4.0 but will not work on 5.0. I also tried modifying the mediumtext to longtext in the dumpfile but it produces a column with a -1 size for some reason. Any ideas? Thanks, James
Re: Setup for MythTV
[EMAIL PROTECTED] wrote: Erich Beyrent [EMAIL PROTECTED] wrote on 10/25/2005 12:06:28 PM: [EMAIL PROTECTED] wrote: Jim C. [EMAIL PROTECTED] wrote on 10/25/2005 11:10:50 AM: Where can I get tips on how to manually set up MySQL for MythTV? MythTV's site was not very helpful in this regards. Jim C. Jim, Did you see this: http://www.mythtv.org/docs/mythtv-HOWTO-6.html And also: http://wilsonet.com/mythtv/tips.php (see the section on Front Ends) And lastly: http://dipper.info/project/ivtv/ -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP and mysql
Yes.
Whenever you have a problem, go back to basics. Can you login as root
on the commandline? What password do you use there? Once you're
there, check root's permissions, but I'd be willing to bet your
password is incorrect, or root is only allowed to logon from localhost
and not the machine the webserver is on.
-Sheeri
On 10/25/05, Alastair Roy [EMAIL PROTECTED] wrote:
Greetings everyone, I am having a problem with PHP and mysql I have copied a
script exactly off a website to open the database and insert a new user I
try to access the mysql database using the root user, and enter information
into the user table, this is the PHP I am using
?
include 'library/config.php';
include 'library/opendb.php';
$query = INSERT INTO user (host, user, password, select_priv, insert_priv,
update_ priv) .
VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y',
'Y');
mysql_query($query) or die('Error, insert query failed');
$query = FLUSH PRIVILEGES;
mysql_query($query) or die('Error, insert query failed');
include 'library/closedb.php';
?
When I try this I get the access for user [EMAIL PROTECTED] host denied, it is
driving me nuts no matter what I try I get the same thing other scripts I
have tried do the same thing, have tried messing around with the config.php
and opendb.php included scripts but nothing works if I run php -f opendb.php
I get no errors which I think means it is working, then again I don't know.
Anyone have any ideas ??
Thanks in advance
Visit our websites:
http://www.dailysnack.com +IBw-bite size news and gossip+IB0
http://www.express.co.uk The Worlds Greatest Newspaper
http://www.dailystar.co.uk Simply The Best 7 Days A Week
http://www.happymagazine.co.uk The One Stop Shopping Magazine
http://www.ok.co.uk First For Celebrity News
http://www.northernandshell.co.uk The Mark Of Excellence
http://www.expresspictures.com Express Newspapers and OK Magazine online
picture archive
Also visit:
The NMA: Opening Up Newspapers http://www.nmauk.co.uk
###2004###
Any views or opinions are solely those of the author
and do not necessarily represent those of Express Newspapers
The information transmitted is intended only for the person
or entity to which it is addressed and may contain confidential
and/or privileged material.If you are not the intended recipient
of this message please do not read ,copy, use or disclose this
communication and notify the sender immediately. It should be
noted that any review, retransmission, dissemination or other
use of, or taking action in reliance upon, this information by
persons or entities other than the intended recipient is prohibited.
E-mail communications may be monitored.
##EXN2000##
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'Autoextend' datafile - Mysql-Max-4.1.14 - Linux
Hi I need to have a single db that will have in excess of 100gb of data in it - For this i will need to create more that 1 datafile as the max file size of linux would be exceeded in this scenerio. On Oracle you can say that a datafile is x mb in size and it can autoextend to be x mb - Is this possible in MySQL 4.1.x ? I see that you can say that each datafile is x mb but that this filesize is created when the server is started eg if i want 100GB of space available then i can create 4x25GB files but they have to start at 25GB rather than all starting at say 1GB and then 'extending' to 25GB. So is it possible for all datafiles, rather than just the last one, be 'autoextending' Hope this makes sense -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order By Statement in Views not working properly
Mysql seems to have a problem returning ordered lists from views. ie, 'select * from myView order by column' does not return a properly ordered list. (it does change the ordering sequence, but to an erroneous list) converting myView to be a Temporary Table using 'ALGORITHM=TEMPTABLE' in the 'CREATE VIEW' statement works, but I imagine this is a performance hit, especially on large tables. Has anybody figured out why this is and come up with a solution, or am I missing something that wasn't in the manual? Note: I'm aware that any order by clauses in the create view statement itself are overridden by an order by clause when calling the view, but this isn't what I'm referring to. The myView statement has no order by clause, yet I can't seem to order the list when calling the view. Thanks, Scott Klarenbach -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Addition: Order By Statement in Views not working properly
Must be a bug of sorts: If you add an ORDER BY clause to the view itself, then mysql allows the caller to override with their own ORDER BY clause and it works as expected. However; if no ORDER BY clause is present in the CREATE VIEW statement, then the caller receives erroneous results if they call the view with their own ORDER BY clause. Scott Klarenbach. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP and mysql
Alastair Roy wrote:
Greetings everyone, I am having a problem with PHP and mysql I have copied a
script exactly off a website to open the database and insert a new user I
try to access the mysql database using the root user, and enter information
into the user table, this is the PHP I am using
?
include 'library/config.php';
include 'library/opendb.php';
you can't issue a query until you've defined your connection. Is that done in
one of the above include files? We need to see your connection code (don't show
us the password, of course) if we are to help troubleshoot this.
$query = INSERT INTO user (host, user, password, select_priv, insert_priv,
update_ priv) .
VALUES ('localhost', 'phpcake', PASSWORD('mypass'), 'Y', 'Y',
'Y');
mysql_query($query) or die('Error, insert query failed');
Error, insert query failed isn't very helpful. You need to print the mysql
error if you want to know what went wrong.
$query = FLUSH PRIVILEGES;
mysql_query($query) or die('Error, insert query failed');
Same here, except this is even worse, as it's the same message as before for a
different query. How will you tell which statement didn't work?
include 'library/closedb.php';
?
When I try this I get the access for user [EMAIL PROTECTED] host denied, it is
Perhaps that's just a typo, but it should be '[EMAIL PROTECTED]', not
'[EMAIL PROTECTED] host'.
driving me nuts no matter what I try I get the same thing other scripts I
have tried do the same thing, have tried messing around with the config.php
and opendb.php included scripts but nothing works if I run php -f opendb.php
I get no errors which I think means it is working, then again I don't know.
Anyone have any ideas ??
First, from what I can see, you don't know what's wrong because you don't print
the errors when something goes wrong. Of course, that means we can't tell you
either. I recommend following Example 1 in the PHP manual
http://www.php.net/manual/en/ref.mysql.php#mysql.examples, but I'd add that
you should actually print the query string in addition to the error. That way,
you can verify the query is actually what you intended. Something like this:
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die(Query: $query
failed: . mysql_error());
Then, if you still need help, copy and paste the exact error message into your
next post.
Second, you really should use GRANT to add a user and set privileges rather than
directly editing the user table. It's safer (for example, your INSERT will fail
if [EMAIL PROTECTED] already exists, but the equivalent GRANT statement would
still work), and you don't need FLUSH PRIVILEGES.
You appear to be trying to grant global SELECT, INSERT, and UPDATE privs to
[EMAIL PROTECTED] In that case, your two queries can be replaced by this one:
$query = GRANT SELECT, INSERT, UPDATE ON *.*
TO [EMAIL PROTECTED] IDENTIFIED BY 'mypass'
See the manual for details of using GRANT (and REVOKE) to manage users
http://dev.mysql.com/doc/refman/5.0/en/grant.html.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.0 cpu usage
Hello, Mysql 4.1 and 5 will NOT seem to play nice with french characters so I have tried to revert back to 4.0. I am trying to install 4.0 on a Win2003 Dell PowerEdge 2850 and when I run the service it uses a constant 25%cpu and seems locked up. I cannot even stop the service I have to end the process in the task manager. Any ideas? Thanks, James
Oracle DMP to mySQL -- Possible???
I have some data that has been dumped from Oracle into what appears to be a proprietary text file... It is not delimited into a format that I can just import into mysql (easily). The IT guy has provided me an outline of the dump.. ie; SQL describe isup051024; Name Null?Type - CCS_UNITIDNOT NULL CHAR(7) DIS_POSI_TION NOT NULL CHAR(3) OPCCHAR(11) CIRCUIT_ID_CODENUMBER etc.. etc.. etc.. Does mysql have a way to import these Oracle dumps easily?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reporting question
This is probably a silly question, but here goes... I have a table that logs access to web services, and I am looking to create a report to track the number of times each service was accessed in a given month. I know I can loop through the days in the month in PHP and do a seperate query for each day, but is there a way to do this in MySQL and populate dates not accessed with zeros? In other words, if I have hits for the 23rd and 25th, but not the 24th, can I get something like +++ | Day| Hits | +++ | 23 | 46 | | 24 | 0 | | 25 | 156| +++ Thanks in advance! -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.0 cpu usage
On Tue, 2005-10-25 at 15:39 -0300, James Sherwood wrote: Mysql 4.1 and 5 will NOT seem to play nice with french characters so I have tried to revert back to 4.0. I am trying to install 4.0 on a Win2003 Dell PowerEdge 2850 and when I run the service it uses a constant 25%cpu and seems locked up. I cannot even stop the service I have to end the process in the task manager. Any ideas? Logs? -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle DMP to mySQL -- Possible???
that looks like a spool file from sqlplus. does it have the data too or just a bunch of describes? if this is what he gave you he is either severly clue-challenged or trying to sabotage you (my $ on later though they're not mutually exclusive). you could write a perl program to parse this into something intelligable (to mysql) but it would be a fair amount of work. On 10/25/05, Cummings, Shawn (GNAPs) [EMAIL PROTECTED] wrote: I have some data that has been dumped from Oracle into what appears to be a proprietary text file... It is not delimited into a format that I can just import into mysql (easily). The IT guy has provided me an outline of the dump.. ie; SQL describe isup051024; Name Null? Type - CCS_UNITID NOT NULL CHAR(7) DIS_POSI_TION NOT NULL CHAR(3) OPC CHAR(11) CIRCUIT_ID_CODE NUMBER etc.. etc.. etc.. Does mysql have a way to import these Oracle dumps easily?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle DMP to mySQL -- Possible???
What you need is the table schemas and the data. Oracle doesn't have SHOW CREATE TABLE like mysql does. Furthermore, Oracle also doesn't have fun tools like AUTOINCREMENT -- you're going to want to know what the code is for any triggers on the data, because in order to do AUTOINCREMENT in oracle you use a trigger. So I think you're stuck on the Oracle DDL - MySQL DDL. Ask your Oracle DBA to give you a sample of the data, too (is it just a comma-separated, enclosed-by-something file? It's not likely going to be a series of INSERT statements, like mysqldump gives), AND the full code of all the triggers. -Sheeri On 10/25/05, Sid Lane [EMAIL PROTECTED] wrote: that looks like a spool file from sqlplus. does it have the data too or just a bunch of describes? if this is what he gave you he is either severly clue-challenged or trying to sabotage you (my $ on later though they're not mutually exclusive). you could write a perl program to parse this into something intelligable (to mysql) but it would be a fair amount of work. On 10/25/05, Cummings, Shawn (GNAPs) [EMAIL PROTECTED] wrote: I have some data that has been dumped from Oracle into what appears to be a proprietary text file... It is not delimited into a format that I can just import into mysql (easily). The IT guy has provided me an outline of the dump.. ie; SQL describe isup051024; Name Null? Type - CCS_UNITID NOT NULL CHAR(7) DIS_POSI_TION NOT NULL CHAR(3) OPC CHAR(11) CIRCUIT_ID_CODE NUMBER etc.. etc.. etc.. Does mysql have a way to import these Oracle dumps easily?? -- 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: Error installing perl module DBD::mysql
On 10/25/05, Ziaul Mannan [EMAIL PROTECTED] wrote: ** Low Priority ** Hello, I was able to install the following perl modules fine: DBI Data::Dumper Data::ShowTable DBI.pm Then,when I am try to install perl module called 'DBD::mysql' on AIX 5.2(64-bit) and 5.3. In both cases I am getting following errors: dogwood:mysql:/home/mysql/DBD-mysql-3.0002 # perl make Can't open perl script make: A file or directory in the path name does not exi st. dogwood:mysql:/home/mysql/DBD-mysql-3.0002 # make Typically, you would do the following to install Perl modules: perl Makefile.PL make make test make install I noticed that you typed 'perl make' instead of 'perl Makefile.PL'. -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reporting question
I'm not *certain* of this but I'm pretty sure that SQL will never give you zero rows of the kind you want from a query on a single table. (Then again, I'm not an SQL Genius so maybe someone else can suggest something that would work.) However, you can probably accomplish what you want fairly easily by creating a second table. Populate the second table with every date in the date range you care about - such as every single date in 2005 - and then do an outer join between the second table and the first. That result should produce a row for every date, including the ones that aren't represented in the first table. SQL would normally give you a null in the Hits column for rows whose date didn't exist in the first table but you should be able to use a coalesce() function to convert that to a zero. Mind you, null already means unknown or not applicable so maybe that is sufficient. The second table need not be permanent; you could generate it as a temp table when you need it so that it includes the appropriate range of dates (or days). In fact, that might be safer: if you make the date table permanent, you will have a problem later once your report is run for a period that falls fully or partially after the highest date in the date table. I assume you can write a program or script on your own to generate the INSERT statements that contain the desired dates. If you need code examples on any aspect of this, let me know and I can probably knock something together. Also, if you're not clear about the concept I'm suggesting, let me know and I'll try to clarify with a short example. Rhino - Original Message - From: Erich Beyrent [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 25, 2005 3:00 PM Subject: Reporting question This is probably a silly question, but here goes... I have a table that logs access to web services, and I am looking to create a report to track the number of times each service was accessed in a given month. I know I can loop through the days in the month in PHP and do a seperate query for each day, but is there a way to do this in MySQL and populate dates not accessed with zeros? In other words, if I have hits for the 23rd and 25th, but not the 24th, can I get something like +++ | Day| Hits | +++ | 23 | 46 | | 24 | 0 | | 25 | 156| +++ Thanks in advance! -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.361 / Virus Database: 267.12.5/148 - Release Date: 25/10/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.361 / Virus Database: 267.12.5/148 - Release Date: 25/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.15 on Red Hat 7.3?
Has anyone here successfully managed to install mysql 4.1.15 on a RedHat 7.3 box? RedHat 7.3 seems to be using the glibc 2.2.5 libraries and mysql seems to insist on using glibc 2.3. Am I out of luck? __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL for a single user
I was a database professor in a school of business until my retirement last year. I taught Oracle SQL for several years and taught one class in Uganda with MySQL so I am reasonably familiar with it from a user standpoint. However, I am not a developer nor a programmer. A professor in Uganda has asked me to work with him in developing MySQL teaching materials for African students, and we are considering an open source database textbook. I need to download MySQL 5.0 but I am not sure exactly which version to download. I use Windows, so the versions available to me are: Windows Essentials, Windows, and Without Installer. This will be installed on my personal machine and I will be the only user, so I don't need a network. Can someone advise me which of these is best? I mainly want to be able to create some tables and run some queries and views for testing the materials. Thanks much, Bruce Rollier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Scheme for Displaying Either/And/Or
I hope this isn't too long-winded. It's kind of hard to explain. I'm trying to figure out a scheme that will display animal distribution information. First, I want to divide animal species by continents. Next, I want to further identify species that live on islands that are associated with continents, like Japan (Continent = Eurasia, Island = Japan). Now, imagine four species with the following distribution: Eurasian bird - Mainland Eurasia, plus the British Isles and Japan Eurasian bird2 - Mainland Eurasia only Island Bird - British Isles and Japan only Japanese bird - Japan only I'm working on a content management system, so each bird will have its own page. Tentatively, I'd like the distribution for each bird to display like this: EURASIAN BIRD Eurasia* *Including British Isles and Japan EURASIAN BIRD2 Eurasia ISLAND BIRD Eurasia* British Isles Japan only JAPANESE BIRD Eurasia* Japan In other words, I always display a species' native continent by default, followed by a more detailed description if it also inhabits an island(s) or if it lives on an island only. What's the best way to do this? I thought one way to do that might be to make two columns, one for the broadest distribution (e.g. Eurasia), the other for the specific distribution (e.g. Japan). Then I could make a separate row for each place a species inhabits, as follows: SPECIES | DIST | DIST2 Eurasian Bird | Eurasia | Eurasia Eurasian Bird | Eurasia | British Isles Eurasian Bird | Eurasia | Japan Eurasian Bird2 | Eurasia | Eurasia Island Bird | Eurasia |British Isles Island Bird | Eurasia | Japan Japanese Bird | Eurasia | Japan In the table above, every species listed would display Eurasia, from colum DIST. Then I'd like to display this additional information based on the data in the last field, DIST2: Eurasian bird: *Including the British Isles Japan Island Bird: *British Isles and Japan only Japanese Bird: *Japan only I should add that I already have geographic tables that display parent-child relationships. However, they're tricky to work with because of varying number of steps between children and parents. For example, Europe's parent is Eurasia, but the Philippines have TWO parents - Asia, then Eurasia. Anyway, before I try to figure out how to display this with PHP I want to make sure I'm using a workable scheme. Any tips? Thanks. __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with syntax 4 creating 1 table
Shawn green was very kindly helping me ouy trying to make mysql tables and I thought I had go the hang of it until I get this error-any ideas? Thanks a lot CREATE TABLE PurchasedProducts( `int_saleCart` INT AUTO_INCREMENT NOT NULL ,`int_ClientID` INT,`int_ProductID` INT,`int_Quantity` INT,`int_saleCart` PRIMARY KEY ( int_saleCart ) ) ENGINE = MYISAM MySQL said: #1064 - 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 'PRIMARY KEY ( int_saleCart ) ) ENGINE = MYISAM' at line 6 Here is what I'm trying to make: PurchasedProducts:int_saleCart int IDENTITYint_ClientID intint_ProductID intint_Quantity int
Re: 'Autoextend' datafile - Mysql-Max-4.1.14 - Linux
From: Tom Brown [EMAIL PROTECTED] (...) starting at say 1GB and then 'extending' to 25GB. So is it possible for all datafiles, rather than just the last one, be 'autoextending' The short answer: no. http://dev.mysql.com/doc/refman/4.1/en/innodb-configuration.html states: The autoextend attribute and those following can be used only for the last data file in the innodb_data_file_path line. autoextend is available starting from MySQL 3.23.50 and 4.0.2. You could use two methods to manage the increasing need for space in your situation: 1) Use fixed sizes and keep track of the free space. Use SHOW TABLE STATUS LIKE 'tablename'; and use the `Comment` column which shows you something like InnoDB free: 5278720 kB. If this number gets too low, modify your config file and add an extra datafile. 2) Use an autoextend file and make it fixed size if it gets too large. http://dev.mysql.com/doc/refman/4.1/en/adding-and-removing.html tells you that you can change an autoextend datafile to a fixed size file by getting the size, rounding it to the closest multiple of 1024 x 1024 bytes (1MB), changing the settings in the configuration file in such a way that the autoextend file is now fixed to the size you calculated and that you added a new autoextend file. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in 4.0? 'CREATE TABLE `db`.`table ..' fails silently on replication slave
More investigation shows that the query does make it to the slave, at least into the relay log, yet it's still ignored. 'replicate-do-db' is set and shows the correct DB in the slave status. Atle - Flying Crocodile Inc, Unix Systems Administrator On Thu, 20 Oct 2005, Atle Veka wrote: I ran into a problem on a replication setup, where if you issue the following CREATE statement on the master the table will get created and the query entered into the binlog, however the slave ignores it silently: CREATE TABLE `db`.`table` (a INT DEFAULT 0) I have duplicated this exact issue on several different setups, one running 4.0.24 on both the slave and master. Is this by design, is it a bug, or? The manual states (http://dev.mysql.com/doc/refman/5.0/en/create-table.html): In MySQL 5.0, the table name can be specified as db_name.tbl_name to create the table in a specific database. This works whether or not there is a current database. If you use quoted identifiers, quote the database and table names separately. For example, `mydb`.`mytbl` is legal, but `mydb.mytbl` is not. Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with syntax 4 creating 1 table
Ed wrote: Shawn green was very kindly helping me ouy trying to make mysql tables and I thought I had got the hang of it until I get this error-any ideas? Thanks a lot CREATE TABLE PurchasedProducts( `int_saleCart` INT AUTO_INCREMENT NOT NULL , `int_ClientID` INT, `int_ProductID` INT, `int_Quantity` INT, `int_saleCart` PRIMARY KEY ( int_saleCart ) ) ENGINE = MYISAM MySQL said: Documentation |#1064 - 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 'PRIMARY KEY ( int_saleCart ) ) ENGINE = MYISAM' at line 6 | || |Here is what I'm trying to make:| | | |PurchasedProducts: int_saleCart int IDENTITY int_ClientID int int_ProductID int int_Quantity int| As the error message is telling you, you have the wrong syntax. The correct syntax is described in the manual http://dev.mysql.com/doc/refman/5.0/en/create-table.html. In short, you define a name with col_name COLUMN_TYPE optional_extras but you define an index with INDEX_TYPE optional_index_name (column or columns to be indexed). Your last line starts with a name, like a column defintion, but then tries to define an index (PRIMARY KEY). Instead, you need CREATE TABLE PurchasedProducts( `int_saleCart` INT AUTO_INCREMENT NOT NULL, `int_ClientID` INT, `int_ProductID` INT, `int_Quantity` INT, PRIMARY KEY (int_saleCart) ) ENGINE = MYISAM or simply CREATE TABLE PurchasedProducts( `int_saleCart` INT AUTO_INCREMENT NOT NULL PRIMARY KEY, `int_ClientID` INT, `int_ProductID` INT, `int_Quantity` INT ) ENGINE = MYISAM Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL for a single user
Since you want the setup with the least headache and because you are thinking of writing a book about how to use MySQL, I recommend the Without Installer version. It's basically 4 steps: 1) unzip 2) edit config file 3) install as service 4) start the service It can actually take you more time to diagnose a botched MSI install than it will take to do it by hand, correctly, the first time, while reading the step by step instructions. May I kindly refer you to the INSTALLATION section of the manual: http://dev.mysql.com/doc/refman/5.0/en/windows-installation.html I got my first MySQL database (ever) running in about 15 minutes by following these steps (as a complete MySQL newbie). Way less time than it ever took me to do a CD install of MS SQL server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Bruce Rollier [EMAIL PROTECTED] wrote on 10/25/2005 05:27:02 PM: I was a database professor in a school of business until my retirement last year. I taught Oracle SQL for several years and taught one class in Uganda with MySQL so I am reasonably familiar with it from a user standpoint. However, I am not a developer nor a programmer. A professor in Uganda has asked me to work with him in developing MySQL teaching materials for African students, and we are considering an open source database textbook. I need to download MySQL 5.0 but I am not sure exactly which version to download. I use Windows, so the versions available to me are: Windows Essentials, Windows, and Without Installer. This will be installed on my personal machine and I will be the only user, so I don't need a network. Can someone advise me which of these is best? I mainly want to be able to create some tables and run some queries and views for testing the materials. Thanks much, Bruce Rollier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scheme for Displaying Either/And/Or
David Blomstrom [EMAIL PROTECTED] wrote on 10/25/2005 06:23:52 PM: I hope this isn't too long-winded. It's kind of hard to explain. I'm trying to figure out a scheme that will display animal distribution information. First, I want to divide animal species by continents. Next, I want to further identify species that live on islands that are associated with continents, like Japan (Continent = Eurasia, Island = Japan). Now, imagine four species with the following distribution: Eurasian bird - Mainland Eurasia, plus the British Isles and Japan Eurasian bird2 - Mainland Eurasia only Island Bird - British Isles and Japan only Japanese bird - Japan only I'm working on a content management system, so each bird will have its own page. Tentatively, I'd like the distribution for each bird to display like this: EURASIAN BIRD Eurasia* *Including British Isles and Japan EURASIAN BIRD2 Eurasia ISLAND BIRD Eurasia* British Isles Japan only JAPANESE BIRD Eurasia* Japan In other words, I always display a species' native continent by default, followed by a more detailed description if it also inhabits an island(s) or if it lives on an island only. What's the best way to do this? I thought one way to do that might be to make two columns, one for the broadest distribution (e.g. Eurasia), the other for the specific distribution (e.g. Japan). Then I could make a separate row for each place a species inhabits, as follows: SPECIES | DIST | DIST2 Eurasian Bird | Eurasia | Eurasia Eurasian Bird | Eurasia | British Isles Eurasian Bird | Eurasia | Japan Eurasian Bird2 | Eurasia | Eurasia Island Bird | Eurasia |British Isles Island Bird | Eurasia | Japan Japanese Bird | Eurasia | Japan In the table above, every species listed would display Eurasia, from colum DIST. Then I'd like to display this additional information based on the data in the last field, DIST2: Eurasian bird: *Including the British Isles Japan Island Bird: *British Isles and Japan only Japanese Bird: *Japan only I should add that I already have geographic tables that display parent-child relationships. However, they're tricky to work with because of varying number of steps between children and parents. For example, Europe's parent is Eurasia, but the Philippines have TWO parents - Asia, then Eurasia. Anyway, before I try to figure out how to display this with PHP I want to make sure I'm using a workable scheme. Any tips? Thanks. My suggestion: Don't use parent-child tables to organize your geographic information. Read these articles and see if it doesn't make better sense to organize your geographies into trees. Basically, you end up assigning each animal only to the set of leaf nodes where it lives. The structure can be as deep as you want it to be. That way if something only lives on the Galapagos islands, it automatically lives in Argentenia and South America because that's how the tree would be set up. Make sense? http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://www.sitepoint.com/article/hierarchical-data-database/2 Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Oracle DMP to mySQL -- Possible???
On Tue, Oct 25, 2005 at 04:22:01PM -0400, sheeri kritzer wrote: What you need is the table schemas and the data. Oracle doesn't have SHOW CREATE TABLE like mysql does. Furthermore, Oracle also doesn't The oracle-supplied DBMS_METADATA package can do this for you. -Jason Martin -- Silence is more eloquent at times than words. This message is PGP/MIME signed. pgpLVYHOkxNxK.pgp Description: PGP signature
Re: Scheme for Displaying Either/And/Or
OK. I thought about that earlier, but I wasn't sure if I'd be able to adapt it to this particular problem. Do you think the automated tree traversal method described at the link you posted... http://www.sitepoint.com/article/hierarchical-data-database/2 ...is the only one that would work? I've never been able to get that to work. But I do have a working copy of the adjacency model, described at http://www.sitepoint.com/article/hierarchical-data-database It sounds like they do the same thing, but one is a little more efficient than the other. Thanks. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with syntax 4 creating 1 table
- Original Message - From: Ed To: mysql@lists.mysql.com Sent: Tuesday, October 25, 2005 6:40 PM Subject: Problem with syntax 4 creating 1 table Shawn green was very kindly helping me ouy trying to make mysql tables and I thought I had go the hang of it until I get this error-any ideas? Thanks a lot CREATE TABLE PurchasedProducts( `int_saleCart` INT AUTO_INCREMENT NOT NULL ,`int_ClientID` INT,`int_ProductID` INT,`int_Quantity` INT,`int_saleCart` PRIMARY KEY ( int_saleCart ) ) ENGINE = MYISAM MySQL said: #1064 - 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 'PRIMARY KEY ( int_saleCart ) ) ENGINE = MYISAM' at line 6 You need a comma before the "PRIMARY KEY" clause. That should solve your problem. In other words, change the second last line so that it looks like this: `int_saleCart`, PRIMARY KEY ( int_saleCart ) --^ Rhino No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.361 / Virus Database: 267.12.5/148 - Release Date: 25/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
