Monday, August 26, 2013

[how to] mysql.sock missing

[how to] mysql.sock missing

mysql.sock missing

Posted: 26 Aug 2013 06:52 PM PDT

I started my MYSQL database this morning but failed. And I tried all means I can think of:

chown -R mysql.mysql /var/lib/mysql  chmod g+w /var/run/mysqld  chgrp mysql /var/run/mysqld  chmod -R 755 /var/lib/mysql/  service mysqld stop; mysqld_safe --user=mysql &

but the file is still not there (/var/lib/mysql/mysql.sock). I checked the file /var/log/mysqld.log, it says:

130827 07:49:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql  130827  7:49:26  InnoDB: Initializing buffer pool, size = 8.0M  130827  7:49:26  InnoDB: Completed initialization of buffer pool  130827  7:49:26  InnoDB: Started; log sequence number 0 44233  130827  7:49:26 [ERROR] /usr/libexec/mysqld: unknown option '--qviheadfv<F4><F4>--bdcvadsfefa'  130827  7:49:26 [ERROR] Aborting    130827  7:49:26  InnoDB: Starting shutdown...  130827  7:49:31  InnoDB: Shutdown completed; log sequence number 0 44233  130827  7:49:31 [Note] /usr/libexec/mysqld: Shutdown complete    130827 07:49:31 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

I got totally lost. What is the option '--qviheadfv--bdcvadsfefa'? Any suggestion?

Oracle 12c: Invalid username/password while logging in via sqlplus

Posted: 26 Aug 2013 05:32 PM PDT

I got a version of Oracle 12c installed, I setup the $ORACLE_HOME and $ORACLE_SID to the proper value.

When connecting to the database with sqlplus:

sqlplus /nolog  connect / as sysdba

I got the following error:

ERROR: ORA-01017: invalid username/password; logon denied

I didn't got a chance to setup password during oui installation, for both sys and system. In this case, how can I login to the database?

MySQL Performance Issues

Posted: 26 Aug 2013 06:33 PM PDT

Over the past 3 weeks we have been having bottle-necking issues at our MySQL server. I have been using MonYOG to watch the processlist in an attempt to get a grasp on the issues. We are aware that some of the queries and processes we are running in our code is less than optimized, but I am not completely convinced that is the main source of our issues. I feel our server should be able to overcome these issues.

Our tables are a mix of innodb and myISAM. I am by no means a DBA and do not pretend to be one so I am not sure what engine is the best in our current environment. We are pretty read heavy, but mix in a great deal of updates and inserts as well. I was seeing a lot of locked tables which made me think innodb might be better since it does row lock instead of table lock. I converted a few of our tables from MyISAM over to innodb to take advantage of some of the settings that are available. The developers are using a great deal of complex joins as well.

Here is what we are running in our my.cnf:

  [mysqld]  datadir=/var/lib/mysql  port=3306  socket=/var/lib/mysql/mysql.sock  user=mysql    key_buffer_size=512M    innodb_file_per_table  innodb_buffer_pool_size=6GB  #the following line is causing some odd errors when doing db dump  #innodb_log_file_size=128M  innodb_log_buffer_size=8M  innodb_additional_mem_pool_size=32M    max_allowed_packet=16M  join_buffer_size=8M  sort_buffer_size=8M  max_connections=500  wait_timeout=500  skip-name-resolve  thread_cache=256  table_cache=256  tmp_table_size=48M  max_heap_table_size=48M    query_cache_size=64M    #logging of slow queries  log-slow-queries=/var/log/mysql-slow-query.log    # Default to using old password format for compatibility with mysql 3.x  # clients (those using the mysqlclient10 compatibility package).  #old_passwords=1    # Disabling symbolic-links is recommended to prevent assorted security risks;  # to do so, uncomment this line:  # symbolic-links=0    [mysqld_safe]  log-error=/var/log/mysqld.log  #pid-file=/var/run/mysqld/mysqld.pid

I changed the current "innodb_buffer_pool_size" to 6GB last week and that made a huge improvement to the bottle-necking. We have actually only really started seeing issues again today. Traffic has increased this week since we have schools starting and football season starting as well.

I have all kinds of statistical information I can supply you if needed. I am not sure what else to supply at this time.

If I cannot get this figured out I will be forced to call in a consultant. I am hoping someone will be able to give me a hand with some of these settings.

Thanks in advance.

------------------ EDIT--------------------

Here is some additional information requested:

MySQL Version - 5.0.95
OS version - CentOS 5.6 (will be upgrading to 6.4 in the coming weeks)

Server - Dell PowerEdge R710
CPU - Dual Quad Core 2.8GHz
RAM - 12GB
This is a dedicated machine. The only thing running on this box is MySQL

Maintain a data warehouse with old data that were removed with new data that is added

Posted: 26 Aug 2013 06:16 PM PDT

i want to make a datawarehouse for a postgres database. For that i have read i need to use an ETL data warehouse, which is responsible for transforming the data into a format more suitable for the reports and pass them to the target database (the datawarehouse itself). for the ETL i chose kettle from pentaho, using spoon and kitchen to build and run the transformation jobs

but I can't find how to take the changes made to a date from the last transformation in the ETL.

For example I have the invoices table has the following data:

  • Data prior to 2012 that are no longer used for transactions, but are still used for reports
  • Data after the 2012 can be modified or deleted, and must be updated to reports

Therefore, the data warehouse must have data prior to 2012, even if i delete it from the transaction database, and the data after 2012 updated

is this possible? How I can do this?

Need Help Executing a Remote Task from SQL Server Agent

Posted: 26 Aug 2013 05:50 PM PDT

There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues. We can solve this if we can have SQL Server Agent execute the .exe on the remote server to ensure the schedules never conflict.

Is there any way to do this with SQL Server Agent, perhaps with a Powershell command? If so, can you please give me the steps?

The .exe on the remote server is a data importer for SQL. It is a stand alone .exe. We want it to run on the batch server so it doesn't cause resource contention on the DB server. We are running SQL 2008 R2 Enterprise.

Please let me know if any additional information is required to answer this question.

What steps are necessary to safely restart a machine hosting Microsoft SQL Server?

Posted: 26 Aug 2013 03:46 PM PDT

Are there any special steps necessary to prevent data corruption when restarting a server hosting an MS SQL instance?

For example, I recently encountered the recommendation of stopping the SQL service manually. My understanding is that this is handled by the shutdown process.

I'm sure there are a zillion steps which individual people may recommend, like that I just mentioned, but are there any recommended by Microsoft, or widespread industry standards?

Database Design - Global Asset

Posted: 26 Aug 2013 12:40 PM PDT

We are trying to implement a "global" asset depreciation system in our company. One of my pet-peeves is overengineering and we seem to be doing quite bit of that lately. We oversee two very disconnected businesses... one dealing with automobiles and the other trains/rail cars. I've explained to them keeping these separate will provide them most flexibility. Personally I hardly see any value in combining their systems... very little these two businesses can share. Also, not sure why we're reinventing the wheel on asset depreciation when it's been beaten to death already... just buy something. But that's besides the point.

There are really 3-4 specific types of assets. And these assets, predictably, have very different attributes. Sure, there are a few common ones they share between all of them. ie. PurchaseDate, PurchasePrice. Our business has not changed in over 80 years... in fact, they didn't have a true IT department until this year. Yet, they want their asset management system to be super flexible. Assets should be self referencing so that they can maintain ParentAsset/ChildAsset to the N-th degree.

They also want to treat all their assets the same... so we are design a system where Servers are stored in the same table as train engines and automobiles. I've exhausted all my reasons why this is a terrible idea... can someone help me articulate why this is a bad idea????

Designing a user table, FName LName v.s Username

Posted: 26 Aug 2013 02:56 PM PDT

I did some research on different ways of getting user's name. Some database designers prefer to have a username that contains first name and last name with space separator, and some other designers have it in two separate fields such as fname and lname. When it comes to searching using mySql, which approach makes it easier to search for a user based on his full name, or only either his last name or first name ? Any advice on the design approach ? My application is probably going to have about 5,000,000 users registered, I'm a bit concerned about in what way to approach user full name.

This is how I'm doing it, assuming that the user enter "alex coner" in one input textbox and press enter, then I parse the string based on space separation and then run the query like this which doesn't know which one is fname and lname : 

select * from User   where UserFName in ("alex", "coner")      OR UserLName in ("alex", "coner")

is there a way to run this and then get the one that matches the closest ? Assuming there is these users as below:

alex buche  coner johnson  alex coner  david rudd    and the result appears as :    alex coner  alex buche  coner johnson

Enabling LOAD DATA LOCAL INFILE in mysql

Posted: 26 Aug 2013 12:52 PM PDT

LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian 7, but not Debian 7 minimal, though both installations come from the same precompiled deb package. Both on OpenVZ VPS.

How to debug why local-infile=1 does not work for an installation, and how to safely activate LOAD DATA LOCAL INFILE?

xtrabackup prepare - after dump or before restore?

Posted: 26 Aug 2013 10:43 AM PDT

i have doubts about xtrabackup --apply-log. is there any difference when use it? should it be done right after dump or before restore (even few months later)? seems stupid, but maybe i missing something.

documentation says this:

Prepare a backup in BACKUP-DIR by applying the transaction log file named xtrabackup_logfile located in the same directory. Also, create new transaction logs. The InnoDB configuration is read from the file backup-my.cnf created by innobackupex when the backup was made.

thanks for help :)

Oracle - Clause where in case when

Posted: 26 Aug 2013 05:31 PM PDT

all! I want to know how wrote this code of SQL, I already trying but i get message error. I wrote two way different this SQL. See you below

SELECT *    FROM tabela   WHERE coluna IN (CASE WHEN 1 = :parametro THEN 1 WHEN 0 = :parametro THEN (2, 3) END)

And

SELECT *    FROM tabela   WHERE (CASE WHEN 1 = :parametro THEN coluna = 1 WHEN 0 = :parametro THEN coluna <> 1 END)

I tried to run but it get error. Please! you help to tick or fix this code.

Thank a lot.

Regards, Jeison Pereira

Linked server query failures, fixed by scheduled query?

Posted: 26 Aug 2013 09:36 AM PDT

For a while now, I'm getting seemingly random failures in a couple of stored procedures that contains a linked server query. I got lucky and was able to determine that when the linked query fails, it fails everywhere (Access Client app, SSMS etc). These failures last from 2-10 minutes or so.

Error says something like ConnectDB Lib: SQL Server does not exist or Access Denied

So I setup a SQL Agent job on both servers, each asking the other server for 10 rows and then logging whether the select worked or not. I set this to run every minute for each server. Trying to map duration and time of failures to see what's happening at those times.

Now for the strange part. Since I set this up, I'm getting WAY LESS FAILURES! I'm trying to understand why this is working, because it seems like I'm doing a clumsy operation that is keeping the communication "fresh" between the servers, but I feel like there must be an actual SQL Server or Windows setting that is the right way to control this.

Any idea what this effect is? If a scheduled query works, is there some timeout setting that I've missed which governs it? Any suggestions are appreciated.

What is the best practices to backup and restore full and partial database with SQL Server 2012 and C#?

Posted: 26 Aug 2013 05:19 PM PDT

How can I do the following with C#:

  • Completely backup an existing database.
  • Partially backup an existing database.
  • Completely restore an existing database from a backup file.
  • Partially restore an existing database from a backup file.

My database saves images on the HARDDISK sometimes I need to backup and restore the database without the stored Images (might be 50 GB).

I need partially backup that execluding the FILESTREAM data (The saved files should be not included in the backup file).

I need partially restore that retrive the database without FILESTREAM data (The stored files should be not restored).

What is the best practices to do that?

Recover the disk space after deleting rows from table

Posted: 26 Aug 2013 12:24 PM PDT

I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the production queries. Is there any other way to recover the space without affecting the production queries.

replication required to configure - central office must be able to read and modify all customer data [on hold]

Posted: 26 Aug 2013 09:34 AM PDT

Each office must be able to enter its own customer data and to read customer data at other offices. The central office must be able to read and modify all customer data. what type of replication do i configure?

isql*plus crashes when multiple users run queries

Posted: 26 Aug 2013 12:11 PM PDT

In my workplace, multiple users are using isql*plus (Oracle 9i) to access a single database through server url. But after some time isql*plus crashes and its services need to be restarted.

Server is a desktop with 2 GB of RAM and 2.5Ghz AMD processor; 25 users.

  1. isql*plus is installed in pc in which main database access is provided (like proxy server). Here is the main problem and you can say isql*plus services crashes in proxy server.
  2. If services run correctly then it is routed to database and database gives result to proxy server.
  3. user see the result in isql*plus browser(url = ip address of proxy).

MySQL probelm in port: 3306

Posted: 26 Aug 2013 04:46 PM PDT

All websites on my VPS getting very slow, after dedcting the problem found that there are a line take 99.9% of the CPU:

        /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/cd.49.be.static.xlhost.com.err --pid-file=/var/lib/mysql/cd.49.be.static.xlhost.com.pid

Opened this file found nothing, everything goes right in this file.

EDIT:

etc/my.cnf

[mysqld] innodb_file_per_table=1

tried to know more, and closed all the content of my domains, also removed each database, one by one and nothing change still the same problem !

Could you help to know from where this error

Finding swap causes of MySQL

Posted: 26 Aug 2013 01:36 PM PDT

In my centos 6.3 server I have a MySQL 5.5.33 database.
It has 17 tables (15 InnoDB, 2 MyISAM) and total records 6.7M rows. I refactored my schemas and added indexes for my slow logs. My average query time is 20-30 ms. And my database performs well.

But I have some cron queries that runs every 3 hours. They don't use any index, they runs very slow and every query runs nearly 1500-2000 ms. I don't plan to add new indexes for them, because in that case I have to add many indexes and that queries run very rare.

When I restart my database server, -normally- swap is zero. After some time swapping becomes large gradually. After 13 days, I get 650MB swap of MySQL. I want to find what causes this swapping and try to reduce the swap without performance grade.

I want to be sure that the cause is cron queries or some other thing causes this swap size.

My top results: 

top - 13:33:01 up 13 days, 11:04,  1 user,  load average: 0.77, 1.02, 1.07  Tasks: 148 total,   1 running, 147 sleeping,   0 stopped,   0 zombie  Cpu(s): 27.4%us,  5.3%sy,  0.0%ni, 59.1%id,  7.8%wa,  0.0%hi,  0.3%si,  0.0%st  Mem:   1020564k total,   854184k used,   166380k free,    73040k buffers  Swap:  2097144k total,   643036k used,  1454108k free,    94000k cached      PID USER        PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  SWAP COMMAND   9573 mysql       20   0 2336m 328m 3668 S  7.3 33.0 349:14.25 554m mysqld  15347 examplecom  20   0  219m  32m  10m S  2.7  3.2   0:02.66    0 php-cgi  15343 examplecom  20   0  215m  28m  10m S 10.0  2.9   0:05.80    0 php-cgi  15348 examplecom  20   0  215m  28m  10m S 12.3  2.8   0:03.62    0 php-cgi  15346 examplecom  20   0  215m  28m  10m S  9.6  2.8   0:06.39    0 php-cgi  15350 examplecom  20   0  212m  25m  10m S 10.0  2.6   0:02.19    0 php-cgi  15345 examplecom  20   0  211m  24m  10m S  6.6  2.5   0:04.28    0 php-cgi  15349 examplecom  20   0  209m  22m  10m S  5.3  2.2   0:02.66    0 php-cgi  12771 apache      20   0  334m 5304 2396 S  0.0  0.5   0:02.53  10m httpd  12763 apache      20   0  335m 5224 2232 S  0.3  0.5   0:02.33  11m httpd

Edit: I restarted mysql server 2 days ago, so swap is low now. But with time passes it again will become bigger. When I make top I get this now:

top - 23:30:46 up 15 days, 21:01,  1 user,  load average: 0.35, 0.42, 0.42  Mem:   1020564k total,   931052k used,    89512k free,    76412k buffers  Swap:  2097144k total,   280528k used,  1816616k free,   233560k cached      PID USER        PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  SWAP COMMAND  23088 mysql       20   0 1922m 311m 3440 S  1.0 31.3  50:04.53 143m mysqld  10081 examplecom  20   0  216m  28m  10m S  4.0  2.8   0:01.67    0 php-cgi  10069 examplecom  20   0  215m  27m  10m S  3.3  2.7   0:04.81    0 php-cgi  10070 examplecom  20   0  215m  26m  10m S  8.3  2.7   0:04.75    0 php-cgi  10062 examplecom  20   0  215m  26m  10m S  6.0  2.7   0:06.26    0 php-cgi  10060 examplecom  20   0  214m  25m  10m S  5.3  2.6   0:07.51    0 php-cgi  10074 examplecom  20   0  214m  25m  10m S  6.6  2.6   0:03.01    0 php-cgi  10080 examplecom  20   0  212m  23m  10m S  6.0  2.4   0:01.58    0 php-cgi

When I make free -m I get this:

             total       used       free     shared    buffers     cached  Mem:           996        927         68          0         76        219  -/+ buffers/cache:        631        364  Swap:         2047        273       1774

My /etc/my.cnf file contents:

[mysqld]  datadir=/var/lib/mysql  socket=/var/lib/mysql/mysql.sock  user=mysql  symbolic-links=0  skip-name-resolve    slow_query_log=ON  long_query_time=1.2    innodb_file_per_table    max_allowed_packet=32M  thread_stack=256K  max_allowed_packet=32M  thread_stack=256K  max_connect_errors=100000000  max_connections=600    key_buffer=256M  sort_buffer_size=2M  read_buffer_size=2M  read_rnd_buffer_size=2M    thread_cache_size = 8  tmp_table_size=128M  max_heap_table_size=128M  query_cache_size = 209715200  query_cache_limit = 52428800  join_buffer_size=4M  table_cache=2400  low_priority_updates=1  tmpdir = /var/tmp    query_cache_type = 1    innodb_buffer_pool_size=256M  innodb_additional_mem_pool_size=512K  innodb_log_buffer_size=500K  innodb_thread_concurrency=8    [mysqld_safe]  log-error=/var/log/mysqld.log  pid-file=/var/run/mysqld/mysqld.pid

Problems migrate database from 4 to 5.5 mysql

Posted: 26 Aug 2013 05:10 PM PDT

I have a server with 4.0 mysql version innodb engine.

i need to migrate to other server with 5.5 version (linux) ok

i dump the full database using mysqldump, when i try to restore on the 5.5 server it has a problem, because the file generate in the 4.0 set the variable on the sql scritp TYPE=INNODB and mysql 5.5 accepts only ENGINE=INNODB format on the time table creation and it abort the process.

i search on google, call any friends and nothing! the sql file has 16 gigabytes. is not possible edit with an file editor.

error exemple: CREATE TABLE tabadi ( nAdicao char(3) default '0', nSeqAdic char(3) NOT NULL default '', cFabricante varchar(60) NOT NULL default '', vDescDi varchar(15) NOT NULL default '', xPed int(6) NOT NULL default '0', nItemPed int(6) NOT NULL default '0', nDI varchar(12) NOT NULL default ''

) TYPE=InnoDB

ERROR 1064 (42000) at line 19: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB' at line 9

Ps: i did try to change TYPE TO ENGINE and its solve the problem.

can anybody help me?

Thanks a lot.

Michel

SQL*Plus default output format

Posted: 26 Aug 2013 05:39 PM PDT

I have noticed that using the same SQL*Plus client to connect to different Oracle database result in different outputs for the same SQL query.

Database 1:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))'    SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:54:45 2013    Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.      Connected to:  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production  With the Partitioning, OLAP and Data Mining options    SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;    'XXXX TO_CHAR(UNIQUES  ----- ---------------  XXXX          300f483

Here the output is on one line, the headers are truncated.

Same thing on an other database:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))'    SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:58:15 2013    Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.      Connected to:  Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production  With the Partitioning, Data Mining and Real Application Testing options    SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;    'XXXX'  --------------------------------  TO_CHAR(UNIQUESEQUENCENUMBER,'XXXXXXXXXXXXXX')  --------------------------------------------------------------------------------  XXXX          3a1393b

Here the output is split on two lines.

I am not looking for SQL*Plus formatting options like linesize, column format, etc. that just impact the current session. What I need to understand is what is making the output different even when I don't specify any options.

Is there some default settings on the server side? Is it depending on the Oracle server version (10.2.0.1 in one case, 10.2.0.5 in the other)?

Note: column UniqueSequenceNumber is of type NUMBER

Note 2: Tried on a Server version 10.2.0.3, same result as on 10.2.0.1 (one liner)

In reply to miracle173 :

C:\Oracle\instantclient_10_2>sqlplus /nolog    SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 26 09:32:02 2013    Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.    SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))  Connected.  SQL> select 'xxxx' from dual;    'XXX  ----  xxxx    SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))  Connected.  SQL> select 'xxxx' from dual;    'XXXX'  --------------------------------  xxxx    SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))  Connected.  SQL> select 'xxxx' from dual;    'XXX  ----  xxxx    SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))  Connected.  SQL> select 'xxxx' from dual;    'XXXX'  --------------------------------  xxxx

How do I maintain more audit history?

Posted: 26 Aug 2013 09:50 AM PDT

I created a SQL Server audit and when I view the audit logs I only see today's audit. I am saving it to a file and I want to store audit for one year. Is there a way to store database and server audit for one year? Does SQL Server store audits for only a day?

CREATE SERVER AUDIT [Audit-20130618-142022] TO FILE   (    FILEPATH = N'C:\Documents\Audit\Audit' ,    MAXSIZE = 0 MB ,    MAX_ROLLOVER_FILES = 2147483647 ,    RESERVE_DISK_SPACE = OFF   )   WITH   (     QUEUE_DELAY = 1000 ,    ON_FAILURE = CONTINUE   );

SQL Server: subscriber as publisher and hierarchical replication

Posted: 26 Aug 2013 04:16 PM PDT

In Oracle Streams one way replication from one DB to another is a basic block for many replication topologies (N-way, hierarchical, combined and so on), and changes could be captured and applied for the same table at the same time.

But I can't find anywhere in SQL Server documentation whether table (article) could be a source (publisher) and destination (subscriber) simultaneously. Is it possible, for example, to setup bidirectional replication using two pairs of publisher->subscriber transactional replication?

Naturally I am more interested in multi-level hierarchy: for example one table is replicated from root node through intermediate nodes to leaves, second one - from leaves to root, and third one - bidirectionally, and for all tables intermediate nodes could perform DMLs to be replicated too. With Oracle Streams it is easy to achieve, and even more sophisticated configurations are possible, but are they with SQL Server?

UPDATE: It seems it is with use of merge replication and republishing (http://msdn.microsoft.com/en-us/library/ms152553.aspx), but what about transactional replication?

Cannot see Green Button with white arrow in object explorer; DTC config problems?

Posted: 26 Aug 2013 01:16 PM PDT

I have SQL Servers across locations setup for replication. One of the SQL Server instances that I installed, running locally I can see the green button with the white arrow in the object explorer when I connect to it. However, when I connect to it from any other location, I cannot see that or neither can I start or Stop the SQL Serer Agent; even though locally I am able to do that.

Additionally, the MDTC doesn't has only 2 items sent both with were rejected, where as other servers have 100's committed and sent. Is there something wrong with the DTC settings?

Please help.

Server permissions of an activation stored procedure of a Server Broker queue

Posted: 26 Aug 2013 11:42 AM PDT

I have a stored procedure that queries the sys.dm_exec_requests view. In the stored procedure the view only returns one row, while the stored procedure needs to see all of them. The MSDN article on the view says that what is returned depends on the user permission:

If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.

The stored procedure is actually an activation stored procedure of a broker queue:

CREATE QUEUE test_queue    WITH     STATUS = ON,    RETENTION = OFF ,    ACTIVATION (      STATUS = ON,      PROCEDURE_NAME = test_procedure,      MAX_QUEUE_READERS = 1,       EXECUTE AS SELF ),  POISON_MESSAGE_HANDLING (STATUS = ON)   ON [PRIMARY]

When I read the MSDN article, I changed 

EXECUTE AS SELF

to

EXECUTE AS 'dbo'

which did not make any difference. sys.dm_exec_requests would still return a single row.

Also I tried to do

EXECUTE AS OWNER

Which made no difference either. As I understand the stored procedure owner is who created it, which was me. And as I'm in a sysadmin role that should be working, but it does not. I'll appreciate any troubleshooting tips.

In particular I would like to know how to list a server permissions for a given user so I could check if they include 'VIEW SERVER STATE' permission as per the article.

The confusing part is that user as I understand it is on database level, so it's unclear to me how it can have server permission. If you could clarify that, it would be great too.

MySQL failover - Master to Master Replication

Posted: 26 Aug 2013 05:16 PM PDT

My company is trying to implement a MySQL failover mechanism, to achieve higher availability in our webservices tier - we commercialize a SaaS solution. To that end we have some low-end VMs scattered through different geographical locations, each containing a MySQL 5.5 server with several DBs, that for the time being are merely slave-replicating from the production server - the objective up until now was just checking the latency and general resilience of MySQL replication.

The plan however is to add a Master-Master replication environment between two servers in two separate locations, and these two instances would handle all the DB writes. The idea wouldn't necessarily imply concurrency; rather the intention is having a single one of the instances handling the writes, and upon a downtime situation using a DNS Failover service to direct the requests to the secondary server. After the primary comes back online, the b-log generated in the meantime in the secondary would be replicated back, and the DNS Failover restored the requests back to the first one.

I am not an experienced administrator, so I'm asking for your own thoughts and experiences. How wrong is this train of thought? What can obviously go wrong? Are there any much better alternatives? Bash away!

Thanks!

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Posted: 26 Aug 2013 12:16 PM PDT

Here is my scenario

Master1 hosting DB1  Master2 hosting DB2  ...  MasterN hosting DBN    replicate to:    Slave1 hosting DB1,DB2... DBN

I've read similar questions and they recommend to start different instances at Slave1 and simply do MasterN-Slave1(instanceN) replication, as instructed here:

Single slave - multiple master MySQL replication

That would be piece of cake, but running different instances might be a waste of resources.

I really want to achieve this with an single DBMS instance at Slave1, and if possible with PostgreSQL; but can try with MySQL if there is a solution for this.

Any help is appreciated.

How to search whole MySQL database for a particular string

Posted: 26 Aug 2013 12:08 PM PDT

is it possible to search a whole database tables ( row and column) to find out a particular string.

I am having a Database named A with about 35 tables,i need to search for the string named "hello" and i dont know on which table this string is saved.Is it possible?

Using MySQL

i am a linux admin and i am not familiar with databases,it would be really helpful if u can explain the query also.

SQL Server account delegation setup

Posted: 26 Aug 2013 11:16 AM PDT

I am try to run bulk insert command on SQL Server 2008 but I am having issues with the security. after researching via internet, the problem has something to do with account delegation and impersonation.

However all solutions seems to point to an Active Directory setup which my setup is not.

My setup is SQL Server 2008 Express on Windows Server 2008 Standard configured as a workgroup.

Despite a SQL Server user account, assigned all the user mappings and server roles and ensuring security set to SQL, I am getting this error:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\server_name\file_name.txt" could not be opened. Operating system error code 5 (Access is denied.).

So, how does one setup SQL Server account delegation and impersonation on a workgroup environment?

Thanks in advance

Trouble setting up Nagios to monitor Oracle services

Posted: 26 Aug 2013 03:16 PM PDT

I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and LD_LIBDRARY_PATH of course, but it keeps generating the following error:

/usr/local/nagios # libexec/check_oracle_health --connect "oracle-server:1551" --username user --password "pass" --name OFFDB1 --mode tablespace-can-allocate-next --warning 20 --critical 30  CRITICAL - cannot connect to oracle-server:1551. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA (DBD ERROR: OCIServerAttach)

I'm still fairly new to Oracle, but my googlings seem to indicate that SERVICE_NAME should match the GLOBAL_DBNAME in listener.ora, which is OFFDB1. Do I need to do something else here like modify the connect string?

As a note, there are multiple instances of oracle sharing the target box, but each seems to be intalled to separate partitions and are running their own listeners or various ports.

What is difference between Oracle Apps and Oracle Application Server?

Posted: 26 Aug 2013 06:49 PM PDT

I am very new to oracle, could anybody tell me what Oracle Apps and Oracle Application Server are.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog