Help needed urgently
Hello, I am a software developer, I am using Mysql-4.1.3b-beta-nt, ODBC3.5.1 driver on windows PC for an application development. the application which i am developing is a client-server architecture based,in which we have to store data of the BSM(Base Station Manager of CDMA network). the nature of data is records containing alarms and faults occuring in the CDMA system,after storing the data we need to generate statistical reports on these data my table structure is as fallows, CREATE TABLE ind_kar_bng_robocop_bsc_0_pm_ipc_0 ( gan_id INTEGER NOT NULL, bsc_id INTEGER NOT NULL, bts_id INTEGER NOT NULL, bd_type VARCHAR(10) NOT NULL, bd_id INTEGER NOT NULL, duplex VARCHAR(10) NOT NULL, data_GenTime DATETIME NOT NULL, item_id INTEGER NOT NULL, M0 INTEGER NOT NULL, M1 INTEGER NOT NULL, M2 INTEGER NOT NULL, M3 INTEGER NOT NULL, M4 INTEGER NOT NULL, M5 INTEGER NOT NULL, M6 INTEGER NOT NULL, M7 INTEGER NOT NULL, M8 INTEGER NOT NULL, M9 INTEGER NOT NULL, M10 INTEGER NOT NULL, M11 INTEGER NOT NULL, M12 INTEGER NOT NULL, M13 INTEGER NOT NULL, M14 INTEGER NOT NULL, M15 INTEGER NOT NULL, M16 INTEGER NOT NULL, M17 INTEGER NOT NULL, M18 INTEGER NOT NULL, M19 INTEGER NOT NULL, M20 INTEGER NOT NULL, M21 INTEGER NOT NULL, M22 INTEGER NOT NULL, M23 INTEGER NOT NULL, M24 INTEGER NOT NULL, M25 INTEGER NOT NULL, M26 INTEGER NOT NULL, M27 INTEGER NOT NULL, M28 INTEGER NOT NULL, M29 INTEGER NOT NULL, M30 INTEGER NOT NULL, M31 INTEGER NOT NULL, KEY DateIndex (data_GenTime), KEY gan_idIndex (gan_id) , KEY bsc_idIndex (bsc_id) , KEY bts_idIndex (bts_id) , KEY bd_typeIndex (bd_type) , KEY bd_idIndex (bd_id) , KEY item_idIndex (item_id)); the type of query that is executed is as below mysql select Sum(m0),Avg(m1),Max(m5),Min(m6) from ind_kar_bng_robocop_bsc_0_pm_ipc_0 where bsc_id = 0 and bts_id = 255 and data_Gentime between 2004-11-22 00:00:00 and 2004-12-10 19:41:44 and item_id = 0; +--+---+-+-+ | Sum(m0) | Avg(m1) | Max(m5) | Min(m6) | +--+---+-+-+ | 23376896 | 1154.9079 | 0 | 0 | +--+---+-+-+ 1 row in set (3 min 30.35 sec) this query executed when the record count in the table ind_kar_bng_robocop_bsc_0_pm_ipc_0 was 79,21,988 records mysql select count(*) from ind_kar_bng_robocop_bsc_0_pm_ipc_0; +--+ | count(*) | +--+ | 7921988 | +--+ as the number of record in the table keep on growing the query response time increases.. the explain select result of the same query is here mysql explain select Sum(m0),Avg(m1),Max(m5),Min(m6) from ind_kar_bng_robocop_bsc_0_pm_ipc_0 where bsc_id = 0 and bts_id = 255 and data_Gentime between 2004-11-22 00:00:00 and 2004-12-10 19:41:44 and item_id = 0 \G *** 1. row *** id: 1 select_type: SIMPLE table: ind_kar_bng_robocop_bsc_0_pm_ipc_0 type: ref possible_keys: DateIndex,bsc_idIndex,bts_idIndex,item_idIndex key: item_idIndex key_len: 4 ref: const rows: 820535 Extra: Using where 1 row in set (0.28 sec) these are my system variables. mysql show variables; +-+---+ | Variable_name | Value | +-+---+ | back_log| 50 | | basedir | C:\mysql\ | | 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 | C:\mysql\share\charsets/ | | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir
Unable to login in MySQL database after installation
Hi, After I installed rpm packages on my system, I am unable to connect to the databse. I have installed in the following order 1)Server 2)Client 3)Devel 4)Shared Compat After starting the databse with: /etc/rc.d/init.d/mysql start I am unable to login as: /usr/bin/mysqladmin -u root password 'new-password' The following error occurs: /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Please Help Me.
problems with php 4.3.9 / mysql 4.1.7
Hello, I recently upgraded the MySQL server from 4.0.15 to 4.1.7, without significant problems. But I continued to use PHP-4.3.9 linked against the old MySQL 4.0.15 on the webservers for some time. Everything went ok, up until the point when I tried linking PHP-4.3.9 against MySQL-4.1.7. After starting the new Apache/PHP (ldd httpd shows '/usr/local/mysql-4.1.7/lib/mysql/libmysqlclient.so.14'), I get a whole lot of Segmentation Faults in the Apache error log. Also, I have noticed in the PHP error log that a lot of MySQL queries aren't succesfully made (usually the error message is 'unable to save result set'). Running those queries by hand, in a mysql console yields a valid row(s), but the table header is completely messed up. Traced the problem to those queries being cached, so I ran a 'reset query cache'. Then those queries executed successfully, but I'm still getting Apache segmentation faults. Here are some details: On the MySQL server I have FreeBSD 4.10-STABLE running. MySQL 4.1.7 was compiled from ports with the command line: make DB_DIR=/db/mysql WITH_LINUXTHREADS=yes OVERWRITE_DB=no BUILD_OPTIMIZED=yes BUILD_STATIC=yes The webservers are running FreeBSD 4.10-STABLE as well, Apache/1.3.33 having PHP-4.3.9 compiled as a static module. PHP's configure line is: ./configure --prefix=/usr/local/php --enable-track-vars --enable-trans-sid --disable-path-info-check --enable-memory-limit --without-pear --with-apache=../apache_1.3.33 --with-config-file-path=/usr/local/apache/conf/ --enable-safe-mode --with-curl --with-mysql=/usr/local/mysql-4.1.7 --with-jpeg-dir=/usr/local/jpeg-6b/ --with-png-dir=/usr/local/libpng-1.2.5/ --with-ttf --with-pdflib --with-gd --enable-gd-native-ttf --with-zlib-dir=/usr Any idea what could lead to this incompatibility between PHP using the 4.1.7 MySQL library and the MySQL 4.1.7 server, incompatibility which doesn't exist between the same MySQL 4.1.7 server and PHP linked against MySQL 4.0.15 library? Thanks, bogdan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize table table / nonascii letter crash
Description: OPTIMIZE TABLE on a table will sometimes destroy a table with non ascii characters (e.g. Norwegian letters). mysql optimize table feil; crashtest.feil | optimize | error| 127 when fixing table crashtest.feil | optimize | error| Can't copy datafile-header to tempfile, error 9 crashtest.feil | optimize | status | Operation failed myisamcheck -o is required to restore the table. How-To-Repeat: DROP TABLE feil; CREATE TABLE `feil` ( `a` int(10) unsigned NOT NULL default '0', `b` varchar(50) NOT NULL default '', `c` text NOT NULL, `d` smallint(5) unsigned NOT NULL default '0', `e` int(10) unsigned NOT NULL default '0', `g` varchar(15) NOT NULL default '', `f` varchar(50) NOT NULL default '', `h` int(10) unsigned NOT NULL default '0', `i` int(10) unsigned NOT NULL default '0', `j` int(10) unsigned NOT NULL default '0', `k` int(10) unsigned NOT NULL default '0', `l` int(10) unsigned NOT NULL default '0', `m` varchar(15) NOT NULL default '', `n` smallint(5) unsigned NOT NULL default '0', `o` tinyint(4) NOT NULL default '0', `p` int(10) unsigned NOT NULL default '0', `q` smallint(5) unsigned NOT NULL default '0', `r` char(1) NOT NULL default 'S', `s` char(1) NOT NULL default 'z', `t` int(10) unsigned NOT NULL default '0', `u` int(11) NOT NULL default '0', `v` varchar(30) NOT NULL default '', `w` decimal(4,2) NOT NULL default '0.00', PRIMARY KEY (`a`), KEY `d` (`d`), KEY `g` (`g`), KEY `f` (`f`), KEY `h` (`h`), KEY `i` (`i`), KEY `n` (`n`), KEY `b` (`b`), KEY `s` (`s`), FULLTEXT KEY `ft` (`b`,`c`,`f`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; insert into feil (a, b, c, f) values (0, 'samkjensle menigheten','tylfta serverte folkloren bestykning ram evolusjon tchad prent assistere homofili','sjekkhefte opklarningen'); insert into feil (a, b, c, f) values (1, 'syntaks sprelsk','vigler side argentinske monstret kontinentalsokkel attført aust nødvendigste horka travsportens','mulmet digelstålet'); insert into feil (a, b, c, f) values (2, 'ordentlige voldsom','huke vekhet lisse uvant porter manet skjemten skarven skrir hette','fjesket berliner'); insert into feil (a, b, c, f) values (3, 'helsing utjevninga','gud pekte entropien avgangen kompetansen uganderen drenert brosjyren prakker talg','bevoktede brygg'); insert into feil (a, b, c, f) values (4, 'sørlandsbanen måpe','hedningmisjon sjefer gutten hardna hyppig fordervet protegerte flis bowle snibler','ararat gylt'); insert into feil (a, b, c, f) values (5, 'spenningas læreren','demra fratrukket gjenskinnet fyr dørg prøysser toarma tverrpolitisk stridsskrift tjuveriet','anionen kafeer'); insert into feil (a, b, c, f) values (6, 'kulissen røske','pysete genealogien engstelsen havarert sjukling understøttet solusjonen kremter famlende klosettet','prosessen demonstrerte'); insert into feil (a, b, c, f) values (7, 'posøren kalkunen','sterkstrømsanlegget guineansk kronikøren klassisismen agitator hjemsøkt fritidssysler thorium eple stutte','sinoberen tott'); insert into feil (a, b, c, f) values (8, 'døyvet godtas','innsjø trikse spilles tallmessig konverteringslån råfiskloven kunngjorde tilpasningsvansker effektiviserte filantropi','opplivelse lunka'); insert into feil (a, b, c, f) values (9, 'balsamere stedets','punkthvirvler arbeidskontor tumler tildragelse fingeren deputasjon innstillinger forberedelsene tørstedrikken trøytte','pløyd oppfordring'); update feil set b='vandalisere marxisten',c='arbeidsulykker barnsomshjem kommisjon kristiansand konditori makedonieren skattepolitikk substantivet annonsebyrå pressgjæren',f='pysen panamaner' where a=0; update feil set b='tilstedelig karaffel',c='val trøyte tråvbane assuransen kontinentet økonomien skånsom badedrakt forspille parodien',f='plakat francisturbin' where a=1; update feil set b='faksimile ansette',c='voss overhalingen intellektuelle haglladningen trøttet motorveier oppkava lovgivningen irra elegisk',f='avvirkning skjelettet' where a=2; update feil set b='faderlig fremme',c='tilbaketrekningen beslutningsprosessen diagnostiserte modning gode pute ekspress borda bommert utbyggingsfond',f='nage tilbehøret' where a=3; update feil set b='retusj ansiktsuttrykket',c='substratet observeres meiet sanskulott akset balalaikaen kvantitative vennlighet sufflere pakkasse',f='kommisjonen grell' where a=4; update feil set b='einveldig amfetamin',c='sørtrøndelag selvråderett moderninsering tilbringertjeneste resp fyllinga forgifta vantrivselen forfeila bearbeidet',f='kanner fremgangsmåte' where a=5; update feil set b='beklemt vassgraut',c='smitt uforståelig uforenlig bemanna let vatner fråtser gruggen atomfellesskap blygd',f='roterer skibakke' where a=6; update feil set b='gnuen tjernet',c='nobeliumet spartansk svigermor frostvæske tar vanke utgivelsen kveldsmat venns påvisningen',f='marsjandiser fakler' where a=7; update feil set b='posthuset forskningspolitikk',c='vorspiel typene solvens remedier makkoen
Re: Excel 2 mysql
Ziggy, I'm not sure what you need help with. If you want to get data into MySQL from Excel, consider saving the data (I assume it is a table) as a CSV then using the LOAD DATA command to bring the data into a temporary table in MySQL. Take a look at the command in the help pages. Regards, Adam David Ziggy Lubowa Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to login in MySQL database after installation
Hi, Most likely your MySQL has no password for root in the default instalation. Try: # mysql -u root -p and simly press enter when asked for the password. Worked ? Alright, so now it's time to give root a password: # mysqladmin -u root password 'my_secret_password' That's all that there is to it :) Regards, Mário Gamito Mysql Plusplus wrote: Hi, After I installed rpm packages on my system, I am unable to connect to the databse. I have installed in the following order 1)Server 2)Client 3)Devel 4)Shared Compat After starting the databse with: /etc/rc.d/init.d/mysql start I am unable to login as: /usr/bin/mysqladmin -u root password 'new-password' The following error occurs: /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Please Help Me. -- Mário Gamito Administração de sistemas e desenvolvimento Netual - Multimédia e Telecomunicações, Lda. Rua João Afonso, Nº1 3800-198 Aveiro - Portugal Tel. +351 234 371 431 / Fax. +351 234 371 438 E-mail: [EMAIL PROTECTED] www.netual.pt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data question in cross database replication
Hello. It seems to be a bug: http://bugs.mysql.com/bug.php?id=6353 Sanjeev Sagar [EMAIL PROTECTED] wrote: Hello Gleb, My question was related to LOAD DATA INFILE, not LOAD DATA FROM MASTER.=20 LOAD DATA INFILE work those slaves which are not using --replicate-rewrite-db. It do not work for those which are using this. Thanks for you reply. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Friday, December 03, 2004 4:14 AM To: [EMAIL PROTECTED] Subject: Re: Load data question in cross database replication Hello. --replicate-rewrite-db is not taken into account while executing LOAD DATA FROM MASTER. See: http://dev.mysql.com/doc/mysql/en/LOAD_DATA_FROM_MASTER.html Sanjeev Sagar [EMAIL PROTECTED] wrote: --=20 For technical support contracts, goto https://order.mysql.com/?ref=3Densita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com --=20 MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: Excel 2 mysql
On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- 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: Excel 2 mysql
David, In the load data infile command you can specify the delimiter character (see manual). It is a comma in CSV files, but I think it defaults to the TAB character in load data infile. You can specify the delimiter to be comma in the load command. However, I prefer to export from Excel as TAB separated, as you don't have to mess with the load data command. Also, more importantly, data fields often contain commas which screws everything up, whereas TABS are much less common in Excell data fields. Hope this helps, Andy -Original Message- From: David Ziggy Lubowa [mailto:[EMAIL PROTECTED] Sent: 08 December 2004 23:55 To: Eric Bergen Cc: [EMAIL PROTECTED] Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
David, Please provide the complete LOAD DATA INFILE command you used. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: David Ziggy Lubowa [EMAIL PROTECTED] To: Eric Bergen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 6:54 PM Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
David Ziggy Lubowa wrote: cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . - if you're copying the csv file between a Windows and a *NIX box, you might have issues with carriage-return characters. Try running dos2unix against the file to convert it. - ian -- +---+ | Ian Sales Database Administrator | | | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server hanging once a week (Solaris 8)
Hi all; I have a Mysql 3.2.58 server running on Solaris 8 (the latest 3.2 version downloaded from Mysql.com). The system is a Ultra-250 server with multiple CPUs and 2G RAM. There are many databases but they're not large and the load on the system is not high at all. We're using this older version because we developed the database with the 3.2.x release, years ago, and don't have the manpower to check to see if the newer version will work or fix issues we might run into--although we may have to find a way. This version worked without problems for over a year. But, for the last 4-6 weeks we've seen a problem once every 8-10 days where the server hangs and will not come back. Attempts to attach normally hang; current operations hang; etc. Using processlist shows some processes which have existed for a long time; that may or may not be correct, I'm not sure. Almost all our queries are coming in through the Perl DBI/DBD interface. When I try to use mysqladmin kill they don't die; they do go to a state of killed though. When I try to myqladmin flush-tables, that hangs. Flush-logs works but there is nothing interesting in the logs. When I try to run mysqld stop, the server process doesn't stop, although most of the hung requests When I run strace on the PID, I get this: lwp_sema_wait(0x1bfe50 then it hangs until I ^C. I have to kill -9 the server PID to bring it down. Then I check my tables but so far (cross fingers) they've all been fine except they are not shut properly, so I fix that with myisamchk. Since I have not changed anything I suspect that the IS folks have installed some new patch on the Solaris system which is causing this problem to appear, but I have no proof of that. Does anyone have any advice or information for me: places to look, things to do, etc. etc.? While so far we haven't lost any data this is a problem since it seems like it will continue to happen. -- --- Paul D. Smith [EMAIL PROTECTED] HASMAT--HA Software Mthds Tools Please remain calm...I may be mad, but I am a professional. --Mad Scientist --- These are my opinions---Nortel Networks takes no responsibility for them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help me pls
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Wu_Yang_SKZ, JPcommon wrote: java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306? at org.gjt.mm.mysql.MysqlIO.init(MysqlIO.java:275) at org.gjt.mm.mysql.Connection.init(Connection.java:230) at org.gjt.mm.mysql.Driver.connect(Driver.java:126) at java.sql.DriverManager.getConnection(DriverManager.java:512) at java.sql.DriverManager.getConnection(DriverManager.java:171) Wu, You're using a _very_ old version of the JDBC driver. Try upgrading to something more recent, like 3.0.16 at http://dev.mysql.com/downloads/connector/j/3.0.html and see if the problem goes away. (This is especially true if you are trying to connect to MySQL 4.1 or newer). -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity Office: +1 708 332 0507 www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBtwEctvXNTca6JD8RAh49AJ9/Nk62obyErj6QGDiGZKeTcaCumgCdFiJb G6WC74IeSWAN4o3e3k5CIzI= =D4BQ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication update bug/error/problem.
Eric Bergen wrote: Jay, Are you using the replicate-do-db option on the slave? This option relies on 'use' being set correctly when the query is issued. A quote from the manual explains it better than I can: Tells the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this will not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database URL:http://dev.mysql.com/doc/mysql/en/Replication_Options.html Other possibilities are to use show slave status; and show master status; to make sure queries are actually being sent from the master to the slave. I am not using cross database updates. It is all on one database but the update uses two tables. The query update content_review_site as a,site_rating_factors as b set a.overall_rating = 77 where a.content_id=243 is a stripped down version of a bigger but i stripped down to the point of failing. The failing factor is when i use content_review_site as a,site_rating_factors as b (not a cross database but a cross table query). And i am using replicate-do-table on both the tables in the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup for 24/5 system
Hi! I'm running a system that will have fairly high demands in uptime (24/5) soon. It would be ok to be down on the weekends but during workdays it must be running. Right now we stop the server during nights but it will not be possible soon so we are looking into replicate our server to a backup that we can take down whenever we need to to make the backups (or other maintaince). So, is this a good way to solve the backups or do you think that I should consider any other solutions? Thanks in advance Roland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sum problem....
I have 2 tables, identical in fields. One is an 'archived items' table, the other is a live items. I want to run a query that will grab the sum of all products in both tables, like: The results would display the total qty by item: QTY NAME The logic is something like: sum(table1.qty) + sum(table2.qty) The query that I came up with is: select (sum(table1.qty) + sum(table2.qty)) as val,name from table1,table2 group by name order by val desc It is just a bit off... (like 3 off the actual count :) ) I have tried to do it with a where and group by clause, but no luck there either... Any suggestions? TIA! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sum problem....
[snip] I have 2 tables, identical in fields. One is an 'archived items' table, the other is a live items. [/snip] Are they also identical in record count? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SubQueries
Hi, I have a sub query in oracle, I want to convert it into Mysql 4.0.21 compatible SELECT PAGE_SERVICE.TIMEOUT, PAGE_SERVICE.PAGE_SERVICE_COMMENT, PAGE_SERVICE.NUMERICMSGMAXSIZE, PAGE_SERVICE.ALPHAMSGMAXSIZE, PAGE_SERVICE.PASSWORD, PAGE_SERVICE.PHONE_NO, PAGE_SERVICE.NAME, PAGE_SERVICE.PAGE_SERVICE_ID FROM PAGE_SERVICE WHERE NOT EXISTS (SELECT 1 FROM PAGER WHERE PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID) WITH CHECK OPTION; The above query is not displaying all the records whose PAGER.PAGE_SERVICE_ID is equal to PAGE_SERVICE.PAGE_SERVICE_ID. As subqueries are not supporting in mysql, could any one of you please give a solution . Waiting for the reply. Thanks in Advance Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: Sum problem....
Mike Morton wrote: I have 2 tables, identical in fields. One is an 'archived items' table, the other is a live items. I want to run a query that will grab the sum of all products in both tables, like: The results would display the total qty by item: QTY NAME The logic is something like: sum(table1.qty) + sum(table2.qty) The query that I came up with is: select (sum(table1.qty) + sum(table2.qty)) as val,name from table1,table2 group by name order by val desc It is just a bit off... (like 3 off the actual count :) ) I have tried to do it with a where and group by clause, but no luck there either... Any suggestions? - you've got a Cartesian results set because you haven't joined the tables. Try joining on name, or writing two queries and then using a UNION. - ian -- +---+ | Ian Sales Database Administrator | | | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help interpreting SHOW INNODB Status Message
Emmett, if you have an index on a column, say c CHAR(10) and the index contains adjacent records 'aabbaguu' and 'abbaguu', then a 'gap' is the gap between those values in the alphabetical order. If you try to insert, say, 'aac' to the index, it would fall in the 'gap'. Note that the 'next-key' locking information in InnoDB is carried by existing records in the index. You cannot lock just some subinterval of a 'gap'. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: Emmett Bishop [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Lähetetty: Wednesday, December 08, 2004 12:16 AM Aihe: Re: Help interpreting SHOW INNODB Status Message Heikki, Thanks for the input. Right now we can't move to 4.1, we're stuck with 4.0.20 for sometime. So, it there someway that through improving the schema these issues could be addressed? I added an index on these tables reflecting the way that data is usually looked up (always by session_guid and label). Every user has a unique session_guid that looks something like: 7E6E4C38-4819-11D9-9802-84B0A828F8C4 What I don't understand is what the gap is. I can understand in the example on the mysql site (select * from table where id 100 for update) how that would lock all records with id 100. How does it work in the case of a string based index? Suppose there were two different users, one with guid 7E6E4C38-4819-11D9-9802-84B0A828F8C4 and the other with guid 5G4F4H10-4819-11D9-9802-84B0A828F8C4 (note, these two guids are fairly representative in form). The first user was performing a delete and the second was performing an insert what gap would be created? What kind of schema change might reduce/eliminate the contention? Thanks, Tripp --- Heikki Tuuri [EMAIL PROTECTED] wrote: Emmett, ok, trx (1) is trying to lock gap G, but trx (2) already has a lock on it. And trx (2) is trying to insert a new record into that same gap G. Even a waiting lock request on a gap prevents new inserts into it = a deadlock. In 4.1.7, you might avoid this deadlock. In 4.1.7, a waiting gap lock does not need to wait just because someone else has a granted lock on the same gap. But, of course, your transactions might deadlock a bit later. DELETEs and INSERTs in the same gap very easily lead to a deadlock. In 4.1, you can also set innodb_locks_unsafe_for_binlog in my.cnf. That removes almost all gap locking. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Emmett Bishop [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, December 07, 2004 5:44 PM Subject: Re: Help interpreting SHOW INNODB Status Message Heikki, We're using MySQL 4.0.20. Here's the full output: = 041207 8:33:26 INNODB MONITOR OUTPUT = Per second averages calculated from the last 15 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 49506785, signal count 48823098 Mutex spin waits 963371106, rounds 3338424450, OS waits 2853796 RW-shared spins 80886102, OS waits 38728129; RW-excl spins 2488320, OS waits 302033 LATEST FOREIGN KEY ERROR 041206 23:52:41 Transaction: TRANSACTION 0 10799828, ACTIVE 2 sec, process no 31424, OS thread id 2942950320 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 6 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 88051, query id 14858689 host ip user update INSERT INTO s_filter (fname, lname, email, eff_oldest_dt, eff_latest_dt, move_oldest_dt, move_latest_dt, agent_id, no_agent_sw, exclude_holds_sw, custom_message_sw, pics_only_sw, price, wiz_city_name, subdivision, tot_finished_sqft, bedrooms, garage_spaces, bathrooms, area, subarea, year, property_t Foreign key constraint fails for table `ahf_test/s_filter`: , CONSTRAINT `s_filter_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) Trying to add in child table, in index `idx_session_guid` tuple: DATA TUPLE: 2 fields; 0: len 36; hex 3745364534482d343831392d313144392d393830322d383442304138323846384334; asc 7E6E4C38-4819-11D9-9802-84B0A828F8C4;; 1: len 6; hex 0048a79b; ascH ;; But in parent table `ahf_test/s_session`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32 0: len 30; hex 37454139364146452d343743442d313144392d393830322d383442304138; asc 7EA96AFE-47CD-11D9-9802-84B0A8;...(truncated); 1: len 6; hex 00a49aaf; asc ;; 2: len 7; hex 0340082073; asc@ s;; 3: len 8; hex 8000123a34f916a6; asc:4 ;; LATEST DETECTED DEADLOCK 041207 7:32:14 *** (1) TRANSACTION:
RE: Sum problem....
[snip] No, they have different record numbers... [/snip] a. Always reply to the list. I just happened to spot this in my spam folder while cleaning. 2. You're getting Cartesian results, so your query needs to be a little more specific Your original query -- select (sum(table1.qty) + sum(table2.qty)) as val,name from table1,table2 group by name order by val desc try this (not tested) SELECT (SUM(table1.qty) + SUM(table2.qty)) as val, table1.name FROM table1 LEFT OUTER JOIN table2 ON(table1.identifier = table2.identifier) GROUP BY table1.name ORDER BY val DESC You may have to do 2 queries with (1)WHERE table2.identifier IS NULL //or (2)IS NOT NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries
Since 4.0.22 does NOT have subqueries, you will have to use a JOIN http://dev.mysql.com/doc/mysql/en/JOIN.html http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html ... as in this example SELECT PAGE_SERVICE.TIMEOUT , PAGE_SERVICE.PAGE_SERVICE_COMMENT , PAGE_SERVICE.NUMERICMSGMAXSIZE , PAGE_SERVICE.ALPHAMSGMAXSIZE , PAGE_SERVICE.PASSWORD , PAGE_SERVICE.PHONE_NO , PAGE_SERVICE.NAME , PAGE_SERVICE.PAGE_SERVICE_ID FROM PAGE_SERVICE LEFT JOIN PAGER ON PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID WHERE PAGER.PAGE_SERVICE_ID IS NULL; ... best wishes! Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 12/08/2004 09:36:09 AM: Hi, I have a sub query in oracle, I want to convert it into Mysql 4.0.21 compatible SELECT PAGE_SERVICE.TIMEOUT, PAGE_SERVICE.PAGE_SERVICE_COMMENT, PAGE_SERVICE.NUMERICMSGMAXSIZE, PAGE_SERVICE.ALPHAMSGMAXSIZE, PAGE_SERVICE.PASSWORD, PAGE_SERVICE.PHONE_NO, PAGE_SERVICE.NAME, PAGE_SERVICE.PAGE_SERVICE_ID FROM PAGE_SERVICE WHERE NOT EXISTS (SELECT 1 FROM PAGER WHERE PAGE_SERVICE.PAGE_SERVICE_ID = PAGER.PAGE_SERVICE_ID) WITH CHECK OPTION; The above query is not displaying all the records whose PAGER.PAGE_SERVICE_ID is equal to PAGE_SERVICE.PAGE_SERVICE_ID. As subqueries are not supporting in mysql, could any one of you please give a solution . Waiting for the reply. Thanks in Advance Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: chroot mysql
Hello Usually error log is located in data directory. You may manually specify the location of error log with --log-error[=file] or use --console command line option for mysqld to see error messages. Or just start it with -V command line option to make sure that it at least can start. M?rio Gamito [EMAIL PROTECTED] wrote: Hi, I'm trying to chroot MySQL according to these instructions in linux.com: http://security.linux.com/article.pl?sid=04/08/19/1422204tid=73tid=35 But the fact is that MySQL never starts (actualy, i think it starts and dies the next second). Can't find any errors in logs :( Any ideas of what can it be ? Any help would be apreciated. Warm Regards. -- 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 to reduce the query response time?
note: this is this message's second sending after some snippage to meet the 3 character limit for the list and other mild editing.-- SG This is the query you would like to optimize: select Sum(m0),Avg(m1),Max(m5),Min(m6) from ind_kar_bng_robocop_bsc_0_pm_ipc_0 where bsc_id = 0 and bts_id = 255 and data_Gentime between 2004-11-22 00:00:00 and 2004-12-10 19:41:44 and item_id = 0; If you frequently query on bsc_id, bts_id, item_id, and data_Gentime (which may be a reasonable combination of values to frequently search by) you can usually improve query responses by creating a compound index that contains all of those columns because MySQL does not *yet* combine single-column indexes to improve performance (it's a 5.2+ optimization and is still in the works) ALTER TABLE ind_kar_bng_robocop_bsc_0_pm_ipc_0 ADD KEY(data_Gentime, item_id, bts_id, bsc_id) That index should also improve any using any of these combinations of fields in the WHERE statement: date_Gentime (alone) data_Gentime, item_id data_Gentime, item_id, bts_id But that index could not assist for queries only looking for: item_id, bts_id, bsc_id bts_id, bsc_id bsc_id data_Gentime, bts_id, bsc_id data_Gentime, bsc_id data_Gentime, bts_id That is because the index can only assist to locate values where you query the index from left to right. If you skip an indexed column then a compound index may become unuseful. If your query patterns are slightly different, you may want to try a different order to the columns or to try creating multiple compound indexes, each one with a slightly different column composition and order. Additional reading (also available in other languages!): http://dev.mysql.com/doc/mysql/en/EXPLAIN.html http://dev.mysql.com/doc/mysql/en/SELECT_speed.html http://dev.mysql.com/doc/mysql/en/Where_optimizations.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine chetan t [EMAIL PROTECTED] wrote on 12/07/2004 12:07:39 AM: Hello, I am a software developer, I am using Mysql-4.1.3b-beta-nt, ODBC3.5.1 driver on windows PC for an application development. the application which i am developing is a client-server architecture based,in which we have to store data of the BSM(Base Station Manager of CDMA network). the nature of data is records containing alarms and faults occuring in the CDMA system,after storing the data we need to generate statistical reports on these data my table structure is as fallows, CREATE TABLE ind_kar_bng_robocop_bsc_0_pm_ipc_0 ( gan_id INTEGER NOT NULL, bsc_id INTEGER NOT NULL, bts_id INTEGER NOT NULL, bd_type VARCHAR(10) NOT NULL, bd_id INTEGER NOT NULL, duplex VARCHAR(10) NOT NULL, data_GenTime DATETIME NOT NULL, item_id INTEGER NOT NULL, M0 INTEGER NOT NULL, M1 INTEGER NOT NULL, M2 INTEGER NOT NULL, M3 INTEGER NOT NULL, M4 INTEGER NOT NULL, M5 INTEGER NOT NULL, M6 INTEGER NOT NULL, M7 INTEGER NOT NULL, M8 INTEGER NOT NULL, M9 INTEGER NOT NULL, M10 INTEGER NOT NULL, M11 INTEGER NOT NULL, M12 INTEGER NOT NULL, M13 INTEGER NOT NULL, M14 INTEGER NOT NULL, M15 INTEGER NOT NULL, M16 INTEGER NOT NULL, M17 INTEGER NOT NULL, M18 INTEGER NOT NULL, M19 INTEGER NOT NULL, M20 INTEGER NOT NULL, M21 INTEGER NOT NULL, M22 INTEGER NOT NULL, M23 INTEGER NOT NULL, M24 INTEGER NOT NULL, M25 INTEGER NOT NULL, M26 INTEGER NOT NULL, M27 INTEGER NOT NULL, M28 INTEGER NOT NULL, M29 INTEGER NOT NULL, M30 INTEGER NOT NULL, M31 INTEGER NOT NULL, KEY DateIndex (data_GenTime), KEY gan_idIndex (gan_id) , KEY bsc_idIndex (bsc_id) , KEY bts_idIndex (bts_id) , KEY bd_typeIndex (bd_type) , KEY bd_idIndex (bd_id) , KEY item_idIndex (item_id)); the type of query that is executed is as below mysql select Sum(m0),Avg(m1),Max(m5),Min(m6) from ind_kar_bng_robocop_bsc_0_pm_ipc_0 where bsc_id = 0 and bts_id = 255 and data_Gentime between 2004-11-22 00:00:00 and 2004-12-10 19:41:44 and item_id = 0; +--+---+-+-+ | Sum(m0) | Avg(m1) | Max(m5) | Min(m6) | +--+---+-+-+ | 23376896 | 1154.9079 | 0 | 0 | +--+---+-+-+ 1 row in set (3 min 30.35 sec) this query executed when the record count in the table ind_kar_bng_robocop_bsc_0_pm_ipc_0 was 79,21,988 records mysql select count(*) from ind_kar_bng_robocop_bsc_0_pm_ipc_0; +--+ | count(*) | +--+ | 7921988 | +--+ as the number of record in the table keep on growing the query response time increases.. the explain select result of the same query is here mysql explain select Sum(m0),Avg(m1),Max(m5),Min(m6) from ind_kar_bng_robocop_bsc_0_pm_ipc_0 where bsc_id = 0 and bts_id = 255 and data_Gentime
slow query issues
Hello all,
Recently, I have been seeing some strange behavior from a particular query on
my 4.0.21 mysql server. Here is the query in question:
SELECT size_id, sum(imps) imps, sum(clicks) clicks, sum(convs) convs,
sum(imp_revenue) imp_revenue, sum(click_revenue) click_revenue,
sum(conv_revenue) conv_revenue, sum(international_imps) international_imps,
sum(adjustments) / 60 adjs, sum(publisher_compensation) / 60 comp FROM
publisher_summary WHERE ymdh = '2004-11-01 05:00:00' AND ymdh '2004-12-01
05:00:00' AND is_ym_advertiser=0 GROUP BY size_id;
There is an index on ymdh, another on both is_ym_advertiser and ymdh, and
another on size_id. The query is using the index that is on both
is_ym_advertiser and ymdh and is scanning 7 million rows according to explain
select. Explain select also says that it is a query of type ref. The table it
is selecting from contains approximately 27 million rows.
Until a few days ago, the query would take about 2 minutes to complete. This is
a long time, but considering how often this query is run, it was acceptable.
Now this query is taking more than 30 minutes to return data and I have no idea
why. I did some testing and replicated the table to my testing environment
without the indexes. Here the query is taking the normal 2-3 minutes without
the indexes. Is it possible to determine if the index is corrupted? I also
tried to run this query adding an IGNORE INDEX to skip all the indexes in
production, but it still took forever. So this makes me think it isn't the
indexes. I don't really have a good window to run OPTIMIZE in so I just want to
see if the table or index is corrupted before I go ahead and schedule that
window. Better yet, is there a more efficient way to write this query?
Any help is greatly appreciated.
Regards,
Max
Max Michaels
Right Media, LLC | 276 5th Avenue, Suite 605 | New York , NY 10001
212.561.6475 | mmichaels {at} rightmedia.com | www.rightmedia.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrating Live Chat and MySQL
Hello. I didn't find any similar problem in bugs.mysql.com and bugs.php.net. To exclude inconsistence with Apache compile PHP as a command line program and check your queries. May be you should turn to PHP developers. I recently upgraded the MySQL server from 4.0.15 to 4.1.7, without significant problems. But I continued to use PHP-4.3.9 linked against the old MySQL 4.0.15 on the webservers for some time. Everything went ok, up until the point when I tried linking PHP-4.3.9 against MySQL-4.1.7. After starting the new Apache/PHP (ldd httpd shows '/usr/local/mysql-4.1.7/lib/mysql/libmysqlclient.so.14'), I get a whole lot of Segmentation Faults in the Apache error log. Also, I have noticed in the PHP error log that a lot of MySQL queries aren't succesfully made (usually the error message is 'unable to save result set'). Lindsay [EMAIL PROTECTED] wrote: unning those queries by hand, in a mysql console yields a valid row(s), but the table header is completely messed up. Traced the problem to those queries being cached, so I ran a 'reset query cache'. Then those queries executed successfully, but I'm still getting Apache segmentation faults. Here are some details: On the MySQL server I have FreeBSD 4.10-STABLE running. MySQL 4.1.7 was compiled from ports with the command line: make DB_DIR=/db/mysql WITH_LINUXTHREADS=yes OVERWRITE_DB=no BUILD_OPTIMIZED=yes BUILD_STATIC=yes The webservers are running FreeBSD 4.10-STABLE as well, Apache/1.3.33 having PHP-4.3.9 compiled as a static module. PHP's configure line is: ./configure --prefix=/usr/local/php --enable-track-vars --enable-trans-sid --disable-path-info-check --enable-memory-limit --without-pear --with-apache=../apache_1.3.33 --with-config-file-path=/usr/local/apache/conf/ --enable-safe-mode --with-curl --with-mysql=/usr/local/mysql-4.1.7 --with-jpeg-dir=/usr/local/jpeg-6b/ --with-png-dir=/usr/local/libpng-1.2.5/ --with-ttf --with-pdflib --with-gd --enable-gd-native-ttf --with-zlib-dir=/usr Any idea what could lead to this incompatibility between PHP using the 4.1.7 MySQL library and the MySQL 4.1.7 server, incompatibility which doesn't exist between the same MySQL 4.1.7 server and PHP linked against MySQL 4.0.15 library? Thanks, bogdan -- 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: problems with php 4.3.9 / mysql 4.1.7
On Wed, Dec 08, 2004 at 11:25:55AM +0100, Bogdan TARU wrote: Everything went ok, up until the point when I tried linking PHP-4.3.9 against MySQL-4.1.7. After starting the new Apache/PHP (ldd httpd shows '/usr/local/mysql-4.1.7/lib/mysql/libmysqlclient.so.14'), I get a whole lot of Segmentation Faults in the Apache error log. Also, I have noticed in the PHP error log that a lot of MySQL queries aren't succesfully made (usually the error message is 'unable to save result set'). I noticed other MySQL 4.1 + PHP oddness, but with PHP 5 and Linux: http://00f.net/blogs/index.php/2004/11/28/bug_or_vulnerability_in_php_5_with_mysql_1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: SQL Dump
Hello, I am new to mySQL. Someone gave me a dump file and i need to do an import from linux. How can i go about doing this can someone help me? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems with php 4.3.9 / mysql 4.1.7
I didn't find any similar problem in bugs.mysql.com. To exclude inconsistence with Apache compile PHP as a command line program and check your queries. May be you should turn to PHP developers. Bogdan TARU [EMAIL PROTECTED] wrote: Hello, I recently upgraded the MySQL server from 4.0.15 to 4.1.7, without significant problems. But I continued to use PHP-4.3.9 linked against the old MySQL 4.0.15 on the webservers for some time. Everything went ok, up until the point when I tried linking PHP-4.3.9 against MySQL-4.1.7. After starting the new Apache/PHP (ldd httpd shows '/usr/local/mysql-4.1.7/lib/mysql/libmysqlclient.so.14'), I get a whole lot of Segmentation Faults in the Apache error log. Also, I have noticed in the PHP error log that a lot of MySQL queries aren't succesfully made (usually the error message is 'unable to save result set'). Running those queries by hand, in a mysql console yields a valid row(s), but the table header is completely messed up. Traced the problem to those queries being cached, so I ran a 'reset query cache'. Then those queries executed successfully, but I'm still getting Apache segmentation faults. Here are some details: On the MySQL server I have FreeBSD 4.10-STABLE running. MySQL 4.1.7 was compiled from ports with the command line: make DB_DIR=/db/mysql WITH_LINUXTHREADS=yes OVERWRITE_DB=no BUILD_OPTIMIZED=yes BUILD_STATIC=yes The webservers are running FreeBSD 4.10-STABLE as well, Apache/1.3.33 having PHP-4.3.9 compiled as a static module. PHP's configure line is: ./configure --prefix=/usr/local/php --enable-track-vars --enable-trans-sid --disable-path-info-check --enable-memory-limit --without-pear --with-apache=../apache_1.3.33 --with-config-file-path=/usr/local/apache/conf/ --enable-safe-mode --with-curl --with-mysql=/usr/local/mysql-4.1.7 --with-jpeg-dir=/usr/local/jpeg-6b/ --with-png-dir=/usr/local/libpng-1.2.5/ --with-ttf --with-pdflib --with-gd --enable-gd-native-ttf --with-zlib-dir=/usr Any idea what could lead to this incompatibility between PHP using the 4.1.7 MySQL library and the MySQL 4.1.7 server, incompatibility which doesn't exist between the same MySQL 4.1.7 server and PHP linked against MySQL 4.0.15 library? Thanks, bogdan -- 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: Server hanging once a week (Solaris 8)
Hello. Very often similar symptoms causes various lock problems. Do you use InnoDB? Try log_slow_queries to find hung queries. Paul Smith [EMAIL PROTECTED] wrote: Hi all; I have a Mysql 3.2.58 server running on Solaris 8 (the latest 3.2 version downloaded from Mysql.com). The system is a Ultra-250 server with multiple CPUs and 2G RAM. There are many databases but they're not large and the load on the system is not high at all. We're using this older version because we developed the database with the 3.2.x release, years ago, and don't have the manpower to check to see if the newer version will work or fix issues we might run into--although we may have to find a way. This version worked without problems for over a year. But, for the last 4-6 weeks we've seen a problem once every 8-10 days where the server hangs and will not come back. Attempts to attach normally hang; current operations hang; etc. Using processlist shows some processes which have existed for a long time; that may or may not be correct, I'm not sure. Almost all our queries are coming in through the Perl DBI/DBD interface. When I try to use mysqladmin kill they don't die; they do go to a state of killed though. When I try to myqladmin flush-tables, that hangs. Flush-logs works but there is nothing interesting in the logs. When I try to run mysqld stop, the server process doesn't stop, although most of the hung requests When I run strace on the PID, I get this: lwp_sema_wait(0x1bfe50 then it hangs until I ^C. I have to kill -9 the server PID to bring it down. Then I check my tables but so far (cross fingers) they've all been fine except they are not shut properly, so I fix that with myisamchk. Since I have not changed anything I suspect that the IS folks have installed some new patch on the Solaris system which is causing this problem to appear, but I have no proof of that. Does anyone have any advice or information for me: places to look, things to do, etc. etc.? While so far we haven't lost any data this is a problem since it seems like it will continue to happen. -- 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: Integrating Live Chat and MySQL
Sorry this meant to be follow-up to Bogdan Taru post. Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. I didn't find any similar problem in bugs.mysql.com and bugs.php.net. To exclude inconsistence with Apache compile PHP as a command line program and check your queries. May be you should turn to PHP developers. I recently upgraded the MySQL server from 4.0.15 to 4.1.7, without significant problems. But I continued to use PHP-4.3.9 linked against the old MySQL 4.0.15 on the webservers for some time. Everything went ok, up until the point when I tried linking PHP-4.3.9 against MySQL-4.1.7. After starting the new Apache/PHP (ldd httpd shows '/usr/local/mysql-4.1.7/lib/mysql/libmysqlclient.so.14'), I get a whole lot of Segmentation Faults in the Apache error log. Also, I have noticed in the PHP error log that a lot of MySQL queries aren't succesfully made (usually the error message is 'unable to save result set'). Lindsay [EMAIL PROTECTED] wrote: unning those queries by hand, in a mysql console yields a valid row(s), but the table header is completely messed up. Traced the problem to those queries being cached, so I ran a 'reset query cache'. Then those queries executed successfully, but I'm still getting Apache segmentation faults. Here are some details: On the MySQL server I have FreeBSD 4.10-STABLE running. MySQL 4.1.7 was compiled from ports with the command line: make DB_DIR=/db/mysql WITH_LINUXTHREADS=yes OVERWRITE_DB=no BUILD_OPTIMIZED=yes BUILD_STATIC=yes The webservers are running FreeBSD 4.10-STABLE as well, Apache/1.3.33 having PHP-4.3.9 compiled as a static module. PHP's configure line is: ./configure --prefix=/usr/local/php --enable-track-vars --enable-trans-sid --disable-path-info-check --enable-memory-limit --without-pear --with-apache=../apache_1.3.33 --with-config-file-path=/usr/local/apache/conf/ --enable-safe-mode --with-curl --with-mysql=/usr/local/mysql-4.1.7 --with-jpeg-dir=/usr/local/jpeg-6b/ --with-png-dir=/usr/local/libpng-1.2.5/ --with-ttf --with-pdflib --with-gd --enable-gd-native-ttf --with-zlib-dir=/usr Any idea what could lead to this incompatibility between PHP using the 4.1.7 MySQL library and the MySQL 4.1.7 server, incompatibility which doesn't exist between the same MySQL 4.1.7 server and PHP linked against MySQL 4.0.15 library? Thanks, bogdan -- 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: SQL Dump
I guess that all depends on the nature of your dump, doesn't it? Database administrators call any transfer of data to a separate file a dump and they come in all shapes and formats. What can you tell us about the file you received? Where did the data come from (what database system)? What tool was used to create the file? What format is the dump file? Is it ASCII or binary or unicode or Asgard or what? Is it a fixed-width dump format or are there delimiters? If so, what are the delimiters? Are text fields surrounded by single quotes, double quotes, or no quotes at all? Basically tell us anything and everything you can about the file and how it's arranged and then we will have our first clue about how to get it into MySQL. Shawn Green Database Administrator Unimin Corporation - Spruce Pine IT Guy [EMAIL PROTECTED] wrote on 12/08/2004 12:28:43 PM: Hello, I am new to mySQL. Someone gave me a dump file and i need to do an import from linux. How can i go about doing this can someone help me? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 3.23.58 to 4.1
Joshua J. Kugler wrote: Take a look at http://mysql.he.net/doc/mysql/en/Upgrade.html Hmmm... Can 4.1 mirror a 3.2 server? That might be a good deal easier. -- A. Clausen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow query issues
-Original Message-
From: Max Michaels [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 08, 2004 8:32 AM
To: [EMAIL PROTECTED]
Subject: slow query issues
Hello all,
Recently, I have been seeing some strange behavior from a particular
query on my 4.0.21 mysql server. Here is the query in question:
SELECT size_id, sum(imps) imps, sum(clicks) clicks, sum(convs) convs,
sum(imp_revenue) imp_revenue, sum(click_revenue) click_revenue,
sum(conv_revenue) conv_revenue, sum(international_imps)
international_imps, sum(adjustments) / 60 adjs,
sum(publisher_compensation) / 60 comp FROM publisher_summary WHERE ymdh
= '2004-11-01 05:00:00' AND ymdh '2004-12-01 05:00:00' AND
is_ym_advertiser=0 GROUP BY size_id;
The compound index I see that is usefull is
ymdh,is_ym_advistiser,size_id
If the range covers more then 30% of the data in the table mysql will
turn off index lookups.
Set max_seeks_keys=100 in you're my.cnf file and or ANALYZE TABLE to
recompute the cardinality of the keys. Additionally you can perform
tighter ranges on ymdh and use a script to simulate the additional
summation to make it faster.
212.561.6475 | mmichaels {at} rightmedia.com | www.rightmedia.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]
Repeated repair table ... quick takes same amount of time (long) under 4.1.7
Hi, If I remember correctly, if table is ok, then if one does a table repair, the command should return immediately. I have a table with 14 million rows, and I repeated table repair (quick) 3 times, it took the same amount of time (55 min). It is almost the same time as if I built the index from scratch. Any idea? The table seems ok for query as I checked. This is under MySQL 4.1.7, MyISAM table with full text indexes. Thanks Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server hanging once a week (Solaris 8)
%% Gleb Paharenko [EMAIL PROTECTED] writes: gp Very often similar symptoms causes various lock problems. Do you gp use InnoDB? Try log_slow_queries to find hung queries. I'm using MyISAM tables. When I wrote this application InnoDB wasn't even an option :-). I don't think it's hung queries, because even new queries that I know are very fast fail. Heck, I can't even attach to the DB except through the admin interface (mysqladmin). Unless you're saying that the hung queries are using up the processes/threads in the DB so that new queries can't get through? Thanks for the response... -- --- Paul D. Smith [EMAIL PROTECTED] HASMAT--HA Software Mthds Tools Please remain calm...I may be mad, but I am a professional. --Mad Scientist --- These are my opinions---Nortel Networks takes no responsibility for them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server hanging once a week (Solaris 8)
When the database is in a `hung` state, is the file system where the MYI, MYD and frm files accessible? Paul Smith wrote: %% Gleb Paharenko [EMAIL PROTECTED] writes: gp Very often similar symptoms causes various lock problems. Do you gp use InnoDB? Try log_slow_queries to find hung queries. I'm using MyISAM tables. When I wrote this application InnoDB wasn't even an option :-). I don't think it's hung queries, because even new queries that I know are very fast fail. Heck, I can't even attach to the DB except through the admin interface (mysqladmin). Unless you're saying that the hung queries are using up the processes/threads in the DB so that new queries can't get through? Thanks for the response... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server hanging once a week (Solaris 8)
%% Victor Pendleton [EMAIL PROTECTED] writes: vp When the database is in a `hung` state, is the file system where vp the MYI, MYD and frm files accessible? Definitely. I can log in, see the files, run the mysqladmin commands, etc. The databases live on the local disk, not a remote server (I do backups every night to a remote server). Other operations and services on that server continue to work. Thanks for the response! -- --- Paul D. Smith [EMAIL PROTECTED] HASMAT--HA Software Mthds Tools Please remain calm...I may be mad, but I am a professional. --Mad Scientist --- These are my opinions---Nortel Networks takes no responsibility for them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server hanging once a week (Solaris 8)
What state are the queries in while they are hung? What command or SQL are they executing? Paul Smith wrote: %% Victor Pendleton [EMAIL PROTECTED] writes: vp When the database is in a `hung` state, is the file system where vp the MYI, MYD and frm files accessible? Definitely. I can log in, see the files, run the mysqladmin commands, etc. The databases live on the local disk, not a remote server (I do backups every night to a remote server). Other operations and services on that server continue to work. Thanks for the response! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem using debug switch with mysqlimport
Sure enough... I just installed the latest 4.1 linux binaries, I didn't realize that the server itself had to be compiled with the debug enabled (although now that I realize that it makes complete sense). It would be nice if the documentation for mysqlimport would at least make note of this. Thanks for the response.
Re: Server hanging once a week (Solaris 8)
%% Victor Pendleton [EMAIL PROTECTED] writes: vp What state are the queries in while they are hung? What command or vp SQL are they executing? Unfortunately I lost the output of the processlist command. Whenever the server hangs it's always a crazy race to get it back up again. Next time it happens I'll be sure to stow the list somewhere before restarting the server. However, IIRC there were a mix of queries: some were Wait, some were Sleep. -- --- Paul D. Smith [EMAIL PROTECTED] HASMAT--HA Software Mthds Tools Please remain calm...I may be mad, but I am a professional. --Mad Scientist --- These are my opinions---Nortel Networks takes no responsibility for them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server hanging once a week (Solaris 8)
Understandable. The state, command and info values from `show processlist` could shed some light on what is occurring. Paul Smith wrote: %% Victor Pendleton [EMAIL PROTECTED] writes: vp What state are the queries in while they are hung? What command or vp SQL are they executing? Unfortunately I lost the output of the processlist command. Whenever the server hangs it's always a crazy race to get it back up again. Next time it happens I'll be sure to stow the list somewhere before restarting the server. However, IIRC there were a mix of queries: some were Wait, some were Sleep. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup for 24/5 system
Hello. Manual doesn't have any objections: Another benefit of using replication is that you can perform backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made. Roland Carlsson [EMAIL PROTECTED] wrote: Hi! I'm running a system that will have fairly high demands in uptime (24/5) soon. It would be ok to be down on the weekends but during workdays it must be running. Right now we stop the server during nights but it will not be possible soon so we are looking into replicate our server to a backup that we can take down whenever we need to to make the backups (or other maintaince). So, is this a good way to solve the backups or do you think that I should consider any other solutions? Thanks in advance Roland -- 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: Replication update bug/error/problem.
On Dec 8, 2004, at 9:12 AM, Jay Ess wrote: I am not using cross database updates. It is all on one database but the update uses two tables. The query update content_review_site as a,site_rating_factors as b set a.overall_rating = 77 where a.content_id=243 is a stripped down version of a bigger but i stripped down to the point of failing. The failing factor is when i use content_review_site as a,site_rating_factors as b (not a cross database but a cross table query). I wonder if you're running into this bug http://bugs.mysql.com/bug.php?id=7011 We saw it on 4.0.22 and Mac OS X. MySQL has been able to reproduce it, but only on OS X, not Linux. The query works on the master, makes it into the binlog but doesn't update and records on the slave (and doesn't cause replication failure). When the query is run manually on the slave from the command line it works fine, the records are updated as on the master. However cross table updates won't run on the slave under replication. We had to downgrade to 4.0.21. Good luck, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Query Browser
The MySQL Query Browser online documentation clearly mentions that it runs on 32-bit Windows operating systems, including Windows 95, 98 and Me. However, while installing it on WinME, I get the following error: The Operating System is not adequate for running MySQL Query Browser 1.1 Can anyone please explain? Thanks. Hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where is my my.cnf files??
hi, i compiled mysql-4.1.7 from source. actually, i want to configure a mysql cluster. from the documentation, i need to add some flag into a file called my.cnf. i searched through the files. it consisted of my-small.cnf, my-medium.cnf, my-huge.cnf. but, i cant see the file my.cnf file. where does it located pls enlighten me..thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
