texts:
I have fallowing configuration for my SQL 2014 server:

SSD RAID 1 - for data files
SSD RAID 1 - for tempdb? logfiles?
HDD (SAS 15K) RAID 1 - for tempdb? logfiles?

I noticed that sql uses tempdb quite heavly and I wonder how shall I place the logfiles and tempdb to optimize the throughput (the application is not very write intensive). Does it make sense to put logfiles and tempdb on the same SSD array or I should better use the HDD array for logfiles?


texts: Best way to know if my environment has workload repository license

texts:
Usually, when I need to use something that use some resource from awr, I look for LAST_USAGE_DATE at dba_feature_usage_statistics with feature name Automatic Workload Repository, but I'm almost sure this is not a good way.

How I know if my environment has workload repository license?


texts: Function for matching email address returns full table of emails

texts:
I am using Postgres 9.4.6.

I have created a Function for finding email address that match an email variable that is passed. However, when I search for 'user@email.com' I get a full table of email addresses that do not match 'user@email.com'. My function is as follows:-

CREATE OR REPLACE FUNCTION spgetmatchingusers(IN email text)
RETURNS TABLE(email character, password character, userid smallint, teamname character, reset_password character, reset_password_expiry timestamp with time zone) AS
$BODY$
select "email", "password", "userid", "teamname", "reset_password", "reset_password_expiry"
from "RegisteredUsers"
where LOWER("RegisteredUsers"."email") = LOWER(email)
$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION spgetmatchingusers(text)
OWNER TO bjlizgykrnyffr;

I call the function with:

select * from spgetmatchingusers('user@email.com')

If I type:

select * from spgetmatchingusers('randomtext')

I still get a full table of users returned.

For reference, my RegisteredUsers table is as follows...

CREATE TABLE "RegisteredUsers"
(
userid smallint NOT NULL DEFAULT nextval('user_id_seq'::regclass),
teamname character(50) NOT NULL,
first_name character(50),
surname character(50),
email character(50),
company_name character(50),
password character(50),
reset_password character(50),
reset_password_expiry timestamp with time zone,
CONSTRAINT "RegisteredUsers_pkey" PRIMARY KEY (userid)
)
WITH (
OIDS=FALSE
);
ALTER TABLE "RegisteredUsers"
OWNER TO bjlizgykrnyffr;

Apologies if I have missed something obvious but after searching around the internet I can't seem to work out where I have gone wrong!


texts: EXECUTE within function not using index?

texts:
I have an accounts table with ~200k rows and an index for these columns:

  account_type_id BIGINT
, member_id BIGINT
, external_id VARCHAR(64)

CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id) TABLESPACE index_tbsp;

And I have a function that is performing some ETL work with the following query:

EXECUTE '
   SELECT * FROM normal_object.account
   WHERE account_type_id = $1
   AND member_id = $2
   AND external_id = $3'
INTO e_row
USING r_row.account_type_id, r_row.member_id, r_row.external_id;

Yet, the EXECUTE command is NOT using the index and I'm not sure why. My only guess is that the data types are not lining up. Yet r_row.account_type_id is a BIGINT, r_row.member_id is a BIGINT, and r_row.external_id is a VARCHAR(64).

Any suggestions as to why it is not using the index?
How can I get it to use the index? (I've already tried setting enable_seqscan off.)


texts: How to make a table with fields that are other tables in MySQL?

texts:
I am making a music library system with php in MySQL.  In this system there would be a table named Songs.  Since many songs can be by the same Artist (Person), I wanted to store a table of Person that contains a persons data (name, DoB, hometown, fan page URL), then just store that ID in the Songs table.

I'm trying to avoid repeating the same 10 fields for an Artist over and over for each of their songs. I'd rather store the ID (Primary Key) and then be able to pull all of their specific data.

Two questions:

What is this called, so I can research how to do it (or can someone give me an example)?
If I do this, will queries for "Barry Manilow", who's ID  may be '123', be found in every song that contains the field Artist='123'?

Thanks,
-CJJ
(Basically, what's the best way to reuse fields)


texts: MariaDB & MySQL - Trigger not firing: “Field '__' doesn't have a default value” (Old Bug not really fixed?)

texts:
I think my problem might be caused by this old MySQL bug (and this one: /bug.php?id=6295 ), in which they were checking the column constraints before running triggers. But, according to that bug, they fixed it years ago.

I was originally running on MariaDB v10.1.19; but according to this MariaDB ticket: jira.mariadb .org/browse/MDEV-10002, they also fixed this in v10.1.6.

I have tried it on:

[FAILS] -> MariaDB v10.1.19
[FAILS] -> MySQL v5.6
[WORKS] -> MySQL v5.7

Is there perhaps a config problem with my setup that’s causing this to not work in MariaDB? Or is it more likely that the bug got broken again since its original fix? I don’t want to bother the MariaDB devs if it turns out to be my fault.

The Problem

I have a restricted view which filters on a base table using a discriminator column. The view does not contain the discriminator column. In the base table, this column does not allow NULL values.

I created a BEFORE INSERT trigger to populate this column when inserting data through the view, but as far as I can tell, the trigger is not firing. When inserting a record into the view, the DB throws the error:

Field 'groupId' doesn't have a default value

I’ve tried doing several insert tests on the base table, and it looks like the trigger is just not firing at all. I searched around online for other people with this problem, but they all seemed to just be syntax errors. I don’t have much experience with this kind of “deep” SQL, but I think mine’s correct.

Here’s an example implementation, I also made a SQL Fiddle:

P.S. Sorry about the 2 links with spaces in them. StackExchange won't let me post more than 2 links; apparently, I'm 4 reputation short.

CREATE TABLE privateNotes (
  id int(11) NOT NULL,
  userId int(11) NOT NULL,
  text varchar(500) NOT NULL,
  groupId int(11) NOT NULL
);

INSERT INTO privateNotes (id, userId, text, groupId) VALUES
(30, 1, '[John] A Secret Message',         101),
(32, 2, '[George] A Secret Message',       101),
(34, 3, '[Sarah] A Secret Message',        202),
(36, 4, '[Mary] A Secret Message',         202);

-- --------------------------------------------------------

-- Get
CREATE FUNCTION getCurrentGroupId()
    RETURNS int(11)
    NO SQL
    BEGIN
      RETURN @currentGroupId;
    END //

-- Set
CREATE FUNCTION setCurrentGroupId(groupId_in int(11))
    RETURNS int(11)
    NO SQL
    BEGIN
      SET @currentGroupId = groupId_in;
      RETURN @currentGroupId;
    END //

-- --------------------------------------------------------

CREATE VIEW myPrivateNotes AS
SELECT
  privateNotes.id AS id,
  privateNotes.userId AS userId,
  privateNotes.text AS text
FROM
  privateNotes
WHERE
  (
    privateNotes.groupId = getCurrentGroupId() /* 101, 202, etc. */
  );

-- --------------------------------------------------------
-- Trigger

CREATE TRIGGER before_insert_privateNotes
    BEFORE INSERT ON privateNotes
    FOR EACH ROW BEGIN
        IF NEW.groupId IS NULL THEN
            SET NEW.groupId = getCurrentGroupId();
        END IF;
    END //

-- --------------------------------------------------------
-- ------- --
--  TESTS  --
-- ------- --

-- View Insert Test
SELECT setCurrentGroupId(999);
//
-- (Uncomment) Error: Field 'groupId' doesn't have a default value
-- INSERT INTO privateNotes (id, userId, text) VALUES (50, 51, 'a cool message');
//

-- View Select Tests
--
-- Copy the full chunk bellow to the Sql box over there ->
-- Don't forget to change the delimeter to: [//]
SELECT setCurrentGroupId(NULL);
//
SELECT * from myPrivateNotes; -- Returns Zero Results [Correct]
//

SELECT setCurrentGroupId(101);
//
SELECT * from myPrivateNotes; -- Returns Valid Results [Correct]
//


texts: how to find out what process(es) caused the autogrowth of a database or a database file?

texts:
I have a process(es) hungry for tempdb, but I am struggling to identify this process.

any ways I could achieve this?

  We have been alerted as Tempdb has now grown into the space you
  reclaimed on the T:\ drive earlier. Again, there is 10MB remaining on
  disk. I can see a number of auto growth events across the data files
  on the REP instance starting at 10:18AM this morning. In total there
  were 330 auto growth events at 512MB each, totalling 168GB.

  It is difficult to highlight after the fact what has used this space
  in Tempdb, are you aware of any processes that may be using Tempdb in
  this manner today?

/------------------------------------------------------------\

Identifying How Often an Auto-growth Event has Occurred

When SQL Server performs an auto-grow event, the transaction that triggered the auto-grow event will have to wait until the auto-grow event completes before the transaction can finish. These auto-growth events cause your performance to degrade a little when an auto-grow event is taking place. For this reason it is best if you can size your database appropriately so auto-growth events rarely occur.

If you are interested in how often an auto-growth event occurs on your system you can capture those events using a trace. By knowing which databases are performing auto-growth events allows you to adjust those database file growth properties so they will perform auto-growth events less frequently. You can use the profiler “Data File Auto-grow” and/or the “Log File Auto-grow” events to track these database auto-growth events. If you are running SQL Server 2005 or above, both these auto-grow events are already being captured by the default trace. If you haven’t turned off the default trace then you can use the default trace file to find these auto-grow events. If you have turned off the default trace you can either enable it, or setup a new profiler trace to capture the “Data File Auto-grow” and “Log File Auto-grow” events.

The default trace logs to a file. I have provided the code in Listing 4 to show you how to extract all the auto-growth events from the default trace files. If you create your own profiler trace session to capture these auto-grow events then you will need to modify this script to meet your profiler trace settings.

https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

marcelo miorelli
11-mar-2014

\*--------------------------------------------------*/
DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime


texts: How to set ADR Base directory correctly on OracleDB on Linux?

texts:
Installed Oracle Database on Linux based OS. Listener is ok. But Oracle is not. (I changed all angular brackets to vertical, because parser not shows angular brackets)

  ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
  ORA-48140: the specified ADR Base directory does not exist
  [/volumeX/app/oracle/product/11.2.0/xe/dbs/|ORACLE_BASE|]
  ORA-48187: specified directory does not exist

Thats line in initXE.ora and init.ora:

  diagnostic_dest='|ORACLE_BASE|'

Result of echo $ORACLE_BASE:

  /u01/app/oracle

u01 is a symlink of volumeX directory.

How to solve this problem?

It is on Linux DiskStation 3.2.40.


texts: Optimizing a simple query joining two big tables

texts:
I don't understand why the following query is so slow (with Postgresql 9.6):

SELECT s.pkid AS pkid_site , s.geom AS geom_site , z.pkid AS pkidEmprise, z.geom AS geom_emprise, z.précision_contour
FROM traitements.sites_candidats AS s
JOIN traitements.zones_sites AS z
    ON z.pkid_site = s.pkid
WHERE s.statut = 'selected'
AND z.statut = 'selected';

The structure of the tables is the following:

CREATE TABLE traitements.sites_candidats (
    pkid serial PRIMARY KEY,
    statut varchar(255) NOT NULL,
    geom geometry(Point, 2154)
);
CREATE INDEX ON traitements.sites_candidats (statut);

CREATE TABLE traitements.zones_sites (
    pkid serial PRIMARY KEY,
    pkid_site integer NOT NULL,
    geom geometry(MultiPolygon,2154),
    statut varchar(100)
);

CREATE INDEX zones_sites_idx_pkid_site  ON traitements.zones_sites (pkid_site);
CREATE INDEX zones_sites_idx_statut ON traitements.zones_sites (statut) ;
ALTER TABLE  traitements.zones_sites
    ADD CONSTRAINT zones_sites_references_sites_candidats FOREIGN KEY (pkid_site) REFERENCES traitements.sites_candidats(pkid) ON DELETE CASCADE;

(I deleted a couple of columns that are not involved in the query in order to improve readability).

The result of EXPLAIN ANALYZE:

Hash Join  (cost=17709.29..152088.29 rows=147368 width=887) (actual time=137.074..879.884 rows=210708 loops=1)
  Hash Cond: (z.pkid_site = s.pkid)
  ->  Seq Scan on zones_sites z  (cost=0.00..85198.14 rows=210717 width=855) (actual time=0.140..384.964 rows=210708 loops=1)
        Filter: ((statut)::text = 'selected'::text)
        Rows Removed by Filter: 23
  ->  Hash  (cost=13433.16..13433.16 rows=210490 width=36) (actual time=136.772..136.772 rows=210708 loops=1)
        Buckets: 65536  Batches: 8  Memory Usage: 2191kB
        ->  Seq Scan on sites_candidats s  (cost=0.00..13433.16 rows=210490 width=36) (actual time=3.085..87.774 rows=210708 loops=1)
              Filter: ((statut)::text = 'selected'::text)
              Rows Removed by Filter: 90265
Planning time: 0.386 ms
Execution time: 888.436 ms

It's obviously the JOIN .. ON that consumes time because it does a hash.

Table traitements.sites_candidats has about 300k rows (210k of them with statut='selected') and traitements.zones_sites about 210k rows (nearly 99% of have statut='selected'). The query takes more than 4 minutes.


texts: Is it Possible to have Multiple Named Instances with No Default Instance?

texts: Using SQL Server 2008 R2, is it possible to install multiple named instances, and no default instance?


texts: SQL Server 2016 Log shipping restore job stuck

texts: SQL server 2016 log shipping restore job will run every 15mins, Database size is 1TB, Job will not take two minutes, Yesterday job was running for last 13 hours and the database is in "InRecovery" state, The job status is InProgress, couldn't see any blocking on the server and SPID of the restore is in suspended state, anyhow killed the restore job reconfigured the log shipping, I just want to know the possible reasons to create RCA to this issue, I do not see any info in event or SQL error logs since i killed the job manually.We do not have IO or storage issues also.


texts: Merge statement with two conditions in same CLAUSE alternative in Postgres

texts:
I was able to convert a Merge which has one condition in both MATCHED & UNMATCHED clauses using CTE. But not sure how to do (both DELETE & UPDATE), if I have two conditions in the same clause.
Below is the sample example:

SELECT * FROM source;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5

SELECT * FROM destination;

         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

I have Merge implementation in ORACLE like below:

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET d.description = 'Updated'
    DELETE WHERE d.status = 10;

5 rows merged.

SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Updated
         3         20 Updated
         5         20 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

8 rows selected.

If the above code will have as below:

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET d.description = 'Updated'
    DELETE WHERE d.status = 10
  WHEN NOT MATCHED THEN
     INSERT (ID, STATUS, DESCRIPTION) VALUES (s.id,s.status,s.description);

Please help me how to re write the above code using WITH in Postgres.


texts: Why does DTExecUI.exe run fine but DTExec.exe gives me an error?

texts:
When I try to execute an SSIS package with DTExec.exe with a batch file or in the command prompt it gives me error code 0xC0040019 Data Flow Task 1 (SSIS.Pipeline) data flow objects cannot be loaded. Check if Microsoft.Sqlserver.pipelinexml.dll is properly registered.
I tried storing the SSIS package in the server and in the file system but get the same error. The strange part is when I try to execute the package from DTExecUI.exe it runs without any problems. I tried generating the command line in DTExecUI.exe and using that in the batch file but I still get an error.
I did notice that my server is 64bit and DTExec.exe and DTExecUS.exe are both 32bit versions. I don't know if this is the problem but if so why would one work and the other fail?


texts: I'm using third party tool for making schedule database backups. I need to make one additional full backup. Should it be affected on the scheduled backups? If yes,  is there any solution?


texts: Mysqli prepare error: Unknown storage engine 'InnoDB'

texts:
I'm getting this error on my XenForo installation. Server logs show out of memory because of too many php processes opened up which cause mysqld to shutdown. The WordPress installation associated with MyISAM works fine. The error goes away once I have restarted the mysql service. I'm unable to fix the issue. Would be grateful for some input!

MySQL logs:

..more similar errors above..
150117 20:18:56 [ERROR] Cannot find or open table wpdb/wp_wpsa_subscribe_author from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.

150117 21:24:38 [Warning] IP address '222.186.15.72' could not be resolved: Name or service not known
150117 21:38:35 mysqld_safe Number of processes running now: 0
150117 21:38:35 mysqld_safe mysqld restarted
150117 21:38:38 [Note] Plugin 'FEDERATED' is disabled.
150117 21:38:38 InnoDB: The InnoDB memory heap is disabled
150117 21:38:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150117 21:38:38 InnoDB: Compressed tables use zlib 1.2.3
150117 21:38:38 InnoDB: Using Linux native AIO
150117 21:38:39 InnoDB: Initializing buffer pool, size = 119.0M
InnoDB: mmap(127713280 bytes) failed; errno 12
150117 21:38:39 InnoDB: Completed initialization of buffer pool
150117 21:38:39 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150117 21:38:39 [ERROR] Plugin 'InnoDB' init function returned error.
150117 21:38:39 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150117 21:38:39 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150117 21:38:39 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
150117 21:38:39 [Note] Server socket created on IP: '0.0.0.0'.
150117 21:38:40 [Note] Event Scheduler: Loaded 0 events
150117 21:38:40 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
150117 21:38:40 [ERROR] /usr/sbin/mysqld: Table './wpdb/wp_wfConfig' is marked as crashed and should be repaired
150117 21:38:40 [ERROR] /usr/sbin/mysqld: Table './wpdb/wp_wfConfig' is marked as crashed and should be repaired
...more similar errors follow..

The logs at /var/log/messages show loads of PHP processes opened (50+) and server going out of memory, shutting down mysqld. I couldn't find anything that would be making so many processes stuck up. Could the mysql error before the crash occurs - "Cannot find or open table" be causing the error?


texts: MySQL 5.7 performance issue with 300 GB Innodb

texts:
We have a database running on MysSQL 5.7. The database size is around 300 GB, running on a dedicated Linux server (RHEL 6) with 144 GB RAM, 16 CPUs and 15 GB swap size.

The server is busy throughout the day with a minimum of 50 connections. Most of the queries have indexes and are optimized once weekly. But we are still facing a performance issue.

So requesting a review of the my.cnf configuration and please suggest any changes.

my.cnf

[mysqld]
basedir=/usr
datadir=/sql/mysql/data57
socket=/sql/mysql/data/mysql.sock
skip-external-locking
key_buffer_size = 4000M
max_allowed_packet = 5120M
table_open_cache = 4000
sort_buffer_size = 128M
read_buffer_size =8M
join_buffer_size=128M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 100
query_cache_size=0
query_cache_limit=4M
query_cache_type="ON"
query_cache_min_res_unit=20K
query_prealloc_size=40K
query_alloc_block_size=40K
max_connections=300
sql_mode = ""
interactive_timeout = 28800
wait_timeout = 7200
connect_timeout = 60
default_password_lifetime=0
old_passwords=2
lower_case_table_names=1
tmpdir=/tmpfs
tmp_table_size=20G
max_heap_table_size=170M
innodb_buffer_pool_size=100G
innodb_buffer_pool_instances=16
innodb_buffer_pool_chunk_size=6562M
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_log_file_size=2G
innodb_lock_wait_timeout=180
net_buffer_length=8K
transaction-isolation=READ-COMMITTED
innodb_lock_wait_timeout=180
federated

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/sql/mysql/data/mysql.sock

[mysql.server]
user=mysql
basedir=/usr
log-bin=mysql-bin
binlog_format=mixed
server-id=1

[mysqldump]
quick
max_allowed_packet=1G

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size=1024M
sort_buffer_size=256K
read_buffer=8M
write_buffer=8M

[mysqlhotcopy]
interactive-timeout

Query

 SELECT year_no              AS "Year",
       month_no             AS "Month",
       area                 AS "Area",
       hq                   AS "Headquarters",
       region               AS "Region",
       depot                AS "Depot Name",
       aw                   AS "Dist Name",
       ss_channel           AS "Channel",
       sales.classification AS "Classification",
       sales.beat           AS "Beat",
       sales.salesman       AS "Salesman",
       sales.cust           AS "Retailer UID",
       Sum(qty)             AS "Qty",
       Sum(amt)             AS "Amount"
FROM   sales
       JOIN date_tbl
         ON dt = sales.dt
       JOIN or_areas
         ON area = sales.area
       JOIN hq
         ON hq = sales.hq
       JOIN or_regions
         ON region = sales.region
       JOIN depot
         ON depot = sales.depot
       JOIN or_aws
         ON aw = sales.aw
       JOIN prods
         ON sku = sales.sku
       JOIN prod_grp
         ON product_group = sales.product_group
       JOIN brand
         ON brand = sales.brand
       JOIN dsm
         ON ( salesman = sales.salesman )
       JOIN cust
         ON retailer = sales.retailer
WHERE  ss_inv_status IN ( 1, 3, -3 )
       AND ss_cust_name IS NOT NULL
       AND cal_date BETWEEN ? AND ?
       AND sales.zone IN ( ? )
       AND sales.region IN ( ? )
       AND sales.depot IN ( ? )
       AND sales.zone IN ( ? )
       AND sales.distributor_division = ?
       AND sales.brand IN ( ? )
GROUP  BY year_no,
          month_no,
          aw,
          sales.cust,
          prod,
          cal_date;

The ? are place holders and the values are entered as inputs.

Explain

More information

The performance problem is very big queries, which take 30 mins to execute.
These are report queries which are generated on daily basis.
It is not a DW system.


texts: Doubts on Setting up Master-Master replication (Percona)

texts:
I have two 5.6 Percona servers in which I'm planning to setup Active master- Passive master replication. Secondary master will be in read-only mode.

Caveat is that the servers in which i'm going to setup is live databases. Master is already running with server-id=1 and it is in place. Secondary master is newly created instance.

Now to backup the production master and copy it to the secondary master, is it mandatory that I need to take backup the live database and then copy it to secondary? Or will it suffice if I take last night's backup?

Also, when I set the Active master to be the slave for secondary, will it impact my production?

I know I need to do this off-live hours but situation demands, hence I wanted to reconfirm before proceeding.

Please highlight on what impact that could do in live database for setting up this replication.


texts: Joining one table with multiple tables for aggregate functions possible?

texts:
I am trying to get this seemingly simple looking query to work :

SELECT
c.id,
c.name,
count(chc.id) as contacts_count,
count(cha.id) as addresses_count

FROM
(customers c LEFT JOIN customer_has_contacts chc on c.id = chc.customer_id)
    LEFT JOIN customer_has_addresses cha on c.id = cha.customer_id

WHERE
c.company_id = ?

GROUP BY
c.id

But I keep getting the following result. I do not know where I am going wrong :


texts: What to do when a field in a table approaches the max signed or unsigned 32 bit integer?

texts:
In any given database that hold user records in the form of an unique auto-increment field (for the sake of the example, inter-user messages)... what to do when the time comes and it approaches the max signed or unsigned number of the current datatype? (A 32-bit INT)? I'm guessing that the database server would overflow when it tries to assign the (2∧32)-1 number to the next entry, so, how to avoid that happening (without changing the datatype, for the sake of the question) and keep adding records? What would you do?

Why would I use INT's and not, for example, VARCHARS?

It has been several days since I've asked myself this hypothetical question and I would like to know what a professional would do.


texts: Sql Server Installation can't find files

texts:
Okay, I wasn't sure whether to ask this here or on Server Fault, but the FAQ for DBA says it includes db configuration, so I'm going with here.

I'm installing Sql Server Express 2012 on a clean Windows Server 2012 box.

The install seems to go fine right up till the very end.

I left the program install directory at C:\Program Files....
I changed the instance root directory to D:\SQLE2012ROOT\.

Install chugs along happily, and when it ends, it says setup completed, but encountered the following error:

Feature:              Database Engine Services
Status:               Failed: see logs for details
Reason for failure:   An error occurred during the setup process of the feature.
Next Step:            Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
Component name:       SQL Server Database Engine Services Instance Features
Component error code: 0x851A001A
Error description:    Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

(it also gives a URL to go to, but clicking it just takes you to a Microsoft home page that shows consumer products to buy, like Surface and XBox. Thanks MS!)

The SQL service in Service Manager is stopped, and if you try to start it, chokes with message about service-specific error code 945, which googling, says it means Sql Server cannot find the necessary files.

Looking in the Event Logs after trying to start the service shows these two errors (each shows twice, once for MSDBData.mdf and once for model.mdf):

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.

and

FCB::Open failed: Could not open file e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1. OS error: 21 (The device is not ready.).

It would make sense that it cannot find those files, as the E: is an optical drive on the server, with no media mounted in it.

The million dollar question though is WHY it is looking for the files in that location? I've checked, and the setup DID actually create the files in the correct location under my specified instance root (D:\SQLE2012ROOT\MSSQL11.SQLE2012\MSSQL\DATA). But for some unknown reason, Sql Server is LOOKING for them under what looks like a development project build directory off the E: drive.

I looked in the registry and grepped on the C: to see if I could find where it was pointing to this wrong directory and simply re-point it, but I couldn't find anywhere where it pointed to that location.

I've already uninstalled once, and reinstalled from scratch again, and it did the same thing. The install file used was the 64 bit one that includes Management Studio, SQLEXPRWT_x64_ENU.

I've tried a number of things now, and could not get the 2012 to install. So I figured maybe just go to 2016 and see if that works. It installed but again had the same error as before, about Waiting on the Database Recovery handle. After setup concluded, I tried to start the service manually, and got the same errors in the event log about being unable to locate the MSDBDATA and MODEL files, though it has a different wrong path this time. Now it is attempting to look for those files in the directory D:\B\S2\Sources.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\. I checked the startup parameters for the service in Sql Server Configuration Manager...

The file paths listed in the startup parameters ARE CORRECT.
I have no idea why it is looking for the files in this other wonky D:\B\S2... directory.


texts: In Oracle multi-tenant architecture, is a PDB a container?

texts:
Below is an excerpt from Oracle document.

I just want to confirm, is it correct to consider a user-created PDB or a seed PDB as a container? It's a little weird to me. If this is true. Then essentially everything is a container in an Oracle multi-tenant architecture. CDB root, system container, application container, PDBs, seed PDBs are all containers?


texts:
I am using MySQL 5.1.56

I have a DB with about 70 tables and I have an issue with a particular corrupted table e.g. Table_X

When I try to access the table mysql> select * from Table_x; ERROR 1105 (HY000): Failed to read from the .par file

I am unable to add partitions to the table.

When I try to drop the table I see the below errors. mysql> drop table Table_X; ERROR 1051 (42S02): Unknown table 'Table_X'

The create query gives the error: ERROR 1050 (42S01): Table 'Table_X' already exists.

In my DB files locations, I can see the corresponding Table_X.frm, Table_X.ibd and Table_X.par files. But in addition, I also see a file '#sql-Table_X.frm' in the location.

When I check the 'Tables' table in the information_Schema DB, the Engine value is NULL for this particular table, where as it should have been InnoDb. The table seems to be corrupted somehow.

I tried the Flush-tables command,but that did not help as well. I am still unable to drop and recreate the table.

I do not wish to take a backup of this particulate table, but I need to preserve other tables of the database. Is there any way, I can just recreate this individual table without having to restore the entire Database.


texts:
I'm building a Go-based system, with MariaDB as the storage back-end. As the responses from MariaDB/MySQL are the slowest part of the whole application I was wondering a specific thing;

Does the field order in a query matter? Say I have this simpler query (but might be much bigger):

SELECT u.*, cu.FirstName, cu.Surname, uu.FirstName, uu.Surname
FROM UserContact u
LEFT JOIN User cu ON cu.ID = u.CreatedByID
LEFT JOIN User uu ON uu.ID = u.UpdatedByID
WHERE u.UserID = ?

Would u.* be faster before or after the custom fields? Or would the query optimiser tweak it correctly?

It might be trivial speed differentials, but I really care about speed, and as a SQL query easily has up to 1ms response time in my app's requests that normally are only a few microseconds themselves; every tweak in the usage of the SQL queries might be a big help in the overall performance of the system. I'm already buffering a lot of queried data with (in)validated cache, but sometimes in the back-end still need to run custom logic.

I can't test the performance in a Go benchmark at the moment, as in the latest macOS High Sierra the whole OS kernel panics on a filesystem crash  if the mysqld gets called too excessively.


texts: Microsoft SQL Server 2012 Mirrors Go Into Disconnected

texts:
I have some Microsoft SQL server 2012 mirrors, without a witness in asynchronous mode, that are sporadically going into disconnected.  I am running SQL Server 2012 11.0.5058.  Looking at the logs on the mirror side I see

  Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 1

There is a whole bunch of memoryclerk messages

  Error: 802, Severity:17, state: 0.

There is insufficient memory available in the buffer pool.

Error:1454, Severity: 16, State: 1.

  Database mirroring will be suspended. Server instance 'instance' encountered error 802, state 0, severity 17 when it was acting as a mirroring partner for database 'database'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

Any help as to what to check to find out why the mirrors go into suspended and then disconnected following this chain of events would be appreciated.  I can reestablish the mirrors after this and they work just fine for a couple of days and then it happens all over again.


texts:
I have a sql server which being monitor by Nagios. One day, i received some alerts from nagios come with some sensitive information like:

Critical Alert: dbi connect (Server name: ABC, Encrypted Password: 1, User:
Sa.......)

How can i hide this ?


texts:
I have some tables:

teste // id, descricao
teste2 // id, descricao
relacao // id, idTeste, idTeste2

With this structure, I got all products owned by a client. Now I'm trying to use FOR JSON PATH to extract data from the database:

This is where I am at the moment:

SELECT
    teste2.descricao AS "nome",
    relacao.id AS "produto.id"
FROM teste2
    INNER JOIN relacao ON relacao.idTeste2 = teste2.id
FOR JSON PATH

And this is the result from the server:

[{
    "nome": "Raphael Schubert",
    "produto": {
        "id": 1
    }
}, {
    "nome": "Raphael Schubert",
    "produto": {
        "id": 2
    }
}, {
    "nome": "Lorraine Schubert",
    "produto": {
        "id": 3
    }
}, {
    "nome": "Lorraine Schubert",
    "produto": {
        "id": 4
    }
}]

I was expecting to create a query where the answer is like this:

[{
    "nome": "Raphael Schubert",
    "produto": [{
        "id": 1
    }, {
        "id": 2
    }]
}, {
    "nome": "Lorraine Schubert",
    "produto": [{
        "id": 3
    }, {
        "id": 4
    }]
}]

I think with for each at select was possible, but I'm new to SQL Server, I know a bit about MySQL but was not able to extract that info.


texts: Is there a way to watch for schema changes in mySQL?

texts:
ALL,

Is there a way to watch for a schema changes in mySQL? Or I will have to continuously query information_schema tabes?

TIA!!


texts: Performance impact of having Polymorphic collections in MongoDB?

texts:
Say one document(Level 1) is supposed to have N(k) different kinds of items as sub-document arrays(Level 2), and these are stored in a separate collection(s?) due to lack of query optimisation for deep nested array sub docs.
Now, we are supposed to fetch N(d1) level-1 docs, each has N(d2) number of sub docs (total count of level-2 docs for one level-1 doc is N(d2))

What are the pros and cons of having all those item kinds in a single collection vs a collection of each type. Taking into consideration the following points:

The storage engine used is WT

DB can read while other documents are being written to same collection. here.
The read-write lock is document level and not collection level as with previous cases. here.

Sparse Indexing is allowed, since the fields can differ from type to type
The deployment type is a sharded cluster
All query-able fields are indexed (not blindly though) => $or operator can use indexes
A point in favour of one approach automatically means it leads to degradation of performance in other approach.

How the query will work in case of Polymorphic collection:

All different types can be fetched in a single query(using $or) operation and then attached to their parent documents in the server app. We can also use aggregate $group to group one type of docs together. Thus,

decreasing the network induced query latency.
decreasing the number of connections the from the server.

Since, the sub-docs are attached back to their parent docs, it requires a for loop on the server side. Which has to loop over N(d1) docs at minimum - in case aggregation $group is used at query level - or  N(d1) * N(d2) times if the sub-docs are not already grouped.

How the query will work in case each type has its own collection:

N(k) queries are made, as soon as a query returns we can attach the sub-doc to its parent doc.
We can, again, use aggregation $group and thereby limiting the server side loop to N(d1) iterations

In favour of a single collection:

Less number of connections used. ( consistent at 1 per query )
consistent latency. ( as an implication of earlier point )
?

In favour of multiple:

An organisation structure we are more used to. ( subjective )
?

How else could each case effect performance?


texts: Postgres trigram match acting strange for specific characters

texts:
I'm using pg_trgm to run a query, but I'm getting a lot of 1 matches when searching with a symbol difference. I have the following query:

SELECT my_column, similarity('$ Hello', my_column) AS sml
FROM my_table
WHERE my_column % '$ Hello'
ORDER BY sml DESC, my_column;

In my_table, I have the following:

- Hello
? Hello
| Hello
$ Hello
! Hello
!? Hello

They all return with a similarity match of 1. Do I need to escape the '$' or something similar?


texts: How to UPDATE a table for the level of parents in a column?

texts:
I have a table with internal parents as

id    parent    parent_level
1     NULL      1
2     1         2
3     1         2
4     3         3

where parent has a FK to id.

How can I update the table to assign the values of parent_level, according to the parent, and parent of parent of each row.


texts: Restricting mail sending from SQL Server 2008

texts: On a remote database server, hosting databases from a number of different clients, how do I ensure that someone does not send emails using my profile?


texts:
When I run "/usr/bin/mongod --config /var/data/rs1/rs1db0v3.conf". It failes. The error is:

warning: bind_ip of 0.0.0.0 is unnecessary; listens on all ips by default
about to fork child process, waiting until server is ready for connections.
forked process: 16970
ERROR: child process failed, exited with error number 1

The permission of the relative files and folds are following:

drwxr-xr-x 6 root    root     115 Mar 16 14:47 .
drwxr-xr-x 5 root    root      43 Jan 20 12:12 ..
drwxr-xr-x 6 mongodb mongodb 4096 Mar 16 15:05 db
drwxr-xr-x 2 root    root      28 Jan 20 10:04 keys
drwxr-xr-x 2 mongodb mongodb   23 Jan 20 10:32 log
-rwxr-xr-x 1 mongodb mongodb  593 Mar 16 14:47 rs1db0v3.conf
-rwxr-xr-x 1 mongodb mongodb 21303832 Mar  3 08:03 /usr/bin/mongod

I found similar questions in this website, but no answers can help me...
-----SOLVED-----
I have no idea how to run mongod as mongodb, so I changed the ownership to user1, and then run mongod as user1. It works.

drwxr-xr-x 6 root    root     115 Mar 16 14:47 .
drwxr-xr-x 5 root    root      43 Jan 20 12:12 ..
drwxr-xr-x 6 user1  mongodb 4096 Mar 16 15:05 db
drwxr-xr-x 2 root    root      28 Jan 20 10:04 keys
drwxr-xr-x 2 user1  mongodb   23 Jan 20 10:32 log
-rwxr-xr-x 1 user1  mongodb  593 Mar 16 14:47 rs1db0v3.conf
-rwxr-xr-x 1 user1  mongodb 21303832 Mar  3 08:03 /usr/bin/mongod


texts:
How do i check memory usage by my SQL server in production box.
I am using SQL Server 2016.When ever i check task manager,it shows above 90%.
I don't think that is the real memory usage by sql server.

I have a SQL performance tool grafana which shows CPU usage very less than what i see in task manager.
I checked Resource Monitor,there is can see Average CPU value.I am confused as to which is the SQL server memory usage.
I am trying to determine if memory pressure is an issue to some of my problem.

Can someone direct to a good/proper explanation.


texts: Concatenate in MS Access - how to omit space and comma if first value is missing?

texts:
I am concatenating 2 values in a MS Access Report: "Method" and "Experiment" and they are to be separated by a comma (,).

I have figured out how to concatenate succesfully when the second value is missing (Experiment), but I am struggling to omit a blank space and comma if the first value ("Method") is missing.

The code I am using is:
=([Method] & (", "+[Experiment]))

What I would like to obtain is:

Harvard, Mixed sequence
Intensity
Bradford


texts: Minimum credentials on SQL Server to connect via Power BI Desktop

texts:
Note: This is not a Power BI issue, this is a DB credential issue.

In Power BI Desktop, I am trying to connect to a database, but I only want that user to have SELECT access.

I have one User that is a "sysadmin" under the Server Role and that works.  But, that's too many permissions.

I created a new User and gave it Login Properties > Server Role = "public", then went into the database I need and gave it Membership > db_datareader.  I thought this would work, but it doesn't.  When I log into SSMS as that User and try to query that database, it says "The database {name} is not accessible."  In Power BI, the error reads: "We couldn't authenticate with the credentials provided. Please try again".

Power BI Users: Yes, I am on the Database tab, not the Windows tab.


texts: MongoDB CSV Export: One Array/Column & Array Index on rows, or vice versa

texts:
I have a MongoDB schema that looks like this:

const batchSchema = mongoose.Schema({
  _id: mongoose.Schema.Types.ObjectId,
  time: {type: [Number]},
  tmp: {type: [Number]},
  hum: {type: [Number]},
  co2: {type: [Number]},
  coolRelay: {type: [Boolean]},
  humRelay: {type: [Boolean]},
  fanRelay: {type: [Boolean]},
  lightRelay: {type: [Boolean]},
});

My goal is to have a csv file I can import to excel for use in creating charts, graphs, and other visuals based on the data.

Using Studio 3T(https://robomongo.org/), this doesn't seem to be possible unless I'm just not understanding the settings. Doesn't really matter if the data is organized horizontally or vertically. As long as each is on it own column/row, excluding the _id field. 3T seems to be only capable of placing them all along the same row.

I just tried using mongo's native cli csv export tool but found that didn't seem to come with my release. I'm running version 3.6 on openSuse, arm64, on a raspberry pi.

mongoexport as a command was not found, and when I ran the suggested cnf mongoexport to find a package containing it, nothing was returned either.

I know that I can export one file, per array using 3T, then cut/paste them together, however I hadn't planned on paying the annual license for 3T and was only using the free trial to get a more usable gui while in development. Was hoping to be able to write predefined bash script that could be reused repeatedly to export each "batch" document as it was completed.

Is there something I am missing about mongoexport or is this a limitation of the aarch64 release? I really don't want to have to go back, learn, and re-write everything for postgreSQL at this point. I mean, I will if I have to, but wanted to check in with you all first.


texts: Thought about this SQL Server backup plan?

texts:
I just started a new job, and I'm reviewing the database maintenance plan.  I've got quite a bit of experience writing SQL, but not much experience with DB administration.  My last job was at a large company, and they didn't let regular people touch that sort of stuff.

We are locked into SQL Server 2000 (it's embedded in some quite old software and we can't upgrade yet).  The current maintenance plan (Full Recovery model) does the following:

Every hour from 6am - 11pm:

  backup log Accounting to Accounting_Logs with noinit

Every night at 1am, this happens:

  backup Log Accounting WITH TRUNCATE_ONLY
  DBCC SHRINKDATABASE (Accounting, TRUNCATEONLY)
  backup database Accounting_ReadOnly to Accounting with init

Then at 3am:

  all the indexes are rebuilt

Is this a decent plan?  Will this give us good backups that are easy to recover?  I know I'm asking for a lot, but any thought/comments/suggestions would be appreciated.

Please let me know if you need more information.  Thanks!


texts: Overestimated query resulting in poor performance - TVF with xml processing

texts:
Today i asked question about bad query performance. That query had a lot of joins over a lot of views. I think i have found which view is a trouble for SQL server. Here is actuall query plan for select run on that view https://www.brentozar.com/pastetheplan/?id=BJB4WOV6S As you can see SQL Server highly overestimates number of rows and needed memory. Outter apply is used to run TVF that is returning processed XML, xmls are rather very small. I wonder how to avoid such overestimation(and what cuase them)? Should i create xml indexes for my table?

One of the TVF

FUNCTION [schema].[functionname](@xmlData  XML)
                RETURNS  TABLE
            AS
            return
                SELECT Tbl.Col.value('PN[1]', 'nvarchar(32)') as PPN
                    FROM   @xmlData.nodes('PI') Tbl(Col)
GO
example

declare @para as xml
set @para = '<PI><PGID>00000000000000000000000000000000</PGID><PN>0</PN></PI>'
Select * from [schema].[functionname](@para)


texts:
I have a database table (UTF-8) with a lot of names in different languages with lots of special characters. In another script I use these names and one part is to replace the special characters. To know all appearing characters I need to get a list of them from somewhere.

Is it possible to get a distinct list of characters appearing in a column? What would be the way in PostgreSQL?

For example I have a table with names:

id | name
-----------
1  | Peter
2  | Andrea
3  | Bob

The resulting list would be

petrandbo

The order is not important but could be alphabetical


texts: How to reset SQL Server Configuration Manager Windows To Original Windows Configuration (as if it had been newly installed)

texts:
I have been looking at a lot of articles, and still cannot come up with an answer to the following. I am running Windows 10 workstation, and have installed SQL Server 2008 R2 Developer.

This problem refers to SQL Server Configuration Manager, not SQL Server Management console.

Somehow, I managed to configure the view of SQL Server Configuration Manager (see attached picture), so that only Protocols for MSSQLSERVER window appears. I want to see the full series of views from the root.

How do I reset things back to the default? By this question, I mean that I can no longer see all the tab choices.

Now, the windows on which the application opens is the Protocols for MSSQLSERVER window. I somehow advanced to a new window using the open new window directive. When I went to close the application, I was prompted with a prompt I didn't understand, and when I saved and restarted the application found I could no longer see all the choices that exist above the Protocols window.

I tried re-installing, and that did not work.


texts: Sql server 2014: backup log to 2 destinations

texts:
We want a single job to back up log to azure and file system every time.

So we can log ship from file system and from azure at the same time.

I seem to recall that the redgate sql backup tool has a "backup and copy to..." feature. In sql natively yet?

Can this be done?


texts: Does the concept of candidate key exist only in theory?

texts:
I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate key in any of the SQL database management systems, say SQL Server, Postgres, MySQL, Oracle etc.?

Is there any reserved keyword for designating column(s) as a candidate key like PRIMARY KEY or UNIQUE in case of primary key column and unique column?

I feel UNIQUE constraint itself provides implementation of the candidate key concept. I don't see any practical value of having a separate CANDIDATE KEY keyword. Is it so?


texts: Not able to access a database on shard mongodb cluster

texts:
We have a mongoDB cluster with 2 shards, 3 config servers and 5 mongos instances.
We create new database for each day and drop database(s) that are older than 10 days.

On any one mongos, when I list all databases, there is one database shown that is occupying 40 GB space. This database should have been removed by timely scripts, but that did not happen.

When I try to drop this database, error Occurs as - "database does not exist".
Find below console output of step carried out -

    mongos> show dbs
    dailyDB_A23    37.890GB
    mongos> use dailyDB_A23
    switched to db dailyDB_A23
    mongos> show collections
    mongos> db.dropDatabase()
    { "info" : "database does not exist", "ok" : 1 }
    mongos>

On using this database and listing all collections, there is nothing shown on console.
Plus when I tried taking a dump of this database, nothing is backed up and command exits withing 2 to 3 seconds.

On connecting to PRIMARY of each replica set in shard, this database is visible (ran 'show dbs' after connecting to mongo shell) and files are also present in to data directory.

Kindly help me out here. If this database does not exist (as per error on dropping) why it is showing it in list with size of ~ 40 GB?

Thanks and Regards,

Vibhav


texts: Are SQL Server temporary tables (prefixed with #) supported in LocalDB instances?


texts: How to Develop Union Reports from Single Tenant Databases?

texts:
We have a database for each customer, for over 700 databases. All the schemas are the same. Now we want to conduct Reporting for : All Databases Customer Analysis. Many db professionals prefer single tenant databases. All database reside on the same server instance.
Single Tenant Database Discussion

CREATE TABLE CustomerOne.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL default(1),
    [QuantityBought] [int] NULL,
)

Does anyone know of any other ideas not listed below, which would work?
Our idea is to either create large Views, however, View performance is slow. The second option is stored procedures.

(1)

CREATE VIEW Reportingdb.dbo.CustomerTransaction
as
SELECT
    [Customertransactionid]
    ,[Customerid]
    ,[Quantitybought]
FROM [CustomerOne].[dbo].[customertransaction]

UNION ALL

SELECT
    [Customertransactionid]
    ,[Customerid]
    ,[Quantitybought]
FROM [CustomerTwo].[dbo].[customertransaction]

2) We can also create a stored procedure. Stored Procedure which will insert into this large table. However there will be a time lag between runs, we want instantaneous data.

insert into ReportingDB.[dbo].[customertransaction]
select * from CustomerOne.[dbo].[customertransaction]
UNION ALL
select * from CustomerTwo.[dbo].[customertransaction]

SQL How to Convert Single Tenant Databases to a Multitenant Database


texts: Availability Group Read Only Routing Not Working for SQL Logins

texts:
We have following config of Availability Group:

server1.domain.com - primary
server2.domain.com - sync secondary
server3.domain.com - async secondary

sql-listener.domain.com - availability group listener DNS name

Recently we have setup Read Only Routing (ROR)
Read Only Routing URLs:

server1 - TCP://server1.domain.com:1433
server2 - TCP://server2.domain.com:1433
server3 - TCP://server3.domain.com:1433

Read Only Routing Lists:

server1: server2, server3, server1
server2: server1, server3, server2
server3: server2, server1, server3

It worked fine when tested in SSMS using Windows Authentication Login (Domain\MyLogin) with Options >> Additional Connection Parameters tab -> ApplicationIntent=ReadOnly

While server1 is primary, I connect in SSMS to AG Listener (sql-listener.domain.com) using above parameter and run select @@servername, and it shows server 2, which means ROR works

Issues/Questions:

When I try to connect in SSMS to AG Listener using SQL Authentication Login (SQLAuthLogin), and ApplicationIntent=ReadOnly parameter, and then run select @@servername, it shows me server1

>>> So in my case, ROR does not work with SQL Authentication Login
is there any way to fix this ?

add: double checked SQLAuthLogin's SID on server1 and server2 - SID is the same, SQLAuthLogin can login to both servers and query MyDatabase just fine


texts: User permissions for Microsoft SQL Server

texts: Please advice to give permission for a SQL server user. Conditions are, this user must be able to create,delete,modify,drop the tables/view/procedure on user default schema(not dbo) not in any other schema. I have given db_ddladmin privilege but here the user will be able to perform any action on other schema's as well.


texts:
A common issue, just not sure on the keywords.

I have a table of assessments (14k+ records).

Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done').

Most records (13k) are 'done'... so doing a:

WHERE state != 'done'

It should ignore most records, however MySQL ignores the index (presumably due to the low cardinality).

I could USE/FORCE INDEX, which kind of helps, but was wondering if this is the best approach.

One option is to add a datetime for when it is done, but not really used at the moment... but would have high cardinality (with NULL for those 1k records).


texts: Does a unique non-clustered index help with locks?

texts: I was wondering if SQL Server behaves differently with a unique non-clustered index versus a non-unique clustered index when it comes to KEY locks.


texts: UTF8 Trouble while migrating from MSSQL to MySQL with MySQL Workbench

texts:
I am tasked with migrating part of our database from an on-site MSSQL server to an Amazon RDS MySQL. This will be my first attempt with a database migration, so I am doing a dry run from our MSSQL server to a locally installed instance of MySQL using MySQL Workbench. Local install MySQL Workbench version 6.3. MySQL Server version 5.7

Error generated: Inserting Data: Incorrect string value: '\xA0\xA0 I-D...' for column 'Address1' at row 77 while performing bulk data transfer.

This happens twice in the same table (two different rows). All other tables are successful.

Symptoms:

On the Object Migration -> Manual Editing -> Column Mappings screen, I see a list of migration messages/warnings.

On columns that go from source VARCHAR/NVARCHAR to VARCHAR, I get "Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci. Same for Char to Char.
On columns that go from source BIT to TINYINT(1), I get "Source column type BIT was migrated to TINYINT(1)

Settings:

Source Selection  is MSSQL Server, ODBC (Free TDS)

Advanced tab has "Driver sends Unicode data as UTF-8" checked. Required for Free TDS

Target Selection is local MySQL. Connection method is Standard(TCP/IP)

I have read THIS article which seems quite promising, but I am not sure how to implement step 3 as it is a migration and when I get to the "Create Schemas" step, it says it will drop the schema and recreate anew if it already exists.

All the indexes on this database are on small columns, so the 3-bit to 4-bit utf8 will not be an issue for indexes.
As far as "Modify connection, client, and server character sets" to be utf8mb4, I cannot even find the file to modify. I went to MySQL Server 5.7/my-default.ini and changed it to include

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

as requested, but my variables still look like the below picture, so that is obviously not the right file. Plus, will I actually have access to this on the Amazon RDS instance?

Any thoughts?


texts:
I'm getting a weird message from phpmyadmin saying:

#2006 - MySQL server has gone away

What I found online is that I should increase the size of max_allowed_packet witch should be in the my.conf file witch is located at /etc/my.cnf. Untill now everything is fine, but the problem is that this file does not contain what I need.

Here is the content of the .cnf file on my server:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Where should I look in order to increase the size of the packet? I'm new in the linux world and I'm lost.


texts: Oracle restrict user to use USERS tablespace

texts:
I have a schema 'TEST' that schema has own tablespace 'TEST'. But when I create index on USERS tablespace, it created. How can I restrict to use USERS tablespace in ORACLE database?

CREATE UNIQUE INDEX "TEST"."INDEX1" ON "TEST"."TESTTABLE" ("TESTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

It success, I want TEST user can't create any object on USERS tablespace. It has own TEST tablespace. Is there any configuration? Should user use TABLESPACE "TEST" instead of TABLESPACE "USERS" ?


texts: ERP System Blocking Transaction - Not Sure If There Is A Fix

texts:
I am experiencing a problem with our ERP system that is causing my company a huge headache and I am not sure if there is anything that I can do to try to resolve.
Essentially, when someone at our firm approves an expense report from an employee it causes the entire Expense Report Application to hang.  The following is the query:
SET TRANSACTION isolation level READ uncommitted;

SELECT statuscode,
       completion,
       completionstatuscode,
       reviewaction,
       reject,
       description,
       duration,
       cfgapprovaltype.autoapprove,
       cfgapprovaltype.concurrentapprovals,
       cfgapprovaltype.autoapprovedetaillines,
       cfgapprovaltype.approvallevel
FROM   cfgapprovaltype
       LEFT OUTER JOIN approvalworkflow
                    ON approvalworkflow.itemtype_uid =
                       '63DF2CF7B1224152BDA9EA37ECF16F4D'
                       AND approvalworkflow.step = '3'
                       AND active = 'Y'
WHERE  cfgapprovaltype.itemtype_uid = '63DF2CF7B1224152BDA9EA37ECF16F4D'

And the following is the IO when I run it separately:

(1 row(s) affected)
Table 'ApprovalWorkflow'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CFGApprovalType'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

However, when I run sp_WhoIsActive I see that it has been running for an fifteen minutes to an hour.  It has a sleeping status.  Also, it is part of transaction.
This query is really basic. I am wondering if there is anything that I can do because it seems that the application (ERP) maybe opens a transaction and doesn't commit it and then that query is stuck holding onto those resources.
Also, in sp_WhoIsActive returns that it has read close to 25K pages.


texts: Code First Migrations with an existing database - How do I make sure migration won't affect the existing data?

texts:
I am referring https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/migrations/existing-database

I am using Entity frame work Code First Migrations for an existing database.

I get the below error

  Error Number:2714,State:6,Class:16
  There is already an object named 'MyGroups' in the database.

I can comment out the table creation script, but is it right way? Why it is not detecting existence to table and skip the creation?

In production we may not have any database. In such cases it shall create the database. Basically how do I automate DB creation based on environments? or how do I make sure existing data not getting affected by migration.


texts: MariaDB Grants for set session variables

texts:
On MariaDB, what grants are needed for set session variables?

For set global are the SUPER grants. But can any client without SUPER grants set his own session variables?


texts:
I am experiencing sporadic deadlock behaviour when my application tries to execute any DROP TABLE statement against my database. I tried capturing the deadlock graph and this is the output from deadlock xml

<deadlock>
     <victim-list>
      <victimProcess id="process2bddc28" />
     </victim-list>
     <process-list>
      <process id="process2bddc28" taskpriority="0" logused="0" waitresource="KEY: 6:281474978938880 (85262e079cd2)" waittime="1900" ownerId="76493296" transactionname="user_transaction" lasttranstarted="2016-02-22T05:58:15.867" XDES="0x621cb38e0" lockMode="S" schedulerid="6" kpid="10748" status="suspended" spid="119" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2016-02-22T05:58:16.080" lastbatchcompleted="2016-02-22T05:58:16.080" lastattention="1900-01-01T00:00:00.080" hostname="DEV01" hostpid="13420" isolationlevel="read committed (2)" xactid="76493296" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
       <executionStack>
        <frame procname="mssqlsystemresource.sys.sp_columns_100" line="138" stmtstart="12020" stmtend="17532" sqlhandle="0x0300ff7f78a964e13656e600e7a5000001000000000000000000000000000000000000000000000000000000">
      </frame>
       </executionStack>
       <inputbuf>Proc [Database Id = 32767 Object Id = -513496712]</inputbuf>
      </process>
      <process id="process45c8ca8" taskpriority="0" logused="6748" waitresource="KEY: 6:562949956108288 (5db581fe7a0b)" waittime="1900" ownerId="76493663" transactionname="user_transaction" lasttranstarted="2016-02-22T05:58:16.087" XDES="0x16aeded90" lockMode="X" schedulerid="1" kpid="18268" status="suspended" spid="129" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-02-22T05:58:16.090" lastbatchcompleted="2016-02-22T05:58:16.090" lastattention="1900-01-01T00:00:00.090" hostname="DEV01" hostpid="20128"  isolationlevel="read committed (2)" xactid="76493663" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
       <executionStack>
        <frame procname="adhoc" line="1" sqlhandle="0x01000600702bfd0bd083047f0600000000000000000000000000000000000000000000000000000000000000">
    DROP TABLE "dbo"."USER_TABLE_1"    </frame>
       </executionStack>
       <inputbuf>
    DROP TABLE "dbo"."USER_TABLE_1"   </inputbuf>
      </process>
     </process-list>
     <resource-list>
      <keylock hobtid="281474978938880" dbid="6" objectname="DEV_01.sys.sysschobjs" indexname="clst" id="lock35af27b80" mode="X" associatedObjectId="281474978938880">
       <owner-list><owner id="process45c8ca8" mode="X" /></owner-list>
       <waiter-list><waiter id="process2bddc28" mode="S" requestType="wait" /></waiter-list>
       </keylock>
      <keylock hobtid="562949956108288" dbid="6" objectname="DEV_01.sys.syscolpars" indexname="nc" id="lock453c34600" mode="U" associatedObjectId="562949956108288">
       <owner-list><owner id="process2bddc28" mode="S" /></owner-list>
       <waiter-list><waiter id="process45c8ca8" mode="X" requestType="convert" /></waiter-list>
      </keylock>
     </resource-list>
    </deadlock>

This behaviour was not seen in earlier versions of SQL Server. Any thoughts on this?


texts: MySQL: Securing Access using Stored Procedures

texts:
I'm new to using MySQL. I'd like to use stored procedures to help secure my database when accessing it from a web service (i.e., grant only proc execute to the web service account).

What permissions do I need to grant so the web service account does not have table access but the stored procedure the service executes does?


texts:
I am trying to run the below Pl/SQL statements on my Oracle DB 11g on Linux box, but getting the error "missing keyword". Please let me know if I miss anything.

BEGIN

FOR X in (select * from all_tables where owner in ('owner1', 'owner2')) LOOP

EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||X.owner||'.'||X.table_name|| 'to myuser';

END LOOP;

end;
/

Error starting at line : 1 in command -
BEGIN
FOR X in (select * from all_tables where owner in ('TESTDTA', 'TESTCTL')) LOOP
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||X.owner||'.'||X.table_name|| 'to ARCTOOLS212';
END LOOP;

end;

Error report

  ORA-00905: missing keyword ORA-06512: at line 3

  00000 -  "missing keyword"

  *Cause:

  *Action:


texts: Proper database structure for storing multiple foreign keys

texts:
I am using MySQL 5.7.25 and need to store multiple foreign keys for multiple columns.

Scheme of my table:

CREATE TABLE `tours` (
  `id` int(10) UNSIGNED NOT NULL,
  `countries` text NOT NULL,
  `themes` text NOT NULL,
  `code` char(5) NOT NULL,
  `type` tinyint(3) UNSIGNED NOT NULL,
  `difficulty` int(10) UNSIGNED NOT NULL,
  `paceLevel` int(10) UNSIGNED NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The countries column should store country IDs like: 10,12,14.
Storing data that way forces me to use LIKE query which I have to avoid for better performance.
How you would design this kind of tables?

UPDATE:

I created another table to store meta table of tours:

CREATE TABLE `tour_meta` (
  `id` int(10) UNSIGNED NOT NULL,
  `metakey` int(10) UNSIGNED NOT NULL,
  `metaval` int(10) UNSIGNED NOT NULL,
  `tour_id` int(10) UNSIGNED NOT NULL,
  `status` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

metakey -> is something predefined, like 4 for countries,
metaval -> is real data id, for example country id

But it will be useless when I need to filter the data like:

SELECT ... WHERE COUNTRY = 'Brazil' AND THEME = 'Discovery'

Brazil ID = 5, Discovery ID = 3

SELECT *
FROM tour_meta
WHERE metakey = 4 -- (predefined meta key for country data)
  AND metaval = 5 -- (country ID)
  AND metakey = 5 -- (predefined meta key for theme data)
  AND metaval = 3 -- (theme ID)

UPDATE 2:

@Akina helped me a lot and the following query solved my problem:

SELECT tour_id, COUNT(tour_id) as counted from metas WHERE (metakey, metaval) IN ((3, 5), (4, 6)) group by tour_id having counted = 2

But now I am very confused about how to count different meta keys.

Hypothetically, let's say we have the following resultset:

|--------|-------------|
|tour_id |  countries  |
|--------|-------------|
|   1    | 15, 18, 23  |
|--------|-------------|
|   2    | 19, 23, 25  |
|--------|-------------|

How can I get the following resultset?

|-----------|---------|
|country_id |  count  |
|-----------|---------|
|    15     |   1     |
|-----------|---------|
|    18     |   1     |
|-----------|---------|
|    19     |   1     |
|-----------|---------|
|    23     |   2     |
|-----------|---------|
|    25     |   1     |
|-----------|---------|


texts:
I had an SSRS service account password expire. This is on an Azure Windows server.

I've tried the following:

Updating the password caused "The report server can’t access or use the encryption key"
Attempting to delete the key via all the normal methods fails.
Manually deleting the key (keys table) doesn't give me an option to re-create a new one.

The only solution I can think of at this point is un-installing and reinstalling SSRS.

Is there a better way, or is this approach even likely to work?


texts: Grant a database role in current database with permission to execute a SP in msdb

texts:
I have created a role in my current SQL Server 2012 database with db_datareader, db_datawriter, and execute permissions. A procedure in the current db calls sp_start_job in msdb.

How can I grant access to my database role to execute the procedure in msdb?

I tried executing the procedure in my current database which calls sp_start_job as owner and the user who is a member the user defined role is still not able to execute the procedure.

I have made the server role public for the login, and mapped it to msdb database, but I'm not able to grant permission. The command I'm trying to execute is:

GRANT EXECUTE ON OBJECT::[msdb].[dbo].[sp_start_job] TO [db_executor]

When I execute from msdb, the error I get is:

  Cannot find the user 'db_executor', because it does not exist or you do not have permission'.

When I execute from the database in which the user defined role, db_executor is created, it throws this error:

  You can only grant or revoke permissions on objects in the current database.

I don't want to add users to SQLAgentOperatorRole. The idea is to group all users in one role within the app's database and the members of this role should be able to run a procedure in the database which calls sp_start_job.


texts:
My SQL Server version at home is 661 and at my school's is 655, so every time I try to open my web's SQL Server database at school it says there is an error:

So I thought that uploading the SQL Server version at school will fix it (or downgrading the SQL Server version in my home).

On both computers there is Microsoft Visual Web Developer 2010

How do I upgrade the SQL Server version?


texts: Select where table and column name are from another table

texts:
I know I need dynamic sql, but haven't gotten this to work:

I want to loop through a table and for each row, return the value of the field that is located by two columns for that row.

Master table:

Tableabc        Columnabc
Tabledef        Columndef
Tablexyz        Columnxyz

And I'm looking to return basically

Select [column] from [table] where column = 'desiredtext'

Where [column] and [table] are from the same row of another table, looping through all rows of the master table.

So, I'm basically searching hundreds of tables in my database for one specific textphrase, where the column and table names are not always the same, however they are readily identified by the query that results in the master table being created.

The goal being to search specific columns in the couple hundred tables that might have the 'desiredtext' I'm looking for, rather than search the entire database of thousands of tables.

What other info can I provide to help someone help me?

Thanks,

Andy


texts: Why can't I use NEWSEQUENTIALID() as the default value for my column?

texts:
I'm trying to create a  table in Management Studio and after reading about the new (from SQL 2005 on) NEWSEQUENTIALID() function, thought I'd give it a go.

This is what I'm doing:

But it's not letting me. The error message I get is:

'FormTemplate (Forms)' table - Error validating the default for column 'FormTemplateId'.

Am I missing a trick here? I'm definitely running SQL Server 2008 R2.


texts: 2 databases in sql server 2005 "Production Server"

texts:
Can 2 databases decrease the performance in a SQL Server 2005 production server?

Server specs:

Dell Power edge 2950, 2 Quad core Xeon, 16 GB of RAM, 3x73 GB SATA hard drive hot plug, Windows Server 2003 R2 Standard Edition 64 bits and SQL Server 2005.

Our accounting/service software company asked me to make a second database "test", basically is a copy of the live database which is 30Gb. I don't have enough disk space on my server. The live database is on a second partition and 42.2 free space. I have setup SQL Server to use a max of 13Gb RAM and lock pages in memory, leave the rest of memory ram to the OS. I'm concern that to have a copy of the live database in the same server will have a high degradation in performance and to start dumping memory.


texts:
I have a subquery that gets the DATEDIFF of two dates. However the first case always returns null yet the actual sub query returns dates?!

CASE
WHEN [date1] > [date2]
THEN DATEDIFF(DD,getdate(),date1)
WHEN date1 < date2
THEN DATEDIFF(DD,getdate(),date2)
END[date diff]

FROM(

SELECT
licext.new_licenceId,
CASE
WHEN CONVERT(time,licext.new_EmergencyLicenceExpiryDate) like '%23:00:00.000%'
THEN DATEADD(dd,1,CONVERT(date,licext.new_EmergencyLicenceExpiryDate))
ELSE CONVERT(date,licext.new_EmergencyLicenceExpiryDate)
END [date1], --EM Code

CASE
WHEN CONVERT(time,licext.new_standardLicenceExpiryDate) like '%23:00:00.000%'
THEN DATEADD(dd,1,CONVERT(date,licext.new_standardLicenceExpiryDate))
ELSE CONVERT(date,licext.new_standardLicenceExpiryDate)
END [date2]  --FRL
from new_licenceExtensionBase licext
)QUERY

The reason the second cases are on there is because the database I am working with stores datetime in a really weird way, ie)If it has 23:00 at the end it actually means tomorrows date?! (This is a Microsoft CRM Database) -But that's a whole different matter.

EDIT:

It did have an ELSE clause which worked fine for date2 being bigger than date1, because that was being evaluated. For Example, the following code works fine for getting the difference between today and date2:

CASE
WHEN [date1] > [date2]
THEN DATEDIFF(DD,getdate(),date1)
ELSE DATEDIFF(DD,getdate(),date2)
END[date diff]

EDIT 2:

I've just done a couple of tests:
Using a select to return the dates works fine. -They return as expected.

Test 2: I've added a third WHEN to the query to say that when a date is null, return 1990-01-01..However it still returns null, Is my whole case being skipped over?


texts: Split value from a date range in to individual values per date

texts:
I am modifying a SSRS sales report for my customer. I have already calculated the number of days and based on that the daily value. In the software you set the start and end date of the agreement which are visible on the report.
My customer wants to split the agreement value in to individual date values. Is there a way how I can split the date range in to individual dates? Then I would just group all the individual dates values from all agreements in to one daily total.
I am using SQL Server 2017 and Visual Studio 2013


texts:
I'm using Postgres 9.4. I'm trying to dump a database (both the schema and the actual data) with the PostGIS extensions (set up using CREATE EXTENSION):

$ pg_dump prescribing -U prescribing -h localhost -Fc > prescribing.dump
Password:

But when I type in the db password, I see this:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for schema topology
pg_dump: [archiver (db)] query was: LOCK TABLE topology.topology IN ACCESS SHARE MODE

How can I dump this database?

UPDATE: it says these tables are owned by postgres:

 public | frontend_sha                               | table             | prescribing
 public | geography_columns                          | view              | postgres
 public | geometry_columns                           | view              | postgres
 public | pg_stat_statements                         | view              | postgres
 public | raster_columns                             | view              | postgres
 public | raster_overviews                           | view              | postgres
 public | spatial_ref_sys                            | table             | postgres
 public | vw_chemical_summary_by_ccg                 | materialized view | prescribing

Maybe I could just dump the other tables, and import them into another database set up in the same way? Or maybe I could run pg_dump as a superuser? But if I try pg_dump prescribing then it still asks me for a password, and I don't know what that should be.


texts:
I have 12 GB MySQL InnoDB Database with around 800 queries per second. No sooner I start 'mysqldump' over it to make backup I start getting '503' on my website.

How do I make backup of my live database without affecting the site performance?


texts:
Running DBCC Checktable on one of my tables causes several errors:

  Msg 2570, Level 16, State 3, Line 1 errors,  Page (1:442), slot 1 in
  object ID 565577053, index ID 0, partition ID 72057594041270272, alloc
  unit ID 72057594046906368 (type "In-row data"). Column "XXXX" value is
  out of range for data type "decimal".  Update column to a legal value.

Column XXXX is Decimal (18,4)

I've done some investigation suggested by:
https://support.microsoft.com/en-us/help/923247/troubleshooting-dbcc-error-2570-in-sql-server-2005-and-later-versions

1)

SELECT XXXX FROM Table
WHERE XXXX > 99999999999999.9999
OR XXXX < -99999999999999.9999

This is not returning any rows

2)

DBCC TRACEON ( 3604 )
DBCC PAGE ( Database, 1 , 442, 3 )

I found entry with XXXX = INVALID COLUMN VALUE
I found primary key values for it

And when I run:

Select XXXX from Table where ID = ValueFound

It returns -0.4500 - valid value for Decimal (18,4).

Is this value correct or is this some random value?

Do I understand it correctly - the only way to fix this is to provide correct values for given fieds?


texts:
I have access to a database in read uncommitted mode and need to do maintenance on a table in an operative environment. I need to acquire an exclusive lock on a table, do a job, then release the lock.
During this maintenance even read queries should not get uncommitted data back.
This question and answer looks promising. I do:
begin transaction;
SELECT TOP (1) 1 FROM a WITH (TABLOCK);

but then, a SIX lock is acquired on table a. This however does not lock the table from select queries. How can I acquire Sch-M lock on the table? At most I can get the Sch-S lock with:
SELECT TOP (1) * FROM a WITH (TABLOCK);

this still does not block select queries on the table.


texts: Powershell SQL Server High Availability PrimaryReplicaServerName - inconsistent results

texts:
I developed a simple PowerShell script to list which High Availability replica is the current Primary.  Seemed to work.  Did a manual failover to test it, but results become inconsistent, often describing both replicas as Secondary.
Simple illustration code:

Import-Module -Name SQLPS -DisableNameChecking
"Server1:"
cd \sql\Server1\WSSAPPS\AvailabilityGroups
gci | select Name, LocalReplicaRole, PrimaryReplicaServerName
""
"Server2:"
cd \sql\Server2\WSSAPPS\AvailabilityGroups
gci | select Name, LocalReplicaRole, PrimaryReplicaServerName

If Server2 is Primary AND I run the code on the Server2, the results seem fine:

Server1:

Name                 LocalReplicaRole      PrimaryReplicaServerName
----                 ----------------      ------------------------
SP-STAGE-APPS               Secondary      Server2\WSSAPPS

Server2:

SP-STAGE-APPS                 Primary      Server2\WSSAPPS

But if I run exactly the same code on Server1 (cut and paste) with Server2 still as Primary I get different results:

Server1:

Name                 LocalReplicaRole      PrimaryReplicaServerName
----                 ----------------      ------------------------
SP-STAGE-APPS              Secondary       Server2\WSSAPPS

Server2:
SP-STAGE-APPS              Secondary       Server2\WSSAPPS

This is now reporting BOTH replicas have the role Secondary!
Can anyone explain why these results should differ?
Please don't say 'Just always run it on the primary then' The whole point is to give people the information as to which instances are primary!  I've got 11 AGs on 12 replicas to monitor.
Once I understand what's happening I can mod my main script code accordingly.
Thanks


texts: Tuple calculus: unsure if my reasoning is correct

texts:
I'm currently working through a tuple calculus question and I ran into a situation where I have two answers that seem correct to me and I am unsure if my logic is wrong or if they are both correct answers (and if so, is one more correct than the other).

I am trying to find the sid of the store that supplies every item. The schemas are:

Stores(sid: integer, sname: string)

Items(iid: integer, iname: string)

Supplies(sid: integer, iid: integer, price: integer)

What I came up with was:

{ P | ∃ S1 ∈ Supplies(P.sid = S1.sid ∧ ∀ i ∈ Items(∃ S2 ∈ Supplies (S2.iid = i.iid ∧ S2.sid = S1.sid)))}

{ P | ∃ S1 ∈ Supplies(P.sid = S1.sid ∧ ∀ i ∈ Items(S1.iid = i.iid))}

My reasoning is that I am looking for the sid where for all Items there is an entry in the Supplies table that corresponds to the iid. The only real difference between my answers is that the second one doesn't use a second separate Supplies table which allows us to cut out the additional S2.sid = S1.sid. My textbook has a similar question to this and has an answer similar to 1 but my reasoning gets me the second answer.


texts:
I had created the database with the name globacom

  create database globacom;

and grant all the permissions as

    grant all on globacom.* to 'globacom'@'localhost' identified by 'globacom';

after this I have looged in using this credentials

       mysql -u globacom -pglobacom

when I import the .sql file  using this

    source E:\ravi_globacom\globacom_data4.sql

I am getting an error as

 ERROR 1044 (42000): Access denied for user 'globacom'@'localhost' to database 'avi_globacomglobacom_data4.sql'

please anyone can help me.


texts: Set var="NEW.this_word_column" in a trigger function if "word" is present in a column name

texts:
Context

I'm using PostgreSQL 10.12.

I want to be able to fire a trigger function on some specific tables with geographic data.

This trigger function is designed to create a PotGIS point into a column geom from lat and lon values.
I want to apply this same trigger function over several tables each time a new row is inserted.
The naming of columns holding latitude and longitude values is not consistent across tables but it follows a pattern...

What I am 100% sure is that:
 1. there are always two columns containing latitude and longitude values,
 2. the name of theses columns are not always the same in the
    different tables (otherwise it would have been too simple), but the word 'latitude' and the word 'longitude'
    always appear in their name and never appear in other column names.

E.g. in table A:

  _loc_longitude_ and _loc_latitude_

and in table B:

  _building_longitude_center and _building_latitude_center

for example (+ many others).

The trigger function is as follows:

CREATE OR REPLACE FUNCTION make_point_with_latlon()
  RETURNS trigger AS
  $$
  DECLARE
      varlon := NULL;
      varlat := NULL;
  BEGIN
      IF to_jsonb(NEW) SIMILAR TO '.*longitude.*' THEN
          varlon := NEW.the_column_whith_longitude_in_its_name;
      END IF;
      IF to_jsonb(NEW) SIMILAR TO '.*latitude.*' THEN
          varlat := NEW.the_column_whith_latitude_in_its_name;
      END IF;
      NEW.geom = ST_MakePoint(varlon, varlat);
  END
  $$
LANGUAGE 'plpgsql';

With the following triggers:

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table1
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table2
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table3
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

-- (...and many more other tables that need the same trigger).

I don't know how to search for the pattern .*latitude.* in the columns names passed to the function, and how to get their value into two variables that I can use to build the point with.
And my googling skills for searching these kind of stuff are far from perfect, hence returning noisy results.

Question

Is there any possibility to do that?


texts: How To Manage Random Data Without Repitition?

texts:
We have a database design problem of a Learning System.
Please can somebody help us ?

These are the requirements :

A person can register for a module which has "n" number of questions.
The person gives an exercise of that module of "n" questions every day.
The questions of that person should not be repeated.
If the answer of the question is wrong, the person will correct it.

Now there is a issue. Consider the entries as :

RegistrationTable

RegNo StudNo ModNo QPerModule
R0    S0         1          2
R1    S0         2          2

ExcerciseTable

ExcerciseID  RegID  ExcerciseDate
E0           R0     1/7/2015
E1           R0     2/7/2015
E2           R1     1/7/2015
E3           R1     2/7/2015

AttemptedQuestionsTable

QAttemptDate ExerciseID QuestionID Answer   Status
1/7          E0         1          "äbcdef" "Correct"
1/7          E0         2          "agsdgh" "Wrong"
2/7          E1         3          "äsdasd" "Correct"
2/7          E1         2          "hsdahj" "-----"
2/7          E0         2          "ajsklj" "----"
2/7          E2         2          "fhasgd" "bkhijio"

Question is :

Here in this table, the entries

2/7   E1  2  "hsdahj" "-----"
2/7   E0  2  "ajsklj" "----"

Are not valid as the Questions are getting repeated for the same person on the same date. The two exercises are given by the same person.

But the entries

2/7   E0  2  "ajsklj"  "----"
2/7   E2  2  "fhasgd"  "bkhijio"

Are allowed as the two diff exercises are given by two diff people.

What design change should be made to solve this problem ?


texts: Benchmarking MyISAM vs InnoDB using Sysbench on MariaDB-10.0.14

texts:
I performed a "read" benchmark on MariaDB-10.0.14 db server using sysbench to compare performance between InnoDB and MyISAM on a single. Below are the sysbench commands i used:

Create test table in subtest database with 1,000,000 records

InnoDB engine:

#sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=testdb --mysql-password=testpw --mysql-socket=/var/lib/mysql/mysql.sock prepare

MyISAM engine:

#sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-db=sbread --mysql-user=testdb --mysql-password=testpw --mysql-socket=/var/lib/mysql/mysql.sock prepare

Perform Read Test:

InnoDB engine:

sysbench --num-threads=1 --max-requests=500000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-db=sbtest --mysql-user=testdb --mysql-password=testpw run

MyISAM engine:

sysbench --num-threads=1 --max-requests=500000 --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-db=sbread --mysql-user=testdb --mysql-password=testpw run

Test Results:

InnoDB engine:
Thread                   Total Time                         Transactions
1                        3823.6033s                         500000 (130.77 per sec.)

MyISAM engine:
Thread                   Total Time                          Transactions
1                        2148.4370s                          500000 (232.73 per sec.)

I have two questions:

As seen from the results, the total time for MyISAM seems less, which is what i was expecting. But i do not understand why the "transaction per second"  of MyISAM performs worse than that of InnoDB. Can somebody explain that to me why this is?
I tried to increase the --num-threads to 2 (to perform multiple concurrency) but with MyISAM, i kept getting an error "failed: 1062 Duplicate entry for primary key". I understand that MyISAM does not support multiple threads, would this be the reason? Insight on this too would be appreciated.


texts:
We have a number of DBs where EXECUTE privs are required on a SP by SP basic. We have other DBs where we don't need to do this at all.

In the latter case, I understand that this is a blanket GRANT, but is there anyway to tell up front whether the blanket grant has been applied? For the blanket grant I don't see any users set up under the Permissions page in SSMS for any of the SPs in question and yet it just works.

If there are no users here I don't know whether I need to specify users or assume a blanket grant.


texts: How do I change where database diagram relationship lines attach in MySQL Workbench?

texts:
Sometimes, MySQL Workbench makes some strange choices about where table relationship lines get attached.  For example, two vertical lines would be better in the following:

For a while, these boxes (representing tables) had two vertical lines between them, and the crossover happened when other boxes were added elsewhere in the diagram, and things got moved around a bit.

Is there a way to say something like "this line should go to this attachment point"?


texts: Shared entities across multiple databases

texts:
I'm currently working on an application with a master database and individual user databases where all user input data resides. These individual databases are exactly the same. master connects the dots and links the users to their database.

Today, we're looking into sharing a specific user entity in the way that other users should be able to access the other.

Say we have the table comments. Each comment have a primary key that may or may not exist in other user databases. What I'm thinking is to have a relation tracking table in master that basically says "this user have access to this user's comments".

The conceptual problem I'm facing is the union part, where multiple users have access to one users comments. Given that database names are pattern based user_id.comments. I just don't see how it would be possible to union all comments across multiple databases.

Scenario;

master.comments_relation
user_1   user_2
user_1   user_3
user_2   user_1

user_1.comments
1   "comment from user 1"
2   "another comment from user 1"

user_2.comments
1   "comment from user 2"
2   "another comment from user 2"

user_3.comments
1   "comment from user 3"
2   "another comment from user 3"

What I want to be able to output now as "user_1"
1   "comment from user 1"
1   "comment from user 2"
2   "another comment from user 1"
2   "another comment from user 2"

This is a totally new thing for me and I have no idea on how this is supposed to work, and the primary keys are conflicting. Is it better to move all comments to master database, and skip the multi-database approach, having shared entities in the same database?


texts:
I want to create MySQL replication using GTID.

According to MySQL Manual I need the following settings in my.cnf (for the master)

server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
gtid_mode = ON
log-slave-updates
enforce-gtid-consistency

I have also seen this guide, which suggests the following settings in my.cnf (for master):

log-bin = mysql-bin
server-id = 1
relay-log = relay-log-slave
gtid-mode =ON
enforce-gtid-consistency
binlog_format = MIXED
log_slave_updates

Questions:

I have noticed that underscore and dash are being used loosely in the documents, for example when I installed MySQL on my computer, the variable name is log_bin but in MySQL manual they are using log-bin. Are _ and - interchangeable?
In the second tutorial that I have linked they are setting these two values in addition to setting values from MySQL manual: relay-log = relay-log-slave and relay-log = relay-log-slave. What are these two settings? And do I need them?


texts:
Why is the country_rank value 1 for each row, which I assume is due to STRCMP returing false everytime. I think @current_country is not set correctly but dont know why.

Table:

sn country city
1  AAA     aaa
2  BBB     bbb
3  AAA     ccc

Output Expected:

country city country_rank
AAA     ccc  1
AAA     aaa  2
BBB     bbb  1

Query:

SELECT country,city,
@country_rank := IF(STRCMP(country,@current_country) = 0,  @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM table
ORDER BY country,sn  DESC

The query is supposed to rank the cities in each country as per sn.
MYSQL 5.6


texts: Agent service account shows as not clustered

texts:
One of the SQL Server 2008 R2 instances I administer is running on a two node Windows Cluster (Windows Server 2008 R2 Enterprise)
When I run the DMV sys.dm_server_services, SQL Server Service Account shows as Y under the 'is_clustered' column.
However, Agent Service Account is an 'N' for the same field 'is_clustered'. Is it something that I should be worried about and what might be causing this? Does this mean that Agent will not fail over?
Thank you


texts: Pre-sorting postgres table instead of running CLUSTER

texts: Does pre-sorting a csv by a designated column and then inserting the data into postgres have the same effect as not pre-sorting, importing the data, creating the index and then CLUSTERing the table using that index?


texts: Conditionally throwing based on pre-fetched result stored in a temp table inside a function

texts:
I need to apply some validation rules on set of a data I've fetched for a given account in order of using postgres v9.6:

Fetch all rows applicable to account.
For each validation rule, query subset of in step 1, and throw where condition isn't met.

And this is how I am doing it:

CREATE OR REPLACE FUNCTION public.my_function(account_id character varying)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
BEGIN

    -- dump all relvant rows for account_id into temp table
    CREATE TEMPORARY TABLE my_temp_table ON COMMIT DROP
        AS
        SELECT ...

    -- validation 1
    IF EXISTS (SELECT ...
               FROM my_temp_table
               WHERE ...
              ) THEN raise  exception 'failed validation 1'

    -- validation 2
    IF EXISTS (SELECT ...
               FROM my_temp_table
               WHERE ... and ... and ...
              ) THEN raise  exception 'failed validation 2'

    -- validation 3 with bit more logic to it
    IF EXISTS (WITH my_cte AS
               (
                   SELECT CASE WHEN ... IS NULL THEN 'foo' END AS r
                   FROM my_temp_table
                   WHERE ...
               )
               SELECT r
               FROM my_cte
              ) THEN raise exception 'failed on validation 3';

    -- If we got here, then all is well
    return true;

END;
$function$

The function is called as select * from public.my_function('xxxxxx');

Above works, but I'm not 100% sure if usage of temporarily table is correct here!

Will I get into trouble of table my_temp_table already exist if multiple queries come through all at the same time?
If I don't do it using Temp table, how else should I do this?

PS. Obviously not an DB expert here so feel free to criticise/advise as you see fit :)


texts: Return value formatted similar to if casted as money

texts:
My client's system is very outdated, and there is nothing I can do about it. What I'm trying to achieve is to extract values always with at least 2 decimal places.

E.g.

From

1826.5695600000
1826.3000000000
34.2300000000
0.0030000000

To

1826.56956
1826.30
34.23
0.003

If I cast these values as money I lose everything after 4th decimal place, but I need to achieve something similar but with min 2 max 10 decimal places. It does not matter if it is a string at the end, all that matters that I can extract value in this format.

My first try was to cast to float, find decimal point and accordingly cast the value as decimal(20,variable) but that was impossible, so I am now stuck in my mind how to achieve this.

These values are going trough SSIS processes

Extracted from Database
Staged
Pushed to client's import file


texts: Converting SQL Server recovery model simple to full

texts:
I am using SQL Server 2008 R2 with a recovery model of simple.

Monthly we have:

Total number of insert is 2196716
Total number of delete is 639542
Total number of update is 4203398

Now we are changing this server to active – active cluster environment.

Automatically recovery model change to full.

Above count’s are fine with recovery model fine? Any action required to take?


texts: SQL Developer - can't create DB objects in mysql

texts:
I have SQL Developer Version 19.2.1.247 on Debian Linux, with Java version "13.0.2" 2020-01-14 (I know, not supported, but it seems to work), and JDBC installed from mysql-connector-java_8.0.19-1ubuntu18.04_all.deb.

I have connected successfully to mysql (Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)) and can see the system databases with their tables and an empty test database I created for this purpose. However, when I right-click on 'Tables' under any of the databases, I don't see any options for creating a table. I haven't found any information about whether this simply isn't implemented yet, and one would hope that with MySQL now being an Oracle product, this would work. But does it?


texts: Increase in temporary tablespace while import

texts:
When i am importing some tables in my database, the temporary tablespace is getting full. This never happened earlier for other tables.

Please help me know why this is happening and any solution for it.

ParFile:

ParFile:
Directory=IMPDIR
DUMPFILE=expdp.dump
TABLE=scott.table1,scott.table2,scott.table2,scott.table3,scott.table4
TABLE_EXISTS_ACTION=append
CONTENT=DATA_ONLY
REMAP_SCHEMA=scott:hr
REMAP_TABLESPACE=users:testtbs
PARALLEL=10
LOGFILE=impdp.log