texts:
I'm using MySQL 5.7.29 on Ubuntu 16.04.  I'd like to increase the number of connections allowed by the MySQL server.  This involves setting configuration for both MySQL and Ubuntu.

MySQL

The primary config parameter is MySQL's max_connections.  I've edited /etc/mysql/mysql.conf.d/mysqld.cnf to have

max_connections = 800

(previously it was commented out and set to 100).  I also need to make sure that table-open-cache is high enough; for our application that's about 2 x max_connections plus a little extra.  $ mysqld --verbose --help reports

table-open-cache                2000

which should be high enough.

Ubuntu

For every open connection, MySQL opens a placeholder file for it.  Thus, in addition to increasing MySQL's max_connections, I need to make sure that the OS allows the MySQL process to open a sufficient number of files.

Existing answers for doing this divide into systemd and non-systemd approaches.  This system uses systemd.

non-systemd

The non-systemd approach involves editing /etc/security/limits.conf.  I followed instructions for doing this before I learned that systemd ignores this file for this case.  The original version of this question concerned that effort; I've edited it to remove what I now know is irrelevant.  I'm happy to restore information about what I did if anyone thinks it's relevant.  Long story short is that I successfully increased the output of $ ulimit -n from 1024 to 16384.

systemd

Raising the number of files MySQL can open on systemd requires editing the MySQL service file.

The systemd MySQL service file on this system is /lib/systemd/system/mysql.service.  Rather than editing the file directly, which I understand is bad practice, I created the directory /etc/systemd/system/mysql.service.d/ and the file override.conf within it.  To override.conf, I added

[Service]
LimitNOFILE=65536
LimitMEMLOCK=infinity

I also added fs.file-max = 65536 to /etc/sysctl.conf, though I'm still not clear on what relation that file has to systemd or whether it has any effect on a systemd system.

I reloaded systemd config ($ sudo systemctl daemon-reload) and MySQL ($ sudo service mysql restart).

Checking to see if it worked, I note that $ service mysql status indicates that the new override.conf file is being recognized:

$ sudo service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled
  Drop-In: /etc/systemd/system/mysql.service.d
           └─override.conf
...

However, MySQL continues to report a value of 151 for max_connections:

$ sudo mysqld --verbose --help | grep max-connections
  --max-connections=# The number of simultaneous clients allowed
max-connections                                              151

(I get the same result checking mysql> SHOW VARIABLES LIKE 'max_connections' from within the interactive MySQL shell).

What else do I need to do to permanently increase max_connections?


texts: SQL Server 2000 Enterprise Manager - "Navigation to the webpage was canceled" error when trying to design a table

texts:
While working in SQL Server 2000 Enterprise Manager on a remote server today, I wanted to look at a table's columns, so I found the table, right-clicked on it and selected "Design Table".

Instead of getting the table design view, I'm greeted with a white page with the following informational message:

  Navigation to the webpage was canceled

  What you can try:

  Refresh the page.

If I click "Refresh the page", I get another white screen with the following message:

  This program cannot display the webpage

  Most likely causes:

  You are not connected to the Internet.
  The website is encountering problems.
  There might be a typing error in the address.

I'm not sure who was tinkering around with the server, but I would love to know how to resolve this.

2011/04/19 update:  I checked, and KB961260 hasn't been installed.  The server is Windows 2003 R2, with SQL Server 2000 and Internet Explorer 8 for what it's worth.  I tried dropping security settings in IE to Low for Local and Trusted sites, which did not fix the issue.

We've also discovered that right-clicking on a table and selecting Open Table > Return All Rows gives the same error.  Designing and opening a view gives the same errors as above, but double-clicking on a view to open its properties lets us view the properties as normal.  We can also manage triggers, generate SQL scripts, etc. without issue.

Right-clicking on the first error page and checking its properties gives me this:

  res://ieframe.dll/navcancl.htm#http://%7b464ee255-fdc7-11d2-9743-00105a994f8d%7d/

Right-clicking on the second error page and checking its properties gives me this:

  res://ieframe.dll/dnserrordiagoff_webOC.htm#http://%7b464ee255-fdc7-11d2-9743-00105a994f8d%7d/


texts: With MySQL 5.6, Is there any way to keep a replication slave X seconds behind_the_master?

texts:
It'd be really nice for disaster recovery to be able to set this up.  We are doing snapshots, but this seems like it'd be a really nice backup to have.  For example, I'd like to always keep a replicated slave 30 minutes behind our master, so that we could have a sort of live backup all the time.

This is a secondary replicated server, as ypercube points out -- if you only have a single replicated server, running it 30 minutes behind would mean you'd potentially lose 30 minutes of data in the case of the master crashing.

Although I suspect the relevant binlogs for those 30 minutes of binlog events would actually be stored on the slave (and just not processed until it was 30 minutes behind), so I'm thinking that I wouldn't actually, even if this were the only slave.

I'll be doing some testing to confirm this behavior, as it is relevant for some potential use-cases for us.  I'll update this later.


texts: How to check the timezone for a given datetime?

texts:
A database / table I'm working it saves all its date values as datetime (without a timezone).

How can I find out which timezone that date was in?

For instance, the Central European Summer Time (+02) starts at 29th of March 2015 and the previous Winter Time (+01) started at 26th of October 2014.

Let's say I'm seeing a date of 2014-11-06, is there a function I can use to check to which timezone it belongs?

NB I know that there will be edge cases for when the time zone changes

I'm happy with some big CASE WHEN statement if that what it takes, but of course rather use some system function.


texts: How to install Oracle's sqlplus on a Mac?

texts: I need steps how to install Oracle's sqlplus on a Mac.


texts: Network line switch-over in Oracle Data Guard

texts:
We have a Primary database with a remote Standby which established using Oracle Data Guard. They can be syncing over a private Wireless Network and a Wired LAN (There are two options as network medium)

Is there any setting in Data Guard to handle network line failure? For example, I want to set wired LAN as main line and when it is not available data guard automatically switch to wireless network.


texts: I need to find what tables have been changed in the last period

texts:
is it possible to get the xmin of all tables in a schema to find what has been changed or created in the last period in postgres, I have been trying with little success

with tnames as (select tablename from pg_catalog.pg_tables
                 where schemaname = 'public' limit 1)
select max (xmin::text::bigint)
      ,tnames.tablename
from (select tablename from tnames)  as foo


texts:
MySQL does not have any SQL commands or internal mechanisms for

making an individual database unavailable / offline
moving an individual database

Thtis being the case, how can you take an individual database offline?


texts: Oracle DB - Is it possible to distribute data of a table onto multiple servers?

texts:
Bigtable-like systems (HBase for instance) allows a table to be distributed transparently across multiple servers. This works as follows:

Each server perform the read/writes operations on distinct partitions (shards) of the same table.
The DBA does not need to explicitely specify the server holding the partition in his query.
When the DBA run his query, the API will route the request to the proper server.
(The main benefit is scale-out scalability for write operations)

My assumption is that Oracle can not do so, but I am not an Oracle expert. References to confirm or correct this hypothesis are much appreciated.


texts: Oracle Text: query scoring with CTXCAT index?

texts:
If a text column is indexed with a CTXSYS.CONTEXT index it can return a score showing how well the text matches the query.

create index ix on t(text) indextype is ctxsys.context parameters ('');
select id, score(1) from t where contains(text,'my query',1) > 0;

How can I see the score for text columns index with a CTXSYS.CTXCAT index?

create index ix on t(text) indextype is ctxsys.ctxcat parameters ('');
select id, ??? from t where catsearch(text, 'my query',null) > 0;


texts: Performance Counters for SQL Server Related to iSCSI Disk Access

texts:
I am planning to move my SQL Server databases (plus TLogs and TempDB) to a new LUN on our iSCSI SAN.  The current LUNs used by these files are on their own two disk RAID 1 disk group and I'm going to a larger but shared 14 disk RAID 10 disk group.  I want to measure the performance of the current configuration and the new configuration as I move each database over and ensure that I am not starting to hit any disk performance issues (or see if I am actually increasing the performance).

There are a bunch of posts on the internet on SQL performance counters such as this one, but I am really interested in just the few that are related to network/disk usage and any latency or limits that are associated with disk reads/writes.  What are some of the important SQL Server or Windows performance counters that I should look at to create a current baseline/comparison for iSCSI disk access for SQL Server?


texts: INSERT DATA The database disk image is malformed

texts:
We use sqllite to store our data.

Today,when I wanted to insert data in sqllite db,the log showed

  The database disk image is malformed


texts:
I have a table called paths that looks like this:

id  parent_id
--  ---------
 1       null
 2          1
 3          2
 4          3
 5          4
 6          5
 7          5
 8          5
 9          5
10          5
11          1
12         11
13         12
14          1
15          2
16         15
17          3
18          2
19         18
20         19

I have the query:

SELECT count (id) as count_id, id FROM paths where parent_id=5 group by id

It returns me:

count_id  id
--------  --
1          6
1          8
1          9
1          7
1         10

But I want to get the following:

total_count  id  parent_id
-----------  --  ---------
5             6          5
5             8          5
5             9          5
5             7          5
5            10          5

Since the total count is 5, I want to show 5 in every column.
What I am doing is finding all nodes whose parent id is the specified number, count the nodes and return every found node along with that total number.

If I found nothing, I'd like to have

total_count  id    parent_id
-----------  ----  ---------
0            null       null

Is there a way to do it?
I use PostgreSQL 9.5.


texts: Could you give me some advice on the best practice to update a SQL Server 2014 instance that is on version 12.00.2000.00? Can I install the Service Packs or do I need all of the cumulative updates as well?


texts: Multiple aggregations in select slow on postgres

texts:
I have a table with columns: id, antenna_id, latitude, longitude. There are two composite indexes on (antenna_id, latitude) and (antenna_id, longitude). When I do a max(latitude) for a specific antenna id(s), the speed is acceptable, but doing a min and max for both latitude and longitude at the same time is very slow.
Using PostgreSQL 12.3
Query

EXPLAIN (analyze, buffers, format text)
SELECT max(latitude)
FROM packets
WHERE antenna_id IN (1,2)

Finalize Aggregate  (cost=443017.21..443017.22 rows=1 width=32) (actual time=4373.679..4373.679 rows=1 loops=1)
  Buffers: shared hit=10812 read=16887
  ->  Gather  (cost=443017.10..443017.21 rows=1 width=32) (actual time=4373.412..4389.032 rows=2 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        Buffers: shared hit=10812 read=16887
        ->  Partial Aggregate  (cost=442017.10..442017.11 rows=1 width=32) (actual time=4313.576..4313.577 rows=1 loops=2)
              Buffers: shared hit=10809 read=16887
              ->  Parallel Index Only Scan using idx_packets_antenna_id_latitude on packets  (cost=0.57..433527.51 rows=3395835 width=7) (actual time=0.375..3435.488 rows=2201866 loops=2)
                    Index Cond: (antenna_id = ANY ('{1,2}'::integer[]))
                    Heap Fetches: 0
                    Buffers: shared hit=10809 read=16887
Planning Time: 5.992 ms
JIT:
  Functions: 8
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 6.236 ms, Inlining 0.000 ms, Optimization 1.549 ms, Emission 32.058 ms, Total 39.842 ms
Execution Time: 4706.406 ms

The explain looks almost identical for max(longitude), min(latitude) and min(longitude) on their own. Speed is acceptable.
But when I combine the queries
SELECT max(latitude), max(longitude), min(latitude), min(longitude)
FROM packets
WHERE antenna_id IN (1,2)

Duration
[2021-03-06 09:28:30] 1 row retrieved starting from 1 in 5 m 35 s 907 ms (execution: 5 m 35 s 869 ms, fetching: 38 ms)
Explain
Finalize Aggregate  (cost=3677020.18..3677020.19 rows=1 width=128)
  ->  Gather  (cost=3677020.06..3677020.17 rows=1 width=128)
        Workers Planned: 1
        ->  Partial Aggregate  (cost=3676020.06..3676020.07 rows=1 width=128)
              ->  Parallel Seq Scan on packets  (cost=0.00..3642080.76 rows=3393930 width=14)
                    Filter: (antenna_id = ANY ('{1,2}'::integer[]))
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true

EXPLAIN (analyze, buffers, format text)
SELECT max(latitude), max(longitude), min(latitude), min(longitude)
FROM packets
WHERE antenna_id IN (1,2)

has been running for 24h now, and isn't done yet
Indexes
create index idx_packets_antenna_id_time
    on packets (antenna_id, time);

create index idx_packets_antenna_id_longitude
    on packets (antenna_id, longitude);

create index idx_packets_device_id_time
    on packets (device_id, time);

create index idx_packets_antenna_id_latitude
    on packets (antenna_id, latitude);

Data stats
select count(*) from packets
136758098

select count(distinct (antenna_id)) from packets
17558

select antenna_id, count(*) as records
from packets
where antenna_id in (1,2)
group by antenna_id
order by records desc

1,4361049
2,42683

Question
Why does the second query which does the min and max on the latitude and longitude field not use the indexes? And how can I rewrite the query so that it is faster?


texts: Should I index an integer column which is already ordered?

texts:
I have a table like the picture below, I want to index two columns of this table (user_id, question). The type of all columns is int. The user_id and the     question_id columns are foreign keys.

CREATE TABLE user (
    user_id int(10) NOT NULL AUTO_INCREMENT,
    username varchar(50) DEFAULT NULL,
    password varchar(60) DEFAULT NULL,
    PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE question (
    question_id int(10) NOT NULL AUTO_INCREMENT,
    question varchar(60) NOT NULL,
    answer varchar(60) NOT NULL,
    PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE answer (
    user_id int(10) NOT NULL,
    question_id int(10) NOT NULL,
    score int(10) NOT NULL DEFAULT 0,
    timer int(10) NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    FOREIGN KEY (question_id) REFERENCES question(question_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A lot of "updating" and "reading" query goes to this table. As you can see these two columns are already ordered because every time a user signup I will add the user's id in the user_id column and the numbers from 1 to 300 to the question column. the query is something like this:

UPDATE score SET user_score_for_question = 20 WHERE user_id=400 AND  question=100

Question1: since "user_id" and "question" columns are already ordered, Is it necessary to index them or not?

Question2: If I should index those columns, since I always use "user_id" and "question" together, how is the correct way to index these columns? I not familiar with "hash index" and "unique index" which one should I use?


texts:
I'm trying to load a file into DB2 database table using DB2 Load. I'm able to load the file, using the following statement from the unix prompt.

db2 +p -x load from BOM_Sales_20130326.txt of DEL MODIFIED BY COLDEL\| timestampformat=\"YYYY-MM-DD hh:mm:ss\" insert into GEO_SALES.SDM_STL_VFRSK_SALES NONRECOVERABLE

But, when I try to call DB2 Load from a script after parameterising many of the options, it does not seem to work.

For example, I have re-written the statement as follows:

db2 +p -x "load client from $FILE_DIR/$filenm of DEL MODIFIED BY COLDEL\$FILE_DELMTR timestampformat=\"YYYY-MM-DD HH:MM:SS\" insert into $TGT_SCHEMA_NM.$FILE_STG_TBL_TGT NONRECOVERABLE"

All the variable are assigned with correct values prior to calling the statemant. But it seemed to fail due to wrong timestampformat. The error is as follows:

SQL3191N  The field in row "F2-1", column "1" which begins with
"2013-03-26|STL|5678|D|3212345"
does not match the user specified DATEFORMAT,
TIMEFORMAT, or TIMESTAMPFORMAT. The row will be rejected.

In fact, my need is to give the value "timestampformat" also as a parameter, as follows:
db2 +p -x "load client from $FILE_DIR/$filenm of DEL MODIFIED BY COLDEL\$FILE_DELMTR timestampformat=$DTTIME_FMT insert into $TGT_SCHEMA_NM.$FILE_STG_TBL_TGT NONRECOVERABLE"

Since it was not working, just for the testing purpose, the variable $DTTIME_FMT was replaced with \"YYYY-MM-DD HH:MM:SS\". As said earlier, that too does not seem to work.

Request your help to fix this and also like to know whether I can parameterise the all these load options as I've tried.

Thanks,
Tom


texts: Troubleshoot corruption in SQL Server Database

texts: I have been asked by my interviewer "How do you troubleshoot corrupted data in SQL in SQL Server 2012?"


texts: How to insert UNIQUE IDs values from several tables into events table

texts:
I have a table called events defined as follow:

rep_id, hcp_id, brand_id, email_id and content_id is the IDs in other tables.

event_type should be kind of array containing this values:

[
    "rep_login",
    "rep_share",
    "email_viewed",
    "brand_page_opened",
    "media_viewed",
    "rep_contacted",
]

I need to insert 10k rows on events table but compliance with these conditions:

Each row should contains UNIQUE values for each column
event_type should be randomize for populate the table with several event types

This data is for testing purpose. Can this be done from MySQL query? How?


texts: Compare values between columns in different tables

texts:
I have two tables that looks exactly the same:

CREATE TABLE `_sync_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `object_name` varchar(45) DEFAULT NULL,
  `sync_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `_sync_pdone` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `object_name` varchar(45) DEFAULT NULL,
  `sync_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to check and perform actions based on result, if it's possible. See below what I want to achieve in kind of pseudo-code:

IF ( MAX(`_sync_history`.`sync_time`) > `_sync_pdone`.`sync_time` ) THEN
    PERFORM SOME ACTION HERE
ELSE
    DO NOTHING

In both tables object_name will be the same: territory so I made this first SQL:

SELECT MAX(sync_time) FROM `_sync_history` WHERE object_name = 'territory'

But I don't know where to go from here and also doesn't know if what I want to achieve is possible at all. Any advice? work around? solutions?


texts: Nested Loop has very low estimates due to skewed data

texts:
On SQL Server 2016 SP2 we have a query that has a very low estimate on the nested loop operator. Due to the low estimate this query also spills to tempdb.

If I'm correct SQL Server 2014+ uses Coarse Histogram Estimation to calculate the estimated number of rows on a join.
But when I execute the query, SQL Server uses the density vector to calculate the number of estimated rows.
Is SQL Server only using the Coarse Histogram Estimation if there is no where clause?

Normally I would use filtered statistics to improve estimations when I have a table with skewed data. But in this case that doesn't seem to work.

Is there a way to improve the estimations on the nested loop?

Using following code you can reproduce the data:

create table MyTable
(
    id int identity,
    field varchar(50),
    constraint  pk_id primary  key clustered (id)
)
go

create table SkewedTable
(
    id int identity,
    startdate datetime,
    myTableId int,
    remark varchar(50),
    constraint  pk_id primary  key clustered (id)
)

set nocount on

insert into MyTable select top 1000 [name] from master..spt_values
go

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 1000

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go

CREATE NONCLUSTERED INDEX [ix_field] ON [dbo].[MyTable]([field] ASC)
go

CREATE NONCLUSTERED INDEX [ix_mytableid] ON [dbo].[SkewedTable]([myTableId] ASC)
go

--95=varchar in sys.messages
set nocount off

;with cte as
(
    select GETDATE() as startdate ,95 as myTableId, REPLICATE(N'B',FLOOR(RAND()*(40))+1) as remark
    union all
    select * from cte
)
insert into skewedtable select top 40000 * from cte
option(maxrecursion 0)
go

update statistics mytable with fullscan
go

update statistics skewedtable with fullscan
go


texts: Oracle Automatic Memory Management - SGA_MAX_SIZE

texts:
When using Automatic Memory Management, what value should SGA_MAX_SIZE be?  Should it be 0?  These are my current settings, and I'm unsure whether to leave SGA_MAX_SIZE or set it to 0.

memory_max_target     2147483648
memory_target         2147483648
pga_aggregate_target  0
sga_max_size          1610612736
sga_target            0

Oracle 11gR2


texts: How can I change the names of all tables to uppercase?

texts:
User created one database, for example TestDb.

Within this database the user created 30 tables, with the table name in lower case letters (Eg:Employee).

Is it possible to change the tables names to upper case (eg:EMPLOYEE) in SQL Server 2008 R2?

Thanks.


texts: How can I prove / disprove If A ↠ BC and A → B then A → C

texts:
/ From GATE 2007 IT:

  Consider the following implications relating to functional and multi
  valued dependencies given below, which may or may not be correct.

A) If A ↠ B and A ↠ C then A → BC
B) If A → B and A → C then A ↠ BC
C) If A ↠ BC and A → B then A → C
D) If A → BC and A → B then A ↠ C

  Exactly how many of the above implications are valid?

  A)    0 B)    1 C)    2 D)    3

I've just studied 4th Normal Form & Multi Valued Dependencies & I'm struggling to apply inference rules for multi Valued dependencies.

I've searched online though I've not found any resource on it.

I tried:

A) If A ↠ B and A ↠ C then A → BC.  This is incorrect. It is very obvious to see.

B) If A → B and A → C then A ↠ BC. This can be proved by using Augmentation rule of FD. A ->B & A-> C. Then we get A->BC. Using Replication rule we get  ↠ BC.

D) If A → BC and A → B then A ↠ C. This is true, because A->BC. From which we can get A->C. From which using Replication rule we can get  A ↠ C.

C) If A ↠ BC and A → B then A → C. Here using Coalescence rule I'm getting A->B back. I'm not able to prove/disprove A → C.

Please help me prove/disprove option C.
What does it means to have a multi valued dependency on two attributes like A ↠ BC? All examples I've seen in books are like Employee ↠ Project | Dependent, where Employee simultaneously determines two attributes. So we end up storing duplicated. But I have never encountered a multi valued dependency with two attributes on the right hand side. Please give an example of such a dependency.


texts:
Suppose you have a collection of students of the following form:

{"_id" : ObjectId("50c598f582094fb5f92efb96"),
"first_name" : "John",
"last_name" : "Doe",
"date_of_admission" : ISODate("2010-02-21T05:00:00Z"),
"residence_hall" : "Fairweather",
"has_car" : true,
"student_id" : "2348023902",
"current_classes" : [
   "His343",
   "Math234",
   "Phy123",
   "Art232"
]}

Now suppose that basic inserts into the collection, which only include the last name, first name and student_id, are too slow (we can't do enough of them per second from our program). What could potentially improve the speed of inserts. Check all that apply.

1) Add an index on last_name, first_name if one does not already exist.

2) Remove all indexes from the collection, leaving only the index on _id in place

3) Provide a hint to MongoDB that it should not use an index for the inserts

4) Set w=0, j=0 on writes

5) Build a replica set and insert data into the secondary nodes to free up the primary nodes.

I'm sure that the number 4 is correct. The numbers 1,3,5 are not correct. I'm not sure on the number 2. Is it correct?


texts:
Will mongodb a good choice as a data warehouse considering following situation?

We are building analytics, report generation platform for our client and a typical table growth in terms of rows will be 2 million rows every year (We need 5 yr history at max, total tables ~ 50)
We have proof of concept ready using mysql and it works but my worry is as we hit production the growth in data might create issues with mysql. mysql doesn't support horizontal scaling whereas sharding in mongodb makes it scale well
On average once or twice a year schema of a table changes where either new column is being added or existing one is deleted (deletion is not a concern as we can always keep the column but how difficult it would be to add new column in mysql table containing few million records?). This is other reason we are thinking about mongodb as it is flexible in terms of schema changes
Writes: Average 5000 records per table per day. Reads will be less then this and it will be performed for generating analytics.

I don't have much experience dealing with this kind of data volume both with mysql and mongodb hence pardon my ignorance if some of the things mentioned above are naive.


texts: SSIS Guarantee that all data will be moved to target when running data flows in parallel

texts:
My package has the following two tasks which run in parallel.

DF - Move source data to target loads a copy of all source data to target via OLEDB Dest - Target and also loads a copy of source PK values to a staging table via OLEDB Dest - SourceRowsToDelete. The staging data is used by ExecSQL - Delete Source Data to delete all source data in parallel.

The OLEDB Dest - SourceRowsToDelete destination always completes before the OLEDB Dest - Target destination.

ExecSQL - Delete Source Data deletes all source data using the following SQL

DELETE  sd
FROM    [Source].dbo.[SourceData] sd
INNER JOIN
        [Source].dbo.SourceRowsToDelete s
        ON  sd.ID = s.ID

If the rows to be moved are currently in the buffers, have been written to the [source].dbo.sourcerowstodelete table and subsequently deleted from source but not yet fully written to target. Other than me stopping the package and dropping the buffers, is there any other way that they won't make it to the target?


texts: Benefits of using backtick (`) in MySQL queries?

texts:
In MySQL we can create queries with or without the backtick (`) symbol. Example:

SELECT * FROM TEST;
SELECT * FROM `TEST`;

Both works fine in mysql-console.

Is there any technical difference between them?

Is there any benefit using (`) over over simple queries?


texts: Oracle - abort within a "before insert" trigger without throwing an exception

texts:
I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these records, for business reasons, we don't want in this table. I was thinking I could write a trigger that "aborts" any of these rows without throwing an error (so that the application doesn't fail in the front end, we want it to think it did its thing). I am intimately familiar with this application and know it won't cause any ugly side effects, we just can't modify it to behave the way we need it to.

So, is this possible, and if not, what would be another way to go about it? I thought about having some sort of Job that routinely cleans the table for records that we don't want, but I would rather for these records to never be there to begin with so there's no degree of "dirtiness" at any one time in the table.


texts: Does sql server calculate views every time when someone query the views?

texts:
I wonder whether sql server calculate views every time when someone query the views. It is because everytime I query the views, it take such a long time.

And when I tried to shorten the time by selecting only top 10 rows from the view, it takes the same amount of time. I suspect sql server need to calculate the view for the whole table even if I only want a few rows from the view, am I right?


texts: Can connect successfully to SQL Server Express but not SQL Server

texts:
On my local machine I have installed:

Microsoft SQL Server Enterprise Evaluation Edition (64-bit) 12.0.4100
SP1
Microsoft SQL Server Express Edition (64-bit) 12.0.2000 RTM

I installed both using the default install options, and have restored different databases to each. In SQL Server 2014 Management Studio, I can access both databases on their separate servers, and run queries against them.

I am able to connect to a database in Express from Python and by using the interface brought up when you open a .udl file see here.

However, I am unable to connect to the SQL Server Evaluation Edition server from outside of Management Studio and I can't figure out why. My machine can 'see' the server  but once I select the server, it cannot see any of the databases in the server. When I click 'select database on the server' it brings up a "login timeout expired" warning. In contrast, when I follow the same steps after selecting SQLExpress everything works as expected.

,

Are there additional settings I need to change in SQL Server to enable access? I imagine there's something quite simple I have neglected to do as I'm a SQL Server novice.

Here are the connection details as reported in Management Studio for the SQL Express instance I can successfully connect to:

Here are the connection details as reported in Management Studio for the SQL Server instance I cannot connect to from outside of Management Studio:

Thanks very much for any help with this - my objective is to connect to the database from Python.

Robin


texts:
I'm trying to build a game server and we are using MongoDb as the database.

We have a collection Games that has models like this:

{
    "_id" : ObjectId("5d1b345b8ea742034db76431"),
    "Users" : [
        {
            "_id" : "0e76bd95-a7c2-4e15-b2bc-4cdf741fe9aa",
            "UserName" : "l98lNhLHPh",
            "Cards" : [
                249
            ]
        },
        {
            "_id" : "6ffec61d-45cc-46fa-a1f0-2a3e0a03fef6",
            "UserName" : "Vun12sWp4W",
            "Cards" : [
                234
            ]
        }
    ],
    "CreatedAt" : ISODate("2019-07-02T15:09:23.303+04:30"),
    "UpdatedAt" : ISODate("2019-07-02T15:09:23.674+04:30"),
    "IsFinished" : false
}

We can easily query users that participated in a game, but what if we need to know the games that a special user had played before?

We reached two solutions but we don't know which one is better.

First, query using nested field with an index on Users.UserName:
db.games.find({"Users.UserName":"Amin"})

Second, create a new collection that holds the User and Games data.

// collection: UserGames
{
    "_id" : ObjectId("5d1b35c58ea742034db79fea"),
    "UserId" : "6ffec61d-45cc-46fa-a1f0-2a3e0a03fef6",
    "GameId" : ObjectId("5d1b35c58ea742034db79fe9")
}

and join Games and UserGames collections to find the which user played which games.

I came from a Relational database mindset and I don't know which approach should I take?


texts:
I have a table with m columns and n rows. The ETL tools I
was using wrote an empty ('') to every cell without a value and I would like
them to be NULL instead.  This got me thinking if it would be
possible to UPDATE every cell with value '' with a NULL, in a single statement?
In my table every column happens to be VARCHAR (or CHARACTER VARYING).

    Col1|Col2|...|Coln          Col1|Col2|...|Coln
Row1   a   ''  ''    1      Row1   a NULL NULL   1
Row2   c   ''   e   ''      Row2   c NULL   e NULL
...                     -->  ...
Rown  aa    f  ''  123      Rown  aa    f NULL 123

Is it possible with SQL?  How about PL/pgSQL?  I did some
googling around but couldn't find anything really relevant.


texts: Why didn't MySQL keep the innodb_file_format after loading a mysqldump

texts:
I was loading a mysql dump today when I ran into

ERROR 1118 (42000) at line 279: Row size too large (> 8126)

Which I thought was weird because I know I had turned on row compression and was using Barracuda on that server. I also knew I wasn't running into that problem on the production database.

I checked on the production database and sure enough

show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

But on the server that loads the dump the file_format variable is set to:

mysql> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Antelope  |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Antelope  |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

Here's what my MySQL dump looks like

sudo mysqldump -u mysqlbackups --databases limbo | gzip > /local_backup_directory'+%m-%d-%Y'.sql.gz

Why wasn't the innodb_file_format and format_max variables dumped? How can I tell mysqldump to dump the file_format and format_max variables?


texts: Correct way to perform backup of Cassandra/Scylladb

texts: What's the correct(advisable) method to backup a cassandra or scylladb so that we can restore it on development environment with ease?


texts: SQL Server Mirroring kicks in before cluster has time to fail over

texts:
I have two clusters each with two nodes configured in active/passive; the two clusters take part in a high-availability mirroring session using a separate server as the witness.

When a node fails in the primary cluster, the mirroring failover seems to kick in before the secondary node of the primary cluster has had time to come online.

Is there anyway of telling the witness server to let the passive node attempt to come online before initiating the failover to the secondary cluster?


texts: Determining Homogeneity by Common Foreign Keys

texts:
I manage a MySQL database of ten or so tables.

In higher code, I have a list of primary keys belonging to entries of some table A. These A entries have foreign keys to entries in another table B. The relationship is 1-to-many, so several As may point to the same B.

Problem: I need to determine if the list of primary keys I hold in higher code (Scala) is homogeneous. That is, if their foreign keys to B all correspond to the same entry in B.

Question: Would it be possible to determine this by analysing the results of a single SQL call?


texts: How can I use a MySQL query in an openquery query on Microsoft SQL Server.?

texts:
I have the following query which works fine in MySQL:

DELETE sb
FROM stockbatches sb
    INNER JOIN batchestodelete btd
ON sb.batch_batch = btd.batchno
    AND sb.batch_part = btd.batchpart

What I want to do is run this query on the MySQL table from SQL Server using an openquery to a linked MySQL server.  I cannot get the syntax right.

Can anybody help please?


texts:
I had a serious CPU bottleneck 24/24 (it reaches 100% very often) for some weeks. The application has a pure aggressive OLTP (a thousand of query that retrieves often 1 row based on several joins).

This week I decided to begin a backup of the transaction log file, and hopefully the CPU bottleneck disappears.

Is there a direct relation between the transaction log file (size,backup or whatever) a the CPU bottleneck?


texts: PostgreSQL; Update different column on condition using CASE statement

texts:
I have a table which has different types of balance and I need to deduct the balance from different columns based on availability.
Here is the query (Sample) I tried to use but it didn't work
update table_A
set case
    when column_A>table_B.balance then column_A
    when column_B>table_B.balance then column_B
    when column_C>table_B.balance then column_C
    end =value
from table_B
on table_A.Id=table_B.id

Thanks!


texts: Elapsed time in SSMS is consistently higher than SQL Server Execution Time - connection issue?

texts:
I have a fresh install of SQL Server 2012 Developer Edition on a pretty snappy laptop. I'm using it as a backend for an ASP.NET webapp. I noticed query timings that were consistently higher than I'd expect coming from a MySQL background.

For example, MiniProfiler shows select * from Comments is taking 30-50ms, when Comments has only one row in it. Simple things like that.

In investigating, I opened SQL Server Management Studio and issued a simple query:

set statistics io on
set statistics time on
go
select 1 as one;
go
set statistics io off
set statistics time off
go

This outputs what I'd expect:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

But in the right sidebar of SSMS, 'Aggregate Status > Elapsed Time' shows 77ms. Running it multiple times shows timings varying between 50ms and 80ms.

I removed the timing commands and just ran select 1 as one; (again from SSMS). Profiler again shows 0ms execution times, but SSMS shows 55ms. My first question: Is it unreasonable to expect (exponentially) better performance? If SQL Server is just slow, I don't want to waste time on this. Have a hard time believing that, though.

At this point, I believe this is a connection issue, but I'm not sure where to start. I did disable McAfee's on-access protection, to no avail. I've also tried using SQL Server auth instead of Windows auth, also with no effect.

What are your thoughts? These 50ms hits for every query really add up on pageloads, and I believe we're also seeing this in production (I'm light on the details there).


texts:
I am trying to repair a suspect database.
The problem is that when I run:

EXEC sp_resetstatus property;

it gives me this response:

Warning: You must recover this database prior to access.

when I try this:

ALTER DATABASE property SET EMERGENCY;

it also fails:

Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'property', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

what do I do next?
Thanks.


texts:
I want to connect to MySQL Server hosted on a Linux Machine from my Windows Machine using Command Prompt with the following command:

prompt>mysql -u root -p -h remote_hostname

Connections, ports, user accounts and credentials are all fine. But it is throwing the below error:

  ERROR 1130 (HY000): Host 'my_hostname' is not allowed to connect to this MySQL server

I am wondering is it possible to communicate with Linux Shell from Windows Command Prompt? or is it not at all in the very design of prompt to connect to Linux Shell for any remote operations? Or is it special in case of MySQL.

I observed that I can connect from Linux to Windows but the reverse is not possible when I am trying just like I explained above.

Anybody know the theoretical explanation to this?


texts: How to know if a particular DW project is using Kimball methodology

texts:
When I look at the data warehouse/BI/ETL processes in my group, how can I tell if we are using Kimball methodology or not?

If I use Slowly Changing Dimensions i.e expire the old record and create a new record, and are also using a Star Schema, does it automatically mean we are using Kimball?


texts: Unable to connect to SQL server 2008 R2 via Instance name?

texts:
I don't understand :

I've installed sql server 2008 R2. the installation process asked me for the desired instance name  , so I wrote: MSSQLSERVER.

after the installation , running Setup Discovery Report does shows me the instance name :

I've also installed all the service packs.

Also , all the services are up :

Also , all the ports are open ( tcp:1433 + udp :1434)

Also , Named pipes are on :

So ,  where is the problem ?

I'm unable  to connect via the instance name :

Also unable   with :

user\MSSQLSERVER where user is the computer name.

Also unable  with : .\MSSQLSERVER

But (weird) :

Those 3 do work :

user ( "user" is my machine name)
(local)
.

Question :

why I can't use my instance name to connect to my sql server ?

Related info :

After connecting via one of the 3 who work  , I run a helpful info :


texts: I have several hundred databases running on the same MySQL server. How can I get a list sorted by the size of the databases?


texts: Windows MySQL: Create table with DATA DIRECTORY

texts:
I'm using Mysql on Windows 10, can I create a new table on another drive(different than the drive that contains the MySQL data directory) without creating a new MySQL instance?

When trying to create the new table with "Data Directory" option I noticed that it says on the GUI that "Data Directory" option only works for MyISAM engine and not working on Windows. My table used InnoDB. And it really doesn't works after my try.

Can this ever be done on Windows 10, if so ,which version is required?


texts:
How can I speed up this 2m5s query that has indices?

select urls.id as urlId,
    count(case when s1.hit_type = 0 then 1 end) as aCount,
    count(case when s1.hit_type = 1 then 1 end) as bCount,
    count(case when s1.hit_type = 2 then 1 end) as cCount,
    count(distinct s1.source_id) as sourcesCount
from urls join stats s1 on urls.id = s1.url_id
where s1.hit_date >= '2017-12-12'
group by urls.id
order by aCount desc
limit 0,100;

mysql> show create table stats;

| stats | CREATE TABLE `stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `url_id` varchar(100) DEFAULT NULL,
  `hit_date` datetime DEFAULT NULL,
  `hit_type` tinyint(4) DEFAULT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `url_id_idx` (`url_id`),
  KEY `source_id` (`source_id`),
  KEY `stats_hit_date_idx` (`hit_date`),
  CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`url_id`) REFERENCES `urls` (`ID`),
  CONSTRAINT `stats_ibfk_2` FOREIGN KEY (`source_id`) REFERENCES `sources` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6027557 DEFAULT CHARSET=latin1 |

mysql> describe select...
| id | select_type | table   | type   | possible_keys                                                                                | key     | key_len | ref                      | rows    | Extra                                        |
+----+-------------+---------+--------+-------------------------------------------------------------------------------------------------+---------+---------+--------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | s1      | ALL    | url_id_idx,stats_hit_date_idx                                                                   | NULL    | NULL    | NULL                     | 5869695 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | urls    | eq_ref | PRIMARY,urls_email_idx,urls_status_idx,deptId_idx,deptId_status_email_idx                       | PRIMARY | 102     | db.s1.url_id             |     1   | Using index                                  |

It doesn't seem to be using the hit_date index or url_id index.

I tried using a sub-select (select count(*) from stats where url_id = ... and hit_date >= ... and hit_type = 0) as aCount and it was faster and took 24s. Is there a way to make it less than 5s? The limit for the entire request is 30s.

MySQL Server version:         5.6.35-log MySQL Community Server (GPL)


texts: Find the functional dependencies that hold in a relation

texts:
There is a question in my book that asks the following:

Suppose we have relation R ( A ,B ,C ,D ,E ) , with some set of FD’s, and we wish to project those FD’s onto relation S(A, B, C). Give the
    FD’s that hold in S if the FD’s for R are:

A->D
BD->E
AC->E
DE->B

In each case, it is sufficient to give a minimal basis for the full set of FD’s of S.

So I attempted to compute the closure of attributes for all of the subsets of (A,B,C).  I could not find any minimal basis of FD's for S.  I computer the closure of A, B, C, and AC, but I could not get all of the attributes.  A only implies AD, B only implies B, C only implies C and AC only implies ACE.  I could not find a functional dependency where there is a attribute on the left hand side that implies all of the attributes on the right hand side.


texts: How to order by alphanumeric codes in SQL Server

texts:
I have a table with some project codes. These codes are

e.g.: B001, B002, Z15001, Z14001, P003,...

I need this result table:

Z15001
Z15002
...
B001
B002
...
C001
C002
...
Z14099
Z14098
Z14097
...
Z13099
Z13098
...
Z13001

I tried this but the order is wrong.

select *
from table
order by
    case
        when Kod like 'Z15%' then 1
        when Kod like 'B%' then 2
        when Kod like 'C%' then 3
        when Kod like 'D%' then 4
        when Kod like 'E%' then 5 else 6 end asc, Kod asc
    , case when Kod NOT LIKE 'B%'
        AND Kod NOT LIKE 'C%'
        AND Kod NOT LIKE 'D%'
        AND Kod NOT LIKE 'E%'
        AND Kod NOT LIKE 'Z15%' then 6
      end desc

How I can do this in SQL Server?


texts: SQL Server 2012 Agent hangs at a particular time of the day

texts:
There is a 3 node SQL Server Failover Cluster with 3 instances running on same node at the moment. SQL Server version is 2012 with SP3.

At a particular time of the day at 4 PM daily, SQL Agent hangs and following error comes in SQL Agent Log:

  2019-12-17 20:17:53 - + [260] Unable to start mail session.

  2019-12-17 20:17:53 - + [396] An idle CPU condition has not been
  defined - OnIdle job schedules will have no effect

  2019-12-17 20:17:53 - + [408] SQL Server XXXXXXXX is clustered -
  AutoRestart has been disabled

  2019-12-18 16:20:30 - ! [298] SQLServer Error: 65535, SQL Server
  Network Interfaces: Error Locating Server/Instance Specified
  [xFFFFFFFF]. [SQLSTATE 08001]

  2019-12-18 16:20:30 - ! [165] ODBC Error: 0, Login timeout expired
  [SQLSTATE HYT00]

  2019-12-18 16:20:30 - ! [298] SQLServer Error: 65535, 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 08001]

  2019-12-18 16:20:30 - ! [382] Logon to server 'XXXXX\XXXXXXX' failed
  (ConnUpdateStartExecutionDate)

  2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, SQL Server
  Network Interfaces: Error Locating Server/Instance Specified
  [xFFFFFFFF]. [SQLSTATE 08001]

  2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, SQL Server
  Network Interfaces: Error Locating Server/Instance Specified
  [xFFFFFFFF]. [SQLSTATE 08001]

  2019-12-18 16:20:31 - ! [165] ODBC Error: 0, Login timeout expired
  [SQLSTATE HYT00]

  2019-12-18 16:20:31 - ! [165] ODBC Error: 0, Login timeout expired
  [SQLSTATE HYT00]

  2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, 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 08001]

  2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, 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 08001]

Also if I query sp_readerrorlog through ssms, following error comes:

  Msg 22004, Level 16, State 1, Line 0 Failed to open loopback
  connection. Please see event log for more information.

  Msg 22004, Level 16, State 1, Line 0 Error log location not found.

I have found the same symptoms in the support page of Microsoft:
https://support.microsoft.com/en-in/help/3185365/sql-server-2016-agent-fails-to-start-or-failed-to-retrieve-data-error

But the above issue in the Microsoft Support Page is for SQL Server 2016, where the resolution is to update the ODBC Driver 13.1

But in my case it is SQL Server 2012, and in ODBC Data Source > Drives - no ODBC version is to be found other than Native Client.

This issue is occurring only on one of the instance among the total 3 clustered instances on the server.


texts: Multiple Instance with different service packs in SQL Server

texts: I would like to install a Default instance with SQL Server 2008 R2 RTM and I want to install another named instance with SQL Server 2008 R2 SP2. Is this possible?


texts: Is dmp file suitable despite ORA-01555 'snapshop too old' warnings?

texts:
Well, the title of the question is self-explanatory.

Export finished successfully with warnings.
Log shows several ORA-01555 snapshot too old errors.

- Is it OK to use that dmp file to perform an import full in another instance?

- Would running the export in restricted mode (no users connected) garantee no 'ORA-01555 snapshot too old' would occur?


texts: Postgresql. Generate sets of random integers

texts:
With this command I can generate 16384 random integers between 1 AND 200,000.

SELECT generate_series (1,16384),(random()*200000)::int AS id

I want to generate 10 sets of such integers. Each set must have an integer identifier, something like that:

  1 | 135

  1 | 1023

  ... end of first set of 16384 random numbers

  2 | 15672

  2 | 258732

  ... end of second set of 16384 random numbers

Is this possible with an SQL command, or should I write a function for that?


texts: mysqlbackup 8.0.16 does not recognise --defaults-extra-file option

texts:
OS-RHEL 7.4

MySQL Server- 8.0.16 commercial (trial)

defaults-file=/mysql_data/config/options.cnf

defaults-extra-file=/mysql_data/config/extra_options.cnf

[root@exampledb01 logical_backup]# ls -l /mysql_data/config/
total 12
-rw-r--r-- 1 mysql mysql 1020 Jul 25 15:36 extra_options.cnf
-rw-r--r-- 1 mysql mysql 6164 Jul 25 16:25 options.cnf

I am using mysqlbackup 8.0.16-commercial (trial version) for backing up the
databases in my environment.

I have been successfully using the option (--defaults-extra-file)  with mysqldump program from community and commercial versions 5.5, 5.6, 5.7, and 8.0. I save mysql user names and passwords (and other login information in the file). When I tried to use the same with mysqlbackup, it does not recognise the option.

[root@exampledb01 logical_backup]# mysqlbackup --defaults-extra-file=/mysql_data/config/extra_options.cnf --backup-dir=/mysql_backup/physical_backup/full_backups/2019-07-21 --with-timestamp backup-and-apply-log

MySQL Enterprise Backup  Ver 8.0.16-commercial for linux-glibc2.12 on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starting with following command line ...
 mysqlbackup --defaults-extra-file=/mysql_data/config/extra_options.cnf
        --backup-dir=/mysql_backup/physical_backup/full_backups/2019-07-21
        --with-timestamp backup-and-apply-log

MAIN: [ERROR] unknown option '--~'.

mysqlbackup exit code: 7, Invalid Argument

mysqlbackup failed with errors!

The option defaults-file is very happily accepted by the mysqlbackup program, however, it doesn't do so for --defaults-extra-file option.

The mysqlbackup command reference mentions that the option --defaults-extra-file is acceptable for the program.

I have searched the documentation and googled about it but there is not much I could find.
https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/standard-options.html

I can proceed with the --defaults-file option in my backup script but I am not sure what's wrong with the --defaults-extra-file option.


texts: Keeping data in old Database (SQL 2008) identical to new Database (SQL 2016)?

texts:
I am moving database to new server.

New server has SQL Server 2016 running, current server has SQL Server 2008.

I need to have backup/fallback plan in place for few weeks (in case something goes wrong - I can switch my application back to 2008 Server and continue working).

I am wondering is it possible to enable replication (or alternatives?) and have all transactions/merges be in sync on 2008 Instance so both databases are up to date in case I need to switch to old one?

Due to business requirements, we must be able to fall back to SQL Server 2008.  This is non-negotiable.


texts: which sql server edition is good for practice

texts: Which SQL Server (free/trail) would be good to install, which requires minimum resource and have maximum features. I mean, I can practice mirroring,logshipping and replication. I found this link, but confuse with enterprise and standard edition.


texts: After migrating from MySQL 5.7.16 to MariaDB 10 database fails to start

texts:
MySQL Server Version is 5.7.16-0ubuntu0.16.04.1 (Ubuntu)

I want to switch over to MariaDB, but I can't get MariaDB to accept the existing database. I also tried doing a slow shutdown with InnoDB Engine and importing the database as .sql created with mysqldump.

The import of the .sql fails because the mysql.user table is different and I need to keep passwords, users and grants.

After migration I am getting this error:

  Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

This is the final error before the DB fails to start.

Also mysql_upgrade does not work.

How should I perform the migration?

Edit:

This didn't work either (Official Bug Report).


texts: Foreign keys without any checking or index

texts:
I have a problem on SQL Server that led me to the question of whether it is possible to define a foreign key that is not enforced, not backed by an index, and does not need the referenced columns to constitute an (enforced) key.

Foreign keys do two things:

They enforce integrity - which I am willing to give up in my scenario - and
they can be used by the query planner to drop joins that are guaranteed to
not change the result set's number of rows.

The last thing is what I'd still get - the integrity I'll enforce at the application layer.

(Note that what you can have the database enforce is more limited that what you could have as a droppable join condition. A frequent case is that where you'd want to specify a foreign key with ...foreign key (some_constant, almost_key) references... where some_constant is an illegal immediate constant.)

The reason why I need this is I have a SQL Server specific problem in that it does bad query plans in the presence of such an index when the clustered index is a columnstore index (at least I saw on case of such a bad query plan). Since the feature of combining a clustered columnstore indexes with nonclustered btree indexes is brand-new, I take it that there are still some issues they need to iron out.

However, I'm also curious about whether SQL databases generally always demand foreign keys to be backed by indexes on the foreign and/or the domestic side, or whether there are some that take them as hints but allow the user to enforce.


texts: SSIS - How do I execute a single OLE DB Command for mutiple paths?

texts:
I have an SSIS package (2008 R2) that I'm working on and I find myself duplicating the same OLE DB commands repeatedly and whenever there is a change its a nightmare to go back through and update all the duplicate OLE DB command tasks.

Is there a way to execute a single OLE DB command for multiple paths?


texts: How Maxscale deals with "Select for update transactions"

texts:
We are facing intermitent deadlock issues that doesnt got reflected in the maxscale log nor the mariadb log. Only in the applications triying to do sql transactions in the database.

We use mostly readwritesplit with 2 nodes (yes, i know, bad design) and we dont know exactly how maxscale deals with the select for update queries.

to the slave, because is a read query (select)
to the master, because is a write query (update)

Maxscale version 2.1.16, MariaDB 2.1.29

Thanks!


texts: How do I compare 1000 items every minute over 1 million records in mongodb without pulling all the data?

texts:
I am developing a real time stock price alert system that gets new prices every 1 min. There are 1000 tradable assets from one exchange currently and I plan to add other exchanges soon and 1 million records are stored in the database currently. A user is able to add alerts on any of the assets and is to be notified in real time (every minute)

The user would add alerts of type "Alert me if XYZ reaches 100$" I am using redis to store the current prices and mongodb to store the alerts.If the current price of the asset is 90$, this alert is considered to be a "greater than" alert else it is a "less than" alert. This attribute is represented by direction in the database. Here's what a single alert in the database looks like...

{
    "_id" : //alert id,
    "2" : //user id,
    "3" : e1:XYZ:1, //exchange: asset: direction
    "4" : 100,
    "5" : 0
}

I am indexing this database over the field "3". After 1 min, when I fetch all the stock prices, I want to be able to find all the "greater than" alerts that are below the current price indicating that the price has risen and all the "lesser" alerts that are above the current price indicating that the price has fallen.

Approach 1

I dynamically build a query in mongodb that is of the form

db.getCollections("alerts").find({
$or: [
{3: "e1:XYZ:0", 4: {$gt: 100}},
{3: "e1:XYZ:1", 4: {$lt: 100}},
...2000 more assets here
]
})

The process above is limited by the 16 MB document size restriction applicable while querying Mongodb

Approach 2

I found the above approach was very slow and therefore decided to get all the alerts stored in the database and do the comparisons on my node.js server which takes rougly 2.5 seconds per round to fetch data currently

Right way to do 1000 comparisons like this (server side or mongodb side)?
Can I further reduce the amount of data being sent from the database
to server in any way?

Thanks


texts: Why Snapshots of the model, master, and tempdb databases are prohibited

texts:
I have been researching on snapshots and found at many places including Microsoft

  Snapshots of the model, master, and tempdb databases are prohibited.

My question is why?

Edit:

Actually because at one of our client's server I saw backup statements with Snapshot every hour for each DB at server, I am just wondering if this is useful and what will be the purpose of this

Backup statement

BACKUP DATABASE [master] TO VIRTUAL_DEVICE='{886}4' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024,COPY_ONLY

BACKUP DATABASE [msdb] TO VIRTUAL_DEVICE='{886}4' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024,COPY_ONLY


texts:
We use a ssd disk for master database and SATA disk for slave.

On high write load (300 writes/s), slave delay is very serious. I have set:

innodb_flush_log_at_trx_commit = 0
increased innodb_log_file_size

on the slave, but the delay continues.

the slave is for backup only, no read or write request.

Seconds_Behind_Master: 92265, continue increasing.

some conf:

innodb_buffer_pool_size=15G
innodb_log_file_size=1G
innodb_log_buffer_size=32M

du -hs mysql
338G    mysql

iostat
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sde               0.00    73.00   34.50  185.00   552.00  7086.00    69.59     5.63   25.63   19.19   26.83   3.65  80.20


texts: Migrate SQL Server database to multiple files (shrinkfile emptyfile vs log shipping)

texts:
I have a SQL Server 2008 database in production that we are moving onto a new server. The current database has a single ~400GB .MDF file. The new server will be running SQL Server 2012, and we are running mirrored Intel 910 SSDs. These drives will present us with 4x 200GB partitions.

To make this work, we will need to split the single .MDF into 4 smaller ones using DBCC SHRINKFILE with EMPTYFILE. We have done this in test, and it still takes ~ 3.5 hours to do which is too long. The existing database is OLTP, and 365/24/7 and I know blocking will occur during this process, so we can't do it on production first.

My question, is there a way to backup and restore the database to the new server in a temp location. create the new files, EMPTY the temp .MDF into the new locations, then apply transaction logs after? That way we can move the data while current old production is up and running, then do a short shutdown, apply logs, and bring up the new DB?

Or are there any other options to get from Server A with one file and Server B with 4 files on different drives with minimal downtime?


texts:
I have a big user defined table type variable that has 129 columns. I will store around 2000-3000 records in this table variable at a time and pass it to various stored procedures and functions to get additional data and make modifications.  These additional data and new modifications will then be stored in a new table variable of the same type and returned to the source stored procedure via an OUTPUT parameter. (This is because a table type parameter can only be passed as READONLY.)

This is my pseudo-code:

  SP1
  @tmp tableType
  {
        INSERT @tmp EXEC
        SP2 (@tmp)

        INSERT @tmp EXEC
        SP3 (@tmp)
  }

Should I use a @table_variable or #temp_table?


texts:
If I have rows like this:

lec1 major1 class1 10
lec1 major2 class1 40
lec1 major1 class2 30
lec1 major3 class3 35
lec2 major1 class3 15
lec2 major3 class3 10

And I need it to combine the ratio into:

lec1 major1 0.2 -- 10/(10+40)
lec1 major2 0.8 -- 40/(10+40)
lec1 major1 1 -- 30/30
lec1 major3 1 -- 35/35
lec2 major1 0.6 -- 15/(10+15)
lec2 major3 0.4 -- 10/(10+15)

Or something like this:

lec1 major1 1.2 -- 10/(10+40) + 30/30
lec1 major2 0.8 -- 40/(10+40)
lec1 major3 1 -- 35/35
lec2 major1 0.6 -- 15/(10+15)
lec2 major3 0.4 -- 10/(10+15)

What query sould be done?


texts: wrong number or types of arguments in call to X?

texts:
Here is the code for which I am getting the error mentioned in the question:

CREATE OR REPLACE PACKAGE collegeManagement IS
TYPE studId_scholarshipModification IS RECORD(studentID studenti.id%TYPE, modificationPercentage NUMBER);
TYPE modificationsRecordVarray IS VARRAY(2) OF studId_scholarshipModification;

PROCEDURE modifiyScholarships(modificationsList IN modificationsRecordVarray);
END collegeManagement;
/

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ --

CREATE OR REPLACE PACKAGE BODY collegeManagement IS

v_currentSholarship studenti.bursa%TYPE;

PROCEDURE modifiyScholarships(modificationsList IN modificationsRecordVarray) AS
BEGIN
    FOR i IN modificationsList.FIRST..modificationsList.LAST LOOP
        dbms_output.put_line(modificationsList(i).studentID);
        IF modificationsList.EXISTS(i) THEN
            SELECT bursa INTO v_currentSholarship FROM studenti WHERE id=modificationsList(i).studentID;
            IF v_currentSholarship=NULL THEN
                UPDATE studenti SET bursa=100+100*modificationsList(i).modificationPercentage WHERE id=modificationsList(i).studentID;
            ELSE
                UPDATE studenti SET bursa=bursa+bursa*modificationsList(i).modificationPercentage WHERE id=modificationsList(i).studentID;
            END IF;
        END IF;
    END LOOP;
END modifiyScholarships;
END collegeManagement;
/

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ --

set serveroutput on;

DECLARE

    TYPE studId_scholarshipModification IS RECORD(studentID studenti.id%TYPE, modificationPercentage NUMBER);
    TYPE modificationsRecordVarray IS VARRAY(2) OF studId_scholarshipModification;

    modifications modificationsRecordVarray:=modificationsRecordVarray();

BEGIN

    modifications.extend;
    modifications(1).studentID:=500;
    modifications(1).modificationPercentage:=0.4;
    modifications.extend;
    modifications(2).studentID:=501;
    modifications(2).modificationPercentage:=0.8;

    collegeManagement.modifiyScholarships(modifications);

END;

I already tried debugging the code using the steps suggested here but for the moment I can not understand which is the cause for the error.


texts: Database design -- companies, employees, individuals

texts:
I'm having troubles modeling this database schema, will appreciate any help. Here is the requirements and description.

A user on registration creates his profile.
User can be an individual or a company, which is selected on registration.
User of type company can have multiple employee profiles.
All profiles have some data in common, like name, location, images, phones.
Company profiles have additional details like company type.
Humans (individuals & employees) share some additional details like age, gender.
Businesses (individuals & companies) also share some common details like work schedule, pricing.
Most frequent queries would be:

showing all profiles (company & individual & employee);
filtering profiles by selected types (company and/or individual and/or employee);
filtering all profiles by common details; filtering all profiles of selected type (all humans, ind. humans, employees, companies) by the type's details.

Profile of any type can be created by an admin, w/o linking it to a user.
One user has only one profile.
Only company profile can have multiple employee profiles.
An employee can't work for multiple companies.
An employee can't be the same person as some individual.

I want to normalize this as much as possible and then possibly denormalize something to improve usability and speed.

Database vendor preferably lowest common denominator – MySQL, possibly PostgreSQL, if it suits this task better.

My initial schema (I think it sucks, because it doesn't enforce many of the constraints and badly expresses the domain):


texts: If your table is named Articles, do you name your column article_number or simply number?

texts:
A question that I ask myself quite often is: If my table is named articles, should I name my column article_number or simply number?

Is there a best practice? Is there a difference if you create tables and columns in a database, or work in an abstract layer like an ORM, where the query would perhaps be

a = Article.get(1);
my_num = a.number;
// or
my_num2 = a.article_number


texts:
We have SQL Azure database running with our website. However today's outage of SQL Azure give us a setback. And now we are looking a way to replicate [as I read on internet] is best to do have 2 database running in some sort of fault tolerance mode. Well, Frankly I didn't know much of terminology or don't have much knowledge. I am software developer and can just manage 100-200MB Database.

Now, my question/request is what we can do to ensure that if one SQL Azure DB is down then we can back our site on other database, which is same, with almost latest data from main database.

I try to search on internet, but most of article use terminology that I hardly understand them. Maybe some wise word will help me find direction. Thanks.


texts: Index Optimization for Datetime comparing day of week and hour

texts:
I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day: model_id int, hour_of_day int, model_val int.

To gap fill missing values, we would join these two tables through a crossover table that is: sensor_id text, day_of_week int, model_id int

If our observations table is massive, what would be an optimal way of indexing it for joining on isodow and hour. Does indexing a timestamp also index functions like EXTRACT(isodow FROM obs_ts) or should I make those functional indexes explicit, e.g. CREATE INDEX ON observations (EXTRACT isodow FROM obs_tx). For joining on hour, would it be better to convert the hour_of_day to a timerange?


texts: Inspecting Anonymous Column in Stored Proc Resultset

texts:
I am writing an SQL Script using a Stored Procedure, that somebody else wrote and I cannot change, which identifies whether or not a record can be safely deleted, based on its dependents.

My problem is that this stored procedure returns an anonymous resultset in the form of

IF <Condition>
BEGIN
  SELECT 'N', 'Cannot be deleted'
END
ELSE
BEGIN
  SELECT 'Y', ''
END

Now, I think this is poor design on the part of whoever wrote this because the messages are redundant, but that is besides the point because I cannot change it and so must work with what I have got.

The problem I have is that when I go to execute this proc

IF (EXEC CanDeleteRecord @RecordID = @Record_ID)

I don't really know how I am supposed to select the first column of the result set to compare with 'Y' or 'N' because the columns are anonymous. Is there some way of enumerating the results? This is a problem I have never encountered before with SQL and Google turns up squat.


texts:
I have a several process that aggregate some statistics on items (how much was shown, clicked ... around 30 factors).
Once in a 5 minutes i flush the aggregate data to postgres (9.1).
I have 250K aggregate items with statistics.
tabe key:
item_id,process_id (in order to avoid locking process-id is part of the key),channel,(more 2 fields).

The process is like this:
open transaction
try to update (most of time this item already exist in db, so update success).
update is done like this  - update statistics set counter_a= counter_a+1... where id = x and channel = y and ...;
insert in case update failed.
commit

This take 15 minutes for 250K updates.
Any advices?
I saw this question :
Optimizing bulk update performance in PostgreSQL
But the case there is not relevant.

I cant drop indexes because i need them for the update.
Any way to avoid the overhead of the mvvc when i know that this is the only process that will write the data and no need in isolation or even transaction in case of failure.

db machine run on "hi1.4xlarge" amazon instance (64G RAM , 8 cores).


texts:
First step in a long process of creating a Linked Server to an Informix database on a Sql 2008 (Win 2003 x64) cluster.

I was wondering how to go about creating an ODBC connection as a clustered resource, or if I even have to do that.

The OLE DB provider "MSDASQL" for linked server "__server__" reported an error. The provider reported an unexpected catastrophic failure.
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "__server__". (Microsoft SQL Server, Error: 7399)

More info: Using IBM's OLEDB and ODBC drivers. It worked on a dev instance but not on a nearly identically-configured prod instance.


texts: granting access for remote account to local database instance

texts:
i have two computers in a same domain, there is database instance on one of them, which is the DC, the question how can i add an account (reside on the other computer) to grant access to that DB!. i tried to add the account in login window but it didn't work! it is even not listed.
these screenshots will help:


texts: MySQL proxy and SHOW PROCESSLIST query time

texts:
I have MySQL replication currently occurring between two masters bidirectionally, one in a data center locally, another over at Amazon EC2.

Everything seems to function normally replication wise, no issues per se, except for the occasional query that causes a collision but those are few and far between. Recently I set up mysql-proxy to attempt to load balance the two servers, the load balancing really on kicks in after the local machine receives 40 or more connections at which point subsequent database connections get shuffled off to the EC2 machine.

Something we noticed recently is that mysql proxy will notify us that there are 41 connections and then start its balancing. However when I connect to the local machine and do a SHOW PROCESSLIST; it may only provide me with 30 connections.

Anyone have any idea why this maybe?

In addition to that as a result of issuing the SHOW PROCESSLIST; command I've noticed that there are a great many queries running on both machines that state they have been running in excess of 5000 seconds. I'm pretty sure these are "zombie" queries but does anyone know why they are created in the first place?

FYI We're running mysql version 5.1.54 on the latest versions of ubuntu and debian.

Any ideas would be extremely helpful.

[Addendum]

Turns out we're not using mysql_pconnect and are infact using the mysqli libraries. I still have been unable to find out why this happening and will report back once I find out.


texts: Inheritance and foreign keys in Postgres

texts:
This is about using inheritance and foreign keys in Postgresql databases.
Consider the following simplistic example whose structure is based on what I am building at the moment (but the specifics were contrived in realtime just for this question, so please excuse any shortcomings!):

Parent table1: Person (columns: ID, Name). Child tables: Man, Woman.
Parent table2: Relationship (columns: ID, Partner1 and Partner2). Child
tables: Gay, Lesbian.

Each table has a primary key set on the column ID.
The table Relationship has two foreign keys set on columns Partner1 and Partner2 which reference the table Person (column ID).
The (inherited) tables Gay and Lesbian also need to have foreign keys set on their Partner1 and Partner2 columns. The question is whether these foreign keys should reference the parent table Person, or whether they should reference (as appropriate) the child tables Man and Woman.
The questions comes up because, as stated in the manual for v9.6, section 5.9.1 :

Caveats
... A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply
to single tables, not to their inheritance children ...... These
deficiencies will probably be fixed in some future release ...

To me, this (the fact that foreign keys do not apply across inherited tables and must be done separately) is a feature, and not a limitation. And a very useful feature too, as can be seen in the case of the aforementioned example:
When the child table Lesbian references the child table Woman (instead of the parent table Person), it is very easy to prevent errors of the sort where there's a lesbian relationship between two men!

Of course, constraints can very well be imposed to achieve this, but it seems to me as though what I wrote above is a more elegant way of doing things. But I am also concerned about what the manual states towards the end - that the development team sees this as a problem, and might get rid of it. So I am also worried if my design would totally break after a future upgrade.
Any tips on the design above and suggestions for alternate ways would be most appreciated.
I would also be very grateful if there's someone from the Postgres dev team lurking around here, and is kind enough to comment.


texts: Database User not reported as orphaned after Windows login dropped

texts:
I have dropped a SQL Server Windows Login. I then ran the below code to check for orphaned database users. However, the database user corresponding to the dropped Windows login does not appear as an orphaned user.

Why would this be?

EXEC sp_change_users_login @Action = 'Report';


texts: How to change the default delimiter in PostgreSQL 9.1.9 using SET?

texts:
From within a bash script I tried to use a replace and a detection on a field containing a pipe like:

IF position ('|' in var) IS NOT NULL THEN ...

...REPLACE(field, '|', '#')

The data itself was loaded into the DB using the COPY statement e.g.

COPY records FROM '$datasource' WITH DELIMITER AS ',' CSV HEADER NULL as '';

I am using psql (9.1.9) and the default field separator is pipe:

#> \f
#> Field separator is "|".

Ideally I would like to SET the default limiter in a CREATE OR REPLACE function at runtime like:

...
END; \$\$ LANGUAGE plpgsql SET config_param TO '#';

OR as a seperate statement just like
    SET config_param TO '#';

I searched the documentation and internet for the right name of the config parameter without luck. Any help appreciated.


texts: How to remove an installed AE certificate from server using PowerShell Scripts?

texts:
I have created the encryption certificate using below PowerShell script in dev. environment. Now, I need to create deployment document to configure the encryption on staging environment.

$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:LocalMachine\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

I need to keep the rollback PowerShell scripts to remove the certificate from local machine. What is the correct way of removing the certificate using PowerShell scripts? Can anyone guide me on this?


texts: sp_help_revlogin for contained database users?

texts:
I've been using sp_help_revlogin for a long time to transfer server principals from one SQL instance to another.

Is there a sp_help_revlogin equivalent that can output create scripts for partially contained database users? I understand that if I backup/restore the contained user comes along, but I have a need to deploy users from one contained database to another, different, contained database.


texts: Is there such thing as Custom Data Types?

texts:
Does MySQL have any support for custom data types? For example, zip codes might be stored in a varchar(10) field, but they could be compacted in to an int, with options for blank, and a flag as to whether it is a 5 digit or 5+4 digit zip code.

Is there a way to install seamless data types for such things? As far as the application is concerned, it would be a string type, there would simply be data truncation (with or without warning), if the application passed invalid data.

Custom functions could be used (for example, there is a built in function INET_ATON for IPv4 addresses. But that does not allow for such things as zip LIKE '12345%' which should be properly indexed. A well written support for custom data types would allow a data type to be marked as sortable. So the compact zip int, when sorted, would sort just as if it was a zip varchar(10).

This would allow for the column to be fixed width, it would allow 6 or 10 byte variable storage to be cut down to 4 byes of fixed width.

There are several applicable uses

Zip Codes
IPv6 addresses
Custom timestamp fields with minute level precision and capacity beyond 2038 with less storage usage than datetime, but no need to support dates before the year of implementation (say the min could be 2007 if those are the oldest dates in the system)
Timestamps that implement DST (which don't seem to exist)
Two letter US state can be stored in a single byte
long ENUMs can be separated into a custom data type so that DESCRIBE's output would not be so messy looking with all the wrapping.

I expect the data type handlers would be stored similar to the way functions are stored.

Is there anything remotely like this on any database engine? I mostly use MySQL, but I am curious if this has ever been implemented, short of making the application call a function like the INET_ATON function.

MS SQL does seem to have something of that nature, but I would like to know if it is more than just a synonym. (for example boolean could be a synonym for tinyint(1), or postal_code for one of char or varchar (5 or 9 or 10)) Synonyms are not what I am asking about here.


texts:
I've just adopted a Postgres 9.0 database running on RHEL 6.1 on a rather beefy 256 core, 2TB system with ok drive (Hardware RAID 50).

I've started benchmarking the system using pgbench so I can get an idea of what this thing can do.  The application spends most of its time reading data, so I used the simple 'select' bgbench tests with the following parameters against a data set initialized with a scaling factor of 300:

pgbench -S -j 8 -T 60

I recorded the TPS reported from pgbench using n+8 (n = 0 to 16) clients for 16 samples.  I produced the following chart from the results:

My question is, why the peak at 16 concurrent connections?  I ran pgbench from the same host and looked at IO, memory, CPU utilization, all the usual suspects and found nothing that stood out as being a cause.  For postgresql.conf I have the following settings:

max_connections = 1024
shared_buffers = 16G
temp_buffers = 2G
work_mem =128M
maint_work_mem = 1G

%iowait is ~3.8% w/ 16 connections, 8.2% w/ 32, and 8.9% w/ 48.

I recently saw that PG 9.2 has a new 'feature' (http://www.postgresql.org/about/featurematrix/detail/218/) labeled as 'Multi-core scalability for read-only workloads' but this seems like a red herring.  I can't help but think it a configuration issue.

TIA.


texts: db_owner unable to drop database - Error 615, SQL Server

texts:
I have a SQL Server 2012 database running on amazon EC2. I've created a user to be able to create, edit and drop databases. I gave the new user with dbcreator server role.

My user can connect remotely and successfully runs the create database foo; command. But when the user attempts to drop the database again with drop database foo; the command fails with the following error:

Warning: Fatal error 615 occurred at Feb  1 2014  5:15PM.
   Note the error and time, and contact your system administrator.
ErrorCode: 21

Even though the selected database is master (so I don't think it's because it's in use). And the command succeeds when I run it again logged in as an administrative user.

I checked the newly created database, and my user has been assigned the db_owner role in the database as I expected so my understanding is that this should be sufficient permissions for that user to be able to drop the database they just created.

According to http://technet.microsoft.com/en-us/library/ms178613.aspx the db_owner role should be sufficient permissions. "Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role."

I've looked up error 615 and found "Could not find database table ID %d, name '%.*ls'." which makes no sense to me. http://technet.microsoft.com/en-us/library/aa937592(v=sql.80).aspx

Sql server version info: Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64) /n    May 22 2013 17:10:44 /n Copyright (c) Microsoft Corporation/n   Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)/n - from select @@version.


texts: T-SQL Query to calculate Settele and withdraw amount in MSSQL- SERVER

texts:
I have a table with below structure :

FactSatnaEvent(OPRDATE date,OPRCOD int, voucherno int, SATNAPAYATYPE int, CFCIFNO int , AMOUNT int)

based on this table I wrote this query

       SELECT
          S.OPRDATE ,
          S.CFCIFNO ,
          SUM(CASE WHEN S.SATNAPAYATYPE=1 THEN S.AMOUNT ELSE 0 END) AS SATNA_DEPOSIT_AMNT,
          SUM(CASE WHEN S.SATNAPAYATYPE=2 AND
                        S.OPRCOD=0 AND
                        S.voucherno<>-1 THEN S.AMOUNT ELSE 0 END) AS SATNA_WITHDRAW_AMNT,

          COUNT(CASE WHEN S.SATNAPAYATYPE=1 THEN 1 ELSE NULL END) AS SATNA_DEPOSIT_COUNT,
          COUNT(CASE WHEN S.SATNAPAYATYPE=2 AND
                          S.OPRCOD=0 AND
                          S.voucherno<>-1 THEN 1 ELSE NULL END)   AS SATNA_WITHDRAW_COUNT
   from factsatnaevent s
   where s.cfcifno<>-1
   GROUP BY S.OPRDATE ,
            S.CFCIFNO ;

I Just want to know if there are better way to write this query .
I need to have this output

OPRDATE   CFCIFNO    SATNA_DEPOSIT_AMNT    SATNA_WITHDRAW_AMNT     SATNA_DEPOSIT_COUNT   SATNA_WITHDRAW_COUNT

Please tell if any further information is required. Thanks in advance


texts: How to design data about a column can belong to the whole table?

texts:
I'm not a DBA so I apologize for lack of knowledge.
Here's my situation...Say I have several tables. Products, Customers, Vendors, etc.

Our application may have a help text for some of the data in those tables.
Products may hold a column "SpecNumber" which needs to be in a specific format that may need a help text to display to the user, Customers table may hold another column, and so forth.
Our application will need a way to maintain this list of help text so keeping it as one table with maybe ID, helpText, etc. May have other columns if we need to keep other information about the field in the future.

Now, how do I go about querying this help text?
In an application where I display a list of products, for "SpecNumber", I want to show this text once on the form. So having a foreign key to HelpText table in Products table makes no sense.

Products table may have Id, Name, VendorId, MedicalCode. (sample data may be Id=1, Name='MedicineABC', VendorId=12 (foreignkey to vendor table), MedicalCode='123ABC3232') HelpText table may have Id, description.

Products
--------
  ID
  Name
  VendorID
  MedicalCode

HelpText
--------
  ID
  Description


texts: Can you make SSL and SSL-only (hostssl) connections to PGPool?

texts:
The new version of PGPool (3.2) has an "SSL" section in the conf file, and directions on how to use it in the manual. However, pool_hba.conf specifically says that PGPool doesn't support SSL (i.e. using "hostssl" to force ssl connectivity in the conf file). These seem contradictory. My company wants me to experiment with PGPool, but only if they have assurance that it will work with SSL forced always-on from the client side (from  psql/postgres drivers in our web applications).

This leads me to two questions:

Does PGPool 3.2 or newer support inbound SSL encrypted Postgres client connections?

Does PGPool 3.2 or newer support SSL encrypted connections to its backend Postgres instances?

Anecdotal experience or a reference to someone with it is preferred; the documentation seems spotty.


texts: Storing a 3D point array representing a flight path?

texts:
I need to store an array of 3D points in a column: latitude, longitude, altitude

What datatype would be required for this?

Also, if I wanted to extend this in the future to include time (ie an array of 4D points), how could I do so?

I have gone for a custom datatype, but I am not sure if this is wise:

CREATE TYPE position3d AS (f1 DOUBLE PRECISION, f2 DOUBLE PRECISION, f3 DOUBLE PRECISION)

I then created a new column with type position3d[]


texts:
I'm new to CouchDB. I'm going through its config. Current configuration is as below.

n = 3

q = 8

I understand n = 3 means 3 nodes and q = 8 means 8 shards. I don't have a cluster in place. Is it possible to make n = 1 without affecting data consistency? (This is a production DB.)

Will it help me reduce overall DB size?


texts:
Is it possible for an entire set of committed transactions to be rolled back in SQL Server 2008 R2 without any notification or logging anywhere?

We had a set of data apparently get rolled back this weekend on an HA cluster SQL Server 2008 R2 environment. Data existed and now it doesn't. Nothing in the SQL Server error logs, nothing in the cluster logs. SQL Server agent job that originally loaded the data ran successfully (including progress emails sent from the processes themselves).

The only thing that wasn't rolled back was the bulk inserted data. The database is set to full recovery, so bulk inserted data SHOULD have been rolled back as well, correct?

Sorry for the lack of details but I am THOROUGHLY stumped. Can't even think where else to LOOK.


texts:
There is a user with read permissions on my SQL Server. I don't want them to see my critical columns on a table. How can I censor those columns to the user?

Could I use data masking?


texts: Why can't unused columns and tables be ignored in a recursive CTE

texts:
Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and joined tables are queried, even if not necessary. Why is that? Can I help SQL Server to ignore unused columns/tables here?
Example:
;WITH cte AS
(
    SELECT cn.CatalogNodeId,
           cn.ParentId,
           cn.CatalogNodeType,
           (SELECT Name FROM dbo.LocalizedCatalogNodes WHERE CatalogNodeId = cn.CatalogNodeId) AS Name
    FROM dbo.CatalogNodes AS cn
    WHERE cn.ParentId IS NULL
    UNION ALL
    SELECT cn.CatalogNodeId,
           cn.ParentId,
           cn.CatalogNodeType,
           (SELECT Name FROM dbo.LocalizedCatalogNodes WHERE CatalogNodeId = cn.CatalogNodeId) AS Name
    FROM dbo.CatalogNodes AS cn
    JOIN cte ON cte.CatalogNodeId = cn.ParentId
)
SELECT CatalogNodeId FROM cte

This is a simple recursive CTE resolving a hierarchical parent-child structure, adding a localized name to each node.
Even though not necessary, the execution plan shows that column CatalogNodeType is retrieved and  dbo.LocalizedCatalogNodes is joined for each iteration.
Now comment out the UNION ALL part making it a non-recursive CTE, and the execution plan suddenly consists of just two steps, not containing column CatalogNodeType or table dbo.LocalizedCatalogNodes.


texts: What's the best practice when deploying Redis replication cluster on 2 data centers connected by MPLS?

texts:
I deployed Redis replication cluster on 2 data centers (ping time between 2 DCs is around 1ms).

I used  Master (Node1 on DC 1) - Slave (Node2 in DC2) replication model and Sentinel to control promoting or demoting.
I faced some scenarios with issues of network and VMs (all Redis nodes using virtual machine)

Node 1 (Master) has network interface issue,  all nodes cannot reach Node1, Sentinel promote Node2 to Master. After 1-2 minutes,  Node1 back normally, what happens in this case? (Node1 Master and Node 2 Master also)
Node 1 (Master) down, Sentinel promote Node2 to Master. After 2 hours, Node1 back, when Node1 synchronizes data from Node2, suddenly Node2 down. What happens with Node1? (assume node2 cannot back totally)
Node 1 (Master) and Node 2 (Standby) in turn down after 5 minutes. Ex: at 10:00 am, Node1 down, at 10:05 am Node2 down, node1 back. At 10:10am, Node1 down, node2 back and so on...
What happens?

Case 1 and 2 are reality. Case 3 is my assumption when node1 and node2 unstable often. My solution for 3 cases is using script manual promote or demote and does not use Sentinel because I don't believe it can process complex cases.

Could you give me advice on my cases?


texts:
My laptop has a small hard drive. I want to load a big data set. I have moved the entire /usr/local/var/postgres to the external hd, and symlinked it back. Now I'm getting this error,

    psql: could not connect to server: Socket operation on non-socket
    Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I have now found the problem.  Changing the default data directory causes Postgres to refuse connections to the server. The online Manual is a little [sic!] unspecific about "config files in the same directory"…

The solution I used (with psql) is to:

    CREATE TABLESPACE tablespacename OWNER username LOCATION '/Path/to/Folder'

then,

    CREATE DATABASE databasename OWNER username TABLESPACE tablespacename

and finally

    psql databasename < /Path/to/databasedump.sql

NOTE to future visitors of this question; please see the warning below from Craig Ringer regarding how to solve this problem properly.


texts:
I recently installed Oracle 11g R2 on a Windows 8.1 machine, but for some reason SQL Developer does not seem to launch easily.  What specific steps do I need to take in order to launch a working SQL Developer instance?

Here are my steps so far (There are print screens in hyperlinks below because I do not have enough reputation points to embed the print screens in this posting.):

1.) I searched for SQL Developer in Windows 8 and clicked to launch the resulting entry for the program.

2.) The resulting dialog box then said it was searching for a named SQLDEVELOPER.bat file, and when it could not find the SQLDEVELOPER.bat file, it gave the following dialog box suggesting that it use a different selecthome.bat file instead:  http://s23.postimg.org/4nr1d1r8b/sql_developer_delete.jpg

3.) I hesitated to authorize what looks like a hard to un-do setting, so I looked in the windows file system and found the following items in the root directory of Sql Developer:  http://s30.postimg.org/5vtk3k269/sql_developer_directory.jpg

Note that there is a sqldeveloper.sh file in the directory.  There is also a sqldeveloper.exe file, but clicking on that file does not launch the program.

So what steps need to be taken in order to successfully launch Sql Developer?

ONGOING EFFORTS:

I followed the advice of @SoleDBGuy and downloaded and installed a newer version of SQL Developer.  But I am not yet able to make a database connection from it to validate that the new SQL Developer can work with my existing 11G R2 installation.

I validated that the 11G R2 installation is running and that I know the password by using the windows command line to connect by typing sqlplus system/password, where password is the real password.

But I am not yet able to connect to the same 11G R2 instance with the same credentials through the new SQL Developer because the xe listener is being rejected.  I looked this up and it seems that the xe listener is associated with the express edition.  This may be the problem as I installed the enterprise edition of Oracle 11G R2.  I did not install the express edition.

So my next step is to try to determine what listener to use to connect to the enterprise edition, and to try replacing xe with the name of the correct listener for the enterprise edition.  If this works, then I will have validated a solution and resolved this question.

RESOLUTION:

I was able to validate @SoleDBGuy's approach by creating a connection using orcl as the SID in the SQL Developer Add New Connection Wizard.  Initially, the connection wizard was not connecting because the default listener is ex, which is for the express edition.  I installed the enterprise edition, so the ex listener did not work and I had to use the orcl edition instead.

Once I connected to the database, I then validated that it was the correct database by running the query select tablespace_name from users_tablespaces;.  When I saw the correct table spaces, I was able to validate @SoleDBGuy's approach and mark his answer as accepted.


texts: Slow insert on conflict do nothing for Postgres 13

texts:
I'have rather odd case.
I have a "cache" table in Postgres and I need to insert 100K - 1M records in parallel from different sources. Sources can try to insert duplicated data (by Primary key).
I'm too lazy to write complex sync code around INSERT process that is why I do it this way:

create UNLOGGED table WITH (autovacuum_enabled=false)
do insert into TABLE (foo,bar) values (1,2) on conflict do nothing.

I'm fine with lower perf compared to the COPY command since writing synchronisation logic is 100 times more expensive from business point of view than slow insert. We can live with relatively slow inserts.
BUT the performance is waaay to slow.

It takes around 12.000 ms to insert 50.000 rows. around 4-5 records per millisecond.
Each row has 150 columns.
insert batch size in 50.000
Table has single PK (I can't drop it)
The pattern is to insert 100K-1M records.
Table won't grow more than 10M records.
Postgres version is 13.2
I'm using JDBC driver 42.0.0
Table has only on PK (varchar, long)

Why is it so slow?
Can I do something with it?