texts: How does a transaction log backup deal with active log?

texts: What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup?


texts: Creating a test database from Production database (different machines & servers)

texts:
I'd like to set up a test database on a separate server. Production database is MS SQL Server and the test server is a *nix box so I'd like to have the test server running either PostgreSQL or MySQL.

The dataset is pretty large; some of the tables have 30k+ rows and there are a lot of tables.

Some of the tables don't change much at all, and many of the tables can be empty for the purposes of testing, so ideally some kind of solution that can copy some but not all of the records from a database would be ideal.

Barring that, I may just make a test copy of the production database on the same server rather than putting the test db server on the development server. Are there potential problems with hosting the testing database on the same server as the production server, assuming that the load on the testing database will be relatively minor?

And if I am copying from production -> test within MS SQL Server, is there some kind of automation script I can set up that selectively copies only some of the records (for example, only the last 1k rows from some tables, none from others, and all rows from a selected few)? Is there a way to set this up as a selective backup, and then use that backup to generate the test database?

Additional info

The thing being tested here is just the code, not the database or even the interface into the database. The code (Django) uses an ORM so it doesn't matter which RDBMS is being used. I'm comfortable with assuming that if I call .save() it will work regardless of the database; my concern is whether I have the forms, utility functions, data imports, etc. set up correctly and all of these are abstracted through Django's relational model system which has been shown to be completely indifferent to RDBMS (indeed, in the production version of one of my sites I am pulling from both a MySQL and a SQL Server database at the same time (one contains legacy data).


texts: Release MySQL Memory and Performance Tunning

texts:
I am new to the MySQL database. I have recently installed MySQL 5.7 and configured the my.ini file. Please find the below copy of the file.

The server is a dedicated server for MySQL database and the configuration of the server is:

OS = Windows 8.1 (64 bit),
RAM = 16GB (this can be automatically extended up to 32GB),
Processor = Intel Xenon 2.30 GHz (4 processors),
Size of the MySQL database = approx. 100GB,
Concurrent connections = Maximum up to 100

My questions are:

I have initially allocated 12GB RAM to Innodb buffer pool. I can allocate up to 24GB to Innodb buffer pool, so that, MySQL can use extra memory in case of heavy load. I want MySQL to release extra memory after completion of query executions/processes. Is there any way we can do it?
Do we need to make any changes in the my.ini file if any variable(s) are configured wrongly and also to improve the performance of the database?

my.ini file:

character-set-server=utf8,
default-storage-engine=INNODB,
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION",
max_connections=301,
query_cache_size=0,
table_open_cache=2000,
tmp_table_size=333M,
thread_cache_size=10,
myisam_max_sort_file_size=100G,
myisam_sort_buffer_size=4G,
key_buffer_size=8M,
read_buffer_size=64K,
read_rnd_buffer_size=256K,
innodb_flush_log_at_trx_commit=1,
innodb_log_buffer_size=16M,
innodb_buffer_pool_size=12G,
innodb_io_capacity = 2000,
innodb_read_io_threads = 64,
innodb_log_file_size=128M,
innodb_thread_concurrency=0,
innodb_write_io_threads = 64,
innodb_autoextend_increment=64,
innodb_buffer_pool_instances=8,
innodb_concurrency_tickets=5000,
innodb_old_blocks_time=1000,
innodb_open_files=300,
innodb_stats_on_metadata=0,
innodb_file_per_table=1,
innodb_checksum_algorithm=0,
back_log=80,
flush_time=0,
join_buffer_size=256K,
max_allowed_packet=4M,
max_connect_errors=100,
open_files_limit=4161,
query_cache_type=0,
sort_buffer_size=256K,
table_definition_cache=1400,
binlog_row_event_max_size=8K,
sync_master_info=10000,
sync_relay_log=10000,
sync_relay_log_info=10000,
explicit_defaults_for_timestamp=1,
innodb_lock_wait_timeout=120,
transaction-isolation=READ-COMMITTED,
innodb_support_xa=0,
autocommit=1,
event_scheduler=ON

Update:

We are on the Cloud Network. The default RAM on the server is 16 GB. There is a facility to increase (up to 32 GB RAM) or decrease the RAM automatically based on OS load.

For example, if I allocate 20GB RAM to INNODB_BUFFER_POOL_SIZE then MySQL server can use the memory up to 20GB during the heavy load/pick hours. However, I want MySQL to release the memory up to 16GB during the non-pick hours. So that, we cannot charged for 20GB RAM (i.e. for extra 4GB RAM during non-pick hours).


texts:
Oracle 12cR2.
Error:

channel default: deleting archived log(s)
archived log file name=+RECO/.../thread_1_seq_166200.26582.1054942673
RECID=62 STAMP=1054943183
archived log file name=+RECO/.../thread_1_seq_166201.11529.1054941077
thread=1 sequence=166201
channel default: deleting archived log(s)
archived log file name=+RECO/.../thread_2_seq_223357.10403.1054942671
RECID=58 STAMP=1054943182
unable to find archived log
archived log thread=2 sequence=223358
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get
error below
ORA-01194: file 4 needs more recovery to be consistent
ORA-01110: data file 4: '+DATA/.../data_d...fno-4'
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================
RMAN-03002: failure of recover command at 10/28/2020 00:24:13
RMAN-06054: media recovery requesting unknown archived log for thread
2 with sequence 223358 and starting SCN of 1871183322646

My commands:
restore primary controlfile from '/.../RESTORE/..._controlfile_20201027_....rman';

alter database mount;

restore database preview;

The final output part of 'restore database preview;' command:

recovery will be done up to SCN 1871164140237
Media recovery start SCN is 1871164140237
Recovery must be done beyond SCN 1871183871662 to clear datafile fuzziness
Finished restore at 27-OCT-20

catalog start with '/.../RESTORE/';

run
{
set newname for database to '+DATA/.../%U' ;
set newname for tempfile 1 to '+DATA/.../%U';
set newname for tempfile 2 to '+DATA/.../%U';
set newname for tempfile 3 to '+DATA/.../%U';
set newname for tempfile 4 to '+DATA/.../%U';
set newname for tempfile 5 to '+DATA/.../%U';
set until scn 1871183871663;
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

NOTE: all of the full database backuppieces, archivelog backups, daily spfile backup and daily controlfile backup are under this path '/.../RESTORE/'.
I setted my limit to scn 1871183871663 (Recovery must be done beyond SCN 1871183871662 to clear datafile fuzziness + 1) but it says "media recovery requesting unknown archived log for thread 2 with sequence 223358 and starting SCN of 1871183322646."
I couldn't find any clear solution. How can I resolve it?
Best regards,


texts:
I am designing a web application that will have probably around ~1000 users.  The website is facing the internet but it can only be used by partners (i.e. the general public cannot just "sign up an account").  Account maintenance will be handled by our own IT support.

I am considering having each individual user to have their own SQL login associated.  This way I do not need to store their salted/hashed password - SQL server will handle the authentication.  This also allow us to use SQL Server based audit and logging tool.

Is there any disadvantage doing that?


texts: Create a foreign key to a table that doesn't exist in postgres

texts:
I'm trying to put together a solution where I have a bunch of script files that contain my schema creation. Something similar to Visual Studio's SQL Project, but for Postgres.

My problem is that the files won't necessarily be read in the correct order, for instance the first file/script/table read has a dependency on the 2nd file/script/table.

I was hoping there was some way I could disable the check to see if the referred table/column does exist, creating all the tables, and then re-enabling the check.

I'm running 9.2.24

My 2nd solution would be to order the files in such a way that it does only create a table when the dependencies already exist, but my above mention method would be preferred.


texts: Does the function inside the Select clause evaluate the passed in columns before the where clause?

texts:
I have a simple select statement that uses a (not so) simple function. I'll try to simplify and demonstrate to the best of my abilities for purposes of this question.

            select submission_identifier,
                   submitted_by_person_id,
                   submission_date,
                   form_version_date_time,
                   form_id,
                   func_get_open_days_2(
                     submitted_by_person_id,
                     submission_date,
                     form_version_date_time,
                     form_id)` open_days
            from form_submission
            where submission_identifier = 2070;

submission_identifier is the PK for the form_submission table, so naturally, this query returns one result.

As you can see, the function takes 4 parameters. I'm assuming the values in those parameters are the results of the query AFTER the where clause has been evaluated. Here is a snippet of the function.

create or replace function        func_get_Open_Days_2
   ( SUBMITTED_BY_PERSON_ID in number,
  SUBMISSION_DATE in date,
  FORM_VERSION_DATE_TIME in timestamp,
  FORM_ID in number
   )
RETURN number

IS
   n1 number:=0;
   n2 number:=0;

   cursor records is
      SELECT
        SUBMITTED_BY_PERSON_ID,
        SUBMISSION_DATE,
        FORM_ID,
        FORM_VERSION_DATE_TIME,
        AUDIT_DATE_TIME,
        STATUS_DATE,
        SUBMISSION_STATUS_CODE,
        CREATE_USER_ID
      from form_state_audit_trail t
      where
        t.submitted_by_person_id = SUBMITTED_BY_PERSON_ID and
        t.form_id = FORM_ID and
        t.submission_date = SUBMISSION_DATE and
        t.form_version_date_time = FORM_VERSION_DATE_TIME and
        t.submission_status_code NOT IN (3469, 3462);
        order by t.status_date asc;

   rec records%ROWTYPE;

BEGIN

dbms_output.put_line('submitted by person id ='||submitted_by_person_id);
dbms_output.put_line('form id ='||form_id);
dbms_output.put_line('submission date ='||submission_date);
dbms_output.put_line('form_version_date_time ='||form_version_date_time);

  -- go through all the durations in audit_trail table
  FOR rec in records
  LOOP

I have tried hard-coding the return values and putting them in this cursor select query. I receive 1 result.

This suggests to me that the "records" array or list should only be 1 result long. However, I am getting numerous results as it stands now. "records" are being returned which suggest the where clause is not being evaluated using the 4 values/results I'd expect I'm not sure what I'm doing wrong. The the "dbms_output.put_line" are the BEGIN but before the LOOP confirm the expected values are reaching the function.


texts: Why Postgresql 9.2 word boundary not working!

texts:
In my postgresql version 9.1 this query is working fine:

select id,name from flash_card_set where name ~* '\yACT\y'

and matching:
text ACT txt,ACT tests,tests ACT
But in my postgresql version 9.2 it is not matching any of them.Am I missing something!


texts: Unusual column comparisons and query performance

texts:
We have some consultants working on expanding an inhouse data warehouse.  I was doing a code review and ran across this pattern in all of the load procs:

    MERGE [EDHub].[Customer].[Class] AS TARGET
    USING (
        SELECT <columns>
        FROM [dbo].[vw_CustomerClass]
            WHERE JHAPostingDate = @PostingDate
        ) AS SOURCE
        ON  TARGET.BankId = SOURCE.BankId       -- This join is on the business keys
            AND TARGET.Code = SOURCE.Code
    WHEN NOT MATCHED BY TARGET
        THEN
            <INSERT Statement>
    WHEN MATCHED
        AND TARGET.IsLatest = 1
        AND EXISTS (
            SELECT SOURCE.[HASH]
            EXCEPT
            SELECT TARGET.[Hash]
            )
        THEN
            <UPDATE Statement>

The gist is, if we have a new business key, insert but if the business key exists and the hash of the attributes don't match our current row then  update the old row and insert a new one (later in the code).  It all works fine but I paused when I got to this code

AND EXISTS (
            SELECT SOURCE.[HASH]
            EXCEPT
            SELECT TARGET.[Hash]
            )

It seems overly complicated compared to SOURCE.[HASH] <> TARGET.[Hash].  The EXCEPT will do an accurate NULL comparison but in our case hashes will never be NULL (or we have bigger problems).  I want our code to be easy to read so that when someone has to maintain it, it doesn't confuse.  I asked our consultants about it and they speculated that it might be faster because of set operations but I decided to write a simple test (test code below).

The first thing I noticed was the EXISTS/EXCEPT had a more complicated query plan but that's not always bad

I ran each select client statistics on and the <> join yielded total execution time of 12,000 vs 25,000 with the EXISTS/EXCEPT.  I want to take this to our consultants with the request to refactor that statement but wanted to get feedback here on:

is this a good test? - am I missing anything?
is there a case where EXISTS/EXCEPT would be a better comparison?

Test script:

CREATE TABLE r (hash VARBINARY(8000))
CREATE TABLE l (hash VARBINARY(8000))

SET NOCOUNT ON
DECLARE @x INT = 10000
WHILE @x <> 0 BEGIN

   INSERT INTO dbo.r ( hash ) SELECT HASHBYTES('SHA2_256',CAST(NEWID() AS VARCHAR(200)))
   INSERT INTO dbo.l ( hash ) SELECT HASHBYTES('SHA2_256',CAST(NEWID() AS VARCHAR(200)))

   SET @x = @x-1
END

INSERT INTO dbo.r ( hash ) VALUES ( NULL  )
INSERT INTO dbo.l ( hash ) VALUES ( NULL  )

SELECT COUNT(1)
FROM dbo.l
CROSS JOIN dbo.r
WHERE ISNULL(r.hash,0) <> ISNULL(l.hash,0)

SELECT COUNT(1)
FROM dbo.l
CROSS JOIN dbo.r
WHERE EXISTS(SELECT r.hash except select l.HASH)


texts:
I have oracle 11.2.0 installed on linux. And I have arcgis server 10.2 on the same machine. I created an geodatabase. But when i try the sde functions like sde.st_asText i got:

-ORA-28575: unable to open RPC connection to external procedure agent

-ORA-06512: at SDE.ST_GEOMETRY_SHAPELIB_PKG

-ORA-06512: at SDE.ST_GEOMETRY_OPERATORS

I googled it and figured it out that there is some changes to be made in the listener.ora and tnsnames.ora files. So i did.

here is the changes that i made in the listener.ora file:

LISTENER=(DESCRIPTION = (ADDRESS=(PROTOCOL= TCP) (HOST= xxx.xxx.xxx.xxx)(PORT=1525)(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))

(SID_DESC=
(SID_NAME =PLSExtProc)
(ORACLE_HOME= /mypath/dbhome_1)(PROGRAM =extproc)(ENV="EXTPROC_DLLS=/mypath/libst_shapelib.so"))

After restarting the listener. I tried the same query above. But the error remained same. So i checked the listeners status with lsnrctl - status:

Service "PLSExtProc" has 1 instance(s)
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service..

I searched for reason for UNKNOWN status part. And i figured out that the listener should not be added by manual intervention on listener.ora file. It should be added dynamically with srvctl addlistener command.

Here is the command that i am going to run:

srvctl add listener -o /mypath/dbhome_1 -1 "TCP:1522/IPC:extproc"

Before trying this, i have couple of questions:

Shall i remove both the changes that i made. or just first one. Because as i understand, the command shall create the first part. Do i have to write another command or just left it as it is.

LISTENER=(DESCRIPTION = (ADDRESS=(PROTOCOL= TCP) (HOST= xxx.xxx.xxx.xxx)(PORT=1525)(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))

(SID_DESC=
(SID_NAME =PLSExtProc)
(ORACLE_HOME= /mypath/dbhome_1)(PROGRAM =extproc)(ENV="EXTPROC_DLLS=/mypath/libst_shapelib.so"))

In oracle documantation for 11.2.0  it says; for the external libraries configuration in the extproc.ora file is enough. But i tried it it does not work and give the same error why is that.

Thank you.


texts: Return only one row from table with similar values in column that match a criteria

texts:
I want to run a query from a table with similar values in the same column that match a certain criteria, but only return 1 row per similar value.

Let's say I have this simplified table:

Column1   Column2
  A           1
  A           2
  A           3
  A           4
  B           1
  B           2
  C           1
  C           3
  C           4
  D           2

I want to say, for example, return when Column 2 is not 3, and the result should be:

 B
 D


texts: Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY?

texts:
In my MySQL database I have a table with 4 columns, the PRIMRAY KEY, two columns with a FOREIGN KEY CONSTRAINT and one UNIQUE  index on all fields except for the PRIMARY KEY.

The SHOW CREATE TABLE:

CREATE TABLE `zdb_userbeschikbaarheid` (
  `UserBeschikbaarheid_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `UB_User_ID` int(10) unsigned NOT NULL,
  `UB_PlanDagdeelTaak_ID` int(10) unsigned NOT NULL,
  `UB_Datum` date NOT NULL,
  PRIMARY KEY (`UserBeschikbaarheid_ID`),
  UNIQUE KEY `UQ_UB_User_ID_PlanDagdeelTaak_ID_Datum` (`UB_Datum`,`UB_User_ID`,`UB_PlanDagdeelTaak_ID`),
  KEY `FK_UB_User_ID` (`UB_User_ID`),
  KEY `FK_UB_PlanDagdeelTaak_ID` (`UB_PlanDagdeelTaak_ID`),
  CONSTRAINT `FK_UB_PlanDagdeelTaak_ID` FOREIGN KEY (`UB_PlanDagdeelTaak_ID`) REFERENCES `zdb_plandagdeeltaak` (`PlanDagdeelTaak_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_UB_User_ID` FOREIGN KEY (`UB_User_ID`) REFERENCES `zdb_user` (`User_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=522 DEFAULT CHARSET=utf8

For some weird reason when I select the full table it's using the UNIQUE index instead of the PRIMARY KEY. An EXPLAIN SELECT * FROM zdb_userbeschikbaarheid; yields:

id  select_type table                   type    possible_keys
1   SIMPLE      zdb_userbeschikbaarheid index   \N

key                                     key_len ref rows    Extra
UQ_UB_User_ID_PlanDagdeelTaak_ID_Datum  11      \N  415     Using index

It will not use the PRIMARY KEY until I specifically tell it to ORDER BY the primary key.
EXPLAIN SELECT * FROM zdb_userbeschikbaarheid ORDER BY UserBeschikbaarheid_ID;

id  select_type table                   type    possible_keys
1   SIMPLE      zdb_userbeschikbaarheid index   \N

key     key_len ref rows    Extra
PRIMARY 4       \N  415     \N

It just seems odd that the UNIQUE KEY is chosen instead of the PRIMARY KEY.

What's causing this behavior, is it a potential problem for my database? And if possible, how can I change it to use the PRIMARY KEY by default?


texts: pg_hba.conf allow mutliple I.P. addresses for single user

texts:
In my pg_hba.conf, I need a single user to be allowed to connect from two completely different IP addresses (like work and home, for example).

Something like the following would be really nice:

host    all    my_awesome_user    151.123.45.67/0, 72.11.12.13/0    md5

Putting adding two entries (one for each IP doesn't work: postgres just sees the first entry, which ether matches or doesn't and completely ignores the second entry.

Any help would be appreciated.


texts: Avoid Sql Conflicts When Inserting data At Same Time

texts:
Let suppose I have a query that runs multiple times because it is in a while loop which inserts data into a table.

INSERT INTO <table>( psuiKey, group_id) VALUES ('54ds8a784daEd','1');

The problem is when two users from different computers add data at same time. So what is happening is user 1 adds data to the table to looks like this:

psuiKey       group_id
54ds8a784daEd  1
54ds8a785daEd  1

and at the same time user 2 adds data where group_id is 2, but the data looks like this:

psuiKey       group_id
54ds8a784daEd  1
54ds8a785daEd  1
54ds8a786daEd  1
54ds8a787daEd  1

instead of:

psuiKey       group_id
54ds8a784daEd  1
54ds8a785daEd  1
54ds8a786daEd  2
54ds8a787daEd  2

So in short if two users add the same data at same time so group_id is not changing when I find max group_id using:

SELECT MAX(group_id) + 1

So SQL can't able to update it when two users add data at the same time.

Note I cant use group_id as primary key and I also use datetime.

So any help would be helpful!


texts: Is it possible to sync a Pervasive SQL database to a PostgreSQL database, and vice-versa?

texts:
I was wondering if it is possible to sync a Pervasive SQL database to a PostgreSQL database.

If I set this up I would need the ability to sync both ways either on command or via a schedule.

Does anyone know if this is even remotely possible?


texts: How to "update" change_tracking setting to enable TRACK_COLUMNS_UPDATED

texts:
I have a table where I have enabled change tracking without TRACK_COLUMNS_UPDATED. I know this because if I go through SSMS's Generate scripts, I get the line:

ALTER TABLE [dbo].[dt] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = OFF)

I want to enable TRACK_COLUMNS_UPDATED. If I run:

ALTER TABLE dt
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

I get error Msg 4996, Level 16, State 1, Line 19
Change tracking is already enabled for table 'dt'.

Do I have to disable change_tracking first? If so, is this explicitly documented somewhere?


texts: Extract Date Portion Only From DateTime Field

texts:
In SQL Server 2008 I use this to take out only the date piece Cast(field as date).  For example, if I use the below garbage DDL

  Create Table #Bbb
  (
    id int
    ,datetime1 datetime
  )

  Insert Into #Bbb Values
  ('1', '2016-03-01 17:35:19.000')
  ,('2','2016-03-01 18:44:15.000')
  ,('3','2016-03-05 18:58:05.000')
  ,('4','2016-03-05 19:52:27.000')

I run this query it will only give me the 2 rows where the date is equal to 03/01/2016:

Select *
FROM #Bbb
WHERE CAST(datetime1 As Date) = '2016-03-01'

Now the question being, how would I do the same in a SQL Server 2000 instance, since Cast(datetime1 As Date) does not exist?


texts: Find the second highest value from a subquery or derived table

texts:
I have a subquery which returns the following results.

'c003', '120000.00'
'c002', '90000.00'
'c001', '20000.00'
'c005', '8000.00'
'c004', '5000.00'

I want to find the second highest value from this. How can it be done?

The subquery is :

SELECT SUM(coursefees)
FROM enrollment e INNER JOIN batch b INNER JOIN course c
ON e.batchid = b.batchid AND b.courseid = c.courseid
GROUP BY c.courseid;


texts: SSMS 2014 working with SQL Server 2005 instances

texts:
Is there any major complication that one could run into while using the latest (developer) version of SSMS with older SQL Server 2005 instances?

Any way one could affect the data by using this version?


texts: How to modify an existing AlwaysOn Availability Group IP

texts:
I have an unusual question regarding AlwaysOn Availability Group configuration.

I have built out an AOAG cluster on a VM. The build out did not use a Heartbeat network as I had originally requested from our SysOps team. When I did the POC for this HA solution, I tested everything in failing over except what is most important, a true ESX host failure.

I have since proven that the need for the Heartbeat network was necessary and our VM team has built this out with a new IP range.

My question now is what do I need to do to change the IP's for SQL Server to utilize this network? Do I need to uninstall and reinstall SQL Server and configure the AOAG again?


texts: Creating index on computed field: string or binary data would be truncated

texts:
I have a table Foo with the following fields:

ID bigint not null identity(1,1),
SerializedValue nvarchar(max),
LongValue as TRY_CAST(SerializedValue as bigint)

Now I want to create an index on LongValue, so that I can easily look up on serialized values that represent numbers.

create nonclustered index IX_Foo on Foo(LongValue);

Which spits out the following error at me:

  String or binary data would be truncated.

Yes, there is existing data in SerializedValue.  But what, pray, can be truncated by creating an index on a computed field?


texts: MariaDB starts from cmd prompt but not as service

texts:
Attempting to start MariaDB as a service results in an error that says:

Windows could not start the MariaDB service on Local Computer
Error 1053: The service did not respond to the start or control
request in a timely fashion.

The error log does not show any errors after that start attempt but the server is not started. The error log looks completely normal and contains:
InnoDB: using atomic writes.
2021-02-17  7:05:16 0 [Note] InnoDB: Uses event mutexes
2021-02-17  7:05:16 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-02-17  7:05:16 0 [Note] InnoDB: Number of pools: 1
2021-02-17  7:05:16 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-02-17  7:05:16 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2021-02-17  7:05:16 0 [Note] InnoDB: Completed initialization of buffer pool
2021-02-17  7:05:16 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1051746172,1051746172
2021-02-17  7:05:16 0 [Note] InnoDB: 128 rollback segments are active.
2021-02-17  7:05:16 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-02-17  7:05:16 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-02-17  7:05:16 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-02-17  7:05:16 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2021-02-17  7:05:16 0 [Note] InnoDB: 10.5.8 started; log sequence number 1051746283; transaction id 199153
2021-02-17  7:05:16 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-02-17  7:05:16 0 [Note] InnoDB: Loading buffer pool(s) from c:\ZZ-MariaDB_Data\ib_buffer_pool
2021-02-17  7:05:16 0 [Note] Server socket created on IP: '::'.
2021-02-17  7:05:16 0 [Note] Reading of all Master_info entries succeeded
2021-02-17  7:05:16 0 [Note] Added new Master_info '' to hash table
2021-02-17  7:05:16 0 [Note] c:/mariadb/bin/mysqld: ready for connections.
Version: '10.5.8-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution
2021-02-17  7:05:17 0 [Note] InnoDB: Buffer pool(s) load completed at 210217  7:05:17

The INI file being used contains:
[mysqld]
datadir=c:/ZZ-MariaDB_Data
character-set-server=utf8mb4
collation_server = 'utf8mb4_unicode_ci'
default-storage-engine=InnoDB
[client]
plugin-dir=c:/MariaDB/lib/plugin

The Path to executable setting in Services is c:/mariadb/bin/mysqld --defaults-file=C:/ZZ-MariaDB_Data/my.ini.
If I run that from a command prompt the server starts properly and everything works as expected. Things only fail when I try to start it from Services.


texts:
Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a better memory caching solution (such as memcache.d).
But they also mentioned that there are a few cases in which query_cache is helpful. So a general recommendation would be to have it enabled to on-demand (using SELECT SQL_CACHE, with a query_cache_type = 2 config setting).
My question is, assuming you've got a caching solution like memcache.d in place, what type of circumstances would make the query_cache more optimal?
Edit: added link


texts:
I've got a datavalue table with ~200M rows or so, with indices on both site_id and paramter_id. I need to execute queries like "return all sites with data" and "return all parameters with data". The site table has only 200 rows or so, and the parameter table has only 100 or so rows.

The site query is fast and uses the index:

EXPLAIN ANALYZE
select *
from site
where exists (
      select 1 from datavalue
      where datavalue.site_id = site.id limit 1
);

Seq Scan on site  (cost=0.00..64.47 rows=64 width=113) (actual time=0.046..1.106 rows=89 loops=1)
  Filter: (SubPlan 1)
  Rows Removed by Filter: 39
  SubPlan 1
    ->  Limit  (cost=0.44..0.47 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=128)
          ->  Index Only Scan using ix_datavalue_site_id on datavalue  (cost=0.44..8142.71 rows=248930 width=0) (actual time=0.008..0.008 rows=1 loops=128)
                Index Cond: (site_id = site.id)
                Heap Fetches: 0
Planning time: 0.361 ms
Execution time: 1.149 ms

The same query for parameters is rather slow and does NOT use the index:

EXPLAIN ANALYZE
select *
from parameter
where exists (
      select 1 from datavalue
      where datavalue.parameter_id = parameter.id limit 1
);

Seq Scan on parameter  (cost=0.00..20.50 rows=15 width=2648) (actual time=2895.972..21331.701 rows=15 loops=1)
  Filter: (SubPlan 1)
  Rows Removed by Filter: 6
  SubPlan 1
    ->  Limit  (cost=0.00..0.34 rows=1 width=0) (actual time=1015.790..1015.790 rows=1 loops=21)
          ->  Seq Scan on datavalue  (cost=0.00..502127.10 rows=1476987 width=0) (actual time=1015.786..1015.786 rows=1 loops=21)
                Filter: (parameter_id = parameter.id)
                Rows Removed by Filter: 7739355
Planning time: 0.123 ms
Execution time: 21331.736 ms

What the deuce is going on here? Alternatively, whats a good way to do this?

Some of the table description:

id BIGINT DEFAULT nextval('datavalue_id_seq'::regclass) NOT NULL,
value DOUBLE PRECISION NOT NULL,
site_id INTEGER NOT NULL,
parameter_id INTEGER NOT NULL,
deployment_id INTEGER,
instrument_id INTEGER,
invalid BOOLEAN,
Indexes:
    "datavalue_pkey" PRIMARY KEY, btree (id)
    "datavalue_datetime_utc_site_id_parameter_id_instrument_id_key" UNIQUE CONSTRAINT, btree (datetime_utc, site_id, parameter_id, instrument_id)
    "ix_datavalue_instrument_id" btree (instrument_id)
    "ix_datavalue_parameter_id" btree (parameter_id)
    "ix_datavalue_site_id" btree (site_id)
    "tmp_idx" btree (site_id, datetime_utc)
Foreign-key constraints:
    "datavalue_instrument_id_fkey" FOREIGN KEY (instrument_id) REFERENCES instrument(id) ON UPDATE CASCADE ON DELETE CASCADE
    "datavalue_parameter_id_fkey" FOREIGN KEY (parameter_id) REFERENCES parameter(id) ON UPDATE CASCADE ON DELETE CASCADE
    "datavalue_site_id_fkey" FOREIGN KEY (site_id) REFERENCES coastal.site(id) ON UPDATE CASCADE ON DELETE CASCADE
    "datavalue_statistic_type_id_fkey"

Edit: Here's the count distribution

select count(parameter_id), parameter_id from datavalue group by parameter_id

88169   14
2889171 8
15805   17
8570    12
4257262 21
3947049 15
1225902 2
4091090 3
103877  10
633764  11
994442  18
49232   20
14935   4
563638  13
2955919 7


texts: On Duplicate Key Update - million times per day optimization?

texts:
I am using the following query with different variables millions of times per day, and I am wondering if there is any way to optimize it:

    INSERT INTO campaign_stats (user_id, date, $country)
    VALUES      (:user_id, :date, :country)
    ON DUPLICATE KEY UPDATE $country=$country+1

The ON DUPLICATE KEY UPDATE $country=$country+1 is true in 99% of the time, so it might be better to check for it first?


texts: MongoDB + WiredTiger cache usage stuck on 95%

texts:
I'm running a mongo 3.4.7 cluster (8 CPU, 120GB ram, primary + 2 secondaries) with ±130 open connections, each one of them to a separate DB with ±50 collections.

After a few minutes of running, cache usage percent goes to 95% and stays there. Sometimes is goes down a bit to ±93%, but quickly returns to 95%.

According to the docs, when cache usage is at 95%, WiredTiger will start using application threads for eviction, which slows down the DB and can explain why cache usage doesn't get higher than 95%.

What is the reason that cache eviction can't keep up with the load, while machine resources are under-utilized (CPU, Disk)?

Tried increasing the number of eviction threads to the maximum of 20, no luck there.

Update:

Here are the result of iostat -myx 1

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          15.40    0.00    0.63    0.00    0.13   83.84

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
xvde              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
xvdc              0.00     9.00  166.00   22.00     2.27     0.17    26.52     0.14    0.74    0.51    2.55   0.40   7.60
nvme0n1           0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00


texts:
I have a requirement where I need to audit (and potentially be notified) whenever the 'sa' user account logs into an SQL Server 2005 instance. Additionally, there is a preference that the database that the login accesses is also auditted.

I understand that I could use extended events or server audit in a newer version of SQL Server, but as I am limited to SQL Server 2005, the only option I can think of is to:

set up a server-side trace for user logon events for the 'sa' user into a table
set up a job to email the contents of the table at a specified interval

Are there more efficient ways to accomplish this in 2005 ?


texts:
My company has two locations: Head Quarters (HQ) and a sub location (SL).

Both locations (HQ and SL) has separate PostgreSQL databases with the same schema and they will do transactions (insert/delete/update) in off-line. When both comes on line, both databases need to be synchronized (ie, come in the same state). Is it possible?


texts: leading 0 missing in column when exporting to csv

texts:
I have a query I export which is then used to import products into our website.

One of the ways we help people find products more easily is by using a long and short item code. It's basically the last 5 digits of the main code.

People can search for the whole or partial number.

However when I export my query as a csv, any leading zeroes are removed.

Here is how I create the column in Access:

(Right(products_vendor1.shortISBN13,5)) AS item_code

Obviously, in Access I have no problem viewing this query and the values display even with the zeroes.

How can I export the csv without removing the leading zeroes?

These are the settings I'm using when I export:

  UTF-8 delimited by comma and quotes ,"

  and checking the box Include field names in first row.

And it's not that the leading zeroes are missing only with excel. It doesn't matter if I open with notepad++ or libre office calc

I'm using Access 2013 64bit.


texts: Auto-generate scripts to create tables, indexes, etc

texts:
In SSMS, we can right click a database/table/index/... and select the "Script As" option to generate a drop and create script.

Is there a way to automate this script generation and drop the scripts to a temp location, rather than manually right clicking each object and generating them?


texts: Issue with accented characters in SQLiteStudio

texts: I downloaded a SQLite 3 dump of Northwind, and opened it in SQLiteStudio. Accented characters aren't displayed nicely. How to solve the issue? I suspect some encoding issue.


texts: pg_upgrade --link fails: `directory "/..." already in use as a tablespace`

texts:
I'm attempting to upgrade from 9.6.2 to 10.5 using pg_upgrade

My upgrade command (from OpenBSD pkg-readme):

su _postgresql -c "cd /var/postgresql && \
    pg_upgrade --link -b /usr/local/bin/postgresql-9.6/ -B /usr/local/bin \
    -U postgres -d /var/postgresql/data-9.6/ -D /var/postgresql/data"

results in the following error

CREATE TABLESPACE "mydb" OWNER "mydb" LOCATION '/path/to/postgresql/mydb'; psql:pg_upgrade_dump_globals.sql:87: ERROR:  directory "/path/to/postgresql/mydb/PG_10_201707211" already in use as a tablespace

I'm using --link because the tablespace mentioned above is using 1.5TB of the available 1.8TB on its partition. So I don't think I'd have the disk space to do pg_upgrade without --link

I have disk space on other partitions if that helps.

EDIT

Before I encountered this error, I ran thepg_upgrade command without the --link flag, but then killed it when I realized there was insufficient disk space.  After that I re-ran initdb followed by the pg_upgrade command above.

Now I have 9.6 running again, but I have two directories in my tablespace location:

[root@host:~]$ls /path/to/postgresql/mydb/
PG_10_201707211   PG_9.6_201608131

Can I safely rename PG_10_201707211 to PG_9.6_201707211 and try the upgrade again?


texts: Duplicate Oracle Database from Windows to Linux

texts:
I have a regular Oracle 12c database already created, named ECLS. I've been given a pile of .BKP files, not from this database, and have been told to replace everything in ECLS with the contents of these files.

Here's how I've attempted to do that so far:

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initECLS.ora' nomount;
SQL> exit
$ rman auxiliary /
RMAN> duplicate database to ECLS backup location '/u01/dumpfilesgohere' NOFILENAMECHECK;

Inside the pfile, I have this line set:

*.enable_pluggable_database=true

It runs for 2.5 hours, then it fails with this error message:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/02/2017 14:05:04
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/fast_recovery_area/ECLS/archivelog/2017_02_02/o1_mf_1_73358_d970psod_.arc'
ORA-10562: Error occurred while applying redo to data block (file# 66, block# 799698)
ORA-10564: tablespace NCDS_TS
ORA-01110: data file 66: '/u01/app/oracle/product/12.1.0/db_1/dbs/D:ORADATAFUUVZPD152204CC3CA44789537C2BE3F39041EDATAFILEO1_MF_NCDS_TS_CQ476L1O_.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 106975
ORA-00600: internal error code, arguments: [ktbrcl:NOOP incompat_opt], [212], [], [], [], [], [], [], [], [], [], []

In the logs at /u01/app/oracle/diag/rdbms/ecls/ECLS/alert/log.xml I see these messages:

<msg time='2017-02-02T14:05:03.614-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_addr='127.0.0.1' pid='5138'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/ecls/ECLS/trace/ECLS_pr07_5138.trc:
ORA-00308: cannot open archived log &apos;D:\ORADATA\FUUVZP\ONLINELOG\O1_MF_1_CQ39NGLV_.LOG&apos;
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00600: internal error code, arguments: [ktbrcl:NOOP incompat_opt], [67], [], [], [], [], [], [], [], [], [], []
 </txt>
</msg>
<msg time='2017-02-02T14:05:03.614-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_addr='127.0.0.1' pid='5138'>
 <txt>Slave exiting with ORA-10562 exception
 </txt>
</msg>
<msg time='2017-02-02T14:05:03.614-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_addr='127.0.0.1' pid='5138'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/ecls/ECLS/trace/ECLS_pr07_5138.trc:
ORA-10562: Error occurred while applying redo to data block (file# 66, block# 1200147)
ORA-10564: tablespace NCDS_TS
ORA-01110: data file 66: &apos;/u01/app/oracle/product/12.1.0/db_1/dbs/D:ORADATAFUUVZPD152204CC3CA44789537C2BE3F39041EDATAFILEO1_MF_NCDS_TS_CQ476L1O_.DBF&apos;
ORA-10561: block type &apos;TRANSACTION MANAGED DATA BLOCK&apos;, data object# 106975
ORA-00600: internal error code, arguments: [ktbrcl:NOOP incompat_opt], [67], [], [], [], [], [], [], [], [], [], []
 </txt>
</msg>

The fact that I'm seeing Linux errors about files not being found and Windows file paths suggests to me that this is somehow related to the fact that the .BKP files were generated on a Windows machine while the place they're being used is a Linux machine.

How can I account for this? Is there some setting in the pfile that I need to change during startup? Or is there another argument that needs to be passed to RMAN in the duplicate command?


texts:
I am trying to get my SQL search to work. What I am looking for is a search result that pulls information based on either a company's name or their stock trading ticker. If one would to type in M, I would expect:

MAT  - Mattel
MFST - Microsoft
MA   - MasterCard
MDLZ - Mondelez
MON  - Monsanto

It queries the "ticker" just fine, but how would I get it to allow both? If I search MO instead of "M", I would expect to see this:

MON  - Monsanto
MDLZ - Mondelez

I want it to give bias towards the "ticker", but also query the name if it is like the search term. Here is my code:

SELECT `stock_id`, `stock_ticker`, `stock_simpleName`
FROM `stocks`
WHERE `stock_ticker` = 'M'
GROUP BY `stock_ticker`, `stock_simpleName`
ORDER BY CASE
    WHEN `stock_ticker` LIKE 'M%' THEN 1
    WHEN `stock_simpleName` LIKE 'M%' THEN 2
    ELSE 3
END
LIMIT 5

I wrote a different query that easily pulled the "ticker" alone, but trying to query them both with bias towards one of the columns like stated above seems to be a bit difficult. My AJAX returns an error which obviously means it's returning a PHP error rather than the data that should be requested.

Is there a tweak for this to get it to work?


texts: Stored procedure big where clause or multiple procedures

texts:
Is it better to have one big query or N small ones?

For example to select all the customers with multiple parameters:

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@Name is null or name like @Name)
and (@FirstName is null or firstname like @FirstName )
and (@City is null or name city @City)
and (@Birthday is null or birthday like @Birthday)

Or too have multiple stored procedures like

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@Name is null or name like @Name)
and (@FirstName is null or firstname like @FirstName)

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@City is null or name city @City)

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@Birthday is null or birthday like @Birthday)


texts:
When I run the following

select t.type
  from (values ('Green'),('Blue'),('Red')) as t(type)
   for xml path('')

I receive this output

<type>Green</type>
<type>Blue</type>
<type>Red</type>

If I run the following

select t.type + '/'
  from (values ('Green'),('Blue'),('Red')) as t(type)
   for xml path('')

I receive this output

Green/Blue/Red/

Why does adding the concatenation in the select lead to the removal of the type tags and output on one line in the xml file? Running SQL Server 2012.


texts:
I am trying to execute a PL/Sql block by using the OracleClientProvider in .Net . The language  I am using is c#, the DB is oracle10g

What I am actually doing is the following :

  //ConnectionSting is the connection String
  OracleConnection connection = new OracleConnection(connectionString);
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = connection;

  // Open the connection
  connection.Open();

  //queryFile contains the PL/SQL Script I am trying to execute;
  String queryFile = ConfigurationManager.AppSettings["MasterDbScript"];
  String dataInFile = new StreamReader(queryFile).ReadToEnd();
  cmd.CommandText = dataInFile;

  connection.Open();
  cmd.ExecuteNonQuery();
  //close the connection
  connection.close();

The PL/SQL block executes properly when I run it through ORACLE client but over here it throws an error ORA-00922: missing or invalid option

I want to ask :
1. Are the scripts executed in a different way from the normal query?
2. What am I doing wrong ?

Suggestions/Answers

Thanks.


texts: Disk performance causing SQL Server Command Timeout?

texts:
I setup a perfmon collector to capture data around some recent SQL Server command timeout errors and most indicators point to disk issues. CPU and memory numbers look normal. This is a SQL Server 2008 physical server with 2 disks. SQL data files reside on a one disk (RAID 5) and SQL log files on another.

At the time of the errors these particular counters are way beyond normal (about 10x worse than normal):

% Disk Time
Disk Reads/Sec (on the data disk)
Disk sec/Read (on the data disk)
SQLServer:Buffer Manager\Page life expectancy

With these perfmon findings, what would the next step be to isolate the problem?  Running SQL Profiler?


texts: scaling options for mysql on linux vm (ubuntu) : how to scale when the RAID 0 is out of space

texts:
I was asked to give a scaling road map for our mysql database.

Currently, I have a single mysql instance v5.6.28 running on an ubuntu VM on azure. It has a (software) RAID 0 disk made with two 1TBs.

My question is, when this 2TB disk gets filled up, what am i supposed to do?
I am thinking that my options would be

add another RAID 0.
add another mysql VM to existing infrastructure
migrate to mysql cluster
???

I want to know what is the recommended method to scale when the tables grow to an extent that the existing RAID runs out of space.


texts: Did anybody use sqlcmd mode in practice?

texts:
SQLServer 2005 introduced something called  SQLCMD Mode msdn link.

At first glance this mode adds variable subsitution from command line batch files,  and some escaping to OS commands.

Is this feature used in your environments, production or test?


texts: Is there any way to monitor execution of xp_cmdshell in SQL Server 2012?

texts:
I've been tasked with assessing the security risk involved with allowing a third party vendor to debug their queries remotely within SSMS. This means that the user's account must be in the sysadmin fixed role. While this means that the user for all intents and purposes "owns" the SQL Server instance, we have set that instance to a service account specific to that vendor. I'm left with primarily assessing the damage that can be done using xp_cmdshell.

I know that a sysadmin can undo almost anything that is implemented to monitor that activity, but if there is a way to monitor it (using Extended Events perhaps?) and/or log function use it would provide us with an additional level of security.


texts: Minimum permissions required for mail attachment in DbMail

texts:
I know that SQL DbMail requires for attaching, a windows login or a sysadmin privilege is required.

This is somewhat similar to  minimum dbMail permissions which was asked by me at an earlier point.

I want to understand whether without sysadmin what is the minimum privilege required for attaching files.


texts: How do I get the size of a Vertica database?

texts:
I have a Vertica database. How can I tell how big the database is?

I need a rough answer for the purposes of reporting basic statistics about our environment to upper management.


texts: Fill percentage of total field conditional on some other field with MS Access SQL

texts:
This question is an extension of Fill percentage of total field with MS Access SQL.

I have the following 2013 MS Access SQL query:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable") ;

This query fills field F with the percentage over the total of some field f, such that each one of the records F(i) in field F is given by:

F(i) = f(i)/SUM_i(f(i))

Both fields f and F belong to table MyTable.

Now, I would like, instead of simply computing the percentage over the whole table, to do it by Date, where Date is a 3rd field also to be found in table MyTable. I will make up an example for illustration purposes. Let's assume a simplified MyTable with 2 records per date; then that's what I would like MyTable to look like after executing my desired query... - see fields f and F:

|    Date    | Department |     f     |     F     |
|------------|------------|-----------|-----------|
|     t1     |     D1     |     2     |    40%    |
|     t1     |     D2     |     3     |    60%    |
|     t2     |     D1     |     1     |    20%    |
|     t2     |     D2     |     4     |    80%    |

... And this is how it is looking right now, after executing the query displayed above:

|    Date    | Department |     f     |     F     |
|------------|------------|-----------|-----------|
|     t1     |     D1     |     2     |    20%    |
|     t1     |     D2     |     3     |    30%    |
|     t2     |     D1     |     1     |    10%    |
|     t2     |     D2     |     4     |    40%    |

I have tried the following code, but none yielded what I wanted.

Try #1:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable", "Date");

Try #2:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable", "Date=Date");

Any ideas on how I could manage to do this?

Note: I have just called the two fields "f" and "F" here for illustration purposes, it is not how they are named in the actual DB.

I will add here further tests that haven't worked out:

Try #3:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable", "Date=" & Date);

Try #4: this has worked but only for a single Date, the 1st one to be recorded - i.e. the one in the 1st row/record of the table MyTable - for the rest of dates the value for F is empty.

UPDATE MyTable
SET F = f / DSUM("f", "MyTable", "Date=#" & Date & "#");


texts: Is @@ROWCOUNT preserved by EXECUTE or not?

texts:
The docs for @@ROWCOUNT say:

  EXECUTE statements preserve the previous @@ROWCOUNT.

I take this to mean that @@ROWCOUNT should have the same value after an EXECUTE statement that it had before.

However, considering the following:

CREATE TABLE #wat (id int);

INSERT #wat VALUES (1),(2),(3);

SELECT @@ROWCOUNT before;

EXECUTE('INSERT #wat VALUES (1),(2);');
SELECT @@ROWCOUNT after;

DROP TABLE #wat;

On SQL Server 2016 SP2 CU 8, before and after are different.

Do I misunderstand something here, or is this a documentation issue?


texts: postgresql 9.1 Multiple Cluster on same host

texts:
I have 2 cluster databases, running on the same host, Ubuntu. My fist database port is set to default but my second database port is set to 5433 in the postgresql.conf file. While everything is ok with local connections, I cannot connect using any of my tools to the second database with port 5433, including pgAdmin. Please help. Any parameter that I need to modify for the new database with port 5433?

netstat -an | grep 5433 shows,

tcp 0 0 0.0.0.0:5433 0.0.0.0:* LISTEN
tcp6 0 0 :::5433 :::* LISTEN
unix 2 [ ACC ] STREAM LISTENING 72842 /var/run/postgresql/.s.PGSQL.5433

iptables -L shows,

Chain INPUT (policy ACCEPT)
target     prot opt source               destination

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination


texts: Moved 'tempdb', now file 'tempdev2' does not exist

texts:
I moved the tempdb first locating the files:

use master
select name, physical_name
from sys.master_files
where database_id = DB_ID('tempdb');
GO

then I moved each file:

use master
go
alter database tempdb
modify file (name = tempdev, filename = 'S:\')
go
alter database tempdb
modify file (name = templog, filename = 'S:\')
go
alter database tempdb
modify file (name = tempdev2, filename = 'S:\')
go

The ndf tempdev2 did not move, though the path was set. My SQL Server will not start unless I use the "/f" switch.  When I retry the move or set the path to original location I get the following message:

  Msg 5041, Level 16, State 1, Line 1 MODIFY FILE failed. File
  'tempdev2' does not exist.

Any ideas? tempdev and templog are fine, just the tempdev1 ndf.
Is it possible to set the path for tempdev2 via the registry?

The mistake I made was probably not shrinking the ndf before the move. Now I can not shrink it because the path is not correct.

Additional information

I know get the following error in event viewer:

  CREATE FILE encountered operating system error 5 (Access is denied.)
  while attempting to open or create the physical file 'S:\tempdb'.

I checked permissions on the partition and folder, and everyone has full access and this permission error happens.


texts:
Let us we have university library database. We need to store data about books and keep track of what departments use each book. It needs to write simple requests to obtain number of departments that use given book and names of these departments.
I have several solution, but they seem to me non-sql style solutions.. For example, I store each record of book as values (title,author,pages,MATH_DEPT_USE,CS_DEPT_USE, ... )
where MATH_DEPT_USE and others like this takes value 0 (does not use) or value 1 (does use)
For example, ('Functional Analysis','Walter Rudin',510,1,0, ...)
What the best way to store such kind of data ?


texts: When should nonclustered indexes be stored on separate filegroups?

texts:
I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the index refers.  I have also heard that this is a myth.

When is it advisable to store nonclustered indexes on a separate filegroup and drive?  What perfmon/profiler evidence would lead me to arrive at that conclusion?  Does hardware play a role in the decision (whether a RAID/SAN is used over a single drive)?


texts: Positional value issues with PostgreSQL crosstab

texts:
I am running this query:

select *
              FROM crosstab (
                $$
                  select postcode::text, indicator::int, value::float
                  from indicator_data
                  where
                    (indicator = 35 or indicator = 36 or indicator = 37 or indicator = 38 or indicator = 39 or
                    indicator = 105 or indicator = 106 or indicator = 107 or indicator = 108 or indicator = 108) and date = '2017-04-01'
                  order by 1,2
                $$)
              AS ct (
                "postcode" text,
                "one_y" float,
                "two_y" float,
                "three_y" float,
                "four_y" float,
                "five_y" float,
                "one_l" float,
                "two_l" float,
                "three_l" float,
                "four_l" float,
                "five_l" float
                )

against the table:

indicator   postcode    date        value
35          NW3 3       2017-04-01  1000
105         NW3 3       2017-04-01  2
37          NW3 3       2017-04-01  2000
107         NW3 3       2017-04-01  3

postcode    one_y   two_y   three_y four_y  five_y  one_l   two_l   three_l four_l  five_l
NW3 3       1000    2000    2       3       NULL    NULL    NULL    NULL    NULL    NULL

instead of this expected result:

postcode    one_y   two_y   three_y four_y  five_y  one_l   two_l   three_l four_l  five_l
NW3 3       1000    NULL    2000    NULL    NULL    2       NULL    3       NULL    NULL

How can I fix the query to return the exact positions for the values in the temporary table like in the expected result?

UPDATE
I have added the pivotal query in this change:

select *
                  FROM crosstab (
                    $$
                      select postcode::text, indicator::int, value::float
                      from indicator_data
                      where
                        (indicator = 35 or indicator = 36 or indicator = 37 or indicator = 38 or indicator = 39 or
                        indicator = 105 or indicator = 106 or indicator = 107 or indicator = 108 or indicator = 108) and date = '2017-04-01'
                      order by 1,2
                    $$,
                    $$
                    SELECT g FROM generate_series(1,10) g
                    $$)

but it doesn't help


texts:
I want to deploy Multi Site SQL Server HA/DR Solution. I am not sure if I use FCI/AG. In my case whenever the wan link is down at Site1 my ISP is redirecting my wan traffic to Site2.

I was thinking of deploying Node1 at Site1 and Node2 at Site2 and File Share Witness at Site3/Cloud, also making sure SAN - SAN Async Replication in place. So whenever my Site1 wan is failing I want the SQL at DR site to be active.

So Kindly Suggest what kind of deployment will be best for this case.

I have SQL Server 2012 Enterprise and Windows Server 2012 R2 Standard. I have to chose whether AOFCI or AOAG.

I have experience with 2 Node SQL Cluster but in single site. But this is new application and I want to have multi-site (multi-subnet) deployment. I have very less knowledge about AOAG but willing to learn of course.

Suppose if I chose to implement FCI i will have Node1 at Site1 with one shared disk1 from SAN1 and Node2 at Site2 with same disk1 from SAN2 but off-line. There will be SAN-SAN asynchronous replication in place and I will use Site3/Cloud for File Share Witness. Will this be the good solution ? Or you suggest AOAG assuming Node1(primary) and Node2(secondary replica - synchronous) at Site1 and Node3(secondary replica - asynchronous) at Site2.


texts: User created index question - sp_Blitz 5/17/18 - Incorrect output?

texts:
I have SQL Server 2014 CU11 with sp_Blitz 5/17/18 8:44AM release. sp_Blitz is reporting that I have user created statistics on some of my databases. I've executed a script that deletes all user created statistics. However, when I re-run sp_Blitz it still reports them. If I use the T-SQL on Brent's page: SELECT * FROM sys.stats WHERE user_created = 1 then I see all the user created indexes. However if I use alternate code like:

SELECT st.name AS TableName, ss.name AS StatisticName
FROM sys.stats ss
JOIN sys.tables st ON ss.object_id=st.object_id
WHERE ss.user_created = 1
ORDER BY st.name, ss.name;

I see no user created indexes. I've tried several variations of the "find user stats" script & the only one that reports any is Brent's. Why would this be? I've removed almost all the user created statistics, and would like to take care of these leftovers if possible.


texts: problem installing mytop , need a up to date tools for monitoring mysql

texts:
so i'm trying to installl mytop to monitor the database

i'm following this instruction :
http://www.sohailriaz.com/how-to-install-mytop-a-top-clone-for-mysql/

when i try to install it with yum i get this :

yum -y install mytop

    --> Finished Dependency Resolution
    Error: Package: perl-DBD-MySQL-4.013-3.el6.x86_64 (base)
               Requires: libmysqlclient.so.16()(64bit)
    Error: Package: perl-DBD-MySQL-4.013-3.el6.x86_64 (base)
               Requires: libmysqlclient.so.16(libmysqlclient_16)(64bit)
     You could try using --skip-broken to work around the problem
     You could try running: rpm -Va --nofiles --nodigest

i have mysql 5.5.x installed and apparently

libmysqlclient.so.16 is provided by MySQL 5.1 packages

i'm certinly not going to downgrade to 5.1 .

so is there anyway around this without downgrade or any majore change ?
if not is there any other more up to date , free , similar tool ?


texts: Formal name for functions that use the OVER clause

texts:
Is there a formal/academic name for the family of functions in Sql Server like ROW_NUMBER(), LAG, LEAD, RANK etc, that make use of the OVER(ORDER BY <PARTITION BY> ) syntax? How much of this is covered by the ansi standard?

I can see on MSDN that some of those are listed as analytic functions, but others are ranking functions.

I ask using Sql Server names, but one of the reasons I want to know is to help me find out how to translate some of these queries for other databases, and gauge support among those databases. The other reason is to help me wrap my head around the concept more completely. Most of my experience pre-dates their Sql Server use, and I find lately I'm too used to old workarounds when one of these functions might be the easy fit. Knowing how to refer to them will help me when reading other things.


texts:
I have a instance in my AWS RDS running SQL Server, setting up with 16 TB (this is the maximum accepted by AWS), I'd like to change this size to 2TB.
Have anyone some idea how can I do it?
AWS console do not offer support to change this value.
At this moment I'm paying for it, but I'm not using this total space.


texts: The meaning of the Roles in SQL Server 2008 R2

texts:
Just because I’m confused around this issue I’d like to ask the following questions, and please I would like to receive clear and easy understandable answers. Not deep analyze.

1.What is the Roles for a programmer?
2.What is the role of the Schemas owned by this… which related with them?
3.What is the meaning and the use for Securables in Roles
4.Why we add Users to a Role?
5.How we may handle the Roles in conjunction with to the Users, inside of a project?

Thank you very much in advance.

ADDITIONAL INFORMATION

The reason uploading this question based on an issue which I’m facing developing a web.site. And the Issue is.
I have a number (100) of Web.Pages and a number (20.000) of Users (application users), each User has a special code, thus numerous Users belong to a Group (regarding to there code) and others in another Group.
Now, when a User of a certain Group tries to open a page I need a rule to look if he has the right to open this page.
For that reason I was thinking to use the Roles… But I steel don’t know if that is a good idea rather to create my own rule between Web.Pages and Application Users…


texts:
I have four tables, categories, category_permissions, category_relation, and documents.

It's a part of a documentation project I'm working on, just to learn more about relational databases.

Pseudo data for the example tables:

Categories

id | name
-----------------------
 1 | Default Category

Category Permissions

category_id | permission_id
-----------------------------
1           | 1
1           | 2

Category Relation

document_id | category_id
---------------------------
1           | 1
2           | 1

Documents

id | title            | content
--------------------------------------
1  | Sample Document  | Content!!!!!
2  | Another Document | Hello, World!

What I'm trying to get is rows returned containing information using all of the tables combined. Right now I can get either all of the information surrounding the document (document table, joined with the relation and category tables) or only information surrounding the permissions.

I suspect the issue is because the category permissions aren't directly related to the documents that this is happening.

Something like this is what I'm expecting:

id | title            | content       | category_id | permission_id
--------------------------------------------------------------------
1  | Sample Document  | Content!!!!!! | 1           | 1
1  | Sample Document  | Content!!!!!! | 1           | 2
2  | Another Document | Hello, world! | 1           | 1
2  | Another Document | Hello, world! | 1           | 2

But I'm getting more like this:

id | title            | content       | category_id | permission_id
--------------------------------------------------------------------
1  | Sample Document  | Content!!!!!! | 1           | (NULL)
1  | Sample Document  | Content!!!!!! | 1           | (NULL)
2  | Another Document | Hello, world! | 1           | (NULL)
2  | Another Document | Hello, world! | 1           | (NULL)

Here's what I think the query should be, and like I mentioned it works how I expect except the permission id's return null.

SELECT d.id, d.title, d.content, c.id, cp.permission_id
FROM documents d
LEFT JOIN category_relation cr ON cr.document_id = d.id
LEFT JOIN category_permissions cp ON cp.category_id = cr.category_id
LEFT JOIN categories c ON c.id = cr.category_id;

If I switch the second LEFT JOIN category_permissions to a RIGHT JOIN, that is when the data flips and I get the permissions, but not the documents.

My question is, am I structuring my data wrong, or lacking the know-how in MySQL to pull this off? I could just pull all of the documents and then loop through them to query for their categories and permissions, but putting queries in loops has never worked out for me performance-wise, especially when trying it against larger sets of data.


texts: SQL Server 2012 AlwaysOn cannot connect using the Listener IP

texts:
I have setup a Windows network in an Amazon in a VPC. I have setup two SQL servers and configured Always On. Everything works OK, except that I cannot connect to the SQL Server using the AAG Listener IP from any other computer in the network except from the Primary Node.

I also cannot ping the Listener IP or the Cluster IP.

For the servers the IPS are specified on the NIC.

DC: 10.0.0.100
SQL1: 10.0.0.90
SQL2: 10.0.0.92
Cluster: 10.0.0.80
AAG ListenerL: 10.0.0.82

Any ideas?


texts:
I have a table images, which I use to store a file name, file hash and date created.

I also need the width, height and watermark overlay of each image, but only on very small occasions do I need to access/update this data, should I create another table and setup a foreign key relation to hold just these values, and when I do need this data, use a join?

Should I just store the width, height and watermark overlay values inside the images table?

Thanks


texts: MySQL time_zone = SYSTEM not getting correct date on CURRENT_TIME

texts:
Via SSH I print the current date of my server, which is correct based on my location:

When I run SHOW GLOBAL VARIABLES on MySQL, I have time_zone = SYSTEM - which makes me think MySQL will be using the system date and time.

However, when I run SELECT CURRENT_TIME it returns to me a result that looks like it's in another time zone (ignore the diff in minutes due to the time I took between both snapshots):

Is there any other necessary configuration to make CURRENT_TIME in MySQL reflect exactly the same system date time?


texts: pg_upgrade issue from postgres 9.3 to 9.6

texts:
I am trying to use pg_upgrade to upgrade postgres server from 9.3 to 9.6.
I have both binaries on the windows server R8.

Here are the 2 pg_hba.conf :

> 9.3 :
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
#local    all             all             127.0.0.1/32            TRUST
host    all             all             127.0.0.1/32            TRUST
host    all             all             10.0.0.0/8              md5

9.6 :
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
#local    all             all            127.0.0.1/32           TRUST
host    all             all             127.0.0.1/32            TRUST
host    all             all             10.0.0.0/8              md5

I have all permissions on the directories.
From 9.6 bin I execute :
pg_upgrade.exe path_of_oldbin path_of_newbin path_of_olddata path_of_newdata

All I got is :

  connection to database failed : could not connect to server : connection refused

I tried unsuccessfully some different config (local) as you see in pg_hba.conf, but can't find the reason why it can't connect. Any idea ?


texts: Grouping results with left and inner join with zero counts

texts:
I have a query like

SELECT
    `campaign_question_options`.`text`,
    COUNT(`campaign_submission_answers`.`answer`) as `count`
    FROM `campaign_questions`
    INNER JOIN `campaign_question_options` ON `campaign_question_options`.`campaign_question_id` = `campaign_questions`.`id`
    LEFT JOIN `campaign_submission_answers` ON `campaign_submission_answers`.`answer` = `campaign_question_options`.`text` AND `campaign_submission_answers`.`campaign_question_id` = 1
    LEFT JOIN `campaign_submissions` ON `campaign_submissions`.`id` = `campaign_submission_answers`.`campaign_submission_id`
    LEFT JOIN `participants` ON `participants`.`id` = `campaign_submissions`.`participant_id`
WHERE
    `campaign_questions`.`id` = 1
GROUP BY `campaign_submission_answers`.`answer`
ORDER BY `campaign_question_options`.`index`;

This gives me a result set like

+--------------+-------+
|     text     | count |
+--------------+-------+
| 1 (positive) |   114 |
| 2            |    48 |
| 3 (neutral)  |    34 |
| 4            |     6 |
| 5 (negative) |     0 |
+--------------+-------+

So the problem is that I then need to filter the results further on the participants.appraisee_id column. However if I add this to the where clause I lose my zero result (since the left join returns a null row).

SELECT
    `campaign_question_options`.`text`,
    COUNT(`campaign_submission_answers`.`answer`) as `count`
FROM `campaign_questions`
INNER JOIN `campaign_question_options` ON `campaign_question_options`.`campaign_question_id` = `campaign_questions`.`id`
LEFT JOIN `campaign_submission_answers` ON `campaign_submission_answers`.`answer` = `campaign_question_options`.`text` AND `campaign_submission_answers`.`campaign_question_id` = 1
LEFT JOIN `campaign_submissions` ON `campaign_submissions`.`id` = `campaign_submission_answers`.`campaign_submission_id`
LEFT JOIN `participants` ON `participants`.`id` = `campaign_submissions`.`participant_id`
WHERE
    `campaign_questions`.`id` = 1 AND `participants`.`appraisee_id` = 1
GROUP BY `campaign_submission_answers`.`answer`
ORDER BY `campaign_question_options`.`index`;

Which returns

+--------------+-------+
|     text     | count |
+--------------+-------+
| 1 (positive) |    16 |
| 2            |     1 |
+--------------+-------+

When in fact I wish for

+--------------+-------+
|     text     | count |
+--------------+-------+
| 1 (positive) |    16 |
| 2            |     1 |
| 3 (neutral)  |     0 |
| 4            |     0 |
| 5 (negative) |     0 |
+--------------+-------+

Can anyone help me improve this query?

Thanks


texts: Is there a strategy to use timestamp as primary key?

texts:
The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that strategy, because I assume that the machine cannot do two things at the same "time"...right?

I am working on a database with huge hibernate sequences in the keys, so I thought of using timestamps as a solution. Are there any approaches like this that are used in practise that work with any database? That would be good to know.

EDIT : What I am asking is that since no two moments of time are the same for us (humans), does that also hold true for computers? I know that the computers track time through the vibration of some crystal (not sure how small a time it can measure).

In the database I have some table where no primary key is applicable except time (Audit trail), and specifying a sequence (hibernate_sequence) increases the number to an extremely high degree which makes it inconvenient to work with other tables as it affects all tables with sequence as primary key.

So, if PostgreSQL can guarantee that no two insert/update operations will occur at the same timestamp, then I could use it as a primary key, BUT if not then obviously I cannot use this solution.

Basically the question is: Does PostgreSQL provide any guarantee (through some approach or configuration) that no two records will be inserted/updated at the same "time"?

Thanks


texts: Checking for Alwayson manual sync process for redo queue data

texts:
I have seen frequently few databases are having synchronization issue with secondaries and monitored the same and found redo data queue size is around in 2MB in secondary replicas. I have tried in GUI for resume data and do syncup but it's failed giving error.

Do we have any code for apply Redo pending data on secondary replica databases manually.


texts: time difference between two machines makes problem in service broker communication

texts:
I have two machines and each machine has a SQL Server instance. Between these machines I run service broker. One of my machines has a different time from the other. I mean the machine time of A is 9:00 o'clock and the other one is 11:00 o'clock. The difference between two times means the message can't be received from another machine. When I sync both times with the same time the messages are received.

My question is how can I configure service broker to skip the time difference?


texts: Update a table using summed values from another table in MySQL/mariaDB

texts:
I was trying to use the query below to update the ORDERS table by making the O_TOTALPRICE column up-to-date when the LINEITEM table is modified. And I was using a standard TPC-H database.

UPDATE ORDERS O, LINEITEM L
  SET
    O.O_TOTALPRICE = SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT/100) * (1 + L.L_TAX/100))
  WHERE
    L.L_ORDERKEY = O.O_ORDERKEY;

However, this results in 1111 - Invalid use of group function.

The operation is like this:

-----------------LINEITEM----------------       ---------ORDERS---------
L_ORDERKEY  L_EXTPRICE  L_DISCOUNT  L_TAX       O_ORDERKEY  O_TOTALPRICE
==========  ==========  ==========  =====       ==========  ============
1           12.99       20          8.5     ┬   1           <TO CALC>
1           8.99        30          11      ┘
2           99.99       10          10      ┬   2           <TO CALC>
2           49.99       0           8.5     ┤
2           3.99        50          14.7    ┘

Since there are multiple rows in LINEITEM corresponding to the record in ORDERS, I tried to add GROUP BY L.L_ORDERKEY, but that just came to a syntax error.

Is this possible to accomplish in the UPDATE statement, or does it have to be split to multiple statements? Thanks!!


texts: PostgreSQL performance worse when adding CPUs

texts:
I ran into a strange behaviour when benchmarking PostgreSQL. The setup is like this:

Virtual box VM, with 3 GB of RAM. The host has 16GB of RAM and 4 CPUs.
Ubuntu 14.04, standard PostgreSQL 9.3 installation with the default configuration
a single database with a single table having ~4k rows

I wrote a program that runs 10k simple selects (select * from table_name where id = 1) and times how long it takes to run them all.

When I assign a single CPU to the VM, it takes ~2.5 seconds to execute the queries.

With 2 CPUs, it takes more than 4 seconds.

With 3 CPUs, it takes more than 7 seconds.

With 2 (or 3) CPUs, if I also run at the same time another program which uses 100% CPU (but doesn't use the database), performance improves.

What's going on here?


texts: mysql server password change while restoring

texts:
I restored all database backup from another server's dumpfile to my machine. At the end my mysql server's password changed to that mysql server's password. Why it got overwritten? What i need to do to prevent this?
Thanks & Regards,
Praveen.


texts:
As the Google Cloud SQL is a managed instance of MySQL under the hood, limited to an absolute max on 2nd generation machines of 100GB of RAM, should I be compressing some of my table and index data ? I am designing a global system  so eventually the working set will probably exceed the 100GB.

Previously i've achieved performance improvements by compressing tables and indexes in SQL Server and Oracle systems that were memory bound.

I'm new to GCP, and consequently MySQL. Would the usage of compression still apply in this case? are there any such functions or procedures (like compression_estimate in SQL Server) I can run to assess if a table or index will benefit from the compression?


texts: How to retrieve the value of deleted key in jsonb?

texts:
Using the minus operator of jsonb, we can delete the required key, which is super cool, but what if we need to retrieve the value of deleted key?

Thus, if the key was not found, a null is returned or so. Otherwise the value is returned.

Is this possible?


texts:
I'm writing a program for my client in C# Winform. And the client gave this excel sheet.

Process

I have a table that used to calculate BULK amount of alcohol by "DIP" and "SLIDE".
In excel I need to select the specific DIP and SLIDE to get the BULK. I need to do the same thing in the database.

I exported a table from excel to MySQL table.

I can get the BULK amount by this query

SELECT SLIDE 0.8 FROM tbl_vat_01_alcohol_esa WHERE DIP = 212;

Requirement

I need to get the DIP and/or SLIDE when I enter BULK amount. I wanna know how to do it? or Is it possible to do that kind of work in this table? or do I need to change the table structure or something.

OR

How to access the excel sheet from C# winform. I mean, If I make a form that contain 3 textbox for DIP, SLIDE and BULK.
When I enter DIP and SLIDE I get BULK and when I enter BULK I get DIP and SLIDE...

Is it possible? If it is, How to achieve that? OR Is there any other method.

I'm new to StackExchange and Database!


texts:
I already received an answer on similar question, but it's not applicable in the case below.

In last time, I just rewrited my query on two distinct, first was fetching first N rows from one table and then it was joined (if and only if it contains sufficient amount of data). But here there is enough of data, but query still be very slow. Here is simplified example of slow query:

DECLARE @Top int = 1000

SELECT TOP (@Top) *
FROM (
    SELECT [t0].[DateTimeUtc] AS [value], [t2].[SystemName], [t2].[Name], [t0].[Id], [t1].[Discriminator], [t1].[ParentActionTemplateId], [t0].[DateTimeUtc], [t1].[Id] AS [Id2]
    FROM [directcrm].[CustomerActions] AS [t0]
    INNER JOIN [directcrm].[ActionTemplates] AS [t1] ON [t1].[Id] = [t0].[ActionTemplateId]
    LEFT OUTER JOIN [directcrm].[ActionTemplates] AS [t2] ON [t2].[Id] = [t1].[ParentActionTemplateId]
    ) AS [t7]
WHERE (([t7].[Discriminator] = @p9) OR ([t7].[Discriminator] = @p10) OR ([t7].[Discriminator] = @p11) OR ([t7].[Discriminator] = @p12)) AND ([t7].[ParentActionTemplateId] IS NOT NULL) AND (([t7].[DateTimeUtc]) <= @p13) AND (([t7].[DateTimeUtc]) > @p14) AND ([t7].[Id] > @p15)
ORDER BY [t7].[Id]

But if we hack optimizer:

DECLARE @Top int = 1000

SELECT TOP (@Top) *
FROM (
    SELECT [t0].[DateTimeUtc] AS [value], [t2].[SystemName], [t2].[Name], [t0].[Id], [t1].[Discriminator], [t1].[ParentActionTemplateId], [t0].[DateTimeUtc], [t1].[Id] AS [Id2]
    FROM [directcrm].[CustomerActions] AS [t0]
    INNER JOIN [directcrm].[ActionTemplates] AS [t1] ON [t1].[Id] = [t0].[ActionTemplateId]
    LEFT OUTER JOIN [directcrm].[ActionTemplates] AS [t2] ON [t2].[Id] = [t1].[ParentActionTemplateId]
    ) AS [t7]
WHERE (([t7].[Discriminator] = @p9) OR ([t7].[Discriminator] = @p10) OR ([t7].[Discriminator] = @p11) OR ([t7].[Discriminator] = @p12)) AND ([t7].[ParentActionTemplateId] IS NOT NULL) AND (([t7].[DateTimeUtc]) <= @p13) AND (([t7].[DateTimeUtc]) > @p14) AND ([t7].[Id] > @p15)
ORDER BY [t7].[Id]

OPTION (OPTIMIZE FOR (@TOP = 100000000)) -- < here

We get an instant answer, because in first case it uses loop join which is slow as hell, in second - hash join.

Of course I rebuilded all statistics and all indices for all tables in database.

I don't know what can I do with this query, because it's just a simple join without any magic. I tried build some indices, rewrite query and so on, but optimizer always chooses the same plan and runs very slow. I'm unable to use hints because of ORM and because they seems to be really hacky. I use them to determine the bottleneck and then fix it by using DDL modifications. It usually works, but today it failed.


texts: In SQL Server 2008, in DMV sys.dm_db_index_phsicial_stats, what does compressed_page_count mean in comparision with page_count

texts:
In SQL Server 2008, DMV sys.dm_db_index_physical_stats, what does compressed_page_count mean in comparison with page_count. I have a table with the following info:

Object_id     Index_type_desc      avg_frag_in_percent    page_count     compressed_page_count        avg_page_space_used_in_percent       avg_record_size_in_bytes
5464654        CLUSTERED INDEX         0                    140992          1410                                        99.24                        111.2
4465464        NONCLUSTERED INDEX      0                    52532           526                                         99.69                         32.2

Does it mean, that the table has 140992 pages, but after compression it is brought down to 1410 pages or something else?

  [BOL says:] MSDN
  compressed_page_count: The number of compressed pages.


texts:
How to write the following code into mysql,

    SELECT SUM(COUNT(col1)) over (PARTITION by col2) as summation
    FROM TABLE
    GROUP BY col2


texts: How to append zeros to substring Datetime values in sql table?

texts:
I need to change a DateTime to a new format by appending zeros to dates less than 10. DateTime column is nvarchar(MAX)

Currently: - April 8 2018
4/8/2018 00:00:00 AM

New wanted format:

4/**08**/2018 00:00:00 AM


texts:
I have an Oracle 11gr2 database running on Enterprise Linux 6.6 server.

I use the following command to split the expdp dump file to 10G size chunks:

expdp userid=/ DIRECTORY=dmpdir dumpfile=dump%u.dmp filesize=10G schemas=user1

this creates 4 files that I should move to another server (rsync then rm).

Can I move a file as soon as the expdp starts creating the following one? and how should I do that? I have idea like while (next_file not exists) wait.

Thank you.


texts: MySQL INSERTs waiting for UPDATEs (MyISAM)?

texts:
In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables.

We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. However, this did not change the order. Whenever there is an UPDATE in the queue, INSERTs wait for them to finish.

Is this by design? I could not find any write-ups detailing how this ordering works, any references we can read on this?

UPDATE:
I'm posting how a typical show processlist; looks. It might clarify the question.
SELECTs are working just fine. LOW_PRIORITY on the UPDATE pushes it to the back of the list. But INSERTs are waiting as well. This is database has concurrent_insert set to ALWAYS. Therefore I expect SELECTS and INSERTS to work at the same time and UPDATE LOW_PRIORITYs to take affect only when there are no more INSERTs or SELECTs waiting around. When there are no UPDATEs, concurrent_insert kicks in just fine.


texts: PowerShell to get all servers with SQL Server or Oracle installed

texts:
Overview:

Trying to get all servers with SQL Server or Oracle installed.

Troubleshooting:

get-service -computername "" works fine when run by itself.

But when used in script block of Foreach statement it results in this error:

  Get-Service : Cannot open Service Control Manager on computer
  '@{Name=}'. This operation might require other privileges.

Code Used:

CLS
Import-Module ActiveDirectory

$servers = Get-ADComputer -Filter {OperatingSystem -like "*Server*"} -Property Name | Select Name

Foreach ($server in $servers)
{
    get-service -computername $server |where{$_.name -like "OracleService*" -or $_.name -like "*SQL*"}| select @{N='Sever';E={$_.name}}, Name, DisplayName | Format-Table -A
}

What to do?


texts: App domain is marked for unload due to memory pressure

texts:
In my sql server log (2005 SP4) I have this message every couple of seconds :

"AppDomain XXX [DataBase.dbo[runtime], YYY] is marked for unload due to memory pressure"

Our server :

Windows Server 2003 R2 Enterprise Edition SP2
32 Gb RAM
Physical Address Extension
Intel Xeon CPU X5675 x 2 (6 core) 3.07Ghz
Awe enabled
We have CLR code in SQL Server
-g2048 on startup (I'm no DBA, and this value was set by a former emplyee)

I read many article about memtoleave or vas, but I don't get how to diagnose my problem.

Any idea ?


texts: Is it possible to define a function within a stored procedure?

texts:
I have a stored procedure:

create proc sp_MyProc(@calcType tinyint) as
begin
    -- some stuff collating data into #MyTempTable

    if (@calcType = 1) -- sum
        select A, B, C, CalcField = sum(Amount)
        from #MyTempTable t
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
    else if (@calcType = 2) -- average
        select A, B, C, CalcField = avg(Amount)
        from #MyTempTable t
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
    else if (@calcType = 3) -- some other fancy formula
        select A, B, C, CalcField = sum(case when t.Type = 1 then 1 else 0 end) * t.Factor
        from #MyTempTable t
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
    -- plus a whole bunch of other, similar cases
    else
        select A, B, C, CalcField = 0.0
        from #MyTempTable t
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
end

Now all these different cases for different values of @calcType seem to be wasting a lot of space and causing me to copy and paste all over, which always sends shivers down my spine.

Is there some way of declaring a function for CalcField, similar to lambda notation in C#, to make my code more compact and maintainable?  I would want to do something like this:

declare @func FUNCTION(@t #MyTempTable) as real -- i.e. input is of type #MyTempTable and output is of type real

if (@calcType = 1) -- sum
    set @func = sum(@t.Amount)
else if (@calcType = 2) -- average
    set @func = avg(@t.Amount)
else if (@calcType = 3) -- some other fancy formula
    set @func = sum(case when @t.Type = 1 then 1 else 0 end) * @t.Factor
-- plus a whole bunch of other, similar cases
else
    set @func = 0;

select A, B, C, CalcField = @func(t)
from #MyTempTable t
join AnotherTable a on t.Field1 = a.Field1;
group by A, B, C

Obviously the syntax here doesn't work, but is there something that will achieve what I want?


texts: What grants should I give a user if I want the user to see all tables and views (view definitions) in every database in the server

texts:
I want a user to see all the tables and views in the each database on the server. I also want them to see the view definitions. By that I want to also be able to see how the views are created. That is, the query inside the view not the results

What server level access should I grant them to be able to do that? We have several databases and a huge amount of views and tables in each database. I don't want to do that manually one by one

Thanks


texts: Integration Services 'All Execution Reports' Loads Forever

texts:
I'm using SQL Server 2012 to run my Integration Services (SSIS) package by deploying it to the SSIS catalog.

Then an error happened in my packages, so I went to check what it is by opening:

Reports | All Executions | All Messages

However, it never actually opens and loads forever:

And when I tried clicking the refresh button, this error showed up instead:

I don't have any clue what's going on, and I don't remember it ever being like this before, so something (or someone) must have been changing things.

Where should I check on this?

Any advice would be greatly appreciated.


texts: Basic trigger in PL/pgSQL, to flip a boolean field if another field has text

texts:
I want a BOOLEAN field to be set to TRUE when a sibling TEXT field is set to a non-empty string. Vice versa, the BOOLEAN is set to FALSE when TEXT field is set to empty string.

The following function is running, but fails to flip the BOOLEAN field.

CREATE OR REPLACE FUNCTION set_flags_on_recipe_trigger_function_()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$

DECLARE

BEGIN

    IF (TG_TABLE_NAME = 'recipe_') THEN

        IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN
            BEGIN
                NEW.is_baked_ = ( length( NEW.baking_instructions_ ) > 0 );
                NEW.is_roasted_ = ( length( NEW.roasting_instructions_ ) > 0 );
            END;
        END IF;

        RETURN NEW;

    ELSE
        RAISE EXCEPTION 'This trigger is being used on the wrong table.';

    END IF;

     /* Should never reach this point. Branching in code above should always reach a call to RETURN. */
    RAISE EXCEPTION 'Unexpectedly reached the bottom of this function without calling RETURN.';

END;

$BODY$;

Is this kind of question about programming PL/pgSQL appropriate to this site? Or should I be posting this to StackOverflow?


texts: Rearranging Data in an MS Access 2007 table into a new table format/Result Output

texts:
I have Access tables that have the following format:

--------------------------------------
|Name |Math|Science|Reading|Civics|
--------------------------------------
|Mike | A  |  C    |  A    | B    |
|John | B  |  A    |  A    | B    |
|Randy| A  |  C    |  A    | A    |
|Sasha| B  |  C    |  D    | A    |
|Max  | A  |  C    |  A    | A    |
|Jenn | A  |  A    |  A    | C    |
-----------------------------------

Is there any Access Sql code or Access VBA script that I can use so that I can convert (or output in a query/resultset) the above table in the below format?

 --------------------
|   Name      |Grade|
---------------------
|Mike Math    | A   |
|Mike Science | c   |
|Mike Reading | A   |
|Mike Civics  | B   |
|John Math    | B   |
|John Science | A   |
|John Reading | A   |
|John Civics  | B   |
|Randy Math   | A   |
 etc...

What I have found so far are the below 2 threads which shows how to bring in all the field names into an array or just basic output using VBA in Access. It's a start for what I am trying to accomplish (since in essence I will need to bring in the field names into the first column). I'll post back if I'm able to figure it out.

https://stackoverflow.com/questions/11548697/how-to-list-fields-name-in-table-in-access-using-sql

http://forums.asp.net/t/1143922.aspx

I'm wondering If I can do something like...

For each fieldname in table (where fieldname != Name)
Begin
select
Name + ' ' + Subject as Name,
(select Grade from table where Name = Name and Subject = Subject) as Grade
End


texts: Error 1064 - You have an error in your SQL syntax;

texts:
I am new to developing field, and I have encountered an error while creating a trigger.Could anyone please figure out what is the error?

DELIMITER $$

CREATE TRIGGER update_feedback_dashboard
AFTER INSERT
ON tblcustomfeedback FOR EACH ROW

BEGIN

DECLARE averageScore float default 0;
DECLARE avgscore float default 0;
DECLARE IdCount float default 0;

SELECT SUM(avg_score) into avgscore, COUNT(Id) into count FROM tblcustomfeedback  WHERE feedbackDate = NOW() ORDER BY Id DESC LIMIT 1;

SET averageScore  =avgscore;
SET IdCount  =  count;

IF (SELECT count(*) FROM tblfeedbackdashbd WHERE Date=CURDATE()) = 0
THEN
 INSERT INTO tblfeedbackdashbd(AverageScoresAday ,Date)     VALUES(averageScore/IdCount,CURDATE());
ELSE
 UPDATE tblfeedbackdashbd SET AverageScoresAday=averageScore/IdCount  WHERE   Date=CURDATE();
END IF;
END $$

The error encountered is,

  [Err] 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 'COUNT(Id) into count FROM tblcustomfeedback  WHERE
  feedbackDate = NOW() ORDER BY' at line 11


texts: How does mysqldump take place when there are more than one database?

texts:
I'm writing a shell script wherein I'm doing a mysqldump of all databases.

My questions are:

Are the tables of all databases in the server locked during the mysqldump?
If I have to do an incremental backup using mysqlbinlog from the last full backup, should I take the time when mysqldump started or when it ended?
What happens when one is using the database when mysqldump is running?
How can I take the start time for mysqlbinlog? What happens when mysqldump is executed?


texts: Perform SELECT without Locking Table but don't allow Dirty Reads

texts:
Is there a way I can perform a SELECT query which wont lock the table it is reading  but also won't perform dirty reads?

for example, assume Table1 has 100m records:

SELECT Col1,
       Col2
FROM   Table1

on the default SQL Server isolation level, the query above will block other queries inserting into Table1,

However

SELECT Col1,
       Col2
FROM   Table1 WITH (NOLOCK)

Will mean other queries are able inserting records. The downside of this is that the query above is susceptible to dirty reads.

Is there a way to get the "good" part of no lock behaviour (no locking) but not the "bad" (dirty reads) and if not, why is this?


texts: What are the practical reasons behind learning relational algebra?

texts:
Learning how to formulate query-like expressions in relational algebra is a traditional part of many, perhaps most, "Introduction to Databases" courses.

This is usually justified by the assertion that relational algebra is the mathematical foundation of relational databases in general and SQL in particular with the implication that it is important to know it.

However it seems to me that formulating expressions in relational algebra is basically the same as formulating queries in SQL and that much the same thought processes underly both tasks. In particular, I can't really see that knowing relational algebra makes it easier to write SQL queries or vice-versa. This makes me wonder if the teaching of relational algebra is just some sort of historical hangover, or if there actually specific benefits to knowing it.

So my question is "are there specific practical benefits to knowing relational algebra, of sufficient importance to make it worthwhile teaching?"

As Database Administrators, do you feel that relational algebra is or was important to your career trajectory?

A sort of sub-question is whether the time spent learning relational algebra could more effectively be used by learning more SQL?


texts:
I am trying to reduce the size of our SYSAUX Table.  Currently the largest four occupants are below.  As you can see it is mostly the Server Manageability items.

select occupant_desc, space_usage_kbytes
  from v$sysaux_occupants
  where space_usage_kbytes > 0 order by space_usage_kbytes desc
/

OCCUPANT_DESC                                       SPACE_USAGE_KBYTES
Server Manageability - Advisor Framework                4613440
Server Manageability - Optimizer Statistics History     2522944
Server Manageability - Automatic Workload Repository    1457280
Enterprise Manager Repository                       172480

I have reduced retention to 7 days

select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                          7

I have purged statistics older than 7 days:

exec DBMS_STATS.PURGE_STATS(SYSDATE-7);
PL/SQL procedure successfully completed.

All this but no luck reducing space in the tablespace.  I also tried following the instructions in Purging statistics from the SYSAUX tablespace   but the space is still full.

Any suggestion?


texts: How to create date ranges of events coming from an API where open state represents the date range and close state the gaps between the ranges?

texts:
I would like to store date/time ranges in PostgreSQL of some arbitrary events that have a state of open or closed and
a date/time of when the state changed.

Events coming from an API will have the following data for a single event:

Request 1:
{
  id: 1,
  state: 'open',
  date: '2020-02-17T10:00:00Z'
}

Request 2:
{
  id: 1,
  state: 'close',
  date: '2020-02-17T10:10:00Z'
}

Request 3:
{
  id: 1,
  state: 'open',
  date: '2020-02-17T11:00:00Z'
}

The requests can come in any order, so future dates can come before past dates or states are not always
open -> close -> open -> close. For example the API could send the open states for the same event one after the other.

I was thinking of using the tstzrange for saving this data in the database in the following form:

CREATE TABLE events (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  event_id int,
  validity tstzrange
);

The open states are captured in the validity column and close states are the gaps between the validity columns.
For example if a single event has the following states and date/times (using only times for simplicity) in this order:

state          date/time
close          20:30
open           18:00
close          16:00
open           15:00
open           20:00
close          19:30

The validity rows should look like:

id          event_id           validity
1           1                  [15:00, 16:00)
2           1                  [18:00, 19:30)
3           1                  [20:00, 20:30)

Event 1 has the state open between 15:00 - 16:00, state close between 16:00 - 18:00, state open between 18:00 - 19:30 and so on.

To illustrate this visually:

My problem is that the events are not coming in order, so I don't know how to manipulate the individual validity
columns to insert / update these rows.


texts: CONNECT BY using 3 tables(2 DIM and 1 FACT to get the Level and dependencies)

texts:
Below is my scenario and any help in this regard would be highly appreciated.
I need to get the list of Managers and reporting persons to that manager with LEVEL at ID level.
But the Fact table doesnt have hierarchial relationship with itself. Needs to get the id's from key's from 2 different dimension tables to generate the list.
I am not familiar with CONNECT_BY and JOINS to get list.

Fact table:

Person_ky    Mngr_ky
100          3
50           6
2000         9999
60           3
150          4
20           2

In DIM Person table

Person_ky    Pers_Id
100          x
50           y
2000         aaaa
60           a
150          m
20      q

In DIM Manger table

Mngr_ky    Mngr_id
3           m
6           a
4           q
2           t
999         bbb

In the above scenario,
pers_ky 100 (Id -'x') is reporting to mngr_ky 3(Mngr_id - 'm') and  person_ky 150 ('m') is reporting to mngr_ky 4 ('q') and person_ky 150 ('q') reporting to mngr_ky 2('t')..
So Need to have for person x, his managers are 'm','q' and 't' in the result.

pers_id mgr_id level
x       m        1     --> for pers_ky 100
x       q        2
x       t        3
y       a        1     --> for per_ky 60
y       m        2
y       q        3
y       t        4
aaaa    bbb      1     --> for pers_ky 2000
a       m        1     --> for pers_ky 60
a       q        2
.
.


texts: New PostgreSQL 9.1 configuration doesn’t work

texts:
I changed the length of table names in PostgreSQL in the file pg_config_manual.h (in C:\Program Files\PostgreSQL\9.1\include) to 128. Then I reloaded the new configuration by selecting menu "Reload Configuration" under the menu PostgreSQL 9.1 in start menu. Also, I restarted the PostgreSQL. But the change did not take effect. Length of table names in my database still has the limit 64 characters.

How to solve this problem? I am using PostgreSQL 9.1 on Windows 7 Professional.