texts: Database migrated to SQL Server 2005 with 80 compatibiltiy from SQL Server 2000 experiencing locks

texts:
I am supporting an application whose code my company did not write, and was originally developed back in 2000-2002, by a company that no longer exists.  The code, while not great to maintain, thankfully has been working with only minor issues every few months, and almost never requires a code change (over the past several years).

The company that the application was written for just migrated their SQL Server 2000 database to SQL Server 2005, leaving the database in SQL Server 2000 (80 I believe) compatibility mode. Since this update several of the places in the code in multiple applications are now throwing SQL timeout errors (error code -2).

We can point the applications to the old database however with the same queries/same stored procedures, and the queries run with no problem.  However when we point them to the new SQL Server 2005 instance, they fail.

Checking the database to see the status of the users (sp_who2), we see that there are locks happening.

I am reluctant to make any changes to the code, and am wondering if there should even be changes made to the DB, since this code has been in production for over a decade. I am not a SQL guru, and don't even quite know what to look for. I am wondering if there isn't some property that governs how the queries run that had been changed by the original company, that wasn't documented and changed when the migration happened. Anyone have any ideas of what could cause multiple applications pointed to the same DB to begin having timeout issues after migration? This is happening not just with stored procedures, but even on a .NET DataSet Fill operation.


texts:
I ran a few commands to see whether MySQL is caching queries/result sets. It looks like this is not the case.

mysql> show variables like 'query_cache_size';
--------------
show variables like 'query_cache_size'
--------------

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.01 sec)

mysql> show status like 'Qcache%';
--------------
show status like 'Qcache%'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

At the same time, I can run the same query at one given time, and it will take 42 seconds, and at another given time, and it will take 1.10 seconds.

If the MySQL server is not doing the caching, what is? Is it the mysql client? Is it my operating system (OS X)? Is it some service on the server that's not MySQL? Are all of these possibilities, or only one?


texts: How do you find SQL Server version/edition without SSMS installed?

texts:
How do find out which edition is installed without having the management studio installed?  I have a server that functions as a license manager for another software.  Upon investigation of a high RAM usage alert, I found that the sqlservr.exe process is taking up almost 2 GB of RAM.

I looked through the program menu and found that the configuration manager was installed, otherwise, it is pretty bare bones.  I clicked on properties of the EXE file and found 10.50.1600.1, but there is no place that I've found that states whether it is Express, Dev, STN, ENT, etc.

If I had to guess, this is an express edition, but I wanted to know if there is an obvious tell-tale sign.

Update:
@Bob - The file tells me what I know, not the edition.

@valo - I get the following error when I run that command and I did verify named pipes was enabled:

  HResult 0x35, Level 16, State 1
  Named Pipes Provider: Could not open a connection to SQL Server [53].
  Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance->specific error has occurred while establishing a connection to SQL Server. Server is not >found or not accessible. Check if instance name is correct and if SQL Server is configured >to allow remote connections. For more information see SQL Server Books Online..
  Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

@thomas - I noticed the Stock Keeping Unit Name before I asked the question, but that seemed too easy, I guess my initial suspicion was correct.


texts: Backup Functions in PgAdmin 4 Version 1.6

texts:
How to backup all functions in pgadmin 4 version 1.6?
After executing the below query the backup functions stored in which place?
And how to find it?
How to know all information is backed up with what's being done?

SELECT pg_get_functiondef(f.oid) || E';\n'
FROM pg_proc f
   JOIN pg_namespace n ON f.pronamespace = n.oid
WHERE n.nspname = 'schemaname';


texts: Backupset server name on dev server shows Prod server name

texts:
I'm using the following code to determine the last time a database was backed up:

SELECT
@@ServerName as HostServerName
   ,bus.server_name AS BUSservername
   ,sdb.Name AS DatabaseName
   ,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101), '-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus
ON bus.database_name = sdb.name
WHERE
   bus.backup_start_date >= DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0) -- Previous day at midnight.
   AND bus.backup_start_date < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) -- Today at midnight.
GROUP BY
   sdb.Name
   ,bus.server_name

And it appears to show the data I'm looking for, but if I run this code on a development server, for example, it shows its corresponding production server as the bus.server_name value.
I'm trying to understand why it's showing a production server' name and not itself.  I may not be understanding how the backupset table is getting its value.
I know I can use @@ServerName for that value but I'm not sure if that is correct way to get that value.
Below is a screenshot of my results with the server names obviously blacked out:


texts: How do I allow users to add members to a specific database role?

texts:
I want to GRANT permissions to a new database role so that defined users can add and remove members from that role WITHOUT granting ALTER ANY ROLE permissions.

Here's how my query runs/dies:

CREATE ROLE [NewDBRole]
GO

The command(s) completed successfully.

GRANT ALTER ON [NewDBRole] TO [domain\username] WITH GRANT OPTION AS DBO
GO

This statement returns this error:

  Server: Msg 15151, Level 16, State 1, Line 1
  Cannot find the object 'NewDBRole', because it does not exist or you do not have permission.

But clearly the Role exists and I do have permissions, because I am able to DROP it:

DROP ROLE [NewDBRole]
GO

This statement works fine and returns:

  The command(s) completed successfully.

Is there special syntax needed for this GRANT, and if so, what syntax will be required to REVOKE it in the future?


texts: Duplicating database on the same server without data loss

texts:
I want to duplicate an innodb database on my mysql server for testing another version of the software.
Using tools for example phpmyadmin reduce the database around 20 MB and a "lot" of rows are missing.

Just copying the folder under /var/lib/mysql won't work, because I didn't enabled something special for the innodb engine.

How can I create a duplicate of an innodb database?


texts: How to specify the exact .ndf file on a filegroup to save a table in?

texts:
I have a filegroup(not the primary one) which contains two different .ndf files, for example a1.ndf and a2.ndf.

Now I want to create a table and I want my table to be saved in a2.ndf file not a1. Is there a way to do things like this? I am using SQL Server.

Thanks.


texts: Why does 'sysfiles1' show up in the sys.objects catalog view in SQL Server 2008?

texts:
We were recently using this query to find tables in our database that didn't have clustered indexes and found that one of the results it reported back was the 'sys.sysfiles1' table. We're running SQL Server 2008 and I was under the impression that this table wasn't used anymore (as some answers have pointed out there seems to be a misconception that this table only exists for databases upgraded from SQL Server 2000). Furthermore, I'm not able to directly select anything out of the sys.sysfiles1 table (though I am able to select directly out of the sys.sysfiles view).

Running the following to create a fresh database against a local installation of SQL Server 2008 (@@version = Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64), though we've seen it on our production instance of SQL Server 2008 as well) illustrates what I mean:

CREATE DATABASE SysFilesTesting
--returns a row:
SELECT * FROM SysFilesTesting.sys.objects where name = 'sysfiles1'
--throws 'invalid object name' error:
SELECT * FROM SysFilesTesting.sys.sysfiles1

Why is sys.objects reporting the existence of a sysfiles1 table?


texts: how to execute sql inside a column in postgres

texts:
I am trying to execute SQL statements that exist within a column of a database table.

The records of this table has 'tests' in each row.

sid  name     sql
1    test1    SELECT source_system_name, Count(*), CASE WHEN Count(*) BETWEEN 500 AND 2000 THEN true ELSE false END AS valid FROM prod.listing WHERE source_record_status = 'active' AND modified_on BETWEEN %L AND(%L - interval '24 hours') GROUP BY source_system_name;
2    test2    SELECT source_system_name, ....;

the output from the SQL inside sql column generates 3 columns - source_system_name, count, and valid.
Whenever function validation() is run I want to output the result - when valid = false

I wrote code for the innermost logic i.e. whenever there is false in value column.

CREATE OR REPLACE FUNCTION validation()
   RETURNS text AS $$
DECLARE
 show_log TEXT DEFAULT '';
 rec_val RECORD;
 cur_val CURSOR
  FOR SELECT   source_system_name,
         Count(*),
         CASE
                  WHEN Count(*) BETWEEN 500 AND 2000 THEN CAST(true AS BOOLEAN)
                  ELSE CAST(false AS BOOLEAN)
         END AS valid
      FROM     prod.listing
      WHERE    source_record_status = 'active'
      AND      modified_on BETWEEN '2001-06-29'::TIMESTAMP AND     (
                        '2017-06-29'::TIMESTAMP - interval '24 hours')
      GROUP BY source_system_name;
BEGIN
   -- Open the cursor
   OPEN cur_val;

   LOOP
    -- fetch row into the rec_val
      FETCH cur_val INTO rec_val;
    -- exit when no more row to fetch
      EXIT WHEN NOT FOUND;

    -- build the output
      IF rec_val.valid =  false THEN
         show_log := rec_val.source_system_name;
      END IF;
   END LOOP;

   -- Close the cursor
   CLOSE cur_val;

   RETURN show_log;
END; $$

LANGUAGE plpgsql;

I am a bit confused as in how to use this logic for all the statements in sql column.


texts: SSIS : Null value for date keeps failing

texts:
I have a sequence container and in that sequence container I have select that is passing through data into a foreach loop container. The problem arises with one of the columns that's a date datatype. When there is a date in that column it works fine, but when it's a null it fails. Is there anyway around this issue with null values?

This is the column

This is the error message below :

Below is the sql statement where I change the date from null to a date
SELECT
    cast(ISNULL([Submitted_Date],'01/01/1900') as datetime) as [Submitted_Date]
    FROM [dbo].[Application_Tbl]
WHERE
Application_Form_Id IN (12345)
processed = 0


texts: Error 13141 in mongos shell while trying to write to a result of the mapReduce invocation to a sharded collection

texts:
In our db init script, we have the following lines:

var conn = new Mongo();
var admin = conn.getDB("admin");
var db = conn.getDB("foodb");
admin.runCommand({ "enableSharding": "foodb" });
// ...
db.runCommand({ "create": "foo_stats" });
admin.runCommand({ "shardCollection": "foodb.foo_stats", key: { "_id": "hashed" } });

Then, when I try to perform a mapReduce() on another collection and write to foo_stats, the following happens (this is a simplified example which we actually tried after the "real" one failed):

mongos> db.foo_data.mapReduce(function(){ emit(this.foo_id, null)}, function(key, values) { return {};}, {"out": {"replace": "foo_stats","sharded": true}});
2016-05-31T06:50:07.061-0700 E QUERY    Error: map reduce failed:{
 "code" : 13141,
 "ok" : 0,
 "errmsg" : "exception: Chunk map pointed to incorrect chunk"
}
    at Error (<anonymous>)
    at DBCollection.mapReduce (src/mongo/shell/collection.js:1353:15)
    at (shell):1:16 at src/mongo/shell/collection.js:1353

What can be the cause of this error? In the documents from the foo_data collection, foo_id is a string that should theoretically match the regex: ^[A-Za-z0-9\-_]{16,48}$


texts: Restoring an entire SQL Server database instance

texts:
I have a server that we will be completely reformatting then rebuilding.  Everything will be rebuilt exactly as it is now - same filenames, same directories, etc.

After SQL Server is installed, I could reattach each of the databases on the server instance one by one from the full backups I've taken.

However, will the following also work to make the job faster?

Shut down the SQL Server instance
Copy all databases, including system databases, to another disk
Reformat the server and reinstall SQL Server
Shut down the SQL Server instance
Copy all files back to their original locations, overwriting existing files (would I also need the hidden Resource database?)
Start the SQL Server instance again

As long as everything is in the correct location, shouldn't the master database fire right up without issue, and the full server will be restored to the same way it looked pre-reformat?  I'm just wondering if this could save me some time.


texts: Display all days of the month with/out values

texts:
I am trying to generate a display of time_in and time_out of employees. One pair in the morning and another pair in the afternoon. The query below only shows the dates that has values in it.

QUERY

SELECT empID, emp_day,
MAX(am_in1) as am_in,
MAX(am_out1) as am_out,
MAX(pm_in1) as pm_in,
MAX(pm_out1) as pm_out
FROM (
  SELECT empID, DATE_FORMAT(`time_stamp`,'%e') as emp_day,
  CASE WHEN am_pm='am' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as am_in1,
  CASE WHEN am_pm='am' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as am_out1,
  CASE WHEN am_pm='pm' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as pm_in1,
  CASE WHEN am_pm='pm' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as pm_out1
  FROM attendance
) as o
GROUP BY empID, emp_day

WORKING OUTPUT

empID   |  emp_day    |    am_in    |    am_out    |    pm_in    |    pm_out
152     |  1          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
152     |  3          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00

How can I join the query below...

QUERY THAT GENERATES DAYS OF THE CURRENT MONTH

SELECT date_field
FROM
(
    SELECT MAKEDATE(YEAR(NOW()),1) +
    INTERVAL (MONTH(NOW())-1) MONTH +
    INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum FROM
        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
        UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
    ) as AA
) as AB WHERE MONTH(date_field) = MONTH(NOW())

...to come up with the following output?

empID   |  emp_day    |    am_in    |    am_out    |    pm_in    |    pm_out
152     |  1          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
152     |  2          |             |              |             |
152     |  3          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
152     |  4          |             |              |             |

...until the end of the current month

152     |  31         |             |              |             |

Below are the tables and and sample values I am working with.

TABLE DEFINITION AND VALUES

CREATE TABLE IF NOT EXISTS `attendance` (
  `empID` int(11) NOT NULL,
  `time_stamp` datetime NOT NULL,
  `in_out` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `am_pm` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL
);

INSERT INTO `attendance` (`empID`, `time_stamp`, `in_out`, `am_pm`) VALUES
(152, '2017-01-01 12:00:00', 'out', 'am'),
(152, '2017-01-01 08:00:00', 'in', 'am'),
(152, '2017-01-03 12:00:00', 'out', 'am'),
(152, '2017-01-03 08:00:00', 'in', 'am'),
(152, '2017-01-01 05:00:00', 'out', 'pm'),
(152, '2017-01-01 01:00:00', 'in', 'pm'),
(152, '2017-01-03 05:00:00', 'out', 'pm'),
(152, '2017-01-03 01:00:00', 'in', 'pm');

Working Code: http://rextester.com/SVC13416


texts:
We are trying to evaluate the potential performance improvements that could be gained by switching an existing applications .mdf files to a RAID10 LUN vs. the existing RAID5 LUN they are living on right now. The transaction log already lives on a RAID10 LUN.

So the question is, if we move data files to RAID10 would the end user see performance improvements in the GUI? or would it be just faster writes to the data files when a checkpoint occurs and the changes get written to the data file but no improvement would be seen at the end user level?


texts: query slows down exponentially as table grows

texts:
I have the following query running on mysql 5.6 on my CENTOS server

select tb1.field2, tb1.field1, tb2.field1
from dns.table1 tb1, dns.table2 tb2
where tb1.field2 = tb2.field2
  and tb1.field1 != tb2.field1

All relevant columns are indexed.  On tables of 10 million rows, it finishes within 400 seconds, however, on 100 million rows, it finishes within 10 hours!

table schema:

CREATE TABLE `my_table` (
  `table_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `field2` varchar(255) NOT NULL,
  `field1` varchar(255) DEFAULT NULL,
  `field3` longtext,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`table_id`),
  UNIQUE KEY `field2` (`field2`),
  KEY `field1` (`field1`)
) ENGINE=InnoDB
  AUTO_INCREMENT=1
  DEFAULT CHARSET=utf8
  ROW_FORMAT=COMPRESSED
  KEY_BLOCK_SIZE=8 |

Both innodb tables are of equal sizes.

Why? I suspect file swapping within mysql, what can be optimized on mysql? Would increasing the buffer pool help?


texts: Repair with data loss did not fix index corruption. What now?

texts:
A few months ago we migrated our SQL Server 2014 database to a virtual environment.

Soon afterwards, corruption started showing up in indexes, causing all sorts of trouble.

I've made repairs using DBCC CHECKDB (repair_allow_data_loss) a few times, and while it improves the situation, it never fully gets rid of the corruption within certain indexes.

Some CHECKDB results:

Msg 8977, Level 16, State 1, Line 1
Table error: Object ID 452196661, index ID 1, partition ID 72063073671905280, alloc unit ID 72063073860190208 (type In-row data). Parent node for page (3:266287) was not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 452196661, index ID 1, partition ID 72063073671905280, alloc unit ID 72063073860190208 (type In-row data). Index node page (1:68133), slot 60 refers to child page (3:299616) and previous child (3:264884), but they were not encountered.
Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 452196661, index ID 1, partition ID 72063073671905280, alloc unit ID 72063073860190208 (type In-row data). B-tree chain linkage mismatch. (1:62351)->next = (3:429015), but (3:429015)->Prev = (1:285490).
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 452196661, index ID 1, partition ID 72063073671905280, alloc unit ID 72063073860190208 (type In-row data). Page (3:468549) is missing a reference from previous page (3:261242). Possible chain linkage problem.

Does anyone have any ideas on how I can get my database back to a perfectly clean state? There is no clean backup from which to restore since this has been going on for a while.

I am going to attempt a new VM to see if that will fix the problem. I haven't been able to identify any I/O subsystem flaws.


texts: Databse ER Design for Apartment Complex and Automaker Employees

texts:
I recently completed an assignment which required me to complete an ER diagram for an apartment complex housing automaker employees. There are multiple automaker companies, and each of these companies have employees. There is a series of apartment complexes owned by the same person that houses these employees. If an employee stays in an apartment complex for over 7 days, a discount is applied that is unique to the company.

Some of the requirements are as follows:

Keep track of the new Employees/Trainees and their permanent (Home) addresses and contact information who stay at each of the Alpha Apartment Buildings during a given training session.
Keep track of the new Employee's/Trainee's current address and contact information. (What apartment are they residing in?)
Keep track of the length of stay for an Employee/Trainee in a specific apartment. This should be based on a Check In and Check Out date.
Keep track of all the individual apartments a given Employee/Trainee has ever stayed in. Note: This is historical data. An Employee/Trainee that stays at the one of the Boarding Houses, may get the same apartment or a different one each time that they come to Detroit for training.
Keep track of the different apartments that are in each apartment building, along with each apartment   number and floor
Keep track of the different nightly charges for each of the different apartment types.
Keep track of the name, location, address and contact information of each of the apartment buildings
Keep track of the address and contact information of the Big Three Automakers
Keep track of which Employee/Trainee works at which auto company for billing purposes.
Keep track of which apartments at each building are available for rental.

My ER-diagram:

As someone new (4 months) to the logical design, I am wondering if there was anything that could be improved on or changed regarding this ER diagram. I have a firm grasp on the concept of relationships, but I get confused when it comes to invoices, order lines, etc.

I also want to make it clear that this assignment has already been turned in and cannot be re-submitted. This is strictly a question out of the desire to learn.


texts:
I'm doing a direct-path insert which executes successfully. When I try to autotrace the execution, I get the ORA-12838: cannot read/modify an object after modifying it in parallel.

SQL> insert into emp_big select * from emp_big_temp;

411843 rows created.

Elapsed: 00:00:00.89

Execution Plan
----------------------------------------------------------
Plan hash value: 3203427748

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |   357K|    45M|  1164   (1)| 00:00:14 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP_BIG      |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | EMP_BIG_TEMP |   357K|    45M|  1164   (1)| 00:00:14 |
-----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         15  recursive calls
      31909  db block gets
      12829  consistent gets
          0  physical reads
   34685216  redo size
        845  bytes sent via SQL*Net to client
        802  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     411843  rows processed

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.18
SQL>
SQL> insert /*+ append */ into emp_big select * from emp_big_temp;

411843 rows created.

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          0  recursive calls
       4438  db block gets
       4283  consistent gets
          0  physical reads
       7648  redo size
        829  bytes sent via SQL*Net to client
        816  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     411843  rows processed

Questions:

Table emp_big has degree of parallelism set to 1, according to select table_name, degree from user_tables. Why does Oracle force parallel insert?
Why does using autotrace trace cause ORA-12838?


texts: Client application disconnects from database/s it is connected to after 10 minutes of inactivity

texts:
When working with pgAdmin 4 (and actually, several other programs that act as clients to the database), the connection to the server disconnects after some 10 or 15 minutes of inactivity. One coffee and one phone call, and you get a message similar to "Sorry, connection to database is lost. Do you want me to try to reconnect?"

And pgAdmin reconnects always need more than one trial, and the object-tree that was already expanded gets collapsed... So, it's a little bit annoying.

pgAdmin doesn't seem to have any parameter related to that behaviour. (There seems to be ways to change some connection timeouts, but they're related to how pgAdmin behaves with regard to the server taking too long to connect).

What can be done it to avoid pgAdmin from disconnecting from the database?

Disclosure: This is actually a "pseudo-question". It is spin-off of another one, that ended up not being related to loss of connectivity... Given the fact I already had one answer in place, I (not very humbly) think it is worth "asking", in case the answer is helpful to someone.


texts: PostgreSQL: Why is CREATE TABLE AS faster than CREATE ... INSERT INTO?

texts:
Below are the two different syntaxs for the same thing.

With a COPY TABLE AS SELECT (CTAS).

CREATE TABLE main
AS
  SELECT *
  FROM other;

As separate statements with CREATE TABLE and INSERT INTO

CREATE TABLE main (like other);

INSERT INTO main
SELECT *
FROM other;

I have observed that the CTAS is faster than distinct CREATE TABLE .. INSERT. The first takes 20 seconds to complete execution. The second  syntax two takes 1 min 15 secs to complete execution.

What could be the reason for difference?


texts:
My MySQL Query Browser doesn't run. I just started to work with XAMPP and PHP.
I just installed XAMPP on my local machine. Now I have a problem when opening MySQL Query Browser. What I'm getting while trying to open it is:

Could not connect to host 'localhost'. MySQL Error Nr. 2002 Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Click the Ping button to see if there is a networking problem.

Can anyone help me to fix the above problem?


texts:
This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I make sure it also has plpgsql extension. Then when restoring this happens:

pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2053; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

I'm creating a bunch of scripts and it's pretty important for me that pg_restore returns 0, so the fact that I can just ignore this isn't doing any good. What puzzles me is that IIRC I need to create extension as postgres master user, so I have no idea why all this EXTENSION stuff ends up in my dump. After all, I'm not owner of the language/extension?

Anyway, I'd be gratefull for any suggestion how to get rid of this. Please note, that I'm aware how the -l/-L switches work. This however seems to be way too much effort to correct just one simple extension comment.


texts: Does SQL Server ever delete old error log files?

texts: I know I can cycle the current error log easily by running sp_cycle_errorlog, but I'm wondering if SQL Server will ever delete the old/archived error log files at all. Can't seem to find an answer to this anywhere...


texts: Merging a .bak to an existing SQL database

texts:
I have a SQL Server database with data from 1990-2018 in a .mdf file.
I recently downloaded data for 2018-2020 and it's in a .bak file.
When I went to "Restore" the database, it wiped out all the data from 1990-2018 and just replaced it with the new one (2018-2020).
How do I merge an existing .mdf and a new .bak file? Or am I just thinking about this wrong?


texts: Postgresql: large table or union of small tables

texts:
I have a table which have PK column and json column.
I received new request for versioning of the Json (not in the meaning of history but meaning of the same data just in different json schema) and decided to save the Json in it's different versions.

From performance perspective, is it better to add new column to the table for each version, or create new table for each version and union the results when needed?


texts: Connection Timeout Expired without apparent network issue

texts:
We have one particular SQL Server which is intermittently timing out when accepting connections.  The issue is consistent throughout the day, but occurs at a very low incidence.  How can I continue to troubleshoot?

  Connection Timeout Expired.  The timeout period elapsed while
  attempting to consume the pre-login handshake acknowledgement.  This
  could be because the pre-login handshake failed or the server was
  unable to respond back in time.  The duration spent while attempting
  to connect to this server was - [Pre-Login] initialization=0;
  handshake=15002;  (Microsoft SQL Server, Error: -2)

Server Configuration:

SQL Server 2016 SP1 CU5 Enterprise (issue also occurred prior to SP1)
Windows Server 2012 R2 on both server and client
VMware ESXi, 6.5.0 on HP ProLiant DL360 Gen9
VM has 8 vCPU, 64 GiB of memory (fully reserved)

Test Script (executed once per second):

$failed = $false;
$loginDuration = (Measure-Command {
    $ncon = New-Object System.Data.SqlClient.SqlConnection `
        @( 'Data Source=1.2.3.4,16143;Database=Test;User=Test;Password=****;Pooling=false;' );
    try
    {
        $ncon.Open();

        $cmd = New-Object System.Data.SqlClient.SqlCommand `
            @( 'SELECT @@VERSION', $ncon );
        $cmd.ExecuteNonQuery();

        $ncon.Dispose();
    }
    catch
    {
        $failed = $true;
    }
}).TotalMilliseconds;
Write-Metric -metric 'itp.dbserver.logintime' -unit 'milliseconds' `
    -value (&{if ($failed) { 120000 } else { $loginDuration }});

Observations:

Issue started occurring after OS Updates, SQL Server Updates, San move, and move from Hyper-V to VMWare
Most connections succeed (4 failures out of 1,440 attempts)
Failures are always listed with a low number in "[Pre-Login] initialization=0;" and a high number in "handshake=15002".  We do not get errors like "Not found" or "No such host is known", only "Connection Timeout"
No encryption is enabled for the listener
Pings show no loss over extended period (0 lost out of 96,045 sent)
All firewalls are disabled
Connections attempted using IPv6 and IPv4 addresses fail at the same rate
CPU is low-ish (<40%)
Active sessions is persistently around 400
Balloon driver is disabled
Connections once made are stable, no unexpected errors when executing queries, no odd disconnections.
Multiple clients are having problems connecting - both ODBC and ADO from multiple computers

Update: I finally got a client-side Wireshark trace of a failed connection.  No packet loss is apparent, client receives TCP ACK's in real-time (<10ms).  Client was using DNS name at time of failure, but failure does occur using the IPv4 address in the connection string.

Am I correct in thinking that the fact that I get immediate TCP ACK's to the pre-login request packets sent would localize the issue to the OS or SQL Server?


texts: What do I call a relational schema without the constraints?

texts:
It is common in db theory to define a ‘relation schema’ as consisting of a relation name, the (names of the) attributes, as well as constraints on the relation.  A ‘database schema’ is then a set of such relation schemas.
An instance of the database schema consists of a finite set of tuples for each of the relations, which satisfies all of the constraints.
Now what I need is the notion of a relation schema, and sets of such without the constraints.  I want to add the constraints separately, and then be able to talk about how some instance of the (constraint-less) schema satisfies the constraints or not.  E.g. define ‘legal instances’ as instances that satisfy the constraints.
Is it defined like that somewhere in the literature?  What are the common names for a db schema sans constraints?


texts: How to union tables into a new table in mysql?

texts:
I have a table as following:

id col1 col2 col3 type
1  12   12   23   1
2  23   33   1    0
3  333  2    21   1
.....

I try to select 1% sample of where type=1 and all the data where type=0, I wrote the code as following:

 create table tbl2(
    SELECT *
    FROM tbl1
    WHERE type=1 AND RAND() <= 0.01
    UNION
    SELECT *
    FROM tbl1
    WHERE type =0
    );

It ends up as an error :

ERROR 1064 (42000): 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 'UNION
SELECT *
FROM tbl1
WHERE type =0
)' at line 3

Anyone can help me?


texts: Merge rows with common field value in MySQL

texts:
I have a table which was imported from MS-SQL, it has about 1.9 million rows. After importing I realized that a lot of the rows are duplicates and there are no primary key constraints.

The rules to check for constraints are:

Check the email address.
If no duplicate is found in the table, leave the row as it is.
If email address matches then compare the columns in both.
Select one of the two as the row that will be kept i.e. TO_KEEP, the other will be deleted i.e. TO_DELETE.
If there is any column in TO_DELETE that has some value but there is no value in TO_KEEP the update the TO_KEEP row with the value from the matching column in TO_DELETE.

I need help with writing a query for that. It's beyond my current skills. I wouldn't mind if I get some pointers which can make it easier as to how the query can be structured and which ones to use.


texts: Syntax changes between DB2 for i (DB2/400) v5r4 and v6r1

texts:
Does anyone know where can I find list of syntax changes between DB2 for i v5r4 and v6r1?

We are currently migrating a backing database for an application from DB2 for i v5r4 to v6r1 and found out that some queries no longer work on the new version. However we cannot seem to find any official reference regarding this in ibm sites (or anywhere else for that matter).


texts: Is there better way to 'touch' a row than performing a dummy update?

texts:
I use the xmin system column to implement a form of optimistic locking, and sometimes need to 'touch' the row to bump xmin without actually updating the row. I currently just do a 'dummy' update:

create table t(id integer);
insert into t(id) values(1);
insert into t(id) values(2);

select xmin::text from t where id=1
/*
|  XMIN |
---------
| 87159 |
*/

update t set id=id where id=1

select xmin::text from t where id=1
/*
|  XMIN |
---------
| 87196 |
*/

(SQL Fiddle)

I'm curious whether there is another way of bumping the xid without doing an update, analogous to the unix touch command?


texts:
i have a problem creating cumulative function in mysql database.

let say i have a stored procedure which getting data and counting quantity/sum_quantity

example
A table consist of consumer goods which has attributes {name, brand, quantity}

Row 1 = {cookies, brand z, 100}.

Row 2 = {tissue, brand a, 200}.

Row 3 = {toothpaste, brand v, 100}.

which has sum_quantity = 400;

then i create stored procedure to divide quantity/sum_quantity and i get the result

Row 1 = {cookies, brand z, 100, 0.25} ; 0.25 is result from division method (quantity/sum_quantity)

Row 2 = {tissue, brand a, 200, 0.75} ; 0.75 is result from division method in First Row then   Added to division method in second row. and so on.

[the real case]

i have done the stored procedure to divide quantity/sum_quantity using the code below:

 DELIMITER $$

 USE `sre`$$

 DROP PROCEDURE IF EXISTS `sp_checkPotentialProduct`$$

 CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_checkPotentialProduct`()
   BEGIN
DECLARE sumtotal INT DEFAULT 0;
DECLARE first_margin DOUBLE;
DECLARE count_rows INT DEFAULT 0;
DECLARE startnum INT DEFAULT 0;

/* assign values to variables */
SELECT SUM(sum_product) INTO sumtotal FROM `vShowTopSellingQuantity12`;
SELECT COUNT(sum_product) INTO count_rows FROM `vshowtopsellingquantity12`;
SELECT (sum_product/sumtotal)*100 INTO first_margin FROM `vshowtopsellingquantity12`  LIMIT 1;
    /*creating algorithm to analyze the percentage quantity/total */

SELECT `idms_kodebarang`, `nama`, sum_product , (sum_product/sumtotal)*100 AS '%', "THE CUMULATIVE FUNCTION GOES HERE" AS 'startnum' FROM `vShowTopSellingQuantity12`;
  END$$

DELIMITER ;

TABLE SOURCE

i want to cumulative the (sum_product/sumtotal) inserted into the column "THE CUMULATIVE FUNCTION GOES HERE" so, the first row will counting like my excel below

any ideas to do this?

thanks.


texts:
IS it possible that a not nullable column can be setup to either
a) add value freely with INSERT/UPDATE query
b) if value not provided on insert, to avoid null entry added default the value to that of another column?
I know that with a computed column I can set the default to be another columns value, but I cannot then override this.


texts: How to copy the postgresql's xlog files safely

texts: I mean how to safely copy xlog files when postgresql is running? Any reply will be appreciated.


texts:
As per the title: In SQL server is a check constraint applied before or after a default constraint on insert?

So taking the following table (Unnecessary columns omitted for brevity)...

CREATE TABLE [dbo].[Customer](
    [CountryCode] [varchar](5) NULL CONSTRAINT [CK_Customer_DefaultCountryCode]  DEFAULT ([app].[GetUnknownCountryIsoCountryCode]()),
) ON [PRIMARY]

...Which also has the following check constraint...

ALTER TABLE [admin].[Customer]  WITH CHECK
    ADD  CONSTRAINT [CK_CustomerHasCountryWhenIntercompanyBusinessModel]
    CHECK  (
        ([app].[BusinessModelMustHaveCountry]([BusinessModelId]) = (1) AND [app].[CheckCountryCodeExists]([CountryCode]) = (1) OR
        [app].[BusinessModelMustHaveCountry]([BusinessModelId]) <> (1))
    )
GO

... will the default constraint be applied and populate the default value before the check constraint is applied?


texts: Restarting hot_standby replication when the *slave* fails

texts:
Context: master-slave replication on Postgres 9.4, slave died a couple of weeks ago:

the slave copied a WAL-File from the master,
told the master that the file is copied,
the master deleted the wal-file,
the slave tried to play the corrupt wal-file,
found that the file is corrupt,
and gives up to start up.

In the meantime the master have accumulated more logs than the database itself.

How can I tell master that the replication is over (to reconfigure the replication)? Any general comments how to proceed with this situation?


texts: Is there a way to tell your hostname within a stored procedure?

texts:
Is there a way to determine the hostname of the server you're running in w/ in a stored procedure?

You can run system hostname from a client but you can't make system calls from SP.  I don't see it any of the global variables or statuses.


texts: Validate the name of objects in the creation of stored procedures in SQL SERVER

texts:
When I execute the command below no SQL SERVER 2017:

Create or Alter Procedure stp_Teste01
as
select name, type_desc  from sys.objectsx
go

SQL SERVER does not validate if the SYS.OBJECTSX object exists. Only when I run the procedure that SQL Server presents the error message.

EXECUTE STP_TESTE01
GO
Msg 208, Level 16, State 1, Procedure stp_Teste01, Line 3 [Batch Start Line 11]
Invalid object name 'sys.objectsX'.

Why does it not validate at the creation stage of the stored procedure?

If you place the name of the TYPE_DESCX column, SQL Server displays the error message.

Create or Alter Procedure stp_Teste01
as
select name, type_descX  from sys.objects
go

Msg 207, Level 16, State 1, Procedure stp_Teste01, Line 3 [Batch Start Line 5]
Invalid column name 'type_descX'.

Is it not in the ALGEBRIZER phase that SQL Server validates the existence of objects in the database and the creation of the procedure does not execute the PARSE and ALGEBRIZER phase?


texts: What is the differrence between mysql.innodb_table_stats and information_schema.innodb_tablestats?

texts:
I am using mysql-8.0.19. I want to find a way to detect changes in database tables. E.g. compare the value of a query to the value of the last query.
I see that there exist two tables from which I could obtain table change information:
informatin_schema.innodb_tablestats with columns:
TABLE_ID
NAME
STATS_INITIALIZED
NUM_ROWS
CLUST_INDEX_SIZE
OTHER_INDEX_SIZE
MODIFIED_COUNTER
AUTOINC
REF_COUNT

mysql.innodb_table_stats with columns:
database_name
table_name
last_update
n_rows
clustered_index_size
sum_of_other_index_sizes

My questions are:

Why there exist two tables with similar information?
Which table should I use to obtain update information?


texts:
Not sure if this question is appropriate here but I believe that it is of some interest for the db2:ers of the site.

Once upon a time the DB2 Cookbook by Graeme Birchall could be downloaded from:

http://mysite.verizon.net/Graeme_Birchall

The account seems to be closed, does anyone know if these are hosted elsewhere by the author or by anyone else? I believe that the pdf's where a great contribution for the DB2 community so it is a pity to see them gone.

For 9.7 they can be downloaded from: db2hub.com/freebies Does the cookbook exist for later versions?


texts: Failing to connect to Integration Service with SSMS on one VDI but on another it works fine

texts:
I have two VDIs with the exact same version of SSMS installed. On one, I can log into the standalone Integration Service of a remote server running SQL Server 2017, on the other I cannot.

Every single other online result is not helpful, the usual culprit is not having the right version of SSMS or permissions set up on the remote server but that is not the case here. I also can't just use SSISDB because I need to grab packages from the Integration service itself, and also I want to make sure this error doesn't mean there's general problems with the VDI's setup.
The ONLY difference between the two copies of SSMS are the version of the Microsoft Data Access component and the OS Version.
If Anyone's encountered this specific case where a working Integration Service that can be connected to without issues won't work on a new machine please share what was wrong. Apparently the Firewall settings on the new VDI are correct but I'm not so sure myself. I don't have access to change them however.


texts: Trigger BEFORE INSERT from date to datetime

texts:
I have this schema:

CREATE TABLE `prices` (
  `id_product` int(11) unsigned NOT NULL,
  `date` date NOT NULL, # Important: DATE
  `value` decimal(8,5) unsigned NOT NULL DEFAULT '0.00000'
  PRIMARY KEY (`id_product`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `prices_changes` (
  `id_product` int(11) unsigned NOT NULL,
  `date` datetime NOT NULL, # Important: DATETIME
  `value` decimal(8,5) unsigned NOT NULL DEFAULT '0.00000'
  KEY (`id_product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER ;;
CREATE TRIGGER `prices_before_insert`
  BEFORE INSERT ON `prices`
  FOR EACH ROW
    INSERT INTO `prices_changes` (`id_product`, `date`, `value`) VALUES (NEW.`id_product`, NEW.`date`, NEW.`value`);;
DELIMITER ;

INSERT INTO `prices` SET
  `id_product` = 1,
  `date` = "2016-08-11 13:50:00", # Is a DATETIME value
  `value` = "20.50"
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

mysql> SELECT * FROM `prices` WHERE id_product = 1;
+------------+------------+---------+
| id_product | date       | value   |
+------------+------------+---------+
| 1          | 2016-08-11 | 20.50   |
+------------+------------+---------+

mysql> SELECT * FROM `prices_changes` WHERE id_product = 1;
+------------+---------------------+---------+
| id_product | date                | value   |
+------------+---------------------+---------+
| 1          | 2016-08-11 00:00:00 | 20.50   |
+------------+---------------------+---------+

It should update prices table with last value without product/date duplicates and insert into prices_changes all updates using a trigger.

The problem is that in prices_changes the date column defined as datetime is storing the date value without the time.

How can I store into the prices_changes the data as datetime using the original value?


texts: How to differentiate between class and attribute

texts:
I have this problem:

Library has books, VDOs, CDs Journals. It gives loan to users. Books and Journals can be referential which cannot be loaned out more than 2hrs and non-referential which can be loaned out for 2 weeks. user have ID and name and address. Users can be staff and student. Staff can be FT or PT. Student can be FT and PT and Undergrad or Postgrad. Library has copies of every items. Books and Journals have one or more authors..Design a conceptual schema.

My Soln:

Main classes: Libraryitems, copy, loan, user, author, period-for loan time.

Generalisations: Libraryitem, user.

Specific classes: FullTime/Parttime, UG, PG for students and FT/PT for employee

Upto now am I correct in identifying the classes?


texts: Database Hangs while impdp "DATABASE_EXPORT/STATISTICS/MARKER" at this stage

texts:
I am using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

i am facing issue while impdp on other server. it hangs at the last line message below. it takes min. over 2-3 hours to continue. while importing i can not  find any error message on  alert log file or no any lock . then why it is taking too much time for importing ?

 -***************-
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER

here is my par file for impdp syntax :-

DIRECTORY=dir1
LOGFILE=abc_imp.LOG
DUMPFILE=abc.dmp
INCLUDE=SCHEMA:"IN('u1','u2','u3')"
include=role
content=METADATA_ONLY
TRANSFORM=SEGMENT_ATTRIBUTES:N

please let me know if any solution for this issue ?


texts:
I have run into an interesting dilemma working on optimizing a table in a Microsoft SQL database.

Table details:
The primary key is 3 parts (Date (date), Account (bigint), Field (nvarchar(4)).

The table itself is somewhat large, at 72 million records. Most of that is because of the daily load (there are around 250k records per day).

The table is primarily being joined on Date and Account, though it is sometimes filtered on Field.

I believe the clustered index on the primary key is the best way to go, but I'm not entirely sure. I know in general its better to have narrow CIs than wide CIs, and the table will only be joined on Date and Account.

Given this: is a narrower CI on Date and Account ever a good idea, or is CI on primary key optimal.

As an additional question: if the third part of the primary key was almost never filtered on, would that change the answer? In a scenario like this are there circumstances where narrow is better than unique.


texts: How to recover from Backup Pending SQLSTATE=57019 in DB2?

texts:
I updated the DB2 parameters in LOGARCHMETH1 to OFF.  It worked fine after bouncing the database.

When I tried to turn on again the LOGARCHMETH1 and specify a DISK for the log archive of the database, used the command:

update db cfg for "DES" using LOGARCHMETH1 DISK:G:\db2\DES\log_archive\

It prompts that the parameters submitted for immediate modification were not changed dynamically. Database must be shutdown and reactivated before the config parameter changes become effective. So I restart/bounce the database with:

db2stop
db2start

Tried to check the database config again if the changes was made, but now
I cannot anymore connect to the database and I was being prompt with error:

  SQL1116N A connection to or activation of database "DES" cannot be
  made because of BACKUP PENDING. SQLSTATE=57019

  SQL1116N A connection to or activation of database "DES" cannot be
  made because of BACKUP PENDING.

Any suggestions on how to activate back the database? Since now I cannot turn on the application for this one.


texts:
I am looking for small advice. My database has 12 log files and x_log11 is showing file type as Rows data instead of Log and file group as primary. I am using SQL 2014.
I need to change it from rows data to log as it is log file?
will there be any impact if I will keep like this.
If I am trying to shrink(for other purpose) log files x_log11 is showing in data files.
will this settings impact on file growth of log files?


texts: Machine login for Microsoft SQL Server for Linux

texts:
-- old question --
I'm building a multi-tenant SQL Server for Linux solution for hosting companies. Basically end-users will get access to SQL server with their own user account.
Now, I want to be able to backup their databases every day using the SQL Commandline tools. This is no issues, except for the fact that I don't know the password set for the user's account to login into SQL, AND I don't know the SA account password (as my clients choose this themselves).
Is there any way to log into SQL server as the 'machine' account (much like the local accounts that you can use on the Microsoft OS) to login so I can set up unattended backups through scripting without needing to know passwords for these users ?
I checked SQL Server Management Studio, connected to the Linux box and saw there is a user called BUILTIN\Administrators, but I have no idea how to login with this user to SQL, I tried logging in using root+password, but this doesn't work.

-- Altered question --
I'm building a plugin for a hosting panel (like plesk). This plugin allows hosting customers (end users) to use SQL Server for their hosting needs within their own environment. My real customer is the server owner (hosting company). THey need to

Install SQL server on linux

After that's been done I have the following tasks:

Whenever a new hosting account is created, I hook into this event and Create a new SQL user with a new database assigned to this user. Account creation is not a manual process, so this can run n times per day, unattended.
I will add said database to a script that performs backups automatically using cronjobs
The (hosting customer) will be able to create new "databases" from their hosting control panel on demand (self service). In practise we will validate the user account, and create a new database on their behalf (using a shell script with SQL commandline)

For all actions specified above I will need a sysadmin user to perform these tasks. I can't prompt the hosting company for the SA password every time I need to perform something (as the processes above are unattended), and it's bad practise to save/store the SA password somewhere in a text file.
How can I execute options 2-3-4 without storing the SA password? Should I create a secondary sysadmin account with a password that's generated by the machine?
I'm open-sourcing this plugin when I'm done, and even if we use generated passwords, people will be able to find them out (we need to store it, somewhere).
SQL Agent won't work (as I don't have the SA password), and some actions can't be run by SQL agent as they are 'on-demand' (point 2 and 4 above)
Any tips on my revised question?


texts: Single Server (Replica) Availability Group

texts:
First time caller, long time listener.

I have a situation where I need to stand up a single server Availability Group for around 2 months (i.e. move DBs to a new server, rebuild the current server, and add the rebuilt current server to the AG).  Both servers are physical and have roughly the same specs. Windows Server 2016, SQL Server 2014.

My thought was to stand up the AG with the listener now so that when the current server is rebuilt and added to the group, we wouldn't need to add the listener then and update all of our apps/clients (i.e. that would force two migrations, one to the new server, one for the new listener).

I've tested this situation on a few test servers and its working as expected.  I also can't foresee a reason not to do this, but I thought it would be worth the community ask since we're talking about Production DBs.  My question is has anyone tried this and had any issues?  Would anyone recommend against this solution. Thanks.


texts: Return document that has $max value in specific field using mongodb

texts:
I have a json file that has some football results. I need to calculate which team won the most away games, and how many wins there were.

My query is this:

db.football.aggregate(
{"$unwind": "$rounds"},
{"$unwind": "$rounds.matches"},

{"$project":
    {
        //"rounds.matches.team2.name":1,
        away_team: "$rounds.matches.team2.name",
        winner_away:
            { $cond: {if: {$gt:["$rounds.matches.score2","$rounds.matches.score1"] }, then:1,else :0} },
        _id:0
    }
},
{"$group": {
    _id: {team_name:"$away_team"},
    total_wins: {$sum:"$winner_away"}
    }
}
)

and what I get is this:

{ "_id" : { "team_name" : "Stoke City" }, "total_wins" : 6 }
{ "_id" : { "team_name" : "Newcastle United" }, "total_wins" : 2 }
{ "_id" : { "team_name" : "Chelsea" }, "total_wins" : 7 }
{ "_id" : { "team_name" : "Everton" }, "total_wins" : 5 }
{ "_id" : { "team_name" : "Bournemouth" }, "total_wins" : 6 }
{ "_id" : { "team_name" : "Manchester United" }, "total_wins" : 7 }
{ "_id" : { "team_name" : "Liverpool" }, "total_wins" : 8 }
{ "_id" : { "team_name" : "Manchester City" }, "total_wins" : 7 }
{ "_id" : { "team_name" : "Leicester City" }, "total_wins" : 11 }

If I add this to get just team's with more wins name, and number of wins

{"$group": {
        _id: "$team_name",
        max_wins: {$max: "$total_wins"}
        }
    }

I get this

{ "_id" : null, "max_wins" : 11 }

Can someone tell me how to get the name as well? I can do it using sort, limit but this will not work in case of two or more teams having equal number of wins.


texts: Is it possible to have a shared secondary Mongo DB instance between two replica sets..?

texts: I have two separate mongo db instances say A and B running in a remote location and i want to maintain a collective synchronized copy/backup of these instances in a separate mongo instance say C. So i just want to know is it possible to configure two separate replica sets for A and B instances having C as common secondary instance for both the sets..?


texts: Amazon RDS Slow Query Log Does Not Show SQL Statement

texts:
I enabled the slow query log on Amazon RDS and I executed a "select sleep(5)". But when I look at this table, how is it that sql_text does not show the actual SQL statement?
mysql> select * from mysql.slow_log\G
*************************** 1. row ***************************
    start_time: 2021-01-27 15:45:08.033183
     user_host: XX
    query_time: 00:00:05.002547
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db: test
last_insert_id: 0
     insert_id: 0
     server_id: 3213123123
      sql_text: 0x73656C65637420736C656570283529
     thread_id: 103213

Below are the settings:
log_output = TABLE
slow_query_log = 1
long_query_time = 1
log_slow_admin_statements = 0
log_queries_not_using_indexes = 0

What I am not sure is why I got this output:
sql_text: 0x73656C65637420736C656570283529 which I suspect is the select sleep(5) ?

Update:
I ran this again to make sure I didn't execute select HEX(sleep(5)), and I didn't do that. Also, the sql_text are all the same even though I ran different SQl statements.
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.09 sec)

mysql> select * from mysql.slow_log\G
*************************** 1. row ***************************
    start_time: 2021-01-27 15:45:08.033183
     user_host: XX
    query_time: 00:00:05.002547
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 1499
      sql_text: 0x73656C65637420736C656570283529
     thread_id: 107032
*************************** 2. row ***************************
    start_time: 2021-01-28 21:26:11.324022
     user_host: XX
    query_time: 00:00:02.000258
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 1499
      sql_text: 0x73656C65637420736C656570283229
     thread_id: 103121
*************************** 3. row ***************************
    start_time: 2021-01-28 21:26:46.160363
     user_host: XX
    query_time: 00:00:03.000205
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 1499
      sql_text: 0x73656C65637420736C656570283329
     thread_id: 110323
3 rows in set (0.10 sec)

Also, I tested with log_output = FILE and I see the correct SQL statements. See below.
# Time: 2021-01-28T21:18:10.623686Z
# User@Host: XX Id: 131232
# Query_time: 1.000215 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1611868690;
select sleep(1);


texts:
I wonder if there is an equally elegant solution for Oracle for the problem posted and answered here:

Comparing Columns that can contain NULLS - is there a more elegant way?

I can't get this solution (the one which was accepted) to work in Oracle environment.


texts: Does reporting webservice url contain the function that helps to export the report to the client version?

texts:
The reporting webservice url contains the webservice url that contains all the information of the Reports which are created in SSRS. As the report has been created on reporting service, i want to export/send that report to my client using the SOAP feature.

http://localhost/reportserver_INSTANCE2012/reportservice2005.asmx?wsdl

While creating the project under soapUI using the above wsdl, it returns 105 functions like

Function Name

CreateBatch()
CancelBatch()
CancelJob()
CreateDataDrivenSubscriptionMethod()
CreateDataSource
CreateFolder
CreateLinkedReport
createModelReport
createReport
createReportHistorySnapshot
createResource
CreateRole
CreateSchedule
CreateSubscription
DeleteItem
DeleteReportHistorySnapshot
DeleteRole
DeleteSchedule
DeleteSubscription
DisableDataSource
EnableDataSource
ExecuteBatch
FindItems
FireEvent
FlushCache
GenerateModel
GetCacheOptions
GetDataDrivenSubscriptionProperties
GetDataSourceContents
GetExecutionOptions
GetExtensionSettings
GetItemDataSourcePrompts
GetItemDataSources
GetItemType
GetModelDefinition
GetModelItemPermissions
GetModelItemPolicies
GetPermissions
GetPolicies
GetProperties
GetRenderResource
GetReportDefinition
GetReportHistoryLimit
GetReportHistoryOptions
GetReportLink
GetReportParameters
GetResourceContents
GetRoleProperties
GetRoleProperties
GetScheduleProperties
GetSubscriptionProperties
GetSystemPermissions
GetSystemPolicies
GetSystemProperties
GetUserModel
InheritModelItemParentSecurity
InheritParentSecurity
ListChildren
ListDependentItems
ListEvents
ListExtensions
ListJobs
ListModelDrillthroughReports
ListModelItemChildren
ListModelPerspectives
ListReportHistory
ListRoles
ListScheduledReports
ListSchedules
ListSecureMethods
ListSubscriptions
ListSubscriptionsUsingDataSource
ListTasks
Logoff
LogonUser
MoveItem
PauseSchedule
PrepareQuery
RegenerateModel
RemoveAllModelItemPolicies
ResumeSchedule
SetCacheOptions
SetDataDrivenSubscriptionProperties
SetDataSourceContents
SetExecutionOptions
SetItemDataSources
SetModelDefinition
SetModelDrillthroughReports
SetModelItemPolicies
SetPolicies
SetProperties
SetReportDefinition
SetReportHistoryLimit
SetReportHistoryOptions
SetReportLink
SetReportParameters
SetResourceContents
SetRoleProperties
SetScheduleProperties
SetSubscriptionProperties
SetSystemPolicies
SetSystemProperties
UpdateReportExecutionSnapshot
ValidateExtensionSettings

I would like some one to help me out which function is used to send the existing report of SSRS reporting to my client. I am using the wsdl feature to connect directly to the report server and want to use function listed on above, to send the report in any format like pdf etc.


texts:
When deploying MySQL multi-source replication, can the multi-source slave be a master for other 'normal' slaves?

I can't find this particular case covered in the documentation


texts: SQL Select query is taking so long time on new Server

texts:
I am migrating our live website server from rackspace to AWS. We have already done all parts but stuck at below point:

One of query which is running within a second on live server, it's taking around 5 mins to execute in aws server.

I have already checked about configuration of mysql and its same. We keep same configuration as live for mysql, Tried to import database thrice but same problem persist:

Here is mysql Versions of both server:
Rackspace - 5.5.61
AWS       - 5.7.29

Here is that query:

SELECT DISTINCT(listname),
               `tbcc_l_lists`.`id`,
               `tbcc_l_lists`.`reportcode`,
               `tbcc_l_lists`.`list_type`,
               `tbcc_l_lists`.`list_url`,
               `tbcc_l_lists`.`researchby`,
               `tbcc_l_lists`.`linkassigndate`,
               `tbcc_l_lists`.`startdate`,
               `tbcc_l_lists`.`completeddate`,
               `tbcc_l_lists`.`team`,
               `tbcc_l_lists`.`status`,
               `tbcc_l_lists`.`priority`,
               `tbcc_l_lists`.`list_url_path`,
               `tbcc_l_lists`.`comments`,
               `tbcc_l_lists`.`added_by`,
               `tbcc_l_categorygroup`.`name`,
               `tbcc_l_listtype`.`listtype_name`,
               (SELECT COUNT(tbcc_l_contacts.primary_list)
                FROM tbcc_l_contacts
                WHERE tbcc_l_lists.id=tbcc_l_contacts.primary_list )   AS `contactcount`,
               (SELECT COUNT(tbcc_l_contacts_rejects.primary_list)
                FROM tbcc_l_contacts_rejects
                WHERE tbcc_l_lists.id=tbcc_l_contacts_rejects.primary_list)   AS rejectcount,
               `tbcc_l_admins`.`username`,
               `tbcc_l_admins`.`fname`,
               `tbcc_l_admins`.`lname`,
               `admins`.`username` AS `team_username`,
               `admins`.`fname`   AS `team_fname`,
               `admins`.`lname` AS `team_lname`,
               `admins1`.`username` AS `addedby_username`,
               `admins1`.`fname` AS `addedby_fname`,
               `admins1`.`lname`   AS `addedby_lname`
FROM `tbcc_l_lists`
LEFT JOIN `tbcc_l_categorygroup` ON tbcc_l_lists.categorygroup = tbcc_l_categorygroup.id
LEFT JOIN `tbcc_l_listtype`   ON tbcc_l_lists.list_type = tbcc_l_listtype.listtype_id
LEFT JOIN `tbcc_l_contacts` ON tbcc_l_lists.id = tbcc_l_contacts.primary_list
LEFT JOIN `tbcc_l_contacts_rejects` ON tbcc_l_lists.id = tbcc_l_contacts_rejects.primary_list
LEFT JOIN `tbcc_l_admins`   ON tbcc_l_admins.id = tbcc_l_lists.researchby
LEFT JOIN `tbcc_l_admins` AS `admins` ON admins.id = tbcc_l_lists.team
LEFT JOIN `tbcc_l_admins` AS `admins1`   ON admins1.id = tbcc_l_lists.added_by
WHERE (1=1) LIMIT 200000;

I am working on this issue since last couple of days, Any help will be highly appreciable.


texts: SQL Server 2016: Run xp_cmdshell as a different sysadmin user aside from the SQL Server service account

texts:
I been trying to research this for hours and can't seem to find anything. Basically what I am trying to do is run a xp_cmdshell command as a currently signed in sysadmin user

To give some background, I have two accounts here

Domain\App_service
Domain\sql_service

Now SQL_Service and app_service are both in the sysadmin role. SQL_Service is what SQL Server is running as.

The problem I'm encountering is that when I run xp_cmdshell, it executes as the sql_service user. What I need to do is run this as the app_service as the command I am executing has the folder permissions locked down to app_service user

Here is an example of the code that I have tried to make it run as app_service

Execute as login='DOMAIN\app_service'
Exec xp_cmdshell 'whoami'

It keeps returning the sql_service user. From what I am researching, I believe that is intended by design. However, I am trying to run the cmd_shell as the domain\app_service user. This user is a local system admin and in the sysadmin server roles. And I'm running as this user in SQL Server Management Studio and the query is running as this user.

I keep seeing information around in regards to running non sysadmin users with xp_cmdshell. However, I'm not doing that. I'm simply just trying to run under a particular sysadmin user instead of xp_cmdshell running as the sql service username.


texts: Efficient query for an advanced EAV model

texts:
I'm kind of stuck on how to build an effecient query which returns the data of the following EAV structure.

Today, a product table already exists, containing 4 fixed fields. We want to upgrade the system, allowing an infinite amount of additional product fields, defined by the manufacturer of each product. We call these additional fields 'parameters'.

A parameter can be of the following data type:

Text
Date (range)
Boolean
Dropdownvalue (single)
Dropdownvalues (multiple)

Based on that, I created the following database model:

:

Product: the original product table, containing the fixed fields.
Parameter: this table defines all types of parameters. Currently 5: Text, Date, Boolean, Dropdown value (single), Dropdown value (multiple)
ManufacturerParameter: this table stores which parameters are defined by the product manufacturer (Manufacturer table not included in the picture) for all of his products.
ProductParameter: this is where the actual product data of all the defined parameters is being stored. A parameter of the type 'Text', stores its information in the field 'Text', 'Date' in 'DatumBegin' and 'DatumEnd', Bit/Boolean in 'Boolean'.
ParameterValueListItem: this table contains the pre-defined values of the Dropdownlist values for both single and muliple coupling. These are the values you get to see in the Dropdownlist when creating or altering a product.
ProductParameterValueListItem: this is where the actual product data is stored for the Dropdownlist values.

I need to build a webpage in ASP.NET C# which shows a list of all product data, including each defined parameter with it's value for that product. I need to be able to filter the list by searching on any field/parameter. This list also needs to be sortable on any field/parameter.

I tried to filter the list by outer joining Product - ProductParameter - ProductParametervalueListItem - ParameterValueListItem to obtain a table result containing all data, and then filtering that list by using a dynamic where clause, built and passed through by the web app. This works when you search on 1 parameter but when you start searching on multiple parameters, you get incorrect results because each parameter is a different line in the table result and there simply is no 'AND'-match between parameter A and B, because they don't exist in the same record (line).

Can anyone advice me how this can be achieved? The most perfect solution would be 1 table result, containing all data and each parameters being shown as a column in that same product row. Is this too complex to handle at database level?

Thanks for reading this far. Any advice is welcome.


texts: What characters are word breakers in English for SQL Server 2005 and 2008 R2?

texts:
I can find what DLL supports English word breakers by using sp_help_fulltext_system_components but I have not been able to find an actual list of the word breaking characters for English (like blank, ., %, etc.).

Anyone know of a source for this info?


texts: Trigram similarity (pg_trgm) with German umlauts

texts:
I try to figure out how to improve Postgres 10.6 pg_trgm queries with German umlauts (äöü). In german 'ö' can be written as 'oe'. But beware: not every 'oe' can be written as 'ö'.
CREATE TABLE public.names
  (name text COLLATE pg_catalog."default");

CREATE INDEX names_idx
    ON public.names USING gin (name COLLATE pg_catalog."default" gin_trgm_ops);

SHOW LC_COLLATE; -- de_DE.UTF-8

When I use the similarity()
function to query the similarity for 'Schoenstraße'.
SELECT
    name,
    similarity (name, 'Schoenstraße') AS similarity,
    show_trgm (name)
FROM
    names
WHERE
    name % 'Schoenstraße'
ORDER BY
    similarity DESC;

I get the following result:
Name            similarity  show_trgm

Schyrenstraße   0.588235    {0x9a07c3,0xde3801,""  s"","" sc"",chy,ens,hyr,nst,ren,sch,str,tra,0x76a40e,yre}
Schönstraße     0.5625      {0x9a07c3,0xde3801,0xf00320,0x095f29,""  s"","" sc"",0x6deea5,nst,sch,str,tra,0x76a40e}

db<>fiddle here
Is there anything I can do to improve that or do I need to replace all umlauts in the DB?


texts:
I am configuring AlwaysOn in my test environment but getting the following error.

Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active.  The command cannot be processed.

I followed the below link to solve the issue but no luck

https://blogs.msdn.microsoft.com/alwaysonpro/2013/12/09/create-availability-group-fails-with-error-35250-failed-to-join-the-database/

I checked the SQL LOG for error in node1 appears as

  The Service Broker endpoint is in disabled or stopped state.

checked the same at node2 for error appears as below:

  Database Mirroring login attempt failed with error: 'Connection
  handshake failed. An OS call failed: (80090350) 0x80090350(The system
  cannot contact a domain controller to service the authentication
  request. Please try again later.). State 66.'.  [CLIENT: my server IP
  address(node1)].

Kindly check and suggest to move forward.


texts: Does anyone know how to change the color of the right click menu in SSMS 2016 with the dark theme enabled?


texts: why mysql binlog creating new log file daily

texts:
I have started binary logging on MySQL server on my Ubuntu. It start creating log files in /var/log/mysql. My my.cnf configuration is as follows:
log-bin="/var/log/mysql/"
expire_logs_days=10
max_binlog_size=50M
binlog-ignore-db="phpmyadmin"

Problem:
When I check logs folder a new file appears daily. Which is created at around 6:45AM to 7:00AM IST i.e 1:30AM to 1:45 GMT. But According to official documentation of MySQL on Binary Logging says:

The server creates a new file in the series each time it starts or
flushes the logs. The server also creates a new binary log file
automatically after the current log's size reaches max_binlog_size.

Why new log file is created daily as:

My system is not rebooted from last 10days which I checked with uptime command in terminal.
The max_binlog_size is given 50M in configuration and my logs reached only 2-3M.
I have not used FLUSH logs.
I have no CRON which do some task auto magically.

I have googled this query but didn't find the reason.
Can anyone help me to know the reason. Thanks in advance


texts: How to update MariaDB if the current version is not in default directory?

texts: I will try to update my MariaDB from 5.5.56-MariaDB to 10.3.9-MariaDB. However my current version was configured or located into another directory. How should i do it?


texts: Enabling JMX For Hadoop HDFS & Also MapReduce

texts:
I'm having a hard time figuring out how to enable JMX to submit metrics for HDFS and MapReduce jobs in Hadoop (CDH4).

I've seen several links and read through 'The Definitive Guide' and 'Hadoop Operations' on the 'monitoring' chapters and it goes through a great background on Metrics1, Metrics2, JMX JSON files, and that the latter is the preferred method, but I can't find out how to enable it for HDFS and MapReduce independently.

This blog has a good intro but it doesn't monitor Task and Data Nodes.  I'm not sure how to enable it for everything.  Does anyone have any steps?  Thanks!


texts:
I'm newish to MySQL and a lot of my query is pieced together from this site and trial and error, so any help is appreciated.  If I left out anything, or more detail is needed, I will be happy to give what I can.

My query takes, on average, about 90 secs to run.  It also doesn't return all the correct values.  Even if speed can't be fixed, how do I correct it to return the proper values?

I'm trying to get the most recent set of temperatures for a given location and also an average for the last 24hrs.  A location can have up to 20 thermometers.

SELECT t.Common_name, f.Food_name, e.Temperature, e.Temp_timestamp AS Time, Daily_avg.avg_temp
FROM Thermometer t
JOIN Temp_records e
  ON e.Temp_therm_id = t.Therm_id
LEFT JOIN Food f
  ON f.Food_therm_id = t.Therm_id
JOIN (
  SELECT Temp_therm_id, AVG( Temperature ) AS avg_temp
  FROM Temp_records
  WHERE Temp_timestamp >= NOW( ) - INTERVAL 1 DAY
  GROUP BY Temp_therm_id
) AS Daily_avg
USING ( Temp_therm_id )
WHERE t.Therm_unit_id =2
AND Temp_records_id
IN (
  SELECT MAX( Temp_records_id )
  FROM Temp_records
  GROUP BY Temp_therm_id
)

Thermometer Table

Therm_id
Therm_unit_id
Common_name

Temp_records Table (~18k records at the moment)

Temp_records_id
Temp_therm_id
Temp_food_id
Temperature

Food Table

Food_id
Food_unit_id
Food_name
Food_therm_id

What I'm currently seeing:

(The probe isn't in food, that's just the ambient temperature in C)
The db has slightly different temperatures for the given food items, and since I can only get the temperature of one food item at a time, they obviously can't have the same time stamp.

Should I remove the averaging part in to it's own query?

If I remove the food part of the query, it returns my 3 most recent temperatures and their averages. (I only have 3 thermometers for testing at the moment)


texts: Unexpected behaviour for to_date() with week number and week day


texts: MySQL - Left Join takes too long, how to optimize query?

texts:
A leader may have many followers. A notification_followers table gets a single notification when a leader adds a post with an entry leader_id 1 and notifiable_id 0 (id 1,2 in table). The same table gets a single notification when the current user 14 is followed by someone, with an entry leader_id 0 and notifiable_id 14 (id 3 in table).

notification_followers (id is PRIMARY, each field except data is an index on its own)

| id | uuid               | leader_id | notifable_id | data   | created_at
-----------------------------------------------------------------------------------
| 1  | 001w2cwfoqzp8F3... | 1         | 0            | Post A | 2018-04-19 00:00:00
| 2  | lvbuX4d5qCHJUIN... | 1         | 0            | Post B | 2018-04-20 00:00:00
| 3  | eEq5r5g5jApkKgd... | 0         | 14           | Follow | 2018-04-21 00:00:00

All the follower related notifications are in one place now which is perfect.

We need to now check if the user 14 is a follower of leader_id 1 to know whether to show them notifications 1 and 2. For that, we scan the user_follows table to see if the logged in user exists as a followed_id to the leader_id so they know about the notification, but only if they followed the leader before the notification was posted (new followers should not get older post notifications when the follow the user, only new ones).

user_follows (id is PRIMARY, each field is an index on its own)

| id | leader_id | follower_id | created_at
----------------------------------------------------
| 1  | 1         | 14         |  2018-04-18 00:00:00 // followed before, has notifs
| 2  | 1         | 15         |  2018-04-22 00:00:00 // followed after, no notifs

The final thing to note, is the user should know if the notification was read or not, this is where the notification_followers_read table comes in. It stores the follower_id along with the notification_uuid for all read notifications, along with their read_at timestamp.

notification_followers_read (composite index on notification_uuid, follower_id)

| notification_uuid | follower_id | read_at
--------------------------------------------------------
  qIXE97AP49muZf... | 17          | 2018-04-21 00:00:00 // not for 14, we ignore it

We now want to return the latest 10 notifications ordered by the auto incrementing nf.id desc for user 14. They should see all 3 notifications from notification_followers, since non of them were read by this user yet. The first 2, since they followed the leader before the leader made the posts, and the 3rd notification, since they were followed and their notifiable_id is 14.

Here is the query which works, but take too long ~9 secs:

SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at, nfr.read_at
FROM notification_followers nf
LEFT JOIN user_follows uf ON uf.leader_id = nf.leader_id AND uf.follower_id = 14
LEFT JOIN notification_followers_read nfr ON nf.uuid = nfr.notification_uuid AND nfr.follower_id = 14
WHERE (nf.created_at > uf.created_at OR notifiable_id = 14)
ORDER BY nf.id DESC LIMIT 10

notification_followers has ~100K records and we're using InnoDB. Here is the EXPLAIN for the query:

How can we optimize the query so it runs in a few ms?

UPDATE WITH SQL DUMP

SQL DUMP TO REPRODUCE LOCALLY just create speed_test database locally and import file to see the slow query issue live with all the table data (~100K rows).


texts: Database diagrams & DENY on tables/schemas

texts:
So, my developers want to be able to create database diagrams in the dev database, which is fine. And I want to DENY them access to selective schemas and tables which are deprecated, so that I can start to cut down on some database design that's run amok until we have time to pursue a formal cleaning process.

However, even with granting them the db_owner role to the database in addition to the specific object denies, they cannot create a new diagram and receive an error message concerning the table they cannot access (first table alphabetically that they are denied from is used in the message).

Is there any middle ground here where they can use database diagrams and still retain a DENY right on key tables/schemas?


texts: Could a large log file be the reason cron job keeps dying?

texts: The log is 15 Gigabytes. I guess I have to back it up and delete. I have enough hard disc space, but did not realize log was so large. And overnight cron jobs suddenly started to die in the middle.


texts: mongodb running single instance with --auth flag enabled is it means mongob running autorization enabled?

texts:
I am actually upgrading 2.2.0 single instance of MongoDB to 3.2 current version.

My MongoDB single instance running with --auth flag enabled so while upgrading from 2.4 to 2.6, do I need to consider authentication and authorization specific instructions given in upgrade notes or can I just simply follow Upgrade Standalone MongoDB Instance to MongoDB instructions just copying the 2.6 binaries?


texts: Error when trying to add a shard set in Mongo db

texts:
I'm trying to add a shard set in mongos I got this error and . Kindly help.

mongos>db.runCommand( { addshard : " firstset/ip_1:port_1,ip_2:port_2,ip_3:port_3" } );

{
      "ok" : 0,
      "errmsg" : "failed listing   firstset/ip_1:port_1,ip_2:port_2,ip_3:port_3's databases{ ok: 0.0, errmsg: \"not authorized on admin to execute command {listDatabases : 1 }\" ,code : 13 } "
}


texts: Why doesn't this query limit results by first and last name?

texts:
I have an ugly query, horrible table names courtesy of MySQL Workbench, and a severe lack of planning on my part to change to less verbose names prior to actually writing the code.  Try to move past all of that!

The Problem

I have multiple many-to-many relationships for publications.  I'm trying to pull publications by their tag name (category) or by the author's first and last name.  I originally was pulling exclusively by tag name, but was requested to search by author as well.  When it was solely by tags everything worked flawlessly.  I've joined the tables required to search by name and everything has gone haywire.  I can sort by tags still, but it returns duplicates.  Searching by name gives results, but they aren't limited by name as expected.

The Query

SELECT publicationsId, title, summary, image, publicationDate, citation, publications.dateAdded, firstName, lastName, employee_employeeId,
GROUP_CONCAT(DISTINCT tagName) AS tagName,
GROUP_CONCAT(DISTINCT publicationType) AS publicationType
FROM publications
LEFT JOIN publications_have_publicationTags ON publications_have_publicationTags.publications_publicationsId = publications.publicationsId
LEFT JOIN publicationTags ON publications_have_publicationTags.publicationTags_publicationTagId = publicationTags.publicationTagId
LEFT JOIN publications_have_publicationTypes ON publications.publicationsId = publications_have_publicationTypes.publications_publicationsId
LEFT JOIN publicationTypes ON publications_have_publicationTypes.publicationTypes_publicationTypeId = publicationTypes.publicationTypeId
LEFT JOIN employee_has_publications ON publications.publicationsId = employee_has_publications.publications_publicationsId
LEFT JOIN employee ON employee_has_publications.employee_employeeId = employee_employeeId
WHERE firstName LIKE :firstName AND lastName LIKE :lastName
GROUP BY publicationsId, title, summary, image, publicationDate, citation, publications.dateAdded, employee_employeeId, firstName, lastName
HAVING tagName LIKE :tagName
ORDER BY publicationDate DESC, publications.dateAdded DESC
LIMIT :limit

Parameters

:tagName, :firstName, and :lastName are '%%' if not searching by name or tag.

Table Structure


texts: Nested subquery giving eror of unknown column

texts:
I am facing uknown column 'id' issue which is exist in db_emp.test_etl. Then also it is giving uknown column error why?.This query successfully working in mysql version 8.0.14 but not mysql version 8.0.11.

18:30:48
update db_emp.test_etl
   set name='abc'
 where id in( select max(SETNO)
                from ( select sum(SETNO)
                         from AUC1
                        where SETNO=id
                     ) a
            )
Error Code: 1054. Unknown column 'id' in 'where clause' 0.0024 sec

  db_emp.test_etl table structure :

CREATE TABLE `test_etl` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

  Above query work for single level sub query as below :

12:02:57
update capitalmarket.test_etl
   set name='abc'
 where id in(select sum(SETTLEMENTNO)
               from capitalmarket.AUCTION
              where SETTLEMENTNO=id
            )
0 row(s) affected Rows matched: 0  Changed: 0  Warnings: 0  0.0020 sec


texts: Normalize or Denormalize - Which would be better

texts:
I have the following data structures:

Regions ( About 150 Regions )
-------
id    Region_Name

Sub Regions (10 -12 for each region)
------------------------------------
id  Sub_Region_Name  Region_id

Showrooms ( 150 - 170 per Sub Region )
--------------------------------------
id  Showroom_Name  Sub_Region_id

Users ( 750 - 1,000 per Showroom)
-----------------------------------------
id  UserName Address  Age  Showroom_Id

What would be the best option for the Users table?

Should I have all the users in one table, which would take the number of rows to 20 million; or should I use a table structure like Users_Arizona, Users_Ohio etc. as I am most interested in users grouped by region.

The database is not open to public interaction.


texts: alter table alter column causes huge log and data file growth

texts:
I have a table with 2.6B rows in it and I need to change a column from Char(1) to Varchar(64).  the table is over 300GB with a few indexes.  The column in nullable, so the transaction i'm running is:
ALTER TABLE XXXX ALTER COLUMN YYYYY varchar(64) NULL

I understand that this is a logged operation, so i pre-sized the transaction log to 300GB thinking it would be enough especially with log backups every 5 minutes to allow log space reuse.  well the transaction log grew to 812GB before I had to cancel the transaction due to drive space issues.
I also experienced a very large increase in data file USED size which I don't know why that would be.  data file used size came to right around 200GB (very minimal transactions are done in this DB so i know the increase is from this alter table command).
I have some questions:

Why would the data files experience an increase when just changing from char(1) to varchar(64)?  I was under the impression that this should not change the amount of data stored unless that space is actually needed since its varchar.  all existing values were either null or 1 byte since char(1) is the existing datatype, no existing data needed to be expanded.
While trying to figure out how to do this better, I came across this answer from Aaron Bertrand and it seems like it would work in my case as well.  would this be a better way to accomplish this task?
this DB is in a 2 node synchronous commit AG and i noticed the redo_queue_size got to over 130GB, which means that the primary was sending log data at a faster pace than the secondary could apply them. This means that the log on the primary could not truncate when the log backups were completed.  is this a normal behavior for something like this?   is the alter column statement processed as one large transaction?  if so that would explain why the log kept growing.


texts: “Cannot continue the execution because the session is in the kill state” Error?

texts:
We use SQL Server 2019 Standard edition on Windows Server 2019. We have 200 users and thousands of connections at the same time.
SQL Server 2019 raises an error

Cannot continue the execution because the session is in the kill state

Once a week I restart the SQL Server service and the error is gone.

I installed CU1, CU3, CU4, CU5 and CU6, but that didn't fix the error.

Database's compatibility level: SQL Server 2019 (150)

Our programs use mostly 4 tables. I read that broken indexes could cause this error. I checked these tables' indexes, but no problem was found. Anyway, I rebuilt all indexes for these 4 tables, but that didn't fix the error.

How could I detect the root cause of this error, and how could I fix it?
Thanks.

i modify a stored procedure and test it, error raised.
in sql server management studio :
Msg 596, Level 21, State 1, Line 93
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 93
A severe error occurred on the current command.  The results, if any, should be discarded.

in exception.log file :
09/21/20 12:23:33 spid 697 Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION reading address 000001BDA9BA6000 at 0x00007FF8BCD099B3

in SQL Server Log:
2020-09-21 12:23:33.62 spid697     CImageHelper::Init () Version-specific dbghelp.dll is not used
2020-09-21 12:23:33.62 spid697     Using 'dbghelp.dll' version '4.0.5'
2020-09-21 12:23:36.07 spid697     ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\SQLDump10000.txt
     SqlDumpExceptionHandler: Process 697 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
     * *******************************************************************************
     *
     * BEGIN STACK DUMP:
     *   09/21/20 12:23:36 spid 697
     *
     *
     *   Exception Address = 00007FF8BCD099B3 Module(sqllang+00000000009099B3)
     *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
     *   Access Violation occurred reading address 000001BDA9BA6000
     * Input Buffer 150 bytes -
     *             dbo.SP_STOCK_INFORMATION '', '175812'
     *  , 7
     *
     *
     *  MODULE                          BASE      END       SIZE
     * sqlservr                       00007FF78DE00000  00007FF78DE9DFFF  0009e000
     * ntdll                          00007FF8D4640000  00007FF8D482CFFF  001ed000
     * KERNEL32                       00007FF8D4170000  00007FF8D4222FFF  000b3000
     * KERNELBASE                     00007FF8D0BE0000  00007FF8D0E73FFF  00294000
     * CRYPT32                        00007FF8D07F0000  00007FF8D09CBFFF  001dc000
...

    Stack Signature for the dump is 0x00000001415746C8
External dump process return code 0x20000001.
External dump process returned no errors.

2020-09-21 12:23:39.73 Server      Error: 17310, Severity: 20, State: 1.
2020-09-21 12:23:39.73 Server      A user request from the session with SPID 697 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.

Thanks.


texts: What is the most efficient way of inserting 199 millon rows of data?

texts:
I have a 2.2 GB text file in the format of:

1234567890
1234567890
etc.......

I need to insert each line into a table (field 1) such as:

CREATE TABLE [dbo].[table](
    [field1] [varchar](10) NOT NULL,
    [field2] [varchar](3) NOT NULL

What is the most efficient way of inserting this data?
Is there a premade program that can be used? (i.e can it be done from the command line externally)

I've created a script and I select 1,000 records create a sql query around them and then insert. This takes a while, is there a better way? (Better as in easier, faster, less overhead)

Note: I've tried 1M, 100,000, 10,000 batch sizes, but the query is long at those sizes, so am stuck with 1k for now.

(query used)

INSERT INTO table (field1, field2)
SELECT '1234567890','123'
UNION ALL
SELECT '1234567890','123'
UNION ALL
SELECT '1234567890','123'
// etc....


texts: How to access NEW or OLD field given only the field's name?

texts:
I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create multiple triggers, each with a different set of fields to check.

For example, I have the following schema:

CREATE TABLE daily_reports(
     start_on date
   , show_id uuid
   , primary key(start_on, show_id)

     -- _graph are hourly values, while _count is total for the report
   , impressions_count bigint not null
   , impressions_graph bigint[] not null

   -- interactions_count, interactions_graph
   -- twitter_interactions_count, twitter_interactions_graph
);

The validation must confirm that impressions_count = sum(impressions_graph).

I'm stuck because I don't know how to dynamically access a field from NEW from within plpgsql:

CREATE FUNCTION validate_sum_of_array_equals_other() RETURNS TRIGGER AS $$
DECLARE
  total bigint;
  array_sum bigint;
BEGIN
  -- TG_NARGS = 2
  -- TG_ARGV[0] = 'impressions_count'
  -- TG_ARGV[1] = 'impressions_graph'

  -- How to access impressions_count and impressions_graph from NEW?
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_daily_reports_impressions
ON daily_reports BEFORE INSERT OR UPDATE
FOR EACH ROW EXECUTE
  validate_sum_of_array_equals_other('impressions_count', 'impressions_graph');

I tried Executing Dynamic Commands by doing EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0], but PL/PGsql complains that NEW is an unknown relation.

I am specifically targeting PostgreSQL 9.1.


texts:
I have a database on SQL Server. Yesterday It went to Suspect Mode, To recover that from the suspect mode I have followed some steps (Run some commands). But while running those commands it is giving me the error like : "User Does not have permission to alter database, the database does not exist, or the database is not in a state that allows access checks SQL server". To resolve that issue I ran the SSMS with 'run as administrator' privilege, and I also tried to change the permissions for the database for the user, But it is not working. I am not able to delete that also and this is the second time I am facing this issue Now I have 2 databases in suspect mode

.


texts: Query to update the JSON that does not contain a specific key

texts:
I want to query a table that has JSON data. The data of the column detail in the table details are as follows.

id     | 1

detail | {
   myCompany: [{ email: 'company@company.com', inProfit: true }],
   myData: [
      { email: 'example@example.com', personalUse: true },
      { email: 'test@test.com', personalUse: false },
      { email: 'test@example.com' },
   ],
};

The JSON value of the column detail is as follows

{
   "myCompany":[
      {
         "email":"company@company.com",
         "inProfit":true
      }
   ],
   "myData":[
      {
         "email":"example@example.com",
         "personalUse":true
      },
      {
         "email":"test@test.com",
         "personalUse":false
      },
      {
         "email":"test@example.com",
      }
   ]
}

I want a query that updates the myData. If the myData field doesn't contain personalUse then update that field with personalUse:true. In the given example { email: 'test@example.com' } field should update as { email: 'test@example.com', personalUse: true }

You can use the below queries to try it locally.

CREATE TABLE details (id bigserial primary key, detail json not null);

INSERT INTO details (detail)
VALUES
  ('{"myCompany":[{"email":"company@company.com", "inProfit":true } ],
     "myData":[{"email":"example@example.com", "personalUse":true },
               {"email":"test@test.com", "personalUse":false },
               {"email":"test@example.com"} ] }');

Please, someone, help me out.


texts:
I have table with three integer columns id, a and b.
I want to get all records where a or b match specified parameter ordered by id:

select id, a, b from t where a=x or b=x order by id

Note that x value is same for a and b.

What it the most suitable index here?

Upd: Is there any use of the fact that we always look for same value in columns a and b? Can we create an expression index for that?


texts:
So has anyone else applied a covering index in MySQL and gotten improved performance but when you run explain, it shows using where, using temporary under the extra column?  Any thoughts why this is the case?  I'm happy about the performance improvement but the engineer in me wants to know why the extra column is not saying "using index".  If you need me to provide details, I will but this question is directed to the experts who have already experienced this with MySQL and know why. I would add that in my particular case, I applied a covering index to an update statement.  All the columns in the query were included in the composite (covering) index. Here is an example of exactly what I did:
Table A has the following properties:
25,000 rows.
Has the following columns: one, two, three, four, five, six, seven, eight, and nine.
My covering index query target:
update table A set one = 'somevalue', two = 'somevalue2', four = 'somevalue3', five = 'somevalue4'
where seven = 'somevalue5' and eight = 'somevalue6'
covering index on table A:
idx_covering(seven,eight,
one,two,four,five)


texts: Disabling protocols using SQL Server Configuration Manager or ENDPOINTs

texts:
Besides SQL Server Configuration Manager, it's possible to disable access to specific SQL Server protocols by disabling the default ENDPOINTs, but what's the difference between the two options?

Are there security concerns regarding disabling one and not the other? (Should I disable both?)


texts: How to avoid deadlocks when running queries on a remote server

texts:
I need to pull data from many different systems, all on remote, LIVE SQL Servers into one place for a one-off piece of work that will last a few weeks.  This has to be run multiple times during the day and MUST come from LIVE systems.

I am just running multiple:

SELECT * INTO #temp_table FROM [linked_server].CRM.dbo.[table];

queries using a linked server to get all the data I need.  Then I process it.

The one system that is causing me issues is Microsoft Dynamics CRM, as there seem to be a lot of background processes running constantly.  I have found that I mostly get a deadlock after 1 minute of execution, but other times my query completes with no deadlocks in around 1.5 minutes.  So I have to run my query, wait a minute, run it again, wait a minute, etc. until it finally works.

At some point I can see this causing problems for users, trying to access a live system at the same time I am attempting to bulk-extract data multiple times.

I had a think about why I would be getting deadlocks on SELECT queries in the first place and my (non-DBA) thought was that this was probably due to indexes being changed under my feet as I pull data out.  So I tried removing any WHERE clauses on the tables I needed data from.

The net effect of this is that my script runs much more consistently without deadlocks, but it also takes a lot longer to execute (as it is pulling over a lot of data I don't want).

I had the following ideas to improve upon this, but I don't want to start developing a whole load of work if these aren't going to help at all:

write a stored procedure on the CRM server to extract the data I want.  Then call the stored procedure to get at the data (would this resolve the deadlock issue though?);
stage the data locally into a permanent table, and poll it when it has updated;
use the CRM server as the "master" server, and pull all the other data into this (but there are space issues here);
use a different mechanism to get at the data, e.g. OPENQUERY, OPENROWSET, etc.;
add a temporary covering index to the tables I need to extract data from (as I have heard this might help);
I could also write this as a .NET application, where I would basically connect directly to each server in turn, pull out the data I want and store it in memory until I have processed it.

Any experience on resolving these sort of issues?