texts:
I have a master that has 298 relay bin files as recent as today, going back well 298 days.

There is no relay-log definitions in the .cnf

and

mysql> show variables like '%relay%';
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| innodb_overwrite_relay_log_info | OFF            |
| max_relay_log_size              | 0              |
| relay_log                       |                |
| relay_log_index                 |                |
| relay_log_info_file             | relay-log.info |
| relay_log_purge                 | ON             |
| relay_log_space_limit           | 0              |
+---------------------------------+----------------+

Reset slave clears them out, but then they just start getting regenerated.

Any idea what's causing this?  How to stop it?

EDITS TO REQUESTS

General critiques of the cnf are welcome but let's keep the OP topic in mind.

---- cnf request

[mysqld]
character_set_server = utf8

max_connections=200
max_user_connections=160
max_connect_errors=10000

userstat_running = 1

log_warnings
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2

innodb_file_per_table

innodb_open_files=2048

innodb_additional_mem_pool_size=1M

innodb_buffer_pool_size=512M

innodb_log_buffer_size=1M

innodb_log_file_size=128M

innodb_autoextend_increment=16

innodb_flush_method=O_DIRECT

datadir=/var/lib/mysql/

tmpdir=/var/lib/mysql_ramdisk

server-id=2

log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/mysql.index

key_buffer_size = 800M

preload_buffer_size = 256K

max_allowed_packet = 8M
table_cache = 512
sort_buffer_size = 8M
join_buffer_size = 8M

read_buffer_size = 2M
read_rnd_buffer_size = 2M
thread_cache_size = 32
query_cache_size = 32M
query_cache_limit = 16M

myisam_sort_buffer_size = 2000M

tmp_table_size = 64M
max_heap_table_size = 64M

---- now for the cli requests

mysql> show slave status\G
Empty set (0.00 sec)

mysql> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| awesome-bin.xxxxxxx | yyyyyyyy |              |                  |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

---- version

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.1.47-rel11.1-log |
+--------------------+
1 row in set (0.00 sec)


texts: Files left behind by killed `CREATE INDEX` commands?

texts:
I wrote a migration for a web application that ran CREATE INDEX as part of its deploy. When it took too long to create, our deployment system ruled it as a failed deploy, killed the deployment, and tried again. It did this until our deployment system gave up on deploying it altogether.
This led to about 20 CREATE INDEX processes on a large table, which was locking rows and pegging the CPU/IO, so we killed the pids.
Now, outside of this deploy process, I'm running a (more responsible, with time to run) CREATE INDEX CONCURRENTLY, but getting:
ERROR:  could not write to file "base/pgsql_tmp/pgsql_tmp18746.4": No space left on device

It could very well be that we're just out of space, but I suspect brutally killing ~20 large index creation processes left some large files that Postgres isn't able to use.

Is there a way to inspect/remove such files, if they exist?
Any other poor states I should look for?


texts: Consistent drop in performance on SQL Server 2016 SP2

texts:
Recently upgraded from 2008R2 to 2016SP2. Have a slow process that I can't change. Application contacts the server back and forth constantly and runs tiny queries, including doing an update 30K+ times serially.

I've tried any suggested indexes, etc...to try to help since it's a sticking point for completing the upgrade to production. The new server is loaded with twice as many procs, double the memory, etc..

The interesting thing about the process is that in testing it, using the same process and parameters, every time the server is restarted, it runs fast, then each subsequent run is about 2 minutes slower. It steadies about about the 5th or 6th run, but it's this same pattern every time. I would expect it to get better as it stores the plans and reuses them, but it's consistently the same behavior. I know that the underlying process needs to be corrected, but I don't have any control over that atm, and I find the pattern very odd, since nothing else is running on the server.

Testing has consisted of running the process from the app several times after each change. Started in 2008 compatibility mode, reindexed and updated, stats, applied missing indexes based on cache info., updated to use current 2016 Query Optimizer and Cardinality Estimator, added Optimize for adhoc queries, moved to full 2016 compatibility mode. With each change, the same pattern ensues.

The baseline runs against a 2008DB and is consistently somewhere in the middle of the times I get for 2016. It is run from the same application and workstation.

VMs for the app server and DB server. No AGs yet. The queries are all trivial in time. There's tons, but the queries themselves aren't slowing down. Max memory around 64GBs (and nothing else is hitting this server right now),

*Update:
I'm sorting through some Extended Event logs now to see if I can pare down specifics as to what runs longer. Even sorting out the query hash = 0, each run executes 13602 executions (ad hoc sql statements + rpc completed), so it's a lengthy process. Was hoping for some insight as to why a SQL Server restart would have a 10 minute difference on the duration of the process since nothing else is running on this server. I would think a restart would make it slower the first few runs, but it's exactly the opposite. Even things like sp_setapp role are taking longer on subsequent runs.

Any ideas why I would have query hash plans all equal to "0" on queries over 20 secs? Is this telling me these are compilation timeout queries?


texts: FillFactor: IndexOptimize script by Ola Hallengren

texts:
We are using Ola Hallengren's IndexOptimize script for index rebuild on SQL Server 2008 and 2012. These maintenance scripts are very good and have worked for our DBs for months now. However, very recently I noticed that it changed the FillFactor to 90 after it executed index rebuild on the DB even when the FillFactor parameter was NULL.

Prior to executing the script, I checked sys.indexes had FillFactor of 0 for all indexes but I am not sure why the script changed FillFactor to 90 after it completed. This caused a lot of issues on the database.

Do you know if this is the intended behavior in IndexOptimize script where the FillFactor gets changed to 90?

Here is what I executed:

EXECUTE dbo.IndexOptimize
 @Databases = 'dbname',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @LogToTable = 'Y'


texts: Why does CREATE INDEX ... WITH ONLINE=ON block access to the table over a period of minutes?

texts:
I have an existing table:

CREATE TABLE dbo.ProofDetails
(
    ProofDetailsID int NOT NULL
        CONSTRAINT PK_ProofDetails
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , ProofID int NULL
    , IDShownToUser int NULL
    , UserViewedDetails bit NOT NULL
        CONSTRAINT DF_ProofDetails_UserViewedDetails
        DEFAULT ((0))
);

This table has 150,000,000 rows.  The system is in operation 24x7x365, so there are no regularly occurring maintenance windows.

I want to add an index to the table, and with the Enterprise edition of SQL Server, I should be able to do that without blocking write access to the table.  The command I used was:

CREATE INDEX IX_ProofDetails_ProofID_Etc
ON dbo.ProofDetails (ProofID, IDShownToUser)
INCLUDE (UserViewedDetails)
WITH (ONLINE=ON
    , ALLOW_ROW_LOCKS=ON
    , ALLOW_PAGE_LOCKS=ON
    , FILLFACTOR=100
    , MAXDOP=4
);

I executed the statement by itself in SSMS, by pressing F5. It ran for over a minute, then began blocking other sessions.  I then immediately cancelled the CREATE INDEX command since I cannot block other sessions.

During the first minute, nothing was blocking my CREATE INDEX command, sys.dm_exec_requests showed the process with a wait type of CXPACKET - of course.  I don't think that is a bad thing since the operation was parallelized.

I didn't have a lot of time to inspect the output of sys.dm_exec_requests.  There was only a single row returned from the query WHERE session_id = xxx.  The blocked sessions were attempting to insert rows into the target table.

I don't know how long the locks lasted, except to say I cancelled the execution of the statement around 2 minutes after it started. Blocks were occurring for around a minute at that point.

Am I misunderstanding the implementation of WITH (ONLINE=ON)?  Or is there something else I need to be aware of?

The server is a fairly beefy machine, with 2 quad-core Xeon E5-2643 3.3Ghz processors, 192GB RAM, and SAN storage capable of 5,000+ iops.  CPU is typically below 20%, RAM is 93% utilized, mostly by SQL Server.  There is nothing else running on the box, just Windows Server 2012, and SQL Server 2012.


texts:
We are having problems on SQL Server 2012 with contention on tempdb page 2:1:128, which seems to be related with system table sysobjvalues.

We have about 400 sessions waiting on this resourse 2:1:128 with wait type PAGELATCH_EX.

Nothing has changed in our application or on database, so we don't know the reason for this. We've tried to identify the offending query (if there is one) without success.

The problem starts to occur all of a sudden and a couple of hours later it goes away. There are no backups running except for the transaction log backup every 15 minutes taking 1 to 2 minutes.

tempdb is using 8 files, on a 2 CPU x 4 cores machine. Anyway, the latch seems to be unrelated to IO problems.

We have 256Gb of RAM.

CPU is normal on the machine for the load it has (80%), and IO is normal also. The number of transations processed is also normal.

Here is the result of SELECT @@version:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    Dec 28 2012 20:23:12
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2  (Build 9200: )

Any ideas on what is happening or where should we look to find the problem?


texts: Unable to import DMP file that is made in 11g2 to Oracle 18c

texts:
My OS=CENTOS 7
Oracle=18c XE

I am trying to import a DMP file that is exported from XE 11g2 and now trying to import it in 18c XE.
Here are the errors:

[root@linux gsw]# impdp user/pass remap_tablespace=SYSTEM:myUserName remap_tablespace=USERS:myUserName dumpfile=myUserName.dmp directory=myUserName

Import: Release 18.0.0.0.0 - Production on Thu Jun 4 01:09:34 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "myUserName.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1142
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "SYS.KUPV$FT", line 1035
ORA-06512: at "SYS.KUPV$FT", line 1023

What I did is:

create TABLESPACE myUserName
DATAFILE '/opt/oracle/oradata/XE/myUserName01.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;

ALTER DATABASE
DATAFILE '/opt/oracle/oradata/XE/myUserName01.dbf'
AUTOEXTEND ON
MAXSIZE UNLIMITED;

SQL> ALTER USER myUserName quota unlimited on myUserName;

User altered.

SQL> create user myUserName identified by pass;

User created.

SQL> grant connect, resource to myUserName;

Grant succeeded.

SQL> grant read, write on directory myUserName to myUserName;

Grant succeeded.

SQL> grant create database link to myUserName;

Grant succeeded.

SQL> grant create table to myUserName;

Grant succeeded.

UPDATE-1

SQL> GRANT CREATE ANY TABLE TO myUserName;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO myUserName;

Grant succeeded.

Now the errors changed as:

Import: Release 18.0.0.0.0 - Production on Thu Jun 4 01:39:50 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "/opt/oracle/admin/XE/dpdump/myUserName/myUserName_110516.dmp" may be an original export dump file

Best Regards


texts: Creating a table with parent_id from a flat table

texts:
I have a flat table

(id, city, county, state),

Example

(1,'Beckingen', 'Merzig-Wadern', 'Saarland')

A city belongs to a county. A county belongs to a state.

First my attempt was to divide the data into 3 tables cities, counties, and states  and to build up the associations via linking tables (cities_counties). Now I want to create a 'locations' table out of it, where county is parent of city and state is parent of county.

locations:
(id, name, parent_id, type)

 (type is 'city','county', or 'state')

What would be best practice to create the adjacent list from the flat list?

I'm working in a LAMP environment, just in case a php script would fit.


texts:
I have a mysql table(trips) with records about cars. I wish to query the data and return one item per carId. But am unable. Below is the query example getting 50 records, which should also have 50 carId

SELECT *
FROM `trips`
WHERE (   carId = 147
       OR carId = 62
       OR carId = 59
       OR carId = 42)
LIMIT 50

Below is an example that returns 8 records. How to return limit 8 records but one record per car. Example here


texts: Applying user-defined fields to arbitrary entities

texts:
Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and it wasn't really designed to scale well. Our system has around 60 different types of entities, which makes things even more complicated. Essentially the implementation looks like this:

USER_DEFINED_FIELDS:

UDF_ID         int
ENTITY_TYPE    int
UDF_NAME       nvarchar(64)
UDF_DATA_TYPE  int
UDF_FORMAT     nvarchar(16)
UDF_MASK       nvarchar(24)
UDF_FLAGS      int

UDF_VALUES_NUMBER:

UDF_ID         int
ENTITY_ID      int
VALUE          int
MODIFIED       datetime

UDF_VALUES_TEXT:

UDF_ID         int
ENTITY_ID      int
VALUE          nvarchar(255)
MODIFIED       datetime

etc...

This gets nice and fun when we generate our own ways to index compound primary keys, but that's another DailyWTF-worthy story.

Obviously this is pretty hideous, and leads to some spectacularly horrific queries being generated, but it's worked alright for now because we limit each entity to a maximum of 5 user-defined fields. As a quick disclaimer, I wasn't with the company when this design decision was made! ;)

Anyway, we're about to start a shiny new project and will inevitably need a better way of doing this, with no restrictions on the number of UDFs we can apply to entities, increased performance, and less horror in the generated query department. If we could run a NoSQL solution like Mongo or Redis I'd be happy and wouldn't need to bother you all, but sadly that's not an option. Instead, I need a way to do this from within SQL Server 2008 R2.

So far, I've come up with the following options:

Individual UDF table for each entity type, with identical structures.

Benefits: Queries are faster and simpler, solves the compound index problem, can search by UDF.
Downsides: Table versioning is more cumbersome, lots of extra tables, difficult to index across similar entity types (e.g. purchase orders and sales orders)

Binary blob UDF column in every entity's table.

Benefits: Really fast: no fetch overhead, no extra JOINs.
Downsides: Can't search by UDF, extra proprietary code required.

XML column in every entity's table.

Benefits: Fast like binary blobs, seems to be indexable.
Downsides: No idea how they work - looks complicated!

So, do any of these ideas have merit? Is there a better alternative?


texts: Recommended MySQL statistics for production environment

texts:
Other than usual system stats (i/o, ram usage, cpu, load, etc) I am currently collecting questions, qps, running queries and buffer pool hit.

I find qps pretty useless since our production servers uptime is very high, and its average value.

I was wondering about best practices for statistics collection for mysql production server. What other stats should I collect/monitor to understand the load on my server and act quickly without putting more stress on it?

Edit :

I am not looking for a 3rd party solution, I am already using zabbix (and ability to create hand written scripts) to collect stats / monitor our mysql cluster. There is a list of possible stats for collection in this link.  And of course there are stats which are not listed here and can be collected via shell scripts. The real question is what stats must be collected to monitor our cluster efficiently without creating an unnecessary junk full of statistics.

Example : should we get Qcache_hit / Qcache_hit + queries ratio to see if our tables are hot enough ?


texts:
I have an SSIS package that loads up an Excel file on daily bases; from time to time the file changes. It can have additional columns in the Excel worksheets.

How can I know if the Excel file has changed?

Is there a way to add that column automatically?


texts: How to permanently enable query cache in MySQL?

texts:
I want to know if there's a way to permanently set the size of my query cache and to make sure it's always enabled.
Right now, I'm just using the set global query_cache_size command to set the size, but when the database is restarted it goes away.


texts: how long can CU and SP be different across a SQL Server Always on Cluster nodes

texts: Does Microsoft support running SQL Server Always On clusters for a short period of time on different CU and/or SP levels. For a week or a couple of days?


texts: How to return null values from a joined table if the matching row is missing?

texts:
Here is what I need to achieve:

person_id   last_name   first_name  region_id   region name
  1         barnum        phineas      1        maricopa
  2         loman         willy        2        pima
  2         loman         willy        3        pinal
  2         loman         willy        4        santa cruz
  3         kay           mary         5        cochise
  3         kay           mary         6        gila
  3         kay           mary         7        graham
  4         lillian       vernon       NULL     NULL

Here are my tables:

Create database sales
use sales

create table if not exists
`Sales_People`
(`person_id` int primary key,
`last_name` char(16) not null,
`first_name` char(16) not null);
INSERT INTO Sales_people (`person_id`, `last_name`, `first_name`)
values
('1', 'barnum', 'phineas'),
('2', 'loman', 'willy'),
('3', 'kay', 'mary'),
('4', 'lillian', 'vernon');
create table if not exists
`Sales_Region`
(`region_id` int primary key,
`name` char(16) not null);
INSERT INTO Sales_Region (`region_id`, `name`)
Values
('1', 'maricopa'),
('2', 'pima'),
('3', 'pinal'),
('4', 'santa cruz'),
('5', 'cochise'),
('6', 'gila'),
('7', 'graham');

create table if not exists
`Sales_People_Region`
(`person_id` int not null,
`region_id` int not null,
constraint spr_pk primary key(person_id, region_id),
constraint spr_fk1 foreign key(person_id)
references Sales_People(person_id),
constraint spr_fk2 foreign key(region_id)
references Sales_Region(region_id));

INSERT INTO Sales_People_Region (`person_id`, `region_id`)
Values
('1', '1'),
('2', '2'),
('2','3'),
('2', '4'),
('3', '5'),
('3', '6'),
('3','7');

create table if not exists
`Sales` (`year` int not null,
`month` int not null,
`region_id` int not null,
`amount_sold` decimal(11,2),
constraint s_pk primary key(year, month, region_id),
constraint s_fk foreign key(region_id) references
Sales_Region(region_id));

INSERT INTO Sales (`year`, `month`, `region_id`, `amount_sold`)
Values
('2016', '01', '1', '800000'),
('2016', '02', '1', '850000'),
('2016', '03', '1', '990000'),
('2016', '01', '2', '425000'),
('2016', '02', '2', '440000'),
('2016', '03', '2', '450000'),
('2016', '01', '3', '200000'),
('2016', '02', '3', '210000'),
('2016', '03', '3', '220000'),
('2016', '01', '4', '50000'),
('2016','02', '4', '52000'),
('2016', '03', '4', '55000'),
('2016', '01', '5', '40000'),
('2016', '02', '5', '41000'),
('2016', '03', '5', '42000'),
('2016', '01', '6', '3000'),
('2016', '02', '6', '31000'),
('2016','03', '6', '32000'),
('2016', '01', '7', '20000'),
('2016', '02', '7', '21000'),
('2016', '03', '7', '22000');

Here is my query:

select sales_people.person_id, `last_name`, `first_name`, sales_region.Region_id,
trim(sales_region.`name`) AS 'Region Name'
 from `sales_region`
 inner join sales_people_region on sales_people_region.region_id = sales_region.region_id
  inner join sales_people on sales_people_region.`person_id` = sales_people.`person_id`
group by  sales_region.region_id, sales_people.person_id
having sales_people.person_id >= ''
order by sales_people.person_id, sales_region.region_id asc;

What I get:

person_id   last_name   first_name  Region_id   "Region Name"
1       barnum      phineas 1       maricopa
2       loman       willy       2       pima
2       loman       willy       3       pinal
2       loman       willy       4       "santa cruz"
3       kay     mary                5       cochise
3       kay     mary                6       gila
3       kay     mary               7        graham

I can't get it to let me see the last person_id because they have no region_ids assigned to them. In the table sales_people_region region_id is a primary key and therefore not able to be null.
If I query sales_people and group by person_id she does come up.


texts: sql agent job notification email without configuring database mail

texts:
Is there any way to send sql agent job notification email without configuring Database Mail?

Currently I am using Database Mail but due to security concern, i need to disable Database Mail feature.

Thanks.


texts: When placing an order in a shop, should a shapshop be taken of the products in an order also?

texts:
...to prevent messing up with data updates, such as prices, titles, of the products that are placed in an order.

Namely, a customer buys 3 items: for $5, $10 and $33 and pays for them. All is well. When I as an owner of a shop step in and, before I've delivered those products to a customer, decide to descrease the price of the product, say, #2. Its new price will be $8 instead of $10. And I'll also rename the product #3 a little bit. A customer then would go to a status tracking page and they'll see updated data. Yes, they've paid and the order is being delivered, but the data has already changed in the database.   They'll be consufed.

And so will be I in a year after I'll have changed the prices again, renamed something, etc... and decide to view history of the orders for a year.

Question 1: is there a practise of making a snaphot of the current prices, names, total order price and other characteristics of the products that constitute an order, as well as other details (chosen shipping rate and shipping method, taxes, discounts, etc) at the moment when it's being placed? Rather than calculating those dynamically when an "order page" is opened?

If yes, does it have a name? And are there recommendations of how to do it properly?

Question 2: where and how should I store a snaphot?

option #1:

I'd have to create multiple tables then:

frozen_products
frozen_discounts
frozen_shipping_method_and_rates

etc...

that will have the same structure as their dynamics corresponding ones.

Laborious. Is there a better way?

option #2:

along with an order, in the "orders" table. But how again, given the fact that an order is a single row? For instance, the products in an order is a list. How would I store a list with its characteristics (price, weight, colour, material, what have you) in a row in such a way that'll be more or less easy to retrieve in the future? Not as a string.


texts: An exception occurred while opening a connection to the server

texts:
I've encountered this from time to time and I cant seem to find the fix.
Usually when this error occur, I am seeing increasing number of connections being used while its connection is not being disconnected.
Here is a sample logs on mongo.

But I observed that this log below is its normal behaviour:

Another thing that I noticed before the error occur is that there are numerous "Unable to gather storage statistics for a slow operation due to lock aquire timeout"
At the first time I encountered this issue. We set MaxConnectionPoolSize = 200; then it occurred again. Any idea how to fix this?


texts: Make another column in a table equal to the primary key column

texts:
In my notes table (MySQL database) I have id as the primary key. I want the column originalid to be the value of the primary key. Here is the query I run after creating a record which I thought would work.

UPDATE notes SET originalid = (SELECT MAX(id) FROM notes) where id = (SELECT MAX(id) FROM notes);

But I get the error

  You can't specify target table 'notes' for update in FROM clause

Any advice or solutions on how I can work around this?


texts: Log shipping monitor primary not seeing recent backups, warnings triggered in error

texts:
I am receiving the Log shipping Primary Server Alert warning in error

In msdb.dbo.log_shipping_monitor_primary the last backup time is 1215 however on that machine there are .trn files that have been created since

In msdb.dbo.log_shipping_monitor_secondary the destination is updating as I'd expect it to

So the .trn files are being created correctly, transferred correctly, and saved correctly, however SSMS appears to be unaware of this hence the warnings that are being triggered in error

Any ideas on why this might be happening? I set up TLS around 3 months ago and apart from a few little problems it's worked fine, this has only occurred today

edit: Rather than the full output as there are a lot of databases being backed up, I've added below the relevant mismatch, please excuse my formatting I'm not sure exactly how to neaten this up

msdb.dbo.log_shipping_monitor_primary

primary_server  primary_database    backup_threshold threshold_alert    threshold_alert_enabled last_backup_file                        last_backup_date        last_backup_date_utc    history_retention_period
DREAM           ACCDRE              60               14420              1                       e:\tlshipping\ACCDRE_20170209121501.trn 2017-02-09 12:15:02.010 2017-02-09 12:15:02.003 5760

msdb.dbo.log_shipping_monitor_secondary

secondary_server secondary_database primary_server  primary_database    restore_threshold   threshold_alert threshold_alert_enabled last_copied_file                                last_copied_date        last_copied_date_utc    last_restored_file                              last_restored_date      last_restored_date_utc  last_restored_latency   history_retention_period
WHSQLDR          ACCDRE             DREAM           ACCDRE              45                  14420           1                       E:\DreamLogShipping\ACCDRE_20170209163001.trn   2017-02-09 16:32:02.397 2017-02-09 16:32:02.397 E:\DreamLogShipping\ACCDRE_20170209163001.trn   2017-02-09 16:34:11.477 2017-02-09 16:34:11.477 4                       5760


texts:
I'm having trouble working out this apparently simple query, and would appreciate any suggestions for a solution using Sqlite.

I have two tables  t1 and t2 :

Eg.,

Table1 :
--------

Key  Data
A    data11
A    data12
A    data13
B    data21
B    data22
B    data23

Table2 :
--------
Key  Data
A    datax
B    datay

And I want to pad out Table2 to be like this  :

Table2 :
--------
Key  Data
A    datax
A    null
A    null
B    datay
B    null
B    null

Ie., So that both tables have the same number of entries for each Key value.

CREATE TABLE T1(key,data);
CREATE TABLE T2(key,data);
INSERT INTO T1 VALUES ("A","data11"),("A","data12"),("A","data13"), ("B","data21"),("B","data22"),("B","data23");
INSERT INTO T2 VALUES ("A","datax"),("B","datay");

I have tried the following :

CREATE TABLE T1(id integer primary key autoincrement,key,data);
CREATE TABLE T2(id integer primary key autoincrement,key,data);
INSERT INTO T2 SELECT NULL,T1.KEY,NULL FROM T1 LEFT JOIN T2 USING (key,id);

This adds too many rows (6 instead of 4). There should be the same number of rows in both tables.

|1|A|datax
|2|B|datay
|3|A|Null
|4|A|Null
|5|A|Null
|6|B|Null
|7|B|Null
|8|B|Null

The additional records are just padding so that both tables have the same number of rows. This simplifies the task creating the report where there are a variable number of items per key per category. I didn't know how else to do it, and it took quite a while to come up with this solution.

The ID columns are autoincremented as the tables are populated. I have Sqlite 3.13.0. In reality , the 'key' field are project-codes and the 'data' field are object names from 2 repositories. The report is supposed to show what objects are active for a given project and on which repository.


texts: How to analyze impact of a query on another queries?

texts:
Suppose I have two tables let's call them values and candlesticks. I can compute candlesticks over values using three different methods:

In trigger before inserting new value with insert on duplicate key update
Periodically issue insert into candlesticks ... select from values.
Using some service written in some language that would periodically poll values, computing updating or adding new candlesticks.

I'd like to analyze how this methods would impact queries to both values and candlesticks. The best thing I came up so far is to write a script that would put new values and another one which would issue two typical queries (one for candlesticks and one for values) and use performance_schema as it described in manual to gather data about queries and plot it.

Which seems like a problem that should be solved already by some tool. Is there a such tool (free) that can monitor some query performance over time? Maybe there are some other approaches to the problem?


texts: Backup Files growing quickly on SQL Server 2016 Always On Cluster

texts:
I have a two-node SQL Server 2016 Always On Cluster.  I also have a job that updates about 200 records in one table every 3 minutes.  Even when there are no updates, it does change a TimestampDt field with the current time.

Previously my backup files were growing at a manageable 10-20 MB per day.  However, now that I started making the TimestampDt field updates every 3 minutes (this was a recent change) my backup files are growing at a size of 200 MB per day.

I think that the backup is including the entire transaction log.

Here is the backup SQL Query:

SET @BackupUrl = CONCAT('https://backup.blob.core.windows.net/databasebackup/dbname-',GETDATE(),'.bak');
BACKUP DATABASE dbname
   TO URL = @BackupUrl
   ;

What is the recommended approach here?  Should I purge the transaction logs somehow on backup?  If so, how can I do that safely in an Always On Availability Group clustered environment?

Any help would be greatly appreciated.


texts: How to handle TimeZone properly in SQL Server?

texts:
My local development server is in the Middle East, but my production server is in the UK.

I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I need to show the time according to Saudi Arabia format.

Should I create a new database table called TimeZone and save the Time in UTC?


texts: When using class inheritance, can all PKs of the derived tables exist as PKs of the base table?

texts:
Brief questions

When class inheritance is used, the child table inherits the key from the parent, right? Thus, you INSERT INTO the parent and then the child?

This indicates that the PK of the child table (derived class) becomes FK in the parent table (base class). See "but now the primary key of these tables also becomes a foreign key to the People table." That's not true is it? The examples indicate that the child tables reference the parent table. Did I misunderstand something?

Can the PK of both the child and parent tables be the same?

Details
I'm new to handling supertype-subtype instances in RDBs, but I've read a number of posts about three main options. The one I'm interested to implement is class inheritance where I have a base table with all common attributes and derived tables with attributes that are unique to each subtype.
I generically understand that class inheritance and table inheritance aren't the same. Based on the postgres documentation, table inheritance will allow for duplicate data in the child tables that I can't allow. Thus, I don't think table inheritance is a good way for me to go.
I'm interested in class inheritance because I need multiple different subtypes to be referenced as FK in a table. For example, I have a base class table vehicle and derived classes boat and car. I have another table for vehicle_maintenance. As a side note, I have many tables that are equivalent to vehicle_maintenance in that they can be applied to both boat and car. I also have many more subtypes than just boat and car with many attributes that are unique to each subtype.
Here's an example adapted from this post.
CREATE TABLE vehicle (
   vehicle_id          int PRIMARY KEY,
   paint_type    text,

   -- // other common attributes
);

CREATE TABLE boat (
    boat_id         int PRIMARY KEY REFERENCES vehicle (vehicle_id),
    propeller_type    text,
    water_exposure     text,

   -- // other attributes specific to boat ...

);

CREATE TABLE car (
    car_id         int PRIMARY KEY REFERENCES vehicle (vehicle_id),
    steering_type    text,
    wheel_type     text,

   -- // other attributes specific to car ...

);

CREATE TABLE vehicle_maintenance_id (
   vehicle_maintenance_id          int,
   date_complete        datetime,
   FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id)

);

In this example, I would create a unique ID for every vehicle in the base table and carry that over to the derived tables. This would mean that the same paint_type would be repeated without any other attributes being UNIQUE, except the PK. This doesn't seem very normalized, but it's the only way I understand to get all of the PKs from the derived tables into one table without just doing an all-in-one table instead of class inheritance.
Am I just confusing myself, and the example I provided above is acceptable?
Edit
Perhaps I need to make this a separate question: I can't create composite keys based on attributes in the child tables and use them as PKs in the base table, right? This is because each child table will have unique attributes and the base table would require only one set of attributes to form the composite FK, correct?


texts: How to pass the output of select query as column list to another select query in postgresql?

texts:
I'm trying to pass the output of a select query as column list to another select query.

select ARRAY_TO_STRING(ARRAY(SELECT distinct COLUMN_NAME::VARCHAR(50)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='products' AND
COLUMN_NAME NOT IN ('code') ), ', ');

The above query will give the output as like give below,

array_to_string
---------------------------
name, price, manufacturer

Now I need to pass this output as column list to another select query. So I tried like this,

select ( select ARRAY_TO_STRING(ARRAY(SELECT distinct
COLUMN_NAME::VARCHAR(50) FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME='products' AND  COLUMN_NAME NOT IN ('code') ), ', ') )
from products;

But it is not working properly. It gives the output like this:

          ?column?
 ---------------------------
 name, price, manufacturer
 name, price, manufacturer
 name, price, manufacturer

Instead of,

      name        | price | manufacturer
 -----------------+-------+--------------
 Hard drive       |   240 |            5
 Memory           |   120 |            6
 ZIP drive        |   150 |            4

So, how can I solve this?


texts:
I have query that is excluding data based on the value of a float column

select *
from My_Table
where my_Float_column != 0 and my_Float_column is not null

I don't want to index a float type if I can help it. Would the execution plan be clever enough to use a filtered index like the below (Where I am only indexing the 0 and null values) to increase performance?

CREATE NONCLUSTERED INDEX IX_My_Table_Float_Filtered
    ON My_Table (my_Float_column)
    WHERE my_Float_column = 0 or my_Float_column is null


texts: How do I backup SQL Server Agent configuration?

texts:
I've tried googling for this.  The problem is that the all the results are about using SQL Server Agent to schedule automatic backups.

How do I backup the SQL Server Agent configuration itself?  If my database machine fails, I will need to recreate all the SQL Server Agent jobs that are currently defined.  Is all this information stored in the Master database and is a restore of that database all that is required to get the SQl Server Agent jobs back?


texts: PostgreSQL SSD vs HDD - why is there no difference in insert performance?

texts:
I have a PostgreSQL12 on Ubuntu 20.04 with following tablespaces:

The default uses a HDD and there is also another tablespace on an SSD.
Then I run a test to see the differences in insert/update speed for tables placed in different tablespaces:
create table test_hdd
(v int);

create table test_ssd
(v int)
tablespace ssd1;

insert into test_hdd
select * from generate_series(1, 10000000); --~10sec

insert into test_ssd
select * from generate_series(1, 10000000); --~10sec

update test_hdd set v = 1; --~25sec
update test_ssd set v = 1; --~25sec

The expected outcome is that the operations on table test_ssd should be faster, but that is not the case. All the operations on both tables take almost exactly the same amount of time to complete.
My question is: why? How do I get a write speed boost from using an SSD?
Bonus:
I compared the disk write speed using dd to make sure that the disk for /dev is faster than the HDD disk, and it indeed is. The HDD write speed was around 224 MB/s, and for SSD it was around 1.3 GB/s.


texts: SQL Server 2012 Error 9512 when modifying user login

texts:
We are attempting to grant access to a database to one of our employees. We open up his user properties in Security -> Logins, navigate to the User Mappings page, select the database, select db_owner in the options below, set the default schema to dbo, then when we click OK to save the changes, we get the following error:

  Create failed for User 'domain\username'. (Microsoft.SqlServer.Smo)
  Xml data type is not supported as a parameter to remote calls. (Microsoft SQL Server, Error: 9512)

Any thoughts on why we may be getting this error and what we can do to resolve it?


texts: How to Check and Report time taking queries

texts:
I am using MySQL 5.6.24

How do I capture the queries which are using more system resources?

How do I capture slow running queries and queries not using indexes?

What is the best way to optimize the slow running query?

Thanks in advance


texts: Restore of SQL service master key not required to decrypt data after migration?

texts:
This is a bit of the opposite of how this question is usually asked-- I used the following to create a database master key, a certificate, and a symmetric key:

Here's how I created the certs/keys (obviously with real passwords):

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '123456'

CREATE CERTIFICATE EncryptionCert
WITH SUBJECT = 'EncryptionCert'

CREATE SYMMETRIC KEY SymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE EncryptionCert;

I then encrypted some data using that key, backed up the database, and restored it to a different box.

--
-- Add a new varbinary column to the customers table to hold the encrypted
-- version of the password
--
ALTER TABLE customers2
ADD password2 VARBINARY(256)

OPEN SYMMETRIC KEY SymmetricKey DECRYPTION
BY CERTIFICATE EncryptionCert

--
-- Save the encrypted version of the password to the table
--
UPDATE customers2
SET password2 = ENCRYPTBYKEY(KEY_GUID('SymmetricKey'),password)

My expectation was that I wouldn't be able decrypt the data until I first migrated the service master key from the old box, but I actually was able to do that immediately after restoring without providing a single cert or password.

I deduced that this was happening because I was using an Amazon EC2 image to create the box, so I'm assuming that EVERY box created from that image had the same service master key.

To try to force the service key to change, I ran:
    alter service master key regenerate

on both boxes.

I started over with a new database, new keys/certs etc, and this time, when I moved the backup to the new box, I wasn't able to automatically read the data, but I had to first provide the password for the database master key that I created. Once I did that, I was able to get to the data.

From everything I read, I would have thought that I wouldn't be able to decrypt the master key without moving the service master key first.

I'm concerned that the service master key not being required is still due to an oddity of my test environment, and that I might get screwed in a year when I try to do this for real.

Can anyone shed light on what's happening here? Did something change in SQL that made it unnecessary to move the service master key, or did I create the database master key in a way that made moving it unnecessary? Or am I getting back potentially false results?


texts:
This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the concept. I'm using a MS Access .mdb database, and running the query in MS Access 2010.

I need to SUM 'BalanceDue' and GROUP BY 'CustomerID', then I need to replace the 'CustomerID' with the 'Company' name from the Customers table.

Orders table:

OrderNumber CustomerID  BalanceDue
1000        1           250.00
1001        2           100.00
1002        2           50.00
1003        3           100.00
1004        1           200.00

Customers table:

CustomerID  Company
1           ABC Inc
2           XYZ Inc
3           Widgets LLC

And the expected result:

Company     Total
ABC Inc     450.00
XYZ Inc     150.00
Widgets LLC 100.00

Here's the query I came up with, but the results don't appear to be accurate:

SELECT Customers.Company, Sum(Orders.BalanceDue) AS Total
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.Company;

To check the accuracy, I just ran the following "simple" query (one I fully understand/trust):

SELECT Orders.CustomerID, Sum(Orders.BalanceDue) AS Total
FROM Orders
GROUP BY Orders.CustomerID;

When I compare the results from the two queries above, the results don't match. The first query has less rows, so it must be "skipping" some customers?

What am I doing wrong?


texts:
I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin

I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value

skip-innodb

Afterwards, I found some errors in some php scripts in the server

Unknown table engine 'InnoDB'

I checked some databases in phpmyadmin i found in each database some tables IN USE

How can I make a successful conversion of all tables from InnoDB to MyISAM ?


texts: Which system databases should I checkdb and defrag indices?

texts:
I have SQL Server Agent (2005) jobs that periodically perform CHECKDB and a defrag (ALTER INDEX REORGANIZE/REBUILD) of any index that is highly fragmented.  These are typical maintenance best practices.  I'm wondering which of the system databases I should apply these jobs to?  Right now I run CHECKDB only on master, and I vaguely remember setting it up because I saw that in a book somewhere.

So, for which of the following should I perform CHECKDB and/or index defrag?

master
model
msdb
tempdb


texts: SQL Server Database Mail Log is flooded with errors (20-30 per second) when mail server goes down

texts:
We're using SQL Server 2005. Every so often our mail server goes down, and when this happens, as soon as our SQL code tries to send dbmail, the Database Mail Log has an item which looks like this:

Date        12/9/2013 07:38:05
Log     Database Mail (Database Mail Log)

Log ID      327487
Process ID      5680
Mail Item ID        442779
Last Modified       12/9/2013 07:38:05
Last Modified By        sa

Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2013-12-09T07:38:05). Exception Message: Could not connect to mail server. (No such host is known).
)

The problem is that our admin accounts are notified by email with the database mail error, which creates an obvious problem: since the mail server is down, the "Database Mail error" email notification creates another mail log entry because that email couldn't be delivered, which triggers another email notification.  An infinite loop is created, which results in 20-30 log entries generated every second, and when the email server finally does come online, all of these are delivered to our admins email inboxes.

My question is: Where do we configure who gets notified and how when a database mail error occurs?  How do we disable email notification by Database Mail when a dbmail error occurs?

I've looked through Database Mail and SQL Server Agent and can't find anything.  There are two operators defined in SQL Server Agent, but the history tab of the operators doesn't indicate that they have been emailed.  There are no Alerts or Jobs that are set up that email the operators either.  Does Database Mail simply email all operators if an error occurs?

Thanks for you help!


texts: How do I check if a certain SQL statement is being run?

texts: Using SQL Server Management Studio, how can check or see who is running a certain SQL statement?


texts: When should I use shared lock over exclusive lock?

texts:
I know the difference between shared lock and exclusive lock.

But I am not able to figure out a use case when I should go for shared lock instead of exclusive lock. Because using exclusive lock and using normal select query select * from wmployee where id=4; serves the purpose of reading data when exclusive lock is applied.

So in which case should I be using shared lock?
Please could someone explain with a MySQL example statement?


texts: Mysql config for my.cnf make sofware not response

texts:
First time  i am sorry about my English , but please help me on my.cnf . I read many post about config Mysql for best optimize but not thing to imporve. My server is Data center that contains two windows and linux . window has 5 core 5 GBram and linux has 18 CPU , 20GB ram . My window has two software that was developed by c# and i am using dll mysql.data . two software ,they will craw data from another website and insert into data to linux , sometime call "select , update , delete" . they work every time about 10s to craw and 10s to craw and send data to linux  . On my linux with 358 user online same time , they click on link . another link and stay very long , they call always call to Database every time . My server has 60% select and 40% write . I dont know how to config my.cnf . But some time software in window insert data to table of database on linux very long about 30s and some time it show not respoding . I dont know why and i dont know what parameter make table innodb lock or make innodb can not be inserted by c# . Now i would like to know how can i change and what parameter(variable) to change to get best performance for select and insert into with larger data . Thank you very much . This is my my.cnf ( i am using mysql 5.6.31 - MySQL Community Server (GPL))

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
skip_name_resolve
skip_external_locking
default_storage_engine=InnoDB
max_connections=20000
join_buffer_size=2M
open_files_limit=65535
query_cache_type=ON
query_cache_size=1G
thread_concurrency=36
thread_cache_size=25
table_open_cache=10048
wait_timeout=300
read_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=1000
long_query_time=7
key_buffer_size=128M
max_allowed_packet=268435456
interactive_timeout=400
myisam_sort_buffer_size = 64M
tmp_table_size=64M
max_heap_table_size=64M
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=512M
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10
innodb_log_buffer_size=64M
innodb_thread_concurrency=0


texts: How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

texts:
Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option --single-transaction. For production, I am looking for way where I can upgrade mysql prod DB to 5.6 with minimal downtime.

We are using a different server, but the problem is how I can manage ongoing transaction on production DB, because while taking mysqldump and restoring to new server their will be many transactions performed on my existing production server. How can I sync this prod-DB updated transactions with new 5.6 mysql-server ?

I am thinking if we can run mysqldump with --single-transaction --flush-logs and after  restored this dump, restore remaining  transaction by  applying  new binary  logs that  created after mysqldump.

I am also planning to enable GTID replication with our new 5.6 mysql production.


texts:
I am new to here and please let me know if I didn't make the question clearly.

If I have a m rows in a table and I want to select n rows uniformly. In other words, the interval between first row and next row is m / n.

For example, if i have a 10 rows and I want to select 5 rows out of them, then the selected rows would be 1, 3, 5, 7, 9 because the interval is 10 / 5 = 2.

Does anyone can help?


texts: How to limit the number of rows in select statement for a condition in sql server 2012

texts:
Customer|Product_Code|List_Price|Unit_Price|Unit_Unit_Price|Unit_List_Price|
--------+------------+----------+----------+---------------+---------------+
3BEACHES|CA050608    |9.46      |9.46      |EACH           |EACH
3BEACHES|CA050608    |9.46      |9.46      |INNER          |EACH
3BEACHES|CA050608    |9.46      |9.46      |SHIPPER        |EACH

In my select statement I am joining 4 Views - Customer,Product,Price Book and List Price View and I want only 1 row for a combination of Customer and Product_Code, I am getting 3 rows as attached above because there are different values in Unit_Unit_Price field for some Products, it can be any one of  "EACH" or "INNER" or "SHIPPER" but just 1.

I can't put a Where clause on any 1 Value(Each,Inner or Shipper) because for some Customer-Product_Code line it's just showing 1 row(Shipper or Each or Inner), so if I am using where clause on any 1 value of Unit_Unit_Price field then it's deducting the records which is not correct.

What I want: there are no repeating rows for Customer-Product_Code combination, I just need unique rows.

Code I am using:

SELECT TOP (100) PERCENT skh.ARCUS.IDCUST,
                         skh.ICITEM.ITEMNO AS product_code,
                         skh.ICITEM.CNTLACCT,
                         skh.v_list_price_each.price AS list_price,
                         skh.v_price_book.price,
                         skh.v_list_price_each.unit AS Expr2,
                         skh.v_price_book.price_unit
FROM skh.ARCUS
INNER JOIN skh.v_price_book ON skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN skh.ICITEM ON skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND skh.v_price_book.company = skh.ICITEM.AUDTORG
INNER JOIN skh.v_list_price_each ON skh.v_price_book.item_short = skh.v_list_price_each.item_short
WHERE (skh.ICITEM.INACTIVE = 0)
  AND (skh.ARCUS.SWACTV = 1)
ORDER BY skh.ARCUS.IDCUST


texts: Mysql's internal mysql database in replication

texts:
I searched around but the word game seems to delude any valid result and could not find anything relevant.

Mysql (the RDBMS) has an internal database named mysql, this database has information about the users and as far as I know, information and settings about the server running the RDBMS.

Background Info

In our set up we have a few MySQL 5.5 servers and one test MySQL 5.6 server.

Replication is set up so that the 5.6 is a slave of one of the 5.5

Incident

This week the replication to the 5.6 stopped working, when doing a show slave status this error was reported:

Error: Error Column count doesnt match value count at row 1 on query.
Default database: mysql.
Query: INSERT INTO tmp_user VALUES (localhost,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,)

When I checked, the mysql database did not have a tmp_user table, however the error is that the column count doesnt match, what I expected was for the error to be that the table did not exist (Error 1146).

Another conclusion that I gathered from this situation is that we are replicating the database mysql.

Questions

Is there any concern/benefit in replicating the mysql database?
Do you see this scenario as an injection attack? or a security concern?
(Perhaps I am missing a scenario where creating/populating/deleting a tmp_user table is
normal)

Thanks for your time


texts: MySQL - Is it good to have millions of tables in a single database?

texts:
I have designed new MySQL database for my project, In my project I need to create tables dynamically with dynamic columns.
So I want to know is their any impact on database performance if I have unlimited (Millions or Billions) tables in a single database.
And also want to know what are the max tables MySQL allow to create in a single database (As I can check InnoDB permits 4 Million tables, but I don't know "what is InnoDB?")

ParentTable (Static)
+--------------------------------------------------+
| ID | UserId | ParentName | Desciption | Status |
+--------------------------------------------------+

ChildTable1 (Dynamic)
+---------------------+
| ID | UserId | Value |
+---------------------+

ChildTable2 (Dynamic)
+--------------------------------------+
| ID | UserId | Name| Address| Pincode |
+--------------------------------------+

Above is just a example how I have designed my database with dynamic tables, I'm storing Parents details in a static table and to store child data I'm creating tables dynamically with one or more dynamic columns.
Is there any other best way to achieve the same?

Update 1 :

I'm working on a project in which user can create their own web services from UI by simply selecting number of columns and defining name to each column.
For now I'm considering, I'll create one table for each web service with reference of UserId (As shown in above example).
So in this approach if I have 1 Million users and if every user will create 10 web services then it will create 10 Million tables in database.

How I can resolve this problem?


texts:
I can't install SSAS (SQL Server analysis services) in multidimensional mode : I get an error at the end of the installation : startup ask failed for service MSSQLServerOLAPService. I was able at the first time to install SSAS in tabular mode, but I saw that a lot of tutorials use multidimensional cubes, so I wanted to change; I removed the existing SSAS service (via the SQL Server ISO installation disc), and install it but now in multidimensional mode, but I got this error.
I tried to delete the events in event viewer, with no success.
I read here and there this could be a permission problem, I mean the MSSQLServerOLAPService could have insufficient rights, but my level in windows and SQL Server users rights in very low. If I have some things to do, could you explain it simply?
I can access to SQL Server Management Studio, to the SQL Server installation media, to SQL Server Configuration Manager (so I can start/stop services). thank you.
The error is :

Component error code:          0x80131600 Error description:
Impossible to launch the service. Reason : « MSSQLServerOLAPService »
service startup ask failed.

EDIT:
In the terrminal:

# sc qsidtype MSSqlServerOlapService
[SC] QueryServiceConfig2 réussite(s)
SERVICE_NAME: MSSqlServerOlapService
SERVICE_SID_TYPE:  UNRESTRICTED


texts: How much memory is needed to keep a table cached? (SQL Server 2008)

texts:
I run long and complex queries which mostly use one big table - 8GB, 40M rows. AFAIK, all/most rows are used in each query. I'm seeing lots of IO in the activity monitor - for the first query and for every subsequent one. The server is currently using 6.5GB of memory and I want to upgrade. Question is, how much memory is needed to avoid all these disk reads? Is it in the ballpark of the size of the table or more?

This is the SET STATISTICS IO output. BigTable is the one I'm asking about, SmallTable has a 1-to-many relation with BigTable. #entrance holds the output of the query (several hundred rows of output).

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


texts: mysql - merge two large instances to one server (40GB of binaries)

texts:
I'm need to merge two mysql instances (both mysql V5.6) onto one server.

The binaries of 12 databases are approx. 40GB.

I read other posts about mysqldump but as I've previously had many issues dumping and importing multi GByte dumps, I don't dare try this method again.

Can anyone offer some starting points or from experience working with this size of data, some instructions.

Many internet posts I read are on <2GB website databases and point to mysqldump.

Many thanks.

Chris (DBA in training)


texts: Algebra of joins, is there ever a need to repeat a table in a series of joins?

texts:
First, I'm just asking about inner joins. And, I'm thinking self joins or reflexive joins through a join table are special cases which depend on how the data is structured (e.g. city - state - country, child - parent - grandparent).

But for the rest of joining tables there's no need to repeat a table in any given "chain"?

(What I mean by "chain":

               __companies -- locations AS L1 -- N Joined Tables -- locations AS L2
SELECT people |
               __schools -- locations AS L3

So, as best I can figure there is a necessary duplication of L1 and L3 since they're unconnected searches. But any conditions on a search of L2 could be placed on a search of L1.

Is this universally true? Is this generally true?


texts: Why does PostgreSQL require a database connection?

texts:
A more precise version of my question might be, does the fact that PostgreSQL requires a database connection (as opposed to some sort of generic database cluster connection) follow directly from some basic design decisions that could be understood by a novice database user?

I am one such novice database user; I don't have a good understanding of database internals. The SQL database I'm most familiar with is SQLite (although I wouldn't say I'm very familiar with how SQLite works either) and I also use PostgreSQL occasionally.

Something that I find conceptually distressing about PostgreSQL is that it requires you to connect to a specific database in order to execute commands even if the commands in question are database agnostic. For example, even if I all I want to do is to print a list of the databases in a database cluster (i.e. psql -l) I'm still required to connect to a specific database within that cluster; why?

Another case which I find somewhat more upsetting is that some operations require a connection to one database in order to perform operations on another, such as database restoration with the pg_restore utility. From the pg_restore man page:

-C, --create

    Create the database before restoring into it. If --clean is also
    specified, drop and recreate the target database before connecting to it.

    When this option is used, the database named with -d is used only to
    issue the initial DROP DATABASE and CREATE DATABASE commands. All data
    is restored into the database name that appears in the archive.

Apparently the connection database does not matter if the -C flag is used, but does matter if the -C flag is not used. This just strikes me as a needlessly confusing and roundabout way of doing things.


texts: Why ALTER DATABASE .. MODIFY NAME reports an error while succeeding at the same time?

texts:
I am running the following query in SSMS:

SET NOCOUNT ON
ALTER DATABASE [xyz] MODIFY NAME = [xyz_]

And get this:

The database name 'xyz_' has been set.
Msg 911, Level 16, State 1, Line 2
Database 'xyz' does not exist. Make sure that the name is entered correctly.

So, the query fails, but the database is still renamed!

What is going on?


texts:
I'm trying to design entities for dynamic organizations for future use-cases;

Pattern1

.
└── headquarters (the largest unit)
    └── division
        └── department
            └── personnel (the smallest unit)

Pattern2

.
└── headquarters (the largest unit)
    └── division
        └── branch
            └── personnel (the smallest unit)

Pattern3

.
└── headquarters
    └── division
        └── department
            └── foo
                └── bar
                    └── personnel

Question

What is the best design for entities in order to represent dynamic organizations?
I think some geniuses must have already designed such organization patterns with SQL, I'd like to know that the design pattern if it exists.

Assumption

There should be some units between the largest unit and the smallest unit

Numbers of that unit is variable

For example, I'd like to know a personnel's name or division

Thank you


texts: Mongos not returning existing data in shards

texts:
So we have a configuration of 2 shard (each is a replica set) and one sharded collection.

There are around 10 million records in each shard. We are using mongo 2.6.4 version. We have sharded with hash_id shard key which we generated specific for sharding.

We are fetching data by id from mongos and most of the queries work but for some of the queries (around 10k) mongos returns null although they exists in one of the shards and we can fetch it from shard directly.

Does anyone knows why mongos or config server don't have information for some ids and is there any way of fixing it? Is there any kind of operation that would refresh data in config servers to have informations about all records in database?

Thanks,
Ivan


texts:
Given a dataset (with a GIN index on values):

key | values
-------------
 1  | {4,2,1}
 1  | {2,5}
 2  | {4,1,3}

I want to aggregate the arrays:

key | values
-------------
 1  | {4,2,1,5}
 2  | {4,1,3}

My first thought didn't work:

SELECT key, array_agg(DISTINCT unnest(values)) AS values FROM data GROUP BY key

  [0A000] ERROR: aggregate function calls cannot contain set-returning function calls
  Hint: You might be able to move the set-returning function into a LATERAL FROM item.

Not being familiar with LATERAL FROM, it's not obvious to me how to achieve the desired output.


texts:
Recently one of our synchronous AlwaysOn Groups stopped replicating data and went into a Non Synchronising state.  In the dashboard on the primary everything was showing as OK, but on the secondary all the DBs had a little red x against them.

On the primary the log was flooded with an error for one of the databases being:

  The transaction log for database 'xxxx' is full due to 'AVAILABILITY_REPLICA'.

In the dashboard I looked at the redo queue and it was larger than usual, however the more interesting thing was that when I ran this query:

SELECT db_name(database_id) as DBName,
    session_id FROM sys.dm_exec_requests
    WHERE command = 'DB STARTUP'
    ORDER BY db_name(database_id)

No redo threads were there for the database in question.

We tried resuming data movement but that did not work, neither did a reboot of the passive node.  We ended up having to completely remove all databases from that group, then remove the replica itself, re-add the replica and re-add the databases back into the group.

Has anyone seen this before or have any idea about troubleshooting root cause here, and if there is a way to restart redo threads?  Would that have helped?

Also I checked and there was no blocking at the time of the redo thread, nor is this read only so no reports were running. In fact, there was no redo thread running at all on the secondary.  Have you seen that before?

I am taking log backups. Autogrowth is enabled. Running SQL Server 2012 SP2 (no CU).


texts: Result Cache disabled in Oracle 12c Enterprise - BYPASS status

texts:
I have two database instances, development and production that are fairly identical.  On production, result cache is working fine, in development it is not.  The value for the database parameter result_cache_max_size was set to 0 so I reset it to remove the value, bounced the database, and when the database was back up, the value was still set to 0 so I tried starting with a PFILE with no value for result_cache_max_size and same problem.  Then I tried setting it to 20M (value from production) and bounced, but same results.
I then invoked

SELECT dbms_result_cache.status() FROM dual;

which returns BYPASS.

The parameters result_cache_max_result and result_cache_mode are set to 5 and MANUAL on both databases.

I am using Enterprise Edition on both instances (so question Why is the result cache always disabled in Oracle 11g does not apply to me):

Development:

SYS@extdev02> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
...

Production:

extuat01> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
...

Edit:  Tried bypass proc per @BalazsPapp answer but no luck.  I think I tried it already, but without the second parm, which would have defaulted to false per the documentation.  Today's try, including a flush proc just for fun:

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
---------------------------
BYPASS

SYS@extdev02> exec dbms_result_cache.bypass(true, false);

PL/SQL procedure successfully completed.

SYS@extdev02> exec dbms_result_cache.flush

PL/SQL procedure successfully completed.

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------
BYPASS

SYS@extdev02> exec dbms_result_cache.bypass(false, false);

PL/SQL procedure successfully completed.

SYS@extdev02> select dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------
BYPASS


texts: PostgreSQL Change owner of all tables under a specific schema

texts: I am trying to change the owner of all tables under the same schema in one command line. i.e: alter table schema_name.* owner to newowner. Is there a way to accomplish that?


texts:
What are the possible things that would trigger the "disk I/O error"? I've been having this problem and I couldn't find a solution. I have a SQLite3 database, and I'm trying to insert data from a file that contains SQL inserts.

Sample data in the file:

insert into files (filesize, filedate, md5, fullpath, origin) values (5795096,1370159412, "e846355215bbb9bf5f30102a49304ef1", "SDs/16G-1/DSC00144.JPG", "SDs");
insert into files (filesize, filedate, md5, fullpath, origin) values (5435597,1370159422, "1a7bcf3a4aaee3e8fdb304ab995ff80f", "SDs/16G-1/DSC00145.JPG", "SDs");
insert into files (filesize, filedate, md5, fullpath, origin) values (5121224,1370159432, "16d28e83599c731657a6cd7ff97a4903", "SDs/16G-1/DSC00146.JPG", "SDs");

I tried inserting that in the db file with the following command:

$ sqlite3 allfiles.db < insert.sql

See below the error that I get:

Error: near line 27: disk I/O error
Error: near line 28: disk I/O error
Error: near line 34: disk I/O error
Error: near line 39: disk I/O error
Error: near line 47: disk I/O error
Error: near line 129: disk I/O error

The input lines that don't generate error are successfully included, but I don't understand why some lines have errors, and they are not inserted into the DB. There's nothing special in the lines with error, and if I run the command again I get errors in different lines, which means it's random (not related to the data itself). I tried adding pragma syncrhonous = off; and pragma temp_store = memory;, to no success. I'm running that on a lubuntu, which runs in a VirtualBox virtual machine. The host machine is a windows 7. The pwd of the files is a shared folder, i.e., it's a folder in the host machine. If I run it in a "local folder" in the guest machine, the error doesn't happen, although for some reason it's much slower... In any case, I'd like to know about the I/O error.


texts:
Is there any way to shrink SQL Anywhere databases?

With SQL Server, it was an easy task. Just do it, have a faster database again that has a size which can be transferred over a slow internet connection.

Can this be achieved with Sybase?

The question is not whether you should shrink or not - it is how it is done. So please don't quote things like "you should not" from Pinal.


texts:
Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too (col2, col1)?

For example

PRIMARY KEY (col1, col2)

OR

UNIQUE INDEX (col1, col2)

If we have col1=33 && col2=54; how we can avoid INSERT of col1=54 && col2=33?


texts: I have a script that uses aggregate functions many times, and that might cause the slowdown of my script. And I thought to simplify my script and do all the summation/addition/concatenation on the SSRS side. is it okay? What do you prefer? TIA. Happy coding! :)


texts: Setup logical Database model for measurements

texts:
I'm struggling to set up a logical model for the following spreadsheet:

(See a close-up of the table here.)

My problem is that it is not clear to me how to deal with the "gap" values where each gap (nominal, min, max) contains values (forces) from three different sources.

On the right-hand side you can see other forces coming from those three sources but they are not related to the gap - only to the profile which I believe won't let me create a Forces entity.
So I was thinking about one gap entity with (length, min,max,nominal,SourceType,MinForce,MaxForce,NominalForce) but it doesn't look right since I will have repeating data in three rows for each profile.
Or should I manage the different forces within each source?

Also have a look at the FL thickness values. This thickness is an attribute from an FL entity which can have more than one thickness value for a given profile which then changes the Build/Rebuild-Force values for LAB and CalcUnit

I've also left out some columns to not confuse you and myself even more. If I can find a solution for the gap problem I might be able to solve the rest on my own.
Maybe it's the abstract data structure that confuses me.

Hope you can help me with some tips and ideas.


texts: Store CURSOR results into Stored Procedure or View

texts:
I have this SQL code that creates a set of results from a dynamic table range. I want to know if I can create a view from this or a stored procedure?

DECLARE @CommonTablePrefix SYSNAME = 'IncidentResolvedByUserFact_'
DECLARE @SQL VARCHAR(MAX);

SET @SQL = '';
DECLARE TBL_CURS CURSOR FOR
   SELECT [Name] FROM SYs.tables Where name like @CommonTablePrefix + '%';

OPEN TBL_CURS;
DECLARE @TBL_INSTANCE SYSNAME;
DECLARE @FIRST BIT = 1;
FETCH TBL_CURS INTO @TBL_INSTANCE;
WHILE @@FETCH_STATUS = 0
BEGIN
   IF @FIRST = 1
   BEGIN
          SET @FIRST = 0;
   END
   ELSE
   BEGIN
          SET @SQL = @SQL + ' UNION ALL ';
   END

   SET @SQL = @SQL + 'SELECT * FROM ' + @TBL_INSTANCE;
   FETCH TBL_CURS INTO @TBL_INSTANCE;
END
CLOSE TBL_CURS;
DEALLOCATE TBL_CURS;
EXEC(@SQL);


texts:
I"m having a problem creating a new Availability group in a windows cluster. I have 3 nodes in the cluster. Two nodes are running as a FCI and a single stand-alone node that's a member of the windows cluster.

  Msg 19405, Level 16, State 17, Line 2 Failed to create, join or add
  replica to availability group 'SQL Cluster 2', because node 'DRNode'
  is a possible owner for both replica 'DRNode' and 'SQL Instance'. If
  one replica is failover cluster instance, remove the overlapped node
  from its possible owners and try again.

I've fixed this issue by removing the DRNode from being a possible owner of the SQL FCI, but the issue persists.

Do I need to restart the role to make it effective?

I've confirmed in PS that it's correct.

 PS C:\Windows\system32> get-clusterownernode -resource "SQL Network
 Name (FCI Name)"

ClusterObject                                               OwnerNodes
-------------                                               ----------
SQL Network Name (FCI Name)                                 {Node1, Node2}


texts:
I have databases that I am tasked to move two databases DBName1 and DBName2  to SSD and I only have limited space on the SSD to fit these databases.I do not want to shrink log or data files for the obvious reason and Brent or Paul might go crazy. I noticed the database is not growing much and it is using fraction of what it is originally allocated. The initial size for log files are the current size 41GB and 147GB respectively. When I checked the DBCC SQLPERF(logspace) , I found 41017.3 MB log size and 0.4339632 % log space use % and status 0 147474MB log size and  0.08165617 log space use %  and status 0.

Database Name   Log Size (MB)   Log Space Used (%)  Status
 DBNAme1            41017.3      0.4339632           0

 DBName2          147474        0.08165617           0

Database files(Sp_Spaceused)

database_name   database_size   unallocated space
DBName1         126294.31 MB    67443.73 MB

reserved    data    index_size  unused
18261272 KB 8347376 KB  9677480 KB  236416 KB

database_name   database_size   unallocated space
DBName2        271075.31 MB      115074.44 MB

reserved    data    index_size  unused
8731200 KB  5634520 KB  12976 KB    3083704 KB

What do you think I should do. I need to be able to utilize the SSD. Is it worth shrinking or do you think of other way of reclaiming the free space? I know shrinking is the last resort.


texts: Specific user can't view database contents

texts:
I have a particular user, let's call it TheUser.  This user is able to connect to my SQL Server instance and view a list of the databases.  However, upon expanding a database to view tables, stored procedures etc. I am greeted with this lovely, vague message:

This isn't being written to the error log, so I can't actually get a more detailed error (unless I'm looking in the wrong place?)  TheUser's default database is currently set to master.  If I set it to anything else, I'm unable to login.  It has also been granted connect permissions under the database's permissions.  Does anyone know how to resolve this?


texts:
I am getting the following in one of my transformations. I am trying to extract data from SQL server and pushing to mongodb.

2014/04/03 13:11:10 - EMPLOYEE TABLE Input.0 - linenr 700000
2014/04/03 13:11:49 - Database lookup.0 - linenr 700000
2014/04/03 13:12:28 - Select values.0 - linenr 700000
2014/04/03 13:14:26 - EMPLOYEE TABLE Input.0 - linenr 750000
2014/04/03 13:15:05 - Database lookup.0 - linenr 750000
2014/04/03 13:15:47 - Select values.0 - linenr 750000
2014/04/03 13:17:43 - EMPLOYEE TABLE Input.0 - linenr 800000
2014/04/03 13:18:21 - Database lookup.0 - linenr 800000
2014/04/03 13:18:59 - Select values.0 - linenr 800000
2014/04/03 13:20:55 - EMPLOYEE TABLE Input.0 - linenr 850000
2014/04/03 13:21:34 - Database lookup.0 - linenr 850000
2014/04/03 13:22:15 - Select values.0 - linenr 850000
2014/04/03 13:24:12 - EMPLOYEE TABLE Input.0 - linenr 900000
2014/04/03 13:24:51 - Database lookup.0 - linenr 900000
2014/04/03 13:25:30 - Select values.0 - linenr 900000
2014/04/03 13:27:31 - EMPLOYEE TABLE Input.0 - linenr 950000
2014/04/03 13:28:10 - Database lookup.0 - linenr 950000
2014/04/03 13:28:52 - Select values.0 - linenr 950000
2014/04/03 13:30:53 - EMPLOYEE TABLE Input.0 - linenr 1000000
2014/04/03 13:31:33 - Database lookup.0 - linenr 1000000
2014/04/03 13:32:11 - Select values.0 - linenr 1000000
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 - Couldn't get row from result set
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 - I/O Error: Connection reset
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 -
2014/04/03 13:33:43 - EMPLOYEE TABLE Input.0 -

and at the transformation level am getting the following error

I am getting

  2014/04/03 13:33:43 - TASK COLLECTION - Invalid state, the Statement object is closed.  2014/04/03 13:33:43 - TASK COLLECTION -
  ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by
  buildguy) : Errors detected! 2014/04/03 13:33:43 - TASK COLLECTION -
  ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by
  buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
  2014/04/03 13:33:43 - TASK COLLECTION - Error cancelling statement
  2014/04/03 13:33:43 - TASK COLLECTION - Invalid state, the Statement
  object is closed. 2014/04/03 13:33:43 - TASK COLLECTION - 2014/04/03
  13:33:43 - TASK COLLECTION - at
  org.pentaho.di.core.database.Database.cancelStatement(Database.java:664)
  2014/04/03 13:33:43 - TASK COLLECTION - at
  org.pentaho.di.core.database.Database.cancelQuery(Database.java:646)
  2014/04/03 13:33:43 - TASK COLLECTION - at
  org.pentaho.di.trans.steps.tableinput.TableInput.stopRunning(TableInput.java:303)
  2014/04/03 13:33:43 - TASK COLLECTION - at
  org.pentaho.di.trans.Trans.stopAll(Trans.java:1729) 2014/04/03
  13:33:43 - TASK COLLECTION - at
  org.pentaho.di.trans.step.BaseStep.stopAll(BaseStep.java:2605)
  2014/04/03 13:33:43 - TASK COLLECTION - at
  org.pentaho.di.trans.step.RunThread.run(RunThread.java:95) 2014/04/03
  13:33:43 - TASK COLLECTION - at java.lang.Thread.run(Thread.java:722)
  2014/04/03 13:33:43 - TASK COLLECTION - Caused by:
  java.sql.SQLException: Invalid state, the Statement object is closed.
  2014/04/03 13:33:43 - TASK COLLECTION - at
  net.sourceforge.jtds.jdbc.JtdsStatement.checkOpen(JtdsStatement.java:220)

Now How should I proceed with this issue. I am very much new to these things. Any guidance would be much appreciated


texts: Locate the smallest missing element based on a specific formula

texts:
I need to be able to locate a missing element from a table with tens-of-millions of rows, and has a primary key of a BINARY(64) column (which is the input value to calculate from). These values are mostly inserted in order, but on occasion I want to reuse a previous value that was deleted. It's infeasible to modify the deleted records with a IsDeleted column, as sometimes a row is inserted that is many millions of values ahead of the currently existing rows. This means the sample data would look something like:

KeyCol : BINARY(64)
0x..000000000001
0x..000000000002
0x..FFFFFFFFFFFF

So inserting all the missing values between 0x000000000002 and 0xFFFFFFFFFFFF is infeasible, the amount of time and space used would be undesirable. Essentially, when I run the algorithm, I expect it to return 0x000000000003, which is the first opening.

I've come up with a binary-search algorithm in C#, which would query the database for each value at position i, and test if that value was expected. For context, my terrible algorithm: https://codereview.stackexchange.com/questions/174498/binary-search-for-a-missing-or-default-value-by-a-given-formula

This algorithm would run, for example, 26-27 SQL-queries on a table with 100,000,000 items. (That doesn't seem like a lot, but it's going to be occurring very frequently.) Currently, this table has approximately 50,000,000 rows in it, and performance is becoming noticeable.

My first alternative thought is to translate this to a stored-procedure, but that has it's own hurdles. (I have to write a BINARY(64) + BINARY(64) algorithm, as well as a slew of other things.) This would be painful, but not infeasible. I've also considered implementing the translation algorithm based on ROW_NUMBER, but I have a really bad gut feeling about this. (A BIGINT is not nearly big enough for these values.)

I'm up for other suggestions, as I really need this to be as quick as possible. For what it's worth the only column selected by the C# query is the KeyCol, the others are irrelevant for this portion.

Also, for what it's worth, the current query that fetches the appropriate record is along the lines of:

SELECT [KeyCol]
  FROM [Table]
  ORDER BY [KeyCol] ASC
  OFFSET <VALUE> ROWS FETCH FIRST 1 ROWS ONLY

Where <VALUE> is the index supplied by the algorithm. I also haven't had the BIGINT issue with OFFSET yet, but I will. (Only having 50,000,000 rows right now means that it never asks for an index above that value, but at some point it'll get above the BIGINT range.)

Some additional data:

From deletions, the gap:sequential ratio is about 1:20;
The last 35,000 rows in the table have values > BIGINT's maximum;


texts:
I'm trying to move everything from a SQL Server 2008 R2 (enterprise) instance to a new instance on a different server.  To do this, I thought (after some research) that I'd be able to move the system databases - master, msdb and model.

To accomplish this, I've got backups of all my system and user database, and I'm trying to restore them to the new instance.  Note that the new instance is the same version of SQL, but the IP address, instance name and port are all different.

I've restored the master database successfully.  But when I try to restore the msdb database it just doesn't seem to work.

The command I'm using is:

restore database msdb from disk='\\webapp\SQLBackups$\msdb.bak' with replace,recovery

and the response I'm receiving is:

  Processed 2912 pages for database 'msdb', file 'MSDBData' on file 1.
  Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1. Msg
  3283, Level 16, State 1, Server SQLServerName\Instance_Name, Line 1
  The file "MSDBLog" failed to initialize correctly. Examine the error
  logs for more details. Msg 3013, Level 16, State 1, Server
  SQLServerName\Instance_Name, Line 1 RESTORE DATABASE is terminating
  abnormally.

If I try to execute:

restore database msdb with recovery

I receive:

  The database cannot be recovered because the log was not restored.

I didn't have a log backup, because the msdb database was in 'simple' recovery mode.  So I went back to the original server, switched recovery mode to 'full', created a new full backup and a log backup, then tried to restore this new backup, but still got the same result.  If I try and access anything within SSMS I just get a message saying the msdb database cannot be opened as it is in the middle of a restore.

So I seem to be stuck.  I cannot use the server as it thinks it's mid restore.  I cannot drop the msdb database as it's a system database.  I cannot complete the restore because whatever options I try, with whatever backup files, I still get the message "The file "MSDBLog" failed to initialize correctly".

Any ideas?


texts: Delete/Modify row in one table based on a condition

texts:
I have a table structure like

 create table EMPLOYE (
    CodeEmploye varchar2(100) not null,
    NAS varchar2(100),
    CONSTRAINT employe_pk primary key (CodeEmploye)
);

create table SALAIRE (
    CodeEmploye varchar2(100) not null,
    Mois number not null,
    CONSTRAINT salaire_pk primary key (CodeEmploye, Mois),
    CONSTRAINT salaire_code_employe_fk FOREIGN KEY(CodeEmploye) REFERENCES EMPLOYE(CodeEmploye)
);

I want to add a constraint where I should not be allowed to modify/delete a row in EMPLOYE table if the same employee exist in SALAIRE table.

What is the best way to do that ?


texts:
I get the following pgAdmin III error every time I restore a database, using  pgAdmin III:

Error: File '' couldn't be removed (error 2: No such file or directory)

See the pgadmin log.

My configuration:

  PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
  4.8.2-19ubuntu1) 4.8.2, 64-bit

  Linux 3.19.0-26-generic #28~14.04.1-Ubuntu SMP Wed Aug 12 14:09:17 UTC
  2015 x86_64 x86_64 x86_64 GNU/Linux


texts: I have installed MySQL on my laptop but when I open the command line client it gives the warning, and when I type the password and hit enter, it suddenly crashes. please help me what should I do.


texts: Database Hardware and Software to handle million requests per second?

texts:
I'm curious about the necessary hardware and software requirements to support a strong MMORPG. For example, what kind of requirement (both hardware and software) would I need if I were to build a system that can incorporate million requests at the same time and avoid lags? How can I keep the game from glitching by processing the requests efficiently?

I understand it depends on proper programming and database structure. Assuming there is 1 table that will have 2 rows updated for every request and there are a million requests, what kind of system and database would I need to do that flawlessly without lags? What amount of time would it take to process all those requests?

Just wondering what I should expect if I'm going to develop a MMORPG.

I would really like to get your feedback/response on this.


texts: "Server local connection provider has stopped listening" errors in SQL Server error log

texts:
We have started seeing the following errors appearing intermittently (between 1 and 5 a day on average) in the error logs on one of our SQL 2016 servers:

Error: 26050, Severity: 17, State: 1. Server local connection provider has stopped listening on [ \.\pipe\SQLLocal\SQL16 ] due to a failure. Error: 0xe8, state: 4. The server will automatically attempt to re-establish listening.

These are followed a few seconds later by:

Server local connection provider has successfully re-established listening on [ \.\pipe\SQLLocal\SQL16 ].

(SQL16 is the instance name of this server).

We do not appear to be getting any errors from the applications that use this server at the same time and as far as I am aware "\.\pipe\SQLLocal\SQL16" is used by the Shared Memory protocol, which would indicate this was something that would only affect connections coming from the local server.

This server is part of an Availability Group and is the primary replica.  The databases are also log-shipped to a third server.  I have tried to look at any errors from Agent jobs etc. but cannot see anything obvious that ties in with the times we see these errors logged.

I am just curious as to what might cause these messages, as I have never seen them on any other servers.


texts: Why are there different Database Collations for different languages?

texts: There are different database collations, I want to know the need for different database collations with respect to different languages.


texts: SQL Server merge replication across domains

texts:
I'm getting the following message when trying to set up merge replication on SQL Server 2008 R2:

  The Merge Agent failed to locate the partitioned snapshot for this
  subscription in the expected location. If the publication does not
  support Subscriber-requested snapshot generation, ensure that the
  partitioned snapshot for this subscription has been generated.

I've tried adding the following SQL to the run agent step of the snapshot agent

-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel 2

but that just seems to make the agent crash with the following message:

  The replication agent has not logged a progress message in 10 minutes.
  This might indicate an unresponsive agent or high system activity.
  Verify that records are being replicated to the destination and that
  connections to the Subscriber, Publisher, and Distributor are still
  active.

All ideas gratefully received.


texts:
I'm using a MySQL database to store data from my java application.
In java I have two classes:
The VocabularyList class has a name and holds a list of vocabularies.

A Vocabulary has a vocabulary field and a translation field. (E.g. vocabulary=bridge;translation=Brücke)
Currently my database looks like this:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`name`),
  UNIQUE KEY `id` (`id`)
);

CREATE TABLE IF NOT EXISTS `vocabularies` (
  `vocabulary` varchar(20) NOT NULL,
  `translation` varchar(20) NOT NULL,
  `id` int(11) NOT NULL
);

Question:

How can I ensure that if I delete a vocabulary list that all vocabularies with the id of the vocabulary list are also deleted from the vocabularies table?

Is my design bad and if yes, how can I improve it?


texts: MongoDB: Secondary outage is bringing the replica set down

texts:
I have a replica set with 2 members and 1 arbiter. The data center hosting the Secondary was having intermittent network issues today, which caused the Secondary to go down several times, often up/down a few times within a minute.

To my surprise, at a certain point this caused the Primary to go to ROLLBACK state bringing the whole replica down, even though the Primary and Arbiter had no connectivity issues.

The ROLLBACK state persisted for several minutes, until I restarted the DB service and removed the faulty Secondary from the replica set.

What caused this behavior and how can I prevent this from happening?

If I replace the Arbiter with another member, will this make things more reliable?


texts:
I didn't realize but it appears you can put switches on your job to run a maintenance plan. Am I correct on that?

We have 3 jobs that are running the same maintenance plan but doing different things with it.

The first two jobs succeed, and the 3rd job fails. Here are the jobs .I cannot figure out why that 3rd job fails. I was able to find a table in msdb that shows that plan ID, and all three of these jobs have the same plan id, so I know it uses the same maintenance plan.

    The code for the three of them are:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5E6B7EDB-56E5-4EAD-81E6-BDFD1514F9DD  -RebldIdx 100 '
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5E6B7EDB-56E5-4EAD-81E6-BDFD1514F9DD  -VrfyBackup -BkUpMedia DISK -BkUpDB "F:\Backup" -DelBkUps 1DAYS -BkExt "BAK"'
    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5E6B7EDB-56E5-4EAD-81E6-BDFD1514F9DD  -CkDBRepair  '

The first two pass, and the 3rd one is the one that fails…


texts: Finding indexes that aren't used often or at all

texts:
Is there a facility or a recommended practice for determining which indexes are being used and which ones can be "purged"? At the minimum, I'm looking for a log file that over time can be analyzed for which indexes were utilized. From there I can begin researching indexes that were never hit for possible candidates for a clean up.

I'm specifically not interested in running "explain" across our space of queries.


texts: Simple join unusably slow on RDS Postgres

texts:
I just setup a new AWS RDS postgres server and queries are painfully slow (> 10 hours) such that most of the time I just end up killing the query.  I have done some indexing that cut the EXPLAIN cost down by a factor of ten but it is still just as slow.  This is a migration from redshift where the same query used to take about 5minutes to complete so I would've expected this to be around the same time since redshift only has one sort key.

Running AWS RDS Postgres 9.5.2 on a db.r3.8xlarge (32vCPU, 244gb RAM), work_mem=1GB.

Query

SELECT *
FROM
    (SELECT *
    FROM table1
    WHERE create_date >= '2015-10-01' AND create_date <= '2015-12-31')
JOIN
    (SELECT  *
    FROM table2
    WHERE type = 6)
    ON table1.id = table2.id
WHERE table1.indicator1 = 'N' AND table1.product = ‘foo’ AND table1.indicator2 = ‘Y’

EXPLAIN Output

 Nested Loop  (cost=109063.49..593806.97 rows=20886 width=91)
   ->  Bitmap Heap Scan on table1  (cost=109062.91..255339.43 rows=39386 width=80)
         Recheck Cond: (((product)::text = ‘foo’::text) AND (indicator2 = ‘Y’::bpchar) AND (indicator1 = 'N'::bpchar) AND (create_date >= '2015-10-01'::date) AND (create_date <= '2015-12-31'::date))
         ->  BitmapAnd  (cost=109062.91..109062.91 rows=39386 width=0)
               ->  Bitmap Index Scan on multi_no_date_idx  (cost=0.00..22893.13 rows=662445 width=0)
                     Index Cond: (((product)::text = ‘foo’::text) AND (indicator2 = ‘Y’::bpchar) AND (indicator1 = 'N'::bpchar))
               ->  Bitmap Index Scan on create_date_idx  (cost=0.00..86149.84 rows=4108527 width=0)
                     Index Cond: ((create_date >= '2015-10-01'::date) AND (create_date <= '2015-12-31'::date))
   ->  Index Scan using table2_pkey on table2  (cost=0.57..8.57 rows=1 width=19)
         Index Cond: ((id = table1.id) AND (type = 1))
(10 rows)

Table1 Indices

Indexes:
    “table1_pkey" PRIMARY KEY, btree (id)
    “indicator1_idx" btree (indicator1)
    “create_date_idx" btree (create_date)
    “indicator2” btree (indicator2)
    “table1_id_idx" btree (id)
    “product_idx” btree (product)
    "multi_no_date_idx" btree (indicator1, indicator2, product)

Table2 Indices

Indexes:
    “table2_pkey" PRIMARY KEY, btree (id, type)
    “id_idx" btree (id)


texts: Executing SSIS catalogue package from a different machine, authentication fail

texts:
I am having an issue running a SSIS package which uses a C# script component to carry out a HTTPS file download and load the data into a SQL Server database.

On our infrastructure we have a management (MGT), SQL Server (SQL), and Integration Services (SIS) machines. The package is deployed into the Integration Services Catalog on the SIS machine.  The servers are on the same domain but on different vlans.

When the package is executed from SQL Server Management Studio (SSMS), running on the SIS machine, executing from Integration Services Catalog the package runs without issue.

However when I open SSMS on the MGT machine connect to the SQL Database on the SIS machine and run the package from the same catalog I get an error message.

  An error occurred while executing package xxx in the container DFT - Load 1260 the error details are

  0, System.Runtime.InteropServices.COMException (0xC001600C): Server authentication failed. This error occurs when login credentials are not provided, or the credentials are incorrect.

  at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSHttpClientConnection100.DownloadData()
     at ScriptMain.PreExecute()

  The package execution stopped execution. Please investigate.

The code being ran within the script task within the package is

var connMgr = ComponentMetaData.RuntimeConnectionCollection.GetRuntimeConnectionByName(Variables.pvDataConnectionName1260).ConnectionManager;
var hcc = (HttpClientConnection100)connMgr.AcquireConnection(null);
hcc.Timeout = 300;

if (Variables.pvDataConnectionAuthorisationMode1260 == "Server")
{
    hcc.UseServerCredentials = true;
    hcc.ServerUserName = Variables.pvDataConnectionUsername1260;
    hcc.ServerPassword = Variables.pvDataConnectionPassword1260;
}
_downloadBuffer = hcc.DownloadData(); //this is the line that fails

connMgr.ReleaseConnection(hcc);

The URL being requested is along the lines of https://reports.abc.com/def/data_extracts/filename_20170815.csv

The URL has requires a username and password which we are providing to the script task as per the code above.

SPN's for Kerboros have been configured.  And I can verify the connection is using Kerboros auth.  I've enabled kerboros logging but nothing is logged in System event log when the package executes.

Any ideas what else might be causing the authentication to fail?  or if there is any other logging I can do?


texts:
I want to install SQL Server on Linux. From MS site I read that Red Hat, SUSE, and Ubuntu are supported but I want to use it under Debian. Since Ubuntu is based on Debian, is there a chance to succeed with installation?

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup


texts:
This isn't really a question since I've figured out the answer, but I didn't find it documented anywhere else so I'm putting it here.

I attempted to use pg_upgrade with the -c (check-only) and -k (hard links) options (not at the same time), but it consistently ignored them both.  After much searching and trial, error, and hair loss, I finally figured out this nugget - they have to be the first argument (or at least before the ones I used) for them to be applied.

These seem to work:

/usr/pgsql-12/bin/pg_upgrade -c --old-datadir "/var/lib/pgsql/9.5/data" --new-datadir "/var/lib/pgsql/12/data" --old-bindir "/usr/pgsql-9.5/bin" --new-bindir "/usr/pgsql-12/bin"
/usr/pgsql-12/bin/pg_upgrade -k --old-datadir "/var/lib/pgsql/9.5/data" --new-datadir "/var/lib/pgsql/12/data" --old-bindir "/usr/pgsql-9.5/bin" --new-bindir "/usr/pgsql-12/bin"

These do NOT:

/usr/pgsql-12/bin/pg_upgrade --old-datadir "/var/lib/pgsql/9.5/data" --new-datadir "/var/lib/pgsql/12/data" --old-bindir "/usr/pgsql-9.5/bin" --new-bindir "/usr/pgsql-12/bin" -c
/usr/pgsql-12/bin/pg_upgrade --old-datadir "/var/lib/pgsql/9.5/data" --new-datadir "/var/lib/pgsql/12/data" --old-bindir "/usr/pgsql-9.5/bin" --new-bindir "/usr/pgsql-12/bin" -k

OS: CentOS 6.6