texts:
I have a view which is accessed via DBLink in Oracle. This view is made up of different tables. Now we have to execute queries on this view. Example:

Select * from Viewname@dblink where columnname='somevalue';

Now this view contains millions of records. Even this simple select statement with a where clause takes a long time. This query consumes more CPU utilization.

We received some suggestions such as put the query in the functions (since they are precompiled).

But by putting the query in the function will it really make a difference?


texts:
I am trying to emulate MERGE behavior with pl/pgsql:

-- Generate the data from funtion
CREATE TEMP TABLE temp_x  (id int, id2 int, data text, created_at timestamp, updated_at timestamp)  ON COMMIT DROP;
INSERT INTO temp_x SELECT * FROM set_gernating_function(p);

-- DELETE record with same id2
DELETE FROM x WHERE NOT EXISTS (SELECT 1 FROM temp_x WHERE temp_x.id=x.id) AND id2=p.id2;

-- UPSERT by (id, id2)
INSERT INTO x
    SELECT * FROM temp_x z
  ON CONFLICT(id, id2) DO UPDATE
    SET
      updated_at=excluded.updated_at,
      data=excluded.data;

But the temp table usage is generating bloat in pg_class and pg_attr table very fast -- Faster then I could do vacuum -- and affect other queries. Any idea?

There are some restrictions:

Preserve the created_at time if the id already exist
x have a delete-trigger, so no unnecessary delete.
The set_generating_function is slow
The size for temp_x is small. (< 50)
The run rate is very high (thousands per second)


texts: Why ephemeral port been used when connect to AWS RDS?

texts:
Among multiple accounts in AWS, from one EC2 in account 1 connect RDS in account 2, from the VPC flow logs, got this message
2 023910321035 eni-12309fdsaj0jf012 ACCOUNT_1_EC2_IP ACCOUNT_2_RDS_IP 45052 31337 6 3 180 1619490209 1619490213 REJECT OK

The port 31337 hasn't been opened but only the allowed db port 3306. Why it connected this unknown port first? How to diagnose it?


texts: Execution Plan : Table Insert Cost 90% vs Clustured Index Insert Cost 86%

texts:
Situation : We have table It has millions of records. Table has 1 Clustured Index and 1 Non Clustured Index. It was found that Clustured Index is not used by any of SQL Query. But Insert Execution plan has 86% cost on Clustured Index.

Action: If I convert Clustured Index to Non Clustured Index, Execution plan gives me Table Cost 90%.

Which one is better for performance, If I convert clustered index to non clustered index, will it be helpful.

Thanks, Virul


texts: How to create a blank version of a PostgreSQL database

texts:
I've been using pgAdmin to create a database and test it.

I'm at a point now where I need to install this db on different servers.  I've been using pg_dump to do this and I've been able to muddle my way through.

But I'm wondering if there's a way to "reset" all the "current" values in any sequence that I have set up?
If you have any other general suggestions on how to create "empty" or "blank" databases, can you let me know?


texts: How can I optimize performance on a MySQL ndbcluster?

texts:
So, this is what I have:

A simple table classes__to_students:

|class   |student|
---------------
 Math     Alice
 Math     Bob
 Math     Peter
 Math     Anne
 Music    Bob
 Music    Chis
 Music    Debbie
 ...

There's 1000000 classes. Every student attends 500 classes.
(Bad analogy, I know...)
For testing, there's 4000 students in the DB (so there is 2.000.000 rows), but this DB should handle several million students, that's why I'm using a MySQL ndbcluster.

Anyway... a query like this:

SELECT student, COUNT(class) as common_classes
FROM classes_table
WHERE class IN (my_subject_list)
GROUP BY student
ORDER BY common_classes DESC

resulting in something like:

|student |commonClasses|
 Brad     6
 Melissa  4
 Chris    3
 Bob      3
 ...

takes about 1 second with a InnoDB engine on one server, which is ok. On a ndbcluster with 3 datanodes the same query takes up to 10 seconds, which is far too much. I don't know how the above statement is treated internally, but I guess that there is a lot of communication between the nodes, which makes it slow due to latency.
Can someone tell me, what happens in the cluster when I perform this query? How can I make it faster?

Note: this is a question that came up after I posted this question:
https://stackoverflow.com/questions/9095054/how-do-i-compute-a-ranking-with-mysql-stored-procedures
For more informations, have a look there!


texts:
I am running a replica-set in production with slaveOk = false using mongo 2.0.7 in AWS The replication lag on one of the servers is close to 58 hours. This replication lag is sometimes decreasing ( at very slow rate ) and sometimes increasing, but overall its lag is increasing 1-2 hours per day.

I restarted the server, but seeing no benefit
The read rate is 10 times of the read rate, compared to the other secondaries.
I checked the logs, but nothing weird
The lock % is very high ( close to 100% ) only on that server( and there is no such issues with the capped collection ). Other secondaries hardly have 10-20 lock%

One more weird thing that I noted about this server ( secondary ) is that mms is showing the version to be 2.2.1 and type as 'standalone' server, which is not the case ( cross checked using db.version() and rs.status() command )

can someone suggest something ?


texts: Oracle set schema in a PreparedStatement

texts:
Using an Oracle Database through a Java application, I need to execute a SET CURRENT SCHEMA statement using a PreparedStatement to overcome potential SQL injection.

So, I cannot do

public void executeSetCurrentSchema(Connection con, String schema){
  try{
     Statement stmt = con.createStatement();
     stmt.execute("SET CURRENT SCHEMA " + schema);

  } catch(Exception){}
}

but instead I'd like to use a prepared statement, that won't accept a "SET" command, instead I can do a "SELECT setCurrentSchema(" + schema + ")" if such a function would exists.

Is there any function I can issue using a SELECT to set the schema?


texts: Difference between init.ora db_name and query in sqlplus

texts:
I have a question about the content of the file init.ora, according to oracle documentation.

  DB_NAME specifies a database identifier of up to 8 characters. This parameter must be specified and must correspond to the name specified in the CREATE DATABASE statement.

In my understanding the command sqlplus knows to which database connect because it reads the information from the init.ora file.

My init.ora file is as follow

db_name='ORCL'
memory_target=1G
processes = 150

I think is the default one, I would think that the name of my database would appeared in the db_name parameter, then I perform a query in sqlplus to get the name of the database and the result is

My question is why the name is BO1 if in the init.ora is ORCL?
By the way I don't have any other init.ora

Thanks in advance


texts:
Here is the scenario: lets say I have a database named sqlprac that contains a table named dept.

There are two ways to write a query:

use sqlprac
/*1*/
SELECT dept.deptno,
       dept.deptname,
       dept.deptsal
FROM   dept;

/*2*/
SELECT deptno,
       deptname,
       deptsal
FROM   dept;

Both queries should return the same result set. However I have seen that this isn't always the case, especially in sub query/inner query. What's the difference then?

Why should I choose one over another?

Also, a third query might be

/*3*/
SELECT *
FROM dept

I know what * is doing, but in which case we should or not use it.

Are there any performance/optimization issues?


texts: Conceptual Design of a data warehouse (declaring the grain)

texts:
I am trying to design a data warehouse using this example scrubbed data. I need to declare the "grain". I know its the lowest level of detail in the fact table, but I am having a hard time determining it. Any help would be appreciated. (Sorry about the table column alignments - i tried my best to get them aligned)

TABLE: Banks

Bank_ID  Bank_Name  ABA_Code        Bank_City   Bank_State   Recip_Zip
1       Bank of NY  102938475    Albany        NY       12204
2       Bank of NJ  283746591    Englewood  NJ      07631
3       NY Federal  564738290    Albany     NY      12204
4       NJ Federal  123987475    Englewood  NJ      07631
5       NY Credit   654321789    Rochester  NY      14602

Banks consist of one or more accounts.

TABLE: Accounts

Acct_ID Acct_No          Bank_Id    Title
1            102938458  1   Subscriber Payment Acct
2            918273645  1   Provider Payment Acct
3            228374659  2   Zero Dollar Acct
4            374658291  2   BCB Provider Acct
5            234567891  3   BCB Subscriber Acct

Accounts are where the payment checks o electronic payments for drawn from.

TABLE: Recipients

Recip_ID   Recip_Type   Recip_No    Recip_Title   Recip_City    Recip_State   Recip_Zip
1         P          100001 Art Gains     Albany    NY       12204
2         S          220202 Mick Tean     Englewood NJ       07631
3         S          103003 Jake Adam     Binghamton    NY       13905
4         P          404004 Jane Doe         Rochester  NY       14602
5         P          977005 Jac Angel     Belvidere NJ       07823

Recipients (of a payment) can be Providers (P) or Subscribers (S)

TABLE: Payments

Payment_ID  Acct_ID Check_No    Check_Amt   Check_Date  Recip_No
1   1   102938475   6919.70 06/17/2011  100001
2   1   102938476   23.50   09/09/2011  220202
3   2   459287654   3.12    01/21/2012  404004
4   2   459287655   17345.01    07/07/2012  977005
5   3   102938476   300.47  02/11/2013  103003

Payments are master records and are the highest level of transaction. They consist of one or more claims.

TABLE: Claims

Claim_ID    Payment_Id  Claim_Amt   Claim_Date  Claim_Source
1   1   3459.85 06/01/2011  AA
2   1   3459.85 06/15/2011  AA
3   2   23.50   01/02/2012  AA
4   3   100.00  01/28/2012  BB
5   3   200.47  02/01/2013  BB

Claims consist of one or more Line items (services rendered by a provider)

TABLE: Claim Line Items

Item_ID Claim_ID    Start_Date  End_Date    Proc_Code   Proc_Desc   Billed_Amt
1   1   05/01/2011  05/01/2011  30.01   CRM NO  1230.25
2   1   05/01/2011  05/01/2011  30.02   ARB DD  1950.00
3   1   05/01/2011  05/01/2011  30.12   DSJ 01  279.60
4   2   12/15/2011  12/15/2011  99.98   LLL SS  729.95
5   2   12/15/2011  12/15/2011  99.99   IT2 GG  2729.90

Line Items are (services provided) the lowest transactional level detailing the procedure and cost.


texts: Getting vacuumdb :invalid page header in block error while vacuuming

texts:
I am newbie to PostgreSQL and using version 9.2.Disk space for data folder is full causing DB to stop. When i am trying to perform a vacuum to reclaim space, i am getting below error.

  vacuumdb: vacuuming of database "mcfdb3" failed: ERROR:  invalid page header in block 15871 of relation base/17148/30976

Can anybody help?


texts: Can not drop a PostgreSQL database using dropdb?

texts:
I can not drop a database using

dropdb

I get this error:

 ERROR:  must be owner of database

but with psql \l I see user admin (the one dropping the db) with

 admin=CTc/postgres

besides the database I want to drop.

What is wrong in PostgreSQL 9.x in Linux?


texts:
I am troubleshooting a problem that seems to be random

The problem is sometimes an application (dynamics SL) is freezing

When the application is freezing , I notice there is a blocked by in SQL activity monitor
and a head blocker

in my limited understanding , head blocker means a session is currently running and is locking a resource and that resource is also needed by another session. so all sessions that need access to that resource cannot continue thus the "blocked by" that I see in activity monitor

my question is
in five cases that I have seen is that the head blocker task status is actually blank. usually a task status is "running" or "suspended"
now how can a session that is not running becomes the head blocker?

I am thinking my understanding of head blocker maybe wrong. but please enlightened me ELI5 style :)

each of the 5 cases. I just kill the process of the head blocker. then everyone is not freezing anymore.

I am hoping to identify the source of this head blocker and come up with a solution

When I click the details of the head blocker. all I see is

select @@identity

please advise?

thank you


texts:
I need to show the running win/loss streak per row in a query, so given the table below, the query should return the "expected" column. I've tried some approaches with window functions, but no success.

create table matches (player text, dt date,  is_winner boolean, expected integer )
insert into matches values
('A', '2019-01-01', TRUE, 0),
('A', '2019-01-03', TRUE, 1),
('A', '2019-01-04', TRUE, 2),
('A', '2019-01-09', FALSE, 0),
('A', '2019-01-10', FALSE, -1),
('A', '2019-01-15', TRUE, 0);

player  dt          is_winner   expected
A       2019-01-01  true        0
A       2019-01-03  true        1
A       2019-01-04  true        2
A       2019-01-09  false       0
A       2019-01-10  false       -1
A       2019-01-15  true        0

The logic is:

Resets to 0 when winning after a loss, or losing after a win.
Increments after a win, but not if it's case 1.
Decrements after a loss, but not if it's case 1.

Any insights on how to tackle this are welcome. My last resort would be a function with a loop called by every row.


texts: HA and Automatic Failover with PostgreSQL

texts:
The most popular third-party tools are pgpool and regmgr.

pgpool can do HA and automatic failover, regmgr can also do replication and automatic failover. Some articles show using both of them to do in the cluster. Why? What's the best practices go to the goal?


texts: creating PKs for existing tables - altering column constraints

texts:
I am quite new to DB2, so I googled around how to ask questions correctly and so I hope I can provide all necessary information.

I have an existing table A1230922.CALSONW with several (195) columns but no primary key. Now I want to connect an ORM that needs a primary key, I guess for row identification.

Googling brought me the result to use the following queries:

add a column with NOT NULL, and DEFAULT 0 (works fine):

ALTER TABLE tablename ADD COLUMN orm_id INTEGER NOT NULL WITH DEFAULT 0;
add automatic increasing key values (does not work, as it is not compatible with column):

ALTER TABLE tablename ALTER COLUMN orm_id SET GENERATED BY DEFAULT AS IDENTITY;

I only have German error messages (and I cannot change the interface language), saying that the attributes are not compatible with the existing column.

When I try to remove the default 0, or the NOT NULL, I get the error message (translated by myself):

ALTER table A1230922.CALSONW alter column orm_id DROP NOT NULL

An error occurred during the implicit system action "3". The returned information conaint the SQLCODE "-421", the SQLSTATE "42826", and the message token "[...] full documentation.

The docs for this SQLCODE and SQLSTATE say:

Short Description: THE OPERANDS OF A UNION OR UNION ALL DO NOT HAVE THE SAME NUMBER OF COLUMNS
The operands of a UNION or UNION ALL must have the same number of columns. System action: The statement cannot be executed. Programmer response: Correct the SQL statement so that there are exactly the same number of columns in each operand.

Obviously, I am not doing anything with UNION in this context.

Next steps would be to REORG TABLE, SET orm_id = DEFAULT, and ADD CONSTRAINT pk PRIMARY KEY(orm_id).

Version information:

Client: Win 7, 32 Bit, DB2 code release"SQL09075", "08060107".

Version: "DB2 v9.7.500.702", "s111017", "IP23286", FixPak "5".

Server: Win XP (yes, development only), exactly same version


texts: Generate database diagram from SQL Server with no proper integrity

texts:
So... I've ended up in a project, which by far is the worst quality POS I've seen in a long while. It is basically a Visual Basic .NET windows client application used for various administrative tasks and print outs.
It is using inline SQL (concatenated sql-strings of course) and stored procedures all over the place. All reports have their own embedded SQL.
On top of this it is joining in tables from four different databases, two of which is clearly some reverse engineered attempt to integrate two standard systems not meant to be integrated at all.
It's even updating data in these other systems.

OK.
With some sort of hope of a complete rewrite in the near future I'm trying to make sense of this system. My plan is break it down into use cases, and document these from a database/integration standpoint.

Is there, some tool, that's using a SQL-profiler data dump or similar to "reverse engineer" a database diagram. I'd like to see some sort of graphical representation or anything at all to straighten out this spaghetti...

(Or any other ideas or help on how to approach this would be helpful!)


texts: mysql: make the server fail instead of just giving warnings

texts:
I have a mysql 5.7 server.

I am trying to import some .csv files.

Sometimes it may so happen that these are malformed.

Here is a sample case:

  - 'mysql: [Warning] Using a password on the command line interface can be insecure.'
  stdout: |-
    Warning (Code 1366): Incorrect integer value: ' are not separated per hour."' for column 'story_points' at row 10
    Warning (Code 1262): Row 10 was truncated; it contained more data than there were input columns
  stdout_lines: <omitted>

What I noticed is that the row was indeed somehow transformed and inserted (although in a bad way).

How can I prevent this behavior? I want the entire command to fail in such cases cause I cannot selectively roll back such malformed insertions.


texts: In MongoDB serverStatus(), what is the meaning of "connection data handles currently active"?

texts:
When running db.serverStatus() in the MongoDB shell (3.4, WiredTiger), the results contain the following data:

{
    "connection data handles currently active" : 94862,
    "connection sweep candidate became referenced" : 0,
    "connection sweep dhandles closed" : 91885,
    "connection sweep dhandles removed from hash list" : 648514,
    "connection sweep time-of-death sets" : 1514067,
    "connection sweeps" : 40077,
    "session dhandles swept" : 110175,
    "session sweep attempts" : 710643
}

What is the meaning of "connection data handles currently active"? Does it have an impact on the instance's performance?


texts: Unable to open SQL Server Config Mgr, get a WMI error

texts:
I got a WMI error from SQL Server Configuration Manager after I ran sp_dropserver and sp_addserver commands.

Various posts suggested I run the mofcomp command.  I did this, and got the following errors.  I did run it using administrator privileges, and we are on SQL Server 2008 R2, so "100" should be correct.

C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp sqlmgmproviderxpsp2up.mof
Microsoft (R) MOF Compiler Version 6.2.9200.16398
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: sqlmgmproviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
An error occurred while opening the namespace for object 1 defined on lines 4 - 7:
Error Number: 0x80070422, Facility: Win32
Description: The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.
Compiler returned error 0x80070422
C:\Program Files (x86)\Microsoft SQL Server\100\Shared>

Any assistance is much appreciated. I am stumped as to what to do next. The instance is up, but I cannot stop or restart it even from SSMS (options greyed out).

History of what happened, more details and error message as suggested.

SA "cloned" the entire box A to new box B (not actual names, of course).
I was on box B and needed to change name of instance from A to B so
that developers could connect to a "new" instance (B).  Remember, this
is a clone of A, so the instance name was A even though I was on
the physical box B.  Using SSMS, I connected to the database using name
A, because that is what I saw as the server name in the connection box.
OOPS!  I was actually connected to instance A on box A, not B, but
I did not know that at this time. I ran dropserver A, addserver B
and then restarted instance (remember, I am on box B).  The change
did not appear (I now know why, the change happened on box A!).

Discovering my mistake, I go to box A and see that the instance name
is now B (output of sp_helpserver).  I run dropserver B, then addserver
A to "fix" it (put it back to A), then I tried to run SQL Config Manager
(as administrator) to "enable" the rename. That is when I get this error:

Cannot connect to WMI provider.  You do not have permission or the
server is unreachable. The service cannot be started because it is
disabled or because it has no enabled devices attached to it.
Error 0x80070422.

I tried to fix this using the mofcomp command I mentioned in original
post.  The error I got from that seems to match the error above when
I run Config Manager.

When I now run sp_helpserver on A, I still see instance name as B.  However,
the SSMS connection box shows instance name as A, and it cannot connect
to instance B (not found).  The output of SELECT @@SERVERNAME AS 'Server Name';
is instance name A.  So, there is a discrepancy between what @@SERVERNAME
shows and what sp_helpserver shows.


texts:
I'm having a strange problem with my SSMS 2016. Within the query window backspace, del, up, down, left, right keys have stopped working. When I reset my environment settings with "Import and Export Settings" to default setting it works but the changes don't persist when I exit the SSMS client. Every time I launch SSMS 2016 I have to do this exercise.

I also have SSMS 2014 on the same system, which works with out any problem, I tried to uninstall and install the SSMS 16 client but no luck.

Any one having the same problem?

Help please, how can I uninstall SQL Server Management Studio 2016 completely?


texts: How to retrieve foreign key constraints data

texts:
I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema.

I've found this, but does not gives all the info I need:
https://stackoverflow.com/questions/4389228/sql-for-oracle-to-check-if-a-constraint-exists

I'm currently working on it and may end up with a solution in the next minutes/hours. But if someone has already a full working solution, I'll be glad to know it :)


texts: Massive update to Clob fields oracle 10g

texts:
I need to remove a string of characters from 2000+ clobs fields in oracle 10g. How would I do this in PL/SQL?

For example, in table t1 there is a clob field called VBase that needs to have the string ".magic()" replaced with " ".


texts:
This is my procedure:

     DELIMITER $$
     CREATE PROCEDURE sp_test_final6()
     BEGIN
    INSERT INTO `chef_ratings`(`chef_id`,`avg_total_no_votes`,`avg_total_rating`,`no_of_votes`,`avg_rating`)
    SELECT `chef_id`,(SELECT count(chef_id) FROM rates)/( SELECT count(DISTINCT chef_id) FROM rates),
    (( SELECT sum((`question_1` + `question_2` +`question_3` )/3) FROM rates ) / ( SELECT count(chef_id) FROM rates)),
    ( SELECT count(chef_id) FROM rates ),avg((`question_1`+`question_2`+`question_3`)/3)
    FROM rates GROUP BY `chef_id`;
    END $$
    DELIMITER ;

I'm getting error :

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCEDURE sp_test_final6()
BEGIN
INSERT INTO `chef_ratings`( `chef_id`, `avg_t' at line 1

Any idea why it is failing?

Edited: It works fine in mysql version 5.5 but in Server version: 4.1.22 it is giving this problem.  How we can fix without upgrading?


texts: What to do when your Always On cluster loses quorum?

texts:
I was reviewing our company DR procedures and when I looked online for solutions to an Always On Cluster losing quorum, to compare to.  I was three pages into google results before finding the first SE post on the subject Clustering vs. transactional replication vs. availability groups which only lightly touches on the subject of lost quorum.

While everyone agrees the losing quorum is bad, and there are some suggestions for decreasing the potential, it can still happen.  I am looking for a good peer reviewed answer to the best path to recovery from an Always On cluster loss of quorum.


texts: Why are schema changes I've made reverting?

texts:
I have been experiencing issues when making schema changes/stored proc definition changes. The changes that I make are not always persistent. I can make a change today then a day or two later I'll find out that the bug the change was fixing is still occuring. When I check the schema/stored proc my changes are nowhere to be found it will be just as it was before. Then I make the change again and usually it takes the second time around, but there have been a couple of instances where the second change has also reverted.

We're running SQL Server 2008 (10.0.6000.29)

I just wanted to find out if anyone else may have experienced this or might have a suggestion for a solution.


texts: MySQL - Difference between using count(*) and information_schema.tables for counting rows

texts:
I want a fast way to count the number of rows in my table that has several million rows. I found the post "MySQL: Fastest way to count number of rows" on Stack Overflow, which looked like it would solve my problem. Bayuah provided this answer:
SELECT
    table_rows "Rows Count"
FROM
    information_schema.tables
WHERE
    table_name="Table_Name"
AND
    table_schema="Database_Name";

Which I liked because it looks like a lookup instead of a scan, so it should be fast, but I decided to test it against
SELECT COUNT(*) FROM table

to see how much of a performance difference there was.
Unfortunately I'm getting different answers as shown below:

Question
Why are the answers different by roughly 2 million rows? I am guessing the  query that performs a full table scan is the more accurate number, but is there a way I can get the correct number without having to run this slow query?

I ran ANALYZE TABLE data_302, which completed in 0.05 seconds. When I ran the query again, I now get a much closer result of 34384599 rows, but it's still not the same number as select count(*) with 34906061 rows. Does analyze table return immediately and process in the background? I feel its worth mentioning this is a test database and is not currently being written to.
Nobody is going to care if it's just a case of telling someone how big a table is, but I wanted to pass the row count to a bit of code that would use that figure to create a "equally sized" asynchronous queries to query the database in parallel, similar to the method shown in Increasing slow query performance with the parallel query execution by Alexander Rubin. As it is, I will just get the highest id with SELECT id from table_name order by id DESC limit 1 and hope my tables don't get too fragmented.


texts: How to reference a table called `\` (simply just backslash character) in MySQL in a query?

texts:
Through some unknown circumstances I have a table called "\" in a database.

mysql> show tables like '\\';
+-----------------------------+
| Tables_in_in_realestate (\) |
+-----------------------------+
| \                           |
+-----------------------------+

On the disk the files are called @005c.

user@host:mysql/database/$ ls @005c.*
@005c.frm  @005c.ibd

I want to rename it to something more manageable, but I can't reference it in a RENAME TABLE query.

mysql> RENAME TABLE `\` TO `backslash`
    `> `          <-- it thinks there is an open quote
    -> \c         <-- so I just cancel the query

I thought I could escape the "\" with another "\" but that doesn't seem to work either:

mysql> RENAME TABLE `\\` TO `backslash`;
ERROR 1017 (HY000): Can't find file: './in_realestate/@005c@005c.frm' (errno: 2 - No such file or directory)

How can I reference the table in my RENAME TABLE query?


texts: Determine ANSI_DEFAULTS setting ON or OFF?

texts:
How can I determine if ANSI_DEFAULTS is enabled or not on my server. My server is using IMPLICIT transactions and one way from which it can affect it based on setting of ANSI_DEFAULTS.

How can I determine state of ANSI_DEFAULTS?


texts:
I am having problems trying to audit a third party app with a SQLServer backend.

The app manages users and user access via a SQL table, using a single SQL login to access the databases.

I am trying to audit this by using the Host PID much like Windows Task Manager associates a PID and Owner for every process.

Here is what I have attempted and tried

I was able to figure out how to extract the app.exe PID.
I cannot figure out how to get the Windows Owner associated to that PID.
I have tried using xp_cmdshell to query the Windows tasklist and even wrote a .Net console app which gets called by SQL to collect the information but every time I try to extract the Owner it is blank.

Any thoughts on how I can get the Owner?


texts: How to create a differential backup considering the base the previous differential backup in SQL Server?

texts:
I am studying differential backups. I know that I can create a differential backup which will contain all the modified data from last full  backup.
I wonder isn't it possible to create a backup from the last differential backup?
I mean to create a base full backup, then differential backup1, which contains all the data modified from full backup, then create backup2 which contains modified data from backup1, not full backup.

If it is possible how can I do it? Also can I do the same only with Log file backups?

Thanks for your time.


texts: Need to configure Availability Groups between three different SQL failover instance

texts:
We have total 6 Nodes and 3 failover cluster (Each with two node).

Cluster1.TestAD.com(Node1,Node2) - SQL Failover Instance (SQL1) - 1
TB Shared storage
Cluster2.TestAD.com(Node3,Node4) - SQL Failover
Instance (SQL2)- 1 TB Shared storage
Cluster3.TestAD.com(Node5,Node6) - SQL Failover Instance (SQL3)- 1 TB Shared storage

Now we want to create availability group setup between above three SQL instance.

We plan to create new cluster (cluster4.TestAD.com ) with all six node and create Availability groups with all three instance.

SQL Edition : SQL Server 2012 R2 Enterprise

Is it possible? Any other recommendations ?


texts:
I have i'm installing oracle in machine 12c, while installing everything goes smoothly but when it reach to database creation it passes two step.

cleaning up failed stat
copying database file
creating and starting oracle instance

on the last point (creating and starting oracle instance) it gives me following error:

ora-12705 cannot access NLS data files or invalid environment specified

what I have tried is:

set env variable in windows to NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
and also I have go ahead and rename the register edited of NLS

those above point also mentioned in couple of search I did but still when I retry database creation I get same error all the time.

thanks for help


texts: Make Xtrabackup ignore myisam tables & backup only innoDB tables?

texts:
I'm using mysql 5.7 (or rather percona 5.7)

Is it possible to backup only the innodb tables (easily, not to specify each table one by one because I have a lot of tables) with xtrabackup? and not take into account myisam tables that are read-only?

If I remember right, if you have myisam tables it will lock the whole DB thus breaking the application during the backup procedure.


texts: select value between particular date and time schedule

texts:
I have database "raj" with table "pagination"

In table pagination have "id", "actualtime" and "created" field

id - auto_increment actualtime- varchar created- datetime

that looks like this
CREATE TABLE pagination(
    id int auto_increment,
    actualtime varchar(55),
    created datetime
)

I want to display all rows which are created on todays date and will display from today upto yesterday at 06:00 pm.
after that the content will be refreshed based on based on a DATETIME field called 'created' that holds the date and time of each record's creation.

this is my query to fetch rows but it display value after 06:00 pm on yesterday but i want to display all data before 06:00 pm from currentdate.  After 06:00 pm data will be refreshed and clear. plz help me......

SELECT actualtime
FROM pagination
WHERE created BETWEEN date_add(date_sub(curdate(), INTERVAL 1 day), INTERVAL 18 hour) AND curdate()";


texts: Sql Server Full Backup Duration Increased Dramatically

texts:
since last week My Full Backup Increased dramatically .before last week the time is about 3 hour and now the time is about 6 hour.in addition some query take long time to run.
(not all the query).I thought the problem is relate to disk I/o.
and I try to copy/past some files from one hard drive to another and it is done very quickly.(3 giga byte in 4-5 sec).and all the query work with temp table slow down.
is it relate to I/o or what..?


texts: Are online index REBUILD operation possible on every Azure SQL database?

texts:
I am trying to find informations about the possibility to operate online index REORGANIZE/REBUILD on a database.

What I actually found in the Microsoft documentation is that:

  Reorganizing an index uses minimal system resources and is an online operation.

  Depending on the type of index and database engine version, a rebuild operation can be done online or offline.

The documentation is also indicating that:

  Online index operations are not available in every SQL Server edition. For more information, see Editions and supported features of SQL Server.

If I follow the link giving more details about the differences between the multiple SQL Server editions, it says that "Online index create and rebuild" is only supported by "Enterprise Editions".

But now I would like to have more information about the possibility to operate online index REBUILD on Azure SQL database and I cannot find any explicit documentation page about it.

Is there any limitation on Azure SQL databases too?
Or online index REBUILD is possible for any Azure SQL database?

Does someone have some documentation link to share or anymore information about this specific point?


texts: After installing MariaDB, mysql_secure_installation produces error, cannot connect mysqld.sock

texts:
After installing MariaDB, mysql_secure_installation produces the error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

A review of /var/run/myssqld shows that there is no mysqld.sock file:
find / mysqld.sock produces
find: ‘mysqld.sock’: No such file or directory

Issuing a command sudo service mysql start produces:

jmysql ob for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

Issung mysql --version produces:
mysql Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

This appears to be a very common problem. I have reviewed 20(?) similar questions, and tried the solutions suggested.
OS - Ubuntu 20.04


texts: How to monitor or do vacuum without stop all?

texts:
I have postgres9.1

I try to run full vacuum on a specific table , but it run for more than 5 hours.
I stop all process against this table and i cannot have the process down any longer.

Is there a way to run vacuum in background?
Is there a way to monitor vacuum?


texts: Different column for different conditions when joining

texts:
I am using MySQL database and in that two tables, relation is one to many.
One table is product table, sample data:

+----+----------+
| id |   name   |
+----+----------+
|  1 | product1 |
|  2 | product2 |
+----+----------+

Other table is attribute table

+------------+----------+------------+
| product_id |   key    |   value    |
+------------+----------+------------+
|          1 | format   | mp3        |
|          1 | format   | audio      |
|          1 | category | English    |
|          1 | category | Vocabulary |
|          2 | format   | mp3        |
|          2 | format   | book       |
|          2 | category | Grammar    |
|          2 | category | German     |
+------------+----------+------------+

What i want to achieve is that my query should have different columns for different key used in attribute table.

Result:

+------------+----------+-----------+--------------------+
| product_id |   name   |  format   |      category      |
+------------+----------+-----------+--------------------+
|          1 | product1 | mp3,audio | English,Vocabulary |
|          2 | product2 | mp3,book  | German,Grammar     |
+------------+----------+-----------+--------------------+

So far i came up with the solution including joining attribute table twice, like as follows:

SELECT a.*,
GROUP_CONCAT(b.value,',') as format,
GROUP_CONCAT(c.value,',') as category
FROM products as a
LEFT JOIN attributes ON a.id=b.product_id AND key = 'format' as b
LEFT JOIN attributes as c ON a.id=c.product_id AND key = 'category'
GROUP BY a.id

Is there anything wrong with this solution (joining same table twice)? Or should it be done some other way?

--data population:

DROP TABLE IF EXISTS `attributes`;
CREATE TABLE `attributes` (
  `product_id` int(11) NOT NULL DEFAULT '0',
  `product_key` varchar(50) DEFAULT NULL,
  `product_value` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of attributes
-- ----------------------------
INSERT INTO `attributes` VALUES ('1', 'format', 'mp3');
INSERT INTO `attributes` VALUES ('1', 'format', 'audio');
INSERT INTO `attributes` VALUES ('1', 'category', 'English');
INSERT INTO `attributes` VALUES ('1', 'category', 'Vocabulary');
INSERT INTO `attributes` VALUES ('2', 'category', 'Grammar');
INSERT INTO `attributes` VALUES ('2', 'category', 'German');
INSERT INTO `attributes` VALUES ('2', 'format', 'mp3');
INSERT INTO `attributes` VALUES ('2', 'format', 'book');

-- ----------------------------
-- Table structure for `products`
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES ('1', 'product1');
INSERT INTO `products` VALUES ('2', 'product2');


texts: How Commit works in a transactional push replication with updatable subscribers

texts:
I want to know if in a transactional push replication with updatable subscribers, a commit is only done when all subscribers have succeeded to make it, or if it only guarantees the correct commit in the publisher and the subscriber that generated the change in the database.

I've found this in msdn, but I don't know if the two-phase commit protocol is referring to all instances (publisher and subscribers, or just one subscriber and the publisher)

  When data is updated at a Subscriber, it is first propagated to the
  Publisher and then propagated to other Subscribers. If immediate
  updating is used, the changes are propagated immediately using the
  two-phase commit protocol. If queued updating is used, the changes are
  stored in a queue; the queued transactions are then applied
  asynchronously at the Publisher whenever network connectivity is
  available. Because the updates are propagated asynchronously to the
  Publisher, the same data may have been updated by the Publisher or by
  another Subscriber and conflicts can occur when applying the updates.
  Conflicts are detected and resolved according to a conflict resolution
  policy that is set when creating the publication.


texts: MySQL - Changing from system tablespace to file per table

texts:
The MySQL database I am working on has the InnoDB tables and indexes stored in the system tablespace which is now > 20Gb. Some years ago I managed a MySQL database that made use of a file per table and looking to the future this seems the way to go. The documentation says that this can be done by setting “inno_file_per_table = 1” in my.cnf. Obviously I would need to back up and reload the data into the separate tables but the procedures I have seen are not that clear. Besides my  database schema there are also schemas present for information_schema, mysql and performance schema which I suspect come into the equation. MySQL is running under Mint Linux and although I have seen an answer to this question under Windows  I am not 100% confident on translating this to a Linux OS. What is the best way to achieve this change. It is acceptable at this stage for there to be a few hours downtime.

Thank you


texts: Database design for articles with localized prices

texts:
I'm designing a database for a shop for having some fun using Postgresql (but the database should not be really important here, right?).
I'd like to design for each article in the catalog multiple prices (e.g. divided by country). I was thinking about the best solution from the point of view of performance.
I see three options.

Option 1

The classical approach with junction tables, i.e. three tables:

article
price
article_price

Where price is like

CREATE TABLE price (
  id bigserial PRIMARY KEY,
  amount numeric(6,2) NOT NULL
  country  money_country NOT NULL,
  currency money_currency NOT NULL
)

and money_country and money_currency are two enums. One could say that currency and country should be a table. I believe, but maybe I'm wrong, that I can use some constraint to validate money_country and money_currency pairs, e.g. defining an SQL function instead of having additional tables.

Option 2

I define a different approach which will sacrifice some data integrity by using arrays, e.g.:

CREATE TABLE article (
  id bigserial PRIMARY KEY
  prices bigint[]
)

where prices is an array of id of the rows in price to be linked to the row in article.

Option 3

Probably more "wild", I could use types, e.g.

CREATE TYPE my_money AS (
  --something here to define money
)

CREATE TABLE article (
  id bigserial PRIMARY KEY
  prices my_money[]
)

What's the best approach? Am I missing some?


texts:
I'm using Google to geocode addresses, which breaks down an address into geographic units like country, state, city, etc. Depending on the address being geocoded, some geographic units may or may not be returned (e.g. maybe city is not returned, but suburb will be).

I'm tempted to store the data of each broken down address in a single row of a table, like:

USA, California, San Francisco
USA, California, San Jose
USA, California, Los Angeles

Obviously, this doesn't seem normalized as data is being repeated. I still could potentially query this effectively, though (e.g. list all states for USA).

Another option would be to save the data in parent-child relationships, a tree. That would be more normalized, but potentially harder to maintain and query (I can use a library to make this a lot easier, though, so it's not much of a concern for me).

My biggest concern using a parent-child model is that the branches may not all be the same length (e.g. city is not returned, but instead suburb is after the state). If I were to query for all the nodes below the state level, I could get a mix of cities and suburbs -- which might not be ideal. I could avoid this problem if I saved the data in a single row, with each geographic unit in their appropriate columns.

What's the proper way to model address parts?


texts: SQL Query to delete while database size is larger than 200GB

texts:
I have a SQL table which is used to store a large number of logs.

I want to have a stored procedure or something along the lines of that. That runs every minute and deletes items from the end of the log table as long as the database size is above 200GB.

so something like:

While(databaseSize > 200GB)
{
Batch delete 100 entires From [dbo].[logs] OrderBy DateTime
}

How would I go about doing this?

[Edit]
SQL Azure limits my database size to 250GB. Hence why I need to limit and still keep a small buffer for other tables.


texts: Using system tables to identify the columns of a table index in Sybase

texts:
Is there a means of identifying the columns of table index using systematically populated system tables such as: syscolumns, sysindexes, sysobjects, etc.

I have tried to use syskeys unfortunately this table is only populated by sp_primarykey.

So far I have the following query:

SELECT
    i.name,
    o.name
FROM
    sysobjects o
    INNER JOIN sysindexes i on i.id = o.id
WHERE
    o.type = 'U'
    and o.name = 'XXXXX'
      and i.indid > 0
      and i.status & 2 = 2


texts:
I configure sql router under three machines and found if I use simple command mysql -u ia -pTest@123 --port=7001 then its not applying round rubin connection request but when i use with protocol parameter then its working fine "mysql -u ia -pTest@123 --port=7001 --protocol=TCP"
I want to connect with normal command like mysql -u ia -pTest@123 --port=7001
and round rubin should work in this case.

=== Mysql Configuration
[logger]
level = INFO
[routing:secondary]
bind_address = localhost
bind_port = 7001
destinations = 192.168.0.187:3306,192.168.0.190:3306
routing_strategy = round-robin
[routing:primary]
bind_address = localhost
bind_port = 7002
destinations = 192.168.0.186:3306
routing_strategy = first-available (edited)


texts: Selecting rows from same name table from multiple databases in single result set

texts:
For some reason, I have a table Table_1 in n databases (same name and schema structure) instead of one single databases. The columns are like this:

  | Column_1 | Column_2 |

I can select data from those n databases by running this query on each of them:

use Database_n
select * from Table_1

Problem: This will require me to run this query n times. Which will return n result sets as shown in the below image:

Question: Is there a way to get the data from all those databases in one single result set like this:


texts: Populate a table on a selective replication slave

texts:
We have a setup with a data mining slave with a lot of replicate-ignore-table= for tables that aren't needed. Now the developers have requested to populate a table that is currently ignored. How do I go about populating it so that the replication can be started again at the correct point.

I want to avoid locking the table on the master instance as that would lead to application downtime.

We are not yet using GTID.

I have access to MySQL Enterprise Backup if that helps.


texts:
We have a table called Contracts which has a lot of columns. Some columns are for employees, some columns are for managers, some columns are for locals and so on. Since the table is growing, that's why I wanna refactor it. I have the following options,

Keep one table Contracts for all columns.
Keep a separate table for each contract, EmployeeContract, LocalContract, ManagerContract and so on.
Keep one table Contracts for common columns and separate table for  EmployeeContract, LocalContract, ManagerContract and so on.

Is it fine to have a parent-child with one-to-one relations?

Update: I am looking whether it makes sense to have sub-class and super-class like features in tables. Contracts will be super-class(super table) and EmployeeContract, LocalContract, ManagerContract are sub-classes(or sub tables)?


texts:
Is it possible to avoid 'Using temporary' and 'Using filesort' for the following SELECT query? I can't figure out a way to do it.

I tried adding indexes, for both top_expire and program, but didn't help
With the ORDER BY the query takes over 1 second and withoud it is just 0.003 seconds on localhost

Query

SELECT ad.*, p.link
    FROM (SELECT ad.*
        FROM mod_ad ad
        JOIN mod_ad_auta auta ON ad.id = auta.ad_id
        WHERE ad.active != 0 AND ad.usr_active != 0 AND ad.expire > 1371151608  AND ad.cat_id = '1' AND ad.price <= '10000'
          AND auta.rocnik BETWEEN '1950' AND '2013'
          AND auta.km BETWEEN '0' AND '500000'
        ORDER BY top_expire DESC, program DESC,  ad.id DESC  LIMIT 0,10) as ad
JOIN pages p ON ad.page_id=p.page_id;

Schema

CREATE TABLE `mod_ad` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `page_id` int(10) unsigned NOT NULL,
  `cat_id` int(10) unsigned NOT NULL,
  `subcat_id` int(10) unsigned NOT NULL,
  `program` tinyint(1) unsigned NOT NULL,
  `region_id` int(10) unsigned NOT NULL,
  `zone_id` int(10) unsigned NOT NULL,
  `city_id` int(10) unsigned NOT NULL,
  `sort` enum('firm','priv') NOT NULL,
  `type` enum('predaj','kúpa','výmena','darujem','hľadám','ponúkam','iné') NOT NULL,
  `condition` varchar(24) NOT NULL,
  `name` varchar(128) NOT NULL,
  `desc` text NOT NULL,
  `location` varchar(128) NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `price_type` varchar(20) NOT NULL,
  `cperson` varchar(128) NOT NULL,
  `firmname` varchar(128) NOT NULL,
  `zip` varchar(5) NOT NULL,
  `email` varchar(255) NOT NULL,
  `tel` varchar(20) NOT NULL,
  `tel2` varchar(20) NOT NULL,
  `web` varchar(255) NOT NULL,
  `video` varchar(255) NOT NULL,
  `marked_expire` int(11) unsigned NOT NULL,
  `top_expire` int(11) unsigned NOT NULL,
  `ad_hot_expire` int(11) unsigned NOT NULL,
  `ad_border_expire` int(11) unsigned NOT NULL,
  `ad_heading_expire` int(11) unsigned NOT NULL,
  `ad_weblink_expire` int(11) unsigned NOT NULL,
  `active` int(10) unsigned NOT NULL,
  `usr_active` int(10) unsigned NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `archive` int(10) unsigned NOT NULL,
  `expire` int(11) unsigned NOT NULL,
  `token` varchar(32) NOT NULL,
  `views` mediumint(9) NOT NULL,
  `ip` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `page_id` (`page_id`),
  KEY `cat_id` (`cat_id`),
  KEY `region_id` (`region_id`),
  KEY `zone_id` (`zone_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `mod_ad_auta` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ad_id` int(10) unsigned NOT NULL,
  `model` int(10) unsigned NOT NULL,
  `rocnik` smallint(5) unsigned NOT NULL,
  `palivo` varchar(10) NOT NULL,
  `karoseria` varchar(12) NOT NULL,
  `km` mediumint(8) unsigned NOT NULL,
  `prevodovka` varchar(12) NOT NULL,
  `farba` varchar(16) NOT NULL,
  `metaliza` tinyint(1) unsigned NOT NULL,
  `obsah` smallint(5) unsigned NOT NULL,
  `vykon` smallint(5) unsigned NOT NULL,
  `vybava` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ad_id` (`ad_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


texts: Trigger after insert, insert new row into another table

texts:
I'm developing a Wordpress site.
I'm trying to insert a new row into a table when new row with specific value in usermeta table is added.
This is what I got for now:

DELIMITER |
CREATE TRIGGER user_sub AFTER INSERT ON wp_usermeta
FOR EACH ROW
BEGIN
IF meta_value='subscribe' THEN
INSERT INTO newsletter_user_list SET list_id = 1,user_id=NEW.user_id;
END IF;
END;
|
DELIMITER ;

But this breaks the usermeta table. After new user register usermeta table is not populated.Thank you very much for any help because I have no experience with triggers.


texts: mysql cluster with 1 master 1 node and 2 slave

texts:
My Query is I want to set up MySQL cluster with 1 Master 1 Node and 2 Slave with Auto failover. If my master is down node should assign slave as a master.

Can anyone suggest me an idea to do that? As I did research But I couldn't find any link for the same.


texts:
I have a couple queries called from a C# .NET web application which are always fast for me (I am an local admin on the SQL Server) but for a group of users (domain group with required permissions), the query is incredibly slow to the point it times out in the application.

What would cause the exact same query to run differently for different users?

More info:

The query is inline SQL in the C# code, not a stored procedure
The app uses domain authentication and both the user and myself run the query through the app
Seems like the issue is different plans and one was cached so that is why it was different for different users. Something is affecting the cache because now the query is slow for me via the app and fast in SQL Server Management Studio.


texts:
I'm not primarily a dba so I apologize if this question is high-level and please provide feedback if I'm asking the wrong question or going about this the incorrect way.

I currently have one database (virtual) server that many applications point to. Some are line of business apps and some are custom applications using connection strings. The server is old and I want to upgrade the OS and SQL Server version but I don't want to do an in-place upgrade, I'd rather build a new server and migrate all of the applications and I'm realizing that it's going to be a gargantuan task. We're going to have to go through all of the settings in our applications but also all of the code in-house applications to change the name of this server in every connection string.

My question is:

How do I go about this so that this is the last time I ever have to
go through this task?
Should I be using alwayson clustering and use the listener in my
connection strings?
Is there a way I can do this and use the name of my server as the
virtual network name so that I don't have to change all of my
connection strings?
Should I be using something else entirely?

Thank you for any info provided.


texts: Taking hot backup of slave node in postgres (master-slave config with repmgr)

texts:
I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave is in recovery, hence read-only mode.

I need both the master and the slave snapshots to speedup the recovery process  (if taken only on one node, in case of recovery the slave needs to be rebuilt from scratch).

Any advice of how to take a consistent snapshot of slave node? (Master is no problem).

Currently these are the approaches I'm checking:

shutting down slave node and take a cold snapshot backup.
pg_start_backup on master and hopefully it will reflect consistent state on slave (with the WAL shipping) - any confirmation on this one?
stop replication, take hot backup of slave using regular methods and then starting replication again - as far as I tested, not possible because as the replication is stopped, each node becomes independent and has its own timeline, thus require the nodes to resync in order to function again in master-slave config.
make a switch over before taking the backup - don't like this option, don't think is a correct approach to make a switch-over each time I want to take a backup.

Anything I missed? Have you implemented it using different methods?


texts:
I want to query in my xbase database an limit the output like:

SELECT * FROM TB_TEST LIMIT 5;

But it does not work on xbase


texts:
I am using PostgreSQL and trying to write value constraints.

Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value?

For many fields, I want to ensure that the record is either numeric or recorded as missing, but not NULL.

A common solution is to use -9999 to indicate a missing value. However, this can lead to silent errors (e.g. calculating a mean). Is there a better solution than creating a new type that allows either a value of "Missing" or a number?


texts: How to search Query Store for queries with multiple plans?

texts:
There are a number of blog posts about why a single query with multiple plans is bad.  But they mostly address when you find them, not how to find them.

Query Store does not offer a report, that focuses on finding them, nor is it an obvious metric you can choose.

You can use the configure option to filter for queries with multiple plans on specific metrics.

How do you find all the queries with multiple plans?


texts:
I need help to create a trigger that can calculate prices after promotions.
I have two tables "product" and "promotion".
after inserting in promotion table the promotion price and the product id, I want to update the price in product table to the new price after promotion.
here is my try :

 CREATE TRIGGER update_promotion
       AFTER INSERT ON promo
          SELECT @prix_promo = prix_promotion FROM INSERTED;
          SELECT @id = id_prod FROM INSERTED;
          SELECT @prix_sanspromo = prix FROM produit
           WHERE id_produit = @id;
          FOR EACH ROW
           UPDATE produit SET prix_promo=((@prix_promo*prix)/100) WHEREid_produit = @id;


texts: How to shrink space on table with a function-based index?

texts:
I have a partitioned table for which I'd like to remove unnecessary free space. Usually I would run commands like this:

ALTER TABLE {table name} MODIFY SUBPARTITION {subpartion name} SHRINK SPACE;
ALTER TABLE {table name} MODIFY PARTITION {partion name} SHRINK SPACE;

But I get an error for a particular table because the table has function-based indexes (see Restrictions on the shrink_clause):

  ORA-10631: SHRINK clause should not be specified for this object

All of the indexes on my table are local. There are no global indexes. Does anyone know a solution to shrink the free space?

Dropping function-based indexes, shrinking space and re-creating the indexes is not an option. My table is quite big (200 billion rows, resp. 20 TByte) so it would take far too much time. I need to shrink only a few subpartions, not the entire table.

I also tried to disable the function-based indexes on that partition (by using Partial Indexes for Partitioned Tables) but that did not work either.


texts:
We have SSRS 2008 r2 rdls. I open an SSRS report and keep it open in my browser for more than 30 mins. If I now click any drill downs either through an action or go to report. I don't want the report to get me that info. It should state that the report is expired.

Is there a solution already available? Do future version SQL server have a solution for this?


texts: Performance impact MySQL replication vs table-synchronisation script

texts:
Currently I am considering implementing a MySQL table replication solution for a client, this client has 2 servers in 2 different locations:

Server 1 running an application with mysql databases (Running Ubuntu 14.04 LTS)
Server 2 has a PHP webserver frontend to serve it's customers with a website and customer panel (also Ubuntu, same version).

The databases on server 1 total around 2.5 GB in size.
The servers are in different data centers but within 5ms latency of one another both with gigabit uplinks and heavy dataplans.

Currently the webserver(2) relies completely on the availability of the application server (1). Though this is essential it does mean that the webserver  is effectively useless in case of maintenance or unscheduled downtime, the client does not wish to synchronize the databases to both locations but would allow the synchronization of a single table of account login data (just the one table, no other tables from the same database) to at least keep the bare functionality of the website intact.

Now the 2 options I currently am considering are:
A. Setting up MySQL Replication Master->Slave setup for this table
B. Using a "smart/efficient" table sync script (https://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html)

My consideration is also that the binary log of the application mysql server is around 8.5Gb a day and I was unable to find out the exact performance impact of this is on replication. I can say that the table of which I speak is 88MB and has around ~250.000 rows. The daily changes to the account table I wish to synchronize are very minor, I expect less than 1000 rows a day).

I am currently leaning towards option B because

It would avoid future issues on mysql version conflicts between servers (which I do not both manage for the client)
The amount of data changes are rather light on the specific table I wish to synchronize.
The methods MySQL offered to synchronize just specific tables seemed to fall short as the replication technology puts more emphasis for use as a full replication solution (in this my fear that big binary logs would have a negative impact based on mysql forum/article comments)
"to the second" data accuracy is not an concern for the client, a sync every hour would already suffice (his words).

My question then of course is if anyone can provide information on if option A or B (or another) would be preferred in this scenario and more importantly why?

Please note: I aim to keep the core setup which the client runs intact, the application or web server cannot switch to another database engine.


texts: Why does MATCH() give Score 0, if there are less rows in the database?

texts:
I followed this example from the Mysql Documentation:

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     );

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

SELECT *, MATCH ( title, body ) AGAINST ( 'database' ) AS Score
    FROM articles
    WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------+------------+
| id | title             | body                         | Score      |
+----+-------------------+------------------------------+------------+
|  5 | MySQL vs. YourSQL | In the following database... | 0.662664...|
|  1 | MySQL Tutorial    | DBMS stands for DataBase ... | 0.655458...|
+----+-------------------+------------------------------+------------+

this works, but if i delete all rows, but one:

DELETE FROM `articles` WHERE id >1

then the select gives Score 0:

SELECT * , MATCH ( title, body ) AGAINST ( 'database' ) AS Score FROM articles


texts: Can SQL Server 2016 log files be applied on SQL Server 2012

texts:
This is in relation to a rollback scenario from a 2012 -> 2016 upgrade.

The scenario is that the SQL Server 2012 database is restored onto 2016 and log files applied until it is up to date. Then production systems are cut over to 2016.  Database is kept at compatibility level for 2012.  Subsequently, incremental logs from the 2016 instance are kept until the need to roll back (let's say 8 hours worth).

Can the logs now be applied to the SQL Server 2012 instance to bring that up to date?


texts: Counting unique (distinct) users per day

texts:
I am new topsql and am interested in counting the number of unique first time users per day.

My table only has two columns- user_id (there can be multiple uses by one user per day) and start_time which is a timestamp that indicates the time of use. The count should only include users whose min(start_time) fell on that day.

Why does the following query not work? Shouldn't it be possible to select distinct on two variables at once?

SELECT COUNT (DISTINCT min(start_time::date), user_id),
       start_time::date as date
FROM mytable
GROUP BY date

produces

  ERROR:  function count(date, integer) does not exist

The output would look like this

        date count
1 2017-11-22    56
2 2017-11-23    73
3 2017-11-24    13
4 2017-11-25    91
5 2017-11-26   107
6 2017-11-27    33...

Any suggestions about how to count distinct min Date and user_id and then group by date in psql would be appreciated.


texts: Schema Design for a food delivery database SKU variants

texts:
I'm using MySQL. The idea is similar to Ubereats so vendors can add their food SKUs with multiple types of variants and attributes.
(e.g. bolognese pasta has regular and large
regular

drink (only pick one)

coke $1
sprite $1
root beer $2

sides (only pick two)

extra cheese $2
extra olive oil $1
extra meat $2

meat (only pick one)

lamb $0
chicken $3
beef $2
large

drink (only pick one)

coke $1
sprite $1
root beer $2

sides (pick any two)

extra cheese $2
extra olive oil $1
extra meat   $2

meat (only pick one)

lamb $0
chicken $3
beef $2

From all the research I've done this seems the most likely solution for me and I'm just wondering if there's anything wrong with the following schema and what are the upsides/downsides? Also, someone suggested using NoSql / json to store this instead.
---------------------
food_id | food_name |
---------------------
    1   | bolognese |
---------------------

----------------------------------------------------------------------------------------------------
variant_id | variant_name | variant_price | variant_limit | variant_type | variant_parent | food_id|
----------------------------------------------------------------------------------------------------
      1    | regular      | null          | 0             | size         | null           |  1
      2    | large        | null          | 0             | size         | null           |  1
      3    | coke         | 1.00          | 1             | drink        | 1              |  1
      4    | sprite       | 1.00          | 1             | drink        | 1              |  1
      5    | root beer    | 2.00          | 1             | drink        | 1              |  1
      6    | extra cheese | 2.00          | 2             | add-on       | 1              |  1
      7    | extra olive oil| 2.00        | 2             | add-on       | 1              |  1
      8    | extra meat   | 2.00          | 2             | add-on       | 1              |  1
      9    | lamb         | null          | 1             | meat         | 1              |  1
     10    | chicken      | 3.00          | 1             | meat         | 1              |  1
     11    | beef         | 2.00          | 1             | meat         | 1              |  1
------------------------------------------------------------------------------------------------

Thank you


texts: Strange Intermittent Postgres Performance Issue (docker?)

texts:
I am running a local Postgres 9.3 in a docker container with a temporary database that is used for some file import operations.

The basic scenario is:

Load files into input tables
INSERT from a SELECT into the output tables (transform)
Read the output tables

There are several tables that we transform with separate and sequential statements.  We are running into severe performance degradation on two of our transform steps. It changes between which of the two will have the issue and if one has it, the other will not.

The degradation is ~100x slower, 2 seconds -> 20 minutes for one; and 4 seconds -> 40 minutes for the other.

The queries look like:

INSERT INTO target_table (
  field1,
  field2,
  -- elided
  field20,
  member_key,
  source_file_name,
  source_line_number
)
SELECT
  src.field1 as field1,
  src.field2 as field2,
  -- elided
  src.field20 as field20,
  mem.member_key as member_key,
  src.source_file as source_file_name,
  src.source_line as source_line_number
FROM
source_table src
INNER JOIN members mem
ON src.member_identifier = mem.member_identifier;

The two queries are nearly identical, the slower of the two has a few more fields, and about twice the rows. The 'source_table' and 'target_table' change, but both queries use the same members table.

In our sample set the first source table has 360,000 rows and the second has 600,000 rows. There is no where clause, so the SELECT portion will operate on all rows.

This slowdown started showing up for us about a week ago. The only thing that we changed was creating a reduced data set to work on performance issues elsewhere in the system. The original datasets for those queries were 3.6M and 6M rows and the inserts never took longer than a couple of minutes to complete.

Other Information:

This is running on an AWS EC2 i2.2xlarge box.
OS is Ubuntu 14.04.2 LTS
Postgres and the caller of this query are in Docker containers (separate)
The Postgres image is the stock postgres:9.3 image run with postgres -F (disables fsync, this is an ephemeral database)
There is nothing else happening on the box
When running the query, one CPU is at 100%, the others hover around 0%
When running the query, top reports a 'wa' parameter of 0.0%
When the query was running and stuck in the 100% CPU state, I ran just the SELECT portion from a separate connection. This completed in a couple of seconds.
When running the query, the checkpoints_req column of the table pg_stat_bgwriter is 2.


texts: What is the way to paginate last updated rows from multiple tables?

texts:
I want to have something like a newsfeed from multiple objects sort by most_recent. However I need to paginate (load more/offset) the results.

Row1 = Post's - text, username
Row2 = Photo's - text, picture_url, username
Row3 = Post's - text, username
Row4 = Photo's - text, picture_url, username

I was thinking having a table inheritance called Activity that will hold all types of objects, referencing them with the columns item_type and item_id.

This way I query directly from the Activity table.

This should work. But how to maintain the activity to "stay tuned" of it's items "updated_at"?

When I comment in a post, should the comment update the post "updated_at" column and the post update the "updated_at" column on the activity?

Thanks!

EDIT
Activities table:

  id   event      item_type   item_id   parent_type    parent_id    updated_at
  1   "create"      Post         1         Group           100
  2   "create"      Post         2         Group           100
  3   "create"     Comment       1         Post            1
  4   "create"      Photo        1         Group           100

Query: The latest root events (parent_type is "Group") from activities. Should bring Post 1, Photo 1 and Post 2.

SELECT * FROM activities WHERE
parent_type = "**Group**" and parent_id = 100
ORDER BY created_at

Still not retrieving the latest. Because the comments didn't affected the Post activity.


texts:
It's time to ask again this popular question:
"Is it still valuable for the I/O performance to manage disk cluster size?"
But this time I'm interested in a case with vSAN as storage.
I've found this information:

Keep in mind that vSAN is optimized for 64K and smaller I/O sizes; configuring an I/O size >64K will result in increased guest I/O latency.

vSAN performance diagnostics reports: "The size of IOs may not be optimal to achieve the desired goal" (2150019)
So, this is one step towards a Yes.


texts:
I'm just getting into SQL-SERVER, so forgive me if this is a basic question.

I need help, recently work on database programming, For my query development, to understand complex query, to can optimized syntax, I need guideline/good resources. Give me a helping hand to increase my knowledge in sql field.

Thanks in advanced


texts: A weak entity with many to many relationship with its owner SQL

texts:
I have this patient table which is an owner entity:

CREATE TABLE PATIENT
(
    FILE_NO NUMBER(4) PRIMARY KEY,
    NAME VARCHAR2(25),
    SEX VARCHAR2 (7),
    BLOOD_GROUP VARCHAR2(3),
    ALLERGIES VARCHAR (30),
    BIRTH_DATE DATE,
    INSURANCE_COMPANY VARCHAR (30)
);

and the appointment table is the weak entity

CREATE TABLE APPOINTMENT
(
    APP_NO NUMBER(4),
    FILE_NO NUMBER (4),
    FOREIGN KEY (FILE_NO) REFERENCES PATIENT(FILE_NO),
    CONSTRAINT APPOINTMENT_PK PRIMARY KEY (APP_NO,FILE_NO),
    DATE_TIME TIMESTAMP,
    DOCTOR_NAME VARCHAR2(25),
    DEPARTMENT_NAME VARCHAR2(25)
);

they have a many to many relationship. Is this a correct table for the relationship?

CREATE TABLE APPOINTED_TO
(
    APP_NO NUMBER(4),
    FILE_NO NUMBER (4),
    CONSTRAINT APPOINTED_TO_PK PRIMARY KEY (APP_NO,FILE_NO),
    CONSTRAINT APP_TO_FK FOREIGN KEY (APP_NO,FILE_NO)
        REFERENCES APPOINTMENT(APP_NO,FILE_NO)
);

My teacher insists I make a third table for the relationship. I can't take out the FILE_NO from the Appointment table because it's a weak entity.

It didn't make sense to me that's why I wanted to ask for an outside opinion.


texts: Increased size of database dump even though LESS consumed storage

texts:
For various reasons I've migrated my database schema from 4 tables with some additional indexes to keep integrity and stuff to 1 table only. That made some of the former used indexes obsolete and resulted in overall less consumed storage:
The old schema consumed ~42 GiB, while the new is ~16 GiB without the formerly available indexes and ~25 GiB with the same logical indexes. Though, a created dump of the new schema has increased from ~5,52 GiB
to 6,38 GiB. Of course I'm using the same settings to create both dumps:

pg_dump.exe "--username=%USERNAME%" "--encoding=UTF-8" "--compress=9" "--format=c" "--dbname=%DB_NAME%" > "%DMP_PATH%"

My expectation was that the dump would be smaller as well, because the data itself is the same, while lots of duplicate IDs, obsolete indexes etc. in not available tables anymore have been removed.
Any explanation for the increased dump size? Thanks!
The tables themself were changed like the following:
CREATE TABLE clt_rec
(
  id          bigserial                 NOT NULL,
  oms_rec     bigint                    NOT NULL,
  captured_at timestamp with time zone  NOT NULL,
  rssi        smallint                  NOT NULL
);

CREATE TABLE oms_rec
(
  id        bigserial NOT NULL,
  meter     integer   NOT NULL,
  encrypted bytea,
  decrypted bytea
);

CREATE TABLE clt_rec_src
(
  id          bigserial NOT NULL,
  real_estate integer   NOT NULL,
  collector   integer   NOT NULL,
  clt_rec     bigserial
);

CREATE TABLE meter_status_history
(
  id      serial  NOT NULL,
  oms_rec bigint  NOT NULL,
  status  smallint
);

vs.
CREATE TABLE datagram
(
  id            bigserial                 NOT NULL,
  src_re        integer                   NOT NULL,
  src_clt       integer                   NOT NULL,
  src_meter     integer                   NOT NULL,
  captured_at   timestamp with time zone  NOT NULL,
  captured_rssi smallint                  NOT NULL,
  oms_status    smallint                  NOT NULL,
  oms_enc       bytea,
  oms_dec       bytea
);


texts: Deleting data from a large table with replication

texts:
I need to delete all the rows from a given table. The table contains millions or records. The master database is being replicated to several slaves and I wish to do that without creating a replication lag or impacting the performance.

After some research, I tried dropping the table. That took quite a few long seconds, during which time my master DB was locked out.

I know I can gradually delete in smaller batches, just wondering is there's a quicker way.

Thanks,
Z


texts: System Preferences quits unexpectedly due to MySQL plug-in

texts:
I've installed MySQL Preference Pane on my MacBook to start/stop MySQL from my System Preferences and it works ok, except that once in a while System Preferences just quits and shows this message:

The application System Preferences quit unexpectedly.
The problem may have been caused by the MySQL plug-in.

MySQL remains up and running and I can just restart System Preferences without any trouble, but what causes this crash and how can I fix it?

Here's what I see on my OS X Console:

10:45:55 pearl-macbook ReportCrash[6365]: Formulating crash report for
process System Preferences[3671]

10:45:56 pearl-macbook ReportCrash[6365]: Saved crashreport to
/Users/pearl/Library/Logs/CrashReporter/System Preferences_2013-04-11-104552_pearl-macbook.crash
using uid: 501 gid: 20, euid: 501 egid: 20

10:45:56 pearl-macbook com.apple.launchd[83]([0x0-0x5e05e].com.apple.systempreferences[3671]):
Stray process with PGID equal to this dead job: PID 6364 PPID 1 System Preferenc

10:45:56 pearl-macbook com.apple.launchd[83]([0x0-0x5e05e].com.apple.systempreferences[3671]):
Exited abnormally: Bus error


texts: Why does querying the right most index of a composite index does not result in full table scan in postgres?

texts:
Consider the following table

CREATE TABLE public.my_table
(
    my_id text COLLATE pg_catalog."default" NOT NULL,
    my_second_id text COLLATE pg_catalog."default" NOT NULL,

    CONSTRAINT my_table_pkey PRIMARY KEY (my_id, my_second_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.my_table
    OWNER to my_db;

Querying the following

explain select * from my_table
where my_id = ''

explain select * from my_table
where my_second_id = ''

Have the following result

Index Scan using my_table_pkey on my_table  (cost=0.56..178699.70 rows=268 width=917)

Index Cond: (my_second_id = ''::text)

Index Scan using my_table_pkey on my_table  (cost=0.56..3544.14 rows=1142 width=917)

Index Cond: (my_id = ''::text)

I expect the second query that uses the right most index to do a full table scan, however I see index scan instead

Does postgres support sub index scan in composite index or am I missing something?


texts: MySQL: Find ID with only 2 attached IDs in subtable

texts:
I've got 2 tables. 1 containing my Threads and 1 containing the users attached to these threads.

I want to find the ThreadID with 2 specific UserIDs attached, no more no less.

This is what I've come up with:

SELECT mt.ThreadID
FROM MessagesThreads AS mt
WHERE (SELECT COUNT(DISTINCT mtau.UserID)
       FROM MessagesThreadsAccessUsers AS mtau
       WHERE
        mtau.ThreadID = mt.ThreadID
        AND EXISTS (
         SELECT 0
         FROM MessagesThreadsAccessUsers AS mtau2
         WHERE mtau2.ThreadID = mtau.ThreadID
         AND mtau2.UserID = 1
        )
        AND EXISTS (
         SELECT 0
         FROM MessagesThreadsAccessUsers AS mtau2
         WHERE mtau2.ThreadID = mtau.ThreadID
         AND mtau2.UserID = 2
        )
       ) = 2

This specific situation is about finding the ThreadID with UserID 1 and UserID 2 attached.
If no ThreadID is found, I need to create a new one further down the path.

My question is: Is this the best way to query this or can it be more intelligent?


texts: How can I associate different not done continuation blocks with two cursors in the same scope?

texts:
If I have two cursors declared in the same block, how can I associate different not done continuation handler actions that are independent of one another and only execute with the cursor they're somehow associated with?

I would like to do something like this:

drop table if exists Msgs;
create temporary table Msgs (id int key auto_increment, msg varchar(50));
drop procedure if exists MultiCurseTest;
delimiter $$
create procedure MultiCurseTest() begin
    drop table if exists T;
    create temporary table T (id int key);
    insert into T values (1);
    begin
        declare c1 int default 0;
        declare c2 int default 0;
        declare id_ int default 0;

        declare crsr_1 cursor for select id from T;
        declare crsr_2 cursor for select id from T;

        declare continue handler for not found insert into Msgs (msg) values ("crsr_1 done");
        declare continue handler for not found insert into Msgs (msg) values ("crsr_2 done");

        open crsr_1;
        open crsr_2;

        fetch crsr_1 into id_;
        fetch crsr_1 into id_;
        fetch crsr_1 into id_;
    end;

end $$
delimiter ;

call MultiCurseTest();
select * from Msgs;

but MySQL complains: Error Code: 1413. Duplicate handler declared in the same block.  So, clearly it doesn't like the two above declare continue... statements.

One way I can think of doing this is to construct a block for the sole purpose of fetching from one and only one cursor and then declare a continuation handler, like:

begin
     declare continue handler for not found set c1=1;
     fetch crsr_1 into id_;
     fetch crsr_1 into id_;
end

begin
    declare continue handler for not found set c2=1;
    fetch crsr_2 into id_;
end

However, I'm a bit concerned about the performance in that case.  Perhaps installing continuation handlers over and over is expensive?

Is there a way to do this without the inner block hack?


texts: Circular dependency in schema or boolean with CHECK?

texts:
Let's say I have the following tables:

create table repositories (
    id serial primary key,
    name varchar(255) not null
);

create table remotes (
    id serial primary key,
    url varchar(255) unique not null,
    repository_id integer references repositories(id) not null
);

I want to have an "origin" remote for a repository.

I have 2 solutions:

Create a circular dependency, which means using ALTER TABLE in the schema:

alter table repositories add origin_remote_id integer references remotes(id);

Add a flag on remotes with a custom CHECK constraint:

create table remotes (
    id serial primary key,
    url varchar(255) unique not null,
    repository_id integer references repositories(id) not null,
    is_origin boolean not null constraint is_only_origin check (is_only_origin(repository_id))
);

The problem is that the 1st approach makes relationally more sense, but it also introduces a circular dependency.

Is it fine? I'm not sure what I should do.


texts:
I have read many posts regarding the memory but still its not clear.

I have dedicated server SQL Server 2008 R2 with Windows 2008 R2, 32 GB RAM, 2 6 core processor and a database of around 60 GB on it. Min and Max amount is set to 16 GB and 28 GB.

After few days if I check in task manager, it shows the load of 31 GB Memory but actually maximum is allotted to SQL Server is 28 GB and nothing is running on he server.

Why its increasing to the maximum limit? And where this 31 GB used? If its used in cache then how to clear the cache without restarting the SQL Server?

There are different questions in my mind if someone can help and discuss on this.


texts: Cannot recover Database from ibdata file

texts:
My Server recently had a Kernel-Panic and I downloaded all the mysql data using the rescue mode.

Now when I upload the ibdata1 file and table folders to a local XAMPP Server and click on a table to view it's data(phpmyadmin), it says

#1932 - Table 'phpmyadmin.pma__table_uiprefs' doesn't exist in engine

My old Server had Plesk installed, maybe Plesk-MySQL has a different ibdata1 structure, compared to MySQL from XAMPP.

I hope anyone can help me to get my data back.


texts: Sql Server Transaction logs during large deletes

texts:
At my work, I am going to be moving millions of records to a different server, and removing them from the source server. My DBA has set up a backup scenario where change backups are taken every 5 minutes. He had this warning for me regarding deleting the records:

  ...it is going to create a gigantic transaction log that will probably
  cause the 5 minute transaction log backups to fail...

Are there any ways to get around this?


texts: What are these Errors in a Previously Successful MySQL Script?

texts:
Several years ago I began work on a project that uses MySQL for the database. Much of the development happened prior to MySQL 8, but I last deployed the project on an early version of MySQL 8. And, have updated the deployment to 8.0.15 since then.

That prior deployment went smoothly including just running a large DB creation sql script. And it's still working (though hidden / not default) at codehawkins.com/index.php.

I haven't worked on this (or anything SQL) in about a year, but went to deploy a dev instance of it in order to explore continuing development. On a fresh install of MySQL 8.0.19 and using MySQL Workbench, my previously working code is full of errors and odd warnings...

For example, warnings and errors for one function are indicated in comments below:

-- The initial drop function line warns me that the function doesn't exist.
--   I don't remember seeing that when using "IF EXISTS".
-- More importantly, the warning at this point tells me that code
--   prior to this point is not likely the cause of later errors,
--   as it parsed this DROP command correctly.
DROP FUNCTION IF EXISTS fnGetLUID;
CREATE FUNCTION fnGetLUID(newUse VARCHAR(128))
  RETURNS BIGINT
BEGIN
-- The following line has an error indicating that a semi-colon is expected.
--   Yet there is one, and again, this used to work.
  INSERT INTO LUID (usedFor) VALUES (newUse);
-- An error here indicates that "RETURN" is not valid at this position.
  RETURN LAST_INSERT_ID();
-- Likewise, error reports "END" is not valid at this position.
END;

The entire script is at https://github.com/HumanJHawkins/CommunityPress/blob/master/documentation/v4lSchema.sql if useful to understanding this.

Were there changes in mySQL between 8.0.15 and 8.0.19 that break this, or is there some configuration on how to parse the language that I am missing?

Thanks in advance for any help.


texts: Stored Procedure to return dynamically created table data

texts:
Quick back story, we are working with an outside vendor that has a survey system.  The system is not necessarily designed the best in that when you create a new survey and the system creates a new table, i.e:

Tables
____
Library_1 -- table for Survey 1
SurveyId int
InstanceId int
Q_1 varchar(50)

Library_2 -- table for Survey 2
SurveyId int
InstanceId int
Q_2 int
Q_3 int
Q_4 varchar(255)

The tables are generated with the SurveyId at the end of the name (Library_) and the Question columns are generated with the QuestionId at the end of it (Q_).  To clarify, the questions are stored in a separate table so while the question ids are sequential they do not start at 1 for each survey. The question columns will be based on the id assigned to them in the table.

Seems simple enough to query, except we need to extract the data from all of the survey tables to be sent to another system and this is where the problem comes in.  Since the tables are created automatically when a new survey is added by the front-end application, the other system cannot handle this type of structure.  They need the data to be consistent for them to consume.

So I was tasked with writing a stored procedure that will extract the data from all Survey tables and place it in the following format:

SurveyId    InstanceId    QNumber    Response
________    __________    _______    ________
1           1             1          great
1           2             1          the best
2           9             2          10
3           50            50         test

By having the data for all tables in the same format, then it can be consumed by anyone no matter how many survey tables and questions exist.

I wrote a stored procedure that appears to be working but I am wondering if I am missing something or if there is a better way to handle this type of situation.

My code:

declare @sql varchar(max) = ''
declare @RowCount int = 1
declare @TotalRecords int = (SELECT COUNT(*) FROM SurveyData)

Declare @TableName varchar(50) = ''
Declare @ColumnName varchar(50) = ''

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM SurveyData
        WHERE @RowCount = rownum

        SET @sql = @sql +
            ' SELECT s.SurveyId
                , s.InstanceId
                , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
            FROM SurveyData t
            INNER JOIN ' + @TableName + ' s' +
                ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
            ' WHERE t.columnName = ''' + @ColumnName + ''''

        IF @RowCount != @TotalRecords
            BEGIN
                set @sql = @sql + ' UNION ALL'
            END

        SET @RowCount = @RowCount + 1
    END

exec(@sql)

I have created a SQL Fiddle with some sample data and the code.

Is there a different way this type of query should be written?  Are there any noticeable issues with it?

Unfortunately, there are many unknowns with this...how many tables we will have and how many questions per survey.  I would say that we will have between 25-50 surveys, with 2-5 questions each.


texts: How to check if integer array overlaps with int4range?

texts:
PostgreSQL version: 9.5

I am trying to write a query that will check if an array of specified decades overlaps in a range. For example, I'd like to find all of the people employed at a company in the 1980's OR the 2000's. Let's say I have two date columns: start_date and end_date. Here's my best attempt so far:

SELECT *
 FROM date_table
WHERE int4range(EXTRACT(DECADE from start_date)::int,
                EXTRACT(DECADE from end_date)::int) && (int4range(198, 200))

This query will check for anyone employed in the 1980s, 1990s, or the 2000s. I don't want to compare a range to another range; instead, it should be checking if any of the values in the array is contained in the range. How can I accomplish this? Is there another way I could be approaching this problem?

UPDATE

Thanks to the answer by joanolo, my working query is:

SELECT *
 FROM date_table
WHERE int4range(EXTRACT(DECADE from start_date)::int,
                EXTRACT(DECADE from end_date)::int, '[]') @> ANY(ARRAY[198, 200])


texts:
I am analyzing the query which takes several minutes to execute and I want it to be a bit faster:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM filings AS filing
WHERE (filing.sources_id != 10 OR filing.action_date < NOW()::date)
AND (filing_type_id IN (4538,5080))
ORDER BY filing.action_date desc LIMIT 10

Response:

                                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..4124.25 rows=10 width=704) (actual time=128412.522..158528.958 rows=10 loops=1)
   Buffers: shared hit=177017 read=162030 dirtied=81 written=34435
   ->  Index Scan Backward using filings_action_date_idx on filings filing  (cost=0.56..6437894.65 rows=15612 width=704) (actual time=128412.429..158528.832 rows=10 loops=1)
         Filter: ((filing_type_id = ANY ('{4538,5080}'::integer[])) AND ((sources_id <> 10) OR (action_date < (now())::date)))
         Rows Removed by Filter: 351983
         Buffers: shared hit=177017 read=162030 dirtied=81 written=34435
 Planning time: 5.454 ms
 Execution time: 158529.062 ms
(8 rows)

I don't understand why my index is useless and filtering of 300K rows is used instead

Filter: ((filing_type_id = ANY ('{4538,5080}'::integer[])) AND ((sources_id <> 10) OR (action_date < (now())::date)))

although index is in place:

"filings_filing_type_id_sources_id_action_date_idx" btree (filing_type_id, sources_id, action_date)

Instead this index is in action:

"filings_action_date_idx" btree (action_date)

according to the query plan

Update

postgres version:

SELECT version();

PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)

number of rows in the table:

select count(*) from filings;
  count
----------
 37723118
(1 row)

And

select relname, last_analyze, last_autoanalyze from pg_stat_all_tables where relname='filings';

gives:

 relname | last_analyze |       last_autoanalyze
---------+--------------+------------------------------
 filings |              | 2020-03-26 18:32:09.88901+00
(1 row)


texts: Advice for my DB design and how to handle null / measuring errors

texts:
I am seeking advice for my own DB design. I need to make a big step and start a huge load of data processing. What I decide now will have big consequences for my project.

I have this table. It holds data that was acquired online from an external source.

app_data
+--------------+-------+------------+--------+---------+-------+
| surrogateKey | appid |  created   | owners | viewers | price |
+--------------+-------+------------+--------+---------+-------+
|            1 |     2 | 1472428100 | 10     |      25 | 10,00 |
|            2 |     2 | 1472428200 | 11     |      50 | 10,00 |
|            3 |     2 | 1472428300 | 15     |      50 | 10,00 |
|            4 |     2 | 1472428400 | 22     |      51 |  8,00 |
|            5 |     2 | 1472428500 | null   |      50 |  8,00 |
|            6 |     2 | 1472428600 | 20     |      49 |  8,00 |
|            7 |     2 | 1472428700 | 25     |      50 | 10,00 |
|          ... |       |            |        |         |       |
+--------------+-------+------------+--------+---------+-------+

CREATE TABLE app_data(
    surrogateKey BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    appID INT UNSIGNED NOT NULL,
    created TIMESTAMP NOT NULL,
    owners INT UNSIGNED,
    owners_variance MEDIUMINT UNSIGNED,
    viewers MEDIUMINT UNSIGNED, -- TINYINT would be better but application layer cannot handle tinyint
    viewers_variance MEDIUMINT UNSIGNED,
    price MEDIUMINT UNSIGNED,
    CONSTRAINT fk_appData_app_appID
        FOREIGN KEY (appID)
        REFERENCES app(appID)
        ON UPDATE CASCADE
) ENGINE=INNODB;

Does it makes sense to create this surrogateKey? There is no value
added by it. I will not be used for joins. appid and created would
make a fine composite key and I will use these keys for joining
tables.
appID and created will be used for joining, comparisons, range
queries, and so on. How should I create indexes? One index on each?
    (appID, created)? (created, appID)?
This table will be HUGE and take 95% of the storage space. How can I optimize it further performance wise?
How should I handle random null values as in the owners column? These mean basically: The thermometer was broken that day... I was
    contemplating in just turning them to zero or leaving them as null.
    null does mean that there were no measurements that day whereas zero
    would be ambiguous but easier for analysis.
How should I handle values that don't make sense? For example
viewers upper limit is 50. There is one value with 51 that means:
    This value is WRONG. Should I turn it to null? To 50?
Should I normalize price into something like this? Normalization
would make it more difficult to analyse and result in more
complicated queries.

.

CREATE TABLE(
  appID INT NOT NULL PRIMARY KEY,
  created TIMESTAMP,
  start_date TIMESTAMP,
  end_date TIMESTAMP,
  price INT
);

Thanks!


texts: UPDATE vs INSERT INTO... ON DUPLICATE KEY UPDATE

texts:
Alright so I have this table in which until now I was using the following queries to UPDATE else do an INSERT:

$db->query("UPDATE ulogs SET invalid = invalid + 1 WHERE uid = 666 AND date = '2018-04-18'");
if ($db->affectedRows() < 1) {
    $db->query("INSERT INTO ulogs (uid,date,invalid,unique,nonu,ea,ref,bo) VALUES (666,'2018-04-18',1,0,0,0,0,0)";
}

I had a regular combined index (non-unique) on uid&date columns. The update query was using the INDEX. All is fine but I was wondering if switching to ON DUPLICATE KEY UPDATE would be better, especially as the two columns' INDEX can be switched to UNIQUE. So I went ahead and modified the INDEX to an UNIQUE one, then rewrote the query as follows:

$db->query("INSERT INTO ulogs (uid,date,invalid,unique,nonu,ea,ref,bo) VALUES (666,'2018-04-18',1,0,0,0,0,0) ON DUPLICATE KEY UPDATE invalid = invalid + 1";

However, mysql explain is showing that the INDEX is not used in this case. Also for some reason it shows the query as an INSERT operation, even though an UPDATE is performed:

Also, by running the query in phpmyadmin, for some reason it shows that 2 rows have been INSERTed, while in fact only 1 row was UPDATEd with the data:

Bottom line is, which operation would be the fastest in terms of code optimization?
Thank you.


texts: Tracing what went wrong with MySQL server?

texts: I have a local in-house client application that relies on steel server MySQL 5.6 database in our closet.  This morning, people suddenly couldn't log in.  The client server said it couldn't connect with the database.  On the database server itself, the MySQL service was running.  I stopped and started it again and things went back to normal.  However, I don't know what went wrong and I'd like to know why this happened to see if it's something I can prevent in the future.  What are some diagnostics I can do to see what happened with the server this morning?


texts:
I need every 13th row as new column. I am not sure how this can be done using Pivot or transpose. Number of rows are not fixed.

Please guide me the way.
Thanks

Data is as shown below where every 4 row gets repeated

ID    Value
AA     A
BB     B
CC     C
AA     D
BB     E
CC     F
AA     G
BB     H
CC     I

 it should be as below

ID    Value1  Value2     Value3
AA     A           D           G
BB     B           E           H
CC     C            F              I


texts: Return a fully dynamic table from an Oracle function

texts:
I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of type varchar.

As far as I have seen, I have to always to create an object/record and a table type of it. This means that my idea won't work? The underlying goal is to pass a system command output back to a callee as a table.

Edit: more on the task. I want to issue an OS command, consume the output and return it back as a table. The output from the OS command is going to be CSV-formatted data. At the time of execution I do not know the amount of rows to be returned but only the amount of columns which is passed as the second arg. I was thinking about using Java with a dynamic STRUCT and an ARRAY containing them. Though I would prefer the former approach.

It should look like this:

create function(clob query, list of varchars cols) returns table
begin
  execute system command(query, cols);
  examine sysout from command;
  return tabular data from syscmd as table;
end


texts: Can an entity in an entity-relationship diagram have more than one relationship?

texts: I am practicing drawing entity-relationsip diagrams and in the example it states "many employees work on a project, and employee can work many projects but can only be assigned to at most one project at a given time." Does the final statement after the "but" mean that there is another relationship?