Re: How do increase memory allocated to MySQL?
2011/2/3 Yannis Haralambous : > > what am I doing wrong? > > the query was just > > SELECT * FROM wasfoundin WHERE yakoright LIKE '%geography%' When you use a leading wildcard symbol, MySQL will do a full table scan regardless of any indexes you've created. If you've got a MyISAM table, I recommend a FULLTEXT index. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to Get file modified time and date of file by using builtin function or procedure in sql?
On Thu, Jul 24, 2008 at 1:17 AM, Sivasakthi <[EMAIL PROTECTED]> wrote: > Hi All, > > how to Get file modified time and date of file by using builtin function or > procedure in sql? In a related thread from earlier today you were advised that any interaction with the filesystem should be done via a programming language of your choice. Did you have any luck writing a program to do just that? Kevin. -- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: Re: FW: Re: what is a schema? what is a database?
On Mon, Apr 7, 2008 at 1:24 AM, Moon's Father <[EMAIL PROTECTED]> wrote: > Schema is a collection of databases. A schema is a definition of tables & fields and their relationship. Kevin. -- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] - load
On 10/10/07, Ratheesh K J <[EMAIL PROTECTED]> wrote: > So every morning all the queries will be slow for the first time on the DB > server 2 and thereafter will be served by the query cache as they will be > cached and never invalidated until the night. Sorry for the late reply, I'm trying to get caught up on the posts to the list. I *think* your concern is really the first queries of the day having a cache miss and therefore taking longer than you'd like. If you know what the queries are going to be in advance (i.e this is a reporting application and your users typically request the same kind of thing every day), you could setup a cron job to run in the middle of the night to execute those queries. That would seed the query cache and when users begin to use your system in the morning, they wouldn't experience a cache miss slowdown. -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: MySQL patches from Google
On 4/26/07, Mike OK <[EMAIL PROTECTED]> wrote: I read the Google blog post regarding these patches. They admit using MySQL for some internal data storage needs but not in the general search system. Still, that leaves many other applications. Groups, gmail, reader, news et al... -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Administrator problem
On 1/19/07, Daniel Culver <[EMAIL PROTECTED]> wrote: Are you working on a Mac? If so, logging in as root is not good enough. You must have set up and log into the root user account of your Mac computer or server. The OP is talking about managing MySQL accounts with MySQL Administrator. MySQL Administrator does not in any way allow you to login to a host with a shell account, root or otherwise. -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is your favorite GUI tool for creating and querying tables in MySQL?
On 12/21/06, Behrang Saeedzadeh <[EMAIL PROTECTED]> wrote: Hi, What is your favorite GUI tool for working with MySQL. EMS SQL Manager is feature-rich but sometimes buggy and also very expensive. Navicat is not very handy. It forces to switch between mouse and keyboard repeatedly. What is your favorite tool? I've always found the MySQL Query Browser to be rather handy for creating & querying tables. http://dev.mysql.com/downloads/gui-tools/5.0.html -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
-help
Terry Spencer Haigh Consultancy Services Tel: +44 (0)116 262 3966 Fax: +44 (0)116 262 3946 (Leciester Office) Fax: +44 (0)870 052 4572 (Terry) Mob: +44 (0)7796108244 www.haigh-cs.co.uk <http://www.haigh-cs.co.uk>
join question
Hi All, I have a question for clearer brains than mine. I would like to join two tables,. There may be many possible joins in table B to table A, but I only want to join one row from B to table A - the row with the closest, but lesser date. TABLE A Row Id date 1 46 3 Jan 7 20 10 Jan TABLE B Row Id date 4 46 1 Jan 5 46 2 Jan 6 46 4 Jan 8 20 8 Jan 10 20 7 Jan 11 20 9 jan Result Row 1` in A is joined to row 5 in B Row 7` in A is joined to row 11 in B SELECT a.row, b.row FROM A a LEFT JOIN B b ON (a.id = b.id AND a.date > b.date AND ?) Any suggestions would be appreciated. Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop all tables?
On 1/2/06, JJ <[EMAIL PROTECTED]> wrote: > Is there an easy way to drop all (MyISAM) tables of a MySQL 3.23 database > besides deleting it and then recreating an empty database? Read up on mysqldump. For 3.23, something like the following should do the trick: mysqldump databasename --add-drop-table --no-data > your_output_file.sql And then import it: mysql databasename < your_output_file.sql -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Control Center
On 11/22/05, Jim Winstead <[EMAIL PROTECTED]> wrote: > On Tue, Nov 22, 2005 at 04:50:42PM -0800, Jon Drukman wrote: > > What happened to MySQL Control Center (aka mycc or mysqlcc)? The > > dev.mysql.com site redirects to the Query Browser page. QB is a poor > > substitute for mycc. It looks like neither of them has had active > > development much lately but at least mycc, even in its beta stage, is > > fairly useful. > > Development of MySQL Control Center was halted a couple of years ago. > The new GUI tools (Query Browser, Administrator, etc) are still under > active development. The Administrator can be downloaded here: http://dev.mysql.com/downloads/administrator/index.html The Query Browser can be downloaded here: http://dev.mysql.com/downloads/query-browser/1.1.html -- Kevin. -- 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]
Unable to Edit Table Data
I'm on Fedora Core 4. When I right-click on a table in Mysql-administrator and choose "Edit table data" nothing happens, and I get this in the console: (mysql-administrator-bin:4628): glibmm-CRITICAL **: unhandled exception (type Glib::Error) in signal handler: domain: g-exec-error-quark code : 8 what : Failed to execute child process "mysql-query-browser-bin" (No such file or directory) Is this a bug? Is there any other way to edit table data? Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Older tables caught between ISAM and MyISAM
I just upgraded a server to MySQL 4.1.4. I was at 4.0.8 - everything worked perfect at 4.0.8 However, since the upgrade, a few dozen tables in a few older databases are apparently still in ISAM format. The tables now misbehave when trying to access them(marked in use, error out, trash the server, you name it). This is especially troubling because one of the tables is mysql.user so none of my customer's programs work )-; downgrading back to 4.0.8 for other reasons unfortunately so I have to slog through this. What is infuriating is isamchk reports that each table is in fine shape(with an accurate record count), but no other utilities, like myisamchk, mysqldump, will touch the table . Likewise, none of the SQL statements like REPAIR table (with or without use_frm) or ALTER table work. All of the utilities & statements, without hardly an exception, complain that the MYI file is missing. It is missing and never existed for these tables as I have backups going back years and they are on none of them. My guess is these table files appear on the surface to these utilities to be MyISAM(header flag or something) file, but are really ISAM files. Any help if figuring out how to get the data out of the files and into newer tables would be APPRECIATED Spencer Yost Sundance Consulting 336.287.8017 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connect issues
I can connect on the command line, but have problems connecting using DBI/D on the same server. # ./bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.0-alpha-standard Mysql is running # ps -ef | grep mysql root 10626 9589 0 11:57 pts/341 00:00:00 /bin/sh ./bin/mysqld_safe mysql10642 10626 0 11:57 pts/341 00:00:00 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --bag ... I can connect at the command line # ./bin/mysql -username=hcspt Welcome to the MySQL monitor. Commands end with ; or \g. When I connect using perl on the same server the following error occurs ...failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'... Ive noted that mysql.sock is not in the location in the same directory as the above error message # find / -name mysql.sock /tmp/mysql.soc Setting the location of mysql.sock in my.conf [client] socket = /tmp/mysql.sock Causes error Any suggestions? Thanks Terry
RE: Hour counts
There are a few options, for more information see
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
"TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
Returns the integer difference between the date or datetime expressions
datetime_expr1 and datetime_expr2. The unit for the result is given by the
interval argument. The legal values for interval are the same as those
listed in the description of the TIMESTAMPADD() function.
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
TIMESTAMPDIFF() is available as of MySQL 5.0.0. "
It appears you require the answer in fraction hours. Set the interval to
seconds and divide the result by 3600 (60*60 = seconds in an hour)
---
" UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since
'1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is
called with a date argument, it returns the value of the argument as seconds
since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME
string, a TIMESTAMP, or a number in the format YYMMDD or MMDD in local
time.
mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580"
Convert both dates to seconds using UNIX_TIMESTAMP() and subtract one form
the other. Divide the result by 3600 (60*60 = seconds in an hour) to obtain
the fractional hours.
Terry
-Original Message-
From: Gyurasits Zoltán [mailto:[EMAIL PROTECTED]
Sent: 27 July 2005 17:12
To: mysql@lists.mysql.com
Subject: Hour counts
Hello All!
I would like to calculate the hour counts from 2 'datetime'.
Example: 2005-07-27 18:00 and 2005-07-27 19:30 => 1,5 hour
I try this but not good!
R1 : munkaido_end-munkaido_start /simple substract/
R2 : ROUND(ROUND((end-start)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 R3
07-14 15:00 07-14 17:30 23000 2.5 -74
07-14 23:00 07-15 01:30 783000 78.5 2
07-14 15:00 07-15 02:30 873000 87.5 11
07-14 15:00 07-14 16:00 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Explain and indexes
Im trying to speed up a query. select project_id from timesheet ts where ts.del is null and signoff = 'A' The output of explain is detailed below. ++-+---+--+-+--+ | id | select_type | table | type | possible_keys | key | key_len ++-+---+--+-+--+ | 1 | SIMPLE | ts| ALL | signoff,del,del_signoff | NULL |NULL ++-+---+--+-+--+ | ref | rows | Extra | ++-+---+--+-+--+ | NULL | 3907 | Using where | ++-+---+--+-+--+ An index exists on all three columns referred to, in addition to a combination of del and signoff. The indexes are listed as possible keys, but none used by the query; key = null. Can anyone suggest why? How can I optimise this? Thanks Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching a table and replacing all instances of a string with another
On Fri, 12 Nov 2004 22:12:29 -0500, Joshua Beall <[EMAIL PROTECTED]> wrote: > Hi All, > > I would like to search through all fields in a table, and anytime a search > string comes up, have it replace it with another string. By way of example, > let's say I wanted to replace every occurence of 'Peter' with 'Paul' - can I > do this purely with SQL? > > I know I could do it in PHP fairly easily, but I am wondering if there is a > way I can just feed a query to MySQL that will take care of things. > How big is the table? If it's not that big, dump it to a file via mysqldump, open the file in vi, do a global search and replace, re-load the table, and you're done. -- Kev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Posting Question
On Tue, 9 Nov 2004 13:21:54 -0600, Lewick, Taylor <[EMAIL PROTECTED]> wrote: > I am asking before I post so I don't anger everyone... > > Is this list okay to post a specific question regarding multiple row > inserts.. > > I am doing this in perl, and I need some help with the perl part... There are a number of Perl lurkers (myself included) on this list so I'm sure someone wouldn't mind helping out. For future reference, perlmonks.org is a great place to post Perl specific questions if you feel your problem is a little too OT for a MySQL list. -- Kev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ho do I backup
On Thu, 28 Oct 2004 12:24:02 +0100, Barry Zimmerman <[EMAIL PROTECTED]> wrote: > > I have tried the following: > > mysqldump -u admin -p --databases yabbse > yabbsebackup.sql > > It seemed to work, but where can I find the yabbsebackup.sql file? Not very > experienced with backing up. Barry, The yabbsebackup.sql file will reside in whatever directory you were in when you issued the mysqldump command. The > symbol indicates you want the output of mysqldump redirected to a file instead of STDOUT. What you place after the > symbol is the filename. If you do not specify a path (i.e > /path-to/some-dir/yabbsebackup.sql) , the file will be created in the current working directory. -- Kev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing DBIx::DWIW on CPAN
On Wed, 15 Sep 2004 11:27:55 -0700, Sanjeev Sagar <[EMAIL PROTECTED]> wrote: > > Hello All, > > I am trying to install DBIx::DWIW but giving me following error. > > No such file `DBIx-DWIW-0.41.tar.gz' > > I am trying to install from CPAN > > cpan> install DBIx::DWIW > > Could not fetch authors/id/J/JZ/JZAWODNY/DBIx-DWIW-0.41.tar.gz > Giving up on '/root/.cpan/sources/authors/id/J/JZ/JZAWODNY/DBIx-DWIW-0.41.tar.gz' > Note: Current database in memory was generated on Wed, 15 Sep 2004 11:08:29 GMT If the CPAN shell is giving you problems, try downloading and installing the module manually in the usual way. After extracting the contents of the tarball: Perl Makefile.PL make make test make install -- Kev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regular expresion replace possibility?
>From the manual:
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced
by
the string to_str:
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
This function is multi-byte safe.
Terry Spencer
Haigh Consultancy Services
+44 (0)2073007329
www.haigh-cs.co.uk
-Original Message-
From: Dean Householder [mailto:[EMAIL PROTECTED]
Sent: Monday, July 21, 2003 9:49 PM
To: [EMAIL PROTECTED]
Subject: Regular expresion replace possibility?
Is it possible to run a query that will just alter text possibly using a
regular expression? I have about 250 rows that I want to strip quotes
out of. Does anyone know of an easy way to do this?
Dean
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Faster reindexing
Check out the EXPLAIN command "EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name. When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would process the SELECT, providing information about how tables are joined and in which order. With the help of EXPLAIN, you can see when you must add indexes to tables to get a faster SELECT that uses indexes to find the records." http://www.mysql.com/doc/en/EXPLAIN.html Terry Spencer Haigh Consultancy Services +44 (0)2073007329 www.haigh-cs.co.uk -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 5:23 AM To: Dominicus Donny; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 11:23 +0700 7/9/03, Dominicus Donny wrote: >Try analyze your table(s). What information will this yield to make indexing faster? > >"Me fail English? That's unpossible" >###___Archon___### > >- Original Message - >From: "electroteque" <[EMAIL PROTECTED]> >To: "Paul DuBois" <[EMAIL PROTECTED]>; "Florian Weimer" <[EMAIL PROTECTED]>; ><[EMAIL PROTECTED]> >Sent: Wednesday, July 09, 2003 10:23 AM >Subject: RE: Faster reindexing > > >> when reimporting or reinserting or whatever from a huge db i usually drop >> all the indexes reimport then create them again much quicker >> >> -Original Message- >> From: Paul DuBois [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, July 09, 2003 1:09 PM >> To: Florian Weimer; [EMAIL PROTECTED] >> Subject: Re: Faster reindexing >> >> >> At 9:39 +0200 7/7/03, Florian Weimer wrote: >> >I've got a table with 100 million rows and need some indexes on it >> >(one row is 126 bytes). >> > >> >I'm currently using MyISAM and the indexing proceeds at an >> >astonishingly low rate: about 200 MB per hour. This is rate is far >> >too low; if we had to recover the database for some reason, we'd have >> >to wait for days. >> > >> >The table looks like this: >> > >> >CREATE TABLE flows ( >> > versionCHAR NOT NULL, >> > router CHAR(15) NOT NULL, >> > src_ip CHAR(15) NOT NULL, >> > dst_ip CHAR(15) NOT NULL, >> > protocol TINYINT UNSIGNED NOT NULL, >> > src_port MEDIUMINT UNSIGNED NOT NULL, >> > dst_port MEDIUMINT UNSIGNED NOT NULL, >> > packetsINTEGER UNSIGNED NOT NULL, >> > bytes INTEGER UNSIGNED NOT NULL, >> > src_if MEDIUMINT UNSIGNED NOT NULL, >> > dst_if MEDIUMINT UNSIGNED NOT NULL, >> > src_as MEDIUMINT UNSIGNED NOT NULL, >> > dst_as MEDIUMINT UNSIGNED NOT NULL, >> > src_netCHAR(1) NOT NULL, >> > dst_netCHAR(1) NOT NULL, >> > direction CHAR(1) NOT NULL, >> > class CHAR(1) NOT NULL, >> > start_time CHAR(24), >> > end_time CHAR(24) >> >); >> > >> >Indexes are created using this statement: >> > >> >mysql> ALTER TABLE flows >> > -> ADD INDEX dst_ip (dst_ip, src_ip), >> > -> ADD INDEX dst_port (dst_port, start_time), >> > -> ADD INDEX src_ip (src_ip, start_time), >> > -> ADD INDEX time (start_time); >> > >> >In theory, we could represent the columns router, src_ip, dst_ip, >> >start_time, end_time using integers of the appropriate size, but this >> >would make ad-hoc queries harder to type (and porting our applications >> >would be even more difficult). >> >> Perhaps, but as a test, you might add a couple of extra columns to >> the table, then populate them like this after loading the table: >> >> UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = >> INET_ATON(dst_ip); >> >> Then try creating the indexes using int_src_ip and int_dst_ip rather >> than src_ip and dst_ip. >> >> If it's significantly faster, you may want to reconsider whether it might >> not be worth using INET_ATON(X) in your queries rather than X. >> >> > >> >Should I switch to another table type? >> >> It's easy enough to convert the table to, e.g., InnoDB and then >> create the indexes, so an empirical test should not be difficult. >> >> -- >> Paul DuBois, Senior Technical Writer >> Madison, Wisconsin, USA >> MySQL AB, www.mysql.com >> > > Are you MySQL certified? http://www.mysql.com/certification/ -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
KEYS error 1216
Hi All, Im altering a number of table from MyISAM to innoDb and adding foreign keys. The alteration of the table type works. Adding the row as an index works. Adding the foreign key fails, generating the error: alter table project add FOREIGN KEY (company_id) references company (id) [mySQL] ERROR 1216: Cannot add or update a child row: a foreign key constraint fails CREATE TABLE company ( id int NOT NULL auto_increment, PRIMARY KEY (id) ) CREATE TABLE project ( id int NOT NULL auto_increment, company_id int default NULL, ) Would anyone have any idea what causing this error? Thanks Terry Terry Spencer Haigh Consultancy Services +44 (0)2073007329 www.haigh-cs.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update in select
Thanks for the help, that close to what Im after. I however simplified the problem too much in my previous question and Im still not there. :) Im imitating a 'tree' structure. The table has rows - ID and parent_ID. The depth of the tree is only three layers (parent -> child -> grandchild). There may be any number of parents. Various solution exists to the tree problem that involve adding a additional row (or two) to the table. This is not an option available to me at this point. Under certain circumstances if the parent is updated, I also need to update the children and grandchildren. So far I have (using a select while testing :) select t1.*, t2.*, t3.* from tree t1, tree t2, tree t3 where t1.id = 10 and t2.parent_id = t1.id and t3.parent_id = t2.id However this only returns the parent row. If I instead use "select t2.*" the children are returned If I instead use "select t3.*" the grandchildren are returned So my problem is 1. why is only the result selected for the front table; t1 returns only parent, t2 return only the children 2. this fails for parent that have no children, and children that have no grandchildren. Any thought, suggestion, points are greatly appreciated. Im using 4.1. Thanks Terry -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 2:53 PM To: Terry Spencer; [EMAIL PROTECTED] Subject: RE: Update in select Subqueries are only available in MySQL 4.1. However, you should be able to write this as follows: UPDATE test1 a, test2 b SET a.visit_date = NOW() WHERE a.id = b.id AND b.code = 'Z'; Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Terry Spencer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 7:26 AM To: [EMAIL PROTECTED] Subject: Update in select Im attempting to update a table. We perform a select on the table to determine what row to update. update test a set visit_date = now() where a.id in (select b.id from test b where code ='Z') Running this generates an error. "You cant specify target table 'test' for update in FROM clause." I can locate documentation on the constraints on referring to the target table in the FROM clause. Can anyone point me to any? In my example the rows the select is the select are not being updated. Is there anyways to perform this type of statement? Thanks. Terry -- 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]
Update in select
Im attempting to update a table. We perform a select on the table to determine what row to update. update test a set visit_date = now() where a.id in (select b.id from test b where code ='Z') Running this generates an error. "You cant specify target table 'test' for update in FROM clause." I can locate documentation on the constraints on referring to the target table in the FROM clause. Can anyone point me to any? In my example the rows the select is the select are not being updated. Is there anyways to perform this type of statement? Thanks. Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bug with MERGE tables and MAX
>Description: MAX fails to return the correct value in some MERGE table situations. Based on experimentation I am guessing that the query fails to read all of the member tables when the query can be performed entirely by reading the index. I seem to be able to reproduce this every time on MySQL 3.23.51, .55, and one of the versions in between (but I forget which). I don't believe this is the same as any of the known bugs listed in "MERGE table problems" in the docs. But I could be wrong. >How-To-Repeat: Here is a sequence of commands that can be source-d: -- optional drop table if exists whole; drop table if exists part1; drop table if exists part2; -- create tables create table part1 (id int(10) not null, value int(10), key (id, value)); create table part2 (id int(10) not null, value int(10), key (id, value)); create table whole (id int(10) not null, value int(10), key (id, value)) type=merge union=(part1,part2); -- insert some values insert into part1 values (1, 100), (2, 200), (3, 300); insert into part2 values (1, 200), (5, 500); -- this correctly shows all 5 rows select * from whole; -- this correctly shows the 2 values where id = 1 select value from whole where id = 1; -- this ought to say 200 but says 100 instead select max(value) from whole where id = 1; -- this also ought to say 200 but says 100 instead select floor(max(value)) from whole where id = 1; -- this correctly says 200 select max(floor(value)) from whole where id = 1; -- this also correctly says 200 select id, max(value) from whole where id = 1 group by id; >Fix: Workaround: in the query, replace MAX(expr) with MAX(FUNC(expr)) where FUNC is a no-op. The original problem arose with a datetime column (I changed it to an int for the test case above), so e.g. FROM_UNIXTIME(MAX(UNIX_TIMESTAMP(tstamp))) works around the bug. >Submitter-Id: >Originator:S. Spencer Sun >Organization: >MySQL support: none >Synopsis: MAX fails to return the correct value in some MERGE table situations >Severity: serious >Priority: high >Category: mysql >Class: sw-bug >Release: mysql-3.23.55 (Source distribution) >Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for pc-linux on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.55-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 10 min 22 sec Threads: 1 Questions: 121 Slow queries: 0 Opens: 35 Flush tables: 1 Open tables: 6 Queries per second avg: 0.195 >Environment: System: Linux pratt 2.2.22-6.2.2smp #2 SMP Tue Oct 22 20:32:19 PDT 2002 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O3 -mpentiumpro' CXX='gcc' CXXFLAGS='-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 13 2001 /lib/libc.so.6 -> libc-2.1.3.so -rwxr-xr-x1 root root 4106572 Sep 9 09:58 /lib/libc-2.1.3.so -rw-r--r--1 root root 20336836 Sep 9 09:58 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 9 09:58 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local' '--localstatedir=/var/mysql' '--enable-assembler' '--with-mysqld-ldflags=-all-static' '--with-raid' '--enable-local-infile' 'CFLAGS=-O3 -mpentiumpro' 'CXXFLAGS=-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How much data can MySQL push out?
Have you tried installing a Windows copy of MySQL on the web server for the replication suggestion given before? I know it may be difficult to do politically. This is a little hard to do politically for a number of reasons. 1) In order for this to work, the backend database (which allows updates) would also need to be MySQL, right now it is MSSQL 2) We could change the app to maybe use MySQL, but that would be a big hurdle its self 3) We would be adding a database to a machine which is already somewhat busy (we split the web and database off of the same box because the CPUs where not able to keep up with both web and db activity) 4) While we do use MySQL on another website, it might be hard convincing them we should put it on a windows box (we have traditionally be a UNIX shop, though that is changing a little) thanks benji --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How much data can MySQL push out?
Sorry, I do not have much experience with MySQL on Suns (at least not in pushing it to the limits). On an Athlon 700Mhz selecting 1 random rows out of 6, I get over 330MB/sec (1000 queries/sec) on localhost and about 5.5MB/sec via a 100MBit TCP connection using the mysql command line client like this: this helps A LOT. MySQL doesn't have to be on the Sun Box. What OS where you using? We could put MySQL on a Windows box. I don't know that I could convince them to put it on a Linux box though. --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How much data can MySQL push out?
I'm sure you've already specified this, but here's a few questions:
Probably not a bad idea to recap
What size of machine (CPU, RAM, # and type of drives) are you using for
each server? (MySQL vs. MS SQL)
MSSQL (Current setup) - 2-1.2 GHZ CPUs, 4 gig of mem, and a RAID5 SCSI
array, Server 2000
MySQL-UNIX (perspective option) - 2-450 MHZ, 2 gig of memory, an EMC Disk
array, Solaris 2.8
How many queries per second are you getting to the web server?
queries per second or requests per second? (web server vs database server)
Web Server: 5-15 requests per second
Database Server: 15-25 transactions per second (queries per second) with
peaks slightly higher
How big are the tables (in rows) that the queries are made against?
Table A: roughly 16K rows (about 25 fields)
Table B: roughly 15K rows (4 fields, of which 1 is a text field)
Table C: 700 rows
Table D: 75 rows
Data from those tables make up 99.999% of the queries
Do you have indexes on all the fields that are used in JOINs in the
queries? If any are VARCHARs or long CHARs, are the indexes against
subsets of the data ("ADD INDEX data_idx(data(10))" for example)?
We indexed any field which might have a condition run against it as well as
any field which does a join (which happens to be the same ones which
usually have a condition run against it)
benji
---
Ben Spencer
Web Support
[EMAIL PROTECTED]
x 2288
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How much data can MySQL push out?
It of course depends on how frequently the data is needed as well. If the data is being requested a lot more frequently than the data is updated, doing a static page that is updated outside the http request is the faster way to do things. If the page is requested less frequently or barely more frequently than the page's data is updated, doing a static page doesn't help much. This is also true if the page is different for different users (prime examples of course are Slashdot's homepage vs. its story pages). (some people will consider this different then I do) The information isn't something that needs "live content" as it really doesn't change that often. If the cache is updated daily with specific areas being updated every couple of hours, it would be fine. That is why that might be an option. However, it might be easier to switch from MSSQL to MySQL if MySQL would handle the traffic better. We do need a little logic to determine if the user is logged in (if they aren't logged in, they don't see the page) but it shouldn't be all that complicated. thanks. benji --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How much data can MySQL push out?
Been waiting for someone to ask. The entire database is actually slightly over 30K (35K is a closer number...small non-the-less) How does 12 MBytes/s come from 35K rows? Part of the output is a large text field. Each chunk of large text is no more then 100K and not each has a large text with it (and some of the large texts which are returned are essentially empty). It is possible for more then one select to return a couple large text blocks, though the majority do not. Combine that data with 15-25 queries/sec (I am assuming in MSSQL that a transaction = query of some sort), it is a chunk of data. Assume that you also return more then one row at a time (select * from table where start_date < now and end_date > now). The bandwidth numbers are something which PerfMon and MRTG are telling us. The underlying code is (currently) (ASP, so it is using ADO to do the connection.) I also wouldn't disagree with some of the application elements being no-very-good and that fixes are needed there. We can even make some code changes which will help for the short term. However we are trying to build a case for what is really needed both short term and long term. Time and Money are also both issues which we are trying to work with. I hate to jump into this, but I have to ask if you have only 30k rows why are you producing such large amounts of data? Are you trying to store blobs or large text data types? I don't think you will ever find a database vendor that wants to compete with a local filesystem under those kinds of conditions. How do you connect to get the data ODBC, JDBC, DBD:DBI, PHP? none of these are designed to move large amounts of data. I think the problem you have is with the application not the database, I would encourage you to move away from MSSQL if possible, but in this case I don't think changing the RDBMS will fix the problem. --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How much data can MySQL push out?
How much a database can push out has a lot dependencies. The first probably being a very fast disk subsystem. If your disk can't pump out way more than what you are trying to get, then it doesn't matter what software or how many CPU's you have. Disk is hardly being used. The database its self is fairly small (30K rows?) and (from what I can tell) is all in memory. (in the current MSSQL setup). I could go on about dependencies on your data (1 image or many rows), your indexes (very important for queries per second), your web server, etc. Indexes we can add, which we have. Unfortunately we are dealing with a packaged application which we have only some abilities to deal with the code (a total switch to MySQL we could rewrite the code for, but we can not optimize specific queries as there is a common SQL generator which actually generates the SQL statement). But I think you are more interested on if it can be done with MySQL. YES, it can. I was just reading yesterday about Yahoo's switch to PHP and the performance problems they had with remember.yahoo.com (?). That was there first big project using PHP, MySQL, Apache. At first, MySQL got crushed by the load. They added something like 20 more MySQL slaves which then got things working under heavy load. I see a link for this, and looked at it briefly. However it sounds like your resources has more info. Would you still have the link? Their hind site analysis was interesting. A big part of the reason MySQL "failed" was poor database design and lack of indexes on key fields. Join fields were not indexed! DB design is depend on the application. Indexes we can do though, and have. Thanks for this info. Info which is useful to build a case. benji --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How much data can MySQL push out?
Just a silly question: are you pushing those 12.5 Mbytes/s over the network ? If this is the case you have hit the limit of Fast Ethernet (12.5x8 = 100Mbits/s) and no database (not even MySQL ;) ) will be faster ! May be an upgrade to Gigabit Ethernet would help... yes, 12.5 MBytes/s over the network. We know this is the max of the network. We will be teaming the two nics on the box on Monday which will give us 25 Mbytes/s. That will eliminate one possible problem. However, can MySQL (or MSSQL) push out 25 MBytes/s? (we will hit this limit in February with the current setup). Can MySQL (or MSSQL) push out 125 MBytes/s (1 GBit/s)? At what point do we reach the Application (or OS?) limit? thanks. benji --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How much data can MySQL push out?
Thanks to those who responded to me and the list (I am on the digest list) why would you not simply generate the HTML and store it as files on the HD that the web server can then serve without database interaction. I've seen this done when the web pages were quasi-static. (Did not change with every query but changed when something on the server triggered a change) Similar to your option 2 but without the extra database. (and lose the MSSQL box and move to MySQL...had to say it to make this relevant to MySQL!) :) Thought about that one. Didn't find it as a very good solution. 1) in order to not to have to parse the files to replace file names, the file names would end up as "default.asp?id=sfsdsdfsdfs987d897&bob=asdf8sdaf8sad9f8asd" I am not sure how well Apache would like the "?" in the filename. 2) the site has a log in feature which needs dealt with. When the user logs in, they see additional content. If the files where just files on the file system, there would be no way for the login feature to actually work/require people to log in If I create a PHP script called "default.asp" (and tell Apache .asp is to be parsed as a PHP file), then I can set the ID of the file to "id=sfsdsdfsdfs987d897&bob=asdf8sdaf8sad9f8asd" and do a sql such as "select * from table where id='id=sfsdsdfsdfs987d897&bob=asdf8sdaf8sad9f8asd'" (at least at the simplest level). This does require a database though, unless I would just write the file "id=sfsdsdfsdfs987d897&bob=asdf8sdaf8sad9f8asd" out to disk outside of the webroot and just have PHP open the file from the file system. File Systems have a problem with 30K files in a single directory? thanks benji --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How much data can MySQL push out?
We are experiencing some issues with performance on a non-MySQL box and are looking for alternatives (and alternative methods). Once of the issues that we seem to be facing, is that the pure volume of data which needs to be pushed out. The other database is pushing out (at peak) 12.5 megabytes per second and is being hit with 30-45 queries per second. If we rework the application, we end up with one of two solutions: 1) move to MySQL for the database engine (it currently is MSSQL) 2) Rework the application, so that the application still talked to MSSQL, but we generate static pages (this is for a website) and store them in MySQL, which are then served. This will reduce both bandwidth and queries per second. Bandwidth is unknown, but the queries per second are estimated at 15-25 queries per second. The select statements would be very generic though (select * from table where ID='abc123') This leaves one major question. How much data can MySQL push out? Can MySQL handle 12.5 megabytes (not megabits) per second of data? Will MySQL handle 20 queries per second? I know a lot of this also determined by OS/hardware. MySQL would be running on a 2-CPU Sun box. Any information with regards to this would be of use. If anyone also has such information on MSSQL (what is the Application Limit of MSSQL) it would also be helpful. thanks benji --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
table handler errors and lost data
>Description: Users enter data from a php based web site and receive no errors or warnings of any kind that something is amiss, but the data isn't entered into the tables. When I go to the mysql command line and run commands i get a table handler error. >How-To-Repeat: this happens every once in a while and i don't know how to make it happen >Fix: what i do to fix this is to stop mysqld and run myisamchk on all the tables then restart mysqld. This works for a little bit, but the problem always reoccurs. If i upgrade to the lates version of mysql it fixes it for a few months, but every time the same thing has eventually started happening again >Submitter-Id: >Originator: >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: >Severity: <[ non-critical | serious | critical ] (one line)> >Priority: <[ low | medium | high ] (one line)> >Category: mysql >Class: <[ sw-bug | doc-bug | change-request | support ] (one line)> >Release: mysql-3.23.49 (Official MySQL binary) >Environment: System: SunOS ccac 5.7 Generic_106541-04 sun4u sparc SUNW,UltraSPARC-IIi-Engine Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.7/2.8.1/specs gcc version 2.8.1 Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1696236 Apr 19 1999 /lib/libc.a lrwxrwxrwx 1 root root 11 Mar 22 2000 /lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 bin bin 1118620 Apr 19 1999 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1696236 Apr 19 1999 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Mar 22 2000 /usr/lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 bin bin 1118620 Apr 19 1999 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared Perl: This is perl, version 5.005_03 built for sun4-solaris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication Connection: Slave -> Master?
We are looking at using replication between two MySQL servers. In our situation, there might be a firewall between the two servers and we will need to permit the traffic between the two through the firewall. From what I have read, it sounds like the Slave connects to the Master (in stead of the master to the slave, or even the slave contacting the master, and then the master establishing a connection to the slave). Is this (Slave -> Master) correct? It also seems as if the data transfer happens on port 3306 (by default). THis is also correct? thanks benji --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Got an error reading communication packets
Hello I was doing some general maintenance of the server, and ran accross the following in the MySQL err file. I did some initual searching, and came up with nothing. Can anyone shed a little light on what might be causing this? 020201 4:14:24 Aborted connection 263214 to db: 'some_database' user: 'someuser' host: `localhost' (Got an error reading communication packets) MySQL is running on a Sun Box. benji --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LIKE CLAUSE - bug
Hello, I have been successfully using the MYSQL database for the past 3+ months - however I have just encountered a bug with MYSQL that prevents my query from working. Basically I have a field in a table that describes an item, and I have written a program that allows a user to search for a word within this field. Here is a sample table structure: create TABLE my_table ( my_index int unsigned not null auto_increment, my_field varchar(256) ) The command I am issuing is as follows: select * from my_table where my_field like '%string%'; * where "string" is the value I am searching for. As previously mentioned my application has been working perfectly for the past 3 months - but to my suprise it stopped working recently. For example: select * from my_table where my_field like '%hello%'; The above query will fail, even though row 12000 contains the string "hello" within it. I did some experimentation and identified the corrupt record - I then executed a select statement searching for a record (in the same way as above) but for a record before the corruption: For example: select * from my_table where my_index < 12000 AND my_field like'%hello%'; The above query will work! Also if the corrupt record is for example record 12001 (with my_index=12001) and I invoke the following select statement: select * from my_table where my_index=12000; The select statement will work. I simply cant understand why this is the case. I did try running some of the mysql repair tools but they failed. I Also tried doing a mysqldump and reading the table back in - however I noticed taht I lost about 100 records! Finally I wrote my own MYSQL repair command, basically it looped through each record in the table (obtaining each record doing a select on the primary key) then I dumped the data to an output file. I then read the table back into the database. This works - however one record would always be lost. Its really urgent that I get this bug fixed, and I would be exceptionally greatful if there is anyone who can advise me on how to resolve this problem. I am using MYSQL version: mysql Ver 11.15 Distrib 3.23.39, for pc-linux-gnu (i686) Best regards, Spencer ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Executing a \. or source function from an API
I have a big file full of ANSI SQL and a I want a script to be able to tell Mysql to read this ANSI SQL file every few days because it updates from a third party in ANSI SQL. I wish to avoid running a little parser that just passes the SQL to the Perl-DBI module query methods, if I could just simply tell Mysql to execute the SQL script file on the file system. Any ideas? Thanks, Curtis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Novice Data Import Question
One problem might be if you are uploading it from a windows machine where the lines are terminated by '\r\n' rather than a normal unix type '\n'. I am not familiar with PHPMyAdmin so I am unsure whether or not it accounts for that in the parsing of the file. Curtis -Original Message- From: X [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 1:13 PM To: [EMAIL PROTECTED] Subject: Novice Data Import Question I am just getting my feet wet in PHP and MySQL. I'm using PHPMyAdmin to administer my databases online, and I'm having trouble importing data from a textfile over the Internet. It imports one record perfectly and then stops. It gives me no errors. I have checked access privileges, and indeed I have full privileges. I would appreciate any help I can get. Thanks, Rookie Jay Witherspoon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA INFILE Question
I have a text file with around 25 fields but I only want 5 of them. They are not the first 5 fields that I want. Is there a way to skip fields using LOAD DATA INFILE so I don't have to build a 25 field table and then cut it down? Thanks, Curtis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
M$ SQL Export to Mysql Help
I have a nicely sized database of about 40 records, which I have in MS SQL but I need to move it to my production server, which is Mysql. I was going to do it this way: Export MS SQL database into a file and then use LOAD DATA INFILE But I was wondering if there were a way to export into SQL and then just use \. Database.sql to import it very quickly. I am worried that the exporter will mess up the file because it is so large. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Design Question
I was wondering what would be a better database design for long term performance growth. Design A: Data Mapping Definitions Three Tables: Corporation ID 1 --> many Corporation ID Corporation Data Code ID 1 --> 1 Code ID Code Definition Each of these corporations has many code definitions and they are searched by the code definition. Select Definitions.definition from Data, Mapping, Definitions where Data.ID = Mapping.ID and Mapping.CodeID = Definitions.CodeID and Definitions.Definition LIKE "%Searchable Definition%"; Hence it looks like three table joins. Design B: Because each company has this list of Definitions, would it be better to put these definitions using a programming language into a TEXT field called definitions so that one could make a table like this, the TEXT field would be a comma delimited list of Definitions. Data Corporation ID Corporation Data Definitions TEXT And the select would like this: Select * from data where Definitions LIKE "%Searchable Definition%"; Any ideas. A company has a maximum of 10-15 definitions usually just one or two. I know the table ceases to be relational, but could this make it faster? Also, another off topic question. When I do explain on one of these selects even with the definitions indexed it says no indices are used. Does this have to do with the LIKE operator? Thanks for any help, Curtis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA INFILE with JDBC
Is this possible with 2.0 version (type 4 I think) MM driver found on www.gjt.org? Also how do the file reading privileges for this work because I tried doing it and it gave me an authorization error even though my mysql user has FILE on it's privileges. Also, the file to read is chmod 774, so it should be readable by all. Any ideas, Thanks _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
