Re: Sum for time
i dont think its possible. you can do it by using time_to_sec() function of mysql and then sec_to_time after addition Hope that helps Nitin - Original Message - From: Elly Wisata [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 19, 2004 10:08 AM Subject: Sum for time Hi *, Can somebody show me how to sum for time, my format is hh:mm:ss, need to do sum for the time as we usually sum a field with integer type. But I am not sure sum can use for time format. Thanks in advance ~Elle~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Guru's advice needed ........[Security: SQL injection]
From: Tariq Murtaza [EMAIL PROTECTED] Can someone shed some light on how SQL injection attack occurs when *magic_quotes_gpc *isON and how it prevents when its OFF. To my understanding apostrophise are escaped automatically in POST/GET/COOKIE when its ON, so how it tends towards SQL Injection. magic_quotes_gpc ON is supposed to do an addslashes automatically for all get, post and cookie data. *What is the best practices handling 'quotation marks' in input string and how to prevent SQL injection. The best way to prevent SQL injection is to check user input yourself. Never, ever trust any data from an external source. Check numerical data: make sure it's numerical and within the range you defined. Check string data: make sure it contains the characters you support and filter out any other characters, make sure it meets the other requirements you defined (size, etc.). If necessary modify the data or reject it completely. I never use user input to include a script just like that, but always verify it first to make sure it's in the list of scripts that can be included... If you want to supply free text search than you can easily filter out a list of punctuation characters that are not supported by the free text search you implemented. If you filter out enough it will render an SQL query that was posted to your script invalid, and effectively prevent an SQL injection attack. Happy coding! Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count all rows if limit by?
Hi all! Is there a simple way to get the total number of rows a result _should_ have had if no where-clause where present? Without doing a second query? Sincerely Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Guru's advice needed ........[Security: SQL injection]
Hi Jigal, others, Can someone shed some light on how SQL injection attack occurs when *magic_quotes_gpc *isON and how it prevents when its OFF. To my understanding apostrophise are escaped automatically in POST/GET/COOKIE when its ON, so how it tends towards SQL Injection. magic_quotes_gpc ON is supposed to do an addslashes automatically for all get, post and cookie data. *What is the best practices handling 'quotation marks' in input string and how to prevent SQL injection. The best way to prevent SQL injection is to check user input yourself. Never, ever trust any data from an external source. What about using parameters? How are they handled in MySQL? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count all rows if limit by?
From: Victor Spång Arthursson [EMAIL PROTECTED] Is there a simple way to get the total number of rows a result _should_ have had if no where-clause where present? Without doing a second query? Example from http://www.mysql.com/doc/en/Information_functions.html : mysql SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id 100 LIMIT 10; mysql SELECT FOUND_ROWS(); Available since 4.0.0.; in versions prior to 4.1.0. this didn't work with LIMIT 0. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count all rows if limit by?
Victor Sp?ng Arthursson [EMAIL PROTECTED] wrote: Hi all! Is there a simple way to get the total number of rows a result _should_ have had if no where-clause where present? Without doing a second query? If you want to get total number of rows in the table (without WHERE and without LIMIT) you should execute SELECT COUNT(*) FROM table_name; If you want to obtain number of rows the statement would have returned without the LIMIT clause, you can specify SQL_CALC_FOUND_ROWS keyword in the SELECT statement and then use FOUND_ROWS() function: http://www.mysql.com/doc/en/Information_functions.html -- 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]
Key Buffer Size
Hi, Can somebody tell me whether this is good or bad: Our mysql server has key_buffer_size = 402,653,184 (without the commas) The MySQL administrator tool indicates a 100% usage most of the time, and 362,324,992 as current usage within 24 hours which builds up very fast. Are there memory leaks for threads on my system? Or is this normal behaviour? Restarting the mysql server clears the above. I can't seem to find much information on the mysql site or documentation as to what the ideals are. Running MySQL 4.1.1 Dual Xeon 2.4GhZ 1 Gig Ram Redhat 9 Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: writing queries to get distinct results
Casey Sheridan [EMAIL PROTECTED] wrote: I have a table that has employee names, pay rates, and unique IDs. I want to select all of the distinct employee names, and if there are two employees with the same name, I want to be able to choose only one; the one with the highest pay rate. If there are two identical employee names with the same pay rate, I want to select the one that has the lowest unique ID number. Can anyone help me on how to write this query? Thanks! If your version of MySQL supports subqueries (=4.1.0): SELECT name, pay_rate, MIN(id) FROM table WHERE (name, pay_rate) IN (SELECT name, MAX(pay_rate) FROM table GROUP BY name) GROUP BY name, pay_rate; Otherwise you can't do it with one query. You can rewrite the above query using temporary table and JOIN. CREATE TEMPORARY TABLE tmp SELECT name, MAX(pay_rate) pay_rate FROM table GROUP BY name; SELECT table.name, table.pay_rate, MIN(table.id) FROM table, tmp WHERE table.name=tmp.name AND table.pay_rate=tmp.pay_rate GROUP BY table.name, table.pay_rate; -- 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: Key Buffer Size
i did: http://www.mysql.com/doc/en/Server_system_variables.html -Original Message- From: Terence [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 3:11 PM To: [EMAIL PROTECTED] Subject: Key Buffer Size Hi, Can somebody tell me whether this is good or bad: Our mysql server has key_buffer_size = 402,653,184 (without the commas) The MySQL administrator tool indicates a 100% usage most of the time, and 362,324,992 as current usage within 24 hours which builds up very fast. Are there memory leaks for threads on my system? Or is this normal behaviour? Restarting the mysql server clears the above. I can't seem to find much information on the mysql site or documentation as to what the ideals are. Running MySQL 4.1.1 Dual Xeon 2.4GhZ 1 Gig Ram Redhat 9 Thanks -- 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]
Select Performance
A table (Table1) with the relevant indices (e.g. Field1Idx, Field2Idx) exists. Running a query like: Select * from table1 where Field1Idx Like 'Value%' returns result in expected timing scale, so does the query: Select * from table1 where Field2Idx Like 'Value%' The problem (very slow) occurs when the query is run on combination of these fields: Select * from table1 where (Field1Idx Like 'Value%' or Field2Idx Like 'Value%') We have followed everything by the book, the table includes another index on (Field1, Field2), running Explain command on the last query details that it uses the index (Field1, Field2), it's just too slow (minutes we are talking about). Anything we have not done for this delay to occur? please enlighten. regards ___ Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Performance
Thanks, but this is not what we expected. This means to change the SQL command in the application. Our understanding from the manual and other relational DBs is that, it's suffice for existence of an index on the field in the criteria (where clause) to run at optimal speed. regards --- Harald Fuchs [EMAIL PROTECTED] wrote: In article [EMAIL PROTECTED], A Z [EMAIL PROTECTED] writes: The problem (very slow) occurs when the query is run on combination of these fields: Select * from table1 where (Field1Idx Like 'Value%' or Field2Idx Like 'Value%') Try a UNION query: SELECT * FROM table1 WHERE Field1Idx LIKE 'Value%' UNION SELECT * FROM table1 WHERE Field2Idx LIKE 'Value%' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
install issues
Hello people, I made a mistake and installed mysql5 under /usr/local :(. Now I want to remove the files installed by make install how can I do that? regards /Jonas
Re: Select Performance
Thanks, but this is not what we expected. This means to change the SQL command in the application. Our understanding from the manual and other relational DBs is that, it's suffice for existence of an index on the field in the criteria (where clause) to run at optimal speed. You'll probably get more suggestions if you include in your e-mail: - MySQL version - table layout - query (which you did, but just for the record) - output of explain Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Going from 3.23 to 4 - sql statement errors
Mike R [EMAIL PROTECTED] wrote: Sorry if I posted this twice - I had some email issues yesterday, and I didn't know if this made it to the list: I am using php with mysql, and after moving my tables to a new server, I am suddenly getting this error: Query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'this = 'No', fp_ext = 'No', username = 'jreft', password = 'sna I find this rather generic - does anyone have any suggestions as to where I can start to figure out why MySQL is erroring out a statement that worked fine with 3.23, but doesn't with 4? The fields are varchar types, a few with default values. Considering there isn't anything that unusual, I'm having a hard time figuring this one out! Mike, provide the whole query. -- 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: install mysql create databases
zeineb sellami [EMAIL PROTECTED] wrote: we are faced to a problem where creating databases the installation is succeded but we can't create the databases correctly the instruction ./scripts/mysql_install_db shows prepare installing database inspite of creating db table what is the solution? Did you receive any error? Please, be more detailed. -- 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: Load data + odbc ?
I'm soryy for the delay, but i've tried to resolve this on my own. Not a chance. h = SqlConnect(transfer) r=sqlexec(h,vQuery) if r0 ? aError( laErrors ) _cliptext = laErrors[1,2] endif Paste the result here. The error i get on the BoxB, is OF COURSE!! : Connectivity error: [MySQL][ODBC 3.51 Driver][mysqld-3.23.52-nt]The used command is not allowed with this MySQL version Although it works from the other client (BoxA) !!! I've put the local-infile=1 in my.ini on the BoxC(the server), i've created c:\my.cnf on the client BoxB and NADA! the same error. I think it's something from the client side (BoxB) because with the same server it works from the BoxA, but i cant figure it out!! - Original Message - From: Carl Karsten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 12, 2004 3:57 PM Subject: Re: Load data + odbc The first test i've done was on, lets say, BoxA and it worked just fine. Than, i said,'ok, lets install the client on another WKS' that is BoxB. And from BoxB it's not working. I've got 2 workstations and a server on which i have Mysql 3.23.52 - nt. Lets call the workstations BoxA and BoxB, and the server BoxC. The clients are build in Visual FoxPro and use MyODBC for connecting to server. From both WKS statements like select, insert, update, delete work just fine, but when i want to 'LOAD DATA local INFILE' it works OK only from one WKS. Here's my code open database opreluare CREATE CONNECTION transfer ; DATASOURCE MYSQLSERVER ; USERID incarc PASSWORD incarc ; DATABASE OCUPAT vQuery=LOAD DATA local INFILE 'c:/ocupat/preluare/baza.txt' into table baza FIELDS TERMINATED BY '' r=sqlexec(sqlconnect(transfer),vQuery) and r is -1 after that. h = SqlConnect(transfer) r=sqlexec(h,vQuery) if r0 ? aError( laErrors ) _cliptext = laErrors[1,2] endif Paste the result here. Carl K -- 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: Innodb logfiles timestamp question
Yes that makes sense, thanks Heikki. I monitored the ib_logfiles some more and see the cycling between pairs logfile0+logfile1 and logfile0+logfile2. Heikki Tuuri wrote: John, - Original Message - From: John Thorpe [EMAIL PROTECTED] I am running 4.0.4 using innodb tables on a linux box. My innodb config is set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=500M set-variable = innodb_log_buffer_size=30M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 My question has to do with innodb's usage of the redo log files. Currently they are: 524288000 Mar 11 11:19 ib_logfile0 524288000 Mar 3 08:59 ib_logfile1 524288000 Mar 11 11:19 ib_logfile2 I always see them timestamped like this, with two having identical times, or the same within a minute or two. The particular pair of the three having the same timestamp varies. the checkpoint stamp fields are in the first ib_logfile. Does that explain the observed phenomenon? I was under the impression that mysql would cycle through these logfiles - e.g. write to logfile0 until it is full, then switch to logfile1 until full, then logfile2.., then logfile0, etc. Thanks, John Best regards, Heikki Tuuri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL To Change Last Approvers
You could use a temporary table for that: CREATE TEMPORARY TABLE tmp ( deptID INT NOT NULL, appSeq INT NOT NULL ) AS SELECT deptID, max(appSeq) AS appSeq FROM approvers GROUP BY deptID; UPDATE approvers, tmp SET approvers.appName = 'Sting Ray' WHERE approvers.appName = 'Joe Bass' AND approvers.deptID = tmp.deptID AND approvers.appSeq = tmp.appSeq ; Thanks! It's funny sometimes how these things seem so obvious --- AFTER somebody's shown the way! Randolph Randy L. Chrismon [EMAIL PROTECTED] Ph. 732-452-7610 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I upload dumped data
Hi List, Once I have dumped my database using mysqldump how can I upload the data again using the mysql command line (MySQL in ansi mode)? Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I upload dumped data (fwd)
Hi List, Once I have dumped my database using mysqldump how can I upload the data again using the mysql command line (MySQL in ansi mode)? Thanks. -- Andre Matos [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 upload dumped data
Andre MATOS [EMAIL PROTECTED] wrote: Hi List, Once I have dumped my database using mysqldump how can I upload the data again using the mysql command line (MySQL in ansi mode)? mysql -u user_name -p database_name dump_file.sql -- 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: How can I upload dumped data
I tried it, but it didn't work. ERROR 1217 at line 3483: Cannot delete or update a parent row: a foreign key constraint fails I am using InnoDB... that's my problem... Thanks. Andre On Fri, 19 Mar 2004, Egor Egorov wrote: Andre MATOS [EMAIL PROTECTED] wrote: Hi List, Once I have dumped my database using mysqldump how can I upload the data again using the mysql command line (MySQL in ansi mode)? mysql -u user_name -p database_name dump_file.sql -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Performance
Yes, you need to change your query for optimal speed. Prior to 5.0.0, mysql chooses the best index to use, one per table. Your indexes don't help for this query, because no one index does the job. Why? Because you are selecting on one column OR another column. If you are using at least mysql 4.0, then Harald Fuch's solution is the best you can do. Each side of the union will use the appropriate index and be fast. See How MySQL Uses Indexes http://www.mysql.com/doc/en/MySQL_indexes.html in the manual for more. 5.0.0 has a new method to use both of your indexes in an OR query like this and merge the results. See How MySQL Optimizes OR Clauses http://www.mysql.com/doc/en/OR_optimizations.html for the details. Michael A Z wrote: Thanks, but this is not what we expected. This means to change the SQL command in the application. Our understanding from the manual and other relational DBs is that, it's suffice for existence of an index on the field in the criteria (where clause) to run at optimal speed. regards --- Harald Fuchs [EMAIL PROTECTED] wrote: A Z [EMAIL PROTECTED] writes: The problem (very slow) occurs when the query is run on combination of these fields: Select * from table1 where (Field1Idx Like 'Value%' or Field2Idx Like 'Value%') Try a UNION query: SELECT * FROM table1 WHERE Field1Idx LIKE 'Value%' UNION SELECT * FROM table1 WHERE Field2Idx LIKE 'Value%' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Key Buffer Size
Hi, huge-my.cnf specifies that total memory usage be less than 2 G. What is meant by total memory usage ? Is it some of key_buffer + innodb_buffer_pool_size + record_buffer or does the total memory usage not include innodb_bbufer_size. How are these buffers used ? Are they obtained using malloc in the mysql process map in begining ? When I start mysql using pmap I can see a large chunk corresponding to my innodb_buffer_pool_size configuraion but nothing similar to key_buffer ?? I would appreciate any pointers as to what is mysql doing with memory and how is it using the my.cnf memory parameters. TIA VICK __ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Going from 3.23 to 4 - sql statement errors
Thanks for responding! :) UPDATE sites SET start_date = '$MySQLDate', domain = '$host_edit', host_plan = '$host_plan_edit', ssl = '$ssl_edit', fp_ext = '$fp_ext_edit', username = '$username_edit', password = '$password_edit', fp_password = '$fp_password_edit', cust_comments = '$cust_comments_edit', customer_id = '$customer_id_edit', shared = '$shared_edit', ip_address = '$ip_address_edit', server_name = '$server_name_edit', site_id = '$site_id_edit', host_price = '$host_price_edit' WHERE domain = '$domainhosted' -Mike -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 8:21 AM To: [EMAIL PROTECTED] Subject: Re: Going from 3.23 to 4 - sql statement errors Mike R [EMAIL PROTECTED] wrote: Sorry if I posted this twice - I had some email issues yesterday, and I didn't know if this made it to the list: I am using php with mysql, and after moving my tables to a new server, I am suddenly getting this error: Query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'this = 'No', fp_ext = 'No', username = 'jreft', password = 'sna I find this rather generic - does anyone have any suggestions as to where I can start to figure out why MySQL is erroring out a statement that worked fine with 3.23, but doesn't with 4? The fields are varchar types, a few with default values. Considering there isn't anything that unusual, I'm having a hard time figuring this one out! Mike, provide the whole query. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AddressBook CMS
Hi Everyone, Fisrtoff, I know nothing about databases and even less about web design. What I do know is that I want to move my Outlook contacts (~10,000) to a real database :) I've looked at FileMaker Pro and MyGroupWare and the likes but I just want something simple where I can export my contacts and sort them into templates. And those have either to much cost attached to them or simply try to offer to many features for what I want. Can someone point me in the right direction? I know I need to learn MySQL and that I will (I'm getting more and more comfortable with PHPmyAdmin). My main concern is how to access and manage the content in a user-friend way. Cheers, Phil
Re: --replicate-do-table usage example
Andrew Pasetti wrote: I'm a bit new to mysql replication. Can someone please explain how to implement the --replicate-do-table start up option? Replication is up and running, but I would like to limit the replication to just two specific tables in one database only. Using mysql v4.0.18 Linux/x86 Your advice will be greatly appreciated. replicate-do-table=db.table1 replicate-do-table=db.table2 -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with 4.0.18 and mysqldump
Jochen Kaechelin wrote: /usr/bin/mysqldump: Got error: 2003: Can't connect to MySQL server on '212.87.142.236' (111) when trying to connect Permissions are ok and I can modify the complete db with my php scripts. where's the error? Check - that MySQL server is running - that you use correct TCP/IP port when you trying to connect - that MySQL server is not running with --skip-networking option. - MySQL-Server IS running - I use the standard port 3306 - skip-networking ist not set I can run the script without any problems on the machine the db running - I can you phpmyadmin to dump the database - everything is working well - but I'am not able to dump db with mysqldump from an other machine - by the way: I run debian/sid. Check the firewall rules. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't access mysql after kernel upgrade
Hi all. I've just upgraded the kernel on my server from v. 2.4.21 to 2.4.25 (The original had some problems), but now I can't access my database :( If I revert back to the old kernel then MySQL works fine again. Here's some errors and stuff: # /etc/init.d/mysqld status mysqld (pid 1153 1124) is running... # /etc/init.d/mysqld stop Stopping MySQL:[FAILED] # cat /var/log/mysqld.log Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x40973774, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80e43dc 0x4007b8f8 (nil) New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash Uh I'm really not sure what to do from here. I read through the page, but there doesn't seem to be anything suggestive of my problem... Can anybody pass on any hints on what I should be doing next? Thanks, ~James~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems compiling a mysql tool
Jason Unrein wrote: Before I start, this is a compile problem (or so I think) and from what little I read in this forum, it looks ok to post. If not and you know the proper place to post, please let me know. Now for the good stuff I'm attempting to write a simple tool in C that needs to be compiled statically with mysql client support. It needs this (correct me if I'm wrong in what I say) because it will be used on the majority of Redhat'S and SuSE's OSes. Instead of compiling for each one of their glibc's and requiring that the mysql shared libraries are installed, I want to compile it once, statically for all of them (excluding the x86_64 and ia64). When I run CC = gcc CFLAGS = -O2 -Wall -static ALL: gcc -O2 -Wall -static -o logger scsiInquiry.o sg_err.o sgScan.o logger.o /usr/lib/mysql/libmysqlclient.a I get the following -- /usr/lib/mysql/libmysqlclient.a(mf_pack.o)(.text+0x9ac): In function `expand_tilde': : Using 'getpwnam' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking /usr/lib/mysql/libmysqlclient.a(libmysql.o)(.text+0x7a7): In function `read_user_name': : Using 'getpwuid' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking /usr/lib/mysql/libmysqlclient.a(mf_pack.o)(.text+0x9b9): In function `expand_tilde': : Using 'endpwent' in statically linked applications requir Jason: I share with your your dislike of shared libraries. I guess some people have a hard time with the idea that you may want to compile a binary and have it just run everywhere with no fuss :-) To make a long story short, the easiest way to solve the problem, as it seems to me - get MySQL source CXX=gcc CC=gcc ./configure --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --prefix=/opt/my-own-mysql make make install Then compile with -I/opt/my-own-mysql/include/mysql and link with -L/opt/my-own-mysql/lib -static -lmysqlclient -lz -lm For machines with an incompatible libc gethostbyname() will fail when you call mysql_real_connect(), so just use IP addresses instead of host names. There is a solution that works around this problem, but it requires a libc patch. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie: increment an existing record
Is there a mysql command that will increment the value of a field in an existing row? I would think this would be pretty basic, but I can't seem to find anything in the online documentation. Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
server backups
All, I'm new to mysql. I'm interested in writing a cron job to backup mysql that will: - Backup all the global information first (users and groups, typically) - Backup each database independently (not dumpall) For postgres I can dump: - globals pg_dumpall -U $PG_ADMIN -g $PG_BACKUP_DIR/globals.sql - databases for database in $(psql -l -t -d template1 -U $PG_ADMIN | cut -f1 -d'|') ; do if [ $database = template0 ] ; then continue # we cannot backup or restore this database fi # actually dump the database to a pg_dump custom file format... syslog debug Attempting to backup database $database. pg_dump -f $PG_BACKUP_DIR/$database.custom -Fc -U $PG_ADMIN $database if [ $? != 0 ] ; then syslog crit CRITICAL Failed to backup database $database. else syslog info Backed up database $database. fi done Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: just the list please!
Michael T. Babcock wrote: Daniel Kasak wrote: When someone has a MySQL problem, it is often urgent. While most of my posts to the list seem to appear withing a few minutes, I can remember many occasions when they took more than a few hours. Its also not that difficult to notice duplicates; only the messages to yourself are duplicated, not entire threads. I often remove the person's E-mail address if its not some form of urgent posting (like this one) or where they may not care about the response, especially if I'm just adding to a thread and not actually responding to them. It is a good idea to always CC the person you are replying to because he may not be on the list. BTW, if you have an urgent problem, or if you would reduce the chances of having one, purchasing MySQL support contract is a good idea. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AddressBook CMS
Hi Everyone, Fisrtoff, I know nothing about databases and even less about web design. What I do know is that I want to move my Outlook contacts (~10,000) to a real database :) I've looked at FileMaker Pro and MyGroupWare and the likes but I just want something simple where I can export my contacts and sort them into templates. And those have either to much cost attached to them or simply try to offer to many features for what I want. Can someone point me in the right direction? I know I need to learn MySQL and that I will (I'm getting more and more comfortable with PHPmyAdmin). My main concern is how to access and manage the content in a user-friend way. Cheers, Phil Hi Phil, Under the File Menu, choose Import Export... Export your Contacts to a CSV file, and then import that file into MySQL. I believe you can also export your contacts directly to Access and FileMaker Pro. Of course, you mentioned that you want to move your contacts to a real database, so MySQL is the way to go! Look up in the MySQL documentation how to import CSV files. HTH, -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't access mysql after kernel upgrade
James Packham wrote: Hi all. I've just upgraded the kernel on my server from v. 2.4.21 to 2.4.25 (The original had some problems), but now I can't access my database :( If I revert back to the old kernel then MySQL works fine again. Here's some errors and stuff: # /etc/init.d/mysqld status mysqld (pid 1153 1124) is running... # /etc/init.d/mysqld stop Stopping MySQL:[FAILED] # cat /var/log/mysqld.log Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x40973774, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80e43dc 0x4007b8f8 (nil) New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash Uh I'm really not sure what to do from here. I read through the page, but there doesn't seem to be anything suggestive of my problem... Can anybody pass on any hints on what I should be doing next? Try compiling MySQL from source on the machine so it will be linked against the libraries that your system likes. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie: increment an existing record
[EMAIL PROTECTED] wrote: Is there a mysql command that will increment the value of a field in an existing row? I would think this would be pretty basic, but I can't seem to find anything in the online documentation. update t1 set f = f + 1 -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alter table tt union=(t1,t2,t3);
Any idea what this would do? alter table tt union=(t1,t2,t3); The four tables are all the same and all contain data. I posted the details (schema, contents) of the tables here: http://nopaste.php.cd/10918 I'm running mysql Ver 11.18 Distrib 3.23.58, Shane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JVM unable to access MySQL intermittently?
Hi, I'm running into a strange problem with my JVM and MySQL. I'm using Macromedia JRun 4 as my JVM and everytime I restart the Jrun service, my application runs smoothly. However, after an indeterminate amount of time (seemingly random - sometimes a couple of hours, sometimes 12-15 hours), my system is suddenly no longer able to access the MySQL database - my queries are not being executed. No error messages are thrown, nor any timeout's being hit. My problem is that I am not sure where to start debugging this problem. When it happens, I can see the MySQL threads (show full processlist) as being asleep for extended periods of time. MySQL itself is alive, and I am able to access it by manual queries through the command-line interface. This leads me to think that the problem either lies in my Connection Pool or within the JConnector itself. Is there any form of debugging I can enable in the JConnector to see if the JVM is actually attempting to make a connection and a query? Or if a thread is being successfully retrieved by my Connection Pool? I'm running on MySQL 4.0.18 and JConnector 3.0.9. I am using autoReconnect=true and autoReconnectForPools=true as options to the JConnector. I have also set the MySQL wait_timeout high (to 604800 = 1 week) to ensure that it isn't a timeout problem I am seeing. Any help or suggestions would be greatly appreciated! Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial data at InnoDB tables
According to MySQL Manual: MySQL 4.1 introduces spatial extensions to allow the generation, storage, and analysis of geographic features. Currently, these features are available for MyISAM tables only. This means that I can not have transactional processing in a table with spatial data, right? So, does anyone know if it's gonna be available for InnoDB tables? Any specific release? Regards, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie: increment an existing record
Is there a mysql command that will increment the value of a field in an existing row? I would think this would be pretty basic, but I can't seem to find anything in the online documentation. update t1 set f = f + 1 Unfortunately that will update all the records in the database. It's likely he would prefer to update a particular record via update t1 set f = f + 1 where id = 1234; Which will update only the record matching the id of 1234. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table tt union=(t1,t2,t3);
Shane Nelson [EMAIL PROTECTED] wrote: Any idea what this would do? alter table tt union=(t1,t2,t3); With this statement you specify that MERGE table tt will union tables t1, t2, t3 and they will used as one: http://www.mysql.com/doc/en/MERGE.html The four tables are all the same and all contain data. I posted the details (schema, contents) of the tables here: http://nopaste.php.cd/10918 I'm running mysql Ver 11.18 Distrib 3.23.58, -- 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: server backups
On Fri, Mar 19, 2004 at 11:50:43AM -0500, Charles Hauser wrote: I'm interested in writing a cron job to backup mysql that will: - Backup all the global information first (users and groups, typically) This information is in the mysql database; unlike postgres, there's no concept of ''global'' system tables like pg_*. So you'd use mysqldump, or mysqlhotcopy, or similar on the mysql database just as you'd use them on any other database. - Backup each database independently (not dumpall) You can get a list of databases from a commandline: % mysql --batch -e 'show databases' This is a newline-separated list of databases, which should easily fit into your for loop. --keith -- [EMAIL PROTECTED] alt.os.linux.slackware FAQ: http://wombat.san-francisco.ca.us/cgi-bin/fom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie: increment an existing record
Brad That is indeed what I ended up doing, and it worked. Thanks. Jim On Friday, March 19, 2004, at 02:07 PM, [EMAIL PROTECTED] wrote: Is there a mysql command that will increment the value of a field in an existing row? I would think this would be pretty basic, but I can't seem to find anything in the online documentation. update t1 set f = f + 1 Unfortunately that will update all the records in the database. It's likely he would prefer to update a particular record via update t1 set f = f + 1 where id = 1234; Which will update only the record matching the id of 1234. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing and writing out BLOBS
I had posted the following code in the plusplus mailing list but it was
suggested I post this question in this list instead.
I've been writing a test program using the MySQL C API to test the
reading and writing of BLOB data in and out of the database. The file I
read into the database is a small binary program. I then write out this
BLOB to disk as another file. There is obviously something wrong as the
newly written out file does not execute when run:
./a.out: Exec format error. Binary file not executable.
Permissions are executable. I think the code that I use to read in the
BLOB is correct. But I am not sure if I am retrieving the BLOB
properly. I want to do a comparison between my input buffer and what I
get back from a mysql_fetch_row() but I'm not sure if I am doing it
correctly or not. If I am doing the comparison correctly, then there is
something wrong because the comparison between the two buffers fails at
some point (the first 630 some characters are the same but then a
failure), in which case, what am I doing wrong? How do I read out the
program that I stored in the database?
I give my code here and I would much appreciate it if someone could
point out what it is I am not doing correctly.
// First read in the BLOB into the database
ifstream is;
is.open(inputFilename,ifstream::in|ifstream::binary);
is.seekg(0, ifstream::end);
long bufferSize = is.tellg();
is.seekg(0, ios::beg);
char buffer[bufferSize];
is.read(buffer, bufferSize);
is.close();
// Set query string
char mySql[55000] = INSERT into Blob_file (blob_file) values (';
char* tail;
tail = mySql + strlen(mySql);
if ((tail + 2*bufferSize) + 3 mySql + sizeof(mySql)) {
cerr Binary too big endl;
return 1;
}
tail += mysql_escape_string(tail, buffer, bufferSize);
(void) strcpy (tail, '));
mysql_real_query(myConnection, mySql, strlen(mySql));
// Now try retrieve the BLOB out of the database
char getMySql[1024] = select blob_file from Blob_file where
blob_file_id = 79435; // assuming the insert created entry 79435
mysql_real_query(myConnection, getMySql, strlen(getMySql));
MYSQL_RES* Res;
MYSQL_ROW Row;
Res = mysql_store_result(myConnection);
cout mysql_num_rows(Res) endl;
Row = mysql_fetch_row(Res);
unsigned long *lengths;
lengths = mysql_fetch_lengths(Res);
// Is what I retrieved the same as what I put in?
for (int j=0;j=lengths[0]-1;j++) {
cout .;
if (Row[0][j] != buffer[j]) {
cout BAD endl;
return 1; // I end up here and terminate so something wrong
};
}
ofstream os;
os.open(outputFilename,ofstream::binary);
os.write(Row[0],bufferSize);
os.close();
// close database connection
myDatabase.disconnect();
return 0;
Thank You,
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
master cannot find replication slave privilege
How is this possible? On the master (v5.0.0, port 3306), we have +--+ | Grants for [EMAIL PROTECTED]| +--+ | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' | +--+ The slave server (v5.0.0.a, port 3307) accepts ... CHANGE MASTER TO master_host='localhost', master_port=3306, master_user='repl', master_log_file= 'toshnb-bin.33', master_log_pos=582; but in response to START SLAVE the slave server reports ... 040319 15:01:17 While trying to obtain the list of slaves from the master 'localhost:3306', user 'repl' got the following error: 'Access denied. You need the REPLICATION SLAVE privilege for this operation' 040319 15:01:17 Slave I/O thread exiting, read up to log 'toshnb-bin.33', position 582 PB [mysql]
Re: Key Buffer Size
Hi Terence, key_buffer_size is the amount of memory that is used to cache your index tables. If you are using 100% of it most of the time, you may benefit from an increase in it if you can afford to. You can also look at show status and look at key_reads and key_writes to give you an idea of how often you are using your index cache. Basically, the more indexing you can store in memory the faster your queries tend to be (if they are structured to take advantage of the indexes). You can get more info by looking at: http://www.mysql.com/doc/en/Server_system_variables.html When MySQL starts up, the cache is empty, and is loaded upon use. One cool new feature in 4.1.1 onwards is the ability to pre-load the cache, and to create seperate index caches for use by different tables. Hope this helps. Eric Terence [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, Can somebody tell me whether this is good or bad: Our mysql server has key_buffer_size = 402,653,184 (without the commas) The MySQL administrator tool indicates a 100% usage most of the time, and 362,324,992 as current usage within 24 hours which builds up very fast. Are there memory leaks for threads on my system? Or is this normal behaviour? Restarting the mysql server clears the above. I can't seem to find much information on the mysql site or documentation as to what the ideals are. Running MySQL 4.1.1 Dual Xeon 2.4GhZ 1 Gig Ram Redhat 9 Thanks -- 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]
SQL wrapper for MySQL
Hi, Is there a program/project/software suite out there that traps/wraps MS SQL commands (or database calls/commands/etc) and wraps that into MySQL commands? I'd like to trick a program that requires SQL (MSDE). I'm not just talking about porting the data, but a run-time solution... Thanks, Paul _ Paul Rigor [EMAIL PROTECTED] Go Bruins! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AddressBook CMS
Let me ask a few more questions before making any attempt to say anything else. Once I understand your requirements better, I may not be able to say anything useful but hopefully others on this list will jump in and help I'm not clear on whether this is a single one-time only conversion or whether you plan to re-import your contacts to the database on a regular basis. In other words, once you've imported your contacts into MySQL, will you stop saving new contacts in Outlook use MySQL in its place? Or are you dumping your contacts down from Outlook, playing with them in MySQL, but still gathering new contacts in Outlook with the intent of dumping them down again regularly - every week or every month for example? If it's a one-time only conversion, it should be a relatively easy and straightforward job to convert from your old system to MySQL. With any luck, it's just a matter of: 1) defining your new table or tables in MySQL 2) exporting your old data into a common format like CSV or ASCII or DEL 3) writing and executing the command that reads the old data into MySQL 4) deleting the original data (if you want to clean up) Depending on the complexity of the data and how long it takes you to learn the basics of data modelling, this job shouldn't take more than a few days. (I don't mean to brag but I've done this kind of things for years and could probably do the whole job in a day or less, assuming the data is not too complicated). Of course you may want some programs or at least some queries to work with the data. That could take a lot of time and effort or very little, depending on what you need. If you need programs and you don't know any programming languages (or none of the programming languages you know work with MySQL), it could take you a fair amount of time to learn your chosen language well enough to write the necessary programs. Then again, if you're already a fluent programmer in a language that works with MySQL, like C, PHP, or Java, it might not be a big deal at all. If you intend to keep Outlook around and simply refresh MySQL from Outlook periodically, there is additional work involved. If I were doing it, I'd write a script that automates the export/download/load cycle. Assuming that Outlook has at least as complex an Address Book as Outlook Express the complexity of your data model could be fairly high if you use all of the different types of data available. For instance, if you store several different types of phone numbers for each person/business, keep home and business addresses for the people you know professionally, etc. your data model will be a bit more complex than if you simply use name and phone number. The other issue is what you mean by 'templates'. From your note, I'm not at all clear on what you mean. I use Outlook Express but I don't recall ever seeing templates in it. Are you talking about techniques for selecting subsets from the table? For example, list all people whose last name starts with 'L' who live in Montreal? If yes, MySQL should do that quite nicely. If you simply want to display that information, a few simple queries should do the job. If you need to print reports that list this information, you're probably looking at some programming requirements or at least some scripts. What operating system are you using? It is getting easier to handle many requirements with scripts rather than writing full programs these days. If you are running on Linux for example, bash provides quite a few capabilities for writing simple but powerful scripts that could write reports, among other things. A script would also be my first choice if I wanted to refresh the MySQL data from Outlook regularly. Well, it's time to shut up and let you get a word in edgewise ;-) Rhino - Original Message - From: Phil [EMAIL PROTECTED] To: 'Rhino' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, March 19, 2004 1:01 PM Subject: RE: AddressBook CMS Yeah, I realize that asking any of you guide me through this kind of project would be asking way too much. That's why I said point me in the right direction. I'd just like some recommendations as to what is required to setup something like this. The last thing I want is to slave over a setup for months only to realize that because of my initial lack of knowledge I should have done things differently. Maybe these questions can help clarify what I'm asking of you: What are the cosiderations behind this? Should I use an Apache/PHP type config or maybe there's a client/server software I don't know of... Should I forget about importing from Outlook and start from scratch to ensure the db is clean? Maybe you know of something similar to MyGroupWare, phpGrouWare or moregroupware that only deals with contacts like some sort of glorified db driven Phonebook. I'm just looking for insight from people that know databases... that's all. Thanks, Phil -Original Message- From: Rhino [mailto:[EMAIL
RE: SQL wrapper for MySQL
Is there a program/project/software suite out there that traps/wraps MS SQL commands (or database calls/commands/etc) and wraps that into MySQL commands? I'd like to trick a program that requires SQL (MSDE). I'm not just talking about porting the data, but a run-time solution... I suppose you could have a look at Torque from Apache.org and see if that meets your needs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AddressBook CMS
Yeah, I realize that asking any of you guide me through this kind of project would be asking way too much. That's why I said point me in the right direction. I'd just like some recommendations as to what is required to setup something like this. The last thing I want is to slave over a setup for months only to realize that because of my initial lack of knowledge I should have done things differently. Maybe these questions can help clarify what I'm asking of you: What are the cosiderations behind this? Should I use an Apache/PHP type config or maybe there's a client/server software I don't know of... Should I forget about importing from Outlook and start from scratch to ensure the db is clean? Maybe you know of something similar to MyGroupWare, phpGrouWare or moregroupware that only deals with contacts like some sort of glorified db driven Phonebook. I'm just looking for insight from people that know databases... that's all. Thanks, Phil -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 12:34 PM To: Philippe LeCavalier Subject: Re: AddressBook CMS I don't understand your question. Are you asking us to tell you how to design a database for your contact information or how to write a program to access your database? Surely you realize that these questions are far too big to be answered in a simple email. Or are you asking for recommendations for courses that teach these things? We'll help if we can but I think you'll need to clarify just what you're asking first. Rhino - Original Message - From: Philippe LeCavalier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 19, 2004 12:06 PM Subject: AddressBook CMS Hi Everyone, Fisrtoff, I know nothing about databases and even less about web design. What I do know is that I want to move my Outlook contacts (~10,000) to a real database :) I've looked at FileMaker Pro and MyGroupWare and the likes but I just want something simple where I can export my contacts and sort them into templates. And those have either to much cost attached to them or simply try to offer to many features for what I want. Can someone point me in the right direction? I know I need to learn MySQL and that I will (I'm getting more and more comfortable with PHPmyAdmin). My main concern is how to access and manage the content in a user-friend way. Cheers, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie: increment an existing record
Jim [EMAIL PROTECTED] writes: It's likely he would prefer to update a particular record via update t1 set f = f + 1 where id = 1234; Which will update only the record matching the id of 1234. That is indeed what I ended up doing, and it worked. Thanks. Jim, It's good to hear that you were able to accomplish your desired results. Now if I could just figure out how to get a job based upon the knowledge that I've built up over the last decade of working with various RDBMS systems, coupled with over 9 years of web/back-end integration, and 13 years of Unix kernel internal development experience. You may want to take a look at Teach Yourself SQL in 21 Days by Stephens and Plew, Sams Publishing ISBN 0-672-31674-9 for the copy that I have in front of me. It does a good job of explaining these kinds of questions. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
retrieving last record for all distinct users
I have a table SESSIONS with the following fields: SESSION_ID LOGIN IP TIMESTAMP I am trying to select the last login record for all distinct users. The closest I can get to is: select distinct LOGIN, TIMESTAMP, IP from SESSIONS group by LOGIN order by TIMESTAMP desc This kind of works but it does not get the correct IP for the last TIMESTAMP. This seems easy enough, but I can't seem to figure this one out today. Can anybody see what I'm missing? Thanks! -m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial data at InnoDB tables
At 15:38 -0300 3/19/04, Alex Martins Daher wrote: According to MySQL Manual: MySQL 4.1 introduces spatial extensions to allow the generation, storage, and analysis of geographic features. Currently, these features are available for MyISAM tables only. This means that I can not have transactional processing in a table with spatial data, right? That's correct. So, does anyone know if it's gonna be available for InnoDB tables? Any specific release? Regards, Alex -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: master cannot find replication slave privilege
At 15:19 -0600 3/19/04, Peter Brawley wrote: How is this possible? On the master (v5.0.0, port 3306), we have +--+ | Grants for [EMAIL PROTECTED]| +--+ | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' | +--+ The slave server (v5.0.0.a, port 3307) accepts ... CHANGE MASTER TO master_host='localhost', master_port=3306, master_user='repl', master_log_file= 'toshnb-bin.33', master_log_pos=582; but in response to START SLAVE the slave server reports ... 040319 15:01:17 While trying to obtain the list of slaves from the master 'localhost:3306', user 'repl' got the following error: 'Access denied. You need the REPLICATION SLAVE privilege for this operation' 040319 15:01:17 Slave I/O thread exiting, read up to log 'toshnb-bin.33', position 582 Connect manually to the server (using mysql, for example) specifying repl as your username, then issue this query: SELECT CURRENT_USER(); Does it show that you were authenticated as 'repl'@'%'? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: retrieving last record for all distinct users
First, since you group by LOGIN, you don't need DISTINCT. The problem is that GROUP BY is designed for use with aggregate functions, not individual rows. So which values of SESSION_ID, IP, and TIMESTAMP you should get is undefined. The manual gives 3 solutions to this problem: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html. Michael motorpsychkill wrote: I have a table SESSIONS with the following fields: SESSION_ID LOGIN IP TIMESTAMP I am trying to select the last login record for all distinct users. The closest I can get to is: select distinct LOGIN, TIMESTAMP, IP from SESSIONS group by LOGIN order by TIMESTAMP desc This kind of works but it does not get the correct IP for the last TIMESTAMP. This seems easy enough, but I can't seem to figure this one out today. Can anybody see what I'm missing? Thanks! -m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Hot Backup problems with O_DIRECT (ibbackup)
I'm having the following problem while trying to run ibbackup when the database is using innodb_flush_method=O_DIRECT This is on Redhat Enterprise 3.0. As you can see, it's reporting an error code of 0, which supposedly means 'success' Is there a way around this or will I have to use another file flush method? Thanks -- START OUTPUT --- [EMAIL PROTECTED] mysql-hotbak]# ibbackup --compress /etc/my.cnf /var/mysql-hotbak/myhotbak.cnf InnoDB Hot Backup version 2.0-beta5; Copyright 2003 Innobase Oy License xx is granted to [EMAIL PROTECTED] x (--apply-log works in any computer regardless of the hostname) Licensed for use in a computer whose hostname is 'apollo' Expires -0-0 (year-month-day) at 00:00 See http://www.innodb.com for further information Type ibbackup --license for detailed license terms, --help for help Contents of /etc/my.cnf: innodb_data_home_dir got value /var/lib/mysql/ innodb_data_file_path got value ibdata01:2000M;ibdata02:2000M;ibdata03:2000M;ibdata04:2000M;ibdata05:2000M datadir got value /var/lib/mysql innodb_log_group_home_dir got value /var/lib/mysql/ innodb_log_files_in_group got value 3 innodb_log_file_size got value 67108864 Contents of /var/mysql-hotbak/myhotbak.cnf: innodb_data_home_dir got value /var/mysql-hotbak innodb_data_file_path got value ibdata01:2000M;ibdata02:2000M;ibdata03:2000M;ibdata04:2000M;ibdata05:2000M datadir got value /var/mysql-hotbak innodb_log_group_home_dir got value /var/mysql-hotbak innodb_log_files_in_group got value 3 innodb_log_file_size got value 67108864 ibbackup: Found checkpoint at lsn 3 246406950 ibbackup: Starting log scan from lsn 3 246406656 040320 2:01:41 ibbackup: Copying log... 040320 2:01:41 ibbackup: Log copied, lsn 3 246406950 ibbackup: We wait 10 seconds before starting copying the data files... 040320 2:01:51 ibbackup: Copying /var/lib/mysql/ibdata01 040320 2:02:01 InnoDB: Operating system error number 0 in a file operation. InnoDB: Error number 0 means 'Success'. InnoDB: See also section 13.2 at http://www.innodb.com/ibman.html InnoDB: about operating system error numbers. InnoDB: File operation call: 'read'. InnoDB: Cannot continue operation. - END OUTPUT --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: master cannot find replication slave privilege
At 19:57 -0600 3/19/04, Peter Brawley wrote: On logging in as user repl, current_user()[EMAIL PROTECTED] The user table row for user=repl has host=% as specified. There are no other rows for user=repl. Is the manual incorrect in recommending this? No. But it does mean you have an anonymous-user account that has a more specific hostname part than the 'repl'@'%' account. Hostname matching happens before username matching, and it appears that when your slave connects to the master, it's being authenticated as ''@'localhost'. I suggest you delete the anonymous-user account (do you really need it?), flush the privileges, and try again. Alternatively, change your replication account to 'repl'@'localhost'. Do you really want 'repl' to be able to connect from any host? - Original Message - From: mailto:[EMAIL PROTECTED]Paul DuBois To: mailto:[EMAIL PROTECTED]Peter Brawley ; mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Sent: Friday, March 19, 2004 7:27 PM Subject: Re: master cannot find replication slave privilege At 15:19 -0600 3/19/04, Peter Brawley wrote: How is this possible? On the master (v5.0.0, port 3306), we have +--+ | Grants for mailto:[EMAIL PROTECTED][EMAIL PROTECTED]| +--+ | GRANT REPLICATION SLAVE ON *.* TO mailto:'repl'@'%''repl'@'%' | +--+ The slave server (v5.0.0.a, port 3307) accepts ... CHANGE MASTER TO master_host='localhost', master_port=3306, master_user='repl', master_log_file= 'toshnb-bin.33', master_log_pos=582; but in response to START SLAVE the slave server reports ... 040319 15:01:17 While trying to obtain the list of slaves from the master 'localhost:3306', user 'repl' got the following error: 'Access denied. You need the REPLICATION SLAVE privilege for this operation' 040319 15:01:17 Slave I/O thread exiting, read up to log 'toshnb-bin.33', position 582 Connect manually to the server (using mysql, for example) specifying repl as your username, then issue this query: SELECT CURRENT_USER(); Does it show that you were authenticated as mailto:'repl'@'%''repl'@'%'? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Patch for mysqlbinlog
Hello, everyone:
I recently had to recover some deleted data that was important enough to dig
through the binlogs for and fish it out. To make my job easier, I fixed up
mysqlbinlog to do a couple of extra tricks:
* show only entries in a given timestamp range
* exclude queries containing a certain substring
* include only queries matching a certain substring
* exclude queries done by a certain thread
Regexp search, and mutliple patterns still remain on the wish list - fortunately
( for me) and unfortunately for others I did not need them. But anyway, here
is what I ended up with (patch against 4.0.18), let's hope some find it useful:
start--
--- ../../tmp/mysql-4.0.18/client/mysqlbinlog.ccTue Feb 10 11:15:56 2004
+++ mysqlbinlog.cc Thu Mar 18 12:23:27 2004
@@ -27,6 +27,10 @@
#define CLIENT_CAPABILITIES(CLIENT_LONG_PASSWORD | CLIENT_LONG_FLAG |
CLIENT_LOCAL_FILES)
+#define FILTER_MATCH 0
+#define FILTER_SAVE_INTVAR 1
+#define FILTER_NO_MATCH -1
+
char server_version[SERVER_VERSION_LENGTH];
ulong server_id = 0;
@@ -57,6 +61,12 @@
static short binlog_flags = 0;
static MYSQL* mysql = NULL;
+Intvar_log_event* save_insert_ev = 0, *save_last_insert_ev = 0;
+
+static time_t from_ts = 0, to_ts = 0;
+static const char* pattern = 0, *exclude_pattern = 0;
+static ulong exclude_thread_id = 0;
+
static const char* dirname_for_local_load= 0;
static int dump_local_log_entries(const char* logname);
@@ -66,6 +76,8 @@
static void die(const char* fmt, ...);
static MYSQL* safe_connect();
+static int filter_match(Log_event* ev, char* last_db);
+static void clear_saved_events();
class Load_log_processor
{
@@ -300,6 +312,78 @@
Load_log_processor load_processor;
+static void clear_saved_events()
+{
+ if (save_insert_ev)
+{
+ delete save_insert_ev;
+ save_insert_ev = 0;
+}
+ if (save_last_insert_ev)
+{
+ delete save_last_insert_ev;
+ save_last_insert_ev = 0;
+}
+}
+
+static int filter_match(Log_event* ev, char* last_db)
+{
+ if (from_ts ev-when from_ts)
+return FILTER_NO_MATCH;
+ if (to_ts ev-when to_ts)
+return FILTER_NO_MATCH;
+
+ if (exclude_pattern)
+ {
+if (ev-get_type_code() == QUERY_EVENT)
+{
+ Query_log_event* qev = (Query_log_event*)ev;
+ if (strstr(qev-query,exclude_pattern))
+return FILTER_NO_MATCH;
+}
+ }
+
+ if (pattern)
+ {
+switch (ev-get_type_code())
+{
+ case QUERY_EVENT:
+{
+ Query_log_event* qev = (Query_log_event*)ev;
+ if (!strstr(qev-query,pattern))
+ return FILTER_NO_MATCH;
+ break;
+}
+ case INTVAR_EVENT:
+return FILTER_SAVE_INTVAR;
+ default:
+return FILTER_NO_MATCH;
+}
+ }
+
+ if (exclude_thread_id)
+ {
+if (ev-get_type_code() == QUERY_EVENT)
+{
+ Query_log_event* qev = (Query_log_event*)ev;
+ if (qev-thread_id == exclude_thread_id)
+return FILTER_NO_MATCH;
+}
+ }
+ if (save_insert_ev)
+{
+ save_insert_ev-print(result_file,short_form,last_db);
+ delete save_insert_ev;
+ save_insert_ev = 0;
+}
+ if (save_last_insert_ev)
+{
+ save_last_insert_ev-print(result_file,short_form,last_db);
+ delete save_last_insert_ev;
+ save_last_insert_ev = 0;
+}
+ return FILTER_MATCH;
+}
int process_event(ulonglong *rec_count, char *last_db, Log_event *ev,
my_off_t pos, int old_format)
@@ -307,6 +391,37 @@
char ll_buff[21];
if ((*rec_count) = offset)
{
+switch (filter_match(ev,last_db))
+{
+ case FILTER_NO_MATCH:
+delete ev;
+clear_saved_events();
+return 0;
+ case FILTER_SAVE_INTVAR:
+ {
+ Intvar_log_event* iev = (Intvar_log_event*)ev;
+ switch (iev-type)
+ {
+ case INSERT_ID_EVENT:
+ if (save_insert_ev)
+delete save_insert_ev;
+ save_insert_ev = iev;
+ return 0;
+ case LAST_INSERT_ID_EVENT:
+ if (save_last_insert_ev)
+delete save_last_insert_ev;
+ save_last_insert_ev = iev;
+ return 0;
+ default:
+ delete ev;
+ return 0;
+ }
+ break;
+ }
+ default:
+break;
+}
+
if (!short_form)
fprintf(result_file, # at %s\n,llstr(pos,ll_buff));
@@ -395,6 +510,16 @@
return 0;
}
+enum mysqlbinlog_options
+ {
+ OPT_FROM_TS=256,
+ OPT_TO_TS,
+ OPT_EXCLUDE_THREAD,
+ OPT_PATTERN,
+ OPT_EXCLUDE_PATTERN
+ };
+
+
static struct my_option my_long_options[] =
{
@@ -439,6 +564,25 @@
{local-load, 'l', Prepare files for local load in directory.,
(gptr*) dirname_for_local_load, (gptr*) dirname_for_local_load, 0,
GET_STR_ALLOC, OPT_ARG, 0, 0, 0, 0, 0, 0},
+ {from-ts, OPT_FROM_TS, Print entries only after this timestamp,
+(gptr*) from_ts, (gptr*)
PHP MYSQL DWMX04 can't link tables
Hello, and I hope to god someone in here can answer my question as I am at
my wits end.
System, Mysql 4xx, php 4.3x, DWMX 2004, phpMyadmin 2.5, WinXP Pro, IIS
Will be uploading to a unix server
THE PLAN
to register users in a LOGIN table, if successful take them to the login
page where they login, and this brings up a page with their info from the
registration process .
THE PROBLEM
I am using DWMX04 to create and populate these tables and all seems well. I
can successfully register a new user in the LOGIN table, but when I go to
login in the login_id (The Master Key for all subsequent tables and tows
related to this user) is not made available to the target page and the
default is used so no matter who logs in they always see the info for the
first user.
..
this is the dynamic table entry
$colname_rs_log_info = 1;
if (isset($_GET['login_id'])) {
$colname_rs_log_info =
(get_magic_quotes_gpc()) ? $_GET['login_id'] :
addslashes($_GET['login_id']);these take them to a welcome page with
this is the update entry
$colname_rs_login_check = 1;
if (isset($_COOKIE['login_id'])) {
$colname_rs_login_check = (get_magic_quotes_gpc()) ?
$_COOKIE['login_id'] : addslashes($_COOKIE['login_id']);
}
I have tried all the combinations of the recordset wizard (except the
correct one - no doubt), I have checked that session_start(); is on and that
global variables is on (though I would have to turn these off when uploading
to hosting server).
Thanks in advance
stu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory usage probs
Hi,
I've been having problems with segfaults under mod_auth_mysql in Apache
1.3, which I think i've narrowed down to the MYSQL connection structure
getting corrupted on my particular mysql installation - specifically
manifesting itself with strange values of the free_me field, which results
in the structure being incorrectly free'd.
If anyone can confirm that the following shows something fundamentally
wrong with my mysql install i'd appreciate it hugely:
box 130% cat test.c
#include mysql/mysql.h
int main() {
MYSQL *m;
m = mysql_init(0);
printf(free_me is %d\n, m-free_me);
return 0;
}
box 131% gcc -g -L/usr/local/lib/mysql -lmysqlclient -lm -o foo test.c
box 132% ./foo
free_me is 0
This is using libmysqlclient.so.12.0.0 on a P4 Xeon running linux 2.4.22
with libc 2.3.1; mysql 4.0.17 built from source with:
./configure --prefix=/opt/mysql-4.0.17
--localstatedir=/usr/local/var/mysql --without-innodb --without-docs
--without-bench --with-mysqld-user=mysql
The code in libmysql.c appears to say:
mysql_init(MYSQL *mysql) {
if (!mysql) {
malloc mysql
mysql-free_me=1;
}
...
}
so I'm at a complete loss to explain why m-free_me appears as 0 in the
above mini example.
Meanwhile, precisely the same program on a dual PIII machine running
Debian Woody, libmysqlclient 12.0.0 to match 4.0.16 yields:
deb 30% ./foo
free_me is 1
Superficially other operations seem to work - but segfaults ensue on
mysql_close(); and gdb reveals several fields of the connection structure
to change radically between simple operations. For instace running a
mysql_select_db() sets the value of free_me to 0x03, amongst others:
freshly inited mysql_handle, populated with some settings:
(gdb) print *mysql_handle
$1 = {net = {vio = 0x84efd30, fd = 139421480, fcntl = 139429672, buff = 0x84f6728 ,
buff_end = 0x84f6728 ,
write_pos = 0x6 Address 0x6 out of bounds, read_pos = 0x2000 Address 0x2000 out
of bounds,
last_error = [EMAIL PROTECTED], '\0' repeats 174 times, last_errno = 0,
max_packet = 0,
timeout = 0, pkt_nr = 0, error = 0 '\0', return_errno = 0 '\0', compress = 0 '\0',
no_send_ok = 0 '\0', remain_in_buf = 0, length = 0,
buf_length = 0, where_b = 0, return_status = 0x0, reading_or_writing = 0 '\0',
save_char = 0 '\0'}, connector_fd = 0x0, host = 0x0,
user = 0x0, passwd = 0x0, unix_socket = 0x0, server_version = 0x0, host_info =
0x84f01a0 auth.domain.com, info = 0x84f01e8 auth,
db = 0x84f01f8 , port = 0, client_flag = 139395520, server_capabilities =
139395456, protocol_version = 0, field_count = 0,
server_status = 1074172704, thread_id = 0, affected_rows = 0, insert_id = 0,
extra_info = 0, packet_length = 0, status = MYSQL_STATUS_READY,
fields = 0x0, field_alloc = {free = 0x0, used = 0x0, pre_alloc = 0x0, min_malloc =
0, block_size = 0, error_handler = 0x2f43},
free_me = 0 '\0', reconnect = 0 '\0', options = {connect_timeout = 3306, client_flag
= 8197, compress = 44 ',', named_pipe = 0 '\0',
port = 10, host = 0x0, init_command = 0x2 Address 0x2 out of bounds, user = 0x8
Address 0x8 out of bounds, password = 0x0,
unix_socket = 0x0, db = 0x0, my_cnf_file = 0x0, my_cnf_group = 0x0, charset_dir =
0x0, charset_name = 0x0, use_ssl = 0 '\0',
ssl_key = 0x0, ssl_cert = 0x0, ssl_ca = 0x0, ssl_capath = 0x0}, scramble_buff =
\0\0\0\0\0\0\0\0, charset = 0x0, server_language = 0}
(gdb) step
506 if (mysql_select_db(mysql_handle,m-mysqlDB) != 0) {
(gdb) print *mysql_handle
$2 = {net = {vio = 0x84efd30, fd = 139421480, fcntl = 139429672, buff = 0x84f6728 ,
buff_end = 0x84f6728 ,
write_pos = 0x6 Address 0x6 out of bounds, read_pos = 0x2000 Address 0x2000 out
of bounds,
last_error = [EMAIL PROTECTED], '\0' repeats 174 times, last_errno = 0,
max_packet = 0,
timeout = 0, pkt_nr = 0, error = 0 '\0', return_errno = 0 '\0', compress = 0 '\0',
no_send_ok = 0 '\0', remain_in_buf = 0, length = 0,
buf_length = 0, where_b = 0, return_status = 0x0, reading_or_writing = 0 '\0',
save_char = 0 '\0'}, connector_fd = 0x0, host = 0x0,
user = 0x0, passwd = 0x0, unix_socket = 0x0, server_version = 0x0, host_info =
0x84f01a0 auth.domain.com, info = 0x84f01e8 auth,
db = 0x84f01f8 , port = 0, client_flag = 139395520, server_capabilities =
139395456, protocol_version = 0,
field_count = 139395592, server_status = 1074172704, thread_id = 0, affected_rows =
0, insert_id = 0, extra_info = 0, packet_length = 0,
status = MYSQL_STATUS_READY, fields = 0x, field_alloc = {free = 0x,
used = 0x0, pre_alloc = 0x0, min_malloc = 0,
block_size = 0, error_handler = 0x2f43}, free_me = 3 '\003', reconnect = 0 '\0',
options = {connect_timeout = 3306, client_flag = 8197,
compress = 44 ',', named_pipe = 0 '\0', port = 10, host = 0x0, init_command = 0x2
Address 0x2 out of bounds,
user = 0x8 Address 0x8 out of bounds, password = 0x0, unix_socket = 0x0, db =
0x0, my_cnf_file = 0x0, my_cnf_group = 0x0,
*very* strange...
Lo everyone,
I'm *baffled* completely I've never seen something like this before.
: I tried this exact query from PHP, Perl, as well as the MySQL
thingy... They ALL give the same result - it must therefore be my table
mysql SELECT VERSION();
++
| VERSION() |
++
| 4.0.14-log |
++
1 row in set (0.00 sec)
mysql SHOW TABLES LIKE 'Accounts';
++
| Tables_in_SAV001 (DSLAccounts) |
++
| Accounts |
++
1 row in set (0.00 sec)
mysql SHOW TABLE STATUS LIKE 'Accounts';
+-+++--++-+-
+--+---++---
--+-+++---+
| Name| Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Create_options | Comment |
+-+++--++-+-
+--+---++---
--+-+++---+
| Accounts| MyISAM | Dynamic| 42 | 44 |1888 |
4294967295 | 7168 | 0 | 49 | 2004-03-20 08:53:01
| 2004-03-20 08:54:42 | NULL || Accounts |
+-+++--++-+-
+--+---++---
--+-+++---+
1 row in set (0.00 sec)
mysql SHOW KEYS FROM `Accounts`;
+-++--+--+-+
---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-++--+--+-+
---+-+--++--++-+
| Accounts| 0 | PRIMARY |1 | EntryID | A
| 42 | NULL | NULL | | BTREE | |
| Accounts| 0 | Username |1 | Username| A
| 42 | NULL | NULL | | BTREE | |
| Accounts| 1 | isActive |1 | isActive| A
|NULL | NULL | NULL | | BTREE | |
| Accounts| 1 | isCapped |1 | isCapped| A
|NULL | NULL | NULL | | BTREE | |
+-++--+--+-+
---+-+--++--++-+
4 rows in set (0.00 sec)
mysql SHOW FULL FIELDS FROM `Accounts`;
+--+--+--+-+-++-
+
| Field| Type | Null | Key | Default | Extra |
Privileges |
+--+--+--+-+-++-
+
| EntryID | smallint(6) unsigned | | PRI | NULL| auto_increment |
select,insert,update,references |
| Username | varchar(150) | | UNI | ||
select,insert,update,references |
| Password | varchar(150) | | | ||
select,insert,update,references |
| isActive | enum('y','n')| | MUL | y ||
select,insert,update,references |
| isCapped | enum('y','n')| | MUL | n ||
select,insert,update,references |
+--+--+--+-+-++-
+
5 rows in set (0.00 sec)
mysql SHOW FIELDS FROM `Accounts` FROM `SAV001`;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| EntryID | smallint(6) unsigned | | PRI | NULL| auto_increment |
| Username | varchar(150) | | UNI | ||
| Password | varchar(150) | | | ||
| isActive | enum('y','n')| | MUL | y ||
| isCapped | enum('y','n')| | MUL | n ||
+--+--+--+-+-++
5 rows in set (0.00 sec)
mysql INSERT INTO Accounts (Username, Password) VALUES
(Username='[EMAIL PROTECTED]', Password='password');
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM Accounts WHERE Username='[EMAIL
Re: *very* strange...
insert into table (username, password) values ('username', 'password')
Skip out the username= and password= part. What you are ending up
doing here is that the values portion of the insert statement, these two
You may kick my ass... Royally.
Sorry, I feel like a phrick to say the least... That's what copy and pasting
code to save time does for you
--
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: *very* strange...
On Sat, 2004-03-20 at 01:03, Chris Knipe wrote:
Lo everyone,
I'm *baffled* completely I've never seen something like this before.
: I tried this exact query from PHP, Perl, as well as the MySQL
thingy... They ALL give the same result - it must therefore be my table
...
mysql INSERT INTO Accounts (Username, Password) VALUES
(Username='[EMAIL PROTECTED]', Password='password');
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM Accounts WHERE Username='[EMAIL PROTECTED]';
Empty set (0.00 sec)
mysql
HOWEVER
mysql SELECT * FROM Accounts WHERE Username='0';
+-+--+--+--+--+
| EntryID | Username | Password | isActive | isCapped |
+-+--+--+--+--+
| 48 | 0| 0| y| n|
+-+--+--+--+--+
1 row in set (0.00 sec)
There's my entry. It's the row that was inserted, because I can see from
the EntryID (Auto Increment).
Now, WTF is wrong here... Nevermind what the value is I send to Username /
Password, Mysql *ALWAYS* replaces the data specified with a 0. Needless to
say, due to the UNIQUE keys, I dont ever get the data into my table :(((
You are using the wrong syntax for what you want to do. What you want
is this:
insert into table (username, password) values ('username', 'password')
Skip out the username= and password= part. What you are ending up
doing here is that the values portion of the insert statement, these two
parts:
Username='[EMAIL PROTECTED]'
Password='password'
Are being evaluated as comparison expressions. Since the string
'password' does not equal the value of the column Password (although
this seems like an odd syntax to allow without a where clause), the
value of these two columns is zero. Simplified, your query ends up
being (after the expressions are evaluated):
insert into table (username, password) values (0, 0)
because both of the expressions you put in the values evaluate to 0.
I hope this is clear enough.
--
Andy Bakun [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: *very* strange...
Hi,
- Original Message -
From: Chris Knipe [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, March 20, 2004 12:33 PM
Subject: *very* strange...
Lo everyone,
I'm *baffled* completely I've never seen something like this
before.
: I tried this exact query from PHP, Perl, as well as the MySQL
thingy... They ALL give the same result - it must therefore be my
table
mysql SELECT VERSION();
++
| VERSION() |
++
| 4.0.14-log |
++
1 row in set (0.00 sec)
mysql SHOW TABLES LIKE 'Accounts';
++
| Tables_in_SAV001 (DSLAccounts) |
++
| Accounts |
++
1 row in set (0.00 sec)
mysql SHOW TABLE STATUS LIKE 'Accounts';
+-+++--++-+-
+--+---++-
--
--+-+++---
+
| Name| Type | Row_format | Rows | Avg_row_length | Data_length
|
Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Create_options | Comment |
+-+++--++-+-
+--+---++-
--
--+-+++---
+
| Accounts| MyISAM | Dynamic| 42 | 44 |1888
|
4294967295 | 7168 | 0 | 49 | 2004-03-20
08:53:01
| 2004-03-20 08:54:42 | NULL || Accounts |
+-+++--++-+-
+--+---++-
--
--+-+++---
+
1 row in set (0.00 sec)
mysql SHOW KEYS FROM `Accounts`;
+-++--+--+-+
---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
|
+-++--+--+-+
---+-+--++--++-+
| Accounts| 0 | PRIMARY |1 | EntryID | A
| 42 | NULL | NULL | | BTREE | |
| Accounts| 0 | Username |1 | Username| A
| 42 | NULL | NULL | | BTREE | |
| Accounts| 1 | isActive |1 | isActive| A
|NULL | NULL | NULL | | BTREE | |
| Accounts| 1 | isCapped |1 | isCapped| A
|NULL | NULL | NULL | | BTREE | |
+-++--+--+-+
---+-+--++--++-+
4 rows in set (0.00 sec)
mysql SHOW FULL FIELDS FROM `Accounts`;
+--+--+--+-+-++-
+
| Field| Type | Null | Key | Default | Extra
|
Privileges |
+--+--+--+-+-++-
+
| EntryID | smallint(6) unsigned | | PRI | NULL| auto_increment
|
select,insert,update,references |
| Username | varchar(150) | | UNI | |
|
select,insert,update,references |
| Password | varchar(150) | | | |
|
select,insert,update,references |
| isActive | enum('y','n')| | MUL | y |
|
select,insert,update,references |
| isCapped | enum('y','n')| | MUL | n |
|
select,insert,update,references |
+--+--+--+-+-++-
+
5 rows in set (0.00 sec)
mysql SHOW FIELDS FROM `Accounts` FROM `SAV001`;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-++
| EntryID | smallint(6) unsigned | | PRI | NULL| auto_increment
|
| Username | varchar(150) | | UNI | |
|
| Password | varchar(150) | | | |
|
| isActive | enum('y','n')| | MUL | y |
|
| isCapped | enum('y','n')| | MUL | n |
|
+--+--+--+-+-++
5 rows in set (0.00 sec)
mysql INSERT INTO Accounts (Username, Password) VALUES
(Username='[EMAIL
RE: Guru's advice needed ........[Security: SQL injection]
This reply has two purposes.
Firstly, a small suggestion (modest compared to the others which will
follow, no doubt!) - check out mysql_escape_string() - this may be useful to
you.
Secondly, a further question:
In addition to protecting against SQL Injection, has anyone here
experimented with detecting and recording attempts at injection?
I've been pondering checking strings which come directly from user input for
sql keywords (and possibly using regexps to check for potential SQL Syntax
fragments), but before I begin, I thought asking would benefit me, if
someone with more experience has already tried this...
Thanks,
Matt
-Original Message-
From: Tariq Murtaza [mailto:[EMAIL PROTECTED]
Sent: 19 March 2004 18:41
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Guru's advice needed [Security: SQL injection]
*Dear Friends!*
Can someone shed some light on how SQL injection attack occurs when
*magic_quotes_gpc *isON and how it prevents when its OFF. To my
understanding apostrophise are escaped automatically in POST/GET/COOKIE
when its ON, so how it tends towards SQL Injection.
Someone suggested to keep magic_quotes_qpc OFF through .htaccess file
and use following line of codes to prevent attacks at start of the file...
?php
/**
* Checks for magic_quotes_gpc = On and strips them from incoming
* requests if necessary
*/
if (get_magic_quotes_gpc()) {
$_GET= array_map('stripslashes', $_GET);
$_POST = array_map('stripslashes', $_POST);
$_COOKIE = array_map('stripslashes', $_COOKIE);
}
?
But unfortunately it does not work for nested POST requests. do anyone
have better idea?
Secondly why we have to stripslashes while DB (mysql for example) is
doing it for us on execution and another question arises doesn't it
prevent from SQL injection attack when apostrophise are escaped in query.
*What is the best practices handling 'quotation marks' in input string
and how to prevent SQL injection.
*Looking forward for some advice from panel of experts on forum.
Thanks and have a nice day!*
*Cheers!
*Tariq*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
