texts:
Forgive me for my lack of dba experience but I can't find the answer anywhere.

I'm working remotely on a third party to a client that asked for a login, I created the login but there's problems with it.

When I decided to modify the login, I remote in and I can't see the logins subfolder under security, it's replaced with a users subfolder.

I'm using the same windows authenticated login I used on-site. So either there's some restrictions to remoting in, or the client's dba changed something.

Can someone help point me in the right direction?


texts: Is MSMerge_History.time in GMT or local server time?

texts: Is the time column in the MSMerge_History table GMT or local distribution server time?


texts: Is it possible to `REVOKE` all privileges from all members from previous `GRANT`s?

texts:
Let's say I grant a privilege to "user_a", "user_b", "user_c".

Is it possible to REVOKE from all users in previous grants, but without specifying them explicitly? Something like this:

REVOKE ALL PRIVILEGES ON "..." FROM ALL;

Or would I have to specify them individually?

REVOKE ALL PRIVILEGES ON "..." FROM "user_a", "user_b", "user_c";


texts: Need a stored procedure to check and update row when it does not already exists.

texts:
Need a stored procedure that will check and then update the rows (with username & email) except the id (auto incremented with a unique one).

For example, if id = 5 has, the username abc and the email is def@ggg.com and the id is unique.  Before it updates that row, it has to check the table to see if the username and email already exists.  If anybody already has that username and email the table is not updated.

Please provide a stored procedure for this issue.


texts: SSIS/SSAS 2016 intermittent fault: An error was encountered in the transport layer

texts:
I have an intermittent fault in my SSIS ETL process when I try and process my OLAP cube.

There are no error messages in the SQL log, and there's nothing else significant running at the time. The only error message I can find in the event log is:

  An error was encountered in the transport layer.

SSIS and SSAS are SQL are all running locally on the same server, so I don't believe it's network related at all.

The same package has been deployed and has run successfully for weeks. If we manually connect to the cube it processes without any problem.

Windows Server 2012 R2 6.3  (Build 9600: ) on VMWare 64gb of RAM.

Microsoft SQL Server 2016 Enterprise (SP1-CU1) - 13.0.4411.0 SQL Max mem = 48gb

My gut feel is that this is more of an SSIS issue than a SSAS issue.

Has anyone else encountered this? Can anyone reccomend a solution or workaround? Is it possible to get additional debugging information?


texts:
I’m trying to connect MySQL to an identity management (IDM) platform - goal is, to be able to create roles from the IDM site out of the MySQL privileges and be able to provision it to new users, which should create as well in the MySQL.

To do this, I need to connect MySQL to the IDM platform, which I have already done. I’m stuck on the next step though: I need the list of all privileges that can be granted to a user in MySQL then map it as entitlements on the IDM site. I have checked the information_schema.user_privileges/mysql.user but found that it only contains the current privileges of existing users. The privileges were on columns, I think if there is a way I can get it as records then I may consider using it.

Can anyone please help? Or if someone knows any methodology to do this better please let me know.


texts:
I need MySQL dataset for testing purposes to check and improve performance and optimization.

Is there any freely available dataset (MySQL) of 200k rows ??


texts: Which Column is better to be used here VARCHAR or DATETIME?

texts:
I have a Script that uses Date to pick the day name and number of it, Something like the following

2018-06-04 =becomes=> Monday(04, June)
2018-06-04 =or becomes=> Monday(04) // In no need for Month

So What I do is creating a Column in the table using DATETIME datatype, Then I set the values like this

000-06-04 //case1
000-00-04 //case2

Is that a proper way to do this, Or is it better if I use VARCHAR(18) datatype and write it directly like this

Monday(04, June) //case1
Monday(04) //case2

Using DATETIME requires me to use DATE_FORMAT() later on, And using VARCHAR(18) doesn't.

SELECT DATE_FORMAT(date, '%W(%d, %m)') FROM table //case1
SELECT date FROM table //case2

I'm don't care about ease of use But performance.


texts: MMS is assigning same alias to two data nodes of different shards and replicasets

texts:
I am using MMS to monitor a 13 node MongoDB Cluster(3 Config Servers, 4 MongoS, 2 replica sets with 2 members + 1 arbiter each). MMS was working properly since its installation, but now it is not monitoring the shards & replica-sets properly.

It is monitoring only 4 nodes(1 Primary 1 Secondary and 2 arbiters) out of 6 nodes and auto-removing the other replica-set(primary + secondary) every time I am adding them manually. Also for the replica-set it is monitoring it is taking the primary secondary and arbiter as independent nodes(even though it is showing correct TYPE for them in the monitoring window).

When I checked Host Mapping it was surprised to see that it was showing the same alias name for both the Primary nodes and same for both the secondaries. I guess this is the reason it is not showing those 2 nodes in the monitoring window.

I manually removed those entries of same alias names from the Host Mapping window but it is auto-updating it after few minutes.

Why is it considering the two different nodes as same and taking the same alias name for them ? Is it a bug ?

MMS is working well for Config-servers and MongoS nodes.

My MMS group is "MMS@Ork"

My Data nodes are as follows:

Server1 -> Primary of Replica-set1 + Secondary of Replica-set2(2 Nodes) Server2 -> Primary of Replica-set2 + Secondary of Replica-set1(2 Nodes) Arbiters of both replica-sets are on separate machines. I have kept one port number common for both the primaries and other port number common for both the secondaries.

Version of all the nodes in the cluster is 2.4.9 and that of MMS monitoring-agent is 2.3.1


texts:
I am using mongo db with replica sets. I am starting the mongodb using command prompt.

Python Script:

os.system('start mongod --configsvr --dbpath C:\mongoConfigData/configdb1 --replSet conf --port 27019')
os.system('start mongod --configsvr --dbpath C:\mongoConfigData/configdb2 --replSet conf --port 27020')
os.system('start mongod --configsvr --dbpath C:\mongoConfigData/configdb3 --replSet conf --port 27021')

os.system('start mongos --configdb "conf/localhost:27019,localhost:27020,localhost:27021" --logpath E:\mongoSLogs\mongoLog.log  --port 26060')

os.system('start mongod --replSet conf --shardsvr   --dbpath E:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --wiredTigerCacheSizeGB 7 --port 27010 ')

os.system('start mongod --replSet conf --shardsvr  --dbpath F:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --wiredTigerCacheSizeGB 7 --port 27011 ')

os.system('start mongod --replSet conf --shardsvr  --dbpath G:\data --storageEngine wiredTiger --wiredTigerJournalCompressor zlib --wiredTigerCollectionBlockCompressor zlib --wiredTigerCacheSizeGB 7 --port 27012 ')

I want to start the mongodb as a windows service.
Let me know how to convert the above in to windows service


texts: When will PostgreSQL execute archive_command to archive wal files?

texts:
I use the archive_command below to archive WAL files. Suppose I have touched the file archive_active already. When will PostgreSQL execute the supplied archive_command to archive WAL files?

I know we can use the function pg_switch_xlog() to archive the WAL on demand, are there other trigger conditions?

PostgreSQL version: 9.3.0.

--postgresql.conf

archive_command = 'test ! -f /archive/pg93/archive_active || cp %p /archive/pg93/%f'
wal_level = hot_standby
archive_mode = on

--archive directory

[pg93@redhatB pg93]$ pwd
/archive/pg93

[pg93@redhatB pg93]$ ll
total 32M
-rw-------. 1 pg93 pg93 16M Oct 16 11:05 00000001000000000000007A
-rw-------. 1 pg93 pg93 16M Oct 16 11:07 00000001000000000000007B
-rw-rw-r--. 1 pg93 pg93   0 Oct 16 11:05 archive_active


texts: Methods for right-sizing SQL Server virtualization?

texts:
I am starting to plan a migration of SQL Server 2008 R2 Enterprise Ed. to 2017 (edition TBD). One of the more important tasks assigned to me is to determine the specs for the new VMWare hardware my employer will be purchasing.

To do so, I need gather information about the current workload (on Windows Server 2008 R2 SP1 with SQL failover clustering on 5+ year-old hardware) and translate that to virtualized performance on new hardware - a VMware host which will use SQL Server 2017 and, probably, Availability Groups.

I've set up a PerfMon data collector based off of a blog post by David Klee ( link ) which will start tonight at midnight as a starting point but am still trying to wrap my head around the analysis...

i.e. "How do I take the PerfMon results from the workload running on the old hardware and translate that into requirements for new hardware ?".

Apart from PerfMon what are my other options for right-sizing the new hardware?


texts: See X-Request-ID in error messages of PostgreSQL

texts:
In my environment most PostgreSQL queries are generated from http requests.

In my case every http request has a unique header: X-Request-ID

If there is a error or warning in the PostgreSQL logs, I would like to see the related X-Request-ID to see where the sql query came from.

How to add an extra message like X-Request-ID to log messages of PostgreSQL?

Related: https://serverfault.com/a/797611/90324


texts: Seconds_Behind_Master high even though Read_Master_Log_Pos and Exec_Master_Log_Pos are very close

texts:
From what I understand from https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html, seconds_behind_master is the difference in the timestamp of where the IO thread is currently writing in the slave and where the SQL thread is executing now.  And the difference between Read_Master_Log_Pos and Exec_Master_Log_Pos should co-relate to the value of seconds behind master. But that is not the case here.

My DB had a significant slave lag, and I was trying to see if it was because of IO thread or the the SQL thread.

This is the result of show slave status.

         Slave_IO_State: Waiting for master to send event
                Master_Host: 10.85.162.164
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: 1085162164-mysql-binary.001952
        Read_Master_Log_Pos: 148220048
             Relay_Log_File: mysqld-relay-bin.002127
              Relay_Log_Pos: 148035459
      Relay_Master_Log_File: 1085162164-mysql-binary.001952
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table: ____
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: ____
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 148035282
            Relay_Log_Space: 148220460
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 540
Master_SSL_Verify_Server_Cert: No
              Last_IO_Errno: 0
              Last_IO_Error:
             Last_SQL_Errno: 0
             Last_SQL_Error:
Replicate_Ignore_Server_Ids:
           Master_Server_Id: 1085162164
                Master_UUID: 288abc1b-803e-11e7-9c70-02010a55a2a4
           Master_Info_File: /var/lib/mysql/master.info
                  SQL_Delay: 0
        SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: freeing items
         Master_Retry_Count: 86400
                Master_Bind:
    Last_IO_Error_Timestamp:
   Last_SQL_Error_Timestamp:
             Master_SSL_Crl:
         Master_SSL_Crlpath:
         Retrieved_Gtid_Set:
          Executed_Gtid_Set:
              Auto_Position: 0

This is the status on the master.

| File                           | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| 1085162164-mysql-binary.001952 | 776669711 |              |                  |                   |

The master_log_file and relay_master_log_file are in the same file, which is also the same file that the master is writing into. Here the difference between Read_Master_Log_Pos and Exec_Master_Log_Pos is also very small around 180000.

But there is a large difference to the position in the position of the master, which would suggest that the IO thread is the one that is lagging.

From the docs, seconds_behind_master's description contained the following statement.

  In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

Which should be very low in this case. But it is pretty high at 540 seconds.

Also, the value of seconds behind master is oscillating in values from 500 -> 0 -> 501 -> 0 -> 501 -> 0 -> 502 -> 0. What could cause this? One reason according to https://www.percona.com/blog/2013/09/16/possible-reasons-when-mysql-replication-lag-is-flapping-between-0-and-xxxxx/, might be that the IO link is saturated (#4), but I saw the network usage. It was around 50 MBps while the capacity of the link is 75 MBps. Any other possible reasons that could cause this?


texts: Oracle Enterprise Manager 11g: How to see statistics of Interfaces usage?

texts:
I need to give our client the following statistics:

How many users are using the Interfaces or each interface?
What interfaces are mostly used?
What are the usual usage time?
What kind of actions are mostly done?

All this info is needed to show to the client that our product is working.
My question is how to find all this data, numbers and statistics?


texts: How do I generate scripts to drop indexes, constraints, and keys for a database to change columns from int to bigint?

texts:
I've worked myself into a corner, and I'm not sure how to handle this.

I have a large set of databases with generally the same structure.  Each database has a list of ID columns that are currently int that i need to convert to bigint.  Presently, i'm creating scripts by hand to remove all of the constraints, indexes, and keys before modifying the columns to bigint and putting all of those objects back.

The problem I'm running into with slightly modifying the same script over and over is that I wind up losing objects or creating new objects along the way, which i'm not supposed to do.  There's a few catches to this:

1) Some indexes are used in custom logic on a database, so they'll
be present in some databases but not all.  So, losing indexes is
very bad.

2) Disk space is a major concern, so I need to make sure I don't
include additional indexes (which apparently cost this?)

3) There's a lot of logic that depends on keys and constraints
working, so I can't risk losing any of these.

Is there a way to create scripts for this, so I don't have to do these comparisons by hand and risk losing or adding objects that shouldn't be there?


texts: Does export in schema mode also includes tablespaces?

texts: I am executing an expdp command in schema mode. I'm just curious if it also exports tablespaces. I'm total newbie in oracle databases. What if I want to export schemas and tablespaces? What mode do I need to use?


texts: Is there a way to have PostgreSQL consider the Interpunct character · as a letter?

texts:
I'm working on an application storing strings of the Gallo language. In this language the Interpunct character · is considered a letter, not a word divider.

So I want for instance the query

SELECT * FROM records WHERE sentence ~* '\m·r'

to return records where sentence contains word starting with ·r.

Is there a way to force PosgresSQL to consider this Interpunct character ·  as a letter?


texts: Is there a way to partition a mysql table, by range columns, where the 2 columns in question are of datetime and bigint?

texts:
I need to partition a table into 4 partitions, p0 to p3: with p0 having all rows with "id"=0, and p1 having all rows with "id"=1 and "doj" before february, and p2 having "id"=1 and "doj" before march, and partition "future" with others. The value for id, is guaranteed to be either 0 or 1 always.

I've tried to come up with a solution and the following is what i arrived at:

create table temp
(id int not null primary key,
 doj datetime not null primary key)
partition by range columns(id,month(doj))
(partition p0 values less than (1, 13),
 partition p1 values less than (2, 2),
 partition p2 values less than (2, 3),
 partition p3 values less than(maxvalue, maxvalue));

I get the following error when i try and execute the above sql command:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(doj)) (partition p0 values less than (1, maxvalue), partition p1 values less t' at line 4

Any insights as to how to partition the table in a manner described above would be much appreciated.

Version of MySql used is:
5.6.19-67.0-log


texts:
its been a few years since i used SQL i mostly use R and sas now and i have an interview thats states sql understanding is required
The job role is for Risk Assessment Model Developer
The job role states -
•   The role holder will have the ability to leverage  analytics to support the development of models and interpretation of regulations, statutes, and conduct matters in business context and Intelligence, delivering complex information in a way that is simple and easy to understand.
Im comfortable with my skills when im pulling data from a formatted database or live system  but it seems on this test they want knowledge on taking unformatted data and showing indepth formatted MI as a result.
Formatting will be my downfall here if i cant get up to date as i had the easy life of using sas eg where the tools really helps me with my  informatting
Below is all i have been given as what to expect and prep for so any help would be appreciated
What to expect?
• The questions are technical and include database interrogation.
• The data is on separate lines which needs to be concatenated into different columns. The length and end aren’t the same.
• Look up or refresh - order by Rand() limit function


texts:
I get this error ORA-02289: sequence does not exist when I try to import a schema to my database, could you give me some recommendation?

Import Command:

impdp '"sys/mmm as sysdba"' directory=BACK_IMPORT dumpfile=0502190800%U.dmp
logfile=impdp_MBD.log SCHEMAS=MSCHEM PARALLEL=7 encryption_password=0qazw

Export Command:

expdp '"sys/mmm as sysdba"' encryption=all encryption_password=0qazw
encryption_algorithm=aes256 COMPRESSION=ALL DIRECTORY=BACK_EXPORT
COMPRESSION_ALGORITHM=BASIC LOGTIME=ALL CONTENT=ALL flashback_time=systimestamp
DUMPFILE=$name% U.dmp LOGFILE=$name.log PARALLEL=7 SCHEMAS=MSCHEM,IOOUT,ZHIST;


texts: Do certain string patterns affect query performance on a VARCHAR column?

texts:
I am a beginner on MySQL Administration and performance problems. Now this is confusing me.

I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, user:4 to identify a row in one type of entity.

There are 5 class names for now.

Since the string could be constructed as note:123 or 123:note, either holds the meaning of it, I am wondering does 123:note will have better query performance if I do select * from table where identity='123:note' than the other? I am thinking that 123:note will have more variety from the beginning of string, because the id is at the front.

Or it just doesn't matter?

UPDATE:
the identity field is just a redundant field for query, the type and id are actually stored in other two fields in the same table.

So:

1st, where type='note' and id=123' is definitely faster than where identity in ('note:123', 'user:456'). I think it's definitely faster, right?

2nd, if I have to have this redundant column, note:123 or 123:note which pattern is faster when I do where identity in ('note:123', 'user:456'), and the length of identity is 20.

And here is create info: likeable_identity is the identity field I am talking about.

CREATE TABLE `likes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `likeable_id` int(11) DEFAULT NULL,
  `likeable_type` varchar(255) DEFAULT NULL,
  `likeable_identity` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_likes_on_user_id_and_likeable_id_and_likeable_type` (`user_id`,`likeable_id`,`likeable_type`),
  KEY `index_likes_on_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8814 DEFAULT CHARSET=utf8;

Thanks.


texts: Should I create multiple maintenance plans to backup more than 200 User databases

texts:
I have a SQL Server 2008 with more than 200 User databases. Some are larger than others and every two weeks or so a new db gets created.
Should I create only 1 maintenance plan backing up all 200+ databases or should I break them into 3 separate maintenance plans.
How much load does a backup plan generate on the server. Will it affect performance if I have them in the same Plan.
Some more details regarding the infrastructure.
I am using SSD's. The storage is all SATA, It's not a SAN but I have a network drive where I store the backups. I cannot backup to a tape. My backup strategy is Full once a week Differential every day. I am using SImple backup (Due to storage restrictions, cannot let logs grow and cannot have logs backup). The databases as I mentioned are roughly 10 GB around 10 or 12 DBs are 100 GB+ and I cannot use third party tools. I can create Maintenance plans


texts: How can I change the value of min word length for specific query in fulltext search?

texts:
I can set it a new value by adding this line under the [mysqld] section in my.cnf configuration file:

ft_min_word_len = 3 // or any other length

But I don't want that. I want to know, can I do that in a specific query? I have this query:

select * from mytable where match(mycol) against(:val)

And I want to change ft_min_word_len just for query above. I don't want to change it in MySQL configuration file. I mean I want to have another min word length for other queries.


texts: SQL query to INFORMATION_SCHEMA.TABLES to only return those I have select access to

texts:
I currently have a query in SQL 2008 that can return me a list of tables for a given database, provided I have View Definition access to them.

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from information_schema.Tables
order by TABLE_NAME

The way our database roles are managed, the existing select permissions are nicely configured across roles, and adding the new View Definitions is done as a single schema-viewing role.

Is there a way I can limit this to the tables the current user has select permission on?


texts:
I don't know why but if I do the subquery where in the first LEFT JOIN with the value of the variable @id it does not return any results, it is as if the value of @id was always null.
But why not do the LEFT JOIN directly?
Simple, the table is large and the query is taking horrors to execute, so it would be much faster.
SELECT
    DISTINCT @id := p.id_pen,
    p.id_env_pen,

    0 AS id_star,
    FALSE AS is_star,

    u.id_user AS sender_id,
    u.name_user AS sender_name,

    u2.id_user AS response_id,
    u2.name_user AS response_name,

    p.text_pen AS text_send,
    p2.text_pen AS text_response,

    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,

    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
    LEFT JOIN (
        SELECT
            p2.id_pen,
            p2.text_pen,
            p2.created_at_pen,
            p2.id_pen_req_pen
        FROM pendency AS p2
        WHERE p2.id_pen_req_pen = @id
        # HERE, I try do `= p.id_pen` but dont works too,
        # id not identify the alias from outside
    ) AS p2 ON p2.id_pen_req_pen = @id

    INNER JOIN users AS u ON u.id_user = p.id_user_pen
    LEFT JOIN users AS u2 ON u2.id_user = p.id_user_req_pen
WHERE (p.id_user_pen = '5' AND p.id_user_req_pen != -10)
OR p.id_user_req_pen = '5'

CREATE TABLES
CREATE TABLE IF NOT EXISTS `users` (
    `id_user` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `id_fs_user` VARCHAR(30) DEFAULT 'Não informado',
    `email_user` VARCHAR(105) DEFAULT 'Não informado',
    `pass_user` VARCHAR(35) DEFAULT 'Não informado',
    `name_user` VARCHAR(120) DEFAULT 'Não informado',
    `cpf_user` VARCHAR(15) DEFAULT 'Não informado',
    `type_user` VARCHAR(15) DEFAULT 'master',
    `work_week_user` INT DEFAULT 5,
    `is_block_user` BOOLEAN DEFAULT FALSE,
    `permission_id_user` INT NOT NULL DEFAULT 0,
    `attendant_user` BOOLEAN DEFAULT FALSE,
    `version_id_user` INT NOT NULL DEFAULT 0,
    `last_login_user` DATETIME,
    `last_move_user` DATETIME,

    `id_user_user` INT DEFAULT 0,
    `created_at_user` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `safe_del_date_user` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `safe_del_state_user` BOOLEAN DEFAULT FALSE,
    `safe_del_user_user` INT DEFAULT 0);

CREATE TABLE IF NOT EXISTS `pendency` (
    `id_pen` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `id_env_pen` INT NOT NULL,
    `text_pen` TEXT,
    `id_user_req_pen` INT DEFAULT 0,
    `id_pen_req_pen` INT DEFAULT 0,
    `time_res_pen` INT NOT NULL,
    `state_pen` BOOLEAN DEFAULT FALSE,
    `id_old_pen` INT NOT NULL,
    `id_user_pen` INT NOT NULL,
    `created_at_pen` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`id_env_pen`) REFERENCES `env`(`id_env`) ON DELETE CASCADE);


texts: Why is XML taking up more storage than VARCHAR(MAX)?

texts:
We have large tables storing XML data as varchar(MAX). The data is for reference/historical purposes, it's not queried. Based on what I've read, storing as XML datatype instead of VARCHAR(MAX) should result in space savings, but my tests show otherwise.  See below, where the size of t1_XML is smaller than t1_NVARCHARMAX, but larger than t1_VARCHARMAX.

set nocount on;

drop table t1_XML;
drop table t1_VARCHARMAX;
drop table t1_NVARCHARMAX;

create table t1_XML(col1 int identity primary key, col2 XML);
create table t1_VARCHARMAX(col1 int identity primary key, col2 varchar(max));
create table t1_NVARCHARMAX(col1 int identity primary key, col2 nvarchar(max));

go

declare @xml XML = '<root><element1>test</element1><element2>test</element2><element3>test</element3><element4>test</element4><element5>test</element5></root>'
    , @x int = 1;

while @x <= 10000
begin
    begin tran

    insert into dbo.t1_XML (col2) values (@xml);
    insert into dbo.t1_VARCHARMAX (col2) values (cast(@xml as varchar(max)));
    insert into dbo.t1_NVARCHARMAX (col2) values (cast(@xml as varchar(max)));

    commit tran

    set @x += 1;
end

exec sp_spaceused 'dbo.t1_XML';
exec sp_spaceused 'dbo.t1_VARCHARMAX';
exec sp_spaceused 'dbo.t1_NVARCHARMAX';


texts: Create database in separate partition or new drive in mysql

texts:
We are using MySQL 5.6 having all databases in 1 partition /usr/local/mysql.

Now we want to create a new database which is of large size. But the existing partition is not having enough space and one more limitation is we are not able to add additional space to the existing mysql partition (usr/local/mysql).

I came to know about tablespace feature in MySQL. (creating a new tablespace in new partition and create all the tables pointing to the new tablespace). By this, all the tables and data will be using/creating in same tablespace.

Do using of same tablespace is good standard ? This may again loose the feature of innodb_file_per_table right ?

Are there any other ways to create new databases in new partitions/drives without disturbing the existing databases ?


texts: Postgres – how to alter the WHERE clause of a postgres index

texts:
I need to insert a new two-field index on an existing table with about 600 million rows in it. The table contains data accumulated over about a year with approximately 2 million new rows added per day.

I know adding a new index to one very large table in one go will take ages, so to save time I started by only indexing the last few days worth of data using the following command…

CREATE INDEX  idx_001
ON message (deviceNumber, messageTime)
WHERE messageTime >='07-May-2018 00:00:00' ;

This took about 3 hours, or about 40 minutes to index one day.

Ideally I’d like to index the whole table, but at 40 minutes for a days data doing it on one go will take over a week.

I guess I could create lots of small indexes, each for a few days of data, maybe something like this…

CREATE INDEX  idx_002
ON message (deviceNumber, messageTime)
WHERE messageTime <'07-May-2018 00:00:00' AND messageTime >='01-May-2018 00:00:00'  ;

But I’d prefer to stick with one index. Is there any way of ALTERing the where clause of the existing index to increase the days covered? Or do I have to create lots of small indexes?


texts: Validating the GST Identification Number (GSTIN)

texts:
The following image describes the format of the GST Identification Number:

The first 2 digits denote the unique State Code in accordance with the Indian Census 2011. For instance, the State Code of New Delhi is '07' and that of Karnataka is '29'.
The next 10 characters denote the PAN (Permanent Account Number) of the taxpayer.
The 13th digit denotes the Registration Number (or Entity Number) of the tax payer with the same PAN.
The 14th digit is 'Z' by default for all – not intending anything currently.
The 15th digit is the Check sum digit – can be a number or an alphabetical character.

Perhaps I can carry out the validation by means of a PATINDEX or a Regex?


texts: best approach to model relation between customers - dimension dim_customer (SCD2) which can be a HEAD/SUB customer

texts:
Hi there we have the concept of having department. each department can either be its own head or it can have a head department. (similar to each person in a organisation is a employee but some employee they have head/supervisor or some employee they act as a supervisior for a certain time that can also change over time if employees are leaving)
so we have a dim_department (SC2)
dim_department

so it is an SCD2 which can slowly change over time if the name is changing ect
so let's say the dep1 is now becoming the head of the dep2
later there will be another department "dep3" be open and this will then become the head
so the head of dep2 is changing
what would be the best approach to model that
I guess there are several options/solutions to solve that problem
so I could
first option
add the head SK in the dim_department
so every time a Head is changing it would lead to an entry in dim_customer in our case a new entry for dep3 would be created and a new entry (slowly changing entry) for dep1 with a closing old current_yn and creating new current_yn

second option I thought
create a bridge table or factless fact table

or am I totaly wrong?
a third option would just be to put the Head SK in the fact tables
so we would than have

another options
a snowflake dimension ? hmmm wondering if that would work with an SCD2 dimension?
so in a nutshell it is on this SCD2 table a relation with itself
what I see as a problem with option 1 is that I have first to handle the HEAD entries because the attributes could change (because of changing name etc) and would generate a new SK and than I would need to lookup that key when handling the sub
what do you think?
is this total bullshit or totaly on the wrong path?
Thx


texts: Why Sql Server management studio use English regional settings instead my local Windows settings?

texts:
Scenario:
I've a Sql Server instance on English Windows Server.
But I've my own ITALIAN client with Sql Server Management Studio and SSMS regional settings set to Italian.
So when I extract data, i need separator as follow :
, (comma) for decimal
. (point) for thousands
Example: 1.065,00
Instead , Sql Server Management Studio return me number like:
1065.0000
(point for decimal separator)
If I copy & paste into Excel all data are mess..
How can I do without writing specific query with "Format" or similar ?
Thanks
Example : point , but i need comma!


texts:
Suppose my system has 8GB RAM and I have installed SQL Server 2012 on it along with the DataTools. Sql server engine uses 6GB memory. Will the SSIS uses remaning memory apart from 6GB or it uses 6GB?
Also is there a way to count the number of buffers used and the records accomodated in each buffer and the size of the buffer?

If am not wrong buffer refers to the RAM memory?


texts: Import and export data from SQL Server table using query

texts: I need to import and export data from a table using a SQL query so I need query example for importing and exporting data. I don't want to export it to txt file as the data is not getting aligned. I have read that this can done manually but I want to do this dynamically. You can see below manually import and export data.


texts: 2 Oracle SQL queries return different number of rows when number should be the same. Clause `IN`

texts:
2 Oracle SQL queries return different number of rows when number should be the same. At least I believe the number of rows should be the same.
Please see SQL query #1:
select count(*) from temp_dormer_mro
where catalog_number in (select catalog_number from temp_dormer_data);

It returns 961 rows.
Please see SQL query #2:
select count(*) from temp_dormer_data
where catalog_number in (select catalog_number from temp_dormer_mro);

It returns 1278 rows.
Please help me to understand why I got different number of rows for these 2 queries.
---------- Update #1 ------------
Data in tables looks like this:

There is no NULL values in any row.
Table temp_dormer_data has 1,278 records. So basically second SQL query just select all rows in that table.
---------- Update #2 ------------
select count(*) from temp_dormer_mro
join temp_dormer_data
    on temp_dormer_data.catalog_number = temp_dormer_mro.catalog_number
;

select count(*) from temp_dormer_data
join temp_dormer_mro
    on temp_dormer_mro.catalog_number = temp_dormer_data.catalog_number
;

Both above SQL queries return 1,278 rows. And that number seems to be correct. But why the first SQL query returns incorrect number?


texts:
What is the best way to track operations done on my MongoDB instance?
I want to display a graph with the number of user's operations (insert, update, query etc..) done.

I have read about profiling, but is there a better strategy? How does the MMS do that?


texts:
I am a beginner to mysql.

I would like to convert a timestamp like this "12:00:00 AM.260" to miliseconds.

The reference for the conversion will be "12:00:00 AM.000".

How should i go about this ?

Thank you very much !


texts:
We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the new server is slightly worse than the old, slower server.

In testing, our ASP.NET application runs 10 - 20% slower. Running individual expensive queries with STATISTICS IO and STATISTICS TIME shows 10 - 20% greater elapsed time on the new server. SQL Query Profile shows higher CPU usage on expensive queries.

Task Manager on the new server shows sqlserver.exe is consuming 22 GB of RAM, but the CPU values always stay very low.

I've updated all statistics, rebuilt or reorganized indexes, etc. Execution plans should be stored on the new server at this point, given the amount of testing I've done. If there are any missing indexes (I don't think there are) they affect the old and new servers equally. New has a restored backup of the same data on the old.

I'd expected that the performance on the new server would be better, but of more concern is load. If the old server is performing better even under load, then what will happen when this new, slightly worse server has to take that load?

What else could I be missing here?

EDIT: MAXDOP set to 6.

Old server has OS, databases, and tempdb's on the same physical drives (RAID 10). Total of 4 15k 3 Gb/s 3.5 inch SAS.
New server has three drive sets: OS on RAID 1, database on RAID 10, tempdb on RAID 5. Total of 9 15K 6 Gb/s 2.5 Inch SAS.

Old server has 1 x Intel Xeon E5620 2.40 GHz Quad-Core 8 Threads (w H/T).
New server has 2 x Intel Xeon E5-2640 2.5 GHz Six -Core 12 Threads (w H/T).

EDIT 2: Here's the final analysis:

The power plan was on balanced, not high performance. Switched that over.

Tempdb was on a RAID 5, not RAID 10. Added another HD to create two physically distinct RAID 10 configs, one for tempdb and one for everything else.

Excluded SQL-related files (mdf, ldf, ndf, bak) from virus scanning.

Rebuilt all indexes following the move to the new server. They were very fragmented -- possibly as a result of backup, copy, restore?

And I realized that the processor jump was not that big. The queries aren't going to execute that much faster, but with more processors, more cores, more RAM, we'll be more scalable.


texts: How to backup SSIS packages and CUBE Projects from Visual Studio 2013.5?

texts: I'm using VisualStudio 2013.5 to develop SSIS, SSDT and SSAS Cubes (multidimensional and tabular model). I would like to ask you is there a possible to backup SSIS packages and SSAS cubes from VisualStudio to disk to be able to transfer particular once to a diferent workstation with VisualStudio and load/restore them there ? Do you know how to do this ?


texts: SQL Server function invocation syntax question

texts:
The following excerpt is extract from this page.

  CURRENT_USER (Transact-SQL)

  Returns the name of the current user. This function is equivalent to
  USER_NAME().

When I execute the two function with the following TSQL scripts (the questionable ones were commented), I'm confused about the invocation syntax. I want to know when to use the parenthesis and when not. Thanks!

SELECT CURRENT_USER
-- SELECT CURRENT_USER() -- Error!
SELECT USER_NAME()
-- SELECT USER_NAME -- Error!


texts: How can I find where FK = PK in the same table?

texts:
I have one table named employees which contains employees' IDs (which is PK)-stands for managers-and reportsTo(which is FK). I don't know what is the correct condition to find which manager is being reported to by their employees? I've tried this condition but it didn't work

select
   employeeNumber,
   lastName,
   phone,
   extension
from employees
where reportsTo = employeeNumber

I'm new to mysql!
Here is my table
Thank you.


texts: Use SSMS to Create Database Diagrams but not Database Objects

texts:
I'm looking to be able to create a database diagram without actually creating database objects.  It seems like the best way to do this would be directly through SSMS.  My only question is, is there a way to not have SSMS create the database objects when you create them in the database diagram?

I want this as a planning and design tool, not necessarily for implementation yet.  I just want to lay it all out without actually creating any tables yet.  When I am in the Database Diagram and click New Table, I just want it to create the new table to be used in the diagram as opposed to a table database object being created in the database.


texts: AWS KMS Encryption enable for SQL Server Columns?

texts: Is it possible MSSQL on EC2 with a key in AWS KMS to encrypt columns


texts: Will PostgreSQL use parts of a composite index even if all parts aren't listed in the where clauses?

texts:
I could obviously test this empirically but I've found that without loading fake data EXPLAIN shows that Postgres just loads the full page of memory containing the table and scans. I also haven't found the answer in the docs.
My question is, if I have an index on a table such as (colA, colB) and a query containing where colA = 'something' will Postgres use this index even though colB is not in the query? Assume that no index containing just colA exists. It stands to reason that this index could be useful in the query execution, but I've had a hard time tracking the answer down definitively.
(Postgres v12.x)


texts: How do I get the corresponding row for a RowsetID?

texts:
We're faced with the following error when a query runs against a partitioned table with Read-Only file groups:

  The index "ix_my_index" for table "dbo.my_table" (RowsetId 72057600379912192) resides on a read-only filegroup ("MyFileGroup"), which cannot be modified

How would I identify which row the query is trying to modify from the RowsetID?

I know sysrowsets used to exist, but doesn't seem to in SQL Server 2012.


texts: Copy PostgreSQL database from a remote server

texts:
When I was using MySQL, I could run a command that would SSH into my server and copy the database to my local machine.
ssh -t remoteserver 'mysqldump --compress -u dbuser --password="password" db_name' | /usr/local/mysql/bin/mysql -u root --password="password" local_db_name

How can I do the same thing with PostgreSQL?


texts: SQL Server 2008 R2 SQL Server Profiler and "exec sp_reset_connection"

texts:
Yesterday i ran the "SQL Server Profiler" tool and outputted the data to a table, now when i look in it i find many "exec sp_reset_connection" rows and thats expected with our data access layer!

But what i can't explain is that some times it takes 8 seconds to execute this query...

What could cause the sp_reset_connection to delay that much?


texts: How to query hstore tag keys that match part of the filter string

texts:
In PostgreSQL query, how can I filter the results by using any record that tags with a key like %building%

something like:

..
where
    Table.tags ? '%building%'

I didn't find an answer within the postgresql documentation (https://www.postgresql.org/docs/9.6/static/hstore.html)


texts: How make my architecture scalable to handle linear process

texts:
I have a system to calculate Real Time Traffic (RTT), using Automatic Vehicle Location (AVL).

I can know where the car was, where is now, and calculating distance and time can estimate the traffic speed on those roads. I have two store procedures:

near_link using avl (x,y) found the near road link
create_route using pgrouting extension, current link and previous link can calculate the route the vehicule took.

Both process are very linear, calculate the near_link for 1 row take 10 ms, calculate for 100 rows take 1000 ms or 1 sec. calculate the route is a litle more expensive 50 ms for one route or 5 sec for 100 routes.

The problem is the avl fleet growth, so instead of 400 avl/min I now receive 2000 avl/min. And instead of 400*60ms = 24 sec now I need 2000*60 = 120 sec, so every minute I can only process half of the data.

The only solution I can think right now is have two separated servers one handle even car_id and the other odd car_id so split the load between both servers.

Currently Im using just a production Desktop, Windows i3 Core 3Ghz 8gb RAM normal disks. I can request a better hardware for the production server. For example I know the querys are very HDD demanding because need to check the map_rto table very often, but I can see in the resources monitor CPU and Memory very low use. So I could upgrade to SDD disk. And hope my time is reduce to half.

But what happen when the fleet increase to 4000 avl/min or to 8000 avl/min. What are the strategies to scale those linear calculations??.

Aditional description of current design:

I like the idea of use of pgAgent and triggers to move the data from one stage to another. But maybe there is a better way to do it.

avl_sources (table): I can have multiple companies providing data and each one has a separated table and there are triggers on each table to insert new rows into the avl_pool table

Each row has: car_id, x, y, azimuth, datetime.
I check external source every min, receive around 2000 records and take ~5 sec to finish all the process.

map_rto (table): contain my coutry roads information. There are ~3 million links. Is used to calculate the near_link and the route
near_link (sp):  Using avl x, y, azimuth try to find the closest link to that position. I call this sp from a pg_agent job every minute.
export (sp): Also call this sp from pgAgent. Move those avl with near_link to traffic_avl table
traffic_avl (table):: This table has a trigger to using the current position and previous position calculate the route.


texts: Query to find Maximum friends not giving correct resuts

texts:
Table Schema for the Likes table:

id id1
1  2
1  3
2  1

I count the total number of Likes for each student with this:

select id,count(*)
from friends
group by id

I have to find the students with the maximum number of Likes, so I use this query as a temp table:

select * from (
  select f1.id,count(*) as count1
  from Likes f1
  group by id
) temp
where not exists (
  select f2.id,count(asterick) as count2
  from Likes f2 group by f2.id having count2 > temp.count1
)

It returns all of the records. Can anyone point out what I am doing wrong in this query?


texts: Prevent Data Sources from being deployed outside designated folder

texts:
Before I just go ahead and put a really awful check constraint on the Catalog table, I wanted to solicit some better ideas first.

I want to ensure that all shared data sources on our report server are deployed to "/Data Sources". Every now and then, we get one mistakenly deployed to some other directory (particularly if it's a report upgraded from SSRS 2000, which didn't allow specifying a different data source deployment location).

I can put an ugly check constraint on Catalog (Type != 5 OR ParentID = 'GUID of /Data Sources directory', or similar) if it comes down to it, but if there's a better option, I'd rather use that.


texts: How, when and why does implicit changing of transaction isolation level happen?

texts:
RCSI (Read Committed Snapshot Isolation) is the setting of a database changing the behavior of (the default) Read Committed isolation level in SQL Server.

This is, as I understand, the context of the sripts in MSDN blog article "Concurrency Series: Minimizing blocking between updaters" telling that:

"The second trick of using RCSI does not work either and the update gets blocked behind X lock by session-1 on row with (C1 = 1). The reason is that the following UPDATE statement executes at a higher isolation level than read committed.

update basic_locking set c2 = 1000 where c1 = 2

Here is the output showing the blocking

Solutions: Here are two choices to address this issue:"

How does "the output" illustrate the implicit elevation of implicit default isolation level (of Read Committed)?

How to check for real isolation level "jumpings" in context of some statements?

When to expect them and why do they occur?


texts: after executing a back up cmd (via query) how to kill it

texts:
I have a big database (150 GB) that needs a back up and I don't have space in current machine, so I am pointing it to the different box which has space. After executing the cmd, it is executing the query but taking long time, not showing stats also.

Query used:

backup database [DBName] to
disk = '\\Servername\H:\Backup7-7-2017\dbname.bak'
with copy_only, stats = 1

go

It is running for more than 5 minutes and no change on stats, and in destination folder the file is not created yet, so I am planning to go for manual backup from GUI. Now I want to kill this running backup as it is in suspended mode, when I checked in activity monitor.


texts: How do I create a user-defined aggregate function?

texts:
I need an aggregate function that MySQL doesn't provide.

I would like it to be in MySQL's flavor of SQL (that is, not in C).

How do I do this?  What I'm stuck on is creating an aggregate function -- the docs don't seem to mention how this is done.

Examples of desired usage of a product function:

mysql> select product(col) as a from `table`;
+------+
| a    |
+------+
|  144 |
+------+
1 row in set (0.00 sec)

mysql> select col, product(col) as a from `table` group by col;
+-----+------+
| col | a    |
+-----+------+
|   6 |   36 |
|   4 |    4 |
+-----+------+
2 rows in set (0.01 sec)


texts: Increased server load after InnoDB deadlock

texts:
I am using InnoDB instead of MyISAM for a couple of months right now, and it has really optimized my game database-wise. I was aware of the danger of deadlocks, but hadn't had any problems with it, until about a week ago.

I've had a deadlock because two cronjobs were clashing with each other, I believe. Anyway, we've got that fixed by killing both queries and running the cronjobs after each other manually.

All seemed good, except for the fact that the server load is peeking a lot since then (about every 5 minutes) which I don't really know why. I've checked the running queries at those moments and they seem fine.

Do you have any tips where to look on how to fix this? Couldn't find too many information about this on the internet. Thanks in advance.


texts: Equivalent of SQL Not Exists in MDX (or finding another way to accomplish same result)

texts:
I am trying to help a contractor implement a cube in Multi-Dimensional SSAS 2017 and do not have very much experience in MDX.

I have a table that looks something like this:

The goal is to only include rows in the query that have the lowest [seq] per each combo of [Part], [CustomerID], and PersonID after filters have been applied, and then sum the value column. So if no filters are applied, only rows 1 and 3 should be returned and the sum of the value column should be 2. But if the user filters data for only the month of June, only rows 2 and 3 should be returned and the sum of the value column should be 1.

We accomplished this in SQL like this:

SELECT SUM(Value)
FROM (SELECT *
 ,ROW_NUMBER() OVER (PARTITION BY CustomerID,PersonID, Part ORDER BY SEQ asc) AS Seq
 FROM Table WHERE DATEKeY BETWEEN @StartDate AND @EndDate
 )A
WHERE Seq=1;

But the contractor is having big problems with performance of the MDX rank function. Not having very much experience with MDX, I am having a hard time knowing what alternatives there are that will perform well and the contractor has not provided any alternatives that match our business need.

Another approach I came up with is this:

SELECT SUM(Value)
FROM [Table]  AS A
WHERE DATEKey BETWEEN @StartDate AND @ENDDate
AND CustomerID=@CustomerID
AND NOT EXISTS(SELECT 1 FROM Table AS B
                WHERE B.Part=A.Part
                  AND B.CustomerID=@CustomerID
                  AND B.PersonID=A.PersonID
                  AND B.SEQ<A.SEQ)

This is actually performing better than the old way in SQL but I have no clue how to implement either this, the previous method, or equivalent logic in MDX that is going to perform well.

I know the MDX will be hard to do without seeing the whole cube but faux code or just some advice on what functions will offer the best performance for this logic would be a big help.


texts: MySQL Help - Combining 2 MySQL Selects - Result in 1

texts:
I have two MySQL statements (see below), I would like to combine them together so if they both result in a 1 then the end result will be 1. I'm not sure how to construct this and was hoping for some help.

select count(*)
from monitor
where name='job_starttime' and value < ( UNIX_TIMESTAMP( ) -600)

select count(*)
from monitor
where name='job_active' and value = 0

So for example I would like when both statements are true to result in a value of 1, if 1 or none are true it results in a 0.

I nearly achieved it with the below SQL (except it's giving a result for each entry in the table) :(

select
case
when (select count(*) from monitor
where exists (select * from monitor where name = 'job1_active' AND value = 1)
and exists (select * from monitor where name = 'job2_active' AND value = 1)) > 1
then 1
else 0
end
from monitor


texts: PostgreSQL 9.6, Disable archive log or minimal archive log size when do a pg_restore

texts: I have a production DB PostgreSQL 9.6 that is on master/standby structure. Every at 1 AM I restore a few huge tables(from the other DB server) to the master node during restore till finished the archive log generated around 100GB. Is there any good way to minimize the archive_log from the pg_restore task?


texts:
Note location of cursor. At first I thought it was the lack of a semi colon, but as you can see, putting one in does not solve the problem or change the error. I compared this code to samples I found on http://initd.org/psycopg/docs/usage.html :

Based on this, I don't see the problem. FWIW, Pylint in ST3 has no issues with this code. thanks.

from os import getcwd, listdir
import psycopg2
from io import open

conn = psycopg2.connect("dbname=ktab user=malikarumi")
cur = conn.cursor()
path = getcwd()
filenames = listdir(path)

for filename in filenames:
with open(filename, 'rb') as f:
    f1 = f.read()
    cur.execute("INSERT INTO testable (title, content, chron_date, clock)\
    VALUES (%s, %s, %s, %s);"),
    (filename, f1, '2017-12-30', '23:59:00'),

conn.commit()
cur.close()
conn.close()


texts: Oracle Listener stoped as soon as it started

texts:
I started oracle listener. it seemed working well. but I didn't actually.

I hope someone could help me with blow

=========================================================================

[oracle@cube admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-NOV-2015 11:26:06

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /home/oracle/app/oracle/diag/tnslsnr/cube/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.168)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                26-NOV-2015 11:26:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/cube/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.168)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@cube admin]$
[oracle@cube admin]$
[oracle@cube admin]$
[oracle@cube admin]$
[oracle@cube admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-NOV-2015 11:26:58

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.168)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener

=========================================================================

my listener.ora is this

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=PLSExtProc)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)
    )
    (SID_DESC=
      (SID_NAME=orcl)
      (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.168)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /home/oracle/app/oracle

I don't know what is wrong...


texts: Are there alternatives to Microsoft Access?

texts:
In my office, we often use MS Access for many applications (eg creating reports, data analysis and data processing) because it is convenient to use (easy data import, fast query building, easy automatization with VBA)

Unfortunately, it starts to be insufficient for me due to the size limitations of the database file. This is one thing. Another thing is that Access is often unable to execute my complex SQL queries (I omit that the SQL used by Access is limited).
Is anyone out there aware of any tools similar to MS Access that can be used with databases over 2GB?

Possibly, I am considering using Access as a front-end, but what about the back-end? Can you recommend something?


texts: SQL Server configuration manager - New alias grayed out

texts:
I want to create an alias in SQL Server Configuration Manager. I can navigate to "Aliases" in the tree on the left, and I can right-click to open the context menu when "New alias..." is an option. When I click "New alias...", I get the following window:

All of the fields are grayed-out and uneditable. Is this a bug in SQL Server Configuration Manager, or am I doing something wrong?

This is SQL Server 2016 Standard. I was able to create an alias previously so I do not know why this is not working. I have tried multiple workarounds, such as opening Configuration Manager as administrator, and running the 64-bit version of the SQLServerManager13.msc.


texts: Grant privileges to all users on a single table

texts:
Is it possible to grant privileges (such as SELECT, INSERT, UPDATE, etc) to all the users for a specific table at the same time?

Here is what I tried but it didn't work out...

GRANT SELECT ON customer TO * @'localhost';


texts: Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

texts:
I have a table and need to add a new column without a default value:

Constraint:

ALTER TABLE integrations.billables
DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables,
ADD CONSTRAINT cc_at_least_one_mapping_needed_billables
CHECK ((("qb_id" IS NOT NULL) :: INTEGER +
    ("xero_id" IS NOT NULL) :: INTEGER +
    ("freshbooks_id" IS NOT NULL) :: INTEGER +
    ("unleashed_id" IS NOT NULL) :: INTEGER +
    ("csv_data" IS NOT NULL) :: INTEGER +
    ("myob_id" IS NOT NULL) :: INTEGER) > 0);

Column:

ALTER TABLE integrations.billables
DROP COLUMN IF EXISTS myob_id,
ADD COLUMN myob_id varchar(255);

Question:

How can I add the constraint for the next values and not for those which are already in there? (Otherwise I'd get the error check constraint "" is violated by some row).

This is related to my previous question: ERROR: check constraint is violated by some row


texts: Why select max partitioned column is much slower than indexed one?

texts:
I have a big partitioned table (300+M rows, 70 active partitions). The table has clustered PK of (K0, K1) and it's partitioned on column K1. And K1 has very low selectivity.

However the following query takes a half minute and the scan count is 170 and logical read is 603K.

select max(K1) from table

However the similar query on on non-partitioned table with index on K1 takes no time and scan count is 1 and logical read is 5.

Should a none partitioned index be created on K1?

Edit:
Plan text for partitioned table without index on K1. With clustered PK on K0,K1, before create an index on C1.

  |--Stream Aggregate(DEFINE:([Expr1004]=MAX([partialagg1005])))
       |--Parallelism(Gather Streams)
            |--Stream Aggregate(DEFINE:([partialagg1005]=MAX([DB1].[dbo].[table1].[K1])))
                 |--Clustered Index Scan(OBJECT:([DB1].[dbo].[table1].[PK_dbo_tabl1]))

Plan text for non-partitioned table with index on K1.

  |--Stream Aggregate(DEFINE:([Expr1003]=MAX([DB1].[dbo].[table2].[K1])))
       |--Top(TOP EXPRESSION:((1)))
            |--Index Scan(OBJECT:([Db1].[dbo].[table2].[idx_K1]), ORDERED BACKWARD)

Plan text for partitioned table with clustered PK on K0,K1, and index on C1.

  |--Stream Aggregate(DEFINE:([Expr1004]=MAX([partialagg1005])))
       |--Parallelism(Gather Streams)
            |--Stream Aggregate(DEFINE:([partialagg1005]=MAX([db1].[dbo].[table1].[K1])))
                 |--Index Scan(OBJECT:([db1].[dbo].[table1].[IX_C1]))


texts: Return results from SQL JOIN even if criteria not met in all tables

texts:
New to SQL. Building my first contact manager project. Current schema can be seen below.
Issue:

I'm working on a view that shows all contact types (phone, email, socials, address) for a given contact.
I put together the following attempt, but it only returns results if the contactGUID is in every contact_type table.

--Problem: Only returns records if contactGUID is present in every contact_type table

SELECT
c.contGUID, c.contType,
i.indTitle, i.indFirstName, i.indMidName, i.indLastName,
e.emailAddress, e.emailDomain,
p.phnCountryCode, p.phnAreaCode, p.phnNumber,
s.socURL, s.socHandle,
a.addrCountry, a.addrCountryCode, a.addrCountrySubdivision, a.addrCountrySubName, a.addrSecondarySubdiv, a.addrMunicipality, a.addrMuniSubdivision, a.addrStreetNumber, a.addrStreetName, a.addrPostalCode, a.addrLatitude, a.addrLongitude

FROM
CONTACT c

JOIN INDIVIDUALS i
ON c.contGUID = i.indGUID

JOIN CONTACT_EMAIL ce
ON c.contGUID = ce.contactGUID

JOIN EMAILS e
ON ce.emailGUID = e.emailGUID

JOIN CONTACT_PHONE cp
ON c.contGUID = cp.contactGUID

JOIN PHONES p
ON cp.phnGUID = p.phnGUID

JOIN CONTACT_SOCIAL cs
ON c.contGUID = cs.contactGUID

JOIN SOCIALS s
ON cs.socGUID = s.socGUID

JOIN CONTACT_PHYS_ADDRESS cpa
ON c.contGUID = cpa.contactGUID

JOIN PHYS_ADDRESSES a
ON cpa.addrGUID = a.addrGUID

Question:

How do I return all other contact types (email, social, address) if say, CONTACT_PHONE record is not present for a given contactGUID?


texts: Removing multiple values using if else statements?

texts:
I have a program that downloads raw data from a sensor. Due to to some specifications made in the SQL code, there are values that are duplicated which needs to be deleted to maintain data integrity.

Below is a snapshot of the dataset:

Do note that is is just a snapshot of july 10th. I have 33 other devices all of them have data dates from july 10th all the way through october.

Is there a way for me to traverse the whole dataset and find a way to set conditional statements. For example, if there are 2 values that share the same timing, take the row that has the "1" value.

At the same time, i would like to point out that this dataset is data that is collected 24/7.


texts: How to validate mongodb backup and restore

texts:
I am using mongodump to create a backup of the db based on some query params and mongorestore to restore the same in another mongodb in a diff environment.

How can I validate whether the dump created fetched all the records and also whether the restore of dump to the target db is done properly?


texts: Extreme slow query for a simple indexed selection - Why?

texts:
i have a table in my database that is described as follows:
-- Table: public.devicemeasurement_mqtt

-- DROP TABLE public.devicemeasurement_mqtt;

CREATE TABLE public.devicemeasurement_mqtt
(
    measurement_id integer NOT NULL DEFAULT nextval('devicemeasurement_mqtt_measurement_id_seq'::regclass),
    insert_time timestamp with time zone NOT NULL,
    data jsonb NOT NULL,
    smart_device_id integer NOT NULL,
    CONSTRAINT devicemeasur_smart_device_id_ee500e1e_fk_d FOREIGN KEY (smart_device_id)
        REFERENCES public.device_mqtt (device_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.devicemeasurement_mqtt
    OWNER to postgres;

GRANT ALL ON TABLE public.devicemeasurement_mqtt TO postgres;

-- Index: dev_insert__2df020_idx

-- DROP INDEX public.dev_insert__2df020_idx;

CREATE INDEX dev_insert__2df020_idx
    ON public.devicemeasurement_mqtt USING btree
    (insert_time DESC NULLS FIRST)
    TABLESPACE pg_default;

-- Index: devicemeasurement_mqtt_smart_device_id_ee500e1e

-- DROP INDEX public.devicemeasurement_mqtt_smart_device_id_ee500e1e;

CREATE INDEX devicemeasurement_mqtt_smart_device_id_ee500e1e
    ON public.devicemeasurement_mqtt USING btree
    (smart_device_id ASC NULLS LAST)
    TABLESPACE pg_default;

This table, is continuously used from a service that inserts tons of data every minute (at this time it contains around 3 milions of rows), now, in the current situation, when i have to select one device that has continuous measurements the problem does not exist, but, suppose that i have to select the measurements for a specific device that was in use three months ago and now it's disabled (so, no more new measurements), the execution of a simple and effective query like below is really slow, taking up 40 seconds to complete:
This is the reference query that generates this slow select:
EXPLAIN (BUFFERS,ANALYZE)
SELECT
  devicemeasurement_mqtt.measurement_id,
  devicemeasurement_mqtt.insert_time,
  devicemeasurement_mqtt.data,
  devicemeasurement_mqtt.smart_device_id
FROM
  devicemeasurement_mqtt
WHERE
  devicemeasurement_mqtt.smart_device_id = 4
ORDER BY
  devicemeasurement_mqtt.insert_time DESC
LIMIT 15

and here you can find: the explain and the database configuration
Limit  (cost=0.43..40.04 rows=15 width=264) (actual time=39133.474..39134.059 rows=15 loops=1)
  Buffers: shared hit=22093 read=112101
  ->  Index Scan using dev_insert__2df020_idx on devicemeasurement_mqtt  (cost=0.43..356203.28 rows=134878 width=264) (actual time=39133.471..39134.055 rows=15 loops=1)
        Filter: (smart_device_id = 4)
        Rows Removed by Filter: 1794148
        Buffers: shared hit=22093 read=112101
Planning Time: 0.133 ms
Execution Time: 39134.085 ms

I'll be glad if someone will help me to figure out!


texts: SQL query running slow compared to its earlier execution time

texts:
One of our application using an SQL query is reported running slow.

Current execution time:- 15 minutes and so

Earlier execution time:- 30 seconds.

SQL-Sentry Plan for the slow query

I've checked for Fragmentation and stats if they are up to date. The weekend job for same executed successfully and seems OK for this point.

When I am using SQL-sentry plan explorer to check for the execution plan  it shows me RED for a table going through clustered index seek and Green for Key Lookup.

I am new on how to analyse the plan.

Kindly advice on how to proceed and improve the performance of the query, thanks!


texts: Optimizing WHERE condition for TIMESTAMP field in MySQL SELECT statement

texts:
I am working on a schema for an analytics system which tracks usage times, and there is a need to see total usage time in a certain date range.

To give a simple example, this type of query would be run often:

select sum(diff_ms) from writetest_table where time_on > ("2015-07-13 15:11:56");

This query typically takes around 7 seconds on a table that is heavily populated. It has ~35 million rows, MyISAM on MySQL running on Amazon RDS (db.m3.xlarge).

Getting rid of the WHERE clause makes the query take only 4 seconds, and adding a second clause (time_off > XXX) adds an additional 1.5 seconds, bringing the query time to 8.5 seconds.

Since I know these types of queries will be commonly done I would like to optimize things so they are faster, ideally below 5 seconds.

I started by adding an index on time_on, and though that drastically sped up a WHERE "=" query, it had no effect on the ">" query. Is there a way to create an index that would speed up the WHERE ">" or "<" queries?

Or if there are any other suggestions the performance of this type of query, please let me know.

Note: I am using the "diff_ms" field as a denormalization step (it equals time_off - time_on) which improves performance of the aggregation by around 30%-40%.

I am creating the index with this command:

ALTER TABLE writetest_table ADD INDEX time_on (time_on) USING BTREE;

Running "explain" on the original query (with "time_on >") says time_on is a "possible_key" and the select_type is "SIMPLE". The "extra" column says "Using where", and "type" is "ALL". After the index was added, the table says "time_on" is "MUL" key type, which seems correct since the same time can be present twice.

Here is the table schema:

CREATE TABLE `writetest_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sessionID` int(11) DEFAULT NULL,
  `time_on` timestamp NULL DEFAULT NULL,
  `time_off` timestamp NULL DEFAULT NULL,
  `diff_ms` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time_on` (`time_on`)
) ENGINE=MyISAM AUTO_INCREMENT=50410902 DEFAULT CHARSET=latin1;

UPDATE: I created the following index based on ypercube's response, but this increases the query time for the first query to around 17 seconds!

ALTER TABLE writetest_table  ADD INDEX time_on__diff_ms__ix (time_on, diff_ms) ;

UPDATE 2: EXPLAIN output

mysql> explain select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56';
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
| id | select_type | table               | type  | possible_keys        | key                  | key_len | ref  | rows     | Extra                    |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | writetest_table_old | index | time_on__diff_ms__ix | time_on__diff_ms__ix | 10      | NULL | 35831102 | Using where; Using index |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

Update 3: result of requested query

mysql> SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1;
+---------------------+
| time_on             |
+---------------------+
| 2015-07-13 15:11:56 |
+---------------------+
1 row in set (0.01 sec)


texts:
From this SQL schemas I want to count the number of times a user is in a contest.

SELECT * FROM users u
LEFT JOIN trials_has_users tu ON (tu.users_id = '1')
LEFT JOIN trials AS t ON (t.id = tu.trials_id)
WHERE u.id = '1';

Previously, I have the expected number of lines, but I want to make a count
SELECT contest_total FROM users u
LEFT JOIN trials_has_users tu ON (tu.users_id = '1')
LEFT JOIN (
    SELECT
        id,
        COUNT(*) AS contest_total
    FROM
        trials
    WHERE deleted_at IS NULL
    GROUP BY
        id
) AS t ON (t.id = tu.trials_id)
WHERE u.id = '1';

Previously, have 6 rows want 1 (current user id) need LIMIT 1 ?
SELECT contest_total FROM users u
LEFT JOIN trials_has_users tu ON (tu.users_id = '1')
LEFT JOIN (
    SELECT
        id,
        COUNT(*) AS contest_total
    FROM
        trials
    WHERE deleted_at IS NULL
    GROUP BY
        id
) AS t ON (t.id = tu.trials_id)
WHERE u.id = '1'
LIMIT 1;

I would like to receive the number of contests in which the user has participated. My query is right ?


texts: Will installing WebMatrix (and Consequently SQL Server CE) Break My Current Installation of SQL Server 2005?

texts:
We have a server that has (and needs to keep) SQL Server 2005.

We are hosting intranet sites on one server (server 1), but have a database on another server (server 2). One of these sites uses AJAX to go server-side (server 1) and then queries databases on server 2.

We are trying to cut out the middle man and we want to install WebMatrix (which comes with SQL Server CE) on the same server as the database (server 2), but this server already has SQL Server 2005 installed.

Will a simple WebMatrix install (i.e., SQL Server CE) break the SQL Server 2005 install?

My guess is that it won't, but the damage recovery would be considerable if it does.

I don't think it matters, but server 2 has Windows Server 2003.


texts:
I do not know whether this is a mysql(version 5.6.11) bug, the case is as following. today when i update mysql conf and kill -9 and restart mysqld, i find that it can't restart anymore. after having a look at the error log, i find the following:

2013-07-09 17:04:21 7872 [ERROR] InnoDB: Tried to read 1048576 bytes at offset 2097152. Was only able to read 917504.

then i make a strace to trace which file has the problem. the result is ibdata1.

-rw-rw----. 1 mysql mysql   79691776 7月   9 16:51 ibdata1

well, for performance i change log commit as following.

innodb_flush_log_at_trx_commit = 2

but i do not think this can cause data lost since OS does not reboot during mysql restart.
so can anybody help me out on what's the reason(maybe the kill -9?) that makes data corrupt and how to restore the data, thank you very much.


texts: SQL Server replication - recommendations for stability

texts:
We have two SQL Server 2008R2 Standard servers, and we've set up replication of a single database between them. The replication is about as simple as it can get - it's a transactional replication from server A to server B; effectively a one-way push of data.

It's been running for a couple of years, and when it works it works fine - INSERTs or UPDATEs on server A appear on server B within a few seconds. However, at least 3 or 4 times a year, the replication stops for no apparent reason; sometimes after a reboot (mainly due to Windows Updates), but at other times halfway through the day.

I've spent hours reading documents and trawling through log files, but I still can't determine WHY the replication has stopped. A tracer token normally gets from Publisher to Distributor but no further.

Every time this happens, I set out determined to finally crack why it's failing, only to give up, blow away the Subscriber and start again from scratch. This fixes the problem (after a couple of hours bulk copying etc), but I know it will happen again. I may be being unfair, but it does seem to be a ragbag collection of SQL agent jobs that keep the whole thing hanging together, and it seems very difficult to troubleshoot.

So with due apologies for the subjective nature of this question, but: is SQL replication fit for purpose? Are there best practises that I should be following to maintain the stability of replication? Is it just me, and SQL replication is normally pretty solid?


texts: MySQL order by - one column conditional on another column

texts:
In MySQL  I have a table (simplified for this question)

CREATE TABLE `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `priority` int(11) DEFAULT '1000000',
  `status` enum('new','in_progress','complete') DEFAULT 'new',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8

I want to select from the table ordered by priority but with rows where status is 'complete' to come last.

This works:

SELECT * FROM projects
order by
    case
        when status='complete' then 999999
        when status!='complete' then priority
    end ASC

but changing the 999999 to (select max(priority)+1 from projects) gives unexpected results, half way down the first page of results rows with status=complete appear.

Also this last method probably causes repeated calculation of the same value.

What is the best way to do this?


texts: Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?

texts:
I have a table that has a large number of rows in it.

The primary key (an auto-incrementing integer) is, by default, indexed.

While waiting for a row count to be returned I did an EXPLAIN in another window and the the results were as follows:

mysql> SELECT COUNT(1) FROM `gauge_data`;
+----------+
| COUNT(1) |
+----------+
| 25453476 |
+----------+
1 row in set (2 min 36.20 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM `gauge_data`;
+----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+
| id | select_type | table      | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |
+----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+
|  1 | SIMPLE      | gauge_data | index | NULL          | gauge_data_FI_1 | 5       | NULL | 24596487 | Using index |
+----+-------------+------------+-------+---------------+-----------------+---------+------+----------+-------------+
1 row in set (0.13 sec)

Since the primary key is guaranteed to be unique, can I just take the number of rows from the EXPLAIN and use that as the row count of the table?

BTW, I believe the difference in numbers is due to the fact that more data is continually being added to this table.


texts: What is the best way to reduce the size of ibdata in mysql?

texts:
I have some Production servers whose ibdata files increase in size day by day.

It has already consumed 290GB of space.

The tables in the servers are mostly InnoDB and there are high read and write requests.

The log file size also increasing. There is a huge amount of Data in the Tables.

How can I control the growing size of both?

I am not using innodb_file_per_table.


texts: How to change password of user in physical standby database Oracle 11g?

texts:
Hi I am new to Oracle 11g, I have a physical standby database and a primary database of Oracle 11g on two different servers.

I have changed the passwords of users(schemas)like sys, sysman, system, users,etc. in primary database by using command :

"SQL>alter user username identified by password"

and the passwords have been successfully changed, but when I am trying the same thing with physical standby database , its creating oracle error
ORA-16000: database open for read-only access

And I don't want to change the physical standby database to primary i.e. cannot change it to open read - write mode .

Kindly provide some solution.
Thanks in advance


texts: Get the size of list of documents in mongo db

texts:
I want to get the size of the list of documents in mongodb.

db.getCollection('eventDetails').find({"dateTime" : { $gte : new ISODate("2018-05-01T12:00:00Z") },"dateTime" : { $lte : new ISODate("2018-05-31T23:59:59Z") }})

I want to get the size of the documents returned by the above mentioned query.

I tried the following query

var cursor = db.getCollection('eventDetails').find({"dateTime" : { $gte : new ISODate("2018-05-01T12:00:00Z") },"dateTime" : { $lte : new ISODate("2018-05-31T23:59:59Z") }})
var size = 0;
cursor.forEach(
    function(doc){
        size += Object.bsonsize(doc)
    }
);
print(size)

Each time execute the query, i get different results.
Let me know how to get the size of the documents in mongodb


texts: Use result from table_schema query as  in select * from

texts:
Is it possible in PostgreSQL to use the result of the query below as table name in a SELECT * FROM .

SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'seam_event_%'
ORDER BY table_name DESC
LIMIT limit 1

The current database is using table partitioning. 90% of the time we only search the last two partition tables. Therefor we would like to create a view with the records of the last 2 partition tables.

I tried the following:

SELECT * FROM (SELECT table_name
    FROM information_schema.tables
    WHERE table_name LIKE 'seam_event_%'
    ORDER BY table_name DESC
    LIMIT 1) AS a
UNION
SELECT * FROM (SELECT table_name
    FROM information_schema.tables
    WHERE table_name LIKE 'seam_event_%'
    ORDER BY table_name DESC
    LIMIT 1 OFFSET 1) AS b

Unfortunatly, this is returning:

table_name
______________
seam_event_74
seam_event_75

Bottom line, which is the best way to query the last two partition tables and how to achieve this?

Thank you in advance.

Regards,

Bas

---------- EDIT ----------

I've tried to create a functions that will return the results of the last two child tables. But i keep getting a syntax error.

Function:

DROP FUNCTION select_from_table();

CREATE OR REPLACE FUNCTION select_from_table()
  RETURNS SETOF seam_event AS
$BODY$
DECLARE
    TABLE_VAR1 varchar;
    TABLE_VAR2 varchar;
BEGIN
    select table_name from information_schema.tables where table_name like 'seam_event_%' order by table_name desc limit 1 INTO TABLE_VAR1;
    select table_name from information_schema.tables where table_name like 'seam_event_%' order by table_name desc limit 1 offset 1 INTO TABLE_VAR2;

    RETURN QUERY EXECUTE 'SELECT * FROM ' || TABLE_VAR1;
END;
$BODY$
  LANGUAGE plpgsql
  COST 100;

SELECT * FROM select_from_table();

ERROR:

ERROR:  syntax error at or near "'SELECT * FROM '"
LINE 1:  EXECUTE 'SELECT * FROM ' ||  $1
                 ^
QUERY:   EXECUTE 'SELECT * FROM ' ||  $1
CONTEXT:  SQL statement in PL/PgSQL function "select_from_table" near line 8

********** Error **********

ERROR: syntax error at or near "'SELECT * FROM '"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "select_from_table" near line 8


texts: How to make CREATE INDEX in InnoDB table faster

texts:
I have platform RHEL 5 with 8G RAM and MySQL version 14.12 distrib 5.0.86. I want to create an index in an InnoDB table, but it takes a long time:

Time | State             | Info
2291 | copy to tmp table | create index index_test

The table has 8,000,000 rows and in /etc/my.cnf

innodb_buffer_pool_size = 4G

but it is still too slow.

How can I make the create index in innodb table faster?

I already have these variables:

read_buffer_size = 2M
sort_buffer_size = 8M
tmp_table_size   = 64M

SHOW CREATE user_t\G

CREATE TABLE `user_t` (
  `t_id` mediumint(8) unsigned NOT NULL auto_increment,
  `user_id` varchar(50) NOT NULL default '',
  `event_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `user_A` double(11,2) NOT NULL,
  `txt1` varchar(150) default NULL,
  `txt2` varchar(250) default NULL,
  `txt3` varchar(250) default NULL,
  `txt4` varchar(250) default NULL,
  `txt5` varchar(250) default NULL,
  `inv_id` varchar(100) NOT NULL default '',
  `display_text` varchar(250) default NULL,
  `ip` varchar(15) default NULL,
  `testID` varchar(150) NOT NULL default '',
  `bad_t` enum('0','1') default '0',
  `t_id_m` mediumint(11) unsigned default NULL,
  `status` enum('open','pending','processed') NOT NULL default 'processed',
  `b` double(11,2) default NULL,
  `system_id` varchar(50) default NULL,
  `isfixed` int(1) default '0',
  `tmptxt1` varchar(150) default NULL,
  PRIMARY KEY  (`t_id`),
  KEY `undex1` (`testID`,`event_time`,`txt1`),
  KEY `index2` (`t_id_m`)
) ENGINE=InnoDB AUTO_INCREMENT=10260505 DEFAULT CHARSET=utf8

and the following the create index command that take a long time:

create index index_test on user_t (event_time);


texts: any documentation on tpcc-mysql benchmark tool

texts: Where can I find tpcc-mysql documentation? I google it but I can't find any documentation about it.


texts: Selecting 3MB (or more/less) data from database server

texts:
I installed PostgreSQL Server on my Windows 7 machine. I created database and a simple table, and populated the table. Now I want to connect through the network with my Postgres server and select a given amount of data. I'm not interested of the content of the data, in particular, I can even do this:

select * from table;

but I'm interested in the size of the selected data. Something like this (pseudocode):

select * from table while downloaded_data_size < 3MB

Is that possible? I'm not interested in the content of the data, I just want to select / insert some data, no matter what's inside it, the point is, the selected / inserted data must have a given size.


texts: Count Rows that exist before and not after and after but not before

texts:
I have an Azure SQL Database, one Table that has 3 Relevant Columns: Brand, ID and Month/Year and a Temporary Table that simply lists the Month/Year with an ID.
The table shows a list of all IDs that were active for that given month.
Month/Year is a datestamp column, with the date as the first of the Month. The ID Column is just an ID.
The goal is to have an output that for each month will show the count of all the records that were active in this month, but not active in the next month (Churn) and IDs that weren't active in the previous month but where active in this month (New) and have these grouped by brand and Month so:
Brand | Churn | New | Month
1 | 10 | 12 | 2019-12-01
2 | 11 | 9 | 2019-12-01
I should also note that Table1 has 19 million rows.
I tried the below code:
Declare @Id int
declare @my date

set @Id = 300
set @my = '2019-12-01'

select t1.[brand],
count(c.[ID]) as [churn],
count(n.[ID]) as [new],
t1.[month/year]
from Table1 T1

left join Table1 c on
c.[ID] = t1.[ID] and c.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id +1)))
left join Table1 n on
n.[ID] = T1.[ID] and n.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id -1)))

where t1.[Month/Year] = @my
group by t1.[Brand], t1.[Month/Year]

Which although it produced an output, I don't think it was right and it took ages, probably due to my liberal use of Sub-Queries in Joins.
My question is 2-fold - can someone assist in a better way to count the number of rows and join them and is there a better way to do this without so many sub-queries?


texts: Which is more efficient: contradict a unique constraint or check if row exists?

texts:
If I'm regularly going to to attempt to add a duplicate row in a table and duplicates aren't allowed, should I attempt the insert and catch an exception or manually check for the row's existence and only try the insert if the row isn't found?

In both cases, the unique constraint exists.


texts:
Having a product_stock table with following structure:

product_id
warehouse_id
stock
price

Which has bunch of records (same product can be in different warehouses):

1, 1, 0, 500
1, 2, 5, 505
1, 3, 7, 508
2, 1, 0, 400
2, 2, 0, 404

Now, for every product_id, I want to select cheapest one in stock and if product is not in stock anymore, select price and warehouse as NULL - basically, the result should be:

1, 2, 5, 505
2, NULL, 0, NULL

Here's a sqlfiddle.

[Update]: Almost nailed it (now need to figure out how to select appropriate warehouse):

select product_id, min(price) from (
    select product_id,
        CASE WHEN stock = 0 then NULL else warehouse_id end,
        CASE WHEN stock = 0 then NULL else price end from stock
) AS f group by product_id;

Returning (still need to figure out the warehouse_id):

product_id  min
1   505
2   (null)

[Update 2]: I was able to get the warehouse_id, but this query kills the row without price:

SELECT stock.product_id, stock.warehouse_id, stock.price FROM (
    SELECT product_id, min(price) as price FROM (
        SELECT product_id,
          CASE WHEN stock = 0 then NULL else warehouse_id end,
          CASE WHEN stock = 0 then NULL else price end
        FROM stock
    ) AS f GROUP by product_id
) AS ff JOIN
stock on stock.product_id=ff.product_id and stock.price = ff.price;

Result:

product_id  warehouse_id    price
1   2   505


texts: Condition in WHERE vs condition in CONNECT BY

texts:
Can anyone explain to me the difference between the following two queries? Even though they seem the same the results are different.

select concept.concept_id, concept.PARENT_ID
from ebti_thes_concept_v concept
  start with PARENT_ID = '11025'
  connect by parent_id = prior concept_id
  and exists (..)

In the second one, the exists predicate is moved from the connect by clause to the where clause.

select concept.concept_id, concept.PARENT_ID
from ebti_thes_concept_v concept
where exists (..)
 start with PARENT_ID = '11025'
 connect by parent_id = prior concept_id;


texts: How do I find the leafs using PostgreSQL's ltree?

texts:
I have a tree structure based on accounting purposes like

300
300.01
300.02
300.02.01
300.03
.....

I want to assign is_leaf_level column to true for only leaf levels. kod is the accounting number having type ltree. Here is the query:

UPDATE dim_accounting_codes d1
SET is_leaf_level = (
  SELECT count(*)=0
  FROM dim_accounting_codes d2
  WHERE d1.kurum_hesap_plani_id = d2.kurum_hesap_plani_id
    AND d2.kod ~ lquery_in(
      (ltree2text(d1.kod) || '.*{1,}')::cstring
    )
  )

The query plan is as follows:

     Update on dim_kurum_hesap_kodu d1  (cost=0.00..8529064185.14 rows=1468583 width=322)
  ->  Seq Scan on dim_kurum_hesap_kodu d1  (cost=0.00..8529064185.14 rows=1468583 width=322)
        SubPlan 1
          ->  Aggregate  (cost=5807.56..5807.57 rows=1 width=1)
                ->  Bitmap Heap Scan on dim_kurum_hesap_kodu d2  (cost=347.44..5807.53 rows=9 width=0)
                      Recheck Cond: (kod ~ lquery_in(((ltree2text(d1.kod) || '.*{1,}'::text))::cstring))
                      Filter: (d1.kurum_hesap_plani_id = kurum_hesap_plani_id)
                      ->  Bitmap Index Scan on idx_kod_gist  (cost=0.00..347.44 rows=1469 width=0)
                            Index Cond: (kod ~ lquery_in(((ltree2text(d1.kod) || '.*{1,}'::text))::cstring))

Query is running too slowly. (about 40 minutes passed and still working) There are approximately 2000000 records in the table. The index on kod is not used in the plan. Is there any advices on how to mark the leaf level records on ltree structure?

Updated:

result of \d dim_accounting_codes

                                                          Table "public.dim_accounting_codes"
            Column             |              Type              |                                       Modifiers
-------------------------------+--------------------------------+----------------------------------------------------------------------------------------
 dim_kurum_hesap_kodu_id       | integer                        | not null default nextval('dim_kurum_hesap_kodu_dim_kurum_hesap_kodu_id_seq'::regclass)
 version                       | integer                        |
 kurum_hesap_kodu_id           | integer                        |
 kurum_hesap_kodu_adi          | character varying(256)         |
 hesap_tipi                    | character varying(30)          |
 kurum_hesap_plani_id          | integer                        |
 kurum_hesap_plani_ad          | character varying(256)         |
 pasiflestirme_tarihi          | timestamp(0) without time zone |
 aktiflestirme_tarihi          | timestamp(0) without time zone |
 kod                           | ltree                          |
 hesap_kodu_durum              | character varying(8)           |
 ana_hesap_kodu                | character varying(10)          |
 yardimci_ekonomik_kod_i       | character varying(10)          |
 yardimci_ekonomik_kod_ii      | character varying(10)          |
 yardimci_ekonomik_kod_iii     | character varying(10)          |
 yardimci_ekonomik_kod_iv      | character varying(10)          |
 ana_hesap_kodu_adi            | character varying(500)         |
 yardimci_ekonomik_kod_i_adi   | character varying(500)         |
 yardimci_ekonomik_kod_ii_adi  | character varying(500)         |
 yardimci_ekonomik_kod_iii_adi | character varying(500)         |
 yardimci_ekonomik_kod_iv_adi  | character varying(500)         |
 yardimci_ekonomik_kod_level   | smallint                       |
 is_leaf_level              | boolean                        | default false
Indexes:
    "idx_dim_kurum_hesap_kodu_lookup" btree (kurum_hesap_kodu_id)
    "idx_dim_kurum_hesap_kodu_tk" btree (dim_kurum_hesap_kodu_id)
    "idx_kod_gist" gist (kod)


texts: SQL GroupBy Select min return multiple columns

texts:
I have the following sample of a SQL table, which will be filled with around 400 million rows:

I want to be able to return columns Id, UID, STP, A, B, C and then group by the UID and keep the one with the lowest STP values, where a date is in between the StartDate and EndDate (i.e. 23/05/2011)
This is what I have so far:
SELECT Id, UID, STP, A, B, C
FROM [drp-data].[dbo].[schedules]
WHERE StartDate <= CONVERT(date, '2011-05-23') AND EndDate >= CONVERT(date, '2011-05-23')

Microsoft SQL 2019.


texts: Postgresql: Storing a table index on a separate disk to the data

texts:
I have development a postgresql 9.5 database which I originally set up on an SSD. However, as this project will grow to ~200TB (and likely more), I have moved my current test of ~200GB on to a HDD, which will better represent the hardware that will likely be used in the real database, when it is built.

The main tables that are queried are currently 110 million and 785 million rows, and almost all the time is spent on table index scans. Therefore, I would like to know if it is possible (and if so how) to store all the table indexes on an SSD, and the actual raw data on the HDD? I believe this would allow me to keep the improved read performance of the SSD on the index scans, and be able to keep the large volume of data on cheaper HDDs.


texts: Is it good idea to invoke Web Service from the database function or trigger?

texts:
I am using PostgreSQL in my Web API project. I have created Web APIs using .net core. Now I have a situation, where I  want to process the inserted or updated data and store the processed data in some other table.
I was thinking to create a trigger for the table that will invoke the REST API created in .net core along with the data and in .net core, I can easily process the data and will insert it into the table I want. The API will be OneWay web service - i.e. fire and forget.
But I was wondering, whether it is a good idea to invoke web service from the database? If it is, then how could I invoke a OneWay Web service from Postgres trigger?
Another approach I found was to use LISTEN/NOTIFY. For this approach, https://shashangka.com/2020/05/17/listen-postgresql-in-asp-net-core/ article seems fine.
I was wondering, which of the approach will be better for my use case and why?


texts: failed to deploy a very simple Production InnoDB Cluster based MySQL5.7

texts:
If a question has not been answered for a long time, is there any other good way besides deleting the original question and asking it again?
I am learning how to build a MySQL InnoDB cluster based on this document. When executing the step of adding other instance to the cluster, I encountered the following error on mysql-shell which is connected to seed instace:

Adding instance to the cluster...
ERROR: Unable to start Group Replication for instance 'VM-4-3-debian:3306'. Please check the MySQL server error log for more information.
Cluster.addInstance: Group Replication failed to start: MySQL Error 3096 (HY000): VM-4-3-debian:3306: The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer. (RuntimeError

and the MySQL server error log logged following content:

2020-12-12T14:20:15.259407Z 20 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2020-12-12T14:20:15.309498Z 20 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "REQUIRED"; server_key_file: "server-key.pem"; server_cert_file: "server-cert.pem"; client_key_file: "server-key.pem"; client_cert_file: "server-cert.pem"; ca_file: "ca.pem"; ca_path: ""; cipher: ""; tls_version: "TLSv1,TLSv1.1,TLSv1.2"; crl_file: ""; crl_path: ""'
2020-12-12T14:20:15.309596Z 20 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 10.0.4.3/22,127.0.0.1/8 to the whitelist'
2020-12-12T14:20:15.393221Z 20 [Warning] Plugin group_replication reported: '[GCS] Peer address "VM-133-145-debian:33061" is not valid.'
2020-12-12T14:20:15.393235Z 20 [ERROR] Plugin group_replication reported: '[GCS] None of the provided peer address is valid.'
2020-12-12T14:20:15.393245Z 20 [ERROR] Plugin group_replication reported: 'Unable to initialize the group communication engine'
2020-12-12T14:20:15.393251Z 20 [ERROR] Plugin group_replication reported: 'Error on group communication engine initialization'
2020-12-12T14:20:15.393255Z 20 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2020-12-12T14:20:15.393258Z 20 [ERROR] Plugin group_replication reported: 'Error calling group communication interfaces while trying to leave the group'

Before performing adding a new instance to a cluster, I used dba.configureInstance() on the two instances. At that time, the execution results reported that some configuration needs to be modified. I agreed to the adjustment and restarted the two MySQL instances after the adjustment. Then execute dba.configureInstance() and it has reported OK. But I now (after failing to add an instance) execute dba.configureInstance() on this instance that I tried to add to the cluster but could not be added, it reported following content:

ERROR: The MySQL instance at 'VM-4-3-debian:3306' currently has the super_read_only system variable set to protect it from inadvertent updates from applications.
You must first unset it to be able to perform any changes to this instance.
For more information see: https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_super_read_only.
Dba.configureInstance: Server in SUPER_READ_ONLY mode (RuntimeError)

how to solve the problem? Thanks a lot.