texts:
I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g.
Mysql procedures allow me to:
1. Create temporary tables
2. Insert data into temporary tables / Query these tables / Do some processes
3. Drop temporary tables

I've been searching and found I can not create DDL Statements within procedures without EXECUTE IMMEDIATE statement. I tried to create the temporary table with that statement, and immediately insert some values on it but it does not work 'cause the table does not already exist. I tried to create a function which creates a temporary table, then call it from the procedure but the same happens.

I need to achieve the same steps I'm doing with MySQL. This situation happens very often (there are so many procedures with this kind of "issue") and they are quite large.

What options do I have, what do you recommend ?


texts: SQL TOP statement, how to make sure it's "random"?

texts:
I was studying SQL where I encountered a TOP statement. i.e.

SELECT TOP 2 * FROM Persons

select at most 2 row from table Persons.

However, if I were to use it for statistical reason, I'd like such process to be random: i.e. independent of time and order in the table.

My questions was that:

How to make sure TOP statement was totally "random"(it's selection was not ordered)? Further, how to make sure TOP statement was ordered with respect to some variable( i.e. age)
How did the TOP statement actually work?


texts: Calculate start and end dates from a list of only start dates and partitioned

texts:
I'm currently working with a data set with the following table structure

create or replace TABLE FACT_TBL_PRODUCTSUPPLIER (
    DATEID NUMBER(18,0) NOT NULL,
    PRODUCTNUMBER NUMBER(38,0) NOT NULL,
    SUPPLIERNUMBER NUMBER(38,0) NOT NULL
);

Example Data Set:

    DATEID        PRODUCTNUMBER SUPPLIERNUMBER
    2019-07-06    555           1234
    2019-07-06    343           545
    2019-07-10    555           8888
    2019-07-16    555           1234
    2019-08-01    555           1234
    2019-09-15    555           6666

I'm trying to keep the product number linked to the supplier number until the product gets taken over by another supplier. When the product does get taken over then I should add another 30 days to the product so it remains linked.

Expected Output:

    DATEID       END_DATEID    PRODUCTNUMBER SUPPLIERNUMBER
    2019-07-06   2019-08-07              555           1234
    2019-07-06   2020-02-06              343           545
    2019-07-10   2019-08-13              555           8888
    2019-07-16   2019-08-29              555           1234
    2019-08-01   2019-10-13              555           1234
    2019-09-15   2020-02-06              555           6666

I have tried the following options so far:

select td.dateid,
LEAD(DATEADD(day, +28, dateid)) OVER(ORDER BY dateid) AS EndDate,
productnumber, ps.suppliernumber,
from FACT_TBL_PRODUCTSUPPLIER PS

select
    distinct dateid,
    coalesce(
        dateid,
        lag(dateid, 1) over(
            partition by
                productnumber,
                suppliernumber
            order by
                dateid asc
        )
    ) as dateid,
    dateadd(
        'day',
        28,
        coalesce(
            lead(dateid) over(
                partition by
                    productnumber,
                    suppliernumber
                order by
                    dateid asc
            ),
            current_date()
        )
    ) end_dateid,
    productnumber,
    suppliernumber
from
    FACT_TBL_PRODUCTSUPPLIER;

If no one takes over the product number then the current supplier that has been assigned the product should be able to see it.

What I'm trying to do is a product should be displayed to a supplier until someone takes over. When someone does takeover the end date would have an additional 28 days to display. If no one takes over a specific product then the supplier should be able to see it indefinitely e.g. current date.


texts: Restrict two specific column values from existing at the same time

texts:
I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed.

I would appreciate any help creating a constraint that will enforce this.

CREATE TABLE example
(
  example_id    serial PRIMARY KEY,
  example_state CHAR(1) NOT NULL
);

ALTER TABLE example ADD CONSTRAINT
  example_constraint
CHECK (example_state = 'a' OR example_state = 'b' OR example_state = 'c');


texts: Recovering from Oracle host OS disk failure (options)?

texts: If an Oracle host OS has a complete disk failure and the host can be restored (assuming from a recent clone of the host OS disks) from backup, is it possible to then point the host to the existing Oracle datafiles on a SAN, as they were before the host disk was lost, and bring the database back up?  This is assuming we are using Oracle ASM and that there were no OS or Oracle configuration changes made on the OS between the imaging of the host drive(s) and the drive failure.


texts: Why does metadata have to stored in table cells according to Codd's 12 rules

texts:
Among Codd's 12 rules for Relational Database Management Systems is Rule 1, which states the following:

Rule 1: Information rule
All information(including metadata) is to be
represented as stored data in cells of tables. The rows and columns
have to be strictly unordered.

Why is it important that metadata be stored in tables?


texts: MySQL View from different database with the same table schema

texts:
I am in a situation right now where I have to create a MySQL view from different databases on the same host. The team that created the data used different databases for every year (like sales_2014, sales_2015) etc.

All databases have the same tables with almost the same schema. Only some columns are different, but i only need those that are the same so I don't have to care about those that are different.

For my project I need to have that data in one view or table. What is the best way to do this? Should I create a view for every database and connect all views via a single view on a different database, should I just use one big view for all tables from the different databases or should i use something different than views?


texts:
I'm having a bad issue with Always ON on a SQL Server 2017 CU3 running on Windows Server 2016 with last cumulative updates (March 2018).
Availability Mode is set to Synchronous commit and when I reboot the secondary node some DBs (randomly) get in a "Not Synchronized" state and they can't go back to "Synchronized".
Everything looks OK, I have another SQL installation with same patching level and Os and it's working fine.
I don't have specific errors on the event viewer. Antivirus is disabled, the nodes are on the same network and they look ok.
I tried to raise the session timeout to 20s but this didn't solve the issue.
Do you have any idea or suggestion?


texts:
I have root access to the database on VPS, however it looks like root has no rights.

When I tried to create a database, it said :'user 'root' access denied to database..'

When I wanted to select users from mysql.users it said

'SELECT command denied to user 'root'

I wrote 'SHOW GRANTS', it gave
'GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD <secret>' in 'GRANTS FOR 'root@%'' column

Ideas?


texts: Installation of SQL*Plus and SQL Server Management Studio into Windows Tablet

texts: I would like to check if SQL*Plus client and Microsoft SQL Server Management studio (SSMS) are supported in Windows(8.1) tablet? Windows tablets such as Asus or Lenovo MII3/thinkpad 8 comes with Windows 8.1 Bing.


texts: How to best store a timestamp in PostgreSQL?

texts:
I'm working on a PostgreSQL DB design and I am wondering how best to store timestamps.
Assumptions
Users in different timezones will use the database for all CRUD functions.
I have looked at 2 options:

timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC')

bigint NOT NULL DEFAULT

For timestamp I would send a string that would represent the exact (UTC) timestamp for the INSERT moment.
For bigint I would store the exact same thing, but in a number format. (time zone issues are handled before millis is handed over to the server, so always millis in UTC.)
One main advantage with storing a bigint could be that it would be easier to store and to retrieve, as passing a correctly formatted timestamp is more complex than a simple number (millis since Unix Epoc).
My question is which one would allow for the most flexible design and what could be the pitfalls of each approach.


texts: When creating a database using an ERD, what do the entities and relationships become (in database terms)?

texts:
MIS student, here. Learning about ERDs (entity relationship diagrams). ERDs are supposedly used for the purpose of later constructing a database. My question is this:

What will an entity (and, likewise, an entity's attributes) become upon implementing the database? For instance, will entities turn into rows and attributes into columns?
In fact, now that I consider it, I'd like to know how one implements associative entities from ERD to database form as well. Is there a difference between what a regular entity and an associative entity represent in terms of database terminology?


texts: Find the total number of rentals for each genre

texts:
The following are the tables in the database (removed irrelevant tables). I want to query for the total count of rentals for each genre. Note that a movie can have multiple genres.

MOVIE:  Table of movies that are available in the video store.
    id:             Integer identifying the movie instance.
    title:          String with the title of the movie.
    duration:       Integer with the total duration of the movie, in seconds.
    release_year:   Integer with the year of release of the movie.
    rental_rate:    Float with the daily cost of renting the movie.
    rating:         Float in the interval 0-10, measuring how much viewers
                    enjoyed the movie.

GENRE:  Table of movie genres.
    id:             Integer identifying the genre instance.
    name:           String with the name of the genre.

RENTAL:  Table of video rentals.
    id:             Integer identifying the rental instance.
    customer_id:    Integer identifying the customer who performed the rental.
    movie_id:       Integer identifying the movie that was rented.
    checkout_date:  Integer with unix timestamp, in seconds, marking when
                    the video was checked out by the customer.
    return_date:    Integer with unix timestamp, in seconds, marking when the
                    video was returned by the customer.
    cost:           Float with total dollar amount that was charged to the
                    customer for the rental.

MOVIE_GENRE:  Association between a movie and its genre.
    movie_id:       Integer identifying the movie instance.
    genre_id:       Integer identifying the genre instance.

What I have managed to do is simply count all the movies in each genre.

SELECT COUNT(mg.genre_id) AS movieCount, g.name
  FROM MOVIE_GENRE mg, GENRE g
  WHERE mg.genre_id = g.id
  GROUP BY mg.genre_id;

I'm doubting that this query even helps in what I'm trying to do. I know how I can achieve this programmatically, but I'm not sure how to express it in SQL.
We need to map each rental to a movie then to its genres, and count the genres we find here. How do I express this in SQL?


texts: Why conditioning before and after join, gets different results?

texts:
I want to left join two tables and then return some of the records.

My first query was:

SELECT *
FROM card
LEFT OUTER JOIN user_card ON card.id = user_card.card_id
WHERE card.package_id =14
AND ( user_card.user_id =  '5'
OR user_card.user_id IS NULL)

Then when I noticed some problems
(ie result has fewer rows than just SELECT FROM card WHERE package_id=14)

I wrote this:

SELECT *
FROM (select * from card WHERE package_id=14 ) as cc
LEFT OUTER JOIN (select * from user_card WHERE user_id=5 ) as uu ON cc.id = uu.card_id

Now:

Why these two queries act differently?
How can I do it right without sub-querying ?

P.S : I'm using MySQL, and all id columns are INT.


texts: How to grant the USAGE privilege on all types?

texts:
How can I grant the USAGE privilege on ALL TYPES in a schema? For tables and functions we can use ALL TABLES IN SCHEMA schema_name, but this isn't supported for types:

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

I know I can set a default privilege, but in this case I'd already installed an extension with many types before doing that.


texts: I'm new to Brent Ozar's sp_blitz and am trying to do some cleanup in my new job.  I get tons of security findings about agent jobs owned by users.  Most of these hits are system-type accounts (reportexec, webexec, sqlservice), rather than employee-associated accounts.  Is there a problem with this approach?  I'd love to reduce the number of hits I'm getting with sp_blitz, but am not sure it's worth changing some of these things that were purposely set up like this.  Thanks for any help.


texts: Enforce existence of cartesian product in join table without triggers in MariaDB 10.2

texts:
Given tables A, B and C, where C holds a foreign key to both A and B (i.e. C is a "join table"), is it possible to define a constraint so that C contains the whole Cartesian product A x B without using triggers in MariaDB 10.2?

(In other words, for every a in A and every b in B is is possible to enforce the existence of one c in C such that c has a foreign key to a and b)

If not, is there a way to redesign the three tables so that the constraint is enforceable, while keeping 4NF?


texts: SQL Server Failed Login - SQL Server login (not windows) - Random times

texts:
We have the following situation and am wondering if anyone can suggest a way of determining the cause.

One of our SQL Developers can log into, and use, the instances/databases fine and has a SQL logon with sysadmin privilages. He works from approx 8am to 5pm and has no issues.

However we get random "Insufficient Permissions for Action Attempted" for his SQL logon, at random times, with the message:

  Login failed for user '(user)'. Reason: Password did not match that
  for the login provided. [CLIENT: xxx.xxx.xxx.xxx]

The (user) is this developer's Logon, and the CLIENT IP address is the PC he uses on a daily basis.

The only real difference between the developers is this user is using SSMS 18.0 and the others are still on older versions (17.x)

The messages occur all times of the day. The developer doesn't work remotely and would definitely not be trying to log in at 3am.
At one point, we asked the developer to shut down his machine at the end of the day. The alerts stopped happening for a few days but eventually returned.
I've looked at running processes and there's nothing obvious as to what might be causing it.
We've looked into a variety of other potential things, such as incorrectly configured SSIS connection managers.

Does anyone have any suggestions on actions that may lead to finding out the offending "thing" - would wireshark traces help or is there a better way of figuring it out? Thanks for reading.


texts: Extracting the maximum of several subsets and join all maximums in a new table

texts:
I have a big table:

What I need is to obtain the maximum value for each testname, with different entry and get a new table such as:

And then I want to compute the maximum, the minimum and the average value for this new table.

Can you help me?


texts: Using a mysqldump export to create data with a different database name

texts:
I am using the following to import database using a mysqldump export:

mysql -u root -p example_db < path/to/export.sql

I would like to import the database to a database with a different name to the one referenced in export.sql. Is there a way to do this other than manually editing the export.sql file?


texts: Creating same unique constraint with different names

texts:
In MySQL 5.7

alter table mydb.roles add constraint uk1 unique (role_name);
alter table mydb.roles add constraint uk2 unique (role_name);

The 2nd alter command threw me a warning stating:

  Duplicate index 'uk2' defined on the table 'world_x.roles'. This is
  deprecated and will be disallowed in a future release.

When I tried the same in Postgres(10.x), it didn't warn me.

So, what would the SQL standard be in this scenario?


texts: mysql 5.6 FEDERATED table and mysql.servers correlation

texts:
I have no expirence with FEDERATED tables in MySQL. I have database with FEDERATED tables defined and I'm wonder if this tables are used in current env (I have no access to application/developers to ask/check).
The mysql.servers table (https://mariadb.com/kb/en/mysqlservers-table/) is empty.
I wonder if connection info stored there is ephemeral or persitent after first connection to remote server/s.
My question is: if mysql.servers table is empty can I "safely" assume that this tables are not used by apps?


texts:
I'm using Microsoft SQL Server 2016 Standard (64-bit). I have the following backup plan (9 steps long) ...
What I want is just one full (simple) backup everyday.  Do I need to do step 8 (Backup Transaction Log)? Or can I just run some kind of query like "TRUNCATE Transaction Log" after the backup is done? And am I missing any steps, or running something I shouldn't?
"Management" - "Maintenance Plans" - "My_Maint_Plan"

Check Database Integrity
Shrink Database
Reorganize Index
Rebuild Index
Update Statistics
Clean Up History
Backup Database (Full)
Backup Database (Transaction Log)
Maintenance Cleanup Task

This plan creates 2 files daily ... .BAK (1.7 gig), and .TRN (8 gig) ... Do I really need the 8 gig TRN file?


texts:
I have a very large table with 500 mil rows and a Text column that I will be dropping.
In my Dev environment, I have dropped the column and began the reclaim process, but im not sure what the batch size on the “DBCC CLEANTABLE (MyDb,'dbo.LargeTbl, 100000)” statement actually does.

I have tried setting it to 5, expecting it to check the first 5 rows and end.  “DBCC CLEANTABLE (MyDb,'dbo.LargeTbl, 5)” and it took 28 hours.
So I restored the db, set it to 100,000 and it took 4 hours

Actual Question:
Does the batch size tell the dbcc cleantable how many rows to do at a time and continuously keep running 100K at a time till it goes thru all 500mil rows?
Or once I run the 100,000 do I have to run it again till I do all 500 mil rows?

On my second test, (running the 100K once) I was able to reclaim 30GB. Then I ran an index reorg on ALL indexes and reclaimed and additional 60GB..


texts: What factors would cause me to favor inheritence over composition in a small database?

texts:
I have identified two choices for how to model addresses in my corporate entity database.

Method 1: inheritance

Table 1: Addresses [data table, primary key = (id)]
id (integer) - primary key
subclass (string) - describing which subclass table to look in

Table 2: AustralianAddresses [subset table, primary key = (address_id)]
address_id (integer) - foreign key (Addresses.id)
<< other Australia-specific fields >>

Table 3: UKAddresses [subset table, primary key = (address_id)]
address_id (integer) - foreign key (Address id)
<< other UK-specific fields >>

and so on, with one subclass table for each different type of Address.

Method 2: composition

Table 1: AddressFields [validation table, primary key = (id)]
id (integer) - the field
field (string) - which field is this (e.g. "state", "country")?

Table 2: Addresses [data table, primary key = (id, field)]
id (integer) - the address
field (integer) foreign key (AddressFields.id)
value (string) - value of the given field for the given address

Question: in a small database (<1,000 addresses), what would ever cause me to give up the flexibility of the composition version?


texts: How to convert hex chars to byte array in mysql

texts:
Got sha1 checksum in hexadecimal format(40 characters).
Need help to convert hex chars to byte array in mysql.

Thanks.


texts: How to design a database for search history with multiple facets?

texts:
I want to show users a history of their searches, which can often be complicated:

any of these terms: [a,b,c]
all of these terms: [d,e,f]
not these colors: [h,i,j]

Since these searches are done through a UI (not a search input where they would type "a OR b etc...") I assume I would need to store each value in a table.

Would it be overkill to create a table for each facet ("search_terms", "search_colors", etc)? or is there a simpler way?

| search_id |   term(_id)  | operator(enum)|
|:---------:|:------------:|:-------------:|
| 1         | blue         |     any       |
| 1         | green        |     any       |
| 1         | red          |     any       |
| 1         | musthave     |     all       |
| 1         | exclude_me   |     not       |
| 1         | exclude_also |     not       |

Some other considerations are:

The web-URL can change over time, so I don't want to store that as an easy way out.
The index/3rd party service I'm searching could change, so I similarly can't save the query I make.

(The terms themselves can be its own table with a reference instead).


texts: How to reclaim the storage space used by a heap?

texts:
I have a table without a clustered index in SQL Server 2008R2 Standard with a lot of unused space, as shown in the following image:

How do I reclaim unused space from MyTable?

Said table is declared as follows:

CREATE TABLE [dbo].[MyTable](
    [RecordID] [varchar](50) NULL,
    [DocumentID] [nvarchar](100) NULL,
    [DocumentName] [varchar](100) NULL,
    [DocumentOwner] [varchar](50) NULL,
    [DocumentTemplate] [varchar](50) NULL,
    [DocumentData] [ntext] NULL,
    [DocumentDate] [char](10) NULL,
    [DocumentTime] [char](10) NULL,
    [DocumentSize] [int] NULL,
    [DocumentUpdateVersion] [int] NULL,
    [SecondaryStorageURI] [varchar](150) NULL,
    [PreviousExportUri] [varchar](150) NULL
)

What steps should I follow to free unused space?


texts:
After having many issues with ISO-8859-1 and reading a lot about collations I finally understand that I need to use charset UTF8 collate utf8_unicode_ci. So I configure my whole application with these changes and seems to be working smoothly until I needed to execute a manual query
SET @id := '10000016';
SET @invoice := (SELECT invoice FROM WAREHOUSE WHERE invoice_id = @id);

This query is giving me errors in MySQL Workbench with collation message:
Error Code: 1267. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

I can fix it using SET @id := '10000016' collate utf8_unicode_ci;, but this seems weird to me (to say least). I'm sure there's some missing step.
Maybe do I need to tweak something in my.cnf?
Other information
SELECT @@character_set_client; # utf8
SELECT @@character_set_results; # utf8
SELECT @@collation_connection; # utf8_general_ci

Thank you for your time


texts: Split date time, with only time, into minutes and seconds

texts:
I'm creating an Access database from an excel file that contains metadata about vhs tapes and DVDs. The person who created the excel file had the running times as a date/time field in minutes:seconds. I would like to be able to create reports or set up forms/queries etc. where the user can search for minutes greater than such and such. I was thinking that the best way would be to create two fields: minutes and seconds. The date/time type is really for specifying chronological times, not running times.

What is the best way to do this? How can I split the minutes:seconds to create two columns?

I only just imported the Excel table into Access. Would it be easier to do it as a formula in Excel and then import to Access?


texts: Error after upgrading datastax to version 5

texts:
After upgrading to DataStax Enterprise version 5, the node does not come up, and I get this error in the log:

  Insufficient permissions on directory /hints

Where is this directory located?


texts:
I have a table called tblA and its having two columns

id_ (numeric 50,0), obj_ oid

For example, tblA having 1 entries

id_  | obj_
1    | 1001

In pg_largeobject table, there are three entries against loid 1001 (3 page nos) which is fine. But, pg_largeobject table is having three columns (

loid  | pageno    | data
(oid)   (integer)   (bytea)

I read about oids and bytea, both are different data types and being used in that way. But I am confused about pg_largeobject table structure, why its using both data types for same data?

Is postgres internally storing oids as bytea, and just returning oids for reference? If its working in this manner, why can't use just numeric type for reference instead of oid type.

If not, what is the use of oid here and is any data sitting behind the oid somewhere in the database, and what is the bytea column data about.?


texts: AWS aurora (postgres) proper value for the shared_buffers parameter

texts:
I think it's fairly well known that a normal PostgreSQL database has a starting value of 25% of system memory for its shared_buffers parameter, but I haven't been able to find anything regarding a proper value for an AWS Aurora instance.

Now I would normally use the community value for this parameter, but the existing system is already using a value of 50% of the available memory, and I'm just not certain that this is a good value.

Of course, for Aurora the shared_buffers parameter is unique for each database in the system, as opposed to normal PostgreSQL where this is cluster-wide.

Hence, my confusion. Can anyone provide guidance in this manner?


texts: SQL Server 2016 R Statistics: can't find function

texts:
I'm trying to get R Statistics running on my Dev server. I'm using this microsoft tutorial.

I have the default installation of R Analytics as provided my the SQL Server installation:

  C:\Program Files\Microsoft SQL\Server\MSSQL13.MSSQLSERVER\R_SERVICES\

And I also have the Microsoft R Client 3.4.3 which contains the RX functions installed to:

  C:\Program Files\Microsoft\R Client

We have a Tutorial database, with test data, R is enabled and I can do "hello world". Everything works up until Step 3.3 Code Step 3.

> --Save Linear model to table
> DECLARE @model VARBINARY(MAX); EXEC generate_rental_R_native_model "linear", @model OUTPUT; INSERT INTO
> rental_models (model_name, native_model, lang) VALUES('linear_model',
> @model, 'R');

When I run it I get the following error:

  Msg 39004, Level 16, State 20, Line 59 A 'R' script error occurred
  during execution of 'sp_execute_external_script' with HRESULT
  0x80004004. Msg 39019, Level 16, State 1, Line 59 An external script
  error occurred:  Error in eval(expr, envir, enclos) :    could not
  find function "rxSerializeModel" Calls: source -> withVisible -> eval
  -> eval

  Error in ScaleR.  Check the output for more information. Error in
  eval(expr, envir, enclos) :    Error in ScaleR.  Check the output for
  more information. Calls: source -> withVisible -> eval -> eval ->
  .Call Execution halted

I'm on SQL 2016 SP2 CU3, so I know I don't have the predict() function but everything up until that point should work.

If I run RGUI I can prove that rxSerializeModel is there. This works:

myIris <- iris
myIris[1:5,]
form <- Sepal.Length ~ Sepal.Width + Petal.Length + Petal.Width + Species
irisLinMod <- rxLinMod(form, data = myIris)
serializedModel <- rxSerializeModel(irisLinMod)

However if I run this fromSQL, I just get an error.

https://docs.microsoft.com/en-us/machine-learning-server/r-reference/revoscaler/rxsqllibpaths

  Msg 39004, Level 16, State 20, Line 10 A 'R' script error occurred
  during execution of 'sp_execute_external_script' with HRESULT
  0x80004004. Msg 39019, Level 16, State 1, Line 10 An external script
  error occurred:  Error in path.expand(new) : could not find function
  "rxSqlLibPaths" Calls: source ... .libPaths -> Sys.glob -> path.expand
  -> path.expand

  Error in ScaleR.  Check the output for more information. Error in
  eval(expr, envir, enclos) :    Error in ScaleR.  Check the output for
  more information. Calls: source -> withVisible -> eval -> eval ->
  .Call Execution halted

Why can't SQL see it? Is it a path issue? How do I know where SQL is looking for libraries?


texts: How can I copy specific tables into one SQL Server Table into a separate table automatically?

texts:
I'm not sure if this is possible. Looking for some help with a specific issue. I am building an iPhone application with a data layer written in Ruby that will connect to SQL Server 2008. For various reasons, I need to separate the iPhone application from the servers that power my websites. The databases will be hosted on separate servers, and the tables will need to be laid out differently, but I need to push some information from the one database into the other database automatically and instantaneously.

Example: Someone makes a change on the website, updating the website DB. This website DB has a table which will need to be pushed onto a differently named table on a different server.

How can I go about doing this?


texts: Rankings based on "versus" style matchups?

texts:
Let's say I have an application that, from a pool of items, presents two random results to the user. The user selects one as the "winner" and the result would be recorded like this:

INSERT INTO results (winner_id, loser_id) VALUES (26, 45);

What would be the best way to use the results table to rank the items?

This question has a theoretical/subjective component, i.e., there's more than one way to think about ranking them.

For example, this isn't that much different from, say, a season of baseball. Baseball chooses a winner simply by comparing win percentages. But in baseball you could end up with Cleveland with a better winning percentage than Detroit, yet Detroit beat Cleveland every time they played this year. So from another point of view besides winning percentage, you could say that Detroit is better than Cleveland.

It's that other point of view I'm interested in. If you start with two items (A and B) and know from the results table that B beat A 30 times and lost 5 times, you know B should be ranked above A. Then you look at C, which was 8-10 against B and 12-6 against A, and place it between B and A. And so on.

It seems like I'm describing a fairly simple formula, but when you start extending it, it seems like it would get really computationally heavy for large sets of items, and it would be problematic for an item that doesn't fall between two items that it would be expected to fall between based on the rankings up to that point.

Do you think such a ranking system is feasible? Does this concept already have a name? Would it be possible to set up a system of queries that could reasonably provide results in real-time in a web application for, say, 10,000 items?


texts: MySQL :: How to run ANALYZE TABLE for all tables in a database

texts:
I'm using MySQL 5.7

How can I run ANALYZE TABLE for all tables in a database. It should work if new tables are added to the database in future.

This is to refresh the statistics of table indexes.


texts:
I have the following script which drops/ creates six triggers for a MySQL database; v5.5.52.

DROP TRIGGER IF EXISTS gene_lcase_insert;
DROP TRIGGER IF EXISTS morph_lcase_insert;
DROP TRIGGER IF EXISTS genus_lcase_insert;
DROP TRIGGER IF EXISTS species_lcase_insert;
DROP TRIGGER IF EXISTS python_lcase_insert;
DROP TRIGGER IF EXISTS python_extract_year_insert;

/* Enforce lower-case on INSERT into 'gene' table */

CREATE TRIGGER gene_lcase_insert BEFORE INSERT ON gene
FOR EACH ROW
    SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case on INSERT into 'morph' table */

CREATE TRIGGER morph_lcase_insert BEFORE INSERT ON morph
FOR EACH ROW
      SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case in INSERT into 'genus' table */

CREATE TRIGGER genus_lcase_insert BEFORE INSERT ON genus
    FOR EACH ROW
     SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case on INSERT into 'species' table */

CREATE TRIGGER species_lcase_insert BEFORE INSERT ON species
  FOR EACH ROW
      SET NEW.name = LOWER(NEW.name);

/* Enforce lower-case on INSERT into 'python' table */

CREATE TRIGGER python_lcase_insert BEFORE INSERT ON python
  FOR EACH ROW
      SET NEW.sold_to = LOWER(NEW.sold_to),
          NEW.feeding = LOWER(NEW.feeding);

/* Extract year from date_of_birth on INSERT if not NULL */

DELIMITER //
CREATE TRIGGER python_extract_year_insert BEFORE INSERT ON python
   FOR EACH ROW
   BEGIN
          IF NEW.date_of_birth IS NOT NULL
              THEN
                  SET NEW.sequence_year =  YEAR(NEW.date_of_birth);
          END IF;
  END//
DELIMITER ;

Upon executing the script, I receive the following error - when the final 'CREATE TRIGGER' statement is reached:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

I then perform the following check, to see if the TRIGGER exists:

mysql> select trigger_name from information_schema.triggers;
+----------------------+
| trigger_name         |
+----------------------+
| gene_lcase_insert    |
| genus_lcase_insert   |
| morph_lcase_insert   |
| python_lcase_insert  |
| species_lcase_insert |
+----------------------+
5 rows in set (0.00 sec)

It appears that the final 'python_extract_year_insert' trigger is not being created; sanity check:

mysql> drop trigger python_extract_year_insert;
ERROR 1360 (HY000): Trigger does not exist

I am uncertain as to why this is occurring. Thanks in advance.

Note:

I have tried implementing the final trigger (python_extract_year_insert) utilizing a separate file, to see if it was a processing issue of some kind - to no avail.


texts: Do fixed-width rows improve PostgreSQL read performance?

texts:
I have a table articles:
                                                       Table "articles"
     Column     |            Type             |                     Modifiers                      | Storage  | Stats target | Description
----------------+-----------------------------+----------------------------------------------------+----------+--------------+-------------
 id             | integer                     | not null default nextval('articles_id_seq'::regclass) | plain    |              |
 user_id        | integer                     |                                                    | plain    |              |
 title          | character varying(255)      |                                                    | extended |              |
 author         | character varying(255)      |                                                    | extended |              |
 body           | text                        | default '--- []                                   +| extended |              |
                |                             | '::text                                            |          |              |
 created_at     | timestamp without time zone |                                                    | plain    |              |
 updated_at     | timestamp without time zone |                                                    | plain    |              |
 published_date | timestamp without time zone |                                                    | plain    |              |

Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "index_articles_on_published_date" btree (published_date)
    "index_rents_on_user_id" btree (user_id)
    "index_articles_on_user_id_and_published_date" btree (user_id, published_date)

We're on Postgres 9.4.4. The machine has 3.5 GB of memory and 150 GB of disk space on an SSD.
Note: The 'published_date' is always rounded, by the application, to the nearest date. All hours/minutes/seconds are always 00. Legacy. Needs fixed. Etc.
This table has hundreds of millions of articles. The table receives a great deal of read queries from (as many as 16) concurrent processes performing the following queries as quickly as our system will respond:

a count of the total number of articles
SELECT COUNT(*) FROM articles;

a select of all articles published for a given user
SELECT * FROM articles WHERE user_id = $1;

a select of the most recently published article for a given user
SELECT * FROM articles WHERE user_id = $1 ORDER BY published_date DESC LIMIT 1;

I am finding that, with a large number of workers, these queries are quite slow. (At peak load, the first takes minutes to complete; the other two are on the order of 10 seconds.) In particular, it seems that queries are being enqueued.
The question
In the abstract, do tables with only fixed width values perform read queries better than those with varying widths? (Pretend disk space isn't an issue.) In my case, I'm wondering if I would see a performance improvement if I were to extract the 'body' text field to a separate table and transform the character varying fields into fixed width character fields.
I admit the question is a bit cargo cult-y. I simply don't know enough about the internals of the Postgres DB engine to construct an informed hypothesis. I do intend to perform real experiments with different schemas and configurations but I'd like to have a solid mental model of how Postgres actually works before I go much further.
Related question
Where can I learn more about the internals of the Postgres DB engine? I've Googled variations of the above question with little success. What are the correct terms to use for this search? Does this level of documentation exist only in source and the minds of Postgres DBAs? I also humbly invite the suggestion of good books on the topic.


texts: Should I move to InnoDB during a planned migration?

texts:
I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick with my usual rule of thumb that I stay with as standard a Debian install as possible for maximum stability, particularly as enabling InnoDB doesn't seem to be completely fool-proof

The transactional load on the database is minimal as it is a low traffic website. I'm more concerned with keeping management as simple as possible than anything else. The site runs a customised version of WordPress developed by a 3rd party.

I should add that my experience with MySQL in any form is minimal.

Should I stick with my plan or take the opportunity to look into InnoDB?


texts: SQL Server Agent appears to be treating sysprocesses (and dm_exec_sessions) differently

texts:
I have a watchdog process that runs via a SQL Agent job to manage some custom tasks. One of the checks is that the process no longer exists:

UPDATE R
SET IsOrphaned = 1, DebugTimestampUtc = GETUTCDATE()
FROM
    RequestTable AS R
    LEFT JOIN sys.sysprocesses AS P ON
        P.spid = R.SPID
WHERE P.spid IS NULL

In SSMS, I run this statement then close the query window before it completes (cancelling the query):

INSERT INTO RequestTable(SPID, CreateTimestampUtc, ...)
SELECT @@SPID, GETUTCDATE(), ...
WAITFOR DELAY '00:00:30.000'

When I manually run the query to flag orphans in SSMS it matches on the row, but my job does not, as is evidenced by the RequestTable not getting the row marked as orphaned. The job history shows it running as scheduled without any errors, taking negligible runtime (< 1 second).

Furthermore, when I augment the orphan detection statement to also find records where P.status = 'sleeping' it immediately detects when I cancel the INSERT ... WAITFOR ... query (and keep the query window open).

Is there some subtle behavior of SQL Server causing this apparently-inconsistent state of the sys.sysprocesses table between SSMS and SQL Agent? I have also tried with and without a NOLOCK query hint on RequestTable.

I'm running on SQL Server 2014 in compatibility mode for 2008R2.

(Note that using sys.dm_exec_sessions instead still behaves the same)

Thanks.

P.S. The purpose of this is to serialize asynchronous processes that have concurrency issues. Perhaps there is also a better manner to do this (without being able to fix the asynchronous processes)?


texts: How can I hunt down what is trying to use SA?

texts:
I'm not sure of the change that caused this (the logs don't show anything specific) but ever since Monday afternoon I've seen the following error repeated:

Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.10.220.32]

The IP reported is the SQL Server. Since this is ongoing, is there a way I could figure out what is trying to pass this credential? If I could get an application name or anything I could probably fix the problem.


texts: When Mysql chose to kill a Process(automatically)

texts:
I faced some real issues regarding the execution of some stored procedures using Mysql server. Sometimes, one of my procedures is not executed (or maybe stopped) during an external call (from app) so I'm wondering :

Is it possible that Mysql could stop automatically the and kill the
process?
if is it true, what are the indicators and factors that make Mysql
chose its victim?
Is there an alternative to set up a list of priority of running
queries?

Thanks.


texts: Is it good practice to use a table in Postgres as an audit log for login events? The database has other tables like users, posts etc. If the backend receives a failed login, it would insert into the audit table the timestamp, username, action type (in this case failed).


texts: How to Compare Strings In Two Different VARCHAR Columns?

texts:
I am in need of a way to compare the content of two varchar columns, named fistname and lastname correspondingly, and if the content exists in firstname, then remove it from lastname.  Ideally, I would like to do this w/o an update query, but if that is the only way to achieve it then I can go that route.

Here is sample DDL and DML:

Declare @BadData Table
(
    firstname varchar(500)
    ,lastname varchar(500)
)

Insert Into @BadData (firstname, lastname) Values
('Bridget Jones', 'Jones, III'), ('Butch', 'Jones'), ('Key West', 'West')
,('Bob Marly', 'Junior')

Select * From @BadData

Which gives the resultset of:

firstname       lastname
-------------   ----------
Bridget Jones   Jones, III
Butch           Jones
Key West        West
Bob Marly       Junior

My desired resultset is:

firstname       lastname
-------------   --------
Bridget Jones   , III
Butch   Jones
Key West
Bob Marly       Junior

I want to remove the text (that is, the content) from the lastname column if it exists in the firstname column.

How can this be done via a UDF or case statement in SQL Server 2008 R2?


texts: Ternary relationship with history tracking

texts:
I have the following entities

Procedure -> id_procedure , desc_procedure .
Status   -> id_status ,   desc_status .
Subprocess -> id_subprocess , desc_process .

The rules are:

1 procedure can have 1 status
1 status can belong to N procedures
1 subprocess can have N procedures
1 procedure can belong to N subprocedures
1 subprocess can have 1 status
1 status can belong to N subprocess

This ternary relationship is built in this way because I need to track the status of a procedure that belongs to a subprocess.

The full ER diagram (in Spanish)

Now I need to model these requirements:

Need to track changes on the status of the "has" relationship with a time_stamp and a old_Status and and new_status.

I think I need another relationship like this:

Change : id_subprocess , old_status , new_status , time_stamp

How could I model this without adding more fields to my current "has" relationship. Or if I need to add these fields I would highly appreciate your comments.

A procedure can be connected to multiple subprocess at the same time. The subprocesses and processes share the same status. A process can be "student inscription" , a subprocess "get documentation" , "medical test ". A status could be "Active" , "Paused" , "Finished".


texts:
So I need to find the 95th percentile of the age of arrestees in 2018 for a specific charge and  came up with this:

select
percentile_cont(0.95) within group (order by age)
From arrests
Where chargegrpdesc like ‘Vehicle Theft’ and extract(year from adate) = ‘2018’;

The code runs, but I feel like it's too simple and I'm doing something wrong/not getting the correct result. I'm not exactly math-savvy and am new to sql so I just want to make sure I'm doing the right thing. Also, if there is any way I can clean up this query or make it more clear, please let me know. Any help is appreciated!


texts: What do the mysqlds do in my mysql database?


texts: How can I resolve a database name to a directory?

texts:
I am connected to a database foo, and I want to resolve where the underlying directory is for that database, what are my options? Obviously I can reason from datadir, and the database name but there are problems with that: it requires me to implement my own encode function to map identifiers to file names.. Which is encoding-dependant.

This encoding process is mandatory, the database foo._234-asdf maps to data directory /var/lib/mysql/foo@002e_234@002dasdf.

Is there a supported way to query the data-directory for a specific database, or for the connected database?


texts:
How can I transfer information from an Excel file to a table in a SQL server database through SSMS?

Excel file Consists of five columns and a thousand rows.

I followed the steps as explained here,at second 00:52 I can not continue because of the following error message :

  The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local
  machine. (System.Data)

I tried to export the excel file to different versions of Microsoft(2003, 2007, 2010) but the problem was not solved.

Is there any other way to solve this problem?


texts: Crystal reports executing system procedure

texts:
I have many reports executing from Crystal reports 10 using ODBC connection on SQL Server 2008R2 database which are consuming memory.
As per the SQL Profiler I can see when a crystal reports session executed it first executed system procedure SP_Columsn and Sp_Stored_procedure which are actually consuming memory.
I did some research and found about a report option 'verify at first refresh' and 'Verify database' which I by default checked which calls those System SP internally so I unchecked the option but still I can see system Sp executing in profiler.
Can anyone please tell me how can I avoid calling system SP from Crystal Report?


texts: SSIS creates too many connections to a database

texts:
I wonder if anyone had experienced anything similar to the following problem?

I have a SSIS package containing 10+ data flow tasks which I'm using to extract data from an OpenEdge 11.3.002/Progress database to SQL Server 2016 via an ODBC driver.

However, I've been informed by the dba of the progress database that I've created so many connections to the Progress db via SSIS that on several occasions they've needed to restart the Progress database to kill all the connections that SSIS has made as no-one else can connect to it anymore.

I've never experienced an issue when SSIS makes 'too many connections' to a database so I'm trying to reduce the number of connections it opens to Progress. Approaches I've tried so far:

On the connection manager to the Progress DB in SSIS I've set the Retainsameconnection property to true;
Maxconcurrentexecutables property is set to 2 (So only two data flow tasks can run concurrently);
Enginethreads property is set to 5.

But these don't appear to limit the number of connections (enough).

Are there any other ways I can reduce the number of connections SSIS makes to another ODBC database to say max 100 connections (And ensure SSIS releases these afterwards)?

Currently using:

Progress Openedge db 11.3 to extract data from
Visual Studio 2015 for SSIS packages
SQL Server 2016 to load data into


texts: acl to allow nested groups in owner attribute

texts:
I'm trying to create an ACL entry to allow users specified in the owner attribute of a group to manage the group, and also allow groups, including nested groups to be specified as owners.

I've got this rule, which allows admin access to nested members of a specific group:

{0}to * by ssf=128 set="user & [cn=Administrators,ou=LDAP,dc=Applications,dc=example,dc=com]/member*" manage by * break

And I've got this rule which works for granting management permissions to users that are directly specified as owners:

{2}to dn.children="dc=Groups,dc=example,dc=com" by ssf=128 set="user & this/owner" manage

but so far I haven't been able to figure out how to combine the two concepts to allow specifying groups as owners.  Anyone gotten something like this working?


texts: using pg_prewarm after an ETL load has finished - worth it?

texts:
I have a database with many small reference data tables and a few large time series tables. The reference data tables can easily fit into memory. I have various ETL jobs that load data, mainly by left joining incoming data onto the existing tables, and insert/update/delete where required.

Does it make sense to call pg_prewarm on all the reference data tables after an ETL job has finished, so as to ensure that all of them are loaded into memory the next time an ETL load fires? Will I notice much of an improvement?

Assume that the memory is sufficiently large so as to not cause an issue with buffer eviction.


texts: Generated bug data script how not to set the IDENTITY in the insert statements?

texts:
I'm using SQL Server Management Studio to generate sql script (big one)
to update Azure DB.
The problem is that the script is generated with IDENTITY on and the insert statements are exported with the ID column which as result gives me the errors :

  Cannot insert explicit value for identity column in table 'Table_Name' when IDENTITY_INSERT is set to OFF.

My question is how to generate the script right that is will not use  IDENTITY on and the insert.


texts:
I've just recently installed PostgreSQL on an Ubuntu Server machine and there are currently no queries or clients connected to the databases, it's just sitting there idle. Out of curiosity I installed glances and this is what I saw:

With Postgres just sitting there and idling, it's says it's using 1200% of the CPU which if you ask me is quite high. But the overall CPU usage is around 75% with NOTHING running, just Postgres (no clients connected, no queries...)
Any idea what's causing it?
EDIT: I think the pros in the comments were right. I ran last and saw a postgres user log on from an IP in Germany, probably through an open SSH or PostgreSQL port that I forgot to close. I'm still learning the usage of Ubuntu so I'm not surprised that happened.
I just reinstalled the Ubuntu-Server OS on the server and I hope that got rid od the malware.
Now I know not to leave open ports over the weekends when testing stuff!


texts:
I have an Aerospike instance and I messed up one of my namespaces. I didn't know there was a ~32k limit on bins, so I wrote unique bin names into my namespace. I hit that 32k limit, and now my whole namespace is hurting. How can I delete a bin from the entire namespace so I can free up my bin limit?

I've looked at the aerospike documentation and I just can't seem to find anything. I've even looked for 3rd party programs and I can't find anything there ether.


texts: How to avoid hardcoding a database link name in a package body

texts:
I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a single table causes me lots of headache. It is a char(1) field that is used as a bit vector. Because the encodings are different, it gets reencoded and scrambled, so I have to update it using DBMS_HS_PASSTHROUGH:

C := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@remotedb1;
DBMS_HS_PASSTHROUGH.PARSE@remotedb1(C, 'SELECT PKEY, HEX(FLAGS) FROM TABLE1' ) ;
LOOP
   NR := DBMS_HS_PASSTHROUGH.FETCH_ROW@remotedb1(C);
   EXIT WHEN NR = 0;

   DBMS_HS_PASSTHROUGH.GET_VALUE@remotedb1(C, 1, M_PKEY);
   DBMS_HS_PASSTHROUGH.GET_VALUE@remotedb1(C, 2, M_FLAGS);

   UPDATE TABLE1
   SET FLAGS = M_FLAGS
   WHERE PKEY = M_PKEY;

END LOOP;
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@remotedb1(C);

Now I have a specific db-link name, remotedb1, in my package body, while I need to retarget it to another db-link rather often. I tried creating a synonym for a db-link, but that wouldn't work. Is there another way around it other than using a &&variable and recompiling from source? Will wrapping each call in EXECUTE IMMEDIATE work?


texts: need help joining two tables with a having count clause

texts:
I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of.  pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis.  I need to pull the names of patients who have one and only one diagnosis.  I can join the tables on patient_id.  here is what i wrote:

select
PTC_DIAGNOSIS.PATIENT_ID,
PT_BASIC.PATIENT_CODE,
PT_BASIC.NAME_FIRST,
PT_BASIC.NAME_LAST
from PTC_DIAGNOSIS
inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID
where PTC_DIAGNOSIS.create_date>'12/01/2015'
group by PTC_DIAGNOSIS.patient_id
having COUNT(*)=1

I get the error Column 'PT_BASIC.NAME_FIRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have moved columns around, tried having pt_basic first and re-written it 15 different ways with similar errors every time.  i feel i am close, but maybe not.

thank you


texts: Is there a timeout option for remote access to PostgreSQL database?

texts:
I'm working via pgAdmin III on a remote PostgreSQL database. After a not so long period (say 10-15 min.) without doing anything in pgAdmin, the connection automatically expires. I therefore have a error message asking if I want to re-connect. This takes about 10 sec. and the database structure collapses, so I have to re-open the schemas I had open before.

Is there a way to change a timeout parameter somewhere so prevent the connection to expire for a longer period of time?


texts: How to join different tables with same propriety tables?

texts:
Which would be the best solution to join 4 different tables with X tables that has to be the same for the 4 tables?

Let's presume we have 2 tables MENU and PLATES each of that tables could be joined with tables like IMAGES, DESCRIPTIONS, ALLERGENS and other tables that will give some properties to MENU and PLATES

Which would be the best practise to join those property tables with the main ones?

Should i create in each property table a column for each main table as foreign key? Like this:

+----+--------------+--------+----------+
| ID | DESC         | ID_MEN | ID_PLATE |
+----+--------------+--------+----------+
| 1  | PIZZE        | 1      |          |
+----+--------------+--------+----------+
| 2  | CHICKEN SOUP |        | 4        |
+----+--------------+--------+----------+

Or should i create a intersect table for each main table that has FK for each propriety table like this:

+--------+---------+-----------+
| ID_MEN | ID_DESC | ID_IMAGES |
+--------+---------+-----------+
| 1      | 2       | 3         |
+--------+---------+-----------+


texts:
I'm trying to understand the whole subject of worker threads for multiple instances, there's lots of advice on configuring workers but nothing I can find which directly addresses when the server has multiple instances.

Each instance will/can auto configure workers based upon the cpu count, if I have 4 instances for example the theoretical worker count will be 4 times that of a single instance, obviously, but is that actually good .. Am I likely to experience thread starvation? Assume a low cpu count.

I can't publicly discuss the finer details behind the question, however as an example a 4 cpu box would auto configure 512 workers - fine, but with 4 instances the auto config would be 2048 workers ( max )  assuming every instance is busy and gets close to its worker count ( including background workers )  potentially might I expect to have issues?

I have two instances currently installed, not a full database population and a combined worker count of around 400, hopefully that gives the background to my question.


texts: Import data into AWS RDS sql server through SQL Server management studio

texts:
I have created an RDS instance running SQL Server SE, this does not allow remote desktop access, so I also have an EC2 instance running Windows Server 2012 base, and I have installed SQL Server Management Studio and I'm able to connect and login using the master user and password.

I want to restore a database .bak file. So I used the SQL Server management studio and chose the option Databases ->  ‘Restore Database'. I chose ‘File’ as the backup media and then click ‘Add’. When I click on Add, it throws the below error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.

I looked into the internet for answers and some one suggested to grant sysadmin role to the master user. When I try to do that, I get an error saying "The user does not exist or you do not have permissions to do so"

I have run out of ideas on how to resolve the issue. Any help is greatly appreciated.


texts: ComputerNamePhysicalNetBIOS and "Current Host Server" shows different machines

texts:
I have a SQL Server 2008 R2 instance on a two node (say names X and Y) Windows server cluster (Windows Server 2008 R2 Enterprise).

Server Manager on both nodes shows 'X' as the 'Current Host Server'.

However, when I run  ServerProperty('ComputerNamePhysicalNetBIOS') it returns 'Y' which should be the current active node.

If I RDP to the SQL's IP, it indeed connects to Y.

Which one is the correct one?
Why does Server Manager shows X as the active node?

Also, ServerProperty('MachineName') returns neither X nor Y but 'Z'. Z is the name of neither computers. What does it represent?

According to BOL for ComputerNamePhysicalNetBIOS (which returns Y as above):

  If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.

Then, why does it return Z?

These machines are not virtualized, they are physical. Does it mean Z is the virtual server name of the cluster? "current host server" seems to refer to the "Active Node in the cluster" (from an online forum). I am not entirely sure what is meant by that with two FCIs and one active on each node.


texts: PostgreSQL pass table name and column name as arguments to function

texts:
I have two almost similar tables: posts and comments.

Both of them has column id and votes.

Also i have two tables posts_votes and comments_votes that also has an almost similar structure: both of them has column value (can be -1 or +1), has user_id as FOREIGN KEY to user_account table, and field post_id or comment_id depending on which table it is, for posts_votes it will be post_id, and for comments_votes it will be comment_id.

What i need, is to write a trigger that will update votes counter for posts and comments table each time the row inserted/deleted/updated in posts_votes or comments_votes table.

It can be easily done by writing to different triggers, with difference only in table name for update query, and column name for WHERE statement.

This is example of such trigger for posts table (full DDL here):

CREATE OR REPLACE FUNCTION update_votes_counter() RETURNS TRIGGER AS
$$
  BEGIN;
    IF (TG_OP = 'INSERT') THEN
      UPDATE posts SET votes = votes + NEW.value WHERE id = NEW.post_id;
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE posts SET votes = votes - OLD.value WHERE id = OLD.post_id;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE posts SET votes = votes - OLD.value + NEW.VALUE WHERE id = NEW.post_id;
    END IF;
  END;
$$
LANGUAGE plpgsql;

What i want, is to write single function with arguments as table name and column with reference, so i can use this function in both cases for two tables.

It will be like this:

update_votes_counter('posts', 'post_id');
update_votes_counter('comments', 'comment_id');

and the function should build query like this:

UPDATE $table_name$ SET votes = votes + NEW.value WHERE id = NEW.$column_name$

Thanks for any advices!


texts: Some queries not logging using RDS's SQL Server with C2 Auditing enabled

texts:
Similar to a past issue I had with audited queries not showing when using Extended Events, I'm having an issue with queries not logging when using C2 Auditing on an RDS SQL Server instance.  (I would prefer to use Extended Events for my auditing, but this apparently cannot be enabled on RDS SQL Server instances currently.)

In this case, I enabled C2 Auditing via an RDS Parameter Group.  I verified that C2 auditing is enabled by going to my server instance's Properties (via SSMS).  I created a database, mytestdb, and then created a table within it, mytesttable.  I ran a few queries (e.g., INSERT INTO mytesttable ...; SELECT * FROM mytesttable).

I then determined the path to my trace log file (SELECT id, path, is_default FROM sys.traces;) and then retrieved the SELECT queries that have been run: SELECT starttime, textdata FROM fn_trace_gettable('D:\RDSDBDATA\Log\log_2.trc', DEFAULT) WHERE textdata LIKE N'select%' ORDER BY starttime;.

The result is my queries on mytesttable did not show up. My queries on the trace table do show up though, so some queries are being logged.

I also tried a query on one of the master database tables, but that didn't show up as well.  I also tried disconnecting from the database server and reconnecting (in SSMS), hoping that ending the session might clear some buffering going on, but that didn't help with getting the queries to show in the audit results.

I connected to the RDS instance with my "Admin" user account (created during RDS setup) and there is no associated Active Directory. This is an "SQL Server Standard Edition" RDS instance.

Any ideas on why these queries would not be logged?  Any other debugging ideas? Thanks!


texts: MySQL permits setting a foreign key constraint that references only one column of a composite primary key

texts:
In the database structure depicted in the diagram that follows

the columns key1 & key2 make up the composite primary key of table4, but in table3 I'm able to set up a foreign key constraint that makes a reference to key1 only.

Why does MySQL allow this? Does the above database structure make no sense at all?


texts:
I have a program that take 2 databases as input but (in my case) will only use one of those.

There is a INI file that looks like this:

DataSource1=irrelevant_yet_valid_string
Datasource2=DriverID=MSSQL;Server=blablabla;
OtherParameter=something very important

But in fact, I have to give a valid connection string for DataSource1 as the program first check all the provided parameters are valid (even if useless)

I have to send the application and the INI file to a client.
I will ask him to modify Datasource2 to match his environment

Is there a connection string I can provide so that DataSource1=magicString would provide a valid connection on any (mostly vanilla) MSWindows system?

In this particular case, the host computer should run on  Windows Server 2012 R2 system, but I will probably need the same on other windows host.

Edit: To clarify a bit:

My program do something like this

 My connection = new SqlConnection()
 My connection.ConnectionString = param_DataSource1;
 My connection.connect;

And that's the last time this connection will be used

This will crash if param_DataSource1 is random, but could there be a parameter that always work

A bit like

ping (someIP)

will always work if someIP = "127.0.0.1"

Edit2:
my problem is not specific to SQL server. In my exemple, the second conection is indeed SQL server, but it's mostly irrelevant (at most, I am sure that SQL server pilots can be used for this client)

What I want a connection string that work on a (somewhat standard) MSWindows system.


texts: Merge two records in the same table, keeping foreign key relationships intact for both

texts:
I have a table, called SITES, that has three columns, lets say it looks like this:

ID  Name        Path
1   Google      http://www.google.com/
2   Microsoft   http://www.microsoft.com/

I also have a related table called Logs that looks like this:

ID  SiteID   LogData
1   1        --data--
2   1        --more--
3   2        --other--
...

The SITES table gets populated both by users of the system and also occasionally some batch processes. From the batch process, I don't always have the 'name' available, so a record is created that looks like this in SITES (when searching by Path yields no result), because the batch process is mostly interested in creating data in the Log table.

ID  Name        Path
99              http://arstechnica.com/

The problem situation arises like this:

1) User creates new SITE, but doesn't enter the Path (doesn't know it, or in some cases it doesn't yet exist) so the SITES table looks like this:

ID  Name        Path
1   Google      http://www.google.com/
2   Microsoft   http://www.microsoft.com/
3   Yahoo

2) The batch process comes along and needs to add a log for http://yahoo.com, searches SITES by Path and does not find it, so it makes its own and the result is this:

ID  Name        Path
1   Google      http://www.google.com/
2   Microsoft   http://www.microsoft.com/
3   Yahoo
4               http://yahoo.com

My question is, how can I merge Record 3 and record 4 while preserving the referential integrity of both records? Lets say for the sake of argument, that each record has several related records in both the Log table, as well as some other tables.

I understand that it will be a manual process of identifying records that need to be updated, so let any solution assume that I have audited the list and found all of the "duplicate" records.


texts: Can I have Oracle primary 11.2.0.1 and standby 11.2.0.3?

texts:
Can I have data guard system like:

Primary: 11.2.0.1
Standby: 11.2.0.3

In logical standby ?

In physical standby versions(releases) must be the same?


texts:
Introduction

I created a toy example to extract the problem into a smaller part.
Here is the schema definition with two tables users and events, users can have multiple events:

create table users (
    id int, name text, history jsonb
);
insert into users(id, name) values (1, 'Mike'), (2, 'Jake'), (3, 'Toots');

create table events (
    id serial, user_id int, external_id int, type text, timestamp timestamp
);

# Generate test data
insert into events (external_id, user_id, type, timestamp)
    select x % 5 + 1, x % 3 + 1, 'random', NOW() - '1 day'::interval * (random()::int * 100) from generate_series(1, 3000000) as x;

With the actual data, the users table would be lower volume table less than ~1M records and events table quite high ~25M records.

Problem

Update users table history column which would be an aggregated result of the events table as jsonb. The events should be grouped together by external_id, user_id based on that the object with min(timestamp) and max(timestamp) would be great.

History example:
history: [{start: '2018-12-12', end: '2018-12-20', external_id: 1}, {start: '2018-11-12', end: '2018-11-20', external_id: 2}]

My example, the grouping part seems to be not correct as an only single object is returned in the subquery. Also by performance wise, this is not the best solution.

 update users
    set history = e.history
    from (
        select
            user_id,
            json_build_array(
                json_build_object(
                  'start', MIN(timestamp),
                  'end', MAX(timestamp),
                  'external_id', external_id,
                  'is_fetched', true
                )::jsonb) history
        from events
        group by external_id, user_id
    ) e
    where users.id = e.user_id


texts: List all MongoDB databases from Linux bash terminal

texts: Can you list all databases in MongoDB from a linux bash terminal? I cannot find the correct command. I am trying to list all so as that I can write a bash script to backup all DB's individually. Is there a better way?


texts: Same column present on two or more foreign key constraints

texts:
A simple schema leading to my question is the following:

CREATE TABLE A (
    AID INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE A_COPY (
    AID INT,
    CID INT,
    PRIMARY KEY (AID, CID),
    FOREIGN KEY (AID) REFERENCES A(AID) ON DELETE CASCADE
);

CREATE TABLE R (
    AID INT,
    CID INT,
    EXTRANUM INT,
    PRIMARY KEY (AID, CID, EXTRANUM),
    FOREIGN KEY (AID) REFERENCES A(AID) ON DELETE NO ACTION,
    FOREIGN KEY (AID, CID) REFERENCES A_COPY(AID, CID) ON DELETE CASCADE
);

This SQL Fiddle contains sample data and queries along with the schema shown above.

Here, as you can see, we build up on the A table by extending it (say that A_COPY is a "weak entity" in an Entity Relationship diagram) and then use it in some relation R. The question is, that here (table R) we use two different foreign key constraints where one column is the same in both constraints. That might, or might not be meaningful.

So, one thing is whether this would be meaningful. I believe it would, since I might (for some reason) want to opt for different foreign key referential actions when deleting from table A or A_COPY respectively, as shown here.

The second question is how do these statements get implemented in MySQL with InnoDB tables. Generally, the question is: what is the chosen rule for foreign key constraints on the same table with two or more identical columns part of the respective foreign key constraints (e.g. here AID is used identically in two foreign key constraints on the same table R)?


texts:
\d aggregate

              Materialized view "public.aggregate"
          Column      |            Type             | Modifiers
    ------------------+-----------------------------+-----------
     id               | integer                     |
     searchable       | text                        |
     name             | character varying(255)      |
     source_type      | character varying(255)      |
     source_id        | integer                     |
     latitude         | double precision            |
     longitude        | double precision            |
     created_at       | timestamp without time zone |
     updated_at       | timestamp without time zone |

Indexes:
    "aggregate_lat_lng_point" gist (point(latitude, longitude))
    "searchable_tsvector" gin (to_tsvector('english'::regconfig, COALESCE(searchable, ''::text)))

Query:

EXPLAIN ANALYZE SELECT name FROM aggregate
WHERE point(53.574753, -2.1) <@> point(latitude, longitude) < 100;

Result:

 Seq Scan on aggregate_mv  (cost=0.00..23.01 rows=172 width=516) (actual time=0.019..0.522 rows=458 loops=1)
   Filter: (('(53.574753,-2.1)'::point <@> point(latitude, longitude)) < 100::double precision)
   Rows Removed by Filter: 320
 Total runtime: 0.579 ms
(4 rows)

As you can see, the index is not being used. The table contains 525 rows, and the above query returns 195 rows. The table is a materialized view, but that shouldn't make any difference, I have other indexes which work fine. Any ideas as to why my index is not being used in the above query?


texts: Grant privilege to see column names in null constraint violations when inserting in views

texts:
My database has two schemas, one with the actual tables and one that is used by an application. The application schema doesn't have any privileges on the actual tables. Instead there is a view for each table that limits the rows the application schema sees (but not the columns, the view is created as SELECT * FROM ...). For all those views SELECT, INSERT, UPDATE and DELETE was granted to the application schema.

If the application user does an insert into the view and violates a not null constraint, no column name is given. The actual error message is

  ORA-01400: cannot insert NULL into (???).

I see that this makes sense from a security standpoint, but I don't care if the application user can see the name of the column of the actual table since it's always the same as the column name in the view. If it isn't possible otherwise, it would even be OK for me if the application schema sees the table name. Is there some privilege that can be granted that allows seeing the column name without allowing any actual queries on the table?


texts: SQL Server Reporting services migration (2008 to 2012)

texts:
We have a SSRS 2008 (not R2) instance with a few hundred reports, and now, on a new machine we have installed a 2012 instance of SSRS. We need to migrate all the reports, while keeping all their rights and privileges, plus the datasources with credentials (some of them have local data sources). Is there a way to achieve this? The high number of reports does not allow us do it manually one by one. We tried to migrate a report, but as I said, the rights and credentials were lost. Any ideas?

Thank you in advance!


texts:
The variable I am trying to change is innodb_buffer_pool_size. As you can see below the settings is 512M. But SELECT @@innodb_buffer_pool_size; returns 134217728.

When I execute: /usr/libexec/mysqld --help --verbose

My resulting config is:

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

/etc/mysql/my.cnf - does not exist
/etc/my.cnf - is shown below
~/.my.cnf - when I cd ~/ I am taken to /root where no my.cnf files exist. Does mysql run as root, would it be taken to the same directory? How can I tell?

Here is my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

skip-networking

innodb_thread_concurrency = 6
innodb_flush_log_at_trx_commit = 2
thread_concurrency = 12
table_cache = 1024
query_cache_size = 64M
query_cache_type=1
query_cache_limit = 4M
expire_logs_days=10
max_binlog_size=100M
join_buffer_size = 8M
tmp_table_size =256M
max_allowed_packet = 2M
thread_stack = 192K
thread_cache_size = 32
thread_concurrency = 4
table_open_cache  = 4096
myisam-recover=BACKUP
max_connections        = 2500
max_heap_table_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
innodb_additional_mem_pool_size = 8M
innodb_log_buffer_size = 4M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 512M
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:256M:autoextend
innodb_autoextend_increment=512
slow_query_log = Off
slow_query_log_file = /var/www/html/log/mysql/slow-queries.log
long_query_time = 2
innodb_file_per_table = 1

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 32M


texts: PostgreSQL - CAST vs :: operator on LATERAL table function

texts:
While I can
SELECT elem[1], elem[2]
FROM   ( VALUES ('1,2'::TEXT) ) AS q(arr),
       LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem
;

using an explicit call to CAST, I can't
SELECT elem[1], elem[2]
FROM   ( VALUES ('1,2'::TEXT) ) AS q(arr),
       LATERAL String_To_Array(q.arr, ',')::INT[] AS elem
;

using the implicitly calling :: operator:

ERROR: syntax error at or near "::"

One other location at which an explicit CAST is required:
CREATE INDEX ON ... ( CAST(<straw> AS <gold>) );

I doubt there is a syntactical reason, e.g. using extra enclosing parenthesis - which is incorrect here.
Is the explicit function call simply needed at this point as part of the low level implementation? Or does it follow any language rules?


texts: Accessing MySQL through dynamic IP address

texts: I am working on creating a MySQL database server for one of my school projects. I want to allow my teammates to access the server remotely using school network, but I discovered that their IP addresses varied whenever they reconnected to the school network, and it won't be desirable to manually add all the new IP addresses to the user table. Is there any trick I could use to overcome this problem?


texts: How to provide a SQL Server database with a C# application

texts:
I am working on a C# application which depends on a few SQL Server databases. Now, in my development environment, I am using SSMS (SQL Server Management Studio) to create and manage my databases. My connection string looks like this:

 SQLConn.ConnectionString = "Network Library=DBMSSOCN;" & _
                        "Data Source=xxx.xxx.xxx.xxx,1433;" & _
                        "Initial Catalog=mySQLServerDBName;" & _
                        "User ID=myUsername;" & _
                        "Password=myPassword"

I am not sure how to properly ask the question. The point is, how do I provide the databases then? I mean:

Should I backup my databases, ask the client to install both SQL Server instance and SSMS, then restore the databases from SSMS?
Should I provide my databases and ask the client only to install SQL Server instance? In this case, how should they restore the databases?

Please, I am not very experienced, that's why I am asking.


texts: MySQL: why undo log is required in the processes of database recovery?

texts:
Let's imagine that an incident happens when you updates.

start transaction;
update users set gender = 1 where id = 3;

Undo log space is allocated.
Previous values are copied to undo log
Record of undo log modifications are written to redo log.
page is modified in buffer pull.
Record of page modification are written to redo log, and this page is marked as dirty.
then, an incident happen, MySQL crashes!

we restart MySQL, then recover from an incident, there are three steps:

Analysis
Redo, which will restore the state when MySQL crashed.
Undo.

For step3, we are undo uncommitted transactions through undo log in backwards order.

My questions are:

Can we do step3 by undo redo log that caused by uncommitted transaction, rather than apply undo log?
Why undo log is required in the step3?
Is undo log for recover from an crash that happens within recovery?


texts: Stored Procedure Processing and Error Log

texts:
I used to work for a company where they had this 3rd party Data warehouse Solution. Obviously all the objects and tables were hidden in the Supporting database so I don't have clear idea on what exactly happened inside some of the stored procedure. I saw this interesting stored procedure there and want to replicate that in my own solution but I just can't get my head around on how it works.  I am describing the stored procedure below and it will be really helpful if someone can give me some ideas on how to achieve this. Even better if you can suggest me how I can make this even better.

Stored procedure was called process log.  It had Parameters like DBID, ObjectId, Step, Status, Remarks, Reads, Inserts, Updates, Deletes

What we had to do was, inside every stored procedure, we have to execute this stored procedure with Status as 2 (In Progress)  During the process where can execute this stored procedure multiple time at the end of each step or section after increasing the value of variable step. based on the row counts of Insert update select and delete statements we were supposed to record values in respective stored procedure parameter variables.
In the end you can execute same stored procedure with Status of 3 (Completed)  or if the procedure ended up in catch block, status will be 4 (Failed) in the remarks section we can copy SQL's error messages.

To see all this information, we were given access to a Report, For which obviously I didn't have the source code but the report showed what time stored procedure started when it finished, what was the status how many inserts updates deletes and reads it did. if it failed, what was the error message?

I already have few ideas for improvements to store, who started it?, what were the values of parameters? For who started the stored procedure part, I have a confusion. Most of these stored procedure run as part of different Jobs. All our jobs run as a Service Account user but jobs are started manually by various users. I need to find out which user started it, as inside stored procedure, as current user, it will always show service account. Also for Values of Paramters, is there a better dynamic way of finding this out? rather then setting a value for a variable manually. I was hopping to use output from INPUTBUFFER but it only shows the name of the parameter not values.

If someone can guide me regarding the back end table structure and script for this auditing SP, it will be really helpful. Also any more ideas for improvement are welcome as well.

My main confusion:
I believe they had some table where these stored procedure values were stored and if the SP is running already, they did update in the record then doing an  Insert but how would they identify to do an insert instead of update in the scenario where stored procedure failed critically and catch block wasn't executed.


texts: Set indexes for database used for login and register actions

texts: I am thinking on how to set indexes to improve performance of a database for register and login actions.I have seen that alphabetical indexes are not good so I don't know how to do...Can you help me?In this type of databases what are the methods to implement performance?


texts:
We have the generic idea of a rule with specific implementation details stored in their own table.
We currently have the following schema:

Rule
 * id
 * name
 * ruleAId
 * ruleBId
 * ....
 * ruleNId
 * created
 * updated

RuleA
 * id
 * ...

RuleB
 * id
 * ...

....

RuleC
 * id
 * ...

The part we are not enamoured with is having a nullable column for each of the specific rule implementations.

We have two possibly solutions, but both have significant short comings:

Move the relationship to specific implementation - DOS the database

Store the rule Id on the specific implementation table, so our schema becomes:

RuleA
 * id
 * ruleId
 * ...

Rule
 * id
 * name
 * created
 * updated

This as the title suggests, would mean having N queries each time you needed to look up a rule implementation. Presumably bad for performance.

Forgo referential integrity - Yolo integrity

If we remove the need for referential integrity from the Rule table, we'd only require
a single column to store the specific implementation primary key and an enumeration to tell us which table the id is for, giving us:

Rule
 * id
 * name
 * implementationType
 * implementationId
 * created
 * updated

RuleA
 * id

...

Ideally we'd be able to have option two WITH Referential integrity. Where instead of implementationId being a bigInt it could be the FK or RuleA or RuleB or ... RuleN.

This can be encoded in the application itself, but it is nice for the database to keep us honest.

We are heading towards option two, but am interested if people think I have missed a more idiomatic SQL solution?

Adding an example schema:

CREATE SEQUENCE s_rulea_id;
CREATE TABLE rulea (id BIGINT PRIMARY KEY default nextval('s_rulea_id'));
CREATE SEQUENCE s_ruleb_id;
CREATE TABLE ruleb (id BIGINT PRIMARY KEY default nextval('s_ruleb_id'));
CREATE SEQUENCE s_rulec_id;
CREATE TABLE rulec (id BIGINT PRIMARY KEY default nextval('s_rulec_id'));
CREATE SEQUENCE s_rulen_id;
CREATE TABLE rulen (id BIGINT PRIMARY KEY default nextval('s_rulen_id'));

CREATE SEQUENCE s_rule_id;
CREATE TABLE rule (id BIGINT PRIMARY KEY default nextval('s_rule_id'),
                  name CHARACTER VARYING(128) NOT NULL,
                  ruleAid BIGINT DEFAULT NULL references rulea(id),
                  ruleBid BIGINT DEFAULT NULL references ruleb(id),
                  ruleCid BIGINT DEFAULT NULL references rulec(id),
                  ruleNid BIGINT DEFAULT NULL references rulen(id));

INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id'));
INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id'));
INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id'));

INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id'));
INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id'));
INSERT INTO ruleb (id) VALUES (nextval('s_ruleb_id'));

INSERT INTO rulec (id) VALUES (nextval('s_rulec_id'));
INSERT INTO rulec (id) VALUES (nextval('s_rulec_id'));
INSERT INTO rulec (id) VALUES (nextval('s_rulec_id'));

INSERT INTO rule (name,ruleBid) VALUES ('B 1',1);
INSERT INTO rule (name,ruleBid) VALUES ('B 2',2);
INSERT INTO rule (name,ruleBid) VALUES ('B 4',3);
INSERT INTO rule (name,ruleBid) VALUES ('B 4',4);
INSERT INTO rule (name,ruleBid) VALUES ('B 5',5);
INSERT INTO rule (name,ruleBid) VALUES ('B 6',6);

INSERT INTO rule (name,ruleCid) VALUES ('c 1',1);
INSERT INTO rule (name,ruleCid) VALUES ('C 2',2);
INSERT INTO rule (name,ruleCid) VALUES ('C 3',3);

select * from rule


texts:
I'm new to databases and trying to design a model in Django in which I store Contest info and the Players who play the contest.  My first approach was below in which I created a Contest table that had a 1-to-many relationship with Entries.

class Contest(models.Model):
    start_date = models.DateTimeField()
    sport = models.CharField(max_length=200)
    contest_name = models.CharField(max_length=200)
    buy_in = models.IntegerField()

class Entries(models.Model):
    name = models.CharField(max_length=200)
    profit = models.FloatField()
    contest = models.ForeignKey('Contest',on_delete=models.CASCADE)

However when I think about my future usage I'm going to want to, for example, query all players of a specific sport and get all of their profits.  Or I may want to get the names of the top performers from the past month.  I feel the way the current DB is set up won't be really efficient and as my DB grows those queries will get more intensive if I have to filter all the contests by a certain date, and then check if that player was even in the contest...etc

Essentially adding the sport and date field in Entries will make things much easier for me but from what I understand you want to avoid duplicate fields so I'm trying to approach this in the "right" way.  Thanks.

UPDATED MODEL APPROACH

class Contest(models.Model):
    start_date = models.DateTimeField()
    sport = models.CharField(max_length=200)
    contest_name = models.CharField(max_length=200)
    buy_in = models.IntegerField()

class Player(models.Model):
    name = models.CharField(max_length=200)
    contest = models.ManyToManyField(Contest)

class Entries(models.Model):
    num_entries = models.IntegerField()
    profit = models.FloatField()
    player = models.ForeignKey('Player',on_delete=models.CASCADE)

So what I decided to was create a many-to-many relationship between the Contest and Player tables.  And a 1-to-many from the Player to the Entries.  I'm wondering is this better?


texts: SQL Server TempDB on Local SSDs with VMs

texts:
Two physical Servers in a Cluster. Multiple SQL 2016 Virtual Machines. Fibre Channel SAN Storage used for the Virtual Machine Operating System and Database Data Files. I've seen multiple sources claim that using local SSDs for the TempDB is best practice.

How should the Local Storage be presented to the Virtual Machine for the TempDB? If the Virtual Machine migrates within the cluster, it will lose the virtual hard drive. How can this best practice be implemented with the above scenario.


texts: What to do with WAL files for Postgres Slave reset

texts:
So last night our PG Slave ran out of space after a lot of reconfiguring of disk space, new drives etc its now reporting the following error:

FATAL:  could not receive data from WAL stream: FATAL:  requested WAL segment 00000001000018F70000008A has already been removed

From the reading around this I've done, it appears that the only solution is to re-sync the slave with pg_start_backup() et al. Based on this, I have a few questions.

Is there a better way of fixing the slave that I've simply missed or overlooked?
Do I need to clear out the WAL files on the slave and/or master prior or during the backup?
Does pg_start_backup lock the database during this time?

As requested, the log file can be found: http://pastebin.com/9F8vJh6R, have removed the rest of the file as its just 5 hours of the same repeated error

Many thanks


texts: database is not responding for hours trying to import a 2mb csv with MariaDB

texts:
I'm trying to import some csv files into my mysql mariadb database, but I'm having problems with some files.
Importing csv with a line with only spaces (2MB of space character) the database stops for hours without giving an answer.
I then created a csv file with only spaces, copying it exactly from one of the many files that cause the problem: 1.888.653 byte of spaces and when I run the query by importing this file the server no longer responds.

MariaDB version: 10.1.31-MariaDB - mariadb.org binary distribution
Protocol version: 10

This is the query I run:

LOAD DATA LOCAL INFILE
'x:/storage/sizetest.txt'
IGNORE
INTO TABLEdatatest
LINES TERMINATED BY "\n"
(@line)
SET
email= REGEXP_SUBSTR(@line, "^[^;:,]*"),
password= REGEXP_SUBSTR(@line, "[^;:,]+$");

This is the server structure:
email: varchar(255) utf8_bin
password: varchar(255) utf8_bin

My.ini configuration:

key_buffer = 64M
max_allowed_packet = 1M
sort_buffer_size = 5M
net_buffer_length = 8M
read_buffer_size = 5M
read_rnd_buffer_size = 5M
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
innodb_buffer_pool_size = 2650M
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 2048M
innodb_log_buffer_size = 512M
bulk_insert_buffer_size = 2048M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50

Log files do not report any errors while waiting. I waited more than 2 hours and then I stopped loading, and every time I encounter a file like that the server no longer responds. How can I solve this problem?


texts:
I have two virtual servers.

On both servers, I have a table "users".

I have an update trigger on the table "users" on both servers so when one server table is updated, it should be updated on the second server as well.

I've searched for a long time and everyone says "MySql does not do recursion. Period.". This is true if both tables are on the same server or db.period.

So when I try to update one table row it gives me an error:

"Error Code : 1159
Got timeout reading communication packets";

When I add IF statement that won't update anything in one trigger or another, no errors are shown.

One way to do this is by adding one field that will tell us whether or not we should update row using the trigger. I would like the MariaDB version of TRIGGER_NESTLEVEL available in Microsoft SQL Server.


texts:
My database is in TB with different schemas. I would like to search in each schema following information:

Schema name
Table name
Table size
Rows count
Table Created date

Where table name contains: *.bak or dropped_* or *_drop


texts:
The following is part of a bigger system. The tree data table and temporary table cannot be changed easily so I need to work with those. The tree structure is more complex (deeper and wider) but the following are illustrative of the problem I am trying to solve.

I have a simple tree structure:

AAA
  - aaa
      - 111
      - 222

that is represented in data as:

Id  ParentId  Text
------------------
1   NULL      AAA
2   1         aaa
3   2         111
4   2         222

for a common function there is a temporary table where I want to determine which records need to be used to ultimately build the path to a particular item:

Id  ParentId  Text  UseRecord
-----------------------------
1   NULL      AAA   False
2   1         aaa   False
3   2         111   False
4   2         222   False

So for instance if I wanted to find which records are necessary to build the tree to item "222" after updating the temporary table it would look like:

Id  ParentId  Text  UseRecord
-----------------------------
1   NULL      AAA   True
2   1         aaa   True
3   2         111   False
4   2         222   True

What is the SQL statement I need to achieve the above result assuming it updates UseRecord and its only input is the Id value of 4 (ie "222"'s record).

This has to be really simple and fast, but for the life of me I cannot figure it out. All pointers appreciated.


texts: What's the low-down on MariaDB JSON support?

texts:
Does MariaDB ever plan to have full JSON support or is it just going to be an alias forever?

It's a bummer to set constraints definition in the table just to make it work properly. Application-level migration libraries (like Phinx) doesn't support it so you have to do extra work which defeats the purpose of migration libraries.


texts: Transaction Log is big! But simple recovery mode is chosen!

texts:
I am wondering: We have a Database on our MS SQL Server 2012. The recovery mode is "simple".

The DB itself is about 2.7GB big. I have an MAX Size for Trn-Log configured (3GB).

Now i made a Maintenance Plan with "reorganize Index Task" --> "Update Statistics".

The strange thing ist that when i run the Maintenance Plan the Log of the DB in "simple" Mode grows and grows since the 3GB are filed and i get an error...

Why the Trn-Log is used even in simple mode? And why the 3 GB seems to be not enough?


texts:
On my local machine, when I want to start the postgres server (postgres was installed via Homebrew), I do this

pg_ctl -D /usr/local/var/postgres -l logfile start

I just installed postgres on a ubuntu vps (without Homebrew and not on OSX obviously) and the files are obviously set up a little differently, however, based on what I did on the local machine to start pg, I tried to find the path to postgres folder on the vps. From my home/michael directory, I backed up two levels cd ../../ to a folder which contains these folders

bin   etc         lib         mnt   root  selinux  tmp  vmlinuz
boot  home        lost+found  opt   run   srv      usr
dev   initrd.img  media       proc  sbin  sys      var

Inside etc, there's a postgresql inside of which there's a 9.1 folder, inside of which is main that has all these files

environment  pg_hba.conf    postgresql.conf
pg_ctl.conf  pg_ident.conf  start.conf

Based on how I start pg on my local machine, I'm assuming pg_ctl.conf is the file I'm looking for. I tried to start pg this way

 pg_ctl -D /etc/postgresql/9.1/main -l logfile start

but I get the response

-bash: pg_ctl: command not found

I'm assuming that I have to start the server once before launch of the app and then let it run, but I can't get it started.

Update

Once I finished installing postgres, I ran this command

sudo service postgresql restart

I'm not sure if that starts postgres once and I never need to start it again (hence this question is moot), or if that was just something I do at install and then start it everytime I use it as I do on my local machine.


texts: Problems while connecting to a newly installed PostgreSQL 9.2 on same box as 9.1

texts:
I have a new project at work that is using a PostgreSQL 9.2.  But, I'm still having to support  a project that uses 9.1.  So, I'm trying to configure my local dev box to have both installed.

I have gotten 9.2 installed and confirmed it runs fine.

However, I can't connect to it.

xxxxxxx@xxxxxxx-desktop-ubuntu:~$ sudo /etc/init.d/postgresql restart
[sudo] password for xxxxxxx:
 * Restarting PostgreSQL 9.1 database server                                                                     [ OK ]
 * Restarting PostgreSQL 9.2 database server                                                                     [ OK ]
xxxxxxx@xxxxxxx-desktop-ubuntu:~$ su postgres
Password:
postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5432
Password:
psql (9.2.4, server 9.1.9)
WARNING: psql version 9.2, server version 9.1.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# \q
postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$ psql -h localhost -p 5433
Password:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"
postgres@xxxxxxx-desktop-ubuntu:/home/xxxxxxx$

So, how do I connect to this new instance?  I thought that the Ubuntu/OS postgres user would allow me to connect, but it doesn't.

Other info:

9.1 is on the default port: 5432
9.2 is on port: 5433
Using Ubuntu 12.04


texts: Allow users to only view the databases they have access to

texts:
I wonder if there's any way to allow a user to only view the specific databases they have access to. For example.
Let's say we have a alwaysOn with one local only database and 10 databases that is added in the AO. The user need read rights to one of the 10 databases added in AO. But when giving user rights to that database and he connects to the AO he can view every database but only access the one he got rights to of course. And if the AO is active where the local database is located he will also see that one.

We have tried a few things but nothing successful and from what we've been reading it doesn't seem to be a way unless the user is granted dbowner rights.

Anyone got any ideas?


texts: How to clear PostgreSQL temp files after crash

texts:
I faced cumputer crash while doing clustering. The temp files has not got cleared after computer resume. How to clear them safely now?
Update
Windows OS. pgsql_tmp directory is empty but disk space still occupied by clustering temp files located in the table base folder.