RE: Query problem
Hey there
I have the following table structure:
CREATE TABLE documents (
id int(11) NOT NULL auto_increment,
user varchar(50) NOT NULL default '',
olduser varchar(50) NOT NULL default '',
username varchar(100) NOT NULL default '',
uploaddate timestamp(14) NOT NULL,
docdate varchar(100) NOT NULL default '',
docno varchar(255) NOT NULL default '',
title varchar(150) NOT NULL default '',
summary varchar(255) NOT NULL default '',
content text NOT NULL,
doctype varchar(80) NOT NULL default '',
docuri varchar(255) NOT NULL default '',
vjudge varchar(100) NOT NULL default '',
vexpert varchar(100) NOT NULL default '',
vspeciality varchar(150) NOT NULL default '',
didiversity varchar(100) NOT NULL default '',
dicity varchar(80) NOT NULL default '',
didiversitybar varchar(80) NOT NULL default '',
dilawfirm varchar(200) NOT NULL default '',
jstate varchar(100) NOT NULL default '',
jdistrict varchar(100) NOT NULL default '',
jappellate varchar(100) NOT NULL default '',
keywords varchar(255) NOT NULL default '',
PRIMARY KEY (id),
FULLTEXT KEY content (content)
) TYPE=MyISAM;
I run the following type of query against it:
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND jstate = 'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California'
For some reason even when doctype is not equal to Motion it still
returns these documents. Can someone please let me know where I am going
wrong?
The most important thing about the query is that first only documents
that match the doctype should be returned so I suppose before even
bothering to check the rest of the query only those documents should be
found. How do I go about ensuring that only documents that matches the
doctype is returned and no other documents.
Thanks for any help on this.
--
Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Development.Multimedia.Branding
emotionalize.conceptualize.visualize.realize
Tel: +27125468436
Fax: +27125468436
email:[EMAIL PROTECTED]
web: www.volume4.co.za
This message contains information that is considered to be sensitive or confidential
and may not be forwarded or disclosed to any other party without the permission of the
sender. If you received this message in error, please notify me immediately so that I
can correct and delete the original email. Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is my server not tuned properly ?
Context: MySQL 4.0.18 on redhat linux 2.4.20-28.8smp kernel. Usage scenario: Around 10-20 users daily (not necessarily concurrent) I have collected some System variables over 2x periods. Period #1: 7 days uptime | Select_full_join | 1967 | | Select_full_range_join | 0 | | Select_range | 1 | | Select_range_check | 0 | | Select_scan | 47074 | | Slow_queries | 106| | Sort_merge_passes| 0 | | Sort_range | 0 | | Sort_rows| 151760 | | Sort_scan| 7341 | Period #2: 18 hours uptime after a reboot - | Select_full_join | 182 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 4216 | | Slow_queries | 18| | Sort_merge_passes| 0 | | Sort_range | 0 | | Sort_rows| 24016 | | Sort_scan| 696 | Basically I would like to zoom into the variables: Select_full_join, Select_scan, Sort_scan. By reading the docs @ mysql.com, I can infer that my queries/sorts are performing a lot of full table scans. I know that's bad. But I want to know from experienced people if my numbers above indicate normal or excessively horrible performance. Thank you Xanana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Deletes / Swap Problem
Hi, We have just recently moved 1 of our InnoDB cache servers to a new much bigger machine, 4 CPU, 8GB Ram and masses amount of disk space available from a SAN. The OS is Redhat AS 3 with kernel 2.4. MySQL is the only application on this machine and its the latest stable release of 4.0.x. Throughout the day most of the queries are inserts and selects and the machine runs no problem with the mysql sitting at about 1.6Gb in memory. At night we run an expiry job where we clean our expired records from the cache, there are a few million records removed. The job runs for about 4/5 hours but for some reason the machine starts to use swapspace. I dont understand why the machine would use it. The MySQL process never goes above 2GB RAM in memory so where does all the other memory on the machine disappear to. Right now with the job finished these are the stats of the memory. total used free sharedbuffers cached Mem: 79982967978396 19900 0 1582045913696 -/+ buffers/cache:19064966091800 Swap: 419291216962962496616 There is 6GB sitting free in buffer and 1.6GB used of swap. As I understand (correct me if I'm wrong), Linux should free memory from these buffers when needed by something else. Why should the machine be using swap at all if there is plently of memory elsewhere that can be used ?? Any help on this would be great. Marvin Wright Flights Developer Lastminute.com [EMAIL PROTECTED] +44 (0) 207 802 4543 This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Query problem
Sorry guys, I forgot to post to the list as well..
-Original Message-
From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED]
Sent: Friday, 6 August 2004 5:42 PM
To: [EMAIL PROTECTED]
Subject: RE: Query problem
Hi Schalk,
I recommend using parenthesis to group your AND/OR logic more firmly.
Assuming you want all the fields for documents that have content matching
'demyer Padgham robinson' with a document type of 'Motion' that refer to
either the California State court OR the district Circuit Court: Federal,
California.
Example:
SELECT *
FROM documents
WHERE
MATCH (content) AGAINST ('demyer Padgham robinson')
AND doctype = 'Motion'
AND ( jstate = 'California: State Court'
OR
jdistrict = 'Circuit Court: Federal, California' );
It is also good practise to specify precisely the fields you are interested
in rather than just *. It may be easier to write the query this way, but
it's going to slow everything down for you.
As an aside, looking at your table structure, depending on your database
size you may want to consider normalisation. For more info check out:
http://www.databasejournal.com/sqletc/article.php/1428511
Also, it might be interesting for you to know that fixed width tables are a
lot faster than dynamic or variable width ones (like the one you've
provided).. For more info you can check out:
http://dev.mysql.com/doc/mysql/en/MyISAM_table_formats.html
I hope this helps,
Regards,
Lachlan
-Original Message-
From: Schalk Neethling [mailto:[EMAIL PROTECTED]
Sent: Friday, 6 August 2004 5:19 PM
To: [EMAIL PROTECTED]
Subject: RE: Query problem
Hey there
I have the following table structure:
CREATE TABLE documents (
id int(11) NOT NULL auto_increment,
user varchar(50) NOT NULL default '',
olduser varchar(50) NOT NULL default '',
username varchar(100) NOT NULL default '',
uploaddate timestamp(14) NOT NULL,
docdate varchar(100) NOT NULL default '',
docno varchar(255) NOT NULL default '',
title varchar(150) NOT NULL default '',
summary varchar(255) NOT NULL default '',
content text NOT NULL,
doctype varchar(80) NOT NULL default '',
docuri varchar(255) NOT NULL default '',
vjudge varchar(100) NOT NULL default '',
vexpert varchar(100) NOT NULL default '',
vspeciality varchar(150) NOT NULL default '',
didiversity varchar(100) NOT NULL default '',
dicity varchar(80) NOT NULL default '',
didiversitybar varchar(80) NOT NULL default '',
dilawfirm varchar(200) NOT NULL default '',
jstate varchar(100) NOT NULL default '',
jdistrict varchar(100) NOT NULL default '',
jappellate varchar(100) NOT NULL default '',
keywords varchar(255) NOT NULL default '',
PRIMARY KEY (id),
FULLTEXT KEY content (content)
) TYPE=MyISAM;
I run the following type of query against it:
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND jstate = 'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California'
For some reason even when doctype is not equal to Motion it still
returns these documents. Can someone please let me know where I am going
wrong?
The most important thing about the query is that first only documents
that match the doctype should be returned so I suppose before even
bothering to check the rest of the query only those documents should be
found. How do I go about ensuring that only documents that matches the
doctype is returned and no other documents.
Thanks for any help on this.
--
Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Development.Multimedia.Branding
emotionalize.conceptualize.visualize.realize
Tel: +27125468436
Fax: +27125468436
email:[EMAIL PROTECTED]
web: www.volume4.co.za
This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in error,
please notify me immediately so that I can correct and delete the original
email. Thank you.
--
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]
FW: InnoDB Deletes / Swap Problem
Sorry guys, I forgot to post to the list as well.. -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 6:56 PM To: Marvin Wright Subject: RE: InnoDB Deletes / Swap Problem Marvin, Does the job run locally on this machine with MySQL?.. If so.. How is your job run? Is it a bash script, pure SQL, PHP, or..? Maybe your script is consuming the additional memory? Just a thought,.. Regards, Lachlan -Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 6:22 PM To: [EMAIL PROTECTED] Subject: InnoDB Deletes / Swap Problem Hi, We have just recently moved 1 of our InnoDB cache servers to a new much bigger machine, 4 CPU, 8GB Ram and masses amount of disk space available from a SAN. The OS is Redhat AS 3 with kernel 2.4. MySQL is the only application on this machine and its the latest stable release of 4.0.x. Throughout the day most of the queries are inserts and selects and the machine runs no problem with the mysql sitting at about 1.6Gb in memory. At night we run an expiry job where we clean our expired records from the cache, there are a few million records removed. The job runs for about 4/5 hours but for some reason the machine starts to use swapspace. I dont understand why the machine would use it. The MySQL process never goes above 2GB RAM in memory so where does all the other memory on the machine disappear to. Right now with the job finished these are the stats of the memory. total used free sharedbuffers cached Mem: 79982967978396 19900 0 1582045913696 -/+ buffers/cache:19064966091800 Swap: 419291216962962496616 There is 6GB sitting free in buffer and 1.6GB used of swap. As I understand (correct me if I'm wrong), Linux should free memory from these buffers when needed by something else. Why should the machine be using swap at all if there is plently of memory elsewhere that can be used ?? Any help on this would be great. Marvin Wright Flights Developer Lastminute.com [EMAIL PROTECTED] +44 (0) 207 802 4543 This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- 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]
FW: Question
Sorry guys, I forgot to post to the list as well.. -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 9:27 AM To: [EMAIL PROTECTED] Subject: RE: Question Hi there, Can you please provide a sample of a query that is not working? Also if you could, provide a dump of the relavent table structures. Regards, Lachlan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 9:06 AM To: [EMAIL PROTECTED] Subject: Question Dear friends, I am using mysql 4.0.17-nt with php 4.3.3 In mysql tables, I can write to columns of tables with varchar, int etc etc datatypes, however text datatype columns I am not able to write. Any guidance, please. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Table query and column overlap
Sorry guys, I forgot to post to the list as well.. -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 4:12 PM To: [EMAIL PROTECTED] Subject: RE: Table query and column overlap Hi again, To fix this wrapping you can use the \G command, this will display the output vertically instead of horizontally in columns as your paste below.. To use this you replace the semicolon character ';' with a '\G' Example: SELECT * FROM myTable\G Note: If you are expecting more than a few rows you will want to make sure you use a LIMIT on your query otherwise you are going to get a LOT of text. Other than the \G command you could either select less fields in your select (ie. specify only the columns you need instead of *) or make your terminal window wider.. unfortunately the command prompt program (cmd.exe) that comes with most of the modern Windows OS' will not allow you to change the width of the screen. To combat this you can redirect the output of the mysql command line client into a file and view it in a program of your choice. You can do this by using the 'tee' or '\T' command.. This will append all queries sent to the server and their output to an output file. Use this by : \T filename Example: \T c:/mydirectory/outputfile.txt or tee outputfile.txt To turn off this functionality use 'notee' or '\t'. If you want the structure of the table, which is what I suspect you are really looking for here, you will need to do what is called a describe. This is done by: DESC tablename; By doing a select *, you are returning the entirety of the table's data,.. while it will let you know how many fields there are and their names, it will not tell you more detailed information like the data types, if there are indexes, etc. For more information on retrieving information on tables in this way check out: http://dev.mysql.com/doc/mysql/en/DESCRIBE.html To help us in answering your previous question, a copy of the output of a describe on the table involved will be helpful. Kind Regards, Lachlan Mulcahy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 1:16 PM To: [EMAIL PROTECTED] Subject: Table query and column overlap Dear Friends, I am using mysql 4.0.17-nt I have pasted structure of table below, while managing through command prompt using sql without GUI. Once I use without GUI, via command prompt select * from quiz to see contents of full table in each column I get overlap of columns as pasted below, how do I fix it. Any guidance, please. - sql select * from quiz; ---++-+- -- ++-+-+---+ id | q | question| opt1 | opt2 | opt3| answer | activated | ---++-+- -- ++-+-+---+ 1 | 1 | Which drug is the treatment of choice in hypertension? | Loop dirur ics | omperazole | heparin | Loop diruretics | 1 | 2 | q2 | | || | | 1 | 3 || | || | | 1 | ---++-+- -- ++-+-+---+ rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Is my server not tuned properly ?
Sorry guys, I forgot to post to the list as well.. -Original Message- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 6:45 PM To: Xanana Gusmao Subject: RE: Is my server not tuned properly ? Hi Xanana, The variables you are looking at, while giving you some of a picture of your performance aren't enough to definitively say how bad or good things are. What I would suggest having a look at... What is your long_query_time set to? You can check this by: SHOW VARIABLES LIKE 'long_query_time'; This is going to give more meaning to your slow_queries count.. Also, go and read your slow query log file.. If you are not already logging slow queries, I recommend you do.. This will give you an idea of precisely which queries are running slowly. This in turn will allow you to identify where you might be able to create indices where there where none before. http://dev.mysql.com/doc/mysql/en/Slow_query_log.html There really is no 'normal' for these numbers as by themselves they don't give the big picture on performance and applications vary so greatly. The important question really is: Is your database performing well enough for your requirements? If the answer is no, then I would suggest looking into optimisation. A good place to start is as I mentioned above with your slowest queries. They'll help weed out the 'weakest links' in your query performance initially... A good place to start with optimisation is here: http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html I know there is a LOT of content there but it really does have great information.. If you have any other questions, feel free to shoot them to the list.. Hope this helps, Regards, Lachlan -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Xanana Gusmao Sent: Friday, 6 August 2004 5:26 PM To: [EMAIL PROTECTED] Subject: Is my server not tuned properly ? Context: MySQL 4.0.18 on redhat linux 2.4.20-28.8smp kernel. Usage scenario: Around 10-20 users daily (not necessarily concurrent) I have collected some System variables over 2x periods. Period #1: 7 days uptime | Select_full_join | 1967 | | Select_full_range_join | 0 | | Select_range | 1 | | Select_range_check | 0 | | Select_scan | 47074 | | Slow_queries | 106| | Sort_merge_passes| 0 | | Sort_range | 0 | | Sort_rows| 151760 | | Sort_scan| 7341 | Period #2: 18 hours uptime after a reboot - | Select_full_join | 182 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 4216 | | Slow_queries | 18| | Sort_merge_passes| 0 | | Sort_range | 0 | | Sort_rows| 24016 | | Sort_scan| 696 | Basically I would like to zoom into the variables: Select_full_join, Select_scan, Sort_scan. By reading the docs @ mysql.com, I can infer that my queries/sorts are performing a lot of full table scans. I know that's bad. But I want to know from experienced people if my numbers above indicate normal or excessively horrible performance. Thank you Xanana -- 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]
very large HEAP-tables in 4.1.3
I was just wondering if anybody has been using very large HEAP-tables and if there are ways to have mysql use the memory more efficient: (I have no experience with all heap-tables but using them as temporary tables...) I just started testing with 2 heap-tables on a development-system (p4 3.2ghz, 2GB RAM) to get an idea of what's possible and what's not: I think mysql uses way too much memory (overhead) to store my data. I've fed the database with realtime-data for 1 hour now and I need at least 30 times as much in the tables. (200 times would be perfect - of course on some other machine with more memory) Right now top tells me that mysql is using around 10% of the memory. I already increased max_heap_table_size and I will have to increase it much more, but right now it seems that I would need 6GB of RAM to get my minimum amount of data in those tables. Which means I'd need a 64bit-system. But can mysql deal with 6GB-HEAP-tables??? So has anybody tried something like this, yet? We are currently using our own, self-written databases for this, but we are thinking about either using a sql-database in order to be able to make joins to other tables or adding features to our own software (64bit-support, sql-like interface etc.). If it works with mysql, we'd probably prefer that since its much less work for us and easier to handle. It doesn't matter if mysql uses a little more memory, but right now it seems like mysql is wasting way too much memory :( thanks for any help! Jan | max_heap_table_size | 49744 | mysql show table status\G *** 1. row *** Name: geldbrief Engine: HEAP Version: 9 Row_format: Fixed Rows: 2449755 Avg_row_length: 61 Data_length: 157468096 Max_data_length: 60634 Index_length: 19690688 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=2000 Comment: *** 2. row *** Name: umsaetze Engine: HEAP Version: 9 Row_format: Fixed Rows: 236425 Avg_row_length: 45 Data_length: 11402880 Max_data_length: 535713975 Index_length: 1942648 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=2000 Comment: 2 rows in set (0.00 sec) CREATE TABLE `geldbrief` ( `symbol` char(12) NOT NULL default '', `quelle` int(10) unsigned NOT NULL default '0', `kurszeit` datetime NOT NULL default '-00-00 00:00:00', `ticknumber` int(10) unsigned NOT NULL default '0', `bid` double(16,4) default NULL, `bidsize` double(16,4) default NULL, `ask` double(16,4) default NULL, `asksize` double(16,4) default NULL, PRIMARY KEY (`symbol`,`quelle`,`kurszeit`,`ticknumber`) ) ENGINE=HEAP DEFAULT CHARSET=latin1 MAX_ROWS=2000 CREATE TABLE `umsaetze` ( `symbol` char(12) NOT NULL default '', `quelle` int(10) unsigned NOT NULL default '0', `kurszeit` datetime NOT NULL default '-00-00 00:00:00', `ticknumber` int(10) unsigned NOT NULL default '0', `kurs` double(16,4) default NULL, `umsatz` double(16,4) default NULL, PRIMARY KEY (`symbol`,`quelle`,`kurszeit`,`ticknumber`) ) ENGINE=HEAP DEFAULT CHARSET=latin1 MAX_ROWS=2000 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very large HEAP-tables in 4.1.3
Maybe you should try to normalize your table, 'symbol' could have its own table, that would reduce data and index. And then try to reduce the size of your rows, bidsize and asksize should be in integer I think. Maybe 'float' would be enough. What represents the 'quelle' column ? Is kurszeit necessary in your primary key ? CREATE TABLE `geldbrief` ( `symbol` char(12) NOT NULL default '', `quelle` int(10) unsigned NOT NULL default '0', `kurszeit` datetime NOT NULL default '-00-00 00:00:00', `ticknumber` int(10) unsigned NOT NULL default '0', `bid` double(16,4) default NULL, `bidsize` double(16,4) default NULL, `ask` double(16,4) default NULL, `asksize` double(16,4) default NULL, PRIMARY KEY (`symbol`,`quelle`,`kurszeit`,`ticknumber`) ) ENGINE=HEAP DEFAULT CHARSET=latin1 MAX_ROWS=2000 CREATE TABLE `umsaetze` ( `symbol` char(12) NOT NULL default '', `quelle` int(10) unsigned NOT NULL default '0', `kurszeit` datetime NOT NULL default '-00-00 00:00:00', `ticknumber` int(10) unsigned NOT NULL default '0', `kurs` double(16,4) default NULL, `umsatz` double(16,4) default NULL, PRIMARY KEY (`symbol`,`quelle`,`kurszeit`,`ticknumber`) ) ENGINE=HEAP DEFAULT CHARSET=latin1 MAX_ROWS=2000 -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
keep losing login rights with MySQL
I have installed 4.0.20a binary on my own PC to work with MySQL. I take my PC home with me. The only thing I change at home is my IP address as my home n/w is different from my work n/w IP range and I have a VPN link between the office home. Since using this version (.17 was there before) everytime I change the IP addresses of the machine I have difficulty in getting access to the MySQL server with admin rights. I set a user password but if I try and login as root with the password using mysql -uroot -p I get the error ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) If I do not use the password with the command mysql I can login. It tells me that I have logged in with [EMAIL PROTECTED] but I cannot see any of the tables that I could before. I also can't use or view the mysql table. I tried using the MySQL Administrator program. Again I have to login without the password.. The window comes up . If I click on User Administration I get Could not fetch User names MySQL Error Nr 1044 access denied for user 'root@ localhost' to database mysql The only thing changed (done 3 days ago) was to Service Contol-Configure Service add Support for InnoDB and Named Pipes which changed the ImagePath entry from mysqld-nt to mysql-max-nt Is there anything I can do to check the integrity of the mysql database to see if it has corrupted? I have not long started looking at this so there are no backups just my play area. I have a copy on a second machine but I don't just want to copy directory structures over because I presume I need to do more than that. Any help to get my administration rights back gratefully recieved. Kerry
Microsoft's ASP/SQL combo
Do anyone have an ASP website that acquires info from SQL Database? I would like to see an sample file to see how it acquires info from SQL Database for my future Database project that I'm currently researching on Thanks in advance, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
results with utf8 characters
Hi, sorry, I must ask again, because there was no answer to this problem until now. I've installed mysql 4.1.3 as rpm package. If SELECT results columns with unicode characters (multibyte characters like german umlauts), the shown frame in my client is defective: mysql select * from uml; +---++ | ascii | utf8 | +---++ | Text | äöü | | szet | ß | +---++ It seems, that counting of necessary blanks to justify the trailing '|' is wrong. The stored values in such columns are correct: mysql select ascii,hex(utf8) from uml; +---+--+ | ascii | hex(utf8)| +---+--+ | Text | C3A4C3B6C3BC | | szet | C39F | +---+--+ Any ideas? Wolfgang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very large HEAP-tables in 4.1.3
Philippe Poelvoorde wrote: Maybe you should try to normalize your table, 'symbol' could have its own table, that would reduce data and index. And then try to reduce the size of your rows, bidsize and asksize should be in integer I think. Maybe 'float' would be enough. What represents the 'quelle' column ? Is kurszeit necessary in your primary key ? I changed a few columns, bidsize and asksize are integer now, and i changed ticknumber to smallint unsigned. At first I used the ticknumbers by the feedserver, now I count up to 65,000 and then reset the counter back to 0. I need that additional column to handle multiple ticks within one second. now I have a row_length of 41 instead of 61 on the geldbrief-table, but there is still just way to much memory-usage quelle is the stock-exchange (source); That table should store trades and bid/asks of stock-exchanges, so the primary key has to include: symbol ( i.e. IBM) quelle (numeric code for the stock-exchange) date and time ticknumber (in order be able to handle multiple ticks per second) any more suggestions? Maybe I'll test how a InnoDB-table with a huge innodb_buffer_pool_size will work. But since I'll have to do big delete's once every hour (kick old records) I have no idea if that would work out on a table with much more than 100,000,000 rows and insert coming in all the time... Another idea is to use a bunch of myisam-tables (4 or more for each day) and a merge-table. I could then do a flush tables with write lock;truncate table big_merge_table;unlock tables; on the myisam-tables to delete the old rows. I don't think that the disc-based table engines can respond quick enough and handle all the inserts at the same time... but I might give it a try next week. Does anybody have comments on those two ideas in case my in-memory-concept doesn't work.. thanks Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where are the Foreign Keys?
Hi, as far as i know, there is only 1 way to extract the foreign key relations from a table: show create table tablename. Are there any others? regards ralf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where are the Foreign Keys?
Hi Ralf, as far as i know, there is only 1 way to extract the foreign key relations from a table: show create table tablename. Are there any others? The comments column in SHOW TABLE STATUS is supposed to list the FKs as well. A rather silly way of doing things, IMO, cause you can't use the comments yourself anymore. Besides, the comments field is waaay too short for a larger number of FKs. So, as far as I know, SHOW CREATE TABLE is the only way. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table query and column overlap
You need to change the buffer size of your, for lack of a better term, DOS prompt window. RIGHT-click on the title bar of your window and click on properties. On the properties page for my platform (win2k) I have a Layout tab. On that tab are two size settings, Screen Buffer Size and Window Size. You need to change your Screen Buffer Size to be something with a width greater than the output of most of the queries you run. In my case I run 1024 (width) x 2048 (height) which allocates a whopping 2MB buffer (oh--my goodness!!! not two whole megabytes!! 8-D ) You get 1024 horizontal columns so you won't see that wrapping nearly as often. You also get 2048 lines of output buffer as well. I can run most queries and not need to worry about them scrolling out of the top of my buffer. Of course, you can always change those numbers to fit _your_ circumstances. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 08/05/2004 11:15:35 PM: Dear Friends, I am using mysql 4.0.17-nt I have pasted structure of table below, while managing through command prompt using sql without GUI. Once I use without GUI, via command prompt select * from quiz to see contents of full table in each column I get overlap of columns as pasted below, how do I fix it. Any guidance, please. - sql select * from quiz; ---++-+--- ++-+-+---+ id | q | question| opt1 | opt2 | opt3| answer | activated | ---++-+--- ++-+-+---+ 1 | 1 | Which drug is the treatment of choice in hypertension? | Loop dirur ics | omperazole | heparin | Loop diruretics | 1 | 2 | q2 | | || | | 1 | 3 || | || | | 1 | ---++-+--- ++-+-+---+ rows in set (0.00 sec)
Re: Query problem
Schalk Neethling wrote:
I run the following type of query against it:
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND jstate =
'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California'
SELECT * FROM documents WHERE MATCH (content) AGAINST
('demyer Padgham robinson') AND doctype = 'Motion' AND ( jstate =
'California:
State Court' OR jdistrict = 'Circuit Court: Federal, California' )
For some reason even when doctype is not equal to Motion it still
returns these documents. Can someone please let me know where I am
going wrong?
The most important thing about the query is that first only documents
that match the doctype should be returned so I suppose before even
bothering to check the rest of the query only those documents should
be found. How do I go about ensuring that only documents that matches
the doctype is returned and no other documents.
Thanks for any help on this.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where are the Foreign Keys?
You could also try SHOW KEYS FROM dbname.tablename Should work. Naresh -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 14:06 To: [EMAIL PROTECTED] Subject: Re: Where are the Foreign Keys? Hi Ralf, as far as i know, there is only 1 way to extract the foreign key relations from a table: show create table tablename. Are there any others? The comments column in SHOW TABLE STATUS is supposed to list the FKs as well. A rather silly way of doing things, IMO, cause you can't use the comments yourself anymore. Besides, the comments field is waaay too short for a larger number of FKs. So, as far as I know, SHOW CREATE TABLE is the only way. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the originator of the message. This footer also confirms that this email message has been scanned for the presence of computer viruses. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of DA Group. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
standard processing of metadata
as far as i know, there is only 1 way to extract the foreign key relations from a table: show create table tablename. Are there any others? MT The comments column in SHOW TABLE STATUS is supposed MT to list the FKs as well. since SQL89 standard lot of things got standartized but still there are more things to unify. One of them is metadata (system tables that a given DBMS uses) processing. I want to ask if there is going to be any standard on that ? Namely any unified way (for different DBMSs) to extract and modify metadata about the user database - primary keys, foreign keys, info on cascade updates/deletes settings, default values, ranges of allowed values and so on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count() within Join
I think what you need is a pivot table (aka: cross tab report): SELECT c.id , c.campaign_name , count(1) as total , SUM(if(a.status='optin',1,0)) as optin , SUM(if(a.status='optout',1,0)) as optout FROM addresses as a INNER JOIN addresses_incampaign as i on a.email_address = i.email_address INNER JOIN campaigns as c on i.campaign_id = c.id WHERE a.user_id = 1 AND i.user_id = 1 GROUP BY c.id, c.campaign_name ORDER BY c.id Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Haneda [EMAIL PROTECTED] wrote on 08/06/2004 01:19:21 AM: Mysql 4 SELECT c.campaign_name, count(*), c.id FROM addresses as a INNER JOIN addresses_incampaign as i on (a.email_address = i.email_address) INNER JOIN campaigns as c on (i.campaign_id = c.id) WHERE a.user_id = 1 AND i.user_id = 1 GROUP BY c.id ORDER BY c.id This gives me FooNmae 100 BarName 250 FUD name127 Within the addresses table is a field called status, it is a enum and can be 'optin' or 'optout'. I need to also show how many there are of each of those as well. I have looked at count(a.status) but that just returns the total numbers listed above, count(DISTINCT a.status='optin') gives me 2 for some reason. Suggestions? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]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: Table query and column overlap
Shawn, Thanks for posting about the command window sizes. It's been something that I have frustratingly accepted in silence for a while now (especially having a 20 inch monitor at work).. Hooray!! I can finally get some horizontal real estate!! ..obviously the concept of two whole megabytes of buffer also gets me a little weak at the knees too ;) Regards, Lachlan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, 6 August 2004 11:14 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Table query and column overlap You need to change the buffer size of your, for lack of a better term, DOS prompt window. RIGHT-click on the title bar of your window and click on properties. On the properties page for my platform (win2k) I have a Layout tab. On that tab are two size settings, Screen Buffer Size and Window Size. You need to change your Screen Buffer Size to be something with a width greater than the output of most of the queries you run. In my case I run 1024 (width) x 2048 (height) which allocates a whopping 2MB buffer (oh--my goodness!!! not two whole megabytes!! 8-D ) You get 1024 horizontal columns so you won't see that wrapping nearly as often. You also get 2048 lines of output buffer as well. I can run most queries and not need to worry about them scrolling out of the top of my buffer. Of course, you can always change those numbers to fit _your_ circumstances. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 08/05/2004 11:15:35 PM: Dear Friends, I am using mysql 4.0.17-nt I have pasted structure of table below, while managing through command prompt using sql without GUI. Once I use without GUI, via command prompt select * from quiz to see contents of full table in each column I get overlap of columns as pasted below, how do I fix it. Any guidance, please. - sql select * from quiz; ---++-+- -- ++-+-+---+ id | q | question| opt1 | opt2 | opt3| answer | activated | ---++-+- -- ++-+-+---+ 1 | 1 | Which drug is the treatment of choice in hypertension? | Loop dirur ics | omperazole | heparin | Loop diruretics | 1 | 2 | q2 | | || | | 1 | 3 || | || | | 1 | ---++-+- -- ++-+-+---+ rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very large HEAP-tables in 4.1.3
Hi, I changed a few columns, bidsize and asksize are integer now, and i changed ticknumber to smallint unsigned. At first I used the ticknumbers by the feedserver, now I count up to 65,000 and then reset the counter back to 0. I need that additional column to handle multiple ticks within one second. now I have a row_length of 41 instead of 61 on the geldbrief-table, but there is still just way to much memory-usage quelle is the stock-exchange (source); So normally a contract is traded on a principal exchange, not two, I would eventually suggest doing that : CREATE TABLE instrument ( id integer NOT NULL auto_increment, symbol varchar(12) NOT NULL default '', quelle int(10) NOT NULL default 0, PRIMARY KEY(id) ); that would save you around 9 bytes per records. (13 - foreign key) since normally stock are quoted in integer, you could event with a multiplier go for an integer instead of double in your bid/ask, that would save you 4 extra bytes (that's what we do on our side). By using an extra table 'instrument', your primary key will be really smaller and you would be able to use less memory. (try primary key(instrument_id,kurszeit), droping event the ticknumber, it will drop dramatically the memory usage for the index). I don't think that the disc-based table engines can respond quick enough and handle all the inserts at the same time... but I might give it a try next week. or one table per symbol maybe ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Microsoft's ASP/SQL combo
Scott, I think you posted to the wrong group ;-). This is the group for MySQL not MS SQL Server (two totally different products)! You can find MANY examples for making ASP connections from the MSDN site (that's where I got all of mine the first few times I needed to make them work). http://msdn.microsoft.com/ Use the search features. It will probably take you two or three tries to figure out exactly _how_ to ask for what you need. IMHO, MS has done a crappy job of keeping their information organized and available. There are several different libraries you can use to connect your ASP site (BTW, you never said if you were using Jscript, VBScript, PERL, PHP, somekinda-dot-net-language...) to an MS SQL Server database (or to several other database for that matter) IF you are trying to connect to a MySQL database... that's a different story (but you still didn't say which language you were using) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Hamm [EMAIL PROTECTED] wrote on 08/06/2004 07:11:49 AM: Do anyone have an ASP website that acquires info from SQL Database? I would like to see an sample file to see how it acquires info from SQL Database for my future Database project that I'm currently researching on Thanks in advance, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Microsoft's ASP/SQL combo
Sorry, Connecting M$ ASP to MySQL is what I meant. (just noticed the misunderstanding statement in subject line. My apologies) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 06, 2004 9:46 AM To: Scott Hamm Cc: 'Mysql ' (E-mail) Subject: Re: Microsoft's ASP/SQL combo Scott, I think you posted to the wrong group ;-). This is the group for MySQL not MS SQL Server (two totally different products)! You can find MANY examples for making ASP connections from the MSDN site (that's where I got all of mine the first few times I needed to make them work). http://msdn.microsoft.com/ Use the search features. It will probably take you two or three tries to figure out exactly _how_ to ask for what you need. IMHO, MS has done a crappy job of keeping their information organized and available. There are several different libraries you can use to connect your ASP site (BTW, you never said if you were using Jscript, VBScript, PERL, PHP, somekinda-dot-net-language...) to an MS SQL Server database (or to several other database for that matter) IF you are trying to connect to a MySQL database... that's a different story (but you still didn't say which language you were using) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Hamm [EMAIL PROTECTED] wrote on 08/06/2004 07:11:49 AM: Do anyone have an ASP website that acquires info from SQL Database? I would like to see an sample file to see how it acquires info from SQL Database for my future Database project that I'm currently researching on Thanks in advance, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving a database
Good morning, What's the best way to move a database and all of its tables from one server to another? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: standard processing of metadata
On Fri, 6 Aug 2004 16:26:53 +0300, DebugasRu [EMAIL PROTECTED] wrote: since SQL89 standard lot of things got standartized but still there are more things to unify. One of them is metadata (system tables that a given DBMS uses) processing. I want to ask if there is going to be any standard on that ? Namely any unified way (for different DBMSs) to extract and modify metadata about the user database - primary keys, foreign keys, info on cascade updates/deletes settings, default values, ranges of allowed values and so on. That standard already exists, MySQL just doesn't implement it. The SQL standard defines form and function of a so-called INFORMATION_SCHEMA in which various views reside that contain said information. For instance, primary keys and foreign keys would be identified by the CONSTRAINT_TYPE field of the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view. Actions on delete and update would be in the UPDATE_RULE and DELETE_RULE fields of the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view. Etc. etc. etc. If you are interested you can probably find a lot more documentation in the PostgreSQL manual or you can buy a copy of ISO/IEC 9075-11:2003. But don't hold your breath, judging by the roadmap none of it will work in MySQL for quite some years. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where are the Foreign Keys?
From: Naresh Sadhnani You could also try SHOW KEYS FROM dbname.tablename Should work. According to the documentation, this is an alternative for SHOW INDEX FROM tablename A quick try gave me no results for FKs, only PKs and indices. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table query and column overlap
Thanks for posting about the command window sizes. It's been something that I have frustratingly accepted in silence for a while now (especially having a 20 inch monitor at work).. Hooray!! I can finally get some horizontal real estate!! +1 ! I think this should be documented somewhere ! I am sure there is a lot of people out there thinking that it is some kind of command-prompt limitation. Thanks a lot ! Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Microsoft's ASP/SQL combo
What I have here is a basic template of creating and using a DSN-less
connection from an ASP page (using VBScript) through ADO through an ODBC
driver into a MySQL database. I would refer you to the ADO documentation
if you need more specifics about other options (like setting the cursor
location (server, client), recordset types (static, dynamic, keyset, etc),
recordset locking (read-only, batch-optimistic, etc.), retrieving the
results, etc) This script, as written, should be executed on the server
but could also run from a client running MSIE (assuming they also have the
ODBC driver and the networking ability to reach the server) or from a
system script (on either machine). If you do run this as a system script
you will need to change Server.CreateObject to WScript.CreateObject as
it is executed by a different engine (not by ASP.DLL but WSCRIPT. EXE or
CSCRIPT.EXE, depending on how it's called).
I am cross-posting this to the Win32 list in hopes that it may be helpful
there, too.
Option Explicit
Dim oConn,sConn
Dim rsData, sSQL
set oConn = Server.CreateObject(ADODB.Connection)
set rsData = Server.CreateObject(ADODB.Recordset)
sConn = Driver={MySQL ODBC 3.51
Driver};SERVER=127.0.0.1;DATABASE=your_database_name;UID=a_valid_MySQL_account;PASSWORD=a_valid_password
oConn.open sConn
sSQL = SELECT list_of_some_fields FROM some_table(s) WHERE
something_to_be_true
rsData.open sSQL, oConn
You may need to adjust the DRIVER={} phrase to match what you have on your
system. The SERVER= can take either an IP address (faster) or a DNS name
(sometimes more flexible). (I always use IP addresses whenever I can.)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Hamm [EMAIL PROTECTED] wrote on 08/06/2004 09:51:00 AM:
Sorry, Connecting M$ ASP to MySQL is what I meant. (just noticed the
misunderstanding statement in subject line. My apologies)
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, August 06, 2004 9:46 AM
To: Scott Hamm
Cc: 'Mysql ' (E-mail)
Subject: Re: Microsoft's ASP/SQL combo
Scott,
I think you posted to the wrong group ;-). This is the group for
MySQL not MS SQL Server (two totally different products)!
You can find MANY examples for making ASP connections from the MSDN
site (that's where I got all of mine the first few times I needed to
make them work).
http://msdn.microsoft.com/
Use the search features. It will probably take you two or three
tries to figure out exactly _how_ to ask for what you need. IMHO, MS
has done a crappy job of keeping their information organized and
available. There are several different libraries you can use to
connect your ASP site (BTW, you never said if you were using
Jscript, VBScript, PERL, PHP, somekinda-dot-net-language...) to an
MS SQL Server database (or to several other database for that matter)
IF you are trying to connect to a MySQL database... that's a
different story (but you still didn't say which language you were using)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Hamm [EMAIL PROTECTED] wrote on 08/06/2004 07:11:49 AM:
Do anyone have an ASP website that acquires info from SQL Database? I
would
like to see an sample file to see how it acquires info from SQL
Database for
my future Database project that I'm currently researching on
Thanks in advance,
Scott
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: very large HEAP-tables in 4.1.3
Philippe Poelvoorde wrote: Hi, I changed a few columns, bidsize and asksize are integer now, and i changed ticknumber to smallint unsigned. At first I used the ticknumbers by the feedserver, now I count up to 65,000 and then reset the counter back to 0. I need that additional column to handle multiple ticks within one second. now I have a row_length of 41 instead of 61 on the geldbrief-table, but there is still just way to much memory-usage quelle is the stock-exchange (source); So normally a contract is traded on a principal exchange, not two, I would eventually suggest doing that : Hi Philippe, That might be right if you only watch one country, but we currently have 35 international exchanges. So we don't have just one major exchange for IBM but we have NYSE, LSE, Xetra (german) etc. It looks like we'll try something disc-based and have a memory-database only with ask- and asksize for the important exchanges. We want to get rid of our old solution and it seems like the massive amount of data just doesn't fit into memory with mysql because of the overhead mysql has. The idea of introducing a numeric code instead of the char(12)-symbols and have a translation-table might be interesting. It makes everything a little less comfortable but saves a few bytes... I could split the data in individual tables for each exchange... It would save another 2 bytes for the exchange-ID... but the applications will have to choose the right table... I guess we'll have some discussion on that here in the company next week. I don't think that the disc-based table engines can respond quick enough and handle all the inserts at the same time... but I might give it a try next week. or one table per symbol maybe ? ehmmm.. 30 tables? not a good idea ;) I'd split it in tables by the time since that makes cleaning it up much easier (truncate table is much faster than delete from table where datefielddate_sub(now(),interval 5 day)) thanks for your help! Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error while working with MySQL MyODBC and MS Access 97
hi their, i have a problem working with access mysql and myodbc. i ported a access database to mysql. now i had to integrate the mysql db in the old access frontend. i realized this with linked tables. now i have a problem, because myodbc seems to return the wrong results. the relation between the tables in mysql are fixed with the primary keys and the foreign keys. relations: observation // tablename observation_id // primary key ... activity_no_focal // tablename activity_no_focal_id // primary key observation_id // foreign key Activity1 Activity2 Activity3 Activity4 now their is a formular in access that allows me to create a new entry for the observation. this works fine. at this formular their is a button 'activity_no_focal'. pressing this button, a new formular pops up, and now their should be the possibility to enter data for the set activity_no_focal with observation_id like the new set i formerly created. now it is following problem i ran in: the formular opens, but all the fields for entering the new data do not exist. as i looked in the mysql log file, i recognized, that their is a select statement for this specific observation_id at activity_no_focal, and for sure, it is empty, as it should be. if access gets an empty set for a table, the fields access then presents are then used for updating the db. This is the reason why i think this should be a myodbc error. i guess myodbs gives back some strange data, access can't handle with, instead of the empty set, or the empty set is given back in a way access doesn't understand. So this is my question: does one know how to handle the problem? In advance, the technical data of the soft and hardware i use. Hardware: pentium 4 512 MB RAM 40 GB Hard Disk. Operating System: linux 2.4.20-4GB (Suse 9.1) Software under linux: VmWare (running win2k) Mysql Server running on linux Server mysql 3.23.55-Max-log apache, shh, mail ... Software under win2k: Access 97 MyODBC 3.51 All Service Packs installed (for Access i installed the latest Service Release) MDAC 2.8 thanks for every idea, joshua bacher joshua bacher max planck institute -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving a database
-Original Message- From: David Barron [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 14:56 To: [EMAIL PROTECTED] Subject: Moving a database Good morning, What's the best way to move a database and all of its tables from one server to another? Thanks You can use the mysqldump utility to dump the database you want to move. Then use the dump file to recreate the database in the mysql instance running on your other server. If you are using myisam tables and are able to shutdown your servers you can copy all the files in the data/database_name directory into a directory of the same name in the data directory of your new server. Be sure to copy all the files (.MYD, .MYI, .frm) Regards Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving a database
How do I recreate the database on the new server? Do I have to recreate each table again or will the dump file contain that information? -Original Message- From: Mark Pittam [mailto:[EMAIL PROTECTED] Sent: Friday, August 06, 2004 12:06 PM To: David Barron; [EMAIL PROTECTED] Subject: RE: Moving a database -Original Message- From: David Barron [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 14:56 To: [EMAIL PROTECTED] Subject: Moving a database Good morning, What's the best way to move a database and all of its tables from one server to another? Thanks You can use the mysqldump utility to dump the database you want to move. Then use the dump file to recreate the database in the mysql instance running on your other server. If you are using myisam tables and are able to shutdown your servers you can copy all the files in the data/database_name directory into a directory of the same name in the data directory of your new server. Be sure to copy all the files (.MYD, .MYI, .frm) Regards Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving a database
Don't forget to copy the my.cnf file, and make any changes neccesary (due to different directory/disk structures). If you are using InnoDB, and can shut down the database, you should just be able to move the files in data/database name or var/database name like Mark said below (InnoDB stores files there as well). Also, in the my.cnf, look to see if any datafiles are specified. If so, you need to move those files, plus the log files to the new server. David Mark Pittam wrote: -Original Message- From: David Barron [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 14:56 To: [EMAIL PROTECTED] Subject: Moving a database Good morning, What's the best way to move a database and all of its tables from one server to another? Thanks You can use the mysqldump utility to dump the database you want to move. Then use the dump file to recreate the database in the mysql instance running on your other server. If you are using myisam tables and are able to shutdown your servers you can copy all the files in the data/database_name directory into a directory of the same name in the data directory of your new server. Be sure to copy all the files (.MYD, .MYI, .frm) Regards Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving a database
-Original Message- From: David Barron [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 17:10 To: Mark Pittam; [EMAIL PROTECTED] Subject: RE: Moving a database How do I recreate the database on the new server? Do I have to recreate each table again or will the dump file contain that information? The dump file will contain all the create table statements and the insert statements to insert the data into the tables. You will need to create the database using the create database statement and then you can redirect the dump file into the mysql client. e.g. mysql -u root -p -D database_name dumpfile Mark -Original Message- From: Mark Pittam [mailto:[EMAIL PROTECTED] Sent: Friday, August 06, 2004 12:06 PM To: David Barron; [EMAIL PROTECTED] Subject: RE: Moving a database -Original Message- From: David Barron [mailto:[EMAIL PROTECTED] Sent: 06 August 2004 14:56 To: [EMAIL PROTECTED] Subject: Moving a database Good morning, What's the best way to move a database and all of its tables from one server to another? Thanks You can use the mysqldump utility to dump the database you want to move. Then use the dump file to recreate the database in the mysql instance running on your other server. If you are using myisam tables and are able to shutdown your servers you can copy all the files in the data/database_name directory into a directory of the same name in the data directory of your new server. Be sure to copy all the files (.MYD, .MYI, .frm) Regards Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select non-matching fields
This is probably a very easy question for many of you. I have two tables, 'Purchases' and 'Purchased_Items'. For every purchase there is one entry in 'Purchases' and 1 or more entries on 'Purchased_Items' (depending on how many items were purchased'. The two tables are linked by a 'ticket_number' column (same name in both tables). Since life isn't perfect and sometimes purchases are deleted and in theory purchased_items would be deleted too but sometimes things happen and Purchased_Items are left behind after a 'Purchases' row is deleted (usually some human error). I would like to have a query that would find all Purchased_Items that DON'T have a matching 'Purchases' record. Something like: select ticket_number from purchased_items where purchased_items.ticket_number != purchases.ticket_number; Obviously the above statement doesn't give me the results I'm looking for. Thanks! Robert C. Best III - [EMAIL PROTECTED] District Technology Coordinator for N.E.R.I.C. at Potsdam Central School Phone: (315) 265-2000 x266 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very large HEAP-tables in 4.1.3
Hi, A few questions for you. On Friday, August 6, 2004, at 06:17 AM, Jan Kirchhoff wrote: I was just wondering if anybody has been using very large HEAP-tables and if there are ways to have mysql use the memory more efficient: (I have no experience with all heap-tables but using them as temporary tables...) I just started testing with 2 heap-tables on a development-system (p4 3.2ghz, 2GB RAM) to get an idea of what's possible and what's not: I think mysql uses way too much memory (overhead) to store my data. How much overhead do you think it is using? Each row is 61 bytes in geldbrief, which is *exactly* the amount needed for the datatypes you have. char(12) = 12 bytes int(10) = 4 bytes datetime = 8 bytes int(10) = 4 bytes double(16,4) = 8 bytes double(16,4) = 8 bytes double(16,4) = 8 bytes double(16,4) = 8 bytes NULL values = 1 byte --- Total 61 bytes Now if you take 61 * 2449755 (number of rows) = 149435055 bytes used 157468096 Real amount 149435055 Data size - 8033041 Overhead 5.1% Total overhead of data I don't see how you could get it to be much smaller than that. Even with zero overhead it would only be 5% smaller, which still would require *a lot* of memory to store it all. The primary key itself is only using about 8 bytes of memory per row (because it is a hashed index, btree would be much larger), which is also very compact. With your own in-memory database, do you some sort of compression algorithm? That is the only way that I could see it taking up much less space. MySQL is pretty close to as efficient as you can get without compression. I've fed the database with realtime-data for 1 hour now and I need at least 30 times as much in the tables. (200 times would be perfect - of course on some other machine with more memory) Right now top tells me that mysql is using around 10% of the memory. I already increased max_heap_table_size and I will have to increase it much more, but right now it seems that I would need 6GB of RAM to get my minimum amount of data in those tables. Which means I'd need a 64bit-system. But can mysql deal with 6GB-HEAP-tables??? So has anybody tried something like this, yet? We are currently using our own, self-written databases for this, but we are thinking about either using a sql-database in order to be able to make joins to other tables or adding features to our own software (64bit-support, sql-like interface etc.). If it works with mysql, we'd probably prefer that since its much less work for us and easier to handle. It doesn't matter if mysql uses a little more memory, but right now it seems like mysql is wasting way too much memory :( thanks for any help! Jan | max_heap_table_size | 49744 | mysql show table status\G *** 1. row *** Name: geldbrief Engine: HEAP Version: 9 Row_format: Fixed Rows: 2449755 Avg_row_length: 61 Data_length: 157468096 Max_data_length: 60634 Index_length: 19690688 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=2000 Comment: *** 2. row *** Name: umsaetze Engine: HEAP Version: 9 Row_format: Fixed Rows: 236425 Avg_row_length: 45 Data_length: 11402880 Max_data_length: 535713975 Index_length: 1942648 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=2000 Comment: 2 rows in set (0.00 sec) CREATE TABLE `geldbrief` ( `symbol` char(12) NOT NULL default '', `quelle` int(10) unsigned NOT NULL default '0', `kurszeit` datetime NOT NULL default '-00-00 00:00:00', `ticknumber` int(10) unsigned NOT NULL default '0', `bid` double(16,4) default NULL, `bidsize` double(16,4) default NULL, `ask` double(16,4) default NULL, `asksize` double(16,4) default NULL, PRIMARY KEY (`symbol`,`quelle`,`kurszeit`,`ticknumber`) ) ENGINE=HEAP DEFAULT CHARSET=latin1 MAX_ROWS=2000 CREATE TABLE `umsaetze` ( `symbol` char(12) NOT NULL default '', `quelle` int(10) unsigned NOT NULL default '0', `kurszeit` datetime NOT NULL default '-00-00 00:00:00', `ticknumber` int(10) unsigned NOT NULL default '0', `kurs` double(16,4) default NULL, `umsatz` double(16,4) default NULL, PRIMARY KEY (`symbol`,`quelle`,`kurszeit`,`ticknumber`) ) ENGINE=HEAP DEFAULT CHARSET=latin1 MAX_ROWS=2000 With all of that being said, I would just go with InnoDB, which can buffer the data in memory as well. In a later email you mention that you need to delete a lot of rows per hour. HEAP wouldn't work all that well
Re: Select non-matching fields
You need a LEFT JOIN: SELECT ticket_number FROM purchased_items LEFT JOIN purchases ON purchased_items.ticket_number = purchases.ticket_number WHERE purchases.ticket_number IS NULL; Michael Rob Best wrote: This is probably a very easy question for many of you. I have two tables, 'Purchases' and 'Purchased_Items'. For every purchase there is one entry in 'Purchases' and 1 or more entries on 'Purchased_Items' (depending on how many items were purchased'. The two tables are linked by a 'ticket_number' column (same name in both tables). Since life isn't perfect and sometimes purchases are deleted and in theory purchased_items would be deleted too but sometimes things happen and Purchased_Items are left behind after a 'Purchases' row is deleted (usually some human error). I would like to have a query that would find all Purchased_Items that DON'T have a matching 'Purchases' record. Something like: select ticket_number from purchased_items where purchased_items.ticket_number != purchases.ticket_number; Obviously the above statement doesn't give me the results I'm looking for. Thanks! Robert C. Best III- [EMAIL PROTECTED] District Technology Coordinator for N.E.R.I.C. at Potsdam Central School Phone: (315) 265-2000 x266 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1030 (HY000): Got error 127 from storage engine
Hi, I have done following on a 8 million row table (4GB): repair table optimize table drop one of the fulltext index optimize table again Now fulltext search stop working and I am getting: ERROR 1030 (HY000): Got error 127 from storage engine What does this mean? Index corrupted? How that happen? Any place I can get a list of MySQL error code and know what they mean? Thanks a lot Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bash Shell issue with the mysqldump's password.....
I am struggling to get the bash script to use the password. What I'm
doing here is to assign it to a variable..
--snip-
MySQLdump_FilePath=/usr/local/mysql/bin/mysqldump
UserId=root
DB_Production=DB_NAME
#Emarket_Production_Filepath=`/home/website/ProductionDB.sql`
#Prefix_Production_CmdLine=$MySQLdump_FilePath -u ${UserId}
--password='[EMAIL PROTECTED]' $DB_Production
#$Prefix_Production_CmdLine /home/website/test.sql 21
--snip-
I added the backslash-escape to the $ character 'cause of bash. The
password still doesn't work in bash script. I'm not sure if it is
because the password then become a string, along with the \ Beside
the password value, does anyone have ever successfully use the bash
script to use the password option in a string tag or in a variable tag
or something? I welcome suggestions...
FletchSOD
RE: Bash Shell issue with the mysqldump's password.....
This line should do it (no space after -p)
Prefix_Production_CmdLine=$MySQLdump_FilePath -u ${UserId} -pmy_password
$DB_Production
_
Steve Poirier
-Original Message-
From: Scott Fletcher [mailto:[EMAIL PROTECTED]
Sent: August 6, 2004 1:24 PM
To: [EMAIL PROTECTED]
Subject: Bash Shell issue with the mysqldump's password.
I am struggling to get the bash script to use the password. What I'm doing
here is to assign it to a variable..
--snip-
MySQLdump_FilePath=/usr/local/mysql/bin/mysqldump
UserId=root
DB_Production=DB_NAME
#Emarket_Production_Filepath=`/home/website/ProductionDB.sql`
#Prefix_Production_CmdLine=$MySQLdump_FilePath -u ${UserId}
--password='[EMAIL PROTECTED]' $DB_Production
#$Prefix_Production_CmdLine /home/website/test.sql 21
--snip-
I added the backslash-escape to the $ character 'cause of bash. The
password still doesn't work in bash script. I'm not sure if it is because
the password then become a string, along with the \ Beside the password
value, does anyone have ever successfully use the bash script to use the
password option in a string tag or in a variable tag or something? I
welcome suggestions...
FletchSOD
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: results with utf8 characters
I believe there is already a bug report about this. See if this is the same as your problem: http://bugs.mysql.com/bug.php?id=3453
Re: Select non-matching fields
On Fri, 06 Aug 2004 13:17:42 -0400, Michael Stassen [EMAIL PROTECTED] wrote: You need a LEFT JOIN: SELECT ticket_number FROM purchased_items LEFT JOIN purchases ON purchased_items.ticket_number = purchases.ticket_number WHERE purchases.ticket_number IS NULL; No, actually he doesn't. SELECT ticket_number FROM purchased_items WHERE ticket_number NOT IN (SELECT ticket_number FROM purchases); The above will most certainly be faster than any join, because it is only a couple projections and a simple selection over ticket_number (which is almost certainly indexed). Although, I suppose if this is only a maintenance query (I suspect it is), then it probably doesn't matter. But, the bottom line is: if you can avoid join, do it. There's only so much the query optimizer can do. -- Matt Warden Berry Neuroscience Lab Department of Psychology Miami University This email proudly and graciously contributes to entropy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export and destroy relation
From within MySQL 3? I doubt it. It's pretty easy to do in
the language of your choice, though. Here's a version in
perl:
use DBI;
my $dbh = DBI-connect('DBI:mysql:database=test', user, pass);
for (@{ $dbh-selectall_arrayref('SELECT * FROM users') }) {
my ($id, @user_data) = @{ $_ };
my $serials = SELECT serial FROM registered_serials WHERE id = $id;
print
join(\t,
@user_data,
join(',',
map { $_-[0] } @{ $dbh-selectall_arrayref($serials) })
) . \n;
}
Eamon Daly
- Original Message -
From: Scott Haneda [EMAIL PROTECTED]
To: MySql [EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 7:49 PM
Subject: Export and destroy relation
A client wants access to some data in mysql 3, it is a simple case of a
user
table and a registered_serials table, there is always one user, and there
can be many resistered serials. (One to many)
They want to somehow get this data into Excel, so I want to give them one
record per user, even though there can be many registered_serials.
The result would be something like:
Firsttablasttabemailtabserial1,serial2,serial3
Is this possible?
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Fax: 313.557.5052
[EMAIL PROTECTED]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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select non-matching fields
Matt, I humbly beg to differ. I believe that what you wrote will be executed as a correlated subquery. That would make your statement much slower than a JOIN (especially if the correct indexes exist). I believe that when the query engine executes your statement, for each row of purchased_items data it looks at, it will have to run the query SELECT ticket_number FROM purchases scan those results and determine if the current row matches. If it is in the list then it wil exclude that row from the final results. Of course, I could be very wrong and I will have learned something very valuable today. With greatest respect, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matt Warden [EMAIL PROTECTED] wrote on 08/06/2004 02:03:56 PM: On Fri, 06 Aug 2004 13:17:42 -0400, Michael Stassen [EMAIL PROTECTED] wrote: You need a LEFT JOIN: SELECT ticket_number FROM purchased_items LEFT JOIN purchases ON purchased_items.ticket_number = purchases.ticket_number WHERE purchases.ticket_number IS NULL; No, actually he doesn't. SELECT ticket_number FROM purchased_items WHERE ticket_number NOT IN (SELECT ticket_number FROM purchases); The above will most certainly be faster than any join, because it is only a couple projections and a simple selection over ticket_number (which is almost certainly indexed). Although, I suppose if this is only a maintenance query (I suspect it is), then it probably doesn't matter. But, the bottom line is: if you can avoid join, do it. There's only so much the query optimizer can do. -- Matt Warden Berry Neuroscience Lab Department of Psychology Miami University This email proudly and graciously contributes to entropy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select non-matching fields
You're making the assumption that he's using 4.1.x. He didn't state which version he's using so your solution may be of no use to him. -- T --- Matt Warden [EMAIL PROTECTED] wrote: On Fri, 06 Aug 2004 13:17:42 -0400, Michael Stassen [EMAIL PROTECTED] wrote: You need a LEFT JOIN: SELECT ticket_number FROM purchased_items LEFT JOIN purchases ON purchased_items.ticket_number = purchases.ticket_number WHERE purchases.ticket_number IS NULL; No, actually he doesn't. SELECT ticket_number FROM purchased_items WHERE ticket_number NOT IN (SELECT ticket_number FROM purchases); The above will most certainly be faster than any join, because it is only a couple projections and a simple selection over ticket_number (which is almost certainly indexed). Although, I suppose if this is only a maintenance query (I suspect it is), then it probably doesn't matter. But, the bottom line is: if you can avoid join, do it. There's only so much the query optimizer can do. -- Matt Warden Berry Neuroscience Lab Department of Psychology Miami University This email proudly and graciously contributes to entropy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 1030 (HY000): Got error 127 from storage engine
Did you try to repair it again and use myisamchk -r table.MYI Did you check your .err file (log) _ Steve Poirier -Original Message- From: Haitao Jiang [mailto:[EMAIL PROTECTED] Sent: August 6, 2004 1:21 PM To: [EMAIL PROTECTED] Subject: ERROR 1030 (HY000): Got error 127 from storage engine Hi, I have done following on a 8 million row table (4GB): repair table optimize table drop one of the fulltext index optimize table again Now fulltext search stop working and I am getting: ERROR 1030 (HY000): Got error 127 from storage engine What does this mean? Index corrupted? How that happen? Any place I can get a list of MySQL error code and know what they mean? Thanks a lot Haitao -- 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]
Customizing character processing for fulltext indexing?
Is there a way to customize the way that MySQL treats various characters when it builds a fulltext index? I am using version 4.0.18. I would like to treat a . as part of a word, rather than as a separator character. Ideally, I would like to set this for just one index of one table. I have a large table with many text fields. My UI allows users to enter a simple string which is matched against all appropriate fields in the table. Since MySQL uses at most one index per table, and doesn't use them at all for 'like' queries, this was very slow. So I built a new table with an index column and a text column, fulltext-indexed. The text column contains all of the fields from the original table concatenated together, space-separated. Retrieval is now very fast, but I cannot match against embedded URLs since the . is taken as a word separator. I suppose I could turn all of the . characters into something else on the way in, and then back on the way out, but that smells funny. Any ideas? Jeff; -- * RSS Feeds: http://www.syndic8.com * Resume: http://www.syndic8.com/~jeff/resume.html * MSN IM: [EMAIL PROTECTED] * Developer Books: http://www.developer-books.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mixing Innodb MyISAM tables
Im considering a design that mixes InnoDB and MyISAM tables. I want Innodb for speed, etc, but i have one table where i want a column to have a FULLTEXT index on. Thus the need for MyISAM. Im not worried about the performance in using the MyISAM tables, as we speak, the production version of the system is using MyISAM tables without problems. The main concern is that by using a MyISAM table, i lose foreign key support, and cannot do a cascade on delete, which i'd really like to have, and not have to manually mimic the cascade behaviour. I suppose i've gotten along fine without having foreign key support for a number of years, so this probably isnt that bad. Anyone else run into similar issues? Any thoughts? thanks much sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select non-matching fields
I believe that when the query engine executes your statement, for each row of purchased_items data it looks at, it will have to run the query SELECT ticket_number FROM purchases scan those results and determine if the current row matches. If it is in the list then it wil exclude that row from the final results. I would find it very silly if mysql's query optimizer decided that the optimized way to execute the query is to execute SELECT ticket_number FROM purchases N times, where N is the number of rows in purchased_items. There is no reason why that query would be executed any more than one time. FWIW, there is a correlated subquery version of this query (the example I gave is uncorrelated), and it would be the following: SELECT ticket_number FROM purchased_items a WHERE NOT EXISTS (SELECT * FROM purchases b WHERE a.ticket_number=b.ticket_number); I cannot say for certain that these two queries are not executed by mysql in the same manner, but I would be surprised if they were. I say this because you could alter my query and use an explicit set: SELECT ticket_number FROM purchased_items WHERE ticket_number NOT IN (112, 456, 942, 356, 623, 783); I would find it more likely that the above query is executed in the same way as my original solution. And, Emmet Bishop insightfully commented: You're making the assumption that he's using 4.1.x. He didn't state which version he's using so your solution may be of no use to him. Good point. I often forget about which features are/were unimplemented in mysql. My apologies. -- Matt Warden Berry Neuroscience Lab Miami University http://mattwarden.com This email proudly and graciously contributes to entropy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to add time to NOW() function
i want to add time to NOW() function, that means something like this, $addTime = 60; NOW() + $addTime; where 60 are seconds. I am not sure whether to add seconds or is there any other format I can use to add time to NOW() function? please let me know. thanks in advance. deepak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to add time to NOW() function
Your options depend heavily on what version of MySQL you are running. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Deepak Dhake [EMAIL PROTECTED] wrote on 08/06/2004 04:28:20 PM: i want to add time to NOW() function, that means something like this, $addTime = 60; NOW() + $addTime; where 60 are seconds. I am not sure whether to add seconds or is there any other format I can use to add time to NOW() function? please let me know. thanks in advance. deepak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to add time to NOW() function
Use DATE_ADD(NOW(),INTERVAL 1 HOUR) Or DATE_ADD(NOW(),INTERVAL 60 MINUTE) Or DATE_ADD(NOW(),INTERVAL 3600 SECOND) -Original Message- From: Deepak Dhake [mailto:[EMAIL PROTECTED] Sent: Saturday, August 07, 2004 1:58 AM To: [EMAIL PROTECTED] Subject: how to add time to NOW() function i want to add time to NOW() function, that means something like this, $addTime = 60; NOW() + $addTime; where 60 are seconds. I am not sure whether to add seconds or is there any other format I can use to add time to NOW() function? please let me know. thanks in advance. deepak -- 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: Insert problems with InnoDB (big table)
Harrison wrote: Hi, sorry about the long delay in the reply. I will be away for the next 2 weeks, but I will follow this thread if anything new comes up. Hi, A few more ideas you can try: 1. SET UNIQUE_CHECKS=0; You have a unique key that is quite large (model, id name). If you know the data is already unique (ie. importing from another data source), then this can speed up the import *a lot*. We tried that a while back (with the original big table import) but is was not in our test programs. So we added it and it doesn't make a difference (once you have reach the problem). 2. SET FOREIGN_KEY_CHECKS=0; You didn't mention if this table has any foreign keys, but again, since you are importing from a consistent source, no need to check these on insertion. Same thing for this, we tried it originally with the real data, but it was not in our test programs. Same thing happened with this one, no change in behavior. 3. Make innodb_buffer_pool_size as large as possible without swapping. Most of the heavy lifting will go on in here. I know you said you did 50-80%, if you are only doing this load on the server, you might want to increase it temporarily to even larger, assuming no swap. Also feel free to set innodb_max_dirty_pages_pct to 100. We never actually tried to set innodb_max_dirty_pages_pct=100, but it did not changed anything. 4. I notice that id isn't an auto_increment, is this value inserted in an ordered manner? Keep in mind the primary key for InnoDB is a clustered index. If you are inserting randomly ordered data it will take a bit more effort to maintain than normal. Yes, the values are ordered. Both in the original import and in the test programs. 5. Make your innodb log files as large as the innodb_buffer_pool_size that you set above. We had the innodb log files smaller (about 25% of total memory). Making them as large as innodb_buffer_pool did not make a difference either. Hope that helps some, good luck. Thanks a lot, you gave me a few things we did not try, so our testing is getting more thorough. Luc Regards, Harrison On Tuesday, August 3, 2004, at 10:23 AM, Luc Charland wrote: We are evaluating the replacement of a Sybase database with MySQL. The databases are 60+GB, containing more than 100 tables. Since we need transactions, that implies InnoDB. We were happy with the early results, but we hit a major roadblock when trying to import the biggest table (20+GB, with 4 indexes). We have reproduced the problem with a simpler table on many different servers and MySQL versions (4.X). At first, we easily insert 1600+ lines per second. As the number of lines grows, the performance deteriorate (which I can understand), but it eventually gets so slow that the import would take weeks. Doing a vmstat on the server shows that after a certain limit is reached (index bigger than the total mem ?), mysqld starts reading as much as writing, and the CPU usage goes down as the I/O eventually reach the maximum for the server. If you wait long enough, you get less than 50 lines per second (which is 30+ times slower than the first few million inserts). We have done the same tests on Sybase and another database on the same machines and have not seen this behavior, so it is not hardware related. We have done the same import in a MyISAM table and have not see any slowdown (the whole data was imported very fast, even if we had to wait a very long time --5+ hours-- for the index to rebuild after). We have tried to transform the MyISAM table into a InnoDB (same problem occurs). We have tried to import from the MyISAM table into an empty InnoDB, same problem occurs. SETUP: We have of course changed the following innodb_buffer_pool_size= (50% to 80% of total ram) innodb_log_file_size=(20% to 40% of total ram) we have tried different innodb_flush_method we have tried innodb_flush_log_at_trx_commit (0, 1) we have tried ibdata1:1G:autoextend, and also make it big enough so that all the data will fit without autoextending. we have tried creating the indexes after instead of before the inserts, but like the documentation says, it is not better. Is there an upper limit to the size of the indexes of a single table in InnoDB? Anybody else has seen this kind of slowdown for big InnoDB tables? Here is a small table that reproduce the problem (if you make 5 to 15 million inserts). We wrote a few programs (one in C++, one in Python) that generates random data and insert into the database. __ create table smallest ( id int primary key, name varchar(80), model char(20) , description varchar(255), lastupdate date, price decimal(8,2), cost decimal(8,2)) type=innodb create unique index smallcomplex on smalltest (model, id, name) create index smallprice on smalltest (price) create index smallcost on smalltest (cost) create index smallname on smalltest (name)
Re: using mysql in commercial software
Greetings All, LinuxWorld is now finished (meaning that some of the key MySQL people who need to be in on these discussions will be back to a more normal working setup) and the weekend is more or less here. I hope to pick up discussions on these issues with the other MySQLers on Monday (or to word it differently, I hope to avoid talking about licensing on my weekend! ;) Also, this discussion is a better fit for the community list ([EMAIL PROTECTED]). There are already a group of people interested in the licensing issues on that list. Unless people feel strongly, I will move this issue to that list. I urge those with an interest in our licensing and other community issues to subscribe to the community list. Those who are subscribed to our community list but not the MySQL general list, see http://lists.mysql.com/mysql/169680 to catch up on the discussion thread this message refers to. Also, those of you with an interest in licensing might also be interested in the licensing forum at http://forums.mysql.com/list.php?4 - there are additional discussions there on a variety of licensing issues. Have a good weekend! -- Zak Greant MySQL AB Community Advocate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seqment fault using a 3.23.59 client with a 4.0.16 server.
I have a program that uses the C API linked statically with the mysqlclient library. Something in the MySQL client version 3.23.59 is causing a problem with memory. Subsequent calls to malloc fail with a seqment fault. My program works if: A) The server is 3.23.59. or B) My program doesn't access one of the MySQL tables before doing the malloc. Has anyone seen a problem like this? I have yet to try linking my program with a 4.0 mysqlclient library. John Wythe
Server will only start manually when logged in as root with 4.1.3
I'm unable to get the 4.1.3 server to start automatically at startup or even manually unless I'm logged in as root. I'm on an iBook G4. I downloaded and installed the mysql-standard-4.1.3-beta-apple-darwin7.4.0-powerpc binary and the associated startup item. I've done this successfully with 4.0.20-standard (to which I've reverted). I've followed all of the instructions for changing ownership, group, and permissions to the letter. The only way I can get the server to start is to su to root and invoke mysqld_safe. Could this be a bug with the current 4.1.3 Mac OS X binary distribution? One thing that's weird is that there is no libexec folder underneath the mysql folder, either for 4.0.20 or 4.1.3. What's up with that? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
At 11:24 AM 8/2/2004, you wrote: On Mon, 2 Aug 2004 01:35:44 -0700, Jeremy Zawodny [EMAIL PROTECTED] wrote: On Mon, Jul 26, 2004 at 01:26:15PM -0500, gerald_clark wrote: Steve Richter wrote: exactly! Is Linux distributed under the same type of license as MySql? If I sell software that runs on linux I dont have to give away my code, right? To use my software you first have to install no charge Linux and MySql. Why would that not be permitted? Because the MySQL license does not allow you to use it free with commercial software that requires MySQL. If you are running commercial software that requires MySQL you must buy a license. And this is where the confusion start. MySQL is covered by the GPL. So is Linux. As far as the server goes, sure. However there is a key difference in that APIs such as glibc on Linux are licensed under the LGPL. The mysql client libraries used to be the same way, then they changed them to be under the GPL. So don't use the MySQL client libraries.g This means that, according to the most common interpretation of the GPL, just linking with them automatically requires your code be under the GPL. License: The GPL license is contagious in the sense that when a program is linked to a GPL program, all the source code for all the parts of the resulting product must also be released under the GPL. So what if the client app doesn't require the MySql client libraries in order to access the MySQL database? My client application requires no dll's at all, because everything is compiled inside an .exe file. That's because I'm using a 3rd party set of components that bypasses the libmysql*.dll libraries altogether. My application doesn't link to MySQL's code, GPL or otherwise. Does this now make my application license free even if I distribute it for $$$? Or will the software police come banging on my door at 4 AM looking for me? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Uninstalling MySQL
Greetings... How do I uninstall MySQL under linux (i have Fedora Core 2)... i will be installing a new version of it... I have 3.23 currently and will replace it with 4. Please Help Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstalling MySQL
On Fri, 2004-08-06 at 20:49 -0400, [EMAIL PROTECTED] wrote: Greetings... How do I uninstall MySQL under linux (i have Fedora Core 2)... i will be installing a new version of it... I have 3.23 currently and will replace it with 4. Please Help Thanks You dont necessarily have to uninstall it, you COULD do an rpm upgrade if this was installed via RPM. 1) to remove, rpm -e package-name 2) to add, rpm -Ivh package-name 3) to update, rpm -Uvh package-name just watch for dependencies. HTH Michael Weiner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What would you store in a BLOB field?
I know the blob field is binary but what would you store there? and if you could give me an example of real-life uses please.
Finding Database and Tables
I have mysql installed on a pc running Windows 98. I recently had to reformat a partitioned c drive. My tables were stored in a database called samp_db. When I give the command use samp_db I get Error 1044: Access denied for user: '@localhost' to database 'samp_db' When I give the command create database samp_db I get the same error message, and I get the same when I try to create a database with another name. How do I create the database (and what is this error)? Can I get the tables back under samp_db (I saved all mysql files to the d drive)? Thanks in advance. Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would you store in a BLOB field?
In the last episode (Aug 06), Levi Campbell said: I know the blob field is binary but what would you store there? and if you could give me an example of real-life uses please. Say you want to have multiple remote webservers all serving the same data. Create a table with filename, mtime, and content fields and replicate it to a mysql database on each server. The content field would be a blob. You could also add custom HTML fields, like Content-Type: and Expires:. You could have an employee table, with their photo in a blob field. You could implement your own full-text index by creating a table next to a table of documents, with a word field, and a blob field containing a compressed bitmap of documents containing that word. Searches would be done by pulling the bitmaps for each search word and AND/OR'ing them (I have done this; it works well). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would you store in a BLOB field?
Another use for binary columns is for case-sensitive text such as passwords. If you store text in a CHAR, VARCHAR, or TEXT column, comparisons will not be case-sensitive unless you use the BINARY keyword. An example (from section 6.3.22 of the Language Reference: mysql SELECT a = A; - 1 mysql SELECT BINARY a = A; - 0 On Aug 6, 2004, at 7:10 PM, Dan Nelson wrote: In the last episode (Aug 06), Levi Campbell said: I know the blob field is binary but what would you store there? and if you could give me an example of real-life uses please. Say you want to have multiple remote webservers all serving the same data. Create a table with filename, mtime, and content fields and replicate it to a mysql database on each server. The content field would be a blob. You could also add custom HTML fields, like Content-Type: and Expires:. You could have an employee table, with their photo in a blob field. You could implement your own full-text index by creating a table next to a table of documents, with a word field, and a blob field containing a compressed bitmap of documents containing that word. Searches would be done by pulling the bitmaps for each search word and AND/OR'ing them (I have done this; it works well). -- Dan Nelson [EMAIL PROTECTED] -- 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: What would you store in a BLOB field?
I'm not sure if anyone has mentioned this - I only see part of this thread - but music is something else you can store in a BLOB. As an experiment, I stored a photograph (in JPEG format) and a music file (in MIDI format), each in their own BLOB columns and then fetched them out again to display them on a web page. This worked fine and is a technique I will use again in the future. Rhino - Original Message - From: Daniel Lahey [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Friday, August 06, 2004 11:03 PM Subject: Re: What would you store in a BLOB field? Another use for binary columns is for case-sensitive text such as passwords. If you store text in a CHAR, VARCHAR, or TEXT column, comparisons will not be case-sensitive unless you use the BINARY keyword. An example (from section 6.3.22 of the Language Reference: mysql SELECT a = A; - 1 mysql SELECT BINARY a = A; - 0 On Aug 6, 2004, at 7:10 PM, Dan Nelson wrote: In the last episode (Aug 06), Levi Campbell said: I know the blob field is binary but what would you store there? and if you could give me an example of real-life uses please. Say you want to have multiple remote webservers all serving the same data. Create a table with filename, mtime, and content fields and replicate it to a mysql database on each server. The content field would be a blob. You could also add custom HTML fields, like Content-Type: and Expires:. You could have an employee table, with their photo in a blob field. You could implement your own full-text index by creating a table next to a table of documents, with a word field, and a blob field containing a compressed bitmap of documents containing that word. Searches would be done by pulling the bitmaps for each search word and AND/OR'ing them (I have done this; it works well). -- Dan Nelson [EMAIL PROTECTED] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would you store in a BLOB field?
Another use of BLOB fields would be to store things like fingerprints or retina scans, which could be very useful if you have a security-oriented table that needs to store biometric data. I should note that I have never actually stored biometric data or worked with it but I saw an overhead once that said it could be done. I have no idea what file format biometrics would use or how you would get them from the fingerprint/retina scanner into the database though. Rhino - Original Message - From: Daniel Lahey [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Friday, August 06, 2004 11:03 PM Subject: Re: What would you store in a BLOB field? Another use for binary columns is for case-sensitive text such as passwords. If you store text in a CHAR, VARCHAR, or TEXT column, comparisons will not be case-sensitive unless you use the BINARY keyword. An example (from section 6.3.22 of the Language Reference: mysql SELECT a = A; - 1 mysql SELECT BINARY a = A; - 0 On Aug 6, 2004, at 7:10 PM, Dan Nelson wrote: In the last episode (Aug 06), Levi Campbell said: I know the blob field is binary but what would you store there? and if you could give me an example of real-life uses please. Say you want to have multiple remote webservers all serving the same data. Create a table with filename, mtime, and content fields and replicate it to a mysql database on each server. The content field would be a blob. You could also add custom HTML fields, like Content-Type: and Expires:. You could have an employee table, with their photo in a blob field. You could implement your own full-text index by creating a table next to a table of documents, with a word field, and a blob field containing a compressed bitmap of documents containing that word. Searches would be done by pulling the bitmaps for each search word and AND/OR'ing them (I have done this; it works well). -- Dan Nelson [EMAIL PROTECTED] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using mysql in commercial software
Hi Mike, On Aug 06, 2004, at 18:08, mos wrote: This means that, according to the most common interpretation of the GPL, just linking with them automatically requires your code be under the GPL. License: The GPL license is contagious in the sense that when a program is linked to a GPL program, all the source code for all the parts of the resulting product must also be released under the GPL. One important note here: The GPL does not behave in the way described above. There is no requirement to distribute software that you build with or on top of GPL-licensed software to others. However, if you choose to distribute software that is a derivative work (defined in US copyright law as A ''derivative work'' is a work based upon one or more preexisting works) of GPL-licensed software, then the software can only be distributed under the GPL. So what if the client app doesn't require the MySql client libraries in order to access the MySQL database? My client application requires no dll's at all, because everything is compiled inside an .exe file. That's because I'm using a 3rd party set of components that bypasses the libmysql*.dll libraries altogether. My application doesn't link to MySQL's code, GPL or otherwise. Does this now make my application license free even if I distribute it for $$$? Or will the software police come banging on my door at 4 AM looking for me? This architecture might bypass the requirements of the GPL - I don't really know. The best course of action here is to consult a lawyer who is an expert in Free Software/Open Source software licensing who can advise you of the validity of your course of action for your given situation. We would probably assert that the software forms a derivative work with GPL-licensed MySQL because the software would likely not function without MySQL. However, this is a tough area to speculate on. As we are not lawyers (nor are we in the business of giving free legal advice to help people circumvent our own business model :), we always recommend that proprietary applications (aka applications that do not share their source code and the rights to modify it with others) should always use the proprietarily licensed version of MySQL. This recommendation ensures that our licensing terms are never violated and helps us generate revenue to fund development of the database. Usually, people who distribute proprietary applications are selling them. We feel that it is reasonable to charge users who wish to charge their users and who do not give their users the freedom to view, modify and share the source of the application. We also suggest that people consider putting their software under a Free Software/Open Source license (such as the GPL, the BSD license, the Apache license and so on). Then they can use MySQL for free. This model may not work for everyone, but there is still significant potential for revenue with the model by selling the application at a fee that the market finds reasonable, along with related services like hosting, support, consulting, etc. People may also want to consider using a dual-licensing model that allows them to share with others who choose to use Open Source/Free Software licenses, but gives them a revenue stream from people who prefer traditional proprietary licenses. Cheers! -- Zak Greant MySQL AB Community Advocate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
