Re: How thread-safe is mysql_real_connect()?
The Windows DLL is thread safe. You do not have to call my_init() and
my_thread_init() because Windows DLLs receive events when they are attached to
a new process and when they are attached to a new thread in a process. This is
one of the nicer features of Windows shared libraries. Other than that, you
don't have to do anything special. I am a heavy user of libmysql under Win32.
You simply mysql_init() your MYSQL struct, and then mysql_real_connect() and
you're ready to mysql_query().
You should not call my_init() or my_thread_init() as the previous poster
suggested. This could result in memory leaks.
From libmysql/dll.c
BOOL APIENTRY LibMain(HANDLE hInst,DWORD ul_reason_being_called,LPVOID
lpReserved)
{
switch (ul_reason_being_called) {
case DLL_PROCESS_ATTACH: /* case of libentry call in win 3.x */
if (!inited++)
{
s_hModule=hInst;
libmysql_init();
main_thread=GetCurrentThreadId();
}
break;
case DLL_THREAD_ATTACH:
threads++;
my_thread_init();
break;
case DLL_PROCESS_DETACH: /* case of wep call in win 3.x */
if (!--inited) /* Safety */
{
/* my_thread_init() */ /* This may give extra safety */
my_end(0);
}
break;
case DLL_THREAD_DETACH:
/* Main thread will free by my_end() */
threads--;
if (main_thread != GetCurrentThreadId())
my_thread_end();
break;
default:
break;
} /* switch */
return TRUE;
UNREFERENCED_PARAMETER(lpReserved);
} /* LibMain */
- Original Message -
From: John McCaskey [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, October 07, 2005 10:31 AM
Subject: RE: How thread-safe is mysql_real_connect()?
Sean,
First let me thank you for all the great posts and info I've seen you
put on this list for others.
I've been working in C with MySQL in a very multithreaded environment
for several years and think I can explain the thread safety issues
clearly. Rather than try to respond point by point to your question I'm
going to give a summary and if that doesn't help please respond again
and I'll answer specific questions.
First, mysql is in fact pretty much threadsafe when using the _r
library. You definitely do need to use the _r library and not the
normal one as the SIGPIPE discussion applies to both, the non _r library
has additional safety issues surrounding mysql_real_connect() and should
not be used. On windows you don't really need to do anything here I
believe because the Windows binaries are by default compiled to be
thread-safe. (from
http://dev.mysql.com/doc/mysql/en/threaded-clients.html). To validate
this in your client code you should in the main() function close to
startup use mysql_thread_safe() to verify your linked in version is
thread safe.
The next thing you need to do is initialize mysql globally before
creating any threads that will use it. Simply call my_init(); in your
main thread. After this you can go ahead and create any threads. In
the threads you create you need to call mysql_thread_init(); and when
you end the thread mysql_thread_end(); in between these calls you can
just use mysql as normal and the mysql_real_connect function will be
thread safe, you do not need to perform any locking of your own to make
only one call at a time or anything along those lines.
Here is some pseudo code of what you need to do:
int main(int argc, char **argv) {
if(!mysql_thread_safe()) {
fprintf(stderr, Not Thread safe!!!);
return 1;
}
my_init();
// your regular init code
// create the threads that will use mysql
CreateThread();
}
void *mysql_thread(void *arg) {
mysql_thread_init();
//regular mysql code and whatever else here
//use mysql_real_connect and mysql_real_query
//and whatever without worrying about thread safety
mysql_thread_end();
}
John A. McCaskey
Software Development Engineer
Klir Technologies, Inc.
[EMAIL PROTECTED]
206.902.2027
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, October 07, 2005 9:01 AM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: How thread-safe is mysql_real_connect()?
(please excuse the double post but I wanted to reach the two audiences I
thought could help the best)
This is a question about the interpreting the documentation in the
manual
for the C API.
I searched the list archives (all lists) going back 365 days for the
terms
(unquoted): mysql_real_connect thread (I also looked for
alternatives:mysql_real_connect threaded, mysql_real_connect multi
threaded, etc.). I searched on Google Groups for: mysql_real_connect
thread and found a few interesting hits. However, I am still not 100%
clear on how to interpret some of the information on this page:
http://dev.mysql.com/doc/mysql/en/threaded-clients.html
I do a lot of MySQL administration and development using mostly the CLI
and a few
Re: Non-linear degradation in bulk loads?
Jon Frisby wrote: Everyone, We're trying to do some bulk data loads on several different tables (on several different machines, using several different techniques) and seeing dramatically worse-than-linear performance. We've tried the bulk-INSERT syntax, and the LOAD DATA INFILE syntax. We've done ALTER TABLE ... DISABLE KEYS, SET FOREIGN_KEY_CHECKS=0 (where appropriate), and so forth. The one that is the most immediate concern is a table of the form: CREATE TABLE `test` ( `email` varchar(255) NOT NULL default '', `when_happened` datetime NOT NULL default '-00-00 00:00:00', UNIQUE KEY `email` (`email`), KEY `when_happened` (`when_happened`) ) TYPE=InnoDB; I'm loading data using LOAD DATA INFILE with chunks containing 3.4m rows each (~135MB files). The first chunk was very quick (about 1.5 minutes), but the tenth chunk has taken 22.6 hours and is still going. (It's been getting progessively slower with each chunk...) The database is our main sites database but we've dramatically reduced the load on that machine over the past couple months through careful optimization of our code. The box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We have 1GB allocated to the buffer pool, and our usual 1GB * 3 log files. 8 I/O threads. Load on the box sits at around 6-7, with a large (50%) amount of time spent in wait state, but actual disk throughput to our software RAID array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k blocks/s in. Something *has* to be wrong here, but we're not sure what we've missed. We've restored larger data sets from a mysqldump in the past in dramatically less time on far inferior hardware. (A superset of this same data to a schema which is also a superset, PLUS a bunch of other rather large tables -- all in ~8 hours on a 3Ware RAID array on a dual Xeon w/ 4GB of RAM) We're inclined to believe that this is a configuration problem, as opposed to a driver or hardware problem given the non-linear nature of the performance degradation. This implies we're doing something truly stupid with our loads. What could cause this kind of strangeness? -JF Hi Jon, I experienced this same non-linear degradation during large imports, exactly like you are describing, about 18 months ago. I don't remember if I found a specific cause, but I am fairly certain that it was related to a few issues, and that we did resolve it. I have not seen this happen with MyISAM tables, and we were able import our full data by breaking it into chunks and waiting between each chunk, so I believe it to be related to InnoDB's logs in some way. Since you are already importing your data in chunks, try making each chunk a separate transaction, or waiting until disk activity slows to load the next chunk. Also, there have been major improvements to InnoDB in the later 4.1 releases, so if possible, I would suggest upgrading. Hope that helps! Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: databse design and table join problems
Hi, Ok, if you do not recommend a big ( about 20 columns ) table, how can I do the join then and how do I add to each customer with the same street name, street number, block of flats number and flat number, an id that will help me join them ? I need to be able to see a customer even if it only has a packet in cp2/cp3/cp4 table, not only if he has a packet in every table. Best regards, Cristi Stoica Peter Brawley wrote: I have the data from MS Access to MySQL, half imported by now, that is not the problem, the same with importing from excel files. The problem is how can I get all the data in one big table ? With the data you originally described, one big table looks like a bad idea. PB inferno wrote: Hi, I have the data from MS Access to MySQL, half imported by now, that is not the problem, the same with importing from excel files. The problem is how can I get all the data in one big table ? Best regards, Cristian Stoica Peter Brawley wrote: Cristi, In theory it is simple but I need some help on how that will be translated to mysql, I have a concept in mind, but I do not know how to exactly apply it to the situation. I am in a situation where I have the data gathered from more persons and everybody had a personal way of designing the database or excel table and now I need to build a database that will include all the data in the tables bellow, that is way I was not able to give you a table description from mysql since I do not have all the tables imported into mysql. 1. First thing may be to turn the spreadsheets into tables. It's trivial to turn a spreadsheet into an Access table (eg http://databases.about.com/aa123100a.htm). 2. Once you have all the representations in Access, you have a choice to make: standardise the data representation in Access, or export all the data in its various models to MySQL and standardise the mess there. Presumably you know Access much better than MySQL, so you may want to do this standardisation in Access before exporting to MySQL. In that case, you'll want to implement logic of the sort I described in Access, before exporting the data to MySQL. 3. You have choices for how to export tables from Access to MySQL. Simplest may be to export from Access to text files, then use the MySQL utility mysqlimport to import the data into a mysql table. Or, you can also use an Access-to-mysql converter script (Google for them), or a GUI tool like DbTools, which is freeware. Or (perhaps the most complex method), you can create the target MySQL table, use ODBCAdmin to create a DSN for it, and export to that. Is that the info you are looking for? PB - inferno wrote: Hi, First of all thank you for your reply. The table names I've wrote in the e-mail were just for info, I do use underscore instead of space, as you can see I do the same for the fields in the table. In theory it is simple but I need some help on how that will be translated to mysql, I have a concept in mind, but I do not know how to exactly apply it to the situation. I am in a situation where I have the data gathered from more persons and everybody had a personal way of designing the database or excel table and now I need to build a database that will include all the data in the tables bellow, that is way I was not able to give you a table description from mysql since I do not have all the tables imported into mysql. After I will import the tables into mysql I have to make somehow the new table and I would like it to be good, and that it can be further upgraded ( maybe a new service will need to be entered for a customer and I need to have all the data in one place ) and to be able to handle each entry not by using 4 fields that are varchar. If you / someone can help me with this task I will be very grateful, as I have said before I am a beginer and do not have much experience with MySQL. Best regards and thank you for your advice, Cristi Stoica Peter Brawley wrote: Cristi, I have the following tables: ( some in Microsoft Access and some in Excel ) and I want to migrate the data into MySQL and develop an interface in PHP for easy administration and control... A few points: 1. Instead of spaces, use underscores or nothing in table names 2. If Address_Code contains addresses of Sales_Representatives, it needs an column for Sales_Representative ids. 3. One way to merge cp2, cp3 and cp4 would be: (1) add int 'old_id' columns to cp2, cp3 and cp4, populate with sequential values such that old_id values are unique across cp2, cp3 or cp4 (2) make a new customers table structure as desired, and include the new cp2 cp3 cp4 old_ids, and an auto_increment int primary key, (3) import into customer from cp2, cp3, cp4, excluding dupes computed without reference to old_id (4) make a customertype table whose columns include all columns in cp2
Re: MySQL Hangs
Related: http://lists.mysql.com/mysql/44164 No solutions mentioned here. Slack 10.1 is booting and once the newly installed MySQL Standard 4.1.14 starts up with: Starting mysqld daemon with databases from /usr/local/mysql/data The system hangs and is dead to the world. I will need some time to prepare a disk for recovery to get past this inconvenience. Perhaps a hotkey workaround I am unaware of will allow init to skip this standstill? - velusip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Query help - Bayesian Filter detected spam
You might try UNION with the 1st statement pulling all products with
groupid = 0 and the 2nd pulling 1 product with groupid 1.
-Original Message-
From: John Nichel [mailto:[EMAIL PROTECTED]
Sent: Monday, October 10, 2005 3:31 PM
To: MySQL List
Subject: [SPAM] - Query help - Bayesian Filter detected spam
Hi, I have this query below, and I have been pulling my hair out for the
past couple of hours trying to get it to do what I want. As is, it
works, but I need it to consider other conditions. One of the columns
in the products table is called groupid. I need it to pull all products
with a groupid of 0 and only 1 product with a groupid 0 (doesn't
matter which one). Any help will save my sanity. ;)
SELECT products.*, MIN(pricing.price) as price , products_lng.product as
product_lng, products_lng.descr as descr_lng, products_lng.full_descr as
fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant,
IF(classes.classid IS NOT NULL,'Y','') as is_product_options,
MIN(v_pricing.price) as v_price, products_lng.product as product_lng,
products_lng.descr as descr_lng, products_lng.full_descr as
fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant,
IF(classes.classid IS NOT NULL,'Y','') as is_product_options,
MIN(v_pricing.price) as v_price FROM products, pricing ,
products_categories, categories LEFT JOIN products_lng ON
products_lng.productid = products.productid AND products_lng.code = 'US'
LEFT JOIN classes ON classes.productid = products.productid LEFT JOIN
variants ON variants.productid = products.productid LEFT JOIN pricing as
v_pricing ON v_pricing.variantid = variants.variantid AND
v_pricing.quantity = 1 AND v_pricing.membership IN ('','') WHERE
pricing.productid=products.productid AND pricing.quantity=1 AND
pricing.membership IN ('','') AND products.product_type 'C' AND
products.product_type 'B' AND (pricing.variantid = 0 OR
(variants.variantid = pricing.variantid AND variants.avail 0)) AND
products_categories.productid=products.productid AND
products_categories.categoryid = categories.categoryid AND
categories.membership IN ('','') AND
products_categories.categoryid='412' AND (products_categories.main='Y'
OR products_categories.main!='Y') AND products.forsale='Y' AND
(products.avail0 OR products.product_type NOT IN ('','N')) GROUP BY
products.productid ORDER BY products_categories.orderby ASC,
products.product ASC LIMIT 10, 10
--
John C. Nichel
KegWorks.com
716.856.9675
[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]
Re: Help on writing a sql statement
(my response bottom-posted. See below - SG) Imran [EMAIL PROTECTED] wrote on 10/10/2005 09:16:13 PM: Hi Shawn .. Thanks for the offer. I am attaching the info that you requested. mysql SHOW CREATE TABLE salesmaster\G; *** 1. row *** Table: salesmaster Create Table: CREATE TABLE `salesmaster` ( `ID` int(10) NOT NULL auto_increment, `PostingDate` datetime default NULL, `PostingDateJulian` double(15,5) default NULL, `CustNo` varchar(10) default NULL, `ProdNo` varchar(5) default NULL, `SalesMan` char(2) default NULL, `Branch` int(10) default NULL, `Kilos` decimal(19,4) default '0.', `Sales` decimal(19,4) default '0.', `Cost` decimal(19,4) default '0.', PRIMARY KEY (`ID`), KEY `PostingDate` (`PostingDate`), KEY `CustNo` (`CustNo`), KEY `ProdNo` (`ProdNo`), KEY `Branch` (`Branch`) ) mysql SHOW CREATE TABLE customerintermediate\G; *** 1. row *** Table: customerintermediate Create Table: CREATE TABLE `customerintermediate` ( `ID` int(10) NOT NULL auto_increment, `CustNo` varchar(10) default NULL, `CustName` varchar(120) default NULL, `CustShipAdd1` varchar(50) default NULL, `CustShipAdd2` varchar(50) default NULL, `CustShipAdd3` varchar(50) default NULL, `CustShipPostal` varchar(50) default NULL, `Dept` int(10) default NULL, `Class` int(10) default NULL, `Branch` int(10) default NULL, PRIMARY KEY (`ID`), KEY `CustNo` (`CustNo`), KEY `Branch` (`Branch`), KEY `Dept` (`Dept`) ) mysql SHOW CREATE TABLE productintermediate\G; *** 1. row *** Table: productintermediate Create Table: CREATE TABLE `productintermediate` ( `ID` int(10) NOT NULL auto_increment, `ProdNo` varchar(5) default NULL, `Description` varchar(255) default NULL, `Status` smallint(5) default NULL, `Branch` int(10) default NULL, `Source` int(10) default NULL, `Main` int(10) default NULL, `Report` int(10) default NULL, PRIMARY KEY (`ID`), KEY `ProdNo` (`ProdNo`), KEY `Branch` (`Branch`) ) - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 10, 2005 4:17 PM Subject: Re: Help on writing a sql statement Imran [EMAIL PROTECTED] wrote on 10/10/2005 03:52:21 PM: Hi all: I need some help in writing a sql statement. I have three tables (Sales, Cust and Product). The sales table contains a large volume of data and I want to create a sql to group the sales table then join the resultant to both the Cust and Prod and to have additional fields selected from the Cust and Prod. So in effect something like (obviously syntax is wrong) Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales, sm.date from Sales SM where sm.date = date(‘2005-09-01 00:00:00’) Group by sm.prodno, sm.custno, sm.date , (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left join sm.prodno = prod.code left join sm.custno=cust.custno) … Any help would be greatly appreciated. OK, you know you need a GROUP BY, that's good. You also recognized you needed to JOIN a few tables together, also good. There are at least two ways to do what you ask. One is a fairly complex query that does it all in one statement (might take a long time to compute) the other is a sequence of two simpler statements. I think the two-statement solution will be easier to understand and maintain so I would prefer to go over that. However, in order to provide an example of either method I will need more information from you. From the CLI (command line client), please provide the output from these three commands: SHOW CREATE TABLE sales\G; SHOW CREATE TABLE cust\G; SHOW CREATE TABLE product\G; That will tell me exactly which columns live on which tables and where you do or do not have any indexes. Good indexes will make or break the performance of your database. You will not be exposing any data, only the design of the tables. Please remember to CC the list on all responses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Let me see if I can translate what you want in a query into regular language. I think you would like to see, grouped by date, customer, and product, the total cost and total sales for each (date,customer,product) triple along with each product's description ,code, and the customer's number and name. All of that will be limited to activity on or before midnight of a certain date. If I rephrased that correctly, here is how I would build your query. Step 1 is to perform the (date,customer,product) summations. By minimizing the number of rows, columns, and/or tables we need to summarize against, we improve
Re: How thread-safe is mysql_real_connect()?
Jeremiah Gowdy [EMAIL PROTECTED] wrote on 10/11/2005 03:08:40 AM:
The Windows DLL is thread safe. You do not have to call my_init()
and my_thread_init() because Windows DLLs receive events when they
are attached to a new process and when they are attached to a new
thread in a process. This is one of the nicer features of Windows
shared libraries. Other than that, you don't have to do anything
special. I am a heavy user of libmysql under Win32. You simply
mysql_init() your MYSQL struct, and then mysql_real_connect() and
you're ready to mysql_query().
You should not call my_init() or my_thread_init() as the previous
poster suggested. This could result in memory leaks.
From libmysql/dll.c
BOOL APIENTRY LibMain(HANDLE hInst,DWORD ul_reason_being_called,
LPVOID lpReserved)
{
switch (ul_reason_being_called) {
case DLL_PROCESS_ATTACH: /* case of libentry call in win 3.x */
if (!inited++)
{
s_hModule=hInst;
libmysql_init();
main_thread=GetCurrentThreadId();
}
break;
case DLL_THREAD_ATTACH:
threads++;
my_thread_init();
break;
case DLL_PROCESS_DETACH: /* case of wep call in win 3.x */
if (!--inited) /* Safety */
{
/* my_thread_init() */ /* This may give extra safety */
my_end(0);
}
break;
case DLL_THREAD_DETACH:
/* Main thread will free by my_end() */
threads--;
if (main_thread != GetCurrentThreadId())
my_thread_end();
break;
default:
break;
} /* switch */
return TRUE;
UNREFERENCED_PARAMETER(lpReserved);
} /* LibMain */
- Original Message -
From: John McCaskey [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED];
mysql@lists.mysql.com
Sent: Friday, October 07, 2005 10:31 AM
Subject: RE: How thread-safe is mysql_real_connect()?
Sean,
First let me thank you for all the great posts and info I've seen you
put on this list for others.
I've been working in C with MySQL in a very multithreaded environment
for several years and think I can explain the thread safety issues
clearly. Rather than try to respond point by point to your question I'm
going to give a summary and if that doesn't help please respond again
and I'll answer specific questions.
First, mysql is in fact pretty much threadsafe when using the _r
library. You definitely do need to use the _r library and not the
normal one as the SIGPIPE discussion applies to both, the non _r library
has additional safety issues surrounding mysql_real_connect() and should
not be used. On windows you don't really need to do anything here I
believe because the Windows binaries are by default compiled to be
thread-safe. (from
http://dev.mysql.com/doc/mysql/en/threaded-clients.html). To validate
this in your client code you should in the main() function close to
startup use mysql_thread_safe() to verify your linked in version is
thread safe.
The next thing you need to do is initialize mysql globally before
creating any threads that will use it. Simply call my_init(); in your
main thread. After this you can go ahead and create any threads. In
the threads you create you need to call mysql_thread_init(); and when
you end the thread mysql_thread_end(); in between these calls you can
just use mysql as normal and the mysql_real_connect function will be
thread safe, you do not need to perform any locking of your own to make
only one call at a time or anything along those lines.
Here is some pseudo code of what you need to do:
int main(int argc, char **argv) {
if(!mysql_thread_safe()) {
fprintf(stderr, Not Thread safe!!!);
return 1;
}
my_init();
// your regular init code
// create the threads that will use mysql
CreateThread();
}
void *mysql_thread(void *arg) {
mysql_thread_init();
//regular mysql code and whatever else here
//use mysql_real_connect and mysql_real_query
//and whatever without worrying about thread safety
mysql_thread_end();
}
John A. McCaskey
Software Development Engineer
Klir Technologies, Inc.
[EMAIL PROTECTED]
206.902.2027
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, October 07, 2005 9:01 AM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: How thread-safe is mysql_real_connect()?
snip
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thank you very much!!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Lost .frm files
Hello. Maybe this will help, if you're using MyISAM tables. Create a table with the same definition as a table from the backup has. Remove a new data file, and put a data file from the backup instead of it. Then use REPAIR. I have changed the OS on my database server. At that time, I forgot to take the backup of the .frm files in /var/lib/mysql. I have taken all the backup of all the data files. I can recreate all the tables. How can I map these tables to existing data files. Is this possible. Please help me out as the data is huge. (alomost 220G ) Sujay Koduri wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you set a Primary Key when uploading data into a table?
Hello. The Id row gets values of 99897, and then increments by one. Please, send the output of: SHOW CREATE TABLE sic_codes; Probably you don't have auto_increment attribute in the Id definition. Bill Abel wrote: How do you load data into a table and generate a primary key which increments by 1. I want to end up with a primary key value of int 1, 2, 3, 4, and so on. My table has three rows, Id, Code, Description. I'm loading data from a text file into the table like this: load data local infile '/Users/billabel/Desktop/sic.txt' into table sic_codes lines terminated by '\r' (code, description); The Id row gets values of 99897, and then increments by one. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Relocation of database store
Hello. I want to place my database on a NAS drive. Usually it is not recommended to use network filesystems, due to high performance penalty. I'm trying to relocate the database files for MySQL 4.1 I've seen two primary techniques for doing this: Have a look here as well: http://dev.mysql.com/doc/mysql/en/moving.html http://dev.mysql.com/doc/mysql/en/upgrading-to-arch.html William Fry wrote: I'm trying to relocate the database files for MySQL 4.1 I've seen two primary techniques for doing this: 1) create/alter a my.cnf file with 'datadir' set to new location 2) moving the physical files and creating a symlink in the original location Unfortunately, neither of these methods work for me. I have my MySQL server running on a Mandrake 10.1 Linux box with limited hard drive space. I want to place my database on a NAS drive. The NAS drive support many protocols: NFS, SMB, AFP, etc. so I have no problem at all accessing the device from my database server. However, the problem seems to be that, as part of MySQL server's normal operation, it creates a socket file. Unfortunately, this activity is not allowed on a remote share. So, when the server starts up, its attempt to create mysql.sock fails. It appears that both relocation methods indicated above cause EVERYTHING to be relocated, i.e. not only the hard files, but the socket file also. Is there a way to have the hard files relocated but keep the socket file in /var/lib/mysql? Thanks! - Liam -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Suppress table header when using ODBC
Hello. I am using a desktop program that imports data from a mysql What program? for one little glitch: it adds one extra row at the beginning of the dataset with the names of the columns in it. I need for that Does you program show column names in the numeric fields? Gerald Taylor wrote: Hello, I am using a desktop program that imports data from a mysql database using the ODBC mysql driver and everything works fine except for one little glitch: it adds one extra row at the beginning of the dataset with the names of the columns in it. I need for that column name row to NOT be there. Do I do something to the query to suppress is or is it some setting I set up? I've googled and nothing. MySQL 4.1 if it matters. I know I remember reading somewhere how to suppress this. Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: SOCKET directory
Hello. However, when I try to connect I get the error: Have a look here: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Barbara Deaton wrote: With MySQL 4.0 when I wanted to run my application on any of the unix = boxes I had to set the MYSQL_UNIX_PORT environment variable to point to = the location of my socket file. Looking through my 4.1.9 install I no = longer see a mysql/socket directory. =20 However, when I try to connect I get the error: ERROR: Error trying to establish connection: Can't connect to local = MySQL=20 server through socket '/tmp/mysql.sock' (2) Where did it go? Is it no longer needed? Thanks for any information. Barbara -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Charset issue
Hello. Chinese can't be displayer propely. What client do you use to connect to MySQL Server? Check that it supports Chinese. I don't know Chinese and can't suggest what character to use. But, certainly, you should choose one from: http://dev.mysql.com/doc/mysql/en/charset-asian-sets.html Check that SHOW VARIABLES LIKE '%char%' returns this charset for all variables except charset_system, which is always utf8. çé wrote: hi all. Here is the suituation. i've installed mysql 4.1.12 on WinXP plantform, it seems not to support Chinese by its default configuration, say, Chinese can't be displayer propely. as far as i know, what i need to do is modify the charset(server, connection, client...)concerned, but i'm not so sure and is there anything else i should do and howto? 3x. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Out Of Memory problems: One MySQL user, 86 minutes sleeping
Hello.
1981 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.53 mysqld
1982 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1983 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1984 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
These are the threads of the same mysqld process which is using only
72m of virtual memory. May be you should find out other process
which is consuming memory. If you are sure that MySQL is guilty
than send to the list the output of 'SHOW VARIABLES' statement.
thomas Armstrong wrote:
Hi.
Using MySQL 4.1.9 on Linux FedoraCore2 (kernel 2.6.9),
I'm suffering several memory problems ('Out Of Memory'
problem) on my server.
Playing around with my server:
SHOW PROCESSLIST
Id | User | Host | db | Command | Time | State | Info
20138 | user1 | localhost | user1_db | Sleep | 5295 | NULL
(why is this user1 sleeping for 86 minutes?)
SHOW STATUS:
Qcache_free_blocks =0956
Qcache_free_memory =0911676280
Qcache_hits =09762140
Qcache_inserts =09109122
Qcache_lowmem_prunes =0912575
Qcache_not_cached =09897
Qcache_queries_in_cache =09644
Qcache_total_blocks =091582
-
[]# top
-
top - 12:55:40 up 1 day, 2:18, 2 users, load average: 0.39, 0.35, 0.29
Tasks: 88 total, 2 running, 86 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.3% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% s=
i
Mem:508072k total, 499980k used, 8092k free,69828k buffers
Swap: 1084376k total, 2176k used, 1082200k free, 116264k cached
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
1980 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.24 mysqld
1981 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.53 mysqld
1982 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1983 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1984 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1985 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1986 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.21 mysqld
1987 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.13 mysqld
1988 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1989 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.06 mysqld
14337 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
17323 apache15 0 42056 30m 16m S 0.0 6.1 0:02.44 httpd
2316 ogo 16 0 36092 28m 23m S 0.0 5.7 0:01.25 ogo-webui-1.0a
11228 apache16 0 36600 24m 18m S 0.0 4.9 1:51.20 httpd
14276 apache15 0 34408 24m 16m S 0.0 4.8 0:58.17 httpd
15737 apache15 0 34120 23m 16m S 0.0 4.8 0:32.92 httpd
-
[]# more /etc/my.conf
[mysqld]
datadir=3D/var/lib/mysql
socket=3D/var/lib/mysql/mysql.sock
query-cache-size=3D20M
query-cache-type=3D1
default-character-set=3Dutf8
# Slow queries log
log-slow-queries =3D /var/log/mysql/slow-queries.log
long_query_time =3D 5
log-long-format
[mysql.server]
user=3Dmysql
basedir=3D/var/lib
[safe_mysqld]
err-log=3D/var/log/mysqld.log
pid-file=3D/var/run/mysqld/mysqld.pid
---
[]# free -m
--
total used free sharedbuffers cached
Mem: 496487 8 0 68113
-/+ buffers/cache:305190
Swap: 1058 2 1056
---
I'm trying to find out the reason of my memory problems. I suspect this
sleeping user is to blame. Any suggestion?
Thank you very much.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [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: Out of memory (Needed 8164 bytes)
Hello. the problem is that after around 160,000 rows inserted with success , then i get Out of memory (Needed 8164 bytes) error many times. Does your program report this error or MySQL Server? Can someone give me an advice with some optimization or if something is wrong in mysql server configuration? At least check that memory which might be used by MySQL Server can't be more than amount of your RAM. See: http://dev.mysql.com/doc/mysql/en/memory-use.html Use a formula similar to this: min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer thread_stack net_buffers and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool In case Andrei wrote: Hi list, i have a linux server with mysql 4.1.14 and i'm trying to execute a program wich read a file with 100's tohusands lines and for every line must do a SELECT and then an INSERT . the SELECT use 2 InnoDB tables and the INSERT use a MyISAM table. the problem is that after around 160,000 rows inserted with success , then i get Out of memory (Needed 8164 bytes) error many times. i will try to increase the RAM size but i don't know if this is the problem (for sure it will help , but ... ) so, i suspect that after these 160,000 rows ... the SELECT for the following records is not successfully ending but the INSERT is ok. Can someone give me an advice with some optimization or if something is wrong in mysql server configuration? #free total used free sharedbuffers cached Mem:514460 238972 275488 0 28824 131024 -/+ buffers/cache: 79124 435336 Swap: 1975912 200521955860 VARIABLES: +-+-+ | Variable_name | Value +-+-+ | back_log| 50 | basedir | /opt/sql/mysql-4.1.14/ | binlog_cache_size | 32768 | bulk_insert_buffer_size | 8388608 | character_set_client| latin1 | character_set_connection| latin1 | character_set_database | latin1 | character_set_results | latin1 | character_set_server| latin1 | character_set_system| utf8 | character_sets_dir | /opt/sql/mysql-4.1.14/share/mysql/charsets/ | collation_connection| latin1_swedish_ci | collation_database | latin1_swedish_ci | collation_server| latin1_swedish_ci | concurrent_insert | ON | connect_timeout | 5 | datadir | /opt/sql/mysql-data/ | date_format | %Y-%m-%d | datetime_format | %Y-%m-%d %H:%i:%s | default_week_format | 0 | delay_key_write | ON | delayed_insert_limit| 100 | delayed_insert_timeout | 300 | delayed_queue_size | 1000 | expire_logs_days| 0 | flush | OFF | flush_time | 0 | ft_boolean_syntax | + -()~*:| | ft_max_word_len | 84 | ft_min_word_len | 4 | ft_query_expansion_limit| 20 | ft_stopword_file| (built-in) | group_concat_max_len| 1024 | have_archive| NO | have_bdb| NO | have_blackhole_engine | NO | have_compress | YES | have_crypt | YES | have_csv| NO | have_example_engine | NO | have_geometry | YES | have_innodb | YES | have_isam | NO | have_ndbcluster | NO | have_openssl| NO | have_query_cache| YES | have_raid | NO | have_rtree_keys | YES | have_symlink| YES | init_connect| | init_file | | init_slave | | innodb_additional_mem_pool_size | 20971520 | innodb_autoextend_increment | 8 | innodb_buffer_pool_awe_mem_mb | 0 | innodb_buffer_pool_size | 167772160 | innodb_data_file_path | ibdata1:10M:autoextend | innodb_data_home_dir| /opt/sql/mysql-data/ | innodb_fast_shutdown| ON | innodb_file_io_threads | 4 | innodb_file_per_table | OFF | innodb_flush_log_at_trx_commit | 1 |
RE: SOCKET directory
Thank you for the link. I understand that I need a mysql.sock file, what I don't understand is that I used to get one when I installed MySQL. When I look through all my 4.0.x mysql directories I see a socket directory that contains a mysql.sock file. With my 4.1 install I do not see this file. Why do I no longer get a socket/mysql.sock with the install? Thanks again for your time. -Barb. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Monday, October 10, 2005 6:21 PM To: mysql@lists.mysql.com Subject: Re: SOCKET directory Hello. However, when I try to connect I get the error: Have a look here: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Barbara Deaton wrote: With MySQL 4.0 when I wanted to run my application on any of the unix = boxes I had to set the MYSQL_UNIX_PORT environment variable to point to = the location of my socket file. Looking through my 4.1.9 install I no = longer see a mysql/socket directory. =20 However, when I try to connect I get the error: ERROR: Error trying to establish connection: Can't connect to local = MySQL=20 server through socket '/tmp/mysql.sock' (2) Where did it go? Is it no longer needed? Thanks for any information. Barbara -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SOCKET directory
Barbara Deaton wrote: Thank you for the link. I understand that I need a mysql.sock file, what I don't understand is that I used to get one when I installed MySQL. When I look through all my 4.0.x mysql directories I see a socket directory that contains a mysql.sock file. With my 4.1 install I do not see this file. Why do I no longer get a socket/mysql.sock with the install? Thanks again for your time. -Barb. It is created by the server when the server starts, and deleted when the server stops. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SOCKET directory
The sock file is not a permanent file. It is a socket descriptor that should only exist if the MySQL server is running and if the server is configured to supports socket-type connections. Most of the default installations DO NOT start the server as part of the installation process. That way you have a chance to customize any configurations before the first start (very convenient if you are migrating or upgrading). If you do not see a sock file it means: a) you haven't started the server (either in stand-alone or daemon mode) or b) you do not have a version of the server that supports system sockets or c) your server's configuration file is defined in such a way to suppress system socket usage. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barbara Deaton [EMAIL PROTECTED] wrote on 10/11/2005 10:25:46 AM: Thank you for the link. I understand that I need a mysql.sock file, what I don't understand is that I used to get one when I installed MySQL. When I look through all my 4.0.x mysql directories I see a socket directory that contains a mysql.sock file. With my 4.1 install I do not see this file. Why do I no longer get a socket/mysql.sock with the install? Thanks again for your time. -Barb. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Monday, October 10, 2005 6:21 PM To: mysql@lists.mysql.com Subject: Re: SOCKET directory Hello. However, when I try to connect I get the error: Have a look here: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Barbara Deaton wrote: With MySQL 4.0 when I wanted to run my application on any of the unix = boxes I had to set the MYSQL_UNIX_PORT environment variable to point to = the location of my socket file. Looking through my 4.1.9 install I no = longer see a mysql/socket directory. =20 However, when I try to connect I get the error: ERROR: Error trying to establish connection: Can't connect to local = MySQL=20 server through socket '/tmp/mysql.sock' (2) Where did it go? Is it no longer needed? Thanks for any information. Barbara -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SOCKET directory
Wow, totally didn't get that from the article. Thanks so much everyone for the explanation and taking the time to write. Time to recheck my servers. -Barb. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 11, 2005 10:37 AM To: Barbara Deaton Cc: Gleb Paharenko; mysql@lists.mysql.com Subject: RE: SOCKET directory The sock file is not a permanent file. It is a socket descriptor that should only exist if the MySQL server is running and if the server is configured to supports socket-type connections. Most of the default installations DO NOT start the server as part of the installation process. That way you have a chance to customize any configurations before the first start (very convenient if you are migrating or upgrading). If you do not see a sock file it means: a) you haven't started the server (either in stand-alone or daemon mode) or b) you do not have a version of the server that supports system sockets or c) your server's configuration file is defined in such a way to suppress system socket usage. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barbara Deaton [EMAIL PROTECTED] wrote on 10/11/2005 10:25:46 AM: Thank you for the link. I understand that I need a mysql.sock file, what I don't understand is that I used to get one when I installed MySQL. When I look through all my 4.0.x mysql directories I see a socket directory that contains a mysql.sock file. With my 4.1 install I do not see this file. Why do I no longer get a socket/mysql.sock with the install? Thanks again for your time. -Barb. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Monday, October 10, 2005 6:21 PM To: mysql@lists.mysql.com Subject: Re: SOCKET directory Hello. However, when I try to connect I get the error: Have a look here: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Barbara Deaton wrote: With MySQL 4.0 when I wanted to run my application on any of the unix = boxes I had to set the MYSQL_UNIX_PORT environment variable to point to = the location of my socket file. Looking through my 4.1.9 install I no = longer see a mysql/socket directory. =20 However, when I try to connect I get the error: ERROR: Error trying to establish connection: Can't connect to local = MySQL=20 server through socket '/tmp/mysql.sock' (2) Where did it go? Is it no longer needed? Thanks for any information. Barbara -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Hangs
Velu Shk wrote: Related: http://lists.mysql.com/mysql/44164 No solutions mentioned here. Slack 10.1 is booting and once the newly installed MySQL Standard 4.1.14 starts up with: Starting mysqld daemon with databases from /usr/local/mysql/data The system hangs and is dead to the world. I will need some time to prepare a disk for recovery to get past this inconvenience. Perhaps a hotkey workaround I am unaware of will allow init to skip this standstill? - velusip Velu, If you boot the system into run level 1, you should be able to remove the symlink from the appropriate rc.x directory. Once you've done that, the machine will boot and maybe you can find something in the mysql error log. If you don't see anything there, adding strace to the mysql startup script might give some more info which would be helpful in solving your problem. walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Append one table to another?
How do I append one table's contents to another? Both have identical structure. Problem is I don't have shell access, only phpAdmin or a PHP file I write upload myself. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem compiling MyODBC - looking for sql.h file
I posted this on the ODBC list, but there is not much traffic there and no one replied. Hopefully someone here has dealt with this. I am setting up ColdFusion to access a MySQL 4.1.x database. This required new MyODBC drivers to be installed on the system for ColdFusion. After a lot of trial and error, I got it figured out. I install mysql on the system (using --without-server), then install MyODBC. Then copy the libmyodbc3.so file to a directory under ColdFusion, and change ColdFusion's odbc.ini file to reference this new driver. I am now trying to do it on a second system, but am getting errors during the ./configure of MyODBC (3.5.11). This is my configure statement: ./configure --with-mysql-path=/usr/local/mysql \ --without-samples \ --disable-test \ --enable-thread-safe And this is the resulting output: ... ODBC DRIVER MANAGER CONFIGURATION - LIBRARIES AND HEADERS checking for isql... No checking for unixODBC version... Unknown checking sql.h usability... No checking sql.h presence... No checking for sql.h... no configure: error: Unable to find the unixODBC headers in '/usr/local//include' It's complaining about unixODBC headers, maybe specifically the sql.h file in the second to last line? But I don't have unixODBC installed on the FIRST system, the one that is working just fine. (ColdFusion has it's one built in ODBC manager, unixODBC is not necessary.) However there is a /usr/local/include/sql.h file on that system. I don't know how it got there. By looking inside the file it appears to be related to MyODBC 3.5.11. Just for kicks I copied this file to the second system, but got the same error when trying to run ./configure. Any ideas? -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: databse design and table join problems
Cristi, Ok, if you do not recommend a big ( about 20 columns ) table, how can I do the join then and how do I add to each customer with the same street name, street number, block of flats number and flat number, an id that will help me join them ? Oh, you mean get all the _customers_ into one big table. Didn't we start with that question? Once you have imported all the tables to MySQL:: (1) add int 'old_id' columns to the cp2, cp3 and cp4 tables, populate with sequential values such that old_id values are unique across cp2, cp3 or cp4 (2) make a new customers table structure as desired, add the new cp2 cp3 cp4 old_ids, and add an auto_increment int primary key, (3) import into customer from cp2, cp3, cp4, excluding dupes based on name address or whatever you want (4) make a customertype table whose columns include all columns in cp2 cp3 cp4 which are not in customers an auto_increment primary key id customer_id customer_type (whatever values you want corresponding to 'cp2' etc) (5) populate customer_type from joins on customer and cp2 customer and cp3 customer and cp4 using old_id (6) drop the old_id column from customers Now you have the generic customer info in customer, and the type-specific info in customertypes. PB - inferno wrote: Hi, Ok, if you do not recommend a big ( about 20 columns ) table, how can I do the join then and how do I add to each customer with the same street name, street number, block of flats number and flat number, an id that will help me join them ? I need to be able to see a customer even if it only has a packet in cp2/cp3/cp4 table, not only if he has a packet in every table. Best regards, Cristi Stoica Peter Brawley wrote: I have the data from MS Access to MySQL, half imported by now, that is not the problem, the same with importing from excel files. The problem is how can I get all the data in one big table ? With the data you originally described, one big table looks like a bad idea. PB inferno wrote: Hi, I have the data from MS Access to MySQL, half imported by now, that is not the problem, the same with importing from excel files. The problem is how can I get all the data in one big table ? Best regards, Cristian Stoica Peter Brawley wrote: Cristi, In theory it is simple but I need some help on how that will be "translated" to mysql, I have a concept in mind, but I do not know how to exactly apply it to the situation. I am in a situation where I have the data gathered from more persons and everybody had a personal way of designing the database or excel table and now I need to build a database that will include all the data in the tables bellow, that is way I was not able to give you a table description from mysql since I do not have all the tables imported into mysql. 1. First thing may be to turn the spreadsheets into tables. It's trivial to turn a spreadsheet into an Access table (eg http://databases.about.com/aa123100a.htm). 2. Once you have all the representations in Access, you have a choice to make: standardise the data representation in Access, or export all the data in its various models to MySQL and standardise the mess there. Presumably you know Access much better than MySQL, so you may want to do this standardisation in Access before exporting to MySQL. In that case, you'll want to implement logic of the sort I described in Access, before exporting the data to MySQL. 3. You have choices for how to export tables from Access to MySQL. Simplest may be to export from Access to text files, then use the MySQL utility mysqlimport to import the data into a mysql table. Or, you can also use an Access-to-mysql converter script (Google for them), or a GUI tool like DbTools, which is freeware. Or (perhaps the most complex method), you can create the target MySQL table, use ODBCAdmin to create a DSN for it, and export to that. Is that the info you are looking for? PB - inferno wrote: Hi, First of all thank you for your reply. The table names I've wrote in the e-mail were just for info, I do use underscore instead of space, as you can see I do the same for the fields in the table. In theory it is simple but I need some help on how that will be "translated" to mysql, I have a concept in mind, but I do not know how to exactly apply it to the situation. I am in a situation where I have the data gathered from more persons and everybody had a personal way of designing the database or excel table and now I need to build a database that will include all the
RE: How thread-safe is mysql_real_connect()?
Wow, thats good to know. Thanks Jeremiah. It is a little strange that the
documentation doesn't mention that this behavior is different under windows and
leads one to believe that calling mysql_thread_init/end is still neccesary.
John
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tue 10/11/2005 6:52 AM
To: Jeremiah Gowdy
Cc: John McCaskey; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: Re: How thread-safe is mysql_real_connect()?
Jeremiah Gowdy [EMAIL PROTECTED] wrote on 10/11/2005 03:08:40 AM:
The Windows DLL is thread safe. You do not have to call my_init()
and my_thread_init() because Windows DLLs receive events when they
are attached to a new process and when they are attached to a new
thread in a process. This is one of the nicer features of Windows
shared libraries. Other than that, you don't have to do anything
special. I am a heavy user of libmysql under Win32. You simply
mysql_init() your MYSQL struct, and then mysql_real_connect() and
you're ready to mysql_query().
You should not call my_init() or my_thread_init() as the previous
poster suggested. This could result in memory leaks.
From libmysql/dll.c
BOOL APIENTRY LibMain(HANDLE hInst,DWORD ul_reason_being_called,
LPVOID lpReserved)
{
switch (ul_reason_being_called) {
case DLL_PROCESS_ATTACH: /* case of libentry call in win 3.x */
if (!inited++)
{
s_hModule=hInst;
libmysql_init();
main_thread=GetCurrentThreadId();
}
break;
case DLL_THREAD_ATTACH:
threads++;
my_thread_init();
break;
case DLL_PROCESS_DETACH: /* case of wep call in win 3.x */
if (!--inited) /* Safety */
{
/* my_thread_init() */ /* This may give extra safety */
my_end(0);
}
break;
case DLL_THREAD_DETACH:
/* Main thread will free by my_end() */
threads--;
if (main_thread != GetCurrentThreadId())
my_thread_end();
break;
default:
break;
} /* switch */
return TRUE;
UNREFERENCED_PARAMETER(lpReserved);
} /* LibMain */
- Original Message -
From: John McCaskey [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, October 07, 2005 10:31 AM
Subject: RE: How thread-safe is mysql_real_connect()?
Sean,
First let me thank you for all the great posts and info I've seen you
put on this list for others.
I've been working in C with MySQL in a very multithreaded environment
for several years and think I can explain the thread safety issues
clearly. Rather than try to respond point by point to your question I'm
going to give a summary and if that doesn't help please respond again
and I'll answer specific questions.
First, mysql is in fact pretty much threadsafe when using the _r
library. You definitely do need to use the _r library and not the
normal one as the SIGPIPE discussion applies to both, the non _r library
has additional safety issues surrounding mysql_real_connect() and should
not be used. On windows you don't really need to do anything here I
believe because the Windows binaries are by default compiled to be
thread-safe. (from
http://dev.mysql.com/doc/mysql/en/threaded-clients.html). To validate
this in your client code you should in the main() function close to
startup use mysql_thread_safe() to verify your linked in version is
thread safe.
The next thing you need to do is initialize mysql globally before
creating any threads that will use it. Simply call my_init(); in your
main thread. After this you can go ahead and create any threads. In
the threads you create you need to call mysql_thread_init(); and when
you end the thread mysql_thread_end(); in between these calls you can
just use mysql as normal and the mysql_real_connect function will be
thread safe, you do not need to perform any locking of your own to make
only one call at a time or anything along those lines.
Here is some pseudo code of what you need to do:
int main(int argc, char **argv) {
if(!mysql_thread_safe()) {
fprintf(stderr, Not Thread safe!!!);
return 1;
}
my_init();
// your regular init code
// create the threads that will use mysql
CreateThread();
}
void *mysql_thread(void *arg) {
mysql_thread_init();
//regular mysql code and whatever else here
//use mysql_real_connect and mysql_real_query
//and whatever without worrying about thread safety
mysql_thread_end();
}
John A. McCaskey
Software Development Engineer
Klir Technologies, Inc.
[EMAIL PROTECTED]
206.902.2027
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, October 07, 2005 9:01 AM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: How thread-safe is mysql_real_connect()?
snip
Shawn Green
Database Administrator
Re: Append one table to another?
At 7:57 -0700 10/11/05, Brian Dunning wrote: How do I append one table's contents to another? Both have identical structure. Problem is I don't have shell access, only phpAdmin or a PHP file I write upload myself. If the tables are identical, you can do this: INSERT INTO t2 SELECT * FROM t1; -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem compiling MyODBC - looking for sql.h file
Ryan Stille wrote: I posted this on the ODBC list, but there is not much traffic there and no one replied. Hopefully someone here has dealt with this. I am setting up ColdFusion to access a MySQL 4.1.x database. This required new MyODBC drivers to be installed on the system for ColdFusion. After a lot of trial and error, I got it figured out. I install mysql on the system (using --without-server), then install MyODBC. Then copy the libmyodbc3.so file to a directory under ColdFusion, and change ColdFusion's odbc.ini file to reference this new driver. I am now trying to do it on a second system, but am getting errors during the ./configure of MyODBC (3.5.11). This is my configure statement: ./configure --with-mysql-path=/usr/local/mysql \ --without-samples \ --disable-test \ --enable-thread-safe And this is the resulting output: ... ODBC DRIVER MANAGER CONFIGURATION - LIBRARIES AND HEADERS checking for isql... No checking for unixODBC version... Unknown checking sql.h usability... No checking sql.h presence... No checking for sql.h... no configure: error: Unable to find the unixODBC headers in '/usr/local//include' It's complaining about unixODBC headers, maybe specifically the sql.h file in the second to last line? But I don't have unixODBC installed on the FIRST system, the one that is working just fine. (ColdFusion has it's one built in ODBC manager, unixODBC is not necessary.) However there is a /usr/local/include/sql.h file on that system. I don't know how it got there. By looking inside the file it appears to be related to MyODBC 3.5.11. Just for kicks I copied this file to the second system, but got the same error when trying to run ./configure. Any ideas? -Ryan Ryan, It's part of unixODBC-devel walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Append one table to another?
Brian How do I append one table's contents to another? Both have identical structure. Problem is I don't have shell access, only phpAdmin or a PHP file I write upload myself. How about INSERT into tbl1 SELECT * FROM tbl2 PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.14/128 - Release Date: 10/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help on writing a sql statement
Hi Shawn: Thank you very much for your solution. It certainly helped me in understanding SQL a whole deal more. I Have one followup question as to the proposed solution, This query will be used in a multi-user situation and the logon to the database will be the same user (a web based app) ... Since I am creating a temp table, will the temp table creation fail for subsequent users prior to the drop i.e. the table will exist already exist best regards Imran. - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 9:49 AM Subject: Re: Help on writing a sql statement (my response bottom-posted. See below - SG) - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 10, 2005 4:17 PM Subject: Re: Help on writing a sql statement Imran [EMAIL PROTECTED] wrote on 10/10/2005 03:52:21 PM: Hi all: I need some help in writing a sql statement. I have three tables (Sales, Cust and Product). The sales table contains a large volume of data and I want to create a sql to group the sales table then join the resultant to both the Cust and Prod and to have additional fields selected from the Cust and Prod. So in effect something like (obviously syntax is wrong) Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales, sm.date from Sales SM where sm.date = date(‘2005-09-01 00:00:00’) Group by sm.prodno, sm.custno, sm.date , (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left join sm.prodno = prod.code left join sm.custno=cust.custno) … Any help would be greatly appreciated. OK, you know you need a GROUP BY, that's good. You also recognized you needed to JOIN a few tables together, also good. There are at least two ways to do what you ask. One is a fairly complex query that does it all in one statement (might take a long time to compute) the other is a sequence of two simpler statements. I think the two-statement solution will be easier to understand and maintain so I would prefer to go over that. However, in order to provide an example of either method I will need more information from you. From the CLI (command line client), please provide the output from these three commands: SHOW CREATE TABLE sales\G; SHOW CREATE TABLE cust\G; SHOW CREATE TABLE product\G; That will tell me exactly which columns live on which tables and where you do or do not have any indexes. Good indexes will make or break the performance of your database. You will not be exposing any data, only the design of the tables. Please remember to CC the list on all responses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Let me see if I can translate what you want in a query into regular language. I think you would like to see, grouped by date, customer, and product, the total cost and total sales for each (date,customer,product) triple along with each product's description ,code, and the customer's number and name. All of that will be limited to activity on or before midnight of a certain date. If I rephrased that correctly, here is how I would build your query. Step 1 is to perform the (date,customer,product) summations. By minimizing the number of rows, columns, and/or tables we need to summarize against, we improve performance. So I do this part of the analysis before I join in the other tables. Note: Date, time, and datetime literals are represented by single-quoted strings. You do not need the DATE() function to create a date literal. CREATE TEMPORARY TABLE tmpTotals ( key(CustNo) , key(ProdNo) ) SELECT PostingDate , CustNo , ProdNo , sum(Cost) as costs , sum(Sales) as sales FROM salesmaster WHERE PostingDate = '2005-09-01 00:00:00' GROUP BY PostingDate, CustNo, ProdNo; Step 2: collect the rest of the information for the report. SELECT CustNo , c.Name as custname , ProdNo , p.Name as prodname , costs , sales , PostingDate FROM tmpTotals tt LEFT JOIN customerintermediate c ON c.CustNo = tt.CustNo LEFT JOIN productintermediate p ON p.ProdNo = tt.ProdNo ORDER BY ... your choice... ; Step 3: The database is not your momma. Always clean up after yourself. DROP TEMPORARY TABLE tmpTotals; And you are done! The only trick to doing a sequence of statements in a row (like this) is that they all have to go through the same connection. As long as you do not close and re-open the connection between statements, any temp tables or @-variables you create or define remain in existence for the life of the connection. Depending on your connection library, you might be able to execute
Re: Append one table to another?
Hi Brian! How do I append one table's contents to another? Both have identical structure. Problem is I don't have shell access, only phpAdmin or a PHP file I write upload myself. You can do it this way: INSERT INTO table1 SELECT * FROM table2; If you need to have more control over what is copied, you can add a WHERE clause in the SELECT above. Also, you can restrict how it is done by adding some fields restrictions: INSERT INTO table1 (field1, field2) SELECT field1, field FROM table2 WHERE field1 = Some value Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help on writing a sql statement
Will you have name collisions with the same temporary table names used from different connections? Nope. Temporary tables and user-defined (@-variables) are connection-specific. Even if the same username/password combination is used to create several connections, each connection will have its own set of user-defined variables and temporary tables. What may get confusing is if you are using a connection pool manager (like ODBC) and you pick up a connection that you thought was new but was really just recycled. When you request a connection and close one and you are behind a connection pool manager, the manager doesn't actually create and destroy new connections each time. It merely loans you one it already has open and it will open or close the connections as it sees fit. When you try to close the connection, all you are really doing is telling the manager that it is OK for some other thread/process to use it. So long as you do not assume a variable to have a particular value unless you set it to be something (do not assume that a variable you haven't set is still null) and so long as you destroy any temporary tables when you are through using them, you shouldn't run into any inheritance problems from thread to thread. The good thing is that in the case of ODBC (at least on Win32) you can decide for each driver if you want the ODBC connection manager to pool connections or not. Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Imran [EMAIL PROTECTED] wrote on 10/11/2005 11:19:20 AM: Hi Shawn: Thank you very much for your solution. It certainly helped me in understanding SQL a whole deal more. I Have one followup question as to the proposed solution, This query will be used in a multi-user situation and the logon to the database will be the same user (a web based app) ... Since I am creating a temp table, will the temp table creation fail for subsequent users prior to the drop i.e. the table will exist already exist best regards Imran. - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 9:49 AM Subject: Re: Help on writing a sql statement (my response bottom-posted. See below - SG) - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 10, 2005 4:17 PM Subject: Re: Help on writing a sql statement Imran [EMAIL PROTECTED] wrote on 10/10/2005 03:52:21 PM: Hi all: I need some help in writing a sql statement. I have three tables (Sales, Cust and Product). The sales table contains a large volume of data and I want to create a sql to group the sales table then join the resultant to both the Cust and Prod and to have additional fields selected from the Cust and Prod. So in effect something like (obviously syntax is wrong) Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales, sm.date from Sales SM where sm.date = date(‘2005-09-01 00:00:00’) Group by sm.prodno, sm.custno, sm.date , (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left join sm.prodno = prod.code left join sm.custno=cust.custno) … Any help would be greatly appreciated. OK, you know you need a GROUP BY, that's good. You also recognized you needed to JOIN a few tables together, also good. There are at least two ways to do what you ask. One is a fairly complex query that does it all in one statement (might take a long time to compute) the other is a sequence of two simpler statements. I think the two-statement solution will be easier to understand and maintain so I would prefer to go over that. However, in order to provide an example of either method I will need more information from you. From the CLI (command line client), please provide the output from these three commands: SHOW CREATE TABLE sales\G; SHOW CREATE TABLE cust\G; SHOW CREATE TABLE product\G; That will tell me exactly which columns live on which tables and where you do or do not have any indexes. Good indexes will make or break the performance of your database. You will not be exposing any data, only the design of the tables. Please remember to CC the list on all responses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Let me see if I can translate what you want in a query into regular language. I think you would like to see, grouped by date, customer, and product, the total cost and total sales for each (date,customer,product) triple along with each product's description ,code, and the customer's number and name. All of that will be limited to activity on or before midnight of a
concat() function
hello, my friends, the query option 1 return the right value, the query option 2 not. Why concat function make the difference? *nloc_num,ins_numero and ins_digi are decimal(3,0) / option 1 .- select max(bi_num) as num from b_inmuebles where concat(nloc_num) = 1 and concat(ins_numero)= 1770 and concat(ins_digi)= 1 return it 1 / option 2 .- select max(bi_num) as num from b_inmuebles where nloc_num = 1 and ins_numero= 1770 and ins_digi= 1 return it NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Append one table to another?
INSERT into tbl1 SELECT * FROM tbl2 Thanks to both of you, but this is not working. Since one of the fields is a primary key that's duplicated in both tables (both tables have records numbered 1, 2, 3...), it won't allow the duplicate entries. Fortunately I do not need those primary key values to be preserved - it's OK to insert the new records with auto-increment values where the target table left off. So I tried: insert into target_table select `field1`,`field2` from original_table; and I listed all but the auto-increment field. This doesn't work either, it just says to check my syntax, but I can't see a problem. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help on writing a sql statement
Hi Shawn: I tried to run the sequences as you suggested in MySql Query Browser. I ran the first part with the create temp table then I ran the second select to see the result but when I tried the second SQL to get the created rows I get the message 'Table .tmpTotals doesn't exist' where =schema name. In additon, THANK you for taking the time to clarify the confusion about connections .. you are a wealth of information. best regards Imran - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 11:33 AM Subject: Re: Help on writing a sql statement Will you have name collisions with the same temporary table names used from different connections? Nope. Temporary tables and user-defined (@-variables) are connection-specific. Even if the same username/password combination is used to create several connections, each connection will have its own set of user-defined variables and temporary tables. What may get confusing is if you are using a connection pool manager (like ODBC) and you pick up a connection that you thought was new but was really just recycled. When you request a connection and close one and you are behind a connection pool manager, the manager doesn't actually create and destroy new connections each time. It merely loans you one it already has open and it will open or close the connections as it sees fit. When you try to close the connection, all you are really doing is telling the manager that it is OK for some other thread/process to use it. So long as you do not assume a variable to have a particular value unless you set it to be something (do not assume that a variable you haven't set is still null) and so long as you destroy any temporary tables when you are through using them, you shouldn't run into any inheritance problems from thread to thread. The good thing is that in the case of ODBC (at least on Win32) you can decide for each driver if you want the ODBC connection manager to pool connections or not. Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Imran [EMAIL PROTECTED] wrote on 10/11/2005 11:19:20 AM: Hi Shawn: Thank you very much for your solution. It certainly helped me in understanding SQL a whole deal more. I Have one followup question as to the proposed solution, This query will be used in a multi-user situation and the logon to the database will be the same user (a web based app) ... Since I am creating a temp table, will the temp table creation fail for subsequent users prior to the drop i.e. the table will exist already exist best regards Imran. - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 9:49 AM Subject: Re: Help on writing a sql statement (my response bottom-posted. See below - SG) - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 10, 2005 4:17 PM Subject: Re: Help on writing a sql statement Imran [EMAIL PROTECTED] wrote on 10/10/2005 03:52:21 PM: Hi all: I need some help in writing a sql statement. I have three tables (Sales, Cust and Product). The sales table contains a large volume of data and I want to create a sql to group the sales table then join the resultant to both the Cust and Prod and to have additional fields selected from the Cust and Prod. So in effect something like (obviously syntax is wrong) Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales, sm.date from Sales SM where sm.date = date(‘2005-09-01 00:00:00’) Group by sm.prodno, sm.custno, sm.date , (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left join sm.prodno = prod.code left join sm.custno=cust.custno) … Any help would be greatly appreciated. OK, you know you need a GROUP BY, that's good. You also recognized you needed to JOIN a few tables together, also good. There are at least two ways to do what you ask. One is a fairly complex query that does it all in one statement (might take a long time to compute) the other is a sequence of two simpler statements. I think the two-statement solution will be easier to understand and maintain so I would prefer to go over that. However, in order to provide an example of either method I will need more information from you. From the CLI (command line client), please provide the output from these three commands: SHOW CREATE TABLE sales\G; SHOW CREATE TABLE cust\G; SHOW CREATE TABLE product\G; That will tell me exactly which columns live on
Re: Non-linear degradation in bulk loads?
Jon, my guess is that the inserts to the UNIQUE secondary index cause the workload to be seriously disk-bound. Two solutions: 1) sort the rows to be inserted on the key 'email' before inserting. 2) Or: http://dev.mysql.com/doc/mysql/en/innodb-tuning.html If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily turning off the uniqueness checks during the import session: SET UNIQUE_CHECKS=0; For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. But make sure you do not have any duplicates in the rows! Note that now you can get support on this MySQL mailing list from a Vice President of Oracle. I hope that the level of support improves. Best regards, Heikki Vice President, server technology Oracle/Innobase Oy ... Everyone, We're trying to do some bulk data loads on several different tables (on several different machines, using several different techniques) and seeing dramatically worse-than-linear performance. We've tried the bulk-INSERT syntax, and the LOAD DATA INFILE syntax. We've done ALTER TABLE ... DISABLE KEYS, SET FOREIGN_KEY_CHECKS=0 (where appropriate), and so forth. The one that is the most immediate concern is a table of the form: CREATE TABLE `test` ( `email` varchar(255) NOT NULL default '', `when_happened` datetime NOT NULL default '-00-00 00:00:00', UNIQUE KEY `email` (`email`), KEY `when_happened` (`when_happened`) ) TYPE=InnoDB; I'm loading data using LOAD DATA INFILE with chunks containing 3.4m rows each (~135MB files). The first chunk was very quick (about 1.5 minutes), but the tenth chunk has taken 22.6 hours and is still going. (It's been getting progessively slower with each chunk...) The database is our main sites database but we've dramatically reduced the load on that machine over the past couple months through careful optimization of our code. The box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We have 1GB allocated to the buffer pool, and our usual 1GB * 3 log files. 8 I/O threads. Load on the box sits at around 6-7, with a large (50%) amount of time spent in wait state, but actual disk throughput to our software RAID array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k blocks/s in. Something *has* to be wrong here, but we're not sure what we've missed. We've restored larger data sets from a mysqldump in the past in dramatically less time on far inferior hardware. (A superset of this same data to a schema which is also a superset, PLUS a bunch of other rather large tables -- all in ~8 hours on a 3Ware RAID array on a dual Xeon w/ 4GB of RAM) We're inclined to believe that this is a configuration problem, as opposed to a driver or hardware problem given the non-linear nature of the performance degradation. This implies we're doing something truly stupid with our loads. What could cause this kind of strangeness? -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Append one table to another?
At 9:00 -0700 10/11/05, Brian Dunning wrote: INSERT into tbl1 SELECT * FROM tbl2 Thanks to both of you, but this is not working. Since one of the fields is a primary key that's duplicated in both tables (both tables have records numbered 1, 2, 3...), it won't allow the duplicate entries. Fortunately I do not need those primary key values to be preserved - it's OK to insert the new records with auto-increment values where the target table left off. So I tried: insert into target_table select `field1`,`field2` from original_table; and I listed all but the auto-increment field. This doesn't work either, it just says to check my syntax, but I can't see a problem. Any ideas? The manual is your friend: http://dev.mysql.com/doc/mysql/en/insert-select.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Append one table to another?
On Oct 11, 2005, at 8:24 AM, Jose Miguel Pérez wrote: INSERT INTO table1 (field1, field2) SELECT field1, field FROM table2 Jose's solution worked perfectly. Thanks everyone, sorry for being so dense today. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help on writing a sql statement
The CREATE TABLE ... SELECT ... command is all one command. You weren't supposed to break it up. Sorry if I didn't make that very clear (my fault!!). You should probably be on a SCRIPT tab not a QUERY tab if you are running this through QueryBrowser in order to execute more than one statement at a time. I don't use it very often but I think that QB doesn't maintain connections between calls on the same tab (can't remember and can't test right now) and IIRC, the QUERY tabs only take one command at a time. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Imran [EMAIL PROTECTED] wrote on 10/11/2005 12:00:25 PM: Hi Shawn: I tried to run the sequences as you suggested in MySql Query Browser. I ran the first part with the create temp table then I ran the second select to see the result but when I tried the second SQL to get the created rows I get the message 'Table .tmpTotals doesn't exist' where =schema name. In additon, THANK you for taking the time to clarify the confusion about connections .. you are a wealth of information. best regards Imran - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 11:33 AM Subject: Re: Help on writing a sql statement Will you have name collisions with the same temporary table names used from different connections? Nope. Temporary tables and user-defined (@-variables) are connection-specific. Even if the same username/password combination is used to create several connections, each connection will have its own set of user-defined variables and temporary tables. What may get confusing is if you are using a connection pool manager (like ODBC) and you pick up a connection that you thought was new but was really just recycled. When you request a connection and close one and you are behind a connection pool manager, the manager doesn't actually create and destroy new connections each time. It merely loans you one it already has open and it will open or close the connections as it sees fit. When you try to close the connection, all you are really doing is telling the manager that it is OK for some other thread/process to use it. So long as you do not assume a variable to have a particular value unless you set it to be something (do not assume that a variable you haven't set is still null) and so long as you destroy any temporary tables when you are through using them, you shouldn't run into any inheritance problems from thread to thread. The good thing is that in the case of ODBC (at least on Win32) you can decide for each driver if you want the ODBC connection manager to pool connections or not. Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Imran [EMAIL PROTECTED] wrote on 10/11/2005 11:19:20 AM: Hi Shawn: Thank you very much for your solution. It certainly helped me in understanding SQL a whole deal more. I Have one followup question as to the proposed solution, This query will be used in a multi-user situation and the logon to the database will be the same user (a web based app) ... Since I am creating a temp table, will the temp table creation fail for subsequent users prior to the drop i.e. the table will exist already exist best regards Imran. - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 9:49 AM Subject: Re: Help on writing a sql statement (my response bottom-posted. See below - SG) - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 10, 2005 4:17 PM Subject: Re: Help on writing a sql statement Imran [EMAIL PROTECTED] wrote on 10/10/2005 03:52:21 PM: Hi all: I need some help in writing a sql statement. I have three tables (Sales, Cust and Product). The sales table contains a large volume of data and I want to create a sql to group the sales table then join the resultant to both the Cust and Prod and to have additional fields selected from the Cust and Prod. So in effect something like (obviously syntax is wrong) Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales, sm.date from Sales SM where sm.date = date(‘2005-09-01 00:00:00’) Group by sm.prodno, sm.custno, sm.date , (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left join sm.prodno = prod.code left join sm.custno=cust.custno) … Any help would be greatly appreciated. OK, you know you need a GROUP BY, that's good. You also
Re: Suppress table header when using ODBC
Gleb Paharenko wrote: Hello. I am using a desktop program that imports data from a mysql What program? Printbench Pro for one little glitch: it adds one extra row at the beginning of the dataset with the names of the columns in it. I need for that Does you program show column names in the numeric fields? In a manner of speaking, Yes. When you DO the query it shows all the data in spreadsheet fashion. The columns all have headers and you can't edit it. THen when you go to layout, all the field headers are used as placeholders and may be drug around and formatted in the layout. I have also contacted Elkriver tech support about this, but I thought it might be an issue I could solve with a mysql solution. The problem is I want to bar encode one of the fields, and the bar encoding I want to use is numeric only. So it craps out the whole column just because the first row has a non numeric field header. and that is why I need it to go away. Gerald Taylor wrote: Hello, I am using a desktop program that imports data from a mysql database using the ODBC mysql driver and everything works fine except for one little glitch: it adds one extra row at the beginning of the dataset with the names of the columns in it. I need for that column name row to NOT be there. Do I do something to the query to suppress is or is it some setting I set up? I've googled and nothing. MySQL 4.1 if it matters. I know I remember reading somewhere how to suppress this. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temp table full (I think) -- how do I fix this?
Hi, I have a job runnning that's modifying a column on a 15-million-row table and is throwing out the following error: Output: Replication Error 1114, slave: replicatenj07, error: Error 'The table '#sql-5303_3c' is full' on query. Default database 'customer__upgrade'. Query: ALTER TABLE inc_performance MODIFY COLUMN. This indicates to me that the temp table is full. Problem is, I'm not sure what config parm or whatever needs to be changed to allow the temp table to grow sufficiently. Can someone push me in the right direction on what to change, or if I'm barking up the wrong tree, show me which tree to bark at? Thanks, --Walt Weaver Bozeman, Montana
Re: Temp table full (I think) -- how do I fix this?
Walt Weaver wrote: Hi, I have a job runnning that's modifying a column on a 15-million-row table and is throwing out the following error: Output: Replication Error 1114, slave: replicatenj07, error: Error 'The table '#sql-5303_3c' is full' on query. Default database 'customer__upgrade'. Query: ALTER TABLE inc_performance MODIFY COLUMN. This indicates to me that the temp table is full. Problem is, I'm not sure what config parm or whatever needs to be changed to allow the temp table to grow sufficiently. Can someone push me in the right direction on what to change, or if I'm barking up the wrong tree, show me which tree to bark at? Thanks, --Walt Weaver Bozeman, Montana Walt, Run mysql show variables; There are several config options for temp stuff such as size directory. I don't know the syntax to change these off the top of my head but it should point you to the correct tree. :-) walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query help
lets say i have two tables: module - VARCHAR module_name INTEGER module_id module_config INTEGER module_id VARCHAR config_name VARCHAR config_value config item names and values are rather arbitrary and depend on the module. each module can have zero to many config items.. How do i find out the id of a module with the following two module_config entries: config_name='mapping' and config_value='true' and config_name='loc_enabled' and config_value='true' it seems like i would have to use a subquery to check two rows at the same time. I wasn't really sure how to express it in SQL though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help
[EMAIL PROTECTED] wrote:
lets say i have two tables:
module
-
VARCHAR module_name
INTEGER module_id
module_config
INTEGER module_id
VARCHAR config_name
VARCHAR config_value
config item names and values are rather arbitrary and depend on the module.
each module can have zero to many config items..
How do i find out the id of a module with the following two module_config
entries:
config_name='mapping' and config_value='true'
and
config_name='loc_enabled' and config_value='true'
it seems like i would have to use a subquery to check two rows at the
same time. I wasn't really sure how to express it in SQL though.
You have two options:
1) Join to table module_config twice, looking for one row in the first copy
and the second row in the second copy:
SELECT m.module_id, m.module_name
FROM module m
JOIN module_config mc1 ON m.module_id = mc1.module_id
JOIN module_config mc2 ON m.module_id = mc2.module_id
WHERE mc1.config_name = 'mapping' AND mc1.config_value = 'true'
AND mc2.config_name = 'loc_enabled' AND mc2.config_value = 'true'
2) Join to module_config once and look for either row. Each module_id will
have 0, 1, or 2 matching rows in module_config. You want the ones with 2
matching rows (met both requirements):
SELECT m.module_id, m.module_name
FROM module m
JOIN module_config mc ON m.module_id = mc.module_id
WHERE mc.config_value = 'true'
AND mc.config_name IN ('mapping', 'loc_enabled')
GROUP BY m.module_id
HAVING COUNT(*) = 2;
Try both ways to see which is faster for your data. If you ever need to
match more than 2 config_names, you'll definitely want option 2, as it will
be easier to write and faster.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help
[EMAIL PROTECTED] wrote on 10/11/2005 01:27:12 PM:
lets say i have two tables:
module
-
VARCHAR module_name
INTEGER module_id
module_config
INTEGER module_id
VARCHAR config_name
VARCHAR config_value
config item names and values are rather arbitrary and depend on the
module.
each module can have zero to many config items..
How do i find out the id of a module with the following two
module_config entries:
config_name='mapping' and config_value='true'
and
config_name='loc_enabled' and config_value='true'
it seems like i would have to use a subquery to check two rows at
the same time. I wasn't really sure how to express it in SQL though.
No need for a subquery. There are at least two ways. The most direct uses
a self join but it does not scale well to more than two or three
conditions to match
SELECT a.module_id
FROM module_config a
INNER JOIN module_config b
on b.module_id = a.module_id
and b.config_name = 'loc_enabled'
and b.config_value = 'true'
WHERE a.config_name = 'mapping
and a.config_value='true';
The more flexible method is to use a COUNT() and a HAVING() to test for
how many conditions each module_id matches. It scales much better to more
than 2 or 3 conditions to match
SELECT module_id, count(1) as matches
FROM module_config
WHERE config_name IN ('loc_enabled', 'mapping')
AND config_value='true'
GROUP BY module_id
HAVING matches = 2;
Make sense? If you need to reuse the list of module_id's in another query,
just dump the results of this SELECT into a TEMPORARY TABLE and use them
from there.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Temp table full (I think) -- how do I fix this?
Thanks, as it turns out the solution to the problem was a bit more mundane: we ran out of disk space on the partition the tables are on. :) --Walt On 10/11/05, walt [EMAIL PROTECTED] wrote: Walt Weaver wrote: Hi, I have a job runnning that's modifying a column on a 15-million-row table and is throwing out the following error: Output: Replication Error 1114, slave: replicatenj07, error: Error 'The table '#sql-5303_3c' is full' on query. Default database 'customer__upgrade'. Query: ALTER TABLE inc_performance MODIFY COLUMN. This indicates to me that the temp table is full. Problem is, I'm not sure what config parm or whatever needs to be changed to allow the temp table to grow sufficiently. Can someone push me in the right direction on what to change, or if I'm barking up the wrong tree, show me which tree to bark at? Thanks, --Walt Weaver Bozeman, Montana Walt, Run mysql show variables; There are several config options for temp stuff such as size directory. I don't know the syntax to change these off the top of my head but it should point you to the correct tree. :-) walt
Re: Temp table full (I think) -- how do I fix this?
Hello. Have a look here: http://dev.mysql.com/doc/mysql/en/full-table.html Walt Weaver wrote: Hi, I have a job runnning that's modifying a column on a 15-million-row table and is throwing out the following error: Output: Replication Error 1114, slave: replicatenj07, error: Error 'The table '#sql-5303_3c' is full' on query. Default database 'customer__upgrade'. Query: ALTER TABLE inc_performance MODIFY COLUMN. This indicates to me that the temp table is full. Problem is, I'm not sure what config parm or whatever needs to be changed to allow the temp table to grow sufficiently. Can someone push me in the right direction on what to change, or if I'm barking up the wrong tree, show me which tree to bark at? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Suppress table header when using ODBC
Hello. Printbench Pro I haven't found any information while was searching by the 'Printbench' key word at mysql.com (to say the truth I was unable to find a lot of even in Google). So maybe someone other could help. Gerald Taylor wrote: Gleb Paharenko wrote: Hello. I am using a desktop program that imports data from a mysql What program? Printbench Pro for one little glitch: it adds one extra row at the beginning of the dataset with the names of the columns in it. I need for that Does you program show column names in the numeric fields? In a manner of speaking, Yes. When you DO the query it shows all the data in spreadsheet fashion. The columns all have headers and you can't edit it. THen when you go to layout, all the field headers are used as placeholders and may be drug around and formatted in the layout. I have also contacted Elkriver tech support about this, but I thought it might be an issue I could solve with a mysql solution. The problem is I want to bar encode one of the fields, and the bar encoding I want to use is numeric only. So it craps out the whole column just because the first row has a non numeric field header. and that is why I need it to go away. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: concat() function
Hello. *nloc_num,ins_numero and ins_digi are decimal(3,0) You're using 1770 for comparison which is not in the type range. In my opinion, for incorrect data you may obtain incorrect answers. Luciano Centeno wrote: hello, my friends, the query option 1 return the right value, the query option 2 not. Why concat function make the difference? *nloc_num,ins_numero and ins_digi are decimal(3,0) / option 1 .- select max(bi_num) as num from b_inmuebles where concat(nloc_num) =3D 1 and concat(ins_numero)=3D 1770 and concat(ins_digi)=3D 1 return it 1 / option 2 .- select max(bi_num) as num from b_inmuebles where nloc_num =3D 1 and ins_numero=3D 1770 and ins_digi=3D 1 return it NULL -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Aborted connection problems
I seem to be getting heaps of aborted connections, and I can't figure
out why. MySQL's error (from mysqld.err, below) is Got an error reading
communication packets, but this is a socket link to localhost so that
rules out network problems. I simply can't figure it out. Has anyone got
any ideas?
mysqld Ver 5.0.13-rc-log for pc-linux-gnu on x86_64 (Gentoo Linux)
[/tmp/mysqld.sql]
051012 21:53:44 27 Connect [EMAIL PROTECTED] on album
27 Query SET NAMES utf8
28 Connect [EMAIL PROTECTED] on album
28 Query SET NAMES utf8
28 Query CALL getRandomPhoto()
29 Connect [EMAIL PROTECTED] on album
29 Query SET NAMES utf8
29 Query CALL getHelp('gallery')
29 Quit
[/var/log/mysql/mysqld.err]
051012 21:53:44 [Warning] Aborted connection 27 to db: 'album' user:
'album_read' host: 'localhost' (Got an error reading communication packets)
051012 21:53:44 [Warning] Aborted connection 28 to db: 'album' user:
'album_read' host: 'localhost' (Got an error reading communication packets)
--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
directory path storage
Can someone pelase explain to me how to store the pathname and the filename in a MySQL database , I need it to loop thru a Folder and grab all pathnames and filesnames within that folder and store it in a MySQL database,, thanks
MySQL and FULL JOIN
Hi, I would like to know if there is a way to do a full join in MySQL and if yes, what version should I use or if there is any other way to get the same result as a full join. I am curently using 4.0.24 on linux. Best regards, Cristian Stoica -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aborted connection problems
Just to add to this further, if I reduce max_allowed_packet in my.cnf
down to around 16M, then the error changes to Got a packet bigger than
'max_allowed_packet' bytes.
However, as you can see from the SQL below, the longest SQL statement
I'm executing is around 21 bytes, certainly nowhere near 16MB.
Jasper Bryant-Greene wrote:
I seem to be getting heaps of aborted connections, and I can't figure
out why. MySQL's error (from mysqld.err, below) is Got an error reading
communication packets, but this is a socket link to localhost so that
rules out network problems. I simply can't figure it out. Has anyone got
any ideas?
mysqld Ver 5.0.13-rc-log for pc-linux-gnu on x86_64 (Gentoo Linux)
[/tmp/mysqld.sql]
051012 21:53:44 27 Connect [EMAIL PROTECTED] on album
27 Query SET NAMES utf8
28 Connect [EMAIL PROTECTED] on album
28 Query SET NAMES utf8
28 Query CALL getRandomPhoto()
29 Connect [EMAIL PROTECTED] on album
29 Query SET NAMES utf8
29 Query CALL getHelp('gallery')
29 Quit
[/var/log/mysql/mysqld.err]
051012 21:53:44 [Warning] Aborted connection 27 to db: 'album' user:
'album_read' host: 'localhost' (Got an error reading communication packets)
051012 21:53:44 [Warning] Aborted connection 28 to db: 'album' user:
'album_read' host: 'localhost' (Got an error reading communication packets)
--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Non-linear degradation in bulk loads?
At 11:41 PM 10/10/2005, you wrote: Hi Jon, Well, may be the next suggestions might help you. Disable Keys does apply to non-unique keys only. So I suggest to focus on your unique Email key. You could do some tests with: a. drop the unique key on Email b. load the various bulks c. after loading, define Email, eg. as (unique) Primary Key. After b. you can do also ALTER TABLE ORDER BY Email, to get Email index in pace with the physical order of the data. Hope it helps ... Best wishes, Cor - Original Message - From: Jon Frisby [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 4:12 AM Subject: Non-linear degradation in bulk loads? Everyone, We're trying to do some bulk data loads on several different tables (on several different machines, using several different techniques) and seeing dramatically worse-than-linear performance. We've tried the bulk-INSERT syntax, and the LOAD DATA INFILE syntax. We've done ALTER TABLE ... DISABLE KEYS, SET FOREIGN_KEY_CHECKS=0 (where appropriate), and so forth. The one that is the most immediate concern is a table of the form: CREATE TABLE `test` ( `email` varchar(255) NOT NULL default '', `when_happened` datetime NOT NULL default '-00-00 00:00:00', UNIQUE KEY `email` (`email`), KEY `when_happened` (`when_happened`) ) TYPE=InnoDB; I'm loading data using LOAD DATA INFILE with chunks containing 3.4m rows each (~135MB files). The first chunk was very quick (about 1.5 minutes), but the tenth chunk has taken 22.6 hours and is still going. (It's been getting progessively slower with each chunk...) The database is our main sites database but we've dramatically reduced the load on that machine over the past couple months through careful optimization of our code. The box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We have 1GB allocated to the buffer pool, and our usual 1GB * 3 log files. 8 I/O threads. Load on the box sits at around 6-7, with a large (50%) amount of time spent in wait state, but actual disk throughput to our software RAID array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k blocks/s in. Something *has* to be wrong here, but we're not sure what we've missed. We've restored larger data sets from a mysqldump in the past in dramatically less time on far inferior hardware. (A superset of this same data to a schema which is also a superset, PLUS a bunch of other rather large tables -- all in ~8 hours on a 3Ware RAID array on a dual Xeon w/ 4GB of RAM) We're inclined to believe that this is a configuration problem, as opposed to a driver or hardware problem given the non-linear nature of the performance degradation. This implies we're doing something truly stupid with our loads. What could cause this kind of strangeness? -JF JF, It's likely the indexes that are causing the slowdown. If you remove all indexes from the table definition and start with an empty table, you should see a dramatic speed increase. When all the data has been loaded, use one Alter Table command to rebuild all the indexes. You could try to optimize the table after each load to see if that speeds things up (this will rebalance the index distribution). I was able to load 100 million rows relatively fast, but failed miserably at 500 million rows because of insufficient memory. Mike P.S. I suppose you already know when you use Load Data to load data into an empty table, it won't update the indexes until the load has completed. This is why loading data into an empty table is much faster than loading data into a table that has rows in it (even if it is only 1 row). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aborted connection problems
Hello.
I was unable to find reported bugs with similar symptoms for the 5.0
version (hope I haven't missed something). But at the bottom of:
http://dev.mysql.com/doc/mysql/en/packet-too-large.html
we have these lines:
You can also get strange problems with large packets if you are using
large BLOB values but have not given mysqld access to enough memory to
handle the query. If you suspect this is the case, try adding ulimit -d
256000 to the beginning of the mysqld_safe script and restarting mysqld.
I don't know if you're using BLOBS in your SPs, but the problem could be
in that MySQL doesn't have enough memory to complete the query. If you
are able to make a repeatable test case you may want to report a bug
at bugs.mysql.com (remember, you may win one of the iPods :) See:
http://dev.mysql.com/mysql_5_contest.html
Very often trace files might be helpful. See:
http://dev.mysql.com/doc/mysql/en/making-trace-files.html
Jasper Bryant-Greene wrote:
Just to add to this further, if I reduce max_allowed_packet in my.cnf
down to around 16M, then the error changes to Got a packet bigger than
'max_allowed_packet' bytes.
However, as you can see from the SQL below, the longest SQL statement
I'm executing is around 21 bytes, certainly nowhere near 16MB.
Jasper Bryant-Greene wrote:
I seem to be getting heaps of aborted connections, and I can't figure
out why. MySQL's error (from mysqld.err, below) is Got an error
reading communication packets, but this is a socket link to localhost
so that rules out network problems. I simply can't figure it out. Has
anyone got any ideas?
mysqld Ver 5.0.13-rc-log for pc-linux-gnu on x86_64 (Gentoo Linux)
[/tmp/mysqld.sql]
051012 21:53:44 27 Connect [EMAIL PROTECTED] on album
27 Query SET NAMES utf8
28 Connect [EMAIL PROTECTED] on album
28 Query SET NAMES utf8
28 Query CALL getRandomPhoto()
29 Connect [EMAIL PROTECTED] on album
29 Query SET NAMES utf8
29 Query CALL getHelp('gallery')
29 Quit
[/var/log/mysql/mysqld.err]
051012 21:53:44 [Warning] Aborted connection 27 to db: 'album' user:
'album_read' host: 'localhost' (Got an error reading communication
packets)
051012 21:53:44 [Warning] Aborted connection 28 to db: 'album' user:
'album_read' host: 'localhost' (Got an error reading communication
packets)
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [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: MySQL and FULL JOIN
Hello. MySQL doesn't support full joins, but you can emulate them. Read through this article: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.html inferno wrote: Hi, I would like to know if there is a way to do a full join in MySQL and if yes, what version should I use or if there is any other way to get the same result as a full join. I am curently using 4.0.24 on linux. Best regards, Cristian Stoica -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Question about innodb, ibdata1
Fredrik, - Original Message - From: Fredrik Carlsson [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 10, 2005 9:57 PM Subject: Question about innodb, ibdata1 Hi, I'm using InnoDB with tablespaces for almost all tables. The last few week the file ibdata1 has started to grow, should it really do this when using tablespaces? i did an alter on one of the bigger tables some time if you are using innodb_file_per_table in my.cnf, then the tables are stored in .ibd files. If the table that you ALTER is not stored in the ibdata1 file, then the ibdata1 file should not grow in the ALTER. Note that InnoDB stores undo logs to ibdata files. If you forget a transaction dangling then purge cannot remove the undo log files. That would cause ibdata1 to grow constantly. Use: SHOW INNODB STATUS\G to determine if you have dangling transactions. ago to alter the size of a varchar collumn, could this has something to do with it? I'm using MySQL 4.1.11 // Fredrik Carlsson Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Non-linear degradation in bulk loads?
Two solutions: 1) sort the rows to be inserted on the key 'email' before inserting. 2) Or: http://dev.mysql.com/doc/mysql/en/innodb-tuning.html If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily turning off the uniqueness checks during the import session: SET UNIQUE_CHECKS=0; For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. But make sure you do not have any duplicates in the rows! After sending my mail, I discovered SET UNIQUE_CHECKS=0, and subsequent to that it also occurred to me to try putting the data in in sorted order. Unfortunately, doing UNIQUE_CHECKS=0 did not work, and even the combination of both did not work. First chunk (3.4m rows) was ~1.5 minutes, second was ~5 minutes... At this point I'm inclined to believe that there is something very wrong with the disk subsystem because of this and other problems (doing a large cp from the datapool filesystem to another filesystem brought the database to a near-halt, among other things). As a stop-gap solution, I created the table with no indexes, and loaded all the data (loaded in linear time), and plan on doing a CREATE UNIQUE INDEX on the table. Will this happen in linear time, or near-linear time? *sigh* -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Administrator Cron Errors
Hi folks. Downloaded the new Admin for OS X. I set a weekly cron and for some reason, it's emailing my terminal Mail app saying it couldn't load a profile. It's not the address I told it to send it to, and I don't quite get why it can't load a profile. Having just tried to back up my tables twice directly from the application, it crashed twice. Is this thing buggy? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
