texts:
I have a query with 2 anti-joins (UserEmails = 1M+ rows and Subscriptions = <100k rows), 2 conditions, and a sort. I've created an index for the 2 conditions + sort, which sped up the query by 50%. Both anti-joins have indices. However, the query is too slow (4 seconds on production).

Here is the query:

SELECT
    "Users"."firstName",
    "Users"."lastName",
    "Users"."email",
    "Users"."id"
FROM
    "Users"
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            "UserEmails"
        WHERE
            "UserEmails"."userId" = "Users". ID
    )
AND NOT EXISTS (
    SELECT
        1
    FROM
        "Subscriptions"
    WHERE
        "Subscriptions"."userId" = "Users". ID
)
AND "isEmailVerified" = TRUE
AND "emailUnsubscribeDate" IS NULL
ORDER BY
    "Users"."createdAt" DESC
LIMIT 100

Here is the explain:

Limit  (cost=1.28..177.77 rows=100 width=49) (actual time=6171.121..6171.850 rows=100 loops=1)
  ->  Nested Loop Anti Join  (cost=1.28..4665810.76 rows=2643614 width=49) (actual time=6171.119..6171.807 rows=100 loops=1)
        ->  Nested Loop Anti Join  (cost=0.86..3470216.17 rows=2707688 width=49) (actual time=0.809..6062.152 rows=28607 loops=1)
              ->  Index Scan using users_email_subscribers_idx on "Users"  (cost=0.43..1844686.50 rows=3312999 width=49) (actual time=0.055..2342.793 rows=1186607 loops=1)
              ->  Index Only Scan using "UserEmails_userId_emailId_key" on "UserEmails"  (cost=0.43..0.49 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1186607)
                    Index Cond: ("userId" = "Users".id)
                    Heap Fetches: 1153034
        ->  Index Only Scan using "Subscriptions_userId_type_key" on "Subscriptions"  (cost=0.42..0.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=28607)
              Index Cond: ("userId" = "Users".id)
              Heap Fetches: 28507
Planning time: 2.346 ms
Execution time: 6171.963 ms

And here is the index that improved the speed by 50%:

CREATE INDEX  "users_email_subscribers_idx" ON "public"."Users" USING btree("createdAt" DESC) WHERE "isEmailVerified" = TRUE AND "emailUnsubscribeDate" IS NULL;

EDIT:
I should also mention that the users_email_subscribers_idx is showing an Index Scan and not Index Only Scan likely because the index is being updated regularly.


texts: invalid memory alloc request size on simple query with array_agg on Postgres 9.3

texts:
Postgresql 9.3 - Debian 7 54gb RAM 8 cores (On google compute engine)

I have a huge table called search_token (~50M rows) and I'm trying to execute a very simple query that is:

select array_agg(st.search_token) from search_token st limit 200

This column search_token is a varchar(25) and I'm getting this error:

ERROR:  invalid memory alloc request size 1073741824

If I remove the array_agg from the query, it runs smoothly in 1ms.

It used to work fine and started to fail on last days. Nothing was changed in the config file or on OS config.

Another funny fact. If I write the query as:

select array_agg(x.search_token) from (select st.search_token from search_token st limit 100000) x;

It runs in 64ms. (Note that I also raised the limit from 200 to 100000). Why is it trying to allocate 1GB ram to process this query with limit 200 and isn't for the subquery form?


texts: Slow queries on indexed columns (large datasets)

texts:
I've got some large MySQL (5.5.60) tables where queries take 60 seconds+ to complete.

product_base (1.5GB, 26 million rows)

CREATE TABLE `product_base` (
  `EAN13` varchar(13) NOT NULL DEFAULT '',
  `company_tk` int(11) DEFAULT NULL,
  `category` int(11) DEFAULT NULL,
  `modified` varchar(30) DEFAULT NULL,
  KEY `EAN13` (`EAN13`),
  KEY `company_tk` (`company_tk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

attribute (11.8GB, 126 million rows)

CREATE TABLE `attribute` (
  `EAN13` varchar(13) DEFAULT NULL,
  `attribute_type` int(11) DEFAULT NULL,
  `val_t` varchar(1500) DEFAULT NULL,
  `val_n` decimal(15,4) DEFAULT NULL,
  `val_d` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  KEY `EAN13` (`EAN13`),
  KEY `attribute_type` (`attribute_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

attribute_type (16KB, 59 rows)

CREATE TABLE `attribute_type` (
  `attribute_type` int(11) DEFAULT NULL,
  `title` varchar(90) DEFAULT NULL,
  `field_name` varchar(90) DEFAULT NULL,
  `data_type` char(3) DEFAULT NULL,
  `seq` int(11) NOT NULL,
  `html` int(11) DEFAULT NULL,
  KEY `attribute_type` (`attribute_type`),
  KEY `seq` (`seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Example slow query:

Query_time: 61.629684  Lock_time: 0.000138 Rows_sent: 1  Rows_examined: 28141979
select p.EAN13, p.company_tk, a.attribute_type, a.val_t, t.title, t.field_name from product_base p
inner join attribute a on p.EAN13=a.EAN13
inner join attribute_type t on a.attribute_type=t.attribute_type
where p.EAN13=705632109762 and (a.attribute_type=1 or a.attribute_type=3)
order by t.seq;

Running explain on that query:

+----+-------------+-------+-------+----------------------+----------------+---------+--------------------------+---------+-----------------------------+
| id | select_type | table | type  | possible_keys        | key            | key_len | ref                      | rows    | Extra                       |
+----+-------------+-------+-------+----------------------+----------------+---------+--------------------------+---------+-----------------------------+
|  1 | SIMPLE      | t     | range | attribute_type       | attribute_type | 5       | NULL                     |       2 | Using where; Using filesort |
|  1 | SIMPLE      | a     | ref   | EAN13,attribute_type | attribute_type | 5       | eandata.t.attribute_type | 6299937 | Using where                 |
|  1 | SIMPLE      | p     | ref   | EAN13                | EAN13          | 54      | eandata.a.EAN13          |       1 |                             |
+----+-------------+-------+-------+----------------------+----------------+---------+--------------------------+---------+-----------------------------+

Server/OS:
Debian 8 64bit | 24GB RAM (MySQL is using 70% of this) | 60GB SSD | 2xIntel Xeon CPU E5506 @ 2.13GHz | MySQL 5.5.60

Innodb settings here

MySQL my.cnf

# * Fine Tuning
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 20
table_cache            = 9552
thread_cache = 10
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
query_cache_size = 512M
join_buffer_size = 2G
innodb_buffer_pool_size = 15G
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
#innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8

max_heap_table_size = 2G
tmp_table_size = 2G
wait_timeout = 120
interactive_timeout = 120

Any suggestions for the slow queries?


texts:
I inherited an old script, that should, as part of a pl/sql routine, create a temporary index for me. I tried it on Oracle 11.2.0.4.0, but it does not accept the options.

CREATE INDEX x ON y(gid) {IN IndexTableSpace} {ORACLE}COMPUTE STATISTICS{ORACLE OFF};

However I am not familiar with the options listed in {}. Can somebody enlighten me as to what these are? And would the following be a updated equivalent?

create index x on y(gid) tablespace IDX_TS;

Any help is appreciated!


texts: MySQL Query with good execution plan is very slow due to Sending data operation

texts:
I have the following query:

SELECT
    a.borrowerId AS borrower_id,
    a.created,
    NULL AS initiator_team_id,
    NULL AS initiator_user_id,
    NULL AS old_team_id,
    NULL AS old_user_id,
    n.teamId AS new_team_id,
    a.lenderUserId AS new_user_id
FROM a
INNER JOIN b ON a.borrowerId = b.id
INNER JOIN n
    ON a.lenderUserId = n.lenderUserId
    AND a.created >= n.created
    AND (a.created < n.deleted OR (n.deleted IS NULL AND n.status = 'active'))
LEFT OUTER JOIN ass ON a.borrowerId = ass.borrower_id AND a.created = ass.created
WHERE a.`type` = 'created'
AND a.deleted IS NULL
AND ass.borrower_id IS NULL;

EXPLAIN presents me with the following plan which looks fine:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  b   ALL NULL    NULL    NULL    NULL    129132
1   SIMPLE  a   ref type,created,deleted,fk-lenderUser-activities,fk-borrower-activities,idx_type   fk-borrower-activities  4   bi.b.id 3   Using where
1   SIMPLE  n   ref unq_active  unq_active  4   a.lenderUserId  1   Using where
1   SIMPLE  ass eq_ref  PRIMARY PRIMARY 8   a.borrowerId,a.created  1   Using where; Using index; Not exists

But when I run the query it takes ~40 seconds. So I ran the MySQL Profiler and came back with this:

Status  Duration
starting    0.000009
Waiting for query cache lock    0.000004
Waiting on query cache mutex    0.000004
checking query cache for query  0.000063
checking permissions    0.000005
checking permissions    0.000004
checking permissions    0.000004
checking permissions    0.000005
Opening tables  0.000030
System lock 0.000012
init    0.000032
optimizing  0.000019
statistics  0.001302
preparing   0.000023
executing   0.000004
Sending data    40.217320
end 0.000008
query end   0.000006
closing tables  0.000011
freeing items   0.000393
logging slow query  0.000008
logging slow query  0.000005
cleaning up 0.000010

The query actually returns no data with the LEFT JOIN and NULL check present. But removing that check has no effect on the way the query actually executes.

So my question is:

Why is MySQL Sending data when the LEFT JOIN and NULL check prevent there being any rows that match?
How can I rewrite this so that it will actually perform faster?


texts: Ola Backup scripts, How do you specify a different filename for the mirrored set?

texts:
I'm using the backup script below as a test.  I want to use a different file name for the mirror. The format should be @FileName = '{ServerName}-DEFAULT-{DatabaseName}-{BackupType}-MIRROR_{FileNumber}.{FileExtension}
Is there anyway to do that?  I couldn't find an option specify the mirror name other than the path.  Am I missing something?  I have an automated restore job and don't want to have to parse out multiple files in to get the latest backup set in the mirror directory.
EXECUTE [dbo].[DatabaseBackup]
    @Databases                   = 'DatabaseName'
    ,@BackupType                 = 'FULL'
    ,@CleanupTime                = 1080
    ,@Directory = 'C:\BackupLocal'
    ,@Description                = 'Scheduled Daily Full Backup'
    ,@LogToTable                 = 'Y'
    ,@NumberOfFiles = 4
    ,@DirectoryStructure  = '{ServerName}{DirectorySeparator}INSTANCE_DEFAULT{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}'
    ,@FileName = '{ServerName}-DEFAULT-{DatabaseName}-{BackupType}-{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'
    ,@MirrorDirectory = 'C:\BackupMirror'


texts:
I have a database on SQL Server 2012 that I need to backup and send to someone on a different network / domain. Is there a way to create a full backup file that will not include the users / security information from the source database?

i.e. I don't want the users from my network to show up in the restored database's users list.


texts: PID from pg_backend_pid () does not exist in pg_stat_activity

texts:
I have a software written in Python which accesses a postgres database. In order to allow several instances of the software, on one of the screens I need what is the connection identity so I check the PID by accessing the DB and retrieving it through the function pg_backend_pid().

Everything works fine except that when I check the PID of the instance in pg_backend_pid() I get a PID number that does not exist in the pg_stat_activity table. In fact i can identify the PID number of my instance in the table but it is different than the number I received through the function pg_backend_pid().

I would be happy if someone could help me understand why the PID I got from the function is different than what exists in the table.

Thanks in advance.


texts: In sql-server according to syntax, who has to go first: DECLARE or SET NOCOUNT ON?


texts: Mongo Atlas API - Get Databases and Collections in a Cluster

texts:
So with Mongo Atlas I can easily list clusters in a project by calling the API as follows:

curl -i -u "<user@domain.com>:<my-api-key>" --digest "https://cloud.mongodb.com/api/atlas/v1.0/groups/<groupid>/clusters"

I want to list all database (and if possible all nested collections) for a particular cluster.

Is this possible via the API - I am sure it must be but I am not seeing it in the documentation.

I want to avoid having to connect to each cluster and collecting this information with a query - this is for a service discovery tool I am building for inventorying purposes.

Thanks


texts:
We can store Date and Time information in a couple of ways. What is the best approach for storing DateTime information?

Storing Date and Time in 2 separate columns or one column using DateTime?

Can you explain why that approach is better?

(Link to MySQL docs for reference, the question is general, not specific to MySQL)
Date and Time types: Date and Time


texts: SQL Server: CONVERT From ISO8601 To DateTime Within Query

texts:
I have a Microsoft SQL Server 2008 R2 database where the DateTime column is not a datetime data type, but varchar(50). Our website logs that data and I don't really have control over it (I'm sure a proper datetime column would be preferable). I'm trying to do a query that gets some year-to-date results with BETWEEN DateTime field AND NOW().

SQL Server table:

Id  DateTime                    Ip              City            State       Country         Continent   UserAgent
-----------------------------------------------------------------------------------------
1   2013-02-05T17:58:45-06:00   68.55.34.32     Laurel          Maryland    United States   North America   NULL
2   2013-02-05T17:58:52-06:00   70.88.57.62     Port Charlotte  Florida     United States   North America   NULL
3   2013-02-05T17:59:06-06:00   71.3.202.110    Cape Coral      Florida     United States   North America   NULL
4   2013-02-05T17:59:23-06:00   67.239.18.161   Naples          Florida     United States   North America   NULL
5   2013-02-05T17:59:58-06:00   96.19.107.135   Gulfport        Mississippi United States   North America   NULL

SQL query:

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE
   (DateTime BETWEEN CONVERT(datetime, SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19)) AND { fn NOW() })
ORDER BY Ip

I'm getting an error

  Conversion failed when converting date and/or time from character string

This query works...

SELECT CONVERT(datetime, SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19)) AS Expr1

I've also tried CAST()...

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE (DateTime BETWEEN CAST(SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19) AS datetime) AND { fn NOW() })
ORDER BY Ip

That gives the same conversion error, but this works...

SELECT CAST(SUBSTRING('2014-01-01T00:00:00-06:00', 1, 19) AS datetime) AS Expr1

I'm sure that I'm missing some fundamental bit, but it seems like it should work.

What am I doing incorrectly?

JJ


texts: Speed-up lookup of a row with prefix pattern

texts:
Say I have these rows in one UNIQUE column:

202101XXXXX
202101XXXXX
202102XXXXX
202102XXXXX
202103XXXXX
202103XXXXX

The first 6 digits are year and month (YYYYMM), and the XXXXX part are random digits. They are UNIQUE. Let's say there are millions of rows like these.
I need to lookup a certain row e.g., 20210312345, is there a way I could tell MySQL to not look at the whole table and just search on rows with the 202103 prefix? And, if that was possible, would it actually speed up the query?


texts: How to recover from transaction replication failure?

texts: My transaction replication works such that, to curb data growth, every day articles in publishers are purged but this purging of data is not replicated on to the subscriber. So at any point of time my publisher only has current day's data and my subscriber has archived data. How can I recover from a transaction replication failure? My publisher doesn't have all the data so I can't reinitialize my subscription.


texts: Subquery can't find column from superquery's join

texts:
I'm running into some trouble with SQL: Basically I'm trying to get a result set back that contains a sum of ALL questions asked to employees (grouped by company) and also add the "onetime_items" which are manually added items in a different table.

I currently have this SQL statement (I'm using MySQL):

SELECT
CONCAT_WS(
    ', ', count(DISTINCT CONCAT(emailaddress, '_', e.id)),
    (
        SELECT GROUP_CONCAT(items SEPARATOR '; ') as OneTimeItems
        FROM (
            SELECT CONCAT_WS(
                ': ', oi.item_name, SUM(oi.item_amount)
            ) items
            FROM onetime_item oi
            WHERE oi.company_id = e.company_id
            AND oi.date BETWEEN '2015-12-01'
            AND LAST_DAY('2015-12-01')
            GROUP BY oi.item_name
        ) resulta
    )
) as AllItems,
e.id,
LEFT(e.firstname, 1) as voorletter,
e.lastname
FROM question q
LEFT JOIN employee e ON q.employee_id = e.id
WHERE 1=1
AND YEAR(created_at) = '2015'
AND MONTH(created_at) = '12'
GROUP BY e.company_id

Now I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.company_id' in where clause

The dates used are dummy dates and the 1=1 at the bottom where clause is because I'm generating the where statement based on user input values.

All columns DO exist in the table employee and the left join works ( I tried entering an id manually instead of using the column reference and it worked, I got the right result back)

I'm also sorry but I'm not allowed to post a table schema or anything related to the DB structure online.

Any idea as to why the reference to e.company_id fails?

EDIT:
This is the result set I need:

5, Het is je verjaardag: 1; Skivakantie: 1; Telefonische leadvergoeding: 8

The construction is as follows:
5 = leads
comma separated to get the rest.
Leads are the unique combination from the Questions table, and the rest of the results are constructed from onetime_items.

EDIT 2: SQL fiddle keeps throwing an error at me so I'm just going to post a small made up DB with a little bit of made up data: http://pastebin.com/cCveVtGr


texts: Varchar PrimaryKey. Clustered or nonclustered?

texts:
I am working on a legacy database. The primary key is clustered varchar(128) and the table contains a high volume of data.

Does it necessarily help performance if I change the clustered PK to nonclustered?

Changing the table to a heap would at least it prevent more data fragmentation after inserting any new rows (to the best of my knowledge - I am not a DBA). I also have similar problem with uniqueidentifier primary keys here.


texts: Reuse results from correlated subqueries in same query

texts:
I have 4 tables (I actually have lots more that I want to do this with...but this is where I am starting).

distr_catalogs: has many distr_catalog_brands, distr_catalog_system_types
distr_catalog_brands: belongs to distr_catalogs
distr_catalog_system_types: belongs to distr_catalogs
brand_catalog_sections: belongs to distr_catalog_brands, distr_catalog_system_types

I am wanting to create a materialized view whose columns are:

catalog_id | catalog_name | brand_ids | system_type_ids | catalog_sections

catalog_id & catalog_name come from the distr_catalog table
brand_ids holds an array of the brand ids related to the catalog
system_type_ids holds an array the system type ids related to the catalog
catalog_sections holds an array of brand catalog section ids that are related to the brand_ids and system_type_ids

I can do all but the last just fine:

CREATE MATERIALIZED VIEW catalog_system_brands AS
select dc.id as catalog_id, dc.catalog_name,
ARRAY(SELECT brand_id FROM distr_catalog_brands WHERE distr_catalog_id = dc.id) as brands,
ARRAY(SELECT id FROM distr_catalog_system_types WHERE display_status = true AND distr_catalog_id = dc.id) as system_type_ids,
from distr_catalogs dc

This gets me a great table that has all the right data. However the only way I can figure out to get the last column I need ... is to completely re-write the subqueries in the final array statement. I get an error every time I try to use the brands or system_type_ids aliases I defined in the two above arrays.

CREATE MATERIALIZED VIEW catalog_system_brands AS
select dc.id as catalog_id, dc.catalog_name,
ARRAY(SELECT brand_id FROM distr_catalog_brands WHERE distr_catalog_id = dc.id) as brands,
ARRAY(SELECT id FROM distr_catalog_system_types WHERE display_status = true AND distr_catalog_id = dc.id) as system_type_ids,
ARRAY(SELECT id FROM brand_catalog_sections
      WHERE brand_id = ANY(SELECT brand_id FROM distr_catalog_brands WHERE distr_catalog_id = dc.id)
      AND system_type_id = ANY(SELECT system_type_id FROM distr_catalog_system_types WHERE display_status = true AND distr_catalog_id = dc.id)) as section_ids
from distr_catalogs dc

How would I re-write the create statement to not have to re-run the subqueries?

Note: This is the first instance of this. However, the next table I bring into this view will need to use the results of the section_ids column to determine which systems to display. So this will get out of hand quickly if I am not able to reuse the results from a previous query.


texts: What does SSRS technically do when it expires the cache?

texts:
Our data is loaded daily.  My report can be run, using a parameter, for any number of sites (100 is the total number of sites) and I want it to cache a temporary copy of the report and expire it on a schedule - a schedule that is triggered at the end of our daily load.  This is simple.

Where it gets complicated is I have a cache refresh plan to pre-load the cache for all 100 sites.  I need to make sure the cache expires before this plan runs - a plan that is tied to a schedule I want to trigger at the end of our load.

If I trigger the jobs, in order, at the end of the load, they are effectively triggered together, so I would like to understand what happens on the system level when SSRS expires a cache so that I can ensure the refresh occurs after the expiration.  Is there a value in one of the ReportServer tables that is changed or a row deleted?

For what it's worth, this is what I am seeing in my testing (the cache is successfully pre-loaded), but I would like to understand the technical architecture to give myself a better comfort level before using this in production.


texts: How to get a surrogate key value in order to put a foreign key in and create relationship?

texts:
I am trying to create two relationships between three tables, using foreign keys with values of primary keys, which are surrogate keys. But I don't understand how to enter the right value in the foreign keys, since it has no meaning, being a surrogate key.

Here are my tables :

  Quote (quoteID PRIMARY KEY, quoteValue, quoteDate, currency)

  TermStrucureComponent (TSComponentID PRIMARY KEY, type)

  InterestRate (rateID PRIMARY KEY, quote FOREIGN KEY to quoteID, TermStructureComponent FOREIGN KEY to TSComponentID, timeToMaturity)

So I have my InterestRate table that has two relationships: one with Quote, and one with TermStructureComponent. After inserting rows in the two first tables (without any foreign key), I need to insert it in InterestRate. But I don't know how to fill in the quote and TermStructureComponent fields, because I don't know their value in the other tables, since they are surrogate keys.

How should I do that? Thank you for your help


texts: How do I solve this problem of a correlated subquery

texts:
We have an employee table with the following attributes :

employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id

Display the manager name and the salary of the lowest paid employee for that manager.
Exclude anyone whose manager is not known.Exclude any groups where the minimum salary
is less than 6000.sort the output in descending order of salary. Use alias name "salary"

I have tried the following :

SELECT manager_id , (SELECT MIN(salary)
                     FROM employees emp
                     WHERE e.manager_id = emp.employee_id
                     GROUP BY employee_id)
FROM employees e
GROUP BY manager_id;

I don't seem to get correct outputs with this


texts: How does Oracle handle composite index lookups?

texts:
MySQL cannot use a composite index in a lookup in which the WHERE condition doesn't include the columns forming a left-most prefix:

  MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index.

A quote from this answer on PostgreSQL caught my attention:

  This is somewhat different in Oracle 11 which can sometimes also use columns that are not at the beginning of the index definition.

Under what circumstances can Oracle (at least in 11g) do a lookup without the left-most prefix columns existing in the query?


texts:
The join part uses OR that's why running  long I'm trying to make it faster

SELECT sc.clientid
            ,sc.dictionid
            ,sc.firstname
            ,sc.lastname
            ,sc.DB
            ,sc.DOLS
            ,sc.dlnum
            ,Cons.Description AS ConS
            ,Conc.Description AS ConC
            ,'False' as Assigned
        FROM    [monitor].[uxref] xref
            INNER JOIN [Monitor].[dbo].[seacc] sc
                    ON (
                            xref.regid = sc.[consid]
                            OR xref.regid = sc.[concid]
                        )
            LEFT JOIN [Monitor].[dbo].Reg Cons ON Cons.RegID = sc.ConsId
            LEFT JOIN [Monitor].[dbo].Reg Conc ON Conc.RegID = sc.ConcId
        WHERE xref.UserId =1000


texts: Will transaction log grow event trigger time out event?

texts:
i have a .NET program request the MSSQL database, the request contains large amount of deletion and insertion.

In such case, there is a potential that the log would grow as the transaction log space is not enough.

And it is found out that the event of growing transaction log take long time to do.

I want to know if the time required for this event will trigger the database time out or not?

Or the auto grow event will not affect the regular time out setup of the database?


texts: Alternative to Dynamic SQL inside trigger?

texts:
My database has the following tables:

    TAG
    ----------------------
    | tag_id | tag_name  |
    ----------------------

    TAG_VALUE: Stores values associated to each tag
    ----------------------------------------
    | tag_id | insertion_timestamp | value |
    ----------------------------------------

    ALARM: Defines alarms for each tag
    -------------------------------------
    | alarm_id | tag_id | function_name |
    -------------------------------------

    ALARM ACTIVATION: Stores information regarding each time the alarms were triggered
    -----------------------------------------------------
    | alarm_id | activation_timestamp | activation_value|
    -----------------------------------------------------

So, everytime a new value is inserted into TAG_VALUE, I need to check if that new value triggers any alarm associated to it's tag. Since there is no common criteria between alarms, I'm just storing a function name, which will be used later on to determine if the alarm should be triggered or not

TAG_VALUE has the following AFTER_INSERT trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `mydb`.`tag_value_AFTER_INSERT` AFTER INSERT ON `tag_value` FOR EACH ROW
BEGIN
call sp_alarm_handler(NEW.tag_value, NEW.tag_id);
END

And sp_alarm_handler is coded as follows:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_alarm_handler`(IN tag_value VARCHAR(255), IN value_tag_id INT(11))
    BEGIN

    DECLARE exit_loop BOOLEAN;
    DECLARE v_alarm_id INT(11);
    DECLARE function_name VARCHAR(255);
    DECLARE value_triggers_alarm TINYINT(1);

    DECLARE custom_alarm_cur CURSOR FOR
    Select alarm_id, function_name From vw_custom_alarms where tag_id = value_tag_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

    OPEN custom_alarm_cur;

    custom_alarm_lp: LOOP

        FETCH custom_alarm_cur into v_alarm_id, function_name;

        IF exit_loop THEN
        leave custom_alarm_lp;
        END IF;

        #************************************************
        #*********HERE'S THE DYNAMIC SQL PIECE'**********
        #************************************************
        set @query = CONCAT('Select ',function_name,'(',tag_value,')', 'into @value_triggers_alarm');
        PREPARE stmt FROM @query;
        Execute stmt;

        IF fn_is_alarm_active(v_alarm_id) = 0 THEN

            If @value_triggers_alarm = 1 THEN
                    INSERT INTO alarm_activation(alarm_id, activation_timestamp, activation_value)
                    VALUES (v_alarm_id, NOW(), tag_value);
            end if;

        ELSE IF @value_triggers_alarm = 0 THEN
            call sp_deactivate_alarm(v_alarm_id);

            END IF;
        END IF;

    END LOOP custom_alarm_lp;
    Close custom_alarm_cur;

    END

This gives me an ERROR 1336: 1336: Dynamic SQL is not allowed in stored function or trigger
Is there any workaround in my case?


texts: Postgresql enabling extensions without super-user

texts: I have a PostgreSQL 9.5 server on which I have scripts that create roles and databases for users automatically. Within these databases it would be helpful to enable specific extensions (e.g. pgcrypto), but as I understand it one must be a superuser to run CREATE EXTENSION. Is there a way to enable such extensions without manually logging in with a superuser account?


texts: Change Data Capture in a services environment

texts:
Change Data Capture looks to be exactly what I need... Except.

All of my database actions come from users that system accounts.  We have WCF service that is called by an application.  The WCF Service does the authorization for the call, then the system account that the WCF Service runs as makes the needed call to the database.

This allows us to tightly control permissions on the SQL Server.

Because of this, Change Data Capture is only going to be recording that generic user's information.

But I need to know the actual user that initiated the WCF Call.

Is there a way to plug in an alternate user name for Change Data Capture?  Or another way to get Column level logging of who made a change and when?


texts: Does MVC architecture design pattern effect MySQL database performance?

texts:
Our current site is entirely developed in the cakePHP framework with the MVC architecture. If we notice more than around 19-20 users viewing the site at once, it begins to crawl to a hault and begin to time out.

Could this be a performance issue with MySQL? Possibly related to MVC - as the site relies heavily on communication with the SQL database. If so, would increasing the overall RAM help solve the issue?

Thanks.

Config:

open-files-limit                = 20000

#sql-mode                       = TRADITIONAL

#event-scheduler                = 1

### Cache
thread-cache-size               = 16
table-open-cache                = 2048
table-definition-cache          = 512

query-cache-size                = 32M
query-cache-limit               = 1M

### Per-thread Buffers
sort-buffer-size                = 1M
read-buffer-size                = 1M
read-rnd-buffer-size            = 8M
join-buffer-size                = 1M

### Temp Tables
tmp-table-size                  = 64M
max-heap-table-size             = 64M

### Networking
back-log                        = 100
max-connections                 = 300
max-connect-errors              = 10000
max-allowed-packet              = 16M
interactive-timeout             = 600
wait-timeout                    = 60
net_read_timeout        = 30
net_write_timeout       = 30
# This value is the size of the listen queue for incoming TCP/IP connections.
back_log            = 128

#### Storage Engines
#default-storage-engine         = InnoDB

## Makes sure MySQL does not start if InnoDB fails to start. This helps
## prevent ugly silent failures.
innodb                          = FORCE

### MyISAM
key-buffer-size                 = 64M
myisam-sort-buffer-size         = 128M

#InnoDB
innodb-buffer-pool-size         = 16M
innodb-log-buffer-size          = 4M
innodb-log-files-in-group      = 2

server-id                       = 1

[mysqld-safe]
log-error                       = /var/log/mysqld.log

[mysqldump]
max-allowed-packet      = 16M


texts: How to fix error ORA-00322: log is not current copy

texts:
I have to submit my project a day after tomorrow. My project is ready but I am having above mentioned error while connecting to Oracle database. I tried the following solution:

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 239076244 bytes
Database Buffers 289406976 bytes
Redo Buffers 5844992 bytes
Database mounted.
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\APP\LENOVO\ORADATA\ORCL\REDO01.LOG'

I am stuck now. What to do now? I really need help here.


texts: Why are multiple COUNT faster than one SUM with CASE?

texts:
I wanted to know which of the following two approaches is faster:

1) Three COUNT:

 SELECT Approved = (SELECT COUNT(*) FROM dbo.Claims d
                  WHERE d.Status = 'Approved'),
        Valid    = (SELECT COUNT(*) FROM dbo.Claims d
                    WHERE d.Status = 'Valid'),
        Reject   = (SELECT COUNT(*) FROM dbo.Claims d
                    WHERE d.Status = 'Reject')

2) SUM with FROM-clause:

SELECT  Approved = SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END),
        Valid    = SUM(CASE WHEN Status = 'Valid'    THEN 1 ELSE 0 END),
        Reject   = SUM(CASE WHEN Status = 'Reject'   THEN 1 ELSE 0 END)
FROM dbo.Claims c;

I was suprised that the difference is so large. The first query with three subqueries returns the result immediately whereas the second SUM approach needs 18 seconds.

Claims is a view which selects from a table containing ~18 million rows. There is an index on the FK-Column to the ClaimStatus table which contains the status-name.

Why does it make such a great difference whether i use COUNT or SUM?

Execution-plans:

COUNT
SUM

There are 12 statuses in total. Those three statuses belong to 7% of all rows.

This is the actual view, i'm not sure if it's relevant:

CREATE VIEW [dbo].[Claims]
AS
SELECT
   mu.Marketunitname AS MarketUnit,
   c.Countryname     AS Country,
   gsp.Gspname       AS GSP,
   gsp.Wcmskeynumber AS GspNumber,
   sl.Slname         AS SL,
   sl.Wcmskeynumber  AS SlNumber,
   m.Modelname       AS Model,
   m.Salesname       AS [Model-Salesname],
   s.Claimstatusname AS [Status],
   d.Work_order      AS [Work Order],
   d.Ssn_number      AS IMEI,
   d.Ssn_out,
   Remarks,
   d.Claimnumber     AS [Claim-Number],
   d.Rma_number      AS [RMA-Number],
   dbo.ToShortDateString(d.Received_Date, 1) AS [Received Date],
   Iddata,
   Fisl,
   Fimodel,
   Ficlaimstatus
FROM Tabdata AS d
   INNER JOIN Locsl AS sl
           ON d.Fisl = sl.Idsl
   INNER JOIN Locgsp AS gsp
           ON sl.Figsp = gsp.Idgsp
   INNER JOIN Loccountry AS c
           ON gsp.Ficountry = c.Idcountry
   INNER JOIN Locmarketunit AS mu
           ON c.Fimarketunit = mu.Idmarketunit
   INNER JOIN Modmodel AS m
           ON d.Fimodel = m.Idmodel
   INNER JOIN Dimclaimstatus AS s
           ON d.Ficlaimstatus = s.Idclaimstatus
   INNER JOIN Tdefproducttype
           ON d.Fiproducttype = Tdefproducttype.Idproducttype
   LEFT OUTER JOIN Tdefservicelevel
                ON d.Fimaxservicelevel = Tdefservicelevel.Idservicelevel
   LEFT OUTER JOIN Tdefactioncode AS ac
                ON d.Fimaxactioncode = ac.Idactioncode


texts: Restrict sysdba or system administrator from reading package in oracle 11g

texts:
I have a requirement wherein the oracle database contains packages which have important stored procedures and functions which should not be exposed to the client / customer. The client / customer will be provided with a user account having EXECUTE privilege.

Is there any way in which I can protect the IPR of my package, so that as a owner I should only be able to change the content in the package for future updates.

I tried logging with SYS as SYSDBA and I could see the package and the package body.

I am a newbie to oracle. Please guide me on the steps that I need to take to secure the packages from being copied.


texts:
The following simple test returns an error

CREATE TABLE track (
  position point NOT NULL
);
INSERT INTO track VALUES (
  position=ST_PointFromText('POINT(-45.62390335574153 -3.9551761173743847)')
);

Error:

  ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

What am I doing wrong?


texts: How can I check for fragmentation in PostgreSQL 9.1?


texts: Design issue in transitive dependencies between tables

texts:
This is a made-up scenario, but I imagine that it can be of general interest, so I'll post it here. Imagine the following business rule:

A course has a grade scale that determines what grades a student can get on their diplomas

How would this BR be implemented (no procedure logic) while preserving 3NF? The DBMS does not support general expressions in CHECK constraints, so we are restricted to row expressions

A naive approach would be something like:

create table gradesscales
( gradescale_id int not null primary key );

create table grades
( grade char(1) not null primary key );

create table grades_in_gradescales
( gradescale_id int not null
    references gradesscales (gradescale_id)
, grade char(1) not null
    references grades (grade)
, primary key (gradescale_id, grade)
);

create table courses
( course_code char(5) not null primary key
, gradescale_id int not null
    references gradesscales (gradescale_id)
);

create table diplomas
( student_no int not null
, course_code char(5) not null
    references courses (course_code)
, grade char(1)
    references grades (grade)
, primary key (student_no, course_code)
);

insert into gradesscales (gradescale_id) values (1),(2);

insert into grades (grade) values ('1'),('2'),('3'),('A'),('B');

insert into grades_in_gradescales (gradescale_id, grade)
values (1,'1'),(2,'2'),(1,'3'),(2,'A'),(2,'B');

insert into courses (course_code, gradescale_id)
values ('MA101', 1),('FY201', 2);

So far so good, but nothing prevents us from adding a grade from a grade scale not related to the course:

insert into diplomas (student_no, course_code, grade)
values (1,'MA101','B');

One pragmatic approach is to add gradescale_id to diplomas, and reference grades_in_gradescales instead of grades. In addition add a super key course_no, gradescale_id in course:

alter table courses add constraint ...
    unique (course_code, gradescale_id);

alter table diplomas add constraint C
    add column gradescale_id int not null
    -- drop foreign key against grades
    add foreign key (gradescale_id, grade)
          references grades_in_grade_scales (gradescale_id, grade);
    -- replace foreign key against course
    add foreign key (gradescale_id, course_code)
          references courses (...);

but I'm not too happy about that. Other thoughts?

Even though I used SQL in my example, I would like to keep the discussion at a conceptual/logical level. I.e. model this with attributes from the universe of discourse (no auto-generated surrogate attributes).


texts:
I'm working on an Android application which utilizes a number of MySQL databases.

The design is as such:

Users have many recipes.
Recipes have many ingredients
Recipes have many directions

I understand how to implement joins, but I'm struggling with the schema I should be using to get the desired results. This is where I am so far:

users

user_id
user_email
user_password

recipes

recipe_id
ingredients_list_id
directions_list_id

user_recipes

user_id
recipe_id

ingredients_list

ingredients_list_id
ingredient_id
ingredient_id
ingredient_id
...

directions_list

directions_id
direction
direction
direction
...

ingredients

ingredient_id
ingredient_name

Note: The reason I'm using a separate database for ingredients is that I want to have a shared set of ingredients between all users, this way I can have an "ingredient of the week" with id 8067 or whatever.

I think I'm getting lost around the ingredient_list and directions_list part of the schema. It appears as though it may work, but I'm afraid I'm either over-complicating the problem or missing some crucial/more efficient join(s).

If this is the wrong place to ask this question, please let me know, so I can post elsewhere. Any help would be greatly appreciated!


texts: how to check and list all the files related to mongodb in ubuntu? I am looking for config files, password informations for mongodb

texts: I installed the mongodb package in ubuntu using apt following the instructions on http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/. I know that binaries are in /usr/bin/, and so on. I wonder how to check and list all the files related to the mongodb package.


texts: Slow insert (trigger/indexes) processing

texts:
Whenever an insert is done in a table these tasks are executed:

One Trigger (see down).
Three indexes: one checks that a primary key is unique and the rest makes sure that two columns don't have unique values.

TRIGGER "TMMGR"."USR_SETTINGS_CNFG_PK"
   BEFORE INSERT
   ON USR_SETTINGS_CNFG_T
   FOR EACH ROW
BEGIN
   :NEW.USR_SETTINGS_CNFG_ID := USR_SETTINGS_CNFG_TSEQ.NEXTVAL;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20000, SQLERRM (SQLCODE));
END USR_SETTINGS_CNFG_PK;

I need to know how can I make the insert/trigger/index execution faster.

These are some of the DB settings:

max_string_size string EXTENDED
db_cache_size big integer 112M
shared_pool_size big integer 160M
java_pool_size big integer 160M
log_buffer big integer 7616K
nls_length_semantics string BYTE
open_cursors integer 500
optimizer_mode string ALL_ROWS
optimizer_features_enable string 12.1.0.2
compatible        string  12.1.0.2
noncdb_compatible boolean FALSE
cursor_sharing string EXACT
db_block_size integer 16384
job_queue_processes integer 4000
optimizer_index_cost_adj integer 10
aq_tm_processes           integer 1
asm_io_processes          integer 20
db_writer_processes       integer 1
gcs_server_processes      integer 0
global_txn_processes      integer 1
job_queue_processes       integer 4000
log_archive_max_processes integer 4
processes                 integer 500

Thanks.


texts: Can I select keys stored in a postgres jsonb column as values dynamically

texts:
I have a JSONB column in a Postgres DB that contains some dynamic data about a row. Think of the table as a single table with an id column and a "dynamicData" JSONB column for simplicity for this example. And suppose in a row, I store the following in the "dynamicData" column
{
  dynamicField1: 'value1',
  dynamicField2: 'value2',
}

I understand I can select those as columns in a select such as
Select
  dynamicData->dynamicField1 as dynamicField1,
  dynamicData->dynamicField2 as dynamicField2,
From
  some_table

But what I'm curious is if I can somehow tell PG I want all keys in the JSONB column to be selected as columns.
This is because I have database views that sit on top of the tables, and if I add a new JSONB key in the dynamic data column, I would then need to somehow update these views if I cannot select the keys as columns automatically.


texts: Inner join with greater or equal VS where condition using a variable

texts:
Suppose I have a table with a unique column and this columns has only integer numbers.

Eg: ID column with rows from 1 to 999.

My intention is to return only rows when my ID is greater than 500, for example.

Using T-SQL, I have several stored procedures that create a variable and make this cut like this:

DECLARE @VAR AS INT

SET @VAR = 500

SELECT * FROM sample WHERE ID > @VAR

But for debugging purposes I want to use a temporary table to make this validation throughout the whole stored procedure without having to re-declare or reset my @VAR variable:

CREATE TABLE #cut ( ID int )

INSERT INTO #cut VALUES (500)

SELECT * FROM sample A INNER JOIN #CUT B ON A.ID > B.ID

Is there any performance penalty using this approach on SQL Server 2017?

An extra information is that I don't have sysadmin role on this database, therefore there is no chance using the native debugger.


texts:
I am trying to copy over the live database from one server and restore it on the dev server using SQL Server 2005. I have made a backup of the live db and copied the .bak file over to the dev server. I am trying to run this script and I keep getting errors:

ALTER DATABASE Portal SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE Portal SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE Portal
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Portal.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO

exec dbo.sp_fixusers
GO

ALTER DATABASE Portal SET MULTI_USER
GO

ALTER DATABASE Portal SET ONLINE WITH ROLLBACK IMMEDIATE
GO

Errors:

  Msg 5052, Level 16, State 1, Line 2
  ALTER DATABASE is not permitted while a database is in the Restoring state.
  Msg 5069, Level 16, State 1, Line 2
  ALTER DATABASE statement failed.
  Msg 5052, Level 16, State 1, Line 2
  ALTER DATABASE is not permitted while a database is in the Restoring state.
  Msg 5069, Level 16, State 1, Line 2
  ALTER DATABASE statement failed.
      10 percent processed.
      20 percent processed.
      30 percent processed.
      40 percent processed.
      50 percent processed.
      60 percent processed.
      70 percent processed.
      80 percent processed.
      90 percent processed.
      100 percent processed.
  Processed 307768 pages for database 'Portal', file 'Portal_Data' on file 1.
  Processed 127 pages for database 'Portal', file 'Portal_Log' on file 1.
  RESTORE DATABASE successfully processed 307895 pages in 130.538 seconds (19.322 MB/sec).
  Msg 2812, Level 16, State 62, Line 1
  Could not find stored procedure 'dbo.sp_fixusers'.


texts: Oracle number precision datatype performance impact

texts:
Does number size affect to speed of inserting, updating, ...(DML operations) rows?
If i use number(20) instead of number(6), does it impact performance?


texts: Why does 3NF Normal form allow transitive dependency of prime attributes?

texts:
I'm a beginner in Relational Design, and I came across the definitions of 3NF and BCNF.

According to 3NF, Any non-trivial functional dependency X-> A shouldn't exist unless X is a super key or A is a prime attribute (Where as the BCNF doesn't allow the latter).

Is there a reason for this? How does this interpret? (i.e What does it mean?)


texts:
I have a document structure like this:

{
"_id" : ObjectId("5359816b036493327d7cae95"),
"_class" : "com.myproject.MyObjectDTO",
"signals" : [
    {
        "signalType" : "ABC",
        "quantity" : NumberLong(1),
        "date" : ISODate("2001-12-13T23:00:00.000Z")
    },
    {
        "signalType" : "ABD",
        "quantity" : NumberLong(1),
        "date" : ISODate("2001-12-16T23:00:00.000Z")
    },
    {
        "signalType" : "ABF",
        "quantity" : NumberLong(1),
        "date" : ISODate("2001-12-17T23:00:00.000Z")
    },
    {
        "signalType" : "ABG",
        "quantity" : NumberLong(1),
        "date" : ISODate("2001-12-18T23:00:00.000Z")
    },
    ...
}

with around 160 000 entries and 3000 signals per entry.

I tried to find documents with a date greater than an specified date (I'm using $date because I query through Spring Data):

db.analysisDTO.find(
      "signals" : {
         "$elemMatch" : {
            "date" : { "$gte" : { "$date" : "2001-12-01T22:00:00.000Z"}})

The request takes more than 7 minutes and I get no result. Even if I change $gte to $lte, I don't get anything.

Is the request wrong?


texts: Seeing recompiles when changed AUTO_UPDATE_STATISTICS to OFF

texts: The reasons why proc cache is flushed are listed here http://msdn.microsoft.com/en-us/library/bb522682.aspx, and AUTO_UPDATE_STATISTICS is not this list. So I changed this option to OFF on production and saw a 100% spike that lasted about 4 minutes and saw a lot of recompiles. What am I missing?


texts: Update record column with id found in the table of an associated model

texts:
Given three tables:

A users table with id's:

╔════╗
║ id ║
╠════╣
║ 1  ║
╟────╢
║ 2  ║
╟────╢
║ 3  ║
╚════╝

A purposes table with user_id's

╔════╤══════════╗
║ id │ user_id  ║
╠════╪══════════╣
║ 1  │ 2        ║
╟────┼──────────╢
║ 2  │ null     ║
╟────┼──────────╢
║ 3  │ null     ║
╚════╧══════════╝

Where the user_id can be null.

And a journeys table with user_id's and purpose_id's.

╔════╤═════════╤════════════╗
║ id │ user_id │ purpose_id ║
╠════╪═════════╪════════════╣
║ 1  │ 2       │ 3          ║
╟────┼─────────┼────────────╢
║ 2  │ 1       │ 2          ║
╟────┼─────────┼────────────╢
║ 3  │ 1       │ 1          ║
╚════╧═════════╧════════════╝

We want to create a PostgreSQL query that will find all of the purposes with null in their user_id column, select their associated journeys (by selecting them based on the id of said purposes), and then update the then empty user_id column of the purpose with the user_id found in the first associated journey.

What is the best way to do this?

I've gotten as far as being able to find all the user_id's from the journeys table who have purposes with an empty user_id column using an exists subselect query but I'm unsure how to update that null user_id column with the matching user_id found in the journeys table...

select
   user_id
from
   journeys
where exists(
    select
       id
    from
       purposes
    where
       user_id is nil
       and purposes.id = journeys.purpose_id
);


texts: Delete records from table A where col 1 and col2 are in table B

texts:
I am wanting to delete records from a table if multiple columns in this table are in another table.
Currently, I usually just concatenate the columns and use the WHERE IN method:
DELETE FROM TableA
WHERE Col1+Col2 IN (SELECT Col1+Col2 FROM TableB)

Is there a better way to achieve the same result?
Thanks.


texts: Autorefresh for Postgres materialised view?

texts:
Is it possible to set an automatic refresh frequency for materialized views?

Here's the situation, I have an important but relatively small (20 thousand row) table that's subject to constant updates (hundreds of updates/second). The table is also subject to frequent (low priority) reads. What I've done before is to copy the table every 60 seconds into a second table and allow public read-only access to that table.

I suspect that using a materialized view is a more suitable solution, but if possible I'd also like to automatically refresh the view based on the number of SELECTs made against the view. If no-one SELECTS from the view the don't bother with a refresh, but if the view is being accessed then the table should be refreshed every 60 seconds.

Is this something that postgres provides a solution to, or should I code the refreshing of the view into my application?


texts:
I have a third party incident management tool, that creates tickets from errors in the SQL logs.

Occasionally replication stops replicating without creating errors in the SQL logs.

I want to create a job that runs every 10 minutes or so to check on replication and then uses RAISERROR to start our ticketing (and alerting) process.

There are a number of stored procedures to Programmatically Monitor Replication that will show issues, and there is also a pretty good solution at Monitoring Transactional Replication in SQL Server(by Francis Hanlon 11 April 2013) that is good start on what I want. But would need to be tweaked a bit to meet my needs.  Before I start reworking Francis's solution I am wondering if there are any other solutions I might leverage on.

I have searched around here and google, and Francis's solution is the only one I found that gets close to inhouse monitoring, without new third party tools.

Are there any solutions to monitor SQL replication real time with T-SQL?

SQL 2008 to SQL 2019
Mostly transactional replication
Same AND cross server replication


texts: How to point SQL Server Python to a specific Python install?

texts:
SQL Server 2017 installs an instance of Python to "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES" (documentation link) that it uses for its Python calls. This is installed with specific packages that SQL Server needs to communicate with Python.

Is there documentation on these packages, where to pull them (pip?), and how to point SQL Server to a different install of Python on the same server?


texts: What parameters should be modified after upgrading RAM memory in Oracle server

texts: I have a linux server (rhel4) with oracle 9i (9.2.0.8) to which we have recently upgraded the RAM memory from 4G to 16G. I would like to know what server parameters I should modify and even more what Oracle parameters should I modify to make use of this new configuration from memory, thank you very much for your answers.


texts: zero rows fetched, selected, or processed Mysql

texts:
Help me to solve this problem

error

call cd3(@VAR)  0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed   0.000 sec

delimiter ;;
create procedure cd3(out var int)

begin

declare finished int default 0;

declare cur cursor for select id from a;

declare continue handler for not found set finished=1;

open cur;

curloop:loop

fetch cur into var;

if finished then

close cur;

leave curloop;

end if;

end loop curloop;

end;;
call cd3(@VAR)
select @var


texts: Two Different Sources Taking Transaction Log Backups

texts:
I have inherited a database server with SQL Server 2005.  The databases have full recovery mode with hourly transaction log backups.  There are two sources taking full backups overnight and hourly transaction log backups: Tivoli Data Protection (TDP) which is part of IBM's Tivoli Storage Manager (TSM) as well as a regular SQL Server Agent job.

If a disaster occurred and I needed to recover the database from backup, would I be missing data?

I was thinking that if the SQL transaction log backup fires off, it will mark the committed transactions in the log file as ready to be overwritten.  After that happens, users will commit more transactions.  When TSM/TDP comes in, it will mark the committed transactions in the log as ready to be overwritten.  If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data.


texts: How to change the backup and archive destination to a network drive in oracle 11g windows

texts:
Due to space issue I want to change the backup and archive destination from FRA to a network drive in my system (I'm using windows). I've done the following steps to change the destination:

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO '\\192.168.20.200\HOME\DEVELOPMENT\JOE';

RMAN> configure channel device type sbt parms

sbt_library='//192.168.20.200/Home/Development/Joe/';

Now, when I'm trying to BACKUP DATABASE I'm getting the following error:

  ORA-19554: error allocating device, device type: SBT_TAPE, device name:
  ORA-27211: Failed to load Media Management Library


texts:
There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; like backup each table separately. However, the claim for better performance is questionable.

In my test, there is no difference in performance of innodb_file_per_table and ibdata1 for a database of 60GB. Of course, it was a simple test with normal queries, and the situation can be different for complicated queries in real life (this is the reason that I asked this question). 64-bit linux with ext4 can effectively handle large files.

With innodb_file_per_table, more disk I/O operations are needed; and this is significant in complicated JOINs and FOREIGN KEY constraints.

Tablespace is shared on single ibdata; how dedicated tablespaces for separate tables can save disk space? Of course, it is easier to free table space for each table with ALTER, but it is still an expensive process (with table lock).

QUESTION: Does innodb_file_per_table has an effect on a better performance of mysql? If yes, why?


texts: Error "ORA-0091:Invalid Character" when executing a PL-SQL statement in Oracle

texts:
I'm new to oracle pl-sql and I'm facing error ORA-0091:Invalid character when executing this query below. But when I comment the Execute Immediate statement and use dbms_output.put_line the result of the variable qry is fine and there is no problem with the query inside qry variable.

declare
qry                  varchar2(5000);
effdate              date := to_date('5/19/2020', 'mm/dd/yyyy');
user_id              varchar2(1000) := 'pantea';
search_number        varchar2(50)   := '8080';
deposit_filter_query varchar2(5000) := '  deposit_group_code =  -1
                                    and deposit_group_desc =  -1
                                    and currency_desc      = ''-1''
                                    group by customer_num
                                    having sum(dep_avg_balance) > 1;';

begin

  qry := 'insert into dep_filter_cust
           select distinct customer_num ,''' || search_number || '''
              from vmi_customer_deposit_detail v
                inner join branchlf_user b
              on b.userid = ''' || user_id || '''
                and dep_branch_cod = v.dep_branch_cod
           where effective_date = to_date(''' ||
             to_char(effdate, 'mm/dd/yyyy') || ''','' mm/dd/yyyy'') and ' ||
              deposit_filter_query;

  --dbms_output.put_line(qry);
  execute immediate to_char(qry);
         commit;

 end;

I was wondering if you could help me finding the problem here . Thanks in advance.


texts: Cannot find DReplay.exe for SQL Server 2019

texts:
I have been working on SQL Server 2019 version and trying to setup a load test environment using SQL Server Distributed Reply Controller - Client, however I cannot find the DReplay.exe. I thought the it will be installed as part of the installation of Distributed Reply Controller, but it didnt. Some articles suggest that it will be installed as part Management Tools from the installation media for SQL Server but the Management Tools option has been removed from the installation option.

There is also no link online to suggest an alternative way to get the DReplay.exe installed. Running out of options now.

As a desperate measure, I did try to copy the  the "C:\Program Files (x86)\Microsoft SQL Server\150\Tools\Binn" folder content from an older server where it was installed to the new SQL Server 2019 which did not work, throwing error "Failed to load assembly...."

Any ideas any suggestions please?? Thank you.


texts: SQL Server Upgrade - A new Approach for Upgrading

texts:
Microsoft's technical guide for SQL Server upgrade references two upgrade paths: in-place and side-by-side.

I don't see a third, visibly more viable/convenient option, which is to do an in-place upgrade of a cloned version of the server/SQL Server. Here is what I mean: why not clone the server in question, then perform an in-place upgrade, and if all goes well, then one could simply create an ALIAS and then have all applications and users automatically re-routed to the new server. Are there any downsides to this? Is there something I am not seeing? Is using an alias to redirect users and apps not wise?

ASSUMPTIONS:

I am assuming that this is a simple case of upgrade: no mirroring, no replication, etc. This is just one server with a number of databases that need to be upgraded.
I am assuming that direct upgrade is supported. Specifically, in this case, it's from SQL Server 2008R2 to 2016.


texts: Setup the user for edit/view permission to assigned one assigned entity of three (tables) available

texts: I have three entities of store, business, and corporation. User can edit/view/etc (permission) of entities they are assigned to. User can only be assigned to one to the related entities. The above entities can have multiple of all the users in the database.


texts: oracle database - sql for counting the columns with the same id with the one in current row

texts:
I have an oracle database and a few tables and with this query:

select rap.DENUMIRE,a3.ID,op.FK_ID,
    (SELECT COUNT(*) FROM ANEXA3_TABEL1_ROWS) AS count
from ANEXA3_TABEL1_ROWS a3
inner join RAPORTORI_AMB rap on rap.id = ch.FK_ID_RAPORTOR
inner join ANEXA3_TABEL1_OP op on op.FK_ID = a3.ID

the result is:

DENUMIRE    ID  FK_ID   COUNT
SC AAA      720 720     7
SC AAA      720 720     7
SC AAA      720 720     7
SC AAA      741 741     7
SC AAA      720 720     7
SC AAA      720 720     7
SC AAA      742 742     7

and I'd want to select in COUNT the number of columns with FK_ID equals to the ID in the current row. In this example, I'd want the following result:

DENUMIRE    ID  FK_ID   COUNT
SC AAA      720 720     5
SC AAA      720 720     5
SC AAA      720 720     5
SC AAA      741 741     1
SC AAA      720 720     5
SC AAA      720 720     5
SC AAA      742 742     1

Thanks in advance!


texts: mysqldump dies on the same row. Analyze is fine. Timeouts are long

texts:
I have an innodb table with about 70M rows. Recently a cron job to back it up with mysqldump started failing at the same row.

Mysqlcheck also fails.

Mysqlanalyze runs fine and prints OK.

Alter table also fails with an orphan temporary table starting with # which I need to nuke later.

Here is the configuration:

skip-external-locking
event_scheduler = ON
innodb_buffer_pool_size = 6G
**wait_timeout = 3600**
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
innodb_flush_log_at_trx_commit  = 1
sync_binlog     = 1
bind-address            = 192.168.0.100
key_buffer_size         = 32M
max_allowed_packet      = 64M
thread_stack            = 192K
thread_cache_size       = 8
tmp_table_size          = 3G
max_heap_table_size     = 3G
myisam-recover-options  = BACKUP
max_connections        = 500

Host is on ubuntu 16.04.1, MySQL  Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using  EditLine wrapper.

Any insight is highly appreciated.


texts:
I create a foreign key constraint and know that there are no violations, so I mark it as NOT VALID to skip the check.
From the documentation:

But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

It doesn't mention the consequences – not even in the notes –, if there are any at all.
Does it matter if a constraint is not marked as valid forever?


texts: SQL Server 2012 Developer edition questions

texts:
I'd like to use SQL Server 2012 Developer Edition on our development/test server, which is a dedicated server rented from an online company.

When the product is ready, I would like to have it deployed on production server(s) using SQL Server 2012 Web Edition, by hosting with companies that offer SQL Server Web Edition through their SPLA.

Is this possible and the correct use of the Developer and Web editions?
Does Web Edition allow one to create multiple databases on the same physical server, and if so, how many?
How can I buy a license online for the developer edition? All online sales seem to be for physical media, and I don't have physical access to the development server, so the only option seems to be, to rip the CD/DVD and then upload it to the development server which would be a tedious process. I would like to download SQL Server directly on the development server, buy the development edition license online, and then put that license into SQL so it is properly licensed.

Thanks.


texts:
Is it possible for a  SELECT query to block INSERT / ALTER queries?

I have the following (taken from ENGINE INNODB STATUS) on my slave database:

---TRANSACTION 26DA7060, not started
mysql tables in use 5, locked 12
MySQL thread id 516232, OS thread handle 0x7fcf8304a700, query id 350388626 event_scheduler Waiting for table metadata lock
ALTER TABLE xxxxxxxxxxxx REORGANIZE PARTITION . . . .

---TRANSACTION 26DA705F, ACTIVE 201692 sec
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7fc99aa91700, query id 350388642 Waiting for table metadata lock
INSERT INTO xxxxxxxxxxxx VALUES (. . . . . . . . . . . . .)

---TRANSACTION 2499F3C3, ACTIVE 1031443 sec fetching rows
mysql tables in use 11, locked 0
MySQL thread id 338078, OS thread handle 0x7fcf830dd700, query id 260018270 127.0.0.1 xxxxxxxxxxx Sending data
SELECT . . . .
Trx read view will not see trx with id >= 2499F3C4, sees < 2486B035

---TRANSACTION 2499EB0F, ACTIVE 1031500 sec fetching rows
mysql tables in use 11, locked 0
MySQL thread id 338037, OS thread handle 0x7fcf8307b700, query id 260008885 127.0.0.1 xxxxxxxxxxx Sending data
SELECT . . . .
Trx read view will not see trx with id >= 2499EB10, sees < 2486B035

---TRANSACTION 2486B035, ACTIVE 1091724 sec fetching rows
mysql tables in use 11, locked 0
MySQL thread id 330522, OS thread handle 0x7fcf8313f700, query id 255888600 127.0.0.1 xxxxxxxxxxx Sending data
SELECT . . . .
Tr  Trx read view will not see trx with id >= 2486B036, sees < 2486B036

They all use the same table.
The SELECTs are identical queries, and have a couple of joins to little used tables.
The INSERT is a simple INSERT with no joins.
THE ALTER is a simple ALTER TABLE REORGANIZE PARTITION.

The top one (ALTER TABLE) should have started about 48 hours earlier.
The INSERT about two minutes before the ALTER.

I can understand why the INSERT would block the ALTER, but can't see why the SELECT would block the INSERT.
And if it really had been running that long, it should have blocked previous INSERTs (we INSERT about 250 times a minute on average).

So I'm just trying to figure out what had happenned.


texts: What are my options in creating this Location table?

texts:
Say I have an Items table. Each item has a location associated to it. This location can either be a city, a state or a country. I believe this could normally be solved with a column Type in the Location table.

However, a city is part of a state and a state is part of a country. So when I add a new item and associate it to a city, for example, I also want it to be associated to that city's state and that state's country.

What are my options in creating this location table and how do I relate it to the Items table?

PS:
My approach so far was to keep using a type column but also create a foreign key in the location table that references that same table. This key indicates which record is the next up in the hierarchy (city->state->country). My conclusion is that this might work, but I'll have some relativelly complex selects (which could in turn slow everything down) and the application will have to deal with some of the work.


texts: Avoiding performance hit from GROUP BY during FULLTEXT search?

texts:
Is there any clever way to avoid the performance hit from using group by during fulltext search?

SELECT p.topic_id, min(p.post_id)
FROM forum_posts AS p
WHERE MATCH (p.post_text) AGAINST ('baby shoes' IN BOOLEAN MODE)
GROUP BY p.topic_id
LIMIT 20;

In this example it's fetching the lowest post_id for unique topic_ids that match the text.

With the group by to find the min, it's taking 600ms in a million row database, with about 50K rows examined.

If I remove the MIN but leave the GROUP BY, it's the same slowness, so it's the GROUP hit.

I suspect this is because it can only use one index, the fulltext ?

key: post_text | Using where; Using temporary; Using filesort

Query_time: 0.584685  Lock_time: 0.000137  Rows_sent: 20  Rows_examined: 57751
Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0

Without the GROUP BY it's 1ms so this has to be filesort speed?

(I've removed ORDER BY and everything else to isolate where the hit is)

Thanks for any insight and ideas.

(using MyISAM under mariadb if it matters)


texts: Python script leaves the database in restoring mode

texts:
I am attempting to use python to automate some of our database restores.  When I run the SQL query the database restores and comes back up like normal, but when I run the python script that uses the same SQL query the database is left in the restoring mode.
Any help with what I'm doing wrong would be greatly appreciated.
import pyodbc

#Set these Variables
sourceserver = 'MySourceServerName'
destinationserver = 'MyDestinationServerName'
dbname = 'test'

sourceserverplusport = sourceserver + ',1433'
destinationserverplusport = destinationserver + ',1433'

def ExecSQL(serverplusport, dbnameIN, sql):
    try:
        connexecsql = pyodbc.connect('Driver={SQL Server};'
                                         'Server=' + serverplusport +';'
                                         'Database=' + dbnameIN + ';'
                                         'Trusted_Connection=yes;')

        #Set AutoCommit
        if connexecsql is not None:
           connexecsql.autocommit = True

        print(sql)
        cursor = connexecsql.cursor()
        cursor.execute(sql)
    finally:
        cursor.commit
        cursor.close
        connexecsql.commit
        connexecsql.close

sqlcommand = r"""IF @@SERVERNAME = 'MyDestinationServerName'
RESTORE DATABASE test FROM DISK = '\\MyBackupPath\test.BAK' WITH FILE = 1, MOVE N'test' to N'X:\Database\MDF\test.mdf',  MOVE N'test_log' to N'Y:\Database\LDF\test_log.ldf',  NOUNLOAD,  REPLACE, RECOVERY, STATS = 5;"""

ExecSQL(destinationserverplusport, "master", sqlcommand)

If I add one more ExecSQL command at the end of the script to run RESTORE DATABASE test WITH RECOVERY; at the end returns the error:

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

Including the REPLACE with the RECOVERY removes the error but leaves the database in restoring still.


texts: Mount mdf file to sql server desktop edition

texts:
I'm trying to attach a .mdf file to a SQL Server Desktop Edition.

After reading this I connected to the database using the

osql -E -S servername\instancename

command.

I tried to attach the .mdf file with the following command

EXEC sp_attach_db @dbname = N'mydb',
     @filename1 = N'C:\Backup\mydb.mdf',
     @filename2 = N'C:\Backup\mydb.ldf'`

and then I get the following error.

  Msg 602, Level 21, State 50, Server serverName, Line 1
  Could not fine row in sysindexes for database ID 5, object ID 1, index ID 1.

I don't really know what any of this means I have never used osql before.


texts: where clause: if this value from col x make sure col y is this value

texts:
I feel like this is an easy one for you Experts!  I haven't done any kind of Advanced SQL in years.

Fundamental question:

select
    *
from myTable
where ((if col1 = 'this', make sure col2 = 'that')
else, don't include it in the results)

I would want to include all of this in my result set EXCEPT the first row, because where col1 = 'this'  col2 doesn't = 'that'

I want to only check where col1 = 'this' .. other values I want to include in the results.

How do I do the above logic in SQL Oracle.  I don't want to use PL/SQL  just plain SQL.
I was thinking there was a clever way to do this without some weird if/then/else statement

Thank you,
Mike


texts: Is there an equivalent to sys.dm_exec_query_memory_grants for non-waiting queries?

texts:
Basically I have a proc that executes and requires massive amounts of memory. We can sometimes get into a situation where the proc executes multiple times, in which case the memory grants are visible in sys.dm_exec_query_memory_grants, because theyre waiting to be fulfilled.

We're trying to refactor the query so that it doesnt need such large amounts of memory, but its hard to get a baseline because we have to put the server in an unusable state to get the info from sys.dm_exec_query_memory_grants. Is there an equivalent to this view for any currently running query, not just those waiting or that have waited for grants? We're using SQL Server 2008.


texts: Replicating only significant records to specific database

texts:
I have a remote database which holds data for various databases in various locations. I want to do database replication, but only data specific to a certain database should be replicated to it. I can identify the data using a column id.

i.e if my id starts with dba23 then this data should be replicated to database with the dba23 in this case collection_db.

Am using dblink currently, but I want a real-time solution.

suggestions are highly welcome


texts: Can't see connexion to Postgresql using php

texts:
I have created a php form to connect to a remote postgresql database. It seems that I can log in successfully because my message "connexion succeeded" appears.
But I can't see the corresponding line in the pg_stat_activity view.
Does it mean that the connexion is closed just after completion ? or is there some other reason ?
Thanks


texts: Identifying connection and publisher vs subscriber in merge replication scheme

texts:
We have an app that uses a central SQL Server 2008 database for folks here in the building on the domain.  For teams out in the field - they connect to a local version of SQL Express while offline and then sync with the main server when able via merge replication.  Both inhouse and field teams use the same application on top of the database.  The application doesn't care if it's attached to the publisher database or subscriber.

From time to time we need to run a stored procedure on the subscribers.

My first thought is to write into the procedure an if begin end statement that checks for the existence of the dbo.MSsubscription_properties table in the System Tables of the database.

Since this table doesn't exist at the publisher (as long as the publisher it's self doesn't subscribe to some other publication - which we have no plans on doing) it seems to be an easy way to distinguish that the connection is attached to a subscriber vs the publisher.

Is there a better (documented) way to identify subscriber vs publisher that I'm overlooking?  Any reason why my approach would produce an unpredictable result.


texts: help in the initial configuration of postgreSQL9.6

texts:
I recently installed postgreSQL9.6 version on windows 7 64 bit machine. During the installation process, I was asked to key in the super user password and the locale of the database (I chose default locale). Now during installation, there was a step by the name "server clusterinitialization". This step failed and I got the error message for the same. However, the server got installed successfully.

Now when I launched the postgreSQL9.6 command line application, I am required to provide some information. I am not sure what are they exactly asking for. So, I am giving a snapshot of the fields required.

As evident, the prompt asks for the Server [localhost]:, Database [postgres]:, Port [PG_PORT]: and username [PG_USERNAME]:

The installation instructions said that for these fields I am supposed to enter the default values. What are the default values for these fields. I only know the port number and superuser password.

Basically, I do not know what information needs to be entered in each of these fields.

Also, when I tried to add a server using pgadmin III, I get the following error

**

Server doesn't listen
    The server doesn't accept connections: the connection library reports
    could not connect to server: Connection refuse (0x0000274D/10061)     Is   the server running on host "localhost" (::1) and accepting TCP/IP     connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?
If you encounter this message, please check if the server you're trying to contact is actually running PostgreSQL on the given port. Test if you have network connectivity from your client to the server host using ping or equivalent tools. Is your network / VPN / SSH tunnel / firewall configured correctly?
For security reasons, PostgreSQL does not listen on all available IP addresses on the server machine initially. In order to access the server over the network, you need to enable listening on the address first.
For PostgreSQL servers starting with version 8.0, this is controlled using the "listen_addresses" parameter in the postgresql.conf file. Here, you can enter a list of IP addresses the server should listen on, or simply use '*' to listen on all available IP addresses. For earlier servers (Version 7.3 or 7.4), you'll need to set the "tcpip_socket" parameter to 'true'.
You can use the postgresql.conf editor that is built into pgAdmin III to edit the postgresql.conf configuration file. After changing this file, you need to restart the server process to make the setting effective.
If you double-checked your configuration but still get this error message, it's still unlikely that you encounter a fatal PostgreSQL misbehaviour. You probably have some low level network connectivity problems (e.g. firewall configuration). Please check this thoroughly before reporting a bug to the PostgreSQL community.

**

I am trying to create a local server on my machine so that I can learn postgreSQL.

As suggested in the comments section, I tried to find the postgresql.conf file to which I got postgresql.conf.sample file. I am posting the contents of this file in the form of a snapshot.

Do I need to remove the # to make the configuration work?


texts:
I have a sql server user that has the ability to drop any database. I've been running the code below to check the rights that the user has in SQL Server but have not been able to identify how the user has the ability to drop databases.  Is there a sql script that can help me identify how this user can drop dbs?  Is there a command to deny them dropping any databases?  (SSMS is not showing the user as part of the dbcreator role)

select USER_NAME(p.grantee_principal_id) AS principal_name,
    dp.type_desc AS principal_type_desc,
    p.class_desc,
    OBJECT_NAME(p.major_id) AS object_name,
    p.permission_name,
    p.state_desc AS permission_state_desc
from    sys.database_permissions p
inner   JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id
order by principal_name

The output of the query above provides the following three records for the user if it is helpful

  class_desc    object_name permission_name permission_state_desc
  OBJECT_OR_COLUMN  xp_cmdshell EXECUTE          GRANT
  DATABASE          NULL           CONNECT             GRANT
  DATABASE             NULL           CREATE DATABASE GRANT


texts: Can anyone recommend a beginner DBA course in London

texts:
My organisation is in need of a SQL Server DBA and as the companies only "SQL Guy" my boss has suggested me to take SQL Server DBA course.  My general T-SQL knowledge is fairly good, I can write fairly complicated queries, have done stuff with stored procedures etc.  But when it comes to proper DBA knowledge, is where I fall down.  All my previous role had a DBA team who guarded their knowledge jealously so I am missing lots of best practice knowledge on how to deal with SQL server.

Can anyone recommend a good beginner SQL Server 2012 DBA course in London (UK)?

Thanks

Ian


texts: Adding new key(s) to enable partitioning, INSERT performance?

texts:
I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 different devices)  during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was thinking of create a partition in the table by Date or DeviceID.

In that case I will need to add the partitioning keys as primary keys?, how will that affect the insertion performance?

CREATE TABLE `carlocation` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Date` datetime DEFAULT NULL,
  `GpsLatitude` double DEFAULT NULL,
  `GpsLongitude` double DEFAULT NULL,
  `DeviceID` int(11) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

The server is a Dual Core Xeon with 2GB RAM.
Thanks.

Update: The queries are like

SELECT *
FROM carlocation
 WHERE ((carlocation.DeviceID = 2) AND (carlocation.Date> '2010-01-01') AND (carlocation.Date< '2010-01-02'))

Always quering for ONE device only, in one or multiple days.


texts: PHP MYSQL Update Two Tables Using Cross Joins

texts:
I am running into a problem updating my two tables at the same time.
My first table res which needs to be updated every time the other table
rest gets input from a HTML Form.

Table res=> http://sqlfiddle.com/#!9/82bfc2/2 . column id =>Primary Key
This table holds all the information related to Train Journey. This has columns(ie. col AC_I=no. of tickets at the begging, book= tickets booked and bal=balance of tickets from the total tickets)
 which describe against which Train how many tickets are available, how many were booked and what's the current balance when the user is logged in to book their tickets.

My Second table rest  http://sqlfiddle.com/#!9/557e4. column PNR =>Primary Key
This table takes input from users.

My question is how to update table res and update columns which is created to store the tickets booked for each class and for a particular train name or train number.

For example: IF customer books a seat in TRAIN NAME ='A', CLASS='AB' then my other table with the same row having a column "BOOK" will auto-increment.

What I tried is My SQL Trigger and Join.
Both are not working at this time.

Trigger=>

DELIMITER $$
DROP TRIGGER IF EXISTS t1i $$
CREATE TRIGGER t1
  AFTER INSERT ON rest
  FOR EACH ROW
  BEGIN
    UPDATE res
      SET book = 1
      WHERE Tr_Num = NEW.Tr_Num;
  END $$
DELIMITER ;

JOIN=>

UPDATE res
JOIN rest ON res.id = rest.PNR and res.TRAIN_NUMBER=rest.Tr_Num
SET res.book = 1
where case when rest.class ='AC I' then res.book=1
           when rest.class ='AC II' then res.book=1
           when rest.class ='AC III' then res.book=1
      end

Result shows 0 Rows affected

Kindly assist.


texts: Retire a SQL instance without impacting the primary server?

texts:
I have a SQL Server 2008 instance (TST01) that has some databases on it that are being migrated, still a few weeks of work to finish up, before the server is retired, this is a Virtual Server. There is also a separate instance on the server (TST01/ATST01) which is no longer required.

In theory I can run an uninstall and only uninstall the instance (TST01/ATST01). But I believe this will require a restart. As with any uninstall or update there is also some risk, and this is not worth any significant risk to the primary server.

I have off lined the user databases, disabled jobs, and maintenance plans.

This particular instance also has SSRS (built but never used) on it.

Is there anything else I can or should do?

The goal is to release as many resource to the VM pool, as practical, pending the full server retirement.


texts: SQL Server: Same backup copy is verified in the original server (SQL Server 2008 Standard) but not in other server (SQL Server 2008 Enterprise, ...)

texts:
I have got a production server with SQL Server 2008 Standard version doing backup copies using maintenance plans. I am doing one full copy on Sundays and one differencial copy the rest of the days of the week.

The problem comes when I take the copies to other servers to restore them. In the original server I verify the bak file is OK, but when I do the same on the other servers, it says:

  Msg 3241, Level 16, State 0, Line 5
  The media family on device '[path].bak' is incorrectly formed. SQL Server cannot process this media family.
  Msg 3013, Level 16, State 1, Line 5
  VERIFY DATABASE is terminating abnormally.

I use this script to verify the database:

DECLARE @full_fillename varchar(1024);

SET @full_fillename = N'[path].bak';

RESTORE VERIFYONLY
FROM DISK = @full_fillename
GO

IF @@ERROR = 0
BEGIN
   PRINT 'Backup usable'
END
ELSE
BEGIN
    PRINT 'Backup not usable'
END
GO

I test this on my local machine with SQL Server 2008 Enterprise, SQL Server 2012 Standard, SQL Server 2008 Express, SQL Server 2012 Express. But this only works on the original server where the backup is done.

Other notes about the problem:

To restore the database I need to use MOVE commands. For example I use this script to restore the backup file:

RESTORE DATABASE [db_name]
    FROM DISK = '[path].bak'
    WITH
        FILE = 1,
        MOVE N'[db_name]_cs_as' TO N'C:\Archivos de programa\Microsoft SQL Server\MSSQL10.CLASS\MSSQL\DATA\[db_name]_cs_as.mdf',
        MOVE N'[db_name]_cs_as_log' TO N'C:\Archivos de programa\Microsoft SQL Server\MSSQL10.CLASS\MSSQL\DATA\[db_name]_cs_as_log.ldf'

See that my database name is "XXXX" and the files to move are called "XXXX_cs_as" and "XXXX_cs_as_log". This issue is because I rename the database in the past.

UPDATE

Rename logic and physical names does not solve the problem.

This is fustrating. The original server is a SQL SERVER 2008 STANDARD SP4, and the backup created by this server cannot be restore in any other SQL SERVER that I tested (SQL SERVER 2008 ENTERPRISE, SQL SERVER 2008 EXPRESS, SQL SERVER 2012, etc).

UPDATE 1

Server: Windows Server 2008, SQL SERVER 2008 STANDARD SP4

select @@version

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)   Sep  3 2014 04:11:34   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Other computer: Windows 8.1, SQL SERVER 2008 STANDARD SP4

select @@version

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)   Sep  3 2014 04:11:34   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

In other computer I cannot restore the backup, neither execute this script:

RESTORE FILELISTONLY
FROM DISK = @full_fillename

Can Windows 8 be the problem?

UPDATE 2

I've downloaded the SQL SERVER 2008 ENTERPRISE EVALUATION, I install this version on the original server and my computer (windows 8).

I create a new database in the original server and import a script with the content of the database with the problem (there are only tables and rows).

I make a backup from the new SQL SERVER 2008 ENTERPRISE. I take the backup (.bak) and put it in my computer with the same version of SQL SERVER.

The backup cannot be restored!

UPDATE 3

At this time, the problem must be Windows 8, because I virtualized one WS2008 and the backup was restored without problem.


texts:
I have built an application as part of my UG thesis that uses mysql database (community edition). Now, my professor wants me to provide fault-tolerance to the database (by parity!!)! I argued that we wouldn't need that. Anyway I searched, and found here that mysql has built-in replication engine (a mechanism to provide fault-tolerance, right?), along with many other techniques to provide reliability. But what I learned from there is that there is a master server and some slave servers to provide the fault tolerance using replication. Now my questions are:

What if I have only one database server? Does mysql have any fault-tolerance for single standalone database servers (i.e. no master-slave formation, no cluster, etc.)?
Do I need to try to provide any kind of fault-tolerance whatsoever for the data stored in mysql database?
What kind of differences are there (in terms of fault-tolerance only) between community edition and enterprise edition of mysql servers?

Somehow I get the feeling that we don't need to do anything for providing fault-tolerance to mysql db, and it is fine by itself. But I need some solid info on the matter.

Bounty edit:

The second question from above again:

Do I need to try and provide any kind of fault-tolerance whatsoever for the data stored in mysql database?
What is the sensibleness of trying to provide fault tolerance to a mysql database by using parity (bit per byte)?

A little details about the data in the database:

The data to be specific is a collection of about 6500 Unicode strings, less than 1 MB in size, is initialization data, that will never change over time. The only transaction will be to read the data from the database, no update no delete. My application requires full-text search on those strings, and this is the only reason I am using mysql since it provides full-text search. I am aware that I could avoid mysql's FT search by using something like elasticsearch instead.


texts: Compare datetimes with dfferent timezones in MySQL

texts:
I have a field `opened_at` in MySQL table that stores datetimes in string format with different timezones:
2021-01-30T18:07:40+02:00
2021-01-31T01:32:40+05:00
2021-01-31T21:17:40+03:00
etc.

I need to SELECT rows from this table with values in `opened_at` within the specified local date of local timezone, for example 2021-01-31 +02:00.
The problem is that when I cast `opened_at` to datetime I loose timezone offsets, so I can't do correct comparison. For example:
mysql> SELECT CAST("2021-01-30T18:07:40+02:00" AS DATETIME) = CAST("2021-01-30T18:07:40+00:00" AS DATETIME);
+-----------------------------------------------------------------------------------------------+
| CAST("2021-01-30T18:07:40+02:00" AS DATETIME) = CAST("2021-01-30T18:07:40+00:00" AS DATETIME) |
+-----------------------------------------------------------------------------------------------+
|                                                                                1 |
+-----------------------------------------------------------------------------------------------+

But "2021-01-30T18:07:40+02:00" is not equal to "2021-01-30T18:07:40+00:00". There's a two-hour difference between them!
I've read this, but the way in winning answer is too hard for SELECT from big number of rows.
Is there some quick and processor-efficient way to cast strings with times in different timezones for further comparison with some datetime interval?


texts: Migrate large postgresql database (windows -> linux)

texts:
I have a "large" PostgreSQL (9.6 with timescale plugin) database (about 1Tb). And would like to migrate it to a Linux machine. The naive approach would be pg_dump and pg_restore.
(Not sure how long that might take on a powerful machine with SSD on both sides, maybe you have an idea?)

Just wanted to ask if this is the way to go or if there might be a better more convenient solution. What I saw so far were pgBackRestore and barman.

I'm asking this question since I already had some trouble migrating a MongoDB database which took really very long. So now I would like to avoid errors : )

Thanks a lot for any hint!


texts: Set PostgreSQL table to LOGGED after data loading

texts:
I have created an empty UNLOGGED table to faster copy a large amount of data (over 1 Billion rows). Loading the data took around 4 hours.

Now I want to set the table to LOGGED to make it safe for unexpected shutdowns and crashes. This process takes a long time. In fact it takes longer than loading the data. Is this normal, or is there a way to speed it up?


texts: Copy large PostgreSQL table to multiple csv files with limited row number

texts:
Is there a way, using a sql function or an already existing psql function, to copy a very large table into multiple csv files and specify the maximum number of rows allowed in the output files ?

For example, a 35 million rows table would output 35 csv files of 1 million rows.


texts: How to do a Join with optionally matching columns

texts:
I have two tables as follows (in a Postgres 10 database):

Maize:

id|acreage|date      |region
1 |20     |2018-10-18|A
2 |10     |2018-10-07|A
3 |26     |2018-10-18|B
4 |06     |2018-10-07|B

Paddy:

id|acreage|date      |region
1 |17     |2018-10-18|A
2 |29     |2018-10-09|A
3 |24     |2018-10-18|B
4 |09     |2018-10-09|B

I want to get the following result:

Maize|Paddy|date      |Region
20   |17   |2018-10-18|A
Null |29   |2018-10-09|A
10   |Null |2018-10-07|A

Do note that when the dates do not match, I would like to get a Null in the cell.

I've been trying to write a query like this, but I only get the first row, and not the 2nd & 3rd Rows:

Select c1.acreage as "Maize", c2.acreage as "Paddy", c1.date, c1.region FROM
Maize c1
FULL OUTER JOIN Paddy c2 ON c1.date =c2.date AND c1.region=c2.region
order by c1.date;

Obviously the date in the Join is causing the issue; But I don't know how to remove it, and still get the Matching values in one row.


texts:
Using Postgres 9.4, I have a table page_views which has a page column. I'd like to select all the distinct pages and have a count before them and sort them descending. I'd also like to have a minimum count of 20 in the past 24 hours. So that it looks like this:

120 / home
 56 / about
 24 / locations

I was thinking:

select count(*),page from page_views group by page where count(*) > 20;

but this does not work. How to do this?


texts: Is it possible to set the prompt to a variable - MySql - MariaDB

texts:
I would like to change the prompt to my mariadb or mysql client.

I would like the prompt to be the contain of a variable like report_host or more, to something that can change like default_master_connection.

Does someone know how to do it?


texts:
I have below query:

select entity_id from table1 exc
UNION
select entity_id from tabl2 inc

On execution, I got 24419 rows.

When I execute select entity_id from table1 exc I got 16235 rows and on the execution of select entity_id from tabl2 inc I got 35943 rows.

Below is the explain result of above query:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: exc
         type: index
possible_keys: NULL
          key: IDX_ENTITY_ID
      key_len: 4
          ref: NULL
         rows: 15603
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: inc
         type: index
possible_keys: NULL
          key: IDX_ENTITY_ID
      key_len: 4
          ref: NULL
         rows: 32697
        Extra: Using index
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
3 rows in set (0.00 sec)

Table structure of table1

Create Table: CREATE TABLE `table1` (
  `entity_id` int(11) NOT NULL DEFAULT '0',
  `rule_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`entity_id`,`rule_id`),
  KEY `IDX_ENTITY_ID` (`entity_id`),
  KEY `idx_rule_id_jos_tag_rules_content_exclude` (`rule_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

Table structure of table2

Create Table: CREATE TABLE `table2` (
  `entity_id` int(11) NOT NULL DEFAULT '0',
  `rule_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`entity_id`,`rule_id`),
  KEY `IDX_ENTITY_ID` (`entity_id`),
  KEY `idx_rule_id_jos_tag_rules_content_include` (`rule_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

If we see on algebric calculation behind Union operation then it should be max(No(a)+no(b)).
Now, I am unable to understand behind above result. How MySQL perform Union operation?


texts: Which file do I need to modify my.cnf MariaDB on Ubuntu 18.04

texts:
I want to modify these values:

[mysqld]
key_buffer_size    = 256M
max_allowed_packet  = 128M
thread_stack    = 256K
query_cache_limit  = 64M
query_cache_size        = 384M
innodb_buffer_pool_size = 1073741824
thread_cache_size = 256
back_log = 256
thread_handling = pool-of-threads

I've tried to edit the files that MariaDB says:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

But none of them seems to apply changes.

I'm running last version of MariaDB on Ubuntu 18.04
(10.1.34-MariaDB-0ubuntu0.18.04.1)

I've checked Stack Overflow before asking, including How do I find the MySQL my.cnf location


texts: mySQL changing output unit of ST_Distance function

texts:
I'm trying to get a result in nautical miles between two lon/lat saved in a point data type field in mySQL. However, the result seems to be not in any unit I'm familiar with (maybe degrees). How can I convert to my preferred unit?. My query as follows...

select t1.id, t2.id, ST_Distance(t1.test,t2.test) as DistanceApart from table1
t1 join table2 t2 on (ST_Distance(t1.test,t2.test) <= 10)


texts:
We have a thick client application using an Oracle 10g database. We recently upgraded the application and in the process migrated the database tables from a latin to unicode character set.

After the upgrade some fields behaved 'read only' from the GUI screens. We found that we had some wrong values and updated them using an SQL command line tool and these fields suddenly became editable in the application.

So we updated the fields for all the rows and the problem disappeared completely.

The two queries we used were:

update listings_text set text = 'Y' where text = 'y';

and

update listings_text set text = text;

Now the second query should be a noop. But its not. Can anyone explain why this query might have an effect?


texts: How to migrate MySQL Cluster (NDB) backup to a different ndb cluster?

texts:
I have two MySQL Clusters (NDB), Prod and Dev. Each cluster contains SQL nodes, Management nodes and Data nodes. Backups are done on one cluster using the article below:

https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-backup-using-management-client.html

How do I import the created backup file to separate MySQL Cluster? i.e. refresh dev cluster with prod data.

The backup seems to be tied to the cluster it was created on. Is there a way to do a 'restore' on a separate cluster with the backup file?


texts: do partitions make sense when dates are not involved?

texts:
We are having some discussions about partitioning some application tables in SQL Server 2008 based on their quickly growing size.  One is an event table that is typically used for INSERTS (80% of the queries) or SELECTS that look for the primary key of the table (20% of the queries).  The other table is a "mapping" table that is all SELECTS looking up the primary key.

Would partitions along the primary key be helpful here?  I've done a lot of reading and all the classic examples of partitioning seem to be data warehouse tables that are partitioned on dates.  It also seems that partitioning can sometimes cause more harm than good.

What do you think about these tables?