Re: Tool for shifting tables from Mysql to Postgresql
2011/3/1 Adarsh Sharma adarsh.sha...@orkash.com: Dear all, I want to convert some tables from Mysql database to Postgresql Database in Linux Systems ( Ubuntu-10.4, CentOS ). [...] invalid byte sequence for encoding UTF8: 0xe3ba27 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence for encoding UTF8: 0xee6c65 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence This is not a MySQL-specific error. You are using a character set encoding as if it were another. Please, check documentation for the options to export and import database dumps such as: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_default-character-set -- Jaime Crespo MySQL Java Instructor Software Developer Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql to Postgresql
2011/2/22 Adarsh Sharma adarsh.sha...@orkash.com: Dear all, Today I need to back up a mysql database and restore in Postgresql database but I don't know how to achieve this accurately. Have a look at: mysqldump --compatible=postgresql command: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_compatible Anyway, most of the times you will need a more manual migration, with human intervention (custom scripts) and migrating the data through something like CSV (SELECT... INTO OUTFILE). -- Jaime Crespo MySQL Java Instructor Software Developer Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: contact gives empty result
2011/2/22 Joerg Bruehe joerg.bru...@oracle.com: You have not understood the concept of NULL in SQL: NULL does not mean empty, it means unknown. [...] Apart form fully agreeing with Joerg, just a tip: you can use the the IFNULL() operand as a workaround: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull Anyway, you should get rid of misplaced NULL fields, as they also affect performance. -- Jaime Crespo MySQL Java Instructor Software Developer Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
2011/1/21 Jerry Schwartz je...@gii.co.jp:
-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Friday, January 21, 2011 12:47 PM
To: mysql@lists.mysql.com
Subject: Re: CURRENT insert ID
Ok, you must have your own reasons to do that.
The fact is: You can´t set the auto_incremente value field to another field
in the same table and record even in a trigger.
So, the best way is a second update.
[JS] That's what I'm trying to do now, but I'm using MS Access through an ODBC
connection and I haven't figured out how to retrieve last_insert_id.
I will tell you a secret. But shh. Do not tell anyone:
--8--8--8--8--8--8--8--8--8--8--8--
mysql create table mytable(id int auto_increment primary key, name
varchar(255));
Query OK, 0 rows affected (0.07 sec)
mysql insert into mytable (name) values ('test data');
Query OK, 1 row affected (0.00 sec)
mysql select id from mytable where id is null; -- OMG!!!
++
| id |
++
| 1 |
++
1 row in set (0.00 sec)
--8--8--8--8--8--8--8--8--8--8--8--
--
Jaime Crespo
MySQL Java Instructor
Software Developer
Warp Networks
http://warp.es
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Loading Unicode Data to mySQL
2011/1/20 swagat.le...@accenture.com: Hi, I lack on knowledge about Informatica software. But if you are really sure that the problem is not on source data and not on backend configuration, then it is just in the middle. :-) I will be more specific (at least, as far as I can be). In a MySQL, a charset is negotiated on connection. This character set can be different from the server's default. For example, PHP connector uses latin instead of utf8 by default unless specifically configured. Check your software/ODBC settings (if they are available) http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-parameters.html Charsets can be a mess if you have several layers and only one of them fails, but if you understand fully all of MySQL parameters is not so difficult. -- Jaime Crespo MySQL Java Instructor Software Developer Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to unstall MySQL 5.1 on Ubuntu 8.04 LTS
2010/8/23 Manasi Save manasi.s...@artificialmachines.com: Dear All, I need to install MySQL 5.1.42 on ubuntu. MySQL site does not have installer packages for ubuntu(.deb). If I need to use tar.gz is there any document available which will help me do this specific changes. Follow the official guide: http://dev.mysql.com/doc/refman/5.1/en/installing-binary.html MySQL works flawlessly on Ubuntu. -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: RHEL Auto Start / stop mysql???
2010/8/12 Nunzio Daveri nunziodav...@yahoo.com: Hi Guifre, thanks for answering. I already have mysql installed and works just fine, but I did untar and then go to folder and run. I used what is called mysql no-install so no yum, rpm etc.. No files in /etc/init.d and no startup or services script since this is using the no-install version. Nunzio: You will find an example init.d script on $MYSQL_INSTAL_DIR/support-files/mysql.server Follow the instructions corresponding to your distribution to setup it. Generic instructions can be found here: http://dev.mysql.com/doc/refman/5.1/en/automatic-start.html -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Replication
2010/6/30 Tompkins Neil neil.tompk...@googlemail.com: Hi Just one other question. With regards the replication in MySQL 5.1 - does it it replication the whole row of data or just the field in which the data has been changed for the current record ? MySQL 5.1 supports two replication formats: row and statement-based. Please, have a look at the manual page: http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Replication
2010/6/24 Tompkins Neil neil.tompk...@googlemail.com: HI, We have set-up MySQL Community Server 5.1.46 with Master to Slave replication and everything appears to be working correctly, however I have a couple of questions which I hope somebody can shed some light. (1) When the network connection goes down between the master and slave servers, it would appear that the updates are only sent from the master to the slave, but not from the slave to the master when the connect is re-established. Is this correct ? In a master-slave architecture, updates are always from the master to the slave. If you want two-way replication, that is a master-master setup, but not recommended in general unless in a very controlled environment. (2) What is the situation regarding conflicts if the same master and slave record is edited at the same time ? Fail :-) Whenever there is a conflict in the replication process, it stops. You have to solve the issues manually and then start the replication again. This usually occurs due to the synchronous/distributed nature of the replication. -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Replication
2010/6/24 Tompkins Neil neil.tompk...@googlemail.com: Hi Regarding two-way replication what do you mean by very controlled environment ? What things do I need to consider ? Control at application level that you are not going to insert/update/delete the same record on the two servers. Even if MySQL gives some support to handle this (auto-increment-offset, replicate-ignore-table), you should mostly handle it at business logic (application server) layer, not in the MySQL database. Alternatively, as Johan pointed, have a look at the semi-synchronous replication. -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Missing database file names
2010/6/4 Jesse F. Hughes je...@phiwumbda.org: Is it possible to build a .MYI file from scratch? I have found a file that I believe is recorded.MYD. I have the .frm file as well, but I don't think that recorded.MYI survived the file system event. Yes, the mysql utility myisamchk and the REPAIR command should be able to regenerate an MYI file from a .MYD and .frm files. After all, MDI file only contains the disk version of the indexes. Beware of possible issues if using different server version, though. -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
2010/1/25 Yang Zhang yanghates...@gmail.com: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. Non-primary key works for me, as documented: --8 mysql create table test_ai (i int PRIMARY KEY, c int auto_increment, index(c)); Query OK, 0 rows affected (0,07 sec) mysql desc test_ai; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | i | int(11) | NO | PRI | NULL|| | c | int(11) | NO | MUL | NULL| auto_increment | +---+-+--+-+-++ 2 rows in set (0,00 sec) mysql insert into test_ai (i) values (100), (200); Query OK, 2 rows affected (0,00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from test_ai; +-+---+ | i | c | +-+---+ | 100 | 1 | | 200 | 2 | +-+---+ 2 rows in set (0,00 sec) --8 Regards, -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimization
2010/1/25 John G. Heim jh...@math.wisc.edu: I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is running the latest mysql-server from debian lenny (5.0.1). I have databases for drupal, moodle, spamassassin, horde3, and a small database for departmental stuff. The problem is that inserts/updates are sometimes very slow, on the order of a minute. I am hoping somebody can sspot something wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced below that: Are your databases using MyISAM or InnoDB? * If MyISAM, you could be suffering contention problems on writes because of full table locks. No easy solution but engine change or database sharding. Also key_buffer, (and the other buffers) coud be too small for 16GB of RAM. Are you really using more thant 10% of it? You could also disable other engines if unused. * If InnoDB, you have not set innodb_buffer_pool_size nor log size. You could increase the pool to 50% of ram available. Those are very general suggestions. It depends a lot on your hardware (slow storage?), other apps installed on the same machine or the load of the server, among others. There also exists a tool to get introduced into MySQL server variables tuning: Tuning primer - https://launchpad.net/mysql-tuning-primer It is also a very general tool, but it could be helpful for a starting point. Regards, -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Table level locking when inserting auto-increment PK to InnoDB
2009/12/23 Ryan Chan ryanchan...@gmail.com: Hey. Back to few years ago, InnoDB require table level locking when inserting auto-increment PK to the table, and Heikki said there will be a fix. Is this problem still exist now? If you refer to this bug: http://bugs.mysql.com/bug.php?id=16979 there is a manual page discussing the changes done for the 5.1 version: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql load balancing
El día 22 de diciembre de 2009 13:44, Miguel Angel Nieto cor...@miguelangelnieto.net escribió: It depends a lot on how you plan to coordinate the db servers (sharding, replication, ndb), the kind of applications you are going to deploy and how much scability you need. Thank you. I have read about LVS and keepalived but I can't see the difference between them. Are they the same thing? I want the load balancing for my replicated servers. I suppose that LVS can't distinguish between inserts and selects (to send queries to the master o slave server). I do not know about keepalived, but it seems to provide the same service that heartbeat does: user-level awareness of the failure of a machine or a service. LVM, however, does kernel module, ip-level, load balancing and automatic failure clustering. The big confusion with those apps is that most of them are extensible and combinable to achieve the same goal. About the read/write balancing (which is a good question, and that is why I asked what you were intending to do on the other end), I have seen it done **only at application level**, not transparently, because of the problems derived from asynchronous replication (lag between master writes and slaves see the data). I am sorry I cannot help you, but please, share here if you found something useful AND with good performance. Merry Xmas! -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql load balancing
2009/12/21 Miguel Angel Nieto cor...@miguelangelnieto.net: Hi, I am searching fot a Mysql Load Balacing tool. I read about mysql proxy, sqlrelay, haproxy... What do you prefer? Hi, The solutions I have heard most from our customers (in production) are not mysql-specific: 1) Simple, not load-aware *DNS balancing* for simple applications without fault-tolerance: http://en.wikipedia.org/wiki/Round_robin_DNS 2) More complex and customizable *Linux Virtual Server*, with integrated heartbeat and session aware: http://www.linuxvirtualserver.org/ It depends a lot on how you plan to coordinate the db servers (sharding, replication, ndb), the kind of applications you are going to deploy and how much scability you need. I hope that helps. Regards, -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: different 5.0 and 5.1 about long_query_time
2009/12/11 Yang Wang yw...@lfm-agile.com.hk: Hi,All The version of 5.1 include microtime slow query patch? As of MySQL 5.1.21, the minimum value is 0, and a resolution of microseconds is supported when logging to a file. http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html Regards, -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How normal mysql server 5.1 uses multiple cores
2009/11/24 Johan De Meersman vegiv...@tuxera.be: If you are wondering about parallel query execution (that is, splitting a single query over multiple cores for faster execution), that is currently not supported by MySQL. [offtopic] Probably is something stupid, but could that be done with ndb cluster on a single host? Anyway, I suppose performance loses on distributed joins and so on would outcome multiple-core benefits. And for most queries, the bottleneck is usually on disk access, not processor. Has anybody done any serious testing on this? -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: OPTIMIZE TABLE
Not an expert, but: 2009/11/14 Krishna Chandra Prajapati prajapat...@gmail.com: I would like to know how optimize table work internally. A table contains 200 millions records. Whether query performance will be faster after deletion of 30 million records from this table or not. Use of OPTIMIZE TABLE varies from engine to engine (MyISAM vs. InnoDB vs. ARCHIVE) but, generally, it will perform the following actions: * Regain data space, defragmenting deleted rows * Repairing indexes * Updating statistics that enables better optimizer execution plans (similar to ANALYZE TABLE) Deleting 10% of rows is the paradigmatic case where optimize table should be run to improve performance. However, these optimizations are engine-dependant, as for example, InnoDB without the 'multiples tablespaces' option will not release free space, for performance reasons. An important drawback that you must consider is that executing OPTIMIZE TABLE is very process-heavy and that it will block your entire table even for read (unless PARTITONS are used). It could take some time if we are talking about millions of records. If you need High Availability, you will have to use replication or any other way of load balancing while performing administrative tasks. If you could have a pre-production host to test these kind of operations it would be great! Each application is a world on its own. You can read more info here: url:http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Find neighboring rows
2009/11/8 Andrew Tran aochi...@gmail.com: I want to find the first occurrence of Andrew, but also the neighboring rows (like the two rows above the first Andrew and the There is a conceptual issue with what you are trying to accomplish. What are the rows above and below? According to relational theory, entities are grouped into sets, and thus, without any implicit order. So, if you want them by alphabetic order, it is ok, if you expect them in the order you inserted them, you should explicit this through an additional timestamp or auto_increment column, as records are not guaranteed to be returned in that order. Let's suppose that you want them on natural order, your query could be done with something like this: SET @name = 'whatever'; SELECT n FROM names WHERE n = @name ORDER BY n ASC LIMIT 3 ) UNION ALL ( SELECT n FROM names WHERE n @name ORDER BY n DESC LIMIT 2 ) ORDER BY n; Row has been named n; table: names. Unexpected results if @name does not exist. If you expect a lot of records, expect also bad performance with this query: add an index to speed up the orderings; also several performance optimizations could be applied depending on your case. -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Choose certain columns in mysqldump?
2009/10/29 Michael Dykman mdyk...@gmail.com: mysqldump is not really a data manipulation tool.. as the name implies, it is a dumper. What you are trying to accomlish can be done rather elegantly via the SELECT .. INTO OUTFILE syntax http://dev.mysql.com/doc/refman/5.1/en/select.html and then loaded into your new structure via LOAD INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html Yes, in fact, you can still do it from the command line with mysql command line client: mysql -urxxxt -pxxx db_name -e SELECT [any, column, you, want] INTO OUTFILE '/var/www/folder/table_name.txt' FROM table_name WHERE [any, filter, you, want] ORDER BY [any, order, you, want] -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using IF in a query to set a variable then sort on said variable
2009/10/26 Jeff j...@platinumsynergy.com: Perhaps case is the way to go, I'll look into that this morning. Unfortunately there are three groupings. So my IF or CASE needs to check for example: if timezone = 3,5,6,7 then 1 if timezone = 1,2,4 then 2 if timezone = 8,9 then 3 Yes, Case function is the way to go. Anyway, check for performance issues: in that case, precalculating and storing an aditional field would be the best way (it could be done with a trigger, for example). -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using IF in a query to set a variable then sort on said variable
2009/10/23 Jeff j...@platinumsynergy.com: I currently have a query like so: SELECT p.fldId, p.fldFName, p.fldLName, p.fldEmail, p.fldPhone, p.resellerId, d.timezoneId, d.bestTime, d.lastDate, d.lastTime, d.lastConnected FROM tblProspects as p LEFT JOIN tblProspectsDetails as d ON d.prospectId = p.fldId WHERE p.uId = 46437 ORDER BY d.lastDate ASC, d.timezoneId DESC You do not need to set a variable, just 'ORDER BY FIELD(timezoneId, ..., ..., ...)' : url:http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field You could also use the IF() or CASE functions to achieve similar results. -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MYSQL slow query log in table.
2009/10/7 Brown, Charles cbr...@bmi.com: Hello All. I would like to implement MYSQL slow query log in table. Can someone kindly assist me with the table definition and implementation. SHOW CREATE TABLE mysql.slow_log; http://dev.mysql.com/doc/refman/5.1/en/log-tables.html -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
