RE: MySQL BLOB fields and large binary data
Hi, I'm not sure my reply will be of any use to you, but here it goes.. I've recently had problems with a perl driver (DBD::mysqlPP) with regards to, amongst other things, large BLOB data entries. My short-term work-around is simply to handle all BLOBs on the client side, i.e. split up all long queries into several small ones. This works for almost all types of statements. I know it's more or less obvious, and that you might think it's too impractical. My, smallish, experience does however say that it's not that much work and the performance impace, for me, isn't too great. Just a pointer.. -- Aigars -Original Message- From: Bruno Batarelo [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 20:37 To: [EMAIL PROTECTED] Subject: MySQL BLOB fields and large binary data Greetings Seemingly MySQL has a persistant problem that I could not solve whatever I tried to do. It seems that it is impossible to send large amount of binary data to mysql's BLOB field. It is written in manual that 4.0x versions do not have limit for max_allowed_packet. It is also written that bugs regarding that problem are corrected in 4.08. However, I can not send a file to blob field that is larger than 4-5 MB. I use ADO 2.6 and recommended STREAM object for inserting binary data. max_allowed_packet is very large, 60-100MB, database has one table without records, MySQL is 4.08 with its 128 MB of ram, and other 128 is for windows 2000 system. Wait timeout is also very big. Table type is InnoDB. But error message persists: MySQL Server has gone away. MyODBC is 3.51.05. This is recent data regarding this problem in documentation: You will also get a lost connection if you are sending a packet >= 16M if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around. One thing that I do not understand is client's version. I presume that it referes to mysql.exe, but I access server using VB application and MyODBC. Has anyone solved this problem. It is, I'd say "stupid", that these kind of things can not be done with such robust database system. I like MySQL very much, but my whole project depends on this situation and I'll seriously think about using some other RDBMS if I can't solve this problem. Thank you all in advance Bruno Croatia - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: C-API Query
Hi, [..] > produces the following error messages. > > /tmp/ccPhnnJk.o: In function `main': > /tmp/ccPhnnJk.o(.text+0x14): undefined reference to `mysql_init' > /tmp/ccPhnnJk.o(.text+0x5a): undefined reference to `mysql_real_connect' > /tmp/ccPhnnJk.o(.text+0x6d): undefined reference to `mysql_query' > collect2: ld returned 1 exit status [..] I would think that the error message is from ld (the linker). So, it's not a fault in your code per se, but rather the arguments to the linker. Have you stated to include the mysql library? Using gcc it could be something like: "-L/usr/local/lib/mysql -lmysqlclient". -- Aigars DISCLAIMER: Internet communications are not secure and therefore Defcom does not accept legal responsibility for the contents or accuracy of this message. The views and opinions contained in the message are solely those of the author and do not necessarily represent those of Defcom unless otherwise specifically stated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: selecting rows where all rows meet a criterion
Hi, > SELECT * FROM students,grades WHERE students.ID=grades.ID AND grades.grade > > students.average_grade > > This doesn't work. It gets me all the occasions where a student has beaten > his average. But I only want students who have never gone below their > average! In other words, I want to select from the students table, based on > information on multiple rows in the grades table, where ALL the grades rows > need to fulfil a certain criterion. I dunno but this _might_ work: SELECT * FROM students, grades WHERE students.ID = grades.ID AND min(grades.grade) > students.average_grade GROUP BY students.ID -- Aigars sql, mysql, query DISCLAIMER: Internet communications are not secure and therefore Defcom does not accept legal responsibility for the contents or accuracy of this message. The views and opinions contained in the message are solely those of the author and do not necessarily represent those of Defcom unless otherwise specifically stated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating the correct SQL statement
Wouldn't something like this work? SELECT a.price AS pesetas_price, b.rate AS exchange_rate, ROUND(a.price*b.rate) AS pound_price FROM prices AS a LEFT JOIN rate AS b ON 1 = 1 -- Aigars - Original Message - From: "Simon Green" <[EMAIL PROTECTED]> To: "'Craig Atkins'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, June 21, 2001 3:54 PM Subject: RE: Creating the correct SQL statement Hi If I am right you can not do this in one move.. You can use temp tables to help thow Hope this helps a bit Simon -Original Message- From: Craig Atkins [mailto:[EMAIL PROTECTED]] Sent: 21 June 2001 15:48 To: '[EMAIL PROTECTED]' Subject: Creating the correct SQL statement Hello, I am a little stumped on how to create the select statement that I need. I have a table of items, with a column called price, which contains a price in Spainish Pesetas. I have another table, with 1 record, that contains an 'exchange rate' to convert Pesetas to Pound Stirling. ( divide by approx 250) I want to select an item from my database and return the price in pounds, but I am getting stuck. My select statement needs to: Select an item from the 'items' table, select the price, divide the price by the number in the 'exchange' table. Can someone possibly give me any ideas?? (if I haven't confused you already!) ;-) Thanks in Advance Craig Atkins Web Developer Sire Technology Ltd. +44 (0) 1344 758700 http://www.sire.co.uk * This e-mail and its contents are confidential and are for the use of the intended recipient only. If you are not the intended recipient, please notify the sender immediately. The opinions, statements and thoughts expressed in this email are only those of the individual sender. * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: recursive select
Hi, > I'm having a slight problem trying to figure out some logic. I have a > mySQL table that contains 3 columns (child, category, parent). > What I like to do is retrieve all the childrens of the parent and store > them in an php array. I assume you want to get all children for all parents (somehow buched together) and not only to get the children for a single (known) parent. I have a very vague idea of how php arrays really work but this might get you started: (I'm assuming there is a fourth id column here. What do you use for references otherwise? Btw. what does the child column contain?) SELECT * FROM the_table a LEFT JOIN the_table b ON a.id = b.parent This will effectively get you the children for each parent and the parent itself (the parent parts will be repetead for each child). You'll have to do some parsing when outputting/putting into the php array but that should be of little problem. The left-join 'technique' can be added on and on and will work as long as you know the maximum depth before the select is done. Eg. SELECT * FROM the_table a LEFT JOIN the_table b ON a.id = b.parent LEFT JOIN the_table c ON b.id = c.parent If the depth is fixed at design time you could add another column which you have to keep close track of. That column would be one which if used to ORDER BY would make certain each child comes after each parent. If the depth is small you could easely use a float to make that happen. -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Advice on Random Numbers
> > > > *] I need to generate a RANDOM UNIQUE number for every new record. Is > > there an > > > > *] easy way to accomplish this inside of mySQL. > > > > *] > > > > What are the requirements for 'random' and 'unique'? Do you need some sort > > of cryptographic randomness? Does uniqueness be kept across tables? > > > > If you 'only' need a unque id you could you AUTO_INCREMENT. If you want > > uniqueness to carry over tables etc. you could take a look at the aproach > > made by the Apache module mod_unique_id. If you need a cryptographic > > randomness you should look into some litterature about the area so as to get > > a better understanding of what you truly need. > > Would an MD5 encryption be another option? Use a auto incrementing ID to > keep your records sorted, then the MD5 field for your unique randomness. Good idea. Practical and easy (there are a lot fast MD5 implementations easy accessable). That should take care of the cryptographic randomness. Uniqueness won't be true across tables but that might not be necessery. If it was a value similar to mod_unique_id could be MD5'ied. -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Advice on Random Numbers
> > *] I need to generate a RANDOM UNIQUE number for every new record. Is there an > > *] easy way to accomplish this inside of mySQL. > > *] What are the requirements for 'random' and 'unique'? Do you need some sort of cryptographic randomness? Does uniqueness be kept across tables? If you 'only' need a unque id you could you AUTO_INCREMENT. If you want uniqueness to carry over tables etc. you could take a look at the aproach made by the Apache module mod_unique_id. If you need a cryptographic randomness you should look into some litterature about the area so as to get a better understanding of what you truly need. -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Reg. determining version
- Original Message - From: <[EMAIL PROTECTED]> To: "Aigars Grins" <[EMAIL PROTECTED]> Sent: Monday, April 23, 2001 10:51 PM Subject: Re: Re: Reg. determining version > Your message cannot be posted because it appears to be either spam or > simply off topic to our filter. To bypass the filter you must include > one of the following words in your message: > > database,sql,query > > If you just reply to this message, and include the entire text of it in the > reply, your reply will go through. However, you should > first review the text of the message to make sure it has something to do > with MySQL. You have written the following: > > > > Yes, although 32235 represents 3.22.35, not 3.33.35. > > Yes. Typo. Sorry. > > -- > Aigars > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Reg. determining version
Hi, When using there is defined a: MYSQL_VERSION_ID It's defined as something like: 32235 I guess this is representing version 3.33.35. Is this the 'right' way of determining version? I want to do something like an ifdef to incorporate support in my code for different versions of MySQL. -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Max table size [Was: Reg. DBI error]
Hi, I'm using MySQL 3.22.32 on OpenBSD 2.7. Does there exist a maximum table size? How big (4G)? Can I avoid this in any way? -- Aigars > I'm using MySQL on OpenBSD 2.7 and I'm accessing it throhgh DBI/DBD. While > filling a large table with data I finally get the error : > > "Got error 22 from table handler" > > while trying to do an insert statment. The table file is about 4G so I > assume the file is 'full'. Can anyone confirm that the error message really > tries to convey that? > > Secondly, can I get around it? Would a start with "--big-tables" help? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Reg. DBI error
I'm using MySQL on OpenBSD 2.7 and I'm accessing it throhgh DBI/DBD. While filling a large table with data I finally get the error : "Got error 22 from table handler" while trying to do an insert statment. The table file is about 4G so I assume the file is 'full'. Can anyone confirm that the error message really tries to convey that? Secondly, can I get around it? Would a start with "--big-tables" help? -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Propagating a Table
> ... my problem is I want 3 images to appear in any one row in the table.
> Subsequent images I want to start in the next row down, subject to a limit
> of 3 and so forth.
Some naive and simplistic 'pseudo' code:
int current = 0, max = 3;
print_table_row_start();
while (sql_row = get_sql_rows())
{
if (++current > max)
{
print_table_row_end();
print_table_row_start();
current = 0;
}
print_table_entry(sql_row, current);
}
print_table_row_end();
Another approach could be:
create table images (
id int not null primary key auto_increment,
image
);
select a.image as first, b.image as second, c.image as third
from images as a
left join images as b on (a.id = b.id+1)
left join images as c on (a.id = c.id+2);
In this case you should skip all rows but every third when outputting. That
could be mitigated with a group_id column of sorts in the images table
together with a group by expression.
(A sql guru can propably tell you how to do this without a group_id column)
--
Aigars
Bait: MySQL, SQL
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is MySQL support distributed database ?
> Is MySQL support distributed database or not ? > If yes, then please let me know , how it's doing . Maybe not in a sense you'd like, but it has support for replication. Just read up on the manual, as in (the url will propably wrap): http://www.mysql.com/documentation/mysql/bychapter/manual_Replication.html#R eplication Now that you know about the term 'replication' you can just read the manual.. -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem in C API
> sprintf(szQuery,"Insert into mytable(mycol)values '%s' ", > mydata); > where mydata is a C variable containing a large text item > > The problem occurs when mydata contains a single quote, then that single quote is taken as the end of the column value by the MySQL parser and the data after the quote is ignored resulting in an error. use: /* unsigned int mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned int length) */ mysql_real_escape_string(ms, szQuery, mydata, strlen(mydata)); then: sprintf(szQuery, "INSERT INTO mytable(mycol) VALUES '%s'", szQuery); as stated from: http://www.mysql.com/doc/m/y/mysql_real_escape_string.html -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Signal to Noise Ratio...
> This is an open plea to the MySQL team. The amount of spam and other noise > on the list has been growing more and more every day. I would like to make > a couple basic plea's. The first, is that you require a poster to the list > have an actual account on the list, that should help cut out some of the > general spam. Second, perhaps setup some basic moderation, I'll help if > need be :) Intelligent moderation must be performed by a physical person. No? This means delays on mails which is something I can live without. Another solution would be to create even more specialized lists.. (eg. how to write SQL/database design, one for compilation/installation/configuration). The downside of that is of course that most people on this might want to listen all those anyway.. (Besides the point of MySQL-'newbies' being uneccessery disoriented by a huge amount of different lists). Just my .02. (more spam?) -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Virtual Domains ?
> Then two MySQL installed on a machine can use the same port 3306 or > one of them must changed to another port ? Unless you have some auto-magical port-proxy thingy you have to have different ports for each working server on the same machine. Or does MySQL have native handling of this? (I would be suprised) -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: speed problems
> [..] Why this is > s slow with BSD we still don't know (like i said in my first mail, > same query was 3 secs or ~20 sec on Linux) I haven't followed the entire thread so feel free to diss me.. There was problem with the userland threading under early OpenBSD 2.8 versions (including the 'stable' release). This affected MySQL. This is in the docs. Use a 2.8 dated after 2001-01-25 (if I remember correctly) or revert to 2.7. -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to have faster select?
> MySQL doesn't use a table index when doing a string search like %string%, > but it MAY use the index when doing something string%. Is there a way you > can change your search to do a "string starts with" instead of a "string > contains" ? This should help with MySQL using the index that you want it to > use. Also, if you only search on whole 'words' only, eg. 'cisco', 'pc', 'router', etc. You could of course make a second table where you have split up Title into words (one entry for each word). In that table you could search without using like (and therefore use indexes). -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reg. Synchronization between MySQL DBs
Tnx for the answer. From: "nigel wood" <[EMAIL PROTECTED]> > The important question is how much availability (& the ability to survive > network partitions) your looking for: > > 1) Is simple master --> slave replication good enough for your application ? > all inserts & updates going onto the Master the slave being read only at all > times ? > >MySQL has in built support for this (Gamma). This would not be enough. > 2) Are the queries your performing on the data you capturing 'transactional' > i.e. dependent on the presence/state of the rest of the data set all the time? > > If not MySQL + some scripting can be made to re-sync two 'peer' databases > on a regular basis providing you design your insert & update queries carefully > particually incrementing keys. This can be done with the plain text update log > or with timestamped fields. Several people who frequent the list have made > similar applications to this. Here we run two copies of a critical database in > this manner both operating read/write & suviving each other in the event of > failure. > > Both of these are documented in the manual, if your application needs fully > distributed transactions, MySQL is not the system your looking for. Sigh. Maybe it isn't. That would be too bad. I like MySQL. I'll look over the replication descriptions once more.. -- Aigars Grins - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reg. Synchronization between MySQL DBs
From: "Ralph Graulich" <[EMAIL PROTECTED]> > Read about "replication" in the corresponding chapters in the online manual > of mySQL (http://www.mysql.com/) -> Documentation. From: "Mat Murdock" <[EMAIL PROTECTED]> > Did you look at doing something with replication? Thanks for the quick reply. I hadn't looked at replication previously. I've read the text about it now, and might have missed something. But.. In the setup I'm looking for there is no clear master-slave relationsship. With this in mind I could setup numerous servers to all act master to all others. This would be way of keeping all in sync. There are problems with this setup (as stated in the manual), ie. things are not allowed to go wrong if updates are done in slightly different choronological order. I understand why this is so. This would be a problem for all but very simplistic informations. In our setup the different db's aren't backups (slaves), but all are used as masters (they're located differently geographicaly). I guess I'm looking for a 'clustered' service where all db's act as if there we a single one. I didn't expect MySQL to handle this out of the box but was hoping there was a 3rd party add-on/sub system/super system through which it could be 'easely' done. -- Aigars - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Reg. Synchronization between MySQL DBs
Hi, The short version: Does anyone know an automated way of keeping two MySQL db's (located on different machines) in sync? A small window of non-sync could be acceptable. The long version: I'm working on a project which basically is about capturing a lot of data from different places and storing them in a central database. Once there we make a lot of interpreting of the data etc. So far we've used a proprietery distributed database/information forwarding solution. Some of the data inquires are suited to be stated in SQL. For that purpose we regularilly export subsets of the data to a MySQL db and from there extract the compiled data. The export routines take a lot of time, hence we would like to do the export routines work parallel with the rest of operations. This isn't a problem initself. While our project grows we get more and more demands on how the thing should work. One thing is redundancy. To cope with that in this specific instance we would like to have an auto-magical way of synchronizing two different db's (located on different machines). Does anyone know a way to do this? One, obvious, way would be to have a gathering point previous to the MySQL db. From there we could make sure all data is inserted in both db's. Since we have our own solution of how to get the data that far this isn't a problem initself. The problem is that so far we've managed to let different applications change things in the db directly. Those changes would have to be auto-magically reflected to the other db's as well. This part is what we like to auto-magically automate. (It wouldn't be acceptable to require all 'analyzing' apps to update all db's by themselves.) One way would of course be to build a through'n'through multi-tier solution where the db is simply the local db for each 'node'. This has two down-sides. One is the development cost of such a system (_big_). The other is performance. The extra (real-time) abstraction layer would most propably have a performance hit on the system. And we're really short on resources (the data amount in from time to time enormous). So once again, has anyone done something like this? Any experiences to share? Any pointers to where to start and look? Should I forward this message to somewhere else? -- Aigars Grins - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
