| 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. - 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.
- If services run correctly then it is routed to database and database gives result to proxy server.
- 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. |
