texts: Review database schema / thought process

texts:
Please review my thoughts below. It is written in an authoritative style because it is the most succinct, not because I am competent. Please poke as many holes as possible.

I am trying to create a database to store graphs. Each graph is represented as a set of Nodes and Relations:

Node (Table)
id (PK)

Relation (Table)
id (PK)
from (FK Node)
to (FK Node)

It should be possible to create multiple graphs, and no information is shared between the graphs. This gives me the following options:

Create a new database for each graph

Simple: Pick the right database at the start of the request, and we are done.
Scalable: Possible to split the graphs easily across different servers.

Create a new set of tables for each graph, i.e., Node_{graphid} and Relations_{graphid}

Simple: Need only a single database.
Complex: Must always transform the table names.

Modify the schemes above to have a graphid

Simple: No need to transform table names.
Complex: Queries are more complex, since we must always account for the graphid.
Expensive: Need to index on graphid, or do some other tricks?

A new database for each graph seems the best choice, and is assumed in the discussion below.

Users will create and share graphs, which leads to two levels of access:

Access (Table)
id (PK)
graph (database id for graph)
edit (FK Group)         # allows editing of graph
view (FK Group)         # allows viewing of graph

A user can be part of many groups:

User (Table)
id (PK)

Group (Table)
id (PK)

User_Group (Table)
id (PK, is this really needed?)
user (FK User)
group (FK Group)

Some graphs will be public. Can use either:

NULL means public.
Add every user to public group by default.

Trade offs (approached from NULL means public):

Saves storage: but a laughable amount (assuming 1 million users: 1e6 * (8 + 8) / (1024 * 1024) = 15 MB)
Complex: Some queries need to check for NULL explicitly (while the same code would handle both public and restricted group otherwise)
Simple: Easier to deal with users that do not have an account (only access to graphs with NULL group).
Faster: To answer the query: ''Can the user view this particular graph?'', we will not always have to check the User_Group table.
Implicit information: NULL has a special meaning that I will need to document somewhere.

Gracefully handling users without account (otherwise I have to always keep in mind what particular ID belongs to the public group), is enough to sway this in favor of 'NULL means public'.

The backend will be written in Python, with either PostgreSQL or MySQL as database management system. Ideally users will be able to edit graphs concurrently.


texts: Generate rows based on value in 2 columns. Bug?

texts:
I have the following table in Oracle:

  ID     START  END
_ID01   AB050   AB100
_ID01   AB101   AB150
_ID02   AB0251  AB0300
_ID01   AB00301 AB00302

I want to generate as many rows and every records defines. So the first need 50 more with a new col start with AB50 up to AB100. Everything works fine, but if I don't add that junk row, the sql developer can't solve the problem, just loading.

I tried with some random SQL hint but none of them worked. Need some advice or hint what's "wrong" with my working query, because I don't think that's normal behavior.

And my SQL query is the following.

     with m as (
      select  to_number(substr(t.START,3)) as num
      from FCS_FP_TOMB t
      where t.START ='AB0251' --<--- THIS ROW IS A JUNK BUT DOESNT WORK WITHOUT IT
     ),
      n as (
      select level - 1 as n
      from m
      connect by level <= m.num + 1
     )
select  ft0.*, regexp_substr(ft0.START,'([A-Z])+')||to_char(to_number(substr(ft0.START,3)) + n.n) as "bizonylat"
from FCS_FP_TOMB ft0 join
     n
     on to_number(substr(ft0.START,3)) + n.n <= to_number(substr(ft0.END,3))

 order by 2,3,7
     ;

The out put:

...
_ID01   AB050   AB100   AB098
_ID01   AB050   AB100   AB099
_ID01   AB050   AB100   AB100
_ID01   AB101   AB150   AB101
_ID01   AB101   AB150   AB102
...

EDIT: I tried MATERIALIZE and INLINE hints, and my Oracle server is 10g.


texts: Get current time, on commit, with oracle database

texts:
When inserting a row I want to make a column, equal to the current time on commit.

If I use default value sysdatetime on the column, it uses the current time at insert (which could be significantly different to commit time). If I use an on insert trigger, it again uses the time at the insert - not the actual commit.

Are there any possible solutions to get current time on commit with oracle ?


texts: Are read replicas heavy reading in affect masterDB?

texts:
We have a very heavy load  in Read side with large DB tables in 2 hours per day that affect to Db so as solutions I have created read replica for it.

If application fetches large heavy data on read replica, is MasterDB affect with that havy load or it will work as it is.


texts: SQL Server Full-text population deadlock

texts:
I've created a full-text index on a non-persisted computed column in a table (details here).

However, every couple days, the population gets deadlocked and stopped. Specifically:

  DESCRIPTION:  A fatal error occurred during a full-text population and
  caused the population to be cancelled. Population type is: AUTO;
  database name is XYZ (id: 7); catalog name is UsersCatalog (id:
  8); table name Users (id: 863342140). Fix the errors that are
  logged in the full-text crawl log. Then, resume the population. The
  basic Transact-SQL syntax for this is: ALTER FULLTEXT INDEX ON
  table_name RESUME POPULATION.

The deadlock report is as follows:

<deadlock>
 <victim-list>
  <victimProcess id="process28fdc561088" />
 </victim-list>
 <process-list>
  <process id="process28fdc561088" taskpriority="20" logused="0" waitresource="PAGE: 7:1:6079614 " waittime="1171" ownerId="2020759813" transactionname="IFTSAutoNested"
           lasttranstarted="2019-09-16T00:16:49.027" XDES="0x28f61f40408" lockMode="S" schedulerid="8" kpid="8000" status="background" spid="35" sbid="0" ecid="0" priority="0" trancount="3">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="60" stmtend="814" sqlhandle="0x020000008bfb2728b1fbd70b14e785d0a8727ca1f478b5690000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
   </inputbuf>
  </process>
  <process id="process2966e0368c8" taskpriority="20" logused="20752" waitresource="PAGE: 7:1:6079661 " waittime="1171" ownerId="2020759849" transactionname="IFTSEndOfBatch"
           lasttranstarted="2019-09-16T00:16:49.047" XDES="0x29230f2e408" lockMode="IX" schedulerid="4" kpid="12296" status="background" spid="33" sbid="0" ecid="0" priority="0" trancount="0">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="6079614" dbid="7" subresource="FULL" objectname="XYZ.sys.fulltext_index_docidstatus_863342140" id="lock2897baa0d00" mode="IX" associatedObjectId="72057606237061120">
   <owner-list>
    <owner id="process2966e0368c8" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process28fdc561088" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="6079661" dbid="7" subresource="FULL" objectname="XYZ.sys.fulltext_index_docidstatus_863342140" id="lock294a6477a00" mode="SIU" associatedObjectId="72057606237061120">
   <owner-list>
    <owner id="process28fdc561088" mode="S" />
   </owner-list>
   <waiter-list>
    <waiter id="process2966e0368c8" mode="IX" requestType="convert" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

In other cases it's deadlocking with a bulk process that copies data from one table to that one. Something like this:

-- Select a batch
UPDATE  TOP (@BatchSize)
        [import].[TempTable]
SET     BatchStatusFlag = 1
OUTPUT  inserted.Id, inserted.ServerId  INTO @UpdatedIds (SourceId, TargetId)
WHERE   BatchStatusFlag = 0

-- Move/update info for that batch
UPDATE  u
SET     ...
FROM    dbo.Users u
INNER JOIN [import].[TempTable] s ON s.Id = u.Id
INNER JOIN @UpdatedIds ids ON ids.Id = u.Id

In that case, the second UPDATE (the one that updates dbo.Users) is the one that deadlocks with the full-text service.

2 questions:

Why is it deadlocking/how can I avoid it?
When that happens, does it retry and reindexes those changes anyway? (even if with a longer delay)

SQL Server version: Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64)   Jul  5 2017 23:41:17   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Datacenter 6.3  (Build 14393: ) (Hypervisor)


texts: Named instance configured on dynamic default port

texts:
I was recently asked to take a look at at some SQL Servers for a friend and carry out a few config and security audits.

There were a couple of servers belonging to the same application group which interested me because there were many inconsistencies and generally odd configuration options set.

One thing which attracted my interest: Named instances configured on a dynamic default port (i.e tcp port 1433). SQL Browser was running but clients connected using ,.

My thoughts on this were that this was totally wrong. I mean, why bother creating a named instance only to put it on a default port, and why make that port dynamic?

I contacted the vendor who very assertively told me that their documentation and installation guide stated that instances should be named and configured on dynamic port 1433 ("As you know, this is the default port that connections expect SQL Server to be listening on and changing this would void your client's support contract with us...")

I can't bring myself to fashion a reply at the moment. Part of me feels it's not worth the argument as I'm doing this for a favour really.

To arrive at the point, my question is two-fold:

When would it ever be useful to operate named instances on default ports, and why would it ever make sense for them to be dynamic?


texts: MySQL - "CREATE_OPTIONS" in "information_schema.tables"?

texts:
I have a MySQL 5.7.16 Enterprise running on a Centos 6 machine with InnoDB as the default storage engine. I have around 300 tables in which around 90 are "list" partitioned.

I have the option innodb_stats_on_metadata=0 in my server configuration.

When I query the metadata using the query,

SELECT * FROM information_schema.TABLES WHERE TABLE_sCHEMA = 'mydb';

I can see the column CREATE_OPTIONS as empty for some non-partitioned columns, row_format=DYNAMIC for some non-partitioned tables, partitioned for some partitioned tables and row_format=DYNAMIC partitioned for some partitioned tables.

Why does this happen when all my tables have the same row format "Dynamic"?

I have table collations as "utf8_general_ci" for some tables and "latin1_swedish_ci" for some tables. Does this have something to do with the above scenario?


texts: stored procedure times out while subsequent runs take 1/6 the time

texts:
So I have this stored procedure which is running very slow (and timing out) in one particular production environment.  This stored procedure produces an xml of profile data.  In all other environments, the sp runs very fast.  After tracing the procedure in the particular environment, I found that the final "SELECT" statement was taking up 96% of the total time (should be under 30%).  This statement involves multiple SELECTS, UNIONS, and JOINS.  I will refer to this as the "Last Select" statement.

On subsequent runs, the "Last Select" statement, and subsequently the whole batch,  took much less time.  What is especially curious is that subsequent runs used many more profiles (15,000 new in the second run, 25,000 new in the third run).

Comparing the query plans, I found some interesting differences between 10000.trc and 25000.trc.  Both of the following were seen in steps before the "Last Select"

The faster run (25K) utilizes many operations involving parallelism.  I do not see any operations involving parallelism in the slow run.
The faster run uses “hash matches” and “merge joins” for inner and outer Joins (instead of nested loops).  With a few exceptions, the slow run seems to always use nested loops.

The slow run uses many clustered index scans and clustered index seeks during the final select that the fast run does not.  The slow run also uses a "concatenation" step (not found in the fast run) which appends multiple tables to make an output table.

Here is a summary of the times;

Any help is appreciated.  Thank you!


texts: Oracle 19c installation stuck at creating and starting oracle instance

texts: I'm trying to install oracle 19c but it's stuck at 52% for over 7 hours. So cancelled it and uninstalled it. I have started installing again but still stuck at 52% that is in creating and starting oracle instance.


texts: Dimension attribute with one-to-many relationship

texts:
I have a request for an attribute in a dimension that has a one-to-many relationship with the lower level of the dimension.

Here is the case:

Dimension: Employee
Attribute: Immatriculation

I have 3 source tables : Employee, Language and LanguageLevel.

One employee can have multiple immatriculation codes. Each immatriculation has a number, start_date, expiration_date.

How can I model this case ?


texts: How can I script out a copy of the backup to multiple locations?

texts:
Is there a way to script a copy of the backup file to multiple destinations? For example, the following will split the backup into 3 segments which make up the entire backup file. But, I'm looking to send 3 complete backups to 3 different locations. Is this possible?

BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB1.BAK',
DISK = 'D:\TestDB2.BAK',
DISK = 'E:\TestDB3.BAK'
GO

I'm looking for something more like...

BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB1.BAK',
TO DISK = 'D:\TestDB2.BAK',
TO DISK = 'E:\TestDB3.BAK'
GO

Thanks in advance.


texts: Oracle 12c and Jobs running one hour apart after changing time for hourly use

texts:
I have an Oracle 12c database in which I have detected that the scheduled jobs are running one hour apart after the change of time on the server, checking in the database I see that the SYSTIMESTAMP differ with the DBTIMEZONE

SQL> select dbtimezone, sessiontimezone from dual;

DBTIMEZONE

SESSIONTIMEZONE

-04: 00
-05: 00

In the operating system I run:
date + "% Z% z"

CST -0500

Is it correct that these values of SYSTIMESTAMP and DBTIMEZONE are different?, when the time change is made is it necessary to modify something in the database?


texts:
I have a date field that is in a 'yyyymmdd' format, and I need to filter the query by the year, using that field.

For example, in my table there is a "Posting Date" field that is in the '20180401' format, and I need to filter the query to only the records with a year of 2018.


texts:
I've been trying to get rid of expensive lookups in my query plan but cannot seem to get my head around it.
I understand the idea of creating a covering index to eliminate the need for lookups but I don't know how to approach it with a complicated plan like this.

Any ideas on how to approach this would really be appreciated.

Query plan can be found here:
https://www.brentozar.com/pastetheplan/?id=HyBbQ0eRr

Thanks


texts: Mysql checksum for master-slave replication returning different values, despite being identical

texts:
I've set up a master slave replication in mysql and to make sure that I know if the data starts getting out of sync I created a script that:

Locks both databases on both servers.
Prints the checksums of all the tables to a text file.
Copy's the slave checksum text file to master.
Compares the both files to see if anything is different.

But on some tables I got different checksums. So I downloaded both those tables and created another script to find whats different on them. And Nothing is different! They're identical.

I only get the different checksums on tables that have float variables, so I suspect it has something to do with that.

I do have slightly different versions of mysql installed, could this be causing it?

+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| innodb_version          | 5.5.34                      |
| protocol_version        | 10                          |
| slave_type_conversions  |                             |
| version                 | 5.5.34-0ubuntu0.13.04.1-log |
| version_comment         | (Ubuntu)                    |
| version_compile_machine | x86_64                      |
| version_compile_os      | debian-linux-gnu            |
+-------------------------+-----------------------------+

+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| innodb_version          | 5.5.37                      |
| protocol_version        | 10                          |
| slave_type_conversions  |                             |
| version                 | 5.5.37-0ubuntu0.14.04.1-log |
| version_comment         | (Ubuntu)                    |
| version_compile_machine | x86_64                      |
| version_compile_os      | debian-linux-gnu            |
+-------------------------+-----------------------------+

Anyone of you know what could be causing this?


texts: Optimal SQL Server 2012 Disk Configuration

texts: I will be installing SQL Server 2012 on a new Dell T430 server containing four (4) physical 15K SAS 6Gbps disk drives using a PERC H730P RAID Controller and RAID 1. Since this will be the first time I've installed SQL Server 2012, can someone please recommend an optimal configuration for disk allocations. My thoughts were to install the OS, programs and main databases on one physical drive and put the transaction and logs (both TEMPDB and main DBs) on the other disk drive. Would someone have a better recommendation so I can get the best performance out of the limited resources? Thanks.


texts: Maximum number of columns per table in SQLite Manager

texts:
I'm trying to increase the number of available columns of my table to about 25 or even more.

However, the SQLite Manager add-on for Mozilla products that I'm using supports the maximum of 20 columns per table.

How can I create a table with more than 20 columns?


texts: How can I change three hundred procedures at once?

texts:
I need to change about 300 procedures and packages in the database, due to a migration that will accomplish this weekend. We will do a migration from one server to Exadata.

However the database has been developed in a very sloppy way. The bank carries out a number of text files written directly to disk, but nobody uses directories. In Exadata, the path to writing the files will be different due to the use of DBFS, for that I must change all calls via UTL_FILE.

Let me give an example: Currently the code is this:

file: utl_file.fopen = ('/file/folder/documents', filename, 'W');

What I want to do:
Create a directory

create or replace directory directory_name as '/file/folder/documents';

Change the 300 procedures for:

file: = utl_file.fopen ('directory_name', filename, 'W');

During migration only change the directory:

create or replace directory directory_name as '/dbfs/documents';

The real question: Is there a way I make a search and replace changing all 300 procedures in the database at once?  I mean, there's a way to change all '/file/folder/documents' to directory_name?


texts:
Export MySql data in following command.

SELECT id,documentfile
FROM documentuploads
INTO OUTFILE '/home/home2/agri_doc.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

CSV file data

"3","%PDF-1.7\
%����\
1 0 obj\
<<\
/Type /Catalog\
/Pages 2 0 R\
>>\"

I am currently using mongodb shell version v3.6.3. i want to import my csv file in mongo database, and I have created a collection documentupload where only two field id, docs. data is latin1_swedish_ci encoded. I tried following command.

mongorestore -d agrilicense2 -c documentupload  --type csv --file /home/shiva/home/agri_doc3.csv  --fields id, docs --headerline

Show output
error parsing command line options: unknown option "type"
Thank you


texts: Rebuild index in SQL Server does not reduce fragmentation

texts:
I am trying to reduce the fragmentation of my indexes.

First of all I rebuild all indexes with fragmentation higher than 30%, but the most of my indexes still with high fragmentation or does not even change 1%.

Looking deeply, when I reduce the fill factor on the rebuild statement then reduces the fragmentation too.

Now, what is the best option?

A higher fragmentation with a high fill factor or reduce the fill factor and then gets lower fragmentation?

Thanks for now.


texts: Why is my update affecting twice the number of records than expected?

texts:
I have the following query:

update Table1
set id=2
where nbr in (select nbr from Other.dbo.table)

The "select nbr from Other.dbo.table" has 500 records.  Yet when I run the update, it says I am updating 1,000 records.  I have checked table1, and there are only 500 rows that match the filter.

select * from table1 where nbr in (select nbr from Other.dbo.table)

I tried changing the query to be a join and using a temp table to store the filter list, but I receive the same results.

Why am I updating 1000 records when only 500 match?

There is an update trigger on Table1.


texts: SQL Server Page Life Expectancy sharp drop

texts:
I'm trying to put my hands on the reason this SQL Server slows down sporadically. There are no patterns detected so far.
It's a SQL Server 2008 R2 running on a Windows Server 2008R2 VM on top of VMware 5.0. The VM has 16GB of memory with max server memory in SQL set to 8GB.
On seemingly random days i see Page Life Expectancy drop off a cliff from 42,000+ sec to 200-300 sec for a few minutes. It does not decrease with time. It literally falls of a cliff. A complete 90 degree drop on the chart when i plot the values. then i it starts to build up again

The Target Server Memory and Total Server Memory counters are consistent at 8GB throughout. There also no spikes in batch requests/sec or user connections at these times.

On the VM level, Memory Mapped is consistently at 16GB and Memory Ballooned is 0, so the VM is not under memory pressure.

I'm trying to think where i should look next. What counters or areas of interest should i put my eyes on as a next step to understand these performance drops on the server.


texts: Can Oracle Heterogeneous Services be used to connect to a SQL Azure database?

texts:
I would like to be able to use a database link from an Oracle 10g database to a SQL Azure database.

Will Oracle Heterogeneous Services support this?

I can see various articles relating to connecting to SQL Azure via ODBC, so does this mean that it will be possible to do it using Oracle HS, or am I oversimplifying things?


texts: How to use document age as weigt in ts_rank

texts:
In a search mode I would like to impact the rank of my search related to age of the document. I have a working version but I found it very complicated.

It say "Does the same founded younger document is more relevant than an older one"?

SELECT
  ts_rank(to_tsvector('an example of document'), to_tsquery('document')) /
    (EXTRACT(epoch FROM (SELECT (NOW() - INTERVAL '3 days')))/3600)::int >
  ts_rank(to_tsvector('example of document'), to_tsquery('document')) /
    (EXTRACT(epoch FROM (SELECT (NOW() - INTERVAL '10 days')))/3600)::int AS relevancy;

How to deal with more precise time without having "complex" result like:

SELECT ts_rank(to_tsvector('example of document'), to_tsquery('document')) /
    (EXTRACT(epoch FROM (SELECT (NOW() - INTERVAL '10 days')))/3600)::int; -- 1.4441788961179666e-07

Should I round for performance?


texts: Can anybody help me on "what all users are using the undo tablespace and how much" in oracle database 11g r2. any pointers would be much appreciated.


texts: What is the equivalent of a "Certificate of Destruction" when using a cloud-hosted database?

texts:
We have a Rails application hosted by Heroku and using an Heroku Postgres database. Some of the information we store is considered to be sensitive by our users, and we've been asked if we can provide a Certificate of Destruction in the event they wish to delete their account. In other fields (e.g. law) CODs are apparently common practice, issued for instance after sensitive paper documents are shredded or otherwise destroyed.

So I have three questions:

1) When we issue the ActiveResource::Base#destroy command on a record, is the data truly destroyed? We keep backup snapshots with Heroku's PG Backups, so even if the original command truly destroys the data, its probably still in the backups. Without deleting the backups, is it even possible to destroy completely the client's data?

2) Is there an equivalent to a Certificate of Destruction for this purpose?

3) If there is not an equivalent, how have other people mitigated or otherwise assuaged similar client concerns?


texts:
I have been assigned a task to setup Streaming replication in Postgresql. I am not a DBA by profession, doing this thing for the first time.  I have set up replication, now as we don't want to change the database connection string in the application during failover, I need to set up VIP for the same so that I provide only one IP in the connection string.

I have used this article for setting and testing VIP. I am able to setup VIP on a machine and able to connect PostgreSQL with this VIP. But when I tried to assign the same VIP to the slave server, i got an error that cannot assign requested address bind.

I need help to setup VIP and make it work with streaming replication so that applications always point to the master server i.e. point to new master after failover.

lets suppose a case , if VIP is assigned to master and that VM goes down, how will I unassign VIP from old master and assign to a new master.


texts: Is MongoDB index creation single threaded?

texts:
I have rather big dataset - 2TB of text data, 18.000 millions of lines.

Each document is same: 5 fields: 4 strings + 1 integer.

All fields should be indexed and searched by.

That is: array of 3 string tokens, 1 separate string, 1 integer.

"t" : ["param","pam","tadam"],
"p" : "haha",
"f" : 3062632

I tried two ways:

to import, then create index
to create index, then import

Mongo journal is disabled, XFS, CentOS7.6, MongoDB 4.0.6
Tried very beefy server (72core, 144GB RAM, 6X SSD RAID)

Speed was above million inserts in second. After insertion, which took less then a day, I started foreground indexing. I've set this parameter maxIndexBuildMemoryUsageMegabytes=10000

Command is:

db.runCommand( { createIndexes: "records", indexes: [
                { key: { "f" : 1 }, name: "find" },
                { key: { "p" : 1 }, name: "pind" },
                { key: { "t" : 1 }, name: "tind" }
    ]
  }
)

it applies fine, because mongo reports that "using bulk method; build may temporarily use up to 3333 megabytes of RAM"

speed is terrible, saturation of hardware is close to zero.
1 cpu core at 100%
SSD at 3%

If I create index prior to inserting, speed starts from 100k inserts, but fast drops to single thousands of records per second at 200-300 million documents.

Can this problem be fixed with MongoDB?
Or another DBMS?

One cave: I need all dataset on one server, no clusters.


texts: Oracle: deleting data from tables leads to data file fragmentation?

texts: When randomly deleting ~5GB from a ~10GB Oracle table, I suspect that disk space is left in fragments.  Is there a way to know about the disk state and any query to defragment or shrink the space?


texts: Indexing the difference between two columns in MySQL

texts:
Is there a way to optimize/index a query that sorts the difference between two columns.  I have a modest sized table of 5 million rows that I'm trying to do the following:

SELECT pk_column, column_x, column_y, column_x-column_y
FROM table_a
ORDER BY column_x-column_y DESC

This particular query takes about 2 seconds, however this ORDER BY seems to create a fairly large bottleneck in more complex queries.


texts:
I have two linked servers and want to create a stored procedure that simply SELECTs data from one table from the first server and INSERT the data in other table from the second server.

The procedure code looks like this:

BEGIN TRAN

INSERT INTO [RI].[TEST_DB].[TEST_TABLE] (...)
SELECT ...
FROM [TABLE]

TRUNCATE [TABLE]

COMMIT TRAN

But I get the following error:

  OLE DB provider "SQLNCLI11" for linked server "RI" returned message "Cannot start more transactions on this session.".
  Msg 7395, Level 16, State 2, Procedure usp_test, Line 46
  Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server "RI".
  A nested transaction was required because the XACT_ABORT option was set to OFF.


texts: Database Normalization: A Case Of The NULLs

texts:
I'm trying to learn about proper database management and am having a hard time with designing a database that doesn't rely on NULLs. If you think I'm crazy for embarking on this, it is because of SQL and Relational Theory: How to Write Accurate SQL Code (ch.4, p.79), where the author makes the case that simply because having NULLs produces unexpected results in certain situations, they should be avoided.

The alternative to NULL-able fields, as I understand, is to create 1::0-1 relationships for each field. This seems like it would make querying information very difficult. Is this the correct approach? Are NULLs, in fact, much safer than the author leads me to believe?

Would using a VIEW to represent the many 1::0-1 relationships be applicable and any more beneficial than simply having the NULLs in table in the first place?

My scenario is a user table with optional fields that may, or may not, be foreign key relationships to other tables. Obviously, making those fields NOT NULL means they must be required, but I can't put 0 in there since it breaks the foreign key. Other situations occur when trying to put 0000-00-00 for, say, a birthdate field, since MySQL doesn't allow me to do that.

Thus, I'm back to wondering if eliminating NULLs are, indeed, a best practice and provide the greatest flexibility and performance than the alternative.


texts: Get table sizes without SELECT permission

texts:
I want a scheduler to run a simple SQL script to collect the sizes of tables in our MySQL database:

SELECT table_name, data_length, index_length
FROM information_schema.TABLES

The user the scheduler logs in as has very limited privileges:

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'our-user'@'%'
IDENTIFIED BY PASSWORD '...'

This is intentional - we don't want the user to access any data; only metadata such as table sizes.

From the MySQL manual I thought those permissions should suffice. In particular, that's what it has to say about information_schema.* tables:

  Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges.

but then adds

  These restrictions do not apply for InnoDB tables; you can see them with only the PROCESS privilege.

Our tables are InnoDB tables.

Still, when running the script under our-user, I only get the system tables (TABLES, VIEWS, INNODB_*) listed. The same script does show our user tables when run by a different user with SELECT permission for our tables.

From the manual it also follows that VIEWS can be run with the privileges of creator, so I tried wrapping my query in a view:

CREATE DEFINER = CURRENT_USER SQL SECURITY DEFINER
VIEW view_table_sizes AS <the query above>

However, when this view is SELECTed from by our-user, only the system tables are returned. Needless to say, the view was created by a privileged user.


texts: Modulo operator with nonintegers - unexpected results

texts:
MySQL 5.5.32, Oracle 11g

MySQL returns strange results for queries involving mod() with noninteger moduli. Why is this?

I have a column defined thus:

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| col    | double(15,6) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

I have selected a known value from that column to use for testing - 5850. If I query the table, testing that column for whole numbers, I get these results:

mysql> SELECT thing_id,name FROM table WHERE col=5850 AND mod(col,1) != 0;
Empty set (1.07 sec)

This is expected. But this is not:

mysql> SELECT thing_id,name FROM table WHERE col=5850 AND mod(col,.1) != 0;
+-----------+-----------+
| thing_id  | name      |
+-----------+-----------+
|   4444444 | some      |
|   4444445 | names     |
...
|  55555555 | go        |
|  55555556 | here      |
+-----------+-----------+
416 rows in set (1.07 sec)

The same results from substituting col MOD .1 and col % .1 so it has to do with the computation not the semantics.

Compare this with the behavior of Oracle (using SQL Developer if that matters), where the type of col is NUMBER:

SELECT thing_id,name FROM table WHERE col=5850 AND mod(col,.1) != 0;
thing_id    name
[NO RECORDS]

The precision on the MySQL column seems to make this difference, but I don't understand how. Conceptually, shouldn't integer_value % .1 = 0?


texts:
I'm trying to import a mysqldump backup of a drupal database taken from a server running MySQL 5.0 (which will be upgraded to 5.5) onto my local machine running MySQL 5.5.23. The import fails with the below error:

ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES

When I open up the backup at line 11084 here is what I get:

LOCK TABLES `sessions` WRITE;
/*!40000 ALTER TABLE `sessions` DISABLE KEYS */;
INSERT INTO `sessions` VALUES ...
-- MySQL dump 10.11
--
-- Host: localhost    Database: drupal
-- ------------------------------------------------------
-- Server version       5.0.77-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `search_index`
--

DROP TABLE IF EXISTS `search_index`;        <=== LINE 11084
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `search_index` (
  `word` varchar(50) NOT NULL default '',
  `sid` int(10) unsigned NOT NULL default '0',
  `type` varchar(16) default NULL,
  `score` float default NULL,
  UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
  KEY `sid_type` (`sid`,`type`),
  KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

I checked the other statements and normally INSERT statements are followed by UNLOCK TABLES;. Accordingly if I make the below modifications to the mysqldump file the problem is fixed:

INSERT INTO `sessions` VALUES ...
UNLOCK TABLES;                   <=== ADDED LINE
-- MySQL dump 10.11
[...]
DROP TABLE IF EXISTS `search_index`;

I don't know what's wrong there but the whole comment block starting with -- MySQL dump 10.11 is the same as the one at the beginning of the mysqldump file, and it looks like mysqldump started a new dump while writing the INSERT INTO sessions statements which were not closed properly with UNLOCK TABLES;.

Does someone have any idea as to what's going on here?


texts: Select only rows that equal x but not x and y?


texts: What's the best way to store and search through many product specifications

texts:
Let's say I have a lot of products and all of those products have a lot of specifications. These specifications can change at any time (e.g. "colour" could suddenly be available for new products) and some products do not have certain specifications.

What would be the best way to store this?
What would be the best way to search through this?

In the end, there will be tens of thousands of products each with dozens of specifications. Adding a column for each specifications feels wrong, so I'm assuming having a separate table for the specifications and then mapping them through a third table is the best way to go.

How would I be able to search through these specifications easily while maintaining performance when the products_specs table grows to millions of records?

Here's the schema and query I came up with: http://sqlfiddle.com/#!9/53f49/3


texts: How to stop Log Reader Agent from SQL/Command Line

texts:
Say I've got replication on a SQL Server 2012 box set up.  Everything is working as expected, and the Log Reader Agent is running.

Now, say that I need to remove all traces of this publication/subscription from the database programmatically.  Previously, I had this working with SQL 2008R2.

The steps I was taking are -

Drop subscription at source database (sp_dropsubscription)
Drop subscription at target database (sp_droppullsubscription)
Drop publisher (sp_droppublication)
Remove everything else (sp_replicationdboption)
Remove databases

This used to work perfectly, but since upgrading to SQL 2012, when I try to drop the publisher, I get the following error -

  System.Data.SqlClient.SqlException (0x80131904): Only one Log Reader
  Agent or log-related procedure (sp_repldone, sp_replcmds, and
  sp_replshowcmds) can connect to a database at a time. If you executed
  a log-related procedure, drop the connection over which the procedure
  was executed or execute sp_replflush over that connection before
  starting the Log Reader Agent or executing another log-related
  procedure.

Now, I think I understand what's going on here, but I don't know how to prevent it.

What's the best way of stopping the Log Reader Agent, either via command line, or via SQL?


texts: Database Space Used Decreased after Index Rebuild (Ola Script)

texts:
I have rebuilt some indexes which were highly fragmented. After the index rebuild I am seeing my database space used reduced by almost 50% (from 77gb to 33 gb)

Is that normal behavior? I dont have auto shrink turned on - did I lose data?

Note: space used decreased in my database file; the actual physical size of my database file is unchanged.

Command used to calculate the space used:

SELECT sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB

Results of select @@version

  Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)

Rebuild of indexes performed using Ola Hallengren's script. Parameters used:

Databases = 'USER_DATABASES',
FragmentationMedium='INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE
',
FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
FragmentationLevel1 = 5, FragmentationLevel2 = 30, FillFactor=80,
UpdateStatistics = 'ALL', OnlyModifiedStatistics = 'Y', LogToTable = 'Y'" -b


texts: how to estimate rebuild index on 140 million records?

texts:
My existing project has data of about 140million records in Contentq table. And I have planned to rotate the table just incase not to face any load on the table.
So I have planned to rotate data older than 2 months to another table contentq_old and rename current latest 2 months data to contentq1 and create a structure only table contentq. As I have only updates/deletes for the  latest inserted records I can take that risk. Let the application to point to empty table contentq for its operation. As there were almost 80million records deleted I should be going for rebuild indexes on contentq1 that has latest 2 months records. Once rebuilt done lock table contentq and do -

"insert into contentq1 select * from contentq';" .

Then do drop contentq and rename contentq1 to contentq.

My problem is no down time for application hits, but I can park for somewhile. I should be knowing how long would the rebuild go on a latest 2months record that holds 50 million records max. Below is the structure of the table contentq. But how can I estimate how long an index rebuild would take?

Mysql 5.0.77 on 16GB RAM

 contentq | CREATE TABLE `contentq` (
 `idcontent` bigint(120) unsigned NOT NULL auto_increment,
 `keyvendor` varchar(32) default '0',
 `msisdn` varchar(16) default NULL,
 `cli` varchar(16) default NULL,
 `message` text,
 `udh` text,
 `priority` int(10) unsigned default '1',
 `recorddate` timestamp NOT NULL default CURRENT_TIMESTAMP,
 `callback_url` text,
 `transactionid` varchar(256) default NULL,
 `status` tinyint(4) default '0',
 PRIMARY KEY  (`idcontent`),
 KEY `PriorityIndex` (`status`,`priority`,`keyvendor`),
 KEY `NewIndex1` (`keyvendor`,`status`),
 KEY `idx_contentq_msisdn` (`msisdn`)
 ) ENGINE=InnoDB AUTO_INCREMENT=136583422 DEFAULT CHARSET=latin1

Please advise. Thanks!!


texts: Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?

texts:
We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and optimized the db and indexes) and one avenue is to get better hardware.

Currently our mysql DB is on a 6 year old server with 16GB of mem with 2 x Xeon 2.00GHz (2 cores) with the ability to have 32 GB mem max.

We are looking to buy a new server for the mysql database with the following specs:

Dell PowerEdge R420 - Highlight specs

2 Intel Xeon E5-2450 2.10GHz, 20M Cache
128 GB RAM
400GB Solid State Drive

So if the database is 80 GB and we have 128 GB of Memory on this new server my question (s) are:

Can we put the full database into memory and see performance gains?

Would setting the innodb_buffer_pool_size to 88GB effectively be the same as putting the "the DB in memory"?

Can mysql take advantage of this memory? Are there any limitations on this on the mysql side?

Would there be any pitfalls of loading the full mysql in database or having such a large innodb_buffer_pool_size?


texts: Get the last statement execution date on an MSSQL Database

texts:
In an MSSQL Database, I am trying to know when was the last statement executed for that database.

So, is there any log that I can use to see the required activity records (could be the Transaction Log, but I am not sure how to get dates from it)?


texts:
what is oracle active session pool? is it activated by default?
what is the default maximum active session that there is per user?


texts:
I want to have a fast lookup based on if two columns are equal.  I tried to use a computed column with an index, but SQL Server doesn't seem to use it.  If I just use a statically populated bit column with an index, I get the expected index seek.

Seems there is some other questions like this out there, but none focused on why an index wouldn't be used.

Test Table:

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS isnull(convert(bit, case when [DataA] is null and [DataB] is not null then 1 when [DataA] <> [DataB] then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    )

create index ix_DiffPersisted on Diffs (DiffPersisted)
create index ix_DiffComp on Diffs (DiffComp)
create index ix_DiffStatic on Diffs (DiffStatic)

And the Query:

select Id from Diffs where DiffPersisted = 1
select Id from Diffs where DiffComp = 1
select Id from Diffs where DiffStatic = 1

And the resulting execution plans:


texts: Postgres jsonb column or standard normalized table?

texts:
I'm implementing an app with a payment system, and I need to record transactions that are made through the app. Also I need to use some of the information about the transaction for rendering some KPIs. I have an implementation already in Postgres in which my table has two columns, id and transaction (jsonb).
Inside my transaction column I have an object that looks like this:

2018: {
   November: {
      list_of_transactions: [],
      totalAmountEarned: 0,
      numberOfTransactions: 0,
      avarageSpending: 0,
      numberOfCoins: 0,
      numberOfUsers: 0
   }
}

Now, whenever I make a transaction, I check that the year and the month that comes with the request exists, else add them to the object, and push the transaction inside list_of_transactions, and update all the other keys accordingly.

I was wondering whether this is a good way of tackling the problem, or is in fact a really bad way. Is creating different tables and normalizing them in a "SQL fashion" a better solution? Have you got any suggestion?

Additional considerations

A side question: since there's going to be many many transactions is it a good idea to create a new table for each year?

The structure of all the data involved will be exactly the same, so I should create multiple databases and do joins. Since there's going to be many, many transactions: is it a good idea to create a new table for each year?


texts: Log Shipping Backup and Destination Folder Location

texts:
We plan to incorporate Log Shipping for basic disaster recovery with reporting needs. Server A Source Database will log ship to Server B database. We will conduct log shipping every four hours.

Original plan, source backup location will be in Server A Harddrive backup folder. Backups will copy to Server B Harddrive destination folder.

However after reading two MS articles below,

Are the two bold statements saying the same thing or different?

(1) Should source backup folder not be located in Server A Harddrive folder? (I can make it accessible to Server B as needed.)

(2) If so, why should I save source backups on separate server host computer? What benefit will this serve?

(3) Do I need to copy log backups in Server B Hard drive folder? Why not keep in separate server host computer, as they recommended to have consistent philosophy?

About Log Shipping
"The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.
Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder."

Database Mirroring and Log Shipping
During a log shipping session, backup jobs on the primary database create log backups in a backup folder. From there, the backups are copied by the copy jobs of the secondary servers. For the backup jobs and copy jobs to succeed, they must have access to the log shipping backup folder. To maximize availability of the primary server, we recommend that you establish the backup folder in a shared backup location on a separate host computer. Ensure that all the log shipping servers, including the mirror/primary server, can access the shared backup location (known as a backup share).


texts: PostgreSQL trigger doesn't insert all records

texts:
I'm working with PostgreSQL 10. I'm updating another table when a record is inserted into the original table.  The trigger doesn't insert the last record if there are more than one. If it's just one record it won't get inserted at all. Any ideas why it's doing this?

create table employee
(
   empID SERIAL unique PRIMARY KEY NOT NULL,
   first_name text not null,
   mi text not null,
   last_name text not null,
   full_name character varying (150) UNIQUE NOT NULL,
   employee_email character varying(150) UNIQUE
);

CREATE TABLE empmerge
(
  empid integer NOT NULL REFERENCES employee(empID) MATCH FULL
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  full_name character varying NOT NULL REFERENCES employee(full_name) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  org_section text,
  date_of_xfer date

);

The employee trigger:

create or replace function concat_full_name() returns trigger as $$
begin
    if NEW.full_name is null
      then NEW.full_name := concat(new.first_name, ' ',new.mi,'.', ' ', new.last_name);
    end if;
    return new;
end;
$$ language plpgsql;

create trigger trig_concat_full_name
before insert
on employee
for each row
execute procedure concat_full_name();

The merge trigger

create or replace function insert_empMerge()
  returns trigger as $$
begin
  INSERT INTO empMerge(empID, full_name)
  SELECT employee.empID, employee.full_name
  FROM employee
  WHERE NOT EXISTS (SELECT 1
                    FROM empMerge
                    WHERE employee.empID = empMerge.empID
                     and  employee.full_name = empMerge.full_name);
    return new;
end;
$$ language plpgsql;

create trigger trig_insert_empMerge
before insert
on employee
for each row
execute procedure insert_empMerge();

The insert statement:

insert into employee(first_name, mi, last_name)
values
 ('dora','l', 'explorer'),
 ('atticus','m', 'finch'),
 ('nancy','s', 'drew');

select * from empMerge;


texts: Good way to limit access of rows different for different users? (Postgres)

texts:
I am writing a closed application in which a user enters his db-connection details and then all queries are hard coded.
Let's assume I got a table "tbl" with 1000 entries (rows).
One day the users "userA", "userB" and "userC" should have access the following rows in tbl:
userA: 0-300
userB: 301-1000
userC: 400-500, 600 and 900-1000
On another day it is maybe necessary to change all these numbers to new ones (made by DB admin).
The closed application shall fetch the allowed rows for every individual user.
How would you implement that?
I thought about creating a table with the columns "user" and "allowed_rows". Then I would just allow the program to query all row numbers in "tbl" for the current user. The numbers I would write maybe as a range string like "400:500,600,900:1000" for userC.
Is this a good way or do you have a better idea?


texts: MySQL master to master race conditions, do I need to use transactions/locks for this code?

texts:
I'm playing around with master to master MySQL replication hoping I can run two copies on my C# application on two different servers to help improve performance. Rather than the one copy on one server I have currently.

However I'm struggling to understand how MySQL prevents or handles race conditions and maintains the integrity of the database. For example my application has code similar to the below.

SELECT 100 TASKS TO PROCESS

UPDATE THE 100 TASKS TO SAY WE'RE WORKING ON THEM

INSERT THE RESULTS OF THE 100 TASKS INTO THE DATABASE

Because the code above runs so frequently eventually my application will try to work on a task that has already been "SELECTED" but not "UPDATED" yet by the other instance of my application and when it does that it will try and "INSERT" data that has already been inserted and this produces unique key errors which break the MySQL master to master replication.

I can see from this answer that the mysql instances seem very separate and appear to have no awareness of each others locks/transactions.

https://stackoverflow.com/questions/36026769/transactions-between-two-replicating-master-mysql-servers

I can see that I can disable unique key errors with the config below but I'm not sure if that will cause more problems than it will fix.

slave-skip-errors=1169

Is this something I can avoid with locks and transactions?

Or is this just part of master to master replication and I somehow have to design my application around this?


texts: Restore a SQL Server 2012 backup file in SQL Server 2016/2014

texts:
I have SQL Server 2012 backup files and want to restore these to SQL Server 2016/2014. Will it work?

I need to find this out before I uninstall SQL Server 2012.


texts: Any relation between SQL Server Service Name and Instance Name?

texts: My SQL Server's instance name is SQLEXPRESS and SQL Server Service Name looks like MSSQL$SQLEXPRESS. Is there any relation between instance name and service name? I'm trying to check SQL Server Service Status by name and I wonder can SQL Server Service name be different on another computers?


texts: Cancel Query in remote ssms from ssms in my pc

texts:
We connect to our customer's database server with vpn. Sometimes we manage Sql server to connect database server or to connect with ssms in our pc's.
I executed a query with ssms in database server, then the connection has gone down. I have tried to connect many times but failed. I have to cancel this query.
I can connect with ssms in my pc's.
Can I cancel query with ssms in my pc?


texts: MySQL - Slow InnoDB Write Speeds on Windows 10

texts:
I have Windows 10 bootcamped. I've installed the latest version of MySQL community server. I've also installed WordPress onto IIS. The MacBook has 16Gb of memory. Here is my config file

[client]
no-beep

# pipe
# socket=0.0
port=3306

[mysql]

default-character-set=utf8

[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 5.6/Data
character-set-server=utf8
]default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="DESKTOP-F1OO1LC.log"
slow-query-log=1
slow_query_log_file="DESKTOP-F1OO1LC-slow.log"
long_query_time=10
log-error="DESKTOP-F1OO1LC.err"
server-id=1
max_connections=151
query_cache_size=0
table_open_cache=2000
tmp_table_size=90M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=171M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_additional_mem_pool_size=13M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size=7M
innodb_buffer_pool_size = 4G
innodb_log_file_size=48M
innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
query_cache_type=0
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

Is there anything obvious I'm doing wrong or do you need more information? I've tried following various guides but getting lost with all the options avilable.

Currently looking at a maximum of 20 writes per second!

Thanks


texts: MySQL Database Poisoning: How to recover to a known state quickly

texts:
If you want to avoid database poisoning (ie want to recover back to a point-in-time quickly) which methodology do you prefer?

Let me define data poisoning. You insert some things in your database which totally messes up the internal structure and interdependencies. I know it means that database design probably also needs to be revisited, but the damage is done.

The methods I have in mind are

Somehow setup replication in which the slave is passive and is X hours behind. If I have a failure all I have to do is reset the application and point it to the slave as my new master. I suspect that it is possible.
Do a hot backup of MySQL every few hours and when failure is detected restore to a backup from X hours before. This would mean a downtime for the application since I cannot let the current application keep on running. One could use innobackup or percona for quick backup and recovery steps.
Design the application and database specifically so that newly added data gets nuked (or shelved). This means I store all events/states (I guess this is the most difficult and theorotical solution)

If the first option is possible and it also stores all the relay logs (ie what ever happened on Master gets transfered to Slave at the same instant but is applied in a few hours automagically) then it would be a perfect solution. Perhaps one could setup multiple slaves in a setup to recover from both an outage and data poisoning


texts: What are the hazards of upgrading a SQL Server service pack?

texts:
Are there any specific pitfalls/standard considerations when upgrading an instance of MS SQL Server to a higher service pack? Is there anything special that is generally done besides making sure there are current backups of system and user databases?

I'm asking the question in general since I presume the answer is similar regardless of version or situation. However, in the case that I'm mistaken and the specifics do matter, the situation that prompts my question is an upgrade of SQL Server 2008 from SP2 to SP3, running on Windows Server 2008 with failover clustering.


texts: PG fulltext search: Boost factor for word in the title

texts:
I use PostgreSQL full text search and it works fine.
The database schema is simple. A page has a title and a content.
Up to now I just index the string which I get by joining title+content.
In the future I would like to adapt the ranking. If a search term is in the title
of a document, then I would like to rank this page higher.
How can I influence the search ranking in PostgreSQL?


texts: Adding columns upped my RAM usage significantly

texts:
I have a Google Cloud Platform MySQL 2nd Gen 5.7 instance with two databases, one for testing and one for production.

There are about 20 or so tables. One of the tables accounts for 99% of the storage.

I added 8 columns to the largest table (it already has ~150 columns) of the testing db. 6 of the new columns are Integer and 2 of them are Boolean (or more accurately, TinyInt). No indexes of any kind added.

Below is a snapshot of the metrics from the migration operation. You can clearly see CPU and Storage peaks for each of the 8 columns.

Weird part is, RAM usage goes up an stays up. Any insight on what I may have forgotten to do?

The columns did not have any data populated to them, all are Null for each row so far


texts: UPDATED: Relational (or NoSQL) Database Design

texts:
(Updated this question with more specifics. Decided on a Relational Database, and would love some comments on its design)
This database holds assembly instructions data for shipbuilding, later use in a Augmented Reality Android application. The application asks the server (Node.JS) for the necessary files.
Simply put: Work Packages contain several Parts that must be mounted, each of these parts has the Files necessary needed by the  application.

Work Packages depend on other Work Packages (WD_Dependencies), in the figure above, S1 depends on 1 and 2;
Workers can be assigned a specific Work Package, if not, the return Work Package is a combination of matches between Type and Department.

Update:
(Open in new tab for better view)

The database is "divided" between the blueprint of a project, and the specific execution of that blueprint. This also allows for multiple executions of the same blueprint.
Questions:

Is this a good design? Dividing Blueprint and Projects?
Efficient SQL Query to recursive lookup all the dependencies of a Work Package?
Do you see any problems with the relationships or any redundancy?
Any comments or help are welcomed.

I understand this could be a broad/opinion question, but I have no experience with database of this size, and would appreciate the help.


texts:
What's the best way of creating a password generator user defined function function in SQL Server if it is not possible to use non deterministic funtions within functions?

I would like to create a function that returns a randomly generated varchar(10) using characters from a given string like this one:
"1234567890QWERTYUIOPASDFGHJKLZXCVBNM"

I have my own ideas that work but are not very natural solutions.

The idea behind the function is that if somebody requires a password reset  I could do the following:

UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID

A function would be easier to deploy and reuse than a separate table of preallocated passwords.


texts: Clustered index on last_update_at timestamp

texts:
I have a Votes table , which can be considered a wrapper.
I have a sub_items table, which holds one votable item. One Vote has many sub_items. On sub_item is owned by only one Vote.
I have a Vote_actions table, that will record what changes were made to the STRUCTURE of a vote/or it sub_items.
I have a table called Vote_casts , which will hold which user voted on which sub_item.

My service will need long-polling (so constant db checking every second or so).

And votes is not the only dynamic thing that I have. Therefore I decided to create a GENERAL_ACTIONS table, which will hold if some kind of action was performed on any of my modules (e.g. of a module is Voting). If there is some action performed, then my poller will look up my Vote_actions table IF some sort of structural change was made to the Vote, and it will look up the Vote_cast table, if a voting was performed, my GENERAL_ACTIONS table will have enum values like "Action_cast_vote","Action_updated_vote_structure".

For action_cast_vote as well as action_vote_structure I will be creating a clustered index on (timestamp,group_id,user_id,vote_id/sub_item_id,id).

I am doing this because I need to order by timestamp to get the newest rows that have just been added.

In my opinion, this table will work for long polling. If there is a brand new row added just after I had last checked, this means there is no way that the client knows of it.

But, say the user wants to SCROLL (it will be lazy loaded). User scrolls up, 50 entities are loaded. If he scrolls up again, 50 new entities are loaded etc.

I will do this by a query like :

  GET 50 NEWEST VOTES WHERE TIMESTAMP IS OLDER THAN X

My Actions_table will not be efficient for this. This is because a Vote might have been already loaded, but still have actions performed at an older time. In that case, the same Vote will be loaded up and sent again. But my client already has that data.

To fix this I want to create a table called Vote_scrollable. It will hold information about when a Vote (or sub item, or if somebody cast a vote) was last updated. By doing this I have a certainty that there is nothing newer/or older for that vote. If A vote has, say, Y time as last updated, I can be sure that SOMETHING was done to that vote.

Now if my user scrolls up, I would run a query like :

  GET 50 NEWEST VOTES FROM SCROLLABLE TABLE WHERE TIMESTAMP IS OLDER THAN X

Again, I will create a clustered index on timestamp, because I need it to be ordered and limited to 50.

My questions are:

Is this design extremely flawed? If so, what are they, and how may I fix them?
Is is okay to have a clustered index on a value that will be updated fairly often? (I have given a vague idea about voting. Users can vote on items/ revote on items/ perform CRUD on sub_items, etc. All these actions will cause update on updated_at timestamp value, which is clustered index of the vote_scrollable table.)
Any tips, help, suggestions etc. are much appreciated.


texts: Migrate MySQL tables to new server with same tables already on it?

texts: We migrated our web server to a newer server. We have the old and new both running while we do testing on the new one. The old one is still being used in production by our clients and internal staff so is more up-to-date than the new one which was current as of a week ago when took a snap shot and did the migration. So I need to migrate the new data on the old server to the new server. What is the best way to accomplish this? If I do a mysqldump and import into the new server, will it overwrite the data already in there? That is fine if so, just concerned about it duplicating records. Basically just want to take everything that is on the old server and dump it into the new one when no one is on the system.


texts: Same server, same query, different response time

texts:
I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes.
The setup is:

* virtual server;
* 4 virtual CPU;
* 8 GB of virtual memory ;
* 80 GB of virtual HD (the real HD is a SDD one), I had still 36 GB available;
* OS Debian 9;
* Mysql 5.6.47;

To avoid all problems about network and the Web App, I simply do my queries directly on the host where Mysql is installed.
I had enabled the logging of slow query, and find the slowest query. This query start from a certain table, that I report below:

CREATE TABLE `MALICIOUS_TABLE` (
  `column_1` int(11) NOT NULL AUTO_INCREMENT,
  `column_2` varchar(8) NOT NULL,
  `column_3` datetime NOT NULL,
  `column_4` int(11) NOT NULL,
  `column_5` int(11) DEFAULT NULL,
  `column_6` int(11) DEFAULT NULL,
  `column_7` int(11) DEFAULT NULL,
  `column_8` tinyint(1) DEFAULT NULL,
  `column_9` datetime DEFAULT NULL,
  `column_10` int(11) DEFAULT NULL,
  `column_11` varchar(2048) DEFAULT 'column_11',
  `column_12` tinyint(1) DEFAULT NULL,
  `column_13` datetime DEFAULT NULL,
  `column_14` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`column_1`),
  KEY `fk_ual_aut_idx` (`column_2`),
  KEY `fk_aul_c_idx` (`column_4`),
  KEY `kf_ual_po_idx` (`column_5`),
  KEY `fk_ual_ute_idx` (`column_10`),
  KEY `column_1` (`column_1`),
  KEY `column_2` (`column_2`),
  CONSTRAINT `fk_aul_c` FOREIGN KEY (`column_4`) REFERENCES `t_table2` (`column_4`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_aut` FOREIGN KEY (`column_2`) REFERENCES `t_tabl3` (`column_2`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_po` FOREIGN KEY (`column_5`) REFERENCES `t_table4` (`column_5`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_ute` FOREIGN KEY (`column_10`) REFERENCES `t_table5` (`column_10`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2357917 DEFAULT CHARSET=latin1

The table has a not so small number of records:

select count(*) from `MALICIOUS_TABLE`;
+----------+
| count(*) |
+----------+
|  2308414 |
+----------+
1 row in set (2,67 sec)

If I try the slowest query, always from the mysql command line on the server, every about 10 seconds, I got different response times, this is the production server, so users keep insert data:

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666411 |
+----------+
1 row in set (4,39 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666477 |
+----------+
1 row in set (4,94 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666752 |
+----------+
1 row in set (17,02 sec)

The last attempt has a great variation of response time. At the beginning I thought that maybe indexes are the problem, I drop them and recreate them. Yet I got the huge variation of the response time.

The RAM of the server it's good, still getting about 2 giga of free RAM.

The query caching of Mysql it's active, and maybe the second attempt retrieve the query from the cache, and the last one no.

Any suggestion of what I can check to understand the problem? The machine, the db (now I'm trying to modify query cache settings) or the table itself?

Thank you in advance.


texts: MySqlDump's DISABLE KEYS have no effect on import

texts:
I have a follow up to my previous question regarding import speed with Inno-Tables (surprise!).

Scenario
I try to import some big* database dump on my local dev machine in reasonable time. We have a lot of KEYs attached to the tables which have turned out to be a bottleneck but are still important for our live system.

My approach after asking question above was to delete KEY ... statements from dump, import and re-add keys.

However I often find myself editing a current dump to import it locally and I stumbled across these funny "comments" (The disable/enable keys-lines)

--
-- Dumping data for table `monster`
--

LOCK TABLES `monster` WRITE;
/*!40000 ALTER TABLE `monster` DISABLE KEYS */;
INSERT … INSERT … INSERT
/*!40000 ALTER TABLE `monster` ENABLE KEYS */;
UNLOCK TABLES;

But in fact these "comments" are conditional MySql-Statements

That was news for me but ok, given the output form mysql --version everything looks fine to me:
mysql  Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.3

What I assume
The table is locked (fine, it's just me on the dev mashine). Then the keys as defined in table schema are disabled, data is imported, keys are enabled.
So during "data insertion"-phase there should be no time wasted on keys but rather examined after all data is inserted.

I would be thinking this is the same behaviour as if I delete all the KEY 'foo' (foo)'-lines from the dump, import the dump and run a script with ADD KEY 'foo' ... afterwards.

What I observe
It is way faster to manually delete the keys, import and re-add keys then relying on conditional DISABLE KEYS statements created my mysqldump

Manual editing of dump + mysql import + adding keys = 15+8+8 ≈ 30min
Plain mysql import: given up, (I'm just getting paid for 8 hours/day >:) )

I can't help but thinking I'm missing something very fundamental here (or database is trolling me).


texts: 'user' is not valid at this position, expecting an identifier

texts:
CREATE TABLE 'users' (
'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
'name' varchar(75) NOT NULL,
'password' varchar(100) NOT NULL,
'email' varchar(100) NOT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'email' ('email')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;


texts: How to detect the "Server admin" user on Azure SQL Server

texts:
The following article describes the AZURE "Server admin" user.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

This user has access to every database but unfortunately the HAS_DBACCESS(%database_name%) function fails to return 1 so I need some other way to detect this user and recognize that they have access to all databases.

I tried a number of the other methods including:

IS_MEMBER ('db_owner')
IS_SRVROLEMEMBER ('serveradmin')
IS_SRVROLEMEMBER ('sysadmin')

Note: Oddly enough the HAS_DBACCESS(%database_name%) actually returns NULL.

Any suggestions would be appreciate.

The below queries were run from the Master database while logged in as rootadmin, which is the name of my Server Admin user.

Edit: When I switched to using the "Demo" database the query results changed and HAS_DBACCESS(t.name) returned NULL for the Master database and "1" for the "Demo" database.

So now I am confused.


texts:
I have created the following multi column index

CREATE INDEX ON message_log (website,  in_use, insert_timestamp);

However when I perform the following query, it does not seem to be using the query as it's taking a very long time to complete.

select id from message_log where website = 'forgezilla' AND in_use = false ORDER BY insert_timestamp NULLS FIRST;

Can the multi column index be used for order by? if so did I create the ordering wrong when creating the index?


texts: MongoDB user account required for arbiter?

texts:
I have a test environment with two mongo v2.6.5 instances (#1, #2) and an arbiter instance in a replica set. I have a a common key file configured for all three instances. I have created an admin account on #1 which replicated automatically so that I could authenticate via the shell to #1 and #2.

The account is not valid on the arbiter, as the admin db doesn't replicate to it. Despite have a keyFile specified in config, I am still able to login to the arbiter apparently anonymously (albeit from localhost) and retrieve rs.status() information. Why is this?

If I try this on instances #1 or #2 it fails unless with a permissions error unless I login with credentials.

What is the correct security config for the arbiter?
Do I need to create an identical user account directly on it or is there a better way to configure it?

Update: relates to https://jira.mongodb.org/browse/SERVER-5479


texts: Increase logging/tracing on Oracle 12c database

texts:
I need to connect TLS-secured-ly to an Oracle 12c (from some random Java program, using JDBC connection)

I set up my listener to use TCPS, I made my wallet and everything and still my program is failing with a handshake failure.

Tracing it on client side is not helfpul.

How can I increase the logging on Oracle side (and where to find extra logs) to debug this?


texts:
I am trying to create a database for 2 Tables like:

  Table1 : tb1-id(pk) | tb2-id | other

  Table2 : tb2-id(pk) | other

  FOREIGN KEY Table2(tb2-id) REFERENCES Table1(tb1-id) ON DELETE CASCADE

Table pseudo-code:

CREATE TABLE Table1 (
tbl1-id        INT PRIMARY KEY
, tbl2-id      INT FOREIGN KEY REFERENCES Table2(tbl2-id) ON DELETE CASCADE
...
)

Table2:

CREATE TABLE Table 2(
, tbl2-id      INT PRIMARY KEY
...
)

There can be 2 records in Table1 that point to a single record in Table2.  If we DELETE 1 of the 2 records pointing to Table2 from Table1 MySQL would also DELETE the referenced record from Table2.  I want the referenced record to be deleted only when both the referencing records in Table1 are deleted.

Is there any other MySQL method/constraint that can fulfill the above requirement.


texts:
I am more of a Vertica database user than admin, so I'm not even sure if what I'm asking is absurd, but here goes nothing...

Could I, as an admin, put default restrictions on the number of rows that can be exported per export query? I would like to make sure most users can only export a certain number of rows at a time, and that they cannot change the setting which prevents a massive export. I realize there are probably be ways around this restriction
Would there also be a way to catch a user pulling small amounts of data incrementally from the same data set?

An answer to question (1) would suffice, but I would appreciate any input on question (2), as well.


texts: Is it dangerous to expose MongoDB's db.collection.find to users?

texts:
I know in SQL land people love referring to Little Bobby Tables as the main reason to have a very strict interface between your user base and your database.

However, I want to implement a robust query solution using MongoDB, and it seems like I can theoretically give a user an interface that grants them full reign to what they want to query for.

The only danger I see is a user creating a long running query, which can be mitigated by using $maxTimeMS with my db.collection.find query. Am I being naive?

I figure since db.collection.find forces queries to a particular collection, users would only have access to that collection. So they could only search in the websites collection, but not in the users collection.


texts: What is the best practice for storing image varbinary(max) data in a database table?

texts:
I have a table that stores images that range in size between 16-100 KB each.  Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type.  The table is constructed simply:

CREATE TABLE Screenshot(
         Id bigint NOT NULL,
         Data varbinary(max) NOT NULL,
CONSTRAINT PK_Screenshot PRIMARY KEY CLUSTERED
(
Id ASC
)WITH (PAD_INDEX  = OFF,
   STATISTICS_NORECOMPUTE  = OFF,
   IGNORE_DUP_KEY = OFF,
   ALLOW_ROW_LOCKS  = ON,
   ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The table is heavily inserted into (2 million records in the past week) and rarely selected.  The key is using a hilo algorithm, so for the most part new rows are added at the end.

I've been having problems when a lot of processes try to insert into this table because of locking and contention.  Queries are timing out from waiting for locks.

Should I migrate this table to its own file group and drive?  How can I improve the insert performance and decrease contention in this type of situation?


texts: Index fragmentation increased significantly after rebuild

texts:
I've googled this and I know that some people say index fragmentation doesn't matter, but they go on to describe scenarios where it might matter.  Mine might be such a scenario.

I have an index on a huge table (86 million rows) that was about 33% fragmented.

A few days ago I started noticing certain queries were taking too long.  It seemed like they weren't using one of the indexes even though the queries are written in such a way that they should use the index (I could be wrong about the index not being used)

So one of the things I did was look at the index fragmentation. It was 33%  I did a rebuild at a time when nothing would be using the table (except that as a test I did run a query on the table at the same time as rebuilding the index - Foolish perhaps).

The rebuild took approximately 10 to 20 minutes. For some reason I didn't check the fragmentation immediately after.  I checked it the day after (queries still slow) and saw that it had increased to 56.06%!

If I try another rebuild will I make it even worse? Should I try a reorganize instead? (I read that Microsoft recommends a reorganize if fragmentation is less than 30% and a rebuild if more than 30%)

Disclaimer: I am aware that there may be some bad practices going on here (size of table, design, or anything)  I didn't create this, I merely inherited responsibility for it.

Edit: I risked another rebuild.  This time checked immediately.  It was 0.01 fragmented.  I checked again ten or so minutes later and it was about 1% fragmented.  I checked again approximately one minute intervals and it's becoming gradually more fragmented.   Some hours later it's now 53.55% fragmented.

Output of @@version:

  Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
  Feb 10 2007 00:59:02
  Copyright (c) 1988-2005 Microsoft Corporation
  Standard Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

Autoshrink is on (shows 'true' in the database properties dialog box)

Edit : Some further information to give extra detail to the question....

At a fixed time each day the table gets a bulk update of approximately 50,000 to 100,000 rows of new data.  For the rest of the day there are no updates or new records,   it's purely queried for data.

The primary key is on 'recordid' and it is an auto increment field.  It's value is higher for new inserts than anything else in the table so there's no having to sandwich the data between existing data in the index.

Here is the create index code for the index in question...

USE [EWS]
GO

/****** Object:  Index [IX_AmtoteAccountActivity]    Script Date: 03/03/2016 21:07:14 ******/
CREATE CLUSTERED INDEX [IX_AmtoteAccountActivity] ON [dbo].[AmtoteAccountActivity]
(
    [AccountNumber] ASC,
    [_Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

In case it matters/is relevant (I'm not sure if it is) here is the create index code for the primary key...

USE [EWS]
GO

/****** Object:  Index [PK_AmtoteAccountActivity]  Script Date: 03/03/2016 21:10:11 ******/
ALTER TABLE [dbo].[AmtoteAccountActivity] ADD  CONSTRAINT [PK_AmtoteAccountActivity]
PRIMARY KEY NONCLUSTERED
(
    [RecordID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY]
GO

Finally, as suggested by @Shanky and @KookieMonster I believe the fragmentation (which steadily grows after rebuild) is caused by auto_shrink being on for this database.


texts:
I am trying to simply shutdown and restart an Oracle db instance on a Windows Server 2008 machine (Oracle 11.2.0).  Logged in via sqlplus, I issue the command shutdown immediate;.  I get the

Database closed.
Database dismounted.
ORACLE instance shut down

reponses.  I exit out of sqlplus, and try to log back in (ORACLE_SID is set correctly) so I can issue the startup mount; command, but I get the

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

error.  All of the Windows services involving my database dantest3 are started.  Doing research, I found the oradim command, but while that starts my database and allowing me to login, that is the full startup, and I want to only be in "mount" mode.

I am logging in with the following command -

sqlplus sys/[passwordforacct]@dantest3 as sysdba

If I try the simple sqlplus / as sydba, I get a

ORA-12560: TNS:protocol adapter error

oradim command -

oradim -start -sid dantest3

Requested variables -

TNS_ADMIN = C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN
ORACLE_SID = dantest3
ORACLE_HOME_11gR2 = C:\Oracle\product\11.2.0\dbhome_1


texts: How can I set block size on Oracle 11 when creating a database instance

texts:
Is there any way to configure Oracle 11g's default block size on installation?
I have tried Oracle 11g r2 and r3, during both installations block size is 8k and it is disabled (and can not be modified).

I shall restore a data-pump backup from a 16k block size database, so I need to make mine 16k too.

I know I can do this by assigning block size to individual tablespaces (data files), but I am looking for a way to set the default data file size on the whole oracle instance.


texts:
I accidentally deleted 2 data (mydb.47, mydb.62) files from my /data/db of my mongodb. I have rest of the files in place...

Now my mongodb won't start up..I tried repairing it using this command

sudo -u mongod  mongod  --dbpath /data/db-new --repair --repairpath /data/db

Repair finished with no errors. Mongo docs say

  You may also wish to move the repaired files to the old database location or update the dbPath to indicate the new location

I copied the files from /data/db-new to /data/db and started mongo server. But it is not starting...

Is there any way I could reclaim my data?


texts: Group results from two different tables on same hours

texts:
I have two simple tables:

indoor

id | timestamp | temp | humi

outdoor

id | timestamp | temp

And two selects which give me time and average temperature grouped by same hour for the last 24 hours:

SELECT DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;

SELECT DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;

And now what I need to do is to group those two results by same hour, with respect to possibility that there can be no records in indoor or outdoor table for whole hour, so I need to get:

time | avg_out_temp | avg_in_temp
11:00 | 12.5 | 21.4
12:00 | 13.9 | null
13:00 | null | 22.4
14:00 | 14.0 | 22.5

I'm working with MariaDB:

mysql  Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2


texts: Can plpython3u open files on the file system?

texts:
I am trying to write a plpython3u function that should open a file in the file system and read some values out of it that get returned by a query. But I am getting a permission denied error when doing so. I am well aware of SQL injection and the dangers of mixing up the database with the file system, this is just for the sake of testing the boundaries for my own knowledge, not for deployment in a production environment.

I tried using chmod 777 on the file in question so that anybody can do anything with it, but I still get permission denied when trying to open the file.

This is the script in question:

CREATE OR REPLACE FUNCTION roof_type_to_name(roof_type TEXT) RETURNS TEXT AS
$$

import xml.etree.ElementTree as ET
ns = {'gml': "http://www.opengis.net/gml",
    'bldg': "http://www.opengis.net/citygml/building/1.0"}
rooftype_schema = ET.parse(r'/path/to/file.xml')
definitions = rooftype_schema.findall(".//gml:Definition", ns)
definition_list = list(definitions)
for definition in definition_list:
    prop_list = list(definition)
    if prop_list[1].text == roof_type:
        return prop_list[2].text

$$ LANGUAGE plpython3u;

I also know that there are other ways of reading xml with postgres, again I am just using this as a personal learning experience with plpython3u with something I am familiar with.

Is it possible at all to open a file on the file system with plpython3u? Or is it totally locked-down for safety reasons?


texts: MySQL: Does the default value checked by the `CHECK` constraint?

texts:
Say I have the following schema, would the default value https://sample.com be checked by the CHECK (Website LIKE "https://%")?
CREATE TABLE Company (
    AccountID varchar(25),
    Name varchar(20)        NOT NULL,
    Website varchar(255)    DEFAULT 'https://sample.com',
    Categories varchar(255) NOT NULL
                            DEFAULT '[]',
    PRIMARY KEY(AccountID),

    CHECK                   (CHAR_LENGTH(AccountID) BETWEEN 4 AND 25),
    CHECK                   (CHAR_LENGTH(Name) BETWEEN 2 AND 20),
    CHECK                   (Website LIKE "https://%")
);


texts: Why does DBCC TRACEON (1204, 1222) not work?

texts:
I have executed DBCC TRACEON (1204, 1222) on several servers.

Some of them simply do not turn the trace flags on. The command brings the same expected informational message for each but some simply do not react.

DBCC TRACESTATUS(1204, 1222) delivers  0, 0, 0 in this case for both flags.

Why do they ignore it?


texts: SSIS: How do I create tables using Foreach Loop Container?

texts:
I got a bunch of .sql files each containing a script that would create a certain table.

I want to create tables using these scripts in said files (each table to be created using one file).

I got a foreach loop container that specifies the path and which files to use.

I don't know how to configure the Execute SQL Task inside the foreach loop container to execute the script in each one of these files in order to create the tables.


texts: What is the best way to save a large array in database?

texts:
Currently, I'm working on a simple CBIR (Content-based Image Retrieval) and I have a question.

What is the best way to save a large array on the database? For example, I want to save the results of Canny or GLCM (Gray-level Co-Occurrence Matrix) which is very large.

Also, if someone knows about Image Retrieval techniques, Please tell me is it a good way to save all of the information which mentioned algorithms produced for comparison or not? If it's not, so what is a good way?

Thanks.


texts:
I encountered a strange behavior and I would like to understand better what is happening.
I set in my.cnf the parameter open_files_limit = 32000 in the [mysql] section.
I restarted MariaDB. I have two processes mysqld_safe and mysql.

Result of ps:

root     10439     1  0 11:27 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe
--datadir=/var/lib/mysql/data
--pid-file=/var/lib/mysql/data/rcentdb02.pid

mysql    10880 10439  0 11:27 pts/1    00:01:14 /usr/sbin/mysqld --basedir=/usr
--datadir=/var/lib/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql
--log-error=/var/lib mysql/data/rcentdb02.err --open-files-limit=32000
--pid-file=/var/lib/mysql/data/rcentdb02.pid --socket=/var/lib/mysql/mysql.sock
--port=3306

Result of cat /proc/$(pgrep mysqld_safe$)/limits | grep "Max open":

Max open files            32000                32000                files

Result of cat /proc/$(pgrep mysqld$)/limits | grep "Max open":

Max open files            90162                90162                files

My question is: why mysql_safe have the good parameter for open_files_limit but not mysqld process?

Thanks.

The result of SHOW GLOBAL VARIABLES LIKE 'open_files_limit' is 90162.
I tried to remove the open_files_limit from my.cnf but it does not work.

I have always this value 90162 which seem to be setup nowhere ...

Any idea?

I setup my.cnf to have:

[mysqld]
open_files_limit = 32000
[mysqld_safe]
open_files_limit = 32000

I stop mysql and start mysql.
Result of SHOW GLOBAL VARIABLES LIKE 'open_files_limit'; => 90162

I changed the value of open_files_limit to 100000 and now the result of query SHOW GLOBAL VARIABLES LIKE 'open_files_limit' is 100000. So now my question is : I understand that mysql may autoadjust this parameter but how he can choose a value (90162) higher than the system limit ... ?

With root account:

ulimit -Sn => 1024
ulimit -Hn => 4096

With mysql account (I changed nologin to bash for my test):

ulimit -Sn => 32000
ulimit -Hn => 64000


texts: SELECT query slowing down the wal replay in replication server

texts:
While running a SELECT query (bit large query ~1GB of data output which took ~15minutes to execute) in hot standby replication host, its halts the wal archiving process. Once the Query done with the execution, the replication takes place and started replicating. Is that normal behaviour of postgres or need to tune system runtime configuration?

I have tried to change work_mem=64MB to work_mem=96MB, but still I couldn't solve the delay.

(using PG 9.5)


texts: How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

texts:
I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use it, it seems to ignore any index I put on it:

    -- +++ THE QUERY THAT I WANT TO IMPROVE PERFORMANCE-WISE +++

    SELECT TOP 1 *
    FROM    dbo.TB_test1 t1
            INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1
    ORDER BY t1.somethingelse1
           ,t2.somethingelse2;

    GO

The table setup is as follows:

two tables
they are joined by an inner join by the query above
and ordered by a column from the first and then a column from the second table by the query above; only TOP 1 is selected
(in the script below there are also some lines to generate test data, just in case it helps reproducing the problem)

-- +++ TABLE SETUP +++

CREATE TABLE [dbo].[TB_test1]
    (
     [PK_ID1] [INT] IDENTITY(1, 1)  NOT NULL
    ,[something1] VARCHAR(40) NOT NULL
    ,[somethingelse1] BIGINT NOT NULL
        CONSTRAINT [PK_TB_test1] PRIMARY KEY CLUSTERED ( [PK_ID1] ASC )
    );

GO

create TABLE [dbo].[TB_test2]
    (
     [PK_ID2] [INT] IDENTITY(1, 1)  NOT NULL
    ,[FK_ID1] [INT] NOT NULL
    ,[something2] VARCHAR(40) NOT NULL
    ,[somethingelse2] BIGINT NOT NULL
        CONSTRAINT [PK_TB_test2] PRIMARY KEY CLUSTERED ( [PK_ID2] ASC )
    );

GO

ALTER TABLE [dbo].[TB_test2]  WITH CHECK ADD  CONSTRAINT [FK_TB_Test1] FOREIGN KEY([FK_ID1])
REFERENCES [dbo].[TB_test1] ([PK_ID1])
GO

ALTER TABLE [dbo].[TB_test2] CHECK CONSTRAINT [FK_TB_Test1]

GO

-- +++ TABLE DATA GENERATION +++

-- this might not be the quickest way, but it's only to set up test data

INSERT INTO dbo.TB_test1
        ( something1, somethingelse1 )
VALUES  ( CONVERT(VARCHAR(40), NEWID())  -- something1 - varchar(40)
          ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1)   -- somethingelse1 - bigint
          )

GO 100000

RAISERROR( 'Finished setting up dbo.TB_test1', 0, 1) WITH NOWAIT

GO

INSERT INTO dbo.TB_test2
        ( FK_ID1, something2, somethingelse2 )
VALUES  ( ISNULL(ABS(CHECKSUM(NewId())) % ((SELECT MAX(PK_ID1) FROM dbo.TB_test1) - 1), 0) + 1 -- FK_ID1 - int
          ,CONVERT(VARCHAR(40), NEWID())  -- something2 - varchar(40)
          ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1)   -- somethingelse2 - bigint
          )

GO 100000

RAISERROR( 'Finished setting up dbo.TB_test2', 0, 1) WITH NOWAIT

GO

The indexed view should probably be defined as follows and the resulting TOP 1 query is below. But what indexes do I need so that this query performs better than without the indexed view?

    CREATE VIEW VI_test
    WITH SCHEMABINDING
    AS
        SELECT  t1.PK_ID1
               ,t1.something1
               ,t1.somethingelse1
               ,t2.PK_ID2
               ,t2.FK_ID1
               ,t2.something2
               ,t2.somethingelse2
        FROM    dbo.TB_test1 t1
                INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1

    GO

    SELECT TOP 1 * FROM dbo.VI_test ORDER BY somethingelse1,somethingelse2

    GO


texts: TEMP Tablespace Exceeded when DBMS_REDIFINITION

texts:
Oracle Environment.
Table size is 500Gb. Free space left on hard drive - 300Gb. TEMP Tablespace is currently 20GB
Task: do the DBMS_Redefinition for the table (partition by month) and optimize performance.

PROBLEM 1 - INDEXES
I decided to try out the process on a smaller table first. I took a similar table by structure around 50 GB size. The redefinition ran okay, but I exceeded the temp tablespace when creating indexes. Tried several options: Regular Indexes Creation, Global Partitioned Index Creation, but no luck.

Ran the following to identify the TEMP tablespace required. Doesn't look like it exceeds the allocated space.

 declare
   u_bytes number;
   a_bytes number;
begin
   dbms_space.create_index_cost
   (
      'create index days_ind on Tab (PAr1, PAr2)',
      used_bytes => u_bytes,
      alloc_bytes => a_bytes
   );
   dbms_output.put_line ('Used MBytes      = '|| u_bytes/1024/1024);
   dbms_output.put_line ('Allocated MBytes = '|| a_bytes/1024/1024);
end;

Is there a way to sacrifice performance, but minimize the temp tablespace used? I realize that indexing of a 500 GB will require over 200GB.

PROBLEM 2 - SPACE REQUIRED
I realize that DBMS_REDEFINITION requires free space of the same size as the table itself. Therefore it looks logical enough to do ONE partition at a time.

QUESTIONS:
1. Any useful queries to monitor the resources? (I've already seen this)
2. Suggestions about the right format of INDEXES, optimizing INDEX CREATION to fit into a smaller TEMP TABLESPACE?
3. Would the DBMS_REDEFINITION partition by partition (around 60 partitions) work the best in this case? Any suggestions on performing the procedure?


texts:
I have this SQL query that I have to speed up:

SELECT DISTINCT A.attribute1, A.attribute2
FROM table1 A, table2 B
WHERE A.attribute1 = B.attribute3
AND B.attribute4 = 'String';

What I am currently doing is creating a hash index on B.attribute3 to speed up the JOIN and creating another hash index on B.attribute4 to speed up the equality with 'String'.

CREATE INDEX atr3_idx ON table2 USING HASH (attribute3);
CREATE INDEX atr4_idx ON table2 USING HASH (attribute4);

Is there a better way to optimise this query?


texts: How to add generated timestamp on existing table

texts:
on Db2 v11.1 fixpack 5 on Linux I have table with automatically generated timestamp:
create table admin.tab (
id int,
datetime timestamp not null generated always for each row on update as row change timestamp
)
;

Now I need to drop generated timestamp, because I need to manually change some datetime data
alter table admin.tab alter column datetime drop generated
;

Now I do the update (not important to us).
So far everything works as expected.
Finally, I would like to recreate back "generated timestamp column"
alter table admin.tab alter column datetime set generated always for each row on update as row change timestamp
;

but I am getting the error message:

SQL0104N  An unexpected token "alter table admin.tab alter column
datetim" was found following "BEGIN-OF-STATEMENT".  Expected tokens
may include: "".  SQLSTATE=42601

How to add generated timestamp back to the table?
Regards


texts:
I'm searching one way to set ARITHABORT ON for all opened connections, and I found this question.

Some answers says to run the following command, and it really works:

EXEC sys.sp_configure N'user options', N'64'
GO
RECONFIGURE WITH OVERRIDE
GO

But, for me run this command in my company I need some explanations, and I searched a lot of about user options, but I don't understand some things:

Why the value needs to be 64?
How does it works?


texts: Why does assigning FOR XML to an XML variable warn about CONVERT_IMPLICIT(XML)?

texts:
SET STATISTICS XML ON;
DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'));

  Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1001],0)) may affect "CardinalityEstimate" in query plan choice

Why is a conversion happening? I thought that FOR XML caused the result to be the XML type already. If this warning can/should be ignored, how do I stop the warning from appearing in the execution plan (so that when I debug a bigger query with a similar warning it doesn’t distract me from actual issues)?


texts: I'm getting the below error while trying to ADD CONSTRAINT in Azure DataWarehouse

texts:
Msg 104467, Level 16, State 1, Line 1
Enforced unique constraints are not supported in Azure SQL Data Warehouse. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement.


texts:
I have successfully setup ndbcluster version 7.1.26. This contains 2 data nodes[NDBD], 2 mysql [MYSQLD] nodes and one management [MGMD] node. Replication works successfully. My Web application is deployed in JBoss-5.0.1 and using JNDI for connection resources which are specified in application specific ds.xml file in load balanced url forms e.g. jbdc:mysql:loadbalance:host1:port1,host2:port2/databaseName.

host1 : refers to first mysqld node and port1 refers the port it is running on. host2 : refers to second mysqld node and port2 refers the port it is running on.

When both of the [MySQLD] nodes are up and running everything works fine and cluster responds well, replicates data, and data retrieval operations also work properly.

But issues are raised when any of the [MySQLD] nodes goes down. Data gets inserted/updated/replicated but the application is unable to retrieve data from cluster and web page remains busy working which means busy retrieving data. As soon as the node which was down goes up it responds properly and application goes forward and shows up data retrieved from cluster.

At JBoss 5.0.1 startup it showed up a NullPointerException in class LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:439). Tell me if the above Exception plays any role in the above explained issues.

If anyone had faced issues like above and if has any solution regarding the issues please let me know.

Thanks and regards.