Friday, May 3, 2013

[how to] Transfer data from multiple different servers to single database server

[how to] Transfer data from multiple different servers to single database server

Transfer data from multiple different servers to single database server

Posted: 03 May 2013 05:28 PM PDT

I am trying to transfer a particular table from multiple different servers(30) into one server.I do not want to create 30 different ole db source and merger them. Is there a simple way to resolve this issue?

Thanks!

DR - Moving databases from Active/Active cluster to Standalone server with LUN sync

Posted: 03 May 2013 07:27 PM PDT

We have an active/active cluster on LUNs (with one named instance on each node and a total of an 100 DBs)

For DR purpose, LUNs will be replicated (with same drive letters) and attached to a standalone server with same configuration of SQL Server but not same Windows Server editions (enterprise vs standard, but i don't think it may be an issue), and the two same named instances pre-installed.

I proposed the Attach / Detach solution, with a script that auto-generate the attach / detach to simplify the tasks, and other scripts to copy Logins, jobs and so on.

Other DBA said a better option will be to rename the MASTER and MSDB databases from the pre-installed named instances on standalone server with _old, and to attach the MASTER / MSDB databases from Cluster node instance to both corresponding named instance in standalone instance.

He said it works and had tested it.

However, I've never heard of such a solution nor can i find any documentation of it.

I would like to hear some other opinions.

Thank you, Kukah

Ubuntu / MySQL - How to choose from the various downloads?

Posted: 03 May 2013 05:02 PM PDT

I have Ubuntu 12.04.2 LTS. I would like install MySQL 5.5.29.

So I go to the MySQL 5.5 archive page
http://downloads.mysql.com/archives.php?p=mysql-5.5&o=other and scroll down to section 5.5.29

Here I see the following options:

enter image description here

There appear to be duplicates here, so I hover the mouse over the links.

mysql-5.5.29-linux2.6-x86_64.tar.gz  mysql-5.5.29-linux2.6-i686.tar.gz  mysql-5.5.29-debian6.0-x86_64.deb  mysql-5.5.29-debian6.0-i686.deb  mysql-5.5.29.tar.gz  mysql-5.5.29.zip

My operating system, Ubuntu 12.04.2, is 64 bit, so that rules out #2 and #4. I assume the .zip is for Windows, so that rules out #6.

That leaves #1, #3 & #5.

Please explain how I choose from these remaining options? Perhaps Ubuntu is considered similar to Debian so thats what I should choose?
Or should I select the generic download?

How to create Database in MS SQL 2012 from lot of XML and XSD

Posted: 03 May 2013 05:55 PM PDT

I´m new in programming form MS SQL 2012. I got few thousands of XML data and several tens of XSD, which having a dependency among themselves, and comes form one huge database.

I would like to create a tables by the XSD definitions and populate the tables from XML data. I´m working with Microsoft SQL Server 2012 and Visual Studio 2012.

Please can anyone help me, how to do that??

I tried many ways - for example I tried to create XML Schema Collection from XSD in T-SQL (CREATE XML SCHEMA COLLECTION - http://msdn.microsoft.com/en-us/library/ms176009.aspx) but did not find how to do this from file.

So I tried VS 2012, but advices and guidance are only for VS 2010 (for example http://msdn.microsoft.com/en-us/library/dd193276(v=vs.100).aspx) and in VS 2012 it´s seems to be different.

I´m starting to be desperate, so I´d like to pleased you for any help or advice how to solve this problem whether or not it is necessary to first create the XSD schema and then perform the XML.

Thanks a lot!

Select first row (grouping) + add aggregate function

Posted: 03 May 2013 03:03 PM PDT

First have a look at this question on StackOverflow.

I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query.

How can I combine the answer using DISTINCT with an aggregation...

I've tried:

SELECT DISTINCT ON (name, zonedistrict_id) ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname   FROM zones  ORDER BY name, zonedistrict_id, zonestyle_id;

Which screams: column "zones.gid" must appear in the GROUP BY clause or be used in an aggregate function

Which is strange, because if I remove ST_Union(geom) as geom,, the query works. But it's absent of the union'd geometry.

MySQL - Index on a query with order

Posted: 03 May 2013 01:29 PM PDT

I have the following table with >1M rows:

CREATE TABLE `wishlist_place` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `wishlist_id` int(11) DEFAULT NULL,    `place_id` int(11) DEFAULT NULL,    `city_id` bigint(20) DEFAULT NULL,    `created_by_id` int(11) DEFAULT NULL,    `created_at` datetime DEFAULT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `wishlist_place_unique` (`wishlist_id`,`place_id`),    KEY `IDX_20B83B35FB8E54CD` (`wishlist_id`),    KEY `IDX_20B83B358BAC62AF` (`city_id`),    KEY `new_places_on_wishlist` (`wishlist_id`,`place_id`,`id`)  ) ENGINE=InnoDB AUTO_INCREMENT=3079689 DEFAULT CHARSET=utf8;

And I'm doing the following query to get the newest places added to that wishlist:

SELECT place_id   FROM wishlist_place as wp  WHERE wishlist_id = 113   ORDER BY wp.id desc   LIMIT 0, 9

I thought the index 'new_places_on_wishlist' would work for this query, but it uses the index 'wishlist_place_unique' instead:

id  select_type table   type    possible_keys                                                       key                     key_len ref     rows    Extra  1   SIMPLE      wp      ref     wishlist_place_unique,IDX_20B83B35FB8E54CD,new_places_on_wishlist   wishlist_place_unique   5       const   28      Using where; Using index; Using filesort

UPDATE

I've noticed that the index that works best is:

KEY `new_places_on_wishlist` (`wishlist_id`,`id`,`place_id`)

but I still have to use the USE INDEX statement:

SELECT place_id   FROM wishlist_place as wp  USE INDEX(new_places_on_wishlist)  WHERE wishlist_id = 113   ORDER BY wp.id desc   LIMIT 0, 9

Why does MySQL takes the UNIQUE index first? The order for the indexes are fields in where, fields in order, fields in select?

--

And another related question, if I do a JOIN with the city table to get the wishlists of that city, is the index IDX_20B83B358BAC62AF the best one or it would be better (city_id, wishlist_id)?

Does MySQL 5.6 support fan-in replication?

Posted: 03 May 2013 03:01 PM PDT

I've known for quite awhile that Continuent's Tungsten Replicator supports fan-in replication (example).

By fan-in replication I mean combining multiple datasources into a single instance (like a datawarehouse).

I recently came across this MySQL deck that seems to suggest that this could be done with MySQL 5.6. Specifically slide/page 35 says "Arbitrary Topology" under Topologies:.

Can someone confirm that fan-in replication is possible with MySQL 5.6?

Has anyone setup this topology in 5.6?

Thanks!

Oracle - Best way to export 30 million rows to csv

Posted: 03 May 2013 11:30 AM PDT

I have a query that hits multiple tables in an ORACLE db (11g). I don't have access to the db machine itself. I use sql developer or sqlplus to connect.

there are about 31 million rows, and 7 columns, and I want to dump it into a csv.

Looking to learn about the various/best options for this case.

Different Result When Insert Data Contain Quote on Localhost and Online MySQL Hosting

Posted: 03 May 2013 06:25 PM PDT

I installed xampp on localhost and insert data contain quote using this php code

$content    = htmlentities($_POST['content']);  $content_esc = mysql_real_escape_string($content);  $save = mysql_query("INSERT INTO tbl_post (title,content,date,publish) values('$title_esc','$content_esc','$date','$publish')");

The result on localhost website is

this is 'example data'

The row on database filled with this

this is "example data"

But using the same code to insert data the result from my hosting provider is different

this is \'example data\'

The row on database filled with this

this is \"example data\"

I test my website on localhost, insert post contain quote and it show on page correctly. Then i surprise when i upload on online hosting, insert post and the post page show '\' before the quote.

At this time i replace the '\' with nothing. How to insert the quote so it can show same result on local mysql database and online mysql database ? What is wrong, my php code or my mysql database ?

Can I use Try_Cast to give me a query result column with different datatypes?

Posted: 03 May 2013 11:53 AM PDT

I have a column that is varchar(max). It contains mostly valid XML. But sometimes it has invalid xml (that I still need).

I am trying to make a view for this table that allows the value to display as XML (with the nice link feature that comes with SSMS) when the data is valid XML, but still shows the string when it is invalid XML.

Like this:

SELECT TRY_CAST([RunLog] AS xml) as [Log], -- works         RunLog,                             -- Also Works           -- This is the one I want, but it tries to parse all of them          -- (even the ones that fail the cast)         Case              When Try_Cast(RunLog as xml) is NULL                   Then NettinRunLoggLog else CAST(RunLog AS xml)         end as CombinedLog    FROM   LoggingTable

I think that this is a lost cause, because SQL Server will not allow a result to have more than one datatype. But I thought I would ask the experts just to be sure.

How can I copy a user while preserving all privs?

Posted: 03 May 2013 08:48 AM PDT

We're changing domains. I'd like to create a new account for myself, NewDomain\Bob which has the same su-level privileges as OldDomain\Bob, to minimize the chance of disruption when IT flips the switch.

I can easy assign the new login the same database roles. However, I'd like to associate the new login with the same users. Sadly, it seems that a user can have only one login. So what are the alternatives?

I could create the new login with a temporary user, reassign ownership of schemas, drop the old dbo user, and finally rename the new user. However, this would strip some privileges from the old login. In practice this may not matter, but I don't like the idea of cutting off any of my own privs before the new account is available.

The sp_change_users_login sproc looked promising to repoint the dbo user to the new login, but it has some serious limits - like not being usable with Windows logins.

Is there a workaround, or am I perhaps trying to fix the wrong problem?

What's the meaning of the Collation Code (blank, 90, 100, 110) in SQL Server 2012

Posted: 03 May 2013 07:57 AM PDT

During the installation of SQL Server 2012, in the Collation tab, i can choose between French_CI_AS and French_100_CI_AS.

What do they mean and which one is newer? When there's no Code, is it newer or older than the 100?

MySQL InnoDB does not return the last row on select with DESC order

Posted: 03 May 2013 09:09 AM PDT

We have a table scheme like following:

CREATE TABLE IF NOT EXISTS `offers` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `campaign_id` int(11) NOT NULL,    `user_id` int(11) NOT NULL,    `price` double NOT NULL,    `ip` varchar(15) NOT NULL,    `cdate` int(11) NOT NULL,    PRIMARY KEY (`id`),    KEY `campaign_id` (`campaign_id`,`price`)  ) ENGINE=InnoDB  DEFAULT CHARSET=latin5 AUTO_INCREMENT=190514 ;

On each new offer by the user we check if the last order is given by the same user with:

"select user_id from offers where campaign_id='".$campaign['id']."' order by id desc limit 1"

if the user_id is same we prevent the new offer to protect the user from accidental double clicks.

if there is not any problem with the offer we insert the offer with:

"insert into offers(campaign_id,user_id,price,ip,cdate) values (".$campaign['id'].",".$user['id'].",'".$price."','".$_SERVER['REMOTE_ADDR']."',".time().")"

But the problem is the select only returns the last inserted row after about 1 second. That means the user can insert multiple offers if they click the button too fast.

We use 5.5.30-30.2-log Percona Server as our database server. Following is our my.cnf file:

[mysqld]  datadir                         = /var/lib/mysql  tmpdir                          = /var/lib/mysqltmp  socket                          = /var/lib/mysql/mysql.sock  skip-external-locking           = 1  skip-name-resolve  open-files-limit                = 40000  max_heap_table_size             = 64M  tmp_table_size                  = 64M  log-error                       = /var/log/mysqld.log  thread-cache-size               = 50  table-cache                     = 4096  table-open-cache                = 4096  table-definition-cache          = 512  query-cache-size                = 0  query-cache-limit               = 16M  query-cache-type                = 0  sort-buffer-size                = 1M  read-buffer-size                = 1M  read-rnd-buffer-size            = 8M  join-buffer-size                = 1M  tmp-table-size                  = 64M  max-heap-table-size             = 64M  back-log                        = 100  max-connections                 = 10000  max-connect-errors              = 10000  max-allowed-packet              = 256M  interactive-timeout             = 360  wait-timeout                    = 360  innodb                          = FORCE  key-buffer-size                 = 32M  myisam-sort-buffer-size         = 4M  innodb-buffer-pool-size         = 60G  innodb-log-file-size            = 256M  innodb_log_files_in_group       = 2  innodb-log-buffer-size          = 4M  innodb-file-per-table          = 1  innodb-thread-concurrency       = 8  innodb-flush-log-at-trx-commit  =2  server-id                       = 1  slow-query-log                  = 1  slow-query-log-file             = /var/lib/mysqllogs/slow-log

How would i make online exam application? [closed]

Posted: 03 May 2013 04:55 AM PDT

  1. As per user login The exam will be scheduled with 30 questions randomly for each user.
  2. It have the timer of 30 - 50 mns.
  3. Each question have 4 options and 1 answer is correct.
  4. All the questions and answer are coming from the database.

So, I want to know what will be the database structure for this.

Working Linked Server Query fails in sp_send_dbmail

Posted: 03 May 2013 10:40 AM PDT

Take the following example:

EXEC msdb.dbo.sp_send_dbmail   @recipients = 'me@whatever.co.uk' ,  @query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' ,  @attach_query_result_as_file = N'True' ,  @query_attachment_filename = 'test.txt' ,  @subject = 'test' ,  @body = 'test' ,  @body_format = 'HTML' ;

This is giving the following error (even when ran under a windows credential that has sysadmin privileges to both servers):

Msg 22050, Level 16, State 1, Line 0  Error formatting query, probably invalid parameters  Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504  Query execution failed: OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVERA" returned message "Login timeout expired".  OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVERA" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correc  t and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".  HResult 0xFFFF, Level 16, State 1  SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].

Now, the linked server in question is configured as follows (note the use of @useself & no mappings):

EXEC master.dbo.sp_addlinkedserver @server = N'LINKEDSERVERA', @srvproduct=N'SQL Server'  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVERA',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'collation compatible', @optvalue=N'false'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'data access', @optvalue=N'true'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'dist', @optvalue=N'false'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'pub', @optvalue=N'false'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'rpc', @optvalue=N'true'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'rpc out', @optvalue=N'true'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'sub', @optvalue=N'false'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'connect timeout', @optvalue=N'0'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'collation name', @optvalue=null  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'lazy schema validation', @optvalue=N'false'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'query timeout', @optvalue=N'0'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'use remote collation', @optvalue=N'true'  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Here is where it gets funky: If I run the following in SSMS manually (i.e. not within sp_send_dmail) the query runs fine!

SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE

So the linked server IS working, its just sp_send_dmail thats having trouble with it.

So next I assumed it was a problem with the credential being used by the SQL Server services (including agent) - both of these are running as a DOMAIN\SQL service account - If I login to MSSMS as that service account and run the query manually - again it works, so the service account definitely has the permissions to run the query over the linked server.

I have double checked the protocols enabled on the native client (on both servers), TCP is definitly enabled on both servers and as I say, it works fine when not ran within sp_send_dbmail.

To confirm sp_send_dmail was executing the query as the service account I ran the following (note the use of SYSTEM_USER):

EXEC msdb.dbo.sp_send_dbmail   @recipients = 'me@whatever.co.uk' ,  @query = 'SELECT SYSTEM_USER' ,  @subject = 'test' ,  @body = 'test'

This returned the DOMAIN\SQL credential.

The last part of the error mentions something about the registry but I don't understand why that would only be effecting sp_send_dbmail (nor am I 100% certain on what needs checking in the registry) – the only article I could find was for SQL 2005 and the key location seems to be different in SQL 2008 R2?

My question: how do I get this linked server query to work in sp_send_dbmail when it works fine outside sp_send_dbmail?

PS: articles like this get round the issue by providing different credentials on the linked server - this isnt an option as this Linked Server is used by hundreds of other cross-server queries and they are all working fine.

Similar Issues:

Same Parameter in MySQL Stored Procedure

Posted: 03 May 2013 08:56 AM PDT

How do I cache dynamic query from store procedure? Right now I have created my store procedure like this :

CREATE PROCEDURE usp_MyProcedure (    IN UserID INT,    ....   )  BEGIN       SET @sqlQuery = CONCAT("SELECT Name From Users WHERE UserID > ", UserID, " AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = ", UserID, " ) Order by Name")        PREPARE stmt FROM @sqlQuery;     EXECUTE stmt;     DEALLOCATE PREPARE stmt;    END;

But this kind of query does not cached. so, every time it gets more time to execute/process query.

Now I have tried some other method like this:

CREATE PROCEDURE usp_MyProcedure (    IN UserID INT,    ....   )  BEGIN     SET @UserID = UserID;       SET @sqlQuery = "SELECT Name From Users WHERE UserID > ? AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = ? ) Order by Name";       PREPARE stmt FROM @sqlQuery;     EXECUTE stmt @UserID, @UserID; -- here i passed same variable twice.     DEALLOCATE PREPARE stmt;  END;

In the above case I have to pass same variable (@UserID) twice, because it is used 2 times in my query. but this job is very hectic in long or complex query. so, how do I avoid this?

One another method I tried as follows: 

CREATE PROCEDURE usp_MyProcedure (    IN UserID INT,    ....   )  BEGIN     SET @UserID = UserID;       SET @sqlQuery = "SELECT Name From Users WHERE UserID > @UserID AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = @UserID ) Order by Name";       PREPARE stmt FROM @sqlQuery;     EXECUTE stmt;     DEALLOCATE PREPARE stmt;  END;

But above query again does not cached. so, execution time is very long. and this type of variable declared as session global variable has may be conflict with another store procedure's variable. because I have call store procedure within this store procedure and variable name should be same in another store procedure.

So, let me know what is the best solution for the same.

Thanks.

Changing PostgreSQL port using command line

Posted: 03 May 2013 10:05 AM PDT

My PostgreSQL default port is 5432, I want to change default port by using command line (in Windows 7).

I saw this solution before: http://stackoverflow.com/questions/187438/want-to-change-pgsql-port

I tried running the following command, but it failed:

postgres --p 5431

Could someone tell me how to change port in using command line?

Real-Time Merge of two SQL Server databases on different servers

Posted: 03 May 2013 06:26 AM PDT

One of our customer requirements is to merge two databases on two different machines in a real time context. This means that we have to build some sync policy (or use SQL Server Tools to manage that). But I have some doubts:

  • How can I be sure that my databases are totally merged? I mean, we are in a real time environment with n/sec web and local requests, it's impossible to share table updates/merges with the same speed.

  • Is this a good architecture in spite of a master/slaves one (with an unique DB) topology? I'm pretty sure sync both databases each other will cost too much since our tables store a lot of data in terms of rows and information.

  • Does everyone provide a good solution to build the best architecture possible?

Thanks!

Is it possible to backup an sql database if it's status is not online?

Posted: 03 May 2013 04:56 AM PDT

We have an sql job which backs up all database in the sql server every night(excluding tempdb) whose state is 0 (online), but due to some reason it didn't back msdb. So the issue is at that time msdb state was other then 0. So my question is : is it possible to backup an sql database if it's status is not online (either RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY)?

Windows Server 2008 R2, Active Directory, and Oracle Database Server

Posted: 03 May 2013 05:03 AM PDT

I have a group of clients, Windows server 2008 R2(Active Directory Installed in it), and an oracle database Server

I Want to know how to make the configuration of the following Diagram in this link:

So how to configure the clients to reach the database server through the active directory?

enter image description here

Update

If Active Directory is not of any use here (as comments suggest), is there another way I can do remote routing from clients to the DB server through a proxy?

What's are the different ways to keep track of active and archived data?

Posted: 03 May 2013 07:05 PM PDT

I'm looking for different ways to keep track of both active and archived data so I can pro and con them.

The system: I have a computer with a database on it. The database has several tables in it; one of which contains a list of users that can use the computer; and several tables for auditing (user 1 did this, user 2 did that, etc). This database is a slave of a master database in which a Content Management System is used to say, add a new user and see reports on what user did what.

Example: As stated above, I have a table (lets call it users) that keeps track of all the users that are allowed to use the computer. As time goes by users will be added and removed. The problem is the audit tables keep track of a user ID so if the user is removed I lose the user information because the rows can't be joined. One idea I had was to use MySql's triggers so that if a user is added, an insert trigger is triggered and inserts a copy of the data to an 'archived' user table (lets call it users_archive). That way the computer can use users to determine if the user has permission to use it and reports can use users_archive for reports.

This seems like the easiest and most simple way to do it, but I can't find any other ways via google search to see if there are any other ways to do something like this.

Database stuck in restoring and snapshot unavailable

Posted: 03 May 2013 11:05 AM PDT

I tried to restore my database from a snapshot. This usually took around a minute to complete the last couple of times. When I did it today, it didn't complete for around 30 minutes and the spid was in a suspended state. I stopped the query and now my database is stuck in restoring state and my snapshot is unavailable. Am I screwed?

USE master;  RESTORE DATABASE QA from   DATABASE_SNAPSHOT = 'QA_Snap_Testing';  GO

Database design - do I need another table?

Posted: 03 May 2013 08:05 PM PDT

I am trying to make a database that follows a form that the company uses. When a client walks in the membes of staff have to fill in a form and the data is recorded. The form on paper is 10 pages long. The first time a client is seen the entire form is filled in and the client gets a clientID.

I have split the form into sections that make sense like accommodation and employment. I know I can link these tables together with the clientsID. Thats the simple part.

Now when a client returns the form comes out again but this time only certain parts are filled in, what ever the clients needs are. The records for most parts don't need updating but a new record needs inserting. what would be the best way around this.

So at the moment I have for example a table called client with an id and name another table called accommodation with clientid and address and another table employment with clientid and employer.

But how do I go about it when a client comes in to let us know he has a new employer. I cant update the current one as that is needed but I would need to add new record for the client.

Would this mean I would have to add a look up table for all my current tables?

event scheduler not called

Posted: 03 May 2013 02:05 PM PDT

I had created one event scheduler which looks like this

mysql> show create event event1      -> ;  +--------+----------+-----------+--------------------------------------------------------------------------------------------+  | Event  | sql_mode | time_zone | Create Event  | character_set_client | collation_connection | Database Collation |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+    | event1 |          | SYSTEM    | CREATE DEFINER=`root`@`localhost` EVENT `event1` ON SCHEDULE EVERY 1 MONTH STARTS '2013-02-02 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN   update samp set col1 =col1  + 1; END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |  +--------+----------+-----------+-----------------------------------------------------------------------+----------------------+----------------------+---------  -----------+  1 row in set (0.00 sec)

This events has not called on 1st of month. So i tried show processlist\g to find it is runnung or not; it showed following o/p

mysql> show processlist\g;  +-----+-----------------+-----------------+---------------+---------+------+---     | Id  | User            | Host            | db            | Command | Time | State                       | Info             |  +-----+-----------------+-----------------+---------------+---------+------+---  | 136 | event_scheduler | localhost       | NULL          | Daemon  | 1855 | Waiting for next activation | NULL    |

so NULL in db col means that no DB is assigned to it?

Please help me to solve it.

How can I improve my table design for different types of an entity?

Posted: 03 May 2013 06:05 PM PDT

Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables for different types of Client, each having fields applicable to the respective type and have one master table referencing all of them and have fields applicable to all types.

Currently, I come up with the following design:

enter image description here

But I don't think my design is efficient enough (or even correct and free of errors). What would you suggest? Also, if this is important in any way, I am planning to utilize MariaDB.

FETCH API_CURSOR causing open transaction in tempDB

Posted: 03 May 2013 03:05 PM PDT

A select statement run from Oracle 11gR1 to SQL Server 2005 using Gateway leaves an open transaction in tempdb. The user is a datareader in Database XYZ. Not sure why the open tran is in tempDB when the statement run is SELECT.

Any one had seen this issue before ?

Thanks in advance sekhar

What's the best way to backup DB files via network on Linux and Solaris machines?

Posted: 03 May 2013 08:17 AM PDT

I wonder what the best way to backup files via the network.

I have Solaris machines with Oracle 10i on a Veritas cluster. Machines are connected to EMC storage.

The /data/oracle directory is mounted on the EMC storage.

What I want is to backup the /data/oracle directory (70G) on some backup machine via the network (include soft-linked files). The reliability of copying files is very important.

I checked and found some ideas to do that.

For example the first option is to use rsync:

rsync   -WavH –progress   /data/oracle $backup_server_ip:/Backup_dir

The second option is transfering the files with tar and ssh, dealing with compression on both sides of the pipe:

cd /directory_that_we_want_to_backup  tar cpf - . | bzip2 -c | \    ssh  $backup_server_ip  "cd /Backup_dir && bzip2 -d | tar xpf -"

and so on.

I need advice on what is more reliability from the options above, and maybe on what other good options there are for this.

Cannot find MySQL Server Config Wizard for MySQL 5.6 build

Posted: 03 May 2013 01:05 PM PDT

Not sure if this is the right Stack Exchange site for this but...

I am trying to reconfigure my mySQL instance but cannot find the config wizard. I looked here: http://dev.mysql.com/doc/refman/5.0/en/mysql-config-wizard-starting.html

As one user pointed out, the config .exe file is not included in version above 5.5. Does anyone know how to reconfigure the MySQL server instance?

What causes InnoDB to write 100% more pages while slowing down answering queries?

Posted: 03 May 2013 06:05 AM PDT

I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, 15KRPM, in the data partition in RAID10, and 3 SSD, in the Journal partition in RAID5, and 2 SAS, 15KRPM in RAID 1 for the operating system. The data partition has it's own RAID controller, and the O.S. and Journal share the other card.

I'm running Ubuntu 12.04.1 LTS and MySQL 5.1.56 with InnoDB plugin 12.7 from Percona on top of that.

To the Data Partition MySQL writes indexes and data files; to the Journal partition it writes binary logs, slow and error logs, and innodb journal files.

In the last few weeks I've been observing some weird behaviour: MySQL slows down answering to queries, and at the same time doubles the "InnoDB Buffer Pool Pages Written" metric. At the same time, I see InnoDB Log buffer filling up 5 times the usual levels for the usual workload. Also, I don't observe any mutex and lock granting activity changes on the graph.

This is my current configuration:

  basedir=/usr/    datadir=/var/lib/mysql/data    tmpdir=/var/lib/mysql/tmp    server-id=1    socket=/var/run/mysqld/mysqld.sock    port=3306    user=mysql    pid-file=/var/run/mysqld/mysqld.pid    language=/usr/share/mysql/english    default-storage-engine=InnoDB    read_only    max_heap_table_size=256M    tmp_table_size=256M    table_cache=1024    thread_cache_size=64    thread_stack=1024K    max_allowed_packet=16M    max_connections=255    max_user_connections=250    skip-external-locking    skip-slave-start    master-info-file=/var/lib/mysql/relay/master.info    relay-log=/var/lib/mysql/relay/relay-bin    relay-log-index=/var/lib/mysql/relay/relay-bin.index    relay-log-info-file=/var/lib/mysql/relay/relay-log.info    log-slave-updates=1    expire_logs_days=1    sync_binlog=1    max_binlog_size=1G    binlog-format=MIXED    log-bin=/var/lib/mysql/binary/mysqld-binlog    log-bin-index=/var/lib/mysql/binary/mysqld-binlog.index    log-warnings=2    log-error=/var/lib/mysql/mysqld-err.log    slow-query-log    log_slow_slave_statements=1    log_slow_timestamp_every=1    slow_query_log_microseconds_timestamp=1    log_slow_verbosity=full    long-query-time=0.05    slow-query-log-file=/var/lib/mysql/mysqld-slow.log    innodb_adaptive_flushing=1    innodb_additional_mem_pool_size=20M    innodb_buffer_pool_size=16G    innodb_data_file_path=ibdata1:20M:autoextend    innodb_data_home_dir=/var/lib/mysql/data    innodb_doublewrite_file=/var/lib/mysql/journal/ib_doublewrite    innodb_fast_shutdown=0    innodb_file_per_table    innodb_flush_log_at_trx_commit=1    innodb_flush_method=O_DIRECT    innodb_io_capacity=1500    innodb_log_group_home_dir=/var/lib/mysql/journal/    innodb_max_dirty_pages_pct=75    innodb_open_files=1024    innodb_rollback_on_timeout    innodb_thread_concurrency=20    query_cache_size=0    query_cache_type=0    key-buffer-size=200M    server-id=233111    sql-mode=NO_AUTO_CREATE_USER    max_connections=850    max_user_connections=800    read-only    table-open-cache=1300    log-error=/var/lib/mysql/log/dbserver-err.log    slow-query-log-file=/var/lib/mysql/log/dbserver-slow.log    relay-log=/var/lib/mysql/relay/dbserver-relay-bin    relay-log-index=/var/lib/mysql/relay/dbserver-relay-bin.index    relay-log-info-file=/var/lib/mysql/relay/dbserver-relay-log.info    log-bin=/var/lib/mysql/binary/dbserver-mysqld-binlog    log-bin-index=/var/lib/mysql/binary/dbserver-mysqld-binlog.index    relay_log_purge=0    innodb_buffer_pool_size=240G    innodb_log_buffer_size=2G    innodb_log_file_size=4G    large-pages

And this is my current filesystem organization:

  SSD RAID5, Controller #0    /var/lib/mysql/journal -> /srv/mysql/ssd/journal    /var/lib/mysql/log     -> /srv/mysql/ssd/log    /var/lib/mysql/relay   -> /srv/mysql/ssd/relay    /var/lib/mysql/tmp     -> /srv/mysql/ssd/tmp      SAS RAID 10, Controller #1    /var/lib/mysql/backup  -> /srv/mysql/sas/backup    /var/lib/mysql/binary  -> /srv/mysql/ssd/binary    /var/lib/mysql/data    -> /srv/mysql/sas/data

Can you please help me understanding what is going on with my database server? Why is it slowing down service and increasing the InnoDB Buffer Pool Write activity?

Get the rank of a user in a score table

Posted: 03 May 2013 09:43 AM PDT

I have a very simple MySQL table where I save highscores. It looks like that:

Id     Name     Score

So far so good. The question is: How do I get what's a users rank? For example, I have a users Name or Id and want to get his rank, where all rows are ordinal ordered descending for the Score.

An Example

Id  Name    Score  1   Ida     100  2   Boo     58  3   Lala    88  4   Bash    102  5   Assem   99

In this very case, Assem's rank would be 3, because he got the 3rd highest score.

The query should return one row, which contains (only) the required Rank.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog