RE: Crystal Reports and MySQL
Sounds to me that you have some permission problems. I use Crystal Reports against MySQL and many other databases and it works fine. All you have to do is setup a ODBC DSN (user or system DSN) with a username and password. Make sure this user exists in MySQL so the user can get in. I would check your MySQL log files and system log files to see if you are getting denied access. > -Original Message- > From: Brei, Matt [mailto:[EMAIL PROTECTED] > Sent: Sunday, March 30, 2003 9:48 PM > To: [EMAIL PROTECTED] > Subject: Crystal Reports and MySQL > > > I am looking for any type of documentation on using Crystal > Reports with > MySQL. I have connected to mysqld from CR but when it shows the > selected database, there are no tables listed. Is there something I > have to configure in the MyODBC driver? Please help me out with any > advice you may have. > > Matt > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL with MyODBC & Access
Hi I recently upgraded MyODBC as you stated on our terminal server and two PCs. Now I tried running the install.bat file but it didn't seem to work so I just looked at the batch file and did it all manually. I believe for some odd reason the variable %windir% wasn't working. Either way the upgrade seem to go over well. The only issue I noticed since upgrading MyODBC to 2.50.39.00 is that now I have two MySQL drivers installed or so it appears. The first driver is "MySQL" and the second is "MySQL Driver". Both show the version at 2.50.39.00 and company as "MySQL AB". Is this a problem? The main reason I ask is for some odd reason on our terminal server the users using MyODBC are prompted with the MyODBC dialog box and seems to be missing the password. I've checked the registry and it's in their perfectly. If you enter the password manually when the dialog box appears it works with no problem. I do not want the users knowing this password (even though they could look it up in the registry, smile). How do I stop this dialog popping up and use the password I providing the System DSN profile? Is this issue maybe related to me having to MySQL drivers? > -Original Message- > From: Venu [mailto:[EMAIL PROTECTED]] > Sent: Friday, November 02, 2001 6:17 AM > To: Adam Douglas > Subject: RE: MySQL with MyODBC & Access > > Just download the driver files and copy them to system directory by > ovveriting previous files. - 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
MySQL with MyODBC & Access
I've recently setup MyODBC and MS Access to use a ODBC profile that is global to everyone. What I did was make a new user in MySQL and then create a MyODBC User DSN. I made the links to MySQL in Access using this profile. The problem I'm having is this, when you initially go into MS Access you have to always click on a specific table then close it and begin the task you wish to do. If you do not do this then nothing works. For some odd reason if you try to open a form or a link it will come up with the ODBC dialog box and always seems to have the database name wrong. The database name seems to match the DSN name. Any ideas why this is not working properly for me? If you need any more information please let me know. Oh yeah the part that makes this really odd is that I have a User DSN setup for myself and one other person for administration purposes only. This User DSN seems to work fine on both our machines. Really odd. Also I've noticed that using Access as a front end to MySQL it seems to not bring through data that is using a type of bigint. Is this a limitation of using Access as a front end? If so what other column types would MS Access not support? - 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: InnoDB??
> On Wed, Sep 26, 2001 at 09:57:33AM -0600, Adam Douglas wrote: > : Is there a list of features that InnoDB provides? Is InnoDB > represented as a > : table type? The reason I'm asking all these questions and > require details is > : because MIS at where I work would like to have such > features in a database. > : I'm trying to convince management and MIS to use MySQL as > our sole database. > > http://www.innodb.com/features.html > > There's your feature list. For 95% of use-cases, it's a completely > viable table type within MySQL (that's what it is, another table type, > like MyISAM or BDB). It's amazingly fast, but like I said, young, so > you may find issues. Alright, thanks. Saying that it's young, would you consider InnoDB to be stable and capable enough to be used in a business enviroment? - 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: Info Needed to Promote MySQL!!
> as appealing for most people. One of the changes that I > intend to make > is to allow the code to be smart enough to determine whether the user > has insert/update/delete permissions (verses just select) in which > case, it will link them to the master copy instead of a slave > for read > only access. > > That process has also allowed me to work around a couple of other > issues with ODBC that I do not care for. Another issue is that the > user name is stored in the registry and the MS Access user > name is not > used. I have code in the startup routines that rewrite the registry > keys to use the username of my choice (Currentuser()). The ODBC > password is stored in plain text in the registry. The shortcomings > there is obvious. So, I am rewriting the password as well. An > interesting thing to note is that ODBC only reads the registry keys > when relinking the tables, So I write the registry keys, delete the > links, relink the tables, and then re-write the registry keys with > bogus data. Anyone reading the registry keys won't recover the > username and password. One other side note is the password > that I use > is not the password that the user gives me. I hash their > password, and > give the hash to MySQL as a password. That prevents the users from > using anything but my application to access the tables. Most of the > business logic for our application is in the front end. It would be > unfortunate if someone was to side step that and edit the tables > without any validation or referential integrity. I would be interested in this code you are talking about. I not sure I understand how exactly you are doing this. Are you doing VB/macros? To detect insert/update/delete would be the best route I would think, instead of always deleting the linked tables each time the database is loaded. What would happen if someone tries to modify the schema of table through M$ Access? Would it not screw things up or I assume at the least limit your control. > -Original Message- > From: mysql-digest-help [mailto:[EMAIL PROTECTED]] > Sent: Monday, September 24, 2001 6:08 PM > To: venu; turner; mysql > Cc: adouglas > Subject: RE: RE: Info Needed to Promote MySQL!! > > > > As a quick note, I tried the same with MS SQL Server through > > its latest ODBC > > driver by adding the new column, and I couldn't see any > changes in the > > linked tables from Access 2002. > > > > So it shows clearly that, its some thing related to MS > > Access, not with the > > driver. It might be a bug from Access or there might be some > > setting that we > > need to do it, inorder to see the changes to the table structure. > > So is this going to be looked into on how to resolve this issue or is > it not > fix able unless M$ does something? > - 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
InnoDB??
I've just recently heard about InnoDB. Correct me if I'm wrong here but does InnoDB just enable MySQL to have transactions and row level locking? Is there more to InnoDB.. I went to the web site and didn't see any explanation of what InnoDB is other then the title on the first page. - 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
MyODBC vs. iODBC
Is there any advantage of using MyODBC vs. iODBC with MySQL as the backend and M$ Access as the front end? - 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: Info Needed to Promote MySQL!! - Solved
> What I did to handle global changes was have one access file on the > fileserver that everyone had a shortcut to. That way the updates > were global since they were all to the same file. How is the speed of running Queries, Forms and Reports from Access using linked tables to MySQL? I've found it quite slow. Tasks that use to take a few seconds now takes 3 minutes or more. > On Mon, Sep 24, 2001 at 04:19:55PM -0600, Adam Douglas wrote: > > > Another small research in Access made me to see the changed > > > table structure > > > completely. Please use this procedure to view or to refresh > > > links when the > > > structure or location of a linked table has changed. > > > > > > 1. Open the database in access that contains links to tables. > > > 2. On the Tools menu, point to Database Utilities, and then > > > click Linked > > > Table Manager. > > > 3. Select the check box for the tables whose links you want > > > to refresh. > > > 4. Click OK to refresh the links. > > > > All I can say is, Wow! Excellent response! Thanks! > > > > > So, this solves all your problems. Let me know whether it > > > helps you or not. > > > > Looks like it works great but how about this (smile).. > > > > Is there a way to have this update done on linked tables > automatically and > > is it possible to have this done on a global scale. Meaning > that the linked > > tables would work on all machines not a specific machine? > Would having a > > general ODBC username/password setup on multiple machines > resolve the > > problem? Sorry meant to ask this question before. > - 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: Info Needed to Promote MySQL!! - Solved
> Another small research in Access made me to see the changed > table structure > completely. Please use this procedure to view or to refresh > links when the > structure or location of a linked table has changed. > > 1. Open the database in access that contains links to tables. > 2. On the Tools menu, point to Database Utilities, and then > click Linked > Table Manager. > 3. Select the check box for the tables whose links you want > to refresh. > 4. Click OK to refresh the links. All I can say is, Wow! Excellent response! Thanks! > So, this solves all your problems. Let me know whether it > helps you or not. Looks like it works great but how about this (smile).. Is there a way to have this update done on linked tables automatically and is it possible to have this done on a global scale. Meaning that the linked tables would work on all machines not a specific machine? Would having a general ODBC username/password setup on multiple machines resolve the problem? Sorry meant to ask this question before. - 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: Info Needed to Promote MySQL!! - Solved
> Another small research in Access made me to see the changed > table structure > completely. Please use this procedure to view or to refresh > links when the > structure or location of a linked table has changed. > > 1. Open the database in access that contains links to tables. > 2. On the Tools menu, point to Database Utilities, and then > click Linked > Table Manager. > 3. Select the check box for the tables whose links you want > to refresh. > 4. Click OK to refresh the links. All I can say is, Wow! Excellent response! Thanks! > So, this solves all your problems. Let me know whether it > helps you or not. Looks like it works great but how about this (smile).. Is there a way to have this update done on linked tables automatically and is it possible to have this done on a global scale. Meaning that the linked tables would work on all machines not a specific machine? Would having a general ODBC username/password setup on multiple machines resolve the problem? Sorry meant to ask this question before. - 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: Info Needed to Promote MySQL!!
> As a quick note, I tried the same with MS SQL Server through > its latest ODBC > driver by adding the new column, and I couldn't see any changes in the > linked tables from Access 2002. > > So it shows clearly that, its some thing related to MS > Access, not with the > driver. It might be a bug from Access or there might be some > setting that we > need to do it, inorder to see the changes to the table structure. So is this going to be looked into on how to resolve this issue or is it not fix able unless M$ does something? - 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: Info Needed to Promote MySQL!!
> > Now I've found when I do changes to a table schema the > changes are not > > entirely at least reflected in a the same table linked in Access. > > I had once > > case where I changed a data type on one of the columns and > that data type > > was not changed on the linked table in Access. I had to remove > > the link and > > then recreate it. The table type I'm using is MyISAM. From > my experience > > with MyODBC it seems that it's not a live link correct? > This problem has > > occurred on three machines including mine. Do I have > something setup wrong > > then? Is there anything else that you need to know? > > Ok. I tried now, and even for me the latest table strcuture > is not visible > in the linked tables. I am using Access 2002. But I can see the > inserted/deleted rows of data in the linked table after I do > the refresh > from records menu. If it doesn't work, you need to use > filters from record > menu as follows: > > - Refresh > - Apply filter / sort > - Remove filter / sort I can see inserted/deleted rows as well it's just the schema issue that bothers me and was wondering if this was normal. > I will crosss check with the MyODBC code and Access calls, > why it is not > able to display the latest changes to the table structure, > and if I find > something, let you know. Also, what about other vendors ? Does Access > displays the changed table structure in linked tables ? That would be great, thanks! - 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: Info Needed to Promote MySQL!!
> What type of schema do mean here ? Because as you know, MySQL doesn't > explicitly supports any schema's except the catalogs( in MySQL term > 'database'). If you can elaborate what exactly the problem > you are facing, > then we can provide a rapid solution. > > Also, as for as I know, when I change something in my table > in MySQL server > side (including the table structure), it does reflect on the > linked tables > without any problems, and vice versa too. I have the following setup... - MySQL 3.22.32 on OpenBSD v2.9 - MyODBC on Windows '98 SE - MS Access 2000 Now I've found when I do changes to a table schema the changes are not entirely at least reflected in a the same table linked in Access. I had once case where I changed a data type on one of the columns and that data type was not changed on the linked table in Access. I had to remove the link and then recreate it. The table type I'm using is MyISAM. From my experience with MyODBC it seems that it's not a live link correct? This problem has occurred on three machines including mine. Do I have something setup wrong then? Is there anything else that you need to know? Also if I may ask, is there any difference between MyODBC for Unix going to Windows to MyODBC for Windows going to Unix? I'm assuming that all the difference is the starting point but achieves the same tasks. - 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: Info Needed to Promote MySQL!!
>> Now I have quite a bit of information on all these questions.. my >> main concern is to find a Windows MySQL GUI Client that will do everything >> in a similar fashion as Access. I believe we would need the >> basics but also >> including visual query builder and reports. I've recently found Artronic >> MySQL Admin which is quite similar to Access but has bugs and is >> lacking in >> the visual query builder and reports. >Check the clients section from http://www.mysql.com/downloads/contrib.html, >where you can find the solution for this. There are 'n' GUI utilities using >MyODBC as the underlying layer to control MySQL. Some of them are really >doing a great job. I've looked at the list and have tried just about everyone on the list for GUI Clients there is. The problem that seems to be an issue is all applications seem to be in the beginning stages not developed to a stable point all around. Need visual query, report & forum builder. Some seem to have it but very limiting. Just wondering if there is anything else not listed or that I've missed. I've found MySQL Admin, M2D and Mascon to the best ones so far to use, all having their good and bad points. - 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
Info Needed to Promote MySQL!!
Hi. I'm looking for any support/help that one is willing to give to assist me in convincing the company I work for to switch over to MySQL as there sole database system. We currently use MS Access which we all know is a some what to be desired database. I have to answer the following questions and then present to management. 1. Replication between 2 sites via the internet over a VPN or just using a secure connection. 2. Transaction processing capabilities with the ability to roll-back a transaction. 3. A Windows based interface that is easy to use, similar to Access look/feel. 4. ODBC compliant database and drivers 5. Ability to provide connections to website via PHP 6. Commercial support.. consultants, services, etc. Now I have quite a bit of information on all these questions.. my main concern is to find a Windows MySQL GUI Client that will do everything in a similar fashion as Access. I believe we would need the basics but also including visual query builder and reports. I've recently found Artronic MySQL Admin which is quite similar to Access but has bugs and is lacking in the visual query builder and reports. Any information that would assist me would be greatly appreciated. I would love to have this excellent database a part of the company I work for... it's going to be a tough battle (smile). --- Adam Douglas Webmaster Venmar CES Inc. E-mail: [EMAIL PROTECTED] Ph: (306) 242-3663 x285 Fx: (306) 242-3484 - 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
MySQL Lost Connection...
Ok I've finally gotten MySQL 3.23.37 installed and operating. Thanks to all
that helped! The problem I'm now experiencing is when I try to interface
with MySQL via PHP. MySQL seems to work fine by logging into MySQL directly
and through a MySQL GUI Client. I get the following error...
Warning: MySQL Connection Failed: Lost connection to MySQL server during
query...
I'm assuming this is a MySQL issue since everything worked fine before the
upgrade from MySQL 3.32.22 to MySQL 3.23.37.
I'm doing very simple query to see if it works and no success. Even code
that worked before I upgraded doesn't work anymore. Here's the test query I
made... (BTW, the below query works fine via MySQL/MySQL GUI Client)
$szQuery = "SELECT * FROM People WHERE First_Name='adouglas'";
$dbconnection = mysql_connect("207.195.58.33","webuser","adamacc226");
mysql_select_db("venmar", $dbConnection) or die("could not connect to venmar
for security authentication.");
$saResults = mysql_query($szQuery, $dbConnection) or die ("2could not query
venmar for security authentication.");
$obResults = mysql_fetch_row($saResults)
echo "$obResults[0], $obResults[1], $obResults[2], $obResults[3],
$obResults[4], $obResults[5]";
The MySQL Database has it's own dedicated machine running on OpenBSD 2.9.
The web server accessing the MySQL Database is on it's own machine with
Apache 1.3.12 and OpenBSD 2.9. Any other details needed let me know.
+--+---+
| Variable_name| Value |
+--+---+
| Aborted_clients | 0 |
| Aborted_connects | 6 |
| Bytes_received | 4125 |
| Bytes_sent | 45247 |
| Connections | 15|
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files| 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 4 |
| Handler_read_first | 7 |
| Handler_read_key | 16|
| Handler_read_next| 2 |
| Handler_read_prev| 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next| 2597 |
| Handler_update | 1 |
| Handler_write| 4 |
| Key_blocks_used | 4 |
| Key_read_requests| 28|
| Key_reads| 4 |
| Key_write_requests | 18|
| Key_writes | 12|
| Max_used_connections | 1 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 22|
| Open_files | 44|
| Open_streams | 0 |
| Opened_tables| 38|
| Questions| 135 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 15|
| Slave_running| OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes| 0 |
| Sort_range | 0 |
| Sort_rows| 0 |
| Sort_scan| 0 |
| Table_locks_immediate| 52|
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 14|
| Threads_connected| 1 |
| Threads_running | 1 |
| Uptime | 7235 |
+--+---+
+-+-
--+
| Variable_name | Value
|
+-+-
--+
| ansi_mode | OFF
|
| back_log| 50
|
| basedir | /usr/local/
|
| binlog_cache_size | 32768
|
| character_set | latin1
|
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru
latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr
win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | /var/mysql/
|
| delay_key_write | ON
|
| delayed_insert_limit| 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| flush | OFF
|
| flush_time | 0
|
| have_bdb| NO
|
| have_gemini | NO
|
| have_innodb | NO
|
| have_isam | YES
|
| have_raid
RE: MySQL Connection Failure: Lost...
Yes I'm missing the query line in this posting my error (frustration is
taking over me ). The error occurs on line 5, which is the $dbconnection
line.
> -Original Message-
> From: Adam Douglas [mailto:[EMAIL PROTECTED]]
> Sent: Monday, August 13, 2001 2:00 PM
> To: Mysql@Lists. Mysql. Com (E-mail); '[EMAIL PROTECTED]'
> Subject: MySQL Connection Failure: Lost...
>
>
> Ok I've finally gotten MySQL 3.23.37 installed and operating.
> Thanks to all
> that helped! The problem I'm now experiencing is when I try
> to interface
> with MySQL via PHP. MySQL seems to work fine by logging into
> MySQL directly
> and through a MySQL GUI Client. I get the following error...
>
> Warning: MySQL Connection Failed: Lost connection to MySQL
> server during
> query...
>
> I'm doing very simple query to see if it works and no
> success. Even code
> that worked before I upgraded doesn't work anymore. Here's
> the test query I
> made... (BTW, the below query works fine via MySQL/MySQL GUI Client)
>
> $szQuery = "SELECT * FROM People WHERE First_Name='adam'";
> $dbconnection = mysql_connect("host","username","password");
> mysql_select_db($szQuery, $dbConnection);
> $obResults = mysql_fetch_row($saResults)
>
> echo "$obResults[0], $obResults[1], $obResults[2], $obResults[3],
> $obResults[4], $obResults[5]";
>
>
> The MySQL Database has it's own dedicated machine running on
> OpenBSD 2.9.
> The web server accessing the MySQL Database is on it's own
> machine with
> Apache 1.3.12 and OpenBSD 2.9. Any other details needed let me know.
>
> -
> 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
MySQL Connection Failure: Lost...
Ok I've finally gotten MySQL 3.23.37 installed and operating. Thanks to all
that helped! The problem I'm now experiencing is when I try to interface
with MySQL via PHP. MySQL seems to work fine by logging into MySQL directly
and through a MySQL GUI Client. I get the following error...
Warning: MySQL Connection Failed: Lost connection to MySQL server during
query...
I'm doing very simple query to see if it works and no success. Even code
that worked before I upgraded doesn't work anymore. Here's the test query I
made... (BTW, the below query works fine via MySQL/MySQL GUI Client)
$szQuery = "SELECT * FROM People WHERE First_Name='adam'";
$dbconnection = mysql_connect("host","username","password");
mysql_select_db($szQuery, $dbConnection);
$obResults = mysql_fetch_row($saResults)
echo "$obResults[0], $obResults[1], $obResults[2], $obResults[3],
$obResults[4], $obResults[5]";
The MySQL Database has it's own dedicated machine running on OpenBSD 2.9.
The web server accessing the MySQL Database is on it's own machine with
Apache 1.3.12 and OpenBSD 2.9. Any other details needed let me know.
-
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: Upgrading to 3.23.37 from 3.22.32
Ok but I've have chmod 777 on a couple of tables and I still receive table in read only message. Plus I was under the understanding that you only need say 700 because the mysql daemon it self would be modifying the files directly and not the client using MySQL. > -Original Message- > From: Gerald Clark [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, August 08, 2001 7:38 AM > To: Adam Douglas > Cc: [EMAIL PROTECTED] > Subject: Re: Upgrading to 3.23.37 from 3.22.32 > > > Except, mysql needs write permission. > > Adam Douglas wrote: > > > MySQL reports read only table. As for file permissions, > everything is fine. > > Everyone has read and execute privileges. > > > > > >> They are probably not owned by mysql anymore. > >> > >> Adam Douglas wrote: > >> > >> > >>> I just upgraded MySQL from 3.22.32 to MySQL 3.23.37. All I did was > >>> pkg_delete the old MySQL and install the new one. Before I > >> > >> delete the old > >> > >>> MySQL I copied all the database files to a temporary > >> > >> location. Once the new > >> > >>> installation was successful I copied over all the database > >> > >> files into the > >> > >>> proper location. > >>> > >>> My problem is I seem to have 3 extra tables now, A-1, A-9 > >> > >> and A-ce. The > >> > >>> funny thing is they all seem to be linked to my tables. > >> > >> These tables to my > >> > >>> knowledge were not present in the MySQL 3.22.32. Also I > >> > >> can't seem to remove > >> > >>> the test user from mysql->db, MySQL says it's read only. I > >> > >> never made the > >> > >>> table read only and doesn't seem to be. - 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: Upgrading to 3.23.37 from 3.22.32
That sounds more accurate to me, but I still seem to be getting table is read only error messages when trying to do updates/grants (haven't tried inserts). I'm lost as to why it's in read only mode. > -Original Message- > From: Stefan Hinz [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, August 08, 2001 2:23 AM > To: Adam Douglas; 'Gerald Clark'; [EMAIL PROTECTED] > Subject: Re: Upgrading to 3.23.37 from 3.22.32 > > > Dear Gerald, > > does mysqld run as user "mysql"? And does user mysql have the correct > rights for datadir (e. g. /var/lib/mysql/) and all subdirectories? > > (The MySQL Daemon is supposed to be the only one who has rights for > datadir. So, this could be 700 with user mysql as owner. No one else > needs rights to this directory, as no one else will access the > directories and files in datadir directly, only the daemon / > server has > to.) > - Original Message - > From: "Adam Douglas" <[EMAIL PROTECTED]> > To: "'Gerald Clark'" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Tuesday, August 07, 2001 11:02 PM > Subject: RE: Upgrading to 3.23.37 from 3.22.32 > > > > MySQL reports read only table. As for file permissions, > everything is > fine. > > Everyone has read and execute privileges. > > > > > > > > They are probably not owned by mysql anymore. > > > > > > Adam Douglas wrote: > > > > > > > I just upgraded MySQL from 3.22.32 to MySQL 3.23.37. > All I did was > > > > pkg_delete the old MySQL and install the new one. Before I > > > delete the old > > > > MySQL I copied all the database files to a temporary > > > location. Once the new > > > > installation was successful I copied over all the database > > > files into the > > > > proper location. > > > > > > > > My problem is I seem to have 3 extra tables now, A-1, A-9 > > > and A-ce. The > > > > funny thing is they all seem to be linked to my tables. > > > These tables to my > > > > knowledge were not present in the MySQL 3.22.32. Also I > > > can't seem to remove > > > > the test user from mysql->db, MySQL says it's read only. I > > > never made the > > > > table read only and doesn't seem to be. > > > > > > > > Anyone have any ideas of what is going on? I feel kind of > > > lost now, any help > > > > would be greatly appreciated. - 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: Bad path to ibpthread.so.14.20?
Alright, but what if I do not have a ld.so.conf file? Do I just create one in /etc/? > -Original Message- > From: Gerald Clark [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, August 08, 2001 8:26 AM > To: Adam Douglas > Cc: Mysql@Lists. Mysql. Com (E-mail) > Subject: Re: Bad path to ibpthread.so.14.20? > > > 1. Add the path to /etc/ld.so.conf > 2. Run ldconfig > > Adam Douglas wrote: > > > I'm installed MySQL 3.23.37 and I can't get it started. I > know there has be > > number of posts regarding such a problem but I haven't seen > a resolution to > > fix mine specificly. I get this error when I try and load mysqld > > > > /usr/libexec/ld.so: my_print_defaults: libpthread.so.14.20: > No such file or > > directory > > Starting mysqld daemon with databases from /var/mysql > > 010807 15:42:38 mysqld ended > > > > Now I know the file is located at > /usr/local/lib/pth/libpthread.so.14.20 but > > where is the location for me to fix this bad path? Funny > thing is I had > > MySQL Daemon running just fine but when I did a reboot I > haven't been able > > to start it since. I can go ldconfig -m /usr/local/lib/pth/ > and that seems > > to fix the problem for that session. Once a reboot of the > system is done the > > path is no longer known. > > > > Frustration is settling in very quickly . - 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
Bad path to ibpthread.so.14.20?
I'm installed MySQL 3.23.37 and I can't get it started. I know there has be number of posts regarding such a problem but I haven't seen a resolution to fix mine specificly. I get this error when I try and load mysqld /usr/libexec/ld.so: my_print_defaults: libpthread.so.14.20: No such file or directory Starting mysqld daemon with databases from /var/mysql 010807 15:42:38 mysqld ended Now I know the file is located at /usr/local/lib/pth/libpthread.so.14.20 but where is the location for me to fix this bad path? Funny thing is I had MySQL Daemon running just fine but when I did a reboot I haven't been able to start it since. I can go ldconfig -m /usr/local/lib/pth/ and that seems to fix the problem for that session. Once a reboot of the system is done the path is no longer known. Frustration is settling in very quickly . - 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: Upgrading to 3.23.37 from 3.22.32
MySQL reports read only table. As for file permissions, everything is fine. Everyone has read and execute privileges. > > They are probably not owned by mysql anymore. > > Adam Douglas wrote: > > > I just upgraded MySQL from 3.22.32 to MySQL 3.23.37. All I did was > > pkg_delete the old MySQL and install the new one. Before I > delete the old > > MySQL I copied all the database files to a temporary > location. Once the new > > installation was successful I copied over all the database > files into the > > proper location. > > > > My problem is I seem to have 3 extra tables now, A-1, A-9 > and A-ce. The > > funny thing is they all seem to be linked to my tables. > These tables to my > > knowledge were not present in the MySQL 3.22.32. Also I > can't seem to remove > > the test user from mysql->db, MySQL says it's read only. I > never made the > > table read only and doesn't seem to be. > > > > Anyone have any ideas of what is going on? I feel kind of > lost now, any help > > would be greatly appreciated. - 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
Upgrading to 3.23.37 from 3.22.32
I just upgraded MySQL from 3.22.32 to MySQL 3.23.37. All I did was pkg_delete the old MySQL and install the new one. Before I delete the old MySQL I copied all the database files to a temporary location. Once the new installation was successful I copied over all the database files into the proper location. My problem is I seem to have 3 extra tables now, A-1, A-9 and A-ce. The funny thing is they all seem to be linked to my tables. These tables to my knowledge were not present in the MySQL 3.22.32. Also I can't seem to remove the test user from mysql->db, MySQL says it's read only. I never made the table read only and doesn't seem to be. Anyone have any ideas of what is going on? I feel kind of lost now, any help would be greatly appreciated. - 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: data checking during input?
Just do a SELECT query to MySQL before you do an INSERT then compare the two. Compare whatever you don't want to be a duplicate. In most cases you'll have a central point at which you should be able to check but it all depends on your setup/situation. Understand? I could go into a more in depth detail if needed. > -Original Message- > From: Chip [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 03, 2001 12:08 AM > To: [EMAIL PROTECTED] > Subject: data checking during input? > > > I have a database I enter data into from a web based php form. > I wonder if there is a way to have mysql check the database for > a duplicate entry before it gets written into the database? And > inform me of such so I will not have duplicate entries? - 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: generate database script from existing table?
The only place I've seen a tool like this is in urSQL (a MySQL GUI Client for Windows 95/98/NT 4.0). I'm just using the unregistered version and this feature seems to work fine. You can grab the program from the following location, http://www.urbanresearch.com/software/utils/urbsql/index.html. > -Original Message- > From: Tong Kiat, Chiah [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 03, 2001 4:45 AM > To: [EMAIL PROTECTED] > Subject: generate database script from existing table? > > I just took over a mysql database with no documenation. Is there any > tools or anyway I could generate a database/table creation script from > the current database? This is because I need to recreate the database > on another machine and currently I have no way of doing so. - 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: Access to MySQL Column Type Error
Well I did the trace and it's about 2.81MBs. What exactly do you need out of it? I didn't notice much help from it but then again I'm not very knowledge in that area. Considering the size do you still want me to directly e-mail it to you? One other thing I forgot to mention was the data in the Fax_Machine_ID field starts with a * and then 3 integers (max size 4). There are the odd few that do not have a * in front and those seem to get through. Would this maybe be taking as a wildcard and I would have to escape it some how so I will be able to append the query over to MySQL successfully? > Hi ! > > Can you send me the ODBC trace ? > > Thanks in advance > venu > > > -Original Message- > > From: Adam Douglas [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, August 02, 2001 10:52 AM > > To: '[EMAIL PROTECTED]' > > Subject: Access to MySQL Column Type Error > > > > > > I'm trying to do an append query in Access to MySQL on > CompanyRep table. > > BTW, I'm using MyODBC to allow connectivity between MS-Access and > > MySQL. Now > > I've done many queries like this with no problem. For some > reason this > > append query will not work anymore. I get the following > error message > > > > "Microsoft Access can't append all the records to the table" > > > > "Microsoft Access set 148 field(s) to Null due to a type > > conversion failure, > > and it didn't add 0 record(s) due to key violations, 0 record(s) > > due to lock > > violation, and 0 record(s) due to validation rule violations." > > > > This error seems to occur only on the Fax_Machine_ID field > based on what I > > see missing for data in MySQL. Any help would be greatly > appreciated, > > thanks! > > > > Schema for Fac_Machine_ID field on Access... > > ++--+--+-+-+-- > > -- > > + > > | Field | Date_Type| Null | Key | Default | Extra > > | > > ++--+--+-+-+-- > > -- > > + > > | Fax_Machine_ID | text(4) | YES | | NULL| > > | > > ++--+--+-+-+-- > > -- > > + > > > > Schema for Fax_Machine_ID field on MySQL... > > > > ++--+--+-+-+-- > > -- > > + > > | Field | Type | Null | Key | Default | Extra > > | > > ++--+--+-+-+-- > > -- > > + > > | Fax_Machine_ID | varchar(4) | YES | | NULL| > > | > > ++--+--+-+-+-- > > -- > > + - 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: Query Acting Weird
> Adam Douglas schrieb am Donnerstag, 2. August 2001, 19:14:15: > > > I have a group of 3 queries that's used to find our > representative for > > Unitary, Applied and Wholesale based on a Zip Code. This > all is executed > > through PHP to MySQL. The three queries work fine and get > the results I > > want. The problem is the 3 queries seem to work one minute > but not the next. > > You could enter say 3 or 4 zip codes and have it return > results with no > > problem, but on the 5 try it will crap out. This seems to > be random, one > > minute it wouldn't work on the first try and then the next > it will. When the > > 3 queries crap out it renders MySQL useless, you can send > or do any thing to > > MySQL when the 3 queries crap out. It puts my CPU usage to > 99.0% and stays > > roughly there forever. I've left the query running for more > then 8 hours and > > still nothing seems to be returned. Can anyone direct me in > the right > > direction on how to resolve this problem? > > I didn't have a look at your code as I don't think it is probable > that you will have a problem here. These days, I am hunting > problems similarly weird. I'm looking into faulty hardware now. > > Yesterday, we changed 2 of the 3 128 MB RAM modules, which has > reduced problem frequency from 30 minutes to 10 hours. We see > from hostname.err that the problem occurs exactly when the log > records > > 010802 22:35:30 Aborted connection 231 to db: > 'pferdezeitung' user: 'pferdezeitung' host: `localhost' > (Got an error writing communication packets) > > So we will replace the 3rd module or all three to see what will > happen then. It might be the hard disk, though, or the > controller, or something else. > > Also, it may be a good idea to look at the hostname.log to see > what mysql does when running wild. I did this to test queries > that seemingly produced errors, but when reproduced, they did > not, so this was proof that neither the query nor the data had > problems. Mmm.. that's interesting, I will have to look into checking out those two files. But I believe I've found the reason as to why the queries are acting crazy. I was reading through the MySQL manual today and thought I should read the OpenBSD Notes (should have a long time ago, ). Well to my surprise it states that "OpenBSD 2.8 has a threading bug which causes problems with MySQL" and "The symptoms of this threading bug are: slow response, high load, high cpu usage, and crashes". So I was going to upgrade OpenBSD to 2.9 anyways so why not do it now and solve my problems :-). Also upgrade MySQL as well. Do you have any tips on upgrading either OpenBSD or MySQL? - 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 add an excel file directly to MySQL database using PHP?
Well you have a couple options, you could export the Excel file to a delimited text file and then import into MySQL. You could also just use the Excel file and import directly into MySQL with the use of MyODBC and MS Access. That's all I can think of right now. Either way would work fine I believe. > -Original Message- > From: Yeong CN [mailto:[EMAIL PROTECTED]] > Sent: Thursday, August 02, 2001 10:43 PM > To: [EMAIL PROTECTED] > Subject: How to add an excel file directly to MySQL database > using PHP? > > > How can I add an excel file with *.xls extension > directly into MySQL database..is it possible to do or > I need to convert it into *.csv format or *.txt format > first? - 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
Query Acting Weird
I have a group of 3 queries that's used to find our representative for Unitary, Applied and Wholesale based on a Zip Code. This all is executed through PHP to MySQL. The three queries work fine and get the results I want. The problem is the 3 queries seem to work one minute but not the next. You could enter say 3 or 4 zip codes and have it return results with no problem, but on the 5 try it will crap out. This seems to be random, one minute it wouldn't work on the first try and then the next it will. When the 3 queries crap out it renders MySQL useless, you can send or do any thing to MySQL when the 3 queries crap out. It puts my CPU usage to 99.0% and stays roughly there forever. I've left the query running for more then 8 hours and still nothing seems to be returned. Can anyone direct me in the right direction on how to resolve this problem? Now I'm not sure if this would happen if I sent the queries directly to MySQL, as I am not aware of a way to send all three queries one after another (no delay). Oh also I've noticed that MySQL seems to lose connection and tries to reconnect. This too happens randomly. Losing connection seems to happen when I run the queries directly in MySQL (one query at a time). I don't believe this would be a PHP problem and that is why I've posted this message here. Here's the three queries... SELECT Company.Company_Name, Company.Phone, Company.Fax, Company.Email, Company.Web_Page, CompanyShipInfo.Ship_Addr1, CompanyShipInfo.Ship_Addr2, CompanyShipInfo.Ship_City, CompanyShipInfo.Ship_Postal_Code, Countries.Country_Name, ProvincesStates.Prov_State_Name FROM PostalCodeInfo,PostalCodeReps,Company,CompanyShipInfo,Countries,ProvincesSta tes,CompanyRep WHERE ( PostalCodeInfo.Postal_Code_Info_ID = PostalCodeReps.Postal_Code_Info_ID AND PostalCodeInfo.Postal_Code = "12110" AND Company.Company_ID = PostalCodeReps.Company_ID_Unitary AND Company.Company_Status_ID = 1 AND (Company.Company_Type_ID = 12 OR Company.Company_Type_ID = 14) AND CompanyShipInfo.Company_ID = Company.Company_ID AND Countries.Country_ID = CompanyShipInfo.Country_ID AND ProvincesStates.Prov_State_ID = CompanyShipInfo.Prov_State_ID AND CompanyRep.Company_ID = Company.Company_ID AND CompanyRep.Invoice_Only = 0 ) SELECT Company.Company_Name, Company.Phone, Company.Fax, Company.Email, Company.Web_Page, CompanyShipInfo.Ship_Addr1, CompanyShipInfo.Ship_Addr2, CompanyShipInfo.Ship_City, CompanyShipInfo.Ship_Postal_Code, Countries.Country_Name, ProvincesStates.Prov_State_Name FROM PostalCodeInfo,PostalCodeReps,Company,CompanyShipInfo,Countries,ProvincesSta tes,CompanyRep WHERE ( PostalCodeInfo.Postal_Code_Info_ID = PostalCodeReps.Postal_Code_Info_ID AND PostalCodeInfo.Postal_Code = "12110" AND Company.Company_ID = PostalCodeReps.Company_ID_Applied AND Company.Company_Status_ID = 1 AND (Company.Company_Type_ID = 12 OR Company.Company_Type_ID = 14) AND CompanyShipInfo.Company_ID = Company.Company_ID AND Countries.Country_ID = CompanyShipInfo.Country_ID AND ProvincesStates.Prov_State_ID = CompanyShipInfo.Prov_State_ID AND CompanyRep.Company_ID = Company.Company_ID AND CompanyRep.Invoice_Only = 0 ) SELECT Company.Company_Name, CompanyShipInfo.Ship_Addr1, CompanyShipInfo.Ship_Addr2, CompanyShipInfo.Ship_City, ProvincesStates.Prov_State_Name, Countries.Country_Name, CompanyShipInfo.Ship_Postal_Code, Company.Phone, Company.Fax, Company.Email, Company.Web_Page FROM CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, ProvincesStates, Countries WHERE ( CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID AND PostalCodeInfo.Postal_Code=98188 AND CompanyRep.Company_ID=Company.Company_ID AND CompanyShipInfo.Company_ID=Company.Company_ID AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID AND CompanyShipInfo.Country_ID=Countries.Country_ID AND Company.Company_Status_ID=1 AND CompanyRep.Invoice_Only=0 AND CompanyRep.Wholesale_Rep=-1 ) - 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: Query Not Using Indexing
> Adam Douglas writes: > > Correct 6 tables are being joined. Here's the explain of > the query below > > (btw, I'm not getting a index on CompanyShipInfo and > PostalCodeInfo not > > CompanyRep like I previous said). Excuse me if this seems > dumb but I'm not > > sure what you are saying in the last two comments. I > understand an index > > could be missing but where? I'll put the indexing/schema > below the explain > > for you. Basically I need to get the query to run faster. I > have three > > queries one for our Applied Product, Unitary Product and > Wholesalers. A user > > will send to the query a US Zip Code and based on that zip > code it will > > return the representative(s) that looks after that area > (according to zip > > code) for each product line. It takes about 14 seconds to > run this query and > > two others via PHP. Now this seems to vary and sometimes > renders mysqld > > useless. Mysqld will not accept at times user logins or > even checking for > > database status. The CPU usage on this process when it > doesn't work goes > > from 99.0% to 99.02%. It doesn't seem to matter how long > you leave it, never > > seems to end. Now the biggest tables I have is PostalCodeInfo and > > PostalCodeReps. Both contain about 42657 rows, so table > size is not a > > problem. > > > > If you scrutinize more closely your query and your table you can > notice that : > > - all tables are not related with common columns > > - There are two indices missing > > - MySQL is using all of the indices available except on some small > tables, like CompanyShipInfo Ahh what do you mean "all tables are not related with common columns"? They are not suppose to be related with a common columns. PostalCodeInfo and Company table are completely two different things. Yes that is correct, I've resolved the issue with PostalCodeInfo for indexing in the query. I had column types not matching the same through my tables. But I'm still lost as to why I can not get CompanyShipInfo to use indexing in the query. That's find but shouldn't I still be able to go down even further as to how many rows are analyzed? CompanyShipInfo has 235 rows and all are being read by the query. Here's the new explain of the query below. Another thing that happens is when I tried to ALTER PostalCodeInfo and PostalCodeReps table schemas MySQL seemed to be hung and put the CPU usage at 99.0% to 99.02% (never ending it seems). Both those tables only have 42657 rows each. Any ideas why these two tables seem to hang when anything major is done on them? Funny thing is it seems to be random when you execute a query that uses these two tables. Is there something I'm missing for MySQL configuration or bad table design? I'll post the table schema's below the explain query. Explain of Query +-++---+ ---+-+---+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-++---+ ---+-+---+--++ | CompanyShipInfo | ALL| Company_ID,Prov_State_ID,Country_ID | NULL |NULL | NULL | 345 || | Company | eq_ref | PRIMARY,Company_Status_ID | PRIMARY | 3 | CompanyShipInfo.Company_ID|1 | where used | | CompanyRep | ref| Company_ID,Wholesale_Rep,Invoice_Only | Company_ID| 3 | Company.Company_ID|1 | where used | | PostalCodeInfo | ref| Prov_State_ID,Postal_Code | Prov_State_ID | 2 | CompanyShipInfo.Prov_State_ID | 223 | where used | | ProvincesStates | eq_ref | PRIMARY | PRIMARY | 2 | CompanyShipInfo.Prov_State_ID |1 || | Countries | eq_ref | PRIMARY | PRIMARY | 2 | CompanyShipInfo.Country_ID|1 || +-++---+ ---+-+---+--++ 6 rows in set (0.00 sec) PostalCodeInfo Schema +-+--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+--- -+ | Postal_Code_Info_ID | mediumint(9) | | PRI | 0 | auto_increment | | City| varchar(50) | YES |
RE: Query Not Using Indexing
> -Original Message- > From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 31, 2001 1:27 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: Query Not Using Indexing > > > Adam Douglas writes: > > I'm running MySQL v3.22.32. Here's my problem I run the following > > select query and both CompanyRep and PostalCodeInfo tables are not > > using my Indexing. Why? How can I get these two tables to > use indexing > > to speed up my query. You can look at my indexing on the two tables > > below the query. > > > > Any help would be greatly appreciated, thanks! > > > > SELECT > > Company.Company_Name, > > CompanyShipInfo.Ship_Addr1, > > CompanyShipInfo.Ship_Addr2, > > CompanyShipInfo.Ship_City, > > ProvincesStates.Prov_State_Name, > > Countries.Country_Name, > > CompanyShipInfo.Ship_Postal_Code, > > Company.Phone, > > Company.Fax, > > Company.Email, > > Company.Web_Page > > FROM > > CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, > > ProvincesStates, Countries > > WHERE > > ( > > CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID > > AND PostalCodeInfo.Postal_Code=98188 > > AND CompanyRep.Company_ID=Company.Company_ID > > AND CompanyShipInfo.Company_ID=Company.Company_ID > > AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID > > AND CompanyShipInfo.Country_ID=Countries.Country_ID > > AND Company.Company_Status_ID=1 > > AND CompanyRep.Invoice_Only=0 > > AND CompanyRep.Wholesale_Rep=-1 > > ) > > Hi! > > You have 6 tables in a join. > > Possible causes of not using indices could be some index missing or > low number of rows. > > Beside that, you do have Cartesian product as there are some tables > not related. Correct 6 tables are being joined. Here's the explain of the query below (btw, I'm not getting a index on CompanyShipInfo and PostalCodeInfo not CompanyRep like I previous said). Excuse me if this seems dumb but I'm not sure what you are saying in the last two comments. I understand an index could be missing but where? I'll put the indexing/schema below the explain for you. Basically I need to get the query to run faster. I have three queries one for our Applied Product, Unitary Product and Wholesalers. A user will send to the query a US Zip Code and based on that zip code it will return the representative(s) that looks after that area (according to zip code) for each product line. It takes about 14 seconds to run this query and two others via PHP. Now this seems to vary and sometimes renders mysqld useless. Mysqld will not accept at times user logins or even checking for database status. The CPU usage on this process when it doesn't work goes from 99.0% to 99.02%. It doesn't seem to matter how long you leave it, never seems to end. Now the biggest tables I have is PostalCodeInfo and PostalCodeReps. Both contain about 42657 rows, so table size is not a problem. EXPLAIN Query +-++---+ +-+---+---+- -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-++---+ +-+---+---+- -+ | CompanyShipInfo | ALL| Company_ID| NULL |NULL | NULL | 345 | | | Company | eq_ref | PRIMARY,Company_Status_ID | PRIMARY | 3 | CompanyShipInfo.Company_ID| 1 | where used | | CompanyRep | ref| Company_ID,Wholesale_Rep,Invoice_Only | Company_ID | 3 | Company.Company_ID| 1 | where used | | PostalCodeInfo | ALL| Prov_State_ID | NULL |NULL | NULL | 42657 | range checked for each record (index map: 2) | | ProvincesStates | eq_ref | PRIMARY | PRIMARY | 2 | CompanyShipInfo.Prov_State_ID | 1 | | | Countries | eq_ref | PRIMARY | PRIMARY | 2 | CompanyShipInfo.Country_ID| 1 | | +-++---+ +-+---+---+- -+ 6 rows in set (0.00 sec) CompanyShipInfo Table Schema +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+-
Query Not Using Indexing
I'm running MySQL v3.22.32. Here's my problem I run the following select query and both CompanyRep and PostalCodeInfo tables are not using my Indexing. Why? How can I get these two tables to use indexing to speed up my query. You can look at my indexing on the two tables below the query. Any help would be greatly appreciated, thanks! SELECT Company.Company_Name, CompanyShipInfo.Ship_Addr1, CompanyShipInfo.Ship_Addr2, CompanyShipInfo.Ship_City, ProvincesStates.Prov_State_Name, Countries.Country_Name, CompanyShipInfo.Ship_Postal_Code, Company.Phone, Company.Fax, Company.Email, Company.Web_Page FROM CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, ProvincesStates, Countries WHERE ( CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID AND PostalCodeInfo.Postal_Code=98188 AND CompanyRep.Company_ID=Company.Company_ID AND CompanyShipInfo.Company_ID=Company.Company_ID AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID AND CompanyShipInfo.Country_ID=Countries.Country_ID AND Company.Company_Status_ID=1 AND CompanyRep.Invoice_Only=0 AND CompanyRep.Wholesale_Rep=-1 ) mysql> show index from CompanyRep; +++---+--+---+-- -+-+--+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +++---+--+---+-- -+-+--+ | CompanyRep | 0 | PRIMARY |1 | CompanyRep_ID | A | 213 | NULL | | CompanyRep | 1 | Company_ID|1 | Company_ID | A |NULL | NULL | | CompanyRep | 1 | Wholesale_Rep |1 | Wholesale_Rep | A |NULL | NULL | | CompanyRep | 1 | Invoice_Only |1 | Invoice_Only | A |NULL | NULL | +++---+--+---+-- -+-+--+ mysql> show index from PostalCodeInfo; +++---+--+-- ---+---+-+--+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +++---+--+-- ---+---+-+--+ | PostalCodeInfo | 0 | PRIMARY |1 | Postal_Code_Info_ID | A | 42657 | NULL | | PostalCodeInfo | 1 | Prov_State_ID |1 | Prov_State_ID | A |NULL | NULL | | PostalCodeInfo | 1 | Country_ID|1 | Country_ID | A |NULL | NULL | | PostalCodeInfo | 1 | Postal_Code |1 | Postal_Code | A |NULL | NULL | +++---+--+-- ---+---+-+--+ - 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
MySQL GUI Client
Hi, I just downloaded the MySQL GUI Client and was trying to set it up. The client keeps saying "Access denied for user: 'adouglas@' (Using password: YES)". I've typed in the Host Name (IP Address) and have the proper user name. Also I have the proper grant tables set for adouglas to access it from the machine I'm on. I'm also using port 3306. Do you have any ideas on how to solve this problem? --- Adam Douglas Webmaster Venmar CES Inc. E-mail: [EMAIL PROTECTED] Ph: (306) 242-3663 x285 Fx: (306) 242-3484 - 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
