texts: Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3

texts:
I keep getting this pg_restore: [archiver] did not find magic string in file header when i execute a pg_restore -Fc -U "username" -d "dbname" "filename".dmpon my staging DB.

These DB are running on two separate AWS instances with windows server 2008 for both environments(staging and distribution). The postgres version used on distribution is the 9.2 and the version on staging is 9.3.

I think this issue could be related to the fact that i get several pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used warnings when i execute a pg_dump -Fc -U "username" -w "dbname" > "filename".dmp on the distribution DB.

Any help is appreciated.


texts: MySQL Preference Pane completely erased my entire /usr/local directory

texts:
During a one-click uninstall of MySQL via the MySQL preference pane in macOS Mojave, I watched in horror as, one by one, the contents—all directories and files—of my usr/local directory disappeared forever. This include Homebrew and all of my brew installations/caskets.

In the MySQL Preference Pane, all of the directory settings explicity pointed to subdirectories of usr/local/mysql/..., so I figured nothing outside of that subdirectory would be deleted. Besides, it did not appear to exist in the first place. I had no idea the entire contents of my usr/local directory would be permanently trashed.

Question: Now that I've lost my entire usr/local directory, is this behavior cause for concern for others, or is this the expected behavior of MySQL uninstallation via the macOS MySQL Preference Pane?

Could a nefarious symlink have caused this?


texts: Measuring MongoDB I/O and CPU performance

texts:
I am new to MongoDB and I'm experiencing this NoSQL db through a series of very simple queries, with an Oracle exported data set.

Aside measuring the execution time with primitives like db.system.profile.find(), I would like to measure I/O performance and system calls. Is there a simple, relevant, way to do this? What would be the best approach?

Also, I have noticed that the size of the collections are way much bigger than in Oracle. For example, an Oracle table (average row length x number of rows) translates to a 5x time bigger collection in MongoDB. Why? Are there any reasons for this?

Thanks for your insights.

Cheers


texts: How to query last restore date in SQL Server?

texts: Is there a T-SQL query that shows the last restore datetime for a certain database?


texts:
I have bunch of sql-scripts used to create various tables, procedures, functions and views. Everything is in separate files; is there a good way to bundle these and run each file in a specific order? Merging/concatenating all the files into one is also a possibility, but I'd prefer a bundle if possible.

Example (fictional/pseudocode): Master.sql contains the following:

src: DDL/table1.sql
src: DDL/table2.sql
src: fns/function1.sql
src: fns/function2.sql
src: views/view1.sql

...and so on. Running Master.sql would then execute each of the scripts listed in the file in the order listed. Is something like this possible?


texts: MySQL Trigger to Update a Row in a Separate Table via Joins if Only One Row is Updated

texts:
I am trying to create a trigger so that when a column in a table called bookings is updated it will update a a calculated price value in table departments. I currently have my tables set up as so

CREATE TABLE bookings    (
    idBookings int(4) AUTO_INCREMENT,
    idMember int(4),
    departure_date DATE,
    idVehicle int (2),
    location VARCHAR(45),
    checkout_signed_faculty BOOLEAN,
    checkout_signed_tfbs BOOLEAN,
    odemeter_start int(5),
    odemeter_end int(5),
    maintenance_needed BOOLEAN,
    fuel_purchased DOUBLE(3,2) NOT NULL,
    card_number BIGINT(16),
FOREIGN KEY (idMember) REFERENCES faculty_members(idMember),
FOREIGN KEY (idVehicle) REFERENCES vehicles(idVehicle),
FOREIGN KEY (location) REFERENCES locations(location),
    PRIMARY KEY (idBookings)
);
CREATE TABLE vehicle_type (
    type VARCHAR (20),
price DECIMAL(3,2)
PRIMARY KEY (type)
);
CREATE TABLE vehicles (
    idVehicle int (2) AUTO_INCREMENT,
    type VARCHAR (20),
    registration VARCHAR(7),
    price DOUBLE(2,2),
PRIMARY KEY (idVehicle)
);
CREATE TABLE departments (
department VARCHAR (20) NOT NULL,
owed_to_tfbs DECIMAL(3,2) NOT NULL,
PRIMARY KEY (department)
);
CREATE TABLE faculty_members (
    idMember int (4) AUTO_INCREMENT,
    member_name VARCHAR(45) NOT NULL,
    department VARCHAR(20),
PRIMARY KEY (idMember),
    FOREIGN KEY (department) REFERENCES departments(department)
);

And my trigger contains a lot of joins (I am unsure whether this is because of a possible flaw in the design) but is as so...

DELIMITER $$
CREATE TRIGGER owed AFTER UPDATE ON bookings FOR EACH ROW
IF NEW.odemeter_end <> OLD.odemeter_end THEN
UPDATE departments
   left join faculty_members on departments.department = faculty_members.department
   inner JOIN bookings on faculty_members.idMember = bookings.idMember
   inner JOIN vehicles on vehicles.idVehicle = vehicles.idVehicle
   inner JOIN vehicle_type on vehicles.type = vehicle_type.type
SET departments.owed_to_tfbs = ((((NEW.odemeter_end - NEW.odemeter_start)
    * vehicle_type.price)
    + departments.owed_to_tfbs) - bookings.fuel_purchased)
WHERE NEW.odemeter_end <> OLD.odemeter_end;
END IF
$$

While the calculation is correct it is applying it to all the owed columns that appear in the department table that has a booking. I am unsure as to how to only apply this to only the updated rows
Can any body help or offer any advice? I so far cannot find anything that works


texts: MySQL Server hitting 100% unexpectedly (Amazon AWS RDS)

texts:
We've been struggling with this issue for months.

This week we upped our RDS instance to the highest performing instance and although the occurrences have reduced, we're still seeing our database suddenly hit 100% CPU Utilization.  This seems to come out of nowhere, sometimes at 2am, sometimes at midday.

I've ruled out a DOS - our site access logs show normal traffic.

I've ruled out memcached sudden death as hits and misses continue at a normal rate.

SHOW PROCESSLIST while we have issues reports about 500 queries in the queue. If I kill them off or restart the server, they keep coming back.  Eventually out of nowhere our server goes back to normal CPU utilization. Sometimes this takes up to 3 hours.

Our bad performing queries typically take 0.02 seconds to execute when the server is normal but while we're in this 100% CPU state, those queries never finish executing.

Does anybody know anything about MySQL query optimization? Could it be the server is deciding to use different indexes all of a sudden, which puts it into a spiral?


texts: How to remove multiple records from one table based on the non-existence of a record in another table?

texts:
I have two tables, 'Products' and 'Details'.

--------------------------
Products [ID, Name]
--------------------------
1, Link's Simple Syrup
2, Team Shirt
3, Animal DVD

-----------------------------------------------------------
Details [ID, Product_id, attribute_name, attribute_value]
-----------------------------------------------------------
1, 1, potency, "high"
2, 2, color, "blue"
3, 2, size, "large"
4, 2, words, "go team"
5, 3, title, "Animals with wide angle lenses"
6, 3, price, "1 million dollars"

So say 'Team Shirt' gets removed from the Products table, and now I need to clean up (remove) the 3 leftover Details it used.
Basically I want to look at each record from the Detail table and see if it's product_id corresponds to an existing ID in the products table.
If I can construct a SELECT statement that returns only these orphaned Detail records, then I can change select to DELETE and remove them.
That's my thinking anyway, any help creating the SQL query to accomplish that or suggestions on a different approach altogether if necessary would be much appreciated.

pseudocode of the logic might be something like this:

For each record in Details{
    if post_id is a NULL ID in Products delete this record
}


texts:
How can I normalize a table that has four columns for logging the latest activity for a single row:

Created On
Created By
Modified On
Modified By

We have dozens of tables, all with these 4 columns. Is there a way or pattern to normalize this in an efficient and flexible manner?


texts: Crash of VMWare Workstation when generating EER Diagram

texts:
I'm facing an issue when I'm generating an EER diagram with MySQL Workbench 8.0
I'm using MySQL Workbench on a local Windows 10 virtual machine. My VM is running with VMWare Workstation 12.5.0.
I select some tables, a stored procedure and generate the diagram. In the progress dialog, I can see the Reverse Engineering is successful. When it comes to place Objects on Diagram, the virtual machine crashes and shows this message :

VMware Workstation unrecoverable error: (svga)
Exception 0xc0000005 (access violation) has occured


texts: Results of SAMPLE clause using a DBlink between 2 Oracle databases not randomised

texts:
I'm using the SAMPLE clause to retrieve random rows from a table. It works fine except when the table is from another database. I'm using a database link between 2 Oracle 11g databases (11.2).

This query returns different random rows everytime I run it:

select * from myTable sample(10);

But this one always returns the same rows and in the same order:

select * from myTable@myDbLink sample(10);

I expected at least the same behavior as the tables are on the same version of Oracle. Why isn't it working? Is there a specific way to write this query so it can work with the link?


texts: "Read by other session" wait event occurring in a particular Data Block of a Table

texts:
I'm using Oracle 10.2.0.4 with Oracle EBS R12 Application on it.

Recently we found sudden slowness in running certain Standard Concurrent Programs.

When we check from the Snap_id, we found "User I/O" wait with "Read by other session" wait event occurring on certain SQL_ID's.

From this we found a particular Data Object was causing this issue which is unfortunately a Oracle Standard table.

On further analysis we found three Data Blocks which was causing this wait event. All these Data blocks belongs to the same Data object(Table).

In such case what should we need to do ?


texts:
I was trying to run through the docs for manually working with the MONyog internal sqlite dbs for purposes of configuration automation.

At the point trying to open the data file I get an error

$ sqlite ./connection.data
Unable to open database "./connection.data": file is encrypted or is not a database

I've made sure the actual file is world readable (and writable even).  I've tried both the stock SQLite command line client as well as the Firefox plugin suggested in the doc mentioned about.

I'm pretty sure this file is a SQL lite database file

$ head -c 16 ./connection.data
SQLite format 3

Any clues on how to resolve this?


texts: Add column to a table for every shema in an oracle db 11g

texts:
Our oracle db has various users with same schema. I would like to add a column to one table in those schemas. The same column to same table in every schema. I know that I can do that manually, like that

alter table user1.EMER_COMP add TELEFONE  varchar2(20) NULL;

alter table user2.EMER_COMP add TELEFONE  varchar2(20) NULL;

...

Is there an easier way to do this?


texts: Issues setting up Oracle 18c XE on Windows Server 2012 r2

texts:
I've been running into some problems getting my Oracle installation up and running on Windows Server 2012 r2 at work. Initially, I had installed Oracle on my Win10 laptop for test purposes - I connected to it using Toad for Oracle and had no problems.

However, after installing on Windows Server 2012 r2 I noticed that there were no control files and/or database files in the %ORACLE_BASE%\oradata\XE directory. In addition, I noticed that my listener.ora file was missing an entry for the XE service instance - once this was added, I could connect via SQLPlus but then the database could not be mounted because there were no control files. I even tried using DBCA to create a new database but ran into invalid memory parameter issue.

I'm starting to wonder if 18c is even compatible with Server 2012 r2 even though the documentation says it is - am I missing some configuration? Any help would be greatly appreciated - I've been trying to figure this one out for the last few days. Thanks.


texts: Need Expert suggestion for choosing best DB scalability

texts:
I am looking best opinion on choosing best DB scalability based on below requirement.

Application
App install tracking platform (Ad serving).
It's used to track click, impression, app install.

Framework
Spring Boot, Java 8 with Tomcat 9

HAProxy
Application Load Balancing.
At present there are 3 nodes connected to it.

Database
MySQL

We are expecting 1000 clicks per sec (it can go up to 10,000-100,000 clicks per sec). So please let me know best solution to active it.
As of now I am little confused which one I used to go either:

MySQL Master Master Replication
MySQL Master Slave
MySQL Cluster

Please assume we have 85% read and 15% write. Read is for checking campaign daily, Monthly or total conversion capping before redirecting clicks to landing page.
Also please suggest whether MySQL is best fit for these type of requirement or not. If not which one is best.
Also please highlight what will be the:

Backup Strategy
Future table or row adding
Network Failure


texts: Copy Azure SQL Database Between Azure Subscriptions

texts:
Getting straight to the point, is this even possible?

I have successfully copied an Azure SQL database within the same subscription.  In doing so, I kept it simple by copying the database to the same server (within the subscription).  I used the Powershell commands Add-AzureRmAccount and New-AzureRmSqlDatabaseCopy.

I have tried executing the Add-AzureRmAccount twice (once for each subscription) but this seems to replace the first subscription with the second in the context as the New-AzureRmSqlDatabaseCopy command reports that it cannot find the specified resource group (which exists in the source subscription).

All that said, if this is something that is possible, what are the steps / commands that I need to execute?


texts: Fetching recent/historical queries run on a database from an application

texts:
I'm getting some database errors from an application related to unique constraints however none of the records have any duplicate values so I want to check what query was run that produced the error.

How can I check a history log of queries to pinpoint the problem? I'm using Oracle 12c


texts:
Hoping you can help me. I have a column in a table called indicator. (Data type: NVarchar(80))

Within this column I have some numbers the length of this number is 6 characters in length. The total the Length of indicator column can vary from 0 to 56 Characters long.

Below is small Subset of the data. What I would like to do is produce another column which extracts the numbers from indicator column.

Row 1 : GB-JP--123456
Row 2 : 401015 - GB-NA
Row 3 :
Row 4 : 999999 - LX - UK

I tried the following query but I get this error

select Substring (Indicator, PATINDEX('%[0-9]%', Indicator), LEN(Indicator)) from Table1

However I am presented with the following error

  "SUBSTRING": invalid identifier"


texts: Will Derby, H2, or SQLite give faster load time and/or smaller file size than HSQL?

texts:
I have some flat files with the following columns; 3 integers, 3 reals, and 1 varchar(20). For querying I need an index that contains both 1 of the integer columns and the varchar column.  Each file is around 1.8GB in size with around 38 million rows.

Currently I am using a HSQL(Standalone) database to load a file for processing; one database per file. It is very slow to load (120+ min) the file and results in a 4.7GB database file when the database is created with the following options.

"Properties" -> {
  "check_props" -> "true",
  "shutdown" -> "true",
  "hsqldb.default_table_type" -> "cached",
  "sql.syntax_mss" -> "true",
  "hsqldb.log_data" -> "false",
  "hsqldb.inc_backup" -> "false"
  }

The file read in batches of 100k records.  The read is very fast (almost instant) so I do not think it is the read that is slowing things down.  It also takes a very long time to close the connection to the database.

I have the option to use Derby, H2, or SQLite.  Will any of these result in faster load time and/or smaller database file size in this scenario?  If so what are the connection string options that should be used to achieve this?  Alternatively, are there different connection string options I can use with HSQL(Standalone) that will reduce the load time and/or database file size?

Driver information added.

JDBCDriver[
  "Name" -> "HSQL(Standalone)",
  "Driver" -> "org.hsqldb.jdbcDriver",
  "Protocol" -> "jdbc:hsqldb:file:",
  "Version" -> 3.1,
  "Description" -> "HSQL Database Engine (In-Process Mode) - Version 2.3.3 -  This ...",
  "Location" -> "C:\... "]

Driver information for the other options available to me.

Derby

JDBCDriver[
  "Name" -> "Derby(Embedded)",
  "Driver" -> "org.apache.derby.jdbc.EmbeddedDriver",
  "Protocol" -> "jdbc:derby:",
  "Version" -> 3.1,
  "Description" -> "Derby Database Engine (Embedded Mode) - Version 10.12.1.1 - This...",
  "Location" -> "C:\... "]

H2

JDBCDriver[
  "Name" -> "H2(Embedded)",
  "Driver" -> "org.h2.Driver",
  "Protocol" -> "jdbc:h2:",
  "Version" -> 3.1,
  "Description" -> "H2 Database Engine (Embedded Mode) - Version 1.3.176 - This...",
  "Location" -> "C:\... "]

SQLite

JDBCDriver[
  "Name" -> "SQLite",
  "Driver" -> "org.sqlite.JDBC",
  "Protocol" -> "jdbc:sqlite:",
  "Version" -> 3.1,
  "Description" -> "SQLite using Zentus-derived JDBC Driver - Version 3.8.11.2",
  "Location" -> "C:\..."]

Additional variants include the below. However, I need it all to run on the client's computer. I believe this excludes server and webserver modes.

{"Derby(Embedded)", "Derby(Server)", "H2(Embedded)", "H2(Memory)",
 "H2(Server)", "HSQL(Memory)", "HSQL(Server)", "HSQL(Standalone)",
 "SQLite", "SQLite(Memory)"}


texts: Does MySQL InnoDB compression combined with Windows compression save more space?

texts:
We have a very large MySQL InnoDB database, compressed using the following syntax at the end of the table declaration:

  ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

We also have windows compression enabled on the logical disk drive that the database resides. It is an 800GB SSD hard drive. The drive is used solely for the database, no other files reside on it.

Does having the two compressions enabled help to save more space, or is it hurting or not beneficial at all?


texts: Oracle MODEL clause giving unusual output

texts:
This is my table structure:

Name         Null Type
------------ ---- -----------
PAID_MM           VARCHAR2(2)
GA                NUMBER
SA                NUMBER
MA                NUMBER
TOTAL_AMOUNT      NUMBER
AMT_VAR           NUMBER

and this is my data:

PAID_MM  GA     SA     MA     TOTAL_AMOUNT  AMT_VAR
01       98.5   48.16  161.47  308.13          0
02       116.34 53.14  115.46  284.94          0
03       288.03 25.31  136.04  449.38          0
04       138.41 118.84 148.5   405.75          0
05       75.69  190.74 51.7    318.13          0
06       451.18  0     214.84  666.02          0

I want calculate the AMT_VAR as (TOTAL_AMOUNT of current month - TOTAL_AMOUNT of previous month / TOTAL_AMOUNT of previous month) *100.

I am using Oracle MODEL clause as this:

SELECT
  PAID_MM,
  TOTAL_AMOUNT,
  TRUNC(AMT_VAR*100, 2) AS VARIAN
FROM
  AGNT_PAID_SUMM
MODEL RETURN UPDATED ROWS
DIMENSION BY (PAID_MM)
MEASURES (AMT_VAR, TOTAL_AMOUNT)
RULES
(
AMT_VAR [PAID_MM] = ((TOTAL_AMOUNT[CV(PAID_MM)] - TOTAL_AMOUNT[CV(PAID_MM)      -1])/TOTAL_AMOUNT[CV(PAID_MM) -1])
)
ORDER BY PAID_MM

and I am getting the following output:

PAID_MM  TOTAL_AMOUNT VARIAN
01       308.13       NULL
02       284.94       NULL
03       449.38       NULL
04       405.75       NULL
05       318.13       NULL
06       666.02       NULL
07       339.47       NULL
08       445.86       NULL
09       548.52       NULL
10       363.94       NULL
11       362.8        -0.31
12       409.37       12.83

This looks quite weird to me. Can somebody help me understand why it's not working for all the rows except the last two ones?

I am using Oracle 12c.


texts: Denied updating a field that is modified within a BEFORE UPDATE TRIGGER

texts:
I have a table with 4 columns:

ID
FirstName
LastName
Synced

If row contains synced information, then Synced is 1, otherwise 0.

I have a BEFORE UPDATE TRIGGER to catch when FirstName or LastName is modified to change Synced from 1 to 0:

BEGIN

IF
    NEW.FirstName <=> OLD.FirstName OR
    NEW.LastName <=> OLD.LastName
    THEN
        SET NEW.`Synced` = 0;
END IF;

END

The trigger works as expected, if row modified, then Synced = 0, but I cannot update Synced value to 1.

If I run:

UPDATE `x` SET `Synced` = 1 WHERE `ID` = 4367

I got 0 affected rows, 0 errors.


texts: How to change the default timezone in Amazon Redshift?

texts:
Setting a timestamp column to SYSDATE by default, stores it as UTC. Is it possible to change the timezone so SYSDATE stores dates and times to a different timezone?

So far, I've checked the SET command but I'm not sure if it is possible to use it to change the timezone.


texts: Issue with variable length string between compatibility level 80 and 100

texts:
I was getting tons of issues related with a specific scenario on my current enterprise.

We have a SQL Server 2008 and we want to move to SQL Server 2016, so we are moving the compatibility from 80 to 100, but some of our store procedure are having a problem, demonstrated by the code below:

CREATE TABLE #PRUEBA (texto1 char(30), condicion char(30))
INSERT INTO  #PRUEBA VALUES ('PFI','1')
INSERT INTO #PRUEBA VALUES ('CFI','2')
SELECT
    CASE
        WHEN condicion= '1' THEN texto1
        ELSE 'TFI'
    END
FROM  #PRUEBA
DROP TABLE #PRUEBA

Compatibility -> 80

'PFI                           ' -> char of 30 length
'TFI                           ' -> char of 30 length

Compatibility -> 90

'PFI                           ' -> varchar of 30 length
'TFI'                            -> varchar of 3 length

Compatibility -> 100

'PFI                           ' -> varchar of 30 length
'TFI'                            -> varchar of 3 length

Why did this happen?

Can we keep the blank spaces to get the same result as compatibility 80, without change the query, just only doing a configuration on the server?

Is there any solution to get the same result on compatibility 100 like compatibility 80?


texts: After modifying the throughput of a DynamoDB table, where can I see the new throughput value before it gets active?

texts:
After modifying the throughput of a table in DynamoDB, the "Modify Throughput" is greyed out:

Where can I see the new throughput value before it gets active? Sometimes it takes a while for a throughput to get applied, especially for large values, and I would like to check whether the person (myself or colleague) who changed the throughput didn't make a mistake.


texts: How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

texts:
I am  a total newb, and I couldn't find a good way to do this anywhere.  I have a database table that contains statistics that are recorded at various time throughout the week.  The reporting week starts on Thursday.  The table contains a datestamp column (date) that stores when the data was recorded.

I need to pull the data for a given week (reporting weeks start on Thurs).  I have written the following query:

   SELECT *
FROM `table`
WHERE 1 = CASE
  WHEN WEEKDAY(NOW()) = 0 THEN DATEDIFF(NOW(),`date`) BETWEEN -2 AND 4
  WHEN WEEKDAY(NOW()) = 1 THEN DATEDIFF(NOW(),`date`) BETWEEN -1 AND 5
  WHEN WEEKDAY(NOW()) = 2 THEN DATEDIFF(NOW(),`date`) BETWEEN -0 AND 6
  WHEN WEEKDAY(NOW()) = 3 THEN DATEDIFF(NOW(),`date`) BETWEEN -6 AND 0
  WHEN WEEKDAY(NOW()) = 4 THEN DATEDIFF(NOW(),`date`) BETWEEN -5 AND 1
  WHEN WEEKDAY(NOW()) = 5 THEN DATEDIFF(NOW(),`date`) BETWEEN -4 AND 2
  WHEN WEEKDAY(NOW()) = 6 THEN DATEDIFF(NOW(),`date`) BETWEEN -3 AND 3
END

This appears to work on initial testing.  But I am not sure it is the best way to go about it.  I don't know much about MySQL performance, but there will be over a hundred thousand records to filter.  Will this query be real slow due to the number of conditions checked?

The NOW() function is used when pulling the most current report- however, it some cases I will need to do reports for other weeks- so I would substitute another date into the place.

Also, doing it this way requires re-writing the query if the reporting week changes- say the starting day changes to Wednesday.

I can't use the WEEK() function because you can only start a week on Sun or Mon with it.

Any ideas to improve this query are much appreciated!

Other Notes: Currently using MariaDB 5.3.


texts: Why do we have SHRINKDATABASE command in SQL Server

texts:
I have read across the internet where Finest Developer says shrinkdatabse should be avoided as it causes increase in fragmentation and decrease in performance .

I would like to know from you guys have you ever used the shrink database command if yes then in which practical scenarios we should be using them.


texts: Efficient way to fetch/update X records in a loop

texts:
What I need is a simple process that handles requests.
I have setup a simple table as follows:

CREATE TABLE Requests (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  request_param VARCHAR(250) NOT NULL,
  status ENUM ('requested', 'done', 'failed'),
  when TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished TIMESTAMP NULL
);

What I am doing is something like:
SELECT * FROM Requests where status = 'requested' ORDER BY id LIMIT 1

This way I pick up each request one at a time.
Then I do UPDATE Requests set status='done', finished=NOW() where id = 123;
(the id=123 assume it is from the SELECT request).
This works but I am not sure if this is another approach.
What would be better? E.g. doing a LIMIT 100? My concern is that the order by is not a good idea as the data grow. Does it make an impact if I always process e.g. per 100? I still have to order by right?


texts: Which one is better performance and (approach): store as rows or as a varchar column

texts:
OptionA:

TableA:

TableA_ID (PK,int)
..

TableB:

TableB_ID (PK,int)
..

TableC:

TableC_ID (PK,int)
..

TableD:

TableA_ID (FK, int)
TableB_ID (FK, int)
TableC_ID (FK, int) (Nullable)

Composite Key on (TableA_ID,TableB_ID,TableC_ID)

I am thinking to design TableD as the following (OptionB)

TableD:

TableA_ID (FK, int)
TableB_ID (FK, int)
TableC_ID (varchar(max) ) (Nullable)
and remove the composite key.
So, TableC_ID column will have something like these:
{1,2,3,4,....}

My application will need to use TableD as search a value in TableB_ID and/or TableC_ID column by giving the key for value in TableA_ID.

I am just wondering which option will be better performance.


texts: What's wrong with my provided admin password for Oracle Apex instance administrator account?

texts:
I have installed Oracle Database 19c Enterprise Edition on Oracle Linux 7.8.I am now trying to install Apex 20.1 .
Got to the chapter "5.4.2.3 Running apxchpwd.sql" to set the instance administrator password. Executed the @apxchpwd.sql command, it asks me for user name, e-mail and password but then it gives an error as if the password does not have enough complexity, although the password I provide is literally H29dN8%Ih^^IV$Y3$j4Mx. Tried various other passwords, nothing seems to be acceptable.
What can be the problem?
Here is how I got to the error:
[oracle@oraclemachine apex]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 25 09:50:03 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN] apexadmin
User "apexadmin" does not yet exist and will be created.
Enter apexadmin's email [apexadmin] a@b.c
Enter apexadmin's password []
--------------------------------------------------------------------------------
PASSWORD_COMPLEXITY_ERROR
* PASSWORD_ONE_ALPHA_ERR
* PASSWORD_ONE_PUNCTUATION_ERR
* PASSWORD_ONE_UPPER_ERR
* PASSWORD_ONE_LOWER_ERR
--------------------------------------------------------------------------------
declare
*
ERROR at line 1:
ORA-20001: Password validation failed.
ORA-06512: at line 30
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_INT", line 3744
ORA-06512: at line 20


texts: PostgreSQL: how can I create an index for querying variable combinations of booleans

texts:
I have a table of people, each person has a color and they seek various other colors and require various skills.
So I can query this I've created a table like so:
person
id, color_is_red, color_is_green, color_is_blue, color_is_yellow,
seeks_red, seeks_green, seeks_blue, seeks_yellow, seeks_technical, seeks_creative, seeks_leadership
I can then query the table like so:
-- Searching for green & yellow people that looking for red people with skills creative or technical

select id
from person WHERE

    color_is_red = false
    AND color_is_blue = false
    AND seeks_red = true
    AND
    (
        seeks_creative = true
        OR seeks_technical = true
    )
limit 200

I'm unclear if this is the best way to structure this data. I also don't know how I can index this. I had hoped to use a BLOOM index but bloom doesn't support booleans. I think maybe I need a GIN index as I'll be querying different combinations of the colors depending on whats required however I've tried creating a gin_btree index but the query planner isn't using it. Another option would be a to use a bitset but I'm not sure how I would do this or index it.
Any help would be greatly appreciated!
Thanks very much
update:
I have created a single btree index for each column but the query planner doesn't seem to be choosing to use them. Here is the explain output:
https://explain.depesz.com/s/TZLG
update 2
I've tried to use intarray to index int[] but I've not had much look with basic queries taking over a minute to execute: https://explain.depesz.com/s/RyBn


texts: Aggregation vs Ternary relationship - When to use?

texts:
I am reading a book about database systems and in the chapter covering database design using ER, the author aims to explain when to use aggregation versus ternary relationship. In short, each Project entity is sponsored by one or more Department entities and each Department can sponsor zero, one or more Projects. Each Sponsorship relationship has a Monitors relationship, which connects Employees with Sponsorship.

This can be expressed in 2 ER diagrams:

Now, we want to express an additional constraint that each Sponsorship relationship is monitored by at most one Employee.The author states that this cannot be done with ternary relationship. I can't wrap my head around why. To express this in diagrams, I would just point an arrow from Sponsors2 to Emplyees in ternary relation. Does this not satisfy the additional constraint?


texts: Mirroring sql server , how to change the port?

texts: I have two servers on the same cluster, in fact my goal is to set up "high availability", but the databases that I have integrated are not synchronized, so I think it is " mirroring "that I created but the port on which the two servers is not the same, I have a 1433 on the other and a 5022 on the other. what should i do ?


texts: 100x less insert performance with Galera multi-master

texts:
I am running an application which inserts a lot of rows and we were experiencing some low performance. So i started benchmarking the Galera Cluster with sysbench and oltp_insert.lua (practically inserts with random key).

The performance of the cluster is really bad.

With the same test:

As a standalone DB i can perform about 35000 tps
With Galera multi-master the performance is: 350 tps

The table structure is this one:

       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

What i tried so far (with no success):

increase wsrep_slave_threads=16
no matter how many client threads i use, the TPS is still 350 (looks like the transactions are serialized somehow by the DB)
set wsrep_sync_wait=0 (although i perform no reads)
the db does not use binlog, its not enabled.

Configuration:

[mysqld]
binlog_format=ROW
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
datadir=/var/lib/mysql

innodb_buffer_pool_size = 300MB
innodb_large_prefix=1
innodb_file_format=Barracuda

transaction-isolation = READ-COMMITTED

wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_slave_threads = 16
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_convert_LOCK_to_trx=0
wsrep_causal_reads=OFF
wsrep_sst_method=xtrabackup-v2


texts: AlwaysOn Availability Groups on Secondary Replica

texts:
We have a Primary Server A with a Database. It has AlwaysOn ReadOnly Asynchronous which moves data to Server B.

Out of curiosity, is it possible to place Server B on AlwaysOn and move it to another Server C Asychronous Read Only?

We want to reduce the workload/performance on our main server A OLTP; however, we want two replicas. We're just curious.

So AlwaysOn from

Server A → ServerB → ServerC

Additional details

We want to make Database B as a primary replica on AG, which then passes to secondary Database C.


texts: click any database get error :- #1932 - Table 'ass_test.reg' doesn't exist in engine

texts:
HELLO,
In phpmyadmin click any database show error :- #1932 - Table 'ass_test.reg' doesn't exist in engine
how to solve this error.
Please help me.
THANKS.


texts: Toad for Oracle 64-bit : 1603 - Fatal error during installation

texts:
I'm trying to install Toad for oracle client, my Toad version is 12.1 for 64 bit system, i have succeeded to install oracle client 12c for 64 bit system, but i have stumbled by a problem when installing the Toad, the error is :

The content of the 'Toad for oracle 12.1 64bit.msi.lo' file is available via this link (beacause his size is big and we can not write it here):

Toad for oracle 12.1 64bit.msi.lo

Can anyone here help me please, Thanks in advance.


texts: Multiple transaction logs and preferred order of use

texts:
Here's my situation:  I have a SQL Server 2012 installation with a relatively small (185GB) SSD volume, and a large (3TB) RAID10 spinning disk volume.  I currently have tempdb data and log files on the SSD, as well as the transaction logs for all user databases.  One database is very large and does massive batch processing; it collects network switch metrics every 10 minutes and loads it into the database.  The database is set to SIMPLE logging.
The app owners recently added a second collector node, causing the # of active transactions to skyrocket.  This in turn resulted in the transaction logs filling up the SSD drive, causing the user database and tempdb to be unavailable due to insufficent space.
I added a log file to tempdb and the user database on the RAID10 volume to get both databases available again.
I've read Multiple Transaction Log files and performance impact, and especially Mike Fal's answer to it.  But, I'm confused about a few things in Mike Fal's answer.

Once it does, it will attempt to return to the first available VLF in the file. If one is not available, the log file will then grow.

I read the answer as "try to re-use a VLF in the current file, and then try to grow the current file, and if the current file is able to grow, don't switch files".
Is there a way to indicate to SQL Server to prefer the re-use of the log files on the SSD, if available, rather than grow the file on the spinning disks?   Ideally I'd like to set the files on the SSD to almost-filling-the-disk, and turn auto-growth off, and then only use the files on the spinning disks as overflow when necessary - and let the files on the spinning disks grow as needed, as I have 2.5TB+ free on that volume.
As I read it though, if I set the files on the spinning disks to auto-grow, I won't ever fall back to the files on the SSD - I have to set a hard cap on the files on the spinning disk volume in order to someday fall back to the files on the SSD.  Due to the architectural change, I'm not sure how large the transaction logs will need to grow; the current hardware was sized for one collector node, and they didn't inform me they were adding a second collector node until after the disk filled.


texts: Compare two tables amount with respect to priortization of FundType

texts:
I have two tables one is PaymentDetail and another is Ledger

Here i want to prioritize paymodetype in manner that cash should be considered first for
R,A and then I FundType, then Credit Card, then DD and then Cheque.

I want that amount received in cash should first be considered for R then A and lastly for I FundType.Another example

Here is sqlfiddle as well. Can it be solved by SQL only. I don't want to use Cursor.
Edit:
I have updated the sqlfiddle with what i have tried. I am trying to use join due to which multiple rows are obtained in my result with respect to Payment Detail. I want to span amount of one row of payment detail over Ledger then second row should be used.
How can i achieve that?
Result of current sqlfiddle is


texts: Replication of databases with specific prefix on master

texts:
There are options to dynamically set variables on replication slaves, like replicate_wild_do_table and replicate_wild_ignore_table.
But, are there any options for replication master to dynamically set database names which begin with same prefix, and not repeating:

replicate-do-db=prefix_db1
replicate-do-db=prefix_other_db
replicate-do-db=prefix_third_database
...

in my.cnf.


texts:
I am exporting excel sheets through SSIS export, and creating excel sheet at run time using execute sql task.
Now the client needs a logo(image) to be attached to the excel sheet on top of data part. Do we have any way out to do that.

NB: we cannot use "file system task" to copy a pre-existing excel file as we have to create the task on runtime. cannot copy a excel sheet from any other location.

Thanks


texts: Netezza Equivalent to T-SQL ROWVERSION or PL/SQL ORA_ROWSCN

texts: Is there a Netezza equivalent to the MSSQL ROWVERSION or the Oracle ORA_ROWSCN?  Essentially I'd like to know if a record changed and be confident that it changed.  Using a checksum isn't reliable 100% of the time.


texts: Get new ID for inserted ID that comes from another table?

texts:
Simple question but I'm a newbie. I have existing orders table 'Orders'(table1)  and it has an OrderNumber as a column and the orders are number up 156.
I have a existing table called 'orders'(table 2) from another website. I have to insert these orders from table 2 into table one and the OrderNumber for the inserted new order has to be 157....and so on for all orders coming in. I have asked this question and get answers that make no sense. Anyone?

http://sqlfiddle.com/#!6/d423d/11/0

I got it to work but why did it increment with 50? It went from 156 to 206. I only want it to take the next number in sequence.


texts: What to consider when generating rows via cross joins vs recursive unions?

texts:
These two queries return the same result (generating a sequence of integers):

1
2
3
4
5
...
16

What performance/other considerations should be taken into account when choosing one or the other?

A (from a blog post)

;with
T1(number) AS ( SELECT 1 UNION ALL SELECT 1 ),
T2(number) AS ( SELECT 1 FROM T1 a cross join T1 b ),
T3(number) AS ( SELECT 1 FROM T2 a cross join T2 b ),
Nums(number) AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) from T3 )
select * from Nums;

B

;with Ints(i) as (
    select 1
    union all
    select (i + 1)
    from Ints
    where i < 16
)
select * from Ints;

I prefer B...


texts: Postgres : Executing SELECT within a transaction does not return most recent rows

texts:
Given a table layout that looks like this

inv_items
=========
* id bigserial
sku character varying(22)
name character varying(32)
...

inv_items_stock
===============
item_id bigint (inv_items)
qty int
...

inv_items_group
===============
group_id bigint (inv_items)
item_id bigint (inv_items)

where an item may be a "stock item" or a "group item" (with compound items...) I have a trigger that checks before insert / update on each inv_items_stock and inv_items_group to make sure that some conditions are met; for example, a stock item cannot be a group item.

The problem is that, in my program, I run a batch of inserts inside a transaction; first I insert into inv_items, then into one of the other table. Unfortunately, the SELECT inside my trigger function  does not find any item that I just inserted within the transaction, thus always fails.

For example, here is the group item check trigger

CREATE FUNCTION inv_check_set_group_items() RETURNS trigger AS $INV_GRP_SET$
DECLARE found_item bigint;
BEGIN
   SELECT id INTO found_item FROM inv_groups_vw WHERE id = NEW.group_id;
   IF found_item IS NULL THEN
       RAISE EXCEPTION 'Invalid group item';
   END IF;
   RETURN NEW;
END;
$INV_GRP_SET$ LANGUAGE plpgsql;

CREATE TRIGGER inv_check_set_group_items BEFORE INSERT OR UPDATE ON inv_items_group
FOR EACH ROW EXECUTE PROCEDURE inv_check_set_group_items();

Where inv_groups_vw is a VIEW with the predefined SELECT to return all the item groups inside inv_items.

Is the problem with the VIEW or the FUNCTION? What can I do to fix this and be able to select rows of newly inserted items within the transaction? ... am I missing anything else?

Could it be that my condition IF found_item IS NULL THEN is flawed?


texts:
Our SQL Server 2016 development servers have trace flag 10260 set on start up.
The other servers that run SQL Server 2016 do not.
I've searched high and low, but have not been able to find any info on the subject.

What does this trace flag do?

Is it safe to remove?


texts: Problem installing Oracle 19c to symlinked directory from RPM

texts:
I've created destination directory and linked it to default install path:
$mkdir /u01/oracle
$ln -s /u01/oracle /opt

Then I've changed the owner:
$sudo chown -R oracle:oinstall /opt/oracle

But when I tried to install RPM I got an error:
$sudo rpm -i ./oracle-database-ee-19c-1.0-1.x86_64.rpm
warning: ./oracle-database-ee-19c-1.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

[SEVERE] The install cannot proceed because ORACLE_BASE directory (/opt/oracle)
is not owned by "oracle" user. You must change the ownership of ORACLE_BASE
directory to "oracle" user and retry the installation.

error: %prein(oracle-database-ee-19c-1.0-1.x86_64) scriptlet failed, exit status 1
error: oracle-database-ee-19c-1.0-1.x86_64: install failed

How to properly fix this problem?


texts:
I have a table of fatalities and populations that I need to normalize to fatalities per 100K people:

CREATE TABLE all_states (
  state character varying(20) NOT NULL,
  fatalities integer NOT NULL,
  population integer NOT NULL
);

I want to calculate the fatality rate per 100,000 people, so I did:

SELECT *, (fatalities/population)*100000 AS rate
FROM all_states;

But what I get is just 0s:

STATE          population   fatalities  rate
"Alabama"      4802740      75          0
"Alaska"       722718       39          0
"Arizona"      6482505      69          0
"Arkansas"     2937979      93          0
"California"   37691912     390         0

I feel like I'm missing something super simple here. I tried casting it as a float but that didn't change the results:

SELECT state, population, fatalities,
       CAST(((fatalities/population)*100000) AS float) AS rate
FROM all_states;


texts: Oracle - how to know what internal executable or program performs DML (SELECT,UPDATE,INSERT,DELETE)?

texts: My goal is to bypass the query parsing when writing data (INSERT or UPDATE). Is it possible to use directly the executable beneath these commands?


texts: SQL Server Concurrent Insert/Update and Storing Data

texts:
From: SQL Server 2014 Concurrent input issue

Follow up question:

Table Orderline stores information we receive from shops. OrderLineID is the unique, primary key in the table, since orders have multiple line items.

Orders can be updated, so we first check if OrderLineID is present in the table, and insert or update accordingly.

Due to customer modifications, line issue, server busy, etc, the OrderLineId can be sent more than once. We have a time Lastmodified column. If the incoming timestamp is older, it has no effect on the table. We work in a multithreading, parallel processing environment.
Utilize SQL 2016 Read Committed Snapshot Isolation level.

Previous question gave this response:

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT *
    FROM OrderLine WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
)
    INSERT INTO Orderline () VALUES ()
ELSE
    UPDATE Orderline
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;

COMMIT TRANSACTION;

New question:
Sometimes I do not have the OrderLineId initially, so we search by customer LastName, FirstName and ProductDescription and other columns (we have an old legacy system, don't bother explaining). I want to validate setting variables during these transactions will not affect anything. I will need the @OrderLineId for a future sql item, and wanted to hold value. I executed sp_lock to test, seems to work. I may just save the value after transaction is complete, but did not want to relook it up again, for optimization purpose.

Method 1: Save variable all at once

begin transaction

declare @OrderLineId int

set @OrderLineId =
( SELECT OrderLineId
  FROM Orders WITH (UPDLOCK, SERIALIZABLE)
  WHERE LastName = @LastName
  AND FirstName = @FirstName
  AND WebLoginDate = @WebLoginDate
  AND ProductName = @ProductName )

IF @OrderLineId is null
BEGIN
   INSERT INTO Orderline () VALUES ()
   SET @OrderLineId = SCOPE_IDENTITY()
END
ELSE
BEGIN
    UPDATE Orderline
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;
END
COMMIT TRANSACTION;

Method 2: is this method safer?

BEGIN TRANSACTION;

declare @OrderLineId int
IF NOT EXISTS
(
    SELECT *
    FROM Orderline WITH (UPDLOCK, SERIALIZABLE)
    WHERE LastName = @LastName
    AND FirstName = @FirstName
    AND WebLoginDate = @WebLoginDate
    AND ProductName = @ProductName
)
BEGIN
   INSERT INTO Orderline () VALUES ()
   SET @OrderLineId = SCOPE_IDENTITY()
END
ELSE
BEGIN

set @OrderLineId =
(  SELECT OrderLineId WITH (UPDLOCK, SERIALIZABLE)
   FROM Orderline
   WHERE LastName = @LastName
   AND FirstName = @FirstName
   AND WebLoginDate = @WebLoginDate
   AND ProductName = @ProductName)

    UPDATE Orderline
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;
END
COMMIT TRANSACTION;


texts:
While doing restore from a MySQL binary log file, it is failing with the following error .

mysqlbinlog mysql-bin.000036 | mysql -u root -p asdb
ERROR 1032 (HY000) at line 48: Can't find record in 'dlfilerank'

The error is after a full database backup using mysqldump.

The table dlfilerank in question is used by liferay CMS which seems to keep inconsistent data. The table is used to keep track of recently downloaded files, which is displayed in the Recent Downloads portlet.

I thought one way to resolve this issue is to delete all records from this table before restoring from binary log file . But this lead to restore failing with a different error.

mysqlbinlog mysql-bin.000036 | mysql -u root -p asdb
ERROR 1062 (23000) at line 387: Duplicate entry '768524' for key 'PRIMARY'

Next I am thinking to delete this table from both mysqldump and binary log file . It will be easy to delete the table after restoring to mysql database , but not sure how to delete db transactions for this table from binary log . Any suggestions how I can I do this . Also , will this approach will resolve the problem mentioned in original description .


texts:
I posted this on StackOverflow and was told to come here.

I have an XAMPP MySQL installation and want to recover the root password. I am aware that questions have asked this already, however they are Linux methods of doing it (I'm on Windows) and I can't seem to figure out what exactly the Linux commands translate to in XAMPP - I tried looking for a place to configure the arguments to not use network etc but couldn't. This is a localhost installation.


texts: MS Northwind Traders DB Inventory Transactions Entity relationship question

texts:
Trying to understand Northwind Traders' db schema but I cannot comprehend the relationship between:

Inventory Transactions<--->Purchase Order details
Inventory Transactions<--->Order details

I'm using this PDF as reference
Adapting the Access Northwind Database to Support a Database Course
here is the original ERD from MS Access

I've used a MySQL port from https://github.com/dalers/mywind in order to highlight the relationships in MySQL Workbench
In specific what blocks my mind are the following questions:
a) Why is there a one-to-many relationship between Inventory Transactions and Purchase Order Details(Fig.1) but not between Inventory Transactions and Order Details(Fig.2), despite the fact that both Purchase Order Details and Order Details tables have an Inventory ID FK that points to Transaction ID field of Inventory Transactions table.

(Fig.1 Inventory Transactions<->Purchase Order Details)

(Fig.2 Inventory Transactions<->Order Details)
b) Conceptually how does that make sense? How can we narrate this relationship?

Inventory Transactions have many Purchase Order Details and a Purchase
Order Detail belongs to Inventory Transactions?

I cant write a logical sentence using this relationship.
c) It would make more sense if this was a One-to-one relationship. Is it?
d) Why are Inventory Transactions associated with either Purchase Orders or Orders? It seems to me it would make more sense to be associated with Purchase Order Details or Order Details, since a transaction acts on a subset of the order tables (conceptually)
Thank you for your time and if you have more material I can read regarding Northwind Traders db, please share it.


texts: Column name or number of supplied values doesn not match table definition

texts:
I have created three tables using

USE UserDecomAudit
GO
CREATE TABLE ADUserData
(
ADUserID int NOT NULL PRIMARY KEY,
Name varchar(50),
SamAccountName varchar(10) NOT NULL,
DistinguishedName varchar(200) NOT NULL,
HomeDirectory varchar(100),
OfficePhone varchar(20),
Extension varchar(4),
Manager varchar(50),
Title varchar(50),
Department varchar(50),
Company varchar(20),
DateOfAccountDecommissioning DATETIME NOT NULL
)

USE UserDecomAudit
GO
CREATE TABLE CSUserData
(
CSUserID int NOT NULL PRIMARY KEY,
ADUserID int NOT NULL,
SIPAddress varchar(50),
LineURI varchar(20),
EnterpriseVoiceEnabled varchar(3),
ExternalAccessPolicy varchar(50),
PersistentChatPolicy varchar(50)
)

USE UserDecomAudit
GO
CREATE TABLE ADGroups
(
GroupID int NOT NULL PRIMARY KEY,
ADUserID int NOT NULL,
ADGroup varchar (200)
)

And am trying to create a stored procedure using

USE UserDecomAudit
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.LogUserDetails
-- Add the parameters for the stored procedure here
@Name varchar(50)= null,
@SamAccountName varchar(10)= null,
@DistinguishedName varchar(200)= null,
@HomeDirectory varchar(100)= null,
@OfficePhone varchar(20)= null,
@Extension varchar(4)= null,
@Manager varchar(50)= null,
@Title varchar(50)= null,
@Department varchar(50)= null,
@Company varchar(20)= null,
@SIPAddress varchar(50)= null,
@LineURI varchar(20)= null,
@EnterpriseVoiceEnabled varchar(3)= null,
@ExternalAccessPolicy varchar(50)= null,
@PersistentChatPolicy varchar(50)= null,
@ADGroup1 varchar (200)= null,
@ADGroup2 varchar (200)= null,
@ADGroup3 varchar (200)= null,
@ADGroup4 varchar (200)= null,
@ADGroup5 varchar (200)= null
AS
BEGIN
DECLARE @ID INT

INSERT INTO dbo.ADUserData (Name,SamAccountName,Distinguishedname,HomeDirectory,OfficePhone,Extension,Manager,Title,Department,DateOfAccountDecommissioning)
VALUES (@Name,@SamAccountName,@Distinguishedname,@HomeDirectory,@OfficePhone,@Extension,@Manager,@Title,@Department,getdate())

SET @ID = cast(scope_identity() AS INT);

INSERT INTO dbo.CSUserData (ADUserID,SIPAddress,LineURI,EnterpriseVoiceEnabled,ExternalAccessPolicy,PersistentChatPolicy)
VALUES (@ID,@SIPAddress,@LineURI,@EnterpriseVoiceEnabled,@ExternalAccessPolicy,@PersistentChatPolicy)

WITH UserADGroups AS
(
    SELECT @ADGroup1 AS ADGroup UNION all
    SELECT @ADGroup2 UNION all
    SELECT @ADGroup3 UNION all
    SELECT @ADGroup4 UNION all
    SELECT @ADGroup5

)
INSERT INTO dbo.ADGroups
SELECT @ID, ADGroup
FROM UserADGroups
WHERE ADGroup IS NOT NULL

END
GO

But I'm receiving an error saying column name or number of supplied values does not match the definition. I'm not sure what I've done wrong


texts: Viewing how much transaction log is used in Simple Recovery Model

texts:
We have a DW database in Simple Recovery Model. Every night, we load in data from different sources and run a few procs. I'd like to see how much the transaction log is actually used during this nightly load that takes hours to run.

Currently, the log has ballooned to over 50GB due to a one-time cleanup query we ran recently. The used space on the log is near 0% due to Simple Recovery Model. We'd like to shrink the log file down to a more reasonable size. I suspect that the nightly load only uses 5GB but is there a way to actually find this out? I thought about polling the database every minute or five and capturing the space used and unused in a logging table for a couple nights. Is there a better way?


texts: Understanding repeatable read isolation level

texts:
Wikipedia defines following terms:

  With a lock-based concurrency control DBMS implementation, serializability isolation level requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon.
  In repeatable reads isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so phantom reads can occur.

As long as I interpret it correctly, both serializability and repeatable read isolation levels corresponds to rigorous two phase locking (in which both shared and exclusive mode locks are held till transaction commits/aborts), in which transactions are serializable by their commit order, according to the book by Korth et al..

However, book by Korth et al also says following repeatable read isolation level:

  Repeatable read allows only committed data to be read and further requires
  that, between two reads of a data item by a transaction, no other transaction
  is allowed to update it.
  However, the transaction may not be serializable
  with respect to other transactions. For instance, when it is searching for data
  satisfying some conditions, a transaction may find some of the data inserted
  by a committed transaction, but may not find other data inserted by the same
  transaction.

My doubt is, if rigorous 2PL schedules are serializable by commit order of its transaction, then why book by Korth et al says repeatable read isolation level may not ensure serializability?


texts:
I have recently started a new job that will include lots of db related tasks. There is no document describing the various db tables/columns. I will need to do some BI and I am extremely stressed out by the thought that nobody can help me find my way around the database. There is no  db dictionary available. The db is huge, with ~1000 tables, some of them have ~800 columns.
I already have 5 tasks, and I have no idea where to start. There is 1 contractor that works on optimizing the db, but has no knowledge of the business.
Please throw at me some coping strategies. How can I create this db dictionary? Maybe there are tools that I can use?
Thank you!


texts: Remote MySQL Dump not working with cnf file

texts:
I have created a user 'backup'@'%' with Grant all privileges access to .

I have added the user backup to my.cnf file

When I do the following in CMD:

mysqldump -p3306 -h<dns/ip> db-name table-name > D:\table-name.sql

I get an error 1045: Access denied for the user 'backup'@'' using password: YES when trying to connect

However, when I do the below with user and password credentials it work

mysqldump -p3306 -h<dns/ip> --user=backup --password=<secret> db-name table-name > D:\table-name.sql

I cant get my head around this can someone please help


texts: SQL Server 2008 RC2 Edit transaction Log

texts:
I have been handed an issue with a database where last nights backup will not restore. Having spent all morning on this and getting nowhere, I started to wonder about other things I could do to fix my current issue, then I can go back to the backup problem without having to fend off irate users.

I have isolated the individual transaction that has caused the issue, and have its id by using sys.fn_dblog

How can I edit the records in the transaction file to undelete them?


texts:
I have a problem with parse simple xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <data-set>
        <department>
            <ID>1</ID>
            <Name>IT</Name>
            <ID_CUSTOMER>1</ID_CUSTOMER>
            <ID_CUSTOMER>2</ID_CUSTOMER>
            <ID_CUSTOMER>3</ID_CUSTOMER>
            <ID_CUSTOMER>4</ID_CUSTOMER>
            <ID_CUSTOMER>5</ID_CUSTOMER>
            <Adress>Pražská</Adress>
            <Desc>platba kartou</Desc>
        </department>

    </data-set>

This XML file I parsed with use code:

DO $$
   DECLARE myxml xml;
BEGIN

myxml := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('MyData.xml'), 'UTF8'));

DROP TABLE IF EXISTS my;
CREATE TABLE my AS

SELECT
     (xpath('//ID', x))[1]::text AS ID,
     (xpath('data-set/@Name', x))[1]::text AS Name,

     (xpath('//ID_CUSTOMER', x))[1]::text AS id_customer,
     (xpath('//Adress', x))[1]::text AS Adress,

     (xpath('//Desc', x))[1]::text AS tgen

FROM unnest(xpath('//data-set', myxml)) x
;

END$$;

select * from my

Unfortunately, this parse gives me only first single row in result.

I need create table where are all record in to relevant rows:

Rows1 - ID 1, ID_CUSTOMER 1, Adress Pražská, Desc Platba kartou
Rows2 - ID 1, ID_CUSTOMER 2, Adress Pražská, Desc Platba kartou
Rows3 - ID 1, ID_CUSTOMER 3, Adress Pražský, Desc Platba kartou
Rows4 - ........
Rows5 - ........

Thank you for your tips.


texts: How can I prevent/detect that a end user restored a backup?

texts:
We need to prevent the SQLServer 2008 .mdf file from being replaced by the end user of our application.

we collect data in the MDF that is important to us.  If the user saves an old copy of the MDF, and then restore it at a later date, we lose that data.  We want to prevent the user from doing this.

Is it possible, and if so, how could it be done?


texts: High Availability Error 35250 and “An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)

texts:
I am facing problem in configuring High Availability, this is my 30-40 time configuration of Always On Availability Groups with central subscriber replication topology and I have never been stuck like this with SQL Server error 35250. I searched a lot but I was unsuccessful in fixing it.

I got this error:

  An error occurred while receiving data:
  '10054 (An existing connection was forcibly closed by the remote host.)'.

I am using Windows Server 2012 R2 with two node server on SQL Server 2014 (64 bit).

Windows login account and SQL Server service account are both running under a domain account (abc\Administrator).

hadr_endpoint (port-5022) are started with window login permission.

My Always On endpoint ([Hadr_endpoint]) are not blocked by firewall because all firewalls are stopped.

  Msg 35250, Level 16, State 7, Line 1
  The connection to the primary replica is not active.  The command cannot be processed.

Note I am not installed any hotfixes.

I create a domain admin account (on DC) like domainname.in\Administrator and give the all permission to this user (under Member Of tab) and login with domainname\Administrator to the both server. Login in SQL Server with same domainname\Administrator, same service account domainname\Administrator. Note - I created user domainname.in\Administrator but login with domainname\Administrator without '.in'. Is it the issue?

Endpoint URL (please check with ".in" and service account without ".in" (is this the issue?)

My network team not using any kind of network capture and we stopped firewall. We are not using any security software. We are using cyberoam and we also pin all the three server into same switch.

CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)

Service Broker script:

CREATE ENDPOINT [BrokerEndpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
    FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

I am not using RC4 algorithm for endpoint or service broker. I also increased session timeout time with 60 seconds for secondary replica. I also see the .dag file and it show "failure_condition_level 3". I know probably this is network issue but my network person unable to solve it.

Is there any user mapping issue in SQL Server? Any database is compulsory add to my SQL Server login account?


texts: using PERFORM to insert a string of SELECT statement into a temp table

texts:
I am trying to insert data into a temp_table and then truncating the table after analyzing the result.

Here is my code:

CREATE OR REPLACE FUNCTION validation()
  RETURNS text AS $$
DECLARE counter INTEGER;
DECLARE minsid INTEGER;
DECLARE maxsid INTEGER;
DECLARE rec RECORD;
DECLARE stmt varchar;
BEGIN
  SELECT MIN(sid) INTO minsid FROM staging.validation;
  SELECT MAX(sid) INTO maxsid FROM staging.validation;

  CREATE TEMPORARY TABLE temp_table (col1 TEXT, col2 INTEGER, col3 BOOLEAN) ON COMMIT DROP;

  FOR counter IN minsid..maxsid LOOP
    RAISE NOTICE 'Counter: %', counter;
    SELECT sql INTO stmt FROM staging.validation WHERE sid = counter;

    RAISE NOTICE 'sql: %', stmt;

    PERFORM 'INSERT INTO temp_table (col1, col2, col3) ' || stmt;

    IF temp_table.col3 = false THEN
      RAISE NOTICE 'there is a false value';
    END IF;

  END LOOP;
END; $$
LANGUAGE plpgsql;

Whenever I run this function
SELECT * FROM validation();
I get an error:

ERROR: missing FROM-clause entry for table "temp_table" Where: PL/pgSQL function validation() line 21 at IF

Here is how my staging.validation table looks -

https://docs.google.com/spreadsheets/d/1bXO9gqFS-GtcC1qJtgNbFkR6ygOuPtR_RZoU7VNhgrI/edit?usp=sharing


texts:
I have a MariaDB table "mdata" with the following schema:

+-----+-----------+---------------------+------+
| id   | room      | date                | temp |
+------+-----------+---------------------+------+
| 1    | a2        | 2019-08-09 11:04:30 | 34.0 |
| 2    | a4_01     | 2019-08-09 11:04:30 | 32.0 |
| 3    | a3        | 2019-08-09 11:04:32 | 0.0  |
| 4    | a1_4      | 2019-08-09 11:04:32 | 27.0 |
| 5    | a2        | 2019-08-09 11:09:30 | 33.0 |
| 6    | a4_01     | 2019-08-09 11:09:30 | 34.0 |
| 7    | a3        | 2019-08-09 11:09:32 | 0.0  |
| 8    | a1_4      | 2019-08-09 11:09:32 | 29.0 |
+------+-----------+---------------------+------+

Every 5 minutes new temperature values will be written into the database.
I don´t want to overwrite the old values, because a timeline will be implemented later on.
Now I´ve created a web interface to display the youngest values.

Currently I´m struggling to find the right SELECT query.
It is important that the output of the query is in the same order as in the table, because it is put into an array and processed further.

I tried something like this:

SELECT *
FROM mdata
WHERE temp IN
(SELECT temp FROM mdata WHERE date = (SELECT MAX(date) FROM mdata))


texts: Role, whose name doesn't match database name, can't access the database

texts:
Role app can access database app. But role app2 can't. I don't see any specific privileges for role app, and it's not an owner according to \l:

app2=> \l

      Name       |  Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
-----------------+----------+----------+-------------+-------------+--------------------------
 app             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres            +
                 |          |          |             |             | postgres=CTc/postgres   +
                 |          |          |             |             | app=CTc/postgres        +
                 |          |          |             |             | app2=CTc/postgres
...

app2=> \dp
                                               Access privileges
 Schema |                    Name                     |   Type   | Access privileges | Column access privileges
--------+---------------------------------------------+----------+-------------------+--------------------------
 public | access_users                                | table    |                   |
 public | access_users_id_seq                         | sequence |                   |
...

app2=> select * from users;
ERROR:  permission denied for relation users

Why role app is able to access database? What can I check?


texts: Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included

texts:
I am troubleshooting a some tables in MySQL used by a custom Drupal module. Documentation for these tables is poor so I used mysqldump to get the structures in the Drupal database without the data.  I know nothing really about InnoDB but have been doing regular MySQL database stuff for a while now.

In the below examples there should be a table named corporate_subscription_flag_list with

A Primary Key: subscription_id
A Foreign Key: sflid

mysqldump shows a table named `corporate_subscription_flag_list, like:

DROP TABLE IF EXISTS 'corporate_subscription_flag_list';
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE 'corporate_subscription_flag_list' (
    'sflid' int(10) unsigned NOT NULL AUTO_INCREMENT
    'subscription_id' int(11) NOT NULL
    'subscription_flag_id' int(11) NOT NULL
    PRIMARY KEY ('subscription_id')
    KEY 'sflid' ('sflid')
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

However sflid only exists in the above table - am I wrong, or is the way it's been done wrong?  sflid isn't a primary key in any other table nor is it a column in any other table in the database.

Looking at the other tables that supposedly use subscription_id as a foreign key to reference corporate_subscription_flag_list it seems, to me, that there is also a problem.

Comments seem to suggest (as does a strange diagram I have found) that corporate_promolist_data (another table) uses subscription_id as a foreign key referencing corporate_subscription_flag_list.

However, unless I'm missing something there is no foreign key being defined, unless KEY 'pludid' ('pludid') is defining a foreign key?

DROP TABLE IF EXISTS 'corporate_promolist_user_data';
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE 'corporate_promolist_user_data' (
    'pludid' int(10) unsigned NOT NULL AUTO_INCREMENT
    'subscription_id' int(11) NOT NULL
    'title' varchar(255) DEFAULT NULL
    'first_name' varchar(255) DEFAULT NULL
    'last_name' varchar(255) DEFAULT NULL
    'email_address' varchar(255) DEFAULT NULL
    'organisation' varchar(255) DEFAULT NULL
    'job_title' varchar(255) DEFAULT NULL
    'department' varchar(255) DEFAULT NULL
    'address_line_1' varchar(255) DEFAULT NULL
    'address_line_2' varchar(255) DEFAULT NULL
    'town_city' varchar(255) DEFAULT NULL
    'county_state' varchar(255) DEFAULT NULL
    'post_code' varchar(255) DEFAULT NULL
    'country_id' varchar(255) DEFAULT NULL
    PRIMARY KEY ('pludid')
    KEY 'pludid' ('pludid')
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


texts: Tracking changes made in database structure MYSQL

texts:
Is it somehow possible to keep track of changes made to the STRUCTURE (not the data) made to tables?

I want to know when the table definition is been changed (and what those changes were). E.g. to know if/when a column was added, removed, if a column type was changed, if an index was added, and also the new table added to database schema etc.

In other words, to keep track of all the "ALTERS" of the tables and "CREATE" of the tables. (and the state before those alters)

I'd like this to know exactly when some changes were made, and to keep track of discrepancies of data, for example to know when a column was changed to hold VARCHARs instead of INTs (just an idea), or when a new column was added, and who made the changes, who added new table and etc.

Is there any way to find it out? Or enabling an option to be able to track this in the future?


texts: Postgres text search has unexpected query conversion

texts:
I have a dictionary built in this way:

DROP TEXT SEARCH DICTIONARY IF EXISTS simple_english_with_stopwords CASCADE;
CREATE TEXT SEARCH DICTIONARY simple_english_with_stopwords (TEMPLATE = pg_catalog.simple);
CREATE TEXT SEARCH CONFIGURATION simple_english_with_stopwords (copy = english);
ALTER TEXT SEARCH CONFIGURATION simple_english_with_stopwords
   ALTER MAPPING FOR asciihword, asciiword, hword, hword_asciipart, hword_part, word
   WITH simple_english_with_stopwords;

which should include not only generic terms but stop words.

Based on that I am expecting that something like:

SELECT to_tsvector('open plan') @@ to_tsquery('off <-> plan');

would return false as off is not in the text. But for some reason it is returning true.

If  I run just:

# SELECT to_tsquery('off <-> plan');
 to_tsquery
------------
 'plan'
(1 row)

which explains why the previous expression is returning true. But why the query has removed the off term? Any idea?


texts: Extra fields in user_role / roles tables or a new table

texts:
I have a user_roles table which I use to manage permissions and user's access to certain things. For example, a team manager is a role. Since a team manager belongs to a specific team, the way I handle that is by having extra fields in these tables for example:

roles_table
  * has_record
  * record_str

user_roles_table
  * record_id

So if I need to get the team of a team manager, I would have to check the roles record to get the record_str, in this case 'Team' and instantiate an object using that string and the record id in user_roles.

I did it this way because it was a generic way to apply it across all roles in which case I can continue to add roles without changes. Its also nice for adding roles to users. If I am assigning roles, and it has a record, I can then query for the records using the str and display the options in a drop down.

My concern with this is the performance. Do you guys think it will last at a high scale?

The other option I was considering was to just create a table for each user role. This is less modular since if I want to create a role I need add a table, but it provides a more direct way of getting from the user_role to the record without all the joins in between.


texts: How to run update query without interrupt other processes

texts:
I want to update a table and what I want to do is when other processes want to use this table, It will pause my update query and when other processes complete, it will continue to update.Is there any way to do this?

I am using Mysql 5.5 by the way.


texts: How to Variable table name in Insert into Table statement?

texts:
I want to insert into Tables but error:

  Msg 207, Level 16, State 1, Line 1
  Invalid column name 'A'.
  Msg 207, Level 16, State 1, Line 1
  Invalid column name 'B'.

My table names is : TBL_PriceKeyBoard
Keyboard is variable and maybe hard or ram but TBL_Price is static.

Declare @sql as nvarchar(max)
Declare  @TBL as nvarchar(max)
Declare @Name_Edit  nvarchar(max)
Declare @Name1 as nvarchar(max)= N'B'
Declare @Price as nvarchar(max) = N'12'
Declare @NameTBL  as nvarchar(max)  = N'KeyBoard'

set @Name_Edit = N'A'
 set @TBL = N'TBL_Price'+ @NameTBL
Set @sql = N'INSERT INTO [DB1].[dbo].'+quotename(@TBL) +'
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             '+@Name_Edit+'
            ,'+@Name1+'
            ,'+@Price+'
            )'
            EXECUTE sp_executesql @sql


texts: Large Transaction Log Backups in Service Broker-enabled system

texts:
We have a system that uses Service Broker for asynchronous messaging, with a fairly high throughput of messages.  We don't really care about the persistent features of SSB in this scenario -- the messages are pretty much just immediate notifications and if they are not processed immediately when the intended receiver application is up we end up just discarding any backed-up messages when the application restarts.

What we've noticed is that our database is manifesting very large transaction log backups, most of which are filled up with SSB transactions.  This is complicating our backup management and test restore scenarios, and generally just making life harder on the DBA side of the fence.

Are there any tips or tricks that can be used to minimize the transaction log overhead of SSB messages in the case where we don't really care about having them available for restore?


texts:
I have a PostgreSQL db that was running on my server.
I tried to do a db_dump and got a weird error message. I eventually thought I would just restart PostgreSQL. However its now stopped and won't restart.

  Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

I get the error above when I try and run:

sudo service postgresql start

Can anyone give me some clue please?

I'm running on PostgreSQL 9.3 on Ubuntu 14.04


texts: DBeaver not showing any Comments/Remarks in MSSQL

texts:
Recently, I am immigrating some data from Postgresql to a MSSQL2012 server. In SQLWorkbench, I can see comments on tables and columns in SQLServer.

But in DBeaver, I can't see any comment or remarks on any object. The Description column in Properties tab are always empty.
Is this a configuration issue, or is DBeaver just not ready for this ?

Both SQLWorkbench and DBeaver are using MS' JDBC driver.


texts:
I have performance issues with one particulary large table, 500+ Million rows, 300Gb data, Postgres 10.5.
It is already partitioned. I am working on optimising it here and there, but that is not trivial and only provides small improvements.
Table is constanlty growing and we expect our userbase increace significantly so I need a way to scale up.

I want to use multi-tenant sharding approach. X tenants per shard. Shard resolving on app layer.
Most of tenants have relateively small datasets, but few are huge and I want to be able to place them to separate shards . To do that I need lookup tables.
Cross-shard queries are not concern at all, naturaly we have almost all of our queris per tenant, so all the data for the tenant will sit in same shard.

I will be using logical sharding, 4 phisical shards x32 logical (that is twice more shards than partitions currenly).
Each logical shard is separate database. In most tutorials/talks people seem to use schemas instead of databases. Why? Databases are more isolated, and when moving single tenant or virtual shard to other location it does not seem to have any difference. So db looks like a better candidate to me.

Drawbacks look acceptable: update existing code (significantly), app should be shard aware

The question is: How do I handle migrations(schema changes)?

As first step I will have to create 128 databases, ensuring all of them have all tables, indexes, etc. I also want each of dbs have its own sequences to have ids unique accross all shards. Not trivial to me.

But further changes are problem aswell. Do I just iterate all connections and aplly changes? Is there a better (maybe async) way? What do I do if at some point shema in one shard is different from another.


texts: Dump first N documents of a collection with mongodump

texts:
I am very new to MongoDB and I would like to know how to dump the first N elements of a collection with mongodump. I can't figure out the query I have to use.

And how would you dump the last N elements?


texts: Query store data maintenance background tasks

texts:
Query store has been running for years on the server without any noticeable disk IO spikes on log backups. We run on SQL Server 2016 EE version 13.0.5622.0
Recently we experienced a sudden surge in disk I/O every twenty minutes or so. We backup logs every five minutes, and every third or fourth backup would increase dramatically (from ~100 MB to >3 GB in size).
It happened at 2 AM without any changes being done to the server.
I dumped the contents of some of the log files and noticed a huge surge in swappages on the Query store system data tables when these log backup IO spikes occured. Disabling query store entirely caused the disk IO to resume at normal levels when backing up the log.
I presume the reason for the large transaction log backups would be online index rebuilds on the query store tables. We had configured query store to capture all transactions, auto cleanup, retain data for 180 days and max the data cache to 15 GB.
So far so good. I re-enabled query store after changing the configuration to auto, auto, 120 days and 8 GB, but now we are starting to see the same type of spikes in disk IO every 20 minutes or so when the transaction log is being backed up.
Have anyone had an experience like this and what did they do to fix the issue?


texts: DROP database doesn't appear to drop all database objects

texts:
I am kind of new to Postgres and am using recent version on Mac.  I'm writing a .sql script that will create a database and tables and load data.  (I need to be able to automate this, so I will be running it in a cron job, I will NOT be running it from within psql shell.)  Every time I run, I want to start from a clean slate, wiping out the database and all its objects.

When I run the below commands from bash in a .sql file with this content:

DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
CREATE TABLE progress (
foo character varying(10)
);

Then

psql -U postgres -d some_db -a -f db_etl.sql

results in:

DROP DATABASE IF EXISTS mydb;
DROP DATABASE
CREATE DATABASE mydb;
CREATE DATABASE
CREATE TABLE progress (
    foo character varying(10)
);
psql:db_etl.sql:17: ERROR:  relation "progress" already exists

How could the relation still exist if the DROP of its database was successful?  Do I need to be using fully qualified object names like my_db.public.progress?


texts: Replication fails with "Transaction log for database is full due to LOG_BACKUP"

texts:
Replication is failing with the subject error.

The log_reuse_wait_desc states LOG_BACKUP.

I have performed both database and log backups.

After changing the recovery mode to single, performing backup, and then changing recovery mode back to FULL, log_reuse_wait_desc goes to NOTHING.

As soon as I start the replication jobs they fail with the subject error.

I did shrink on log file to no avail.  Any suggestions to resolve this?


texts: Difference between all_nodes VS cluster_nodes CouchDB 2.X cluster membership?

texts:
The _membership endpoint in CouchDB (http://user:pass@domain:5985/_membership) returns the following json to me:

{
  all_nodes: [
    "couchdb@n1.domain.me",
    "couchdb@n2.domain.me",
    "couchdb@n3.domain.me"
  ],
  cluster_nodes: [
    "couchdb@n1.domain.me",
    "couchdb@n2.domain.me",
    "couchdb@n3.domain.me"
  ]
}

When would all_nodes NOT be the same as cluster_nodes?

I managed to setup a cluster incorrectly when building CouchDB from source - there were 3 nodes in 'all_nodes', but none in 'cluster_nodes'.

An answer points to the Server Fault question BigCouch all_nodes vs cluster_nodes, where using BigCouch the question author found _membership returned a list of cluster_nodes but nothing in all_nodes. (So similar to the question I'm asking, although opposite to what I've seen where only all_nodes had values).

I don't understand the answer:

  I just heard from Robert Newson on IRC that BigCouch nodes are connected lazily.

Because:

I thought I read in CouchDB somewhere that nodes to be online at the time they are added to a cluster; and
This is at best one reason why there may be differences in the all_nodes and cluster_nodes results.

I'm effectively asking why there are 2 fields for what seems to be the same thing in CouchDB 2.x? My (very limited) experience is that a cluster that shows nodes in the all_nodes field but not in the cluster_nodes field doesn't work.

It's either not an answer to this question, or so far above my level that I don't understand it.


texts:
I created one role QI_RO, granted all privileges on all schemas on the server and I was trying to grant role privilege to another user but throwing error.
GRANT QI_RO to test_user@'%';

Error Message:
ERROR 1064 (42000): 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 'QI_RO to test_user@'%'' at line 1

DB version:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.10    |
+-----------+
1 row in set (0.00 sec)

How to grant role privileges to users in Mysql like other databases, Postgres and oracle?
Thanks


texts: SQL Server Transaction Log Initial Data Load

texts:
What is the standard process when initially populating a database with 5GB+ to avoid a large transaction log? From my understanding the log file would jump to a large size after the initial load and not decrease in disk space from that point on. Regular transaction log backups would free up space within the file, but the file would remain large.

Is it advisable then to set the target database to simple backup, load the data, use CHECKPOINT to truncate log, and then set the database to full backup mode?

Additionally, I know there is a shrink file operation that can be performed on the log, but some say never to use it, others seem to recommend it as a solution. Any thoughts?


texts: Oracle SQL, how to filter on a set by a rank?

texts:
I am struggling with the following SQL query right now:

http://sqlfiddle.com/#!4/736a7/12

select uc.*
from usercredential uc
inner join users u on u.userid = uc.userid
inner join credential c on c.credentialid = uc.credentialid
inner join healthsystemcredential hsc on hsc.credentialid = c.credentialid
inner join accesslevel ac on hsc.hscredentialid in (
  -- Trying to figure out how to filter here?
  select 1 from dual
)
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) )

Basically I am trying to get UserCredential that are Many To One with Credential.  Credential is One To Many with HealthSystemCredential.

The HealthSystemCredential has a foreign key to AccessLevel, however this AccessLevel column in HealthSystemCredential is actually the Lowest Allowable Access Level.  AccessLevel records have a Type Rank between 1 at the lowest and 4 at the highest.

  Example: A HealthSystemCredential with an AccessLevel record that has a Type Rank of 2 should be fetched when I filter my query by AccessLevels with Types of 2, 3, or 4.

The problem is I can't figure out how to do this in one query.  Is there some way I can get a DENSE_RANK column in a subquery of AccessLevels, apply my filter in the subquery and join it to the table somehow to give me all the applicable HealthSystemCredentials I want?


texts:
I am working with a Firebird 2.1.1 database and I started reading about the Monitoring tables.

I am trying to see somehow in real-time what queries does each client performs in the database.

I had a look at the MON$STATEMENTS table but it does not provide much info. I would have expected more content inside the table.

Am I looking in the wrong place or did I not understand the purpose / use of the Firebird monitoring tables?


texts: I want to set lower_case_variables to 1 but I'm unable to locate my.cnf

texts:
MySQL was started with service mysql start only, but it is not showing the default path of my.cnf:

[root@localhost home]# ps -ef|grep mysql
root     23505     1  0 19:23 pts/4    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid
mysql    23596 23505  0 19:23 pts/4    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/localhost.localdomain.err --pid-file=/var/lib/mysql/localhost.localdomain.pid
root     23713 19420  0 19:49 pts/4    00:00:00 grep mysql

my.cnf is not under all our default locations.

When I start MySQL by creating a new my.cnf it does not start up.

I want to restart MySQL & add a variable in my.cnf.

When I remove my.cnf from /etc, MySQL is starting up with out any errors.


texts:
I have a graph database with multiple edge types. For example

create table Person(Name varchar(50)) as node;
create table Friend as edge;
create table Manager as edge;

insert Person(Name) values ('Alice'), ('Bob'), ('Chris');

insert Friend($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Bob');

insert Manager($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Chris');

I can find all the nodes connected to a given node (graph polymorphism) through UNION queries:

select
    b.Name
from Person a, Person b, Friend f
where a.Name = 'Alice'
and match(a-(f)->b)

union

select
    b.Name
from Person a, Person b, Manager m
where a.Name = 'Alice'
and match(a-(m)->b);

However, this rapidly becomes cumbersome. SQL Server does not have a more elegant solution built into the syntax.

Is there a design pattern which allows for polymorphism?


texts: Testing if temp table exists in iSeries SQL stored procedure

texts:
What would be the syntax for testing if there is already a global temporary table in DB2 for IBM i 7.1?

I tried this after DECLARE GLOBAL TEMPORARY TABLE:

SELECT * FROM QSYS2.SYSTABLES WHERE SYSTEM_TABLE_SCHEMA = 'QTEMP';

and it returned an empty result set. The permanent tables seem to be included into that view, but not the temporary tables.

Ultimately I need to conditionally drop a temp table or truncate/delete data if it exists.


texts: Creating a Friendships database that includes a Block Friend option

texts:
My database teacher asked us to program a clone of Facebook using MySQL as the database management system, so I'm trying to represent a friends relationship in the corresponding database.

So far I have built the following tables by searching everywhere:

CREATE TABLE Users (
    UserID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Nome VARCHAR (20) NOT NULL,
    Sobrenome VARCHAR (20) NOT NULL,
    Foto LONGBLOB,
    Cidade VARCHAR (180),
    PRIMARY KEY (UserID)
);

CREATE TABLE Friends (
    UserID INT UNSIGNED NOT NULL,
    FriendID INT UNSIGNED NOT NULL,
    PRIMARY KEY (UserID, FriendID),
    CONSTRAINT FK_UserID
    FOREIGN KEY UserID REFERENCES Users (UserID),
    CONSTRAINT FK_FriendID
    FOREIGN KEY FriendID REFERENCES Users (UserID)
);

I pictured the Friends table as a self-relationship using UserID (declared as the Users table primary key).

Beyond some basic #1064 errors I'm working on: Is it possible to add a Block Friend option to this relationship? For example, if I don't want to talk with a person anymore, I just block them.


texts: Effect of TRUNCATE TABLE different from DELETE on subsequent queries

texts:
I always thought of TRUNCATE TABLE as an equivalent of DELETE, except that:

1) TRUNCATE can be used only in some cases (no FK referencing table, user must have ALTER permission etc)

2) TRUNCATE is faster to execute

Apart from that, I expected them to have identical results.
But from my experience, it seems that a TRUNCATE improves the performance of subsequent queries over a DELETE.
I have a script that clears some tables, then fills them with millions of rows from another database on the same server, like:

INSERT NEWDB..newtable(a,b,c) SELECT a,b,c FROM OTHERDB..oldtable

Switching the clearing of tables from DELETE to TRUNCATE TABLE made the deletion much faster (as obvious) but also made the subsequent data copying at least 10 times faster.
Interesting to note, the recovery model for the database is SIMPLE, so it is not a matter of logs.
Can someone explain why this is happening? Or maybe I am missing something and the gain in speed is caused by some other factor?


texts:
This is the code I've written:

delimiter //
create procedure `update_pts`()
begin

set @ctr=1;
set @cnt=(select count(*) from participant);

declare cs1 cursor for select sap from participant;

open cs1

while @ctr<@cnt do

fetch next from cs1 into @sap

set @points=(select sum(players.points) from players where pname in (select player1,player2,player3,player4,player5,player6,player7,player8 from team where sap=@sap));
set @pointsc=(select points from players where sport='cricket' and dept=(select cdept from team where sap=@sap) and year=(select cyear from team where sap=@sap));
set @pointschess=(select points from players where sport='chess' and dept=(select chessdept from team where sap=@sap) and year=(select chessyear from team where sap=@sap));
set @pointstt=(select points from players where sport='tt' and dept=(select ttdept from team where sap=@sap) and year=(select ttyear from team where sap=@sap));
set @pointst=(select points from players where sport='throwball' and dept=(select tdept from     team where sap=@sap) and year=(select tyear from team where sap=@sap));
set @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

update participant set points = @pointsf where sap=@sap;

set @ctr=@ctr+1;

end while;

close cs1;
deallocate cs1;
end //;

MySQL gives me an error on the declare keyword saying 'syntax error, unexpected DECLARE_SYM'.
I tried changing the order of my statements by putting the 2 set statements

set @ctr=1;
set @cnt=(select count(*) from participant);

after the declare statement but then MySQL gives me an error in the while statement saying 'syntax error, unexpected WHILE_SYM, expecting ';''

Can anyone please explain to me why this is happening and how do i fix it?

Thanks

Updated code @Abdul:

delimiter //
create procedure `update_pts`()
begin

 DECLARE noMore BOOLEAN DEFAULT FALSE;

declare cs1 cursor for select sap from participant;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = TRUE;
#set @ctr=1;
#set @cnt=(select count(*) from participant);
open cs1;

#while @ctr<=@cnt do
REPEAT
fetch cs1 into @sap;
if not noMore then
set @points=(select sum(players.points) from players where pname in (select player1 from     team where sap=@sap
                    union all
                    select player2
                    from team where sap=@sap
                    union all
                    select player3
                    from team where sap=@sap
                    union all
                    select player4
                    from team where sap=@sap
                    union all
                    select player5
                    from team where sap=@sap
                    union all
                    select player6
                    from team where sap=@sap
                    union all
                    select player7
                    from team where sap=@sap
                    union all
                    select player8
                    from team where sap=@sap));
set @pointsc=(select points from players where sport='cricket' and dept=(select cdept     from team where sap=@sap) and year=(select cyear from team where sap=@sap));
set @pointschess=(select points from players where sport='chess' and dept=(select     chessdept from team where sap=@sap) and year=(select chessyear from team where sap=@sap));
set @pointstt=(select points from players where sport='tt' and dept=(select ttdept from     team where sap=@sap) and year=(select ttyear from team where sap=@sap));
set @pointst=(select points from players where sport='throwball' and dept=(select tdept     from team where sap=@sap) and year=(select tyear from team where sap=@sap));
set @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

update participant set points = @pointsf where sap=@sap;
end if;
#set @ctr=@ctr+1;
until noMore
end repeat;

close cs1;
#deallocate cs1;
end //


texts:
I am creating a cataloging system for rocks that come from all around the world. As part of this system, I need to handle the location of the where the rocks were found.

The problem is that the location needs to contain the country, a sub-item (e.g. State, Province), possibly another sub-item (e.g. County), then maybe a city, and then finally the mine/quarry/forest/etc that it was found in.

I don't think that I should allow the user to enter in the location every time in plain-text, as that runs the risk of multiple spellings for the same location. Also, because there are so many possibilities for the way location is structured (United states would be USA:CALIFORNIA:ORANGE:LOS ANGELES but Canada might be CANADA:BRITISH COLOMBIA:VANCOUVER) I don't know how to effectively store and query this information.

What is the best way to do this?


texts: Checks to be done, before/after changing the windows login password?

texts:
Here we have a scenario where a Service account , added under security as sysadmins for windows login, going to have its password change as per windows policy.

Since windows team, server admins are going to change the password, where should i check or what pre-activity should i consider before the password is changed.

This account (windows domain) is running the SQL services as well.


texts: How to change query_cache_type to 2 in AWS RDS?

texts:
I want to change my AWS RDS parameter query_cache_type value to 2 , I have done it in parameter group dashboard as in the given image.

I have also restarted my mySQL server with the changed parameter,
but when I am checking this by query I am getting a different response:-

How can I change this static parameter ?


texts: Convert Login from Windows Authentication to SQL Server authentication?

texts:
I have an account that is setup for Windows authentication. The account is used by SSRS for running reports and has various permissions in multiple databases. I need to convert this account to use SQL Server authentication.

I don't see a way to change it in SSMS. Is there a way to "flip a switch" and make it use SQL Server authentication?

For details as to why I want do this, see this question.


texts: Cardinality between tables in the present or over time?

texts:
I have a question regarding cardinality between two tables.
I'm not sure how to approach it.
Imagine the following:
There are groups: group 1, group 2, group 3 etc
One group can have multiple items and those items can be only in one of those groups.
A store can have only one group of items and one group can be in one store at a time, so you can say a store posseses a group. This being like this, group and store would have a cardinality of 1:1.
However, over time groups will change between stores, so every stroe will change the group it has, but groups wont repeat over stores. Sö, stores will interchange groups every now and then.
The problem is I'm not sure if it still is 1:1 or it can be considered as a 1:N, if you think it as a long term thing over time, more so if I want to register the date of the change.
Also, this whole thing results in a kind of ternary relationship, since stores can sell multiple items which at the same time are in a group and only in 1 group of items, but this groups are possed only by one store at a time, but, this stores will exchange groups over time. So this is making my head explode.


texts: Using start and end event logs to create a table/view containing spans between the times of each log


texts: transaction log in RAM or physical file?

texts:
I'm a beginner in transaction, just a question on transaction log.
We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical files? If it is in RAM and when system failure happens, obviously the RAM will be re-erased so we lose the transaction information, so how can we recover the commit?


texts: What is the function that determines the maximum value in a series?

texts:
Say I have a table with columns C and D (among others but these are the relevant ones) with the date type.

I am instructed to update the values in C to be 90 days before its corresponding value in D, or the current date, whichever is later.

I'm given the hint that it "will be useful to search for the Oracle function that determines the maximum value in a series. Hint: it is not max(), which is an aggregate function!"

I have no idea what this function is and would appreciate any help!