Re: MySQL version 4.0.23 uses 99% of processor on FreeBSD 4.11
On Fri, 2006-07-14 at 09:47 -0300, Miles Thompson wrote: At 09:15 AM 7/14/2006, Jeremiah Foster wrote: Nearly once or twice a day the mysql daemon on our FreeBSD server has to be restarted since it is taking up most of the processor. That is about 3 hours and a billion rows examined. Is it likely that this is the source of my problems? I'd say YES. But first, how much time does it take if you remove the join conditions for the LIKEs? The time it takes after removing the two likes is 7 hours; 100 rows in set (7 hours 23 min 43.04 sec) There are two difficulties there: 1. Use of LIKE with wildcard search forces a table scan, although see [1] below. 2. The two LIKEs are joined by an OR so Stdnsv will be scanned until the first condition is met, and if it is fulfilled, then pl is scanned until the second condition is met. Full scan on each table. Alternately ... If the first condition for Stdnsv is not met, then OR has failed, but you will have a full table scan on the left hand side of the OR regardless. Second, have you indexes on the columns which are being compared? Yes, I believe when I ran EXPLAIN it showed the tables were indexed. I will run explain again to make sure. Your memory usage is probably pretty high as well, as MySQL tries to do as much work as it can in memory. I will look into investing in more memory. Have you tried this: SELECT pp.art_num, pp.showprice as price, pc.custom_price, pc.custom_name, pc.custom_delivery, pd.instock, Stdnsv.Description FROM product_tags pt, products_prices pp, products_dists pd, filter_categories fc, product_linking pl, cds_Stdnsv Stdnsv WHERE Stdnsv.ProdID = pp.art_num AND pc.art_num = pp.art_num AND pp.art_num = pl.art_num AND pp.art_num = pd.art_num AND fc.filter_type_id = 1 AND fc.filter_id = pl.filter_id AND (Stdnsv.Description != 'None' OR Stdnsv.Description != '') GROUP BY pp.art_num ORDER BY pp.showprice DESC LIMIT 100; If that gives weird or unexpected results, try whacking off AND conditions until it's fast, then think about why the query slows when they are added. Note the simple WHERE is an implicit LEFT JOIN. Also formulate time query, and time it, with (Stdnsv.Description LIKE '%Media%Center%' OR pl.art_num LIKE '%Media% Center%') as the WHERE. Apologies if this has been too pedantic. Hope this is helpful - Miles Thompson Miles, this has been extremely helpful, not at all pedantic. Thank you. [1] MySQL 4.0 does another optimisation on LIKE. If you use ... LIKE %string% and string is longer than 3 characters, MySQL will use the Turbo Boyer-Moore algorithm to initialise the pattern for the string and then use this pattern to perform the search quicker. This is taken from http://mysqld.active-venture.com/MySQL_indexes.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow mysql...
Ritesh Shetty wrote: Hello, My mysql server sometime just slows responding to database queriesIt is as slow as less than 1 per sec. But it speeds up by itself after some time , I have noticed that when it speeds up there are lot more MYsql thread than just one thread when it is slow. Sounds like you have some queries running that aren't indexed properly. Try enabling your slow-query-log and see what gets put into that, then you can work on fixing those queries. See http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help: PHP won't connect to MySQL
Kay C. Tien wrote: Hi All, The simple script I'm testing works on a Linux server. I got tired of having to upload and test them, so I decided to install MySQL on my computer, which is running Win XP. MySQL works fine on it's own, I've set the the database and etc., but nothing happened when I tested the page. So I googled online and found out that I was missing php_mysql.dll and libmysql.dll. So I copied them into the C:\WINDOWS\SYSTEM32 folder. Then modified php.ini in the C:\WINDOWS by uncommented the php_mysql.dll extension and changed to extension_dir to C:\WINDOWS\SYSTEM32. Rebooted and now I'm getting the following error: PHP Warning: PHP Startup: Unable to load dynamic library 'C:\WINDOWS\SYSTEM32\php_mysql.dll' - The specified procedure could not be found. in Unknown on line 0 You have a version mismatch in your dll's. One of them will be a dll for mysql 4 and one will be for mysql 5. You installed php5 right? The dll included with that probably won't talk to mysql 4, only mysql 5.. I'd suggest removing both and installing php5 and mysql5 from scratch. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql cluster so slow...
Xueron Nee wrote: Hi all, There is a table contains about 60,000 rows. where select from this table with 'order by xxx' statement, it is tooo slow. but if i do it without 'order by xxx', it works fine. Is there any tips and suggestion for me? Thanks! Add an index to the 'xxx' column? That's not a lot of information to go on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table 'mysql.host' doesn't exist
thomas Armstrong wrote: I created this very-simple '/etc/my.conf': [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/var socket=/tmp/mysql.sock --- But it doesn't still work. '/usr/local/mysql/var' hosts the data, but within 'mysql' subdirectory, there are only two files: -- -rw-rw 1 mysql root0 may 30 2005 func.MYD -rw-rw 1 mysql root 8877 may 30 2005 tables_priv.frm Where are 'host.frm', 'host.MYD' and 'host.MYI'? Hope you have a backup. The myd file contains all of your data, so without it, you're out of luck. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: arrays in stored procedures - pl. help
L P wrote: Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data to deal with (one address / one phone number) - with simple data types passed in as parameters. What alternatives / options do I have to accomplish storing multiple sets of data? insert into table(field1, field2, field3) values (value1, value2, value3), (value4, value5, value6); http://dev.mysql.com/doc/refman/5.1/en/insert.html Don't use arrays for storage, you'll lose a lot of performance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load-file() doesn't work
[EMAIL PROTECTED] wrote:
I'm using MySQL 3.23.36 on RH 7.1. I've created a table photos (in
database album) with the following colums:
ID NUM AUTO_INCREMENT PRIMARY KEY
IMAGE MEDIUMBLOB
I'm running mysql from the command line:
mysql -u root
'root' has all privileges.
The file I'm trying to load is /home/fleet/image.jpg
It *is* world readable.
INSERT INTO photos (image) VALUES (load_file('/home/fleet/image.jpg'));
Gives no errors; but all I get in the column is NULL.
SHOW TABLE STATUS photos;
Shows column width to be 30.
I've tried every permutation of this statement I can think of, with NO
success. MySQL Reference Manual for version 3.23.36. suggests:
mysql UPDATE photos
SET image=LOAD_FILE(/home/fleet/image.jpg)
WHERE id=1;
This produces exactly the same thing - no errors; but no data in the
column.
Does
show warnings;
produce anything after you try to import the file?
Does anything show up in your mysql logs (general or error logs) ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
On Wednesday 12 July 2006 20:58, Chris White wrote: performance?. From what I know of MySQL, not really, because MySQL does a good amount of work in memory. The only time I'd see disk access being a factor is if you had a large mass of swap/virtual memory. I have to play with 300 gig of data (and growing). MySQL cannot keep enough of the indexes in memory unfortunately - not when the index for one of the tables is 6 gig. Whether you use SATA, PATA or SCSI (on the back of FC), the answer for speed is spindle rotation speed and number of heads. There's a reason that the older HP9000 boxes used disk packs full of 9 GB drives - heads. SCSI has the advantage (for now at least) of being designed in a manner that lets it do 'things' faster. Oh, as a small example - the DB server attached to the SAN can pull data faster than my personal server, even though the personal server is only dealing with one request and the DB/SAN is dealing with hundreds per second (and the personal server is no slouch). Fun to watch all the SAN disk lights light up when that happens. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
Duncan Hill wrote: Oh, as a small example - the DB server attached to the SAN can pull data faster than my personal server, even though the personal server is only dealing with one request and the DB/SAN is dealing with hundreds per second (and the personal server is no slouch). Fun to watch all the SAN disk lights light up when that happens. What do you use on your SAN? We're looking at deploying a SAN on our Linux MySQL setup. Cory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqld-nt memory issue!
Hi to all, I am using MySql 5.0.15 windows version and java application to access the database using MySql JDBC driver. I come to one interesting issue regarding memory used by mysqld-nt service and displayed in the task manager. The service memory constantly increases as I write or read data from the database. Either if I closes connection from java or closes the application memory used by mysqld-nt service stays unchanged. Can anyone help me with this issue? The problem is when using application on PCs that are poor with RAM memory and mysqld-nt service memory don't frees properly. Can I INIT mysqld-nt servise from Java. I know I can stop and start service but I don't like this solution, BUT the best solution would be if someone could me provide the answer why mysqld-nt service behaves this was. Thanks for help Dusan
Random Table Lock
I am stumped by a table lock issue. First, some details. I am running MySQL 5.0.22 on an AMD 64 machine. mysqld -V outputs: /usr/local/libexec/mysqld Ver 5.0.22-debug-log for unknown-linux-gnu on x86_64 (Source distribution) uname -a: 2.6.8-11-amd64-generic #1 Wed Jun 1 00:42:47 CEST 2005 x86_64 GNU/Linux I compiled MySQL as follows ( from the docs ): CC=gcc CFLAGS=-O2 CXX=gcc CXXFLAGS=-O2 -felide-constructors \ -fno-exceptions -fno-rtti ./configure --prefix=/usr/local/mysql \ --with-debug --with-extra-charsets=complex The problem I am having is the following: Randomly, on any DB or table within, a lock will remain in place after an insert. The insert could be something very simple, but it will fail to unlock the table. Following this failure, any other query that needs to lock the table afterwards ( updates/inserts ) will fail and will queue up - visible when doing a show full processlist. Killing the PID of that particular INSERT doesn't do anything. In fact, the only way to kill it and unlock the table is to do a kill -9 on the mysqld process. This is what it looks like from within mysql: | 448 | root | localhost | shopDani | Query | 233 | update | insert into items set custID='X',item='y',qty='1' | I am not sure from where to start tackling this issue and my first step was to recompile MySQL with debug support. I then managed to catch it happening in the debug log but I'm afraid it's not offering more details: do_command: info: Command on socket (47) = 3 (Query) dispatch_command: query: insert into items set custID='XX',item='',qty='1' thr_lock: info: write_wait.data: 0x0 thr_lock: info: dellink: -1 r_locks: 0 w_locks: 1 mi_get_status: info: key_file: 311296 data_file: 217356 concurrent_insert: 1 unique_table: info: real table: store.items mysql_lock_have_duplicate: info: no duplicate found ha_myisam::start_bulk_insert: info: start_bulk_insert: rows 1 size 0 do_command: info: Command on socket (72) = 1 (Quit) There is more data but I thought only this was relevant to this particular insert. If anyone has any suggestions or clues, feel free to write back. Thank you, George -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP won't connect to MySQL
I´m having the same trouble a long time ago and still looking for the solution. Kay C. Tien [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hi All, The simple script I'm testing works on a Linux server. I got tired of having to upload and test them, so I decided to install MySQL on my computer, which is running Win XP. MySQL works fine on it's own, I've set the the database and etc., but nothing happened when I tested the page. So I googled online and found out that I was missing php_mysql.dll and libmysql.dll. So I copied them into the C:\WINDOWS\SYSTEM32 folder. Then modified php.ini in the C:\WINDOWS by uncommented the php_mysql.dll extension and changed to extension_dir to C:\WINDOWS\SYSTEM32. Rebooted and now I'm getting the following error: PHP Warning: PHP Startup: Unable to load dynamic library 'C:\WINDOWS\SYSTEM32\php_mysql.dll' - The specified procedure could not be found. in Unknown on line 0 I've checked the PATH in the System also, C:\WINDOWS\SYSTEM32 is there. So why does it work?? It's been a very frustrating day. Help anyone? Thanks. Kay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE VIEW xxxx
I'm using mysql on Linux and all is going well but I can't find the proper way to write a CREATE VIEW. I'm using 4.1 and is this function still in mysql? Karl Larsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to copy a large innodb table
Recently I deleted ~200.000.000 rows out of a history table. Still there are 20.000.000 rows in the table. So now I want to clear some discspace by copying the table, dropping the old one and renaming the copy afterwards. Is there another (faster) way to do that and how does one copy such a large table? Normally I would do create table new like table old; insert into new select * from old; But this runs into this error: The total number of locks exceeds the lock table size. So I wrote a shell script which creates the table and copies like this: insert into new select * from old limit 100 offset 0 insert into new select * from old limit 100 offset 100 insert into new select * from old limit 100 offset 200 and so on. But I'm not sure if this will produce an exact copy of my table and on top of that the seventeenth loop fails with the same error mentioned above. Any help would be appreciated. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to copy a large innodb table
Hi Dominic, Why not just use an OPTIMIZE TABLE ? This will map to an ALTER table command for an InnoDB table which will free the now unused space. From the manual at http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. and from http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html 14.2.14.3. Defragmenting a Table snip snip It can speed up index scans if you periodically perform a null ALTER TABLE operation: ALTER TABLE tbl_name ENGINE=INNODB That causes MySQL to rebuild the table. Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file. snip snip Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Monday, 17 July 2006 9:58 PM To: mysql@lists.mysql.com Subject: How to copy a large innodb table Recently I deleted ~200.000.000 rows out of a history table. Still there are 20.000.000 rows in the table. So now I want to clear some discspace by copying the table, dropping the old one and renaming the copy afterwards. Is there another (faster) way to do that and how does one copy such a large table? Normally I would do create table new like table old; insert into new select * from old; But this runs into this error: The total number of locks exceeds the lock table size. So I wrote a shell script which creates the table and copies like this: insert into new select * from old limit 100 offset 0 insert into new select * from old limit 100 offset 100 insert into new select * from old limit 100 offset 200 and so on. But I'm not sure if this will produce an exact copy of my table and on top of that the seventeenth loop fails with the same error mentioned above. Any help would be appreciated. Regards Dominik -- 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]
ON DELETE CASCADE question
Hello. I have a question about on delete cascade. If i have 2 tables such as this: Table1Table2 PrikeyPrikey Table2foreinkey name name description description Now if I delete a row from table1 that has a foreign key from table 2 should it delete the row in table 2? Thanks, James
Re: How to copy a large innodb table
Thank you very much. I did not know this command. Well at least I never looked up what it does. I'll give it a try and see how it works out. Why not just use an OPTIMIZE TABLE ? This will map to an ALTER table command for an InnoDB table which will free the now unused space. From the manual at http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW xxxx
On Monday 17 July 2006 13:26, Karl Larsen wrote: I'm using mysql on Linux and all is going well but I can't find the proper way to write a CREATE VIEW. I'm using 4.1 and is this function still in mysql? If the MySQL manual for 4.1 does not list views as a feature of that version, odds are it doesn't exist. Views are a V5+ feature. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help: PHP won't connect to MySQL
At 05:37 PM 7/17/2006 Monday, Chris wrote: Kay C. Tien wrote: Hi All, The simple script I'm testing works on a Linux server. I got tired of having to upload and test them, so I decided to install MySQL on my computer, which is running Win XP. MySQL works fine on it's own, I've set the the database and etc., but nothing happened when I tested the page. So I googled online and found out that I was missing php_mysql.dll and libmysql.dll. So I copied them into the C:\WINDOWS\SYSTEM32 folder. Then modified php.ini in the C:\WINDOWS by uncommented the php_mysql.dll extension and changed to extension_dir to C:\WINDOWS\SYSTEM32. Rebooted and now I'm getting the following error: PHP Warning: PHP Startup: Unable to load dynamic library 'C:\WINDOWS\SYSTEM32\php_mysql.dll' - The specified procedure could not be found. in Unknown on line 0 You have a version mismatch in your dll's. One of them will be a dll for mysql 4 and one will be for mysql 5. You installed php5 right? The dll included with that probably won't talk to mysql 4, only mysql 5.. I'd suggest removing both and installing php5 and mysql5 from scratch. That would explain it. I'll try that. Thanks. Kay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load-file() doesn't work
Fleet, I'd also humbly suggest that the version you're using is VERY
old and you should update to at least the latest 3.23 (3.23.58 I
think), or 4.x, or maybe even 5.0 (production since last Fall).
Untold numbers of bugs fixed since 3.23.36.
Dan
On 7/17/06, Chris [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] wrote:
I'm using MySQL 3.23.36 on RH 7.1. I've created a table photos (in
database album) with the following colums:
ID NUM AUTO_INCREMENT PRIMARY KEY
IMAGE MEDIUMBLOB
I'm running mysql from the command line:
mysql -u root
'root' has all privileges.
The file I'm trying to load is /home/fleet/image.jpg
It *is* world readable.
INSERT INTO photos (image) VALUES (load_file('/home/fleet/image.jpg'));
Gives no errors; but all I get in the column is NULL.
SHOW TABLE STATUS photos;
Shows column width to be 30.
I've tried every permutation of this statement I can think of, with NO
success. MySQL Reference Manual for version 3.23.36. suggests:
mysql UPDATE photos
SET image=LOAD_FILE(/home/fleet/image.jpg)
WHERE id=1;
This produces exactly the same thing - no errors; but no data in the
column.
Does
show warnings;
produce anything after you try to import the file?
Does anything show up in your mysql logs (general or error logs) ?
--
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: ON DELETE CASCADE question
If I understand your question correctly - and I'm not sure I do - Table 2 is the parent and Table 1 is the child. In other words, there is a one-to-many relationship between the parent, Table 2, and the child, Table 1. Therefore, if the parent table, Table 2, contains one row for each department of a company and the child table, Table 1, contains one row for each employee, we would have a situation like this: Table 2 - DEPARTMENT === DeptnoDeptname A00Administration B01Sales C01Manufacturing Table 1 - EMPLOYEE = EmpnoDeptnoName 100A00Smith 101B01Brown 102C01Wong 103A00Green 104A00White 105C01Ferguson If I'm following you so far, you're asking what happens in the DEPARTMENT table if you delete an employee from the EMPLOYEE table? For example, if you delete employee 105 or even employee 101 from the EMPLOYEE table, will there be any impact on the DEPARTMENT table? The answer is _NO_. The DELETE rule between a parent table and its child table controls what happens in the _child_ table if a row is deleted from the _parent_ table; deleting a row in the child table has no effect on the parent table. Therefore, if you delete employee 105 from the EMPLOYEE table, the only effect is that employee 105 is removed from the EMPLOYEE table; there is no effect of any kind on the DEPARTMENT table. Ditto for employee 101 who is the only employee in department B01: only the employee 101 row is removed and there is no impact on the DEPARTMENT table at all. If you want to see the DELETE rule between the tables take effect, you need to delete a row from the _DEPARTMENT_ table. If the DELETE rule in effect is CASCADE, as you have proposed, then deleting the B01 row from DEPARTMENT will also cause the deletion of all B01 rows in the EMPLOYEE table. By the same token, deleting the C01 row from the DEPARTMENT table will cause the deletes of _all_ of the C01 rows in the EMPLOYEE table. -- Rhino - Original Message - From: James Sherwood [EMAIL PROTECTED] To: mysqllist mysql@lists.mysql.com Sent: Monday, July 17, 2006 8:44 AM Subject: ON DELETE CASCADE question Hello. I have a question about on delete cascade. If i have 2 tables such as this: Table1Table2 PrikeyPrikey Table2foreinkey name name description description Now if I delete a row from table1 that has a foreign key from table 2 should it delete the row in table 2? Thanks, James No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 2006-07-11 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 2006-07-11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load-file() doesn't work [SOLVED]
SHORT SYNOPSIS:
The statement
INSERT INTO table (blob_column) VALUES
(load_file('/home/somebody/image.jpg'));
Produces no error; but fails to load the image file.
SOLUTION:
The image file (or other binary file, I assume) MUST reside in / or /tmp,
ie /image.jpg or /tmp/image.jpg. (At least in MySQL 3.23.36)
I *hope* this is a bug!
- fleet -
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load-file() doesn't work [SOLVED]
Hi Fleet
[EMAIL PROTECTED] wrote:
SHORT SYNOPSIS:
The statement
INSERT INTO table (blob_column) VALUES
(load_file('/home/somebody/image.jpg'));
Produces no error; but fails to load the image file.
SOLUTION:
The image file (or other binary file, I assume) MUST reside in / or /tmp,
ie /image.jpg or /tmp/image.jpg. (At least in MySQL 3.23.36)
I *hope* this is a bug!
- fleet -
Just to check, is the /home partition actually mounted on the server,
and at the same place ?
In other words, does /home/fleet/image.jpg exists :
- when seen from the host running mysql
- when seen from the host running mysqld
Marc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load-file() doesn't work [SOLVED]
On Mon, 17 Jul 2006, Marc Alff wrote: Just to check, is the /home partition actually mounted on the server, and at the same place ? In other words, does /home/fleet/image.jpg exists : - when seen from the host running mysql - when seen from the host running mysqld In answer to the above: to the best of my knowledge. I haven't a clue how to check that. I'm a user, not a tech. I have another small database that produces an html output. It places it in my $HOME directory without any specific path instructions; so I'd have to say mysql and mysqld *do* see the directories. mysql is running on my home PC. This particular PC is about 5 years old. /home/fleet is my $HOME directory. I'm launching mysql from $HOME using: [~]$ mysql -u root mysql was installed (by someone who presumably knew what they were doing) when I purchased the computer. I've never used it before now. - fleet - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help: PHP won't connect to MySQL
At 05:37 PM 7/17/2006 Monday, Chris wrote: Kay C. Tien wrote: Hi All, The simple script I'm testing works on a Linux server. I got tired of having to upload and test them, so I decided to install MySQL on my computer, which is running Win XP. MySQL works fine on it's own, I've set the the database and etc., but nothing happened when I tested the page. So I googled online and found out that I was missing php_mysql.dll and libmysql.dll. So I copied them into the C:\WINDOWS\SYSTEM32 folder. Then modified php.ini in the C:\WINDOWS by uncommented the php_mysql.dll extension and changed to extension_dir to C:\WINDOWS\SYSTEM32. Rebooted and now I'm getting the following error: PHP Warning: PHP Startup: Unable to load dynamic library 'C:\WINDOWS\SYSTEM32\php_mysql.dll' - The specified procedure could not be found. in Unknown on line 0 You have a version mismatch in your dll's. One of them will be a dll for mysql 4 and one will be for mysql 5. You installed php5 right? The dll included with that probably won't talk to mysql 4, only mysql 5.. I'd suggest removing both and installing php5 and mysql5 from scratch. Well, I reloaded both new versions. Still the same error! What gives?! sigh Kay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNODB Question
Howdy all, I'm trying to clean up a development server so that we can retask it. I tried dropping a very large but no longer necessary database that uses INNODB tables exclusively. Unfortunately the disk usage on the box didn't change. We're using a completely default MySQL 5.0.15 install on the box (a small centos linux box) so we don't have innodb_file_per_table set in a my.cnf file. So I've got two basic questions... First, how can I get rid of the current ibdata file? It's taking up a lot of space. Do any of the mysql databases (information schema and mysql) use INNODB tables? What's the correct procedure for freeing up this disk space? Second, are there still issues with innodb_file_per_table and indexes or has that been eradicated? It seems like it was a problem early on in the mysql 4.1 life cycle. I just wanna be sure. Also, Does innodb_file_per_table enhance performance (even if the tablespaces are all on the same physical disk)? Just curious. Cheers, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with double data
Hi, i'm sorry for my english, i'm from México and i'm gonna do my best, i've made a store procedure but i have some problems becouse when a make a simple operation with double datas the result is not what i wait. look cantidad_origen = 30.5 cantidad_ = 30.4 suma = 0 UPDATE detalle_tanque set cantidad = ( cantidad_origen - cantidad_ - suma ) where id_detalle_transaccion = id_detalle_transaccion_origen and ctl_no=ctl_no_origen; cantidad = 0.101 i don't know what im waiting cantidad = 0.1 thanks any help will be good for me -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help: PHP won't connect to MySQL
Kay C. Tien wrote: At 05:37 PM 7/17/2006 Monday, Chris wrote: Kay C. Tien wrote: Hi All, The simple script I'm testing works on a Linux server. I got tired of having to upload and test them, so I decided to install MySQL on my computer, which is running Win XP. MySQL works fine on it's own, I've set the the database and etc., but nothing happened when I tested the page. So I googled online and found out that I was missing php_mysql.dll and libmysql.dll. So I copied them into the C:\WINDOWS\SYSTEM32 folder. Then modified php.ini in the C:\WINDOWS by uncommented the php_mysql.dll extension and changed to extension_dir to C:\WINDOWS\SYSTEM32. Rebooted and now I'm getting the following error: PHP Warning: PHP Startup: Unable to load dynamic library 'C:\WINDOWS\SYSTEM32\php_mysql.dll' - The specified procedure could not be found. in Unknown on line 0 You have a version mismatch in your dll's. One of them will be a dll for mysql 4 and one will be for mysql 5. You installed php5 right? The dll included with that probably won't talk to mysql 4, only mysql 5.. I'd suggest removing both and installing php5 and mysql5 from scratch. Well, I reloaded both new versions. Still the same error! What gives?! sigh Did you make sure there were no dll's left after the uninstall? ie look for libmysql.dll and php_mysql.dll in all folders in your path. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with double data
Obed Soto Déctor wrote: Hi, i'm sorry for my english, i'm from México and i'm gonna do my best, i've made a store procedure but i have some problems becouse when a make a simple operation with double datas the result is not what i wait. We can't tell much without seeing at least the stored procedure and the table schema(s). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with double data
-Original Message- From: Obed Soto Déctor [mailto:[EMAIL PROTECTED] Sent: Monday, July 17, 2006 7:28 PM To: mysql@lists.mysql.com Subject: problem with double data Hi, i'm sorry for my english, i'm from México and i'm gonna do my best, i've made a store procedure but i have some problems becouse when a make a simple operation with double datas the result is not what i wait. look cantidad_origen = 30.5 cantidad_ = 30.4 suma = 0 UPDATE detalle_tanque set cantidad = ( cantidad_origen - cantidad_ - suma ) where id_detalle_transaccion = id_detalle_transaccion_origen and ctl_no=ctl_no_origen; cantidad = 0.101 Looks like your standard floating-point rounding error. See http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html which says: A double-precision floating-point number is accurate to approximately 15 decimal places. If I counted right, the errant 1 at the end is in place 15, so that is accurate to approximately 15 places. Tim i don't know what im waiting cantidad = 0.1 thanks any help will be good for me -- http://www.obed.org.mx --- blog -- 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]
Incremental Backup
Hi ALL I would like to know following 2 things. 1) if i would like to take mysql incremental backup weekly cp /var/lib/mysql/ /backup/mysqlbak/ how can i do it by above command as i would like to run cron weekly for take mysql backup. i would like to take backup using cp command only as we have more then 1000 databases on one server. IS THERE ANY BETTER WAY TO DO THIS Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incremental Backup
Kaushal Shriyan wrote: Hi ALL I would like to know following 2 things. 1) if i would like to take mysql incremental backup weekly cp /var/lib/mysql/ /backup/mysqlbak/ how can i do it by above command as i would like to run cron weekly for take mysql backup. i would like to take backup using cp command only as we have more then 1000 databases on one server. IS THERE ANY BETTER WAY TO DO THIS Thanks and Regards Kaushal Hi - The following, has been my experience: A) That will not work. B) Depending on the OS, this may never complete, as files may constantly still be written to C) Depending on the OS, cp may not be able to take all of those directories for those 1000's of databases as arguments D) If you were doing that, you'd use 'cp -r' The best way to do it, and for maximum portability, would either be to create a replication slave which all this data would replicate to, allowing you to have pretty much a hot spare if configured properly, and alternatively using mysqldump so that you would not have to bring the server down, aside from maybe some row or table locking while the dump was running - which is completely subject based on how you're currently running. Perhaps others here can school me on this a bit, as well, and provide some other options. However, I thought I could at least give this one a shot :) Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
