texts: Is it possible to disable use of fast forward-only cursor on server side?

texts:
I have an application that is using ODBC to access a SQL Server 2012 database using SQLSetStmtAttr to set the SQL_SOPT_SS_CURSOR_OPTIONS option to SQL_CO_FFO. This means that its reads from the DB are backed by fast forward-only cursor.
This is Ok in most of the cases, however sometimes the performance of the fast forward-only cursor is significantly lower that it would be with a "usual" static one (the one that we would get with default SQL_CO_OFF).
Question: Is there a way to force SQL Server not to use fast-forward cursors?
Obviously, the proper way is to change the application, and that process in on the way, however it's going to take time, and meanwhile I am looking for temporary workarounds.
So far the only idea that I have is to use this: (from Fast Forward-only Cursors)

Implicit Conversion of Fast Forward-only Cursors
Fast forward-only cursors are implicitly converted to other cursor types when:

If the SELECT statement joins one or more tables with trigger tables (INSERTED/DELETED), the cursor is converted to a static cursor

However, that seems a bit too ugly even for a temporary workaround.  Are there maybe better approaches?

Side note:  Main reason why fast forward-only cursors under-perform in this case is that they do not support parallelism.
For non fast forward sys.dm_exec_cursors gives me:
API | Snapshot | Read Only | Global (0)
while fast forward option is:
API | Fast_Forward | Read Only | Global (0)
The snapshot option is 5x times faster than the fast forward one.  If I look at the query plans for both of them, they are not so much different except for DegreeOfParallelism. The snapshot is 16 while fast forward is 0. The problem is that we are reading from the views (that we do not have much control over), and these views are sub-optimal in their design.


texts:
I'm using pgloader to perform a one-time migration from MySQL to Postgres.

For that purpose, I want to temporarily configure Postgres specifically for that workload.

reduce WAL as much as possible: wal_level = minimal
archive_mode = off
Should I autovacuum = off ?

What else can I do to improve things without severely putting my data at risk?

Ee.g. I don't want to fsync = off. Although I can re-do this migration, this additional time would still be unwanted.

Additional setting after Feedback

synchronous_commit=off (thanks @a_horse_with_no_name )


texts: DB Design storing products sold in bulk, priced by vastly different units

texts:
I am trying to design a database that stores products that are sold in varying different units. Some are priced by product, some by volume, some by length, some by surface area. Possibly some by weight. It is not a simple matter of storing the price in one specific unit, and then making the conversions (volume does not convert to surface area).

My current thought is having a product table, a price table, and a unit table. A product has one price, and a price has a per-unit integer cost and one unit. The unit table would contain an entry for every possible unit that products can be sold in, with a single column just being a string containing the name of the unit.

Is this the right approach? Is there a better way to handle such a scenario?


texts: Way to ensure just one connection to a DB2 database

texts:
Let's suppose a database with a bad security design, say everyone access to the database with a user with DBADM or SYSADM authority, and also, there could be only username for everyone (i.e. db2inst1).

I would like to perform an administrative task, for example a schema migration, and I need to be the only one connected to the database.

How can I be sure that I will be the only one that access the database?

Quiesce database is not enough because other users access with a high privilege user.
Quiesce instance does not allow to activate the database if restrict access is selected.
DB2COMM could be removed from db2set, however a local cron could be eventually fired.
MAXAPPLS to 1. However, I should be that one, if not force apps again should be issued.
Connect in exclusive mode could not work if the user is used by others.
Changing the workload configuration to allow only me? (from my IP)

However, those are too many changes just for a simple task.

I saw a similar discussion, however I do not see something that convince me: http://database.ittoolbox.com/groups/technical-functional/db2-l/luw-how-to-prevent-connections-to-a-single-database-within-an-instance-3189860

What are your propositions? What would you do in that case?


texts: How to compare current record with previous records

texts:
Is there a way to group by and compare current record to previous records when reading from a table.  My table looks something like below.

ID    PASSED    LEVEL    CREATEDATE
100   1         2        2016-01-10 07:35:02.123
100   1         2        2016-01-10 07:05:33.135
100   0         2        2016-01-09 08:35:29.123
100   1         2        2016-01-08 09:35:30.123
100   1         1        2016-01-07 11:35:31.123
101   1         1        2016-01-10 08:45:12.123
102   1         1        2016-01-10 09:45:22.222
102   1         1        2016-01-09 08:15:29.123
103   0         1        2016-01-10 07:35:02.123
103   1         2        2016-01-09 06:35:29.023

Ideally, I need to return a BIT value for each ID in the table.

The condition of the BIT value is to return a 1 for each ID when PASSED is 1 and LEVELS are different by CREATEDATE DESC order.

So in my example table the BIT value for

ID 100 would return 1 because PASSED is 1 on the first and fourth record (the level changes on the fourth record).
ID 101 would return a BIT value of 0 because there is only 1 PASSED record with a value of 1.
ID 102 would return a BIT value of 0 because the LEVELs are the same for both records even though PASSED was 1 on both records.
ID 103 would return 0 because both records did not PASS even though LEVELs are different.

Hoping a query could be structured in such a way that the response would be like this:

ID    STATUS_PASSED
100   1
101   0
102   0
103   0

Thanks for any help provided.


texts: Select the oldest records in a table in Oracle

texts:
please, how can I select the oldest records in a table in oracle? and if it is possible to show them by date.
Thank you


texts:
Query:

SELECT
  "places_place"."id"
FROM "places_place"
  LEFT OUTER JOIN "units_unit" ON ("places_place"."id" = "units_unit"."place_id")
GROUP BY "places_place"."id"
HAVING SUM("units_unit"."quantity") >= 123

Index attempt:

CREATE INDEX units_quantity_sum ON units_unit (SUM("units_unit"."quantity"));
-- ERROR:  aggregate functions are not allowed in index expressions

Essentially, I want it to index the result of SUM without storing the result in a separate column of the table. How can I create an index to do this (or is there a better way to optimize this query)?

EXPLAIN ANALYZE of query with 10,000 rows in places_place and 25,000 in units_unit:

HashAggregate  (cost=2057.31..2157.33 rows=10002 width=4) (actual time=38.121..41.174 rows=7727 loops=1)
  Group Key: places_place.id
  Filter: (sum(units_unit.quantity) >= 5)
  Rows Removed by Filter: 2275
  ->  Hash Right Join  (cost=594.04..1932.22 rows=25018 width=6) (actual time=6.383..28.578 rows=26727 loops=1)
        Hash Cond: (units_unit.place_id = places_place.id)
        ->  Seq Scan on units_unit  (cost=0.00..994.18 rows=25018 width=6) (actual time=0.003..7.279 rows=25018 loops=1)
        ->  Hash  (cost=469.02..469.02 rows=10002 width=4) (actual time=6.311..6.311 rows=10002 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 480kB
              ->  Seq Scan on places_place  (cost=0.00..469.02 rows=10002 width=4) (actual time=0.007..3.560 rows=10002 loops=1)
Planning time: 0.584 ms
Execution time: 42.643 ms


texts: MySQL partition on 2 tables one with millions of rows the other with 100's of millions

texts:
I have two mostly de-normalized tables in Amazon Aurora (MySQL). The first table had mostly dimensions that are used for selecting the proper data. Examples might be station, program name, operator, etc. Each of these rows would be considered the "meta-data" surrounding an electrical test. This first table has only a few million rows. Each row is identified with an executionId.

The second table has the results of the first tables electrical test. Each test may generate 4-40k measurements. This table has fewer columns (measurement value and executionId, measurementId), but is fast approaching 200 million rows with 8 million more rows added each week (at current production volumes).

I am after information on the best partitioning scheme for the larger table especially. Specifically:

Does the smaller table even need partitioning? The most common filter on this table would be a datetime.
What's the best way to partition a date, with the most recent data being accessed frequently, data beyond 6 months accessed almost not at all.
Since the second table is just the ID, should I partition based on the ID?
What's the best way to partition an ID like this?
Can MySQL take advantage of 2 partitions? One for filtering the first table and then a second for joining into the second?

Thanks for your help.


texts: In which situations is TokuDB higher performance?

texts:
I noticed that WordPress runs quite a bit faster when I use XtraDB (MariaDB's InnoDB replacement) instead of TokuDB. This is strange, because TokuDB is advertised as being 20x faster.

However, I noticed that YOURLS stat pages are a tiny bit faster when I use TokuDB for the gigantic (~22,000 rows) log table.

So the question is: What situations will TokuDB be a higher-performance database engine than others, such as XtraDB/InnoDB?


texts: Can backups done through external clients cause memory pressure on SQL Server?

texts:
Developer in DBA role here, we recently migrated from 2008 to 2019 and are having memory related problems on the new server. Every 3 to 6 days the OS will start paging and CPU usage would spike to 99%, the server would be totally unresponsive. The server is running on VMWare with 36Gb memory. After the first crash the min max memory on sql server was set to 10gb and 30gb (leaving 6gb for the OS). LPIM is enabled and the full allocation memory is reserved in VMWare and as far as I can see no ballooning happened VMWare side.

I've noticed in the logs the problem always follows the following pattern:

Backup on one of the biggest DB fails with the error: 1453(Insufficient quota to complete the requested service.)
All subsequent backups fail with:
BackupIoRequest::ReportIoError: write failure on backup device '4aa04866-4040-4be1-8453-d7da0116efe5_2'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
Failed allocate pages: FAIL_PAGE_ALLOCATION 1025
Bunch of Memory related messages. some examples:

Memory Manager                                   KB
---------------------------------------- ----------
 VM Reserved                                62168828
 VM Committed                                 377644
 Locked Pages Allocated                     25468848
 Large Pages Allocated                        264192
 Emergency Memory                               1024
 Emergency Memory In Use                          16
 Target Committed                           27262976
 Current Committed                          25846496
 Pages Allocated                            24510040
 Pages Reserved                               332032
 Pages Free                                   397904
 Pages In Use                                5739552
 Page Alloc Potential                       20160280
 NUMA Growth Phase                                 0
 Last OOM Factor                                   2
 Last OS Error                                     0

And then

Message
MEMORYCLERK_XE_BUFFER (node 0)                   KB
---------------------------------------- ----------
VM Reserved                                   12672
VM Committed                                  12672
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                   0

Backups are done through Commvault and are administrated by a different department in the org. I've noticed that when backups are taken, the number of dump devices are set to 10(old server it was set to 5), is that normal? Could it be the backups that are causing the memory/disk space problems or are the backups failing because of some other memory pressure caused by something else? If anyone can point me in the right direction it will be greatly appreciated, thank you.

Edit 1: Log dump here
Current memorystatus here. I also like to note, that all the migrated databases is still in 2008 compatibility mode. I've also disabled query store on all.


texts:
Recently one of our servers has ran out of memory and crashed. After reviewing the munin graphs, it appears that the only metric (other than memory usage) that peaked just before the crash was the MySQL throughput. However we were expecting to see a corresponding increase in number of MySQL queries which didn't happen:

We would like to find out what caused this peak in MySQL throughput. Here is the list of bin logs from the crash:

101M Apr 17 01:27 drupal_master-bin.001270
106M Apr 17 03:00 drupal_master-bin.001271
101M Apr 17 04:05 drupal_master-bin.001272
104M Apr 17 05:53 drupal_master-bin.001273
104M Apr 17 06:39 drupal_master-bin.001274
101M Apr 17 07:02 drupal_master-bin.001275
104M Apr 17 07:22 drupal_master-bin.001276  # 100M filled up in 1 min
106M Apr 17 07:23 drupal_master-bin.001277
101M Apr 17 07:33 drupal_master-bin.001278
101M Apr 17 07:43 drupal_master-bin.001279
104M Apr 17 07:46 drupal_master-bin.001280
102M Apr 17 08:29 drupal_master-bin.001281
102M Apr 17 08:46 drupal_master-bin.001282
105M Apr 17 08:54 drupal_master-bin.001283
 13M Apr 17 09:26 drupal_master-bin.001284  # crash of server around 09:50
# prior to crashing load went very high (we saw 45) and server was extremely slow (few min delay when typing in an SSH session)
101M Apr 17 10:54 drupal_master-bin.001285  # server up again, nothing wrong since then

I've been looking for tools to analyze those bin logs. So far I found:

binlog-analyze.pl: gives me an overview of the of queries processed, with breakdown on select, insert, update.. (FYI I replaced select into with select in the script as it seemed wrong).

$ mysqlbinlog /path/to/bin.log | binlog-analyze.pl -v

pt-query-digest: gives me statistics on query size (min, max, avg...). That utility has plenty of options but I don't know what to look for.

$ mysqlbinlog /path/to/bin.log | pt-query-digest

What we would like to find out is what queries generated the increase in MySQL output.

Can someone give directions as to how reviewing the MySQL bin logs in order to identify queries generating a sudden increase in MySQL throughput?


texts:
I have a Windows 2008R2 box with SQL Server 2012 Express. The problem is that randomly (sometimes twice a day, sometimes once a month) the SQL Server dies leaving those messages in error log:

  Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
  Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
  Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

When I try to start the service again, it says:

  Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
  Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
  Error: 33086, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

After reboot of the machine it starts working again. How can I fix this problem?


texts: What are the main reasons to split a Data Warehouse into multiple databases?

texts:
I'm working with a colleague who's proposed to split our 1 instance-database into about 7 databases (divided by data domain) for development and 7 identical databases for production. I get the test-production duality logic, but in what case or what advantages are there to splitting our 1, relatively straightforward database into 7 databases? Our data warehouse is only consumed/ used by ONE business intelligence application, period.

I am concerned about this direction so hopefully you can discuss the general reasons proposed for this split, and I can give you a run down of the current properties of the database.

1 database Data Warehouse: 352 GB total, 203 tables, 170 views

Proposed split:

A: 280 GB
B: 43 GB
C: 28 GB
D: 1 GB
E,F,G: < 1 GB combined

As you can see, this is already a head-scratcher in terms of proposed benefits as the storage won't even be remotely evenly split, with 80% remaining on 1 database. Apparently partitioning our db by schema isn't possible (from a hardware perspective) because we do not have Enterprise level SQL Server.

Reasons given for split:

Current db is poorly optimized, little documentation, sub-optimal datatypes, sub-optimal indices.

My rookie thoughts: Aren't these problems irrelevant to splitting the database? They are simply problems that need to be solved on their own either way.

There are 372 objects in the current database which makes it slow.

My thoughts: This hardly seems large in my opinion.

One database is harder to document and draw schema diagrams for than 7 databases (we will have views that will span multiple database).

My thoughts: .... This seems completely ridiculous to me, but maybe I'm wrong. We've already organized our data warehouse by 13 'source system' schemas.

One database will lead to more database deadlocks.

-- Isn't this problem also completely irrelevant to having multiple databases? It's my understanding that deadlocks occur at the table level (actually usually even just the row level, but eh). Even then, all our data inserts happen at midnight, all our selects downstream to the BI happen at 2 am. Having two processes update the same table is irrelevant to multiple databases, is it not (deadlock would happen either way)? Also, I personally have seen no evidence of table deadlocks occurring during normal operations.

Database technical ownership/ ownership.

It's only the two of us that work on the database. It's possible he wants to really segregate our 'fiefdoms'. Really, hasn't been an issue, but can't user permissions be determined at the schema level anyway?

What ARE valid reasons for splitting a Data Warehouse into multiple databases?

Would love to further my knowledge here about databases in general. Yes, I happen to be doing a lot of work on one with gaps in my knowledge, but well the job is what it is, what I've been thrust into. Stuff has been working great so far (knock on wood).


texts:
I'm trying to figure out a way that I can pass a filename into a stored procedure that can import a file. However, after setting up the SQL dynamically, the object can't be found.

DECLARE @fileName varchar(200),
    @sql varchar(max);

SET @fileName = 'C:\file.csv';

SET @sql = 'SELECT *
    INTO #import
    FROM OPENROWSET(BULK ''' + @fileName + ''',
    FORMATFILE=''C:\format.xml'',
    FIRSTROW = 2) AS a';

EXEC(@sql);

SELECT * INTO #stage
FROM #import;

The result

(27823 row(s) affected)
Msg 208, Level 16, State 0, Line 29
Invalid object name '#tmtImport'.


texts: SQL Server Profiler is available for SQL Server 2008 Dev Edition?

texts:
I just upgrade from SQL Server 2008 Express with advance services to Dev Edition. I wonder whether SQL Profiler is available for the Dev Edition?

Please let me know how to access that if it is present.


texts: Why is the Read-Only Routing not functioning in this Availability Group?

texts:
First off, my apologies if this has already been answered elsewhere. I have not been able to find any articles or threads describing my exact situation, but no one's Google-fu is perfect.

With that out of the way, here's my situation: I have a SQL Server 2017 (14.0.1000.169) Enterprise environment with three AG replicas (one primary, one secondary with synchronous commit, and one secondary with asynchronous commit), and whenever I try to connect to the Listener using ApplicationIntent=ReadOnly and specifying an AG database, it gives me the following error:

  A network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections.

It connects with no issues if I don't specify the ApplicationIntent flag, or if I don't specify a database. Here are the things I have already checked:

Listener is configured, and can be connected to normally.
Read-Only Routing URLs are configured for each replica, using the FQDN and TCP port for that instance (ran the script provided here to verify that I have those correct).
Read-Only Routing Lists are configured for each replica.
The sync secondary is in Synchronized status, and the async secondary is in Synchronizing status.
The secondary replicas are set to allow all connections (ALLOW_CONNECTIONS = ALL) and can be queried directly using their own instance names.

Each SQL Server instance that hosts a replica is using a different TCP port from the others for both its Endpoint URL and its Read-Only Routing URL (six different ports between the three instances), and the Listener is listening on a different port from all of those.  I don't think this has anything to do with the problem, but I'm mentioning it for the sake of thoroughness.

Like I said above, I haven't found anything useful for my exact situation, since all the results I've been able to find have just been people who haven't set up their routing lists or whatever. Any ideas on next steps here or anything I might be missing? Please let me know if you need any additional info about the environment.


texts: Filtering UNION ALL result is much slower than filtering each subquery

texts:
(edit: see end for a simpler example)

I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), which means that for these records a mix of parent/child fields must be used for filtering and display.

I have identified four different parent-child relationships and created views for them:

caselist_no_specials: not a child record, use record data as is; 116106 rows total.
caselist_disputes_with_ipr: child record; 138 rows total.
caselist_mark_children: child record; 18132 rows total.
caselist_design_children: child record; 671 rows total.

The results from these views do not overlap and together cover 100% of the table.

When I select a union of all of them, and separately filter each view, the query takes about 9 ms. Selecting a union of all views and filtering the result of that takes about 500 ms.

I have also tested this without views, inlining the queries they contain, which did not produce a measurable improvement.

This is the fast query (explain):

  SELECT  c.*
    FROM  caselist_no_specials c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

   UNION ALL

  SELECT  c.*
    FROM  caselist_disputes_with_ipr c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

   UNION ALL

  SELECT  c.*
    FROM  caselist_mark_children c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

   UNION ALL

  SELECT  c.*
    FROM  caselist_design_children c
    JOIN  case_clients cacl ON cacl.case_id = c.main_id
   WHERE  cacl.client_id = 12046

ORDER BY  sort_nr,
          id;

As you can see, the join and filter is duplicated for each view. Trying to avoid the duplication yielded this query, which takes a lot longer (explain):

  SELECT  x.*
    FROM  (
              SELECT * FROM caselist_no_specials
              UNION ALL
              SELECT * FROM caselist_disputes_with_ipr
              UNION ALL
              SELECT * FROM caselist_mark_children
              UNION ALL
              SELECT * FROM caselist_design_children
          ) x
    JOIN  case_clients cacl ON cacl.case_id = x.main_id
   WHERE  cacl.client_id = 12046
ORDER BY  x.sort_nr,
          x.id;

Is it possible to somehow let PostgreSQL know that the filter/join on the outer query can be applied to the inner subquery?

Or is there any other way to avoid filtering each view separately? The user-facing form for this query has over 20 filter fields, and there can be JOINs with up to 14 additional tables.

PostgreSQL is version 9.4.7 running on Linux.

EDIT: I've created a much simplified example, simply partitioning the original table using 3 views, but not involving other tables (and the query plans for both variants). In retrospect, this is what I should have used as an example in the first place.


texts: Changes made by TransactionA not seen by TransactionB eventhough TransactionA commits them. How is that possible?

texts:
Is it possible for 2 non concurrent connections to see the same data for a record even as the 1st connection commits its changes?

Here is an example of what I am seeing from the audit logs:

ConnectionA starts TransactionA
    MasterRecordX gets locked for update.
    ...
    DetailRecordY is queried
    DetailRecordY is changed
    ...
ConnectionA commits TransactionA

---

ConnectionB starts TransactionB
    MasterRecordX gets locked for update.
    ...
    DetailRecordY is queried
    DetailRecordY is changed
    ...
ConnectionB commits TransactionB

The problem is that when DetailRecordY is queried on TransactionB, it does not see the changes made by TransactionA even though MasterRecordX is locked at the begining of both transactions.

The audit logs also show that the 2 requests were non concurrent (around a second between one and the other).

Also, DetailRecordY is not queried before MasteRecordX gets locked. The database is mySQL and the isolation level is the default (REPETABLE READ).


texts: Postgresql: Select a table from different database and different host

texts:
I know this is not new, but I have not found a solution, even though I have followed several posts in this forum.

I'm trying to show table from different database using dblink extension.
Installing dblink using :

CREATE EXTENSION dblink

And run successfully. In db1 then I try querying, like:

SELECT *
    FROM dblink('dbname=db_name2 port=5432
            host=xx.xx.x.x user=username_db2 password=password_db2',
    'SELECT * FROM tableFrom_Db2');

I always get notice like this:

  ERROR:  a column definition list is required for functions returning "record"

When I try replace SELECT * with a list of columns,  I get:

  ERROR: invalid input syntax for type boolean: "SELECT column FROM tableFrom_Db2"

To make sure that the connection is going well, I tried

SELECT dblink_connect('host=xx.xx.x.x user=username_db2 password=password_db2 dbname=db_name2');

And I get notice like :

  ERROR:  could not establish connection
  DETAIL:  FATAL:  no pg_hba.conf entry for host "IP_db1", user "username_db2", database "db_name2", SSL on
  FATAL:  no pg_hba.conf entry for host "IP_db1", user "username_db2", database "db_name2", SSL off

By the way What's wrong?


texts: Is it possible to configure multiple database mail id in single account name in sql server 2012?

texts:
I would like to say that as i want to configure multiple database mail in single account name in sql server 2012.

This is SQL-Cluster-DB , so as per direction from head, If any sql job will fail , then notification should be myself (i,e; DBA) as well as System Administrator.

For single account name , a single email id is working properly.  when i am going to change 'Outgoing mail server (SMTP)' with one mail id to two mail id like this way:

Outgoing mail server (SMTP)
Email address : first mail id; second mail id
display name : MS SQL Server 2012 Backup notification
reply e-mail : first mail id; second mail id
Server name : (My company mail server)                    Port NO:(my company smtp mail port no)

After configuration of multiple mail id in email address , i am getting this error

  The mail could not be sent to the recipients because of the mail
  server failure. (Sending Mail using Account 1 (2015-12-02T10:36:37).
  Exception Message: Cannot send mails to mail server. (The specified
  string is not in the form required for an e-mail address.). )

when i am remove one email id from email address as well as reply e-mail of SMTP require configuration , then test mail is working properly.

My Question is like that:
1) Is it possible to configure multiple emailid alert notification in a single account
) Again required 'second account name' for second email alert notification.

My Working environment of SQL server 2012 is like that :

1) windows server 2012 R2
1) Microsoft SQL Server Standard (64-bit)
3)SQL Server is in Cluster Environment (that's why multiple sql alerts notification required)
4)System is in Domain environment.
5)It's Online Server

Any suggestion or help will be appreciated.


texts: Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

texts:
We have an unusual database structure (I leave the reasoning for another question), that in its current form requires a regular truncation and recreation of a lot of rows (about 1 million).

We're working to re-factor it. However, as a short term solution, are there any query hints that could help in this situation?

Is there a way of making it so if reads are occurring on the table being truncated/repopulated that it ignores this fact i.e. it'll just read the data before the truncation began?

We're trying to avoid locks (we think).

I realise this isn't a long term solution, but looking into possible solutions.


texts:
I have the following MySQL event sheduler to udpate some values in RECETAS table's ESTADO column. I want to run it everyday at 10:05:00.

CREATE EVENT IF NOT EXISTS actualizar_prueba
  ON SCHEDULE EVERY 1 DAY
    STARTS '2014-09-23 10:05:00' ON COMPLETION PRESERVE ENABLE
  DO
    UPDATE precetas.RECETAS
    SET ESTADO = 1
    WHERE ESTADO = 3;

This is what is shown when I run the SHOW events\G command. It seems that it is executed only once (Type) and with no Interval value. But if I change EVERY 1 DAY and I write EVERY 1 MINUTE the event is executed every minute, then could someone tell my what does Type and Interval value mean? Is the event created well?

                  Db: precetas
                Name: test_event_01
             Definer: root@localhost
           Time zone: SYSTEM
                **Type: ONE TIME**
          Execute at: 2014-09-23 10:54:56
      **Interval value: NULL**
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
3 rows in set (0.00 sec)

What am I doing wrong?


texts:
I am getting this error when i try to create a new publicaton in dbserver-1 after reinstalling SQL Server in my database server 2.

Msg 18482, Level 14, State 1, Line 1
Could not connect to server 'dbserver-2' because 'dbserver-1' is not defined as a remote server. Verify that you have specified the correct server name. .

I had replication of some tables enabled from dbserver-1 to dbserver-2 and it was working good.

Both dbserver-1 and dbserver-2 are in the same domain.


texts: Query Transactional Replication Article Filters

texts:
Through the UI, I can see that I have 2 tables with filters on them. I need to be able to query this information but cannot find the table that contains the data. I did a search for any column in the Distribution database with a name like either "Filter" or "Where". The results are below. Unfortunately, none of these tables has the data I'm looking for  in them.

 - SELECT * FROM IHarticles
 - SELECT * FROM IHextendedArticleView
 - SELECT * FROM MSarticles
 - SELECT * FROM MSmerge_articlehistory
 - SELECT * FROM MSmerge_articleresolver
 - SELECT * FROM sysarticlecolumns
 - SELECT * FROM sysarticles
 - SELECT * FROM sysextendedarticlesview
 - SELECT * FROM sysschemaarticles

Does anyone know where this data is held?


texts:
I have a local installation of Oracle. I want to add the XMLDB package to it. As step 1, I need to connect to it via sqlplus. And I am not able to.

The installation has a database called Pm.

Here is what I have tried on the command line:

sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 12:11:44 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: SYSTEM@PM
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name: SYSTEM@Pm
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name: system@PM
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\sgupta>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 12:14:15 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: system@Pm
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Here is what the tnsnames.ora file looks like:

# tnsnames.ora Network Configuration File: C:\app\sgupta\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

PM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bentreki-E6330.callidussoftware.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Pm)
    )
  )

CM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bentreki-E6330.callidussoftware.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CM)
    )
  )

PM20154 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bentreki-E6330.callidussoftware.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Pm20154)
    )
  )

And I have checked that the service is running:

Not sure why I keep getting the error 'ORA-12154: TNS:could not resolve the connect identifier specified'.


texts:
I'm in a situation where I have taken over a very large db that is primarily a single table that is partitioned.  Since it hasn't been partitioned for a while, I need to re-start the partitioning, but it is a 24/7 business and it can't be out for a few hours while SPLITing the older partitions.

If I was to create the new partition for the end of this month, then go back and SPLIT the previous partitions - is there a problem with doing them out of order, other than the partition id being in the wrong order?

Is there any performance or maintenance differences if the order of the partition id is not in sequence with the partition key order?


texts: How do I Delete A Database And Log Files With SQL 2008 Script?

texts:
While working on making an automated Delete and Restore script for my backups I ran into a snag. I can not delete the database; however, the delete button works exactly how I need it to. Can anyone tell me the script for that delete button?

I have also tried another query to do this task, however it is not working like I would like. Here is the code:

> USE CENTRAL
ALTER DATABASE DATABASE REMOVE FILE DATABASE_1.LDF
ALTER DATABASE DATABASE REMOVE FILE DATABASE_2.LDF
ALTER DATABASE DATABASE REMOVE FILE DATABASE.MDF
ALTER DATABASE DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DATABASE SET OFFLINE
EXEC sp_detach_db DATABASE, true;
DROP DATABASE DATABASE
GO

The error that I get when I run the code is:

Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102)  Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102)  Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102).  The step failed.

I am not sure what I am doing wrong, can someone please tell me?


texts: User and Schema Recreated during Stored Procedure Creation

texts:
As mentioned in the subject, a new user and schema are being created when a developer I'm working with creates a stored procedure. Originally I made a user and login for that developer individually but since deleted both. However, whenever they run a script to create a SP a user and schema named after their Active Directory name are recreated and the SP is placed in that "new" schema.

Even when I delete the SP, the user, and the schema, if they create the SP again the user and schema come back.

Does anyone have any insight into this?


texts: Manual Failover in PostgreSQL without changing database connection string in services

texts:
our ADO.net application uses npgsql to connect to postgresql database. We have implemented a normal cluster of 2 servers(master and slave) as of now.

As we don't want to change the database connection string in the application during failover, Is there any other way except VIP to point the application to the new master server after failover?

We are using postgresql-9.6.


texts: View with SentryOne Plan Explorer from SSMS context menu doesn't work if Plan Explorer is already open

texts:
I am using SentryOne Plan Exporer.

When I right click in the execution plan pane in SSMS, the first option is

  View with SentryOne Plan Explorer

Clicking this fires up an instance of Plan Explorer and opens the execution plan.

If I leave Plan Explorer open and then run another query in SSMS and right click in the Execution Plan pane and once again click View With SentryOne Plan Explorer, nothing happens. I would have thought either a new tab or new instance of Plan Explorer would open or a new tab in the already running instance?

Am I doing something wrong or is this a feature?


texts: Why does Table Definitions Opened differ this much between cluster nodes?

texts:
I have a Percona/Galera cluster and see this when i monitor the different cluster nodes. What does this really mean? And is this something erroneous that needs to be taken care of?

This is how one of the nodes looks like.

Thanks in advance.

All other nodes in the cluster looks like this


texts: PostgreSql JSONB SELECT against multiple values

texts:
I have a very simple JSON table which I populate with some sample data:

CREATE TABLE jsonthings(d JSONB NOT NULL);

INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}');
INSERT INTO jsonthings VALUES ('{"name":"Second","tags":["foo","bar"]}');
INSERT INTO jsonthings VALUES ('{"name":"Third","tags":["bar","baz"]}');
INSERT INTO jsonthings VALUES ('{"name":"Fourth","tags":["baz"]}');

CREATE INDEX ON jsonthings USING GIN(d);

And am attempting to use the index when running a SELECT.  A simple SELECT to obtain the rows where the value is a single item works just fine:

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}';

But when attempting to run a query which matches more than one value of name I can't find out how to use the index.  I've tried:

SELECT d FROM jsonthings WHERE d->>'name' = ANY(ARRAY['First', 'Second']);
SELECT d FROM jsonthings WHERE d->'name' ?| ARRAY['First', 'Second'];
SELECT d FROM jsonthings WHERE d#>'{name}' ?| ARRAY['First','Second'];

and all of them show a sequential scan of the table (I'm using enable_seqscan=false to force index use if possible).  Is there some way I can rewrite the query so that it uses an index?  I'm aware that I could do:

SELECT * FROM jsonthings WHERE d @> '{"name":"First"}' OR d @> '{"name":"Second"}';

but then I have a variable-length query and I'm going through JDBC so would then lose the benefits of the query being a PreparedStatement.

I'm also interested in seeing a similar query against any of a number of items in the tags key, e.g.:

SELECT d FROM jsonthings WHERE d @> '{"tags":["foo"]}' OR d @> '{"tags":["bar"]}';

but using an ARRAY rather than multiple conditions and using an index.

This is on PostgreSql 9.4.


texts: Turning multiple fields in column to row

texts:
I have an issue that has really stumped me in SQL Server.

I have recently had the need to insert a row in to a table, made up from a number of columns. I want to make the one row up from something like the below.

id | CustomerID  | Key         | Value
-------------------------
1       2           Firstname    Tim
2       2           Secondname   Skold
3       2           Age          48
4       2           Gender       Male

So the output should be something like:

customerid  |   Firstname | Secondname | Age | Gender
-----------------------------------------------
 2                Tim          Skold       48     Male

I have tried to pivot the result, but can't get it to work, and have also tried to union the results, but this gives the results as a column, not as a single row.

The messy way in which this is being done currently is by joining on to the same table multiple times to get each field (firstname, secondname etc...)

Maybe unpivoting?

Really not sure, thanks for any help...

James.

EDIT***

Updated table view.


texts: AlwaysOn Availability Group Forced Failover

texts:
May I request your input.

I setup SQL 2012 with always on availability group for my databases in sync and secondary readable mode. I completed failover tests with sync mode and it is functioning as expected.

After a forced failover testing with async mode, do I need to rebuild my old primary database? Because changes in the new primary are not reflected back. The database status is not synchronized...instead of synchronizing...

Appreciate your input as I get my head around this concept.

Thank you


texts: How to express this constraint in a database schema?

texts:
I have the following functional dependencies which are in BCNF:

a,b -> c
a -> d
b -> d

With the additional constraint, that no a and b should be combined with a c, where a and b have different ds.

Example:

a | d   b | d   a | b | c
-----   -----   ---------
1 | 3   5 | 3   1 | 5 | 6
2 | 4           2 | 5 | 7

The first row in a,b,c is allowed (1->3,5->3), but the second row is forbidden, since (2->4,5->3) 4 != 3.

This additional constraint can have two effects on my data. For each a,b,c there are two redundant ways of determining the d. There can be data which violates the constraint.
How can my schema reflect this additional constraint?


texts: what is the maximum capacity allowed for SQL managed Instance?

texts:
I am looking in option to migrate to SQL Managed Instance but found on MSDN that it supports max of 8 TB in General tier and 4 TB Business critical. what are option for databases greater than 8 TB?
Is IAAS the only option available at this time? Is Microsoft planning to extend capacity limit on Managed Instance?


texts: SQL Job Step created via wizard does not correctly set server & database_name values

texts:
I've recently been tasked with migrating a database from SQL Server 2005 to 2008R2. This has all been accomplished without any issues, but one part of the system requires a Snapshot Replication to be run as part of an overnight SQL Job.

So there's a step within the job setup with the command

-Publisher [ServerName] -PublisherDB [DatabaseName] -Distributor [ServerName] -Publication [ReplicationName] -DistributorSecurityMode 1

But when this step is run, it fails with the error

The step is improperly defined and so could not be run.  The step failed.

Now when I compare this step with the Job created automatically when replication was setup, they are identical.
But the automatically created job runs just fine.

I scripted the two jobs out to a query window (Script Job as > Create To > New Query Window) and I can see that the job created via the UI Wizard specifies the @database_name parameter to be 'master', and the @server parameter is not set at all.

However the automatically created job sets the @database_name to be 'distribution', and the @server parameter is set.

My question is, how can I set up this job via the provided SSMS UI so that the job will run correctly?
I would prefer to provide the client with instructions that walk them through the wizard (with screenshots) rather than providing them with a script that they have to manually edit.


texts: Slow MySql Server Performance - What and How to check?

texts:
start:disclaimer

I'm not a MySql Server DBA; I know mostly of MSSQL - which is why I need your help.

end:disclaimer

I've been asked to check why the MySql server engine is performing poorly - I have not seen nor held the databases involved and would like to know where to start.

Where do I start?

What questions should I be asking those who have access to MySql - I don't even know if they are using phpmyadmin or some other tool.

Essentially:

What items should I ask for and how would I respond to each piece they give?

What are the key items to ask for when the issue is in the performance of the database?

in MSSQL I can check sp_who2 for existing connections to see if anything is blocking, what is the counterpart in mysql?
* do not have to be specific as there can be many kinds of results for each item but would like to help get the ball rolling since it is affecting users - apparently they set up the mysql engine without having a dba on-board.


texts: Distinguishing between fact and dimension tables in data warehouse

texts:
Is it true that facts in a fact table of a data warehouse always have a relation to time? I.e. is it true that a fact must always be assignable to a specific point in time?
If this is correct, can we conclude that dimension elements (except for maybe a time-dimension) do not need to be assignable to a specific point in time?
Thanks for your help!


texts:
I've seen and read a lot of questions about the transaction log and I know that backing it up to nul is usually frowned upon, however my understanding is that this is from a data recovery perspective.  As there is a lot of transient data passing through this system the logs are changed quite rapidly and can grow quite large.  In order to save disk space I do not want to keep data that has no intrinsic value.

The Setup
We have two SQL servers running SQL Server Mirroring, these are important from a usage (up-time) perspective but the data is transient and for the most part unimportant.

We have a 24 hour RPO on the servers but a 5 minute RTO.

What I Do
In order to manage the transaction log size I run a log backup every 15 minutes to backup the log file to NUL as below.

  BACKUP LOG [DatabaseName] TO DISK = 'NUL:';

So my question is thus: Other than not being able to recover data are there any other potential issues that could occur by backing up to NUL in a mirrored environment?


texts: How to make an union on 2 joined tables without affecting the sum on the column?

texts:
I have the following tables:

cant:
id      CANT_VAL    COD_VAL fk_id_chest c1    c2
18059   18.56       R12     1           100   11
18060   11          R10     2           200   22
18061   15          R11     3           300   33

col:
FK_ID_COL_DMA   CANT_VAL    COD_VAL
18059           1134        R10
18059           1234        R3
18061           1111        R5

to look like that:

id      CANT_VAL    COD_VAL    fk_id_chest   c1      c2
18059   18.56       R12        1             100     11
18059   1134        R10        1             0       0
18059   1234        R3         1             0       0
18060   11          R10        2             200     22
18061   15          R11        3             300     33
18061   1111        R5         3             0       0

Note that the ID should be common on all rows united by id and c1 - c2 not, because it's not ok for a sum on the column.

If I make an:

    select CANT.ID,CANT.CANT_VAL,CANT.COD_VAL,CANT.FK_ID_CHEST,CANT.C1,CANT.C2
        from cant
    union
    select COL.FK_ID_COL_DMA,COL.CANT_VAL,COL.COD_VAL,CANT2.FK_ID_CHEST,CANT2.C1,CANT2.C2
        from col
            inner join cant2 on cant2.id=COL.FK_ID_COL_DMA

I will get an:

    id      CANT_VAL    COD_VAL    fk_id_chest   c1      c2
    18059   18.56       R12        1             100     11
    18059   1134        R10        1             100     11
    18059   1234        R3         1             100     11
    18060   11          R10        2             200     22
    18061   15          R11        3             300     33
    18061   1111        R5         3             300     33

and if someone would want to know the sum on c1, it will be a bad result!
Thanks!


texts:
I have a simple UPDATE statement running on a MySQL 5.6.34 database that's seemingly hanging. The statement only updates a single row with a timestamp and looks like:

UPDATE mytable SET last_updated='2017-05-31 02:13:43' WHERE id=123;

Yet after an hour, this was still running.

Running SHOW FULL PROCESSLIST; doesn't show any other processes accessing the table, so I don't think it's a deadlock. I tried killing and re-running the process, but it still hangs.

What other reasons would cause a simple UPDATE statement like this to hang? How do I diagnose this?


texts:
Basically if I do the following:

SELECT distinct id FROM Table1

I get 20 000 rows

SELECT distinct id FROM Table1
WHERE id IN (SELECT id FROM Table2)

I get 4000 rows

So when I do the following, I expect to get 16000 rows, but I get no result.

SELECT distinct id FROM Table1
WHERE id NOT IN (select id from Table2)

-Empty result-

Why is this? Can it be that I run out of memory or something?


texts: SQL Server Unique Constraint on two columns with an exception

texts:
Hi all and thanks for your advice.
Expense(SupplierID(Foreign Key), DocumentID(vchar))
I understand how to add a simple unique constraint on two columns.  However, if DocumentID = 'NA', I would like to ignore the rules of the constraint.
Some suppliers in our system do not provide an invoice id, for example.  Therefore, I leave the field NULL.  I would like to remove all nulls for the field 'DocumentID' to avoid accounting for the NULLS in my client code.
I am new to SQL Server, but I could figure out how to do this using a trigger.  The reason I'm asking here is to see if there is a better way to respond to this scenario or to avoid it by a different design.
Thanks!
Thanks Tibor.  I was unable to enter a comment correctly, I suppose.  This is what I ended up with using your method.
use CMRBE
CREATE UNIQUE NONCLUSTERED INDEX idx_SupplierID_DocumentID
ON Expense(SupplierID, DocumentID)
WHERE DocumentID <> 'NA';


texts: Limiting read access in postgres to few rows

texts:
We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any select query fired by a particular user.

Is this possible in PostgreSQL? If so, how can this be done?


texts: MongoDB - overloading PRIMARY with multiple replica set

texts:
MongoDB replica sets read data from Primary oplogs. So, Does it mean that having too many replica sets will put load on Primary node as all replica nodes will have to read data from Primary oplog?  We have 7 nodes cluster though the load is not heavy and hence checking if we need to downsize it to 5 or so ..

Thanks


texts:
I have a test server running with a few databases for separate apps. I want to make sure all future users won't be able to see what's going on in their neighbours. My idea is to set default connect privileges to deny, and to selectively set connect permission on each database.
The first I tried was to revoke connect (REVOKE CONNECT ON schema public FROM public) on all databases, but unless I do it for each and every existing (and future) DB, it doesn't work as I want it to.

I then tried to revoke connect privileges on the template DB to the public role (REVOKE CONNECT ON DATABASE template1 FROM PUBLIC), but it doesn't work either.

I also tried directly revoking connect rights from public role, to fail.
For now I'm creating users with NOCREATEROLE NOCREATEDB NOSUPERUSER NOINHERIT properties.

I tried suggested solution from various other topics like

https://stackoverflow.com/questions/17338621/what-grant-usage-on-schema-exactly-do

Created user can access all databases in PostgreSQL without any grants

Why is a new user allowed to create a table?

But for now I'm completely stuck.


texts: EMCA port Issue on Installing Oracle on Windows Server 2008 guested on VMware

texts:
I am trying to install Oracle 11gR2 on Windows Server 2008 guested on VMware Workstation 10 but I am getting this error on Enterprise Manager Configuration.

I also tried to login to the database through SQL*Plus but it is forcing me to connect as SYSDBA or SYSOPER which I do not have passwords for.

Can you please let me know how to fix the issue?

From comments and discussion, nothing seems to be listening on any ports in those ranges according to netstat, and the log apparently doesn't give any more details than the error dialog. There may be multiple interfaces on the VM.


texts: Extract MIN and MAX values related to datetime values on Postgres 9+

texts:
I am trying to query a table that has a collection of student grades recorded over time. I would like to produce a result set that grabs the student id, the first grade, first grade date, last grade, last grade date.

I think I need to use the MIN and MAX functions and some subqueries to achieve this, but I'm just not getting the results I need.

Is there an effective way to achieve the results below on PostgreSQL?

Database Example:

user_id | grade | grade_date
1       | A     | 01/05/2016
1       | B     | 01/15/2016
1       | C     | 01/31/2016
2       | A     | 01/05/2016
2       | B     | 01/15/2016
2       | C     | 01/31/2016
3       | A     | 01/05/2016
3       | B     | 01/15/2016
3       | C     | 01/31/2016
4       | A     | 01/05/2016
4       | B     | 01/15/2016
4       | C     | 01/31/2016

Results I'm aiming for:

user_id | first_grade | first_date | last_grade | last_date
1       | A           | 01/05/2016 | C          | 01/31/2016
2       | A           | 01/05/2016 | C          | 01/31/2016
3       | A           | 01/05/2016 | C          | 01/31/2016
4       | A           | 01/05/2016 | C          | 01/31/2016


texts: PostgreSQL 9.3, set database (or all) to read only mode

texts:
Is any simple function/command for PostgreSQL 9.3 to set whole database to read only mode and then back to RW mode?
For example same as for MySQL:
FLUSH TABLES WITH READ LOCK;
LOCK TABLES customers READ;
UNLOCK TABLES;

or
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;
SET GLOBAL read_only = 0;
UNLOCK TABLES;

Thanks.


texts: Postgresql - how to list indexes on a table that is partitioned?

texts:
This command works for non-partitioned tables:

select * from pg_indexes

But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition.

To drop an index, I need the original name.

How do I list all indexes on a table that is partitioned (with the name it was created)?


texts: Why does SQL Server 2012 Express use 9.5GB of RAM on my server?

texts:
I am building an application in which I plan to embed SQL Server 2012 Express as the primary datastore.  When testing on my development machine (Win7-32 with 3GB RAM), I never observed the sqlservr.exe process to use more than 1GB of RAM as I would expect from the published hardware scaling limits for the Express edition of SQL Server.

I then moved my application to a server-grade machine (Win Server 2008R2 64-bit with 16GB RAM) to evaluate its performance there and was surprised to find that the sqlservr.exe process rapidly expanded to approximately 9.5GB of RAM and stayed there.

I restarted it a couple times to see if that would have any effect, but each time, the process rapidly returned to ~9.5GB.  Now I am certainly pleased to have SQL Server Express make use of my RAM, but I would like to know if this is expected behavior so that I do not come to rely on performance levels that are based on RAM usage that is incorrect.

FYI, the version of SQL Server on my server machine, according to SELECT @@VERSION, is:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
    Oct 19 2012 13:38:57
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

My 9.5GB number came from the "Private Working Set" number in Task Manager.  The first table of output from DBCC memorystatus (albeit on a now idle server) is below:

PROCESS/SYSTEM COUNTS
Available Physical Memory   5543616512
Available Virtual Memory    8734902411264
Available Paging File       22471094272
Working Set                 9664200704
Percent of Committed Memory in WS   99
Page Faults                 2627510
System physical memory high 1
System physical memory low  0
Process physical memory low 0
Process virtual memory low  0

Output from spaghettidba's suggested query on sys.dm_os_memory_clerks:

MEMORYCLERK_SQLBUFFERPOOL   1410
OBJECTSTORE_LOCK_MANAGER    256
MEMORYCLERK_SQLCLR          38
MEMORYCLERK_SOSNODE         26
CACHESTORE_SQLCP            11

plus ~10 more smaller entries that sum to less than 30MB.


texts:
I have scripts generated from a MySQL database using phpmyadmin to create database and insert data. The scripts were exported using compatible with MS SQL. Needless to say they are not compatible at all, transaction is open using

Start Transaction;

I tried compatibility level 100 (I'm using latest SQL Server Express), doesn't work.

I cannot install MySQL and cannot contact the software provider or database creator.

I am planning to use notepad++ to find and replace unless there is a better way. Questions are:

is there a better way?
What should I look for to find and replace (assuming someone has done that before)?


texts: How To restrict DDL access to a sql server 2008 login

texts:
I need to restrict the DDL access to SQL server login, if not possible how to restrict DDL to a particular user

Thank you


texts: How to update a multiple rows all at once with a procedure.?

texts:
Im trying to do a procedure to update an (for now) empty column recently added.

In this new column, i will put the values of the result obtained by a join query.
To explain it better, i have this

delimiter //
create procedure update_amount_products (in id int)
begin
update categories set products_amount=
           (SELECT DISTINCT COUNT( products_to_categories.products_id )
FROM categories_description
INNER JOIN products_to_categories ON products_to_categories.categories_id = categories_description.categories_id
INNER JOIN products_description ON products_description.products_id = products_to_categories.products_id
WHERE categories_description.categories_id =id)
WHERE categories_id = id;
end
//
call update_amount_products(id);

So what i want, is to not calling the procedure everytime with diferents "ids", i want to update all at once (because there are over 1500 rows with diferents ids). What can i do?

I have been thinking of some kind of "for" to get all the ids and saving them on a variable so later used on my update procedure, but i've seen that there isn't any array possibilty in sql nor foreach.

Any help is highly appreciate it.


texts: What's the fastest way to find the first 2 purchases for every customer in Redshift?

texts:
I'm looking to find the first 2 purchases for every customer.

I have a table that looks something like this:

orders

With columns:

order_id
customer_email
grand_total
purchase_date

There are many millions of rows in this table and I'm trying to look for an efficient way to grab the first two earliest records for each customer, so that I get a result set that looks something like this:

order_id
customer_email
purchase_date1
purchase_date2

I'm at a loss on how to do this efficiently.


texts: Rounding of SMALLMONEY Data Type in SQL Server 2005

texts:
I'm on SQL Server 2005, and I am trying to figure out a rounding issue with smallmoney data types.

For example, the value 1.2849, when rounded to 2 decimal places, produces 1.28, but shouldn't it produce 1.29?  e.g. 9 rounds up for 1.285, 5 rounds up for 1.29.

Here is the example:

SELECT
    'CAST(1.2849 AS SMALLMONEY)' OPERATION, CAST(1.284948 AS SMALLMONEY) RESULT
UNION
SELECT
    'ROUND(CAST(1.2849 AS SMALLMONEY), 2)', ROUND(CAST(1.284948 AS SMALLMONEY), 2)
UNION
SELECT
    'ROUND(ROUND(CAST(1.2849 AS SMALLMONEY), 3), 2)', ROUND(ROUND(CAST(1.2849 AS SMALLMONEY), 3), 2)


texts:
I have a table with 3 columns id,Item,parentid
when storing data the top most level will have a parentID of 0
while the second level will have ParentID of 2 (relative) which will be an id an item.

 ----------------------
 id | Items    | parentId
 ----------------------
  1 | Computers|    0
  2 | Dell     |    1
  3 | laptop   |    2

Is this the best design considering the levels might be endless.


texts: activate Service Broker on a database in an Availability Group

texts:
I found some older posts dealing with this(5 yrs old or more), but I am confused on the process or if this information is out of date, hence my new question.  I am on sql 2016 SP2-CU11 enterprise.
I need to enable service broker on a database that is in an availability group.  Its 1.8TB in size, so I really don't want to take it totally out of the AG and reseed it all over again with a fresh backup if I can avoid it.  Some older posts say you have to completely remove and add the db back in the AG, others reference just running some code to create endpoints, and routes which I have never run across before.
Is the process below the only way to enable service broker on a DB in an AG where it is not enabled?

remove the secondary copy of the DB, then the primary copy from the AG.

Run below code to activate service broker:
ALTER DATABASE XXXXXX SET ENABLE_BROKER;
GO

Take a full backup, restore it to the secondary with no recovery.

join the DB to the AG on the secondary.


texts:
This is the issue I am trying to fix since hours, almost a day. I am unable to get an understanding of what might be wrong.
I have a SQL table notification

I am trying to persist a java object via hibernate
public void persistNotification(Notification notify){
        sessionFactory.getCurrentSession().save(notify);
    }

notify object passed to the method looks fine to me with all the values
Notification [notificationId=null, notificationMessage=ethan is now following you, notificationType=follow, notificationRead=false, actor=ethan, subject=diggle, notificationCreationTime=2015-12-09T19:13:23.002, userId=1]

But I am encountering a SyntaxErrorException.
2015-12-09 18:52:05.0550 DEBUG http-bio-8080-exec-10 org.hibernate.SQL – insert into notification (actor, creation_time, notification, read, type, subject, user_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into notification (actor, creation_time, notification, read, type, subject, user_id) values (?, ?, ?, ?, ?, ?, ?)
2015-12-09 18:52:05.0579 DEBUG http-bio-8080-exec-10 org.hibernate.engine.jdbc.spi.SqlExceptionHelper – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, type, subject, user_id) values ('ethan', '2015-12-09 18:51:54', 'ethan is ' at line 1 [n/a]
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, type, subject, user_id) values ('ethan', '2015-12-09 18:51:54', 'ethan is ' at line 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)

2015-12-09 19:00:08.0027 DEBUG http-bio-8080-exec-10 org.springframework.web.servlet.mvc.support.DefaultHandlerExceptionResolver – Resolving exception from handler [public org.springframework.http.ResponseEntity<java.lang.String> com.mycricket.controller.UserController.followUnfollowUser(java.lang.String)]: org.hibernate.exception.SQLGrammarException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, type, subject, user_id) values ('ethan', '2015-12-09 19:00:07', 'ethan is ' at line 1
2015-12-09 19:00:08.0028 DEBUG http-bio-8080-exec-10 org.springframework.web.servlet.DispatcherServlet – Could not complete request
org.hibernate.exception.SQLGrammarException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, type, subject, user_id) values ('ethan', '2015-12-09 19:00:07', 'ethan is ' at line 1
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)

Can anyone help me out what might be the issue? Any clue, guesses would be appreciated.
I also dropped & recreated the table but no luck. I don't I am using any reserved SQL words.


texts: Index or View of all UUIDs in Postgres DB?

texts:
I have a Postgres database with about 35 tables of varying sizes. Every table has an auto incrementing integer primary key, as well as a column of native Postgres uuid data type recording Version 4 UUID values.

The application code logs UUIDs, but...alas...doesn't always log the object type or table.

While we're working on cleaning that up, we sometimes just want to figure out what row a UUID refers to.

Is there a reasonable strategy to create a view, index, or even full on table to efficiently find a row given a UUID, if every row in the database has a UUID? Or am I chasing an antipattern and should solve it at the application level?


texts: SQL Server Actual Plan. Incorrect row estimates

texts:
While practicing in tuning queries on StackOverflow database I got stuck on the following one:

SELECT TOP 50 Id AS [User Link],
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 1 AND
          LastEditorUserId = Users.Id AND
          OwnerUserId != Users.Id
) AS QuestionEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 2 AND
          LastEditorUserId = Users.Id AND
          OwnerUserId != Users.Id
) AS AnswerEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE LastEditorUserId = Users.Id AND
          OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC;

The plan is here.

Execution stats:

(50 row(s) affected)
Table 'Posts'. Scan count 43217677, logical reads 172988050, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 5, logical reads 12692, 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.

(1 row(s) affected)

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

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

I have three questions here:

What does the operator 'Constant scan' refer to?
Why row estimates can be incorrect in the following blocks:

a.

b.

What to do to make estimates more accurate? What index/technique to consider to improve the query performance?

Any hint will be deeply appreciated.


texts:
I was logging slow queries of my system. Now I have few queries to be optimized and reset the global slow_query_log variable to 0. Now I want to delete slow query log file mysqld-slow.log. can anyone tell me what is the standard way to do this ?

I am using Cent OS and do not want to delete or affect other log files (i.e. general log or binary log)
Thanks.


texts:
I'm trying to run mysqldump to create a database snapshot, and I'm finding it will randomly stop midway, without reporting any error. My database is relatively small (about 100MB) and is using InnoDB.

I'm running it like:

mysqldump --force --single-transaction --quick --user myuser --password=mypass -h mydatabasehost mydb > /tmp/snapshot.sql

Checking the exit code reports 0.

My version is: mysqldump  Ver 10.13 Distrib 5.1.52, for redhat-linux-gnu (i386)

I've seen some similar posts and even an official bug report, but neither solutions seem to apply.

How to I get mysqldump to take a complete database snapshot?

EDIT: My database currently resides on Amazon's RDS.


texts:
I am new to thinking in terms of databases. I have read about how B-trees are stored on disk but I haven't seen an actual implementation or read about how it works for B-trees.

Wondering how one might store Skip Lists on disk. There are many descriptions for storing it with pointers in memory, but I haven't seen anything on disk storage. Wondering what it might look like at a high level (if it's just one big file or many small files, if there are different types of files, etc.).


texts:
Windows simply crashes and opens the blue screen for dumping physical memory on trying to import sql dump.
Blue screen error: IRQL_NOT_LESS_OR_EQUAL
So far I have tried 4 methods:

Through workbench
Through command prompt using mysql command
Through command prompt using mysql -h command
Through ant: ant import-database after creating data-sync folder

Windows crashes in all the 3 cases

Before each new trial I un-install mysql and delete the MySQL folder from Program Files and MySQL data from Program Data. Size of dump files is 2.75 GB.

MySQL versions I have tried with:

 mysql-5.5.44
 mysql-5.5.33.2
 mysql-5.5.44.0

System configurations:

Workbench: 6.3.30
My system has 12 GB RAM
Windows 7 Service Pack 1
AMD A6-5400B APU with Radeon(tm) HD Graphics 3.60GHz


texts:
We started to observe a strange behaviour in out filestream table.

We have a couple of records that in field with data we have 0x or 0x000...
In audit trails data we have found that these broken/empty bytes are stored on insert, so we started to investigate our insert statements (by linq to sql/stored proc executed by ado.net) and found nothing.
But what is more strange, in the audit trails we have found a few records in which the data is correct, but no records with the zeros, which are stored in real table. It looks like the data was changed without the update statement.
We never disable the audit trails triggers on production database so each and every change should be logged.
It looks like these records got corrupted in other way, but we cant google anything and have no clue how to debug this situation properly.
We have checked SQL Server logs, Event Viewer but can't find anything.

Can anybody suggest any hints? We are completly stuck at this moment.


texts: Excluding a specific table from a SSDT deploy

texts:
I have an existing database with everything in schema dbo. I have an SSDT project with objects I am adding to it with schema foo

I have a table that looks like this in the project:

CREATE table foo.a (
    id INT NOT NULL
        CONSTRAINT [PK_foo_a] PRIMARY KEY CLUSTERED
        CONSTRAINT [FK_foo_a] FOREIGN KEY REFERENCES [dbo].[a],
    desc NVARCHAR(50) NOT NULL
)

It depends on dbo.a. dbo.a has many columns that are foreign keys to other columns. Someone else (who maintains the default schema) might change dbo.a.

I'd like to simple store dbo.a as:

CREATE table dbo.a (
    id INT NOT NULL
        CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
)

So it gets built internally, but not deployed. Is that possible?


texts: What configs influence whether user credentials/grants are written to the binlog in Mysql

texts:
I have a fairly trivial master-master setup.
When testing this setup I see that mysql users are binlogged and replicated on slaves. However on each of the masters the binlog-do-db* is for a specific database only:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      694 | my_db        |                  |
+------------------+----------+--------------+------------------+

Why are DDL queries like (CREATE USER) and inserts into mysql.user logged as well? I can't see any mechanisms/configs in the manual which explain this, seemingly, exception.

If it matters, the binlog format is:

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

* = Let's forget about the bad-practice of using binlog-do-db for now


texts: SQL Server 2008 R2: From a SQL Server Log file, how do I find out what is causing an event

texts:
Problem:

The attached image is a screen shot of the SQL Server log showing an event that happens every Tuesday at around 1:20 AM. This event is causing the transaction log backup chain to break and all subsequent transaction log backups to fail.

Question:

How can I find out what is causing/doing this?


texts:
I am running azure and considering deploying a SQL Server 2014 instance due to the in memory feature, however, I was looking through the documentation and couldn't seem to find how to configure In-Memory, is it enabled by default, what considerations for data do I need to take?

Does any one have any experience with the In memory feature in SQL Server


texts: Logging user's id with the query he executed

texts:
So, I am trying to log not just the postgres user but his ID from my  table (for example) Person (person_id) when he calls functions.

So far, from what I can tell I can get almost every information I need from these system tables:

pg_stat_activity
pg_stat_statements if I activate it (but probably won't be able to due to some restrictions)

But the part that I can't seem to figure out is if I want to log the user's ID from (for example) the User table (user_id for example).

Basically I'd like to create a small table that will only house the last month's log, and I will insert into it.

user_id (not system id, but from my own table)
IP address
timestamptz of when he called the function/query
query he executed

My main issue is the user_id part, since the other 3 values already exist in the pg_stat_activity (client_addr, query_start, query)

If there's no easy way to make this happen, lets say I want to create a function that will somehow select the user_id based on parameters returned by pg_stat_activity, but I can't seem to figure out how I would do that. Hidden table parameters like tableoid, cmin, cmax, xmin, xmax, ctid don't help me here, at least I don't see how, maybe I missed something?


texts: Permission for business logic layer & application

texts:
I'm very new into db development and currently working on my first production app.

I learned that I would need a business logic layer (BLL) to authenticate and authorize users, for example:
John can only query the database while Andrew can insert new records.

Following are my questions that required clarification:

Does it mean the BLL would have to connect to the database with greatest privilege necessary, instead of least privilege needed for each user?
Will the BLL need INSERT permission to provide service to Andrew, which is more than enough for John?
Can we solve this potential flaw (except by securing BLL better, which I would of course do)?

For example, implement authorization in database layer (as described here)?


texts: MySQL Client can Connect but Replication Slave Can't

texts:
We have an odd issue we've never come across. We have roughly 14 pairs of master/slaves all running MySQL 5.6.35 without issue. We are trying to add a slave to a slave (which we have two other instances of running in other data centers). We can connect from the command line of the new backup DB (slave) server as follows:
[root@BackupDB ~]# mysql -u slave -p -h xx.xx.xx.xx

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 47075
Server version: 5.6.35-log MySQL Community Server (GPL)

mysql xx.xx.xx.xx:3306 <(none)>

However when the slave attempts to connect we get the following status:
mysql localhost:mysql.sock <(none)> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: xx.xx.xx.xx
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'slave@xx.xx.xx.xx:3306' - retry-time: 60  retries: 32
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 200922 16:06:29
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

We have verified that the slave user has SLAVE REPLICATION privileges on the master as follows:
grant replication slave on *.* TO 'slave'@'xx.xx.xx.xx' identified by 'xxxxx';
flush privileges

We have set the master information on the slave as follows:
change master to master_host='xx.xx.xx.xx', master_port=3306, master_user='slave', master_password='abc', master_log_file='bin.000001', master_log_pos=0;

For the purposes of this exercise we have made the password "abc" to avoid a fat-finger situation. We have had two people double-check that the IP address, ports and passwords were the same.
We are stumped and any help would be appreciated.


texts: Ideal CDC Latency for SQL Server Replication

texts:
I am seeking to understand what the ideal latency for an implementation of CDC in SQL Server Standard (2017) is.
The latency I am referring to can be found from the sys.dm_cdc_log_scan_sessions DMV outlined here.
The polling interval for this implement is equal to 1 day (86400 seconds) and continuous mode is being used as both shown from the sys.sp_cdc_help_jobs table.
I know the latency is defined as "The difference, in seconds, between end_time and last_commit_cdc_time in the session" but I am still struggling to understand what is considered an ideal latency.
I would imagine it would be either be near 0 as most ideal latencies are or it is ideally near the polling interval.
Please and thank you


texts:
I know there are multiple questions on the same issue and I followed them but not sure what is happening here. First of all I am trying to change the default db path to a a separate mounted disk. And I changed the group and permissions and I updated the SELinux Contexts. But still I can see that the db is not starting saying permission denied in the log. Here is my log report

2019-03-03T18:53:25.514-0600 I CONTROL  [main] ***** SERVER RESTARTED *****
2019-03-03T18:53:25.517-0600 I CONTROL  [main] Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] MongoDB starting : pid=34998 port=27017 dbpath=/run/media/thomson/data2/mongo 64-bit host=localhost.localdomain
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] db version v4.0.6
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] git version: caa42a1f75a56c7643d0b68d3880444375ec42e3
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.1e-fips 11 Feb 2013
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] allocator: tcmalloc
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] modules: none
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] build environment:
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten]     distmod: rhel70
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten]     distarch: x86_64
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten]     target_arch: x86_64
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { bindIp: "127.0.0.1", port: 27017 }, processManagement: { fork: true, pidFilePath: "/var/run/mongodb/mongod.pid", timeZoneInfo: "/usr/share/zoneinfo" }, storage: { dbPath: "/run/media/thomson/data2/mongo", journal: { enabled: true } }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongodb/mongod.log" } }
2019-03-03T18:53:25.529-0600 I STORAGE  [initandlisten] exception in initAndListen: Location28596: Unable to determine status of lock file in the data directory /run/media/thomson/data2/mongo: boost::filesystem::status: Permission denied: "/run/media/thomson/data2/mongo/mongod.lock", terminating
2019-03-03T18:53:25.529-0600 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2019-03-03T18:53:25.529-0600 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27017.sock
2019-03-03T18:53:25.529-0600 I CONTROL  [initandlisten] now exiting
2019-03-03T18:53:25.529-0600 I CONTROL  [initandlisten] shutting down with code:100

But it works when I put the default path in mongo.conf file
Here is the default directory details (/var/lib/mongo)

[root@localhost lib]# stat mongo/
File: ‘mongo/’
Size: 4096          Blocks: 8          IO Block: 4096   directory
Device: fd00h/64768d    Inode: 70738359    Links: 4
Access: (0755/drwxr-xr-x)  Uid: (  979/  mongod)   Gid: (  973/  mongod)
Context: system_u:object_r:mongod_var_lib_t:s0
Access: 2019-03-03 18:23:09.971149485 -0600
Modify: 2019-03-03 17:49:21.178099641 -0600
Change: 2019-03-03 17:49:21.178099641 -0600
Birth: -

Here is the details of my new directory (/run/media/thomson/data2/mongo)

[root@localhost data2]# stat mongo/
File: ‘mongo/’
Size: 4096          Blocks: 8          IO Block: 4096   directory
Device: 820h/2080d  Inode: 4980737     Links: 2
Access: (0777/drwxrwxrwx)  Uid: (  979/  mongod)   Gid: (  973/  mongod)
Context: system_u:object_r:mongod_var_lib_t:s0
Access: 2019-03-03 18:52:09.764192229 -0600
Modify: 2019-03-03 18:32:17.212162930 -0600
Change: 2019-03-03 18:53:18.814193926 -0600
Birth: -

I am not sure what else need to be done. A help would be greatly appreciated. Thanks.


texts: Modelling a database for a simple IT device tracking scenario

texts:
Sorry, my English is not that good.

I need a simple IT device tracking sytem at work.
For each device I need to know its current location, status and ownership (employee to which it is assigned).
Some devices (for example network printers) are not assigned to anyone.

It’s important to keep track of events that cause changes in device’s status, it’s assignment or location.

For example, an event could be "device receipt". This event cause the device status to change from ordered to received. It’s location is set to “storage room”. It's not assigned to anyone.
In the schema below, the black relationships keep track of the current situation, while the red part keep also all events time sequence.
When an event is registered, the DE, DL and DS relationships must be updated to current values.

My question is: Is it a good design? It does not ensure that the status registered with the event (for example) is the same that is associated to the current device status.

Is it due to ER limitation or a bad design?


texts: Dynamically generate batched data for import

texts:
I need to execute a test in which I have to simulate 20 years' historical data in PostgreSQL (and TimescaleDB) DB. My problem is that I have to generate .sql files and ingest them (using psql-client) in the targeted DB.

We made some calculations and the result is that our database will have 261 billion rows in our table for 20 years, so each year contains 13.05B data.

For each row, we have a timestamp (integer type) and I thought, to be more efficient, I write, in my .sql files transactions of 10.000 elements.
To make the generated .sql files small in disk space (I generate those files in python), I limited each file to 20M rows each.

So I thought that I could generate these files dynamically in a bash file and, when a file is generated, I run a psql command to ingest it into the DB, but the problem is that I don't know how to handle it: The ingest process takes much more time than .sql file generation, so in bash commands, I am afraid my bash script will wait the ingest process before starting to generate a new .sql file and execute the ingest process again.

So to summarize, I try to create an ingest process pseudo-batch based in which each generated .sql file that has been ingested successfully will be removed to avoid to take too much disk space.

How to avoid the fact it will wait the ingest process before starting an other .sql file generation and then start the ingest process?

This is for development purpose, these data I want to ingest are close to the one we would like to create in production mode. The aim, for now, is to execute read requests and compare those request in PostgreSQL and TimescaleDB.


texts: How to avoid invoking functions twice when using GROUP BY and HAVING?

texts:
I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents.

The following query works and returns the correct results:

SELECT node,parents FROM
(
  SELECT nr.child AS node, COUNT(nr.parent) AS parents
  FROM node_relation nr
  GROUP BY nr.child
) AS count WHERE parents > 1;

The result set:

 node   | parents
--------+---------
 n21174 |       2
 n8635  |       2
(2 rows)

The table definition is:

            Table "public.node_relation"
   Column    |         Type          |   Modifiers
-------------+-----------------------+---------------
 child       | character varying(50) | not null
 parent      | character varying(50) | not null
Indexes:
    "node_relation_pkey" PRIMARY KEY, btree (child, parent)

I re-wrote the query to not use a sub-select:

SELECT child AS node, COUNT(parent) AS parents
FROM node_relation
GROUP BY child
HAVING COUNT(parent) > 1;

The new query works, but I wonder about the COUNT function being invoked multiple times.

Update: Here is the query plan:

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.00..1658.81 rows=19970 width=16)
   Filter: (count(parent) > 1)
   ->  Index Only Scan using node_relation_pkey on node_relation  (cost=0.00..1259.40 rows=19971 width=16)

I would prefer to use the parents alias but the following does not work:

SELECT child AS node, COUNT(parent) AS parents
FROM node_relation
GROUP BY child
HAVING parents > 1;

ERROR:  column "parents" does not exist
LINE 1: ...parents FROM node_relation GROUP BY child HAVING parents > ...
                                                            ^

Will PostgreSQL optimize out the multiple invocations of COUNT?

If not, is there an alternative form of this query that would be more efficient?


texts:
Im trying to create a trainer/trainee environment in Oracle 18c XE. So far I have 4 trainee users which all have the hr-sample installed. Now, I plan on having one trainer user that is able see everything the users are doing in real time and can adjust if need be.
This topic seems to be discussed often but I have not seen one solution where the grantee can see newly created objects after a script like this has been run:
BEGIN
  FOR t IN (SELECT object_name, object_type FROM all_objects WHERE owner='TEST1' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
    IF t.object_type IN ('TABLE','VIEW') THEN
      EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON SOURCEUSER.'||t.object_name||' TO TEST2';
    ELSIF t.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      EXECUTE IMMEDIATE 'GRANT EXECUTE ON TEST1.'||t.object_name||' TO TEST2';
    END IF;
  END LOOP;
END;

There has to be a way of giving another user select and create privileges on all objects including the ones that will be written in the future. Of course there is the possibility of writing a trigger but this just seems way too complicated for such an easy requirement.
Is it possible to grant a user the equivalence of ownership over a another users schema?


texts: IF statement not skipping TempDB when Looping through Databases with sp_MSForEachDB

texts:
[SQL Server 2012 SP2 EE]

Why does the following script gives me an error relating to tempdb?

    exec sp_MSForEachDB '
    IF ( (select database_id from sys.databases where name = ''?'') > 4)
    BEGIN
    ALTER AUTHORIZATION ON DATABASE::? TO [sa];
    ALTER DATABASE [?] SET RECOVERY SIMPLE;
    END'

Here is the error I get:

  Msg 5058, Level 16, State 1, Line 5
  Option 'RECOVERY' cannot be set in database 'tempdb'.

It does the job it's supposed to do. But I am unable to think of a reason for the error. I know that the tempdb's databaseID is 2, then at least it should not try to set the option for tempdb.


texts: I'm new to SQL Server SMS and need help.  I have been asked to check that .BAK files restored be checked using SELECT queries in SQL SMS.  I have tried to create a new  database but get the error "CREATE DATABASE permission denied in database 'master'.  (Microsoft SQL Server, Error: 262)".  It does not give much information so I can find out and Googling it didn't help either.  Also if I drag and drop the .BAK file into the SQL SMS window, I am told that there is no editor available?


texts: GFS2 as shared storage for MySQL cluster with active active setting

texts:
I am using MySQL 5.7 on RHEL 7.1. For cluster management I am using Pacemaker and corosync. For shared storage, I am using GFS2 file system with iSCSI server

I am trying to implement MySQL active-active cluster (both nodes running at the same time). I have two nodes with MySQL server running. I am not able to give same data directory (shared storage) for both the nodes.

I tried to change the data directory in my.cnf file. After that the service didn't start. Currently, I am using the default storage engine i.e. InnoDB storage engine.

Is this a problem of the underlying storage engine or something else we need to change in the configuration?


texts: How to correct Failed global > initialization: FileNotOpen

texts:
I am trying to run eos and using the std docs - https://developers.eos.io/eosio-nodeos/docs/build-validation

I am trying - ~/opt/mongodb/bin/mongod -f ~/opt/mongodb/mongod.conf and I get the following -

  trevor@ubuntu-s-4vcpu-8gb-lon1-01:~/opt/mongodb$
  2018-08-03T13:03:01.096+0000 F CONTROL  [main] Failed global
  initialization: FileNotOpen: Failed to open
  "/home/trevor/opt/mongodb/log/mongodb.log"
  ~/opt/mongodb/bin/mongod -f ~/opt/mongodb/mongod.conf

I tried already chmod 755 on the log and checking the conf as per other posts here. Nothing worked. Here is the conf -

trevor@ubuntu-s-4vcpu-8gb-lon1-01:~/opt/mongodb$ more *conf
systemLog:
 destination: file
 path: /home/trevor/opt/mongodb/log/mongodb.log
 logAppend: true
 logRotate: reopen
net:
 bindIp: 127.0.0.1,::1
 ipv6: true
storage:
 dbPath: /home/trevor/opt/mongodb/data

I have Ubuntu 18.04.

Any idea what could be the cause?


texts:
I have a database that is for managing rental properties

It has a property table that has details about the property such as bedrooms, sq feet, price, etc, it also has a column called address_id which is a foreign key to the address table which contains the address like street name, city, etc

Is there any benefit of having the address in another table? Or can I move the address columns to the property table?


texts: generate_series : how to remove or no generate the last one

texts:
I use this function for generating time slots. It's work well. But, there is a little problem.

My business opens at 10:00 and close at 12:00.

-- generate slots

CREATE OR REPLACE FUNCTION slots(start_date TIMESTAMP, end_date TIMESTAMP, delay INTERVAL) RETURNS SETOF TIMESTAMP AS $$
BEGIN
RETURN QUERY SELECT * FROM generate_series(start_date::timestamp, end_date, delay);
END
$$
LANGUAGE plpgsql;

-- call the function

SELECT slots('2019-12-19 10:00', '2019-12-19 12:00', '30 mins')

-- Results

2019-12-19 10:00:00
2019-12-19 10:30:00
2019-12-19 11:00:00
2019-12-19 11:30:00
2019-12-19 12:00:00

As you can see, the last generated slot is not available, because of my business close at 12:00. How can i remove or not generate this one?


texts: migrating DBs from SQL server 2005 to SQL server 2012

texts: We are planning on migrating from SQL server 2005 to SQL server 2012,the DB's are huge and cannot be backed up so we are doing a detach and attach method. I read online SQLServer 2005 to 2012 cannot be updated straight away. I think we need to upgrade to 2008r2 and then upgraded to 2012 not sure if it is the best practice . I got the users & permission scripted out . What are the best practices to follow to have a smooth upgrade without any issues.


texts: Giving server-role to login breaks stored procedures

texts:
I have a login called user1. user1 can execute stored procedure [Test].[SP1]

EXEC [Test].[TestSortation]
EXEC [TestSortation]

Both of the above commands work.

Now if I give the user1 the server role of sysadmin in addition to public. the later of the 2 statements above fails, and throws this error:

  Could not find stored procedure 'TestSortation'.

I don't want to have to change every stored procedure in the database to have the schema name included with EXECute commands manually. Can someone explain to my why this is happening. Also, is there a way to get around this, automatically fix my stored procedures, have the schema default to [Test]?


texts: Create foreign key on foreign table in postgresql

texts:
I need to link my table with another one which is in different database(say table logs in device db and table accounts in user db, both on same server). So using Foreign Data Wrapper I create a foreign table(I check it with running select * from accounts; in device db which works successfully).

But I could not create a foreign key to foreign table from device db still

CREATE TABLE public.logs
(
  id bigint NOT NULL DEFAULT nextval('logs'::regclass),
  ...
  account_id bigint,
  ...

  CONSTRAINT logs_account_id_fkey FOREIGN KEY (account_id)
      REFERENCES public.accounts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

)

The error message is

ERROR:  referenced relation "accounts" is not a table
********** Error **********

ERROR: referenced relation "accounts" is not a table
SQL state: 42809


texts: Download the replication snapshot file using FTPS

texts:
I have two databases for two companies. Company A's database contains domain data. The other company is pulling the data using snapshot replication. We have used FTP to communicate:

Created FTP server on IIS in Window Server 2014
Added the certificate to the Server
Created the replication publisher and given the FTP account information
It is working perfectly without the FTP server
IIS set the certificate and the required SSL connection now it is not working
This data is two company data and we want communication done using FTPS

It is not working, we don't want to use VPN. We got a link from MSDN
  and it is saying:

  If you use SSL to secure the connections between computers in a replication topology, specify a value of 1 or 2 for the -EncryptionLevel parameter of each replication agent (a value of 2 is recommended). A value of 1 specifies that encryption is used, but the agent does not verify that the SSL server certificate is signed by a trusted issuer; a value of 2 specifies that the certificate is verified. Agent parameters can be specified in agent profiles and on the command line.

So where can I set this EncryptionLevel=2?

This is the test cases to connect to the server:

We have changed the Server name during the login ftps://Domain.com
Change the port 990 and open the port still not worked

In short, I want to use FTPS for communication.

I can communicate over FTP. I am working on SQL Server 2014.