texts: Deleted MySQL "root" user (using phpMyAdmin)...how to restore?

texts:
I'm test-running a Joomla installation (locally, using WAMP) with an MySQL database supporting it...this is in preparation to moving everything online once I have it configured.

I was working in phpMyAdmin to change the password of a superuser I had set up with a login-name other than "root."  While doing so, I saw three additional "root" users, and on the phpMyAdmin home page, a warning about these root users not having a password.  I thought that since I created my own superuser with "All Privileges," I didn't need the root users...so I deleted them.  Immediately things stopped working...and now, when I click on the "Users" tab in phpMyAdmin, it simply returns a red bar that says "No Privileges"...my specific superuser is gone.  However, it seems I can still move around in phpMyAdmin (maybe because I'm still logged in?).

Could someone please tell me step-by-step how (if?) I can re-add the root users using phpMyAdmin?  I see instructions posted in various places, but they seem to address users who are using terminal windows or some other program.  I'd  hate to see months of work go down the drain!

EDIT:  Using Windows, I've tried the following:

There is no file called "resetroot.bat" anywhere in the wamp/sql folders, as someone suggested...so that option is out.
I've tried to access MySQL using a terminal connection:  in the MySQL root folder there is a file called mysql.exe that seems to grant this when executed (there is no "mysql_safe.exe" as some solutions have suggested).  However, I have been unsuccessful in starting mysql with the --skip-grant-tables option from outside mysql.exe, and when I try to do it from within mysql.exe, I just get an odd prompt ("->") and no other commands seem to work.

Please keep in mind that I am a newbie at this!  I am trying different things, but without any success...that is why I'll probably need step-by-step instructions.


texts: DBCC Refuses to Shrink a Data File - drive was full

texts:
Yes, fully aware of Paul's article, and "DON'T", et al...

This is SQL 2014 Enterprise - Build 12.0.5538 - SP2 CU3 - iron - 2x8 1TB RAM - all SSD.

There is an unused FILESTREAM data file:

file_id  Size in MB type_desc   state_desc  is_percent_growth   growth  Growth in MB
65537          0    FILESTREAM  ONLINE            0               0        0

A data migration by a.n.other Team inadvertently filled the entire data drive. Adding another set of RAID-10 disks to the existing volume would take two weeks (Hosting Vendor doesn't move very fast). As it is full, SQL Monitor is showing the space as full and alerting to that effect.

Aim: bring the data file down from 2.1TB to at least 20% less, to stop the nag-alert, as that's getting Support's attention, Hosting and internal, and we would like to stop those needless investigations.

Problem:

So we can stop the SHRINKFILE if we need to, we nibble away in 5GB chunks.  The file is at 2135040MB - still too high to shut the Alerts up.  106GB Free on the 2.18TB drive.

The target is 2129920MB for the current chunk.

The Shink ==> Files SSMS Dialog shows the minimum is 1391374MB - we went nuclear on garbage-collection and post-migration truncations.

Repeatable road-block:

 2018-03-05 16:21:48.730 - Started: Original Size 2135040 MB
 2018-03-05 16:21:48.730 - Shrink: to 2129920 MB
File ID 1 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 2018-03-05 16:22:11.250 - Ended: Shrink failed to run - see Messages

The is no such "...another process...", neither, obviously from SSMS Shrink's perspective, is it "...empty."  In fact, this was run on as "silent" a box as we can get - just two DELAY pollers, and nothing, but nothing else running according to sp_whoisactive.

I tried adding some space and retrying.  I had to add MANY MB, not just a few, as was suggested.  The first 5GB SHRINKFILE went OK, then the above error.

Can anyone assist, please?  (Seriously hope it's something obvious to a "real" DBA)  With thanks.


texts: Case Sensitive Ordering within the CMS Server List

texts:
I've got to believe there's a reason for this, but for the life of me I cannot figure it out.  It looks like ordering of Server Names within any Central Management Server is Case SENSITIVE, regardless the collation of the server acting as the CMS, itself.

Attached is an example of a CMS hosted on my local machine, which is configured with a server collation of SQL_Latin1_General_CP1_CI_AS.  CI meaning Case Insensitive.  When selecting from the system table holding the CMS objects, the proper order is returned, however as you can see in this screenshot, the way the servers are listed in the UI, the order is obviously being treated in a Case Sensitive manner.

Since it doesn't seem to be server-level related, I assume it's client tools related, but there is no where within the tools that I can identify a collation level (or even case sensitivity setting) that would affect this.

Obviously there is nothing critical about this question, but if someone does know how to force the UI to be case insensitive, I'd appreciate it as I like to Camel Case my server names which is throwing off the ordering.


texts: Galera Arbitrator system requirements compared with the actual Galera MariaDB node requirements

texts:
Right now our setup is CloudFlare > 1x HAProxy (Dallas,TX), 3x App servers (Dallas,TX), 3x MariaDB nodes in a Galera multi-master cluster (Dallas,TX).
The App servers auto-scale horizontally to handle whatever traffic gets thrown at them. HAProxy and DB servers are scaled up when needed.
The App servers also maintain individual SQL read caches to ease the load on the DB.

Our main selling point is keeping our product online no matter what happens, as our solution caters to government emergency services, and since we're a start-up, our budget is really tight.

It was unexpected that our current cloud provider (which we cannot change in the following months) is experiencing DDOS for around 30minutes a month. For our situation not even one minute of downtime is acceptable.

So I am thinking of going with something like CloudFlare > 3x HAProxy (Dallas,TX; Freemont,CA; Newark,NJ) > 6x App servers (Dallas,TX; Freemont,CA; Newark,NJ) > 3 Galera Multi-master + 2 Galera Arbitrator (Dallas,TX; Freemont,CA; Newark,NJ; Atlanta,GA; San Francisco,SF).

This will increase our costs quite a bit, and that is why I want two Galera Arbitrators if I can skimp on costs while at the same time increase stability.

My main question is, what are the resource requirements for Galera Arbitrator, and would it be worth having three MariaDB nodes and two Galera Arbitrator nodes, instead of only three MariaDB nodes in the cluster?
Another way to put it is, is it worth using garbd on setups over three nodes in size?

Provided that route-wise our HAProxy servers will choose the App servers in the same region, which subsequently choose the DB server in the same region. The choices are based on a blend of connect time and server load.


texts:
So basically this is what I did:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=PASSWORD("~)<iXyyj<w!,s!\yZ%]YN#m;d") where User='root';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

And now I can't access using that password. What did I do wrong? maybe the password isn't supposed to have special characters or something?

PS: centos 6.5


texts:
I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise.

In short, I have what I think is a PostgreSQL dump file that someone sent me, and I need to be able to open it up to extract the information. I was expecting a simple tabular format (such as an Excel spreadsheet), but I got something way more complicated (a .backup file). I know how to run some basic SQL queries, but I think whoever sent this to me strongly overestimated my knowledge of database programs. Could someone give me a very, very, VERY simple, completely 110% spoon-fed explanation on how to open this?

(As an aside, I know the person who sent this to me and I know the nature of the file, so I know it's not something completely different.)


texts: Is an OLAP cube a duplication of data or is it just references to original tables? Or can I control it?


texts: database sharding - handling common/universal tables

texts:
Possible Duplicate:
  sql server database sharding - what to do with common data / non sharded data

We have a very large scale enterprise level database. As part of our business model all web users hit our web servers at the same time each month which in turn hammer our sql box. The traffic is very heavy and continues to grow heavier the larger the company grows. sql proc optimization has been performed and hardware has already been scaled up to a very high level.

We are looking to shard the database now to ensure that we can handle company growth and future loads.

We have decided what particular data should be sharded. It is a subset of our database which is highly utilized.

However, my question is regarding the non sharded data which is common/universal. An example of data like this may be an Inventory table for instance or possibly an Employee table, user table etc .

I see two options to handle this common/universal data:

1) design 1 - Place the common/universal data in an external database. All writes will occur here. This data will then be replicated down to each shard allowing each shard to read this data and inner join to this data in t-sql procs.

2) design 2 - Give each shard its own copy of all common/universal data. Let each shard write locally to these tables and utilize sql merge replication to update/sync this data on all other shards.

concerns about design #1

1) Transactional issues: If you have a situation in which you must write or update data in a shard and then write/update a common/universal table in 1 stored proc for instance, you will no longer be able to do this easily. The data now exists on seperate sql instances and databases. You may need to involve MS DTS to see if you can wrap these writes into a transaction since they are in a separate database. Performance is a concern here and possible rewrites may be involved for procs that write to sharded and common data.

2)a loss of referential integrity. Not possible to do cross database referential integrity.

3) Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards.

4). increased database trips. Like #1 above, when you run into a situation in which you must update sharded data and common data you are going to make multiple round trips to accomplish this since the data is now in separate databases. Some network latency here but I am not worried about this issue as much as the above 3.

concerns about design #2

In design #2 each shard gets its own instance of all common/universal data. This means that all code that joins to or updates common data continues to work/run just like it does today. There is very little recoding/rewriting needed from the development team. However, this design completely depends on merge replication to keep data in sync across all shards. the dbas are highly skilled and are very concerned that merge replication may not be able to handle this and should merge replication fail, that recovery from this failure is not great and could impact us very negatively.

I am curious to know if anyone has gone with design option #2. I am also curious to know if i am overlooking a 3rd or 4th design option that I do not see.

thank you in advance.


texts: PageIOLatch_Sh waits noticed after tuning an index

texts:
Lately, a lot of queries to the database are timing out. While investigating I found that queries are in the suspended state with wait_type as PageIOLatch_Sh. I have not noticed so many queries being suspended before on the server. This has been happening for the since Saturday.

A couple of weeks ago, the cpu on the db server was at 100% constantly. While trying to fix that I changed a couple of indexes which were not really being used and were resulting in user_scans. After changing the indexes, the cpu usage on the server went down, the performance of the site was generally faster and looking at the index stats now a high number of user_seeks were happening, which I thought was a good sign.

However, now after a week or two, even though the cpu usage is not at 100, a large number of queries have started timing out and PageIoLatch_Sh waits are being observed. The interesting thing is that these timeouts were not happening at such a high frequency earlier even though the cpu was at 100%.

Now maintaining indexes is also something which slows down processing of sql server, so I was wondering whether the new indexes could have resulted in a larger number of these waits and timeouts?


texts:
When I'm trying to start a database (after a server patch was applied), I get the following error and I am unable to open the database.

SQL> startup;
ORACLE instance started.

Total System Global Area 7465926656 bytes
Fixed Size                  2267744 bytes
Variable Size            1241515424 bytes
Database Buffers         6207569920 bytes
Redo Buffers               14573568 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1:
'/u00/oracle/app/oracle/product/oradata/XRO/sy_XRO.dbf

NOTE:

SQL> select database_role from v$database;

DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY

SQL> select status from v$instance;

STATUS
------------------------------------
MOUNTED


texts: Monthly Date and Hour wise report generation using PHP MYSQL

texts:
Attendance Portal in PHP

I am facing problem at Final Attendance Report Generation

I'm stuck at the final stage of the Project:

I have a Table- attendance, If a student is Present I mark it as 1 if absent 0. we have a date, day order, hour (we are following Day Order Time Table)

 Table
    dayorder:
        id day
        1 day1
        2 day2
        3 day3
        4 day4
        5 day5
        6 SAT
        7 day6
Table
Hour:
   id
    1
    2
    3
    4
    5
    6
    7

Table attendance:

I must generate a Monthly report like a register as below

Code I have Tried:

SELECT student_id,deptno,Month, Year_c,
branch.description as bdesc,
course.coursecode as ccd,
users.firstname as ufn,
users.lastname as uln,
course.description as ccdes,schedules.hour as hhour,
h,dayorder,AttdDate,
CONCAT(AttdDate,h,dayorder) AS fate,
IF(Day_c=1, p, " ") AS '1',
IF(Day_c=2, p, " ") AS '2',
IF(Day_c=3, p, " ") AS '3',
IF(Day_c=4, p, " ") AS '4',
IF(Day_c=5, p, " ") AS '5',
IF(Day_c=6, p, " ") AS '6',
IF(Day_c=7, p, " ") AS '7',
IF(Day_c=8, p, " ") AS '8',
IF(Day_c=9, p, " ") AS '9',
IF(Day_c=10, p, " ") AS '10',
IF(Day_c=11, p, " ") AS '11',
IF(Day_c=12, p, " ") AS '12',
IF(Day_c=13, p, " ") AS '13',
IF(Day_c=14, p, " ") AS '14',
IF(Day_c=15, p, " ") AS '15',
IF(Day_c=16, p, " ") AS '16',
IF(Day_c=17, p, " ") AS '17',
IF(Day_c=18, p, " ") AS '18',
IF(Day_c=19, p, " ") AS '19',
IF(Day_c=20, p, " ") AS '20',
IF(Day_c=21, p, " ") AS '21',
IF(Day_c=22, p, " ") AS '22',
IF(Day_c=23, p, " ") AS '23',
IF(Day_c=24, p, " ") AS '24',
IF(Day_c=25, p, " ") AS '25',
IF(Day_c=26, p, " ") AS '26',
IF(Day_c=27, p, " ") AS '27',
IF(Day_c=28, p, " ") AS '28',
IF(Day_c=29, p, " ") AS '29',
IF(Day_c=30, p, " ") AS '30',
IF(Day_c=31, p, " ") AS '31'
FROM
(SELECT *,DAY(date) AS Day_c,
MONTHNAME(date) AS Month,
Year(date) AS Year_c,
date(date) AS AttdDate,hour as h, day as dayorder,
(CASE  WHEN present = 1
    THEN 'P'
    WHEN present = 0
    THEN 'A'
    WHEN present is null
    THEN ' '
END) AS p
FROM attendance a
WHERE date between '$from' AND '$to' And branch = $branchid AND coursecode = $courseid AND batch = $batchid
GROUP BY student_id
ORDER BY student_rollno ASC
)
as report
LEFT JOIN branch on branch.id = report.branch
LEFT JOIN course on course.id = report.coursecode
LEFT JOIN users on users.id = report.user
LEFT JOIN schedules on schedules.id = report.hour
ORDER BY Month DESC, Year_c DESC

OUTPUT in PHPMYADMIN:

But I am not able to show up as a HTML Page using PHP and MY SQL.
How to get data without dates which are not recorded?


texts: SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

texts:
Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server's databases. I have a script to do this, and it has been working quite well when the local server is 2008 R2 or 2017. But I just installed 2019 and the same script puts the same value in every row of the table, rather than a different value for each row. It is as though the function is executed only once, then that value is used in the UPDATE statement. Perhaps it is some kind of optimization 2019 is doing? Here is a snippet of the script.
UPDATE Guest SET GuestFirstName=Utility.dbo.RANDWORD() WHERE GuestFirstName IS NOT NULL;
RANDWORD grabs a random word from a table containing approximately 100,000 English words.


texts: ODAC OLE DB provider "OraOLEDB.Oracle" for linked server supplied inconsistent metadata for a column

texts:
I am using SQL Server 2014 linked server to connect to an Oracle 11gR2 (11.2.0.3) database, using the ODAC 12.1.0.2.4 Oracle OLE DB Provider. I have a stored procedure that lives in a SQL 2014 database and inserts data from a local table into a remote Oracle table via the linked server, using the following syntax:

insert into LinkedServerName..RemoteSchema.RemoteTable(column)
select column from localSQLServerTable

However, I receive the error below on the line of the insert:

  Msg 7356, Level 16, State 1, Procedure {MyProcName}, Line 1 The OLE
  DB provider "OraOLEDB.Oracle" for linked server "{MyLinkedServer}"
  supplied inconsistent metadata for a column. The column "{columnName}"
  (compile-time ordinal 23) of object "{remote table name}" was reported
  to have a "DBCOLUMNFLAGS_ISFIXEDLENGTH" of 16 at compile time and 0 at
  run time.

The column in question is defined in Oracle as CHAR(1 BYTE) data type.

Installed "64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64", download name "ODAC121024Xcopy_x64.zip" from http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html


texts: Add a DB2 catalog to a new remote server and make it work

texts:
I'm trying to add a new database catalog to my client environment in a windows server machine.

So I've run the db2cmd tool and then the db2 tool on a windows command console once there I executed the command:

db2 => list db directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = DB01
 Database name                        = DB01
 Node name                            = DB01
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = DB02
 Database name                        = DB02
 Node name                            = DB02
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Then I add a new catalog following the docs:

db2 => catalog tcpip node DB03 remote ***.***.***.*** server *****
DB20000I The CATALOG TCPIP NODE command completed successfully.
DB21056W The directory changes may not take effect until the directory cache is
updated.

After that I run list db directory again and the result was the same as previous one. I then, run the command:

 db2 => list node directory

  Node Directory
  Number of entries in the directory = 3

 Node 1 entry:
  Node name                      = DB01
  Comment                        =
  Directory entry type           = LOCAL
  Protocol                       = TCPIP
  Hostname                       = ***.***.***.***
  Service name                   = *****

 Node 2 entry:
  Node name                      = DB02
  Comment                        =
  Directory entry type           = LOCAL
  Protocol                       = TCPIP
  Hostname                       = ***.***.***.***
  Service name                   = *****

 Node 3 entry:
  Node name                      = DB03
  Comment                        =
  Directory entry type           = LOCAL
  Protocol                       = TCPIP
  Hostname                       = ***.***.***.***
  Service name                   = *****

So the node was created but it didn't show on the directory list, looking through the docs it says that I should run the terminate command

  Notice:
  The command terminate is needed to refresh the directory cache.

Then it leave the db2 terminal and then I entered it again run the list db directory and again nothing changed.

So, my question is: How do I refresh the database directory??

I'm new to DB2. I have a lot experience with Oracle and what I could understand that this configuration is like adding a new entrance on the the tnsnames.ora file


texts:
I am working on the web analytics it is similar to google analytics,  Currently i am using the mongoDB.
Problem using MongoDB
Writing and reading is performed at the same time so after some point writing is bit slow. So i am looking for appropriate database which is suitable for following requirement.
Requirements

Open Source

Can adapt large volume of data.

Better performance while aggregating the data.

Time based aggregation.

Real time processing.

Driver support for node.js and java.

Database should be scalable with node.js and java
 Saving the data using java.

 Fetching the data using node.js


texts: Reasons to maintain FKs on an empty, deprecated table?

texts:
So I have a few dozen empty tables with foreign keys to other, populated tables. These tables are not being accessed by any CRUD operations, but have about 6-12 months before they can be unlinked from Entity Framework applications (due to project timelines) and so are sticking around for a while.

These tables will definitely never be used again and any relationships are already documented / unnecessary to retain.

Does dropping these foreign keys have any benefits?


texts: Default filegroup, specified in the SQL Server CREATE INDEX statement, is ignored (ON [DEFAULT] storage clause)

texts:
Am I missing something?

Documentation clearly states, that:

  ON "default" Applies to: SQL Server 2008 through SQL Server 2017 and
  Azure SQL Database.

  Creates the specified index on the default filegroup.

see: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017

Although, it seems that ON [DEFAULT] is ignored and index would be created on the table's filegroup, unless you explicitly specify some other filegroup.

Consider (tested on 2014/2016/2017, patched to the latest):

-- 1. create empty db with second filegroup
CREATE DATABASE [idxtest]
 ON  PRIMARY
( NAME = N'idxtest', FILENAME = N'c:\SqlData\Data\Sql2014\idxtest.mdf' , SIZE = 10MB , FILEGROWTH = 1MB ),
 FILEGROUP [def_test]
( NAME = N'def_test', FILENAME = N'c:\SqlData\Data\Sql2014\def_test.ndf' , SIZE = 10MB , FILEGROWTH = 1MB )
 LOG ON
( NAME = N'idxtest_log', FILENAME = N'c:\SqlData\Data\Sql2014\idxtest_log.ldf' , SIZE = 10MB , FILEGROWTH = 1MB);
GO

USE [idxtest]

-- 2. show default filegroup
select name from sys.filegroups where is_default=1;

name
--------------
PRIMARY

-- 3. change default filegroup to [def_test]
ALTER DATABASE [idxtest] MODIFY FILEGROUP [def_test] DEFAULT

The filegroup property 'DEFAULT' has been set.

-- 4. demonstrate new default filegroup
select name from sys.filegroups where is_default=1;

name
--------------
def_test

-- 5. test table creation (1)
CREATE TABLE tab1 (x int) ON [DEFAULT]

-- 6. show filegroup
SELECT object_name(i.object_id) TabName, i.[name] IdxName, f.[name] FileGroupName
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
WHERE object_name(i.object_id) = 'tab1'

TabName        IdxName        FileGroupName
-------------- -------------- --------------
tab1           NULL           def_test

-- 7. change default filegroup to [PRIMARY]
ALTER DATABASE [idxtest] MODIFY FILEGROUP [PRIMARY] DEFAULT

The filegroup property 'DEFAULT' has been set.

-- 8. show default filegroup
select name from sys.filegroups where is_default=1;

name
--------------
PRIMARY

-- 9. test index creation
create index idx_tab1$1 on tab1(x) on [DEFAULT]

-- 10. show filegroup
SELECT object_name(i.object_id) TabName, i.[name] IdxName, f.[name] FileGroupName
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
WHERE object_name(i.object_id) = 'tab1'

TabName        IdxName        FileGroupName
-------------- -------------- --------------
tab1           NULL           def_test
tab1           idx_tab1$1     def_test

--
TY


texts: MySql - alternate of auto_increment on multiple columns

texts:
Since auto_increment on multiple columns is supported in MyISAM engine only and I can't use it because of its drawbacks (table lock, slower writes, no-transacts etc), I am looking for an alternative.

One thing I am already doing is to get the last id like following

Table

KEY1 INT NOT NULL -- PART OF PK
KEY2_ID INT NOT NULL -- PART OF PK
OTHERCOLUMNS ...

SELECT KEY1,IFNULL(MAX(KEY2_ID),0) + 1 NEXT_ID FROM TABLE1 WHERE KEY1 = ? GROUP BY KEY1;

Sample data

KEY1     KEY2_ID   OTHERCOLUMNS
1        1
1        2
2        1
2        2
2        3

When the above query is run for the key1=1 NEXT_ID will be 3 and for key1=2 it will be 4

Is there any other way we can do it?

Update: In response to DTest & Chris Travers

I am designing db for my browser based online game where game state data (users game objects like inventory including potions, weapons, buildings, livestock etc) is stored in multiple user_xxx tables.

For example, I have a table user_map where all user buildings (including road path), decorations (chair, lamp posts etc) is stored.

map_items
---------
id(pk)       name     type       dimensions (this is actually stored in two separate columns)
1        "bakery"  "building" "3x3"
2        "chair"  "chair"    "1x1"
3        "main road"  "road"      "2x2"

Above table is the lookup table which a player can buy and place in his virtual world

user_map
-----------
user_id(pk)       map_item_id(pk)       instance_id(pk)      location     state
1234              1                     1                    "10,10"      "producing"
1234              3                     1                    "15,15"      "na"
1234              3                     2                    "17,15"      "na"

Since a user can build/place same object multiple times, I am maintaining the instance_id so that user_id + map_item_id + instance_id will give a unique item

Like above table, I am maintaining instance_id for remaining tables like user_weapons, user_mounts, user_inventory etc.,

Now, to answer why I don't want to user a surrogate key is that I am afraid of limitation of an auto_increment column which will some day going to exceed the maximum number of the data type supports. I am actually infavour of using surrogate key (I actually started changing the model before starting this post and stopped)

Probably I am worrying too much about it because the length mysql supports looks fine but some how the using the unsigned ubigint might slow down my queries?

unsigned int 4,294,967,295
unsigned bigint 18,446,744,073,709,551,615


texts: Why do large images stored in database hurt performance

texts: I have read on the internet that it is better to keep large images on file systems since storing them in the database hurts performance. My question is why do large images hurt db performance?


texts: An alert for Log backup which is less than 2 hours

texts:
I am looking for a script which will send me an email with the below condition:

Send email when there is no log backup for the last 2 hours.

I actually have generated this output using the below script, but I need my output to have a last full backup date in it.

The script:

DECLARE
  @dblist NVARCHAR(MAX) = N'',
  @c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
  @s SYSNAME = @@SERVERNAME;

SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
  + COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
  + @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
  SELECT
   bs.database_name AS db,
   MAX(bs.backup_finish_date) AS d,
   DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age
-------------^^^^ please don't use lazy shorthand like hh
  FROM    msdb.dbo.backupset AS bs
  WHERE [type] = 'L'
  GROUP BY database_name
  HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -2, GETDATE()))

UNION ALL -- why UNION? By definition there are no duplicates to filter.
          -- in fact you could re-write this without two separate queries at all.

  SELECT
    name AS db,
    NULL AS d,
    9999 AS age
  FROM
    master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
  WHERE name <> N'tempdb' AND NOT EXISTS
  (
    SELECT 1 FROM msdb.dbo.backupset
      WHERE database_name = d.name
  )
) AS x
ORDER BY db;

IF @dblist > N''
BEGIN
  PRINT @dblist;
  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'profile',
    @recipients = 'ABC@ABC.com',
    @subject = N'Databases not backed for last 2 hrs',
    @body = @dblist;
END


texts: How to : gcloud Snapshot MariaDB Galera Node?

texts:
How to do a gcloud snapshot of Mariadb Galera Node for backup and restore ? I heard of people talking about gcloud snapshots and nowhere explains the full process of doing it. Kindly delight with the information please.

  Is that possible to use gcloud snapshot for this Mariadb Galera nodes ?
  If yea, how to do it without downtime in production ?


texts:
I just started learning SQL now and trying to figure out this scenario:

We have 3 tables:

Clients (ClientID, Name, Surname, Age)
Products (ProductID, Name, Price)
Purchases (PurchaseID, Date, ClientID, ProductID)

What would be the best SQL query that will show the quantity of purchases (how many products they bought) made by clients?

It must only show the clients who made more than 1 purchase.

The result should contain the following fields:
Full name, Quantity of purchases.


texts: How to grant user rights to create stored procedures in a custom schema

texts:
I'm trying to grant SQL Server login rights to create stored procedures and bind them to a custom schema. In this case I've created a schema called IC. A service account will then be added to the schema with execute rights for the stored procedures.

My question is: how do I grant this SQL Server login the rights to create new stored procedures binded to IC?

I've tried GRANT CREATE ON SCHEMA::IC TO [username]; but only get

  Msg 102, Level 15, State 1, Line 1
   Incorrect syntax near 'CREATE'.

Any suggestions?


texts: sp_updatestats doesn't seem to work for non-sysadmin logins

texts:
Microsoft SQL Server 2017 (RTM-CU16)

When trying to update statistics under a user which is a member of db_owner database role via

EXEC sp_updatestats;

SQL Server throws an error:

  Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15 [Batch
  Start Line 0] User does not have permission to perform this action.

Even though MS here states that "ownership of the database (dbo)" is enough seems that it is not.


texts: ER Modeling; multiplicity basics - one to many or many to many?

texts:
Apologies for a basic question, but I’m having difficulties determining the multiplicity constraints for a database I’m trying to design.

Our team undertakes a number of services/projects...each involve (at least one) activity (prototyping, evaluations, design). The database is intended to record details of each project we complete, and which activities were used in a particular project (plus other details).

Now, down to my confusion:

In one sense, it is a one to many relationship (1..1  1..*); each project involves at least one and possibly multiple activities.

But, if considering it from a different perspective, it is a many to many relationship (0..* 1..*); an activity could be in the database that is never used in any project on record, and, the same activity could be undertaken in multiple projects (for example, multiple projects could involve ‘design’).

Which perspective is correct?

Kind regards,
Mitch


texts: AlwaysEncrypted error when inserting via stored proc

texts:
lets start with the error first:

Encryption scheme mismatch for columns/variables . The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '3' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_P_H', column_encryption_key_database_name = 'Payments_Hub') (or weaker)

I'm trying to insert into a column with AlwaysEncrypt encryption on using a stored procedure:

The column 'PARAMETER_VALUE' is the encrypted column

CREATE PROCEDURE dbo.SP_UI_Parameter
(
@ID int OUTPUT,
@OPERATION_ID int,
@PARAMETER_NAME varchar(100),
@PARAMETER_METRIC varchar(100),
@PARAMETER_VALUE varchar(100)
)
AS
BEGIN

IF @ID IS NULL
BEGIN

INSERT INTO [dbo].[TOKEN_PARAMETER] (OPERATION_ID,
PARAMETER_NAME, PARAMETER_METRIC, PARAMETER_VALUE)

SELECT @OPERATION_ID, @PARAMETER_NAME, @PARAMETER_NAME, @PARAMETER_VALUE

SET @ID = SCOPE_IDENTITY()

END
ELSE
BEGIN

UPDATE [dbo].[TOKEN_PARAMETER] SET
OPERATION_ID = @OPERATION_ID,
PARAMETER_NAME = @PARAMETER_NAME,
PARAMETER_METRIC = @PARAMETER_METRIC,
PARAMETER_VALUE = @PARAMETER_VALUE

END

END
GO

Why am I getting the error for the query ?

Is there something I must include within the STORED PROCEDURE ?

I have included "Column Encryption Setting=Enabled " in my connection settings
I have also enabled Parameteterization for AlwaysEncrypted
The key is stored on the local machine
I have created DETERMINISTIC encryption
SQL Server 2016 database Engine using SSMS 17 - can this be an issue ?

SQL Server Version Info: (EDITED)

Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)   Dec 22 2017 11:25:00   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)

SSMS Stored Procedure Call: (EDITED)

DECLARE @return_value int,
@ID int

EXEC    @return_value = [dbo].[SP_Insert_Parameter2]
 @ID = @ID OUTPUT,
 @OPERATION_ID = 3,
 @PARAMETER_NAME = N'algorithm',
 @PARAMETER_METRIC = N'algorithm',
 @PARAMETER_VALUE = N'99999'

 SELECT @ID as N'@ID'

 SELECT 'Return Value' = @return_value

 GO

Thanks In Advance


texts: Is there a version history for the Postgres upgrades made in the installation?

texts:
I'm trying to determine when the last Postgres upgrade was made. I know my current version (through "show server_version" command, and other ways too). But I wasn't able to find a history of the last upgrades and the respective dates. Is there such a thing?

An analogy: this can be achieved with the file $II_SYSTEM/ingres/version.dat in an Ingres DBMS.


texts: Reducing column size and data type in mysql

texts:
I have a mysql table adv(id(TEXT),index(TEXT) ) which I need to change it as follows

ALTER TABLE adv
  MODIFY COLUMN id VARCHAR(80),
  MODIFY COLUMN index INT(11);

will this preserve all the data in the above columns assuming that they are less then 80 chars for id and a number for index?


texts:
First time posting in Stack Exchange so excuse any faux pas (well let me know them of course).

I'm using union for the first time on an already Frankenstein'ed query I've pull together so I'm looking to get insight into what might be preventing the 2nd part of the union. The query's business problem is sales for a customer have a particular point multiplier for their type of product, and are elliglbe for a redemption once they reach a threshold. So the query is adding this multiplier in, filtering based on the HAVING threshold query, and then dividing by the redemptions.

SELECT
    SUM(points/1000) AS QUARTER_1
FROM (
    SELECT ID,
        SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
            WHEN multiplier = 'ONE' THEN (sales * 1)
            ELSE 0 END ) as points
    FROM transactions
    where start_trx_date between '2017-07-01' AND '2017-10-01'
    group by ID
    HAVING
        SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
            WHEN multiplier = 'ONE' THEN (sales * 1)
            ELSE 0 END ) > 1000
    ) t
UNION
 SELECT
    SUM(points/1000) AS QUARTER_2
FROM (
    SELECT ID,
        SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
            WHEN multiplier = 'ONE' THEN (sales * 1)
            ELSE 0 END ) as points
    FROM transactions
    where start_trx_date between '2017-07-01' AND '2017-10-01'
    group by ID
    HAVING
        SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
            WHEN multiplier = 'ONE' THEN (sales * 1)
            ELSE 0 END ) > 1000
    ) t

The query is working in the sense that no errors are coming up, the problem is my result is one cell of Quarter_1 - any clues on what I need to do?


texts: Multi-master synching in three geographically separate datacentres

texts:
Our company needs to create an application that operates in all parts of the world. We are looking to have three servers (one in Europe, one in Asia and one in America). I'm looking to use multi-master synchronisation using Tungesten on MySQL with row-based replication. So far, I've tested this on three local VMs with success.

However, what happens if Database A disconnects with Database B and Database C still connected to eachother. Let's say Database A updates table Dogs and sets Dog_name = Rover where dog_id = 34, while Database B (and C) update to set dog_name = Fido where dog_id = 34. The situation can arise where B and C take the value Rover, while A takes the value Fido. I've tested this to confirm it does happen.

Tungsten allows you to generate a hash key of the databases and compare them. It generates a hash on one of the masters and sends it to the slaves which computer their own hash. This allows you to see if there is a synchronisation mismatch. However, I'm concerned in a big database this could take a lot of time and consume system resources.

Another option would be to timestamp all tables and use a script file to export all values updated say in the last hour to a central server which generates a hash keys, and if the hash keys are different (accross servers), compares the difference and mails the difference to the sysadmin to correct. The third option, would be to use Nagios to monitor and when the connection is restored, to run a hash file check.

My questions are as follows:
1. What are the risks of the above scenario occuring?
2. If anyone has any experience of this, is there a more optimal way of checking?
3. Are there any other major risks in a multi-master scenario to be aware of?

Ultimately, I plan to do extensive testing before implementing any solution like this, including breaking as much as possible to see what happens.

Many thanks.


texts:
I am trying to export a table under a user schema in oracle using the EXP command. I am doing this on the command line. This is the syntax that I used:
EXP scott/passcode TABLES=users STATISTICS=none INDEXES=n;
My export was successful with the result as follow:

However, I could not find where my table was exported to. I looked all over the internet, and I still don't know the default path of oracle EXP command. Please do not tell me anything about EXPDP. EXPDP and EXP are very different.


texts: Is it possible to display this restriction in an ER diagram?

texts:
I have a question about modeling a particular kind of restriction, but I don't know the technical term for it, so I will illustrate it with an example.

I wish to make an ER diagram for a hotel room booking. Is it possible in an ER diagram to show that no two bookings of the same room, in the same hotel, in an overlapping time interval can occur? In my initial diagram, I have three entity types: Hotel, Room and Booking. But I don't know if I should use a ternary relationship or just three binary ones, or even if I should have three entity types. Any help would be greatly appreciated.

Edit: One booking can have many rooms in the same hotel. Also, I need to be able to book the same room, in the same hotel on distinct bookings, as long as the start date and end dates of booking X doesn't overlap with the start date and end date of booking Y

My problem is that (if I understand correctly) there is no way to tell from this diagram that two distinct bookings can't book the same room, in the same hotel in an overlapping time interval. Can I somehow modify this picture to show that constraint?


texts:
Hi there was wondering if someone could help me out, and can give me a little code example.
i have a table named "projecten", and columns named "pstatus and pafgifte" these columns have numbers only.
I would like to count up all "pafgifte" with "pstatus = 2" only, and echo result.

help would be appreciated.


texts: best ETL design to transfer transaction tables records into the data-warehouse

texts:
I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table.

but for transaction tables, that have many records, i usually increment, that is i run the ETL daily to add yesterdays records.

i have 2 problems that i face always

when the job fails for any reason (i lose that Days transactions)
when for any reason the job run twice or i run it twice (i get duplicates)

now i am trying to design a way where i over come these 2 problems as well as am trying to develop the ETL in such a way that it can auto fix it self incase any of these events occur.

i want it to check if there are missing days and run run the ETL for that day, and check if there are duplicates and delete them.

below are ways i though of
1. i take in the last 5 days regardless, every day the ETL runs, deletes the last 5 days and refill.
2. i check the destination tables if they have missing dates in the last month and then i query the source with the missing days.

keeping in mind that the source is a huge table in a production environment that i have to optimize my query to the maximum when requesting from it.

thanks


texts: Win 2019 + SQL Server 2014 slowly locks up the server

texts:
We have a 2019 + 2014 sql server box that was running fine for a few days after we first stood it up on Ec2.

After a week I removed a temp drive (EBS) that we had initially used to load up the databases for importing. DBs are on D and logs on E. We imported the databases from the F drive (our temp drive).

Since I removed the EBS volume (F) the server has slowly started locking up and I have to force stop it through the AWS console. I can't RDP, it stops responding to DB requests, etc. This process takes around 4 hours after boot before it becomes unusable.

There's nothing crazy in the Event Viewer. There are a bunch of 17187 and 17897 Error Level events from MSSQLSERVER:

  SQL Server is not ready to accept new client connections. Wait a few
  minutes before trying again. If you have access to the error log, look
  for the informational message that indicates that SQL Server is ready
  before trying to connect again.  [CLIENT: 10.33.1.2]

and

  SQL Server is not ready to accept new client connections. Wait a few
  minutes before trying again. If you have access to the error log, look
  for the informational message that indicates that SQL Server is ready
  before trying to connect again.  [CLIENT: 10.33.1.2]

Those are because the PHP instance connecting to it is using an older version of the MSSQL driver to connect. The data shows up on the app it just throws those errors. It shouldn't lock up the entire server.

The only thing I can think of: Does MSSQL need access to the initial drive a db was imported from for some reason?

I'm baffled.


texts: Amazon RDS for MySQL vs installing MySQL on an Amazon EC2 instance

texts:
At work, we host all our webservers on Amazon EC2 and usually have used MySQL databases installed on the same box as our Apache webserver, and communicated with them on localhost. We now face a need to migrate our database to its own server for one of our systems. I have a choice between two solutions: use Amazon RDS, or just launch a new Amazon EC2 box and install MySQL on it.

RDS, being a dedicated database service provided by the same company as EC2, seems like it ought to be the obviously better option. However, when I look at the pricing for the two options (see http://aws.amazon.com/ec2/pricing and http://aws.amazon.com/rds/pricing) it seems that an RDS server costs almost twice as much as an EC2 server for a box with the same specs.

Given that I'm capable of handling backups myself and that EC2 offers the same ability to scale up the instance as required that RDS does, I can't see any reason at all to use RDS instead of EC2. It seems like I'm probably missing something big, though, because if I were right, nobody would use RDS. What exactly am I missing, and what are the advantages of RDS over installing your own database on an EC2 instance?


texts: Can the table spool operator be parallelised?

texts:
Is there something inherently unparallelisable about the table spool operator in SQL Server? I've got several queries that use parallel operators throughout due to the size of the tables, but by tweaking the query slightly to add a table spool (eg adding a second aggregate to the SELECT clause) the entire query suddenly becomes single-threaded.

Is it the table spool that is causing this, or could there be some other reason why SQL Server isn't parallelising operations that it was previously?


texts: how long can your replica be offline when using repmgr with Barman?

texts:
I'm setting up some postgres database EC2 instances that I'd like to use for load balancing.  The application I'm running has some very expensive and very unique queries so CPU usage is a concern.  While a single instance is OK most of the time, I'd like to quickly be able to spin up some read replicas when we're expecting to process a lot of transactions.
The issue is that it could be days or weeks in between needing to bring up these machines.  Since we're using repmgr with Barman, it's very quick to clone a server.  But ideally we'd just like to start\stop instances as needed with little thought\overhead.
My question is, when a replica comes back online after being offline for a while, and the WALs on the primary have long vanished...  Is repmgr on the replicas smart enough to know to get the backup data from barman?  I would have initially thought no, except I had a replica offline for a week, brought it online and when I checked the database, it was in sync with the primary. pg_wal only had 2 days of wals on primary.
I do have restore_command='/usr/bin/barman-wal-restore barman node1 %f %p' but I thought that was more for initial cloning or recovery.


texts: Granting only SELECT permissions on specific views to a specific user

texts:
I have landed in a scenario were I have to let a user inside a database which is not his. Hence I want to make sure that he is only able to SELECT from the views that I deem he can read from.

I have googled and tested, and it seems like I have found a solution, but I want to make sure that I'm right.

So below should give me UserA with full rights to DatabaseA, and only SELECT permission to the 2 views in DatabaseB.

Is this correct, or am I opening up Pandora's box?

CREATE LOGIN UserA with password = 'UserA'

use DatabaseA
GO
create user UserA for login UserA
GO
EXEC sp_addrolemember N'db_owner', N'UserA'
GO

use DatabaseB
GO
create user UserA for login UserA
GO
grant select on DatabaseB.dbo.v_ViewForUserA1 to UserA
GO
grant select on DatabaseB.dbo.v_ViewForUserA2 to UserA
GO


texts: Designing Students Exams and Classes Database

texts:
I'm trying to build a database for students official exams where a student study in a school and does his official exams in a different school.

My current CDM:

After converting this CDM into PDM then into SQL I'm having some problems using the database.

My problems with the current design is:

Student table has a SCHOOLID for the school that the student studies in and SCH_SCHOOLID for the school the student will be doing his exams in and this is what I actually want. The problem is that I'm not sure how to link the student exam school with his CLASSROOM and SEAT in that school.
Another thing I need in my database is that one or many CLASS can have the same EXAM with the same LANGUAGE or in a different LANGUAGE. The generated SQL does not seem to provide the option to do that.

Any help on how I can improve my CDM to get the desired result is much appreciated.

This is my generated SQL:


texts:
I'm using "portable" Mysql(not installed and should be started by batch file)
"@echo off
@color B
echo Starting the MySQL
mysql5\bin\mysqld.exe --console --max_allowed_packet=64M"
it has inside 2db, and I want to log any queries for only one db.
But I can't find the config(lol) It has 5 configs inside: my-huge, my-innodb-heavy-4G, my-large and so on.
What config should I edit?


texts: Replication log reader not updated to new primary after availability group failover

texts:
We set up transactional replication on a database that is part of an AlwaysOn Availability Group on our work environment:

The primary is SP1 and the secondary replica is SS2.
The distributor is on another server SD3. The replication is working fine.

We redirected the Original Publisher to the AG Listener Name using this script:

USE distribution;
GO
EXEC sys.sp_redirect_publisher
    @original_publisher = 'MyPublisher',
    @publisher_db = 'MyPublishedDB',
    @redirected_publisher = 'MyAGListenerName';

To implement this, we follow this documentation:

Configure Replication for Always On Availability Groups (SQL Server)
Setting up Replication on a database that is part of an AlwaysOn Availability Group

The problem is when we perform a failover from my primary SP1 to our secondary replica SS2. The replication monitor is still showing the information from the old primary SP1 (that became the secondary after the failover). And the more important is that the log reader picks up transactions from the old primary SP1. That means if SP1 server fails, replication will not work anymore.

Any idea? Have you made replication with an AG subscriber work? Recommendations or advice?


texts: Is there a MySQL engine or trick to avoid writing so many temp tables to disk?

texts:
I've got a web application connected to a database running MySQL.

Here's the problem: a not insignificant number of queries create temporary tables and many of the tables involved have BLOB fields. This means that I'm getting lots of temporary tables being created on disk (this behavior is described here https://dba.stackexchange.com/a/17679/16724) and this results in pretty crappy performance.

Is there by any chance an easy solution to this problem? Perhaps some fork or other pluggable engine? I'm actually running the Percona fork of MySQL (XtraDB), but it's the same problem with vanilla InnoDB. I'm frankly considering switching to Postgres.

Thanks


texts: Limits of testing HA with a single server replica set?

texts:
I'm teaching myself MongoDB HA, going through various replica set scenarios, setting up priorities, arbiters, hidden replica sets, etc.  Currently I'm using multiple mongod's on a single Windows server, running on separate ports obviously.

Are there specific scenarios that I wouldn't able to simulate with things like rs.stepDown() and rs.freeze()?  Or by stopping services?


texts:
I have plpgsql anonymous block. PostgreSQL 10. I store current time to variable, raise notice with value of the variable, sleep for 20 seconds, store current time to another variable, store both variables into table.
What I expect to see: difference in time between 1st and 2nd variables would be 20 seconds. Instead, I see what query execute for 20 seconds as expected, but values of variables are same. How it can be?

Code to reproduce:

CREATE schema if not exists tmd;

CREATE table if not exists tmd.gt_recommended_log (
    id bigserial NOT NULL,
    start_ts timestamp NOT NULL,
    end_ts timestamp null,
    CONSTRAINT gt_recommended_log_pkey PRIMARY KEY (id)
);

delete from tmd.gt_recommended_log;

do $$
declare _a timestamp;
declare _b timestamp;
begin
   _a := now();
  raise notice '%s', _a::varchar(30);

  perform pg_sleep(20);

  _b := now();

  insert into tmd.gt_recommended_log (start_ts, end_ts)
      values(_a, _b);

  raise notice '%s', _b::varchar(30);
  raise notice '%s', now()::varchar(30);

end ; $$ language plpgsql;

select * from tmd.gt_recommended_log;


texts: PostgreSQL priority between CTE and real table

texts:
Is there some kind of priority ranks assigned to CTE and table name ? For instance, if I have a table called table_a in the public schema, and I create a CTE table named table_a using WITH, which table will be taken if I use table_a in the SELECT query ?

--as an example
CREATE TABLE table_a (
  id serial
);

WITH table_a AS (
     SELECT id
     FROM another_table
     )
SELECT *
FROM table_a --> which table is this ?
;


texts: Index to filter a view before joining the underlying tables

texts:
Using SQL Server 2008 R2

Suppose you have a query like the following

select top 1 *
from view_cms_user
where UserId = 1234 or UserPasswordRequestHash = 'anxazno121o3n'

view_cms_user is basically:

select *
from cms_user
left outer join cms_usersettings
    on cms_user.UserId = cms_usersettings.UserSettingsUserId

Now, the query execution plan looks like the follow:

Now, this query takes a long time because the filter happens after the join between the two tables in the view, but cms_usersettings actually contains both of the fields necessary to answer this question, since UserSettingsUserId is a foreign key field referencing cms_user.UserId.

I tried creating a non-clustered index with both fields in it (UserSettingsUserId, UserPasswordRequestHash), but SQL Server ignored it. Currently it seems to being using an Index Scan of IX_CMS_UserSettings and doing nested loops with the key lookup operator to return all the fields on the table (because of the select *).

IX_CMS_UserSettingUserId contains the field: UserSettingUserId asc

PK_CMS_UserSetting is clustered key with UserSettingId (note: not the UserSettingUserId).

I cannot change the query, only the indexes.

If I change the query to an 'and' instead of an OR it's instant.

edit: In response to some of the comments below, there are already some indexes on the table in question, each in the image includes the column of the same name only:

Also including all the columns would be undesirable since there are about 50.
The table has 1,239,541 rows.

Also, UserPasswordRequestHash is 'Null' 95% of the time.


texts: Select rows from table 1, table 2, then selecting based on ID

texts:
I am dealing with a large dataset, and have run into some issues because of this.

I have a data set like:

Table1

    State    ID  Conditional_Value
     AZ       1         Yes
     AZ       2         Yes
     AZ       5         Yes

Table 2

    State    ID  Conditional_Value
     AZ       1         No
     AZ       3         No
     AZ       7         No

What I need this to return is

   State    ID  Conditional_Value
     AZ       1         Yes
     AZ       2         Yes
     AZ       3         No
     AZ       5         Yes
     AZ       7         No

If there is a duplicate ID in table 2 take that ID's row from table 1 and select it.

I have tried

    Select State, ID, Conditional_Value
    From Table1
    Union
    Select State, ID, Conditional_Value
    From Table2
    Where Table2.ID not in (select Table1.ID from Table1)

But with the large dataset I have, it is too expensive to run.

I have looked at questions about finding duplicates and deleting them and then inserting the non-duplicates into another table, but I need to compare the 2 separate tables as well.

If there is any more info needed, please let me know.
Thanks!


texts: How to check values for records from different rows


texts: How to free temporary lob used in result set of SQL SELECT in Oracle

texts:
A goal is to aggregate rows into very large strings.
I cannot use LISTAGG(XMLAGG,etc.) that support only up to 4000 bytes.
I looked into using PIVOT XML option but we have a strict requirement on using memory(XML is very bloated).

I tried using COLLECT along with to_string function to return CLOB value

CREATE TYPE vc4000_tt AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION to_string (
  coll           IN vc4000_tt,
  delimiter      IN VARCHAR2 DEFAULT ',',
  enclosed_by    IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB
IS
  lv_clob         CLOB;
  delimiter_len   CONSTANT PLS_INTEGER := length(delimiter);
  enclosed_by_len CONSTANT PLS_INTEGER := length(enclosed_by);
BEGIN

  -- Create CLOB
  dbms_lob.CREATETEMPORARY(
    lob_loc => lv_clob,
    cache   => true,
    dur     => DBMS_LOB.SESSION
  );

  -- Open it for writing
  DBMS_LOB.OPEN (
    lob_loc => lv_clob,
    open_mode => DBMS_LOB.LOB_READWRITE
  );

  -- Build CLOB from collections
  FOR i IN coll.FIRST..coll.LAST
  LOOP

    IF enclosed_by_len > 0
    THEN --write enclosed by
      DBMS_LOB.WRITEAPPEND (
        lob_loc => lv_clob,
        amount => enclosed_by_len,
        buffer => enclosed_by
      );
    END IF;

    DBMS_LOB.WRITEAPPEND (
      lob_loc => lv_clob,
      amount => length(coll(i)),
      buffer => coll(i)
    );

    IF enclosed_by_len > 0
    THEN --write enclosed by
      DBMS_LOB.WRITEAPPEND (
        lob_loc => lv_clob,
        amount => enclosed_by_len,
        buffer => enclosed_by
      );
    END IF;

    IF delimiter_len > 0
    THEN --write delimiter
      DBMS_LOB.WRITEAPPEND (
        lob_loc => lv_clob,
        amount => delimiter_len,
        buffer => delimiter
      );
    END IF;

  END LOOP;

  --Remove last delimiter
  IF delimiter_len > 0
  THEN
    DBMS_LOB.TRIM (
      lob_loc => lv_clob,
      newlen => DBMS_LOB.GETLENGTH( lv_clob ) - delimiter_len
    );
  END IF;

  DBMS_LOB.CLOSE( lob_loc => lv_clob );

  RETURN lv_clob;

EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.FREETEMPORARY(lv_clob);
    RAISE_APPLICATION_ERROR(-20900, sqlerrm||dbms_utility.format_error_backtrace );

END to_string;
/

select
  object_type,
  to_string(
    cast(collect(object_name) as vc4000_tt),
    ',',
    '"'
  )
from user_objects
group by object_type;

When Java app executes the query through a connection pool, that is a session stays forever, or when I execute it in SQL*Plus the number of temporary lobs goes up and up.

SELECT * FROM v$temporary_lobs;

I do not have any locators to use in dbms_lob.freetemporary call.
How can I free up these temporary lobs?

Is there an alternative way to return very large strings to a client app?


texts: Some help with a crosstab query in postgresql

texts:
I have a data set that looks like this:

CREATE TABLE game
AS
  SELECT team_name, match_id, sections_won
  FROM ( VALUES
    ( 'Team A', 6  , 4 ),
    ( 'Team B', 6  , 1 ),
    ( 'Team A', 19 , 4 ),
    ( 'Team A', 8  , 4 ),
    ( 'Team B', 19 , 1 ),
    ( 'Team B', 8  , 1 ),
    ( 'Team A', 7  , 4 ),
    ( 'Team B', 7  , 1 )
  ) AS t(team_name, match_id, sections_won);

It's for a game, so teams compete in matches.  The match has a possible 5 points and in these examples 1 team is winning 4 of the 5 sections

I'd like to pivot this into a scoreboard where the report looks like this:

Name    Match 6 Match 7 Match 8 Match 19
Team A  4       4       4       4
Team B  1       1       1       1

I've done this kind of thing before but I can't get this one to work for some reason.

The query looks a little like this.  I tried to simplify for examples sake.

select *
from crosstab(
  $$
    select game.team_name::text, game.match_id, sum(game.rank)::int4
    from game
    where rank = 1 -- i only want the sections the team won, then sum above.
    group by 1,2
 $$
  ) as ct("Team" text, "Match 6" int4 ,"Match 7" int4 ,"Match 8" int4 ,"Match 19" int4)
;

The resulting data set looks like this though, which I don't get:

Name    Match 6 Match 7 Match 8 Match 19
team a  4       null    null    null
team b  1       null    null    null
team a  4       4       null    null
team b  1       1       null    null
team a  4       null    null    null
team b  1       null    null    null

Any thoughts?  I've spent several hours on this puppy so far.  Driving me nuts!  :)


texts:
I'm a new user of SQL Server 2008 R2.

We work in a Windows domain environment and I have access to multiple servers - by this I mean I have various drives mapped to various servers.

I have SQL Server 2008 R2 installed in my local machine and have been able to create a database on my local machine using Windows Authentication.

My question is:  Given that I have SQL Server in my local machine, can I create a database and store it in any other server I have access to?  Or does this other server need to have SQL Server installed as well?

Thank you in advance!


texts:
I have eight 'append' queries in MS Access 2010 that I want to be able to run.
Each query also needs to take one parameter (a project number).

I want to run all eight, entering the parameter only once.

I haven't programmed in MS Access before. There are a lot of posts out there.

Do I need to learn how to create a Macro/ a Routine/ a Sub Routine? Or is there an easier way?


texts: Why would execution plan decide to sort 500k rows instead of 4k?

texts:
Consider the following query:

select top 50 p.value, p.pointid from point p
    where exists (select c.collectionid from collection c
        where c.parentid = 1
        and c.somebool = 0 and c.someotherbool = 1
        and p.collectionid = c.collectionid)
    order by p.value desc

In a given schema where the collection table has points and a parent is referenced from the collection table (part of this query).
Something about this query and the indexes I have on the more complex version of this results in Sql Server thinking its a good idea to execute the "order by" FIRST on a table of ~500k rows instead of executing the where and working on 4k rows instead.
This results in a spill (btw would a spill kill the cache?) which just destroys performance seemingly across the board.

I created this a smaller example of this but couldn't reproduce the problem making me believe there is some sort of issue with another index or materialized view or something that is confusing the execution plan.

On the more complex version of this I can get the desired result by forcing it to use an index that is on the equivalent of point.collectionid. Sadly I'm constrained by an ORM that doesn't output nice SQL otherwise I'd use the hint or temp table.
Amusingly enough top(3250) causes the 500k sort but top(3500) doesn't.

Any ideas about the rationale? Could this be a bug in Sql Server?

EDIT: An image showing the original execution plan and my attempt to recreate the problem.
We can see from this that in the "madness" it begins the query off the PK clustered of the 500k row table, in the recreation (there are a million rows in that one) it correctly executes the WHERE part of the clause first starting with the PK of the other table to reduce its subset to 4k as opposed to sorting the whole thing.

Sorry I can't post the lot but there is a large amount of noise in this db and I don't want to post internals.


texts: Why would I not run "index reorganization" regularly?

texts:
I tried index reorganization queries several times so far. They look like this:

ALTER INDEX ALL ON TableName REORGANIZE

Such query may take hours but it looks like it doesn't interfere with other database operations. For some table its effect is next to nothing but for some tables it actually saves like 15% of overall database space.

What if I just run this query for all indices every week or so? Is there any reason not to do so?


texts: I have an app to deploy in production that uses 'honor system' security. That is, all users connect to the DB using a SQL user/passwd credential and the app manages permissions itself. The latter part doesn't bother me as much as the fact that the connection object contains embedded credentials and can be copied around freely. I'm try to find some way to limit connections to a more limited set of clients. I can create firewall rules to limit by IP, of course. Is there any way to 'prequalify' SQL logins either by Machine account or domain membership?


texts: Disk space unreleased after cleaning up rows from PG table

texts:
I understand space isn't abruptly released (unless one does TRUNCATE). But this one looks abnormal to me.

What could be the reason for a postgresql (9.3.10) table not releasing space after having 70% of its records removed?

I have a table called user_sessions_session. As the name implies, it stores sessions data for each user of a web app.

It's original size was:

              Table               |  Size   | External Size
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

I thereafter deleted all user sessions older than 3 months ago. That was the majority of the rows in the table. This was 3 days ago. I just checked the table size again, here's what I see:

              Table               |  Size   | External Size
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

Moreover, select * from pg_stat_activity where query like 'autovacuum:%'; reveals no vacuuming is going on at this point.

Btw I've had this same issue with this same sessions table before - it's not a one off.

In case it matters, here's the SQL I used to get table sizes (user_sessions_session showed up as number 1 in the list):

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;


texts: Getting the exact edited data from a column in SQL Server

texts:
I have two Tables:

Articles(artID, artContents, artPublishDate, artCategoryID, publisherID).

ArticleUpdated(upArtID, upArtContents, upArtEditedData, upArtPublishDate, upArtCategory, upArtOriginalArticleID, upPublisherID)

A user logs in to the application and updates an article's contents in the artContents column. I want to:

Know which Changes the user made to the article's contents?
Store both versions of the Article, the Original version and
the Edited Version!

What should I do for the above two task:

Are there any necessary changes in the tables?
What is the query for getting exact edited data of the artContents column?

(The exact edited data means that there may be 5000 characters in the columns. The user may edit 200 characters in the middle or somewhere else in column's characters. I want the exact edited characters, before the edit and after the edit.)

Note: I am using ASP.NET with C# for Developing


texts: A way to reference other DB without hardcoding its name

texts:
My customer uses pair of databases (SQL Server) where one references the other. Let's say DataDb and ConfigDb. ConfigDb contains hundreds of SPs that references DataDb like [DataDb].[dbo].[the_object].

This pair of databases is deployed to their customers together with some applications.

For development and testing I need to have locally pairs for several customers at once and run the applications against the right pair just by changing the connection string.

When I restore the customer's databases I use suffix of the customer, like DataDb_Cust1, ConfigDb_Cust1. The problem is that stored procs from ConfigDb have hardcoded the name of the referenced database (there's always [DataDb].[dbo].[the_object] which I need to change to [DataDb_Cust1].[dbo].[the_object]).

I could go through all the object (SPs, functions, views) and rename the referenced DB name. I already have some scripts to automate that. But is there a better way (e.g. just use some alias defined on SQL server level or something like that) so that the referenced DB name does not have to be hardcoded in SPs?

I looked at synonyms, but it turns out they are not supported by Microsoft Entity Framework - ORM used by the application. I'd need a solution that would work with EF6. However I can verify it by myself. Any other solution?

The size of DB varies, but typically the DataDb is between 1 and 8GB, the ConfigDb is quite small. No Enterprise features, but it uses SQLCLR assemblies, for some customers even linked servers to access other DBs in distributed transactions (using MSDTC) but it is rare case. I have now 6 sets and expect at most 20 in near future.

The DataDb uses FILESTREAM, which is not supported by LocalDb.


texts: Single or multi tenancy database recommendations

texts:
I'm really confused searching for a database(nosql or mysql) that can handle unlimited multi or single tenancy with good scaling method either by software or physical resources. So i can differentiate each customers by namespace or any form. I was thinking couchbase before but just to find out that couchbase UPDATE fields is a big mess.

Please i need recommendation that can fit my needs!


texts:
I'm working on a project that requires me to allow users to store/edit properties of what they call "path". A path can be any Zip Code to any other Zip Code.

The system they have now, utilizes one table for 3 digit to 3 digit system for default properties, and then if exceptions to the 3to3 are needed, there's another table that does the 5 digit to 5 digit.

123 > 345
12345 > 34567

Making changes is interesting for them, because it becomes a game to figure out exceptions (5 to 5) in relation to the 3 to 3 properties. Add in the fact that going from zip 12345 to zip 98765 isn't the same as doing the reverse 98765 to 12345.

So, they'd rather have one table that has the immediate detail of "every 5 digit zip code to every 5 digit zip code". By my calculations that means close to 2 billion rows (43000 x 43000. since there's about that many valid zips in the US).

That just seems like a bad idea to me, but without going right back to the situation they already have, I can't see a way around it.

I thought about storing them as one single combination, but then having the extra fields for either direction. Like this:

Zip1: 12345
Zip2: 98765
1to2 Property 1:
2to1 Property 1:
1to2 Property 2:
2to1 Property 2:
1to2 Property 3:
2to1 Property 3:

This would reduce the number of rows to under 1 billion. I would just setup the Zip1 to Zip2 as the primary key, and then have a unique index for the reverse Zip2 to Zip1.

Has anyone ever seen a neat trick or idea to storing data like this?


texts: Optimization of an old query multiple unioned subqueries

texts:
I am currently in the process of optimizing a large query that was written for oracle 8i. My company currently uses oracle 10g which gives us a lot of options for optimization.

Please Reference the following pseudo queries:

Original

SELECT
 *
FROM (

    SELECT
        *
    FROM (
        original.slow_query1
    )

    UNION

    SELECT
        *
    FROM (
        original.slow_query2
    )

    UNION

    SELECT
        *
    FROM (
        original.slow_query3
    )
)

Improved

SELECT
 *
FROM (

    SELECT
        *
    FROM (
        improved.query1
    )

    UNION

    SELECT
        *
    FROM (
        improved.query2
    )

    UNION

    SELECT
        *
    FROM (
        improved.query3
    )
)

Problems
I have been successful in decreasing the query time from about 30 seconds per subquery to less than 3 seconds each. However, when I use the UNION operator to combine the results, my improvements total double the time of the original query when unioned.

I have checked the data that was returned and the number of rows columns and data in each of the original subqueries is equal to the the improved subqueries. However, when I use the UNION operator on my improved version, I get a greater total of returned rows.

Questions
1. Why would the overall performance of my query be worse than the original when my subqueries are now completing in less than a tenth of the time of the original?
2. If I have gone through and compared the data returned by both queries and confirmed them to be the same, why would using the UNION operator return more results in the improved version?

I am currently inclined to think that avoiding the UNION operator would be the best was to go, but I would like to keep all the processing done on the database end. This means that sending the results to the client program to be sorted there is out of the picture.


texts: Best Practice to update columnstore indexed tables

texts:
I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance improvement is really fantastic. However I'm getting each day around 30-50 Mio new rows that I have to load as fast as possible and make available for the users.
I have seen recommendations to make for each day on partition and do partition swapping. This would result for the data I have at the moment in 4380 partitions. Even thinking of the scripts to create this makes me wonder if this is really a smart approach.

What would be best practice in such a situation ? Does disabling and rebuilding the columnstore index only rebuilt for the added 30 - 50 mio rows, or over the whole 22 Bn rows ?

The time frame I have to add these daily new rows is around 30-45 min max.


texts: Why is it truncate table, and not simply truncate?

texts:
Upon reviewing some of our code a colleague posited that he was annoyed at always having to write TRUNCATE TABLE when there is no real other usage for TRUNCATE.

Googling this, I indeed can not find a specific reason why the TABLE part is needed.
What am I missing? Is there some historical reason for the TABLE in TRUNCATE TABLE?


texts: Authorization error running db2 command line with db2inst1

texts:
I need to enable the COLUMNAR setting in DB2 Express Edition on Docker. For that to work I need to set INTRA_PARALLEL ON at the instance or database level.
I connect to the db2 command with db2inst1 that is the instance owner, but I'm getting an error saying that the user is root. How to fix this problem?
db2 => connect to bank0002 user db2inst1 using xxxxx

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.4.0
 SQL authorization ID   = DB2INST1
 Local database alias   = BANK0002

db2 => UPDATE DBM CFG  USING INTRA_PARALLEL ON
SQL5001N  "ROOT" does not have the authority to change the database manager
configuration file.


texts: MySQL Error 1251 on 5.7.33 - Can't log in

texts:
ERROR 1251 (08004): Client does not support authentication protocol requested by server; consider upgrading MySQL client

I have recently imported a backup into a test environment so I can review some slow queries, and just ran into this issue when trying to log in via command line. I had only made a change to @@sql_mode due to the query having an issue with ONLY_FULL_GROUP_BY. After a restart to make sure the change took effect, I get the above error message and can't log in. I tried changing my my.cnf file to include skip-grant-tables, but this hasn't worked. Am I out of luck, or are there any other ways I can get myself back in?


texts: Error 297, Severity 19 trying to create new table as sysadmin

texts:
I am completely scratching my head right now. I have two database instances that I am trying to add a table to, and both give me the same error:
The user does not have permission to perform this action.
and I have triple checked the permissions, I am on a sysadmin account. Running the following query to see what create permissions I have yields the following:
SELECT SYSTEM_USER

-- Now get the permissions assigned to you by the server administrator
use tempDB
GO

;with getPermissions as ( SELECT * FROM fn_my_permissions (NULL, 'DATABASE') )
select permission_name from getPermissions
where permission_name like 'create%'
GO

I have also tried creating another login with sysadmin privileges to no avail. Any ideas?
Error log picture below:

USE [db_test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table](
    [a] [int] IDENTITY(1,1) NOT NULL,
    [b] [nvarchar](50) NULL,
    [c] [decimal](18, 3) NULL,
    [d] [bit] NULL,
    [e] [nvarchar](50) NULL,
    [f] [nvarchar](10) NULL,
    [g] [nvarchar](500) NULL,
    [h] [int] NULL,
    [i] [int] NULL,
    [j] [int] NULL,
    [k] [int] NULL,
    [l] [int] NULL,
    [m] [int] NULL,
    [n] [int] NULL,
    [o] [int] NULL,
    [p] [int] NULL,
    [q] [int] NULL,
    [r] [int] NULL,
    [s] [int] NULL,
    [t] [int] NULL,
    [u] [int] NULL,
    [v] [int] NULL,
    [w] [int] NULL,
    [x] [int] NULL,
    [y] [int] NULL,
    [z] [int] NULL,
    [aa] [int] NULL,
    [ab] [int] NULL,
    [ac] [int] NULL,
    [ad] [int] NULL,
    [ae] [int] NULL,
    [af] [int] NULL,
    [ag] [int] NULL,
    [ah] [int] NULL,
    [ai] [int] NULL,
    [aj] [int] NULL,
    [ak] [int] NULL,
    [al] [int] NULL,
    [am] [int] NULL,
    [an] [int] NULL,
    [ao] [int] NULL,
    [ap] [int] NULL,
    [aq] [int] NULL,
    [ar] [int] NULL,
    [as] [int] NULL,
    [at] [int] NULL,
    [au] [int] NULL,
    [av] [int] NULL,
    [aw] [int] NULL,
    [ax] [int] NULL,
    [ay] [int] NULL,
    [az] [int] NULL,
    [ba] [int] NULL,
    [bb] [varchar](12) NULL,
    [bc] [varchar](12) NULL,
    [bd] [varchar](12) NULL,
    [be] [varchar](12) NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED
 (
    [A] ASC
 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Table] ON
INSERT [dbo].[Table] ([a], [b], [c], [d], [e], [f], [g], [h], [i], [j], [k], [l],
                      [m], [n], [o], [p], [q], [r], [s], [t], [u], [v], [w], [x],
                      [y], [z], [aa], [ab], [ac], [ad], [ae], [af], [ag], [ah],
                      [ai], [aj], [ak], [al], [am], [an], [ao], [ap], [aq], [ar],
                      [as], [at], [au], [av], [aw], [ax], [ay], [trkCusT4SA], [az],
                      [ba], [bb], [bc], [bd])
              VALUES (36, N'City', CAST(2.500 AS Decimal(18, 3)), 1, N'SSS', N'CTY',
                      N'http://google.com', 200, -4, 6400, 6700, 150, 450, 6400,
                      6700, 1400, 1800, 3400, 3800, 8000, 8400, 10100, 10500, 850,
                      2430, 2880, 4460, 7480, 9060, 9540, 11070, 6400, 6700, 150,
                      450, 6400, 6700, 1400, 1800, 3400, 3800, 8000, 8400, 10100,
                      10500, 850, 2430, 2880, 4460, 7480, 9060, 9540, 11070, NULL,
                      NULL, NULL, NULL)

ALTER TABLE [dbo].[Tracks] ADD  CONSTRAINT [IX_Table] UNIQUE NONCLUSTERED
(
    [A] 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

Running SELECT * FROM sys.dm_exec_trigger_stats
Yields the following result:


texts: How Insert data to child table in different schema using trigger function and auto updates for these child tables

texts:
I am trying to insert data to the child tables from different schema i.e parent table and trigger function were in schema 1 and child tables were in schema 2. It throws me an error while I am doing this. Child is not referenced to parent table from schema2.
Error is Child_test_topic does not exist. Do I need to place the trigger in child schema? And how auto insertion works in this case when new data populated in parent table?

Parent table schema - PUBLIC

CREATE TABLE public.topics_test1
(
  id uuid NOT NULL,
  topic character varying(255) COLLATE pg_catalog."default",
  value character varying(255) COLLATE pg_catalog."default",
  has_synonyms boolean DEFAULT false,
  searchable boolean,
  CONSTRAINT topics_pk11 PRIMARY KEY (id),
  CONSTRAINT topics_pk_test_21 UNIQUE (topic, value)

)
WITH (
  OIDS = FALSE
)
TABLESPACE pg_default;

Child table moved to different schema:

CREATE TABLE child_test_topic (
CONSTRAINT pk_pk11 PRIMARY KEY (id),
CONSTRAINT ck_ck11 CHECK ( topic = 'ingredient' )
) INHERITS (topics_test1);
CREATE INDEX idx_ing ON child_test_topic (topic);

ALTER TABLE child_test_topic SET SCHEMA postgres_partition_tables;

Trigger function and trigger:

CREATE OR REPLACE FUNCTION public.fn_test_schemapropagation() RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.topic = 'ingredient' ) THEN
INSERT INTO child_test_topic VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_topic_trigger1
BEFORE INSERT ON public.topics_test1
FOR EACH ROW EXECUTE PROCEDURE public.fn_test_schemapropagation();

Insert statement:

INSERT INTO public.topics_test1(id, topic, value, has_synonyms, searchable) select * from topics where topic = 'ingredient';


texts: Get if field has max value and display value

texts:
I need to display Y or N when displaying rows in a report based on the max value for them on their version. If they have multiple versions all of them will display 'N' but the max (last version) will display 'Y'.
I have tried with case when and max but always I'm getting 'Y'
case when version = (select max(version) from t_proj_f where version = t.version) then 'Y' else 'N' end Is_last_version

Here is fiddle in case anybody can take a look
http://sqlfiddle.com/#!4/813501/4
I'm new on oracle sql any help is appreciated.


texts: generate_series not working in single user mode

texts:
A table is not being populated with generate_series for below.

backend> create table t1 (i int, j int);
backend> insert into t1 values (generate_series(1,200000), generate_series(1,200000));
backend> select count(*) from t1;
backend>


texts: need sql to represent relation negation and date compare problem

texts:
I have the following tables:

Employee(eid,ename,salary,did,classification)
Project(pid,pname,did,budget,ddate)
Department(did,dname,floor,head)
dbudget(did,byear,budget)
Project(pid,pname,did,budget,ddate)
Onproject(pid,eid,fdate)

did department identifier, so we need something that is in employee.did but non on project.did

and I want to retrieve all of the projects whose duedate is not passed, and that all of its workers belong to a department  that is not responsible for the project.

now, I don't know how to check if the ddate is over(ddate is due date, and I don't know if it's passed or not), and how to represent it in a query.

I'll show the query I constructed so far:

SELECT   Project.pname
from         Project
FULL OUTER JOIN Employee
on   Project.did <>  Employee.did
AND ....

(don't know how to check if ddate is passed) is there a function to show date in SQL to compare (so the difference will be positive) or any trick to work with the given info?


texts: Summarizing over Outrigger Dimensions Directly (e.g. Total Customers by Demographic by Signup Date)

texts:
According to Kimball:
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/outrigger-dimension/

A dimension can contain a reference to another dimension table. For instance, a bank account dimension can reference a separate dimension representing the date the account was opened.

This suggests to me you'd be able to just query the Account dimension directly if you wanted to track number of accounts opened over time, for example. Is this common?
In my case, I'm modeling Customer as a dimension, which also references Date (for Signed Up Date) and Demographic dimensions.
A common kind of query the business ask is how many signups we've had over time by demographic, for example. This doesn't involve a fact table at all. All this can be worked out by summarizing the dimension itself. Does this indicate some kind of a smell in the schema design, or is this just a trait of certain kinds of dimensions where the dimension itself has some significance to the business?


texts: SQL Server Agent crashes when scheduled procedures make select in DBLink on an offline Oracle database

texts:
I have SQL Server 2014 and I'm using SQL Server Agent to start some scheduled procedures during the day. These procedures make some selects (in DBlink) on an Oracle database that is handled by another company. Sometimes the Oracle database is offline and my procedures seem to be stucked and I should restart the entire server the make the SQL Server Agent work again.

What am I doing wrong? I suppose there is a sort of timeout to end the procedure and release the SQL Agent.

Example of select:

SELECT * from openquery(positions,
'SELECT * from oracleTableDbLink.POSITION'
)


texts: How to quote qualified table name with format() function?

texts:
I have some problems in properly quoting a table name using the format function in the example below.

CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text)
 RETURNS bool AS $func$
DECLARE query_str text;
BEGIN
  query_str = format($fmt$ DROP TABLE IF EXISTS %1$I; CREATE TABLE %1$I AS (TABLE %s); $fmt$, _target_tbl, _source_tbl);
  EXECUTE query_str;
  RAISE NOTICE '%', query_str;
 RETURN True;
END $func$ LANGUAGE plpgsql;

My dilemma is that I want to quote the input table name _target_tbl as an identifier (to avoid SQL injection). However, given a full table name ex.test1, this causes the schema part ex. to be treated as part of the table name and the table public."ex.test1" created in the default public. schema as shown below.

How should I quote/format the identifier properly here?

=> SELECT copy_table('ex.test', 'ex.test1');
NOTICE:  table "ex.test1" does not exist, skipping
NOTICE:   DROP TABLE IF EXISTS "ex.test1"; CREATE TABLE "ex.test1" AS (TABLE ex.test);

=> \dt ex.test1
Did not find any relation named "ex.test1".
=> \dt "ex.test1"
         List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | ex.test1 | table |
(1 row)

This is with PostgreSQL 10.3.


texts: MySQL customized service script for zip installation

texts:
We are currently using MySQL 5.7.20 on CentOS and are using zip installation.
Since it is a zip installation we do not have service created in /etc/init.d/.

Is there any other method for auto-startup and auto-shutdown as we usually do with service.

We can startup using /etc/rc.local but what about if we want to do auto-shutdown or service status? any sample service script

Please suggest.

Thanks.


texts:
I had Postgresql installed on a previous MacBook.  However, something happened and now the Macbook won't boot up.  The HDD was okay, so I converted it to an external HDD.

I have another computer with Postgresql installed on it.  However, I need to access the tables from the old HDD, that is an external HDD.

I have found the .conf files and everything like that, and I have attempted to copy and paste, replacing the folders on my new computer with the Postgres folders from the external HDD.

However, this hasn't helped me to call up the old tables.  Can someone help me to either use the GUI Pg3admin tool to call up, or tell me what I need to do in Terminal?

Thank you.


texts: sqlplus connect without typing the @local_SID when only have one instance

texts:
Every time I want to connect with a user in Sqlplus I have to specify the database|sid name too,

SQL>connect scott/tiger@orcl

I have installed another database for other purpose but I removed it.
Before the installation of second database I could connect without using @orcl but after I removed it I have to connect that way I said.
How can I fix this issue?


texts: MySQL store current binary log in different folder from past binary logs`

texts:
Is there any way to do this? Specify a different directory for currently active binary log.

(It would nice if possible, because then we could store past binary logs in a compressed file system without compromising write performance of the current log.)


texts: Forgot to run END_UPGRADE during a Timezone File Upgrade (ORA-56924)

texts:
I ran the following to upgrade my timezone file from 4 to 14. The issue was after running the upgrade I forgot to run DBMS_DST.END_UPGRADE and put the DB back up and enabled incoming connections.

exec DBMS_DST.BEGIN_PREPARE(14);

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/

SELECT * FROM sys.dst$affected_tables;
0 rows returned

EXEC DBMS_DST.END_PREPARE;

shut immediate;

startup upgrade

EXEC DBMS_DST.BEGIN_UPGRADE(14);

shut immediate;

startup

SELECT version FROM v$timezone_file;

Flip to a week later when I realise this. When I try to run DBMS_DST.END_UPGRADE I get the following:

ERROR at line 1:
ORA-56929: Ending an upgrade window failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1277
ORA-06512: at line 5

I tried running it in upgrade mode as well but no luck. When I searched online I saw the following:

ORA-56929: Ending an upgrade window failed
Cause: Ending an upgrade window failed or there were tables containing TSTZ data which have not been upgraded.
Action: Check alert or trace file for error(s) or complete upgrading of tables containing TSTZ data.

How would I "complete upgrading of tables containing TSTZ data."?


texts: Why and how and where to use user without login

texts:
I've been trying to find why, how and where to use Users Without Login and I didn't find my exact question or anything that gets closer.
Where I did my research (I also read other posts, saw Youtube Videos):

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9dbd233a-a105-473f-acab-6d8272d5772b/use-of-lt-sql-user-without-login-gt?forum=sqldatabaseengine

https://blog.learningtree.com/sql-server-users-without-logins/

https://blog.sqlauthority.com/2012/10/04/sql-server-importance-of-user-without-login-t-sql-demo-script/

Map a Login to a User that was Created Without Mapping to a Login

https://stackoverflow.com/questions/6468813/assign-a-login-to-a-user-created-without-login-sql-server

I was searching for ways to turn my db more secure and I recently came to know that there's an option to create user without login. I thought it is nice.
But I didn't understand how to use it (not create, use), then after my  research I realized that this user accesses the db while impersonating another user/role.
I thought it would be great to not have a user and somehow access the db.
But when I saw that there's something called execute as I thought that it wasn't useful because why I would execute queries on this "login-less" user as it was another user?
Why I just don't use this user's permission?
Then I thought maybe this user has too much permissions, so I just create a new one with less permissions...
Okay, I don't know why you would create a user without login.
How should this be used?
It's stupid, but I tried to login and failed (sorry, but I'm used to login with users, I never thought on creating a user and not to login, I just know that users login to something, that's where my knowledge is).
So, I don't know why to create this type of user instead of a normal one?
I don't know how (like do I login, do I just type the query and  to use it)?
I don't know where (like, do I need to make an C# app, do I use command...) to use?
My knowledge only takes me till here, I don't have a Senior DBA to ask.
Please explain in simple words if possible.


texts: How do I add a MySQL linked server to SQL Server?

texts:
I am trying to add a linked server on SQL Server to MySQL Server.

Note the SQL Server is installed on a separate machine with a different domain name.

The SQL Server is 10.0.1.1 on old_domain.com.

The MySQL server is 10.0.1.2 on new_domain.com.

I have added an ODBC Data Source on server 10.0.1.1 and tested the connection with no problems reported.

Then I opened Microsoft SQL Server Management Studio and navigated to SQL SERVER > Server Object > Linked Servers.

I got to the wizard to add a linked server and I configured it like so:

Linked Server: "MySQLNewServer"
I selected "Other data source" Provider "Microsoft OLE DB Provider for ODBC" Product name "Connection to MySQL" Data source "MySQL" the same name at the Data Source that was added in the first step.
On the security tab I selected "Be made using this security Context" and I put SQL Server user name and SQL Server password
I hit "Ok"

Note: I followed the instruction on this link http://dbperf.wordpress.com/2010/07/22/link-mysql-to-ms-sql-server2008/

However, I get this error:

  OLE DB Providor "MSDASQL" for linked Server "MySQLNewServer" returned message "[MySQL][ODBC 5.2(w) Driver] Can't connect to MySQL server on 10.0.1.1 (10055) Microsoft SQL Server Error: 7303.

Can someone please tell me what am I doing wrong? How can I get these 2 server to communicate?


texts: Two MySQL query results are different, but shouldn't be

texts:
To the best of my knowledge, these results (especially the data in the 'destination' field) should be the same, but I can't find out why they're different:

mysql> select * from cc_did_destination where id_cc_did=1988\G
*************************** 1. row ***************************
            id: 2256
   destination: SIP/t564/12505551234
      priority: 1
    id_cc_card: 2157
     id_cc_did: 1988
  creationdate: 2012-01-31 07:59:01
     activated: 1
secondusedreal: 0
     voip_call: 1
     validated: 1
1 row in set (0.00 sec)

mysql> SELECT cc_did.did, cc_did.id, cc_did_destination.destination     FROM cc_did, cc_did_destination  WHERE cc_did.iduser='2157' AND cc_did_destination.id_cc_card=cc_did.id\G
*************************** 1. row ***************************
        did: 7805551234
         id: 1988
destination: SIP/someuser
*************************** 2. row ***************************
        did: 6045551234
         id: 1997
destination: SIP/macaddress-1
2 rows in set (0.01 sec)

In fact, the data for the 'destination' field is correct in the first select statement. The data in the second query is old and should have been replaced with the data we see in the first query.

As per Rachel below, what I'm trying to do is this; Join this table:

mysql> SELECT destination, id_cc_card, id_cc_did FROM cc_did_destination where id_cc_card=2157;
+----------------------+------------+-----------+
| destination          | id_cc_card | id_cc_did |
+----------------------+------------+-----------+
| SIP/t564/12505551234 |       2157 |      1988 |
| SIP/testuser         |       2157 |      1997 |
+----------------------+------------+-----------+
2 rows in set (0.00 sec)

To this table:

mysql> select did from cc_did where id=1988;
+------------+
| did        |
+------------+
| 7805553315 |
+------------+
1 row in set (0.00 sec)

Except when I try to do that with a JOIN statement, I get this instead:

mysql> SELECT destination, id_cc_card, did FROM cc_did_destination as A join cc_did on A.id_cc_card=cc_did.iduser WHERE A.id_cc_card=2157;
+----------------------+------------+------------+
| destination          | id_cc_card | did        |
+----------------------+------------+------------+
| SIP/t564/12505551234 |       2157 | 6045553939 |
| SIP/testuser         |       2157 | 6045553939 |
| SIP/t564/12505551234 |       2157 | 7805553315 |
| SIP/testuser         |       2157 | 7805553315 |
+----------------------+------------+------------+
4 rows in set (0.01 sec)

Which makes even less sense!

UPDATE:
I found the problem, and it's all about matching the wrong fields. It should be:

WHERE cc_did.id=cc_did_destination.id_cc_did;

instead of:

WHERE cc_did.id=cc_did_destination.id_cc_card;


texts: Client not reading from Mongo secondary database

texts:
I've implemented a replica set that I'm using globally. I have my master in Oregon, US and 4 secondaries. California and Virginia, Frankfurt and Sydney. I also have web servers in those same regions as well. Those web servers connect to mongo using mongoose:

var mongoose = require("mongoose");
var dbUrl = "mongodb://***.***.***.***:27017,***.***.***.***:27017,***.***.***.***:27017,***.***.***.***:27017,***.***.***.***:27017/exampleDb";
var dbOptions : {
   "replSet": {
      "rs_name": "exampleRepSet",
      "readPreference": "ReadPreference.SECONDARY_PREFERRED",
      "read_preference": "ReadPreference.SECONDARY_PREFERRED",
      "w":0,
      "slaveOk": true
    }
}
mongoose.connect(dbUrl, dbOptions);

My problem is that my client's have higher latency to the database depending on how far away they are from the master. California get 40ms while Sydney gets 400ms. I don't understand why this is happening since they should be reading off of the secondary database in their region.

I understand that writes must be done to the primary but even if I perform a find then shouldn't it be done on the regional secondary and return pretty quick?

I realize there are some redundant options in that config but I'm getting desperate. I've also tried the option "ReadPreference.NEAREST" to no avail.