texts: Index partitioned table to prevent Postgres from doing a sequential scan

texts:
I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries.

I have defined a partitioned database based on the first letter of the "identifier" - this is split into 37 sub-tables, [0-9, a-z, default (catchall for everything else)].

The database is very simple and straight forward, and is defined below.

create table entries (
  id                bigserial,
  identifier        text null,
  password          text null,
  additional_fields jsonb
)
  partition by list (lower(left(identifier, 1)));
ALTER DATABASE credentials SET constraint_exclusion=on;

CREATE TABLE entries_0 PARTITION OF entries for values in ('0');
CREATE TABLE entries_1 PARTITION OF entries for values in ('1');
CREATE TABLE entries_2 PARTITION OF entries for values in ('2');
CREATE TABLE entries_3 PARTITION OF entries for values in ('3');
...
CREATE TABLE entries_z PARTITION OF entries for values in ('z');

ALTER TABLE entries_0 ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = '0');
ALTER TABLE entries_1 ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = '1');
ALTER TABLE entries_2 ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = '2');
ALTER TABLE entries_3 ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = '3');
...
ALTER TABLE entries_z ADD CONSTRAINT first_letter  CHECK (lower(left(identifier, 1)) = 'z');

CREATE INDEX ident_idx on entries(identifier);

However, when I run an EXPLAIN it says that is it is still doing a sequential scan.

EXPLAIN SELECT * FROM entries where identifier = 'some_identifier_from_subtable_s' LIMIT 1;

Output:

Limit  (cost=0.00..140.92 rows=1 width=104)
  ->  Append  (cost=0.00..43418531.72 rows=308113 width=104)
        ->  Seq Scan on entries_0  (cost=0.00..23239.38 rows=2 width=68)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)
        ->  Seq Scan on entries_1  (cost=0.00..150187.81 rows=6 width=68)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)
        ->  Seq Scan on entries_2  (cost=0.00..94694.38 rows=4 width=67)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)
        ->  Seq Scan on entries_3  (cost=0.00..81656.71 rows=3 width=67)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)

        ... etc.

        ->  Seq Scan on entries_z  (cost=0.00..579207.95 rows=13 width=69)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)
        ->  Seq Scan on entries_default  (cost=0.00..15582.36 rows=4 width=69)
              Filter: (identifier = 'some_identifier_from_subtable_s'::text)

What am I doing incorrectly? The intelligent partitioning should be able to redirect the query to just the entries_s partition, should it not? And then the CREATE INDEX ident_idx on entries(identifier); should make the query go through the index?

After adding the explicit query the new plan looks like this:

FROM   entries
WHERE  identifier = 'some_identifier_from_subtable_s'
AND    lower(left(identifier, 1)) = 's'  -- 1st letter of above identifier
LIMIT  1;

Output:

Limit  (cost=1000.00..2583053.76 rows=1 width=71)
  ->  Gather  (cost=1000.00..2583053.76 rows=1 width=71)
        Workers Planned: 2
        ->  Parallel Append  (cost=0.00..2582053.66 rows=1 width=71)
              ->  Parallel Seq Scan on entries_s  (cost=0.00..2582053.66 rows=1 width=71)
                    Filter: ((identifier = 'some_identifier_from_subtable_s'::text) AND (lower("left"(identifier, 1)) = 's'::text))

It's still doing a sequential scan on entries_s. Does declaring the index with CREATE INDEX ident_idx on entries(identifier); not propagate it down to all partitions?


texts: SQL Server service account change from virtual account to AD account

texts:
Had a question about how local permission are granted(like Log on as a service and Replace a process-level token) when you change the SQL Server service account from Virtual account (NT SERVICE\MSSQLSERVER) to an Windows AD account.

I first installed sql server without specifying an windows account.  After installation when I check "Local Security Policy" I could see "NT SERVICE\MSSQLSERVER" added to permissions like "Log on as a service" and "Replace a process-level token".

Then I changed service account to a windows AD account using configuration manager and went back and checked Local Security Policy again and I couldn't find the new windows AD account added in any of them.

So I am wondering how the windows AD account gets those permissions?  Is it somehow linked to the virtual accounts?

SQL Server 2014, Windows 2012R2

I have gone through the below questions, but this specific question is not covered.

SQL Server service account Windows privileges and rights


texts: Move postgresql database from one server to another

texts:
I have created a (spatial) database at my home which is several gigabytes and I want to move the whole database as it is from one computer my works computer.

Nothing fansy, I will create a new postgresql/postgis db instance at my work computer. The db users may or maynot have the same name in both computers.

Is it possible? What should i be carefull about? How can i do it pain free?

I'm using postgresql v9.1.5 with postgis extension. Ubuntu12.04 (kernel 3.2.0-31)


texts: Is the table designer not supported for memory optimized tables?

texts:
If I right click a table, I get the normal designer option:

But if I right click a memory optimized table, the designer is missing:

The server is 2016 and I've tried it in SSMS 16, 17, and 18.3. Is there just no designer support for memory optimized tables? I can usually drop and recreate the table when I need to make a change. That's what SSMS does anyway. But it's a pain in the ass when there are FKs involved.


texts: Schema and Query design for table with multiple languages

texts:
I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and another column(s) with the translated text in that language.
So I decided in the following schema:

CREATE TABLE IF NOT EXISTS Meowficer (
    meowficer_id SMALLINT PRIMARY KEY,
    skill_id SMALLINT UNIQUE NOT NULL,
    -- Cut out irrelevant columns.
);

CREATE TABLE IF NOT EXISTS MeowficerName (
    meowficer_id SMALLINT NOT NULL,
    language TEXT NOT NULL,
    name TEXT NOT NULL,

    FOREIGN KEY(meowficer_id) REFERENCES Meowficer(meowficer_id)
);

CREATE TABLE IF NOT EXISTS MeowficerSkill (
    skill_id SMALLINT PRIMARY KEY,
    next_id SMALLINT NOT NULL,
    level SMALLINT NOT NULL,
    experience SMALLINT NOT NULL,
    icon TEXT NOT NULL,

    -- Only one Skill for every Mewoficer.
    FOREIGN KEY(skill_id) REFERENCES Meowficer(skill_id)
);

CREATE TABLE IF NOT EXISTS MeowficerSkillNameDesc (
    skill_id SMALLINT NOT NULL,
    language TEXT NOT NULL,
    skill_name TEXT NOT NULL,
    skill_1 TEXT NOT NULL,
    skill_2 TEXT NOT NULL,
    skill_3 TEXT NOT NULL,

    FOREIGN KEY (skill_id) REFERENCES MeowficerSkill(skill_id)
);

And I can query the data I need this way:

-- Cut out part of the column selecting from the Meowficer table.
SELECT
    Meowficer.id, Meowficer.skill_id, MeowficerName.name, ..., MeowficerSkill.icon, MeowficerSkillNameDesc.skill_name, MeowficerSkillNameDesc.skill_1, MeowficerSkillNameDesc.skill_2, MeowficerSkillNameDesc.skill_3
FROM
    Meowficer
JOIN
    MeowficerName ON Meowficer.id = MeowficerName.id AND MeowficerName.language = 'ja' AND MeowficerName.name = 'ジャスティス'
JOIN
    MeowficerSkill ON Meowficer.skill_id = MeowficerSkill.skill_id AND MeowficerSkill.level = 2
JOIN
    MeowficerSkillNameDesc ON MeowficerSkill.skill_id = MeowficerSkillNameDesc.skill_id AND MeowficerSkillNameDesc.language = 'ja'

While the data sample I'm currently working with isn't large at all and when I tried the code above it worked without any issues, so: is this the best approach for a multi language database schema and querying? Can it be improved on?


texts:
I have a datetime I want to convert to a formatted date and time. The format is 109(9). I have always used:

CONVERT(char(26), c1.DateTime, 109)

Which works successfully, but at least on SQL Server 2014 the Cardinality Estimator is throwing a warning:

  Type conversion in expression (CONVERT(char(26),c1.DateTime,109)) may affect "CardinalityEstimate" in query plan choice.

I would like to correct this if possible at all, but maybe this is the improved cardinality estimator I am not sure.

The full query in this contrived example is from a temp table populated from an real table which is what I am trying to correct.

CREATE TABLE #Vic (
    DateStamp   datetime    not null,
    TIN int not null
);
GO
INSERT INTO #Vic
VALUES
( N'1985-09-06T00:00:00', 123456789 ),
( N'2003-01-14T20:55:00.983', 123456789 ),
( N'1997-08-07T00:00:00', 123456789 ),
( N'2016-11-15T09:31:28.15', 123456789 ),
( N'2003-01-14T20:45:29.247', 123456789 );
GO

SELECT TOP (10) CONVERT(char(26), v.DateStamp, 109) ,
    CONVERT(varchar(26), v.DateStamp, 9) ,
    CONVERT(nvarchar(26), v.DateStamp, 9),
    CONVERT(nvarchar(30), v.DateStamp, 9),
    CONVERT(varchar(9), v.TIN)
FROM    #Vic AS v;

This gives a warning on every column listed.

This may be related to this Connect Item, but I am not sure the context is the same based on the query this Connect item provides.


texts:
I am making a report, and I am trying to do following:
If the value of the field is NULL, output empty string. Another words, I don't need the word "null" to be output to the user, just empty string. Is that possible to write such a SQL query? How?
I don't even know how to formulate a google search request for this case, so I am sorry for stupid question.


texts:
I'm trying to write a SELECT query that calculates a BatchNumber column that increments by 1 every 150 rows, starting at 1001.

The expected BatchNumber should be 1001 for the first 150 rows, then 1002 for rows 151-300, and so on...

I've tried using a division on a dynamic ROW_NUMBER(), but can't get it.

SELECT ID
    ,ROW_NUMBER() OVER (ORDER BY (SELECT ID)) AS RowNumber
    ,CASE
        WHEN CONVERT(INT, (ROW_NUMBER() OVER (ORDER BY (SELECT ID)) % 150))=0
        THEN 0 ELSE 1001 END
    + CONVERT(INT,(ROW_NUMBER() OVER (ORDER BY (SELECT ID)) / 150))
    AS BatchNumber
FROM Table1 WITH (NOLOCK);


texts: INSERT .. ON CONFLICT DO UPDATE SET error

texts:
When I use the following query :
cur.execute("""INSERT INTO TABLE
(SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp
FROM tmp_table)
ON CONFLICT (ID)
DO
UPDATE SET Last_seen=tmp_table.Last_seen_tmp,Duration=tmp_table.Last_seen_tmp-First_seen;""")

I keep getting this error :
psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "tmp_table"
LINE X: UPDATE SET Last_seen=tmp_table.Last_seen_tmp,Duration=tmp...

And even if I try to use the special table EXCLUDED that references to the possible inserted values with the following code :
cur.execute("""INSERT INTO TABLE
(SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp
FROM tmp_table)
ON CONFLICT (ID)
DO
UPDATE SET Last_seen=EXCLUDED.Last_seen_tmp,Duration=EXCLUDED.Last_seen_tmp-First_seen;""")

I get another error :
psycopg2.errors.UndefinedColumn: column excluded.last_seen_tmp does not exist
LINE X: UPDATE SET Last_seen=EXCLUDED.Last_seen_tmp,Duration=EXCLUDE...

Is it impossible to update the values of the original table with values from the tmp table with an INSERT .. ON CONFLICT CLAUSE ?


texts: Safely moving and creating new tempdb files

texts:
Two things I'd like to know:

how do you safely move tempdb with minimal downtime?
how many tempdb files do you need?

Is it 1 file per core? So quad-core = 4 tempdb files, creating three new ones?


texts: Using Change Data Capture with a frequently changing schema

texts:
We are following an agile development process, which in this case means that we frequently make changes to the database schema, e.g. adding new columns, moving data to other columns, etc.

Is there a mechanism to update the CDC table to the new schema, and are there any best practices to how you deal with captured data when migrating the database schema?


texts: Postgres synchronized backup - what's with the archive?

texts:
I am setting up a Postgres 9.1 master and a hot standby-server. I read the documentation but I'm not quite sure yet what I should do with my WAL files.

Every commit is done on both servers before returning to the application, therefore archiving the WALs onto some third system seams useless. But image my hot backup server fails, then I will have to manually reconfigure the master instance not use any standyby server. Is it wise to activate the WAL archive only then?

I presume not, because the newly created WALs will be useless for any newly set up hot standby. Should I always have WAL archive active and from time to time do a complete snapshot backup of the running system and then delete the old archived WALs manually by their timestamp? Then I could quickly set up a new hot standy instance if the current hot standby instance fails? Or is it better just to let a new hot standby server catch up per TCP connection because it does some high throughput bulk transfer of the database and only switches to replaying stream WALs when it is nearly up to date? This would be great, because then I wouldn't have to care about WAL archiving at all?

If writing to the database doesn't work for a few hours I don't care, only reads have to be highly available.

Edit: I'm also interested in some details on how a new hot standby would catch up with the master database. Is everything done in transactions or is there some high speed TCP catchup involved, which would be similar (in runtime complexity) to just copying the disks.


texts: Change Name of Availability Group Replica

texts:
The name we want it to be is actively being used by a production reporting server, we are building out this VM ahead of time that will replace it and will eventually swap the names.
Was going to go ahead and get the data syncing to avoid down time and then swap the names this weekend. Hoping I don't have to swap the names, add the new VM to the AG and then start syncing

Not seeing a lot of documentation on this.


texts: MySql: #1221 - Incorrect usage of UNION and ORDER BY

texts:
I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts.

SELECT * FROM comment AS tbl WHERE sscm_id IN
(
  SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4
    ORDER BY t1.sscm_id DESC LIMIT 3
  UNION  ALL
  SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3
    ORDER BY t2.sscm_id DESC LIMIT 3
)

which gives me the following error:

1221 - Incorrect usage of UNION and ORDER BY

Some searching & googling seemed to suggest that placing () around each subquery might solve this problem but changing the query to

SELECT * FROM comment AS tbl WHERE sscm_id IN
(
  (SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4
     ORDER BY t1.sscm_id DESC LIMIT 3)
  UNION  ALL
  (SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3
     ORDER BY t2.sscm_id DESC LIMIT 3)
)

but this gives me the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL (SELECT sscm_id FROM bpsocial_comment AS t2 WHERE t2.sscm_oid = 3 OR' at line 4

So: how can I get the UNION in combination with the ORDER BY in the subqueries to work?

Thanks
R


texts: SQL Server 2012 setup fails immediately (Error result: -2067529723)

texts:
I am trying to install SQL Server Enterprise Core 2012 w SP1 64bit (as downloaded from the Microsoft Partner Program site) on a 64-bit Windows 2008 R2 SP1 server. When simply right-clicking on SETUP.EXE in the root and selecting Run as Administrator, seemingly nothing happens. When I run it from a Command Prompt (Run as Administrator), I get this:

  An error occurred during the SQL Server 2012 Setup operation.

  Error result: -2067529723
  Result facility code: 1220
  Result error code: 5

  For more information, review SQL Server 2012 Setup logs in your temp
  folder.

No log files were written anywhere. How can I find out what this error is trying to tell me and what I can do to resolve it?


texts: What happens to existing MySQL databases when I upgrade my Ubuntu distro?

texts:
I am in the midst of upgrading my Ubuntu distro from 10.4 to 12.4. Fearfully, it asked me for root password on MySQL when I was upgrading the distro.

I used to have databases on that server, would they be erased? If so, how could I get them back?


texts: CREATE VIEW as join between tables with identically named columns

texts:
As the title says, I'd like to create a view from a join between tables which have columns with identical names (surprisingly "id").

Postgres doesn't prepend the table name to the result, but for some reason the query works fine, returning multiple columns with the same name and different data.

CREATE TABLE is less forgiving, though, and  returns:

ERROR: column "id" specified more than once

Others have suggested SELECTing the columns under a different name (e.g. here), but there are many columns to both tables.

Do I need to somehow automatically rename / remove the id columns (such as this way), or is there a better way?


texts: Flat similar rows on few columns - Oracle

texts:
I wonder is there any way to flat data like this using sql query
maybe you can help me

The data looks like this:

id  name    customer_code column1 column2
-----------------------------------------
1   Tom      Dilbert        null    text
1   Tom      Dilbert        text2   null
2   Bob      Wally          text3   null
2   Bob      Wally          null    text4

My results need to look like this:

id  name    customer_code column1 column2
-----------------------------------------
1   Tom      Dilbert        text2   text
2   Bob      Wally          text3   text4


texts: Automatically create linked row in a different table

texts: What I mean is that for example if I have a table user with id name prefId, and a second table preferences with id etc..., is it possible that when I add a row to user, it would automatically create a row in preferences and set the row's id to prefId? Should I create the preferences row first and manually put its id? And is this a good practice?


texts: Use event schedule and stored procedure to automatically average every hour MySQL

texts:
I have a database in Mysql Workbench about weather that updates every second. It contains temperature and wind speed. This is my database:

CREATE TABLE `test`.`new_table` (
`id` INT(10) NOT NULL,
`date` DATETIME NOT NULL,
`temperature` VARCHAR(25) NOT NULL,
`wind_speed` VARCHAR(25) NOT NULL,
`humidity` VARCHAR(25) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

I want to find the average temperature every hour and insert the result into a new table like this

CREATE TABLE `test`.`table1` (
`idsea_state` INT(10) NOT NULL,
`dateavg` DATETIME NOT NULL,
`avg_temperature` VARCHAR(25) NOT NULL,
PRIMARY KEY (`idsea_state`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

this is my coding to find average and insert the result into new table

INSERT INTO `table1`
(`dateavg`, `avg_temperature`)
SELECT `date` , avg(`temperature`)
FROM   `new_table`
GROUP BY DATE( date ), HOUR( date );

and this is my code for stored procedure

USE `test`;
DROP procedure IF EXISTS `new_procedure`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
  INSERT INTO `table1`
   (`dateavg`, `avg_temperature`)
  SELECT `date` , avg(`temperature`)
  FROM   `new_table`
  GROUP By DATE( date ), HOUR( date );
 END$$

 DELIMITER ;

the problem is I want this coding average run automatically every hour, should I use stored procedure and event scheduler?
Please help me, I don't know how to use event scheduler in Mysql Workbench.


texts: MySQL Queries to get only runtime/duration, not fetched results

texts:
We are running few benchmark queries against our test RDS instance for checking efficiency of partitioning, before adapting it to production, where there is no partitions presently.

We have few tables of time series data, and hence partitioning based on RANGE( unix_timestamp(event_time) ). Queries we run against sets of tables(partitioned and non-partitioned ones) which has millions of rows is taking too much time, which we are stuck at the moment.

We see from MySQL Workbench it's quite obviously the fetch time(network dependent) takes up the time. What we are concerned about is only the runtime duration of the queries we run. Is there any way we can skip the fetched results part and only get the runtime duration for respective queries?

Query Examples we try :

SELECT  *
    FROM  app_event1
    WHERE  `event_time` BETWEEN '2018-01-01 00:00:01' AND '2018-02-30 23:59:59';

What we do for saving time is with limit the results with,

SELECT  *
    FROM  app_event1
    WHERE  `event_time` BETWEEN '2018-01-01 00:00:01' AND '2018-02-30 23:59:59'
    Limit  0,1000000;

which won't match live scenarios.


texts: SQL Server Backup Job failed multiple times

texts:
I am getting the same error as mentioned by Mr.Ali

Sql Server Backup Job failed

Can anyone please explain the possible ways to fix this error as the backup is failing since 1 month.

Here is the Job history log

Executed as user: SAP\sqladmin.
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started:  21:00:00
Progress: 2014-10-03 21:00:05.83
Source: {E7436186-D15A-4485-92B4-7623DA7DDF4D}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete
End
Progress
Error: 2014-10-03 23:32:25.53
Code: 0xC002F210
Source: Back Up Database Task Execute SQL Task
Description:
Executing the query "BACKUP DATABASE [P01] TO  DISK =N'\\cmgsap31.sap...."
failed with the following error:
"Write on "\\cmgsap31.sap.logica.com\p$\Central-MS-SQL-Backup\SAP0233_P01_Full.bkp"
failed: 64(failed to retrieve text for this error. Reason: 15105)
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 21:00:00
Finished: 23:32:25
Elapsed:  9145.29 seconds.
The package execution failed.  The step failed.


texts: Does anyone deployed a succesful SSAS on docker?

texts:
I'm trying to create a docker image that contains MS SQL server, analysis services, and restore a db (bak) and a cube (abf). The Sql server part is the easy stuff, and I imagine that the restore will be too quite simple... the unsuccessful part is the installation of SSAS.

I've already tried without success:

Splitting images (1 for MSSQL and 1 for SSAS)
installing from the inside of a container with SQL Server installed
trying differents account (NT AUTHORITY\NETWORK SERVICE,BUILTIN\ADMINISTRATORS)

Any chance that anyone as done something similar or is "impossible" to achieve such a [working] image?

This is a clean sample of what I'm doing:

FROM mcr.microsoft.com/windows/servercore:1903

ENV exe "https://go.microsoft.com/fwlink/?linkid=840945"
ENV box "https://go.microsoft.com/fwlink/?linkid=840944"

ENV sa_password="_" \
    attach_dbs="[]" \
    ACCEPT_EULA="_" \
    sa_password_path="C:\ProgramData\Docker\secrets\sa-password" \
    appname="demo"

SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

WORKDIR /

RUN Invoke-WebRequest -Uri $env:box -OutFile SQL.box ; \
        Invoke-WebRequest -Uri $env:exe -OutFile SQL.exe ; \
        Start-Process -Wait -FilePath .\SQL.exe -ArgumentList /qs, /x:setup ; \
        .\setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQL,AS,Tools /UPDATEENABLED=0 /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS /BROWSERSVCSTARTUPTYPE=Automatic /ASSVCSTARTUPTYPE=Automatic  /SQLSVCACCOUNT='domain\user' /SQLSVCPASSWORD='somepassword' /SQLSYSADMINACCOUNTS='domain\user' /ASSVCACCOUNT='domain\user' /ASSVCPASSWORD='somepassword' /ASSYSADMINACCOUNTS='domain\user' ; \
        Remove-Item -Recurse -Force SQL.exe, SQL.box, setup


texts:
Currently on SQL Server 2008/R2. I just read here that "set rowcount" is being deprecated in some post 2012 release:
http://technet.microsoft.com/en-us/library/ms188774.aspx

We have some legacy code that uses it presumably to delete small chunks of data and to minimize locking in our nightly "Data Purge" job.

Without rowcount, I would have to use "top xxx" instead?

  set rowcount 1000 /* Only delete this number of rows at a time */;
  declare @short_time datetime2 = DATEADD(week,-1,SYSUTCDATETIME());

      SET @TotalRowCount = 0
      while ( select COUNT(1) from FlightAudit with (nolock) ) > @min_row_count
      begin
            delete FlightAudit where CreatedUTC < @short_time;
            SET @SaveRowCount = @@ROWCOUNT
            SET @TotalRowCount = @TotalRowCount + @SaveRowCount
            if @SaveRowCount=0 break;
            print 'delete from FuelerCertificationAudit...' + convert(varchar(10),@SaveRowCount) + ' Time=' + dbo.DateOrNullToChar(getdate())
      end
      print 'Total deleted from FlightAudit...' + convert(varchar(10),@TotalRowCount) + ' Time=' + dbo.DateOrNullToChar(getdate())


texts:
when I start my mariadb service and all nodes tries to join, I get below line in the systemd service
WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
-- Logs begin at Tue 2020-11-10 13:17:02 GMT, end at Sat 2020-11-28 00:03:35 GMT. --
Nov 27 23:35:39 raspberrypi systemd[1]: Starting MariaDB 10.3.25 database server...
Nov 27 23:35:43 raspberrypi sh[5169]: WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
Nov 27 23:35:43 raspberrypi mysqld[5339]: 2020-11-27 23:35:43 0 [Note] /usr/sbin/mysqld (mysqld 10.3.25-MariaDB-0+deb10u1-log) starting as process 5339 ...
Nov 27 23:35:43 raspberrypi mysqld[5339]: 2020-11-27 23:35:43 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32183)
Nov 27 23:36:17 raspberrypi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Nov 27 23:36:17 raspberrypi systemd[1]: mariadb.service: Failed with result 'exit-code'.
Nov 27 23:36:17 raspberrypi systemd[1]: Failed to start MariaDB 10.3.25 database server.
Nov 27 23:54:07 raspberrypi systemd[1]: Starting MariaDB 10.3.25 database server...
Nov 27 23:54:13 raspberrypi sh[6252]: WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
Nov 27 23:54:14 raspberrypi mysqld[6367]: 2020-11-27 23:54:14 0 [Note] /usr/sbin/mysqld (mysqld 10.3.25-MariaDB-0+deb10u1) starting as process 6367 ...
Nov 27 23:54:14 raspberrypi mysqld[6367]: 2020-11-27 23:54:14 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32183)
Nov 27 23:54:50 raspberrypi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Nov 27 23:54:50 raspberrypi systemd[1]: mariadb.service: Failed with result 'exit-code'.
Nov 27 23:54:50 raspberrypi systemd[1]: Failed to start MariaDB 10.3.25 database server.

and found
$ sudo cat /var/lib/mysql/grastate.dat
GALERA saved state
version: 2.1
uuid:    00000000-0000-0000-0000-000000000000
seqno:   -1
safe_to_bootstrap: 1


texts: Which database technologies to consider for a data search application?

texts:
I'm designing an application that only does read queries to some database tables (it's a kind of search application); the queried data is updated at night by the system (so speed is less critical, while however very important here too), while application users only do fulltext searches within the stored data.
The retrieval performance is then a key point of the application.

Can you suggest links, Google terms or arguments and other material that I can read to properly design those parts of the database that will be involved in the users searches?
I already know the basics about indexes, and checking the queries with EXPLAIN, I'm reading up about table partitioning to see if there's anything that can help me.

The application will run on MySQL 5.6
So far its builtin FULLTEXT search is enough for our requirements, I'm looking more at hints on how to properly structure the database so that I don't add unnecessary slow downs (by leveraging the fact that no user query will update the data).

What other database technologies should I look for?


texts: SQL Server 2008 R2 Standard Edition not using more than 32GB for BufferPool

texts:
I had SQL Server 2008 R2 Standard running on Windows Server 2008 R2 Standard  with 32 GB of installed Memory.

Since SQL Server 2008 R2 Standard can use 64 GB memory, Windows was upgraded in-place to Enterprise Edition and memory increased from 32 GB to 64 GB.

SQL Server 2008 R2 Standard max memory was then increased from 28 GB to 52 GB.

SELECT name, value [mem_MB]
FROM sys.configurations
WHERE name IN('max server memory (MB)','min server memory (MB)')

Result:

name                    mem_MB
min server memory (MB)   49152
max server memory (MB)   53248

7 day after change, "Total pages" and "Target pages" show 32 GB.

select counter_name, cntr_value
from master..sysperfinfo
where [object_name]='SQLServer:Buffer Manager'
and counter_name in ('Total pages','Target pages')

Result:

counter_name    cntr_value
Total pages          4096000    <--32 GB
Target pages         4096000    <--32 GB

Why is SQL Server only using 32 GB for buffer pool?

Can someone confirm SQL Server 2008 R2 Standard can actually use more than 32 GB for buffer pool?


texts: Mongodb: Document size of 29344731 is larger than maximum of 16777216

texts:
When we are upgrading AEM 6.1 instance with mongo (Mongo version: 2.6 ) to AEM 6.3  with mongo (Mongo version: 3.2) we are facing the exception.

  org.apache.jackrabbit.oak.plugins.document.DocumentNodeStore
  Background operation failed: org.bson.BsonSerializationException:
  Document size of 29344731 is larger than maximum of 16777216.

How DocumentNodestore exceeds its limit?
Is there anyway to resolve the issue?
Is revision garbage collection will reduce the DocumentNodeStore size?


texts: Advice on T-SQL script, change to use dynamic SQL

texts:
The below script will query Ola's Maintenance Solution results table (CommandLog) and come up with a comparison on the bi-weekly reindex exec duration, for the last 4 weeks.

The way it looks right now, it's a quick hack to help me tune the maintenance windows. But I would like to remove the date hardcoding, so that I don't have to manually add every future weekend as a new JOIN.

Please provide a complete rewrite (dynamic SQL?) or just some hints on how to achieve this or some other useful features to be included. Ok to change the sproc and add extra output to the table. Using SQL 2016. If there's already a script out there that serves the purpose, happy to go with that one.

WITH t0 AS
(
SELECT ObjectName, IndexName, IndexType
FROM Tools.dbo.CommandLog
WHERE 1=1
AND DatabaseName = 'testdb'
AND CommandType = 'ALTER_INDEX'
GROUP BY ObjectName, IndexName, IndexType
)

SELECT
 t0.ObjectName
,t0.IndexName
,t0.IndexType
,DATEDIFF(ss,t1.StartTime,t1.EndTime) as '20-40 01-06'
,DATEDIFF(ss,t2.StartTime,t2.EndTime) as '5-40 01-07'
,DATEDIFF(ss,t3.StartTime,t3.EndTime) as '20-40 01-13'
,DATEDIFF(ss,t4.StartTime,t4.EndTime) as '5-40 01-14'
,DATEDIFF(ss,t5.StartTime,t5.EndTime) as '20-40 01-20'
,DATEDIFF(ss,t6.StartTime,t6.EndTime) as '5-40 01-21'
,DATEDIFF(ss,t7.StartTime,t7.EndTime) as '20-40 01-27'
,DATEDIFF(ss,t8.StartTime,t8.EndTime) as '5-40 01-28'
FROM t0
LEFT JOIN Tools.dbo.CommandLog as t1 ON t0.ObjectName = t1.ObjectName AND t0.IndexName = t1.IndexName AND t1.StartTime BETWEEN '2018-01-06 00:30:00' AND '2018-01-06 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t2 ON t0.ObjectName = t2.ObjectName AND t0.IndexName = t2.IndexName AND t2.StartTime BETWEEN '2018-01-07 00:30:00' AND '2018-01-07 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t3 ON t0.ObjectName = t3.ObjectName AND t0.IndexName = t3.IndexName AND t3.StartTime BETWEEN '2018-01-13 00:30:00' AND '2018-01-13 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t4 ON t0.ObjectName = t4.ObjectName AND t0.IndexName = t4.IndexName AND t4.StartTime BETWEEN '2018-01-14 00:30:00' AND '2018-01-14 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t5 ON t0.ObjectName = t5.ObjectName AND t0.IndexName = t5.IndexName AND t5.StartTime BETWEEN '2018-01-20 00:30:00' AND '2018-01-20 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t6 ON t0.ObjectName = t6.ObjectName AND t0.IndexName = t6.IndexName AND t6.StartTime BETWEEN '2018-01-21 00:30:00' AND '2018-01-21 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t7 ON t0.ObjectName = t7.ObjectName AND t0.IndexName = t7.IndexName AND t7.StartTime BETWEEN '2018-01-27 00:30:00' AND '2018-01-27 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t8 ON t0.ObjectName = t8.ObjectName AND t0.IndexName = t8.IndexName AND t8.StartTime BETWEEN '2018-01-28 00:30:00' AND '2018-01-28 23:59:59'
WHERE 1=1
ORDER BY
 t0.ObjectName
,t0.IndexName
,t0.IndexType


texts: Looping through tables where the name increments monthly

texts:
I have a table that is missing 18 months worth of data in one column (it is currently 0). I have found the data on another database but the tables I am getting the data from are created monthly. I could update the table by manually changing the table name and date range 18 times but I would rather use a loop (or something better) for this. I'm using SQL Server. My actual problem is a little more complicated but I was hoping to get some points to get me started.

My example is something like this -

UPDATE customers
SET Account_Number = (SELECT Account_Number
                      FROM Hist01_12 -- I need this to increase by one month every loop until Hist06_13
                      WHERE Hist01_12.Account_Number = customers.Account_Number) -- I need this to increase by one month every loop until Hist06_13
WHERE Account_Number = 0
AND Date between '2012-01-01' and '2012-01-31' ;  -- I need these months to increase until '2013-06-01' and '2013-06-30'


texts: Storing a large number of small size entries in MySQL

texts:
I have a database that stores readings from numerous gauges.  Depending on the gauge, there may be data for that gauge every 1-5 minutes with multiple readings (i.e.- weather station and it stores 5 different readings).

I currently store the data by date.  I group everything for a particular gauge for a single date together and store is as xml in a text column.

Would it be more efficient to store this data as individual rows?

I'm trying to decide which is the smartest solution long term (i.e.- fewer rows with more data per row, or many more rows, each being smaller).

It is a read heavy environment.

EDIT:

By 'efficient' I am most concerned about speed and resource usage (if I pull back a year's worth of data, which would be least processor/memory intensive).

I almost exclusively use this data to graph for clients.  The number of readings per timestamp is variable, from 1 to 6 readings.

The database is currently an INNODB.

EDIT 2:

I'm looking more towards keeping the data in MySQL for ease of access/updating.

The answer I was looking for more was whether it would be smarter to save each individual timestamp (which may have multiple readings) in an individual row or whether to group multiple timestamps (i.e.- up to a day's worth) into a single row.

Thanks


texts: I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?


texts: Best practice for entry position indicators

texts:
My problem is, that I have no idea how I could cleanly make an index (not a real Table index, just an indicator) for each table row representing the entries position while each position must be unique but inserting a new entry may not cause everything to change its positions.

Here is an example:

Let's assume we have a table products, where each product has clearly its product-id as primary key. Now I am making a catalog of all products and another categorized by anything. Now I certainly could take the product-id or the product name to sort the lists but I want some of them to appear earlier and some not so I am going to give them indexes.

My first solution would be:

Each product gets its own position index like product p-2151 => position 1, p-553 => position 2 etc. No problem so far until I insert a new product between p-2151 and p-553. Now I had to reposition all following products what is no good idea, because I would have to refresh the complete product-cache of my application to recognize it.So this is definitely not the way to go.

Normally I have no problems with such almost trivial things but somehow I just don't get it here.

What would be the cleanest way to go here?


texts: Can I use SELECT statement for values in a list partition? Or do I need to explicitly state them?

texts:
So I have a table I would like to partition, and in creating the partitions I am trying to run a statement like this:

partition by list(apple_type)

create table foo.apples_green
partition of foo.apples_master
for values in(select apple_type from foo.fruits where fruit_type = 'apples' and color ='green');

Unfortunately I keep getting an error: ERROR: syntax at or near "select".  I don't understand if it's just not something that can be done in postgres10, or if I am totally missing some type of syntax.  Any help would be great.

Thanks much!


texts: How to prevent sysadmin server role from executing a stored procedure

texts:
I have a SQL Server default instance using SQL Server Authentication and two users, User1 and User2.

User1 has the public server role while User2 has the public server role  + sysadmin server role.

I have created one database, sptest1. Inside that database I have created a stored procedure, sp_viewtbl and also created a schema. Using the schema I have disabled executing stored procedure for User1 and it works.

How can I deny executing the stored procedure by User2, who has sysadmin server role?

Suppose I have created User3 and given permissions as sysadmin and public.
So how can I prevent that new user from executing that same stored procedure? I have to secure sp_viewtbl from others login even if they have sysadmin permission.


texts: Moving tempdb from shared to local drive in a failover cluster

texts:
We have an existing failover cluster that I would like to have the tempdb in local drive. I'm not sure how to proceed. My thought process is below:

Create two drives on each node one for tempdb and one for temp log with same name on each node. Give the SQL Server service account full control permission on the folders that will have the files.
Move the tempdb files by failing over to the active node and moving the tempdb files, this has to be done on all the nodes.

Is there anything else I need to do? Do I need to rerun the cluster validation or add the local disks to dependency? Will this break the cluster?


texts:
What is the significance of sync_binlog?

What exactly happens If I set sync_binlog=1?

How does it affect the replication between master and slave?


texts: Mongodb extremely slow with bulk inserts and showing erratic behavior

texts:
I have been massively frustrated by the erratic behaviour of Mongodb's bulk  inserts. I'm trying to write 20K documents in a bulk-insert query. Sometimes the inserts take as low as 5 seconds and sometimes (for no change in environment) takes 300 to 400 seconds. This is massively frustrating as it leaves the application architect in dark as to have any assumption on time requirement of this query.
I have nearly 6 million documents (tweets) in a collection and I have several indices (including text index) on collection. The overall size of indices is around 7Gb on a 14 Gb machine. Journaling is off.
I have realized that writes (inserts) are super slow when mongod has just been restarted.
There's also a TTL index on the collection and Mongo TTL index are most error-prone from my previous experience. Moreover logs never seem to show the TTL index deletion stats.
I have two identical (same VM, configuration and mongodb 2.6.4) instances of mongod running on different machines and I'm trying to write 20K documents to both of them at the same time. The document gets committed on one machine in 10 seconds and it has log entries like these:

command day_raw_tweet_db.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:17 reslen:40 392ms
The log entries on the second instance (which is same as before) shows individual documents one per line in log output and as feared it takes around 400 seconds to insert the same number of documents.
There's no read load on both the instances and they both are equally idle.

- Can someone please help me as I'm going crazy over this mongodb behaviour.The intermittent bad behaviour is the worst anyone can cope with.


texts: SQL Server: Buffer page read/writes per second reported high

texts:
Our SQL Server is reporting high buffer page read/writes per second. Several documentation sources suggest these stats should be less than 90 per second. Our database server is reporting:

counter          cntr_value  cntr_type
--------------------------------------
Page reads/sec   4023952     272696576
Page writes/sec  6483243     272696576

I'm not certain that I'm reading those stats correctly, but it appears they're much higher than 90. Despite these high read/write numbers, the buffer cache hit ratio is 100%.

Several sources mention that low memory issues can lead to this high read/write buffer page problem.  The server has 96GB of memory and is only using 45GB, 50GB is free. Maximum instance memory is configured for 92GB, although this is standard version of SQL Server and I believe that's limited to 64GB of memory.

The Server is virtualized via KVM on CentOS linux. The virtual guest is running Windows Server 2012 and SQL Server 2012, has 8 virtual CPU's and 96GB of memory. The only other virtual guest on this virtual host is a SQL Server test server which also has 8 virtual CPUs and 24GB of memory in use. The Test system is idle.

The SQL Server has a single instance, with three databases. One database is 50GB, the second 10GB, and the third 2.5GB. The database server has around 20-30 concurrent users and is running a financial system, mostly small single record changes, with periodic medium sized batch transactions.

UPDATE
I found that these counters are cumulative. In other words, you need to take note of the counter at two periods with a known time difference and then perform the math. Basically, we're rarely going to disk and this is not a problem for us.

Yes, we are experiencing some performance issues in some areas. Slow response with data querying from the application screens is the most common. However, I don't know that it's a database related problem, it could be something else in the applications stack.

This is a new RDBMS system, recently converted from HPUX/Oracle. My only baseline is from that Oracle system. Unfortunately, the HPUX box was an older PA-RISC system that was no where near as powerful and with less/slower resources.

In many areas (Copying databases, backups, etc.) the SQL server system is much faster. I'm starting to think the problem is elsewhere.


texts: CU Update to Sql Server 2014 hangs for 7 hours

texts:
How to improve time for patching installs?  Applying patches on test Hyper-V VM's prior to production patching.  It is a two node Always-On AG.

From Detail.txt when I let it run:

(01) 2017-04-26 15:55:36 Slp: Running discovery on remote machine: SQLHADEV1

(01) 2017-04-26 21:48:32 Slp: Discovery on SQLHADEV1 is complete

Things I've checked/done:

Resources are lightly used shown in task manager
Validated the Cluster
Overkill on security, the account running the SQL Services on both nodes is sysadmin on both nodes
Attempted to get cluster log but it error-ed

PS SQLSERVER:\> Get-ClusterLog -Destination C:\tempsql
Get-ClusterLog : The network name cannot be found.

Tested install media on non-AG local box and it installed as expected
Applied windows updates on both servers, failed over, failed back, and restarted servers.  Successful automatic failover

HKLM\System\CurrentControlSet\Services\LanmanServer\Parameters\AutoShareServer to 1. restart the server service.  Validate my navigating to \servername\C$.  If you can't navigate there, the Admin$ share is not available.
Ran the patch install on a non-AG Hyper-V VM successfully
No errors or messages in the Windows Event Logs or SQL Server event logs on either node that are telling.


texts:
After we configured six replica set servers and three config servers with mongos. we are not able to see records from mongos server. Kindly have a look my below errors and please suggest to us.

mongos>use test
switched to db test
mongos> show collections;
2014-12-03T15:11:09.312+0300 error: {
  "$err" : "could not initialize sharding on connection firstset/localhost1:10001,localhost2:10002,localhost3:10003 :: caused by :: mongos specified a different config database string : stored : X.X.X.1:20001 vs given : X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003",
  "code" : 15907,
  "shard" : "firstset"
} at src/mongo/shell/query.js:131 mongos>


texts: What happens when we add an index to an existing table with a large amount of data?

texts:
I've a table which will contain around ~15 Million records. Now I need to add an index to the table.

Adding an index will take some time to update every entry in the table.

I'm quite confused whether adding the index will cause downtime.

If yes, then how can I overcome the downtime?


texts: How to model a course > section > lesson structure where lessons can belong to either sections or courses?

texts:
I have three entities:

Course
Section
Lesson

Each "course" is made up of several "lessons".
The "lessons" inside a course, can either be categorized into "sections", or not.
So, the contents of a course could look either like this:
Foo Course:
    Lesson 1
    Lesson 2
    Lesson 3
    Lesson 4

Or like this:
Bar Course:
    Section 1:
        Lesson 1
        Lesson 2
    Section 2:
        Lesson 3
        Lesson 4

So, in other words, a course can either directly have "lessons", or it can have "sections" that in turn have "lessons".
From the other perspective, a "lessons" can either directly belong to a "course", or belong to a "section" that in turn belongs to a "course".
I'm struggling with how to implement this structure in a a relational database.
If every "lesson" had to necessarily belong to a "section", it would be easy, I could just simply have a "Course" table, a "Section" table with a "CourseId" column, and a "Lesson" table with a "SectionId" column.
But my scenario is not as straightforward as that. A "section" can potentially exist as a middleman between a "course" and several "lessons", but it can also be absent, in which case a "course" directly has the lessons and no there are no "sections".
I'd appreciate any suggestions regarding how such a structure can ideally be implemented in the context of relational databases.
Thanks.


texts:
I have one table in mysql named 'UserFriends' where I am updating my websites user's friends details.

here is the schema of the table (UserFriends)

id  int,
Userid int,
friendid int,
createdate timespan

now, I want to create unique index on userid & friendid. that i have created unique index well. so, right now i am not able to insert same userid and friendid as duplicate. but if i am inserting same value in opposite field it accept without generating error.

example :

insert into userfriends ( userid, friendid )
select 1, 2  --- insert perfect
insert into userfriends ( userid, friendid )
select 1, 2  --- show error because unique index comes in a picture

now i am inserting

insert into userfriends ( userid, friendid )
select 2, 1  --- records insert here (i don't want this)

How do i prevent this?


texts: .NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

texts:
I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .NET CLR assembly to parse strings.

The error I get is:

  An error occurred in the Microsoft .NET Framework while trying to load assembly id 65540. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
  System.IO.FileLoadException: Could not load file or assembly 'clrsplit, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

  System.IO.FileLoadException:      at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)     at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)     at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)     at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)     at System.Reflection.Assembly.Load(String assemblyString) [SQLSTATE 42000] (Error 10314).

Error: 10314, Severity: 16, State: 11.

The database TRUSTWORTHY bit is set:

name             is_trustworthy_on
msdb             1
SimplusStaging   1

PERMISSION_SET is set to UNSAFE. Assembly is marked UNSAFE because those were the vendor installation instructions. Have tried dropping the Assembly and related T-SQL objects and re-creating them. No change. The DBO user is the SA login. And, this is the only CLR assembly we use for this database/server.

This is a new install using detach/attach. Did the KB2919355 install because otherwise SQL Server 2016 wouldn't install.

I would love to use the new STRING_SPLIT function in 2016 except that the application is supported by a third-party and they built everything for 2008 R2. Wanted to take advantage of some of the new features in 2016 where "it just runs faster".

The SID of the db owner is the same for both the original install and the new install. Both of the following queries return 0x01 when run in the context of the problem database:

SELECT [sid] FROM sys.database_principals WHERE [name] = N'dbo';
SELECT [owner_sid] FROM sys.databases WHERE [database_id] = DB_ID();

Do I need to recompile the CLR assembly for a new .Net Framework or should it just work?

Doing this same migration to SQL 2012 and 2014 on Windows 2008 R2 worked without issue.


texts:
I'm quite new to MongoDB and now I was trying to understand the best practices to secure a DB in production and how to automatically backup it.

At the moment for security issues I just changed auth true in the mongod.conf, created a new user for my test DB and changed the port to be something different than 27017.

First question: There is something else to do to enhance security?

Regarding the backup, I have seen different blogs but I didn't understand few things:

Do Mongo create an automatic file for the backup (something called BSON file)?
Is it a good idea to backup the DB every time a value changed?

I wish to understand very well how to be totally safe.

I hope in your help to understand very well the steps I should do to be safe.

Many Thanks


texts: How to set up multiple fields as primary key in MySQL?

texts:
I have a table with fields

EmployeeID

blahblah

blahblah2

.....

RecordMonth

RecordYear

so each employee should only have a matching entry for a month, year, Emp#.  How do I set up a table.

So how do I set up the table so that EmployeeID can be updated once a month but can never have two entries for a matching month and year?


texts: Is SELECT COUNT GROUP BY more efficient than counting a result set?

texts:
I have a table with a rank column which can be either 1, 2 or 3. I need to count the number of records for each rank, but I also need to display all the data ordered by rank.

From my perspective, the two ways to accomplish this would be:

Pull out the data with a normal SELECT and then run a second query to obtain counts:

SELECT rank, COUNT(id) FROM tablename GROUP BY rank

Pull out the data with a normal SELECT and then iterate twice over the result set I get: once to count the occurrences of each rank, and the second time to actually display the data.

Which way would be more performant? Does it depend on table size? I imagine for large tables the SELECT COUNT would be a tad slower than counting in (PHP, ASP.NET, Java), in particular if there's more than 3 discrete values I want to count.


texts:
Trying to implement a simple user follower system in MySQL, using a junction table:

CREATE TABLE users (
    id int NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    username varchar(25) NOT NULL,
    password varchar(25) NOT NULL,
    apikey varchar(45) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(email),
    UNIQUE(username),
    UNIQUE(apikey)
);

CREATE TABLE followers (
    id_follower INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    id_following INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    PRIMARY KEY (id_follower, id_following)
);

I insert records for followers like this(using Python):

db.cursor.execute(
'''INSERT into FOLLOWERS(id_follower, id_following)
values ((SELECT ID FROM USERS WHERE apikey = %s), %s)''',
(request.apikey, int(request.userid)))

I want it so that if a follower account is deleted, all follows for that account will be deleted, and if an account being followed is deleted, all follows for that account will be deleted as well. The ON DELETE CASCADE doesn't seem to be working, if I delete accounts, all follower records remain in the followers table. Am I not using it correctly?


texts:
I  referred this to set up a scheduled job and did  everything as it mentioned(ran the pgagent.sql as well) I need a very simple task to run  periodically. In the 'Definition' tab in the steps, few records are added into one of my table. How ever the statistics does not show it runs.    . I use postgres 9.3. Please any one let me know where I take a look and any solution?

EDIT: Here is how I have schedule the job


texts: how to convert mongodb(2.4.10) startup2 to primary

texts:
I have standalone bitnami mongodb(2.4.10). The output of rs.status() command is below :

rs.status()
{
    "set" : "test",
    "date" : ISODate("2015-11-13T08:07:41Z"),
    "myState" : 5,
    "members" : [
        {
            "_id" : 0,
            "name" : "10.11.208.182:27017",
            "health" : 1,
            "state" : 5,
            "stateStr" : "STARTUP2",
            "uptime" : 1024,
            "optime" : Timestamp(0, 0),
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "errmsg" : "initial sync need a member to be primary or secondary to do our initial sync",
            "self" : true
        }
    ],
    "ok" : 1
}

How can I change it to primary?

I have to use replica set in standalone server.
This server was launched by bitnami mongodb)2.4.10) AMI.

I am new for mongodb. Any help much appreciated.

I Don't have any replica set member. It was standalone server.
rs.conf ()

dwandra:STARTUP2> rs.conf()
{
    "_id" : "dwandra",
    "version" : 93960,
    "members" : [
        {
            "_id" : 0,
            "host" : "10.11.208.182:27017"
        }
    ]
}


texts: need to find instances which have multiple rows with the same userID and ResourceID and have a location of 1 and also have other locations

texts:
I have a table with

User_ID, Resource_ID, Location_ID, INstance_ID
1,1,1,1
1,1,2,2
1,1,2,3
2,2,3,4
2,3,3,5
2,3,5,6
3,1,2,7
3,1,7,8
4,4,1,9
4,4,2,10
5,5,1,11
1,1,3,14
4,4,1,15
1,1,10,18
1,1,1,19
5,6,1,12
6,7,3,13
6,7,1,17

I need a list of instance_IDs where, for a specific User_ID and Resource_ID, that user/resource combination has a row in Location_ID 1 AND has rows where Location_ID is not 1.

For example:

Instance id 1 (as user 1 has resource 1 in location 1 and location 2)
Instance id 9 (as User 4 has resource 4 in locations 1 and location 2)

Expected output  (the 5 rows that have Location_ID = 1 and there is at least one other row with same User_ID and same Resource_ID but different than 1 Location_ID):

User_ID  Resource_ID  location_ID  Instance_ID
-------  -----------  -----------  -----------
1        1            1            1
1        1            1            19
4        4            1            9
4        4            1            15
6        7            1            17


texts: Is it normal to take days to compile PostgreSQL from source code?

texts:
I am trying to install PostgreSQL 10 beta from source code by following their documentation. Is it normal that make and make install take more than 12 hours to complete? Would using extra CPU cores and memory speed things up? Is there a binary available for Debian 8?

Thank you!


texts: mySQL Master not getting data from slave

texts:
I have followed the instructions on the Web on how to set up master-slave mySQL replication and I have spent a day and half trying to get it to work.

The master is simply not getting new data at all. I looked at phpMyAdmin and I see "replication" enabled on both systems - slave/master. But nothing shows up. The DB structure is identical on both systems.

It has never generated any errors. The slave is able to connect to the master as shown in show slave status command. The PROCEESSLIST showed no errors. I used the replicate_do_db set on slave. If I unplug the ethernet cable, it will generate error. Plug it back in, and it just does nothing (except that the error went way). I changed the password and it generated error as expected but put it back to right password and it just went silent with no errors.

It's so aggravating as it doesn't tell me WHY it's not updating at all or why it's not pushing data to master. I am under the impression that any new data that's entered in slave DB should automatically be replicated to the master and the master should be showing data. Is this correct?

Is there any way that I can find what went wrong considering that the replication setup tells no errors whatsoever?  How do I force the master to get data from the slave? Is there a way to do it?


texts: Validate id sequence consistency in records in Redshift

texts:
I have a backend that log event to Redshift, for each event it generates a unique id. The id is a sequential number.

I have something like (events table):

+-------------------------+------+
| created_at              |  id  |
+-------------------------+------+
| 2017-06-30 09:20:47 UTC | 100  |
| 2017-06-30 09:18:31 UTC | 101  |
| 2017-06-30 09:16:19 UTC | 102  |
| 2017-06-30 09:12:08 UTC | 103  |
| 2017-06-30 09:11:59 UTC | 104  |
| 2017-06-30 09:11:15 UTC | 105  |
| 2017-06-30 07:03:41 UTC | 106  |
+-------------------------+------+

I have task than run every hour that move few records to an other table (deactivated_events).

I would like to verify that for the last 3 hours I don't have any missing record using id sequence.
First I thought about using generate_series but this is not present in Redshift. Other suggested to make a table with only the ids but that's still a pain to populate database with integer (here one example to generate 1 million

I'm wondering if the best is not use min max and count like this :

WITH merged_events AS
  (SELECT *
   FROM
     (SELECT id, created_at
      FROM events
      UNION
      SELECT id, created_at
      FROM deactivated_events
     )
   WHERE created_at > GETDATE() - INTERVAL '3 hours'
   ORDER BY id)
SELECT COUNT(*), (max(id) - min(id) + 1) AS diff
FROM merged_events;

PS: Bonus, how to found missing or duplicate records?


texts:
Imagine we have a CRUD application in Python and are using SQLite.
Let's say we have in SQLite a table called CUSTOMERS:

CREATE TABLE CUSTOMERS
(ID INTEGER PRIMARY KEY AUTOINCREMENT,
NAME TEXT
PHONENUMBER TEXT);

Everytime we update something we want to be able to look back to get the previous data. Let's say we do:

UPDATE CUSTOMERS SET PHONENUMBER = "1065165" WHERE ID = 1;

Now I want to be able to get the old PHONENUMBER for ID = 1 or even the whole PHONENUMBER history of that single CUSTOMER ID.

How would I implement that? Does SQLite have native support for such a functionality or do I need to do it with an extra table (with CUSTOMER ID as Foreign Key) like "CUSTOMER_UPDATES" (DATE, FIELD, OLD_VARIABLE)?


texts: Set database offline with rollback immediate takes an hour to complete

texts:
Every night we take our production database offline, detach it, and then re-attach it to sever any active connections before start of our backup procedures. The first command is:

USE master
ALTER DATABASE thedb SET OFFLINE WITH ROLLBACK IMMEDIATE

This command used to take 20 seconds to two minutes earlier to complete which was acceptable. From past few weeks it has been taking 15 minutes to over an hour to complete. There hasn't been any change in database usage that would cause this problem, and it only happens about 3/4 of the time.

As far as I know, we have not changed any settings that would cause this. My understanding was WITH ROLLBACK IMMEDIATE severed all connections and the database goes offline immediately. I have tried investigating the following:

recovery interval is set to 0 so checkpoints happen every few minutes and only take a second to complete
Statistics do not update asynchronously
no large queries are running just before the database goes offline
checking sp_who2 while the database going offline shows no users connected to it
the database's log file is very small, ~30 MB

We are currently running MSSQL Server 2012(Standard Edition) in simple recovery mode with no mirroring. The database is about 300 GB in size.

Any help is appreciated, thank you!


texts: Usage of SQL Server 2008 R2 Express. Please Advise

texts:
I'd like you all to give me hints and advise on the following scenario:

We are considering using Microsoft's SQL Server 2008 R2 Express for a business Web Application.

The Web Application won't have more than 10 CRUDs, and won't have a high data traffic, all of them are simple web forms. About data scalability, the Web Application is basically focused on employee data, like personal information, vacancy control, and the daily frequency control.

From what I've seen, I've estimated a maximum of 5 simultaneous users, generating data traffic.

And also, our employee table should hold a maximum of 100 records.

As to physical limitations I think we're fine, since I don't think a maximum of 100 employee should generate 10 GB for the next, say, ten years? But since I've read that the Express edition only manages 1 GB of RAM and uses only 1 CPU, I thought it'd be best to ask.

Do you think it can manage to keep up a good response and performance in this case?

So please advise on using SQL Server 2008 R2 Express or some other database manager. Just keep in mind that it has to be free.

Aditional info:

Windows Server 2008;
4 GB of RAM;
1 TB HD.


texts: Why are backups of a previous incarnation marked obsolete without regard to redundancy?

texts:
After incomplete recovery and opening a 9i database with resetlogs we ran a full backup which completed successfully. The backup includes a command to delete obsolete backups after it finishes:

delete noprompt obsolete device type sbt;

RMAN is configured to use REDUNDANCY 2:

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

However all the backups from the old incarnation were immediately marked obsolete and deleted without regard to redundancy.

Would this behaviour have been different if we had a RECOVERY WINDOW configured instead of REDUNDANCY 2?
Is this behaviour the same in later versions of Oracle?

edit: added output of LIST INCARNATION:

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN      Reset Time
------- ------- -------- ---------------- --- -------------- ----------
1       1       LIVE     3494832994       NO  1              19-JAN-04
2       2       LIVE     3494832994       NO  11966702870498 01-JAN-14
3       3       LIVE     3494832994       YES 12041003378277 04-JUL-18


texts: Selecting top 10 from indexed field of a big table takes too long

texts:
I have a table with 165M records like this:

Performance
   id        integer
   installs  integer
   hour      timestamp without time zone

I also have an index on hour:

CREATE INDEX hour_idx
  ON performance
  USING btree
  (hour DESC NULLS LAST);

However, select top 10 records ordered by hour takes 6 minutes!

EXPLAIN ANALYZE  select hour from performance order by hour desc limit 10

Returns

Limit  (cost=7952135.23..7952135.25 rows=10 width=8) (actual time=376313.958..376313.964 rows=10 loops=1)
  ->  Sort  (cost=7952135.23..8368461.00 rows=166530310 width=8) (actual time=376313.957..376313.960 rows=10 loops=1)
        Sort Key: hour
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Seq Scan on performance  (cost=0.00..4353475.10 rows=166530310 width=8) (actual time=0.006..327149.828 rows=192330557 loops=1)
Planning time: 0.070 ms
Execution time: 376330.573 ms

Why is it taking so long? If there is an index on date field desc - shouldn't it be super quick to retrieve data?


texts: What is the name of this type of query, and what is an efficient example?

texts:
The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data:

create table marketing_package_product (
  package_id int not null references marketing_package(id),
  product_id int not null references product(id),
  primary key (package_id, product_id)
);

insert into marketing_package_product values
(1,1),
(1,2),
(1,3),
(2,1),
(2,5);

Given products 1,2,3, I want to get marketing_package 1. But given products 1,2 only I do not want marketing_package 1.

Is there a name for this type of query, and what is the most efficient way to go about it?


texts: Windows cluster failed over, but SQL instances didn't move

texts:
I recently inherited a SQL Cluster (2008R2) which for the most part behaves itself impeccably.

The windows cluster is made up of two nodes running Active/Passive, Node1 and Node2 are dedicated blades in two different data centers. There are 3 SQL instances all running on Node1. Quorum is established by a File Share Witness and we have a heartbeat between the two nodes.

The other day someone switched off the file share witness by mistake, and the windows server failed over from Node1 to Node2. Or should I say, in Failover Cluster Manager, Node2 was now specified as the active node by Windows.

However, the SQL Cluster didn't do anything. All the instances stayed up and hosted on Node1. I would have expected them to move Nodes, but no.

There was no adverse affect on the databases at all.

Once power was resumed to the File Share Witness I brought it online again and the Windows Cluster failed back to Node1.

Our Windows Technicians are looking into why the cluster failed over, and I'm left scratching my head with the SQL bit.

All I can think of is that the heartbeat kept the SQL instances on Node1 and losing the witness wasn't important.

I'm still learning the small details of Windows Clustering, being much more used to Log Shippping and Mirroring when it comes to HA solutions, so any insight into why the SQL Instances didn't failover would be appreciated.


texts: MySQL to SQL Server - Unixtime being major problem

texts:
I'm trying to convert this query to SQL Server.

It already has a lot of updates. Still, I don't know what to do from here ( i'm not the best when it comes to programming in MySQL )

 SELECT wo.WORKORDERID,
dpt.DEPTNAME "Department",
aau.USER_ID,
aau.FIRST_NAME "Requester",
sadt.RATING "Rating Value",
spre.COMMENTS "Comments",
    FROM_UNIXTIME((spre.CREATEDTIME/1000)) "criacao da pesquisa",
    #FROM_UNIXTIME((wo.RESPONDEDTIME/1000)) "Resposta da pesquisa",
            MONTH(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Mes Pesquisa",
            YEAR(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Ano Pesquisa",
#wo.RESPONDEDTIME "RespondedTime",
aau1.FIRST_NAME "Technician"
    FROM SurveyPerRequest spr
INNER JOIN SurveyPerRequestExt spre ON spr.SURVEYID=spre.SURVEYID
LEFT JOIN SurveyDetails sdt ON spre.SURVEYID=sdt.SURVEYID
LEFT JOIN SurveyAnswers sadt ON sdt.SURANSID=sadt.SURANSID
LEFT JOIN WorkOrder wo ON spr.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser sdu1 ON wos.OWNERID=sdu1.USERID
LEFT JOIN AaaUser aau1 ON sdu1.USERID=aau1.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
    WHERE (spre.CREATEDTIME >= UNIX_TIMESTAMP('2014-04-01 00:00:00')*1000) AND (spre.CREATEDTIME <= UNIX_TIMESTAMP('2014-08-31 23:59:59')*1000)
AND aau.FIRST_NAME LIKE '%Pumputis%'
        ORDER BY spre.CREATEDTIME

Thanks a lot for the help


texts: store two tables into a single table in mySQL

texts:
create table tempA (txt1 int(3))

insert into tempA(txt1) values(25),(null),(77),(44)

create table tempA1 (txt2 int(3))

insert into tempA1 (txt2) values (null),(null),(null),(11),(88)

Can any one suggest me, to insert these two table into a single table(tempA and tempA1) into temp table. But the condition is like the temp table should hold only the values.

Thanks in Advances


texts: Drop database owned by specific user in PostgreSQL

texts:
I have created database with bad symbols at the end of its name.
Now I want to drop it but I don't know how to type its name in the console.
Can I remove databases owned by this user? I tried to DROP OWNED BY devuser but it does not worked. I see just DROP OWNED in the console.


texts: How to do a junction table referencing constraint?

texts:
I want to create a questionnaire which matches some questions together.
CREATE TABLE questionnaire(
 question_id int  PRIMARY KEY
 question_name varchar
 question type int
 -- some other columns
)
CREATE TABLE mapping(
 question_id_type1 int
 question_id_type2 int
 CONSTRAINT fk_qid_type1
  FOREIGN KEY (question_id_type1)
  REFERENCES questionnaire(question_id)
 CONSTRAINT fk_qid_type2
  FOREIGN KEY (question_id_type2)
  REFERENCES questionnaire(question_id)
)

How do I enforce that question_id_type1 only has values in questionnaire that has a question_type = 1 and question_id_type2 only has values in questionnaire that has a question_type = 2.
I know that creating two tables to replace questionnaire i.e.
CREATE TABLE questionnaire_type1(...)
CREATE TABLE questionnaire_type2(...)

would solve the problem however i have some other columns/referencing keys in questionnaire that would be repeated in both tables. May i know what is the best way to solve this problem?


texts: First query is really slow with WiredTiger, not with mmapv1

texts:
We recently upgraded to MongoDB 4.0. Loving the performance improvements with WiredTiger, but all of a sudden our test suite is timing out on certain machines.

Before running our tests, we run the following command:

mongod --dbpath ./.test-data --bind_ip 127.0.0.1 --fork --logpath ../testlog.txt

Everything seems great. The database starts. Node connects to it. We create a bunch of collections and indexes.

Then once we run the first query, it takes 10+ seconds to run.

To be clear, at this point the database has never had any data inserted into it. It's brand new.

Using MMAPv1 (with the following command) resolves the problem, but I think it'd be great to run our tests with WiredTiger, if possible.

mongod --dbpath ./.test-data --storageEngine mmapv1 --bind_ip 127.0.0.1 --fork --logpath ../testlog.txt

Running mongod v4.0.3 and OS X 10.13.6. Connecting with node v6.9.1 and mongoose v5.4.19.

After each test run, we nuke the data directory:

mongo admin --eval 'db.shutdownServer()' > /dev/null
rm -rf ./.test-data/*

I also tried setting --wiredTigerJournalCompressor none --wiredTigerCacheSizeGB 1.0 --wiredTigerCollectionBlockCompressor none --wiredTigerIndexPrefixCompression false and it didn't make a difference.


texts:
I have a CLR stored procedure which exports a large table into a text file.

The procedure works fine but. When I manually stop its execution from SQL Management Studio I cannot delete the file it created ().

  Error Deleting File or Folder Cannot delete XXXXXXXXXXX: It is being
  used by another person or program. Close any programs that might be
  using the file and try again.

I looked for a SQL process (with sp_who2 active) and for a Windows process (with tasklist) on the SQL Machine but I can't identify the process that keeps the file locked.

Please tell me how to kill the process (without restarting SQL server)


texts: why is blevel null for some indexes in Oracle DB?

texts:
I was trying to see the b-tree levels for indexes and to my surprise I see some of them are set to null.

SELECT index_name, blevel FROM user_indexes ORDER BY 2 desc;

Their name starts with "SYS_"(picture attached below). SO I am assuming they are system generated indexes? Maybe of primary keys? Either ways I am curious as to why they are null. I can understand if the value is 0 which mean everything is in leaf nodes and it will just take 1 hop for unique scan to get the data.


texts: Can I use pg_restore to restore data to subset of tables?

texts:
I am trying to restore data in to select tables of a database (9.3.5).  This is a data refresh from production to test, while maintaining some table data in the test database (like application users table).  I first truncate the tables that I plan to refresh and then I can successfully restore 1 table using this command:
pg_restore -a -d ACMETEST -n public -v -U acme -t hardware /var/acme/backups/restore/ACMEDatabase.bak
, which gives the following response followed immediately by a command prompt: !pg_restore: connecting to database for restore
pg_restore: processing data for table "hardware"
pg_restore: setting owner and privileges for TABLE DATA hardware

Queries show that the data successfully loaded.

The man page doesn't indicate that you can restore data in to multiple tables at once by using an array as an input to -t but I assumed it was possible so tried this:
pg_restore -a -d ACMETEST -n public -v -U acme -t hardware,location /var/acme/backups/restore/ACMEDatabase.bak
The result of the second command is one line that says:   and then it dumps me right back to the command prompt as if the command is complete but none of the data is loaded.

I did look at the -L option to use a listfile but I'm trying to create a repeatable process that can be used by many people on databases across the country so I don't see the process of commenting out tons of lines from a listfile for each restore as having realistic chances of success. Do I have to execute the first command for every individual table I want to restore or is there some way I can feed an array of tables to this command?


texts:
How would I get the names of all the columns in a table that are of a specific type such as datetime?

Even better -- How can I do the same thing but with multiple tables joined together and then list the column name along with the table it comes from?


texts: When I set Datestyle to 'SQL, YMD' all queries still return as 'SQL, MDY'

texts:
I am working on a database migration from Oracle 12c to Postgresql 12.
We must keep the same date format as the program interacting with this database is written in COBOL and should not be changed.
According to the documentation, set datestyle = 'SQL, YMD' should give me the result I need as the NLS_DATE_FORMAT on the Oracle side is set to 'YYYY/MM/DD HH24:MI:SS'.

The output should be 2020/10/15 15:02:39.710888 UTC as I have set datestyle to YMD.
Other formats seem to work as intended:

Is there a way for me to get the intended Datestyle?


texts: can i secure sql server agent job with password

texts: I want to secure SQL Server Agent jobs with password or any other method is it possible?


texts: How to configure global charset on MySQL

texts:
I tried to change the charset for our MySQL via my.cnf and it failed.  I added the charset settings listed below:

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# ------------- MYSQL CHARSETS --------------
character_set_system = utf8
character_set_server = utf8
character_set_results = utf8
character_set_database = utf8
character_set_connection = utf8
character_set_client = utf8

collation_connection = utf8_general_ci
collation_database = utf8_general_ci
collation_server = utf8_general_ci

When I had these enabled, MySQL failed to start.  How can I make sure everything defaults to utf8 - always?

Update

There are still a few places having issues in being UTF8

character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci

Unfortunately I still haven't come up with a soluton... any ideas?

Currently I'm using this configuration successfully:

[mysqld]
default-character-set = utf8
init-connect='SET NAMES utf8'
character-set-server = utf8

[mysql]
default-character-set = utf8

But PHPMyAdmin is still showing some latin1

character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci


texts: How to update data to Live sever with N duration time period

texts:
I have a Web application (MySQL and PHP), and I am handling the data from local database. Now I need to take a backup. So I desired to use two databases. One for local database (for entering data). Another one to backup.

Now the problem is to update the live server. Because I don't want to upload full database each and every time. I just need (e.g. for 1 hours only) within the particular time what type of operations are done (CRUD operations) only to be sent to my second server. I tried lots but nothing works.

Edit 1: I did a full database backup and uploaded to my second sever, but I need particular data because full database means large amount of data so it's a risky task and I don't what that.


texts:
We have a database that was accessed as user MARY on the old SQL Server. All the tables are:
MARY.<tablename>

The application assumes it can access tables by name only. E.g. select * from <tablename> rather than select * from MARY.<tablename>
We have a new userid that the software uses to access the database: APPSERVER.
When APPSERVER logs in, it has full rights to the DB, but can only access the tables as MARY.<tablename>.
So the code will not run.
How do I either:

get new user APPSERVER to magically map to MARY. to access tables
or

update all tables so they are now: APPSERVER.<tablename>.

(This is all via SQL native auth, not integrated.)


texts:
I need a drop a database which is a 2 node RAC configuration and I will also be drooping the disk groups as well completely. What is the right way to drop the database + diskgroups?

Will it be ok to shut down the database and then drop the diskgroups including contents thereby dropping the database as well? In this case, the cluster will still have entries of the database which is dropped?


texts: Delete rows from a table with inner join conditions

texts:
I have a query that selects data from a table based on some inner joins:

select * from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

The output of this contains the rows from table1 that I want to delete. So I tried this:

delete from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

But that is not a valid query and returns an error. How can I delete all the rows from table1 which are the result of the above SELECT statement?


texts: How do you GRANT SELECT on hidden resource database mssqlsystemresource?

texts:
I get an error when I run this query:

    SELECT
      dependencies.referencing_id as object_id,
      dependencies.referencing_minor_id as column_id,
      dependencies.referenced_id AS referenced_object_id,
      RTRIM(referenced_objects.type) AS referenced_type
    FROM
      sys.sql_expression_dependencies AS dependencies
    JOIN sys.objects AS referenced_objects WITH(NOLOCK) ON referenced_objects.object_id = dependencies.referenced_id
    WHERE dependencies.referencing_id != dependencies.referenced_id

Error:

The SELECT permission was denied on the object 'sql_expression_dependencies', database 'mssqlsystemresource', schema 'sys'.

The Login has membership only in the PUBLIC server role and granted VIEW ANY DATABASE and VIEW ANY DEFINITION.  I added a User with membership in the PUBLIC database role for the database I am comparing.  I assumed the server level permission would suffice, but I was wrong. I did this on a 2008R2 database in addition to a 2014 database.

On the 2014 database I also granted server level permission SELECT ALL USER SECURABLES.  The error persisted.

I granted the User SELECT on the database.  The error persisted.

I removed the SELECT permission and added the User to the data_reader database role.  The query resolved, however no data is returned (running the same query as a sysadmin returns 130+ rows).  Now I'm completely baffled.

I granted SELECT to the user on sys.sql_expression_dependencies  explicitly with the same results above.

After researching these permissions, the consensus seems to be that granting SELECT to a User on a database is the same as adding the User to the data_reader role.  But this is obviously not the case.  I want to create a Login/User with the smallest privilege set possible to achieve the comparison.

How do I grant access to the sys.sql_expression_dependencies view in mssqlsystemresource without granting data_reader access on the whole DB? Is there a way to grant SELECT on mssqlsystemresource only?

**Note - I've read a couple of other posts on SE regarding mssqlsystemresource but they don't pertain to this:

I have no DENY anything assigned to this Login/User
Membership in the PUBLIC role (server or database) has no bearing since the permissions are identical to those on a "known good" server.
I'd rather not have the user have blanket SELECT permissions on the entire database.


texts:
I am working in TOAD for Oracle and am trying to use variable substitution in my coding.

I have been able to get variable substitution to work in the code body pressing F5:

Define Value = '1.5'
Create table XX_DELETEMEEE as Select
percent * To_Number('&&Value') Test
from Source_Table;

I have been unable to make the variable work in the same way for text as table names or column names:

Define PROJECT = 'P747'
Create table XX_DELETEMEEE as Select
percent (To_CHAR('&&Project'))
from Source_Table;

The error for this code has 'percent' as an invalid identifier.

Define PROJECT = 'P747';
Create table &&Project||_MU as Select
percent
from Source_Table;

The error for this code has table name as an invalid option.

Any ideas on how to make this work while not straying to far in methodology?


texts: SQL - Best way to limit access to small number of tables

texts: I have a login I've created on SQL Server 2008, it's a SQL Server login not using AD. I want to give them access to every table in two databases except the tables that I store login information in for sites and such. This is one table in each database. Ideally, I'd like them to automatically get access to any new tables added to either database without breaking that restriction on those two login info tables.


texts: Get wall_post and profile data for several users

texts:
I'm trying to build a query for fetch wall_posts and profile fields for several user_id. First see the images at: http://www.dropmocks.com/mBjQ7P. The table with id_user_rq, id_user_ap is the table where I handle the relations. Meaning 1 and 2 are friends and 1 and 3 are friends too because the directionality is 1,2 | 2,1 and 1,3 | 3,1. So what I need here is to get the wall_post for users 1,2,3 and also the profile data for the same users. Wall_posts is image #2 and profile is the first image. I've tried this query:

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid)
    AND (default_wall_posts.userid = 2 OR default_wall_posts.userid = 3)
WHERE
    default_wall_posts.userid = 1 AND
    (
        default_wall_posts.userid = 2 AND
        default_wall_posts.userid = 3
    )
ORDER BY
    default_wall_posts.date_created DESC;

But it's not working because I only get the wall_posts and profile fields for user 1. What I'm doing wrong?

Cheers and thanks in advance


texts:
I want to know if sql server (express) need to have always admin right on a computer.

I ask my company to have a version on my pc, but I need a tech to install it because I dont have admin right.

I'm afraid that I will not be able to use it after the installation.

I will need of course to manage it

and connect local

and connect remote lan

did you think my right will cause problem

(I know taht I can talk to a mysql db on my computer by lan)

I found this http://www.symantec.com/connect/articles/install-and-configure-sql-server-2008-express and its said that I will need to enable TCP/IP, is it that I can do it? did I need it on lan?


texts: Is it possible to edit a partition function on MSSQL 2016?

texts:
I've inherited a partitioned database with millions of rows and I've noticed there is a typo in the partition function:
CREATE PARTITION FUNCTION [Partition_by_customer](varchar(10)) AS RANGE RIGHT FOR VALUES (N'DMA', N'DMB', N'DMC', N'DMD', N'DME')

Is there an easy way to remove those unicode converters (as they are causing implicit conversions), everything should be a varchar(10)
Thanks
Roger


texts: How to move data from Primary Data File to other data file without destroying replication

texts:
I have one Database which is replicated using transactional replication. Now, I want do spare some data of that database to another partition, because my server running out of disk space.

I was trying to move file to another filegroup trough SSMS using GUI tolls by clicking design at table and at properties tab changing "Regular Data Space Specification" to "secondary". When I try to save desing of table I get error Cannot drop the table 'dbo.MYTABLE' because it is being used for replication.

Here are a few questions:

Should I create data files at another partition in already existing filegroup "PRIMARY"
or it is better to I open new filegroup and than in that new "secondary" filegroup to open new datafile?
Is there other way to I tell SQL to save data to another file besides dropping the table?
Is there way to I can temporarily exclude some tables from replication while I move them to another filegroup and then put them again in snapshot without having to reinitialize the subscriber ?


texts: Linking job steps from a single run together in sysjobhistory

texts:
We have several SQL Servers running SQL Agent jobs, recording history in their own sysjobhistory tables. I am attempting to set up one centralized server with a job that collects the history from all the other servers, formats it a bit, and puts it into a table called AllJobHistory. As part of this process, I would like one column to indicate that multiple steps of a job were part of the same job run. They are already marked as being part of the same job through the job_id column, but I want to know that specific rows came from the 3:00 run of the job versus the 4:00 run. Being able to filter based on this column would make troubleshooting jobs that much easier for us, but I don’t see anything that links those steps together in any existing system table or DMV, is there?

My first attempt at rolling my own was to use the run_date, run_time, and run_duration columns. For each step, if I subtract the total run_duration up to this point from the run_time, it should bring me back to a time that is unique compared to all other runs of this job. This looked like it was working until I found a case where it wasn’t (likely because SQL Server is rounding run_time and run_duration at the precision of seconds). Here is my attempt at the query (with extra columns removed).

WITH JobDetails AS
(
    SELECT
        QUOTENAME(UPPER('ServerName')) AS [Server],
        j.job_id AS [JobID],
        j.name AS [JobName],
        s.step_id AS [Step],
        msdb.dbo.agent_datetime(run_date, run_time) AS [RunDate],
        (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100) AS [RunDurationSeconds]
    FROM msdb.dbo.sysjobhistory h
    INNER JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
    LEFT OUTER JOIN msdb.dbo.sysjobsteps s ON s.job_id = h.job_id AND s.step_id = h.step_id
    WHERE h.step_id != 0
), GroupedDetails AS (
    SELECT
        jd.[Server],
        jd.[JobID],
        jd.JobName,
        jd.Step,
        jd.RunDate,
        jd.RunDurationSeconds,
        DATEADD(SECOND,
            -ISNULL(SUM(jd.RunDurationSeconds) OVER
                (PARTITION BY jd.JobName ORDER BY jd.JobName, jd.RunDate, jd.Step
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0),
            jd.RunDate) AS grp
    FROM JobDetails AS  jd
)
SELECT
    gd.[Server],
    gd.JobName,
    gd.Step,
    gd.RunDate,
    gd.RunDurationSeconds,
    CONVERT(VARCHAR(36), gd.JobID) + '_' + FORMAT(gd.grp, 'yyyyMMdd_HHmmss') AS JobRunString
FROM GroupedDetails AS gd;

Here is an example where it worked as I wanted it to, for a job that has three steps. Note that the JobRunString matches for the first run and again for the second run.

And here is an example where it did not work as I wanted it to. Note that Step1RunDate + Step1RunDurationSeconds != Step2RunDate, causing a mismatch of JobRunString.

So, is there any reliable way of linking the steps of a job run together in sysjobhistory?


texts: Production server Log file full due to replication

texts:
I'm getting the error below, log file full due to replication
I have hot dbcc sqlperf(logspace) it is showing 100 and it is in Simple recovery model.
I am not able to shrink it.
I am not able to add space.
I am change the recovery model to Full.

  Error: The transaction log for database XXXXX is full due to
  'REPLICATION'.

I have tried all the possible ways please help, in this regards.


texts: SQL Server Database errors 8935/8934/8977

texts:
I received a database from a customer, that i have to try to recover.
I'm facing some problems, specifically with the following errors:

(Error, Level, State, MessageText, RepairLevel, Status, DbId, DbFragId, ObjectId, IndexId, PartitionId, AllocUnitId, RidDbId, RidPruId, File, Page, Slot, RefDbId, RefPruId, RefFile, RefPage, RefSlot, Allocation) VALUES
(8935, 16, 1, N'Table error: Object ID 41, index ID 1, partition ID 281474979397632, alloc unit ID 281474979397632 (type In-row data). The previous link (1:9273) on page (1:5860) does not match the previous page (1:14296) that the parent (1:7132), slot 170 expects for this page.', N'repair_rebuild', 0, 30, 1, 41, 1, 281474979397632, 281474979397632, 30, 0, 1, 5860, 0, 30, 0, 1, 7132, 170, 1),
(8977, 16, 1, N'Table error: Object ID 41, index ID 1, partition ID 281474979397632, alloc unit ID 281474979397632 (type In-row data). Parent node for page (1:9273) was not encountered.', N'repair_rebuild', 0, 30, 1, 41, 1, 281474979397632, 281474979397632, 30, 0, 1, 9273, 0, 30, 0, 1, 9274, 0, 1),
(8977, 16, 1, N'Table error: Object ID 41, index ID 1, partition ID 281474979397632, alloc unit ID 281474979397632 (type In-row data). Parent node for page (1:9274) was not encountered.', N'repair_rebuild', 0, 30, 1, 41, 1, 281474979397632, 281474979397632, 30, 0, 1, 9274, 0, 30, 0, 1, 14296, 0, 1),
(8934, 16, 1, N'Table error: Object ID 41, index ID 1, partition ID 281474979397632, alloc unit ID 281474979397632 (type In-row data). The high key value on page (1:14296) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:9274).', N'repair_rebuild', 0, 30, 1, 41, 1, 281474979397632, 281474979397632, 30, 0, 1, 14296, 0, 30, 0, 1, 9274, 0, 1)

I already used a lot of configurations of DBCC commands but without success.

ALTER DATABASE [PRI0492004] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ([PRI0492004], REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS;
GO
ALTER DATABASE [PRI0492004] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE [PRI0492004] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC Checkdb([PRI0492004], REPAIR_rebuild)
go
ALTER DATABASE [PRI0492004] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

Any comment about what i can do to get free of these errors?


texts: Sql Server Stored Procedure calling table-valued function - Performance

texts:
I'm using Sql Server 2012 and I have a stored procedure that does about 15 left joins and one of those joins is a CROSS APPLY to a table-valued function. The first 14 left joins are not gonna restrict the results (they are left join so no rows will be ruled out) but the function could restrict records as it is a CROSS APPLY. Normally I would think that taking the CROSS APPLY to the top of the JOINs would be make it faster (we could deal with a small number of records sooner) but that's not real, at least in this case. If I take the CROSS APPLY to the top, the SP gets really, really slow. Why could this happen?

In order to give more context, the function is used to limit records based on entity permissions. Also, it is getting data from some tables that are also part of the joins I have in the SP. So, my first guess is that the JOINs in the SP are executed all together and the function is getting the data from the cache instead of disk. That could make sense but I'm just guessing.

I could do the JOINs that I have in the function in the SP but the problem is that security is managed by specifying what you can do so you could have 1 or more rules that tells you that you can see a record. Because of that, doing an INNER JOIN could cause duplicates so I could only convert the function to an EXISTS clause. The downside of this approach is that I cannot reuse the security function anymore (for the moment I'm not using from somewhere else).

I hope it's clear enough so somebody can help me :)


texts: Connect MySQL workbench with MySQL container

texts:
I pulled MySQL container from Dockerhub and it is running on my macbook pro as you can see as follow

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
12cd3deaa3f0        mysql:latest        "/entrypoint.sh mysql"   47 hours ago        Up 10 minutes       3306/tcp            mysqldb

Then I downloaded the MySQL workbench and I tried to connect it with MySQL container, but it does not work. It shows connection error.

Your connection attempt failed for user 'root' from your host to server at 192.168.99.102:3306:
Can't connect to MySQL server on '192.168.99.102' (60)

The IP from docker machine is

docker-machine ip default
192.168.99.100

and the port from MySQL is 3306 as you can see above.

To export MySQL port, I tried with the statement

docker run -d -P mysql:latest mysqldb

It does not work at all. What am I doing wrong?


texts:
I have 2 VPS.

I would like to connect from the frist to the second's mysql db like this :

$db = mysql_connect("94.xxx.xxx.xxx:222", "user", "pass");

I got this  error:

  Warning: mysql_connect(): Lost connection to MySQL server at 'reading
  initial communication packet', system error: 111 in
  /var/www/pcsart/test.php on line 4

My server is works on first server is works on port 222.
In the first server I don't have a mysql.
On the second is running on port 3306

First and second server's firewall got these rules :

iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:222
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:222
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:222

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:222
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql

What i'm doing wrong?