texts: MySQL (MariaDB) - "Data too long for column" for a very short value, not reproducible elsewhere

texts:
I have a table logs with a column info - the type is text. I get an error running the following query:

  insert into logs (date_time, info, log_code, company_id,
  updated_at, created_at) values ('2019-04-06 22:38:58',
  'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
  508, 410, '2019-04-30 01:23:45', '2019-04-30 00:00:00');

  ERROR 1406 (22001): Data too long for column 'Info' at row 1

Unfortunately the error can't be reproduced in another table. If I run:

create table test like logs

and then run the same query on test then it works.

Even with unicode this amount of data shouldn't be a problem for a data type that can hold over 65 kilobytes.

I've been looking for a solution for a couple hours and can't find anything. And it's especially odd that it can't be reproduced on another server or even on the same server in an identical table.

Results of describe logs:

+-----------------------+---------------------------------------+------+-----+---------+----------------+
| Field                 | Type                                  | Null | Key | Default | Extra          |
+-----------------------+---------------------------------------+------+-----+---------+----------------+
| id                    | int(10) unsigned                      | NO   | PRI | NULL    | auto_increment |
| company_id            | int(10) unsigned                      | YES  | MUL | NULL    |                |
| another_column1       | int(10) unsigned                      | YES  | MUL | NULL    |                |
| date_time             | timestamp                             | YES  | MUL | NULL    |                |
| log_code              | int(11)                               | YES  | MUL | NULL    |                |
| info                  | text                                  | YES  |     | NULL    |                |
| another_column2       | varchar(255)                          | YES  |     | NULL    |                |
| another_column3       | tinyint(1)                            | NO   |     | 0       |                |
| type                  | enum('log','event','warning','error') | NO   |     | log     |                |
| created_at            | timestamp                             | YES  |     | NULL    |                |
| updated_at            | timestamp                             | YES  |     | NULL    |                |
| deleted_at            | timestamp                             | YES  |     | NULL    |                |
| another_column4       | int(10) unsigned                      | YES  | MUL | NULL    |                |
+-----------------------+---------------------------------------+------+-----+---------+----------------+


texts: What are the practical limitations on a column family in Cassandra?

texts:
In Cassandra, it's not recommended to have more than a few thousand column families, let's say 2,000 for the sake of argument. In cases where more than 2,000 types of data need to be persisted, one approach would be to shard multiple unrelated types of data into each column family.

For example, a single CF could contain Orders, Invoices, and Customers, provided their row keys were distinct (e.g. prefixed with the object type, i.e. the keys of a single CF could include both Order|1234 and Customer|1234). A second CF could contain say Addresses, LineItems, and OrderTypes. Given the basic feasibility of this approach, what are the practical limits on it? For example, what would be wrong with putting all 10,000 types of object into a single CF? As far as I can tell from the Cassandra wiki, there is no hard limitation on the size of a CF.


texts:
I Googled how to get the username of the current user and was told to use:
SQL> sys_context('userenv', 'session_user')

But when I type that command in, I get the error:

SP2-0734: unknown command beginning "sys_contex..." - rest of line ignored

What am I missing? The Oracle version is Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production


texts:
I have a DB2 installed on windows 2012

Server name: test017330VM

OS: Windows Server 2012 R2 Datacenter

DB2 port:50000

DB2 Version:DB2 v11.1.1010.160

Database:dbtest.  (this is created by command 'DB2 Create database dbtest')

Local user:db2admin. (DB2 was installed under this account and it has the admin privilege on both windows and db2).

Password:Aa1!Aa1!;

I tried to run  Data Access Tool (v4) on the same server, but got the error immediate after I click the connect:

  Could not connect to data source 'New Data Source':
  An internal network library error has occurred. A network level syntax error has occurred.

Here is the connection string:

  Provider=DB2OLEDB;User ID=db2admin;Password=Aa1!Aa1!;Initial Catalog=dbtest;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=test017330VM;Network Port=50000;Package Collection=MSPKG;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/NT;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Datetime As Date=False;AutoCommit=True;Authentication=Server;Decimal As Numeric=False;Derive Parameters=False;LoadBalancing=False;Persist Security Info=True;Cache Authentication=False;Connection Pooling=False;

Please shed some light on it.

why it's a syntax error?
What is MSPKG  in the parameter Package Collection=MSPKG?
what is 'DB2/NT' correct?


texts:
I was just updated to Access 2016, while my users are still at Access 2010.

I opened our database in MS Access 2016 once, didn't make any changes, and closed it out, and now all the users are having errors for ANY basic VBA code, including:

StrConv (Me.FormField, vbProperCase)
DoCmd.GoToRecord
DoCmd.RunCommand acCmdSaveRecord

etc.

However, it works fine on my end. Totally broken for all my users on Access 2010. Is there any way to fix this? I am thinking, even if I go back to Access 2010, if these are errors for code that would normally work fine in either version, how to I resolve these errors?

Tried a compact and repair, no effect.

Here's an example: In Access 2010,  when the AfterUpdate() event is triggered on a textbox that has the StrConv (Me.FormField, vbProperCase) function, it brings up the VBA debugger.
The error is: Compile error: cannot find  project or library.

Previously, the exact same function worked fine in access 2010, and there have been no changes to this code. It works fine in a backup copy from about 15 days ago.


texts: SQL Server 2008R2 XML Column Index strategy for large tables?

texts:
I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being < 2012. I'm brand spanking new to the idea of indexing XML columns.

The Goals

I have two separate scenarios in which I need to query XML:

In one case, I need to query if a nested value exists across a table with 3 million rows

E.g. columnName.exist('Parent[1]/Child[1]/DifferentChild[1]') = 1'

In another case, I need to extract two values from XML into two columns so that I can match on it, in a table with 4.7 million rows.

E.g. SELECT columnName.value('Parent[1]/FilePath[1]', 'nvarchar(max)') as FilePath, columnName.value('Parent[1]/FileName[1]', 'nvarchar(255)') as FileName

Question

Given the cost of the indexes here and the fixed type of querying, what are indexes I can apply for maximum impact, and are there any pitfalls I should be aware of?


texts:
I'm trying to upgrade our database servers to MySQL 5.5. In doing so, I'd like to convert all of our tables to InnoDB to take advantage of several performance benefits.

However, we use VARCHAR(50) fields for all of our primary keys. From what I've read about InnoDB indexes, the shorter the primary key the better, since all secondary keys reference the primary key. So, instead of converting the tables by issuing an ALTER TABLE tablename ENGINE = InnoDB;, I'd like to create a new temporary table with all of the fields from the old table plus a new autoincrement primary key. The old primary keys will become unique keys. Then, I'll insert all rows into the new table, drop the old table, and rename the new table to the old.

However, I need a clever query to do so, because insert into tablename_temp '',* from tablename; generates a syntax error (I assume because I can't select anything with *). I'm trying to come up with an alternative, but I can't figure it out.

Short question: How can I insert into tablename_temp '',* from tablename;

Any input is much appreciated!


texts:
select DISTINCT id_request,username_request
from darkhast_follower
where darkhast_follower.id_request != '9762952594'
  AND darkhast_follower.id_request != 'null'
  AND darkhast_follower.status !=1
  AND NOT exists ( select *
                   from log_follow
                   where log_follow.other_id = darkhast_follower.id_request
                   AND log_follow.id= '9762952594')
LIMIT 5

Which column should be indexed? Or what is the best query to replace this?


texts: Periodically synchronise databases on 2 different servers

texts:
On my main development workstation I have around 20 databases. Every so often I will work from home on my laptop instead, where I also need a copy of these databases. Because working from home is fairly infrequent, the databases are often a long way behind my main development databases on my workstation.

Currently what I do is take a backup of the workstation database, and then restore it on my laptop, but this is becoming time-consuming. Especially since some software projects require multiple databases.

Is there a more automated way of achieving database synchronisation between my main workstation and my laptop?

Would something like replication come with its own problems? Ideally I want it as automated as possible.


texts: Why would adding a column to a view corrupted a table-valued function?

texts:
I have a TVP myTVP implemented as select val.* from vw_MyView val.  The other day, I modified vw_MyView, replacing:
select Foo,Bar from DB with
select Foo, Bash, Bar from DB.

This had a strange side effect: Calling select Bar from myTVP() returned a column named Bar populated with the contents of Bash (despite the fact that Bar and Bash don't even have the same type).

Anonymized plan demonstrating the problem: https://www.brentozar.com/pastetheplan/?id=ryJLJmqdl

Note that Object2 and Object1 are identical, but the output list of the left-most Nested Loop is completely different between the two plans.

Notes:

There are no plan hints in use.  No trace flags are set.
CheckDB found no errors.
Using option(recompile) did not fix the issue.
Running right-click alter on the TVP with no changes fixed the issue.
Running right-click alter on the TVP with a name change fixed the issue, but the new TVP was identical to the previous other than the name.
Using SQL 2014 Standard Edition.

Questions:

Is it normal for adding a new column to a view to change which column
of the view is used in by other references to that view?
Under what circumstances will adding a column to a view cause such behavior?


texts:
I'm creating the DB for a system and in it is an alert system, where, when a certain action happens, or when certain information is present, an alert (via email) is triggered. I'm allowing users to customize their own alerts so that they can receive alerts when they want, not when I, as the product engineer, thinks they should get them. I'm also allowing admins on the account to create alerts on behalf of other users, i.e. Boss can create alerts to be sent out to employees who have the responsibility to handle the alert.

So, there are three different ways that a user can be signed up for an alert: 1) all employees 2) all admins 3) manually decide which users get it.

I've decided that having a column is_all_users and is_admin as booleans is the right approach to handling those scenarios, but the question is, when dealing with "manually deciding users" is it ok to be a string of user ID's that then get parsed (I don't anticipate there ever being more than 5 recipients in this scenario), or should I make it a separate table?

The two schemas are as follows:

alerts

client_id (int)
alert_id (int)
etc. etc.
recipients (string)
recipients_is_admin    (boolean)
recipients_is_all (boolean)

OR

alerts

client_id (int)
alert_id (int)
etc etc.

alerts_recipients

alert_id (int)
user_id (int)
recipients_is_admin (boolean)
recipients_is_all (boolean)

In scenario 1, there could be a speed and/or parsing concern but scenario 2 just seems unwieldy and unnecessary. Of course, in the second scenario, I could also put recipients_is_admin and recipients_is_all in the first table, but then I'd be dealing with two different tables for a single purpose which just seems wrong.


texts:
I'm creating a split database in Access which has the backend on a drive accessible to all employees in the company at the moment. This isn't such a big deal at the moment because I'm still developing it and have a backup of the file.

The main reason I did this was because the protected drive I intended to use didn't allow read/write access and stopped my VBA code from executing.

As the database goes live it will be important that no one can move or delete my backend file, but I'm unsure how to approach this issue. Is there anything I can do to protect/hide this file to stop anything happening to it?

(Backing up will be done regularly regardless)

Thanks!


texts: CROSS JOIN and updatable model design for Ruby on Rails

texts:
I am trying to build the multi-level model in rub on rails to allow the following to happen: When a user creates a campaign they will select some categories and some metros from tables Category and Metro. Those selections will then be placed within two tables as campaign_category and campaign_metro. But upon the creation of those entries I wish for then another table to be populated with the permutation of those combinations within that table or a series of tables to allow for linking the campaign, the category and metros selected for a user to set a bid price on.

For example say a user creates a campaign that has id 10 and selects category with id 1 and 2, and metros with id 3 and 4. In the tables campaign_category appears:

and in table campaign_metro appears:

How can I allow for the third level table (or series of tables) to be populated with the total permutations of selected category and metro upon the creation of a campaign and also work with ruby on rails active record constructs?


texts: SQL Always On automatic failover incase of multiple secondary

texts: I have three node setup for SQL Always on in SQL 2017 enterprise edition. All three are in same subnet and in synchronous commit mode. Now when there is a failover how do I make sure it should always choose node-2 to be made primary and stall node-3 for being primary.


texts: Generating an expiry date based on fields in different tables

texts:
I want a field that gives the date a certificate expires.

I think i would create a query and add a field using something like this..

dateadd("d", [DaysToExpire], [DateCompleted])

My trouble is, [DaysToExpire] is on a different table to [DateCompleted]. how do i resolve that?

Is there a way to get the query field to refer to both tables?

Can I create a lookup field on one of the tables that auto populates once the 2 tables a linked by a record?

I am all pretty new to this so sorry if my language is a bit off. Any help would be much appreciated.

The code generated by access:

SELECT tbl_Courses.CourseName
     , tbl_Courses.CourseDescription
     , tbl_Training.DateCompleted
  FROM tbl_Courses
 INNER JOIN tbl_Training
    ON tbl_Courses.CourseID = tbl_Training.CourseID;

Error after entering Mr.Brownstone's code:


texts: MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

texts:
I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as  it fails attempting a 'CREATE TABLE, which it should not be doing as the table already exists!  The output is below:-

ERROR 1114: The table 'Analysis' is full
SQL Statement:
ALTER TABLE `Patient`.`Analysis` ADD COLUMN `TestDateTime` DATETIME
NULL AFTER `idResults`

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'Patient' already exists
SQL Statement:
CREATE TABLE `Patient` (
`idAnalysis` int(11) NOT NULL,
`idResults` int(11) DEFAULT NULL,
`ResultPDF` mediumblob,
`ResultCSV` mediumblob
) ENGINE=InnoDB DEFAULT CHARSET=latin1

How can I add this extra column as this database will evolve over time as user requirements expand?

Thank you.


texts: Can't change ORACLE_SID for container-registry.oracle.com/database/enterprise

texts:
I'm running oracle database in docker like this
docker run -d --rm --name oracle_online -e ORACLE_SID=xxx -e ORACLE_PDB=xxx -e ORACLE_PWD=xxx -v /home/data/oracle:/opt/oracle/oradata -it -p 1521:1521 container-registry.oracle.com/database/enterprise:12.2.0.1

When it's started, the ORACLE_SID is unchanged, it's always ORCLCDB:
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCLCDB/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/95c1ad3d33bd/listener/alert/log.xml

...

#ip-address : IP address of the host where the container is running.
#port       : Host Port that is mapped to the port 1521 of the container.
#
# The mapped port can be obtained from running "docker port <container-id>"
# ===========================================================================
Completed:     alter pluggable database ORCLPDB1 open
2020-11-14T05:42:36.136965+00:00
    alter pluggable database all save state
Completed:     alter pluggable database all save state
2020-11-14T05:42:36.426957+00:00
ALTER SYSTEM SET encrypt_new_tablespaces='DDL' SCOPE=BOTH;
2020-11-14T05:42:38.664560+00:00

Does anyone know how to change the SID?


texts: Error updating sql server geography type in trigger

texts:
I have the following trigger in my database:

CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS
    IF UPDATE(Latitude)
        UPDATE pt
        SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)
        FROM dbo.Party AS pt INNER JOIN inserted AS i ON i.PartyId = pt.PartyId
        WHERE (i.Latitude IS NOT NULL AND i.Longitude IS NOT NULL);
        RETURN
    IF UPDATE(Longitude)
        UPDATE pt
        SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)
        FROM dbo.Party AS pt INNER JOIN inserted AS i ON i.PartyId = pt.PartyId
        WHERE (i.Latitude IS NOT NULL AND i.Longitude IS NOT NULL);
        RETURN
GO

But every time I try to update a row in the Party table, I get an error, even if I don't change the Latitude or Longitude fields.

The error is:

  geography::Point' failed because parameter 1 is not allowed to be null.

Any ideas?  I'm pretty new to triggers, so I'm sorry if it's something simple and obvious.


texts: Trying to get this MySQL Trigger working

texts:
create trigger check_td before
for each row begin

declare v_code_ID varchar(25);
declare c_Worker1 cursor for select code_ID from Worker1;
declare c_Worker2 cursor for select code_ID from Worker2;

if inserting or updating Worker1 then
  open c_Worker2;
  fetch c_Worker2 into v_code_ID;

while c_Worker2%found loop
if v_code_ID = :new.code_ID then

  SIGNAL SQLSTATE '-1'
        SET MESSAGE_TEXT = 'Error A.';

end if;
fetch c_Worker2 into v_code_ID;
end loop;

close c_Worker2;
end if;

if inserting or updating Worker2 then
open c_Worker1;
fetch c_Worker1 into v_code_ID;

while c_Worker1%found loop
if v_code_ID = :new.code_ID then

  SIGNAL SQLSTATE '-1'
        SET MESSAGE_TEXT = 'Error B.';

end if;
fetch c_Worker1 into v_code_ID;
end loop;

close c_Worker1;
end if;

end;

I come from Oracle databases and I'm trying to get this MySQL Trigger working. The error says that the problem is in the 4th line (or around) but after searching, I found that this way is the correct way to declare varchar variables in MySQL.

Can somebody give me a hand? It's slightly difficult because some syntax changes a bit.

Thank you very much, any help is appreciated.


texts: Is there any literature on "re-normalisation" of denormalised data?

texts:
I'm involved in a project, the core of which might be generously described as "re-normalisation" of denormalised data. (To be fair, the initial implementation took only the first 90% of the time budget. But, as you might expect, the myriad edge cases are taking the second 90%...)

Being mindful of this site's admonition to ask answerable questions rather than start discussions: is there any literature on this issue? Intuitively, it seems like an idea fraught with danger, and in this instance it's certainly turning out to be a quagmire. But is it, say, provably impossible to get right in some general way?


texts:
I want to define a macro in my SQL script and later based on my conditional query I want to create a new variable name which will be 'X'+macro name.

For example, say I define a macro 'month' which has the current month of the system.

DECLARE @month AS VARCHAR(20);
SET @month = DATENAME(MONTH, GETDATE()), 3)

Upon execution, say it returns 'Nov'.
Now further in the script, I want to execute a query which based on CASE/WHEN will create a new variable which will be called something like 'Product_Nov'. When I use the below mentioned query my output is a new column named 'Product_@month' whereas I wanted 'Product_Nov'.

CASE
WHEN x ='Y' then 1
ELSE 0
END AS Product_@month

Any help/advice/suggestions much appreciated.


texts: Unexpected results from MariaDB IF EXISTS ... THEN

texts:
I am having unexpected results from MariaDB IF EXISTS... in a stored procedure. I created a minimal example to demonstrate the issue:

    PROCEDURE testdb.testSearchResults(IN pParam1 INT, IN pParam2 CHAR(2))
BEGIN

  IF EXISTS(SELECT COUNT(*) FROM test WHERE intval = pParam1 AND text = pParam2) THEN
    SELECT 'Found' AS result;
  ELSE
    SELECT 'Not Found' AS result;
  END IF;

END

No matter what the contents of the file "test" or the parameters I give to the call, this procedure always returns "Found".

Am I doing something wrong?

Thanks.


texts:
I need to create some ACL's to allow TCP connections to some hosts. These hosts can only be accessed by their IP address. To make them easier to keep track of in the future, I'd like to be able to give the ACL some kind of friendly name, but I can't see a way of doing it.

I'm on Oracle 12.2, and from reading the DBMS_NETWORK_ACL_ADMIN documentation it sounds like I'm supposed to use the dbms_network_acl_admin.append_host_ace procedure. I've created the ACL with (details changed to protect the innocent):

BEGIN
    dbms_network_acl_admin.append_host_ace(
        host => '10.10.10.10',
        lower_port => NULL,
        upper_port => NULL,
        ace => xs$ace_type(
            privilege_list => xs$name_list('connect'),
            principal_name => 'USER1',
            principal_type => xs_acl.ptype_db
        )
    );
END;

This appears to have created it OK, but when I look at DBA_NETWORK_ACL_PRIVILEGES, I'm hoping to find some way of making perhaps the ACL field more friendly (I can understand it having an arbitrary ACLID, but it'd be nice to able to one field customisable!)

SQL> SELECT * FROM dba_network_acls WHERE host = '10.10.10.10';

HOST        LOWER_PORT UPPER_PORT ACL                                          ACLID            ACL_OWNER
----------- ---------- ---------- -------------------------------------------- ---------------- ---------
10.10.10.10                       NETWORK_ACL_780DE78AA2A6289CE0531240D80A83F6 0000000080002774 SYS


texts: Cancelling / Stopping ALTER INDEX REORGANIZE

texts:
ALTER INDEX [myIndex] ON [dbo].[myTable] REORGANIZE WITH ( LOB_COMPACTION = ON )

I have the above query running for 16 days (still running), the table is a dummy table used for benchmark tests, it has over 10 Billion rows. (About 1 TB of data including myIndex (non-clustered)).

I know there is progress as when I query for fragmentation, I see the number decrease.

If I cancel / stop the execution of the ALTER INDEX, can I resume it later safely, or will it cause to a rollback?

Will it rollback roll just the last page swapped or the whole operation ?


texts:
Recently my Oracle Installation has been crashed and I reinstalled the Oracle again. I have my old Oracle Data files (.DBF files) which have been recovered from old installation.

Now, I need to restore or reinstate these .DBF files into my new installation, so that I can get all my data and procedures.

Both Old and New Oracle Installation version: Oracle 12c


texts:
I am using merge replication with SQL 2012.

When performing a clean sync using the merge agent I am getting a timeout on this stored procedure,

sp_mssetupbelongs

I am wondering what the best way to diagnose this issue? When I take the SQL that was run, and run it in management studio it runs instantly. I can only assume that part of the required operations were done the first time it was run and timed out.

More specifically it must be a particular group of merge articles that are slowing it down, so how can I isolate this?


texts:
In T-Sql, a user has a single default schema. Is there a concept of schema search path similar to a postgres search_path?

The question behind the question is, if I use schemas as namespaces for objects, will that mean using qualified names in all code?


texts: mysql - how to use INSERT...SELECT syntax for large query

texts:
I'm working on a very big data base and I need to find a trustworthy way to insert rows of a table into another, without crashing or low space errors.

Here are my tables:

tld: 738 rows
============================
| id (int) | tld (varchar) |
============================

sld: 116,000,000 rows
============================
| id (int) | sld (varchar) |
============================

dns: 3,000,000 rows
============================
| id (int) | dns (varchar) |
============================

cache: 300,000,000 rows  --temp data
===============================================================
| sld (varchar) | tld (varchar) | dns (varchar) | date (date) |
===============================================================

history: 128,000,000 rows
===================================================
| sld (int) | tld (int) | dns (int) | date (date) |
===================================================

As you can see there is a cache table for temp data.

I am inserting the cache columns (sld, tld, dns) in three tables (sld, tld, dns)
and get their ids for later use with below sql:

INSERT INTO `sld` (`sld`)
SELECT DISTINCT `sld` FROM `cache`
WHERE `sld` NOT IN (SELECT `sld` FROM `sld`);

After that I want to insert all of cache data into the history table with associated ids.
I ran this query:

INSERT INTO `history` (`sld`, `tld`, `dns`, `date`)
SELECT DISTINCT * FROM (
   SELECT `sld`.`id` AS `sld`, `tld`.`id` AS `tld`, `dns`.`id` AS `dns`, `cache`.`date` AS `date` FROM `cache`, `sld`, `tld`, `dns`
   WHERE `cache`.`tld` = `tld`.`tld` AND `cache`.`sld` = `sld`.`sld` AND `cache`.`dns` = `dns`.`dns`
) AS `t`;

But now I get a low space error.
Then I chunk my select query with repeat syntax:

REPEAT

DROP TABLE IF EXISTS `tmp_cache`;
CREATE TEMPORARY TABLE `tmp_cache`
SELECT `sld`.`id` AS `sld`, `tld`.`id` AS `tld`, `dns`.`id` AS `dns`, `cache`.`date` AS `date` FROM `cache`, `sld`, `tld`, `dns`
WHERE `cache`.`tld` = `tld`.`tld` AND `cache`.`sld` = `sld`.`sld` AND `cache`.`dns` = `dns`.`dns`
LIMIT 1000000;

INSERT INTO `history` (`sld`, `tld`, `dns`, `date`) SELECT DISTINCT * FROM `tmp_cache`
ON DUPLICATE KEY UPDATE `history`.`date` = `history`.`date`;

UNTIL (SELECT count(*) FROM `tmp_cache`) = 0
END REPEAT;

With this method I have some other issues like:
concurrency or changing orders can affect my data...

How can I, without getting low space errors, put the cache table into the history table with associated ids?

Thanks in advance.


texts: global foreign_key_checks = 0 on RBR slave

texts:
Example schema:

CREATE SCHEMA account;
CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `address` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE SCHEMA data;
CREATE TABLE `project` (
    `id` INT(11) NOT NULL,
    `account_user_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_user` (`user_id`),
    CONSTRAINT `fk_user` FOREIGN KEY (`account_user_id`) REFERENCES
`accounts`.`user` (`id`)
) ENGINE=InnoDB;

Problem Background

I have row based replication (RBR) slaves, which should not contain the 'accounts' schema. I use replicate_wild_ignore_table 'accounts'.'%' so that only the 'data' schema exists.

The only problem is, replication breaks when we insert to data.project with: "Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW;" since the accounts.user.id doesn't exist on the slave.

Question:

Setting "set global set foreign_key_checks = 0" permanently on the slaves seems like a good fix in this case. My thinking is:

The key constraints are enforced on the master, where are writes/updates occur; rejected statements do not appear in the binlog / do not replicate to the slave.
Values for each record inserted/updated are explicitly set by the row-based replication. If a FK causes an update, it will be captured in another binlog statement, which will replicate to the slaves.
Therefore, I don't need to worry about having the slave check FK constraints.

Is there a flaw in my thinking? Has anyone tried this? I would appreciate any insights.

Other options I've considered:

set slave-skip-errors = 1452

inserts to data.project won't occur (bad), since the statement that generates the error gets skipped (per MySQL: What happens to the row when skip-errors=1062 is set).

Master & slave - remove foreign key constraint

Not ideal; we want to ensure data integrity on the master.

Slave - remove foreign key constraint

Also not ideal; There are multiple slaves to maintain, and more may be required. There are multiple cross-schema FKs, and likely to add more in the future. I fear it will become unwieldy to manage.


texts: Oracle date not returning the right data

texts:
I don't know whats going on here, but here is my table (budget) with select * from budget :

|budget_id|as_of_date|quarter_1|quarter_2|quarter_3|quarter_4|
|1        |15-MAY-16 |400      |100      |100      |100

So say if I wanted to calculate sum for all quarters on the given date(15th May of 2016) or before I would use this query :

select as_of_dt from budget where as_of_dt <= to_date('2016-05-15', 'yyyy-MM-dd');

This query returns nothing:

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) from budget where as_of_dt <= to_date('2016-05-15', 'yyyy-MM-dd');

However if I add one day to this :

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) from budget where as_of_dt <= to_date('2016-05-16', 'yyyy-MM-dd');

I get the right result 700. I want to get all budget records before or equal to a given date string, why is the equal not working?

This might be a hint but I don't get it :

select to_date(as_of_dt, 'yyyy-MM-dd') from budget;
=> 16-MAY-15

select as_of_dt from budget;
=> 15-MAY-16

Somehow the year gets flipped or whatever is happening here, what do I do to get the right output with both equal and less than a given date ?


texts:
I need to drop a table that is 500 GB in size. It contains historical data that's not needed or used. There are no FK constraints. Any consequences of just dropping the table? I'm also using DB Mirroring. Will there be blocking or some other things that I should be concerned?

Microsoft official statement:

  Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.


texts: Trigger to get next prefixed ID: Is there a better way?

texts:
I have a projects table:

+------------+-----+
| PROJECT_ID | ... |
+------------+-----+
| LC000001   |     |
| LC000002   |     |
| LC000003   |     |
| LC000004   |     |
| LC000005   |     |
+------------+-----+

As you can see, the PROJECT_ID column has a text datatype (NVARCHAR2) and it has a prefix (LC).

I'm guessing that creating an ID with a prefix might have been a been a poor design choice. But, right or wrong, this is how my organization has set up it's business.

I've written a trigger that automatically inserts the next PROJECT_ID when a new row is created in the application's attribute editing environment (more here).

CREATE OR REPLACE TRIGGER "PROJECTS_PROJECT_ID_TRIG" BEFORE INSERT ON PROJECTS
    FOR EACH ROW
      BEGIN
        SELECT 'LC' || LPAD(TO_CHAR(CAST(SUBSTR(MAX(PROJECT_ID),-6) AS NUMBER(15,0)) + 1),6, '0')
        INTO :NEW.PROJECT_ID
        FROM PROJECTS;
      END;

However, this is literally the very first trigger I have ever written. The fact that it deconstructs the textual ID, get's the next number, and then reconstructs the textual ID, seems overly complicated to me. I can't help but think Oracle might have a more sophisticated way of handling this.

Is there a better way?

I have a subsequent, related question here: Can an Oracle IDENTITY column be nullable?


texts: PostgreSQL GiST for compressed entry type

texts:
I am using PostgreSQL 9.5 and I'm trying to understand how to implement a GiST index where I have a representation that is a lossily compressed version of the indexed type. For instance, say I have images stored in BYTEA type, and for the index I store the colour ranges (rmin, rmax, gmin, gmax, bmin, bmax), and I want to compare images based upon colour similarity - e.g. with a === operator that returns true when the colours ranges are exactly the same, allowing me to facilitate queries like:

SELECT COUNT(*)
FROM icons, avatars
WHERE icon.image === avatar.image AND avatar.id = 123;

where icons and avatars are both tables with an image field of type BYTEA.

Having looked through the implementation documentation is looks like this should be possible. Using the above example situation, I think I could do as follows:

the union method would generate the bounding range of all entries
picksplit and penalty would just try to minimise the ranges,
similar to a R-Tree
compress would take the BYTEA data and calculate the colour range
decompress would be an identity function
consistent (for the === operator) would return true if the entry's colour range contained the query range for internal nodes, and only if the ranges exactly matched for leaf nodes.

Is this the right approach? I'm not clear about when the compression steps take place. For instance, consistent is presumably called multiple times on different nodes of the tree. So does this mean that the query will have re-calculate the colour range of the query data every time? And in the index, will the leaf nodes contain a copy of the image data or just its colour range?

Note The example given is just for illustrative purposes. My question is about lossy representations in GiST, not indexing images.


texts: is there Better way to do this kind database schema design, mysql

texts:
P.S. Hopefully I am not posting in the wrong place.  If I am, please let me know where to post.

I am currently using laravel with query builder instead of eloquent for a small self project.
I am using the below as examples.

For example.
I have a document with some fields such as

title,
identifier,
contents

none of those are unique though.
BUT the same document will have the same identifier, this is because the document can be rewritten several times, so the there will be some rows with the same info.  Some of you might ask why not using update instead but due to request update is not an option.

Each document can have lots notes, but all the document with the same identifier will be reading the same notes

for notes, I have field such as

identifier  // this is the identifier from the document
contents  // just contents of the notes

Because the identifer in notes is not unique, I am not able to index it to make querying faster.  Even if I added a FK referencing notes with document_id but after I get the notes  I still have to go through the whole notes to find all with the same identifier so to me, adding FK doesn't seem to help much.

Is there a better way to do this, or I am thinking the logic completely wrong?

Thanks in advance for any advice.


texts: Wrong estimate on a query on partitioned tables

texts:
I wonder why SQL Server makes wrong estimations in such a simple case. There is a scenario.

CREATE PARTITION FUNCTION PF_Test (int) AS RANGE RIGHT
FOR VALUES (20140801, 20140802, 20140803)

CREATE PARTITION SCHEME PS_Test AS PARTITION PF_Test ALL TO ([Primary])

CREATE TABLE A
(
  DateKey int not null,
  Type int not null,
  constraint PK_A primary key (DateKey, Type) on PS_Test(DateKey)
)

INSERT INTO A (DateKey, Type)
SELECT
  DateKey = N1.n  + 20140801,
  Type = N2.n + 1
FROM dbo.Numbers N1
  cross join dbo.Numbers N2
WHERE N1.n BETWEEN 0 AND 2
  and N2.n BETWEEN 0 AND 10000 - 1

UPDATE STATISTICS A (PK_A) WITH FULLSCAN, INCREMENTAL = ON

CREATE TABLE B
(
  DateKey int not null,
  SubType int not null,
  Type int not null,
  constraint PK_B primary key (DateKey, SubType) on PS_Test(DateKey)
)

INSERT INTO B (DateKey, SubType, Type)
SELECT
  DateKey,
  SubType = Type * 10000 + N.n,
  Type
FROM A
  cross join dbo.Numbers N
WHERE N.n BETWEEN 1 AND 10

UPDATE STATISTICS B (PK_B) WITH FULLSCAN, INCREMENTAL = ON

So setup is pretty straightforward, statistics are in place and SQL Server can produce correct estimates when we query one table.

select COUNT(*) from A where DateKey = 20140802
--10000
select COUNT(*) from B where DateKey = 20140802
--100000

But in this simple select estimates are way off, and I see no explanation why.

SELECT a.DateKey, a.Type
FROM A
  JOIN B
    ON b.DateKey = a.DateKey
    AND b.Type = a.Type
WHERE a.DateKey = 20140802

Right after Clustered Index Seek estimation is 57% from actual! Real-world query is even worse, estimate is 2% from actual.

P.S. Numbers table to reproduce setup

DECLARE @UpperBound INT = 1000000;

;WITH cteN(Number) AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
)
SELECT n = [Number] INTO dbo.Numbers
FROM cteN WHERE [Number] <= @UpperBound;

CREATE UNIQUE CLUSTERED INDEX CIX_Number ON dbo.Numbers(n)
WITH
(
  FILLFACTOR = 100,      -- in the event server default has been changed
  DATA_COMPRESSION = ROW -- if Enterprise & table large enough to matter
);

PPS Same scenario but non-partitioned works perfectly.


texts: Getting parsable deadlock XML from SQL Server error log

texts:
I need to be able to grab deadlocks from the SQL Server log file (for various reasons, cannot use Extended events (and that includes the default system health extended event) or WMI)

I have enabled the 1222 trace flag and get the deadlocks in the file.

However, SQL Server is stripping out all of the XML chevrons, which makes parsing the trace flag much more difficult and error-prone!

Does anyone know if it's possible to get SQL Server to log the actual XML into the log file so I can load it into an XML parser?


texts: What would cause the speed difference between servers?

texts:
I have this query on my dev server:

select
    snStudyNumber as ProjectNumber
from CDB.cdb.Quotes q
    inner join CDB.cdb.LineItems l
        on q.PK_quID=l.FK_quID
    inner join CDB.cdb.StudyNumbers s
        on l.FK_snID=s.PK_snId
    left outer join ardb.projects p
        on s.snStudyNumber=p.prProjectNumber
    left outer join b
        on s.snStudyNumber=b.studynumber
where q.quDateWon>''
    and s.snPTWCompletionDate >''
    and p.PK_prID is null
    and l.liCreationDate>'12/31/06'
    and b.studynumber is null
union all

select CAST(ProjectNumber AS int)
from history.ProjectsToProcess a
    left outer join ardb.projects p
        on a.ProjectNumber=p.prProjectNumber
where p.PK_prID is null

order by ProjectNumber desc

This used to work ok on the dev server until yesterday.  Now it's taking about 1 1/2 minutes to run.  However, it works fine on the test server.

Here's the weird part.  If i modified the query on dev to take out one of the left outer joins (either one), it would run fine (but the wrong dataset).  If i added the join back it slowed down again.

Also, the data between the servers are within 90% similarity.  Structure and amount wise.

I also tried rebooting the dev server, but that didn't fix the issue.

So, what could cause this discrepancy?  Where should I be looking?

edit-
ok, so i missed something in the execution plans.  The test server (the one that works) is on the left, the dev server on the right.


texts: MongoDB Fails due to permission error even though its setup

texts:
Attempting to start my MongoDB server using service mongod start fails for the reason: exception in initAndListen: IllegalOperation: Attempted to create a lock file on a read-only directory: /var/lib/mongo/, terminating

I've setup the permissions to the directories it needs using chown mongod:mongod -R /var/lib/mongo/ and also ran chmod 775 -R /var/lib/mongo

Even though I can see that the group and permissions are setup for the directory /var/lib/mongo its still saying the folder is read-only?

Have I missed something in my setup? I'll attach the ls -ld output of my mongo data folder.

drwxrwxr-x. 5 mongod mongod 8192 May 29 15:35 /var/lib/mongo


texts: Unistalled MySQL 5.6 and installed MySQL 5.7 - Restore Database and Tables

texts:
All of the data resides in "C:\ProgramData\MySQL\MySQL Server 5.6\data".  There are 46 folders inside of this folder.  There is one folder for every old database I had.  For example the first three database are "509", "blog" and "bsp".  Inside each of the folders, named after the database, are several files:

auth_history.frm
auth_hisotry.ibd
comments.frm
comments.ibd
db.opt
familymembers.frm
familymembers.MYD
familymemeber.MYI

etc.

Only three tables show in MySQL Workbench, sakila,sys and world.  These may be databases that come along with a complete install.

I'd like to get the old schemas and data back.  How do I do that?  I've tried several things, but reading binary files is not my strongest point.

All help is really appreciated.  Several schemas had hundreds of tables and lots of data.  Nothing is larger than 1/2 Gig.

I'm currently running MySQL 5.7 on a Windows 10 Pro machine.


texts: since COPY command runs inside a single transaction, if its WAL data doesn't fit into wal_buffers? for example if we are bulk importing from CSV file.


texts: Pass (comma separated) -yet single parameter to cursor

texts:
I have to get selection for a given string (with comma separated values) passed to the cursor. There can be multiple values that could be passed via single variable. My code is somewhat like:

CURSOR My_Cursor( vsStr1 )
  IS
  SELECT some_field
    FROM some_table
   WHERE txtfield1 IN ( vsStr1 );    --this field is varchar2 type

vsStr1 varchar2(100) := '01, 25, 80, 100';

How to pass that variable correclty? Any help would be appreciated.


texts: How to add a flexible database role to a database?

texts:
I know there are two types of roles, one predefined (fixed), another user-defined (flexible). Here is my use case: I have a large number of AD Groups, and I am trying to map them up to a role and define permissions on that role. For example, ADGroup1, 2, 3 and 4 only should have access to database 1 tables 1, 2 and 3. To avoid re-defining the permission every single time for every single AD Group, I am trying to assign permissions to a ROLE and define the proper permissions to that single role.

Here is the issue I get:
For Logins, when I get properties, I get this nice "User Mappings" page that lets me define which DBs a user can have access to. But I cannot do that with a role, I cannot pick and choose databases a role can have access to.

What should I use to achieve this? How do you combine logins? (AD groups)?

THank you very much in advance


texts: Oracle Filter Full Transportable Backup for Schema

texts:
I need to use DBMS_DATAPUMP to make a full and transportable backup from my oracle DB for all objects related to a specific schema.
I was looking for something like INCLUDE=SCHEMA:"LIKE 'SCHEMA_1%'" in expdp command Using DBMS_DATAPUMP.METADATA_FILTER(); function.


texts: Create a new table (multiple columns) by selecting random values from an existing table

texts:
I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows):

table1:

id
754
800
330
4
59

My goal is to create another table2 with two columns (source, target), where the values of both columns are a random selection of table1.id values. For example:

table2:

source | target
754    | 59
4      | 4
59     | 330

This is what I've done:

CREATE TABLE table2
(
  id serial NOT NULL,
  source integer,
  target integer,
  distance double precision
);

-- Select 300 table1.id values and insertion into table2.source
INSERT INTO table2(source)
SELECT id FROM table1 ORDER BY RANDOM() LIMIT 300;

-- Select 300 table1.id values and updating table2.target
UPDATE table2 SET target = i.id
FROM (SELECT id FROM table1 ORDER BY RANDOM() LIMIT 300) i;

I got the following result:

source | target
754    | 59
330    | 59
800    | 59

Unfortunately all the table2.target values are all the same. How can I update table2.target with different random values (like in the example) ? Or maybe UPDATE is not the good way for doing this?


texts: Find who and when changed a specific value in the database

texts:
We recent found some record in a table of an oracle database has been updated to another value.and it has been cause very important mistake.

we want to find out who and when has done this?

could someone give some tips?

thanks in advanced.


texts: How to recover deleted/updated record in sql server or mysql?

texts: I want to know that is there any way that I can can recover the deleted or updated record in the sqlserver and mysql as well?


texts:
I have upgraded from SQL Server 2005 to SQL Server 2008 R2 because my database was 4G and needed extra space.
I though SQL Server 2008 R2 has a limit of 10Gigs hence I would have 6 Gigs free, however I only got 1Gig free...

How is this possible?


texts:
I currently have a table which has 2 indices on the same table that are being used to speed up slightly different sets of queries.  They both have 2 index columns and 1 included column and all 3 columns are the same in both.  The table stores sales ranks at particular points in time.  The 3 fields are the ID of the product that the rank applies to (int), the date and time it was checked (smalldatetime) and the rank (int).  In one index I have the rank and checked date/time as indexed cols and the product ID included, in the other I have the product ID and the checked date/time as indexed cols and the rank included.

Am I right in thinking that this isn't a sensible way of having it organised and it would make more sense to have a single index with all 3 columns indexed or does that depend on the way the database is being used?

Create statements are: -

CREATE NONCLUSTERED INDEX [ix_SalesRankCheck_ChannelSKUID_Checked_SalesRank]
ON [dbo].[SalesRankCheck]
(
    [ChannelSKUID] ASC,
    [Checked] ASC
)
INCLUDE ([SalesRank])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [ix_SalesRankCheck_SalesRank_Checked_ChannelSKUID]
ON [dbo].[SalesRankCheck]
(
    [SalesRank] ASC,
    [Checked] ASC
)
INCLUDE ([ChannelSKUID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

UPDATE 2: These are the stats on the indices: -

OBJECT NAME INDEX NAME  USER_SEEKS  USER_SCANS  USER_LOOKUPS    USER_UPDATES
SalesRankCheck  ix_SalesRankCheck_ChannelSKUID_Checked_SalesRank    1140    18  0   4091
SalesRankCheck  ix_SalesRankCheck_SalesRank_Checked_ChannelSKUID    36  0   0   4091


texts: How to let customers use their own reporting tools on a SaaS application?

texts:
We have a SaaS application that generates a lot of operational data that our customers want to report on. We have a built-in business intelligence tool, but some of our customers want to use their existing tools, like Tableau or Cognos.

Some of them have asked us to let them connect to their SQL database directly over a VPN tunnel. That works, but it will be difficult to manage if we have more than a few.

Is there any standard way to let outside reporting tools access data without opening a direct network connection to the database? By "standard" I mean something that we can implement that will be usable by a lot of common BI and reporting tools.


texts:
How to write a trigger that updates a table before startup and shutdown of the database in MySQL 5.7 ?

Any suggestions that could be helpful to us?


texts:
This query runs for 60 seconds:

SELECT mychecklis0, ....
FROM dbo.MyChecklistStatus mychecklis0_
WHERE mychecklis0_.AppFormSubSectionId=@P0 AND mychecklis0_.ProfileId=@P1

There is an index created on profileid and AppFormSubSectionId

My question:

Does the arrangement of columns in the where statement comparing the column id in the sys.index_columns table matter?

Which is better, creating an index for each col, or 1 index for multiple columns?


texts: Self-referencing table vs Mutiple table approach

texts:
I am designing a quiz database and I have to incorporate categories into the questions.  I (think) that I have a hierarchical structure that lends itself to a self-referencing table (I have only just come across this concept in my reading).

There are two top levels:
A-level + GCSE-level
Each of these can contain multiple sub-levels (Electricty, Waves, Forces, etc).
Each of these can then contain even more sublevels (Circuits, Resistance, Voltage, etc).

I have thought of two approaches:

Level table (id, name) containing two entries (a level + gcse)
Subject Table (id, name, level-id (FK)) containing (Electricty, Waves, Forces, etc)
There is a join table Subject_Level (id, subject_id, level_id)
Topic table (id, topicname, subject-id (FK))

Or one self-referencing table
id, name, parent-id

e.g.

1, a-level, null
2, gcse, null
3, electricity, 1
4, electricity, 2
5, circuits, 3
6, circuits, 4
7, current, 3
8, current, 4
etc

Which is the best approach for this particular problem?  What if I wanted to put in a higher parent level later on (chem, phys, etc each of which could be a-level, gcse, etc)?

Thank you in advance for reading (hopefully my question is clear),

Martyn


texts: SQL Server 2017 - Extracting source and target column names within a string containing column mappings

texts:
I have the following format string stored in text (could be any number of columns):

col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;...

I'm trying to come up with an elegant way of extracting and isolating all the xxx_source column names and all the xxx_target column names so I could store them in variables and get the following end result:

@Source_Columns = 'col1_source,col2_source,col3_source'
@Target_Columns = 'col1_target,col2_target,col3_target'

At the end of the day, I'd like to perform SELECTs on my source and target columns to perform data compares.

This is what I've achieved so far, but I find it's just too complex for nothing (with a table valued function):

CREATE FUNCTION [dbo].[UF_miscParseStringToTable]
    (
        @list   nvarchar(MAX)
        , @sep  varchar(8)
    )
RETURNS @ts table
    (
        [ID]        int identity
        , [value]   nvarchar(MAX)
    )
AS
BEGIN

    -- Parameters check
    if ((@sep is null) or (datalength(@sep) < 1)) return

    if ((@list is null) or (@list = '') or (@list = @sep)) return

    -- Add path wildcards directly with sep
    -- ?worth it?
    if (left(@sep, 1) <> '%')   set @sep = '%' + @sep
    if (right(@sep, 1) <> '%')  set @sep = @sep + '%'

    -- First first sep
    declare @i int
    set @i = patindex(@sep, @list)

    -- Acc values
    while (@i > 0) begin
        insert into @ts ([value]) values (rtrim(left(@list, @i - 1)))
        set @list = ltrim(right(RTRIM(@list), len(@list) + 3 - (@i + len(@sep) )))
        set @i = patindex(@sep, @list)
    end

    set @list = rtrim(@list)
    -- Insert last value, if any
    if (@list <> '') insert into @ts (value) values (@list)

    return

END

The function above basically takes my mapping string and converts it to a list of column names in a table (see query logic below):

DECLARE @Delim varchar(1) = '|'
DECLARE @Mapping varchar(max) = 'col1_source|col1_target;col2_source|col2_target;col3_source|col3_target'
DECLARE @String varchar(max) = REPLACE(@Mapping,';', @Delim)
SELECT * FROM dbo.UF_miscParseStringToTable(@String, @Delim)

The above resulting query yields the following table:

 ID| value
 1 | col1_source
 2 | col1_target
 3 | col2_source
 4 | col2_target
 5 | col3_source
 6 | col3_target

I could perhaps do a join on the column indexes but, I'm finding it difficult to isolate my source and target fields so that I could perform data comparisons between them.  In addition, I'd like to avoid performing an extra join to a table if I don't have to.

Here are the results desired (to be able to perform the following):

SELECT col1_source, col2_source, col3_source FROM mytable;

SELECT col1_target, col2_target, col3_target FROM mytable;

Any help or ideas would be great!

Shawn


texts: Usage of foreign key when the referenced table data will not change

texts:
I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely.

For e.g., the user is presented with days of the week and can select any number of days. It is going to be a one to many table UserDayPref(user_id, day). Now I have 3 choices:

Create table Days and make day field in UserDayPref a foreign key to Days table .
Make day field as string and save the name of the day.
Map Name of the day to an integer and save the integer.

I am inclined to implement the 3rd option for this and all other dropdown fields. I am pretty sure data will edited rarely.

Is this the right way to do it? Or going with Foreign Keys is the right way?


texts:
I know the logic behind database snapshot and how it works.
But my problem is how can database snapshot to be used to recover data?
can we take a backup of database snapshot and restore it in a new server?

I am using MS SQL server

Thank you.


texts: module_end extended events duration in microseconds?

texts:
There is no description for the duration event field for the module_end event and I'm struggling to find any additional documentation.

I interpreted this as milliseconds but looking at some of the results this would be incorrect as I have an execution lasting 121 days. Is this duration represented in microseconds?


texts:
Say I have an indexed view that looks like this:

ALTER VIEW dbo.MyIndexedView WITH SCHEMABINDING
AS
    SELECT ord.SomeColumn, COUNT_BIG(*) AS Count
    FROM dbo.Ordered ord
    WHERE ord.CreatedWhen >  CONVERT(DATETIME, '2014-11-01', 121)
    GROUP BY ord.SomeColumn
GO

CREATE UNIQUE CLUSTERED INDEX [CIX_MyIndexedView] ON dbo.MyIndexedView (SomeColumn)
GO

If I change the date time value in this view (say to '2014-11-01') then the clustered index needs to be regenerated.

Is there a way to have the existing view and index stay in effect until the new one is totally built and then have them swap?  Similar to Online=On


texts: Save different account types in a database

texts:
I have the following issue. In one application (imagine a messaging/e-mail app on your smartphone), the user enters its accounts and gets information from the server displayed. I need to store the accounts and there are different types of accounts. Something like this:

All account types: name of the account, account type, ...
Additional fields for account type A: user, password, URL
Additional fields for account type B: user, password, server specific info

I expect to have 1-5 accounts per user, so really small table(s). What I need to consider is that account types may be added/removed in the future, so I want to avoid referencing the account types or their specific fields in the main DB handler.

The best idea that I have right now is to store everything in the same table. This will contain the fields that are common to all account types and an additional text field with the remaining information (e.g. in JSON format).

What is the general/best way for solving the issue? Is my approach the correct one?


texts: “SQL command” vs “Table or view” when importing entire tables

texts:
When using an OLEDB source editor, is there a performance benefit or difference in using "Sql Command" over "Table or View" when you want the entire table?

This is a variation on this question, in which the asker is talking about scenarios where they only want one or some columns in a table - in which case there is a benefit to using "Sql Command", as a select * from is still executed in the background when using "Table or View" even when columns are unticked.

I've also found this entry on the MSDN blog which states it is more efficient when looking at views due to the use of OpenRowset and a SET ROWCOUNT 1 which results in an inefficient query plan being cached and reused for the actual execution.

But if you're loading in a table and want every column, is there still an advantage to using the "Sql Command" option over "Table or View"?


texts:
I want to make a database that stores countries, their respective states, and cities.  I am confused how to normalize these.  Can anyone help?

My ideas:

Table 1  Countries (id pk, name , code)
Table 2  State (id pk, name, country_id fk, code)
table 3  Cities (id pk, name, state_id fk, pin_code)

I want to store hotels along with their address(es):

table 4 address (id pk, street_address, city_id fk, state_id fk, country_id fk)
table 4 Hotel (id pk, name, address_id)

Are there any changes for future proofing I should make, or any other mistakes I've made?


texts: Mysql query grouping by two columns for max date takes too long

texts:
I have table that looks like this:

l_p  l_a     l_timestamp          l_n   l_act   l_name
123  321     2011-1-1T01:00:00Z   B     q       das
21   23      2012-1-1T01:00:00Z   C     q       sd
123  23      2013-1-1T01:00:00Z   D     q       sad
21   321     2008-1-1T01:00:00Z   D     q       sad
21   23      2007-1-1T01:00:00Z   E     q       sad

The timestamp field is a binary. All other fields are varbinary. I am not interested in the l_name field but it is relevant for the indexes.

What I want to get back is for each unique combination of l_p and l_a the row with the max timestamp. I also have some additional conditions: the timestamp should be smaller than 2013-1-1 and the l_act field should be q. The table I want to get would look something like this:

l_p  l_a   l_timestamp          l_n   l_act
123  321   2011-1-1T01:00:00Z   B     q
21   23    2012-1-1T01:00:00Z   C     q
21   321   2008-1-1T01:00:00Z   D     q

The table contains about 50million records. My current query is extremely slow. It takes hours and still I haven't gotten a result yet. This is the query:

SELECT a.l_p,a.l_a,a.l_timestamp,
case a.l_new
when 'B' then 2
when 'C' then 3
...
end as GN
FROM tmp a
INNER JOIN
(SELECT l_a,l_p,MAX(l_timestamp) as max_date
FROM tmp
WHERE l_act = 'q' AND (l_n != 'I' and l_n != 'Z')
AND l_timestamp < cast('2013-01-01T00:00:00Z' as binary)
GROUP BY l_p,l_a) b
on (a.l_p = b.l_p AND a.l_a = b.l_a AND
a.l_timestamp = b.max_date AND
a.l_act = 'q' AND  (l_n != 'I' and l_n != 'Z')

I've also tried this variation with the same results:

SELECT a.l_p,a.l_a,a.l_timestamp,
case a.l_new
when 'B' then 2
when 'C' then 3
...
end as GN
FROM tmp a
(SELECT l_a,l_p,MAX(l_timestamp) as max_date
FROM tmp
WHERE l_act = 'q' AND (l_n != 'I' and l_n != 'Z')
AND l_timestamp < cast('2013-01-01T00:00:00Z' as binary)
GROUP BY l_p,l_a) b
WHERE (a.l_p = b.l_p AND a.l_a = b.l_a AND
a.l_timestamp = b.max_date AND
a.l_act = 'q' AND  (l_n != 'I' and l_n != 'Z'))

The indexes on the table are:

Keyname     Type         Unique    Packed
PRIMARY     BTREE        Yes       No
Field       Cardinality            Collation
l_project   18           A
l_name      18           A
l_a         24799255     A
l_act       49598510     A
l_timestamp 49598510     A

AND
Keyname     Type          Unique Packed
l_a         BTREE         No     No
Field       Cardinality          Collation
l_a         16532836      A
l_name      16532836      A

Any ideas on why it takes so long? The table is innodb.


texts: unable to bring database online from recovery pending

texts:
I am trying to bring a SQL Server database online and accessible  that is in Recovery Pending state.
The SQL Server service uses an Active directory account that is a domain administrator and a member of sysadmin on sql server. The SQL Server account should have rights in the SQL file folders as it is a domain admin. It is however not a local administrator in the SQL Server.

The error messages are:

  Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file
  "F:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBName.ndf". Operating system
  error 5: "5(Access is denied.)". Msg 5120, Level 16, State 9, Line 1
  Unable to open the physical file "F:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBName.ndf". Operating system
  error 5: "5(Access is denied.)".

While

alter database [DBName] set online;

results in the error below:

  Msg 945, Level 14, State 2, Line 1 Database 'databs' cannot be opened
  due to inaccessible files or insufficient memory or disk space.  See
  the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line
  1 ALTER DATABASE statement failed.

Can you think of a solution?


texts: Mongo Shard Server Doubling as Another Shard's Replica Server

texts:
I'm going to be setting up a sharded + replicated mongo cluster, with application software servers co-located with mongod instances-- so that odds are a request is being processed on the same hardware server where the necessary data lives.

But it's a bit of a drag how mongo secondary replica servers are almost dead weight compute-resources, since they can only handle reads... and must forward all writes to their primary. Therefore, there's almost no point to co-locate application servers with secondary replicas... only primaries.

Therefore I've considered a setup where each server is a shard, and also a replica for a neighboring shard. That way, no server is dead weight.

Are there any concerns with this architecture, from anyone who has tried it?


texts: Filtering data from a mysql table using a date range where date format is VACHAR ('AUG-18')

texts:
Sample Data

TRAINING_DATE    GENDER
AUG-18             M
AUG-18             F
SEPT-18            F
OCT-18             M
OCT-18             M
NOV-18             M
NOV-18             F

Expected Output.
Number of people trained between SEPT-18 and NOV-18

   F      M
   2      3


texts: Unable to begin a Distributed transaction

texts:
Can someone help me to troubleshoot this error?

  Error:OLE DB provider "SQLNCLI11" for linked server "TESTSERV"
  returned message "No transaction is active.". Msg 7391, Level 16,
  State 2, Line 7 The operation could not be performed because OLE DB
  provider "SQLNCLI11" for linked server "TESTSERV" was unable to begin
  a distributed transaction.

I am getting the error executing the code below.

CREATE TABLE #Drive
(
    drive char(1) Primary Key,
    freespace int null
)

Insert into #Drive
EXEC TESTSERV.MASTER.xp_fixedDrives

I've already done the following set up:

Allow Remote Clients
Allow Outbound

On Security tab of Local DTC Properties in Component Services:

Go To Run, type comexp.msc.
Double click "Console Root".
Double click "Component Services.
Double click "Computers".
Double click "My Computer".
Double click "Distributed Transaction Coordinator".
Right click "Local DTC" under "Distributed Transaction Coordinator", and click properties.
Click the "Security" tab.
Put tick marks on the checkboxes "Allow Remote Clients" and "Allow Outbound".

Still not fixed.
Different clone is used here for remote machine.


texts:
I've built a website using node js and it has two parts. one for users and one for website administrators for editing content on website that is locked by a login page. The user side, loads data from local json file and administrators side, loads data from mysql database.
I want to know is it better to use mysql on both sides for increased performance?
I am new in programming and I'm a bit confused between mysql Connections and Pools. Which one is better for performance if I have a lot of users receiving data?
this is the function for receiving data:
function getAllProductsLocal() {
    try {

        const products_json = JSON.parse(fsExtra.readFileSync("./public/assets/JSON/products.json"));
        return products_json;

    }catch(e) {
        throw e;
    }
}

and this is an example of the data I'm storing in JSON file:
{
    "ID": 19,
    "Cost": 212,
    "Sizes": "[4,7]",
    "Category": "man",
    "Color": "blue"
}

there will be a hundred of them


texts:
I have a table in MySQL.

The table structure is as follows.

id   |  emp_id  |  call_start  |  call_end  |  call_type
--------------------------------------------------------
1    | 20043    | 20:20:10     | 20:21:19   | SALES
2    | 20043    | 20:25:34     | 20:27:12   | NON-SALES
3    | 20044    | 21:25:34     | 21:27:12   | SALES
4    | 20044    | 22:25:34     | 22:27:12   | SALES
5    | 20044    | 22:45:34     | 22:47:12   | NON-SALES
6    | 20043    | 21:25:34     | 21:27:12   | NON-SALES

I want to select data as total number of calls by an employee, total sales call by an employee.

What will be the appropriate query?

I have written something but result is not correct :

SELECT
  emp_id,
  count(*) total,
  (SELECT COUNT(*) AS TOTAL_CALL FROM Dash_Agent_Disp) total_calls,call_type
FROM
  `Dash_Agent_Disp`
where
  call_type like 'SALES'
GROUP BY
  employee_id

The out put of the query is :

emp_id  |  total  | total_calls  |  call_type
----------------------------------------------
20043   |   1     |   6          |  SALES
20044   |   2     |   6          |  SALES

My Expectation is to get :

emp_id  |  total  | total_calls  |  call_type
----------------------------------------------
20043   |   1     |   3          |  SALES
20044   |   2     |   3          |  SALES


texts:
some time ago I installed SQL server 2016 developer edition on a windows 10 home edition environment (a laptop to be precise) and everything was fine.

then someone - an administrator on the box - decided without telling me, to rename said box.

after that when attempting connection to SQL server we have been experiencing the following error messages:

this is a very similar question to this one:

“The certificate chain was issued by an authority that is not trusted” when connecting DB in VM Role from Azure website

I have been also here:

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

and I have been using the Kerberos Configuration Manager, which gives me a different error message

I should say that I Am able to connect to this instance of SQL server by SQL server authentication but I want to connect using windows authentication.

just to be clear - this is all a local machine, that does not belong to any domain.

I am not quite sure as how to proceed from here, what I want is to use windows authentication.

The error message related to certificates makes me think about re-issuing certificates here.
I am not sure as for how to get this done, or if this is a valid way to go about this situation.

I will not be around this machine for the day, but I will catch up as soon as possible. I will carry on with my research when time permits.


texts: Massive MSreplcommands table, 2 billion rows, cleanup job not deleting commands

texts:
I have a runaway distribution database that is 640gb in size. I have narrowed it down to the msreplcommands table which is holding 2 billion rows.

I have checked all publications and found 2 of them having immediate sync set to 1. I changed this to 0. Reran the cleanup job and it deleted about 240K rows only. Ran this:

SELECT *  from MSrepl_transactions (nolock) where entry_time < getdate() -5;

Found entries as old as 30 days.

The problem is I am running out of space very fast and all delete attempts lock the msreplcommands table and thus lock replication. We are a 24/7 shop and I have no server downtime possible. Is there another way to delete this amount of data without locking replication?

Hi T.H, Thanks for the suggestion. I found that link earlier and decided to try it. I've done some investigating and found very old entries in the MSrepl_transactions table going back 32 days using the following query:

SELECT distinct cast(entry_time as date) as entry_time, publisher_database_id, xact_seqno, count(publisher_database_id) FROM [distribution].[dbo].MSrepl_transactions group by entry_time, publisher_database_id, xact_seqno order by entry_time


texts: Behavior of STUFF function for empty strings

texts:
When working on answering another question (on SO), I came across some unexpected (to me) behavior with the STUFF function.
SELECT STUFF
(
    'a', -- character expression
    1,  -- start
    1,  -- length
    'z' -- replaceWith_expression
)

returns NULL, while I would expect it to return 'z'.

Stuff
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
Syntax
STUFF ( character_expression , start , length , replaceWith_expression )
Remarks
If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.
An error is raised if the resulting value is larger than the maximum supported by the return type.

Per the documentation, there are a number of reasons why a NULL string would be returned:

If start is longer than the first character_expression
If start is negative or 0
If length is negative


texts: Performance comparison between using Join and Window function to get lead and lag values

texts:
I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the lead and lag values of a certain time for a specific id.

I have used two methods to achieve this. One method is using join and another method is using the window functions lead/lag with clustered index on time and id.

I compared the performance of these two methods by execution time. The join method takes 16.3 seconds and the window function method takes 20 seconds, not including the time to create the index. This surprised me because the window function seems to be advanced while the join methods is brute force.

Here is the code for the two methods:

Create Index

create clustered index id_time
 on tab1 (id,time)

Join method

select a1.id,a1.time
   a1.value as value,
   b1.value as value_lag,
   c1.value as value_lead
into tab2
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.time-1= b1.time
left join tab1 c1
on a1.id = c1.id
and a1.time+1 = c1.time

IO statistics generated using SET STATISTICS TIME, IO ON:

Here is the execution plan for the join method

Window Function method

select id, time, value,
   lag(value,1) over(partition by id order by id,time) as value_lag,
   lead(value,1) over(partition by id order by id,time) as value_lead
into tab2
from tab1

(Ordering only by time saves 0.5 seconds.)

Here is the execution plan for Window function method

IO statistics

[

I checked the data in sample_orig_month_1999 and it seems that the raw data is well ordered by id and time. Is this the reason of performance difference?

It seems that the join method has more logical reads than the window function method, while the execution time for the former is actually less. Is it because the former has a better parallelism?

I like the window function method because of the concise code, is there any way to speed it up for this specific problem?

I'm using SQL Server 2016 on Windows 10 64 bit.


texts:
I have a MySQL table with this structure:
Gene  |  Impact  |  Frequency
---------------------------
SCN1A | LOW      |  0.8
---------------------------
CFTR  | MODERATE |  0.1
---------------------------
ADA   | HIGH     |  0.2
---------------------------
ACADM | LOW      |  0.1
---------------------------
SCN1A | HIGH     |  0
---------------------------
CFTR  | LOW      |  0.5
---------------------------

I need to order the table in a SELECT query according values in 'Impact' column (HIGH > MODERATE > LOW) and Frequency descending values when 'Impact' values are the same, like this:
Gene  | Impact   |  Frequency
---------------------------
SCN1A | HIGH     | 0
---------------------------
ADA   | HIGH     | 0.2
---------------------------
CFTR  | MODERATE | 0.1
---------------------------
ACADM | LOW      | 0.1
---------------------------
CFTR  | LOW      | 0.5
---------------------------
SCN1A | LOW      | 0.8
---------------------------

Up to this point the query must contain the next statement:
ORDER BY FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency

The problem is that, after ordering, I need to go up those rows that have same value in first column  in order to have all rows with same Gene name together, like this:
Gene  | Impact   |  Frequency
---------------------------
SCN1A | HIGH     |  0
---------------------------
SCN1A | LOW      | 0.8
---------------------------
ADA   | HIGH     | 0.2
---------------------------
CFTR  | MODERATE | 0.1
---------------------------
CFTR  | LOW      | 0.5
---------------------------
ACADM | LOW      | 0.1
---------------------------

Does anyone can help me with the query?

Imagine that ADA has Frequency=0. Should it be returned before the SCN1A group? If not, then additionally imagine that SCN1A with Impact=HIGH has Frequency=0.1`.

The order of the gene does not matter when both have the same Impact and Frequency value, although the ideal would be to order them alphabetically, so ADA would be first in your example. If SCN1A has Impact HIGH and Frequency 0.1 should be in third position as found in the example.


texts: AG of 2 replicas on 2 nodes in WSFC : is it possible to add 3rd replica to AG and NOT add 3rd machine WSFC?

texts:
Please help to to research the possibility of below:

We have availability group that consists of two replicas:

replica 1: SQL Server 2017 on Windows Server 2016
replica 2: SQL Server 2017 on Windows Server 2016

Both machines are part of one Windows Server Failover Cluster

Is it possible to add a 3rd replica (SQL Server 2017 on Windows Server 2016) to existing availability group, without adding a 3rd machine to existing WSFC ?


texts: MySQL : Extract SQL statements from binlog

texts:
I am trying the following command to extract SQL statements from binlog for using it in another database (Amazon RDS)

mysqlbinlog /var/lib/mysql/binlog.000027 --start-position=54375264 --base64-output=NEVER > /home/ec2-user/output2.sql

But I got the following error:

ERROR: --base64-output=never specified, but binlog contains a Table_map event which must be printed in base64.

I read in the documentation that mysqlbinlog exits with an error if a row event is found that must be displayed using BINLOG.
Is there any work around to extract SQL statements from binlog?

Update 1 :
I run the command again with option --verbose but I did not get any return in the command line

[root@server ec2-user]# mysqlbinlog /var/lib/mysql/binlog.000027 --start-position=54375264 --base64-output=DECODE-ROWS --verbose  > /home/ec2-user/output4.sql
[root@server ec2-user]#
[root@server ec2-user]#

When I browse output4.sql, I can see some valide SQL + some commented SQL like this:

#130906 14:54:55 server id 1  end_log_pos 269447893     Query   thread_id=1221718733    exec_time=0 error_code=0
SET TIMESTAMP=1378479295/*!*/;
BEGIN
/*!*/;
# at 269447893
# at 269447950
#130906 14:54:55 server id 1  end_log_pos 269447950     Table_map: `dbname`.`tablename` mapped to number 102
#130906 14:54:55 server id 1  end_log_pos 269448102     Update_rows: table id 102 flags: STMT_END_F
### UPDATE `dbname`.`tablename`
### WHERE
###   @1='edited by me'
###   @2=0
###   @3=1373178370
### SET
###   @1='edited by me'
###   @2=0
###   @3=1378479295
# at 269448102
#130906 14:54:55 server id 1  end_log_pos 269448162     Query   thread_id=1221718733    exec_time=0 error_code=0

Update 2 :
I am following the tutorial here https://engineering.gosquared.com/migrating-mysql-to-amazon-rds

I want to update Amazon RDS from the output of binlog. Currently  Amazon RDS do not allow writing from binlog. I am trying to extract the SQL statments from binlog so I can update Amazon RDS. Is there any way to update Amazon RDS from binlog?


texts: What ports should I leave open for MySQL - Ubuntu server

texts: I'm building a database server that I want to run on a VPS. I'm planning on using IPTables to block unnecessarily opened ports. What are the ports I should be blocking if I only want to access to my server via ssh and mysql port 3306? I want to make sure I'm not blocking some important service.


texts: how can I count by week, month, and YTD with postgres?

texts:
under Postgres 9.3 I am trying to run a query to summarize some data from table a:

| Entry date-Date | Customer ID-charvar | received-Boolean | Processed-Boolean | finalized-Boolean | billed-Date |

I would like use count(case when) to count all the records by the current week, month, then all of time into table b:

|report date | Total Received Weekly | Received Monthly | Received all of time | so on...

I am having a difficult time wrapping my head around how to write that query.  I have thought about extracting the week day and month number to determine my records to sum for each length of time but then how would I make it repeatable without having to change the dates so I could automate it to run weekly through either PgAgent or CRON?


texts: how to automate DB restore to another server?

texts:
The plan is to have 1 backup set that will contain full and transactional log backup. (Server1)

There is already a scheduled job that will append any transactional log to the file after 30 minutes. (Server1)

There is a shared network drive between Server1 and Server2. But the problem is automating it via T-SQL. Here is the script:

RESTORE LOG [db_test_rep] FROM  DISK = N'Z:\db_rep.bak' WITH  FILE = 19,  NORECOVERY,  NOUNLOAD,  STATS = 5

The issue with this is that everytime the scheduled job executes, it will update the backup file numbering + 1. So in this case on the next transaction log backup, when I script into action the restoration using SSMS, it will generate FILE = 20

Can you guys give me a simple workaround for this?


texts:
Secondary node of mongodb cluster has entered in Recovering state and it's not coming out of it. Below is what I see in log. I know one way to fix this issue is to reinitialize secondary node by deleting data directory and restarting secondary. But I don't want to try that option as I have 2 tb of data and primary is getting write continuously.

  2017-06-13T12:02:14.946+0000 I REPL [replication-12569] We are too stale to use mongodb.prod.mcse-reporting-olap.services.dal1.prod.walmart.com:27017 as a sync source. Blacklisting this sync source because our last fetched timestamp: 59351d47:3357 is before their earliest timestamp: 593f8b97:5b11 for 1min until: 2017-06-13T12:03:14.946+0000 2017-06-13T12:02:14.946+0000 I REPL [replication-12569] could not find member to sync from 2017-06-13T12:02:14.948+0000 E REPL [rsBackgroundSync] too stale to catch up -- entering maintenance mode 2017-06-13T12:02:14.948+0000 I REPL [rsBackgroundSync] Our newest OpTime : { ts: Timestamp 1496653127000|13143, t: 499 } 2017-06-13T12:02:14.948+0000 I REPL [rsBackgroundSync] Earliest OpTime available is { ts: Timestamp 1497336727000|23313, t: 502 } 2017-06-13T12:02:14.948+0000 I REPL [rsBackgroundSync] See http://dochub.mongodb.org/core/resyncingaverystalereplicasetmember 2017-06-13T12:02:14.948+0000 I REPL [rsBackgroundSync] going into maintenance mode with 11386 other maintenance mode tasks in progress


texts:
What is/are the limitation/s to move from SAN storage to SMB 3.0 on windows Server 2012 - SQL server 2012 enterprise production environment?

I want to know the benefit of using SAN over SMB 3.0 as well ?


texts:
I need some help understanding the how archived WAL files are used and when I can clean them up.

Currently we have a master database replicating to a slave, but every so often our master database server runs out of disk space because the files archived using archive_command build up and we have to manually remove them.  I feel like we're missing something if we have to manually remove old archived WAL files because there are too many that the file system runs out of disk space...

Having said that, I'm not sure I understand the replication process enough to start deleting files and be confident that I'm not screwing anything up.  Does our slave use the archived files created by archive_command or does it rely only on the files in $PG_DATA/pg_xlog directory to maintain a consistent state with the master?

If that's the case, and we're doing a pg_dump of our master every six hours, could I update our backup script to also clear out all archive files that exist at the start of the pg_dump (once the pg_dump is complete of course)? from the docs:

  Note: pg_dump and pg_dumpall do not produce file-system-level backups
  and cannot be used as part of a continuous-archiving solution. Such
  dumps are logical and do not contain enough information to be used by
  WAL replay.

I just want to make sure that however we clean up the archived files, we still have what we need for replication to continue as well as be able to perform a PITR in since the last base backup.

Our archive command is cp %p /var/lib/postgresql/9.2/archive/%f and here is our recovery.conf file from the slave:

standby_mode = 'on'
primary_conninfo = 'host=<ip-address> port=<port> user=rep password=<password> sslmode=require'
trigger_file = '/tmp/postgresql.trigger.5432'


texts: How do I fix Replication event checksum verification failed while reading from network. Error_code: 1743

texts:
Our server had disk space problems. As a result, replication's IO Threads fails to start.

Here are the errors in mysql error log:

  [Note] Slave I/O thread: connected to master
  'user@host:3307',replication started in log 'mysql-bin.000030' at
  position 196820914

  [ERROR] Slave I/O: Replication event checksum verification failed
  while reading from network. Error_code: 1743

  [ERROR] Slave I/O: Relay log write failure: could not queue event from
  master, Error_code: 1595

How do I fix the problem?


texts: Postgres Row Level Security policy optimizes poorly compared to inline version

texts:
I have a query which looks like this:

  SELECT post.id, post.author_id, post.published_at, post.content
    FROM post
   WHERE post.group_id = 1
ORDER BY post.published_at DESC, post.id
   LIMIT 5;

This query has an index on (group_id, published_at DESC, id) which gives it this query plan when no Row Level Security (RLS) policies are used.

 Limit  (cost=0.14..1.12 rows=5 width=143)
   ->  Index Scan using post_published_at on post  (cost=0.14..15.86 rows=80 width=143)
         Index Cond: (group_id = 1)

Then I add this policy:

CREATE POLICY select_member_of ON post FOR SELECT USING
  (EXISTS (SELECT 1
             FROM group_member
            WHERE group_member.account_id = current_setting('current_account_id', false)::INT AND
                  group_member.group_id = post.group_id));

There is a compound primary key on group_member.account_id and group_member.group_id on the group_member table.

I expect Postgres to plan this query as an index-only scan of group_member since both group_member.account_id and group_member.group_id will be set to constant values. group_member.group_id should be constant because of the WHERE post.group_id = 1 condition in the SELECT query above.

Indeed it looks like this is happening when I inline my RLS policy into the query like this:

  SELECT id, author_id, published_at, content
    FROM post
   WHERE group_id = 1 AND
         (EXISTS (SELECT 1
                    FROM group_member
                   WHERE group_member.account_id = current_setting('current_account_id', false)::INT AND
                         group_member.group_id = post.group_id))
ORDER BY published_at DESC, id
   LIMIT 5;

I get the query plan:

 Limit  (cost=0.30..1.85 rows=5 width=143)
   ->  Nested Loop Semi Join  (cost=0.30..25.04 rows=80 width=143)
         ->  Index Scan using post_published_at on post  (cost=0.14..15.86 rows=80 width=147)
               Index Cond: (group_id = 1)
         ->  Materialize  (cost=0.16..8.19 rows=1 width=4)
               ->  Index Only Scan using group_member_pkey on group_member  (cost=0.16..8.18 rows=1 width=4)
                     Index Cond: ((account_id = (current_setting('current_account_id'::text, false))::integer) AND (group_id = 1))

Which is what I was looking for. However, when I run my query with the real RLS policy the query plan becomes:

 Limit  (cost=23.08..23.10 rows=5 width=143)
   ->  Sort  (cost=23.08..23.28 rows=80 width=143)
         Sort Key: post.published_at DESC, post.id
         ->  Subquery Scan on post  (cost=8.92..21.75 rows=80 width=143)
               ->  Nested Loop Semi Join  (cost=8.92..20.95 rows=80 width=147)
                     ->  Bitmap Heap Scan on post post_1  (cost=8.76..11.76 rows=80 width=147)
                           Recheck Cond: (group_id = 1)
                           ->  Bitmap Index Scan on post_published_at  (cost=0.00..8.74 rows=80 width=0)
                                 Index Cond: (group_id = 1)
                     ->  Materialize  (cost=0.16..8.20 rows=1 width=4)
                           ->  Subquery Scan on group_member  (cost=0.16..8.19 rows=1 width=4)
                                 ->  Index Only Scan using group_member_pkey on group_member group_member_1  (cost=0.16..8.18 rows=1 width=8)
                                       Index Cond: ((account_id = (current_setting('current_account_id'::text, false))::integer) AND (group_id = 1))

Which is significantly worse.

Is this the expected behavior? Is there any way to get the same query plan for the version where I inlined my RLS policy?


texts: In PostgreSQL URL I can't use a password containing special characters

texts:
I have a postgresql database in Azure

The passwd string has special characters like these:

)mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU

psql --dbname=postgresql://db_user@mydemoserver:)mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU@mydemoserver.postgres.database.azure.com:5432/mydb

when executing the psql command, it shows me in following error:

psql: invalid percent-encoded token: "iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P"

I already tried putting the passwd string in quotation marks but it does not work

What other options do I have?
I can not change the passwd

I'm interested in using the psql client, because I have to do very large backups


texts: Why does this 120 GB insert grow the log by over 780 GB?

texts:
A table [tbl_old] has about 3 billion rows and about 120 GB for disk space, and then created a [tbl_new] with the same schema (actually, just dumped the script of [tbl_old], then rename the table name to [tbl_new], then create it).

But when running this SQL script:

insert [tbl_new]
   select * from [tbl_old]

After the transaction log file used up about 780 GB and hung.

My questions are:

Is it reasonable for just such inserting script for about 120 GB, more than 780 GB transaction log is not enough?
What is in the log file?

Background:

the DB is in simple recovery mode, and no index on these [tbl_old] and [tbl_new]
SQL Server 2008 R2 Enterprise on Window Server 2008 64bit.
And Intel Xeon CPU E5645 @2.4GHz (2 processors), 24 CPUs, 64 GB memory


texts: SQL encrypted connection home experiment

texts:
I have a home SQLserver 2012 that I managed to enable "force encryption" on, using a self-signed certificate and configuration manager. Now I find two things I can't explain:

Once "Force Encryption" is enabled, I expect other computers on my home network to NOT be able to connect unless the certificate has been transferred and loaded on the client machine. That worked for one client computer, but a second client computer seems to be able to connect WITHOUT the certificate being imported (using mmc, certlm.msc or certmgr.msc).

When I "clear" the certificate in Configuration Manager, I can still use encrypted connections. It's only when I set "force encryption" to No that connections are NOT encrypted. But then, when I set it to Yes and restart the SQLserver (without certificate selected in Protocol Properties), I can again use encrypted connections, according to: SELECT DISTINCT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID

Question 1 is: why can I use encrypted connections although the certificate has been Cleared in Protocol Properties?
Question 2 is: why can the second client computer connect to the SQLserver although Force Encryption is enabled, and, more importantly, what is preventing any other computer to connect, why use encryption at all?
SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (Intel X86).  NO Active Directory, NOT explicitly configured in any DNS, just my ISP router.


texts: How can I find out what stats objects were used during a SQL Server 2014 query?

texts:
I’ve read here that trace flags 3604, 9292 and 9204 enable debugging output that shows what statistics objects were used by the cardinality estimator during query compilation in SQL Server 2012.

They don’t work in SQL Server 2014, is there a new flag for this?


texts: postgreSQL update set only where cast is possible and ignore error?

texts:
I have 2 columns in a PostgreSQL table.  The mac_address_temp column is for migration from character type to MAC-address type:

mac_address      | macaddr               |
mac_address_temp | character varying(17) |

I want to migrate data from mac_address_temp to mac_address, but there is some data that can't be cast as macaddr type.

mac_address | mac_address_temp
-------------+------------------
            | AAB5:4f27:e299
            | AAB54f27e299

UPDATE mactable SET mac_address = CAST(mac_address_temp as macaddr);
ERROR:  invalid input syntax for type macaddr: "AAB5:4f27:e299"

Is there a way to ignore where CAST doesn't work and still update rest of the table?


texts: Index Maintenance with Ola starting from day x (7 or 9) till the end of month

texts:
I want to run the index maintenance from Ola daily, but in my company the firsts business days every month I can't, cause heavy application usage during the night.

So I want to run Ola's script only from the 7 till the end of the month.

How can I to this?

In my script I handle it with a simple if:

if
datepart(dd,getdate()) < 7 return
else begin

But how can I do this with Ola's script?

Best regards
Guenter


texts: Can I physically split a SQL Server database?

texts:
I seem to remember some time ago reading an article saying I could split a SQL Server database into more than one file, allow me to elaborate.

I have a database that has a dozen or so tables that reflect statistics for the last 'n' years. The tables are called History2007, History2008 etc and the current year's table is just called History. All the previous years tables contain static data (won't be changed) and I would like to move these files to another file that is linked to the main database. As the old data tables contain foreign keys to Articles, Clients and Suppliers etc I can't just copy these tables to a new database.

The whole idea is to reduce the size of the database and the backups as I can make one backup of the 'old' data and that will not change until next year.

Any thoughts? Ideas? Guidance?

Thanks,
Martin.


texts: How can I find port running Oracle Enterprise Manager

texts:
I need to connect to the Oracle Enterprise manager of a remote machine. But I dont know the port on which OEM is running on the remote machine. I have access to the machine from terminal though.

Is there a command to see the port number OEM is running on. The remote machine is CentOS and I need to open the OEM on my local browser (I am running windows 10)

I would really appreciate all the help. I am new to Oracle.


texts: Sum date between two date and group by month

texts:
I have a table as below:
Name  Start Date  End Date
Joe   20/04/2021  20/05/2021
John  01/05/2021  28/05/2021

I am using a SQL table-valued function to return a table that has 2 columns: Month and Count total Date of this month.
Example:

Joe: 10 days in Apr, 20 days in May
John: 28 days in May

Finally I will return a new table

Month
Count

4
10

5
48

I tried to use datediff and datepart to group by month, but don't know how to sum after group. Is there any way to do this?
Besides, I want to add filter from date and to date.


texts: Ubuntu, postgresql, show whole running sql command

texts:
System

Linux vm16745 3.13.0-77-generic #121-Ubuntu SMP Wed Jan 20 10:50:42 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
psql (PostgreSQL) 9.3.24

Issue

I don't know if it is right section/forum, but I will try. Is possible to show whole sql command in ps? Now, when I try

ps aux | grep postgres

it shows me only begin of command.

postgres 20211  0.0  0.9 2281708 74304 ?       Rs   09:12   0:00 postgres: shop shop_prod ::1(55623) SELECT

Thanks.


texts:
I want to change the owner of all objects in a schema. To do this I use the following function:

CREATE OR REPLACE FUNCTION chown(in_schema VARCHAR, new_owner VARCHAR)
RETURNS void AS
$$
DECLARE
  object_types VARCHAR[];
  object_classes VARCHAR[];
  object_type record;

  r record;
BEGIN
  object_types = '{type,table,sequence,index,table,view}';
  object_classes = '{c,t,S,i,r,v}';

  FOR object_type IN
      SELECT unnest(object_types) type_name,
                unnest(object_classes) code
  loop
    FOR r IN
      EXECUTE '
          select n.nspname, c.relname
          from pg_class c, pg_namespace n
          where n.oid = c.relnamespace
            and nspname = ''' || in_schema || '''
            and relkind = ''' || object_type.code || ''''
    loop
      raise notice 'Changing ownership of % %.% to %',
                  object_type.type_name,
                  r.nspname, r.relname, new_owner;
      EXECUTE
        'alter ' || object_type.type_name || ' '
                 || r.nspname || '.' || r.relname
                 || ' owner to ' || new_owner;
    END loop;
  END loop;

  FOR r IN
    SELECT  p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
    FROM    pg_catalog.pg_namespace n
    JOIN    pg_catalog.pg_proc p
    ON      p.pronamespace = n.oid
    WHERE   n.nspname = in_schema
  LOOP
    raise notice 'Changing ownership of function %.%(%) to %',
                 r.nspname, r.proname, r.args, new_owner;
    EXECUTE
       'alter function ' || r.nspname || '.' || r.proname ||
       '(' || r.args || ') owner to ' || new_owner;
  END LOOP;

  FOR r IN
    SELECT *
    FROM pg_catalog.pg_namespace n
    JOIN pg_catalog.pg_ts_dict d
      ON d.dictnamespace = n.oid
    WHERE n.nspname = in_schema
  LOOP
    EXECUTE
       'alter text search dictionary ' || r.nspname || '.' || r.dictname ||
       ' owner to ' || new_owner;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

(https://www.garysieling.com/blog/postgres-change-owner-all-objects-in-schema)

Unfortunately, the function doesn't work as expected in some cases.

CREATE ROLE user_old LOGIN;
CREATE ROLE user_new LOGIN;

CREATE SCHEMA schema_a AUTHORIZATION user_old;

CREATE TABLE schema_a.test_a (
id serial NOT NULL,
CONSTRAINT test_a_pkey PRIMARY KEY (id)
);

ALTER TABLE schema_a.test_a OWNER TO user_old;

SELECT chown('schema_a', 'user_new')

NOTICE: Changing ownership of sequence schema_a.test_a_id_seq to user_new
ERROR: cannot change owner of sequence “test_a_id_seq”
DETAIL: Sequence “test_a_id_seq” is linked to table “test_a”.
KONTEXT: SQL statement “alter sequence schema_a.test_a_id_seq owner to user_new”
PL/pgSQL function chown(character varying,character varying) line 27 at EXECUTE

********** Error **********

ERROR: cannot change owner of sequence “test_a_id_seq”
SQL state: 0A000
Detail: Sequence “test_a_id_seq” is linked to table “test_a”.
Context: SQL statement “alter sequence schema_a.test_a_id_seq owner to user_new”
PL/pgSQL function chown(character varying,character varying) line 27 at EXECUTE

Does anyone know how to fix this?


texts:
I am taking college classes in relational databases for half a year now. As an assignment get to normalize data with 1NF, 2NF and 3NF.

I do not find any difficulties making my schema compatible with 1NF. After that I have not managed to find any examples where to apply 2NF and 3NF as well. It feels like I think a bit ahead when I do 1NF.

When I deliver the assignment, all I have done is 1NF to my understanding. And it still is being approved as valid design.

Is this a normal thing to have? At the moment I do not see the use of 2NF and 3NF when all the redundancy is taken away in 1NF already. Your thoughts?