Re: phpMyadmin
Quick one... is this program just for Linux? I think not. As long as you have mysql and apache/php running it can run on any unix os i think. Take out the word 'unix' and you'll be right. If you can get a PHP environment (doesn't necessarily require Apache to be the web server) and MySQL, myPHPAdmin will work. Peter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql update
i want to update every row in the mysql table that matches a part of each row in it. ie. for example if a table contains the following rows : Pid:21577 PPid: 21174 Uid:501 Gid:501 i want to change the first row to Pid:30209 similarly for any row plz advice me Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Find entry with max date.
Hi I have a relational db with a couple of tables holding things like comments and complaints and actions. In the main table, I have the contact details. What I'd like to do is to query the db with a php app and to display the entries in the result set with a last action/comment/contact done one max date. ie. main_table: id namesurname date 1 johndoe 2003-07-07 2 janedoe 2003-07-08 contact_table main_id date 1 2003-07-09 1 2003-07-12 2 2003-07-09 complaints_table main_id date 1 2003-07-10 2 2003-07-11 actions_table main_id action date 1 action1 2003-07-13 Now, querying the db for all the entries in main, i'd like to return something like: NameSurname Last action/comment/complaint JohnDoe action:action1 2003-07-13 JaneDoe complaint: 2003-07-11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table crash problem
hi listers i've upgraded my mysql env from 3.23.53 to 4.0.13. also, i changed os from m$ to rh 7.3. what did not change was h/ware. since the upgrade, i often get the error message table is marked as crashed and should be repaired. (errno 145). i then run myisamcheck --auto-repair to fix the problem but this is a reactive. what switches can i set on mysqld to trace the cause of this problem? to handle to symptom, i've set myisam_recover_option=backup,force in my.cnf this morning so hopefully the problem does not re-occur, though i'm sure sure if this will work tks _ ___ _ __ /______/ / ___/ / \ // / / / // / / ^ v / / / / /__/ / / / \/ / / /_ / /__ / /_ / /_ / Cell: 083 440 2213 Ph: 021 980 4969
RE: Rows into Columns
In the following statement replace STRING by
concat(group_concat(payment ORDER BY paymentid SEPARATOR '|'),'|')
SELECT
invoiceid,
if ( STRING IS NOT NULL,
substring(STRING, 1,
if( locate('|',STRING) 0,
locate('|',STRING)-1,
length(STRING)
)
),
NULL) pay1,
if ( STRING IS NOT NULL,
if ( substring_index(STRING,'|',2) IS NOT NULL,
substring_index(substring_index(STRING,'|',2),-1),
NULL
),
NULL) pay2,
if ( STRING IS NOT NULL,
if ( substring_index(STRING,'|',3) IS NOT NULL,
substring_index(substring_index(STRING,'|',3),-1),
NULL
),
NULL) pay3,
if ( STRING IS NOT NULL,
if ( substring_index(STRING,'|',4) IS NOT NULL,
substring_index(substring_index(STRING,'|',4),-1),
NULL
),
NULL) pay4,
if ( STRING IS NOT NULL,
if ( substring_index(STRING,'|',5) IS NOT NULL,
substring_index(substring_index(STRING,'|',5),-1),
NULL
),
NULL) pay5,
if ( STRING IS NOT NULL,
f ( substring_index(STRING,'|',6) IS NOT NULL,
substring_index(substring_index(STRING,'|',6),-1),
NULL
),
NULL) pay6
FROM ...
I did not run this vs a DB so please excuse syntax errors and if I
forgot some brackets. But in principle it should work fine.
Cheers
/rudy
-Original Message-
From: Shazia Fazili [mailto:[EMAIL PROTECTED]
Sent: maandag 14 juli 2003 19:35
To: Rudy Metzger
Subject: RE: Rows into Columns
Hi Rudy,
Thnaks for ur reply. Your solution is adding up all Payments, while I
don't want all the Payments to be summed. I want to show all Payments
against an InvoiceID. YOu see the result which I want my Query to
return.. it doesn't add up the payments
I have a table PAYMENT which has 3 fields..
PaymentID,InvoiceID,Payment
PaymentID is th eprimary key. For each INvoiceID there can be
more than
one payment but less than 6 payments.
PaymentIDInvoiceIDPayment
1123 23
2 123 45
3 123 44
4 4567 35
5 4567 67
6 234 64
Now i want a query which will return result as
InvoiceID Pay1 Pay2 Pay3 Pay4 Pay5
123 2345 440 0
4567 35 67 0 0 0
234 64 0 0 0 0
I am not adding Payments...
So what am I supposed to do now..
Cheers,
S
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: check
Alex82 [EMAIL PROTECTED] wrote: so what can i do to make a column accetp only certain values...there are any other solutions? Check values in your application. - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 1:58 PM Subject: Re: check Alex82 [EMAIL PROTECTED] wrote: i have a problem with the check option for example create table ex ( t INT check(t4) ); but even if i use check(t4) i can insert 5,6,7.all values!:( why? Ale p.s. I'm using 4.0.12 version CHECK clause does nothing. It's just parsed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: !!! NEWBIW !!! how to start? !!! NEWBIE !!!
Best method to start, restart, the server is with the service command: service mysql start service mysql stop service mysql restart Of course, this assumes that you have it configured in the init.d, which is something mysql installation normally does itself (at least with the rpm). These commands use safe_msyqld (mysqld_safe) to start the server. So you from the commandline you can best use mysqld_safe to start (under normal circumstances). Cheers /rudy Ps: there is no linux 8.0 :) think you mean RH 8.0 -Original Message- From: Eternal Designs, Inc [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 4:17 Cc: [EMAIL PROTECTED] Subject: Re: !!! NEWBIW !!! how to start? !!! NEWBIE !!! William R. Mussatto wrote: Hello everyone, Im extremaly newbie with using MySQL under Linux. Im using: mysql Ver 11.18 Distrib 3.23.51, for slackware-linux-gnu (i386) - ( btw - should i uprgade this or its enough to learn ? ) My question is: How to start mysql deamon? When i type: mysqld then apear: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) Whats wrong? Its a default installation I have a little experience with MySQL under MS Windows ( run mysqld then open MySQLadmin and thats it ), but i wish to use database under Linux. If any one can help then o would be in debt forever. -- Best regards, mailto:[EMAIL PROTECTED] Did you try typing 'ps aux | grep mysqld' ? You should get three lines (plus sometimes the grep line) if the server is running. Normally you will start a safe_mysqld rather than mysqld Alternatively you would start it with mysqladmin William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 I believe William is right. I have had the same error more than twice and this is what I did to solve it - I just can't explain why. My Linux 8.0 comes with X Window System and I use the GNOME desktop. Main Menu | Server Settings (I thing - cause I am now in Windows) | Services Then I scrolled down and put a check mark on mysql and then updated I then scrolled down to xinetd, highlighted it and then click Restart on the Services Toolbar. When I went back to the command line, I was able to launch mysql. Hope this helps!! -- Peter K. Aganyo Eternal Designs Inc., +1(617)344-8023 (fax voicemail) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql update
Prem Soman [EMAIL PROTECTED] wrote: i want to update every row in the mysql table that matches a part of each row in it. ie. for example if a table contains the following rows : Pid:21577 PPid: 21174 Uid:501 Gid:501 i want to change the first row to Pid:30209 similarly for any row Sorry, but your explanation is not clear enough for me. Use UPDATE command to change values: http://www.mysql.com/doc/en/UPDATE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs. PostgreSQL -- speed test
Hi! First of all, if I decide to benchmark MySQL vs. PostgreSQL with my application, PostgreSQL will probably be faster. That does not mean that MySQL is generally slower or that I *want* it to look slower. That just means 1) I have no experience in tuning MySQL 2) My application was built with another DBMS in mind Well, after installation and moving my MySQL dbs into PostgreSQL I decided to check if PostgreSQL is as fast as MySQL is. I was shocked... I have made several tests with simple and complicated querys - select, update, insert, drop. PostgreSQL execute those querys even 20 times slower than MySQL. On average, PostgreSQL is 2-3 times slower. Well, while 2-3 times slower looks believable, 20 times slower looks like there is something wrong with your tests. 1) Have you run ANALYZE / VACUUM ANALYZE after loading the data into Postgres? If you didn't do this, its optimizer will be unable to choose the correct query plan as it does not have real statistics. 2) Did you run with default postgresql.conf? That has *very* conservative settings for memory usage. Here is SQLite's benchmark page: http://www.sqlite.org/speed.html It boasts that the thing is 10-20 times faster than PostgreSQL, but this is with *default* configuration, while tuned PostgreSQL (there is a link on the page: http://www.sergeant.org/sqlite_vs_pgsync.html) works considerably faster. So, all people who needs trigers/views/procedures etc. have to be patient and wait for new MySQL versions. And don't you dare switching!!! :] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Find entry with max date.
SELECT c.name, c.surname, substring( if ( max(concat(cont.date,'Conctact ',cont.date) max(concat(compl.date,'Complaint ',compl.date), if (max(concat(cont.date,'Conctact ',cont.date) max(concat(act.date,' Action ',act.action,act.date), max(concat(cont.date,'Conctact ',cont.date), max(concat(act.date,' Action ',act.action,act.date) ), if (max(concat(compl.date,'Complaint ',compl.date) max(concat(act.date,' Action ',act.action,act.date), max(concat(compl.date,'Complaint ',compl.date), max(concat(act.date,' Action ',act.action,act.date) ) ) 11 ) LastActionCommentComplaint FROM contact c, contact_table cont, complaints_table compl, actions_table act WHERE c.id = cont.main_id AND c.id = compl.main_id AND c.id = act.main_id GROUP BY c.id. c.name, c.surname Did not run it vs a DB so expect some typing errors. However I hope you get the meaning. Cheers /rudy -Original Message- From: Petre Agenbag [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 9:25 To: [EMAIL PROTECTED] Subject: Find entry with max date. Hi I have a relational db with a couple of tables holding things like comments and complaints and actions. In the main table, I have the contact details. What I'd like to do is to query the db with a php app and to display the entries in the result set with a last action/comment/contact done one max date. ie. main_table: id namesurname date 1 johndoe 2003-07-07 2 janedoe 2003-07-08 contact_table main_id date 1 2003-07-09 1 2003-07-12 2 2003-07-09 complaints_table main_id date 1 2003-07-10 2 2003-07-11 actions_table main_id action date 1 action1 2003-07-13 Now, querying the db for all the entries in main, i'd like to return something like: NameSurname Last action/comment/complaint JohnDoe action:action1 2003-07-13 JaneDoe complaint: 2003-07-11 -- 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: Extracting data from SQL Server *.DB file
I've read through the archives and spent hours on Google but I still can't figure this out. I must extract the data from a SQL Server *.DB file. Viewing the raw text, I can see that there views, grants, etc. at the top of the file, but this is a process that could not possible be done by hand. I've tried using Crystal Reports and SQLyog but they don't seem to work, either. As the end result is to convert this data for use in MySQL, I'm hoping that the fine folks on here may be able to help me. Thanks a lot. The only reasonable way to get data out of a SQL Server DB file is to use SQLServer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Pulling large Microsoft SQL files into MySQL
-Original Message- From: Rudolf Bekker Sent: Monday, 14 July 2003 14:13 To: MySQL MailList ([EMAIL PROTECTED]) Subject: Pulling large Microsoft SQL files into MySQL Importance: High The MySQL manual mentions that one could specify the column and value separators and the end of file marker explicitly in the LOAD DATA statement. I'm looking for the syntax of this statement to import large data files (.txt) originally exported from Microsoft SQL 7. Example file:. Extract_Date|LAU|Customer_Number|BAN_Number|Telephone_Number|Bill_Date|Local_Usage_Credit_Amount|Local_Usage_Credit_Call_Count|Local_Usage_Credit_Call_Seconds|National_Usage_Credit_Amount|National_Usage_Credit_Call_Count|National_Usage_Credit_Call_Seconds|International_Usage_Credit_Amount|International_Usage_Credit_Call_Count|International_Usage_Credit_Call_Seconds|Cell_Usage_Credit_Amount|Cell_Usage_Credit_Call_Count|Cell_Usage_Credit_Call_Seconds|Other_Usage_Credit_Amount|Other_Usage_Credit_Call_Count|Other_Usage_Credit_Call_Seconds 20030409|63|63000233|630002330001|--|200211|0|0|0|0|0|0|0|0|0|0|0|0|-555.94|0|0 20030409|63|63000233|630002330001|--|200302|0|0|0|0|0|0|0|0|0|0|0|0|-594.24|0|0 20030409|63|63000233|630002330001|--|200303|0|0|0|0|0|0|0|0|0|0|0|0|-615.21|0|0 20030409|63|63000235|630002350001|0539480616|200201|-10.53|-14|-2346|-4.89|-6|-534|-1.3|-1|-26|-14.82|-4|-570|0|0|0 20030409|63|63000283|630002830001|0539480627|200111|-6.14|-1|-7804|0|0|0|0|0|0|0|0|0|0|0|0 20030409|63|63000283|630002830001|0539480627|200201|-140.79|-143|-101519|-68.16|-43|-9723|0|0|0|-5.68|-4|-240|0|0|0 20030409|63|63000421|630004210001|05393621802|200303|0|0|0|-128.27|-22|-4680|0|0|0|-39.65|-6|-840|0|0|0 20030409|63|63000459|630004590001|0539481533|200302|0|0|0|0|0|0|0|0|0|-.01|0|0|0|0|0 20030409|63|63000495|630004950001|0539490419|200302|0|0|0|0|0|0|0|0|0|-.02|0|0|0|0|0 20030409|63|63000519|630005190001|0539490719|200302|0|0|0|0|0|0|0|0|0|-.01|0|0|0|0|0 20030409|63|63000521|630005210001|0539823312|200111|0|0|0|-12.29|-5|-2100|-13.5|-1|-180|-264.15|-63|-17340|-34.15|-19|-6360 I need to specify the text separator as and the column separator as |. Thanx, Rudolf.
Re: alter table 'table' auto_increment = # doesn't work
Ittay, ALTER TABLE ... AUTO_INCREMENT=... does not work with InnoDB type tables. http://www.innodb.com/ibman.html#InnoDB_restrictions For an AUTO_INCREMENT column one must always define a key to the table, and that key must contain just the auto-increment column. InnoDB does not support AUTO_INCREMENT=... in a CREATE TABLE statement. This clause is used to set the first value for an auto-increment column (the default first value is 1). Workaround: insert the first row with the auto-inc column value explicitly specified. After that InnoDB starts incrementing from that value. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Ittay Freiman [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, July 15, 2003 8:44 AM Subject: alter table 'table' auto_increment = # doesn't work i cannot set auto_increment to start from anything other than 1: mysql create table test (id int unsigned not null auto_increment primary key); mysql alter table test auto_increment=2; mysql insert into test() values(); mysql select * from test; ++ | id | ++ | 1 | ++ 1 row in set (0.00 sec) please help, ittay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird Temp File Issue
Feesch ListMaster [EMAIL PROTECTED] wrote: I'm not sure if this is a bug report, possibly a security issue. I have started getting Too many connections error, but not at times when I would expect the database to be busy. The worrying thing is that the temp drive gets completely filled (about 6Gb) with a file called #SQL456.MYD, and #SQL456.MYI. (with a number where the dots are). The only way I have found to fix this is restarting the server. I'm not much of an expert in these matters, so I could really use some help! These files are temporary table files. Also MySQL server creates temporary tables for some SQL statement. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting columns into a single row
Hi guys, I need some help with this. I have the following 3 tables. +--+---+ | uid | name | +--+---+ | 100 | sue | | 102 | harry | | 104 | louis | | 107 | sam | | 110 | james | | 111 | mark | | 112 | rita | +--+---+ +--+---+ | gid | name | +--+---+ | 501 | authors | | 502 | actors| | 503 | musicians | | 504 | chefs | +--+---+ +--+--+ | uid | gid | +--+--+ | 11 | 502 | | 107 | 502 | | 100 | 503 | | 110 | 501 | | 112 | 501 | | 100 | 501 | | 102 | 501 | | 104 | 502 | | 100 | 502 | +--+--+ I'm looking for the following output: 1. Group members group name users authors sue,harry,james,mark actors ..., musicians ..., chefs NULL 2. User memberships user name groups user1 group1,group3, etc user 2 group2, group3, etc and so on Any idea how to do this? TIA, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
working with linked Tables
Database mysql running on localhost Error The additional Features for working with linked Tables have been deactivated. To find out why click here. - Database mysql running on localhost PMA Database ... not OK[ Documentation ] General relation features Disabled response from phpmyadmin 2.3.2 I did nothing but this: # create absence table for grade-keeping project DROP TABLE IF EXISTS absence; CREATE TABLE absence ( student_id INT UNSIGNED NOT NULL, date DATE NOT NULL, PRIMARY KEY (student_id, date) ); All tables and all databases has the same error. What does this mean ? Where is it documented, can it prevent nested tables or linked tables To perform properly ? Software mysql -V mysql Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686) uname -a Linux debian 2.2.17 #1 Sun Jun 25 09:24:41 EST 2000 i686 unknown Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto number primary key - restarting
Hi, is there an SQL command for making a primary key (auto number key) to start numbering from beginning after deleting all the records from the table. thanks
The sales TEAM
The sales team are down ? I'm trying to order MySQL-PRO licences and the team not return my emails... i trying with [EMAIL PROTECTED] and [EMAIL PROTECTED] gerardo is out ? this is my contact in MySQL AB : Tnks in advance, mysql,innodb,sales,money,help... - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another Newbie Question
I am finally able to enter data and am going through the Tutorial in section 3 of the mySQL manual. It suggests that I create a .txt file from which to load date into a table. Where does mySQL look for data to load in the default installation? I thought it would be in the data folder under mysql, but it can't find it. Please advise. Thanks, George -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: check
Use InnoDB tables and a foreign key. Alex82 wrote: so what can i do to make a column accetp only certain values...there are any other solutions? - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 1:58 PM Subject: Re: check Alex82 [EMAIL PROTECTED] wrote: i have a problem with the check option for example create table ex ( t INT check(t4) ); but even if i use check(t4) i can insert 5,6,7.all values!:( why? Ale p.s. I'm using 4.0.12 version CHECK clause does nothing. It's just parsed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto number primary key - restarting
delete all records by this command: TRUNCATE TABLE `mytable`; - Original Message - From: Miroslav I. [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 8:10 AM Subject: auto number primary key - restarting Hi, is there an SQL command for making a primary key (auto number key) to start numbering from beginning after deleting all the records from the table. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FileMaker Pro
I'm actually working on a large Filemaker to MySQL conversion project right now. I don't need live data at this stage, so I've got a script running that reads in a FilePro-generated CSV file each night. It works fine, though early on we had a problem with our old version of Filepro not exporting its data in an escaped format (not escaping double and single quotes, etc.) To fix this I tweaked the LOAD DATA INFILE statement we were using to look for start-of-line, end-of-line, and separator delimiters, like so: DELETE FROM my_table; LOAD DATA INFILE '/path/to/my.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' LINES STARTING BY '' TERMINATED BY '\n'; This will still break if someone embeds the quote-comma-quote (,) sequence in a string, but that isn't an issue in my case. If it is for you then take a look at the Filepro docs and change the delimiters to something less common. If your version supports it, escaping the data that Filepro dumps into its CSV files is the best solution. Also, check your Filepro manual for information on how to change Filepro's exported date format--the default is not a format that MySQL can translate. -Original Message- From: Warren Young [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 12:43 PM To: MySQL List Subject: Re: FileMaker Pro Steve Marquez wrote: Does anyone know how to export a FileMaker Pro Database so that MySQL can use it? Anyone ever do something like this? If you want a one-time transfer, the simple and cheap way is through some sort of text file; CVS or tab-delimited, for example. If you want the two databases to collaborate on a single set of data, it's best to give MySQL the canonical copy and let FileMaker manipulate it through ODBC. If the data doesn't change very often, you can use FileMaker's built-in ODBC support. Just write a script to pull a copy of the data you want, manipulate it in FileMaker, and then you can export a copy through a text file as above. If the data changes often or you need online manipulation of the data, you can add a SQL plugin to FileMaker. Do a Google search, you'll find them. There are at least two of them on the market, as I recall. They're about $100 for a single seat, with site licenses available. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Managing big tables
Hello, i've got a little problem, we're using mysql with two big tables (one has 90 Mio. Rows (60 Gb on HD), the other contains nearly 200.000.000 (130 Gb on HD). Now we want to delete some rows from these tables to free diskspace. It seems that MySQL frees the harddisk-space which was used by these rows only after optimization, which lasts very long on these tables. Both tables are dynamic in terms of row-format what seems to extend the time needed for optimization. I tried to convert the smaller one to fixed-row-format, which increased the disk-space of its data-file from 30 Gb to 60 Gb. This would not be the problem, but some SQLs which are run daily on this table now run 4 times slower than with dynamic structure. So, my questions are: 1) Did i something wrong while converting to fixed row-format ? (i found no indication) 2) Is the fixed structure really faster on optimization ? 3) Can anybody confirm the slow-down on big tables when converted from dynamic to fixed ? (on small tables fixed were faster) I'm running SuSE Linux, Kernel 2.4.20, the above behaviour could be reproduced with our productive server (MySQL 3.23) and with a test server (MySQL 4.0.12). thanks in advance, alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Another Newbie Question
George, Try in the folder with the same name as your database, under the data folder. Andy -Original Message- From: Degan, George E, JR, MGSVC [mailto:[EMAIL PROTECTED] Sent: 15 July 2003 13:30 To: [EMAIL PROTECTED] Subject: Another Newbie Question I am finally able to enter data and am going through the Tutorial in section 3 of the mySQL manual. It suggests that I create a .txt file from which to load date into a table. Where does mySQL look for data to load in the default installation? I thought it would be in the data folder under mysql, but it can't find it. Please advise. Thanks, George -- 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: auto number primary key - restarting
Miroslav I. [EMAIL PROTECTED] wrote: is there an SQL command for making a primary key (auto number key) to start numbering from beginning after deleting all the records from the table. For MyISAM tables you can use ALTER TABLE: http://www.mysql.com/doc/en/ALTER_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Managing big tables
Hi The only thing I can say is that if you optimise the table often there is less work for it to do so you table will be left locked for shorter time. I have not looked in to this but if you use the RAID option. I don't know if splitting the table up you could just work on one bit at a time?? Simon -Original Message- From: Alexander Schulz [mailto:[EMAIL PROTECTED] Sent: 15 July 2003 13:35 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Managing big tables Hello, i've got a little problem, we're using mysql with two big tables (one has 90 Mio. Rows (60 Gb on HD), the other contains nearly 200.000.000 (130 Gb on HD). Now we want to delete some rows from these tables to free diskspace. It seems that MySQL frees the harddisk-space which was used by these rows only after optimization, which lasts very long on these tables. Both tables are dynamic in terms of row-format what seems to extend the time needed for optimization. I tried to convert the smaller one to fixed-row-format, which increased the disk-space of its data-file from 30 Gb to 60 Gb. This would not be the problem, but some SQLs which are run daily on this table now run 4 times slower than with dynamic structure. So, my questions are: 1) Did i something wrong while converting to fixed row-format ? (i found no indication) 2) Is the fixed structure really faster on optimization ? 3) Can anybody confirm the slow-down on big tables when converted from dynamic to fixed ? (on small tables fixed were faster) I'm running SuSE Linux, Kernel 2.4.20, the above behaviour could be reproduced with our productive server (MySQL 3.23) and with a test server (MySQL 4.0.12). thanks in advance, alex -- 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: auto number primary key - restarting
Please check the history on this list. There are numerous answers to this problem. Cheers /rudy -Original Message- From: Miroslav I. [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 14:10 To: [EMAIL PROTECTED] Subject: auto number primary key - restarting Hi, is there an SQL command for making a primary key (auto number key) to start numbering from beginning after deleting all the records from the table. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Managing big tables
http://www.mysql.com/doc/en/Data_size.html [EMAIL PROTECTED] wrote: Hello, i've got a little problem, we're using mysql with two big tables (one has 90 Mio. Rows (60 Gb on HD), the other contains nearly 200.000.000 (130 Gb on HD). Now we want to delete some rows from these tables to free diskspace. It seems that MySQL frees the harddisk-space which was used by these rows only after optimization, which lasts very long on these tables. Both tables are dynamic in terms of row-format what seems to extend the time needed for optimization. I tried to convert the smaller one to fixed-row-format, which increased the disk-space of its data-file from 30 Gb to 60 Gb. This would not be the problem, but some SQLs which are run daily on this table now run 4 times slower than with dynamic structure. So, my questions are: 1) Did i something wrong while converting to fixed row-format ? (i found no indication) 2) Is the fixed structure really faster on optimization ? 3) Can anybody confirm the slow-down on big tables when converted from dynamic to fixed ? (on small tables fixed were faster) I'm running SuSE Linux, Kernel 2.4.20, the above behaviour could be reproduced with our productive server (MySQL 3.23) and with a test server (MySQL 4.0.12). thanks in advance, alex -- Veysel Harun Sahin [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto number primary key - restarting
Hello Miroslav, Tuesday, July 15, 2003, 3:10:10 PM, you wrote: Easiest way to do what you want is to make this: 1.SHOW CREATE TABLE TableName and copy SQL 2. DROP TABLE TableName 3. CREATE TABLE - with SQL copied in 1. MI Hi, MI is there an SQL command for making a primary key (auto number key) to start numbering from beginning after deleting all the records from the table. MI thanks -- Best regards, Krasimir_Slaveykovmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Managing big tables
I do not really think that optimizing (in your case compressing, thus cleaning up free space) is much faster with fixed record length on LARGE tables. Why? When optimizing the table the DB rebuilds the file record for record to a temporary file and then moves it back to the original file (well, this is the theory, some optimization is done of course). So the only advantage you get with fixed record length is, that it does not need to compute the record length for your records. However, this is in the milliseconds, whereas the actual write operation eats up most of the time. So by converting to fixed size you will only get a very small increase of speed, not worth mentioning. What would give you speed is - like already someone suggested - using raid0 or maybe using merge tables, which you then can optimize on demand (e.g. split your table by year, then only delete records from one your and optimize this hear only). Cheerio /rudy -Original Message- From: Alexander Schulz [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 14:35 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Managing big tables Hello, i've got a little problem, we're using mysql with two big tables (one has 90 Mio. Rows (60 Gb on HD), the other contains nearly 200.000.000 (130 Gb on HD). Now we want to delete some rows from these tables to free diskspace. It seems that MySQL frees the harddisk-space which was used by these rows only after optimization, which lasts very long on these tables. Both tables are dynamic in terms of row-format what seems to extend the time needed for optimization. I tried to convert the smaller one to fixed-row-format, which increased the disk-space of its data-file from 30 Gb to 60 Gb. This would not be the problem, but some SQLs which are run daily on this table now run 4 times slower than with dynamic structure. So, my questions are: 1) Did i something wrong while converting to fixed row-format ? (i found no indication) 2) Is the fixed structure really faster on optimization ? 3) Can anybody confirm the slow-down on big tables when converted from dynamic to fixed ? (on small tables fixed were faster) I'm running SuSE Linux, Kernel 2.4.20, the above behaviour could be reproduced with our productive server (MySQL 3.23) and with a test server (MySQL 4.0.12). thanks in advance, alex -- 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: Managing big tables
Always take care what you want to achieve! And consider the circumstances. Yes, adding a lot of indexes makes queries faster. But makes inserts/deletes/updates slower. Alex's problem is NOT that his/her queries takes too long, the problem is that optimize takes too long. Which is something completely different. Cheers /rudy -Original Message- From: Veysel Harun Sahin [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 15:22 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Managing big tables http://www.mysql.com/doc/en/Data_size.html [EMAIL PROTECTED] wrote: Hello, i've got a little problem, we're using mysql with two big tables (one has 90 Mio. Rows (60 Gb on HD), the other contains nearly 200.000.000 (130 Gb on HD). Now we want to delete some rows from these tables to free diskspace. It seems that MySQL frees the harddisk-space which was used by these rows only after optimization, which lasts very long on these tables. Both tables are dynamic in terms of row-format what seems to extend the time needed for optimization. I tried to convert the smaller one to fixed-row-format, which increased the disk-space of its data-file from 30 Gb to 60 Gb. This would not be the problem, but some SQLs which are run daily on this table now run 4 times slower than with dynamic structure. So, my questions are: 1) Did i something wrong while converting to fixed row-format ? (i found no indication) 2) Is the fixed structure really faster on optimization ? 3) Can anybody confirm the slow-down on big tables when converted from dynamic to fixed ? (on small tables fixed were faster) I'm running SuSE Linux, Kernel 2.4.20, the above behaviour could be reproduced with our productive server (MySQL 3.23) and with a test server (MySQL 4.0.12). thanks in advance, alex -- Veysel Harun Sahin [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: auto number primary key - restarting
Easiest way to do what you want is to make this: 1.SHOW CREATE TABLE TableName and copy SQL 2. DROP TABLE TableName 3. CREATE TABLE - with SQL copied in 1. Actually, the easiest way to do this (assuming *all* records have been deleted) is: UPDATE table_name SET auto_increment_field = 0; Replace '0' with any number that you want to start with. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What about auto number primary key - wrapping?
Has anyone had a table that has lived long enough to wrap the auto incrementing number...? i.e. the complete byte span and back to 0x32. I'm just wondering if mySql will fill in the deleted ID's that don't exist, or if it just halts... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: auto number primary key - restarting
truncate table_name does both in one statement. And even optimizes the table (frees up unused disk space). However take care that you cannot rollback this DDL. Cheers /rudy -Original Message- From: Chris Boget [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 15:29 To: Krasimir_Slaveykov; Miroslav I.; [EMAIL PROTECTED] Subject: Re: auto number primary key - restarting Easiest way to do what you want is to make this: 1.SHOW CREATE TABLE TableName and copy SQL 2. DROP TABLE TableName 3. CREATE TABLE - with SQL copied in 1. Actually, the easiest way to do this (assuming *all* records have been deleted) is: UPDATE table_name SET auto_increment_field = 0; Replace '0' with any number that you want to start with. Chris -- 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: auto number primary key - restarting
truncate table_name does both in one statement. And even optimizes the table (frees up unused disk space). However take care that you cannot rollback this DDL. Wow. Learn something new every day! :p Thanks for the tip, Rudy! Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What about auto number primary key - wrapping?
Set the autoincrement column to the max value or the given data type (via alter table), insert a record and see what happens... Cheers /rudy -Original Message- From: TheMechE [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 15:40 To: [EMAIL PROTECTED] Subject: What about auto number primary key - wrapping? Has anyone had a table that has lived long enough to wrap the auto incrementing number...? i.e. the complete byte span and back to 0x32. I'm just wondering if mySql will fill in the deleted ID's that don't exist, or if it just halts... -- 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]
How to import XML into MySQL?
Sorry. I missed this discussion previously. Now I need the answer. Figures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs. PostgreSQL -- speed test
MySQL has posted a very interesting comparison on their website. It appears to be a reasonably fair evaluation. PostgreSQL was faster than MySQL in some areas and MySQL was faster than PostgreSQL in most areas. For speed with all of that functionality, I'd be more inclined to look at DB2 rather than MSSQL since DB2 actually has security. :-) Curtis On Monday 14 July 2003 09:35, Jim Smith wrote: I agree with your opinion in 100%, but in my case I need DBMS with features like subselectes/utf-8/stored procedures but the speed is also very important issue. You might have to spend money! You are saying that there is DBMS with all this features and it is as fast as MySQL ? I don't know, but if there is, it is one you will have to pay for. In any case, speed is as much a matter of application design as a DBMS characteristic. As a minor side issue, we did some _very limited_ testing with MS SQLServer 2000 using unicode v ascii queries. Using unicode, queries tended to run at about half the speed compare to using ascii. This was client server, so it is likely that the increased network traffic is to blame, but bear it it mind. -- 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: Managing big tables
Sorry rudy, but I can not understand what you try to say! I can only say that if you follow the link below and read the explanations on that page and also follow the related links you can find answers to Alex's problems. [EMAIL PROTECTED] wrote: Always take care what you want to achieve! And consider the circumstances. Yes, adding a lot of indexes makes queries faster. But makes inserts/deletes/updates slower. Alex's problem is NOT that his/her queries takes too long, the problem is that optimize takes too long. Which is something completely different. Cheers /rudy -Original Message- From: Veysel Harun Sahin [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 15:22 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Managing big tables http://www.mysql.com/doc/en/Data_size.html [EMAIL PROTECTED] wrote: Hello, i've got a little problem, we're using mysql with two big tables (one has 90 Mio. Rows (60 Gb on HD), the other contains nearly 200.000.000 (130 Gb on HD). Now we want to delete some rows from these tables to free diskspace. It seems that MySQL frees the harddisk-space which was used by these rows only after optimization, which lasts very long on these tables. Both tables are dynamic in terms of row-format what seems to extend the time needed for optimization. I tried to convert the smaller one to fixed-row-format, which increased the disk-space of its data-file from 30 Gb to 60 Gb. This would not be the problem, but some SQLs which are run daily on this table now run 4 times slower than with dynamic structure. So, my questions are: 1) Did i something wrong while converting to fixed row-format ? (i found no indication) 2) Is the fixed structure really faster on optimization ? 3) Can anybody confirm the slow-down on big tables when converted from dynamic to fixed ? (on small tables fixed were faster) I'm running SuSE Linux, Kernel 2.4.20, the above behaviour could be reproduced with our productive server (MySQL 3.23) and with a test server (MySQL 4.0.12). thanks in advance, alex -- Veysel Harun Sahin [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating more than 32 keys?
If you have to create that many keys, you may want to consider changing your data structure. You additions and updates to the database would get pretty slow if that many indexes have to be updated. What I usually do is change my columns to rows and add an additional qualifier column to indicate what type of data is in that row. Then you only need one or two indexes, one for the qualifier and one for the data. But you can index an almost unlimited number of keys. Querying and joins will be a little more difficult from a programming standpoint, but it's very flexible. Hope that helps. On Monday, July 14, 2003, at 04:12 PM, Circus ETL wrote: The documentation states that MyISAM tables can be used with more than 32 keys, but I can't get mysql to accept more than 32. Also, is it possible (advisable) to build a version of the MyISAM tables that exceed the 64 key limit? If so, how is this done? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication
I set up a replication with MySQL 4.0.13, it works very fine, but unfortunately only for several hours (between 10 and 30 hours) Has someone an idea why the replication stopps? Thanks, Moritz 030714 18:02:07 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 030715 13:40:55 Slave I/O thread exiting, read up to log 'log-bin.003', position 388 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Managing big tables
Ok, sorry, I focused too much on the optimization of the table. Because this is an isolated problem and most what is written on the page does not apply to this issues. This is like saying When the sun shines, there is always a shadow which is generally true but not on June,21, 12:00pm on the equator. Anyway... Why are fixed length faster than dynamic length? Because the DB can calculate the record length for all records in the DB once and then use fseek() to directly jump to the beginning of the record. With dynamic structures this is not possible because every record differs. So every field which can be dynamic (varchar) has to be checked for its length (the first byte(s) in the data file) and then the REAL length is found. So no global use of fseek() is possible. However, if you have good indexes this does not turn out to be that much of a problem, because the index does the fseek() for you (gets you the position). Then you have to find the beginning of the field within the column, which is slower on dynamic (but not that much if you do not have too many columns). What now again is the difference is, that with dynamic length only the REAL number of bytes is fetched into memory (the real length is stored in the first bytes of the record). On a fixed length the WHOLE field is fetched into memory. And here you can have the bottleneck why Alex's kwiris now take longer. An extreme case would be, if the field is defined as VARCHAR(255) and always only contains 1 character. So you have a disk read of the first byte (real length) and then the content (again 1 byte). So you read 2 bytes from disk. If on the other hand you then convert this to CHAR(255) you ALWAYS have to read 255 chars into memory. Given that the result set is huge and exactly these disk reads limit your performance because your read 254 byes for nothing (which is 253 bytes more than with dynamic structure). Calculating and finding real length on the otherhand takes no time at all (given that between the 2 reads the read header of the hard disk does not move, which should not be case). Hope this is more clear now. If I sounded offensive in my first mail, this was NOT the intent, but with my limited English knowledge I just type what comes to my mind :( blame it to my Austrian heritage :) Cheers /rudy ps: as to converting dynamic length to fixed length, procedure analyze could be handy. -Original Message- From: Veysel Harun Sahin [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 16:24 To: Rudy Metzger Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Managing big tables Sorry rudy, but I can not understand what you try to say! I can only say that if you follow the link below and read the explanations on that page and also follow the related links you can find answers to Alex's problems. [EMAIL PROTECTED] wrote: Always take care what you want to achieve! And consider the circumstances. Yes, adding a lot of indexes makes queries faster. But makes inserts/deletes/updates slower. Alex's problem is NOT that his/her queries takes too long, the problem is that optimize takes too long. Which is something completely different. Cheers /rudy -Original Message- From: Veysel Harun Sahin [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 15:22 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Managing big tables http://www.mysql.com/doc/en/Data_size.html [EMAIL PROTECTED] wrote: Hello, i've got a little problem, we're using mysql with two big tables (one has 90 Mio. Rows (60 Gb on HD), the other contains nearly 200.000.000 (130 Gb on HD). Now we want to delete some rows from these tables to free diskspace. It seems that MySQL frees the harddisk-space which was used by these rows only after optimization, which lasts very long on these tables. Both tables are dynamic in terms of row-format what seems to extend the time needed for optimization. I tried to convert the smaller one to fixed-row-format, which increased the disk-space of its data-file from 30 Gb to 60 Gb. This would not be the problem, but some SQLs which are run daily on this table now run 4 times slower than with dynamic structure. So, my questions are: 1) Did i something wrong while converting to fixed row-format ? (i found no indication) 2) Is the fixed structure really faster on optimization ? 3) Can anybody confirm the slow-down on big tables when converted from dynamic to fixed ? (on small tables fixed were faster) I'm running SuSE Linux, Kernel 2.4.20, the above behaviour could be reproduced with our productive server (MySQL 3.23) and with a test server (MySQL 4.0.12). thanks in advance, alex -- Veysel Harun Sahin [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:Managing big tables
Thanks for your efforts, and in rudys last mail he confirms what i have feared, the io-overhead for reading the extremely longer rows most probably causes these longer query-times. I think we will have to redesign our table-structure, because we're already working on a (hardware-)raid, so the mysql-raid-option won't really be an option. Perhaps the only way to get this damned big tables smaller in appropriate time will be a solution via merge-tables (if this does not slow down our productive queries on that table too much). alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replication
on the slave, run show slave status at the mysql prompt. if there is an error, one of the fileds will tell u what it is -Original Message- From: Moritz Steiner [mailto:[EMAIL PROTECTED] Sent: 15 July 2003 16:41 To: [EMAIL PROTECTED] Subject: replication I set up a replication with MySQL 4.0.13, it works very fine, but unfortunately only for several hours (between 10 and 30 hours) Has someone an idea why the replication stopps? Thanks, Moritz 030714 18:02:07 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 030715 13:40:55 Slave I/O thread exiting, read up to log 'log-bin.003', position 388 -- 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]
More duhh! questions
I am attempting to create a temporary table to do a complex query and I get an error: error 1044: Access denied for user: '@localhost' to database 'shopsample' what can I do to keep this from happening? I am using the production version of mySQL 4.0.13 in windows 2000. Here is the query: create temporary table tmp ( article int(4) unsigned zerofill default '' not null, price double(16,2) default '0.00' not null); the goal is to continue to the following: lock tables shop read; insert into tmp select article, max(price) from shop group by article; select shop.article, dealer, shop.price from shop, tmp where shop.article=tmp.article and shop.price=tmp.price; unlock tables; drop table tmp; Any assistance would be appreciated. George -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication setup
I have set up a master and slave whose versions are (using 'show = variables') S: 3.23.47-nt, M: 3.23.47-nt-log. They were both installed = from the same distribution zip. I can't get replication going even = though the server and client status seem ok (slave running and file and = position information on master). On master: Grant file on *.* to astusreplic@% identified by 'password' Master my.cnf:=20 log-bin server-id=1 Slave my.cnf master-host=SMCCREA master-user=astusreplic master-password=password master-port=3306 server-id=3 Error messages on slave: 030715 8:53:16 Slave: connected to master '[EMAIL PROTECTED]:3306', = replication started in log 'FIRST' at position 32 030715 8:53:16 Slave: received 0 length packet from server, apparent = master shutdown: (0) 030715 8:53:16 Slave: Failed reading log event, reconnecting to retry, = log 'FIRST' position 32 030715 8:53:16 Slave: reconnected to master = '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at = position 32 show processlist output: id=1, user=system user, host=none, db=null, command=connect, = time=110, state=Waiting to reconnect after a failed read, Info=null --- Steve McCrea eng. Director of Software Development ETL Electronique 2396 de la Province Longueuil Qc, J4G 1G1, Canada Phone: (450) 442 7887 - 229 Fax: (450) 442 0605
WG: wait_timeout in my.cnf
I set a lot of variables in the my.cnf file, all variables are set (I checked it with show variables) except of wait_timeout. If I set the option in MySQL with option wait_status=xxx it works. Does anybody know why this could happen? Thanks, Moritz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
genereal query log
I want to turn on and off the general query log from time to time. Is there a possibility to do this without changing the my.cnf file and restarting the server. I thought for example set option log=ON Thanks, Moritz
CF MySQL
Hi All I've had a quick scoot about and could see anything about cold fusion and mysql is it possible to use this combination successfully if at all? Thanks Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLDump
Hi, I am new to MySQL. I wonder if someone could tell me if I can run MySQLDump on a database while a few users are still connected to it. If yes, is it clever enough to backup the transaction log and use it to restore the database to the nearest state to the before the dump? Many thanks. Gilbert Wu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CF MySQL
Thanks Curtis, interesting indeed... presumably MySQL is in a different location as CF runs off NT4 or is MySQL a win version? The reason I am asking is because a solution has been presented to me in CF and MS SQL which I think will restrict future development and wondered whether a conversion to PHP and MySQL in the future be smooth or a waste of time. Not necessarily a full conversion but addons to the site at least. Andrew -Original Message- From: Curtis Maurand [mailto:Curtis Maurand] Sent: 15 July 2003 17:02 To: [EMAIL PROTECTED] Subject: Re: CF MySQL Set up your ODBC DSN using the MyODBC driver. Set up your database. declare your dsn as normal in cold fusion. have fun. On Tuesday 15 July 2003 12:01, Andrew wrote: Hi All I've had a quick scoot about and could see anything about cold fusion and mysql is it possible to use this combination successfully if at all? Thanks Andrew --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQLDump
The connections will still write to one or two tables. Mainly stock prices from a price server. Hence, not so bother if any prices arrive after the dump starts are not captured. I would like to know if mysqldump will fail to run if the tables are not locked? Cheers. Gilbert -Original Message- From: Sent: 15 July 2003 17:11 To: Gilbert Wu Subject: Re: MySQLDump sure, just tell it to lock the tables while it does the dump. curtis On Tuesday 15 July 2003 12:02, Gilbert Wu wrote: Hi, I am new to MySQL. I wonder if someone could tell me if I can run MySQLDump on a database while a few users are still connected to it. If yes, is it clever enough to backup the transaction log and use it to restore the database to the nearest state to the before the dump? Many thanks. Gilbert Wu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
One Big Data Base or Many Smaller Ones
Hello, I am creating an on-line course and would like to know if it will make a difference in the way I set up my database(s). Option 1 : Create one large Database for the course Here I would prefix the tables with their functions, ie. reg_ quizzes_ forum_ etc. Option 2 : Create a smaller Database for each function Here I would create a registration database, a quizzes database a forum database etc. I do not have my own server, so I would have to get my ISP to create each database, which might be a hassle for them. I want to know which method would be best. Thank you Charla -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE COMPARISON
Hi: I was wondering if there is a date function to get the older date between two dates?. Or smething that indicates me that one date is older than the other one. I know that I have the function YEAR,MONTH,DAY and I can use them, but I don't know if exists a function that can do the same. Greetings everyone. Thnx in advance _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Single Record Locking - Permanent?
Is it possible to lock single records for all but a certain set of users, permanently? The intention is to make old items permanently unchangeable by anyone but managers. Thanks, Dan Ullom TechCentric 314-991-2594 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE COMPARISON
Miguel - You can compare dates directly using and - no need for a special function. Dates are older when they are less than other dates. Cheers. Jeff On Tuesday, July 15, 2003, at 09:36 AM, Miguel Perez wrote: Hi: I was wondering if there is a date function to get the older date between two dates?. Or smething that indicates me that one date is older than the other one. I know that I have the function YEAR,MONTH,DAY and I can use them, but I don't know if exists a function that can do the same. Greetings everyone. Thnx in advance _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- 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: Pulling large Microsoft SQL files into MySQL
LOAD DATA INFILE data.txt INTO TABLE table_name FIELDS TERMINATED BY '|' ENCLOSED BY '' LINES TERMINATED BY '\n'; You can find more on this at: http://www.mysql.com/doc/en/LOAD_DATA.html -Original Message- From: Rudolf Bekker [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 2:43 AM To: [EMAIL PROTECTED] Subject: FW: Pulling large Microsoft SQL files into MySQL Importance: High -Original Message- From: Rudolf Bekker Sent: Monday, 14 July 2003 14:13 To: MySQL MailList ([EMAIL PROTECTED]) Subject: Pulling large Microsoft SQL files into MySQL Importance: High The MySQL manual mentions that one could specify the column and value separators and the end of file marker explicitly in the LOAD DATA statement. I'm looking for the syntax of this statement to import large data files (.txt) originally exported from Microsoft SQL 7. Example file:. Extract_Date|LAU|Customer_Number|BAN_Number|Telephone_Number| Bill_Date|Local_Usage_Credit_Amount|Local_Usage_Credit_Call_Count| Local_Usage_Credit_Call_Seconds|National_Usage_Credit_Amount|Nationa l_Usage_Credit_Call_Count|National_Usage_Credit_Call_Seconds|Interna tional_Usage_Credit_Amount|International_Usage_Credit_Call_Count|Int ernational_Usage_Credit_Call_Seconds|Cell_Usage_Credit_Amount|Cell_U sage_Credit_Call_Count|Cell_Usage_Credit_Call_Seconds|Other_Usage_Cr edit_Amount|Other_Usage_Credit_Call_Count|Other_Usage_Credit_Call_Se conds 20030409|63|63000233|630002330001|--|200211|0|0|0|0| 0|0|0|0|0|0|0|0|-555.94|0|0 20030409|63|63000233|630002330001|--|200302|0|0|0|0| 0|0|0|0|0|0|0|0|-594.24|0|0 20030409|63|63000233|630002330001|--|200303|0|0|0|0| 0|0|0|0|0|0|0|0|-615.21|0|0 20030409|63|63000235|630002350001|0539480616|200201|-10.53 |-14|-2346|-4.89|-6|-534|-1.3|-1|-26|-14.82|-4|-570 |0|0|0 20030409|63|63000283|630002830001|0539480627|200111|-6.14| -1|-7804|0|0|0|0|0|0|0|0|0|0|0|0 20030409|63|63000283|630002830001|0539480627|200201|-140.79 |-143|-101519|-68.16|-43|-9723|0|0|0|-5.68|-4|-240 |0|0|0 20030409|63|63000421|630004210001|05393621802|200303|0|0 |0|-128.27|-22|-4680|0|0|0|-39.65|-6|-840|0|0|0 20030409|63|63000459|630004590001|0539481533|200302|0|0| 0|0|0|0|0|0|0|-.01|0|0|0|0|0 20030409|63|63000495|630004950001|0539490419|200302|0|0| 0|0|0|0|0|0|0|-.02|0|0|0|0|0 20030409|63|63000519|630005190001|0539490719|200302|0|0| 0|0|0|0|0|0|0|-.01|0|0|0|0|0 20030409|63|63000521|630005210001|0539823312|200111|0|0| 0|-12.29|-5|-2100|-13.5|-1|-180|-264.15|-63|-17340| -34.15|-19|-6360 I need to specify the text separator as and the column separator as |. Thanx, Rudolf. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Record Locking - Permanent?
2 methods I can think of: 1) Move the records into a different table, and set permissions accordingly (allow updates on for managers on that table) 2) Control authentication and access in your program. -Cameron Wilhelm On Tuesday, July 15, 2003, at 11:03 AM, Dan Ullom wrote: Is it possible to lock single records for all but a certain set of users, permanently? The intention is to make old items permanently unchangeable by anyone but managers. Thanks, Dan Ullom TechCentric 314-991-2594 -- 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: CF MySQL
Contents are Direct Alliance Corporation CONFIDENTIAL - Andrew, I have setup all the environments you are talking about. CF with MS SQL is much easier to setup and get going than PHP and mysql. However I have see twice the speed and stability with my applications that are written in PHP and MYSQL. I admit that struggling through all the setup can be cumbersome, but in the end it pays off. If you can get both PHP and MYSQL up on a Linux box even better. I have been certified in CF and used it for over 4 years. CF does have a rapid deployment phase and works well with mysql. But I believe php has a much more robust set of function, system, and networking capability that CF. Plus the key. Is that it's all FREE... CF + MS SQL + MS= $3000 + PHP + MYSQL + Linux = Freedom :) -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:24 AM Cc: MySQL-Lista Subject: RE: CF MySQL Thanks Curtis, interesting indeed... presumably MySQL is in a different location as CF runs off NT4 or is MySQL a win version? The reason I am asking is because a solution has been presented to me in CF and MS SQL which I think will restrict future development and wondered whether a conversion to PHP and MySQL in the future be smooth or a waste of time. Not necessarily a full conversion but addons to the site at least. Andrew -Original Message- From: Curtis Maurand [mailto:Curtis Maurand] Sent: 15 July 2003 17:02 To: [EMAIL PROTECTED] Subject: Re: CF MySQL Set up your ODBC DSN using the MyODBC driver. Set up your database. declare your dsn as normal in cold fusion. have fun. On Tuesday 15 July 2003 12:01, Andrew wrote: Hi All I've had a quick scoot about and could see anything about cold fusion and mysql is it possible to use this combination successfully if at all? Thanks Andrew --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This message is for the designated recipient(s) only and contains Direct Alliance Corporation privileged and confidential information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of this email is prohibited.
Frequent Table Corruption - Please Help
Hi everyone, Since we upgraded to MySQL 4.0.13 from 3.23, we have been getting table corruption often. It happens about twice per week (with about 500 queries per second average). I have even set up a cron to run mysqlcheck every hour to try to do some damage control. The biggest problem is that once the table is corrupted, it seems to be locked. Well, no clients can read from it. Once repaired, just one record is usually lost for each time the corruption occurs. I am not sure if this is a MySQL bug or even how to reproduce it, but I was hoping that someone here could help. I have included all the information that I have about this below. Any insight is greatly appreciated! Here is the mysqlbug information: Release: mysql-4.0.13 (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux *.com 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Nov 1 2002 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 2002 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' Other System Information: The system is running hardware RAID-10 with SCSI drives. It has 4 Xeon processors at 2.2GHz each, 2GB RAM. MySQL Configuration (my.cnf): [mysqld] set-variable = max_connections=1000 set-variable = delayed_queue_size=10 innodb_data_file_path=ibdata:30M:autoextend:max:2000M # Set buffer pool size to # 50 - 80 % of your computer's # memory set-variable = innodb_buffer_pool_size=1G set-variable = innodb_additional_mem_pool_size=20M # Set the log file size to about # 15 % of the buffer pool size set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M # log-bin server-id=1 master-host=192.168.1.3 master-user=repl master-password=* master-port=3306 set-variable = query_cache_size=268435456 Log Entries: [The first entry is repeated many times. The second is from the mysqlcheck cron that repairs the tables] 030715 0:43:49 read_const: Got error 127 when reading table 030715 2:00:31 Note: Found 23550 of 23551 rows when repairing Thanks again for your help in advance! -- Richard Gabriel [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calendar SELECT with repeating occurance
I¹m developing a calendar, with a view by month and list view, for a client and I would like to add the option for them to add an event and select if it is to repeat, i.e. Annually, monthly, weekly. As I see it now I can do one of two things (possibly more?). Add a predetermined amount of records in the database for each occurance or use a SELECT that can find a single event that has been marked to repeat and falls within the specified month or year. The client will probably have less then 10 items per month. Here are my questions; Which option/direction would be better? Add records/do SELECT? Is there an example of a SELECT? Something like find records for July, 2003 and records marked repeat that would fall within the month of July, 2003. Any help appreciated, Tom
Re: DATE COMPARISON
This would do it if you are just comparing two dates: $max_date = ($date1$date2?$date1:$date2); It's just using the one line form of an if statement. Note that the function is incorrect if they date are equal. On Tuesday, July 15, 2003, at 12:36 PM, Miguel Perez wrote: I was wondering if there is a date function to get the older date between two dates?. Or smething that indicates me that one date is older than the other one. I know that I have the function YEAR,MONTH,DAY and I can use them, but I don't know if exists a function that can do the same. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table protection
Is there any command that we can use to view the protection on the tables??. Is there any way that we can convert read only tables to read write?. Please ehlp thank u kamran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table protection
Is there any command that we can use to view the protection on the tables??. Is there any way that we can convert read only tables to read write?. Please ehlp thank u kamran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pulling large Microsoft SQL files into MySQL
Hi: Or you can use a DTS to transfer the info to MySQL Greetings From: Ralph Guzman [EMAIL PROTECTED] To: 'Rudolf Bekker' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Pulling large Microsoft SQL files into MySQL Date: Tue, 15 Jul 2003 10:32:03 -0700 LOAD DATA INFILE data.txt INTO TABLE table_name FIELDS TERMINATED BY '|' ENCLOSED BY '' LINES TERMINATED BY '\n'; You can find more on this at: http://www.mysql.com/doc/en/LOAD_DATA.html -Original Message- From: Rudolf Bekker [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 2:43 AM To: [EMAIL PROTECTED] Subject: FW: Pulling large Microsoft SQL files into MySQL Importance: High -Original Message- From: Rudolf Bekker Sent: Monday, 14 July 2003 14:13 To: MySQL MailList ([EMAIL PROTECTED]) Subject: Pulling large Microsoft SQL files into MySQL Importance: High The MySQL manual mentions that one could specify the column and value separators and the end of file marker explicitly in the LOAD DATA statement. I'm looking for the syntax of this statement to import large data files (.txt) originally exported from Microsoft SQL 7. Example file:. Extract_Date|LAU|Customer_Number|BAN_Number|Telephone_Number| Bill_Date|Local_Usage_Credit_Amount|Local_Usage_Credit_Call_Count| Local_Usage_Credit_Call_Seconds|National_Usage_Credit_Amount|Nationa l_Usage_Credit_Call_Count|National_Usage_Credit_Call_Seconds|Interna tional_Usage_Credit_Amount|International_Usage_Credit_Call_Count|Int ernational_Usage_Credit_Call_Seconds|Cell_Usage_Credit_Amount|Cell_U sage_Credit_Call_Count|Cell_Usage_Credit_Call_Seconds|Other_Usage_Cr edit_Amount|Other_Usage_Credit_Call_Count|Other_Usage_Credit_Call_Se conds 20030409|63|63000233|630002330001|--|200211|0|0|0|0| 0|0|0|0|0|0|0|0|-555.94|0|0 20030409|63|63000233|630002330001|--|200302|0|0|0|0| 0|0|0|0|0|0|0|0|-594.24|0|0 20030409|63|63000233|630002330001|--|200303|0|0|0|0| 0|0|0|0|0|0|0|0|-615.21|0|0 20030409|63|63000235|630002350001|0539480616|200201|-10.53 |-14|-2346|-4.89|-6|-534|-1.3|-1|-26|-14.82|-4|-570 |0|0|0 20030409|63|63000283|630002830001|0539480627|200111|-6.14| -1|-7804|0|0|0|0|0|0|0|0|0|0|0|0 20030409|63|63000283|630002830001|0539480627|200201|-140.79 |-143|-101519|-68.16|-43|-9723|0|0|0|-5.68|-4|-240 |0|0|0 20030409|63|63000421|630004210001|05393621802|200303|0|0 |0|-128.27|-22|-4680|0|0|0|-39.65|-6|-840|0|0|0 20030409|63|63000459|630004590001|0539481533|200302|0|0| 0|0|0|0|0|0|0|-.01|0|0|0|0|0 20030409|63|63000495|630004950001|0539490419|200302|0|0| 0|0|0|0|0|0|0|-.02|0|0|0|0|0 20030409|63|63000519|630005190001|0539490719|200302|0|0| 0|0|0|0|0|0|0|-.01|0|0|0|0|0 20030409|63|63000521|630005210001|0539823312|200111|0|0| 0|-12.29|-5|-2100|-13.5|-1|-180|-264.15|-63|-17340| -34.15|-19|-6360 I need to specify the text separator as and the column separator as |. Thanx, Rudolf. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.13 or 4.0.14
I'm getting ready to upgrade a server from 4.0.12 and was wondering if anyone knew the time frame for 4.0.14, or if I should just go with .13 for now. -- Michael Conlen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best practice column type for storing decimal currency amounts?
Is there an accepted best practice on whether to store decimal currency amounts (e.g. dollars and cents) in MySQL decimal column types? Certainly, the most straightforward way is to use decimal columns. But it appears that such values are stored as ASCII strings, which would be inefficient for calculations (requiring conversion to a numeric type for each calculation). I guess the alternative would be to use integer columns (and multiply by 100 to store the value as total cents). My particular context is a PHP/MySQL sales system. What's the consensus? Thanks much, --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I display images from a mySQL Database in a web page?
I have created a BLOB field to store images. Is there any way to embed them within HTML with something like: image start: jpeg /image Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Frequent Table Corruption - Please Help
Richard, you are running a Red Hat kernel 2.4.18? Kernels 2.4.20 seem to be much more reliable. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Richard Gabriel [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, July 15, 2003 9:52 PM Subject: Frequent Table Corruption - Please Help Hi everyone, Since we upgraded to MySQL 4.0.13 from 3.23, we have been getting table corruption often. It happens about twice per week (with about 500 queries per second average). I have even set up a cron to run mysqlcheck every hour to try to do some damage control. The biggest problem is that once the table is corrupted, it seems to be locked. Well, no clients can read from it. Once repaired, just one record is usually lost for each time the corruption occurs. I am not sure if this is a MySQL bug or even how to reproduce it, but I was hoping that someone here could help. I have included all the information that I have about this below. Any insight is greatly appreciated! Here is the mysqlbug information: Release: mysql-4.0.13 (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux *.com 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Nov 1 2002 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 2002 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' Other System Information: The system is running hardware RAID-10 with SCSI drives. It has 4 Xeon processors at 2.2GHz each, 2GB RAM. MySQL Configuration (my.cnf): [mysqld] set-variable = max_connections=1000 set-variable = delayed_queue_size=10 innodb_data_file_path=ibdata:30M:autoextend:max:2000M # Set buffer pool size to # 50 - 80 % of your computer's # memory set-variable = innodb_buffer_pool_size=1G set-variable = innodb_additional_mem_pool_size=20M # Set the log file size to about # 15 % of the buffer pool size set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M # log-bin server-id=1 master-host=192.168.1.3 master-user=repl master-password=* master-port=3306 set-variable = query_cache_size=268435456 Log Entries: [The first entry is repeated many times. The second is from the mysqlcheck cron that repairs the tables] 030715 0:43:49 read_const: Got error 127 when reading table 030715 2:00:31 Note: Found 23550 of 23551 rows when repairing Thanks again for your help in advance! -- Richard Gabriel [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: CF MySQL
I am currently running ColdFusion MX on my Linux Servers access both MySQL from different Linux Servers and MS SQL from a MS2000 WS. ColdFusion and MySQL are both available and run on both Win Linux machines. I also have PHP installed on Linux, but have never attempted to learn fully utilize PHP. ColdFusion is capable of access many DB programs. You simply need to tell CF how and where to access the Data. At 12:01 PM 7/15/2003, Andrew wrote: Hi All I've had a quick scoot about and could see anything about cold fusion and mysql is it possible to use this combination successfully if at all? Thanks Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.13 or 4.0.14
Michael, - Original Message - From: Michael Conlen [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, July 15, 2003 10:36 PM Subject: 4.0.13 or 4.0.14 I'm getting ready to upgrade a server from 4.0.12 and was wondering if anyone knew the time frame for 4.0.14, or if I should just go with .13 for now. a week ago I predicted 4.0.14 comes between the 15th and 25th of this month and I have no reason to change that prediction :). There are lots of bug fixes in 4.0.14. Best to wait a couple of weeks to see if they introduced new bugs. -- Michael Conlen Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What about auto number primary key - wrapping?
I have a copy of 3.23.56 running on one of my machines and it does NOT wrap auto_increment columns when the upper limit is reached. I haven't tested it with any newer versions, though I doubt they'd be any different. If this is a concern for you then I would suggest using a bigger column type for auto_incrementing and making that column unsigned. The extra space required for your DB is really trivial compared to the saved headaches of devising your own wrap/reshuffle scheme. -Rob -Original Message- From: TheMechE [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:40 AM To: [EMAIL PROTECTED] Subject: What about auto number primary key - wrapping? Has anyone had a table that has lived long enough to wrap the auto incrementing number...? i.e. the complete byte span and back to 0x32. I'm just wondering if mySql will fill in the deleted ID's that don't exist, or if it just halts... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Frequent Table Corruption - Please Help
Thanks for the tip. I'll see about upgrading, but it won't be a small task. Any reason why 2.4.18 problems wouldn't have effected MySQL 3.23? I'm trying to search for a solution that does not involve upgrading kernels on 20 machines that are in production use right now. Thanks again! Richard Gabriel Director of Technology, CoreSense Inc. (518) 306-3043 x3951 - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 3:45 PM Subject: Re: Frequent Table Corruption - Please Help Richard, you are running a Red Hat kernel 2.4.18? Kernels 2.4.20 seem to be much more reliable. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Richard Gabriel [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, July 15, 2003 9:52 PM Subject: Frequent Table Corruption - Please Help Hi everyone, Since we upgraded to MySQL 4.0.13 from 3.23, we have been getting table corruption often. It happens about twice per week (with about 500 queries per second average). I have even set up a cron to run mysqlcheck every hour to try to do some damage control. The biggest problem is that once the table is corrupted, it seems to be locked. Well, no clients can read from it. Once repaired, just one record is usually lost for each time the corruption occurs. I am not sure if this is a MySQL bug or even how to reproduce it, but I was hoping that someone here could help. I have included all the information that I have about this below. Any insight is greatly appreciated! Here is the mysqlbug information: Release: mysql-4.0.13 (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux *.com 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Nov 1 2002 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 2002 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' Other System Information: The system is running hardware RAID-10 with SCSI drives. It has 4 Xeon processors at 2.2GHz each, 2GB RAM. MySQL Configuration (my.cnf): [mysqld] set-variable = max_connections=1000 set-variable = delayed_queue_size=10 innodb_data_file_path=ibdata:30M:autoextend:max:2000M # Set buffer pool size to # 50 - 80 % of your computer's # memory set-variable = innodb_buffer_pool_size=1G set-variable = innodb_additional_mem_pool_size=20M # Set the log file size to about # 15 % of the buffer pool size set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M # log-bin server-id=1 master-host=192.168.1.3 master-user=repl master-password=* master-port=3306 set-variable = query_cache_size=268435456 Log Entries: [The first entry is repeated many times. The second is from the mysqlcheck cron that repairs the tables] 030715 0:43:49
Re: 4.0.13 or 4.0.14
Hi! On Jul 15, Michael Conlen wrote: I'm getting ready to upgrade a server from 4.0.12 and was wondering if anyone knew the time frame for 4.0.14, or if I should just go with .13 for now. 4.0.14 release it a matter of days. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: One Big Data Base or Many Smaller Ones
Hi Charla, Generally if the tables will be related to each other then you want to keep them in the same database. as an example, you would want to link quiz scores to the individual students registered in your class. It is a little easier to do table joins and such when there is only one database involved. Besides which, most ISPS put a cap on the number of databases you are allowed to create. Hope that helps! -Rob -Original Message- From: Charla Beaulieu [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 12:33 PM To: [EMAIL PROTECTED] Subject: One Big Data Base or Many Smaller Ones Hello, I am creating an on-line course and would like to know if it will make a difference in the way I set up my database(s). Option 1 : Create one large Database for the course Here I would prefix the tables with their functions, ie. reg_ quizzes_ forum_ etc. Option 2 : Create a smaller Database for each function Here I would create a registration database, a quizzes database a forum database etc. I do not have my own server, so I would have to get my ISP to create each database, which might be a hassle for them. I want to know which method would be best. Thank you Charla -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Frequent Table Corruption - Please Help
Richard, - Original Message - From: Richard Gabriel [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 10:53 PM Subject: Re: Frequent Table Corruption - Please Help Thanks for the tip. I'll see about upgrading, but it won't be a small task. Any reason why 2.4.18 problems wouldn't have effected MySQL 3.23? I'm trying to search for a solution that does not involve upgrading kernels on 20 machines that are in production use right now. Thanks again! it may be worthwhile to test a new kernel in one of those problematic computers. We believe corruption problems in RH 2.4.18/drivers are random. Then any small change can provoke them. But we will probably never know what exactly was wrong in some 2.4.18 computers. Richard Gabriel Director of Technology, CoreSense Inc. (518) 306-3043 x3951 Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 3:45 PM Subject: Re: Frequent Table Corruption - Please Help Richard, you are running a Red Hat kernel 2.4.18? Kernels 2.4.20 seem to be much more reliable. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Richard Gabriel [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, July 15, 2003 9:52 PM Subject: Frequent Table Corruption - Please Help Hi everyone, Since we upgraded to MySQL 4.0.13 from 3.23, we have been getting table corruption often. It happens about twice per week (with about 500 queries per second average). I have even set up a cron to run mysqlcheck every hour to try to do some damage control. The biggest problem is that once the table is corrupted, it seems to be locked. Well, no clients can read from it. Once repaired, just one record is usually lost for each time the corruption occurs. I am not sure if this is a MySQL bug or even how to reproduce it, but I was hoping that someone here could help. I have included all the information that I have about this below. Any insight is greatly appreciated! Here is the mysqlbug information: Release: mysql-4.0.13 (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux *.com 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Nov 1 2002 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 2002 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' Other System Information: The system is running hardware RAID-10 with SCSI drives. It has 4 Xeon processors at 2.2GHz each, 2GB RAM. MySQL Configuration (my.cnf): [mysqld] set-variable = max_connections=1000 set-variable = delayed_queue_size=10 innodb_data_file_path=ibdata:30M:autoextend:max:2000M # Set buffer pool size to # 50 - 80 % of your computer's # memory
Malformed Packet
Using the C API I'm getting this on some of my queries, however if I run the query on a different mysql connection, it seems to run fine: Errmsg: Malformed packet Errno: 2027 Any Ideas? -Ryan.
Re: innodb file won't shrink
Alvaro, - Original Message - From: Alvaro Avello [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 1:13 AM Subject: Re: innodb file won't shrink -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There 's any chance that in the future every InnoDB table files from a determinated database can be placed in the same directory of the mysql ( MyISAM ) database ? . My point is that if you want to take a binary backup of all databases in the mysql directory and you want to restore just one of the databases , you could just copy the directory to the correct place and thats all you have to do to restore a single database.. I guess I'm getting tired to wait for the dump to re-create all the indexes and stuff like that. it will not be straightforward. The undo logs used to purge old versions of rows and roll back uncommitted transaction will not be placed to those table files. But if you let the database to be silent and run purge to completion, then you will get clean tables you can restore later individually to the database. Thanks In advance Saludos / Regards , Alvaro Avello. Regards, Heikki walt wrote: |Heikki Tuuri wrote: | | |On September 15th, 2003 you will be able to put every InnoDB table into its |own file. That should alleviate this kind of problem. | | |Best regards, | |Heikki Tuuri |Innobase Oy |http://www.innodb.com |Transactions, foreign keys, and a hot backup tool for MySQL |Order MySQL technical support from https://order.mysql.com/ | | |That is great news! Will a single table be able to span several |datafiles? | |Thanks! |walt | -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org iD8DBQE/EysJR9NZaw5tbc0RAnMHAJ4/ZxbE5sRwAjW8cDAcXOr6cbsiowCeJU14 y/QT2dFY16n6L/OcJ0vCHyw= =PCGI -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb file won't shrink
Walt, - Original Message - From: walt [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 11:56 PM Subject: Re: innodb file won't shrink Heikki Tuuri wrote: On September 15th, 2003 you will be able to put every InnoDB table into its own file. That should alleviate this kind of problem. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ That is great news! Will a single table be able to span several datafiles? sorry, no. The TODO list is already overloaded. Thanks! walt Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost connection to MySQL server during query
Hello all, After changing RAM and the ethernet card of my DB server, the problem of getting frequent: Database error (error code 102) Could not connect to database server (Lost connection to MySQL server during query) Still occurs ;/ I've tried to eliminate all factors (like multiple servers, load-balancers, replication etc) so I have wrote a php script that only does the following: - opens db connection - does a simple select - closes db connection If I refresh this page over and over, every 6-7 times I get the above error. Anyone have any further solutions, ?? I've tried the software (re-installing mysql, upgrading, etc) and tried the hardware (RAM, ethernet card). Nothing seems to help. The DB server is Linux Redhat 7.3 with the 2.4.18-3smp kernel. I am running MySQL standard 4.0.13 Thanks -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Frequent Table Corruption - Please Help
Thank you very much for the help. I will schedule the upgrade and see if helps. I have 2 other machines running 2.4.18 without problems, but they also do not run the volume that the problematic machine has and they do not have RAID. Take care. Richard Gabriel Director of Technology, CoreSense Inc. (518) 306-3043 x3951 - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 4:08 PM Subject: Re: Frequent Table Corruption - Please Help Richard, - Original Message - From: Richard Gabriel [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 10:53 PM Subject: Re: Frequent Table Corruption - Please Help Thanks for the tip. I'll see about upgrading, but it won't be a small task. Any reason why 2.4.18 problems wouldn't have effected MySQL 3.23? I'm trying to search for a solution that does not involve upgrading kernels on 20 machines that are in production use right now. Thanks again! it may be worthwhile to test a new kernel in one of those problematic computers. We believe corruption problems in RH 2.4.18/drivers are random. Then any small change can provoke them. But we will probably never know what exactly was wrong in some 2.4.18 computers. Richard Gabriel Director of Technology, CoreSense Inc. (518) 306-3043 x3951 Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 3:45 PM Subject: Re: Frequent Table Corruption - Please Help Richard, you are running a Red Hat kernel 2.4.18? Kernels 2.4.20 seem to be much more reliable. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Richard Gabriel [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, July 15, 2003 9:52 PM Subject: Frequent Table Corruption - Please Help Hi everyone, Since we upgraded to MySQL 4.0.13 from 3.23, we have been getting table corruption often. It happens about twice per week (with about 500 queries per second average). I have even set up a cron to run mysqlcheck every hour to try to do some damage control. The biggest problem is that once the table is corrupted, it seems to be locked. Well, no clients can read from it. Once repaired, just one record is usually lost for each time the corruption occurs. I am not sure if this is a MySQL bug or even how to reproduce it, but I was hoping that someone here could help. I have included all the information that I have about this below. Any insight is greatly appreciated! Here is the mysqlbug information: Release: mysql-4.0.13 (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux *.com 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Nov 1 2002 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 2002 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6
Re: CF MySQL
there is a windows version of mysql. However, I agree with this post as to have it all run on Linux would be cool. There is also a Linux version of Cold Fusion. Curtis On Tuesday 15 July 2003 14:15, Cory Lamle wrote: Contents are Direct Alliance Corporation CONFIDENTIAL - Andrew, I have setup all the environments you are talking about. CF with MS SQL is much easier to setup and get going than PHP and mysql. However I have see twice the speed and stability with my applications that are written in PHP and MYSQL. I admit that struggling through all the setup can be cumbersome, but in the end it pays off. If you can get both PHP and MYSQL up on a Linux box even better. I have been certified in CF and used it for over 4 years. CF does have a rapid deployment phase and works well with mysql. But I believe php has a much more robust set of function, system, and networking capability that CF. Plus the key. Is that it's all FREE... CF + MS SQL + MS= $3000 + PHP + MYSQL + Linux = Freedom :) -Original Message- From: Andrew [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:24 AM Cc: MySQL-Lista Subject: RE: CF MySQL Thanks Curtis, interesting indeed... presumably MySQL is in a different location as CF runs off NT4 or is MySQL a win version? The reason I am asking is because a solution has been presented to me in CF and MS SQL which I think will restrict future development and wondered whether a conversion to PHP and MySQL in the future be smooth or a waste of time. Not necessarily a full conversion but addons to the site at least. Andrew -Original Message- From: Curtis Maurand [mailto:Curtis Maurand] Sent: 15 July 2003 17:02 To: [EMAIL PROTECTED] Subject: Re: CF MySQL Set up your ODBC DSN using the MyODBC driver. Set up your database. declare your dsn as normal in cold fusion. have fun. On Tuesday 15 July 2003 12:01, Andrew wrote: Hi All I've had a quick scoot about and could see anything about cold fusion and mysql is it possible to use this combination successfully if at all? Thanks Andrew --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join optimization
Hi folks, I¹m wondering what (in general) causes a join to use temporary tables and filesorts... More specifically, what can I tune to avoid it? I can give more info about a specific situation if anyone¹s interested. All the docs say is that you are likely to get a ³Using temporary² if you sort on a different column set than the ³group by², but I¹m not doing that...something else must be triggering it... Any ideas? -Partap Davis Syncrasy, LLC
Re: innodb file won't shrink
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sorry HeikkiCan you give me a few steps to obtain that...? Thanks a lot. Alvaro Avello. Heikki Tuuri wrote: |Alvaro, | |- Original Message - |From: Alvaro Avello [EMAIL PROTECTED] |To: Heikki Tuuri [EMAIL PROTECTED] |Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] |Sent: Tuesday, July 15, 2003 1:13 AM |Subject: Re: innodb file won't shrink | | |-BEGIN PGP SIGNED MESSAGE- |Hash: SHA1 | |There 's any chance that in the future every InnoDB table files from a |determinated database can be placed in the same directory of the mysql ( |MyISAM ) database ? . My point is that if you want to take a binary |backup of all databases in the mysql directory and you want to restore |just one of the databases , you could just copy the directory to the |correct place and thats all you have to do to restore a single |database.. I guess I'm getting tired to wait for the dump to |re-create all the indexes and stuff like that. | | |it will not be straightforward. The undo logs used to purge old versions of |rows and roll back uncommitted transaction will not be placed to those table |files. But if you let the database to be silent and run purge to completion, |then you will get clean tables you can restore later individually to the |database. | |Thanks In advance | |Saludos / Regards , | |Alvaro Avello. | | |Regards, | |Heikki | |walt wrote: | ||Heikki Tuuri wrote: || || ||On September 15th, 2003 you will be able to put every InnoDB table |into its ||own file. That should alleviate this kind of problem. || || ||Best regards, || ||Heikki Tuuri ||Innobase Oy ||http://www.innodb.com ||Transactions, foreign keys, and a hot backup tool for MySQL ||Order MySQL technical support from https://order.mysql.com/ || || ||That is great news! Will a single table be able to span several ||datafiles? || ||Thanks! ||walt || |-BEGIN PGP SIGNATURE- |Version: GnuPG v1.2.1 (GNU/Linux) |Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org | |iD8DBQE/EysJR9NZaw5tbc0RAnMHAJ4/ZxbE5sRwAjW8cDAcXOr6cbsiowCeJU14 |y/QT2dFY16n6L/OcJ0vCHyw= |=PCGI |-END PGP SIGNATURE- | | | | | -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org iD8DBQE/FGZMR9NZaw5tbc0RAn6lAJ0filp6siUs+TBk7N2CP8Il6mgHvwCfV20j YBIWR33O86CMczdhleqvZKs= =iQu1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb file won't shrink
Alvaro, - Original Message - From: Alvaro Avello [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 11:38 PM Subject: Re: innodb file won't shrink -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sorry HeikkiCan you give me a few steps to obtain that...? sorry, I was talking about the September file per table storage model. Thanks a lot. Alvaro Avello. Regards, Heikki Heikki Tuuri wrote: |Alvaro, | |- Original Message - |From: Alvaro Avello [EMAIL PROTECTED] |To: Heikki Tuuri [EMAIL PROTECTED] |Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] |Sent: Tuesday, July 15, 2003 1:13 AM |Subject: Re: innodb file won't shrink | | |-BEGIN PGP SIGNED MESSAGE- |Hash: SHA1 | |There 's any chance that in the future every InnoDB table files from a |determinated database can be placed in the same directory of the mysql ( |MyISAM ) database ? . My point is that if you want to take a binary |backup of all databases in the mysql directory and you want to restore |just one of the databases , you could just copy the directory to the |correct place and thats all you have to do to restore a single |database.. I guess I'm getting tired to wait for the dump to |re-create all the indexes and stuff like that. | | |it will not be straightforward. The undo logs used to purge old versions of |rows and roll back uncommitted transaction will not be placed to those table |files. But if you let the database to be silent and run purge to completion, |then you will get clean tables you can restore later individually to the |database. | |Thanks In advance | |Saludos / Regards , | |Alvaro Avello. | | |Regards, | |Heikki | |walt wrote: | ||Heikki Tuuri wrote: || || ||On September 15th, 2003 you will be able to put every InnoDB table |into its ||own file. That should alleviate this kind of problem. || || ||Best regards, || ||Heikki Tuuri ||Innobase Oy ||http://www.innodb.com ||Transactions, foreign keys, and a hot backup tool for MySQL ||Order MySQL technical support from https://order.mysql.com/ || || ||That is great news! Will a single table be able to span several ||datafiles? || ||Thanks! ||walt || |-BEGIN PGP SIGNATURE- |Version: GnuPG v1.2.1 (GNU/Linux) |Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org | |iD8DBQE/EysJR9NZaw5tbc0RAnMHAJ4/ZxbE5sRwAjW8cDAcXOr6cbsiowCeJU14 |y/QT2dFY16n6L/OcJ0vCHyw= |=PCGI |-END PGP SIGNATURE- | | | | | -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Netscape - http://enigmail.mozdev.org iD8DBQE/FGZMR9NZaw5tbc0RAn6lAJ0filp6siUs+TBk7N2CP8Il6mgHvwCfV20j YBIWR33O86CMczdhleqvZKs= =iQu1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex select statement
Still having a problem with this. Still have one last thing that isn't working. This is MySQL 4.0.13. UPDATE table_tmp,table2_daily SET table_tmp.period_count = table_tmp.count - table2.count WHERE table_tmp.id = table2.id AND MAX(table2.timestamp); This is giving me: ERROR : Invalid use of group function. I am trying to update the period_count field for all the records in table_tmp, by setting the equal to the count from table_tmp MINUS the count from table2 where the id's match and it is the newest record in table2. Any ideas? Please don't tell me to how to do it 4.1! Please! :) Roy -Original Message- From: Rudy Metzger [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 3:49 AM To: Roy Walker; [EMAIL PROTECTED] Subject: RE: Complex select statement I never heard before that you can use a select statement in an arithmetic expression. Only ALL, ANY, MIN, MAX, =, (and some other which do not come to my mind quickly) should work. Anyway, it is considered a subselect and therefore does not work yet. However, in 4.1 you should also be able to formulate it like this: INSERT INTO table2( id, count, period_count, date ) SELECT t1.id, t1.count, t1.count - x.count, to_days(now()-1) FROM table1 t1, (SELECT count FROM table2 WHERE id = t1.id ORDER BY timestamp desc LIMIT 1) x This would also be the way how I would do it in a different DB (e.g. ORACLE). Cheers /rudy -Original Message- From: Roy Walker [mailto:[EMAIL PROTECTED] Sent: vrijdag 11 juli 2003 18:30 To: [EMAIL PROTECTED] Subject: RE: Complex select statement Eva, Thanx for your help. Still having an issue with this. I forgot to mention I am running MySQL 4.0.13. Tried both of these statements: INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1; INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id, count, (count - (SELECT count FROM table2 WHERE [EMAIL PROTECTED] order by timestamp desc limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1; They both give me an error for the 'SELECT count FROM table2 WHERE id=id1' section. Is this illegal syntax? Would this be considered a subselect? Thanx, Roy -Original Message- From: Paracková Eva, Ing [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2003 12:30 AM To: Roy Walker Subject: RE: Complex select statement try this: INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1; i am not sure, the subselect is ok. if the id1 alias will not work, then try to use a variable (... SELECT @id1:=id ... WHERE [EMAIL PROTECTED] ...). i just corrected a few syntax mistakes in your query. eva -Original Message- From: Roy Walker [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2003 7:19 AM To: [EMAIL PROTECTED] Subject: Complex select statement If anyone could tell me what I am doing wrong here, I would greatly appreaciate it. Have the following tables: table1: id, count table2: id, count, period_count, date, timestamp Trying to do the following; get all rows from table 1 and insert them into table2 while setting period_count to count.table1 minus the most recent entry for that id in count.table2, then set the date field to the previous day. Here goes: INSERT INTO table1 (id,count,period_count,date) SELECT id,count FROM table1, (count.table1 - (SELECT count FROM table2 WHERE MAX(timestamp) AND id.table2=id)) AS period_count, date='TO_DAYS(NOW() -1)'; Sorry if this is easy, but I could not find an example of any mathematic functions while doing an INSERT ... SELECT. Thanx, Roy -- 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: Complex select statement
As far as I know, MAX only returns the greatest row, so what you really want at the end is WHERE table_tmp.id=table2.id AND table2.timestamp=MAX(table2.timestamp) ~MJI Roy Walker wrote: Still having a problem with this. Still have one last thing that isn't working. This is MySQL 4.0.13. UPDATE table_tmp,table2_daily SET table_tmp.period_count = table_tmp.count - table2.count WHERE table_tmp.id = table2.id AND MAX(table2.timestamp); This is giving me: ERROR : Invalid use of group function. I am trying to update the period_count field for all the records in table_tmp, by setting the equal to the count from table_tmp MINUS the count from table2 where the id's match and it is the newest record in table2. Any ideas? Please don't tell me to how to do it 4.1! Please! :) Roy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I display images from a mySQL Database in a web page?
THis is kinda offtopic.. it depends on what frontend you are using to access mysql (php,java,perl,etc) .. You just need to pull the binary data and output it with the correct http headers and it will show up in a browser.. search the list for more info. www.php4.com has an example using php.. good luck. On Tue, 15 Jul 2003, Dan Anderson wrote: I have created a BLOB field to store images. Is there any way to embed them within HTML with something like: image start: jpeg /image Thanks in advance, Dan -- 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: How can I display images from a mySQL Database in a web page?
I think there is a way to insert binary image data in your html, but is there any particular reason you need to do that? First you would need to encode it in some ascii equivalent (check w3c.org, I think) ...and it would increase the size of your html page while rendering the browser's image cache useless... This will both increase the page's load time and your server's bandwidth requirements, as well as possibly being a pita to implement :-/ I would suggest you store the image filenames in the table, and paste that into your html... -Partap On 7/15/03 1:25 PM, Dan Anderson [EMAIL PROTECTED] wrote: I have created a BLOB field to store images. Is there any way to embed them within HTML with something like: image start: jpeg /image Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I display images from a mySQL Database in a web page?
I think there is a way to insert binary image data in your html, but is there any particular reason you need to do that? First you would need to encode it in some ascii equivalent (check w3c.org, I think) ...and it would increase the size of your html page while rendering the browser's image cache useless... This will both increase the page's load time and your server's bandwidth requirements, as well as possibly being a pita to implement :-/ I would suggest you store the image filenames in the table, and paste that into your html... -Partap On 7/15/03 1:25 PM, Dan Anderson [EMAIL PROTECTED] wrote: I have created a BLOB field to store images. Is there any way to embed them within HTML with something like: image start: jpeg /image Thanks in advance, Dan blob datatype. But again why do this. use a file system and put the file's address in the database. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I display images from a mySQL Database in a web page?
I think there is a way to insert binary image data in your html, but is there any particular reason you need to do that? Yes, my client's server is running with particularly restrictive PHP safe mode settings and has informed me that dynamic images are a /must have/. :: bangs head against wall. starts to bleed :: So, it is relatively easy to store images into a BLOB, and slightly tricky to chop them up into the packet size and reassemble them. But trying to figure out how to do this has boggled my mind and I appreciate all the help of the community. :) -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I display images from a mySQL Database in a web page?
On 15 Jul 2003 at 18:31, Dan Anderson wrote: I think there is a way to insert binary image data in your html, but is there any particular reason you need to do that? Yes, my client's server is running with particularly restrictive PHP safe mode settings and has informed me that dynamic images are a /must have/. :: bangs head against wall. starts to bleed :: Dynamic images don't require inserting binary data into your HTML. (It is possible to use 'data:' URLs to insert arbitrary data into your HTML, but very few broswers support it, and it's not relevant for your purposes). The image and the HTML will come from separate HTTP requests, so all that's necessary is for you to put the appropriate URLs into the HTML and set up a corresponding PHP program to return the data, with the Content-type: image/gif (or whatever) header, and possibly others, depending on what you're trying to do. Except for the process of retrieving the data from the database, this has nothing to do with MySQL, so questions related to HTML, PHP, and HTTP headers should be directed to another list. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
errors with source file
The file mysqlqui-win32-static-1.7.5-2.zip with file size of 37k downloaded from http://www.mysql.com/downloads/gui-mysqlgui.html is corrupt. Is there a mirror with a compiled windows xp compatible version somewhere? Pres. Cliff Murphy Hyperactive Media Group Static is for the laundry, Flash is for the net! (910) 298-8260 (910) 290-5180
passing multiple variables
I have a catalogue built and it passes a variable to the sample page that shows the larger pic on the page. But I want to pass a second variable to the page as well, can that be done. This variable is the simple line of text that talks about the product. This page does not access the database when loaded. It just uses the variable passed from the page before Thanks Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MATCH AGAINST () ('XXX' IN BOOLEAN MODE) SYNTEX ERROR
I have MySQL version 3.23 on Linux 8.0 . I am trying to do a full text search IN BOOLEAN MODE and I get an error. If I remove the IN BOOLEAN MODE option my query runs fine . Does it mean I have to upgrade to 4.0 to use this Search mode? Can somebody please confirm ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select, join, order assistance - simple
I have 2 tables similar to T1 --- ID | initial | T2 -- initial | full_name | I want output based on the T1.ID row which has a given ID=' X ' (If ID = 7 then pretend initial = initial3, as example) The first or last row of output to be initial | full_name expanding the T1.initial from T2 where T1.ID=' X ' then all subsequent rows from T2 In a rough sense would need to look like T1.initial3 | T2.full_name3 where ID=' 7 ' T2.initial2 | T2.full_name2 T2.initial1 | T2.full_name1 T2.initial4 | T2.full_name4 etc... Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MATCH AGAINST () ('XXX' IN BOOLEAN MODE) SYNTEX ERROR
At 19:28 -0400 7/15/03, Francis Van-Lare wrote: I have MySQL version 3.23 on Linux 8.0 . I am trying to do a full text search IN BOOLEAN MODE and I get an error. If I remove the IN BOOLEAN MODE option my query runs fine . Does it mean I have to upgrade to 4.0 to use this Search mode? Can somebody please confirm ? The MySQL Reference Manual confirms it: http://www.mysql.com/doc/en/Fulltext_Search.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex select statement
Alright, I am almost there, got one problem now. There are multiple entries for each id in table2. I need only the records with the newest timestamp for each id. Want to do something like this, but doesn't work: INSERT INTO table_tmp (id,count_new,count_old,date) SELECT table1.id, table1.count, table2.count, CURRENT_DATE() - 1 AS date FROM table1,table2 WHERE table2.id=table1.id AND table1.count!='Nope' OR 'NULL' AND timestamp=MAX(table2.timestamp) GROUP BY table2.id; This gives me: ERROR : Invalid use of group function So I tried something like: INSERT INTO table_tmp (id,count_new,count_old,date) SELECT table1.id, table1.count, table2.count, CURRENT_DATE() - 1 AS date FROM table1,table2 WHERE table2.id=table1.id AND table1.count!='Nope' OR 'NULL' GROUP BY table2.id HAVING MAX(timestamp); Which will return 0 results every time. Can't use 'ORDER BY desc limit 1' since that will only give me 1 entry. Anything else I can try? There has to be a way to get all the counts from table2 which have the newest timestamp for each id in the table! Please help! Roy -Original Message- From: Michael Iatauro [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 4:27 PM To: [EMAIL PROTECTED] Subject: Re: Complex select statement As far as I know, MAX only returns the greatest row, so what you really want at the end is WHERE table_tmp.id=table2.id AND table2.timestamp=MAX(table2.timestamp) ~MJI Roy Walker wrote: Still having a problem with this. Still have one last thing that isn't working. This is MySQL 4.0.13. UPDATE table_tmp,table2_daily SET table_tmp.period_count = table_tmp.count - table2.count WHERE table_tmp.id = table2.id AND MAX(table2.timestamp); This is giving me: ERROR : Invalid use of group function. I am trying to update the period_count field for all the records in table_tmp, by setting the equal to the count from table_tmp MINUS the count from table2 where the id's match and it is the newest record in table2. Any ideas? Please don't tell me to how to do it 4.1! Please! :) Roy -- 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: errors with source file
red fraggle wrote: The file mysqlqui-win32-static-1.7.5-2.zip with file size of 37k downloaded from http://www.mysql.com/downloads/gui-mysqlgui.html is corrupt. Is there a mirror with a compiled windows xp compatible version somewhere? Pres. Cliff Murphy Hyperactive Media Group Static is for the laundry, Flash is for the net! (910) 298-8260 (910) 290-5180 Cliff Try this link. I just downloaded and installed. It runs fine. http://www.mysql.com/downloads/download.php?file=Downloads%2Fmysqlgui%2Fmysqlgui-win32-static-1.7.5-2.zipmirror=http%3A%2F%2Fmirror.services.wisc.edu%2Fmysql%2F just copy and paste onto your browser's address line. It should start downloading. -- Peter K. Aganyo Eternal Designs Inc., +1(617)344-8023 (fax voicemail) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Keys
When I type: Show keys from [tablename]; I do not see foreign keys listed there and I cannot seem to find any command that will list foreign keys. Can anyone help? Also, I notice Innodb tables only store one file unlike MyISAM which stores 3. Are Innodb tables any slower than MyISAM since they store all data in a single file? I am looking to use Innodb for an application simply because of the referential integrity. Thanks in advance... Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
