texts: Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working

texts:
I have several tables being reported by pt-table-checksum with CRC_DIFF between my master and slave servers using the following commands:

$ pt-table-checksum h=master,u=user,p=password --empty-replicate-table --databases db --replicate systemadministration.checksums
$ pt-table-checksum h=master,u=user,p=password --databases db --replicate systemadministration.checksums --replicate-check 1

Results in:

Differences on P=3306,h=slave
DB         TBL                         CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
db         table1                          0        0        1 1=1
db         table2                          0        0        1 1=1
db         table3                          0        0        1 1=1
db         table4                          0        0        1 1=1
db         table5                          0        0        1 1=1
db         table6                          0        0        1 1=1
db         table7                          0        0        1 1=1

However, when I run pt-table-sync, the script returns an exit code 0 and says that there are no issues.

$ pt-table-sync --execute --verbose --no-bin-log --tables db.table1 h=master,u=user,p=password h=slave
# Syncing h=slave,p=...,u=user
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 GroupBy   14:10:45 14:12:12 0    db.table1
$ echo $?
0

I've tried the different algorithms for the checksum command and have had no luck.

$ pt-table-checksum h=master,u=user,p=password --empty-replicate-table --algorithm=ACCUM --tables db.table1 --replicate systemadministration.checksums
DATABASE   TABLE                       CHUNK HOST                   ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
db         table1                          0 master                 MyISAM     141836 00141836D0139746   22 NULL NULL NULL
$ pt-table-checksum h=master,u=user,p=password --tables db.table1 --replicate systemadministration.checksums --replicate-check 1
Differences on P=3306,h=slave
DB         TBL                         CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
db         table1                          0        0        1 1=1
$ echo $?
1

Any hints or is there any other tools I can use to verify the data integrity?


texts: What are the first necessary actions needed to help secure a database's schema & data?

texts:
This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions.

It seems like there should be a good checklist out there for all to agree on that addresses the security aspect of database design & implementation.

I'm not asking about the details of storing salted/encrypted data such as passwords... I'm asking about the general guidelines one should follow before and while setting up a database so that it naturally inherits a reasonable degree of security & privacy from unwelcome company.

Can security/privacy be implemented on a schema level?  Or is the safety of the database dependent on the code used to manipulate it?

I am looking for some answers that are somewhere between setting up db user and table rights, creating stored procs, using views, etc.... and encrypting stored data.

Can table relations, field dependencies, data types, lock modes, indexes, keys, etc... be set up to actively help protect a database against attacks and/or theft?


texts: What index should I use for a read-only database?

texts:
The database looks like this:

Parameters has 126000 rows, which link an ExperimentID to the starting parameters for that ExperimentID. Simulations has ~107M rows, but I really only query against Xa (a floating point value with mostly unique entries). I'm interested in the other data, but for now it's just there. The current indices I have are the Primary Key index on Simulations, which is clustered, and a nonclustered columnstore index on the following columns:

Here's an example query I might be running against this database:

USE IMTsimulations
SELECT DISTINCT p.ControlRange, p.SensorError,
    PERCENTILE_DISC(0) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_MIN,
    PERCENTILE_DISC(.25) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Quartile25,
    PERCENTILE_DISC(.5) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Median,
    PERCENTILE_DISC(.75) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Quartile75,
    PERCENTILE_DISC(1) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_MAX,
    AVG(s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Mean,
    STDEV(s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_STDEV,
    COUNT(s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as TotalEvents

FROM dbo.Simulations s JOIN Parameters p ON s.ExperimentID = p.ExperimentID
WHERE s.TimeElapsed <= 7200 and p.ExogDexCurve =  4
ORDER BY p.ControlRange, p.SensorError

As you can see, I'll be mostly looking at aggregates of Xa according to certain starting parameters. I feel like my primary key shouldn't be cluster, and the columnstore index should be clustered, and it should only have Xa, but I don't know for sure. The dataset is complete, so there will be no INSERTs, UPDATEs, DELETEs, or anything like that on either table.

What indexes should I be building for this thing? What's going to let me efficiently query aggregates on Xa that are partitioned according to things in the Parameters table?

I'd appreciate any depth you go into, as I'm doing this on my own, and don't have much technical background with databases.


texts: How can I SELECT an 'instance' in PostgreSQL?

texts:
I am reading a paper called "Real Time Delta Extraction Based on Triggers to Support Data Warehousing" from the IEEE and they proposed the following code:

CREATE TRIGGER cdc_table ON db.table AS
DECLARE @OPER, @INST, @TAB, @PK, @ID
Select @TAB=table of altered tuple
Select @PK= primary key of @TAB
Select @INST= instance in which @TAB belongs
Begin
IF(@OPER = DELETE)
Select @ID= ID from tuple before deletion;
ELSE
Select @ID= ID from tuple after insertion or update;
ENDIF
Insert into log table @ID, @PK, @TAB, @INST, @OPER;
END;

In the line 5 they are asking me to SELECT the instance of the table I am altering. How can I do that?

The log table is defined as follows:

log id – an integer type field, having a sequential
number, that stores the execution order of the
operations;
instance – a text type field, stores the name of the
instance whose tuple was altered;
table – a text type field, stores the name of the table
whose tuple was altered;
operation – stores the type of operation that was
executed: INSERT, UPDATE or DELETE;
pk column – a text type field, stores the name of the
column that was used as a unique identifier in the
table whose tuple was altered;
tuple id – value of the unique identifier of the altered
tuple.

Thanks!


texts: Change Data Capture in MySQL/PHP by writing to a MySQL table (rather than streaming to Kafka/Rabbit/etc)?

texts:
I have a small internal web app I'm developing with PHP and MySQL. I'm looking to sync client webpages to a server by requesting all changes since a certain date.

I've found a number of solutions for doing this via a 3rd party Change Data Capture library in MySQL (Maxwell, Debezium, etc.), but they all seem to stream to Kafka or something similar. This is a smaller, simple project without any sort of message queue/broker.

All I want is for changes to be recorded in the same MySQL database in a table with columns like the CRUD action, the table that changed, the table row ID, and the datetime. Then I could query for all rows changed since a certain date and time.

Is there a simple way or a library that can record this information right in the same MySQL database?


texts: Select with limit, get total count, optimize query

texts:
The site mimics the "likes" tooltip from Facebook, the only difference is that you can like or dislike the content.

The tooltip shows the first 12 users that rated the content and displays the number of users not shown (i.e. John, Mike, Kim and 24 more rated this content.). If the user clicks on the tooltip, the full list is retrieved via AJAX and displayed on a modal window.

This is my current query for both actions:

SELECT
   mr.*,
   u.name
FROM
   medias_ratings mr
   LEFT JOIN users u ON u.id = mr.user_id
WHERE
   mr.media_id = XXX
   AND mr.id_client = YYY
ORDER BY
   u.name

How can I optimize the above query? The first thing I can think of is having a different query for the limited result. Something like this:

/* Get the first 12 ratings */
SELECT
   mr.*,
   u.name
FROM
   medias_ratings mr
   LEFT JOIN users u ON u.id = mr.user_id
WHERE
   mr.media_id = XXX
   AND mr.id_client = YYY
LIMIT 12

/* Get the total rating count */
SELECT
   COUNT (*)
FROM
   medias_ratings mr
WHERE
   mr.media_id = XXX
   AND mr.id_client = YYY

I've read that running a second query is usually faster than using SQL_CALC_FOUND_ROWS.

Edit 1: The media table already keeps a field for the total likes, so the query is just to fetch the list of users that liked the content.

Edit 2: Table structure, most of the user fields were omitted for brevity

CREATE TABLE `medias_ratings` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_user` int(11) NOT NULL,
 `id_media` int(11) NOT NULL,
 `id_client` int(11) NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `rate` int(1) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user` varchar(100) NOT NULL,
 `pass` varchar(100) NOT NULL,
 `category` int(11) NOT NULL DEFAULT '4',
 `name` varchar(100) DEFAULT NULL,
 `company` varchar(100) DEFAULT NULL,
 `area` varchar(100) DEFAULT NULL,
 `position` varchar(100) DEFAULT NULL,
 `country_code` varchar(3) DEFAULT NULL,
 `email` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `category` (`category`),
 FULLTEXT KEY `SEARCH` (`user`,`name`,`email`)
) ENGINE=MyISAM


texts: SQL Server stored procedures fast in SQL but slow when called by ASP.NET

texts:
I am using ASP.NET with MVC3 with a SQL server database.
From time to time, one of our stored procedures become incredibly slow when called by the web platform, but stay as fast when called directly in SSMS.

The solution to this problem is to write "SET ARITHABORT ON" at the beginning of the stored procedure, which magically works.

However, I want this to stop since I don't what is causing procedures to randomly start being incredibly slow on the web and causing timeouts.

Is there a way to resolve the problem definitely?

Thanks in advance,
Eric


texts: How to create an index to speed up an aggregate LIKE query on an expression?

texts:
I may be asking the wrong question in the title. Here are the facts:

My customer service folk have been complaining about slow response times when doing customer lookups on the administration interface of our Django-based site.

We're using Postgres 8.4.6. I started logging slow queries, and discovered this culprit:

SELECT COUNT(*) FROM "auth_user" WHERE UPPER("auth_user"."email"::text) LIKE UPPER(E'%deyk%')

This query is taking upwards of 32 seconds to run. Here's the query plan provided by EXPLAIN:

QUERY PLAN
Aggregate  (cost=205171.71..205171.72 rows=1 width=0)
  ->  Seq Scan on auth_user  (cost=0.00..205166.46 rows=2096 width=0)
        Filter: (upper((email)::text) ~~ '%DEYK%'::text)

Because this is a query generated by the Django ORM from a Django QuerySet generated by the Django Admin application, I don't have any control over the query itself. An index seems like the logical solution. I tried creating an index to speed this up, but it hasn't made a difference:

CREATE INDEX auth_user_email_upper ON auth_user USING btree (upper(email::text))

What am I doing wrong? How can I speed up this query?


texts:
I have a bunch of mysql statements (that work on one MyIsam table only) in my PHP script and I don't want to execute these statements concurrently at any time, regardless of how many connections are being used.

For example, two different clients execute the php script on my server and the script opens one mysql connection per each request. I want first to execute the entire bunch of statements in the 1st request and only when this is completed, to execute the bunch of statements in the 2nd request.

I tried:

using "START TRANSACTION" before the bunch of statements => the table is MyIsam and must stay so, so this has no effect.

using "LOCK TABLE t WRITE" before the bunch of statements => locks are not maintained across connections so this doesn't work.

Thanks for help.


texts: Granting Permission on Databases and Schemas

texts:
I have a  Security permissions script i have inherited that i'd like to improve. The script grants permissions on the user databases and Schemas.
I'd like to improve the script by  iterating through all the schemas instead of hard coding the schemas like i did within the cursor.
Any thought?

**

-- Assigning Permission to 'Domain\Development' at a DAtabase level
DECLARE @command nvarchar(max), @usr sysname, @DB nvarchar(500)
DECLARE @loginName varchar(256)
SET @loginName ='Domain\Development'
DECLARE Devcur CURSOR FOR
SELECT NAME FROM sys.databases WHERE database_id>4 AND NAME <>LOOKUP'
                                                   AND NAME  NOT LIKE  '%ReportServer%'
                                                   AND NAME NOT LIKE '%DBAdmin%'
                                                   AND NAME NOT LIKE '%DBA%'
                                                   AND state_desc='ONLINE' AND is_read_only=0
OPEN Dev
FETCH NEXT FROM Devcur INTO  @DB
WHILE @@FETCH_STATUS = 0
BEGIN
--Granting Access to User databases
BEGIN
    SET @usr='['+@loginName+']'
    SELECT @command = 'Use ' + @DB + ';
    EXEC sp_addrolemember N''db_datareader'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+''';
    EXEC sp_addrolemember N''db_ddladmin'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+''';
    EXEC sp_addrolemember N''db_datawriter'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+''';
    EXEC sp_addrolemember N''db_owner'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+''';
    GRANT Showplan TO '+@usr+';
    GRANT EXECUTE TO '+@usr+';
    GRANT VIEW DEFINITION TO '+@usr+';
    GRANT CONNECT TO '+@usr+';
    GRANT CREATE SCHEMA TO '+@usr+';
    GRANT DELETE TO  '+@usr+';
    GRANT INSERT TO  '+@usr+';
    GRANT UPDATE TO  '+@usr+';'

    PRINT @command
    Print '-----------------BUILD-----------------'
    Print @DB
--EXEC SP_executeSQL @command
END
--Access SOMER schema
SET @usr='['+@loginName+']'
SELECT @command = 'Use ' + @DB + ';
IF (select Count(*) from sys.schemas where name = ''SOMER'') >= 1
BEGIN
EXEC sp_droprolemember N''db_denydatawriter'', N''' + REPLACE(REPLACE(@usr, '[', ''), ']', '') + ''';
    GRANT VIEW DEFINITION ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT DELETE ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT INSERT ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT UPDATE ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT EXECUTE ON SCHEMA::[SOMER] TO ' + @usr + ';
End;'
--PRINT @command
EXEC SP_executeSQL @command
--Access Poser schema
SET @usr='['+@loginName+']'
SELECT @command = 'Use ' + @DB + ';
IF (select Count(*) from sys.schemas where name = ''Lser'') >= 1
BEGIN
EXEC sp_droprolemember N''db_denydatawriter'', N''' + REPLACE(REPLACE(@usr, '[', ''), ']', '') + ''';
    GRANT VIEW DEFINITION ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT DELETE ON SCHEMA::[Poser] TO ' + @usr + ';
    GRANT INSERT ON SCHEMA::[Poser] TO ' + @usr + ';
    GRANT UPDATE ON SCHEMA::[Poser] TO ' + @usr + ';
    GRANT EXECUTE ON SCHEMA::[Poser] TO ' + @usr + ';
End;'
--PRINT @command
EXEC SP_executeSQL @command
--Access Lser schema
SET @usr='['+@loginName+']'
SELECT @command = 'Use ' + @DB + ';
IF (select Count(*) from sys.schemas where name = ''Lser'') >= 1
BEGIN
EXEC sp_droprolemember N''db_denydatawriter'', N''' + REPLACE(REPLACE(@usr, '[', ''), ']', '') + ''';
    GRANT VIEW DEFINITION ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT DELETE ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT INSERT ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT UPDATE ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT EXECUTE ON SCHEMA::[Lser] TO ' + @usr + ';
End;'
--PRINT @command
EXEC SP_executeSQL @command
FETCH NEXT FROM Dev INTO  @DB
END
CLOSE Dev
DEALLOCATE Dev
GO

**

Would very much appreciate.


texts: How to effeciently save this kind of text into database?

texts:
I have this kind of text and want to save it to database:

I. Master Text I                                            <<<Parent
   I.A. Sub Master Text I                                   <<<Child
      I.A.1.  Sub Sub Master Text I                         <<<Child Child
         I.A.1.a Sub Sub Sub Master Text I - Row 1          <<<Child Child Child 1
         I.A.1.b Sub Sub Sub Master Text I - Row 2          <<<Child Child Child 2
         I.A.1.c Sub Sub Sub Master Text I - Row 3          <<<Child Child Child 3

II. Master Text II
   II.A. Sub Master Text II
      II.A.1.  Sub Sub Master Text II
         II.A.1.a Sub Sub Sub Master Text II - Row 1
      II.A.2.  Sub Sub Master Text II
         II.A.2.a Sub Sub Sub Master Text II - Row 1
         II.A.2.b Sub Sub Sub Master Text II - Row 2

III. Master Text III
   III.A. Sub Master Text III
      III.A.1.  Sub Sub Master Text III
         III.A.1.a Sub Sub Sub Master Text III - Row 1
         III.A.1.b Sub Sub Sub Master Text III - Row 2
         III.A.1.c Sub Sub Sub Master Text III - Row 3
   III.B. Sub Master Text III

How to effeciently save these text?. There are always a Parent but the number of Child is very dynamic. I can't predict how many child would be exist.

I currently using a conventional Master Table and Detail Table but I have to define the number of the Detail Table first. So, it's not reliable for this kind of text.

My main question, How to effeciently save these text (with n child) into database and easily display it like in the example?


texts: SQL Server - best practice before applying trace flags?

texts:
We are considering enabling trace flags 1118, 4199, 8048 on a server to fix some some performance issues

We are running into performance issues since migrating from a physical server running SQL Server 2008 to a virtual server running SQL Server 2012 (2 sockets 24 core, 40GB RAM) particularly around one of the ETL process.

As it is a difficult service to get downtime for, is it safe to apply without any downtime?  Or, is it wise to restart the service with these as startup parameters, I'm particularly concerned with the trace flag 8048


texts:
I run pgpool-II as postgres user so I can replicate exactly what I did in one sequence here in this post.

-bash-4.2$ echo "pgpool:$(pg_md5 test)" > /etc/pgpool-II-11/pcp.conf
-bash-4.2$ cat /etc/pgpool-II-11/pcp.conf
pgpool:098f6bcd4621d373cade4e832627b4f6
<Restart pgpool-II-11 service>
-bash-4.2$ echo "cia-db-vip:9898:pgpool:$(pg_md5 test)" > .pcppass
-bash-4.2$ cat .pcppass
cia-db-vip:9898:pgpool:098f6bcd4621d373cade4e832627b4f6
-bash-4.2$ pcp_watchdog_info -h cia-db-vip -p 9898 -U pgpool -w 0
FATAL:  authentication failed for user "pgpool"
DETAIL:  username and/or password does not match

-bash-4.2$ pcp_watchdog_info -h cia-db-vip -p 9898 -U pgpool 0
Password: <I Enter the password test>
2 YES cia-db-3:9999 Linux cia-db-3 cia-db-3

cia-db-3:9999 Linux cia-db-3 cia-db-3 9999 9000 4 MASTER
-bash-4.2$ ls -l .pcppass /etc/pgpool-II-11/pcp.conf
-rw-------. 1 postgres postgres 40 Jul 30 11:22 /etc/pgpool-II-11/pcp.conf
-rw-------. 1 postgres postgres 56 Jul 30 11:23 .pcppass

So only if I enter the password does it work. I've also tried hitting enter without a password.

I tried checking strace -ff pcp_watchdog_info and it starts to read the file .pcppass but I can only see the first line. Have no idea if it continues reading through the file or closes it.

stat("/var/lib/pgsql/.pcppass", {st_mode=S_IFREG|0600, st_size=56, ...}) = 0
open("/var/lib/pgsql/.pcppass", O_RDONLY) = 4
fstat(4, {st_mode=S_IFREG|0600, st_size=56, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f581f3d6000
read(4, "cia-db-vip:9898:pgpool:098f6bcd4"..., 4096) = 56
close(4)                                = 0
munmap(0x7f581f3d6000, 4096)            = 0
write(3, "M\0\0\0\4", 5)                = 5
select(4, [3], NULL, [3], NULL)         = 1 (in [3])
read(3, "m\0\0\0\10\205^\v\341", 1024)  = 9
write(3, "R\0\0\0,pgpool\000381d652cc9ef7891d4cb"..., 45) = 45
select(4, [3], NULL, [3], NULL)         = 1 (in [3])
read(3, "E\0\0\0xSFATAL\0CXX000\0Mauthenticati"..., 1024) = 121
close(3)                                = 0
write(2, "FATAL:  authentication failed fo"..., 98FATAL:  authentication failed for user "pgpool"
DETAIL:  username and/or password does not match

I also tried resetting the postgres user pgpool password to test, including in /etc/pgpool-II-11/pool_passwd but I doubt it matters since I can actually use pcp commands if I enter the password. So it seems that user is separate from the postgres users.

Edit: Also tried temporarily setting SElinux to permissive but it did not help.


texts: Using scope_identity() to get ID "during" insertion of row?

texts:
I have a column that has an ident_current constraint on it to populate another column, like so:

alter table dbo.Employee
add constraint dbo_Employee_D1 default ident_current('dbo.Employee') for SourceID`

When using ident_current, I have a risk of not always getting the correct identity value in case multiple threads execute my SQL. I wanted to use scope_identity() instead. But how do I use it? I cannot simply replace ident_current with scope_identity().

alter table dbo.Employee
add constraint dbo_Employee_D1 default scope_identity() for SourceID`

An option is to use an AFTER INSERT trigger. But won't this have the same issue in multi-threaded environment?


texts: Query to get list of busiest schemas in Oracle 12c database

texts:
I have an Oracle 12c database with many schemas, each schema belongs to a specific application.

I want to produce a query that shows me which schemas are the busiest in terms of read/write activity / IO etc.

I know that I can use the DBA_TAB_MODIFICATIONS table to get modifications since the last stats update, per table.

I've been told that the DBA_HIST objects will be able to give me more information but there's a lot of data there and I don't really know how to get the best out of it for this purpose.

Any help would be appreciated.


texts:
I attempt to connect to SQL Server 2008,I receive the following error.

  Login failed for user <>. Reason: Failed to open the explicitly specified database.

By the way, I have a SQL Server 2008 R2 on Windows Server 2008 64-Bit Enterprise Edition.

Thanks,


texts: Is there an alternative to isolation level SERIALIZABLE?

texts:
I use MySQL 5.7 and want to run an export task on a products table. The process basically runs like this:

SELECT * FROM products WHERE exported = 0
Loop over the result set in my programm and write it to a file
UPDATE products SET exported = 1 WHERE exported = 0

So each product should only be exported once.

I found that I have to wrap the above in a transaction with isolation level SERIALIZABLE because I have another process that inserts records at the same time.

Otherwhise that other process could insert rows with exported=0 while I'm in step 2) which would be missing in the file but get updated to exported=1 in step 3).

Is this the best way to solve this kind of problems? Or are there any drawbacks or pitfalls I'm no aware of?


texts: log_bin_trust_function_creators deterministic always throws an error on server restart

texts:
Everytime my rackspace server restarts because of some maintenance or an outage, all of my mysql functions stop working due to this deterministic error.

To fix this, I have to manually pull up the database and run SET GLOBAL log_bin_trust_function_creators = 1;

is there anyway to have this set by default so my sql calls don't break on restart?


texts: Can Microsoft Log Parser 2.2 open SQL LDF files?

texts:
From the technet article for Microsoft's Log Parser 2.2 it states that

"Can't find an input format you need?  The new COM input format makes it possible to create your own custom Input Format and plug it into the Log Parser engine."

Is it possible to create such a COM input format for Microsoft SQL LDF files (2005/2008), and if so any examples of how it can be done?

We are wanting the ability to investigate the LDF files to better support our development staff without breaking the bank.


texts: Is there any implications on updating Azure SQL server from V11 to V12?

texts: We are planning to update our servers but we can't be sure if there will be any implications or problems if we do that? Did anyone experienced any issue and how big they were? Or should I expect an issue or it would be updated smoothly?


texts: Should the user SYS be assigned new roles beside the roles that it has when it was created?


texts: How to restore accidentally truncate mysql table

texts: I have accidentally truncate mysql table table1. The truncated data are very very important. I used wampserver latest version. Unfortunately I do not have binary logs. I have wamp folder under drive C in Windows 10. Now I am trying to restore the database folder using Windows data recovery tool. Is there any other possibilities to restore my truncated table?


texts: Restore bak file to smaller mdf and ldf database files

texts:
I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have:

MyDatabase.mdf: 24.8GB
MyDatabase.ldf: 114.6GB

This database is backed up to a .bak file every night and shipped over to our reporting server, where it's restored. The .bak file is much smaller at only 1.8GB.

However, when I try to restore it on the reporting server, it fails due to insufficient space. There are around 100GB free on the server, and it's trying to eat up the entire 139.4GB that the files consumed on the original server. Unless my knowledge of compression is horrifically wrong, I'm fairly confident that the 1.8GB file is not actually expanding by 7400%.

My Question: Is there any way to tell SQL Server to restore this backup file without reserving that space upfront? I do not care about any of the logs; I just need the data to be there. I understand databases from the development and schema perspective, but I am by no means any sort of DBA.

This is on SQL Server 2008 R2. Thanks for any help or suggestions.


texts: Why mysql throws "invalid default value for field" only when two or more timestamp fields exist in the create statement?

texts:
In mysql 5.7.28 I create a table like this:

create table t1 (
id int not null,
d1 timestamp)
engine=innodb;

which works fine and creates d1 as non-null with default value current_timetamp,on update current_timestamp.

But when I try to create the same table with a two timestamp fields as below:

create table t1 (
id int not null,
d1 timestamp,
d2 timestamp)
engine=innodb;

I get an error:

  SQL error (1067): invalid default value for 'd2'

Why I get an error only when I add a second timestamp field?
Is this a bug or some expected behavior in mysql?


texts:
In a context of using SQL DB2:

The sql instruction
CHAR(MyDate, EUR) transforms 25/12/16 into 25.12.2016
(and CHAR(MyDate, ISO) transforms 25/12/16 into 2016-12-25)

Is it possible to get directly 25/12/2016
(or 2016/12/25)
without using the greedy scalar function
TRANSLATE(CHAR(MyDate, EUR), '.', /')
(or TRANSLATE(CHAR(MyDate, EUR), '-', /')

Maybe General Settings somewhere ...


texts:
I know what With Recompile means, and I know that it is not suggested at most of the times due to performance issues, no dmv's, etc..

Sometimes there are cases that it may help, but OPTION(RECOMPILE) may help too.

So that, when it is preferable to use With Recompile over OPTION(RECOMPILE)?

Thanks.


texts: Always On Availability Groups and MSDTC for SQL Server 2012

texts:
I have been told that since MSDTC is not supported for SQL Server 2012 Always On Availability Group and should be turned off.

what specific settings do I need to change to turn it off?

This is what I currently have:


texts: How much data can SQL Server full text indexing handle?

texts:
I realize that the question is vague and it depends on hardware and our needs.

We currently have 5 million rows of data, a total of 5GB of data which we want to index using full text indexing. Our data increases quite rapidly and it's not unreasonable to assume that in a few years it will be closer to a billion rows and a TB of data.

The index is searchable by web site users, and they expect responses within a second or two.

Is it reasonable to assume that this data set will be indexable using SQL Server 2012 full text indexing? Is it common to do full text indexing of this amount of data? And is there any good reading on the subject, for example from others' experience?


texts: Create users identified by the same password as another user in MariaDB 10.0.X

texts:
How do I create users with the same password as another user in MariaDB 10.x?

I do not know the password of the existing user , so i want to use same hashed password as existing, also do need to replica of the user with different host , meaning same grants.


texts: Approaches for Real time copy of database on same SQL Server instance

texts:
I have a draft of requirements that look like this:

Production database should be copied in real-time to "Pre-prod" database
The Pre-prod database should be available to read and update
It should be possible to make schema and data changes to Pre-Prod
Schema and data changes on Pre-prod should not effect Prod

The databases will be on the same SQL Server instance.

I'm wondering what approach to take? Transactional Replication seems the best option, but applying schema changes to a subscriber seems like a recipe for disaster.

Mirroring is out because the mirror will not be available; log shipping is out for similar reasons.

Are there any other technologies I should be considering?


texts: I have a users table that has a one to one relationship to a user_preferences table (primary foreign key user_id). When a new row is added to the users table (new id added), is there a way to setup the relationship between the users_preferences table so that a row with the new user id is also added to it?


texts: sp_send_dbmail: Failing When Adding Attachment

texts:
I have a simple script that should send an attachment using send_dbmail.  It  works when I exclude the attachment or send a very small file, e.g. 5KB. The prod file I want to send is 280KB.
    Exec msdb.dbo.sp_send_dbmail
    @profile_name = 'Activeprofile',
    @recipients = 'Dave@Dave.com.au',
    @subject = 'EmailStuff',
    @file_attachments = 'D:\SSIS\SSISData.csv'

The Database mail configurations are default at 1MB file sizes and the only error I get in the database mail log is

Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2020-07-22T15:25:34). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
)

This is SQL 2016 SP1 CU5 (Yes I want to patch but management are 'scared')
The process fails running from SQL Agent Job or Query window.
The email targets an internal relay server. I am able to send the file using a test SQL server running SQL 2017, I was wondering if this was a bug with SQL 2016.


texts: Recently I was informed that since enabling pg_stat_statements, the available disk space is dropping fast. I don't think it's caused by that, but I wonder: Does enabling pg_stat_statements reduce disk space? And by how much?


texts: Trouble removing filestream file / filegroup

texts:
My database was originally using filestreams in one table.  For reasons that I won't go into here, we decided to stop using filestreams.  I therefore wrote a migration to copy the filestreamed table into a new table that doesn't use filestream, I dropped the old table and renamed the new table.  All good so far.

Now I want to drop the filestream file and filegroup.  And SQL server won't let me:

  The file 'MyFilestream' cannot be removed because it is not empty.

I ran the following query:

select * from sys.tables t
join sys.data_spaces ds on t.filestream_data_space_id = ds.data_space_id
-- and ds.name = 'MyFSGroup'

Even commenting out the ds.name filter, I get zero records back.

Like this old, unanswered question, I ran this query:

  exec sp_helpfilegroup 'MyFSGroup'

and got back:

groupname    groupid    filecount
---------    -------    ---------
MyFSGroup    2          0

I've Googled all over for solutions and workarounds.  So far as I can tell, there is no table anywhere in the database that is still using this file - but I still cannot drop it.  I have tried restarting the SQL service, too, but it's not to be fooled.

How do I find out what is still using the filestream file, and how do I drop it?

EDIT: ooh, here's a thought: the database has "Full" recovery model.  Could that have something to do with it?


texts:
How does this work? If I specify an address list, I know I can specify a load balance or failover option to switch the connection to the other address.

But in case of various descriptors (DESCRIPTION {...}) what's happening? Is it a random choice from the connector?


texts: SQL Server Database Design for Reporting

texts:
We are designing a reporting solution for survey results. Although datasets are reasonable in size (rarely more than 500.000 respondents and 50 questions), performance is obviously a major concern.

Due to the nature of the solution, most queries return aggregated values and no locks are needed.

Storing answers in a "normal" tabular format (i.e. a column for each question and a row for each respondent) works well in terms of performance, and allows us to query the data like so:

SELECT COUNT(*) FROM Answers WHERE Gender = 'M' and Age < 20

However, this design requires a new table for each survey as the questions (columns) differ, which is obviously not an ideal solution.

Therefore, we are considering a design where we store answer data in a table that would basically just hold a respondent ID, a question ID and an answer value, thereby "transposing" the data (i.e. there would be a row for each respondent/question combination in the Answers table).

In this design, we would have to use exists conditions (or joins) to filter our data, e.g:

SELECT COUNT(*)
FROM Answers AS A1
WHERE A1.QuestionID = 'Gender' AND A1.VALUE = 'M' AND EXISTS
(
    SELECT *
    FROM Answers AS A2
    WHERE A2.RespondentID = A1.RespondentID AND A2.QuestionID = 'Age' AND A2.Value > 18
)

This would allow as to handle any survey without changing the database schema but we are concerned about what the impact might be on performance?

Or perhaps there is a better way to deal with this issue altogether?


texts: High CPU usage occurs periodically on the subscriber

texts:
I have set up a merge publication and a subscriber with push subscription mode.

On the subscriber, I notice the CPU usage is going high periodically.

It may take about 40 seconds for the CPU Usage come back to 0%. And it occurs at same time as long as the Synchronization Status display : "Uploading data changes to the Publisher". This only occurs at subscriber, the publisher is normally.

I've go to  Reports > Top Queries by Total CPU time at the subscriber to see which queries make the CPU usage go high and below is the result:

I don't know what to do next to fix this issue. Does anybody have an idea on how to solve it?


texts: How to structure this data? (Many columns currently.)

texts:
Working in MariaDB, we're trying to figure out how to store this data. All of our other tables have been normalized and are working nicely, but this has me stumped.

We have a number of objects and each object needs to have a state and two colors stored for each layer. The problem is that the upper limit of layers is around a hundred. Each object needs the data stored for each layer, as the objects are definitely not guaranteed to be identical in any way.

ObjID   | L0  |      L0C      | L1  |      L1C      | ... | L99 | L99C
8374629 | mmq | FFFF00,FFFF00 | mqm | 663FBA,34CF09 | ... | mqq | CDCDCD,00CDCD
8374630 | qmq | EE9329,00FF00 | qqm | 00FF52,ACF03D | ... | qqq | FFFF00,00F000
8374631 | mqq | 597842,F98520 | mmm | 05FF10,3726CD | ... | mmm | 05FF10,C0EAFF
8374632 | qqm | FADD03,A6A6A6 | qmm | DDAA11,763276 | ... | qmm | EEEEAA,EE9329
8374649 | mmm | BC1A44,FF00FF | qqq | 000000,FFCFFF | ... | mqm | 2323DD,01CD11

ObjID is always unique, but the rows are purged periodically as the object is no longer needed from the system. Lookup will always be based on ObjID. There will never be any filtering of the other rows (ie, selecting all where L0 is mmm).

The only other idea we've come up with is the idea of using strings to store the data and breaking them apart in the code that's using the data, but I assume that parsing a 1300 character string into a massive array would be both more inefficient and very inelegant. It also wouldn't allow  easy editing of any given layer, which needs to be available by design.

The objID is referenced on another table, but otherwise the data here isn't replicated in any other location. Is this our best option for storing this many unique points? Some two hundred columns just seems like quite a lot.


texts: SQL server temp table using # - is it only accessible by the containing query for multiple connections with the same login?

texts:
Say we have 2 completely different queries, that reference a temp table with the same name:

query one
....operates on:
    #tempTableName

query two
....operates on:
    #tempTableName

I've researched this and found out that "#temp is a local temporary table, and therefore not visible to other connections", however I'm not sure what a connection really means in-depth in the context of SQL queries. Does SQL consider there to be a separate 'connection' for every separate query that is run, or is a connection shared somehow?

Specifically, my question is "what's going on if the two queries in my scenario are being accessed by the same login" e.g. multiple .NET applications are using the same .NET connection string and concurrently accessing the database. Are they then going to potentially access the same temp table if both queries in my scenario are being run concurrently? What about locks? Do I need to do anything to prevent unwanted access during operations on the table?

I've looked at this answer, https://stackoverflow.com/questions/466947/are-temporary-tables-thread-safe, however I really need something like a 'pretend I'm 6 yrs old' explanation so that I'm crystal clear on what's going on in my hypothetical scenario in terms of exactly how the SQL queries are utilising connections and determining exclusive access etc. Any diagrams or pointers to resources that can give me a complete command of this would be great!


texts: Replacing a SQL server involved in Transactional Replication with least amount of downtime

texts:
I have a physical server that the company wants to move to virtual - currently, it is a subscriber in Transactional Replication on a very large database.  What would be the best way to minimize the downtime?

Initialization from backup will still take ~ 30 minutes for the backup, 2 hours for the restore.  I do have the virtual server available to me - would it be possible to set the database that is being Replicated to Log Ship to the new virtual server (to keep it in sync), then when we get ready to do the cut over somehow just re-create the Replication and have it "pick up" without having new snapshots/etc?


texts: A better model for connecting users to different objects, even efter they cease to exist

texts:
I've got the privilege to create a small in-house application for managing TODO-lists, although I am not really a developer (I know some PHP and SQL, and got a chance to prove myself).

As my experience is not the best, I ask for some opinions/suggestions on how to improve an issue with "imaginary" and real users.

Imagine you have company sections with people working on projects, sometimes they move between sections or quit the company. If a person moves or quits, their data should not disappear or be moved from the project they worked on.

I solved this by having a jointable(?) between Users and Sections. If a user moves or quits, I can just change the relation to Users. I can also "create" users who'll never login, but are used for tracking purpose.

Everything was fine until I started adding Projects and TODOs. All users should not have access to all projects and some should be admins for a project. To solve that I added yet another "jointable" (Profile).

That solution seems to work but feels clumsy, and I am curious to know if there is a better solution to model it.

I supply my current model to give a clearer picture (still working on it, like assigning users to TODO).

-- this table is readonly and updated using "triggers" from another database
CREATE TABLE "users" (
 "id" integer NOT NULL PRIMARY KEY,
 "email" varchar(32) NOT NULL,
 "firstname" varchar(64) NOT NULL,
 "lastname" varchar(64) NOT NULL
);
-- section within a company
CREATE TABLE "section" (
 "id" integer NOT NULL PRIMARY KEY,
 "name" varchar(64) NOT NULL
);
-- section have members, which can only belong to single section
CREATE TABLE "member" (
 "id" integer NOT NULL PRIMARY KEY,
 "section_id" integer NOT NULL REFERENCES "section" ("id"),
 -- NULL is permitted, because the member may not have an account
 "user_id" integer REFERENCES "users" ("id"),
 -- "email" is used as identifier if there is not account
 "email" varchar(254) NOT NULL
);
-- each section have project, but not shared between sections
CREATE TABLE "project" (
 "id" integer NOT NULL PRIMARY KEY,
 "section_id" integer NOT NULL REFERENCES "section" ("id"),
 "name" varchar(128) NOT NULL,
);
-- to connect a member to a project, they have a profile
CREATE TABLE "profile" (
 "id" integer NOT NULL PRIMARY KEY,
 "project_id" integer NOT NULL REFERENCES "project" ("id"),
 "member_id" integer NOT NULL REFERENCES "member" ("id"),
 "isadmin" bool NOT NULL
);
-- todo's within a project. actually would like to "assign"
-- people to it, but havent figured out best method yet
CREATE TABLE "todo" (
 "id" integer NOT NULL PRIMARY KEY,
 "project_id" integer NOT NULL REFERENCES "project" ("id"),
 "complete" bool NOT NULL,
 "title" varchar(128) NOT NULL,
 "datewhendone" datetime
);


texts:
I'm new to SQL but happen to be dealing with a couple of rather larger table (on the order of a billion rows each).

I want to do an inner join like the following:

SELECT *
FROM XTABLE X
   INNER JOIN YTABLE Y
      ON Y.date=X.date
      AND Y.symbol=X.symbol
WHERE X.date > 2011
   AND X.symbol in ('a','b','c')
   AND Y.symbol in ('a','b','c')
   AND Y.date > 2011

My question is:  What will the order of operations be for a query like this?  Will SQL make a new result set for rows with symbols{a,b,c} and dates{>2011} for each table X and Y and then do the inner join?


texts: How can I convert a string to a double precision in PostgreSQL?

texts:
How can I convert a string to a double precision in PostgreSQL ?

I tried something like :

update points set latitude2 = cast(latitude as double) ;

where latitude is a string and latitude2 is a double. I just can't get it to work.


texts:
I want to select an alias based on specific column criteria and output the required XML.

Say we have a table named ACCOUNTS:

Name       Age
---------  ---
John Doe   30
Mary Lane  40
Alice Bob  50

Query:

;WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 'uri' AS def
)
SELECT
     CASE WHEN AGE <= 30 THEN 'BUYER' AS [def:GIRL]
          WHEN AGE >=40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH('abc:OilCompany'), TYPE

The output should be:

 <abc:OilCompany>
   <def:GIRL>BUYER</def:GIRL>
   <def:BOY>SELLER</def:BOY>
   <def:BOY>SELLER</def:BOY>
 </abc:OilCompany>


texts:
I have a problem with this query:

SELECT @dateforaftersixmonth=U_PYMON FROM [@CCS_PYRUNPERIODS]
    WHERE @STRTEMP_DATE between U_BEGDA and U_ENDDA

@STRTEMP_DATE is a datetime variable and @dateforaftersixmonth is a string. U_BEGDA and U_ENDDA are datetime fields and U_PYMON is varchar.

But when I execute this query I get:

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

And when I execute this query separately I can't find any error and I got the answer. Here @STRTEMP_DATE comes is 'Mar  1 2013 12:00AM'.


texts: How are joins Commutative and Associative?

texts:
I have read all over the place that joins are associative and commutative.

So A join (B join C) should be the same as (A join C) join B.

But I have a really hard time understanding how this can be so. Assume that A has a property in common with B and B has a property in common with C, but A and C share no common properties to join on.

It seems that in that case, in the second instance (A join C) join B, when the operation took place to join A and C it would result in an empty set, making the join with B impossible.

Whereas in the first case it would work because B join C would result in the expected joined table that would have a property to join with A.


texts: How to fix ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER error

texts:
I'm getting below error while executing PS script

Error Code: ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER
More Information: The SQL provider cannot run because of a missing dependency. Please make sure that Microsoft SQL Server Transact-SQL ScriptDom is installed.  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SCRIPTDOM_NEEDED_FOR_SQL_PROVIDER.

For this Fix I followed POST and did whatever suggested but no luck. Any idea?

You can find more detail on this issue on This Post


texts:
I want to backup postgresql data,
right now I use disable insert,update,delete in my application level, become readonly mode.

But I'm wondering what happen if insert,update,delete during dump database,
how to prevent it with postgresql function?
does dump automatic lock(insert,update,delete) table,
or is there any method do this, lock(insert,update,delete) and dump in same query?

my dump query:
pg_dump -U postgres -f backup/dbname.sql dbname


texts: how to select rows which are mapped to a single record in a one to many relationship?

texts:
I have two tables, consumer and user. Consumer table has a foreign key for user table,say user_id mapped to user id in user table.
There are some rows in consumer table which have one to one mapping with user table while some rows are mapped to same user id in user table.
I want to select only those rows from consumer table where more than one record is mapped to a same user id.


texts: String that contains letters representing a negative number

texts:
I would like for every string that has a letter on the end make it a negative value.

<field> = '00000000014545p';--Varchar(15)
<field> = '00000000012645x';--Varchar(15)
<field> = '00000000012345p';--Varchar(15)
<field> = '000000004512345';--Varchar(15)

SELECT
    SUM(CAST(<field> AS int))

FROM <table> WHERE <field> IN (38857,45678)

I want a return the sum of the fields.

Update: Found out that this was programmed using "over punch dibol ascii" This is why the letters are in this. p=0,q=1,r=2,s=3,t=4,u=5,v=6,w=7,x=8,y=9
Letter means *-1


texts: In PostgreSQL, how do I show the database privileges granted to public in postgresql?

texts:
Below are the database privileges after I created a brand new database "foobar".

\l+
                                                    List of databases
  Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |   Size    | Tablespace |                Description
----------+----------+----------+-------------+-------------+-----------------------+-----------+------------+--------------------------------------------
foobar    | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7233 kB   | pg_default |
postgres  | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7343 kB   | pg_default | default administrative connection database
template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin          +| 7233 kB   | pg_default | unmodifiable empty database
          |          |          |             |             | rdsadmin=CTc/rdsadmin |           |            |
template1 | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/myuser            +| 7233 kB   | pg_default | default template for new databases
          |          |          |             |             | myuser=CTc/myuser     |           |            |

It has no access privileges listed, but I am able to connect to it with any user because apparently public is granted connect access to all databases, as shown in this question. After revoking connect from public on foobar, it shows the (accurate?) access privs.

 foobar    | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =T/myuser            +| 7233 kB   | pg_default |
           |          |          |             |             | myuser=CTc/myuser     |           |            |

I assume this is done to make the output of \l+ commands less verbose. I noticed that a similar thing happens with \du, when nothing is shown when a role has LOGIN but does show something for NOLOGIN.

Without full privileges listed explicitly, it is hard for a beginner like me to figure out what the 'effective' privileges is.

So, question: how do I get commands like \l+ to show the complete set of privileges?


texts:
The scenario is that developers used a linked server object on ServerM to reach ServerST. This linked server was setup as a "SQL Server" type using a domain login that exists as a login on ServerST.

Now the developers require this linked server object (named ServerST) to be reconfigured to point at ServerAR (even though confusingly the object would still be named ServerST). This allows them to save time rewriting a lot of code, time they do not have.

Now I can create a new linked server on ServerM of the type "Other data source" and provide a connection string that works. However a query on the original linked server that took 5s took 46s using the new linked server.

So my question is how do I provide a linked server named ServerST, that points to ServerAR and operates at the same speed.

All servers are on SQL Server 2008 R2 SP2.

The code I currently use to create the slow linked server is:

sp_addlinkedserver 'ServerST', ' ', 'MSDASQL', '', '',
    'Driver={SQL Server};Server=ServerST;Database=theDB;Uid=link_ServerM;Pwd={P@ssw0rd};'


texts: Performance issues on a MySQL database with 150 million rows

texts:
Current setup:

A managed vps server
InnoDB storage for big tables
10.0.36-MariaDB
18gb ram
8 x 2.40 GHz CPU.

I have a pretty big MariaDB database table which is used for Business intelligence analytics data. The current table data fluctuates from 150-180 million rows depending on data that is being imported.

The database is filled through a java file that was custom written, and the data is being read through a custom PHP api, through a method that uses the table indexes. The data is date separated and currently the table holds around 2-3 years of data.

My problem is that whenever we see an error with the data we usually need to re-import large volumes of data going back a long time. Meaning tens of thousands of report files that each is being consumed by the java file and imported one by one.

I've done some speed optimisation to the import process, and a single report doesn't take long, unless the report is 300mb or so - then it takes around 10-15 minutes depending on the busyness of the database at that moment.

The daily report import usually has 200-400 files and it takes an hour in the early morning when no-ones in the office. It's all ran by cronjobs and custom report downloading scripts from various sources.

The daily imports and data fetches are pretty fast - however when one wants to make let's say a years data fetch where every day would be visible in the graph for one metric - it can take 10-15 minutes or longer if multiple people are trying to do the same, or if something big is being imported

The data that is being fetched is not a single query, I separate every day as a separate request - that sped up the fetches 10 times or more, depending on how long the time period is.

In a perfect world I'd like to optimize the structure or any settings I could so I could import the data faster and fetch longer timeframe data faster. Our current approach is to have a single database separated for analytics data, with a couple of helper tables.

What kind of optimisation could I do for this amount of data, knowing that we grow around 5-8 million rows per month (that could increase)?


texts: Optimize SQL query in form of a Procedure

texts:
I have a Oracle SQL query with multiple "IN" statements. It takes around a minute to execute in an average.

My task is to optimize this query in form of PL/SQL procedure so that the user gets the output resultset as a ref cursor.

Please suggest. Thanks!

SQL Query:

select sp.column1, sp.column2,sp.column3,gen.column1,nvl(gen.column2,0)+nvl(gen.column3,0) GENSIZE,gen.column4,gen.column5
from table1 sp,
(select * from table2 where table2.column1 in (
select table1.column4 from table1 where table1.column2 in (
select table3.column1 from table3 where table3.column2='124578'))) gen
where gen.id=sp.id;


texts: Joining Two Rows (single table) to Single Row, Multiple columns

texts:
Is there any way to JOIN two rows from a single table to a single row, based on two columns.

I built a scrappy little data base for my business, and I finally ran into a situation where I thought I had built in the necessary extensibility. Unfortunately, I found out pretty fast that I can't use the JOIN as I was before now that I need to join info from two columns to a single row (previously, I was only joining on one column).

Here is what I have:

| Name |    ID   |    ID_2 |
|----- |---------| --------|
| Jake |    a    |    b    |
| Jane |    b    |    c    |
| John |    c    |    NULL |

ID   | Info
-----------
a    |  1
b    |  3
c    |  5

And here is what I'm looking for:

Name | Info_1  |    Info_2
--------------------------
Jake |    1    |    3
Jane |    3    |    5
John |    5    |    NULL

Or, better yet, this:

Name | Info_Sum
---------------
Jake |    4
Jane |    8
John |    5


texts: Initializing Associative Array in PL/SQL

texts:
Using PL/SQL, suppose we've declared a nested table type and its instance as follows:

  type nested_tab is table of pls_integer;
  tab nested_tab;.

We are then able to initialize our nested table as follows:

  tab := nested_tab(1, 2, 3);.

Is there something similar we can do to initialize an associative array?

In other words, for the following associate array:

  type associative_tab is table of varchar2(100) index by pls_integer;
  a_tab associative_tab;

is there a shorter way than this:

  a_tab(1) := 'hello';
  a_tab(2) := 'world';
  ...
  a_tab(100) := '100th string';

for intialization?


texts:
I ran into a query today that did something I haven't seen before. It did something like this:

SELECT
    MIN(+ - schema_id) AS [WhatInTheWorld?],
    MIN(schema_id) AS MinimumID,
    MAX(+ - schema_id) AS [Uhhh],
    MAX(schema_id) AS MaxID
FROM sys.objects

Which gives the following results:

WhatInTheWorld? MinimumID    Uhhh       MaxID
-4              1            -1         4

What exactly are the "+" and "-" doing?


texts: How to accomplish local joins between facts and dimensions?

texts:
A fact table typically contains a composite primary key made up of the keys to all the dimensions that define its granularity.  if we distribute the table by this key, it cannot possibly accomplish a local join to any of the dimensions to which it is bound because those tables are distributed by their own PKs, which won't match the hash generated for the fact.

I'm guessing that if dimensions are small it won't matter much, but if the dimension is large e.g. a security master table with millions of CUSIPs and other identifiers, how can local joins be accomplished?

what is the standard approach to selecting a distribution key for facts?


texts: How to search hyphenated words in PostgreSQL full text search?

texts:
I have to search for hyphenated words like 'good-morning', 'good-evening', etc.

My query is:

select id, ts_headline(content,
                       to_tsquery('english','good-morning'),
                       'HighlightAll=true MaxFragments=100 FragmentDelimiter=$')
from table
where ts_content @@ to_tsquery('english','good-morning');

When executing this query I also get results of 'good' and 'morning' separately. But I want exactly matching words and fragments.
(For ts_content I used the same default config english to create the tsvector.)

How can I search such hyphenated words in PostgreSQL full text search?


texts: SQL Server Linked Server to Progress is slow with an openquery view

texts:
We have a SQL Server database setup with a Linked Server setup connecting to a Progress OpenEdge database.  We created a SQL Server view (for using with SSRS) of some of the OpenEdge tables using code similar to the following:

CREATE VIEW accounts AS SELECT * FROM OPENQUERY(myLinkedServerName,
'SELECT * FROM PUB.accounts')

CREATE VIEW clients AS SELECT * FROM OPENQUERY(myLinkedServerName,
'SELECT * FROM PUB.clients')

For some reason the queries seem to bring back the whole table and then filter on the SQL side instead of executing the query on the Progress side.  I'm a dev and not a DBA so I'm hoping the experts can hook me up with a solution or at least an explanation as to why.

Thanks.


texts: In sql server replication when stored procedures get replicated, it creates MSins, MSdel and MSupd procedures for all tables in the database

texts: I have single sql instance and I have created replication of my database in same instance. Now when I have inserted data into the tables I found out that replication of insert stored procedure is getting created in the replica database. This happens for delete and update also. So now my replica database has MSins, MSupd and MSdel for all of my tables available in database. How to stop this?


texts:
Using SQL Server 2008, I can query sysmail_log and other tables to see the results of sql emails.

Is anything remotely similar available for querying email logs in SQL Server 2000?


texts: Discovering databases with specific table on a PostgreSQL server

texts:
Is there a way for a non-admin PostgreSQL user to discover which databases it can access on the server it is connected to, more specifically which of these databases have a table with a specific name that the user has enough permissions to see (and possibly query)?

As far as I can tell, the MySQL equivalent would be something like:

SELECT t.TABLE_SCHEMA FROM information_schema.`TABLES` AS t
    WHERE t.TABLE_NAME = 'the_table_name'

(This would be for PostgreSQL 8.4 preferably, but I'm also interested in solutions for 9.1 if this has changed.)


texts: How to remote access Oracle 12c using SQL Developer?

texts:
The listener has started correctly and its status shows OK!

I enter the correct IP address of the server in the listener.ora file, not localhost.

But when I try to connect to my database by entering the following in SQL Developer:

user: system
password: *******

host: 54.215.5.46
port: 1521
SID: orcl

I get this error:

  Statut : échec -Echec du test : Erreur d'E/S: The Network Adapter could not establish the connection

I think that I need to open the 1521 port but I don't know if this is the problem?


texts: Block insertions on a mysql table with specific value

texts:
I've got a simple question:
I was wondering if it is possible to (temporary or permanent if possible) block the insertion of a record into a table with a specific value for a specific column. I'm using mysql with InnoDB

So I have this table Cart, which contains some columns and one of them is CartID (it is not the primary key thou). And I would like to block insertions in the table Cart with a specific CartID (let's say 100), while I'm doing work on another table in the DB.


texts: Is it possible to write a select * view that includes new columns added to table?

texts:
I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them.

If I do the following:

create database viewtest;
\c viewtest
create table a (x text);
create view b as select * from a;
alter table a add y text;
select * from b;
 x
---
(0 rows)

select * from a;
 x | y
---+---
(0 rows)

\d+ b
                  View "public.b"
 Column | Type | Modifiers | Storage  | Description
--------+------+-----------+----------+-------------
 x      | text |           | extended |
View definition:
 SELECT a.x
   FROM a;

I want to essentially create a view where the definition includes * rather than an explicit list of the columns in the table when I created the view. Maybe I'm crazy, but this seems like a practical use case and I can't figure out how to do it for the life of me.


texts: How do I write a hex literal in PostgreSQL?

texts:
How do you write a hex literal in PostgreSQL? Like say I want 0xCC, if I do;

SELECT 0xCC;
 xcc
-----
   0
(1 row)

So PostgreSQL is parsing the xcc as an alias.


texts:
I am having issues trying to upgrade my mariadb.....

when i do apt-get update && apt-get upgrade i get the following error

Setting up mariadb-server-5.5 (5.5.47+maria-1~trusty) ...
 * Stopping MariaDB database server mysqld                               [ OK ]
 * Starting MariaDB database server mysqld                               [fail]
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing package mariadb-server-5.5 (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mariadb-server:
 mariadb-server depends on mariadb-server-5.5 (= 5.5.47+maria-1~trusty); however                                                                                :
  Package mariadb-server-5.5 is not configured yet.

dpkg: error processing package mariadb-server (--configure):
 dependency problems - leaving unconfigured
No apport report written because the error message indicates its a followup erro                                                                                r from a previous failure.
                          Errors were encountered while processing:
 mariadb-server-5.5
 mariadb-server
E: Sub-process /usr/bin/dpkg returned an error code (1)


texts: How can you have more used_pages than total_pages when looking at sys.allocation_units?


texts: I am from SQL Server background and recently getting my hands dirty with Oracle 12c. I came across few features of Oracle called multi-tenant, Container Database and Pluggbale database. Can somebody tell me what are the direct equivalents of these features in SQL Server 2012?


texts: How To Grant View Definition of Object in SQL Azure

texts:
The tsql syntax throws an error about "view" in SQL Azure, but functions on SQL Server:

GRANT VIEW ON OBJECT dbo::foo TO [bar]

Can access be granted in SQL Azure to view the definition of an object outside of db_owner?


texts:
At our financial company, we have different OLTP transactions for customers. We have a transaction table (debits, credits) and balance table.
We will only have 1000 transactions per month.

What is the best way to have account balance table?

1) One person wanted to use an indexed view, since we have relatively static data.

2) I want to calculate account balance right in sql code. I did not want to deal with issue of queries sometimes not using indexed view in execution plans, deadlocks during update, complicated execution plans when updating/delete data etc. Prefer indexed views for reporting/Olap databases.

Is there a correct answer for concurrency transactional databases? Any issues to watch out for using indexed views in small transactional oltp databases?

create table dbo.AccountTransaction
( AccountTransactionId int primary key identity(1,1),
  CustomerId int null,
  TransactionAmount money not null )

create table dbo.AccountBalance
( AccountBalanceId int primary key identity(1,1),
  CustomerId int null,
  BalanceAmount money null )

SQL Calculation Method:

begin transaction
    insert into AccountTransaction
    values(@CustomerId, @TransactionAmount)

    update AccountBalance
    set BalanceAmount = BalanceAmount + TransactionAmount
    where CustomerId = @CustomerId

end transaction

Indexed View Method:

create view dbo.AccountBalanceVw
with schemabinding
as
select
    customerid,
    sum(transactionamount) as BalanceAmount,
    COUNT_BIG(*) AS CountLines
from dbo.AccountTransaction
group by customerid

create unique clustered index cx_AccountBalanceVw on AccountBalanceVw(CustomerID);


texts:
I got the following column:

full_path "gorfs"."absolute_pathname"

It keeps the path of a BLOB, like on the example below:

Note that the 4865 is the account_id.The path is always like: /userfiles/account/ACCOUNTID

I need to extract that, to use in another query. For example:

select * from gorfs.seg WHERE account_id = 4865

How can I extract that value from the full_path column?


texts: How to extract the last inserted row in SQL Server?

texts: When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?


texts: After piping a file with SQL statements to mysql, how can I keep the command line session open?

texts:
A file, in.sql, contains a CREATE TEMPORARY TABLE statement followed by some INSERTs that populate the above table. Temporary tables are purged after the session closes, so 'mysql -u user -p pass < in.sql' has no net effect once the mysql process is done with in.sql and terminates. I would like to keep the session open to continue working on the temporary table interactively. Does such an option exist?
(A simple workaround, of course, would be to create a non-temporary table and drop it when I'm done with it)


texts:
In MSSQL I'm trying to return the number of rows where the count of a certain activity exceeds X and has occurred more then y number of times in a month.

This query tells me how many times on a given day for the current month the activity has occurred.

SELECT COUNT(DATEPART(DAY,myDATETIME))as dayCount,DATEPART(DAY,myDATETIME)as theDAY, Activity

FROM ALL_Activities

WHERE DATEPART(YEAR,myDATETIME)= DATEPART(YEAR,GETDATE())

AND DATEPART(MONTH,myDATETIME)= DATEPART(MONTH,GETDATE())

AND Activity = 'The door opened'

GROUP BY (DATEPART(DAY,myDATETIME)), Activity

HAVING COUNT(DATEPART(DAY,myDATETIME)) > 0 AND ???????

dayCount, theDAY, Activity

3   19  The door opened

7   18  The door opened

7   17  The door opened

1   15  The door opened

2   14  The door opened

1   13  The door opened

5   12  The door opened

1   11  The door opened

10  10  The door opened

4   9   The door opened

1   8   The door opened

2   7   The door opened

What I'm trying to end up with is a query that returns the activity when it has happened at least X times a day more then Y times a month such that A single row matching the criteria returned would have the total number of occurrences, the total number of days, and the activity. Is it possible to do this strictly within SQL?

44, 12, The door opened

If not that, then even the rows that meet the criteria would help. Meaning maybe return the rows when the number of days in the given month is >=5 where the times per day >=1. I tried using some combination of sub queries, @@ROWCOUNT, and other things, but couldn't get anything to work.


texts: zero value cpu_time and duration in sql_statement_completed extended event

texts:
On Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64)
   Enterprise Edition (64-bit) on Windows NT 6.1
            (Build 7601: Service Pack 1) (Hypervisor)

almost all events have a zero value in cpu_time and duration fields (in 1/1000000 second) in sql_statement_completed extended event. Yet statement are not so simple, for example :

UPDATE <table> SET <field> = Value, etc...
FROM (SELECT <fields> WHERE <condition>) AS t1
WHERE <condition>

And sporadically some have thousands values like 15000 or 16000, with same kind of statement...

How is it possible that it take 0 microsecond to do anything ? Or is it a bug/limitation in extended events ?

EDIT :

here is the XE create statement :

CREATE EVENT SESSION [XE_sql_statement_completed] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    SET collect_statement=(0)
    ACTION(package0.collect_current_thread_id,
        package0.event_sequence,sqlserver.client_app_name,
        sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,
        sqlserver.nt_username,sqlserver.server_principal_name,
        sqlserver.session_id,sqlserver.session_nt_username,
        sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.histogram(
                 SET filtering_event_name=N'sqlserver.sql_statement_completed',
                    source=N'sqlserver.database_name'),
ADD TARGET package0.ring_buffer(SET max_events_limit=(400))
WITH  (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=NO_EVENT_LOSS,
MAX_DISPATCH_LATENCY=3 SECONDS, MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

So I fetch the data in the ring buffer


texts:
I got an issue regarding job stats. So I have a job with the following schedule: Occurs every day every 5 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 12/13/2017.

in 11:27 AM 2019-10-20 I ran two scripts to get last_run_time and next_run_time, below are the scripts and their corresponding results:

USE msdb
GO
SELECT J.Name                     AS 'Job Name'
         ,'Job Enabled' =
          CASE J.Enabled
                 WHEN 1 THEN 'Yes'
                 WHEN 0 THEN 'No'
          END
         ,STUFF(
          STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
          , 8, 0, '-')                   AS 'Job Schedule Start Date'
         ,STUFF(
          STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
          , 8, 0, '-')                   AS 'Job Schedule End Date'
         ,'Job Frequency' =
          CASE S.freq_type
                 WHEN 1 THEN 'Once'
                 WHEN 4 THEN 'Daily'
                 WHEN 8 THEN 'Weekly'
                 WHEN 16 THEN 'Monthly'
                 WHEN 32 THEN 'Monthly relative'
                 WHEN 64 THEN 'When SQLServer Agent starts'
          END
         ,'Last Run Date' = CONVERT(DATETIME, RTRIM(LASTRUN.run_date) + ' '
          + STUFF(STUFF(REPLACE(STR(RTRIM(LASTRUN.run_time), 6, 0),
          ' ', '0'), 3, 0, ':'), 6, 0, ':'))
         , 'Last Run Status' =
          CASE
                 WHEN LASTRUN.run_status = 0 THEN 'Failed'
                 WHEN LASTRUN.run_status = 1 THEN 'Succeeded'
                 WHEN LASTRUN.run_status = 2 THEN 'Retry'
                 WHEN LASTRUN.run_status = 3 THEN 'Cancelled'
                 ELSE 'Unknown'
          END
         ,'Last Run Message' = LASTRUN.message
FROM dbo.sysjobs J
LEFT OUTER JOIN dbo.sysjobschedules JS
       ON J.job_id = JS.job_id
LEFT OUTER JOIN dbo.sysschedules S
       ON JS.schedule_id = S.schedule_id
LEFT OUTER JOIN (SELECT
              job_id
          ,MAX(run_duration) AS RUN_DURATION
       FROM dbo.sysjobhistory
       GROUP BY job_id) MAXDUR
       ON J.job_id = MAXDUR.job_id
LEFT OUTER JOIN (SELECT
              J1.job_id
          ,J1.RUN_DURATION
          ,J1.run_date
          ,J1.run_time
          ,J1.message
          ,J1.run_status
       FROM dbo.sysjobhistory J1
       WHERE instance_id = (SELECT
                     MAX(instance_id)
              FROM dbo.sysjobhistory J2
              WHERE J2.job_id = J1.job_id)) LASTRUN
       ON J.job_id = LASTRUN.job_id
ORDER BY 'Job Name'

use msdb
go
Select [job name] = jb.name
    , [schedule name] = sc.name
    , [next run date] = js.next_run_date
    , [next run time] = js.next_run_time
From dbo.sysjobs as jb
Inner Join dbo.sysjobschedules as js on js.job_id = jb.job_id
Inner Join dbo.sysschedules as sc on js.schedule_id = sc.schedule_id

So, according to the pictures next_run_date should be 11:30 AM instead of 11:15 AM. Have you ever faced with this kind of phenomenon? Could you please help me understand this?


texts: Add an additional listener to Oracle Database Appliance

texts:
After deployment of Oracle Database Appliance with OAK 2.3.0 the standard listeners are configured for the public network on bond0. That includes SCAN listeners and one VIP listener per node. This last one seems to listen on both the public network interface and the VIP interface.

However, I would also like something to listen on the management network that I have configured at bond1.

What would be the preferred way of accomplishing this? Can I make the VIP listener also listen to bond1 or must I add a new listener for this?

Can I just add a listener with:  srvctl add listener -p TCP:1521 -o /u01/app/11.2.0.3/grid?

I'm at a loss to see where the ip address or interface goes into the configuration.

Update:
Support note 1063571.1 "How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure" covers the addition of a second production listener with associated VIP interfaces (but no second SCAN listener) to a generic 11gR2 RAC.

In this case, as this is meant to be a back door for operators and DBAs, I think it might be OK to have a basic listener directly on the physical interface on each node - eg. one that does not fail over, and only connects to the instances on that specific node.

I suppose there could also be ODA specific considerations that need to be taken into account, although none are too clear to me at this point.


texts: How to combine a parent with all children in the same row

texts:
SQL Statement Question: I have two tables PARENT and STUDENTS.

PARENT Table

ParentID | FName | LName | StudentID
---------+-------+-------+----------
       1 | John  | Doe   |        11
       1 | John  | Doe   |        12
       2 | Jane  | Doe   |        11
       2 | Jane  | Doe   |        12
       3 | Joe   | Dirt  |        13

STUDENT Table

StudentID | FName   | LName
----------+---------+------
       11 | Janet   | Doe
       12 | Jackson | Doe
       13 | Jane    | Dirt

Expected Outuput

ParentID | FName | LName | StudentID1 | Student1FName | StudentID2 | Student2FName |...
---------+-------+-------+------------+---------------+------------+---------------+---
       1 | John  | Doe   |         11 | Janet         |         12 | Jackson
       2 | Jane  | Doe   |         11 | Janet         |         12 | Jackson
       3 | Joe   | Dirt  |         13 | Jane          |       NULL | NULL

I would like to have the output of the two tables, but sometimes the parent might have more than two students and some might only have one.

How can I achieve the expected result set while still being flexible in the number of students per parent?

Note: I don't have a definite upper limit on the number of students per parent.


texts: Postgres reduce by key if key is not null

texts:
I need to select N rows with seq > X which reduce_key is null and for each row group with the same reduce_key select the one with the biggest seq. Result should be strictly sequential (but with rows with the same reduce_key filtered except the one with biggest seq) and limited with N.

Here is a query which does that:

explain analyze SELECT
    x2."user_id",
    x2."seq",
    x2."timestamp",
    x2."reduce_key",
    x2."mapping"
FROM
    "user_sequence" x2
WHERE
    (
        (x2."user_id" = 860862404)
        AND (x2."seq" > 3562974)
    )
AND (
    (x2."reduce_key" IS NULL)
    OR (
        x2."seq" IN (
            SELECT
                MAX ("seq")
            FROM
                "user_sequence"
            WHERE
                ("user_id" = 860862404)
            AND (
                ("seq" >= x2."seq")
                AND ("reduce_key" = x2."reduce_key")
            )
            GROUP BY
                "reduce_key"
        )
    )
)
ORDER BY
    x2."seq"
LIMIT 1000

The problem is that it's being executed ~70ms on table with tens millions of rows. Is there any more efficient way to reduce rows by key leaving rows with null key?

Table structure:

actor=# \d user_sequence;
   Table "public.user_sequence"
   Column   |  Type   | Modifiers
------------+---------+-----------
 user_id    | integer | not null
 seq        | integer | not null
 timestamp  | bigint  | not null
 mapping    | bytea   | not null
 reduce_key | text    |
Indexes:
    "user_sequence_pkey" PRIMARY KEY, btree (user_id, seq)
    "user_sequence_user_id_reduce_key_seq_idx" btree (user_id, reduce_key, seq)

EXPLAIN ANALYZE:

 Limit  (cost=0.57..231389.06 rows=1000 width=103) (actual time=0.408..74.754 rows=308 loops=1)
   ->  Index Scan using user_sequence_pkey on user_sequence x2  (cost=0.57..24444343.74 rows=105642 width=103) (act
ual time=0.406..74.645 rows=308 loops=1)
         Index Cond: ((user_id = 860862404) AND (seq > 3562974))
         Filter: ((reduce_key IS NULL) OR (SubPlan 1))
         Rows Removed by Filter: 692
         SubPlan 1
           ->  GroupAggregate  (cost=0.57..447.59 rows=1 width=33) (actual time=0.096..0.097 rows=1 loops=730)
                 Group Key: user_sequence.reduce_key
                 ->  Index Only Scan using user_sequence_user_id_reduce_key_seq_idx on user_sequence  (cost=0.57..4
47.03 rows=110 width=33) (actual time=0.036..0.081 rows=68 loops=730)
                       Index Cond: ((user_id = 860862404) AND (reduce_key = x2.reduce_key) AND (seq >= x2.seq))
                       Heap Fetches: 49371
 Planning time: 0.808 ms
 Execution time: 74.890 ms
(13 rows)

I think that problem is with filter but how to make it use index?

I am using PostgreSQL 9.4.


texts: Tempdb. There is no enough space on the disc encountered

texts:
Does anyone know what really happened here?

All of the sudden database was not accessible.

How to find transactions that cause tempdb filled up?

Seems like it happened right after SQL profiler trace has stopped. Can this cause the issue?


texts: TSQL - Intermediate results returned by User Defined Functions (UDF) and used within Inline Table Valued Function (iTVF)

texts:
I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical functions (from result data viewpoints) and I am putting them in order of creation scripts:

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE
RETURN SELECT dbo.usernames(@user_id))
END
GO

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE
AS
RETURN
(SELECT name = SELECT dbo.usernames(@user_id))
GO

-- dbo.usernames is a simple scalar function which is defined after both the functions above

This is how I create all the functions for my DB:

I drop all the functions from sysobjects
I recreate all the functions again.
"the usernames() function is created after both of the above".

When I try to execute all the function creation script - it seems that my iTVF creation fails because it reports that usernames() function isn't there (which is expected because the objects are dropped, as I mentioned above).

Msg 4121, Level 16, State 1, Server XXXXXXXXX, Procedure get_names_itvf, Line 5
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.usernames", or the name is ambiguous.

But for the UDF function it doesn't run into this error.

Is this because how the inline TVF sees the internal results? Or is it how the dependencies are resolved for UDF and iTVF?

I am not sure why this is happening so it would be good to get some guidance/suggestion.

KR,


texts: Can Oracle to SQL CDC be implemented on the same Oracle table to 2 different SQL instances?

texts: I want to distribute Oracle data changes to the same table name in 2 different SQL instances, on 2 different SQL servers. Can Oracle to SQL CDC be implemented on the same Oracle table to 2 different SQL servers/instances?  Going 1 step further, are the LSN transactions able to be distributed independently from source Oracle tables to 2 different SQL servers?


texts: Give privilege to new created databases in MySQL

texts: I have user "U" who I want to give him full access to all the existing databases except "db1". I want him to only have select privilege for that db. What I did is I gave "U" all permissions for each db one by one and select permission on "db1". What I want to do is to give "U", the create database privilege and full access to any created database by him. Is there anyway that I could do it? I don't want to add privileges every time someone creates a db.


texts: How to downgrade SQL Server 2012 Enterprise to 2012 Express?

texts:
I would like to say that as per company policy we are downgrading SQL Server 2012 Enterprise to SQL Server 2012 Express.

Any suggestion would be appreciated.


texts: What's the easiest way to move tables with identity key columns while keeping the referential integrity between them?

texts:
I have four tables in database A that have identity key columns and have foreign key references to each other on those columns (without actual foreign key objects created, just the columns themselves).

I need to move all the data from these tables to correlating (almost) identical tables in database B. (There's already data in the tables in database B too, so I'm trying to append this data.)

How can I move the data to database B which will generate new key values for each table's records but without disrupting the referential integrity.


texts: how do I execute a void-returning procedure in pgsql 9.1?

texts:
If I execute the following in a file with the pgsql command, it complains that the procedure needs to be called with 'perform'. But when I try to use perform, it tells me that perform is not defined. What's the solution?

create or replace function waitris() returns void as
$$
declare
    cnt integer;
begin
    loop
        select count(*) into cnt from taskflag where taskid = 'rdfdb' and state != 2;
        if cnt = 0 then
            exit;
        end if;
        select pg_sleep(1);
    end loop;
end;
$$
language plpgsql;

select waitris();


texts: How do I get a linked list to display more then just the first search item in Access?

texts:
I just feel like I'm missing something really stupid. I have a simple access database I'm using for tracking my projects. I have multiple individuals and I have multiple projects. For the project, I have a drop down and I can select the lead for the project from my list of employees. No problem. however, it only displays the first name of the person. How do I get it to display the first and last name?

Table 1: ID, firstName, lastName
Table 2: ID, lead, notes - "lead" is a long integer.

The Row Source is

SELECT [Table1].[ID], [Table1].[lastName], [Table1].[firstName], [Table1].[Position]
FROM Applicants
ORDER BY [lastName], [firstName];

On the Form, I get the drop down box. It shows the first, last name, and what position they have but once I select the person I want, it only shows the first name.

The database is far more complex then this but none of the other information is relevant.


texts: PostgreSQL cluster has two primary databases after network glitch

texts:
My test environment had network glitch over the weekend and I ended up having two Primary databases up and running in the cluster.

 ID | Name                              | Role    | Status    | Upstream | Location | Priority | Connection string
----+-----------------------------------+---------+-----------+----------+----------+----------+------------------------------------------------------------------------------------
 1  | node-1 | primary | ! running |          | default  | 100      | host=node-1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node-2 | primary | * running |          | default  | 100      | host=node-2 user=repmgr dbname=repmgr connect_timeout=2

Is there a way to demote the new Primary back to Standby and resume WAL shipping instead of perform the Standby clone?

PgSQL version: 10.7

Repmgr version: 4.3

Any help is greatly appreciated.


texts: Design table that solve a service payment or a package service payment?

texts:
I am learning and practicing some database designing and case study on Selling Services on Internet. In my requirement, user can buy a post service and add more extra option if they need. In fact, post are divided into a few type, for example, 7days, 14days and 30days post. Because of this, I create table ProductCategory and table Product.

Product Category table:

ProductCategoryID
Name

Product table:

ProductID
Name
Price
Duration
Discount
ProductCategoryID

And I also add two more table for payment and payment detail:

Payment table:

PaymentID
AccountID
PaymentDate

Payment Detail table:

PaymentDetailID
QTY
PaymentID
ProductID

I think above design may work but I am not sure if this is good or not as I still need to learn more about it. But, problem isn't stop here, the problem is that I am thinking of allow user to buy a service in form of package which will be cheaper in price. For example, user may buy a 7days post for 1$ + extra option for 1$ but if they buy a package for 5$ then they can get 5 post that displays for 14days with 5 extra option. And that package will be expired in 7days so user must use it or it will be expired.

I have add another BigPackage table as following design:

BigPackageID
ProductID1(must have product category name = package)
ProductID2(must not have product category name = package)
QTY

Doing so I saw lot of flaw and the difficulty to get available package and calculate the price they have spend. So now if anyone has any idea please kindly light my way and I do appreciate your help.


texts: Plan for managing financial transactions in an easily and retrievable manner, indefinitely

texts:
I've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the rows as financial transactions. Clients need to be able to view these rows and/or retrieve a report containing details about the transactions through an online system (going back as far as possible).

My question is this: Is it feasible to allow these rows to accumulate and simply SELECT them from the table when preparing a listing, even accounting for significant growth in volume? The traffic would be relatively low.

We've thought of implementing a clever scheme of separating the table into years; however, that would add significantly to the complexity of the project. Assuming the table was carefully optimized and indexed adequately, would the table still be fast enough at 1,000,000+ rows per year?

I hope this is not too vague or specific to warrant an answer and would like to thank those who take the time to consider responding.


texts:
I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no constraint of disk space. Which default settings should I change to optimize for better performance, reliability, and possible point-in-time backups [high availability].

Edited

At present, whenever I run "Optimize Tables" via Maintenance on MySQL Administrator, it shows:

Table does not support optimize, doing recreate + analyze instead

on all tables. All of my tables are InnoDB, but why they don't support Optimize?


texts: Table redirect / filter / trigger on select

texts:
Is there any way to redirect queries to different tables / views based on the package that's referencing the table?  I.e. packages A and B both have "select grade from schema1.grd_tbl", but I want package A to get the percent grade that's stored in the table, and package B to get a letter grade that's calculated from the percent.  I'd like to avoid modifying the (dozens of) packages that reference the table.  I'd rather 'spoof' the table somehow if we can, replacing the percent in the grade column with a letter when called from those packages. (The column's varchar2; percents are coded as characters.)

First thought was to create a synonym pointing to a view that massages the column based on the calling package, but the code fully qualifies the table name in most cases so that doesn't seem doable. I went looking for something equivalent to a trigger on select; the closest I've found is fine-grained audit, and it's not row-based.

Is there magic somewhere that might let me do this?  Any hints appreciated.

Perry.


texts:
OK I have hit an issue, now I was going to look at having two auto incrementing columns, but I cant using InnoDB.  So I am after a Solution.

I have a table called queue. Everything to do with my task queue should go into it, But each time something happens with a task I want it to write a new entry, so I can see what has been happening with my tasks. But I also Need a Primary ID for each entry, and a different key for the Tasks

So any suggestions, in how I might go about conquering this small issue.

CREATE TABLE IF NOT EXISTS `Queue` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`TaskID` int(11) NOT NULL,
`Task` varchar(255) NOT NULL,
`Client` varchar(30) NOT NULL,
`AddedInfo` varchar(255) NOT NULL,
`Status` varchar(20) NOT NULL,
`Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
);


texts: Update "NULL" string to Actual NULL value

texts:
I have a table that contains NULL values but the problem is that some of the values are actually string "NULL" and not actual NULLS so when you trying something like

where date is null

it will not return the fields that have the "NULL" string.

What I am needing to do is run an update of the whole table that will convert all string "NULLS" to the actual NULL value. The "NULL" strings happen throughout all columns of the table so it is not just 1 column that needs to be updated. I am not sure how to approach this scenario. I'm thinking I might need to use a loop since i have many columns but then again there might be a simple solution without having to use a loop. What would be the best way to resolve this issue?


texts: SQL Server 2017, XEvent: sql_text IS NOT NULL

texts:
I'm attempting to use XEvents to find out which queries are causing my templog.ldf file to grow in size.  I'm getting events, but I see the sizes of the files haven't changed, and that the sql_text field is empty.

What I'd like to do is filter my Event so that only events containing a non-null sql_text with a LEN() > 0 are logged.  However, I'm not sure how to do this.  When I add the term sqlserver.sql_text IS NOT NULL to my filter, I get syntax highlighting that something is wrong.

ACTION(...sqlserver.sql_text)
WHERE (
...AND sqlserver.sql_text IS NOT NULL
)

So how do I use something like [sqlserver].[not_equal_i_sql_unicode_string] to filter out NULLs?  And what is the difference between [sqlserver].[not_equal_sql_unicode_string] and the other variants?

Any help is greatly appreciated.

EDIT:  Below is the XEvent scrip that I used to spy on the statements that were causing my tempdb to grow.

CREATE EVENT SESSION [TempDbGrowth3] ON SERVER
ADD EVENT sqlserver.database_file_size_change(
ACTION(sqlserver.client_hostname,
sqlserver.database_id,
sqlserver.session_id,
sqlserver.sql_text)
WHERE ([database_id]=(2) AND [session_id]>(50))),
ADD EVENT sqlserver.databases_log_file_used_size_changed(
ACTION(sqlserver.client_hostname,
sqlserver.database_id,
sqlserver.session_id,
sqlserver.sql_text)
WHERE ([database_id]=(2) AND [session_id]>(50)))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\TempDbGrowth3.xel',
max_file_size=(10),max_rollover_files=(10),
metadatafile=N'C:\Temp\TempDbGrowth3.xem')
WITH (MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=1 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=ON,
STARTUP_STATE=ON)
GO


texts:
I am hoping to move to WAL backup from pg_dump. But I am confused by this note in 4.3  documentation:

  Perform the backup, using any convenient file-system-backup tool such
  as tar or cpio.

And in postgres 9.0 documentation:

  Perform the backup, using any convenient file-system-backup tool such
  as tar or cpio (not pg_dump or pg_dumpall).

So I am not allowed to run pg_dump? Or it's not allowed only for 9.x ? Filesystem dumps are useless to me as I need to restore 64-bit machine dumps on 32-bit. And I need to be able to restore separate databases.

If pg_dump is forbidden, is the only solution for me to setup slave replication and run pg_dump on slave?


texts: Cross Database Query between Azure SQL Database and SQL Server Database

texts: Can we built a Cross database query for Insert, Update and Delete between SQL Server and Azure SQL DB and vice versa?


texts: PRIMARY index not used if too few entries

texts:
this query :

EXPLAIN SELECT cmd.cmd_date, liv.liv_nom
FROM cmd
    LEFT JOIN liv USE INDEX(PRIMARY) ON cmd.liv_id = liv.liv_id
WHERE 1
ORDER BY cmd_date DESC
LIMIT 0, 20;

gives me a different result when the table liv contains 5 or 6 rows.

6 rows :

1   SIMPLE  cmd     ALL                 134331  100 Using filesort
1   SIMPLE  l       eq_ref  PRIMARY PRIMARY 4   cmd.liv_id  1   100

5 rows :

1   SIMPLE  cmd     ALL                 134331  100 Using temporary; Using filesort
1   SIMPLE  l       ALL PRIMARY             5   100 Using where; Using join buffer (Block Nested Loop)

I've read somewhere (can't find where anymore :/) that MySQL can choose to not use a primary if there is too few rows in a table (here the limit seems to be 5). Is there a way to disable this ? Because with the index the query takes 80ms, vs 4s without index :/

I'm using MySQL 5.7.9

Thanks

Here the 2 tables, simplified.

EDIT :

CREATE TABLE `liv` (
  `liv_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `liv_nom` varchar(100) NOT NULL,
  `liv_denomination` varchar(100) NOT NULL,
  `liv_public` tinyint(1) unsigned NOT NULL,
  `liv_slug` varchar(20) NOT NULL,
  `liv_ordre` tinyint(2) unsigned NOT NULL DEFAULT '99',
  `liv_id_crm` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`liv_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

create table cmd
(
    cmd_id int(10) unsigned auto_increment
        primary key,
    str_id int(10) unsigned null,
    con_id int(10) unsigned null,
    pri_id int(10) unsigned not null,
    liv_id int(10) unsigned null,
    ... 20+ more columns with data only ...
    cmd_transaction_id int(10) unsigned null,
    constraint fk_bout_commande_bout_structure1
        foreign key (str_id) references structure (str_id)
            on update cascade,
    constraint fk_crm_commande_crm_contact1
        foreign key (con_id) references contact (con_id)
            on update cascade,
    constraint fk_crm_commande_livre_campagne1
        foreign key (pri_id) references prix (pri_id)
            on update cascade,
    constraint fk_crm_commande_famille_livreur1
        foreign key (liv_id) references liv (liv_id)
            on update cascade,
);

create index fk_bout_commande_bout_structure1_idx
    on crm_commande (str_id);
create index fk_crm_commande_crm_contact1_idx
    on crm_commande (con_id);
create index fk_crm_commande_famille_livreur1_idx
    on crm_commande (liv_id);


texts: how to select the particular records with filter conditions when duplicates found in combination of two columns?

texts:
consider a table "SCHOOL" table, which contains the below data

when, duplicates found in NAME and LAST NAME combination it should look up for the STATUS which is "Active". if still it has duplicates, then look up for JOIN DATE which is most recent.

So the "SELECT" query should fetch S.NO 1,2,3,4,7.

S.NO 7 has ACTIVE status with most recent date

So could you please let me know the SQL Query to fetch these results?


texts: Does any MariaDB engine have the option to declaratively or configuratively abstract highly-redundant data?

texts:
I have an application which is architected in a "NoSQL" style around one fully denormalized "main table" which currently just holds a primary key and one JSON-valued column. For reasons which are outside the scope of this question, I want to retain this architecture: I do NOT want to go full relational and create a proper normalized data model with entities, foreign keys, etc. I would be willing to parse out the JSON fields into their own columns, if that helps with the question below.

The data has a very high level of redundancy, e.g. some long-text column values such as names, addresses, and descriptions may be repeated 20 or 30+ times across different rows.

Is there a MariaDB engine which can declaratively or configuratively deal with this type of redundancy by internally de-duplicating the values across rows?

I imagine different ways this might be implemented in MariaDB would involve an extension to the BLOB or TEXT data types to leverage a content-based hash along with reference-counting or garbage collection.

What is meant by declaratively: it can be accomplished via a single "ALTER TABLE" statement. What is meant by configuratively: it can be accomplished by modifying one or more system variables.

I have read about ColumnStore, TokuDB, Mroonga, Parquet, and MyRocks but I'm not quite sure this is what they do, as the documentation is very sparse.

This question is specifically about a storage engine to help internally optimize the storage of redundant data. Please refrain from telling me to rearchitect, redesign, or refactor the application.


texts: Why does SQLPackage include my table-valued function in deployments when it hasn't changed?

texts:
I'm using SQLPackage in TFS to automate an SSDT project/DACPAC deployment with SQL Server 2014. I have a table-valued function that appears in the deployment report and script with every deployment, even though the source code and compiled rarely change.  Example; I can do a new build and deployment with no source code changes, and my deployment report will look like this (the corresponding SQL script will have the definition matching what's already in the DB):
<Operations>
  <Operation Name="Alter">
    <Item Type="SqlMultiStatementTableValuedFunction" Value="[stuff].[MyTVF]">
  </Operation>
</Operations>

I would expect the deployment to have nothing in it.  It's the only object for which this happens, in a project containing thousands of objects. Anybody had an experience like this with SQLPackage/TFS/DACPAC before?