Got error 1 from storage engine
Hi all! OS: Win 2000 server MySQL: mysql 4.1 I have an issue trying to restore some databases from a disk replaced, I mean, move from the old disk to the new one, I cannot launch mysqld from the old disk because boot sector was damaged, so I cannot do a mysqldump, We've replaced the disk and copied the data dir from the old one but when we want to access to our databases we get this in error in the client: ERROR 1030 (HY000): Got error 1 from storage engine looking at the error log I've seen that there was some differences between ibdata files, so I deleted them, then I got this from the error log file: InnoDB: The first specified data file .\ibdata1 did not exist: InnoDB: a new database to be created! 070726 14:32:53 InnoDB: Setting file .\ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 070726 14:32:54 InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 33 MB InnoDB: Database physically writes the file full: wait... 070726 14:32:54 InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 33 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 070726 14:32:55 InnoDB: Started; log sequence number 0 0 C:\Archivos de programa\MySQL\MySQL Server 4.1\bin\mysqld-nt: ready for connections. Version: '4.1.22-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) 070726 14:34:46 InnoDB error: Cannot find table dbnicolas/tcondomino from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 070726 14:34:46 InnoDB error: Cannot find table dbnicolas/tcondomino from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. when I do a select, check table, repair table, I always get the error from storage engine. I've also tried starting mysqld-nt with innodb_force_recovery=1 in [mysqld] section, but not luck has somebody moved databases with innodb tables coping the files and get it works ?? if so, please tell me how thanks in advance for any help!!! -- Ing Edwin Cruz LAMP Developer - Grupo SINCO Sierra Pintada #107, Bosques del Prado Sur Aguascalientes, Ags. C.P. 20127 Tel (449) 996 24 90 Cel (449) 111 29 03 http://www.gruposinco.com.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5.0.x and Delphi
Sorry for dont start my own topic... I apologize Hi! No I haven't tested with the v5 driver yet, but I found out that the problem is when I use date_format function If I dont use it the data is returned ok in my app(but with hours, minutes etc), but if i do a date_format i get bytes on each record instance of get date formated, the column type is datetime(of my table) Regards! -Mensaje original- De: Martijn Tonies [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 10 de Febrero de 2006 12:51 p.m. Para: 'Mysql ' Asunto: Re: MySQL 5.0.x and Delphi Hi, First of all: you're sending a message with an enormous quote of someone elses message, this really is not done on an e-mail list. Start your own topic. I'm having problems upgrading my database from 4.0.x to 5.0.x, specially with date fields For example with 4.0.x I do: select startDate from TEvent and in my application it is displayed ok(managed with controls), but the same code, same odbc, same control in delphi, etc with mysql 5.0.x it displays: byte instance of the date storaged in my table What datatype is being returned for this column if you try and use it in Delphi with the ODBC driver? Did you try the v5 driver already? http://dev.mysql.com/downloads/connector/odbc/5.0.html My versions are: Delphi 5 MyODBC-3.51.11-2-win MySQL 5.0.18 MySQL 4.0.26 Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.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]
MySQL 5.0.x and Delphi
Hi all!
I'm having problems upgrading my database from 4.0.x to 5.0.x, specially
with date fields
For example with 4.0.x I do: select startDate from TEvent and in my
application it is displayed ok(managed with controls), but the same code,
same odbc, same control in delphi, etc with mysql 5.0.x it displays: byte
instance of the date storaged in my table
My versions are:
Delphi 5
MyODBC-3.51.11-2-win
MySQL 5.0.18
MySQL 4.0.26
Regards!
-Mensaje original-
De: Peter Brawley [mailto:[EMAIL PROTECTED]
Enviado el: Viernes, 10 de Febrero de 2006 11:01 a.m.
Para: Patrick Duda
CC: mysql@lists.mysql.com
Asunto: Re: question about locking
Patrick,
I thought, that if I turned off autocommit I would enter a transaction.
Then, by using the select...for update, that I would take and hole a
lock on the table.
With a transaction-capable table, eg InnoDB, otherwise neither setting
autocommit off nor adding FOR UPDATE has any effect.
PB
-
Patrick Duda wrote:
Hi,
I am running MySQL 4.0.1 with j/connector 3.1 and I am having problems
trying to figure out why I am not getting the results I am expecting.
I have a table that is used for generating primary keys. It only has
one item, an int that is incremented each time a key is needed. This
is not my code or my design so using something like auto_incrament is
not an option.
The code runs under a container and our desire is to have several
different containers running at the same time, all accessing the same
database. Each container is independent so the controls need to be on
the database side.
The solution also needs to be portable to other databases so I am
trying to stay with standard JDBC or SQL options.
The code for generating a new key is this:
try {
c = DatabaseSetup.getDBConnection();
c.setAutoCommit(false);
statement =
c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = statement.executeQuery(select transfer_id from transferid for
update);
if (!rs.next()) {
nextTransferId = nextTransferId + 1;
StringBuffer query = new StringBuffer();
query.append(insert into transferid(transfer_id)
values ();
query.append(nextTransferId);
query.append());
tempStatement = c.createStatement();
// Now Update the old value with new value
tempStatement.executeUpdate(query.toString());
} else {
rs.previous();
while( rs != null rs.next() ) {
nextTransferId = rs.getInt(1);
// Get the transfer Id and increment it instead of
using
// Db Specific sequence
nextTransferId = nextTransferId + 1;
// Now Update the old value with new value
tempStatement = c.createStatement();
tempStatement.executeUpdate(update transferid set +
transfer_id= + nextTransferId);
}
}
} catch (SQLException e) {
if( c != null )
{
try
{
c.rollback();
c.setAutoCommit(true);
}
catch( SQLException ex )
{
}
}
throw new DBException(i18n.getMessage(dbInsertErr),
e);
} finally {
try {
c.commit();
c.setAutoCommit(true);
if (statement != null) {
statement.close();
}
if (tempStatement != null) {
tempStatement.close();
}
if (rs != null) {
rs.close();
}
if (c != null) {
DatabaseSetup.returnDBConnection(c);
}
} catch (SQLException sql) {
logger.warn(i18n.getMessage(dbStatementErr), sql);
}
}
return nextTransferId;
}
I thought, that if I turned off autocommit I would enter a
transaction. Then, by using the select...for update, that I would
take and hole a lock on the table. That no other transaction would be
able to read the table until I released the lock. However, this is
not what I am seeing when I run some tests. I start up a number of
containers and then fire off a bunch of jobs to each. Each of these
jobs will hit the above code. The problem is that every so often I
see the following error message.
Error inserting records into database [Caused by: Duplicate entry
'131' for key 1]
What am I doing wrong?
How am I suppose to be doing this via JDBC? I know it should work...
Thanks
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.5/256 - Release Date: 2/10/2006
--
MySQL General Mailing List
For list archives:
RE: Dictionary
Have you had a look for aspell check? For example in eventum: http://lists.mysql.com/eventum-users/2683 This may be usefull... For example in php there are some function for aspell http://mx2.php.net/aspell Regards! Edwin. -Mensaje original- De: Scott Hamm [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 01 de Febrero de 2006 11:01 a.m. Para: 'Mysql ' Asunto: Dictionary I've been trying to google to no avail for English dictionary (with definitions) in any format that I can download and import into MySQL. Do anyone know where I can find it? Thanks in advance, Scott -- Power to people, Linux is here. -- 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: Show Description options??
If you are using mysql 5.x you must be able to retrieve that you want using information_schema database Look: mysql select version(); +-+ | version() | +-+ | 5.0.16-standard | +-+ 1 row in set (0.00 sec) mysql use information_schema; Database changed mysql SELECT column_name,data_type FROM COLUMNS C where table_schema = 'erp' and table_name='TAnticipo'; +-+---+ | column_name | data_type | +-+---+ | idTAnticipo | int | | TEmpleado_idTEmpleado | int | | TViaje_idTViaje | int | | TTipoGasto_idTTipoGasto | int | | no_vale | char | | fecha | date | | tipo| char | | cantidad| tinyint | | importe | double| | iva | double| | observacion | char | | activo | char | +-+---+ 12 rows in set (0.01 sec) mysql Regards! ++ | ISC Edwin Cruz [EMAIL PROTECTED]| | Desk: 52 (449) 910 30 90 x3054| | MX Mobile: 52 (449) 111 29 03 | | Skype: e-cruz | ++ -Mensaje original- De: Mike OK [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 19 de Enero de 2006 10:34 a.m. Para: mysql@lists.mysql.com Asunto: Show Description options?? Hi I was looking for a command that will list the names of my columns only. I have investigated show columns but there seems to be no way to return just the names. Any suggestions?? Thanks Mike -- 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: Alter table
ALTER TABLE db.table DROP INDEX nameOfTheIndex; If you don't know nameOfTheIndex use: Show create table db.table Regards! -Mensaje original- De: Yemi Obembe [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 02 de Enero de 2006 10:03 a.m. Para: mysql@lists.mysql.com Asunto: Alter table one of my table columns is unique. i mean cant av duplicate contents. How do i remove this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I'm new to mySQL
MySQL AB has a very good GUI tools Have a look in: http://www.mysql.com/products/tools/ You should try to use them, phpmyadmin also is good. For administration mysql-administrator To execute queries or create functions, procedures, etc mysql-querybrowser To migrate data from another RMDBS so use mysql-mitration-toolkit Those tools work on MAC Regards! Edwin. -Mensaje original- De: James Lumb [mailto:[EMAIL PROTECTED] Enviado el: Sábado, 24 de Diciembre de 2005 02:06 p.m. Para: mysql@lists.mysql.com Asunto: I'm new to mySQL Hi, I am new to mySQL and have mac OS X. Please could any other Mac users or anyone for that matter tell me the best way of connecting to a mySQL database? Should I use terminal or a program like PHPmyAdmin? Cheers, James _ Are you using the latest version of MSN Messenger? Download MSN Messenger 7.5 today! http://messenger.msn.co.uk -- 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]
Mysqldump segmentation fault(core dumped)
auto_increment, `TPais_idTPais` tinyint(3) unsigned NOT NULL, `estado` char(100) default NULL, `activo` char(1) default 'A', PRIMARY KEY (`idTEstado`), KEY `TEstado_FKIndex1` (`TPais_idTPais`), CONSTRAINT `TEstado_ibfk_1` FOREIGN KEY (`TPais_idTPais`) REFERENCES `TPais` (`idTPais`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `TEstado` -- /*!4 ALTER TABLE `TEstado` DISABLE KEYS */; LOCK TABLES `TEstado` WRITE; UNLOCK TABLES; /*!4 ALTER TABLE `TEstado` ENABLE KEYS */; -- -- Table structure for table `TPais` -- DROP TABLE IF EXISTS `TPais`; CREATE TABLE `TPais` ( `idTPais` tinyint(3) unsigned NOT NULL auto_increment, `pais` char(100) default NULL, `activo` char(1) default 'A', PRIMARY KEY (`idTPais`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `TPais` -- /*!4 ALTER TABLE `TPais` DISABLE KEYS */; LOCK TABLES `TPais` WRITE; UNLOCK TABLES; /*!4 ALTER TABLE `TPais` ENABLE KEYS */; -- -- Table structure for table `TRuta` -- DROP TABLE IF EXISTS `TRuta`; CREATE TABLE `TRuta` ( `idTRuta` int(10) unsigned NOT NULL auto_increment, `TCiudad_destino` int(10) unsigned NOT NULL, `TCiudad_origen` int(10) unsigned NOT NULL, `nombre` char(100) default NULL, `km` double(9,2) default NULL, PRIMARY KEY (`idTRuta`), KEY `TRuta_FKIndex1` (`TCiudad_origen`), KEY `TRuta_FKIndex2` (`TCiudad_destino`), CONSTRAINT `TRuta_ibfk_1` FOREIGN KEY (`TCiudad_origen`) REFERENCES `TCiudad` (`idTCiudad`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `TRuta_ibfk_2` FOREIGN KEY (`TCiudad_destino`) REFERENCES `TCiudad` (`idTCiudad`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `TRuta` -- /*!4 ALTER TABLE `TRuta` DISABLE KEYS */; LOCK TABLES `TRuta` WRITE; UNLOCK TABLES; /*!4 ALTER TABLE `TRuta` ENABLE KEYS */; -- -- Table structure for table `VRuta` -- DROP TABLE IF EXISTS `VRuta`; CREATE ALGORITHM=UNDEFINED [EMAIL PROTECTED] SQL SECURITY DEFINER VIEW `VRuta` AS select `a`.`idTRuta` AS `idTRuta`,`a`.`nombre` AS `nombre`,`a`.`km` AS `km`,concat(`c`.`estado`,_utf8', ',`b`.`ciudad`) AS `origen`,concat(`e`.`estado`,_utf8', ',`d`.`ciudad`) AS `Destino` from `TRuta` `a` join `TCiudad` `b`) join `TEstado` `c`) join `TCiudad` `d`) join `TEstado` `e`) where ((`a`.`TCiudad_origen` = `b`.`idTCiudad`) and (`b`.`TEstado_idTEstado` = `c`.`idTEstado`) and (`a`.`TCiudad_destino` = `d`.`idTCiudad`) and (`d`.`TEstado_idTEstado` = `e`.`idTEstado`)); Segmentation fault (core dumped) apps# uname -a FreeBSD apps.medel.com.mx 5.4-RELEASE FreeBSD 5.4-RELEASE #0: Sun May 8 10:21:06 UTC 2005 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/GENERIC i386 apps# tail -f apps.medel.com.mx.err Table status: Opened tables: 12 Open tables:6 Open files:12 Open streams: 0 Alarm status: Active alarms: 0 Max used alarms: 0 Next alarm time: 0 I've had a look at bugs.mysql.com but I wasn't able to find anything similar, What could be the problem? Regards! Tanks in advance. ++ | ISC Edwin Cruz [EMAIL PROTECTED]| | IT Manager | | Transportes Medel Rogero SA de CV | | Desk: 01 (449) 910 30 90 x3054| | MX Mobile: 01 (449) 111 29 03 | | Skype: softr8 | ++
RE: Newbie Question on Update
I guess that the problem is the reserved word table Try it: Update `table` Set column5=number1 where column 1 = number2 and column1 = number3 But the sintax is ok Regards! -Mensaje original- De: Kraer, Joseph [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 30 de Noviembre de 2005 10:59 a.m. Para: mysql@lists.mysql.com Asunto: Newbie Question on Update I am trying to update a couple of rows in a table by doing the following: update table set column5 = number1 where column 1 = number2 and column1 = number3 but it is not working. I tried listing the conditions separated by commas (where column 1 = number2, column1 = number3) and also didn't work. What am I doing wrong? Thanks in advance, Joseph Tito Kraer Business Systems Analyst Taylor, Bean Whitaker Mortgage Corp -- 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: Two MySQL databases on different computers
If you have MySQL 5.0.3 then you could use this storage engine: http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html It is only a recomendation. Regards! -Mensaje original- De: Peter Lauri [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 25 de Noviembre de 2005 03:44 a.m. Para: mysql@lists.mysql.com Asunto: Two MySQL databases on different computers Hi, I have two databases. Database A is located on a server that I run my web hosting from. The other database B is located on a computer with a fixed IP. How can I configure database B so I can access database B from my web server? From my A system I would like to be able to do INSERT, SELECT and UPDATE queries on the database B. Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dotproject
Have somebody tryed to install dotproject 2.x with MySQL 5.0.x??( The stable release) ++ | ISC Edwin Cruz [EMAIL PROTECTED]| | IT Manager | | Transportes Medel Rogero SA de CV | | Desk: 01 (449) 910 30 90 x3054| | MX Mobile: 01 (449) 111 29 03 | | Skype: softr8 | ++
RE: dotproject
I've tryed and I only get a lot of sql errors, looking into the source code I sow that the problem is with JOIN clause. Any ideas abaut how to install it? -Mensaje original- De: ISC Edwin Cruz [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 23 de Noviembre de 2005 05:06 p.m. Para: mysql@lists.mysql.com Asunto: dotproject Have somebody tryed to install dotproject 2.x with MySQL 5.0.x??( The stable release) ++ | ISC Edwin Cruz [EMAIL PROTECTED]| | IT Manager | | Transportes Medel Rogero SA de CV | | Desk: 01 (449) 910 30 90 x3054| | MX Mobile: 01 (449) 111 29 03 | | Skype: softr8 | ++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to SUM every row for SubTotal?
mysql select * from table5; ++--+ | code | qty | ++--+ |100 | 30 | |200 | 20 | |300 | 25 | ++--+ 3 rows in set (0.00 sec) mysql set @a=0; Query OK, 0 rows affected (0.05 sec) mysql select code,qty,@a:[EMAIL PROTECTED] as subtotal from table5; ++--++ | code | qty | subtotal | ++--++ |100 | 30 | 30 | |200 | 20 | 50 | |300 | 25 | 75 | ++--++ 3 rows in set (0.00 sec) mysql I hope that it helps you Regards! -Mensaje original- De: The Nice Spider [mailto:[EMAIL PROTECTED] Enviado el: Martes, 22 de Noviembre de 2005 05:27 p.m. Para: mysql@lists.mysql.com Asunto: How to SUM every row for SubTotal? If I have table like: CODE QTY 100 30 200 20 300 25 and I want a result like: CODE QTY SUBTOTAL 100 30 30 200 20 50 (this come from 30+20) 300 25 75 (this come from 50+25) How to write query like that? - Yahoo! FareChase - Search multiple travel sites in one click. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: dump to more than 1 file
Mysqldump has where condition, you may have to segment your data and dump it in diferents files Mysqldump --where='date between \'dateStart\' and \'dateFinish\' ' (for example) See full documentation at: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Regards!! Edwin Cruz -Mensaje original- De: Tom Brown [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 21 de Noviembre de 2005 09:57 a.m. Para: mysql@lists.mysql.com Asunto: dump to more than 1 file is it possible to do a mysql dump to more than 1 file? We will shortly be needing to dump a db that will be in excess of 50gb so will encounter file size issues This is on 4.1.x and rhel 4 thanks -- 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: Runing MySQL on boot
If you´ve installed mysql from ports the startup sript is under /usr/local/etc/rc.d/ Change its permissions to be extecutable I hope that this help you Regards! ++ | ISC Edwin Cruz García | | IT Manager | | Transportes Medel Rogero SA de CV | | Desk: 01 (449) 910 30 90 x3054| | Movil: 01 (449) 111 29 03 | | email: [EMAIL PROTECTED] | ++ -Mensaje original- De: Andrew Kuebler [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 18 de Noviembre de 2005 01:51 p.m. Para: mysql@lists.mysql.com Asunto: Runing MySQL on boot I'm running FreeBSD and MySQL version 4.1. How do I get FreeBSD to load MySQL on boot? I don't see a script file that came with the installation. Thank you. Andrew -- 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: Backward-compatible authentication in v5.0?
Hi! It is a common error, have a look here: http://dev.mysql.com/doc/refman/5.0/en/old-client.html I have had the same error. ++ | ISC Edwin Cruz García | | IT Manager | | Transportes Medel Rogero SA de CV | | Desk: 01 (449) 910 30 90 x3054| | Movil: 01 (449) 111 29 03 | | email: [EMAIL PROTECTED] | ++ -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 16 de Noviembre de 2005 07:40 p.m. Para: mysql@lists.mysql.com Asunto: Backward-compatible authentication in v5.0? I'm trying to upgrade MySQL from v4.x to v5.0 under an existing Tcl-based application that uses MySQLTcl and I'm getting: mysqlconnect/db server: Client does not support authentication protocol requested by server; consider upgrading MySQL client Is there something I can do to MySQL v5 server to make it compatible with older clients? Chris -- 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: Sum entire group listing
SELECT products.product, products.price, count( log.product ) AS 'Count', ROUND(price*count(log.product), 2) AS 'Total' FROM products LEFT JOIN log ON products.product= log.product GROUP BY product Union SELECT 'Total', '', count( log.product ) AS 'Count', ROUND(price*count(log.product), 2) AS 'Total' FROM products LEFT JOIN log ON products.product= log.product -Mensaje original- De: Noel Stratton [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 10 de Noviembre de 2005 04:36 p.m. Para: mysql@lists.mysql.com Asunto: Sum entire group listing I am running this query below: SELECT products.product, products.price, count( log.product ) AS 'Count', ROUND(price*count(log.product), 2) AS 'Total' FROM products LEFT JOIN log ON products.product= log.product GROUP BY product The query above submits this results: product price Total Sold Total Amount Owed ATM Card 3.002 6.00 Audio Response 3.000 0.00 Check Card 5.001 5.00 Courtesy Pay 5.002 10.00 Draft with Direct Deposit5.000 0.00 Draft without Direct Deposit 3.000 0.00 E-statement 5.002 10.00 Gap 20.00 0 0.00 MBI 10.00 0 0.00 Membersonline5.000 0.00 New Account 5.001 5.00 New Loan 5.000 0.00 New MemBear Account5.000 0.00 The results above is exactly what I want. However, I would like to add one more thing that I can not figure out. I would like to sum all calculations created out of the Total Amount Owed field that was created. The answer should be 36.00. Any suggestions on how to accomplish this? Thank You, Thank You, Noel Stratton Computer Specialist Members 1st Credit Union -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to write this query?
Try it: select distinct a.* from order a, item b, item_status c, status d where a.order_id = b.order_id and b.item_id=c.item_id and c.item_status_id = d.item_status_id where d.status = 'completed' It isn´t tunned but I think that it works for that you want -Mensaje original- De: Jerry Swanson [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 10 de Noviembre de 2005 06:23 a.m. Para: mysql@lists.mysql.com Asunto: How to write this query? How to write the query? table1: order (order can have more than 1 item) table2: item (has order_id). table3: item_status (has item_status_id and item_id) table4: status (has item_status_id). Status can be 'complete', 'pending'. I need to get all orders that have ONLY completed items. Examples: if order has one item and it is completed, I need this order. If order has 2 items and both completed, I need this order. If order has 2 items, 1 is completed and 1 is not completed, I don't need this order. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: report from two tables
Try it: SELECT o.Trans_No, o.SoldBy, 'NA', sm.LName, sm.FName, sm.Salesperson_No FROM v_orders as o, v_salesmen as sm WHERE o.Order_Date = '. $report_starts.' AND sm.Salesperson_No = o.SoldBy UNION SELECT o.Trans_No, o.SoldBy, 'A', sm.LName, sm.FName, sm.Salesperson_No FROM orders as o, v_salesmen as sm WHERE o.Order_Date = '. $report_starts.' AND o.SoldBy = sm.User_ID ORDER BY Trans_No DESC LIMIT 100 It works for me. Regards! -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 03 de Noviembre de 2005 03:55 p.m. Para: MySQL List Asunto: report from two tables Hi, I have two table for orders and I have to create a Report with list of orders from both tables for specific day. I made Reports for each table and they work just fine, but don't know how to put them together: Query for Order Table 1: SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.User_ID FROM v_orders as o, v_salesmen as sm WHERE o.Order_Date = '. $report_starts.' AND o.SoldBy = sm.User_ID ORDER BY sm.LName ASC where Trans_No is transaction no., SoldBy salesperson's no in table orders, LName, FName and User_ID info from (third) table for salespersons (User_ID is salesperson's id - primary key). Query for Order Table 2: SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.Salesperson_No FROM orders as o, v_salesmen as sm WHERE Order_Date = '. $report_starts.' AND o.SoldBy = sm.Salesperson_No ORDER BY sm.LName ASC where Salesperson_No is info from salespersons table - in this case FK. I tried with this: ( SELECT o.Trans_No, o.SoldBy, 'NA', sm.LName, sm.FName, sm.Salesperson_No FROM v_orders as o, v_salesmen as sm WHERE o.Order_Date = '. $report_starts.' AND sm.Salesperson_No = o.SoldBy ) UNION ( SELECT o.Trans_No, o.SoldBy, 'A', sm.LName, sm.FName, sm.Salesperson_No FROM orders as o, v_salesmen as sm WHERE o.Order_Date = '. $report_starts.' AND o.SoldBy = sm.User_ID ) ORDER BY Trans_No DESC LIMIT 100 I'm NOT getting any error but either any result (entry)? What am I doing wrong? Thanks for any help. -afan -- 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]
Migration problem
'; ++-+---+--+---+--+-+--+- --+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+- --+-+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL| NULL | 37851 | Using where | | 1 | SIMPLE | a | ALL | NULL | NULL | NULL| NULL | 54058 | Using where | ++-+---+--+---+--+-+--+- --+-+ 2 rows in set (0.04 sec) I´ve tryed with FORCE KEY, USING,etc. But those don´t work. Should I try changing from InnoDB storage engine to MyISAM ?? mysql show variables like 'Inno%'; +-++ | Variable_name | Value | +-++ | innodb_additional_mem_pool_size | 1048576| | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608| | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500| | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir|| | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF| | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method || | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF| | innodb_log_arch_dir || | innodb_log_archive | OFF| | innodb_log_buffer_size | 1048576| | innodb_log_file_size| 5242880| | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300| | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-++ 33 rows in set (0.00 sec) Tanks a lot for any suggestion ++ | ISC Edwin Cruz García | | Gerente de T.I.| | ++ --\| | || | | \| | | Transportes medel | | | +---+ | | +---===+--+___ï | | (ô)(ô) (ô)(ô) (ô)***(ô) ¨ | || | Tel. 01 (449) 910 30 90 x3054| | Móvil: 01 (449) 111 29 03 | | email: [EMAIL PROTECTED] | ++
