texts: Transaction Replication:Intialize from Backup

texts:
I have a database which i want to replicate to another server.
Since the database is huge(~1.5 TB) i have decided to do initialize from backup.
But since it is a production live database,new insert/updates happen all the time.
I have done intialiaze from backup from a full backup earlier.
I want to know if i can implement transactional replication from a full backup + differential backup.

So when i create subscriber using the below script,do i have to give the differential.bak as the backupdevicename or the fullbackup file name.

-----------------BEGIN: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------
use [AdventureWorks2012]
exec sp_addsubscription @publication = N'Demo_pub', @subscriber = N'DESKTOP-K0UF973\SQLTEST', @destination_db = N'AdventureWorks2012', @sync_type = N'initialize with backup',
@subscription_type = N'pull', @update_mode = N'read only',@backupdevicetype=N'disk',
@backupdevicename=N'C:\Rep\Replication_Scripts\differential.bak'
GO
-----------------END: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------

-----------------BEGIN: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------
use [AdventureWorks2012]
exec sp_addpullsubscription @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publication = N'Demo_pub', @publisher_db = N'AdventureWorks2012',
@independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only',
@immediate_sync = 0

exec sp_addpullsubscription_agent @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publisher_db = N'AdventureWorks2012',
@publication = N'Demo_pub', @distributor = N'DESKTOP-K0UF973\SQLTEST', @distributor_security_mode = 1,
@distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64,
@frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0,
@frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@active_start_date = 20180731, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'',
@use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------END: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------

Since it is a live database,i cannot make the database into single user mode and take full backup and initialize the subscriber.I have trans logs also being taken every hour.So i am planning to restore recent full backup + differential to subscriber and will disable trans log backup job temporarily.
Is this a correct approach.?
if anyone encountered similar situation ,please let me know.


texts: Move Mysql database from Windows Xampp to Linux MySql Server

texts:
I 'am looking for a way to move some database in my local xampp to remote MySql Server , running into a Linux VM (Centos 7)

Any idea or any suggestions to do this migration?


texts: mysql replication master 5.5 slave 5.1 error on create database

texts:
I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we cannot upgrade yet.

Everything is going fine until a new database is created in the master, then the slave replication fails and shows the error:

Query caused different errors on master and slave.
Error on master: message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d. Created with MySQL %d, now running %d.
Please use mysql_upgrade to fix this error.' error code=1558 ; Error on slave: actual message='no error', error code=0. Default database: 'newdb'. Query: 'DROP DATABASE newdb'

My workaround is:

set global sql_slave_skip_counter=2;
create database newdb;
start slave

After doing a checksum of all database tables everything seems ok, but we have an awful application that creates databases very often, so I would like to know if there is a way that doesn't imply coding to solve the problem without manually creating the database.


texts: How do you manage SQL jobs that belong to a database?

texts:
We just had an issue where we lost msdb on one of our servers along with all of the jobs which we created for a multitude of databases. The fact that we didn't have a backup of msdb is one issue, which I won't get into, but it did make me think about how we develop the jobs that go along with our databases.

What are the different ways that one could manage jobs? I guess ideally they would be in source control, but I've yet to find any tools which do a good job of managing large systems of databases with their associated jobs.


texts: Installing time zone data on MariaDB 10.0

texts:
Yesterday, I set the time zone on our DB server (as explained here).  Doing so required using the mysql_tzinfo_to_sql executable.  Fortunately, this already existed on the server, and conversion of the time zone data went smoothly.

However, today I can see that our slave is generating the same error.  The problem is that the slave doesn't seem to have the mysql_tzinfo_to_sql executable.  apt-get tells me that it exists in these other packages:

root@slave:~# mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql -u root mysql
The program 'mysql_tzinfo_to_sql' can be found in the following packages:
 * mysql-server-5.5
 * mariadb-server-5.5
 * mysql-server-5.6
 * percona-xtradb-cluster-server-5.5
Try: apt-get install <selected package>

I'm concerned that installing one of these (probably would choose mariadb-server-5.5) would conflict with our currently-installed version of MariaDB (10.0.17-MariaDB-1~precise-log).

How should I go about installing this time zone data on the slave?

Can it be easily copied over from the master's DB?
Is my concern about installing mariadb-server-5.5 unwarranted?
Is there a totally different approach I'm missing?


texts: Why is my database using Partitioning? ( I know it's not )

texts:
Running this query:

select * from sys.dm_db_persisted_sku_features

Shows me that I'm using Partitioning:

| feature_name | feature_id |
|--------------|------------|
| Partitioning | 200        |

I'm pretty sure that I'm not using partitioning, since this database is only mine, and it's for consulting. There are 3 tables that I created using simple "create table" statements. There's no index on tables too. So, why is SQL Server saying that this database is using Partitioning?


texts: SQL Server Reporting Services 2017: Multiple Instances

texts:
SQL Server Reporting Services are no longer part of the installation media since SQL Server 2017. They are delivered as a separate Installer.

The new Installation Wizard gives no option to install multiple (named) instances of SSRS 2017 on the same machine.

Is that still possible?


texts:
Please note that I'm not talking about transaction logs. I'm referring to a folder within SQL Server called 'Log' where ErrorLog and SQLDrmp files exists.

Well, the fact is that this folder is growing by 8GB a day on a machine dev machine that has 250gb total storage. That's a problem.

I figured out that I can create a new folder called Log2, run sp_cycle_errorlog get the new "errorlog" copy to this new folder, stop SQL Server (and agent) switch the folders, start SQL Server (and agent) again, and delete the old folder.

Ideally I would like to disable this kind of logging but if not possible, is there a way to automatically clean it? I mean, discard the logs perhaps?


texts:
MySQL fabric does not get configured , getting these errors :

[mysql@dev-dm-gitest101z /root]# mysqlfabric manage setup

Error :
[INFO] 1413953890.788408 - MainThread - Initializing persister: user (fabric),
server (dev.local:3301), database (fabric).

Error: Command (CREATE TABLE providers (provider_id VARCHAR(256) NOT NULL, type INT
NOT NULL, username VARCHAR(100) NOT NULL, password VARCHAR(128) NOT NULL, url
VARCHAR(256) NOT NULL, tenant VARCHAR(100) NOT NULL, default_image VARCHAR(256),
default_flavor VARCHAR(256), CONSTRAINT pk_provider_id PRIMARY KEY (provider_id)), ())
failed accessing (dev.local:3301). 1071 (42000): Specified key was too long;
max key length is 767 bytes.

$ mysqlfabric manage setup

[INFO] 1413955889.898494 - MainThread - Initializing persister: user (fabric),
server (localhost:3301), database (fabric).

Error: Command (CREATE TABLE log (subject VARCHAR(40) NOT NULL, reported TIMESTAMP
/*!50604 (6) */ NOT NULL, reporter VARCHAR(64) NOT NULL, message TEXT, category int NOT NULL,
type int NOT NULL, INDEX key_subject_reported (subject, reported), INDEX key_reporter (reporter),
INDEX key_reported (reported), INDEX key_category (category), INDEX key_type (type)),
()) failed accessing (localhost:3301). 2055: Lost connection to MySQL server at 'localhost:3301',
system error: timed out.


texts: How to calculate a value for all rows in sql server?

texts:
I have two tables. One is a linking table to handle a many-to-many relationship between users and construction projects, and the other one is a payment table which includes the users' payments. Now I want to calculate each user's payment to show it along with user's information.
To do this, I wrote this query.

SELECT MemberID,
MembershipDate,
(SELECT SUM(Amount) from Payment where MemberID = 1001) AS Payment
FROM   User_Project_LinkingTable where MemberID = 1001

Now, how can I do this for all users to show all of them in a list?

Thanks in advance


texts:
I asked a question on Stackoverflow, and was told that my schema was way off. I am using mongodb to store whether or not a log file exists. The log file contains info for an hour. I'm not actually storing the file, just the file's name and the hour it represents. I'm also determining the existence of data for a given hour if that file exists in my db. I don't deny my document structure is bad, but:

What is a better way to do the schema I have (below)? Each file represents an hour. The DB just has to know that a file exists for a specific day and hour. My webapp pulls this info to display available days and hours for the user.
Why is my schema so bad?

Current document structure:

{
    _id   : "2014-02-13",
    hours : [
              { hour: "00", file: [
                     {name:"something.csv", date_added : ISODate("2014-04-04T08:59:34.268Z")},
                     {...},
                     {...}]
              },
              { hour: "01", file: [{},{}]},
              { hour: "04", file: [{},{}]} // some hours can be missing
            ]
}


texts: Indexing: Invalid default value for '[column]'

texts:
I am working on a 10 year old web-app (!!!)
& currently running mysql locally, version 5.7.
This is the table I am currently working on:
CREATE TABLE `processes_history` (
  `p_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `exec_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `feature` varchar(100) NOT NULL DEFAULT '',
  `macro` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `ts` date NOT NULL DEFAULT '0000-00-00',
  `seen` int(10) UNSIGNED NOT NULL DEFAULT '1',
  `seen_time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `focus` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `focus_time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `mouse` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `keyboard` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `interactive` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `interactive_time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `last_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(`ts`))
(
PARTITION p0 VALUES LESS THAN (736695) ENGINE=MyISAM,
PARTITION p201701 VALUES LESS THAN (736726) ENGINE=MyISAM,
PARTITION p201702 VALUES LESS THAN (736754) ENGINE=MyISAM,
PARTITION p201703 VALUES LESS THAN (736785) ENGINE=MyISAM,
PARTITION p201704 VALUES LESS THAN (736815) ENGINE=MyISAM,
PARTITION p201705 VALUES LESS THAN (736846) ENGINE=MyISAM,
PARTITION p201706 VALUES LESS THAN (736876) ENGINE=MyISAM,
PARTITION p201707 VALUES LESS THAN (736907) ENGINE=MyISAM,
PARTITION p201708 VALUES LESS THAN (736938) ENGINE=MyISAM,
PARTITION p201709 VALUES LESS THAN (736968) ENGINE=MyISAM,
PARTITION p201710 VALUES LESS THAN (736999) ENGINE=MyISAM,
PARTITION p201711 VALUES LESS THAN (737029) ENGINE=MyISAM,
PARTITION p201712 VALUES LESS THAN (737060) ENGINE=MyISAM,
PARTITION p201801 VALUES LESS THAN (737091) ENGINE=MyISAM,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE=MyISAM
);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `processes_history`
--
ALTER TABLE `processes_history`
  ADD PRIMARY KEY (`p_id`,`exec_id`,`feature`,`ts`),
  ADD KEY `ts` (`ts`),
  ADD KEY `exec_ts` (`exec_id`,`ts`),
  ADD KEY `last_seen` (`last_seen`);

I keep getting an error when adding an index to p_id, exec_id, ts:
ALTER TABLE `dbname`.`processes_history` ADD INDEX `p_id,exec_id,ts` (`p_id`, `exec_id`, `ts`);

Error SQL query:
ALTER TABLE dbname.processes_history ADD INDEX p_id,exec_id,ts
(p_id, exec_id, ts) MySQL said: Documentation
1067 - Invalid default value for 'ts'

Following this post: On Create Index, Invalid default value
From what I understood, using 0000-00-00 as a default value breaks the 'date' type, and that's why it's not working.
But I just couldn't understand what is the solution for this situation. Using TIMESTAMP type instead?
Is there a way to solve this problem without breaking the structure (for now at least) unit I finish the whole web-app? Many things are dependent on that table and i reeeealy don't want to do something risky to index it the way I want.


texts: Does sys.dm_os_waiting_tasks include RUNNABLE threads?

texts:
From MSDN:

  Returns information about the wait queue of tasks that are waiting on some resource.

Is CPU considered a resource in this definition? I initially thought it's not, but what made me concerned is the definition of wait_duration_ms from the same page:

  wait_duration_ms Total wait time for this wait type, in milliseconds. This time is inclusive of signal_wait_time.

If it does include RUNNABLE tasks, then what would be the value of wait_type for them? Is it some special value indicating that it's not blocked, but rather waiting for the scheduler, or is it the same value it had when it was in SUSPENDED state?


texts: SSAS 2014, Relation OLAP - Poor performance, high resource usage, what did I do wrong?

texts:
Let me start with the background, I was working on a project that needed a back end that was scalable and fast. Having worked quite a bit with SSAS, I thought I'd try SSAS's in memory solution, Relation OLAP. Long story short, it ended very poorly, even simple queries redlined the processor, there was no multithreading, and the amount of memory used constantly grew. Barring ROLAP being a lemon, I must have messed up my implementation, and I had some questions on best practices and how I can avoid the pitfalls in the future.

First up multi-threading, was there a setting I missed to enable multi-threading, because SSAS would nail one processor, and leave the other 7 untouched.

Could it be Hyper V messing us up? All of our development servers are virtual, we knew this could be a problem for an in memory app, so we stayed within a single NUMA node, so the memory shouldn't have been cached, and we didn't get cross node traffic.

Measures from mutiple tables killed performance, this was one of the big selling points for me, OLAP in a microsoft biome without having to first transform the data into a star schema. However as soon as we used queries with measures from multiple tables queries that might have taken two seconds individually, the combined queries had to be stopped after 15 minutes or caused the server to crash. Is there a best practice I missed about only having measures in a single table, or could this be caused by the relations between tables?

Is a many to many relationship getting support in SSAS 2016?

Why does the memory used grow continuously, because we only had four queries, so it's not having to create caches for a ton of queries. We did a default reprocess every night, and a full on weekends. A full process only took 7 minutes (which coming from multi dimensional SSAS was jaw dropping fast) and the intial RAM usage was just over 4 gigs. By the next day it would be between 12 and 16 gigs. Is there a memory setting I missed that capped the growth, or released resources after they were finished being used?

MDX and DAX seemed about equal performance wise (in terms of speed and resource usage), but I'd read that running MDX against ROLAP can cause performance issues, for people who haved used both is there really that big of a performance hit?

I have lots more questions, but I'll start with just these. The data is proprietary so it's hard to give detailed implementation diagrams, every relation was PK to FK with one exception where I was forced to use a composite key due to the restrictions against many to many relations. The composite was handled before the ROLAP in the views that the ROLAP was pulling from. I suspect the size of that key might have been one of the things slowing queries down since it was varchar(24). Any advice would be much appreciated, I loved working with ROLAP, and dax was weird but still easier than MDX.


texts: Debug network latency when getting data from oracle

texts:
I have an application which needs to connect to Oracle database to bring my instance up.

Oracle server is running on U.S server, While I am accessing it from India

Sadly it takes an hour to get connected and pull the data back so that my application is up on my local machine.

I need to debug it, why its taking so long time.

Any ideas will be great.


texts:
When I turned off my MySQL server, and turned it back on, it wouldn't read my database anymore. Just a single database, everything else in mySQL was fine.

When I try looking at any of the tables it shows in phpmyadmin, it says:

orderofthebutterfly.tablename does not exist.. (all in lowercase.. even though i've set
lower_case_table_names = 2 in the my.ini config.)

When I try to drop the db, i get this:

1010 - Error dropping database (can't rmdir '.\OrderOfTheButterfly', errno: 41)

I have verified that all the data is still there. There is a file in the MySQL directory with all the tables in it.

I have also deleted the folder and redid the database, and the same thing happened when I turned it on to work with the next day.


texts: Lost my SQL Server 2005 host, SQL Server 2005 database import into SQL Server 2012?

texts: My SQL Server 2005 host died, but the database related LUNS were on a SAN and still exist.  Is there a way to use my SQL Server 2012 host to recover that database or import it in some way?


texts: Unable to create backup with mysqlbackup.exe (MEB)

texts:
I am not able to create a backup with the mysqlbackup.exe
The command:
Z:\path\mysql-5.7.11-winx64\bin>mysqlbackup.exe -u dba --host=127.0.0.1 --backup-image=C:\test\backup --backup-dir=%temp%\test072 backup-to-image

I get the following error message:

mysqlbackup: INFO: Encryption password "encrypt-password" not specified.
mysqlbackup: ERROR: Invalid options, MEB would not be able backup encrypted tables.

The DB backup is intended not to be encrypted.
Added secure_file_priv=NULL to .ini file.


texts: Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE

texts:
If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction.

This is done in order to "lock out" those selected rows from other session's writing/deleting actions (but other sessions can still read the rows) until your transaction completes.

From that point, the rows that were locked with the SELECT LOCK IN SHARE MODE statement are released so other sessions can access them for writing, deleting, etc.

This is exactly what I want for my comments table. Whenever a comment is added to a post on my site, I need to lock all the comment rows tied to that post while I update some meta data on all the locked rows. Additionally, if two comments get submitted at the same time, I don't want them to both have access to the relevant comment rows at the same time because they will basically screw each other (and the meta data) up.

So, I want to incorporate SELECT LOCK IN SHARE MODE into the comment upload script so the first session to run the lock in query gets complete control over the comment rows until it finishes the entire transaction (and the script that was slightly slower has to wait until the entire transaction from the first script executes).

I am a but concerned about creating deadlocks where script A locks data that script B needs, and script B locks data that script A needs.

How can I get around this in my application?
I am using only innodb in my website database, so do I need to worry about table locks?


texts: Is it beneficial to include ORDER BY fields in the list of SELECT fields?

texts: I'm just wondering when you do a query and you use ORDER BY should the fields you use in the ORDER BY clause always be included in the SELECT list even if you don't need them? Is this beneficial in some way?


texts:
I have a created custom Database Role called Managers. Added db_owner to this role. I would like to Deny DROP Database to this custom Role. But the DENY Drop DATABASE TO Managers is not working

Any work around ?


texts:
We have an application running in heroku and we want to add a search feature on a field that contains greek characters and we want to make it accent agnostic.

So the idea is to use postgresql's unaccent functionality. The problem is that by default greek rules are not included at heroku and we somehow need to add them (we already have a rules file).

Has anyone managed to add custom rules files at heroku, and if yes could you please share how?


texts: hstore type does not exist with hstore installed postgresql

texts:
I recently had to copy over a databases structure from one database to another with the command

pg_dump -c -S database_name > pg_dump_date.sql

I then ran the dump file on the new database with

psql < pg_dump_date.sql

And my schemas were recreated, as well as my extensions were installed.

After checking the installed extensions I can see that hstore is installed, but when I try basic operators for hstore like

SELECT id FROM schema_name.table_name WHERE hstore_column->'hstore_key'::TEXT = 'hstore_value'

I get the error that

operator does not exist: schema_name.hstore -> text

I get a similar error if I do not try and cast the key

operator does not exist: schema_name.hstore -> unkown

My first instinct was to simply drop the hstore extension and re-install it, but doing so would also drop my numerous user functions, triggers, and other dependencies on the hstore type.

Short of doing a cascading drop of the hstore exentsion, is there a way that I can fix this error? I am using a postgresql 9.3 server.


texts: Update column based on other column with concat by underscore and manage duplicate value MySQL query for mysql 5.7.27

texts:
I need to update column value based on other column with adding separator and with manage duplicates values using sql query.

For example :

I have one database table A.

Here in it two columns : 1) locality 2) slug

Locality column already have data. So need to update 'slug' column based on locality value. Please review below screenshots.

Currently i have below table.

And i need to update 'slug' column like below screenshot.

I used below sql query but it will not worked on MYSQL5.7
So please help me to convert below query with MYSQL5.7

UPDATE tableA,
( SELECT suburb_id,
         ROW_NUMBER() OVER ( PARTITION BY LOWER(locality)
                             ORDER BY suburb_id ASC ) rn
         FROM tableA ) cte
SET tableA.slug = CASE WHEN cte.rn = 1
                       THEN replace(LOWER(tableA.locality), ' ', '_')
                       ELSE CONCAT( replace(LOWER(tableA.locality), ' ', '_'), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

I know that PARTITION BY and OVER() function not worked into MYSQL5.7
So, please me to create query for above.

Now i created below query.

UPDATE tableA,
(
SELECT
    @row_number:=CASE
        WHEN @customer_no = locality
          THEN
              @row_number + 1
          ELSE
               1
        END AS rn,
    @customer_no:=locality locality,
    id
FROM
    tableA,
    (SELECT @customer_no:=0,@row_number:=0) as t ORDER BY locality) cte
SET tableA.slug = CASE WHEN cte.rn = 1
                       THEN replace(LOWER(tableA.locality), ' ', '_')
                       ELSE CONCAT( replace(LOWER(tableA.locality), ' ', '_'), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

I used above query but not worked properly.
Inner SELECT query worked fine and get right row number for all locality with duplicate value.
But Update query not properly work.
I don't know how to manipulate with right connection for update and inner select query.
Please help me.

I need to output like below :

subrub_id |  locality | slug
-----------------------------
1         |  Post one  | post_one
2         |  Post TWO  | post_two
3         |  Post Three| post_three
4         |  Posts     | posts
5         |  Post One  | post_one_1
6         |  Posts     | posts_1
7         |  Post Four | post_four
8         |  Post Five | post_five
9         |  Post Ten  | post_ten
10        |  Posts     | posts_2
11        |  Post Two  | post_two
12        |  POSTS     | post_3

Solved this by me. See below query if help to others.

UPDATE tableA
JOIN
(
SELECT cte.subrub_id as main_id, cte.post_name, cte.rn, CASE WHEN cte.rn = 1
                       THEN replace(LOWER(tableA.locality), ' ', '-')
                       ELSE CONCAT( replace(LOWER(tableA.locality), ' ', '-'), '-', cte.rn-1 )
                       END AS a  FROM tableA,
(
SELECT
    @row_number:=CASE
        WHEN @customer_no = locality
          THEN
              @row_number + 1
          ELSE
               1
        END AS rn,
    @customer_no:=locality post_name,
    id
FROM
    tableA,
    (SELECT @customer_no:=0,@row_number:=0) as t ORDER BY locality ASC
        LIMIT 0, 10000 ) as cte
WHERE tableA.subrub_id = cte.subrub_id ORDER BY cte.post_name ASC
)
as t
ON tableA.subrub_id = t.main_id
SET tableA.slug =  t.a


texts: Two-way replication between SQL Server 2008 R2 and SQL Server 2008 R2 SP3

texts:
I know that in SQL Server you can't have two-way replication between different versions.

I wanted to know if SQL Server 2008 R2 and SQL Server 2008 R2 SP3 are considered "different versions"?

Did anyone try to build a replication between those 2 versions?


texts: How to decipher the transaction message in innodb status log.

texts:
I have several transactions that seem have stalled. Looking at the innodb status log, I found several messages similar to the following:

---TRANSACTION 69DBBB, ACTIVE 947 sec starting index read, thread declared inside InnoDB 373
mysql tables in use 4, locked 0
MySQL thread id 820, OS thread handle 0x7ef64c454700, query id 625198....

Trx read view will not see trx with id >= 69DBBC, sees < 6699C2

I need help understanding exactly what it means.

Thanks


texts: Unresolvable Ambiguity problem on Join Tables

texts:
I have searched for all answers, but nothing works. Somehow this query gives me the ambiguity error or 'ID':

SELECT wpp.ID AS theid, s.ID AS supplierid, post_title, wp_terms.name AS category
FROM wp_posts AS wpp
LEFT JOIN wp_term_relationships ON wpp.ID = object_id
LEFT JOIN wp_terms ON term_taxonomy_id = term_id
LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID
WHERE post_type = 'post'
AND (post_title LIKE '%bag%'
OR wp_terms.name LIKE '%bag%'
OR post_content LIKE '%bag%')
GROUP BY ID

the problem lies when I added this line:

LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID

But I don't see what I'm doing wrong... Any hints?


texts: Lost entire MySQL Server. Unsure if "master-slave" or "master-master" - How to tell?

texts:
Yup - my db02 server lost a disk, and the array itself is corrupt (RAID5, yet corrupt data.) I was able to get the OS back online, but the corruption is severe.  I have zero documentation about the mysql configuration, other than a copy of the /etc/my.cnf file from the failed host.  (one small joy in this.)
1) How would I determine if it was in a m-s or m-m configuration?
2) Since I am lucky enough to have the /etc/my.cnf file, is there a clean way of restoring from the master?
3) Anyone have any other ideas as to what I should look for, in order to make sure what else needs to be put back in place?  Backups are managed via scripting, from yet another system.


texts:
I just created my first database yesterday. It is now set up and I can write to it from my C# application using the admin user login. I would now like to use a limited write only user so my admin credentials are not stored in my application.

I have created a second login on master using query1

CREATE LOGIN writeOnlyLogin WITH password='myPassword';

I created a user on master using query2

CREATE USER writeOnlyUser FROM LOGIN writeOnlylogin;

I then added this user to the database that has the table I want this user to write to by using query2 with a connection string targeting the new database instead of master.

I granted insert access for this user only on a single table within the new database using query3

GRANT INSERT ON myTable TO writeOnlyUser

The user has no other roles or permissions.

In my c# application I have a datastructure that I want to write to this table. I create the connection string like this and write like this. With the admin credentials the open succeeds. With my new user the open fails.

public void SendToDatabase()
{
    string serverName = "myServer.database.windows.net";
    string databaseName = "myDatabase";
    string tableName = "myTable";
    string user = "writeOnlyUser";
    string password = @"myPassword";
    string sqlConnectionString = $"Data Source={serverName};Initial Catalog={databaseName};User id={user};Password={password};";
    using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
    {
        sqlConnection.Open();
        SqlCommand sqlCommand = HelperMethods.CreateSqlCommandFromObject(this, tableName, sqlConnection);
        int rowsAffected = sqlCommand.ExecuteNonQuery();
    }
}

The Exception message is, "Login failed for user 'writeOnlyUser'."

I am failing to find anything useful through google, however that could be because I don't know what I'm looking for. How can I fix this issue? In general, how can I debug login details?


texts:
I have two table as one is the message table and another one is messageUser table.
Now i need to check before insert a new row.

for example,

  M_MessageMessageId=========
                 1
                 2
                 3

  M_MessageUserMessageId | MemberId | BusinessId
  =========|=========|=========
                 1 |               1 |               0
                 1 |               0 |               2
                 2 |               1 |               0
                 2 |               0 |               2
                 2 |               3 |               0
                 2 |               4 |               0
                 3 |               1 |               0
                 3 |               0 |               2
                 3 |               0 |               4

if a member create a new message, i would like to check the user in this message is there exist before. If yes, then attach the message to previous conversation else create a new conversation.

  Scene 1
  Member 1 sent a message to Business 2, from the table we know that there have a previous conversation which is Message 1

  Scene 2
  Member 1 sent a message to Business 2 & Member 3, from the table we know that there is no previous conversation

I've tried before using UNION, IN for the checking query but basically is just get back all the list. Is there anyone can give me a help? Thanks.

UPDATE

I can solve scene 1 by using but failed to suit scene 2

    SELECT MessageId FROM M_MessageUser
    WHERE (MemberId IN (0,1) AND BusinessId IN(0,2))
    GROUP BY MessageId
    EXCEPT
    SELECT MessageId FROM M_MessageUser
    WHERE (MemberId NOT IN (0,1) OR BusinessId NOT IN(0,2))
    GROUP BY MessageId;


texts: Struggling with json functions when argument is result of select

texts:
I can find a plethora of examples for json_populate_recordset when the argument of json_populate_recordset(base anyelement, from_json json) is a text string such as:

select "values", "Type", "Name"
from json_populate_recordset(null::contact2,'[{"Type":"Email","values":"kneal@someone.com","Name":"WorkEmail"},{"Type":"Email","values":"aneal@somewhere.com","Name":"WorkEmail"}]');

I can't find any help for when that argument is the result of a query such as:

select json_agg(row_to_json((SELECT ColumnName FROM (Select val, valtype, valname) as ColumnName ("Value","Type","Name")) )) as JsonData
from contact ;


texts: How to set a parent from one-to-many relationship?

texts:
I have tables as

CREATE TABLE articles
(
id varchar(10),
parent_id varchar(10),
Text text,
FOREIGN KEY(parent_id) REFERENCES articles(id) ON DELETE CASCADE,
PRIMARY KEY(id)
) ENGINE=InnoDB

CREATE TABLE article_map
(
id varchar(10),
unified_id int(11),
FOREIGN KEY(id) REFERENCES articles(id) ON DELETE CASCADE,
PRIMARY KEY(id,unified_id)
) ENGINE=InnoDB

For each unified_id, there are several ids. The aim is to save all ids in the table article to avoid being added again, but set one of them as the parent, and the reset as children. In this case, the content (Text) is set to NULL for all children, and any request to a child redirects to the parent.

How can I INSERT from table article_map to table articles to set one parent and the reset children of the assigned parent for each unified_id? There is no preference for choosing the parent.


texts: Secure Linked Server - Non privledged user possible? Registry corruption?

texts:
Is it possible to use a non privledged Windows domain account to impersonate itself in a linked server?

And why would it be unable to read the registry for available network protocols?

Overview: Only way I am able to have a scheduled job utilize a linked server is when the local account is mapped to a remote SQL account.  Unable to use 'Impersonate.'

Details:

Two SQL 2008 R2 Std instances on Win Server 2008 R2 x64
One default  + one named
I'll use Server_A_Default + Server_A_Named to refer to the instances
Each instance has it's own AD service account for MSSQL + Agent (4 unique AD accounts in use on server)
Port hard coded for Named instance Server_A_Named
SPNs created for the 2 MSSQL accounts.
SPNs match the default and hardcoded named instance port respectively

Within the named instance (Server_A_Named):

Created a linked server on Server_A_Named to Server_B.  We'll call the linked server SAN-B.

In SAN-B, I've used SQL Nativue Client 10.0 + OLE DB Provider for SQL

Under the Security for SAN-B, I have 3 accounts:

NonPrivADuser
ADuserSysAdmin
LocalSQLuser

For logins not defined, connections will not be made.

As ADuserSysAdmin, I can click on test connection and it works.

Only way to get linked server to work for NonPrivADuser is to have it map to a local SQL account on Server_B
NonPrivADuser has access on Server_B's database as well.

This is the error that NonPrivADuser receives while trying to access the linked server using 'impersonate':

Executed as user: DOMAIN\NonPrivADuser. SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].
[SQLSTATE 42000] (Error 65535)  OLE DB provider "SQLNCLI10" for linked server "SAN-B" returned message "Login timeout expired".
[SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "SAN-B" 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 correct
and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
[SQLSTATE 01000] (Error 7412).  The step failed.

I fired up procmon on Server_A while trying to use the linked server, SAN_B.

SQLAGENT.EXE can read HKLM\SOFTWARE\Microsoft\MSSQLSERVER\Client\SNI10.0
SQLSERVR.EXE receives a BAD IMPERSONATION on the same key.

I fired up regedit and 'users' has read permissions on that key.


texts: Organizing Database Objects in a Database that are not part of the systems original Database Objects?

texts:
I've been working at a place for about 2 years now with a series of heterogeneous legacy database applications, which are tied together using 3rd party Microsoft Access databases (*.mdb).  Soon we will switch them to Postgres in a new integrated system.

The 3rd party *.mdbs worked well for separating out what queries (VIEWs) and which temporary tables belonged to which process for performing a task of some sort; since they were separated into their own files along with the VBA code.

The new system will be similar to the old systems but instead have them integrated together, and there will be less need for such 3rd party databases for the time being; However, should a new need arise, and a new VIEW or temporary table be required in the Postgres database; What is a best practice for grouping a series of VIEWs or temporary tables together so that later it can be known what task/purpose they belong to?

I would think that it would be beneficial to have some sort of a library structure for the VIEWs / temporary tables such that ones used for many different tasks are separated into:

common (used for many different tasks)
task1Views (specific to task1)
task2Views (specific to task2)

I'm trying to recreate the functionality in Access where a list of queries (VIEWs) and tables related to the task at hand are listed on the left side and are searchable only for that task.  And also to prevent confusion among tables that already exist for the system (linked tables in Access) and tables that I am using to perform a task.


texts: SQL Server: Replicated transactions are waiting for the next Log back up or for mirroring partner to catch up

texts: I have two SQL Server DB instances on the same machine with one hosting a replica of the other. In addition, I have transaction replication setup for all tables on the main instance to the replica. I have verified that the log reader and associated agents are all running. However, one of the log reader agent says "Replicated transactions are waiting for the next Log back up or for mirroring partner to catch up". I have done a transactional log backup to trigger it but to no avail; tried deleting the subscriber and publisher and recreating it again…but still end up with this message.


texts: ODBC connection for testing Availability Group Listener

texts:
I am having difficulty verifying my Availability Group Listener configuration.  I would like to create an ODBC connection which points to the listener.  When I configure the system DSN and try to test the connection at the end, I get timeout errors.  However, when I change the server to point to the instance name instead of the listener name, the connection works.  How can I test the listener using ODBC? What other method can I use to test the listener?

Note: I do not have access to an application to test client connectivity.


texts: Error purging PostgreSQL database. Reason: ERROR: syntax error at or near "exists"Position: 15

texts:
I am using PostgreSQL database with Tomcat Server. When I checked webpage today, it shows about mentioned alarm with following logs:

2018-11-05 01:06:05,628 [1AM Thread] DEBUG com.swhh.csta.d.c.k - Purging Devices
2018-11-05 01:06:05,628 [1AM Thread] WARN  com.swhh.csta.a.n - REFRESH: TSAPI service down.
2018-11-05 01:06:05,628 [1AM Thread] WARN  com.swhh.csta.a.n - REFRESH: TSAPI service down.
2018-11-05 01:06:05,628 [1AM Thread] WARN  com.swhh.csta.a.n - REFRESH: TSAPI service down.
2018-11-05 01:06:05,628 [1AM Thread] DEBUG com.swhh.cs.e.u - No lock folder to check yet
2018-11-05 01:06:05,628 [1AM Thread] INFO  com.swhh.cs.uarchive.h - Purging and checking for historic archive runs
2018-11-05 01:06:05,628 [1AM Thread] INFO  com.swhh.cs.uarchive.h - Purging archive \\ip-address\archive3\
2018-11-05 01:06:05,643 [1AM Thread] INFO  com.swhh.cs.e.au - Purging recordings (except those locked) prior to Nov 5, 2013 1:06:05 AM
2018-11-05 01:06:05,659 [1AM Thread] INFO  com.swhh.cs.e.au - Retaining 0 contacts due to locked records from before purge point.
2018-11-05 01:06:05,659 [1AM Thread] INFO  com.swhh.cs.e.au - Retaining 0 contacts due to locked records from before purge point and/or spanning the purge point.
2018-11-05 01:06:05,659 [1AM Thread] ERROR com.swhh.cs.a.p - Alarm raised err.database.purge [ERROR: syntax error at or near "exists"
  Position: 15] [null]
2018-11-05 01:06:05,659 [1AM Thread] INFO  com.swhh.cs.e.au - Daily House-keeping completed


texts: In the Import/Export Wizard inside SSDT, why is the MSAccess DB Engine data source looking for a workgroup information file?

texts:
Running MSSQL12, VS10 Shell, MSA2013 with local .accdb files, Microsoft Access Database Engine 2010 Redistributable (x86_64) and Microsoft Access Database Engine 2007 Redistributable (x86) installed.

While trying to import to MSSQL2012 from SSDT using the wizard from an Access 2013 DB (.accdb) which is password protected, I get the following error with the username blank and the password field correctly populated:

Cannot start your application. The workgroup information file is missing or opened exclusively by another user. (MicrosoftOffice Access Database Engine)

But according to this link:

http://office.microsoft.com/en-us/access-help/what-happened-to-user-level-security-HA010342116.aspx

MSA10 and beyond shouldn't need workgroup information files, although it's not clear to me if they are created/used or not.

I can point the wizard to some local version of system.mdw at Advanced -> All -> Jet OLEDB:System Database, and then the error becomes:

Not a valid account name or password. (Microsoft Office Access Database Engine)

for any username I can think of.

I have discovered, serendipitously, that using the SQL Server Import and Export Wizard from the start menu (Import and Export Data (64-bit)), this problem goes away and the export proceeds normally.

That is, I select as data source "Microsoft Access (Microsoft Access Database Engine)", then my .accdb file.  In this case, it does not even ask for a password with the password field blank.  This suggests that it is not looking for a workgroup information file, as expected for a .accdb from MSA07 or later.

Also, I see that this problem is not limited to the wizard inside SSDT.  If I try to add a Connection Manager using the "Native OLE DB\MS 12.0 Access DB Engine OLE DB Provider", and then select my .accdb in "Server or File Name", when I "Test Connection" I get

Test connection failed because of an error in initializing provider. Unrecognized database format 'C:\myDB.accdb'.

Questions:

Why does this data source insist on looking for a workgroup information file even though user-level security should be disabled?
Why does the Open dialog box for the Microsoft Access Database Engine have *.mdb as the only selectable filetype?
How can I get around these errors for an .accdb that is password protected?
Why is SSDT behaving differently from MSSQL?  I need SSDT to work to use SSIS.

Thanks.


texts: SSIS - Fixed width flat file mapping tool/technique

texts:
I am wondering if there is a tool (or a special technique) that anyone has used to map fixed width flat files in SSIS Flat file connection managers?

Usually I just use the Flat File Connection Manager Editor (Advanced tab) and start adding the column info (name, width, datatype etc.) for all the columns 1 by 1.

This has worked fine for me in the past but now I have to map a fixed width file that has close to 500 columns...

I am thinking (hoping) that there should be a tool or technique I can use that will allow me to accomplish the mapping in a more stream lined fashion...

Using the data dictionary I was provided for the file I could easily create an excel (or text) document for example with a few columns that includes the necessary information SSIS needs (Column Name, Delimiter,InputColumnWidth, OutpuColumnWidth, DataType) to map the file.

I am wondering if there is an SSIS tool or component that can then read this file and automatically create the mappings in the Flat File Connection Manager for me...

If there is no such tool, does anyone have any tricks or tips that might be able to help me map the file in the most efficient manner?

I am wondering if maybe I can modify the SSIS package xml file in a way where I could just create some code that scripts out the column info for all the columns in xml format and then I could just manually copy and paste it into the package xml file... not sure if this would work....

Note: After mapping the file in SSIS, it will then be loaded into a SQL Server table


texts: Oracle RAC failover not working correctly

texts:
I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

Connect to the database and check which instance I am connected to, eg (on SQL*Plus):

select instance_name, host_name from v$instance;

Shut down the instance I am connected to using shutdown abort (in a separate session)
On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: XXXXX
Session ID: YYY Serial number: ZZ

What's going on?

  Note: After step 2, I can exit out of SQL*Plus and reconnect just fine. It just won't work if I try to stay on the same session.

If I modify the tnsnames.ora in the client to something like:

RAC =
  (DESCRIPTION =
    (LOAD_BALANCE = YES)
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
       (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5)
       )
    )
  )

Then it works the way I expect it to. And this is what had to be done prior to 11gR2, but SCAN is supposed to handle all this automatically, so a simple entry like:

RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Should work the exact same way.


texts: SQL Server 2012 - where is the table in dbo?

texts:
I am trying to insert something into the dbo.Documents table.
 After the query is run, I can't find the Documents under the TSQL2012 database.
 I created TSQL2012 for exercise purpose.

I couldn't find the table Documents created in the TSQL2012 database. Please give me some advises on where this table is created. Thanks!

Here is the query:

 INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Additivity of Measures',
 N'docx',
 N'Additivity of measures is not exactly a data warehouse design problem.
   However, you have to realize which aggregate functions you will use
   in reports for which measure, and which aggregate functions
   you will use when aggregating over which dimension.',
 bulkcolumn
FROM OPENROWSET(BULK 'C:\Users\admin121\Downloads\9780735666054_Files\Chapter 06\AdditivityOfMeasures.docx',
                SINGLE_BLOB) AS doc;


texts: Adding 4th node in MySQL group replication stuck at RECOVERING

texts:
Environment:

OS: Debian 9

MySQL: 5.7.23 (Running in group replication cluster)

Issue:

The cluster is running fine with 3 mysql nodes in group replication and all are working fine.
While adding a 4th node in the group replication cluster, the 4th node is able to connect to the master and replication starts, however, the 4th node always shows as "RECOVERING".
I checked the logs but there was no error in the logs.

Since the 4th mysql node is able to connect to the other members and able to replicate the data, I am sure that there is no issue in the mysql config.

One of the suspicion is that it is somewhere related to the binlog events. If I check the count of 'SHOW BINGLOG EVENTS', it shows almost 141957 rows, however, the other nodes are having haardly 3-4 rows.

mysql> SHOW BINLOG EVENTS;
141957 rows in set (10.74 sec)

Please let me know how can I add the 4th node in mysql group replication cluster.


texts: Join multiple tables for aggregate or create a single table with combined attribes

texts:
I have the following ER diagram.

Now i have option of going with a single table ARTDATA as mentioned,
or,
I can go with 13 different tables linked with each other with relations.

Problem is for each art i am gonna have to display all the attributes, i.e. For each ART i'll have to fetch TYPE, AUTHOR, TIMEFRAME, FORM, LOCATION AND SCHOOL. And for that i'll have to join all these tables together and that might take time.
However i can always go with the alternative of single table ARTDATA and get everything in one place.

Given my data set is large (50000+ entries) and will grow in future, what would be best solution to go with?

Assuming i am going with 13 tables, what would be the best query to merge all tables? I'll put my pathetic attempt below.

select
t1.ID,
t1.TITLE,
t1.DATE,
t1.TECHNIQUE,
t1.URL,
t3.AUTHOR,
t3.BORN_DIED,
t5.FORM,
t7.LOCATION,
t9.SCHOOL,
t11.TIMEFRAME,
t13.TYPE

from
ART t1 inner join

ART_AUTHOR t2 on t1.id=t2.art_id inner join
AUTHOR t3 on t3.id=t2.author_id inner join

ART_FORM t4 on t1.id=t4.art_id inner join
FORM t5 on t4.form_id=t5.id inner join

ART_LOCATION t6 on t1.id=t6.art_id inner join
LOCATION t7 on t6.location_id=t7.id inner join

ART_SCHOOL t8 on t1.id=t8.art_id inner join
SCHOOL t9 on t8.school_id=t9.id inner join

ART_TIMEFRAME t10 on t1.id=t10.art_id inner join
TIMEFRAME t11 on t10.timeframe_id=t11.id inner join

ART_TYPE t12 on t1.id=t12.art_id inner join
TYPE t13 on t12.type_id=t13.id;

You get the gist, that is barbaric.

UPDATE:

I have updated the tables and created a view called ARTDATA using above query. Now here is the update ER.


texts: What does TBSCAN GENROW mean in Explain Plan?

texts:
In a DB2 Explain Plan, what does TBSCAN GENROW (line 5 below) mean?

SELECT STATEMENT ( Estimated Costs =  1.811E+03 [timerons] )
   1 RETURN
      2 NLJOIN
          3 [O] TBSCAN
              4 SORT
                  5 TBSCAN GENROW
          6 [I] FETCH EKKO
              7 IXSCAN EKKO~1 #key columns:  2

The IBM docs say GENROW is

  "A built-in function that generates a
  table of rows, using no input from
  tables, indexes, or operators"

What does that mean from a performance perspective?

Also, this is in an SAP system if that matters.


texts: Better parallelization than a sequence container in SSIS 2008?

texts:
We currently have a process that has to process hundreds of thousands of rows an hour.  For valid reasons, it has to call a stored procedure for each row. We're using a SSIS package (SQL Server 2008) to run this in parallel.  If there's 400k rows to do, we divide that by 10, then tell each SQL Task within the container (we have 10 tasks, so that each one basically monopolizes a CPU) to do rows 1-40000, 40001-80000, etc.  Naturally, not all rows are the same, so we frequently have 8 of the 10 SPIDs idle, waiting for the last 2 to finish, so that the job can start again and process the rows that have come in since it started processing.

There has GOT to be a better way to do this.  I can't move this to SSIS 2012/14 yet, and it's CPU bound so I can't run the SSIS package on a different box (plus, overhead would probably be nightmarish).  Also assume I'm not doing it in Service Broker.

One idea we'd had is to save each block of 1000 as a row to a table, then have multiple jobs running.  Each would process a block of 1000, then claim a new row (and thus the next 1000) when they finish.  No need for SSIS, either.  But I'm unsure how the overhead/development cost of doing that would compare to a simple design change in SSIS.

Thanks in advance!


texts:
I have a table with fallowing columns:

id  device_id (string) used_at (datetime) app_id (string) duration (int)
1   device-1           2018-07-20         chrome          30
2   device-1           2018-07-19         chrome          1
3   device-2           2018-07-19         chrome          2
4   device-2           2018-07-20         facebook        100
5   device-1           2018-07-20         chrome          20
6   device-3           2018-07-20         chrome          10
7   device-1           2018-07-20         facebook        5

I need to get list of ten most used app_id for specified device-1 and used_at this list should have duration sum

So I should get for device-1 and uset_at >= 2018-07-18:

device-1 chrome duration (51)
device-1 facebook duration (5)

Anyone can suggest sql ?


texts:
I've got a MySQL database (MyISAM) that handles a lot of read/writes and uses temporary tables quite frequently.

Recently it's started having massive load spikes at peak load, and noticed that there were a lot of temporary tables being written out to disk in the /tmp directory.

I've increased the key_buffer variable to 4GB (was 16MB) and tested performance with JMeter, but to my surprise, while the website seems slightly faster, JMeter data is reporting the change has increased response times by a 100's of milliseconds.

Is there something I'm missing or other variables I need to tune to stop MySQL writing tables to disk?

This server has 16GB RAM.


texts: Sys.dm_io_virtual_file_stats shows high latency for tempdb writes

texts:
We just got a new SAN and I was looking at sys.dm_io_virtual_file_stats to make sure we are seeing an improvement compared to our last SAN. I noticed that all the tempdb files are seeing avg write latencies of about 350ms. This is still an improvement as our last SAN had stats of 1000ms avg write for tempdb in sys.dm_io_virtual_file_stats but not as good as I hoped.

Looking at the win.disk.duration perfmon counter on our tempdb drive for the past three days (since boot) the avg disk latency is < 1ms. Sometimes there are spikes to 150ms for one interval (30 seconds).

Looking at our SAN's monitoring tools it says the LUN has an avg write latency of 0.5ms.

VMWares stats are pretty much inline with the win.disk.duration perfmon counters.

Any pointers to where I should look at next to work out what is causing the discrepancy between Sql Servers io stats and perfmon/vmware/san monitoring stats?


texts: Row level security returning no error when blocking all effects of a query

texts:
With row level security enabled on tbl, but no policy set, as a user the UPDATE throws no error but does not modify the value:

UPDATE tbl SET col = 1;
=> Query returned successfully: 0 rows affected
SELECT col FROM tbl;
=> NULL

If row level security is disabled on tbl, as a user:

UPDATE tbl SET col = 1;
=> Query returned successfully: 1 row affected
SELECT col FROM tbl;
=> 1

I would expect to get something like: ERROR: permission denied for relation tbl instead of a kinda silent 0 rows affected.

Can I get something more explicit?

Using PostgreSQL 9.6.3 on CentOS 6.

Edit: Row level security is the cause, but I'm not clear why there is no error.


texts: Is there a limit to how many credentials SSMS can remember?

texts:
Using SQL Server Management Studio that came with SQL Server 2008 R2, I make connections to the same server using a number of different sets of SQL Server Authentication credentials. On my development machine, I use the Remember password checkbox extensively to avoid having to look up the password each time.

When I choose a Login which I have previously connected with (using Remember password), the Password entry field gets populated with asterisks, and clicking Connect allows me in - most of the time. But sometimes, with no pattern that I can discern, on choosing a previously-used Login, the Password field will prepopulate with asterisks, but clicking Connect tells me the login fails. I enter the password, click Connect, and get in, and then typically the next time I use the same login it will work.

My suspicion is that there is a maximum number of credentials that SSMS is prepared to remember, and only the most-recently-used n credentials are actually remembered, but I can't find any mention of such behaviour in the documentation.

Is there a known limit on how many credentials SSMS will remember?


texts: Adding a data file to existing sql server database taking too long

texts:
I am trying to add file using below

USE [master]
GO
ALTER DATABASE [foo] ADD FILE ( NAME = N'foo_1', FILENAME = N'D:\Data10\Data\foo10.ndf' , SIZE = 102400000KB , FILEGROWTH = 0) TO FILEGROUP [PRIMARY]
GO

Actually i have to add 2 TB file to this 3 TB disk, but trying to add in chunks of 100 GB

But it seems to be running for past 30 mins

HOw can i improve this?

Update:

The Query above completed in 40 mins. The wait was ASYNC_IO_COMPLETION

Also, this database is 24*7 and has DB mirroring configured.

Now, i am trying to increase in 50 GB chunks using below

USE [master]
GO
ALTER DATABASE [foo] MODIFY FILE ( NAME = N'foo_10', SIZE = 153600000KB )
GO

Above seems to be running for past 10 mins with wait (183043ms)PREEMPTIVE_OS_WRITEFILEGATHER


texts: Is it a good way to use an auto increment integer field as the primary key?

texts:
In database design, is it a good way to use an auto-increment integer field as the primary key?

For Example, I have a tenant table, tenant_id is unique. In this case, do I need a auto_increment key?

What the benefits of the auto_increment primary key and when to use it?

Thank you.


texts:
I am developing a social networking website, where I have 3 tables - 1st for users(userid- pk,auto increment,username,etc.), 2nd for Album(albumid - auto increment,userid - of person who owns the album) and 3rd for photo(photoid - auto increment,albumid -where photo belongs to,path- to photo in disk). Now, Before inserting into photo, my trigger checks if the user owns the album given by album id. The trigger is created using MySql workbench which gives the following:

USE `mydb`;
DELIMITER $$
CREATE TRIGGER `photo_BINS` BEFORE INSERT ON `photo` FOR EACH ROW
BEGIN
    DECLARE found_it INT;

    SELECT 1 INTO found_it FROM album WHERE albumid= NEW.albumid and userid=@userid;
    IF found_it!=1 then
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User does not own album';
END IF;
END

I am passing the value to @userid by php as follows:-

$con=NULL;$stmt=NULL;
$con=mysqli_connect('localhost','username','pswd','mydb');
$stmt= mysqli_prepare($con,'set @userid=?');
mysqli_stmt_bind_param($stmt,'i',$user);
mysqli_stmt_execute($stmt);

$stmt= mysqli_prepare($con,'insert into photo(albumid,path) values(?,?)');
mysqli_stmt_bind_param($stmt,'is',$albumid,$path);
mysqli_stmt_execute($stmt)'
$pid=mysqli_insert_id($con);

The problem is that everytime the value gets entered. Even when I oppose the condtion i.e. fount_it=1 , the insert always takes place. Other variant I've tried is:

if(SELECT 1 FROM album WHERE albumid= NEW.albumid and userid=@userid)!=1 THEN

... still the insert always takes place. Please help.


texts: On Heroku in production, how can I copy database A to database B on command?

texts:
From my local terminal, I can use the following commands to do what I need to do:

heroku pgbackups:capture --expire

heroku pgbackups:restore charcoal --confirm max-kiosk

However, I want to do this from the controller of my rails app, when the Version model is udpated. Locally, I'm using:

env = Rails.env
%x{pg_dump -Fc master_bankkiosk_#{env} > database.dump}
puts "I'm dumping..."
%x{pg_restore --clean -d bankkiosk_#{env} database.dump}
puts "I'm restoring..."

I have come to understand that running heroku commands on the heroku server do not work (because the toolbelt is not installed there ?)

I've tried to use pg_dump command on heroku server as well. I've read a few SO questions that seems similar but the OP are satisfied with answers that include heroku pgbackups:blah, and I can't use that.


texts: Error creating scalar function "Invalid Object Name dbo.fnMyFunctionName'"

texts:
I am using SQL Server 2008 R2. I tried creating a new scalar function. The function gets created and I see it in the list of scalar functions.

When I try to modify function, function name is underlined in red and the mouseover text says

  Invalid Object Name 'dbo.fnGetXName'

If I write a query and try to ref any function, intellisense shows no functions after typing dbo.. It doesn't seem to matter what I name the new function, the same issue arises


texts: Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?

texts:
Much has been written about the perils of Scalar UDFs in SQL Server. A casual search will return oodles of results.

There are some places where a Scalar UDF is the only option, though.

As an example: when dealing with XML: XQuery can't be used as a computed column definition. One option documented by Microsoft is to use a Scalar UDF to encapsulate your XQuery in a Scalar UDF, and then use it in a computed column.

This has various effects, and some workarounds.

Executes row by row when the table is queried
Forces all queries against the table to run serially

You can get around the row-by-row execution by schemabinding the function, and either persisting the computed column, or indexing it. Neither of those methods can prevent the forced serialization of queries hitting the table, even when the scalar UDF isn't referenced.

Is there a known way to do that?


texts:
It's possible to connect SQL server file (mdf) with R tool in visual studio 2017? I tried that many times but the connection failed.

From visual studio2017, I want to connect R with Sql server database.
I created R project then from R (tools--> Data --> Add database connection --> Attach database file) but connection failed.


texts: Configuring pg_admin 9.6 while there are users using it on Windows Server 20XX?

texts: I read something about pg_ctl reload (I'm not sure if that's on the Windows distro of pg)...also is there a way to verify the configuration isn't going to break it completely, like apachectl configtest?


texts: Why is an aggregate query significantly faster with a GROUP BY clause than without one?

texts:
I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one.

For example, this query takes almost 10 seconds to run

SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1

While this one takes less than a second

SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1
GROUP BY CreatedDate

There is only one CreatedDate in this case, so the grouped query returns the same results as the ungrouped one.

I noticed the execution plans for the two queries are different - The second query uses Parallelism while the first query does not.

Is it normal for SQL server to evaluate an aggregate query differently if it doesn't have a GROUP BY clause? And is there something I can do to improve the performance of the 1st query without using a GROUP BY clause?

Edit

I just learned I can use OPTION(querytraceon 8649) to set the cost overhead of parallelism to 0, which makes makes the query use some parallelism and reduces the runtime to 2 seconds, although I don't know if there's any downsides to using this query hint.

SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1
OPTION(querytraceon 8649)

I'd still prefer a shorter runtime since the query is meant to populate a value upon user selection, so should ideally be instantaneous like the grouped query is. Right now I'm just wrapping my query, but I know that's not really an ideal solution.

SELECT Min(CreatedDate)
FROM
(
    SELECT Min(CreatedDate) as CreatedDate
    FROM MyTable WITH (NOLOCK)
    WHERE SomeIndexedValue = 1
    GROUP BY CreatedDate
) as T

Edit #2

In response to Martin's request for more info:

Both CreatedDate and SomeIndexedValue have a separate non-unique, non-clustered index on them. SomeIndexedValue is actually a varchar(7) field, even though it stores a numeric value that points to the PK (int) of another table. The relationship between the two tables is not defined in the database. I am not supposed to change the database at all, and can only write queries that query data.

MyTable contains over 3 million records, and each record is assigned a group it belongs to (SomeIndexedValue). The groups can be anywhere from 1 to 200,000 records


texts: Mysql memory usage increasing after upgrading to 8 and converting some tables from MyISAM to InnoDB

texts:
I am having an issue with MySQL gradually consuming more memory every day which seems to have coincided with converting some tables from MyISAM to InnoDB two weeks ago. You can see the trend in memory usage .
I am running a server with 4GB RAM and a LAMP stack. My web site traffic hasn't changed much over this time. The traffic is very seasonal though and typical %MEM for mysqld in previous years at this time was in the 15-20% range.
Also of note is that I upgraded to MySQL 8.0 in November (prior to the memory trace shown in the link). I guess I thought the upgrade and conversion of most tables to InnoDB would help performance. And performance is fine, but this memory increase is troubling and I assume it will eventually degrade performance.
Here if my my.cnf:
[mysqld]
disable-log-bin=1
default-authentication-plugin=mysql_native_password
default-storage-engine=innodb
innodb_file_per_table=1
innodb_buffer_pool_size=1024M
innodb_log_file_size=128M
max_connections=500
# query_cache_type=2
skip_name_resolve
performance_schema=off
key_buffer_size = 300M
max_allowed_packet=128M
open_files_limit=40000
local_infile=on

Any guidance would be appreciated!
Update (Jan 1 2021):
I made the following changes...

Converted remaining tables to InnoDB
Made data types/collation consistent across all tables
changed innodb_buffer_pool_size 1024M -> 1280M
changed key_buffer_size 300M -> 10M
turned performance schema on

I restarted mysql about 12 hours ago and memory usage is already at where it was before the restart and is continuing to increase linearly with time. CPU usage was always pretty low, but it's even marginally lower with the improvement in join efficiency. I am puzzled.
One thing I noticed is that I set the buffer pool to 1280M, but Innodb_buffer_pool_pages_total is shown as 131072 which means the the buffer pool size is actual 2GB. Can I not set the pool to a value between 1GB and 2GB?


texts: Question on compression for very large tables in sql server

texts:
I have a confusion on how this compression feature works in SQL server:
For quite some large tables , ones with over TB, we recently implemented PAGE level compression.
Example of how it was done:-
Use [DBname]

ALTER TABLE dbo.sampletable REBUILD PARTITION =1  WITH (DATA_COMPRESSION = PAGE);

ALTER TABLE dbo.sampletable REBUILD PARTITION =2  WITH (DATA_COMPRESSION = PAGE);

and so on...
The tables in question were all partitioned table.
When checking the details of compression i see at table level ( Clustered index ones) it shows compression =PAGE but for existing non clustered indexes , compression says = NONE
Queries i have used are from here and here
I am not sure what should i interpret from above if compression is enabled at table level or needs to be enabled at index level as well?
Is there space and performance benefit in case we should also consider compressing NC indexes as well? Basically what would be my ideal case here to go with compressing them as well?
Thanks


texts: What is wrong with table_cache hit rate?

texts:
In my.cnf I have:

table_cache            = 524288
open_files_limit        = 65535

Both are at max allowed value for mysql config. Both are less than max open file limit:

# cat /proc/sys/fs/file-max
2097152

MySQL Variable Status:

mysql> SHOW GLOBAL STATUS LIKE 'open%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Open_files               | 193    |
| Open_streams             | 0      |
| Open_table_definitions   | 594    |
| Open_tables              | 802    |
| Opened_files             | 537248 |
| Opened_table_definitions | 4895   |
| Opened_tables            | 9174   |
+--------------------------+--------+
7 rows in set (0.00 sec)

Server has 32GB RAM. Mostly free!

Still, when I run mysqltuner script:

It says:

  [!!] Table cache hit rate: 13% (853 open / 6K opened)

Any reason table_cache hit rate is poor?


texts: quotename(object_schema_name(@objectId)) returns null when called as a synonym

texts:
I have a central utility database in each SQL Server instance, called Utility. In it, I have a bunch of centralized functions and procedures that I call from other databases.

A function I have in it is called GetFqn:

create function [dbo].[GetFqn](@objectId bigint)
returns varchar(100)
as
begin
    declare @fqn varchar(100);
    select @fqn = quotename(object_schema_name(@objectId)) + '.' + quotename(object_name(@objectId));
    return @fqn;
end

When I call this function from another database it returns NULL:

select Utility.dbo.GetFqn([object_id])
from sys.tables

What should I do to make it work?


texts: Why does the optimizer choose nested loops over merge joins here?

texts:
I have 3 tables. #a is main one and two secondary tables, #b and #c.

create table #a (a int not null, primary key (a asc)) ;
create table #b (b int not null, primary key (b asc)) ;
create table #c (c int not null, primary key (c asc)) ;

insert into #a (a)
select x*10 + y
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(x)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))y(y) ;

insert into #b (b)
select a from #a where a % 5 > 0 ;

insert into #c (c)
select a from #a where a % 4 > 0 ;

If I join main table #a with only one secondary table, there will be Merge Join in query plan.

select *
from #a a
inner join #b b on a = b ;

But if I join main table #a with both secondary tables, there will be only Nested Loops.

select *
from #a a
inner join #b b on a = b
inner join #c c on a = c ;

Why does it work like that and what should I do to get two Merge Joins?

Without inner merge join hint.


texts: SQL query - How to select to display group, group total, grand total?

texts:
I've seen some advice but want to know the best way to select a group, group total, grand total from a data table WITHOUT using a subquery or unnecessary join.

My initial thought was something like this:

select   product_family,
         sum(widgets),
         sum(widgets) over ()
from     table.widget
group by product_family

or the following:

select   product_family,
         sum(widgets),
         sum(widgets) over (partition by all_field)
from     table.widget
group by product_family

Obviously neither of these works. I know a partition can be of a higher order than the actual rows/ groups, but not how to partition by "all" other than leaving it blank as the first example. However, it breaks with the group by statement.

Best I can find is something like this:

select product_family,
       family_sum,
       sum(family_sum) over () as grand_sum
from (
      select   product_family, sum(widgets)
      from     table.widget
      group by product_family
     ) as A

That still involves a subquery though, which is okay. I just feel like I'm missing an easy function here.


texts: List Postgresql foreign data wrappers without the use of the `psql` command

texts:
As seen in a stackoverflow answer I can use the psql command and the \dew[+] command in order to list the foreign wrappers.

But the use of universal database tools such as DBEaver, DataGrid, etc make our lives easier. Using these tools how I can list all the available data wrappers and how I can find out whether they are used?


texts: How to extract certificate attributes from a single column and split into many?

texts:
In my table I've a field (let say cert_attr) which stores Certificate X.509 Attributes.
Here is the example of 3 rows (each line corresponds to a field):
"CN=User1, OU=Eng, O=Company Ltd, L=D4, S=Dublin, C=IE"
"CN=User2, OU=Eng, O=Company Ltd, L=D2, S=Dublin, C=IE"
"OU=Eng, O=Company Ltd"

And I'm trying to split the value of a field into separate columns using SELECT in the following way:
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "CN=", -1), ", ", 1) as CN,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "OU=", -1), ", ", 1) as OU,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "O=", -1), ", ", 1) as O,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "L=", -1), ", ", 1) as L,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "ST=", -1), ", ", 1) as ST,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "C=", -1), ", ", 1) as C
FROM mytable

which works, however there is an issue for the rows which are missing some attributes.
So in the case where the attribute is missing in the field's string, I expect the column to be empty, but it returns the whole string instead.
The first two row examples are working as expected, which returns the following columns correctly:
| CN    | OU  | O           | L  | S.     | C  |
| ----- | --- | ----------- | -- | ------ | -- |
| User1 | Eng | Company Ltd | D4 | Dublin | IE |
| User2 | Eng | Company Ltd | D2 | Dublin | IE |

The problem is with the 3rd row example, which I expect to return an empty string when the substring pattern is not found:
| CN         | OU  | O           | L          | S.         | C          |
| ---------- | --- | ----------- | ---------- | ---------- | ---------- |
| OU=Eng,... | Eng | Company Ltd | OU=Eng,... | OU=Eng,... | OU=Eng,... |

but instead the whole string is returned.
Question:
Is there any way to return an empty string when SUBSTRING_INDEX() fails to find the substring? Or maybe there is some other function (like a regular expression) or another workaround?
My goal is to extract the data into TSV file by having these attributes in separate columns with valid values:
mysql mytable < query.sql > cert_attributes.tsv


texts: How to enable sharding for all databases/collections by default

texts:
We are doing sharding process for 6 servers which includes 1 master process, 2 config process, 6 sharding processes.
We enabled the sharding by doing sh.enableSharding(database_name) and sh.shardCollection('mydb.collection_name', {_id: hashed}) It worked.
Now, if i add any other database/collection to the cluster, is there any way that, the database/collection get sharded automatically.


texts: How should I Optimize storage for this table?

texts:
I'm using MySql to store a basic table of this format:

    id      int(11)          //Auto-Incrementing ID
    data    varchar(5120)    //Random input data, compressed by a program, not mysql.
    -----------------------
    Row size ø  916 B

That's kind of troubling, because right now I have about 5,000 records each month, and I'd like to optimize that better than ending up averaging out at 1mb/1000 records.

I set it up this way originally because I wanted to be able to capture the larger sets, but they're very infrequent, as you can see here by this graph:

Link to graph API

count   n*128 bytes
+-------+----------
1       28
1       26
1       24
2       22
8       21
4       20
13      19
12      18
16      17
27      16
43      15
58      14
69      13
114     12
184     11
262     10
399     9
588     8
807     7
1224    6
1245    5
546     4
73      3
9       2
6       1
1       0

My main concern here is how much space I've wasted just to have accommodated the tail end of that chart, and what happens when I end up with even larger outliers? I'll have to bump up my row size again, when roughly 80% of the data fits very nicely into a varchar 1024 block, 1/5th the size of what I'm using now.

So how should I have built this thing in the first place?


texts:
I'm trying to create a database to store values for Profit & Loss statements and I'm wondering what the best way to structure this would be. Specifically, amounts that are are equated from other numbers such as: operating income = gross income - operating expenses, and also operating expenses = wages + rent.

Should there be a field for each value, even if it is a result of an equation, or should I only keep the non-equated values, and equate the others when querying or pulling the data into a report?

In the former I would have to more fields and there's potential for the summed numbers to be entered incorrectly, the latter seems like it would have a complex structure, and would need complex queries. Keep in mind that the P&L statement I'm actually working with has many more accounts and summed accounts.

Is there a best practice for this, or place to get more info on this? I really appreciate the help


texts: Execution plan doesn't show memory grant

texts:
I installed SQL Server 2019 on a VM on Azure (DS11). I am trying to illustrate adaptive query processing using this post here. The issue is that when I hit  CTRL+L in SQL Server Management Studio to look at the execution plan I can't see the memory grant there.

My question now is whether I can only see the memory grant if I am logged in as an admin? Or does SQL Server 2019 have a complete different query engine than SQL Server 2017?


texts: How to manage very large amount of data in mysql

texts:
I have a table in MySQL in which I write logs of users in a specified format. It have just 2 columns:id (auto-increment), log. Currently data is very less, so I'm using this.

Now there is a requirement which involves insertion of very large amount of data, (may be 60-70x of current), that may even involve concurrent insertion of even approx 2000 record. It will obviously slow down our system, and cant be used in production environment. Moreover database size will become an issue.

I need some ideas that will serve my purpose.


texts: What big limitations should I expect from Linked SQL Servers?

texts:
Our product is based on Microsoft SQL Server. Currently, we are using three databases, and have always deployed them on one SQL Server instance.
The three database are OLTP, OLAP, and audit. The OLAP database has massive inbound data on EOD from both OLTP and audit, using cross database queries.
Questions
If we were to deploy these three databases onto three separate Standard Edition instances inside a single physical server, and bind them together using the Linked Server feature of SQL Server:

How transparent will it be to the application code? How much change should I expect?
Inbound data to OLAP amounted in 50k-100k rows, 200-500MB payload per EOD. How much performance drop should I expect?
What other big limitations should I expect?

Background
Currently we are pitching our potentially first client with 500+ concurrent users.
We are drafting a server spec, which includes 64 cores and 256GB RAM.
For SQL Server to utilize all those abundant resources, the client would have to buy Enterprise Edition, which for SQL Server 2016 is only available in per-core based licensing.
We are afraid that the licensing cost alone (64 x $7400) will put them down. So I am thinking of splitting the database into three instances of Standard Edition, and have them linked together, hoping that the linkage feature will be transparent from the application code.


texts: Analysis services access through MS Excel- permissions issues

texts:
I have an instance of Analysis Services set up with my cube and am attempting to access it through MS Excel.

This works for one of my accounts but not for another. My goal is to get the second account to be able to access the cube just like the first. However, I am not sure what permissions I am missing for the second.

I have added the second user as an administrator on Analysis services and even on the SQL Server instance and yet can't get past the below error.

How does my second account need to be set up to access the cube?

The server name looks like: https://[server]/OLAP/msmdpump.dll


texts: Which SQL Server product does version 622 represent?

texts:
I've manually attached the data and log files I received from a client to a SQL Server 2005 Express SP3 database.  During the upgrade to SP4, I get the following error:

  The database 'MyDatabase' cannot be opened because it is version 622. This server support version 612 and earlier. A downgrade path is not supported.

Does anyone know what database version 622 is and how I can install it? This database is from a Canadian customer so maybe it comes from an international version of SQL Server. I really wish there was a reference for the database versions so I knew what 622 is. Much Googling resulted in much frustration and no answers.

Adding some more information. The original database was corrupt. I was only able to restore it using a backup. The data files were a little newer (2 days) so we were seeing if we could use those as well.

Upgraded from SQL Server 2005 Express to SQL Server 2008 Express successfully while the corrupt database was attached. SELECT @@VERSION reports Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86). I ran sp_resetstatus, SET EMERGENCY and DBCC CHECKDB and received the following:

  Msg 946, Level 14, State 1, Line 1
  Cannot open database 'MyDatabase' version 622. Upgrade the database to the latest version.


texts: Differentiating physical standby to cascaded standby

texts:
Why is it that for non-real time apply from cascading to cascaded standby that we have to mention as SYNC as the redo transport mode? Ideally we mention SYNC for real time apply right?

Also, should we mention as SYNC NOAFFIRM for non-real time apply?

Thanks!


texts: Multi Subnet Availability Group IP Requirement for Windows Failover Cluster

texts:
Does the Windows Failover Cluster for a multi-subnet SQL Server Availability Group require a static IP entry for each subnet?

The instructions I am finding for a Failover Cluster Instance (FCI) in a multi-subnet environment require separate IPs but I am not creating any FCIs - only an availability group.

I am running SQL Server 2012 on Windows Server 2012 Hyper V VMs in 2 separate subnets in the same domain. I understand that I will need an IP from each subnet when I create the listener for my AAG. What I am unclear on is the configuration of IPs on the underlying Windows Failover Cluster.

Can I get away with using a single IP in the same subnet as the primary replica or do I need to add an IP from the secondary replica's subnet as well?

Also, if the server hosting the secondary replica does require its own IP, does it also require its own unique cluster name as stated in this blog post (and can you explain why this is necessary)?:

What you need for a Multi Subnet Configuration for AlwaysOn FCI in SQL Server 2012


texts: Should a table have a clustered index even if it doesn't have appropriate fields for it?

texts:
If you have a table without a good candidate field or fields for a clustered index (stable, sequential), is it better to have a clustered index on a bad field or is it better to make all table indices non-clustered?  What's the rule of thumb?

UPDATE

As per feedback, here is a very specific example to make the question more concrete.  Assume I have a 'PlayerStatsView' table, that looks like the following:

CREATE TABLE [PlayerStatsView](
    [PlayerId] [uniqueidentifier] NOT NULL,
    [TeamId] [uniqueidentifier] NOT NULL,
    [FirstName] [nvarchar](255) NOT NULL,
    [LastName] [nvarchar](255) NOT NULL,
    [Status] [nvarchar](255) NOT NULL,
    [BattingAvg] [int] NOT NULL,
    [RBIs] [int] NOT NULL,
    [HomeRuns] [int] NOT NULL,
    [PercentageOnBase] [int] NOT NULL,
    CONSTRAINT [PK_PlayerStatsView] PRIMARY KEY NONCLUSTERED ([PlayerId] ASC)
)

So in more concrete terms...

PlayerId, the primary key, is a GUID, as is TeamId.  FirstName and LastName combined are unique (or just about), but will be nowhere near sequential, not to mention they're too big to both include in a single index.  (For the purpose of this exercise, assume that player names change occasionally - not often, but from time to time.)  The rest of the fields will be updated with every write.

I'm mainly going to be querying by PlayerId and TeamId, I will have a non-clustered index on each.

The table currently contains tens of thousands of records, and will eventually hit hundreds of thousands.

Back to the question:

Am I better off without a clustered index, or should I add a clustered index even though there are no fields that are really suitable for one?


texts: Error on SQL Server 2000 backup (.bak file) restored on SQL Server 2012

texts:
When I try to restore a database in my new SQL Server 2012 instance I get this error :

  Restore of database 'MyDb' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  System.Data.SqlClient.SqlError: The database was backed up on a server running version 8.00.2055. That version is incompatible with this server, which is running version 11.00.2218. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)

Can you help me?


texts:
I'm reviewing a database server, the problem is that there are about 40 databases with each hundreds of tables. I'm looking for tables with a column name containing a specific word.

Is there a way I can do a search to identify these tables?


texts:
Are there any ways that can seamlessly enable the operational continuity of the Application upon a Service Account Password Change at Active Directory Level?

Any Ways?

Any Tools?


texts: Alter mysql database engine doesn't work from cluster to other

texts:
I got into this issue when I tried to alter my database table engine to InnoDB from ndbcluster, It did not work. But When I changed engine from InnoDB to ndbcluster it worked.

It did not throw any error but did not work. I just want to know why this did not work as I got it from MySQL site that it won't work. I just need to know the reason for the same.

For client requirement I may need to alter the engine on installed database without losing any data.

Any Ideas or help that anybody can provide to resolve issue.


texts: How can I get the Oracle TNSListener to start after changing my host name?

texts:
When running

lsnrctl status

I can see that it is using the old host name, but I don't know where it is pulling that value from.

My Oracle installation is 11.2.0 on Windows7 x64, if that makes any difference.


texts: MYISAM can't create table: Row size too large

texts:
We are having database in MySQL 5.1, we have taken the DB backup from MySQL 5.1 and trying to restore in MySQL 5.6. We are getting below error:

  ERROR 1118 (42000) at line 603: Row size too large. The maximum row
  size for the used table type, not counting BLOBs, is 65535. This
  includes storage overhead, check the manual. You have to change some
  columns to TEXT or BLOBs.

Found the error while creating a table. The table details are below:

1) Table is of type MyISAM.

2) Table is having total of 140 columns.

3) Table is having around 105 columns with datatype varchar(255).

4) Table is having 5 columns with datatype longtext.

I have done few comparisons/changes, but still I am getting same error:

1) Both the MySQL servers 5.1 and 5.6 are using character set = latin1

2) I had converted the longtext to varchar(255).

3) I tried to create table with row_format=dynamic, but still getting same error.

4) Tried to create the table with ENGINE=INNODB, getting same error.

5) converted the longtext to varchar(255) using INNODB engine, getting same error.

6) Found in google to increase innodb_log_file_size, which I know it is not concerned to this, still increased to 10 GB and getting same error.

7) I am able to create the table only I create with 98 columns of type varchar(255).

Are there are any other parameters to be changed for creating this table in MyISAM ?

I am able to create this table in Linux Environment in MySQL 5.6 and 5.7. But not able to create same table in Windows with either 5.6 or 5.7. I am using Windows 64 bit. What is the difference between Linux and Windows for creating same table ?


texts:
Is there a way to estimate log file size? I have a scenario where there is a possibility of 40-45K rows per day.

For the above scenario,

What should I consider to estimate log file size?
How much initial size it will be?
On what basis, the log file growth will be there?

Finally, any approximate size for records 40-45K per day?


texts: How Does Oracle Handle Multiple Concurrent INSERTs Against One Table

texts:
I am trying to understand Oracle 11g a little more closely.  My question is simple: how does Oracle handle two sessions that are attempting to insert records into a single table at the same time.

For example, INSERT A runs at 1:00PM against Table 1.  It will take 5 minutes to complete.
INSERT B is executed at 1:02 PM against Table 1.  It will take 1 minute to complete.

What will happen?  Will Oracle queue INSERT B to be executed after INSERT A is finished?  Will INSERT B run simultaneously with INSERT A?

Thanks!


texts: How to return when inserting into multiple tables as part of a views on inert rule

texts:
I am trying to create rules for my views to insert into multiple tables at once but I need to return the new row and I cannot work how to return from multiple insert statements.

This rule works fine but obviously returns nothing:

create rule template_field_insert as on insert to template_field
do instead (
    insert into public.base (
        id,
        organisation_id,
        last_modified_by,
        armicus_data
    )
    values (
        new.id,
        new.organisation_id,
        new.last_modified_by,
        new.armicus_data
    )
    insert into public.template_field_data (
        id,
        template_id,
        format_id,
        armicus_name,
        description,
        armicus_configuration,
        required
    )
    values (
        new.id,
        new.template_id,
        new.format_id,
        new.armicus_name,
        new.description,
        new.armicus_configuration,
        new.required
    )
)

I tried this but it just throws a syntax error on the first insert:

create rule template_field_insert as on insert to template_field
do instead (
    (
        insert into public.base (
            id,
            organisation_id,
            last_modified_by,
            armicus_data
        )
        values (
            new.id,
            new.organisation_id,
            new.last_modified_by,
            new.armicus_data
        )
        returning *
    ) b, (
        insert into public.template_field_data (
            id,
            template_id,
            format_id,
            armicus_name,
            description,
            armicus_configuration,
            required
        )
        values (
            new.id,
            new.template_id,
            new.format_id,
            new.armicus_name,
            new.description,
            new.armicus_configuration,
            new.required
        )
        returning *
    ) f
    select b.id, f.armicus_name
    from b
    join f on b.id = f.id
)

I have achieved this with CTEs outside of the view but you cannot use NEW inside a CTE so cannot use this approach inside the rule. I also tried using subqueries but I couldn't work out how to structure it because I wasn't sure what to enter in for the FROM clause.

How do I convert this query to be returning?


texts:
Essentially all comments are stored in this schema (dbml)

table comments {
  commentID BINARY(16) [not null] //UUID
  post_date timestamp
  postID BINARY(16) [not null] //UUID
  has_reply bool [default: false]
  // replyTo either points to commentID, or null
  // Essentially won't be loaded as a 'root' comment
  reply_to varchar [default: null]
  comment_content varchar [not null]
  // dislikes will decrease, likes increase
  likes int [default: 0]
  visible bool [default: true]
}

As the user goes through the posts, essentially the database would be queried as following
SELECT * FROM foo.comments WHERE postID = "ID of the post the USER is currently on" LIMIT 0, 20

Because of this setup, I realized this table would grow quite large housing all the comments.
After a while (say ambitiously) 1 million comments are created.  How would mariaDB handle this? Would it be dreadfully slow, if so how should it be modeled?

Will the WHERE clause save me from performance issues?


texts: Always-On Read Only Routing vs External Load Balancer

texts:
We are migrating a SQL Server 2008R2 with mirroring and transactional replication to SQL Server 2016 with AlwaysOn. So far, so good but we still have one question.

Is it better to use always on feature "read only routing" or to use an external load balancer to balance connection between replicas ?

I read a lot but I did not find any feedback. What I found is the following :

We already use an external load balancer with our current platform and it's working well
Since Sql server 2016, we can do round robin in sql server ror.
we must keep in mind that in case of persistent connection (or connection pooling), connection will be reused so it will point to the very same server. In other words, connections are balanced, not workload. That's the same for both solutions.
ApplicationIntent=ReadOnly and MultiSubnetFailover=True are useful with both solutions.

To what I understand here are the advantage and drawback of each solution :

Always On ROR : Can "group" instances, failover easy to configure, knows easily wich instance is primary and instance states. Very recent (untested?) feature.
External Load Balancing : easier to manage (does not need a dba to do network work), tried and true, can do other load balancing method than Round Robin (weighted round robin, least connection ...).

Did I miss something? Does somebody have any insight or feedback on always on ROR?


texts: What is the apgcc schema in Amazon RDS Postgresql?

texts:
Recently a mysterious new schema called apgcc appeared in every database in our testing Aurora Postgresql cluster (eu-west region). It is owned by the rdsadmin account. What is it, and where did it come from?

I am aware of a reference to apgcc in a launch announcement for Tokyo region. Twitter reveals that this has happened to others.


texts: Oracle not using index when > used in where clause

texts:
We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date.

Because there is no end clause to the date range, oracle doesn't choose to use the date index and ends up doing a very expensive table scan instead.  However we know this data is temporal, so we know there is no data in the future.  So if we modify the sql to include an end date of sysdate for the time period then the query uses the index and it becomes very cheap.

Unfortunately, due to a bug in the application we use we are unable to change the application. So is there any way in Oracle that I can either "teach" oracle that the max data in that table is today, so it can make a clever decision, or is the only real way to solve this problem at the database level to pin the plan?

Rgds,
Dan

(Oracle 10 Solaris Sparc)


texts:
I have been monitoring Blocking issues on our SQL Server database. Very often the below query is running on tempdb and is blocked with LCK_M_S

What is this query used for and why does it run regularly?

Thanks!

<?query --
select @reservedpages = sum(a.total_pages),
    @usedpages = sum(a.used_pages),
    @pages = sum(
            CASE
                -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
                When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
                When a.type <> 1 Then a.used_pages
                When p.index_id < 2 Then a.data_pages
                Else 0
            END
        )
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id

    /* unallocated space could not be negative */

--?>


texts: Archive Relational OLTP Database by Year

texts:
Our company is seeking to Archive old data.

After two years, we place from source: OLTP FinanceDB into ArchiveDB. We want to archive by Year of Customer Order Date. (eg Current year is 2018, any 2016 or below)

Order dates are different from Etlcreatedate and Etlupdatedates. Only one table dbo.SalesOrder has Order date. The rest of the related (parent, child, bridge) tables,  do not have Order date. This is a Inmon OLTP Relational database, so redundancy in order date will not occur.

Eg:

CustomerApplicationtable (shows customer information/name),

MktgTable (shows marketing material related to order),

Phonecall table (customer relation table),

FeeCommission table

These do not have order dates. How would I archive this? We have 100+ million rows?
I thought partition switching would not work unless every table has year column.

Should I add an archive partition indicator to each table and update, to find partition?

Should I utilize traditional etl, find all order dates with a join to SalesOrderdate table, and archive?