accessing MySQL from VB6 application ( without ODBC )
Hi, I've succesfully connected to MySQL from a VB .NET application using a MySQLDriverCS, I'm now looking for the same thing but on a Visual Basic 6 environment. For a number of reasons ( the first is that the application should start from a cd without installation ), I don't want to use ODBC. the operations i need to perform are quite basic ( simple queries to a server ), any advice on some dll out here ? thank you, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
accessing MySQL from VB6 application ( without ODBC )
Hi, I've succesfully connected to MySQL from a VB .NET application using a MySQLDriverCS, I'm now looking for the same thing but on a Visual Basic 6 environment. For a number of reasons ( the first is that the application should start from a cd without installation ), I don't want to use ODBC. the operations i need to perform are quite basic ( simple queries to a server ), any advice on some dll out here ? thank you, Giulio -- 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: 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' SSL is a reserved word. Use backticks (`) to quote column name: http://www.mysql.com/doc/en/Reserved_words.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Batch table structure update tool
Hi I'm looking for a tool (program, library), which given a file describing the structure of the tables in a database, could update the structure of those tables, i.e. create new table, add fields and change field size. Such a tool (especially as a dll library) would be very useful because is would simplify the proces of deployment the new version of an application. Each version would have a description of its tables and would update the structure of the clients database in order to fulfill the requirements of the new version. I'm looking for a solution, which is as non-interactive as possible, just because each activity made by user is a threat that he/she would do something wrong :). Thank You John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: retrieving last record for all distinct users
If I've understood you what I want, then MySQL time functions can help. It is always possible to substract time with MySQL. So if you substract the value of your column TIMESTAMP from current time, you will get all records that are older than now. I can't try it now, but I believe something in this direction could help you: SELECT distinct LOGIN, TIMESTAMP, IP from SESSIONS WHERE CURRENT_TIMESTAMP - TIMESTAMP = 0 || 0 Note that, I did not try it. HTH Babs -Ursprüngliche Nachricht- Von: motorpsychkill [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 20. März 2004 02:18 An: mysql Betreff: 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] -- 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: 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... Add to the beginning of the dump file: SET FOREIGN_KEY_CHECKS=0; and SET FOREIGN_KEY_CHECKS=1; to the end of dump file 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 -- 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: AddressBook CMS
I think this is getting abit over my head here. I'm not a programmer and don't have much interest in becoming one. Maybe I'll just settle for an existing CMS even if it goes way beyond what is required for this. I've added a few comments below. Cheers, Phil -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 6:59 PM To: Phil Subject: Re: AddressBook CMS It's probably best to stay on the list for this discussion. Others may have some very useful input for you. See below for more comments - Original Message - From: Phil [EMAIL PROTECTED] To: 'Rhino' [EMAIL PROTECTED] Sent: Friday, March 19, 2004 5:22 PM Subject: RE: AddressBook CMS -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 4:32 PM To: Phil Cc: [EMAIL PROTECTED] Subject: 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? Well, I certainly plan on adding contacts on a regular basis. However, my goal with this project is to move away from a client only setup (Outlook) and settle in to a client/server model (access my contacts on my FreeBSD/MySQL/apache/PHP CMS solution). 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) That's exactly what I have in mind. Keeping in mind I need this to be very user-friendly, I would really like to have my users add their contacts via a web interface in IE --after I've imported the original from a CVS of course and crreated the necessary tables and templates...etc. Okay, I see one slight confusion emerging here so let's clean this up first. When I said 'CSV' in point 2 of my list, I was using an acronym meaning 'Comma Separated Values', a commonly-used format for data files. (I use Outlook Express and it has a built-in option to export my Address Book in CSV format.) When you said 'CVS' did you mean to type 'CSV'? Because 'CVS' is a whole different thing: it means 'Concurrent Versioning System' and is a way of organizing source code so that you have every different version of a program's source code handy. The reason I'm confused is that you would normally *export* an Outlook file to a CSV format rather than import from one. Of course once you've put the Outlook data into CSV format, you would *import* it to MySQL. That *is* what you meant, right? So, if I understand you correctly, you want to convert your Outlook data to MySQL *once* and then stop using Outlook for the purpose of gathering contacts. Right? Yes - sorry for the typo. I am aware of the difference. I've been using FreeBSD for a long time. So I'm quite comfortable with system administration. Something I didn't understand from your earlier notes was that this contact information was not yours alone; you are managing contacts for other users as well. That complicates life a little but not too much. Where are all of these users? In other words, are they all connected to one another via a LAN? Or are they in different offices, cities, etc.? Do they all have Internet access? If they do, I would be inclined to write a servlet that they could use to input their new contacts to your MySQL database. All on 1 LAN. But if I can figure out a good system I may apply it to different setups as I administer several LANs that could potentially use this type of setup. I don't know if you have any programming ability, let alone any ability with Java, but if I were building this system, I would write a servlet for this purpose. A servlet would be accessible to anyone with an Internet connection - of course you could screen out people who weren't allowed to add contacts too! - and is industrial-strength, meaning it could accept input from lots of users at the same time without breaking. I can get manage but I'm definitely not a programmer. Depending on the complexity of the data and how long it takes you to learn the basics of data
Re: AddressBook CMS
Well, it's up to you of course. Personally, I think what you need is pretty easy if you can get someone with the right skill set to give you a hand. Unless there are a lot of requirements that you haven't mentioned yet, someone like me could load your database, build your queries, and write your servlet in a matter of a few days. Testing and documentation (especially for the servlet) would add to that but not a lot unless you wanted a really massive amount of testing or documentation. Someone who builds a custom solution for you could also explain in detail everything that they've done, which might help you manage it and enhance it better. But if you'd rather pay for something off the shelf that doesn't really do what you want, you're free to do that. It would probably be cheaper to go that way. It might be buggy, it might not be well-supported, it may do much more or less than you really want, it might be unfriendly but it would likely - but not necessarily - be cheaper to buy off the shelf. Custom solutions usually cost more, although you get a lot more leverage: *you* dictate what it does and how well it does it. Or you could learn the necessary skills and do the work yourself. Writing scripts to do what you need wouldn't be terribly difficult if you keep them reasonably simple. That's why I included that example in my previous note: I wanted you to see that we're not talking about 50,000 line programs, just short scripts. But it does take time to learn those skills and you may not want to spend that much time managing contacts. Anyway, it's up to you. I hope you're happy with whatever you decide to do. Good luck! Rhino - Original Message - From: Philippe LeCavalier [EMAIL PROTECTED] To: 'Rhino' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, March 20, 2004 10:04 AM Subject: RE: AddressBook CMS I think this is getting abit over my head here. I'm not a programmer and don't have much interest in becoming one. Maybe I'll just settle for an existing CMS even if it goes way beyond what is required for this. I've added a few comments below. Cheers, Phil -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 6:59 PM To: Phil Subject: Re: AddressBook CMS It's probably best to stay on the list for this discussion. Others may have some very useful input for you. See below for more comments - Original Message - From: Phil [EMAIL PROTECTED] To: 'Rhino' [EMAIL PROTECTED] Sent: Friday, March 19, 2004 5:22 PM Subject: RE: AddressBook CMS -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 4:32 PM To: Phil Cc: [EMAIL PROTECTED] Subject: 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? Well, I certainly plan on adding contacts on a regular basis. However, my goal with this project is to move away from a client only setup (Outlook) and settle in to a client/server model (access my contacts on my FreeBSD/MySQL/apache/PHP CMS solution). 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) That's exactly what I have in mind. Keeping in mind I need this to be very user-friendly, I would really like to have my users add their contacts via a web interface in IE --after I've imported the original from a CVS of course and crreated the necessary tables and templates...etc. Okay, I see one slight confusion emerging here so let's clean this up first. When I said 'CSV' in point 2 of my list, I was using an acronym meaning 'Comma Separated Values', a commonly-used format for data files. (I use Outlook Express and it has a built-in option to export my Address Book in CSV format.) When you said 'CVS' did you mean to type 'CSV'? Because 'CVS' is a whole different thing: it means 'Concurrent Versioning System' and is a way
RE: retrieving last record for all distinct users
Making the assumption that you are running a version of MySQL which supports subqueries, I believe you could use: SELECT Login, TimeStamp, IP FROMSessions S INNER JOIN ( SELECT MAX(TimeStamp) TimeStamp, Login FROMSessions GROUP BYLogin ) Latest ON Latest.Login = S.Login Or something very similar - using a subquery (and joining to it), to ensure you only look at the latest records. I've made the assumption that 'Login' is your way to uniquely identify a user! Thanks, Matt -Original Message- From: motorpsychkill [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 01:18 To: mysql Subject: 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] -- 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
Had my brain been in gear, I would have typed the 'AND Latest.TimeStamp = S.TimeStamp' which you will also need on that join.. Cheers, Matt -Original Message- From: Matt Chatterley [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 19:51 To: 'motorpsychkill'; 'mysql' Subject: RE: retrieving last record for all distinct users Making the assumption that you are running a version of MySQL which supports subqueries, I believe you could use: SELECT Login, TimeStamp, IP FROMSessions S INNER JOIN ( SELECT MAX(TimeStamp) TimeStamp, Login FROMSessions GROUP BYLogin ) Latest ON Latest.Login = S.Login Or something very similar - using a subquery (and joining to it), to ensure you only look at the latest records. I've made the assumption that 'Login' is your way to uniquely identify a user! Thanks, Matt -Original Message- From: motorpsychkill [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 01:18 To: mysql Subject: 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] -- 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]
Full-Text with JOIN
I have 3 tables to join when the last one is a Full-Text table (ft_table).. I do the next join: SELECT id,title FROM table1 LEFT JOIN table2 USING (id) INNER JOIN ft_table USING (id) WHERE ... But MySQL selects the primary key (id) to join the ft_table, which makes the query run a lot of time and gives wrong results according to the MATCH AGAINST search.. I found that making the join as this: SELECT id,title FROM table1,table2,ft_table WHERE table1.id=table2.id AND table2.id=ft_table.id AND ... gives the wanted results according to MATCH AGAINST, but leave out rows that don't exist in table2 (the join there was LEFT JOIN).. 1- Is there a way to join the full-text table and using the full-text index, so the query will not last long? 2- Is there a way to make something like LEFT JOIN using list of tables seperated by comma (table1,table2,..)? 3- Is there a performance difference between making INNER JOIN or by making list of tables seperated by comma (table1,table2,..) with using WHERE clause? thanks a lot in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Batch table structure update tool
Hi, The only utility I know about that does this kind of thing is mysqldiff which can be found at: http://freshmeat.net/projects/mysqldiff/ I haven't used it myself but it comes quite highly rated on freshmeat.net A quote from it's description: mysqldiff is a Perl script which compares the data structures (i.e. table definitions) of two MySQL databases and returns the differences as a sequence of MySQL commands suitable for piping into mysql which will transform the structure of the first database to be identical to that of the second (c.f. diff and patch). Database structures can be compared whether they are files containing table definitions or existing databases, local or remote. Hope it's what you're looking for.. Cheers, Andrew -Original Message- From: Pointer [mailto:[EMAIL PROTECTED] Sent: Saturday 20 March 2004 10:28 To: [EMAIL PROTECTED] Subject: Batch table structure update tool Hi I'm looking for a tool (program, library), which given a file describing the structure of the tables in a database, could update the structure of those tables, i.e. create new table, add fields and change field size. Such a tool (especially as a dll library) would be very useful because is would simplify the proces of deployment the new version of an application. Each version would have a description of its tables and would update the structure of the clients database in order to fulfill the requirements of the new version. I'm looking for a solution, which is as non-interactive as possible, just because each activity made by user is a threat that he/she would do something wrong :). Thank You John -- 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: String Concatenation Operator?
Hi Jim, Unfortunately you do have to use the CONCAT() function to make sure it works on all MySQL installations. The operator used in other DBs, and which can be used in MySQL when running in ANSI mode, is ||, not +: SELECT firstname || ' ' || lastname AS fullname FROM customers But if MySQL isn't in ANSI mode (specifically, the PIPES_AS_CONCAT part), which is typical since it's not enabled by default, || is logical OR. :-( Matt - Original Message - From: Jim McAtee Sent: Thursday, March 18, 2004 4:29 PM Subject: String Concatenation Operator? Does MySQL have a string contatenation operator, or must you use the CONCAT() function? SELECT firstname + ' ' + lastname AS fullname FROM customers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procs and Commit/Rollback Transactions
Hi Laphan, (I'm sending this to the general list too, since this isn't Windows specific and more people will see it.) MySQL 5.0, which is an early Alpha, does now support stored procedures. http://www.mysql.com/doc/en/Stored_Procedures.html And MySQL also supports transactions with the InnoDB (most common) and BDB table types. MyISAM doesn't. Hope that helps. Matt - Original Message - From: Laphan Sent: Thursday, March 18, 2004 5:19 PM Subject: Stored Procs and Commit/Rollback Transactions Hi All OK I admit it I'm a complete MSV (MySQL Virgin), so I might be asking some very stupid questions!!! I'm used to SQL Server so I think I should have a basic understanding, but I'm sure you'll tell me different!! Basically I just want to confirm that the latest release of MySQL doesn't offer stored procs or commit/rollback functionality - right? How does a MySQL-er get round this? I'm wanting to develop my ASP/Cart with MySQL as the back-bone and I'm trying to find out what the generic do's and dont's are when using this collaboration. Any feedback would be very much appreciated. Rgds Laphan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: String Concatenation Operator?
At 16:45 -0600 3/20/04, Matt W wrote: Hi Jim, Unfortunately you do have to use the CONCAT() function to make sure it works on all MySQL installations. The operator used in other DBs, and which can be used in MySQL when running in ANSI mode, is ||, not +: SELECT firstname || ' ' || lastname AS fullname FROM customers But if MySQL isn't in ANSI mode (specifically, the PIPES_AS_CONCAT part), which is typical since it's not enabled by default, || is logical OR. :-( Matt You can also use simple proximity as a concatenation operator: mysql select 'hello,' ' world'; +--+ | hello, | +--+ | hello, world | +--+ This does not depend on the server SQL mode. -- 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: Stored Procs and Commit/Rollback Transactions
Possibly veering off topic, but I have a strong urge to comment on this, and shall! I am a M$ .NET developer (primarily ASP.NET with SQL Server), and have recently embarked on a project at home, and wished to apply the same sort of principles that I use at work - for example, keeping all 'system logic' embedded within the database itself. I have begun prototyping using MySQL 5.0.0a-alpha on win32 (possibly the most unstable combination you can imagine), and it is excellent. I have the odd crash, or strange glitch (such as procedures not being recognized, requiring a restart before they can be called), but this is fine - it's the first alpha, after all! Now all I need to be truly content is views Cheers, Matt -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 22:57 To: Laphan; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Stored Procs and Commit/Rollback Transactions Hi Laphan, (I'm sending this to the general list too, since this isn't Windows specific and more people will see it.) MySQL 5.0, which is an early Alpha, does now support stored procedures. http://www.mysql.com/doc/en/Stored_Procedures.html And MySQL also supports transactions with the InnoDB (most common) and BDB table types. MyISAM doesn't. Hope that helps. Matt - Original Message - From: Laphan Sent: Thursday, March 18, 2004 5:19 PM Subject: Stored Procs and Commit/Rollback Transactions Hi All OK I admit it I'm a complete MSV (MySQL Virgin), so I might be asking some very stupid questions!!! I'm used to SQL Server so I think I should have a basic understanding, but I'm sure you'll tell me different!! Basically I just want to confirm that the latest release of MySQL doesn't offer stored procs or commit/rollback functionality - right? How does a MySQL-er get round this? I'm wanting to develop my ASP/Cart with MySQL as the back-bone and I'm trying to find out what the generic do's and dont's are when using this collaboration. Any feedback would be very much appreciated. Rgds Laphan -- 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: memory usage probs
In case anyone else encounters this particular symptom, it turns out the problem was gcc using some orphaned headers for mysql 3.23.56 sitting in /usr/include/mysql rather than the correct mysql 4.0.17 ones residing in /usr/local/include/mysql, thus yielding all the strange behaviour. M. On Sat, 20 Mar 2004, Matthew Hodgson wrote: 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 =
Newbie Can't Add a User to MySQL
Greetings all, I have taken it upon myself to try and learn MySQL management this weekend! No more using root for everything anymore! However, I cannot seem to do anything but create a login. I have tried to build an extrmely simply Forum using MySQL and PHP. I can create the DB and TABLES as MySQL root without any problems. Its just trying to create an administrative user that I run into the problems. Ideally I would like to be able to create a MySQL user account, where that user can create there own DB, and do whatever the heck they want to it, while not being able to do anything with any other DB. Please find a list of all my efforts below. Based on what I have read, these attempts should have satisfied my goals 3 times over. Anyway, if some can see any mistakes or ommissions, I would love to hear it! mysql INSERT INTO user VALUES('localhost','ForumUser',PASSWORD('forum123'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); Query OK, 1 row affected (0.00 sec) mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'forum123' WITH GRANT OPTION; Query OK, 0 rows affected (0.11 sec) mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP - ON *.* - TO [EMAIL PROTECTED] - IDENTIFIED BY 'forum123'; Query OK, 0 rows affected (0.00 sec) Spider:/usr/bin# mysql -u ForumUser Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 39 to server version: 3.23.49-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SHOW DATABASES; +--+ | Database | +--+ | forums | | phpBB| | test | +--+ 3 rows in set (0.00 sec) mysql USE forums; ERROR 1044: Access denied for user: '@localhost' to database 'forums' mysql USE forums; ERROR 1044: Access denied for user: '@localhost' to database 'forums' mysql Aborted Spider:/usr/bin# mysql -u ForumUser -p forums Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Sincerely, Lee __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Can't Add a User to MySQL
Nowhere have i seen that it tells u to do this ? mysql INSERT INTO user VALUES('localhost','ForumUser',PASSWORD ('forum123'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); for a forum i would do GRANT SELECT,INSERT,UPDATE ON *.* TO [EMAIL PROTECTED] identified 'somepass' Greetings all, I have taken it upon myself to try and learn MySQL management this weekend! No more using root for everything anymore! However, I cannot seem to do anything but create a login. I have tried to build an extrmely simply Forum using MySQL and PHP. I can create the DB and TABLES as MySQL root without any problems. Its just trying to create an administrative user that I run into the problems. Ideally I would like to be able to create a MySQL user account, where that user can create there own DB, and do whatever the heck they want to it, while not being able to do anything with any other DB. Please find a list of all my efforts below. Based on what I have read, these attempts should have satisfied my goals 3 times over. Anyway, if some can see any mistakes or ommissions, I would love to hear it! mysql INSERT INTO user VALUES('localhost','ForumUser',PASSWORD ('forum123'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); Query OK, 1 row affected (0.00 sec) mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'forum123' WITH GRANT OPTION; Query OK, 0 rows affected (0.11 sec) mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP - ON *.* - TO [EMAIL PROTECTED] - IDENTIFIED BY 'forum123'; Query OK, 0 rows affected (0.00 sec) Spider:/usr/bin# mysql -u ForumUser Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 39 to server version: 3.23.49-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SHOW DATABASES; +--+ | Database | +--+ | forums | | phpBB| | test | +--+ 3 rows in set (0.00 sec) mysql USE forums; ERROR 1044: Access denied for user: '@localhost' to database 'forums' mysql USE forums; ERROR 1044: Access denied for user: '@localhost' to database 'forums' mysql Aborted Spider:/usr/bin# mysql -u ForumUser -p forums Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Sincerely, Lee __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- 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: Newbie Can't Add a User to MySQL
Lee Zelyck wrote: Greetings all, I have taken it upon myself to try and learn MySQL management this weekend! No more using root for everything anymore! However, I cannot seem to do anything but create a login. I have tried to build an extrmely simply Forum using MySQL and PHP. I can create the DB and TABLES as MySQL root without any problems. Its just trying to create an administrative user that I run into the problems. Ideally I would like to be able to create a MySQL user account, where that user can create there own DB, and do whatever the heck they want to it, while not being able to do anything with any other DB. Please find a list of all my efforts below. Based on what I have read, these attempts should have satisfied my goals 3 times over. Anyway, if some can see any mistakes or ommissions, I would love to hear it! mysql INSERT INTO user VALUES('localhost','ForumUser',PASSWORD('forum123'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); Query OK, 1 row affected (0.00 sec) Don't edit the user table directly unless you really need to. It's better, and easier, to use GRANT. For one thing, this statement won't have any effect until you FLUSH PRIVILEGES or restart mysqld, while GRANT handles that for you. mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'forum123' WITH GRANT OPTION; Query OK, 0 rows affected (0.11 sec) Right, this is better. You've created the [EMAIL PROTECTED] account and set a password. Note, however, that this new user is equivalent to root, because you've given all privs on all dbs (ALL ON *.*). I thought that isn't what you want. You might consider something like: REVOKE ALL ON *.* FROM [EMAIL PROTECTED]; GRANT ALL ON `forum%`.* TO [EMAIL PROTECTED]; The first strips ForumUser of root power, while the second gives him/her the ability to create and control databases whose names start with forum, but no others. I think that's closer to what you want. mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP - ON *.* - TO [EMAIL PROTECTED] - IDENTIFIED BY 'forum123'; Query OK, 0 rows affected (0.00 sec) This probably didn't do anything, as you'd already given all those privileges in the previous command. Spider:/usr/bin# mysql -u ForumUser You gave [EMAIL PROTECTED] a password, but notice that you didn't use it to log in. You probably aren't logged in as [EMAIL PROTECTED] You should change this command to mysql -u ForumUser -p The -p will cause mysql to prompt you for the password. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 39 to server version: 3.23.49-log It may not be relevant, but that version is pretty old. The latest 3.23 revision is 3.23.58, which is about one and a half years newer. The current version is 4.0.18. Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SHOW DATABASES; +--+ | Database | +--+ | forums | | phpBB| | test | +--+ 3 rows in set (0.00 sec) mysql USE forums; ERROR 1044: Access denied for user: '@localhost' to database 'forums' mysql USE forums; ERROR 1044: Access denied for user: '@localhost' to database 'forums' Notice that the errors tell you that you were really logged in as the anonymous user, ''@localhost. Most people, I believe, delete the anonymous user in the name of security, and to avoid this sort of thing. mysql Aborted Spider:/usr/bin# mysql -u ForumUser -p forums Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I expect this is either because you mistyped [EMAIL PROTECTED]'s password, or because mysql thinks [EMAIL PROTECTED] does not have access to the forums db. To test, try logging in without specifying the db, like this mysql -u ForumUser -p If that lets you in, try USE forums You can verify who mysql thinks you are with SELECT CURRENT_USER(); You can verify [EMAIL PROTECTED]'s privileges with SHOW GRANTS FOR [EMAIL PROTECTED]; Sincerely, Lee Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]