texts: Advantage and Disadvantage of Partitioning by some of values of Primary Key

texts:
I'm going to implement/deploy a table like this:

CREATE TABLE docref
(
    namespace  VARCHAR(50),
    key        VARCHAR(50),
    time_order INT,

    state      SMALLINT,
    hash       TEXT,
    uri        TEXT,
    created_at TIMESTAMP,

    PRIMARY KEY (namespace, key, time_order)
);

Almost all my queries use primary key to access / group by / sort data:

WHERE namspace = ? and created_at > ?
WHERE namespace = ? and key = ?
WHERE namespace = ? ... GRUOP BY namespace, key ... ORDER BY namespace, key

I'm thinking to partition my table by namespace because:

each namespace will containt a lot of data points (unpredictable and variable: 10k ~ 100m)
all data points in each namespace are isolated and indepdendent from other partitions, which makes it a good choise
I can prune partitions based on unwanted namespaces, and archive them easily

But I'm concerned that:

the number of namespaces is not limited, > 5000
the availabliltiy of each partition (namespace) is not limited; they can being used all the time for 2 or 3 years

Obviously it means that I'll end up with lots of tables which worries me.

Because I'm not experienced with Postgres, I'm looking to assess my reasining and concerns: Should I worry about the number of partitions and lifetime of each parition ? Are my concerns valid? In other word:

Is it costly for Postgres to keep lots of (partitioned) tables avaiable?
What about query planner?


texts:
How can a subscriber of one publication also be a publisher of another publication in SQL?
We have a replication on our server which is not initializing because of the following error: "Cannot drop the table [] because it is being used for replication."
We found out that the client has set the subscriber on their server as a publisher for another replication.
What is the solution to avoid this error?
All databases involved are SQL Server databases.


texts: re-enabling a table for mysql replication

texts:
We were able to setup mysql master-slave replication with the following version on both master/slave:

mysqld  Ver 5.5.28-29.1-log for Linux on x86_64 (Percona Server (GPL), Release 29.1)

One day, we noticed that replication has stopped, we tried skipping over the entries that caused the replication errors. The errors persisted so we decided to skip replication for the 4 problematic tables. The slave has now caught up with the master except for the 4 tables.

What is the best way to enable replication again for the 4 tables?

This is what I have in mind but I don't know if it will work:

1) Modify slave config to enable replication again for the 4 tables

2) stop slave replication

3) for each of the 4 tables, use pt-table-sync --execute --verbose --print --sync-to-master h=localhost,D=mydb,t=mytable

4) restart slave database to reload replication configuration

5) start slave replication


texts:
I'm looking at adding addresses to an existing database, and am trying to work out the advantages and disadvantages of the following methods (and if there are any I've overlooked).

Store the address inline with the source record
Have an address table and add a foreign key to the entity
Have an address table with a foreign key that links to the entity

I'd like to steer clear from storing it inline for normalisation reasons, but I'm unsure as to which of the other options to go for. It's possible at some point down the line that we want additional entity types to have addresses on them, which makes option 2 look like the best one. This option (as far as I can see) has the potential to leave unlinked data around though.


texts: Why InnoDB storage engine not loading csv files concurrently?

texts:
While loading different csv files to different tables of the same database using innodb storage engine(even tested on aria), csv files seems not to be loaded concurrently. Because, a single csv loading to a table is taking x minutes, so i am assuming that loading 3 different csv files to 3 different table should take almost the same(if they are being loaded concurrently), rather it takes 3x minutes in total. Is there any configuration metrics that I am missing to enable concurrent loading to different tables or is it not supported?

NB: I am loading 3 csv into 3 different tables from 3 different connection to mariadb.


texts: Do WHERE IN queries reindex the table after each set within the query?

texts:
I have a basic WHERE IN query that takes too long (30+ secs) to process.

DELETE FROM `table1`
WHERE ( djID, localID )
IN (("128","26"),("110","32"))

The table I'm querying on has about 20 million rows, with two indices - the first is the auto-incremented PK named ID, the second index is over two columns - djID, localID

I'm wondering if MySQL re-indexes the table after each ( djID, localID ) in the query, therefore accounting for the long query time, and if so, is there a way to force it to wait until all the ( djID, localID ) sets have been executed in the query before re-indexing?

Or is there another reason jumping out that may cause the long query time?


texts: Encounter installation problems on postgres 12

texts:
i install postgres-12,but report this error,i have execute sudo systemctl restart postgresql-12 ,and follow this install postgres12 on centos 7, and when i step into Step 5: Enable remote access,and execute sudo systemctl restart postgresql-12 it report error
[root@localhost ~]# systemctl status postgresql-12
● postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Sun 2020-10-25 12:29:05 CST; 11min ago
     Docs: https://www.postgresql.org/docs/12/static/
  Process: 7462 ExecStart=/usr/pgsql-12/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
  Process: 7456 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 7462 (code=exited, status=1/FAILURE)

Oct 25 12:29:05 localhost.localdomain systemd[1]: Starting PostgreSQL 12 database server...
Oct 25 12:29:05 localhost.localdomain systemd[1]: postgresql-12.service: main process exited, code=exited, status...LURE
Oct 25 12:29:05 localhost.localdomain systemd[1]: Failed to start PostgreSQL 12 database server.
Oct 25 12:29:05 localhost.localdomain systemd[1]: Unit postgresql-12.service entered failed state.
Oct 25 12:29:05 localhost.localdomain systemd[1]: postgresql-12.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

[root@localhost ~]# journalctl -xe
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-12.service has begun starting up.
Oct 25 12:56:02 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.994 CST [7586] LOG:  starting PostgreSQL 12.4 on x86_64-pc-linux-gnu, compile
Oct 25 12:56:02 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] LOG:  could not bind IPv4 address "172.18.56.1": Cannot assign
Oct 25 12:56:02 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] HINT:  Is another postmaster already running on port 5432? If
Oct 25 12:56:03 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] WARNING:  could not create listen socket for "172.18.56.1"
Oct 25 12:56:03 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] FATAL:  could not create any TCP/IP sockets
Oct 25 12:56:03 localhost.localdomain postmaster[7586]: 2020-10-25 12:56:02.995 CST [7586] LOG:  database system is shut down
Oct 25 12:56:02 localhost.localdomain systemd[1]: postgresql-12.service: main process exited, code=exited, status=1/FAILURE
Oct 25 12:56:03 localhost.localdomain systemd[1]: Failed to start PostgreSQL 12 database server.
-- Subject: Unit postgresql-12.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-12.service has failed.
--
-- The result is failed.
Oct 25 12:56:03 localhost.localdomain systemd[1]: Unit postgresql-12.service entered failed state.
Oct 25 12:56:03 localhost.localdomain systemd[1]: postgresql-12.service failed.
Oct 25 12:56:03 localhost.localdomain sudo[7572]: pam_unix(sudo:session): session closed for user root
Oct 25 12:56:03 localhost.localdomain polkitd[3564]: Unregistered Authentication Agent for unix-process:7574:595409 (system bus name :1.50, object path /
lines 2127-2149/2149 (END)


texts: How do I update last row of a group in MySQL

texts:
I have a table like this:

+----------------------------+
| user  | date     | current |
+----------------------------+
|    1  | 20010101 |       1 |
|    1  | 20020202 |       1 |
|    1  | 20030303 |       1 |
|    2  | 20010101 |       1 |
|    2  | 20020202 |       1 |
+----------------------------+

I would like to update the current column such that only each user's most recent entry has a 1.

I had planned:

UPDATE table t1 SET current = 0
WHERE EXISTS (
    SELECT id
      FROM table t2
     WHERE t2.user = t1.user
       AND t2.date > t1.date )
)

But this tells me ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

Is there a way around this limitation of MySQL?


texts: Unable to deploy SSIS package to remote sql server

texts:
I am a newbie to SSIS.
I have created Azure VM and installed SQL Server. I need to deploy the ssis package from visual studio in my local to SQL server on Azure VM. Using below command I can login to VM SQL server with windows authentication from SSMS in my local.

runas /netonly /user:domain\user "C:\Program Files (x86)\Microsoft SQL
Server\140\Tools\Binn\ManagementStudio\Ssms.exe"

Now, I'm trying to deploy package from Visual studio to VM SQL Server, In the deployment wizard it asks for destination server where not able to login to VM sql server using windows authentication, it throws below error:

And when using SQL server authentication in deployment wizard for destination server it throws below error:

How can I deploy packages to Azure VM SQL Server from local ?


texts: index used when query process on oracle database

texts:
I a new one using and learn about oracle database.
currently i have sql query like this
select attend_code from attadanstatusdetail
where attend_id = 'attendidcode001'
and company_id = 168

currently i have 2 schema on one instance or database with same index. From explain results
schema1 using index range scan which is this is good for me.
but at schema2 using index fast full scan.
my question

whats going on at schema2, why schema2 not using index range scan ?
after I running exec dbms_stats.gather_schema_stats('schema12','indexname') on schema2 i got same results from explain this is index range scan ?

can explain more to me about thats
thanks


texts: how can I extract values from one cell to make it multiple rows


texts:
I'm having a problem with long elapse times for queries in Azure SQL PaaS, running on the Standard Tier.  We have some small External tables as a vertical shard to the database, and shared across a number of other databases. I was able to find this detailed breakdown of wait statistics:

What is Global Query Consumer, and what can I do to minimize it?


texts:
I have a Production server

On November 5,2011 the ibdata size was 100G.
In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G.

I Have approximately all tables with Innodb.I am not using innodb per table.
I have my idbata on lvm.So How much i should increase it for future use..?

Or any other Best way to Handle the Scenario.


texts:
I have 4 tables that has the same columns. Tier1, Tier2, Tier3, Tier4. Their columns are id, code, and name.
The complete relationship is like this: Tier1->Tier2->Tier3->Tier4 Where Tier1 has many Tier2 and so on. But on some cases there are some tiers that get skipped. Like Tier1->Tier3->Tier4 so there is no Tier2, instead from Tier1 it got directly at Tier3.
My question is, how can I make a schema for this condition? I don't know how to start searching because I don't know the keyword for this problem.


texts: How do i Optimize/Improve ram while running a massive import into sql server

texts:
I currently am importing a massive database of around 180 million records with roughly 300+ columns. I am sure you are all aware this takes a very long time. I am using sql server on a windows server 2012 machine. The first upload failed at about 38 million. I am on my second try and have been running my server for days with no break. So my question is, even though I am running this import currently, am I able to do anything to reduce the physical memory that is being used?
My system has 24gb of ram and it currently is using 22.5 of that (93-94%). I don't want this upload to fail. The system is a 4 core processor. What can I do?


texts: Foreign key massacre: How many tables, Winston?

texts:
I'm designing a feature related to defining insurance policies.

There's table of Provisions that have names and IDs. Anything called an "ID" here is a surrogate key in a table.

We then have Forms, FormBenefits, and Riders. Those are very different kinds of things, each in its own table, but each can have selected provisions. All the provisions are defined in the same table and each one may be selected for any Form/FormBenefit/Rider, at the discretion of the insurance industry lifers who use the tool I'm working on.

When a provision is associated with a Form/FormBenefit/Rider, the user selects a value for it. The value is either an arbitrary float, or or a selection from a set of Value Options defined for that provision in another table. The provision's definition determines which of those it gets.

For example, the provision "Policy Maximum Type" has four available value options ("Dollars", "Days", "Time Elapsed", "Weeks"). A given Form has been given the "Policy Maximum Type" provision, and the user selected the "Dollars" value for that provision on that particular form.

So we have a Form table, a Benefit table, and a Rider table. Those each have an ID, a name, and one or two other things.

My question is a) how do I associate provisions with those three kinds of "owners", and b) how do I associate the two kinds of values with the selected provisions?

I'm thinking of creating these two tables for the selection of provisions and their values:

SelectedProvision
    SelectedProvisionID     primary key not null
    -- Foreign key to Provision table.
    ProvisionID             foreign key not null

    -- Exactly one of these three is not null
    FormID                  foreign key null
    FormBenefitID           foreign key null
    RiderID                 foreign key null

SelectedProvisionValue
    SelectedProvisionID     foreign key not null

    -- Exactly one of these two is not null
    ValueOptionID           foreign key null
    NumericValue            float null

I'm asking about the wisdom of the "exactly one of these X columns is not null" concept. Also, would it be good, bad, or indifferent to put NumericValue in the SelectedProvision table?

My other thought was to have one table to associate selected provisions with Forms, another for Form Benefit provisions, and another for Riders. It seems to me that providing values for them would become complicated.

Are there sensible alternatives to those two options?

Bonus question: Should SelectedProvisionValue have a surrogate key?

I've excluded the parts that seem to me to be irrelevant. I'm happy to go into more detail about the rest of the tables if needed.


texts: Can 'clustering' and 'alwayson failover clustering' be used interchangeably?

texts:
I am reading this summary from the book 'Pro SQL Server 2019 Administration' from Carter.
In 'hot', when citing the examples the author writes 'Clustering'.

But when each technology is explained it is listed as 'AlwaysOn failover clustering'... My question is about naming: can the terms 'clustering' and 'alwayson failover clustering' be used interchangeably? or do they  mean different things?


texts:
I inherited a database that has multiple tables that contain some form of an object ID, a date, and a value. The metrics depend on comparing the current value on any given date to the previous value (and the values do not occur every day). I am looking for a way to quickly fill in a "previous value" field such that for any given record, previous value contains the value from that table where the object ID is the same and the date is the maximum date that occurs before the target row.

Note: I am using Oracle.

Example: {object, date, value}

1,2014-04-21,12
2,2014-03-01,38
1,2014-02-28,22
1,2014-01-01,28

In this case, the first row should have prev_value set to 22 (third row) because the third row matches on object ID and has the maximum date that occurs before the first row's date.

POSSIBLE SOLUTION:

I am using lag, but it is a bit of a nasty query. I select object_id and lag date over partition on object_id ordered by date. Then, I merge that back into the table to fill the previous date. It takes a very long time, but it appears to work.


texts: Questions on database table and index partitioning in SQL Server

texts:
I have developed Transact-SQL script to partition the table. To apply the partition, I am creating one non-clustered index for my partition key column and associating it to partition scheme. I have few questions:

My table could have multiple indexes, and when we create index, duplicate data is created. When I partition using a non-clustered index on only one column (i.e. Partition Key Column), will other index data be partitioned too?
How is the partitioning applied in the case of multiple indexes?
Do we need to partition table data and indexes separately?
What are the best practices for partitioning a table in SQL Server?


texts: SQL Server Integration Services Catalog - Package Errors

texts:
Just getting started with the catalog. Have a package that runs fine in Visual Studio. Fails every time in SSMS (after project is deployed to the SSISDB).

Here are the errors I get each time:

Even though it is not explicit with these messages, we are thinking it is something permissions related. Have any of you ran into similar errors/situation?


texts: How would you normalize a database that has multiple products to a single transaction ID?

texts: How would you normalize this? I can't find a way to keep multiple products under a single SalesID


texts: Is having a 92MB MDF with a 58MB LDF cobo ok? How do I manage the log to keep things running well?

texts:
I have jobs in place to trim tables used for history and logging so to keep them trim. I want to make sure I don't neglect the log file.

How and what can I or should I do to keep the log file in check?

The sql script I have running on a nightly schedule is:

declare @DBname varchar(500)
set @DBname = 'E:\Database\backup\PMIS_backup_'+ convert(varchar(MAX), getdate(), 23 ) +'.bak'

BACKUP DATABASE [PMIS] TO  DISK = @DBname
WITH NOFORMAT, NOINIT,  NAME = @DBname
, SKIP, REWIND, NOUNLOAD,  STATS = 10

(recovery mode is simple)


texts: Execute a query which returns uniformly at maximum 100 records

texts:
I have a table in which every record has an ID, a date and a value.
There are a lot of records, and I want to get those dates and values, to create a graph. But I do not want to have more than 100 points.

What I need to do, is if for example there are 300 records, one for every day, to execute a query which will take 100 records, but uniformly. I mean if I have for example

23, 1 January 2014, 5
34, 2 January 2014, 54
45, 3 January 2014, 35
56, 4 January 2014, 55
67, 5 January 2014, 35
78, 6 January 2014, 56
89, 7 January 2014, 53
90, 8 January 2014, 55
94, 9 January 2014, 57

For example, If I wanted 3 results maximum, my query should return for example the record for 1 January 2014, 4 January 2014 and 7 January 2014. How is it possible to do that? I cannot think anything...


texts: Execute Stored Procedure that cannot be manually stopped

texts:
I am looking for a solution to:

Allow an admin to manually run a stored procedure
Once the stored procedure runs the same admin cannot stop it using the "Cancel Executing Query"
The stored procedure must be invoked from SSMS ONLY.
No other external program or language except for MS Management Studio and TSQL can be used.

I have tried creating a stored procedure than invokes another stored procedure, but it did not work. Any ideas are welcome....


texts: What does normalization say about storing data from objects that use a decorator pattern?

texts:
I am just curious as to what the best practices are for storing the data from objects built using decorator design.  I have a base class and can decorate it with A, B, and/or C, with A B and C having distinctly different properties.  I have read some things and watched some videos on normalization, but am unsure how to apply it to this situation.

The only solutions I can think are:
Having 1 table for the entire thing with null-able properties for all the decorator properties.

Having 4 tables (Base, A, B, C), but put booleans in the base class to keep track of whether or not it has a certain decoration.

Thanks!


texts: I have 1 live server and 1 replication server. For reporting purpose we use the replication server and on the replication server we have created an index. So I want to know if I rebuild my index on replication server or do I have to remove the replication server first and then rebuild the index or can I directly rebuild the indexes on replication server?


texts: Does changing `wal_keep_segments` require a restart?

texts: I have a need to increase wal_keep_segments on our master server. Can I do that on the fly or does it require a restart?


texts: mariabackup of database running in docker

texts:
I have MariaDB running in a Docker container with the container's data directory /var/lib/mysql/ mapped to the host directory /mnt/data/.

I want to run mariabackup from the host to create a physical backup of the data directory (which is /mnt/data as far as the host is concerned and /var/lib/mysql/ from the container's perspective).

I get an error, however, running mariabackup that says it cannot find the data directory:

$ mariabackup --backup --user=backup --stream=xbstream -h /mnt/data/ | gzip > foo.gz
Connecting to MySQL server host: 127.0.0.1, user: backup, password: set, port: 3306 ...
Using server version 10.3.10-MariaDB-1:10.3.10+maria~bionic
Warning: MySQL variable 'datadir' points to nonexistent directory '/var/lib/mysql/'
Warning: option 'datadir' has different balues:
    '/mnt/data/' in defaults file
    '/var/lib/mysql/' in SHOW VARIABLES
mariabackup based on MariaDB server 10.1.29-MariaDB debian-linux-gnu (x86_54)
mariabackup: uses posix_fadvise().
mariabackup: Can't change dir to '/var/lib/mysql/ (Errcode: 2 "No such file or directory")
mariabackup: cannot my_setwd /var/lib/mysql/

Something similar happens when I don't specify the data directory with the -h flag. I expected mariabackup to cd to /mnt/data/, execute the commands it needs to run on the server, and everything to simply work because it doesn't seem like it should matter what SHOW VARIABLES says if there's a valid data directory at /mnt/data/.

I understand the warning that mariabackup sees two different paths for datadir, one that I provided via -h and another on the server.
Why, however, does mariabackup try using /var/lib/mysql/ when I explicity said (via the -h flag) that the data directory is /mnt/data/? Can I get mariabackup to ignore what the server says and cd to the directory I provided?
If my approach is all wrong, what's the best way to do physical backups of a server running in a container with data directory mapped to a host directory?


texts:
I'm trying to replace a column with a lookup from another table.  Seems simple, but I'm thinking PostgreSQL is getting in the way for some reason.  I've seen that some syntax that evidently doesn't work in PostgreSQL

UPDATE A
SET userid = (
SELECT pid FROM B WHERE B.pid = A.userid
)   # => You will need to rewrite or cast the expression (pointing to "(SELECT"

I think this SQL is valid, and I don't know what I am missing.  How can I get this column to do a simple lookup?


texts: MySql takes too long with matching index

texts:
I have the following Table with 774.462 rows.

Its running on mysql server 5.6.16, which has nothing other to do (running local)

But a simple query is running slow:

SELECT serial FROM `event` WHERE id=5586929 AND minute=71;
/* Affected rows: 0  Gefundene Zeilen: 1  Warnungen: 0  Dauer von 1 query: 0,422 sec. */

The explain seems to use the index

Why does this Query which perfecly matches my index take 400ms?

CREATE TABLE `event` (
    `serial` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `minute` BIGINT(20) NULL DEFAULT NULL,
    `running` TINYINT(1) NULL DEFAULT NULL,
    `home` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `away` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `starttime` BIGINT(20) NULL DEFAULT NULL,
    `period` INT(11) NULL DEFAULT NULL,
    `timeReference` BIGINT(20) NULL DEFAULT NULL,
    `timeOffset` BIGINT(20) NULL DEFAULT NULL,
    `goalsHome` INT(11) NULL DEFAULT NULL,
    `goalsAway` INT(11) NULL DEFAULT NULL,
    `twH` DOUBLE NULL DEFAULT NULL,
    `twX` DOUBLE NULL DEFAULT NULL,
    `twA` DOUBLE NULL DEFAULT NULL,
    `region` INT(11) NULL DEFAULT NULL,
    `redCardsTeam1` INT(11) NULL DEFAULT NULL,
    `redCardsTeam2` INT(11) NULL DEFAULT NULL,
    `yellowCardsTeam1` INT(11) NULL DEFAULT NULL,
    `yellowCardsTeam2` INT(11) NULL DEFAULT NULL,
    `offsidesTeam1` INT(11) NULL DEFAULT NULL,
    `offsidesTeam2` INT(11) NULL DEFAULT NULL,
    `goalKicksTeam1` INT(11) NULL DEFAULT NULL,
    `goalKicksTeam2` INT(11) NULL DEFAULT NULL,
    `throwInsTeam1` INT(11) NULL DEFAULT NULL,
    `throwInsTeam2` INT(11) NULL DEFAULT NULL,
    `penaltiesTeam1` INT(11) NULL DEFAULT NULL,
    `penaltiesTeam2` INT(11) NULL DEFAULT NULL,
    `substitutionsTeam1` INT(11) NULL DEFAULT NULL,
    `substitutionsTeam2` INT(11) NULL DEFAULT NULL,
    `cornersTeam1` INT(11) NULL DEFAULT NULL,
    `cornersTeam2` INT(11) NULL DEFAULT NULL,
    `freeKicksTeam1` INT(11) NULL DEFAULT NULL,
    `freeKicksTeam2` INT(11) NULL DEFAULT NULL,
    `overQuote` DOUBLE NULL DEFAULT NULL,
    PRIMARY KEY (`serial`),
    UNIQUE INDEX `id` (`id`, `minute`),
    INDEX `id_minute_index` (`id`, `minute`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1343356
;


texts: Microsoft Access - performance difference between index and constraint?

texts:
I've looked around a bit but haven't come across anything talking specifically about MS Access. So: what, if any, is the performance difference between a field with a constraint and a field with an index?

For example, I can define a field F to be non-null in three different ways as far as I can tell:

-- (1)
create table TestTable (
  ID autoincrement primary key
, F int not null
);

or:

-- (2)
create table TestTable (
  ID autoincrement primary key
, F int
, constraint cnnF not null (F)
);

or:

-- (3)
create table TestTable (
  ID autoincrement primary key
, F int
);
create index idxF on TestTable (F) with disallow null;

All three end up disallowing null values in F. But what specifically is happening when I use the first two methods as opposed to the third?

(1) Does Access internally create a constraint here? Or an index?

(2) Does Access internally create an index when I specify the constraint? Does (2) have the same performance as (3) if I'm doing lots of querying e.g. with WHERE clauses on F?

The ultimate reason for this question is laziness: I'm wondering if I can get away with specifying indexes for my fields by just using the CONSTRAINT clause in the CREATE TABLE statement, instead of having to chain together several SQL statements in a VBA sub/function.


texts: Help on designing SSAS Dim and Fact table

texts:
I am having a problem designing a Dim and Fact Table for the following problem.

I have a Pivot Grid with data as below :

USER | RELATED USER | RELATIONSHIP | PHONE NUMBER
--------------------------------------------------
Alex | Alex         | Self         | 123456
     | Alice        | Spouse       | 123456
--------------------------------------------------
Alice| Alice        | Self         | 654321
     | Alex         | Spouse       | 654321

If i try to remove the Related User Dimension, i am getting:

USER | RELATIONSHIP | PHONE NUMBER
-----------------------------------
Alex | Self         | 123456
     | Spouse       | 123456
-----------------------------------
Alice| Self         | 654321
     | Spouse       | 654321

Anyone can help me how to design the table for this SSAS Cube, so that it will show as?

USER | RELATED USER | RELATIONSHIP | PHONE NUMBER
--------------------------------------------------
Alex | Alex         | Self         | 123456
       Alice        | Spouse       | 654321
--------------------------------------------------
Alice| Alice        | Self         | 654321
     | Alex         | Spouse       | 123456

and

USER | RELATIONSHIP | PHONE NUMBER
----------------------------------
Alex | Self         | 123456
Alice| Self         | 654321

Thanks >.<


texts:
Is it possible to take backup of certain table in mysql? Suppose I have 10 tables, and I want to take a backup of 8 tables through one command.

How can I do this?


texts: #1932 - Table '' doesn't exist in engine after accidental delete

texts: I forgot to export my main table before deleting and reinstalling xampp. I recovered it from the recycling bin, but I get #1932 - Table '' doesn't exist in engine when I try to open any of the tables. Collation for each table says "in use". Is there anyway to fix this mistake?


texts:
I have a lot of SharePoint 2013 databases to backup and if update fail to  restore, so I want to automate this operations.

ALTER DATABASE [TEST_1] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

BACKUP DATABASE [TEST_1]
TO DISK = 'C:\path\test_1.bak' WITH NOINIT, STATS = 10
GO

ALTER DATABASE [TEST_1] SET ONLINE WITH ROLLBACK IMMEDIATE

But there is and error msg:

  Msg 942, Level 14, State 4, Line 1
  Database 'TEST_1' cannot be opened because it is offline.

  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

Is it possbile to switch database offline, make backup and turn it in online mode after this?


texts: SQL Server 2014 - Compute scalar over computed indexed column

texts:
I've created a table a short while ago, and it currently handles roughly 100M rows.

For reporting purposes, I added a computed, non-persisted column which is the HASHBYTES in SHA1 of 4 non-NULLable varchar columns in this table. I'm not using a scalar function for this : it was such as :

    ALTER TABLE myTable
    ADD myHash AS(CAST(HASHBYTES(SHA1, myString1, myString2, myString3, myString4) AS varbinary(20)))

Then I added an index on it, so as to be able to perform a self-join on this computed column, as such :

    CREATE INDEX IX
    ON myTable (myHash)

The query plan of  the report query shows a Compute Scalar operator that does the HASHBYTES computation. I wonder why this happens, since this value is stored in the index; the report query only INNER JOINs once on this computed column (i.e. it does not appear in any other clause, nor between the SELECT and the FROM clauses).

So I persisted the column, but the plan is identical. Can someone explain this behavior please ?


texts: What is the difference between 'two way transactional replication' and 'peer to peer replication'?

texts: Can someone explain in detail the exact differences between 2 way transactional replication and peer to peer replication?


texts:
I am really stuck on the database structure of the shopping cart website. I am trying to create a sample shopping project for learning purpose where I am getting heavily stucked.

Here is my sample database design:

Products Table:

id    name           description    price   productType   ....
1     Sony Mobile    Sony Mobile    15000    mobile
2     Sony TV        Sony TV        35000    tv
3     LG WM          LG WM           7500    Washing Machine
4     Levovo         Lenovo Laptop  20000    Laptop

When the user search for any particular product, he will also get the filter options which will different according to productType.

For example, if the product, the user is searching is mobile smartphone, then the filters available to him should be

price range: 1000 - 100000
Camera:      2px  - 20px ..
Wifi:        yes/no
Bluetoooth:  yes/no
GPS:         yes/no
..

if the product is Laptop, then the filters will be different from mobile phone i.e.

price range: 15000 - 300000
Harddisk:    320GB - 2TB
RAM:         1GB   - 64GB
OS:          Linux
..

Same, the filters will vary from washing machine to TV. The problem is, where could these filters should be stored in the database. What could be the design of the database?

Another problem is, when the user visits the product's page like mobile, he will see the complete details of mobile. For example:

Size:
Battery:
images:
Removable battery:
...

I cannot store all these details of mobile in the above products table since the product table not only contains mobile but also other types of products where these details doesn't match. Example, Washing machine doesn't contains the parameter Removal battery. So, there will be a number of unnecessary columns get generated in the products table.

Now I am confused, where should I store this product details in the database?

First way could be to create one column in the products table which store all these information as json object.

Second could be to generate tables dynamically for each product type i.e. a table for all the mobiles, a table for all the laptops, etc. which should contains all its necessary fields. But this solution is quite complex & I am trying to avoid it.

Third would be to generate tables statically while coding for each possible product type. Here, the problem is that, suppose in future, if any new product type is introduced like books, then again, I will have to code it in the program while the site is live. So, this solution is not flexible.

So, how can I solve these two problem i.e. filters & showing product's details to the user?

Thanks


texts: Select n random rows for each language ID in one table

texts:
I have a table which holds many text entries for many languages, the structure is like this:

`entry` varchar(255),
`language` tinyint,

The language column is an integer type and I have the supported languages pre-defined in my code like LANG_EN = 1, LANG_DE = 2 etc, so I know the maximum possible value.

I want to select only one random row for each supported language ID, preferably with a single query.
So if I have 3 supported languages, the query should return 3 rows, with unique language values, therefore having one entry per each language.

I tried it a few ways, with using DISTINCT and GROUP BY but couldn't get it working with a single query.

SELECT * FROM lang_entries GROUP BY language ORDER BY RAND() LIMIT 3

This selects 3 entries of each language, but always the first occurrences in the table (meaning that only the order is random, the entry field is
always the same and that's what I want to be random)

SELECT DISTINCT lang, entry FROM test ORDER BY RAND() LIMIT 3;

This kind of works, but doesn't select unique items, sometimes the languages are repeated and I need to have distinct languages.


texts: Select rows that have multiple required filters

texts:
How can I select artists who have punk and rock in their musical genres? I need artists with both genres.

A example:

SELECT * FROM artist_genre WHERE genre_id IN (1,2)

In this way I recover all that belong to punk, or rock. But I only need artists who own both. Is there a easy way for this?

If a change in the structure of the databases is required, I'm up to do.


texts: Parameter value in Data Factory Triggered pipeline

texts:
I have a pipeline configured in azure data factory which basically does create a backup file (JSON) from a cosmosDB dataset and it's saved in  a blob storage, my problem comes when I want to schedule the copy task in a trigger, I see that I have to specify the value for windowStart (parameter already defined to name the JSON file with the date of the execution. ) as shown below:

Destination path:

And as you can see when I want to trigger it does ask me to specify the value for windowStart.

I tried below functions to specify that with no success so far:

"@{pipeline().parameters.windowStart}"
"@{formatDateTime(pipeline().startTime,'o')"
"@{formatDateTime(utcnow(),'yyyy-MM-dd')}"
"@{formatDateTime(trigger().startTime,'yyyy-MM-dd')}"

And the error I always get is as shown below:

Have anyone any idea or way to fix that, or have someone faced the same issue before ?


texts: Postgresql - return column of referenced table that is not primary key

texts:
I have tables:

CREATE TABLE users (
  id text PRIMARY KEY,
  internal_id serial,
  other columns...
)

CREATE TABLE listings (
  id bigserial PRIMARY KEY,
  created_at timestamptz default current_timestamp,
  posted_by text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE,
  other columns...
)

Using node-postgres, I am inserting a new record into table listings and returning the listings.id:

 pool.query(`INSERT INTO listings(${columns}) VALUES(${args}) \
           RETURNING listings.id`, [...values]);

In addition to listings.id, who could I also return users.internal_id?


texts: How we can take backup oplog on every hour and apply on top on full backup for mongodb

texts: We have full lvm snapshot backup. We are trying to take oplog backup in every hour and save as a file. We like to apply the oplog backup on top of full backup if we need to. We like to know how we can do this. We are taking everyday LVM snapshot full backup. Our Oplog is 80GB, which can hold 30 days of oplog. But we are facing issue to apply it. I can take the oplog backup in every hour. But question is : how to apply it? We don't want duplicate or any missing data.


texts: Will rand string as custom _id make B tree index split higher frequency in MongoDB?

texts:
In the answer to the question Are there any advantages to using a custom _id using MongoDB?

you can make them more human-friendly, using random strings: t3oSKd9q

For the B tree, random key inserts into it will make the B tree node split in higher frequency. Is it the same issue here when we use random string as _id in MongoDB?


texts: Trim whitespace (spaces, tabs, newlines)

texts:
I'm on SQL Server 2014 and I need to clean whitespace from start and end of a column's content, where whitespace could be simple spaces, tabs or newlines (both \n and \r\n); e.g.

'    this content    '                          should become 'this content'
'  \r\n   \t\t\t this \r\n content \t  \r\n   ' should become 'this \r\n content'

and so on.

I was able to only achieve the first case with

UPDATE table t SET t.column = LTRIM(RTRIM(t.column))

but for the other cases it doesn't work.


texts: HierarchyID--multiple root values in one table

texts:
Currently I am trying to work my way around the HierarchyID concept in order to convert a few tables for our programmers.  I have the gist of it understood, but one of the cases I'm working on has me stumped.

One particular table deals with different kinds of equipment failure codes using a parent/child relationship model.  The problem is, the way the codes are laid out means that the (seemingly) most obvious solution would be to use multiple different trees for each base category.  While the CLR methods work for single-root cases, using multiple root values throws off the methods (for example, getting the ancestor of one particular node/ID will also get the values in the same general spot for the other trees).

For reference, the table code:

CREATE TABLE FailCode
(
  IDOrg hierarchyid,
  ID int IDENTITY,
  Name nvarchar(200),
  ParentID int
CONSTRAINT PK_FailCode_ID
  PRIMARY KEY CLUSTERED (ID)
);
GO

I'm looking at my options.
I've figured one option would be to use a dummy root value and have the categories as child nodes of the dummy root.  My other idea would be to create a separate table for each category on its own and use a single-root hierarchy for each table.

Beyond that, I'm drawing blanks.  My question is:
What other options are there to represent multiple-root hierarchy setups in SQL Server?

EDIT:  I should probably add this--the table in question used to have another column for each category, which I had used in my queries.  This column is being struck from the table at the behest of another DBA and a couple of programmers, so it is no longer an option for me.

(Revised question begins here; I have left the original for the sake of anyone reading.)

@Avarkx:
I humbly thank you for the answer. Unfortunately, there's a bit of a problem--I spoke with the other DBA and he insisted that there have to be three separate root nulls (listed as NULL with parent ID AND having just '/' for the ToString representation).

The table currently looks something like this (sorted by level for ease of reference):

       IDOrg   ID   Name    ParentID
---------------------------------
/      0x       1   Root1   NULL
/      0x       2   Root2   NULL
/      0x       3   Root3   NULL
/1/    0x58     4   P1C1    1
/1/    0x58     9   P2C1    2
/1/    0x58     13  P3C1    3
/1/1/  0x5AC0   11  P9C1    9
/1/1/  0x5AC0   15  P13C1   13
/1/1/  0x5AC0   7   P4C1    4
/1/2/  0x5B40   8   P4C2    4
/1/2/  0x5B40   16  P13C2   13
/1/2/  0x5B40   12  P9C2    9
/2/    0x68     14  P3C2    3
/2/    0x68     10  P2C2    2
/2/    0x68     5   P1C2    1
/2/1/  0x6AC0  17   P5C1    5
/2/1/  0x6AC0  21   P10C1   10
/2/1/  0x6AC0  23   P14C1   14
/2/2/  0x6B40  24   P14C2   14
/2/2/  0x6B40  22   P10C2   10
/2/2/  0x6B40  18   P5C2    5
/3/    0x78    6    P1C3    1
/3/1/  0x7AC0  19   P6C1    6
/3/2/  0x7B40  20   P6C2    6

So I suppose I need to revise my original question:
Is it possible to use this table setup and manipulate queries in order to differentiate between specific trees when retrieving information regarding the hierarchy?
For example, if I needed to get the ancestor for '19', would it be possible to filter things out by tree in a query (or perhaps a query within a stored proc.) so that I just get '6' and not '10' and '14' as well? Adding records is simple enough, but queries like this example are problematic.

(If the answer is "no", I suppose I'll have to work something out with him...that adjacency pair solution is downright brilliant, and I see no reason why we couldn't use that if we weren't willing to do so.)


texts: I am using MySQL v5.6.x. and using mysqldump takes a long time to dump and restore the db and does not seem practical for DB size larger than 50GB. It looks like the mysqlbackup utility that comes with the MySQL Enterprise Backup is very fast. Any other alternatives or ideas to speed up the dump/load time?


texts: How do I get Pentaho Reporting/Kettle to produce PDF reports by business entity?

texts:
We use Kettle/PDI to handle usage records from legacy systems and feed them to an analysis database/DWH and also we report to our customers their activity and a backend commission report on a monthly basis from this legacy data.

Right now, I compile this into one enormous PDF using Pentaho Report Designer, then print it and hand it to the Finance gals who send checks out to the customer for the amount on the statement I've made.  They hand-collate all this.

Obviously we would like to put these online, since we have a file-area for each customer already.  The thing I need is for Pentaho to make, instead of a new page, a new PDF file for each business, and then name it the Business ID number and month, or something equally meaningful.  Is this possible?

We have experimented with splitting up the PDF but it takes someone several hours to process, and it is not pleasant work at all.  It seems this should be possible, but I do not know enough of the intricacies of Pentaho to make it work.


texts: How to avoid implicit conversion for an Integer column

texts:
SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = 5

I get convert_implicit() function called by SQL Server after executing the query in Actual Execution Plan.

  SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = convert(int,5)

Obviously when I explicitly convert the Scalar value to Integer type, convert_implicit() function is not called. Is there any way we can avoid implicit conversion for a scalar int value without using the explicit conversion. Also I would like to know the performance difference between implicit conversion ,explicit conversion and no conversion happened at all.


texts: Incorrect syntax near the keyword 'WHERE' - SQL Server 2014

texts:
The error I get is

  Incorrect syntax near the keyword 'WHERE'.

Script:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[msp3_account_login]
     @LOGINID nvarchar(50),
     @PASSWORD nvarchar(50),
     @Status NVARCHAR(50),  -- A status symbol, 1/0. Don't know what this represents
     @IPAddress NVARCHAR(50)  --Either our server IP, or their remote IP
AS
BEGIN
    DECLARE @COUNT INT;
    SET @COUNT = (SELECT COUNT(FLD_LOGINID) FROM TBL_ACCOUNT WHERE [FLD_LOGINID] = @LoginID AND [FLD_PASSWORD] = @PASSWORD) --AND GETDATE() > (SELECT * FROM fc_ConvertToDate([FLD_PASSFAILTIME])));

    IF (@COUNT > 0)
    BEGIN
        SELECT *
        FROM TBL_ACCOUNT
        WHERE FLD_LOGINID = @LoginID
    END

    IF (@COUNT = 0)
    BEGIN
        DECLARE @TotalTry SMALLINT;
        SET @TotalTry = (SELECT ISNULL([FLD_PASSFAILCOUNT], 0) FROM [TBL_ACCOUNT] WHERE [FLD_LOGINID] = @LoginID);

        IF @TotalTry > 3
        BEGIN
            DECLARE @TryAgainDate DATETIME;
            SET @TryAgainDate = DATEADD(MINUTE, 5, GETDATE());

            UPDATE [TBL_ACCOUNT]
            SET [FLD_PASSFAILTIME] = (SELECT *
                                      FROM [fc_ConvertDate2](@TryAgainDate))
        END
        ELSE
        BEGIN
            UPDATE [TBL_ACCOUNT]
            SET [FLD_PASSFAILCOUNT] = [FLD_PASSFAILCOUNT] + 1
            WHERE [FLD_LOGINID] = @LoginID;
        END
     END

     --UPDATE TBL_ACCOUNT SET [FLD_VER] = '1'
     --SELECT * FROM TBL_ACCOUNT
     RETURN 0;
END

We can't hash the passwords, we do not have the source code for the applications, it's an old game.


texts: Updating row of one table using rows of another table based on specific conditions

texts:
I want to update OFFICE_ID,OFFICE_TYPE of FA_SUBLEDGER_MST table, by using OFFICE_ID,OFFICE_TYPE from EMPLOYEE_MST table based on following conditions:

EMPLOYEE_MST.SL_CODE = FA_SUBLEDGER_MST.SL_CODE
EMPLOYEE_MST.OFFICE_ID <> SL.OFFICE_ID
  OR EMPLOYEE_MST.OFFICE_TYPE <> SL.OFFICE_TYPE
AND EMPLOYEE_MST.OFFICE_TYPE != 'DHB'

I tried this query:

UPDATE FA_SUBLEDGER_MST sl
SET
  (sl.OFFICE_ID, sl.OFFICE_TYPE) =
  (SELECT
       emp.OFFICE_ID, emp.OFFICE_TYPE
   FROM EMPLOYEE_MST emp
   WHERE emp.OFFICE_ID <> sl.OFFICE_ID
      OR emp.OFFICE_TYPE <> sl.OFFICE_TYPE
      AND sl.SL_CODE = emp.SL_CODE
      AND emp.OFFICE_TYPE != 'DHB')
WHERE sl.STATUS = 'A'
  AND EXISTS (SELECT 1
              FROM EMPLOYEE_MST emp
              WHERE emp.OFFICE_ID <> sl.OFFICE_ID
                 OR emp.OFFICE_TYPE <> sl.OFFICE_TYPE
                AND emp.SL_CODE = sl.SL_CODE
                AND emp.OFFICE_TYPE != 'DHB');

But both gave this error:

  SQL Error: ORA-01427: single-row subquery returns more than one row
  01427. 00000 - "single-row subquery returns more than one row"

And this:

UPDATE
  (SELECT em.OFFICE_ID emoffid,
    SL.OFFICE_ID sloffid,
    em.OFFICE_TYPE emofftype,
    SL.OFFICE_TYPE slemofftype,
    SL.STATUS
  FROM EMPLOYEE_MST em
  JOIN FA_SUBLEDGER_MST SL
  ON em.SL_CODE      =SL.SL_CODE
  WHERE em.OFFICE_ID<>SL.OFFICE_ID
  OR em.OFFICE_TYPE <>SL.OFFICE_TYPE
  AND em.OFFICE_TYPE!='DHB'
  AND SL.STATUS      ='A'
  ) t
SET t.sloffid   =t.emoffid
WHERE t.emoffid<>t.sloffid
OR t.emofftype <>t.slemofftype
AND t.emofftype!='DHB'
AND t.STATUS    ='A';

This returns those errors:

  SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
  01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
  *Cause:    An attempt was made to insert or update columns of a join view which map to a non-key-preserved table.
  *Action:   Modify the underlying base tables directly.

And also this:

UPDATE FA_SUBLEDGER_MST
SET
  (
    OFFICE_ID,
    OFFICE_TYPE
  )
  =
  (SELECT OFFICE_ID,
    OFFICE_TYPE
  FROM EMPLOYEE_MST
  WHERE (FA_SUBLEDGER_MST.OFFICE_ID != EMPLOYEE_MST.OFFICE_ID
  OR FA_SUBLEDGER_MST.OFFICE_TYPE!  =EMPLOYEE_MST.OFFICE_TYPE)
  AND FA_SUBLEDGER_MST.SL_CODE      = EMPLOYEE_MST.SL_CODE
  AND EMPLOYEE_MST.OFFICE_TYPE!     ='DHB'
  ) ;

It worked but it updates all 124,451 rows, while the rows that met with the above specified condition are only 4,522 rows.

Both the tables contain more than 100,000 records and OFFICE_ID can be 1,2,3,4,5...18 and OFFICE_TYPE can be 'DE','DI','HO', Hence they repeat frequently.

I need a oracle query to do so. Can't use a procedure.

Would be thankful for your help.


texts:
I am currently using \timing on to do some simple performance testing in Postgres.  I would like to run many queries and pipe the timing results to a file.  However, all of the options I have tried (\o, \l, and their command-line equivalents) pipe only the query results to a file.  The Time: 1.234 ms message is not written to the file.

Is there any way I can pipe the timing output caused by \timing on into a file, or will I have to choose some other method for performing my tests?


texts: Update to MySQL 5.7 and high disk space consumption

texts:
Using: MySQL 5.6 on Debian 9, total DB size is around 450GB

Updated to 5.7, ran mysql_upgrade, noticed that around 150 GB has been taken up. 2 tables are really large and they stayed in 'copying to tmp table' for a couple of hours

Noticed innodb_file_per_table was on and created large ibd files that weren't there previously.

Restored from a snapshot, disabled the file_per_table, ran mysql_upgrade again. 100GB gone, which is almost 1/4 of my total DB.

In the first case, it pulled the data from ibdata and put it into a separate file but ibdata never shrinks so taken space almost doubled.

What happens in the second case? Does the temp table get created within the ibdata file that never shrinks so even when table is not used anymore - space is still gone?

Another thing I noticed is that space consumption doesn't start until query has been in copying to tmp table status for like an hour or so.

1) Is there any way to avoid/minimize space increase?

Would running update with file_per_table on, then disabling it and running alter table engine innodb free up the space?

2) Any way to predict how much space will be occupied? At least per table


texts: Assigning a Variable Generic NEW Value by Pressing a Postgres Trigger

texts:
In the trigger body, how can I get a value of NEW by the field name?

This is what I want to do:

some_key = "some_column";

value := NEW[some_key];


texts:
We are upgrading our hardware for Sybase ASE and minor upgrade in the Sybase ASE Service Pack Version.

Upgrades are as follows:

1.Number of Cores - From 4 core to 8 core

2.Physical memory - 64 GB to 128GB.

3.ASE - Upgraded from "Adaptive Server Enterprise/15.7/EBF 21707 SMP SP110 /P/ia64/HP-UX B.11.31/ase157sp11x/3546/64-bit/FBO/Fri Nov 8 08:40:39 2013" to "Adaptive Server Enterprise/15.7/EBF 27351 SMP SP139 /P/ia64/HP-UX B.11.31/ase157sp138x/4041/64-bit/FBO/Tue Aug 8 07:33:11 2017"

I have configured number of engines from 4 to 8 and max memory from 60GB to 100GB.

can someone please advise what additional parameters should be changed in order to enhance performance of Sybase on the new server


texts: Unable to view MySQL db stored procedure

texts:
We have many stored procedures created with SQL SECURITY DEFINER by user A
Now user B is responsible for maintaining some of the stored procedure. However user B is unable to view/update the stored procedure from workbench since the definer is set to A. Is there an way to give A and B access to the stored procedure at the same time ?


texts: Best table name instead of "user", if the DB doesn't like that

texts:
Yes, I know, it is possible to quote the name of the table, but it is sometimes improper.

If there is no alternative, I name this table as "users", although this semantically unneeded plural makes things a little bit inconsistent.

What alternative solutions do exist?


texts: What's the best way to concatenate a multi-column foreign key?

texts:
We are expanding our data warehouse to include a new source system. In the new system, we want a single column for a primary key, but in the old system it's two columns.

For exmaple:

create table #new_case (case_id varchar(255))

create table #old_case (patient_id int, care_event_counter int)

insert into #old_case (patient_id, Care_event_counter)
values
(55, 1),
(55, 2),
(55, 3),
(56, 1),
(57, 1),
(57, 2)

insert into #new_case (case_id)
select cast(patient_id as varchar(55)) + '_' + cast(care_event_counter as varchar(55))
from #old_case

select * from #new_case

problem: This has got to be super slow on a large scale, and I'd prefer to use int across the board.

Question: How do you merge two integer columns intelligently? is there a math trick you could use to derive the values that would be faster?


texts: Near real time writable copy of production database

texts:
I have a production database (SQL server 2016) which is hosted on an Azure VM. We have set up a log shipping (every 15 mins) onto another Azure VM which is a read replica and would act as a DR system. The team also makes use of this DB for their quick reporting needs.

But since it is a read replica team will not be able to create any tables in it for reporting purpose. So looking for a way to get real-time data from production DB on a periodic basis but also it has to be writable so that teams can dip into it for all their quick reporting needs.


texts:
I have a server with SQL Server 2005 installed with the latest service packs. SQL Server 2012 express was been installed on same machine and seems to have broken the Reporting services service. When I browse to localhost/reports, I get the unhandled exception

  'The ReportServerVirtualDirectory' element is not a configuration file element.

Pretty sure this it to do with the corresponding service being stopped. But it wont start up anymore. The express one is started, but the older 2005 won't start manually (even if I stop the express one), and if I try starting the service in reporting services configuration, it gets a timeout message. I've tried repairing the old installation reporting services but it hasn't helped.


texts: Implementation of an employee and customer scenario in database

texts:
So, I have a scenario of a movie theatre and in this scenario employee can be a customer too... if I create two entities, customer and employee ... and make emp_id of employee a foreign key in customer then it makes employee a customer too.

Customer: cust_id, Name, age, emp_id
Employee: emp_id, Name, age

But when I do this, data of employee gets repeated in customer. What should I do?


texts: Relative cost of two similar queries involving XML columns

texts:
I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column.

I checked the execution plan of both these queries and find that the first query has a lower I/O cost and subtree cost than second one. I was expecting that the first one would have a higher cost as it uses cast and charindex, but that is not the case.

Why does it have a lower cost?

First query:

SELECT *
FROM mytable
WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 )

Second query:

SELECT *
FROM mytable t1
WHERE t1.[xmlcolumn].exist('//*/text()[contains(.,"abc")]')=1


texts: Initialize replication from multiple split backup files

texts:
We are creating a new Node within our existing P2P replication setup. To be able to create the backup and copy the files I am Backing up to 9 files - each will be about 72Gb:

e.g.

BACKUP DATABASE [Database_Name] TO
DISK = 'C:\MSSQL_Backup\Database_Name_file1.bak',
DISK = 'C:\MSSQL_Backup\Database_Name_file2.bak',
DISK = 'C:\MSSQL_Backup\Database_Name_file3.bak',
DISK = 'C:\MSSQL_Backup\Database_Name_file4.bak'

etc. (up to 9)

When I restore at the New Server I will then add the new Node to the P2P Topology.

All good so far?

The question is: When I Initialize the subscription at the new Node - how can I specify the Backup that was used - given that it is made up of 9 files?

I have searched the Net and though I have found the question asked a couple of times - it seems that no one can answer.

I would test this, but I am really pushed for time.

Any help appreciated.


texts: Need to join two similar queries that represent different years

texts:
Rather new to Access (using 2016) and very sketchy on my SQL skill set.

First query totals sales by ALL THREE (user-supplied) criteria:

Salesman
Customer
Product_Code
[Year]

Second query does same except for [Year-1] where [Year] is a user supplied variable. This shows me current and prior total sales grouped by the three criteria shown above.

First Query will produce:

AKER   Cust1   Prod1    CurSales
AKER   Cust1   Prod2    CurSales
CLUT   Cust1   Prod3    CurSales
DELO   Cust3   Prod1    CurSales

etc.... in any combination of the three fields - all producing a total sales number for that combination for the CURRENT YEAR.

Second Query produces same, except for the PRIOR YEAR and may contain more (or fewer) combinations of the three fields.

I want third query to combine the two and show each combination that has a value of "Total Sales" in EITHER YEAR. I can then do comparisons using calculated fields.

I'm using ACCESS 2016 - primarily using the GUI.

For reporting, I need to do full outer join (I think) of these queries (i.e. show all records in both, joining rows where appropriate). I will then use this combined query to report and show comparative date.

I need help creating this second query. Do I need to create a unique "key" perhaps? Chasing my tail.....

Also, a crosstab can give me the exact results I want but I cannot then create a report because the field names are dynamic and I can't see myself writing VBA code at this juncture. I anyone knows how to accomplish this without VBA, I'm all good .


texts: Why isn't my MySQL replication filter working?

texts:
mysql> show slave status\G

               Slave_IO_State: Waiting for master to send event

             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.user,mysql.columns_priv,mysql.tables_priv,mysql.db,mysql.host,mysql.proxies_priv,mysql.procs_priv
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: columnstore.%
                   Last_Errno: 1286
                   Last_Error: Error 'Unknown storage engine 'columnstore'' on query. Default database: 'columnstore'. Query: 'create table columnstore.table1 (hour DATETIME, country VARCHAR(10)) engine=columnstore default charset=utf8'


texts:
Was uploading a few hundred thousand rows in my SQL 2008 R1 database and have come in this morning to an "In Recovery" message next to the database name in SQL Management Studio.

I've tried a DBCC CheckDB, which returns  CHECKDB found 0 allocation errors and 0 consistency errors in database 'DBName'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.  When I check the status (state_desc) in sys.databases, it returns ONLINE.  In fact, nothing in the sys.databases table looks out of the ordinary.

I can't get rid of the 'In recovery' message though, and I don't want to just restart.  Is there any way of telling how long it will be like this for, or are there any other problems I should check for?


texts: SQL Query expensive when try to combine everything into one query

texts:
I'm trying to join two queries into one.
First
EXPLAIN SELECT * FROM list
WHERE plate_type = 3 and plate_letters = 'VH' and plate_numbers > 9949
ORDER BY plate_type, plate_letters, plate_numbers
LIMIT 1

Limit  (cost=0.43..4.45 rows=1 width=70)"
  ->  Index Scan using plate_type_letters_numbers_idx on list (cost=0.43..92.86 rows=23 width=70)

Second
EXPLAIN SELECT * FROM list
WHERE plate_type = 3 and plate_letters = 'VI'
ORDER BY plate_type, plate_letters, plate_numbers
LIMIT 1

Limit  (cost=0.43..4.44 rows=1 width=70)"
  ->  Index Scan using plate_type_letters_numbers_idx on list (cost=0.43..16839.19 rows=4426 width=70)

Shouldn't the following query have a similar cost?
EXPLAIN SELECT * FROM list
WHERE (plate_type = 3 and plate_letters = 'VH' and plate_numbers > 9949) or (plate_type = 3 and plate_letters = 'VI')
ORDER BY plate_type, plate_letters, plate_numbers
LIMIT 1

Limit  (cost=0.43..171.36 rows=1 width=70)
  ->  Index Scan using plate_type_letters_numbers_idx on list  (cost=0.43..760448.31 rows=4449 width=70)

Doing it with UNION have a very good response time, although the cost is higher. Not sure why is showing that low values in the limit.
SELECT * FROM list
WHERE plate_type = 3 and plate_letters = 'VH' and plate_numbers > 9949
UNION
SELECT * FROM list
WHERE (plate_type = 3 and plate_letters = 'VI')
ORDER BY plate_type, plate_letters, plate_numbers
LIMIT 1

Limit  (cost=15994.01..15994.01 rows=1 width=879)
  ->  Sort  (cost=15994.01..16005.13 rows=4449 width=879)
        Sort Key: list.plate_type, list.plate_letters, list.plate_numbers
        ->  HashAggregate  (cost=15927.27..15971.76 rows=4449 width=879)
              Group Key: (list of fields redacted)
              ->  Append  (cost=0.43..15704.82 rows=4449 width=879)
                    ->  Index Scan using list_plate_type_letters_numbers_idx on list  (cost=0.43..92.86 rows=23 width=70)
                          Index Cond: ((plate_type = 3) AND ((plate_letters)::text = 'VH'::text) AND (plate_numbers > 9949))
                    ->  Bitmap Heap Scan on list list_1  (cost=133.80..15545.23 rows=4426 width=70)
                          Recheck Cond: ((plate_type = 3) AND ((plate_letters)::text = 'VI'::text))
                          ->  Bitmap Index Scan on list_plate_type_letters_numbers_idx  (cost=0.00..132.69 rows=4426 width=0)
                                Index Cond: ((plate_type = 3) AND ((plate_letters)::text = 'VI'::text))

Create table:
CREATE TABLE public.list
(
    id bigint NOT NULL DEFAULT nextval('list_id_seq'::regclass),
    plate character varying(10) COLLATE pg_catalog."default" NOT NULL,
    plate_type smallint,
    plate_letters character varying(4) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    plate_numbers smallint,
    CONSTRAINT list_pkey PRIMARY KEY (id)
)

Index used:
CREATE INDEX plate_type_letters_numbers_idx
    ON public.list USING btree
    (plate_type ASC NULLS LAST, plate_letters COLLATE pg_catalog."default" ASC NULLS LAST, plate_numbers ASC NULLS LAST)
    TABLESPACE pg_default;

EDIT
After the suggestion to use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) here is the query that uses OR.
Limit  (cost=0.43..605.05 rows=1 width=71) (actual time=61290.240..61290.242 rows=1 loops=1)
  Buffers: shared hit=1159648 read=288499 dirtied=31655 written=11144
  ->  Index Scan using plate_type_letters_numbers_idx on list  (cost=0.43..764232.91 rows=1264 width=71) (actual time=61290.236..61290.237 rows=1 loops=1)
        Index Cond: (plate_type = 3)
        Filter: ((((plate_letters)::text = 'VH'::text) AND (plate_numbers > 9949)) OR ((plate_type = 3) AND ((plate_letters)::text = 'VI'::text)))
        Rows Removed by Filter: 2698980
        Buffers: shared hit=1159648 read=288499 dirtied=31655 written=11144
Planning Time: 0.301 ms
Execution Time: 61291.201 ms

The number of hits was extremely high.


texts: What type of locks does BEGIN TRANSACTION cause against a specific table that's being read from?

texts:
In the following example, what kinds of locks are generated from the explicit transaction block that wraps the SELECT INTO query?

BEGIN TRANSACTION T1

SELECT Field1, Field2, Field3
INTO TableB
FROM TableA
WHERE Field3 > Value1

COMMIT TRANSACTION T1

Notes:

1) TableA has about 100 billion rows, but the filter causes the query to return only about 5 million rows.

2) Field3 is a clustered index on TableA, and the generated query plan is doing a clustered index seek.

3) The isolation level in my scenario is the default (READ COMMITTED).

Do explicit transaction blocks like this potentially cause more locking (or a higher chance of lock escalation) against the table then if the query wasn't wrapped in an explicit transaction?


texts: SQL Server 2016 Standard or Enterprise for SharePoint 2016

texts:
I know that for SharePoint 2016 version we can go for either SQL server 2016 standard or enterprise. But just wanted to know are there any disadvantages of going for SQL server standard edition instead of enterprise?

My question is in particular to the SharePoint features we might be missing if we go for Standard SQL server.


texts: Regularly clone/backup 2 MySQL databases unidirectionally from PC to Laptop both running Windows

texts:
I have a PC running Windows XP with a custom ERP system and MySQL Server 4.x (A) and a Laptop running Windows 10, same MySQL 4.x and same custom ERP (B). I'm afraid I can't upgrade PC nor MySQL, etc. Can't touch anything.

I installed SQLYog Ultimate (Workbench/Navicat don't like this MySQL version) on laptop and tried scheduling an unidirectional database syncing job from (A) to (B).
It works, but the problem is that SQLYog compares table by table, and when different, copies record by record and this takes hours to complete, because databases to be cloned from (A) are 1.5 and 2 gb each

I've been searching other ways I could setup something in laptop to regularly (once or twice a week through Windows Scheduler) clone a database directly from (A) to (B) without user intervention, but I've run out of ideas.

Any clues? Thanks


texts: Logging within a procedure's transaction

texts:
I have an ETL process running inside a PostgreSQL 9.3 procedure on AWS RDS.  The procedure normally takes about ten minutes to run, but I'm having a rare issue where it takes several hours and never completes.

The procedure has a number of delete and insert statements, and I have them all running inside of a single transaction.  Running the same data by hand didn't reproduce the issue, so I'd like to do some basic logging to see which of the statements is causing a delay.  The process runs unattended, so I need a solution that I can check later.

What I can't figure out is how I can do logging from within a transaction.  Because this is on RDS, a simple grep of server logs isn't so simple, so I was hoping to just write a row to my own log table.  Is there an easy way of doing this?


texts: Query For All Constraints in a SQL Database Diagram

texts:
Is there a way to show constraints in a SQL Server db diagram?

Like for example, in a card number, it shows there is a CHECK constraint etc..


texts: How do I stop PG from wasting ids when doing ON CONFLICT DO NOTHING?

texts:
I have an empty table with one bigserial id column and one unique text column, and do this:
INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test
INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test
INSERT INTO tab (col) VALUES ($1) ON CONFLICT DO NOTHING; -- $1 = test2

The table now has two rows:
1, test
3, test2

I expect and want:
1, test
2, test2

Why is the bigserial id column being increased even when "NOTHING" is happening for the second row due to the uniqueness of the text column?
More like "ON CONFLICT DO SOMETHING".
I doubt that this is related to the real error I'm having and which has been eluding me for months, but I need to straighten this out first as it seems like it could be related.
I could not find any mention in the manual on how to make it not increase the id when it doesn't INSERT any row.


texts: an element with the same SQL SERVER key has already been added

texts:
Good morning community, I am having problems when I run this query from SQL 2018 and Visual basic 2010 shows me the following error "an element with the same key has already been added"
SELECT DESCRIPCION AS nodeText,'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeKey,''AS nodeParentKey FROM DEPARTAMENTO
UNION ALL
SELECT DESCRIPCION AS nodeText,'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeKey,
'DEPA' + CAST(IDDEPARTAMENTO AS VARCHAR) AS nodeParentKey FROM PROVINCIA
UNION ALL
SELECT DESCRIPCION AS nodeText,'DIST' + CAST(IDUBIGEO AS VARCHAR) AS nodeKey,
'PROV' + CAST(IDPROVINCIA AS VARCHAR) AS nodeParentKey FROM ubigeo

As I can avoid duplicate elements, I think it is with the DISTINCT clause but I have no idea where this clause can go, please support


texts: pg_basebackup giving "could not receive data from client: Connection reset by peer"

texts:
I'm trying to get data to a new slave box from my new master (previously the slave).

I've tried the steps already and it worked previously.  Now when I run the following command:

pg_basebackup -h masterhost -D /var/lib/postgresql/9.3/main -U rep -v -P -X stream

Sometimes when I run this command, nothing is logged.  Other times I receive: "could not receive data from client: Connection reset by peer".  I can see this process running on the master:

postgres: wal sender process rep slavehost(60899) sending backup "pg_basebackup base backup"

I'm able to scp from one box to another at 100MB/s
I've set up the snakeoil certs on both boxes
It rejects invalid passwords
When I kill the process on the slave, I get "pg_basebackup: could not initiate base backup: FATAL:  terminating connection due to administrator command"
There is no additional load on either box when I run this command
The /var/lib/postgresql/9.3/main dir on the slave is owned by postgres and is empty
The DB is ~60GB
Eventually the "wal sender process" on the master ends but nothing seems to happen
I can psql to the master box from the slave using a non-replication user
Both boxes are Ubuntu 14.04 running PG 9.3.6

Slave config:

listen_addresses = '*'
hot_standby = on

default_statistics_target = 50
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 22GB
work_mem = 192MB
checkpoint_segments = 16
shared_buffers = 7680MB
max_connections = 160
log_min_duration_statement = 80
log_temp_files = 0

Master has the same but also:

wal_buffers = 8MB
checkpoint_segments = 16
wal_keep_segments = 128
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 2
vacuum_defer_cleanup_age = 100000
ssl_renegotiation_limit = 0


texts: Which method is better to drop all user connections before a restore Change to single user mode or Take offline the database?

texts:
When we restore a database from prod to dev which method is better to disconnect other connection accessing the database? I am on SQL Server 2008r2

1.    Change to Single user mode

use master

Go

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Go

RESTORE DATABASE [MyDB] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\DB.bak'
WITH  FILE = 1,
MOVE N'DB'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB.mdf',
MOVE DB_log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB_log.ldf',
NOUNLOAD,
REPLACE,
STATS = 10

GO

ALTER DATABASE MyDB SET MULTI_USER

Go

2.    Take offline the database and restore database

USE master

GO

ALTER DATABASE MyDB
SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

RESTORE DATABASE [MyDB] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\DB.bak'
WITH  FILE = 1,
MOVE N'DB'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB.mdf',
MOVE DB_log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB_log.ldf',
NOUNLOAD,
REPLACE,
STATS = 10
GO

ALTER DATABASE MyDB
SET OFFLINE

GO

Which method would assure I will be the only user so I can do the restoration without a problem?


texts: Multi-thread Informatica connections to use different processors on target database server

texts:
See this question for reference.

Can data streams from Informatica to SQL Server be multi-threaded?

I've got most of my problem figured out.  I've split up the one large table into four smaller tables.  I'm now moving the data from the four source tables to four target tables.  Each in their own mapping.  In Workflow Manager, each mapping is a separate session, inside of its own workflow.  And then I have a master workflow with four command tasks calling the four workflows.

How do I isolate the connections so that each command connects to the target SQL Server database, so that each runs on a different processor?

EDIT:

Informatica also has functionality called "Partitioning", which does exactly what I did here.  It splits the data up however you want.  But there is some Informatica server level setup for this to work.


texts: How can I implement a quick backup and restore strategy for database script testing?

texts:
I've got a copy of our 130GB production database restored on our development SQL Server 2008 R2 Standard Edition 64-bit.

I'm developing some test scripts, which need to de-duplicate a lot of data that has been created in error, so I've got a copy of the database in order to develop and test the de-duplication scripts.

The full restore just took 45 minutes and I want to be able to test my script, then quickly restore the database back to the initial state, then fix any issues with my script and re-test the improved database script.

Since I'm not on Enterprise Edition snapshot backups is not available to me.

What backup and restore strategy could I use to prevent this 45 minute bottleneck? If there is one available, please can you suggest a script for the backup and restore?


texts:
(SQL Server 12.0.2000.8 running in Azure)

It has been my understanding that REORGANIZEing an index should not interfere with other operations (that is, it should not block queries on the table undergoing index reorganization, and it certainly should not block queries on other tables).  However, I have a nightly index maintenance job that appears to be blocking other queries while it runs.

The query causing the block is in the format:

ALTER INDEX [indexName] ON tableName REORGANIZE

It is causing other queries to wait, even simple ones like:

SELECT * FROM tableName WHERE indexedColumn = @value

I used the sp_who2 procedure to see which queries were waiting, and which other query they were blocked by.  And again, the table undergoing index maintenance and the table in the SELECT are completely unrelated (they're even in different schemas; FWIW the table being reorganized is in dbo).

The table being reorganized has almost 500 million rows.  The index being affected is a non-clustered, non-unique index on a single bigint column used by a foreign-key.  The table itself consists of two bigint columns, one tinyint, and a couple small nvarchars.

Doesn't seem like anything too extraordinary, but I cannot figure out why it's blocking other queries.  Is there some hidden dependency that I'm missing?


texts: For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

texts:
I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4:

gear_category (
   id INTEGER PRIMARY KEY,
   name TEXT,
   parent_id INTEGER
);

As well as a list of items attached to the categories:

gear_item (
   id INTEGER PRIMARY KEY,
   name TEXT,
   category_id INTEGER REFERENCES gear_category
);

Any category can have attached gear items, not just the leaves.

For speed reasons, I want to pre-calculate some data about each category, which I'll use to generate a Materialized View.

Desired output:

speedy_materialized_view (
   gear_category_id INTEGER,
   count_direct_child_items INTEGER,
   count_recursive_child_items INTEGER
);

count_recursive_child_items is the cumulative number of GearItems attached to the current category or any child categories. There should be one row for each category, with a 0 for any counts that are 0.

In order to calculate this, we need to use a recursive CTE to traverse the tree:

WITH RECURSIVE children(id, parent_id) AS (
    --base case
    SELECT gear_category.id AS id, gear_category.parent_id AS parent_id
    FROM gear_category
    WHERE gear_category.id = 37  -- setting this to id includes current object
                             -- setting to parent_id includes only children
    --combine with recursive part
    UNION ALL
    SELECT gear_category.id AS gear_category_id
         , gear_category.parent_id AS gear_category_parent_id
    FROM gear_category, children
    WHERE children.id = gear_category.parent_id
)
TABLE children;

It's simple to count the child gear items attached to this list of child categories:

--Subselect variant
SELECT count(gear_item.id) AS count_recursive_child_items_for_single_cat
FROM gear_item
WHERE gear_item.category_id IN (
SELECT children.id AS children_id
FROM children);

-- JOIN variant
SELECT count(gear_item.id) AS count_recursive_child_items_for_single_cat
FROM gear_item, children
WHERE gear_item.category_id = children.id;

But if you look at the CTE, I've hardcoded the starting category ID of '37'. I can't figure out how to combine these queries to generate the count_recursive_child_items for all categories, not just a single one.

How do I combine these?

Also, currently for each category, I calculate all the child categories, which creates a lot of duplicated work, and I'm not sure how to remove that. For example, say I have Grandparent > Parent > Leaf. Currently I separately calculate child categories for Grandparent and Parent, which means I twice calculate the Parent > Leaf relationship.

And since I'm already returning count_direct_child_items for each category, it might be faster to just use those when calculating count_recursive_child_items rather than starting from scratch in my counts like I currently do.

Separately, each of these concepts makes sense to me. I just can't figure out how to combine them into a single elegant/optimized query.


texts: Oracle migration unable to create indexes

texts:
I want to move the contents of a database instance to another using the expdp and impdp utilities. The source instance is version 12.1.0.1.0 and the destination is 11.2.0.1.0 so I modified the expdp command to include the version:

expdp system@db1/pass schemas=main directory=dmpdir dumpfile=backup-26-11-2015.dmp logfile=backup-26-11-2015.log version=11.2.0.1.0

However when I try to import this dmp file into the 11.2 instance it copies everything except for indexes with this error:

ORA-39083: Object type INDEX failed to create with error:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INDEX'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
Failing sql is:
BEGIN
ctxsys.driimp.create_index('ACTCI_T1DX','ACTCI',USER,
'','1','DESCRIPTION','1',
'100','0','8130','1',
'LANGCODE',NULL, NULL, 0,'Y',NULL, NULL, NULL, NULL, NULL);

ctxsys.driimp.set_object('DATASTORE','DIRECT_DATASTORE',0);

ctxsys.driimp.set_object('DATATYPE','CHAR_DATATY

I've noticed that the ending line is missing some code and I'm seeing this occur for every error thrown. Why is the statement cutting off like that?


texts: Need to update a table with an iterative number based on a subquery

texts:
Massive edit to follow procedure and enable reproducing this in a fiddler somewhere.

Table 1, schedule:

CREATE TABLE `schedule` (
  `scheduleid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sessionid` int(11) NOT NULL DEFAULT '0',
  `roomid` int(11) NOT NULL DEFAULT '0',
  `starttime` time NOT NULL DEFAULT '00:00',
  `schorder` int(11) DEFAULT NUL
);

Sample data:

INSERT INTO `schedule` (scheduleid, sessionid, roomid, starttime) VALUES (1, 13, 2, '18:00');
INSERT INTO `schedule` (scheduleid, sessionid, roomid, starttime) VALUES (2, 17, 3, '19:00');
INSERT INTO `schedule` (scheduleid, sessionid, roomid, starttime) VALUES (3, 18, 2, '19:00');
INSERT INTO `schedule` (scheduleid, sessionid, roomid, starttime) VALUES (4, 23, 2, '17:00');

Table 2, rooms:

CREATE TABLE `rooms` (
  `roomid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sort_order` int(11) NOT NULL DEFAULT '0'
);

Sample data:

INSERT INTO `rooms` (roomid, sort_order) VALUES (2, 1);
INSERT INTO `rooms` (roomid, sort_order) VALUES (3, 2);

The SELECT statement that gets me the schedule in order by start time, then room sort order:

SELECT
    SCH.starttime,
    R.roomid,
    SCH.sessionid
    FROM
         schedule SCH
    JOIN rooms R USING (roomid)
    ORDER BY
        SCH.starttime, R.sort_order;

The result of that SELECT statement, which is accurate:

┌─────────────┬────────┬───────────┐
│SCH.starttime│R.roomid│S.sessionid│
├─────────────┼────────┼───────────┤
│17:00        │2       │23         │
│18:00        │2       │13         │
│19:00        │2       │18         │
│19:00        │3       │17         │
└─────────────┴────────┴───────────┘

What I want to do is use that result to update the sch_order field in the schedule table with an incrementing number.

Is that possible? If so, how would I do it?

Here's the SQL Fiddle for the above.

[ETA] Complicating factor: I'm on a hosted server, and I don't have temporary table privileges.


texts: Should I create new table for each User to store their product List?

texts: I'm creating a Web based billing and inventory management system. I need to store the product details such as name, price, stock left and also want to store details of every bill for each user uniquely. Should I create separate table for every user to store these information?


texts:
In my vps, I thought of switching from MySQL to MariaDB (using Monty's repository for Debian Squeeze). Copying over my.cnf from MySQL to MariaDB, I saw that the new config file (the one from the MariaDB package) does not mention any plugins commands like the old one from MySQL, where I loaded the InnoDB plugin. Now, I understand that XtraDB has become the default ACID storage engine in MariaDB, in place of InnoDB. Since I find MariaDB site's documentation lacking in this area, do I need to put something extra in the new my.cnf to activate XtraDB or is it active by default? show plugins returns this:

MariaDB [(none)]> show plugins;
+--------------------------------+--------+--------------------+---------+---------+
| Name                           | Status | Type               | Library | License |
+--------------------------------+--------+--------------------+---------+---------+
| binlog                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password          | ACTIVE | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password             | ACTIVE | AUTHENTICATION     | NULL    | GPL     |
| MyISAM                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| partition                      | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                        | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                      | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| CSV                            | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                      | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| Aria                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                     | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| PBXT                           | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| PBXT_STATISTICS                | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| InnoDB                         | ACTIVE | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_RSEG                    | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_PAGES       | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_PAGES_BLOB  | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TRX                     | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS                   | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS              | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                     | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET               | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                  | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET            | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLE_STATS             | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_INDEX_STATS             | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| XTRADB_ADMIN_COMMAND           | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES              | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES             | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_STATS               | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
| XTRADB_ENHANCEMENTS            | ACTIVE | INFORMATION SCHEMA | NULL    | GPL     |
+--------------------------------+--------+--------------------+---------+---------+
33 rows in set (0.00 sec)

What do I understand from the output above? That InnoDB=XtraDB?


texts: How to get Page Name given Page ID in Oracle Application Express

texts:
Prefix: I have looked all over the net for this answer. Let me prefix the question by describing what I am trying to do: I am trying to create a unique list of shortcuts to pages in my application for each user. So I am wanting to use a dynamic list to query for shortcut list. I already have procedures in place and ready to get the user ID and the page ID. I am using apex 5.0

The Question: What is the table and column (if it even exists) I need to query for in order to get the page name if given the corresponding page ID. ie:

select UNKNOWN_COLUMN_FOR_APEX_PAGE_NAME
from UNKNOWN_TABLE_FOR_APEX_PAGE_INFO

Alternate Question: Is there an apex global variable like :APEX_USER or :APEX_PAGE_ID that I can use to get the page name/title.


texts: Improve performance with the WHERE NOT IN sub-select clause

texts:
In the following query, I have to count transactions for each customer.  [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year.

Shouldn't the query optimizer be smart enough to only evaluate existence once for each customer?

--Count transactions on customers that are less than 1 year old

  SELECT t1.CUSTID,COUNT(*)
  FROM CUST_TRX t1
  WHERE NOT EXISTS (
    SELECT FIRST 1 1
    FROM CUST_TRX t2
    WHERE
      t2.CUSTID=t1.CUSTID AND
      t2.DATED<CURRENT_DATE-365
    GROUP BY t2.CUSTID
  )
  GROUP BY t1.CUSTID

There are no naturals in my query plan.  This query is performing as if the database is running the existence clause for every transaction instead of running it for every customer.  Performance is the same if I remove the GROUP BY in the sub-query.

Is there a better way to do this so that I may get better performance out of the database?  Hopefully a simple select query will work avoiding a CTE if possible (that would introduce other challenges).  Due to other group by criteria (not shown here) I'm not able to simply check MIN(DATED), I really need to perform another query.


texts: How can I rewrite as Latest Developer a record which was created as LATEST DEVELOPER via SQL query?


texts: SqlAnywhere 12- service started but stopped

texts:
I've been running my *db file on Windows 7 platform. Rrcently I have encountered a problem where I try to revoke the SqlAnywhere server but I received this error:

  The Sql Anywhere- myserv service on local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

Kindly help.


texts: Measuring timing over many commands in psql

texts: The \timing command finds time needed for each individual update/query. But I have a .sql file with 10000 inserts and I want to see how much time it takes. How can I achieve this?


texts: MySqli - select from db based on time difference with CURTIME() or SUBTIME()

texts:
I have a db with some customers data. There I store their last login time as datetime.

I want to run a cron php script every min which will query mysqli for their last login datetime and if that was more than 20 mins in the past i will run some js popup etc (something like NOW() - last_login).

Anyway I am trying to query the db directly for that kind of users based on that 20 min time difference.

With php it's easy to filter results but I want to bring directly results from my mysql db without any intermediate process from php.

How can I accomplish this?


texts: Data modelling: RBAC for small enterprise software

texts:
I'm building the core tables for a small, cloud-based enterprise app.

Is this data model suitable for a role based authentication system (flat hierarchy of permissions) that allows users to work for multiple companies?

Update

As pointed out by Joel Brown, I didn't state my exact requirements. Here there are:

This app is for a startup and specs change quite often, so the model should be flexible and expandable. The underlying idea is to create enterprise software (think ERP).
One User can work for multiple Organizations with multiple Roles & Permissions (imagine a chain of hotels, each hotel is an Organization, and the top management of the chain has access to various Organizations)
Permissions can be flat (ie no hierarchy).


texts:
I am working on figuring out partial and transitive dependencies.  The Transitive ones are really confusing me because I can see a transitive dependency on nearly all of these.

The partial dependencies are Agree_No -> Agree_Date, Cust_No -> Cust_Name and Cust_Phone; Serial_No -> Item_Name and Item_Rate.

The Transitive Dependencies that I see are Item_Rate -> Item_Name.  But could Cust_Phone be a transitive to Cust_Name and the Agree_No/Date?  Are there others I am missing?


texts: Developing an Azure Sql Database backup strategy

texts:
I know Azure databases are backed up and kept for a retention period, which I believe the default is 30 days.  I'd like to come up with a plan to mimic our current strategy with a physical SQL Server box.  I'm also open to other strategies, but the main goal is if a customer says "I think my project was good on March 3, 2016" I can find the closest possible database quickly if their query was within the past year, and also find a close backup, but less quickly (and more cost effective).

Our largest database is around 4GB and most others are very tiny.  It is quite possible that the "hot" storage, which is quickly accessible might be so cost effective that I need not deal with "cold" storage such as Amazon Glacier.

Right now we have backups that occur every 15 minutes.  But we also do full backups:

Every day
The first day of every week
The first day of every month

I mostly use these .BAK file backups for user error data recovery or I download the file to run on a local instance for development purposes, mostly debugging or feature development.  I probably could also just restore the backup to a temporary database on Azure and use the connectionstring from a development box, but I would like to make sure I can get a .BAK file as well.

I keep the daily, weekly, and monthly backups on a backup drive attached to the Sql Server so I can quickly grab any backup in the prior 30 days, any weekly backup for the prior 3 months, and every monthly backup for the past year.

I also take a monthly backup and upload that to Amazon glacier so I can retain those forever (maybe we will start dropping them after 5 years)

Now I believe (correct me if I'm wrong) that with Azure, if I use 30 day retention I can restore a backup from almost any point within the 30 day window.

I'm looking for a way to replicate this entire strategy on Azure.  Are there built in Azure services for this, or would I need to create a VM and have scheduled scripts?  What about third party products such as Cloudberry that would make managing backups simple?  This includes seeing and retrieving both my "hot" and "cold" backups.


texts: Counterexample to demonstrate there are no functional dependencies

texts:
For instance in the following example

Let be F={AB→C, B→D, CD→E, CE→GH, G→A}
Do we have AB→G?

We don't have any functional dependencies.
I am able to show a functional dependency when it works but how to show a counterexample when it doesn't?


texts: What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?

texts:
There is a long and quite elucidating answer on the differences between

TIMESTAMP WITH TIME ZONE
-vs-
TIMESTAMP WITHOUT TIME ZONE

available in the SO post Ignoring time zones altogether in Rails and PostgreSQL.

What I would like to know is: Are there any valid use cases for actually using TIMESTAMP WITHOUT TIME ZONE or should it be considered an anti-pattern?


texts: Big jump in search time for Postgres Index query for results with high selectivity

texts:
I am doing some performance comparison of databases and lucene for full-text searching.

So I use Postgres to create an Index for the data to search:

CREATE INDEX bodies_index
  ON bodies
  USING gin
  (to_tsvector('english'::regconfig, body));

and to query it I use

SELECT *
FROM bodies
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', '" + searchterm + "')"

The results are good for almost all database sizes but there is one exception.
It takes up to 10x longer for the biggest database of 250 000 records,
but only for results with a high selectivity.

Any ideas how this could happen?

EDIT:
My PG version is 9.2.

Here is the result for EXPLAIN ANALYZE for a fast result:
(17k results in under 100ms)

http://explain.depesz.com/s/i1F

And this one is much slower, despite only a small rise in the number of results
(28k results in over 50 seconds)

http://explain.depesz.com/s/WwpX


texts: MySQL - Automatically Replicate structure-only from DB1 to DB2 on the same host?

texts:
I have two databases:

- production
- development

Is there a way I could set a replication schema in order to replicate any structure changes from production to development? Preferably, structure only, no data replication.

Thanks


texts: Using Left or substring with wildcard in SQL

texts:
I am looking to retrieve information from a field, but am having troubles getting exactly what I want.  The information I want has no set order and I am also looking for two different things.  Here is an example of the information I am working with.

|PEvent|   PDate     |         PDescription                          |
|  3  | 2014-11-21 | Padding, 90 Series, Stage, SF550                |
|  4  | 2014-11-21 | Padding, 45 Series, 144 Stage SF90, 15 spor, AR |
|  5  | 2013-03-06 | 400 Series, 96 STAGE RE FLEX 30                 |
|  6  | 2015-01-09 | Padding, 20 Series, 13 Stage SF 550, Housing    |

What I want to get is SF and the number and FLEX and the number that follows it, in these cases SF550, SF90, SF 550 FLEX 30.  I would also like to get FLEX and the number that follows it.  The problem I am having is that there are so many differences in the data.  I cannot say 10 spaces from the left since it is not always 10 spaces.  I have the same problem from the right.  The other issue I have is that sometimes SF and FLEX will be right against the number where other times there will be a space in between them.

I have created a SQL Fiddle sample database with more records for testing.

SQL Fiddle


texts: one to many relation how to create a new row with mysql trigger

texts:
i'm really new to mysql triggers so...i have 3 tables: a,b and c. table a is related with table b with a one to many relaion and table c is the table that get the triggered infromation from the 2 other tables.

 table a
 ------------------
  id     |    name
   1     |    Juan
   2     |    Pete
   3     |    David

 table b
 ----------------------
  id  |  name   | table_a_id
   1  |  Dog    |     1
   2  |  Cat    |     2

Table C gets the information from Table A with a "after insert on A
for each row insert into C "also gets information From table B with a "after insert on B for each row update C".

 table c
----------------------
id | name a | name b | table_a_id |table_b_id
 1 |  juan  |   Dog  |      1     |      1
 2 |  Pete  |   Cat  |      2     |      2

The problem occurs when Pete wants to have Cat and Dog at the same time because it will just update Pete's information. What i want is a way in which i can have a new row with all the information.

      table c
     -------------------------------------------
    id | name a | name b | table_a_id |table_b_id
     1 |  juan  |   Dog  |      1     |      1
     2 |  Pete  |   Cat  |      2     |      2
---->3 |  Pete  |   Dog  |      2     |      1

This 3rd row from table C wont be created it will just update row 2 from table C. Please tell e if i was clear enough.


texts: How to get all column names of a table in in multiple rows

texts:
I have used the following to just view the column names of a table (without any values) Is there a way to view all column names in one column but separate rows? Maybe do a lateral join? Open for suggestions. It is for exploratory purposes only.

This is what I used so far:

select *
from fi.questionnaire
where false;